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'