Free ebook: Introducing Microsoft SQL Server 2012

Free ebook: Introducing Microsoft SQL Server 2012


http://blogs.msdn.com/b/microsoft_press/archive/2012/03/15/free-ebook-introducing-microsoft-sql-server-2012.aspx

Search for a particular keyword in the SQL Server jobs

SQL SERVER 2005/2008:

USE [msdb]

GO

SELECT j.job_id ,

s.srvname ,

j.name ,

js.step_id ,

js.command ,

j.enabled

FROM dbo.sysjobs j

JOIN dbo.sysjobsteps js ON js.job_id = j.job_id

JOIN master.dbo.sysservers s ON s.srvid = j.originating_server_id

WHERE js.command LIKE N'%Keyword%'

GO

SQL SERVER 2000:

USE [msdb]

GO

SELECT j.job_id ,

j .originating_server ,

j .name ,

js .step_id ,

js .command ,

j .enabled

FROM dbo.sysjobs j

JOIN dbo.sysjobsteps js ON js.job_id = j.job_id

WHERE js.command LIKE N'%Keyword%'

GO

Error during SSRS Report Processing

ERROR:

An Error has occured during report processing
Query Execution failed for dataset 'dataset name'
The server principal 'user/role' is not able to access the database 'dbname' under the current security context

SOLUTION:

sp_change_users_login 'update_one', User, Login, Password

Password is not needed if the User and Login already exists.
If a matching login does not exist, sp_change_users_login creates a new SQL Server login and assigns password as the password for the new login. password is sysname, and must not be NULL.

Reset the Identity column in a table

To check the identity value:

DBCC CHECKIDENT ('Tablename', NORESEED)

To reset the identity value to the next starting value:

If we give the number as 20,It will reset the identity value to\from 21

DBCC CHECKIDENT ('Tablename', RESEED, 20)

To Know the Backup set used to restore a SQL Server database

SELECT a.restore_date ,

a.destination_database_name ,

a.user_name ,

b.server_name ,

c.physical_device_name

FROM restorehistory a

INNER JOIN Backupset b ON a.backup_set_id = b.backup_set_id

INNER JOIN backupmediafamily c ON b.media_set_id = c.media_set_id

WHERE destination_database_name = 'databasename'

Copy one Column data from one table to other table in SQL Server

INSERT INTO NewTable ColumnName      
SELECT ColumnName FROM OldTable

Avoid/Remove displaying '1900-01-01 00:00:00.000' if date column is null

UPDATE YOURTABLE
SET ThatColumn = NULL
WHERE ThatColumn = '1900-01-01 00:00:00.000'

List all Tables in a Database

SELECT * FROM sysobjects WHERE xtype='U'

Find Log Size and Log Space Used on all databases

dbcc sqlperf(logspace)

Script: Extract data from all tables in a database to individual text files.

DECLARE @tablename  VARCHAR(256) 
DECLARE @cmd   VARCHAR(4000)
DECLARE @TABLE_NAME  VARCHAR(50)
DECLARE @DBname  VARCHAR(100)
DECLARE @TABLESCHEMA VARCHAR(100)
DECLARE @PathName VARCHAR(50)


DECLARE db_cursor CURSOR FOR
SELECT
  TABLE_DB     AS DBname,
  TABLE_SCHEMA AS TABLESCHEMA,
  TABLE_NAME   AS TABLENAME
FROM TABLE_NAMES_VIEW
OPEN db_cursor  
FETCH NEXT FROM db_cursor
 INTO @DBname,
      @TABLESCHEMA,
      @TABLENAME
WHILE @@FETCH_STATUS = 0  
BEGIN 
    SET @PathName = @DBname + '.' + @TABLESCHEMA + '.' + @TABLENAME
    SET @cmd = 'master.dbo.xp_cmdshell ''BCP '  + @PathName
      + ' out G:\output\' + @tablename + '.txt -t "|" -T -c'''
    PRINT @cmd
    EXEC (@cmd)
 FETCH NEXT FROM db_cursor 
 INTO @DBname,
      @TABLESCHEMA,
      @TABLENAME
END 

CLOSE db_cursor  
DEALLOCATE db_cursor

Change Column Data type in SQL Server

ALTER TABLE tablename
MODIFY COLUMNNAME Datatype.

Disable single constraint and ALTER TABLE MyTable CHECK CONSTRAINT

-- Disable single constraint


ALTER TABLE MyTable NOCHECK CONSTRAINT  myconstraint




--Enable single constraint


ALTER TABLE MyTable CHECK CONSTRAINT myconstraint

List All The Column With Specific Data Types

Simple script to identify all the columns with same datatype in specific database:
SELECT OBJECT_NAME(c.OBJECT_ID) TableName, c.name ColumnName
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE t.name = 'text' --change text to other datatypes
ORDER BY c.OBJECT_ID;
GO

Database Mail not showing under Management Node in SQL Server

Database Mail not showing under Management Node in SQL Server


Database Mail is not available in SQL Server 2005 Express Edition--Check


Select @@version

Import Data from .unl files to SQL Server

Rename the File to .txt


or




BULK INSERT dbo.table FROM 'C.\something\_something.unl'
WITH
(
DATAFILETYPE = 'char',
FIELDTERMINATOR = '#'
)

Save all DTS packages on server to files

This will save all dts packages on the server to storage files.
It uses a trusted connect to access the package - just change the LoadFromSQLServer call to use a sql server connection.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_SavePackages]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_SavePackages]
GO

Create procedure s_SavePackages
@Path varchar(128)
as
/*

*/

 set nocount on

declare @objPackage int
declare @PackageName varchar(128)
declare @rc int
declare @ServerName varchar(128)
declare @FileName varchar(128)
declare @FilePath varchar(128)
declare @cmd varchar(2000)
 
 select  @ServerName = @@ServerName ,
  @FilePath = @Path
 
 if right(@Path,1) <> '\'
 begin
  select @Path = @Path + '\'
 end
 
 -- create output directory - will fail if already exists but ...
 select @cmd = 'mkdir ' + @FilePath
 exec master..xp_cmdshell @cmd
 
 
create table #packages (PackageName varchar(128))
 insert  #packages
  (PackageName)
 select  distinct name
 from msdb..sysdtspackages
 
 select @PackageName = ''
 while @PackageName < (select max(PackageName) from #packages)
 begin
  select @PackageName = min(PackageName) from #packages where PackageName > @PackageName

  select @FileName = @FilePath + @PackageName + '.dts'

  exec @rc = sp_OACreate 'DTS.Package', @objPackage output
  if @rc <> 0
  begin
   raiserror('failed to create package rc = %d', 16, -1, @rc)
   return
  end

  exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null,
   @ServerName = @ServerName, @Flags = 256, @PackageName = @PackageName
  if @rc <> 0
  begin
   raiserror('failed to load package rc = %d, package = %s', 16, -1, @rc, @PackageName)
   return
  end
  
  -- delete old file
  select @cmd = 'del ' + @FileName
  exec master..xp_cmdshell @cmd, no_output
  
  exec @rc = sp_OAMethod @objPackage, 'SaveToStorageFile', null, @FileName
  if @rc <> 0
  begin
   raiserror('failed to save package rc = %d, package = %s', 16, -1, @rc, @PackageName)
   return
  end
  
  exec @rc = sp_OADestroy @objPackage
 end
go

Reference:http://www.nigelrivett.net/SaveAllDTSPackagesToFiles.html

How to backup SQL Server databases to a mapped drive

           

While taking backups for SQL Server databases onto a mapped drive you might get the following error:
"The system cannot find the path specified."
This is because a network share that you map using a local drive letter will not be visible to a SQL Server instance as it is running as a service. So, the SQL Server service runs in the context of the local console with the security context of the startup account of SQL Server. Also, mapped drives are specific to a session and not visible to a service started in the local console context.
So, if you want to backup a SQL Server database to a mapped drive using a local drive letter you have the following options:
1. Run the following command from a query window EXEC xp_cmdshell 'net use <drivename> <share name>'
-- where
<drive name>: Letter used to map the drive
<share name>: UNC path to the share
2. After that you should be able to backup using the mapped drive letter
3. Your Management Studio Object Explorer logical drive enumeration window should be able to list the above mapped drive


Reference:http://blogs.msdn.com/b/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

To Connect and Disconnect a mapped drive using SQL

To Connect:

Exec master.dbo.xp_cmdshell 'net use x: \\123.456.78.90\Shared Password /User:Domain\UID /Persistent:yes'

To Disconnect:

Exec master.dbo.xp_cmdshell 'net use x: /delete'

xp_cmdshell Server Configuration Option

xp_cmdshell Server Configuration Option


Introduced in SQL Server 2005, the xp_cmdshell option is a server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system. By default, the xp_cmdshell option is disabled on new installations and can be enabled by using the Policy-Based Management or by running the sp_configure system stored procedure as shown in the following code example:

Copy

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Reference:http://msdn.microsoft.com/en-us/library/ms190693.aspx

You cannot back up databases to a network drive if your account have not sufficient permissions to access the network drive

SYMPTOMS

When you attempt to back up a database to a network drive, you may receive one o...

When you attempt to back up a database to a network drive, you may receive one of the following error messages, depending on the version of SQL Server you are running.

Back to the top

On SQL Server version 7.0, 2000, and 2005

Server: Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'E:\pubs.dat'. Device error or device off-line. See the SQL Server error log for more details. Server: Msg 3013, Level 16, State 1, Line 1 Backup or restore operation terminating abnormally.

The following information will be in the error log:

998-12-29 11:58:21.62 kernel BackupDiskFile::CreateMedia: Backup device 'E:\pubs.dat' failed to create. Operating system error = 5(Access is denied.).

Back to the top

On SQL Server version 6.5

[SQL Server] Can't open dump device '<dump device path>'. Device error or device off line. Please consult the SQLServer errorlog for more details.

Messages similar to the following appear in the error log:

97/11/25 11:25:49.54 kernel dbsvolopen: Backup device '\\svrname\data\pubs.DAT' failed to open, operating system error = 5 (Access is denied.)
97/11/25 11:25:51.60 kernel dbswritecheck: Backup device '\\svrname\data\pubs.DAT' failed to open, operating system error = 5 (Access is denied.)

The following message may also appear in the error log:

Msg 18204: dbswritecheck: Backup device '<device path and file>' failed to open, operating system error = 5 (Access is denied.)

Back to the top

CAUSE

This error message usually occurs as a result of incorrect permissions to the ne...

This error message usually occurs as a result of incorrect permissions to the network drive.

Back to the top

WORKAROUND

To enable backups to network drives, perform the following steps: The SQL Server...

To enable backups to network drives, perform the following steps:

1. The SQL Server service must be started using a domain user account to access any resources on a remote computer. Verify that the MSSQLServer service is started under a domain account that has write access to both the Windows NT Server share and its underlying partition (if the partition is formatted with the Windows NT file system, or NTFS).
2. In Control Panel, double-click the Services icon.
3. Select the MSSQLServer service and then click Startup.
4. Examine the startup options for the service and verify that This Account is selected and that a valid domain account is supplied in the form Domain_Name\Domain_account (with the correct password).
   
   NOTE: If you changed the service to run under a domain account, you must stop and restart the MSSQLServer service.
5. Verify that the account specified has write access on the Windows NT share to which you are backing up, as well as the underlying partition (if the partition is formatted as NTFS). To do this, perform the following steps:
   
   
   1. Right-click the share name in Windows NT Explorer.
   2. Click Sharing on the shortcut menu.
   3. On the Security tab, click Permissions.

Reference:http://support.microsoft.com/kb/207187