SQL Tips – Generate Script files for deployment quickly using SQLCMD command utility

Often we need DROP and CREATE scripts for Stored procedure modified during development of any CR to deployment them in ST/UAT/PROD environment.

I know I can simply right click the database and ‘Tasks > Generate Scripts’, but that doesn’t script the stored procedure in the template I want.

I need the stored procedure to be scripted in the same template you get when you right click the stored procedure from the object explorer and ‘Script Stored Procedure As > DROP and CREATE’.

To achieve this I found one solution which can help me to reduce my effort during deployment.

Here, we can use SQLCMD utility and achieve what we need. I am sharing the same with you so that you can also get benefit out of this.

Let us discuss the steps to achieve this.

Step 1 – Create the scripting stored procedure, this stored procedure will help us to generate the scripts in the template we want.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_GenerateScriptTip]') 
AND type in (N'P', N'PC'))
  DROP PROCEDURE [dbo].[usp_GenerateScriptTip]
GO

CREATE PROCEDURE [dbo].[usp_GenerateScriptTip] 
(
  @ObjectID INT,
  @Name NVARCHAR(128),
  @SchemaID INT
) 
AS 

DECLARE @code VARCHAR(MAX), @newLine CHAR(2)
SET @newLine = CHAR(13) + CHAR(10)

SET @code = 
    'USE [' + DB_NAME() + ']' + @newLine + 'GO' + @newLine + @newLine
    + 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = '
    + 'OBJECT_ID(N''[' + SCHEMA_NAME(@schemaID) + '].[' + @Name + ']'') ' 
    + 'AND type IN (N''P'', N''PC''))' + @newLine 
    + 'DROP PROCEDURE [' + SCHEMA_NAME(@schemaID) + '].[' + @name + ']' 
    + @newLine + @newLine + 'SET ANSI_NULLS ON' + @newLine + 'GO' 
    + @newLine + @newLine + 'SET QUOTED_IDENTIFIER ON' + @newLine + 'GO'
    + @newLine + @newLine
    + OBJECT_DEFINITION(@ObjectID) + @newLine + 'GO' 
    + @newLine + @newLine + 'SET ANSI_NULLS OFF' + @newLine + 'GO' 
    + @newLine + @newLine + 'SET QUOTED_IDENTIFIER OFF' + @newLine + 'GO'

WHILE @code <> ''
BEGIN
  PRINT LEFT(@code,8000)
  SET @code = SUBSTRING(@code, 8001, LEN(@code))
END
GO

Step 2 – Next, we need to enable Command shell which we are going to run to generate the SQL files for each stored procedures.

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

Step 3 – Run the below script to generate the templates we want. These SQL files we can use during deployment on ST/UAT/PROD environment.

DECLARE 
  @name sysname,
  @objID int,
  @schemaID int,
  @cmd varchar(1000),
  @folder varchar(128)

--This folder should exist on SQL DB server, files generated will be saved at SQL DB server C Drive. 
SET @folder = 'C:\Scripts'

--You can update the below query to select Stored procedures which are needed in deployment.
DECLARE procs CURSOR FOR 
	SELECT name, object_id, schema_id 
	FROM sys.procedures
	WHERE name in 
	(
	'Stored Procedure 1',
	'Stored Procedure 2',
	'Stored Procedure 3'
	)
	ORDER BY [name]

OPEN procs

FETCH NEXT FROM procs
	INTO @name, @objID, @schemaID

WHILE @@FETCH_STATUS = 0
BEGIN

  SET @cmd = 'sqlcmd -S .\ -U sa -P sa -d ' + DB_NAME() 
           + ' -Q "EXEC usp_GenerateScriptTip ' 
           + CONVERT(VARCHAR(20), @objID) + ', N'''
           + @name + ''', ' + CONVERT(VARCHAR(20), @schemaID) 
           + '" &gt; ' + @folder + '\' + @name + '.sql'

  EXEC xp_cmdshell @cmd

  FETCH NEXT FROM procs
  INTO @name, @objID, @schemaID

END
CLOSE procs
DEALLOCATE procs

You can also update select query of above Cursor with below query which fetches the stored procedure names which are updated recently for CR-1234. Here CR-1234 must be present in the stored procedures which you have modified as a comment, which we usually do during development.

SELECT name, object_id, schema_id 
FROM   sys.procedures
WHERE  Object_definition(object_id) LIKE '%CR-1234%'

Similarly, You can generate DROP and CREATE scripts for New Tables. Do not use this if table is already exists and you want to alter the definition of table.

DECLARE tabls CURSOR FOR  
SELECT name, object_id, schema_id  
FROM sys.Tables
WHERE is_ms_shipped = 0  
ORDER BY [name] 
OPEN tabls 

FETCH NEXT FROM tabls 
INTO @name, @objID, @schemaID 

WHILE @@FETCH_STATUS = 0 
BEGIN 

  SET @cmd = 'sqlcmd -S .\ -U sa -P sa -d ' + DB_NAME()  
           + ' -Q "EXEC usp_GenerateScriptTip '  
           + CONVERT(VARCHAR(20), @objID) + ', N''' 
           + @name + ''', ' + CONVERT(VARCHAR(20), @schemaID)  
           + '" &gt; ' + @folder_Tbl + '\' + @name + '.sql' 

  EXEC xp_cmdshell @cmd 

  FETCH NEXT FROM tabls 
  INTO @name, @objID, @schemaID 

END 

CLOSE tabls 
DEALLOCATE tabls 

That is all.

Hope this will help and speed up your deployment process.

For more details on SQLCMD command – click here.

Jay Ganesh !!!!!

SQL Server Data Tools to Visual Studio 2013 – Database Reverse Engineering

Microsoft created SQL Server Data Tools for Visual Studio 2013, to make development easier.

  • Single Tool to support developer’s needs
  • Can build, debug, test, maintain, and refactor databases
  • Developers can use familiar Visual Studio tools for database development
    • Code navigation, IntelliSense, C# language, platform-specific validation
      • Debugging, and declarative editing in the Transact-SQL editor
  • Works connected or disconnected (Tools work on top of design time)
  • Have Schema Model differencing capabilities (Compare and Update Model)
  • Schema and app under TFS control
  • Publish to all supported SQL platforms

Read Full Article – Click

Hope this will help !!!

Jay Ganesh !!!!!!

 

Execute Long Running SQL Statements Asynchronously from .NET

Introduction

This tip aims at understanding the fundamental concept of asynchronous execution, i.e., how to use worker thread in colloboration with ADO.NET’s BeginExecute & EndExecute feature to avoid UI freeze.

Background

Below are the 2 main issues that arise when your application is intended to deal with huge data:

  1. SQL Server takes significant time to process (long running SQL statements) which leads to blocking the execution of your .NET code.

  2. Main thread or UI thread also gets blocked till the response from the SQL Server.

These issues are the serious issues while building interactive applications. User patience is an unpredictable parameter and user’s reaction against long waiting screen is uncertain. At-least UI shouldn’t freeze to engage the user and make him wait for the result.

Since, transactional SQL statements will definitely take time to process the things, the quickest solution sought is on the application programming level. Also, it is known that MS SQL server takes each & every call as synchronous, even if you change the connection string property AsynchronousProcessing to true. It is client application (C#, .NET) which gets affected. So, below are some widely used solutions.

Cancellation Token mechanism – so that user can cancel ongoing longer execution if they are unwilling to wait.

Callback mechanism – so that UI thread can’t get blocked

Read Full Article – Click

Hope this will help.

Jay Ganesh !!!!!

JSON Support in SQL Server 2016

Microsoft has announced that they will provide JSON Support in SQL Server 2016 CTP2. FOR JSON clause is introduced for the same.

Jovan Popovic written very good articles which explain features of FOR JSON clause in detail:

Hope this will help !!!

Jay Ganesh