SQL Server Management Studio - Modify Stored Procedure bug

  • 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

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

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

    Any ideas.

     

    thanks,

     

    Daniel

  • 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

  • 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"

  • 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

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

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