SQL Server Management Studio - Modify Stored Procedure bug

Question

Post reply

SQL Server Management Studio - Modify Stored Procedure bug

DanielP

SSCrazy

Points: 2320
More actions
May 9, 2007 at 8:22 am

#174029

Hi,
I have having a few issues when I want to modify an stored procedure in Management Studio.
1. Seems to throw in a single quote on top, example:
EXEC dbo.sp_executesql @statement = N'
2. Throws in an extra 'End' at the end of the proc with an extra single quote
3. Every place in the stored procedure in which a single quote should exist, it shows double quotes.
Do I have a setting that needs to be changed?
Has anyone else experienced this? This is really annoying.

Thanks,

Daniel

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

marcie-334185 SSC Enthusiast Points: 124 More actions · Answer 1

Look at the SET QUOTED_IDENTIFIERS section in the Books On Line..this should help you out. Good Luck!

DanielP SSCrazy Points: 2320 More actions · Answer 2

I checked that already. I don't have it set at the connection level, database level, or server level.

Any ideas.

thanks,

Daniel

DanielP SSCrazy Points: 2320 More actions · Answer 3

Here is what it looks like if I modify a test proc in SQL Server Management Studio I just created for this example:

As you can see it automatically throws in a an extra 'End' and missing quotes.

/****** Object: StoredProcedure [dbo].[Daniel_Test] Script Date: 05/09/2007 10:43:26 ******/

SET

ANSI_NULLS OFF

GO

SET

QUOTED_IDENTIFIER OFF

GO

IF

NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Daniel_Test]') AND type in (N'P', N'PC'))

BEGIN

EXEC

dbo.sp_executesql @statement = N'ALTER Proc [dbo].[Daniel_Test]

as

Begin

select * from Model

end

'

END

Jeremy Null Grasshopper Points: 19 More actions · Answer 4

In SQL Server Management Studio go to the the "Tools" drop down menu >>

Select "Options" >>

In the tree on the left of the Options dialog box select "Scripting" >>

In the Scripting details on the right look for the section labeled "Object scripting options" (it should be the second one down) >>

Set "Include IF NOT EXISTS clause" to "False"

F. Dwarf SSCommitted Points: 1598 More actions · Answer 5

Sorry Daniel, I don't see the extra END and the missing quotation marks. As far as I can tell SSMS uses the sp_executesql system stored procedure to create the procedure and everything seems to be ok with the code you posted.

Now it's me the one that is confused