List of Tables and Stored procedure created recently

select *

from sys.objects

where (type = 'U' or type = 'P')

and create_date > dateadd(m, -3, getdate())

order by create_date desc

SQL Server 2016 new features

•    R

•    JSON 

•    SSRS combined with Datazen 

•    SSIS includes Power Query 

•    SSAS Tabular supports Many to Many 

•    Query Store 

•    Another fix to SQL Server Data Tools

•    Polybase : Bringing Big Data to SQL Server

SQL Server Databsae from SINGLE USER mode to MULTI USER

SQL Server 2012  Right Click on  DB > Properties > Options > [Scroll down] State > RestrictAccess > select Multi_user and click OK.

SQL Server 2008 R2 and earlier

Script to change the database from single user to multi user

--Script
use master

GO

-- Find the active sessions and kill it

select DB.name, DB.dbid, spid, login_time, nt_domain, nt_username, loginame 

from sysprocesses Process

inner join sysdatabases DB

on Process.dbid = DB.dbid

where DB.name = 'Databsae Name'

GO

kill spid

--Execute the below stored procedure to change to multiuser

exec sp_dboption 'Database Name', 'single user', 'FALSE'

GO statement in SQL

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.

The scope of local (user-defined) variables is limited to a batch, and cannot be referenced after a GO command.

declare @a int

select @a=100

print @a

GO -- @MyMsg is not valid after this GO ends the batch.

print @a

one more use of GO

The batch preceding GO will execute the specified number of times.

with the GO , below insert query runs 100 times.

create table test(a integer)

insert into test values(1)

go 100

Script to generate QVD's

Please find the below script:

 

//DB Conncetion

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

 

// #### DB COnnection #### This will be change if we have more DB's

OLEDB CONNECT TO [ConnectionString];

// ##### QVD Path ####### This will be change if we want to change the QVD folder path

SET vQVDFolderPath = 'qvdata' ;

 

// List of Tables, Here I am creating with InLine. You can Use txt file or excel file. This will be change

LIST_OF_TABLES:

LOAD * INLINE [

TABLENAME

SCHEMA.TABLE1

SCHEMA.TABLE2

] ;

// From This line, the script is static

// generate the Loop for each value in the LIST_OF_TABLES table

 

FOR i=1 to FieldValueCount('TABLENAME')

LET vTableName = FieldValue('TABLENAME',$(i)) ;

 

$(vTableName):

SELECT * FROM $(vTableName) ;

 

STORE $(vTableName) into [$(vQVDFolderPath)\$(vTableName).qvd] (qvd);

 

//QVD DETAILS info

QVDDETAILS:

LOAD '$(vTableName).qvd' AS QVD_NAME ,

       '$(vTableName)' AS SOURCE_TABLENAME,

       NoOfRows('$(vTableName)') AS NO_OF_ROWS

AutoGenerate 1 ;

 

DROP Table $(vTableName) ;

 

NEXT i

 

In the above script, just change the DB connection , QVD Path and List of Tables for the another QVD Generator qvw file.

 

List of SSIS Packages based on keyword search

SQL Query to find the SSIS Packages with wildcard search for specific keyword.

For SQL Server 2008 and above

SELECT name
,CAST(CAST([packagedata] as varbinary(max)) as xml) AS PackageSource
FROM [msdb].[dbo].[sysssispackages]
WHERE CAST(CAST([packagedata] AS VARBINARY(MAX)) AS VARCHAR(MAX))
LIKE '%keyword%'

For SQL Server 2005:

SELECT name, CAST(CAST([packagedata] AS VARBINARY(MAX)) AS VARCHAR(MAX))

FROM msdb.dbo.sysdtspackages90

WHERE CAST(CAST([packagedata] AS VARBINARY(MAX)) AS VARCHAR(MAX))

LIKE '%keyword%'



SELECT name
,CAST(CAST([packagedata] as varbinary(max)) as xml) AS PackageSource
FROM [msdb].[dbo].[sysssispackages]
WHERE CAST(CAST([packagedata] AS VARBINARY(MAX)) AS VARCHAR(MAX))
LIKE '%keyword%'

For SQL Server 2005:

SELECT name, CAST(CAST([packagedata] AS VARBINARY(MAX)) AS VARCHAR(MAX))

FROM msdb.dbo.sysdtspackages90

WHERE CAST(CAST([packagedata] AS VARBINARY(MAX)) AS VARCHAR(MAX))

LIKE '%keyword%'

Generic stored procedure to track the errors in a table

Purpose of the stored procedure is to create a generic stored procedure which will capture the errors whenever error has been occurred

 

Table : 　

CREATE TABLE [dbo].[SQLErrorLog]

([SQLErrorLogID] [int] IDENTITY(1,1) NOT NULL,
[DbID] [int] NULL,
[Application] [varchar](128) NOT NULL,
[Host] [varchar](128) NOT NULL,
[ErrorNumber] [int] NULL,
[ErrorSeverity] [int] NULL,
[ErrorState] [int] NULL,
[ErrorProcedure] [varchar](126) NULL,
[ErrorLine] [int] NULL,
[ErrorMessage] [varchar](2048) NULL,
[MetaCreateTS] [datetime2](7) NOT NULL,
[MetaCreateServerName] [varchar](50) NOT NULL,
[MetaCreateSUserName] [varchar](50) NOT NULL,
CONSTRAINT [PK_SQLErrorLog] PRIMARY KEY CLUSTERED(
[SQLErrorLogID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 Stored Procedure :

CREATE PROCEDURE usp_SQLErrorLog
@Application varchar(100)
AS

BEGIN
SET NOCOUNT ON;

BEGIN TRY

INSERT INTO [dbo].[SQLErrorLog]

([DbID]
,[Application]
,[Host]
,[ErrorNumber]
,[ErrorSeverity]
,[ErrorState]
,[ErrorProcedure]

,[ErrorLine]
,[ErrorMessage]
,[MetaCreateTS]
,[MetaCreateServerName]
,[MetaCreateSUserName])

SELECT DB_ID(),
@Application,
HOST_NAME(),
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
ERROR_MESSAGE(),
GETDATE() ,
@@servername ,
SUSER_NAME()
END TRY

BEGIN CATCH

-- Execute error retrieval routine.

EXEC xp_logevent 60000, @Application, informational;

END CATCH;

END

 

 Testing :

BEGIN TRY

-- Generate divide-by-zero error.

SELECT 1/0;

END TRY

BEGIN CATCH

-- Execute error retrieval routine.

EXECUTE usp_SQLErrorLog ‘Application’

END CATCH;