Post reply

Modifying a stored procedure in a remote database using inline SQL statement

  • February 27, 2009 at 11:06 am

    #384041

    I am trying to modify a stored procedure on a remote host through the web using an ADO connection.

    I simply copied and pasted the text that gets generated when you choose "modify stored procedure" in management studio express into my sql statement, but removing the line breaks so I dont get a "unfinished string" error message like this:

    set rst = adocon.Execute("SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[reportChild](@SchYear char(12),@State_ID int,@Sch_ID varchar(15),@Dist_ID int,@grade char(5),@RepSc bit,@DiagWho TinyInt)as insert into tbChild (SchYear,State_ID,sch_ID,dist_ID, grade,RepSc, Diagwho) values(@SchYear, @State_ID, @sch_ID, @dist_ID, @grade, @RepSc, @Diagwho)")

    [/code]

    But when I do this I get the following error message:

    [Quote]

    Microsoft OLE DB Provider for SQL Server (0x80040E14)

    Incorrect syntax near 'GO'.[/quote]

    Any idea what the problem is? Basically I just need to add 1 new variable into my stored procedure to update an additional field in the table.

  • February 27, 2009 at 11:07 am

    #951303

    just remove the GO just before the ALTER command:

    SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

    changes to

    SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON

    i would simply pass the ALTER procedure command without the SET ANSI stuff ...it's already part of your connection, so no need to add it to the script.

    just pass the ALTER command is my recommendation

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

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