Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Problem with scripting objects through query

By Divya Agrawal,

This article would help you to create a script that has multiple commands together. For example, suppose you want to drop a procedure and then create a new version. You usually have to separate these two statements, 'DROP PROCEDURE' and 'CREATE PROCEDURE', with the batch separator 'GO'. It is very simple to do this; we just need to concatenate these two batches.

I came across a very strange issue while doing such simple thing. Let's say I have a CREATE script for stored procedure named 'Testsp' defined as:

CREATE PROCEDURE Testsp
AS
BEGIN
 SELECT 1
END
GO

The DROP script is simple as:

DROP PROCEDURE Testsp
GO

I want to concatenate these two items dynamically. Let me show you the query i used to generate the script for concatenating the above two items, built dynamically. First I will build the drop procedure statement, and then add in the create procedure with a batch separator.

SELECT 'DROP PROCEDURE '+name + CHAR(13)+'GO'+CHAR(13)+ OBJECT_DEFINITION(object_id)+ CHAR(13)+'GO'+CHAR(13) 
FROM sys.objects 
WHERE name = 'Testsp'

By using the object_definition, it is easy to get the body of the stored procedure. I have used sys.objects to get the object_id, in case there are more stored procedures to be scripted.

I have used CHAR(13), which is a 'Carriage Return' character, in the script to concatenate the batch separator ' GO' with the other items. In other words, to write 'GO' on a new line, CHAR(13) is used.

The query scripts the stored procedure named 'Testsp' as:

When i tried to execute by copying from the Results window to Query window, i got a very strange error:

As shown in the figure above it shows the error as 'Incorrect syntax near 'GO'. This is because along with CHAR(13) , the batch separator 'GO' also requires the line feed character, i.e., CHAR(10) to be concatenated with it.

By rewriting the query again and concatenating CHAR(10) with CHAR(13), we will give the correct output.

SELECT 'DROP PROCEDURE '+name + CHAR(13)+CHAR(10)+'GO'+CHAR(13)+CHAR(10)+
       OBJECT_DEFINITION(object_id)+ CHAR(13)+CHAR(10)+'GO'+CHAR(13)+CHAR(10) 
FROM sys.objects
WHERE name = 'Testsp'

I again tried to copy the output of this query and executed it, and to my surprise this time it worked.

Coming to the conclusion, 'GO' is a batch separator and it requires both the Carriage Return and Line Feed codes to work well. I hope this article will be able to solve this scripting problem for others if they are creating multiple objects or multiple statements using dynamic code.

Total article views: 1907 | Views in the last 30 days: 4
 
Related Articles
FORUM

Generating Scripts for Database Objects

Creating Seperate Scripts Per Object

FORUM

Create ODBC automatically using batch script at user logon

Create ODBC automatically using batch script at user logon

SCRIPT

Generate Drop / Create Script for views, stored Procedures

This Script generates drop and create scripts for views or stored procedures.

FORUM

create Procedure within Procedure

create Procedure within Procedure

FORUM

Creating temp table in Batch file

Creating temp table in Batch file

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones