Modified Stored Procedure Scripts Wrong ANSI Settings

Query Analyzer for 2000 was smart enough to realize that a stored procedure was created with the ANSI settings like such:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER OFF

go

However, when I use SSMS to modify a stored procedure from the context menu in the object explorer, instead get:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

Which is not even the default for my connection.

I'm working on a legacy code base with tons of double quoted strings, so I really need the ANSI settings to stay where they were without fighting the SQL editor about it.

Any suggestions? Is this a bug? I'm using SQL2005 RTM.

Matthew Martin

I would do this from your script in the query pane:

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE <yourprocedure>
...

SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF

GO

Should be something like this at the end.

HTH, Jens Suessmeyer.

|||

True, I can revert to OSQL and generate the script correctly with my keyboard, the problem is that EM used to be able to script out a stored procedure with the correct settings (namely the settings that were in effect when the stored procedure was last altered), now with SSMS, right clicking on a stored procedure and selecting modify will script out a ALTER PROCEDURE script with the wrong settings.

Either I've hit a SSMS bug or I haven't found the 'Make-it-work-the-way-it-used-to' check box. I'm hoping it is the later.

Matthew Martin

|||

This is a known issue in SSMS. It will be fixed in SP1.