Search

SSMS : certain scripting options yield dynamic SQL by aaronbertrand

Closed
as Fixed Help for as Fixed

22
0
Sign in
 to vote
Type: Bug
ID: 242799
Opened: 12/2/2006 9:45:47 AM
Access Restriction: Public
1
Workaround(s)
5
User(s) can reproduce this bug
In SP2, SSMS suddenly adds a lot more paths to get to dynamic SQL, simply by trying to modify an object. For example, if I want to wrap a create procedure statement with an IF NOT EXISTS clause, I get the entire CREATE PROCEDURE batch as a dynamic SQL string. If you use any literal strings in your stored procedures, you will cringe at the sea of red that this yields.

Red-Gate seems to generate change scripts without using IF EXISTS and without using dynamic SQL. This makes it much easier to tweak the scripts without having to start over from scratch.
Details (expand)
Product Language
English

Version

SQL Server 2005 SP2 - Developer Edition

Category

Tools

Operating System

Windows Server 2003 (all Win32 editions)
Operating System Language
English
Steps to Reproduce
Create a stored procedure, set the option "Include IF NOT EXISTS clause" under Tools / Scripting / Options to true, then right-click the stored procedure in Object Explorer and click Modify or Script Stored Procedure As > Create To > New Query Editor Window.
Actual Results
For Modify:

USE [tempdb]
GO
/****** Object: StoredProcedure [dbo].[foo] Script Date: 12/02/2006 12:42:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[foo]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE...'
END

For script as create:

USE [tempdb]
GO
/****** Object: StoredProcedure [dbo].[foo] Script Date: 12/02/2006 12:43:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[foo]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE...'
END
Expected Results
Dynamic SQL should not be used.

Platform

32
File Attachments
0 attachments
Sign in to post a comment.
Posted by unbob on 3/17/2009 at 11:22 AM
Funny. I actually like the approach of passing a string to dbo.sp_executesql - it relaxes certain dependency constraints. When you have multiple objects in a script and the procs/funcs/triggers are quoted this way it reduces the likelihood of a parse failing because some table or column hasn't been created yet.

I certainly wouldn't object to being able to control this independently; that is, having existence checks and proc-bodies-as-strings being separate, orthogonal options.

Do people feel that the new(ish) DB Pro licensing policy (http://blogs.msdn.com/gertd/archive/2008/09/29/team-developer-team-data-team-developer.aspx) ameliorates this any? (It does for us. I suppose it depends on what licenses you already have and how well DB Pro works for you. Being able to do a schema comparison between a database and a project and push changes in either direction means less semi-manual scripting for us).
Posted by hominamad on 12/12/2008 at 6:55 AM
Paul - Any update to this?
Posted by Schneider on 7/28/2008 at 8:22 PM
Yeah I dont understand the 'batching' explaination either. How is it that it all worked fine in SQL 2000?
Posted by Ion Freeman on 6/17/2008 at 11:08 AM
The batching argument is farciful. What's the point in coloring keywords in the editor at all if the code is going to be generated in dynamic SQL?
I'm currently in the process of extracting over a hundred stored procedures from a database for the addition of some synchronization columns. Now that I've learned the source of this bug, I'm going to have to insert my own 'IF NOT OBJECt_ID(user_name() + ..., 'p') IS NULL DROP...' code in each one, which is annoying.
Posted by hominamad on 5/13/2008 at 9:39 AM
I don't understand what is meant by "Batching" in the explanation of this. In SS2000 the behavior was correct. Also when scripting tables with the existence check it seems to script correctly. This is a huge problem for us as we are using generate scripts wizard for scripting our objects to be placed into Source Control. We need to have the objects scripted out correctly. Given the fact that it worked the correct way in 2000, and also works the correct way for tables in 2005, I believe this is a bug. Please see the thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3304208&SiteID=1&mode=1
for more information.
Posted by Microsoft on 12/4/2006 at 9:10 PM
Hey Aaron,

Thanks for the suggestion. There are some reasons that we use dynamic SQL, due to batching. There are some improvements that we can do in this area, though some will require language enhancements. We'll keep this in mind for future releases and keep you in the loop.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/
Sign in to post a workaround.
Posted by Ion Freeman on 6/17/2008 at 11:09 AM
Manually insert object existence test
© 2012 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement