Microsoft – SQLServer – Script Database Objects (Stored Procedure) – “Annoying” sp_executesql

Microsoft – SQLServer – Script Database Objects – “Annoying” sp_executesql

As a DBA one often has a need to script out Database Objects.

You need to copy objects from one System to another, you need to back them up prior to over-writing them, etc.

Whatever your need might be, script them out.

Microsoft SQL Server Management Studio offers a couple of paths for scripting out objects.

Option – 1 – Script Object

  • First, launch Microsoft SQL Server Management Studio
  • Select the object and right click on the selection
  • From the drop-down menu, Select “Script <object> as”

Option – 2 – Generate Scripts

  • First, launch Microsoft SQL Server Management Studio
  • Select the database and right click on your selection
  • From the drop-down menu Select “Tasks” / “Generate Scripts…”

When you choose to generate Scripts via the Database \ “Generate Scripts” path, you

sometimes get a code that looks like:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ProcessTask]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_ProcessTask]
AS
BEGIN
	SET NOCOUNT ON;

END
'

END

What comes out is not just a clean Stored Procedure script, but:

  • A quick check to see whether the object exists
  • If the Object does not exist, a call out to dbo.sp_executesql to go create the object
  • Sending parameters out to sp_executesql is not the easiest thing in the World.  Some of your text needs to be double-quoted

To make the resulting code easier to read, MS could simply have Googled and found a solution SQL Developers have started using:

It reads:

     set noexec off
     go

     if object_id('dbo.usp_ProcessTask') is not null
     begin

        set noexec on

     end

     create procedure dbo.usp_ProcessTask
     as
     begin

            ....
            ....

     end

     go

     set noexec off
     go

What choices are available:

  • Uncheck the “If exists” choice; which is the default choice

MS SQL Server – SQLCMD – Create Table Error – QUOTED_IDENTIFIER Error (Msg 1934)

Error when the command file pasted below is ran:

Msg 1934, Level 16, State 1, Server <DBServer>, Line xxxx
CREATE TABLE failed because the following SET options have incorrect settings: ‘ QUOTED_IDENTIFIER’

_______________________________________________________

Rem This command file re-creates Database Schema objects
Rem  This scripts were created by ScriptDB.exe (Elsasoft.com)

setlocal

rem -E Integrated Security

SET SCMD=sqlcmd -SDBServer -dsales -E -I

for %%d in (Schemas\*.sql) do %SCMD% -i%%d
for %%d in (Tables\*.sql) do %SCMD% -i%%d
for %%d in (Views\*.sql) do %SCMD% -i%%d
for %%d in (Programmability\StoredProcedures\*.sql) do %CMD% -i%%d
for %%d in (Programmability\Functions\*.sql) do %SCMD% -i%%d

endlocal

____________________________________________________

Msg 1934, Level 16, State 1, Server DBServer, Line 2
CREATE TABLE failed because the following SET options have incorrect settings: ‘
QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

____________________________________________________

Cause:
The problem is caused by the fact the sqlcmd executable posts this command “set quoted_identifier off SET TEXTSIZE 4096” upon launching.

____________________________________________________

Correction:

a) Add -I (enable Quoted Identifiers) as one of the arguments to sqlcmd.

BTW, MS “SQL Server Profiler” was used to confirm this setting.

____________________________________________________

setlocal

rem -I Enable Quoted Identifier
rem -E Integrated Security

SET SCMD=sqlcmd -SDBServer -dsales -E -I

for %%d in (Schemas\*.sql) do %SCMD% -i%%d
for %%d in (Tables\*.sql) do %SCMD% -i%%d
for %%d in (Views\*.sql) do %SCMD% -i%%d
for %%d in (Programmability\StoredProcedures\*.sql) do %CMD% -i%%d
for %%d in (Programmability\Functions\*.sql) do %SCMD% -i%%d

endlocal

___________________________________________________

Suggested Readings:

a] How to execute multiple scripts on multiple sql servers – http://www.db-staff.com/index.php/microsoft-sql-server/85-multiple-server-script-execute

b] Sqlcmd Utility – http://msdn.microsoft.com/en-us/library/ms162773.aspx

Microsoft – SQL Server – Scripting Database Objects

Occasionally one needs an automated, scheduled way to script database objects — One of the free and good available tools for doing so is ScriptDB.  Originally developed by Jesse Hersch of ElsaSoft, it is now available in CodePlex.

@ CodePlex, the link is http://www.codeplex.com/ScriptDB

@ ElsaSoft, the link is http://www.elsasoft.org/tools.htm

Sample Invocation scripts:

scriptdb.exe -con:server=LABDB;database=DBDemo;trusted_connection=yes -outDir:DBScripts -v -p -TableOneFile CreateOnly