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.