August 19, 2003 at 9:36 am
Hi,
I am using the ExecuteWithResultsAndMessages method of the database object in SQL DMO to execute SQL scripts that create stored procedures for my database. That is i read the file containing the SQL Script and send the entire thing as a string to this function. While most of the stored procedures are created fine, there are problems with all those stored procedures which has the string GO in it anywhere, for example GOTO statement, GO, statement, or any object or column with 'GO' in it. I figured out that the T-SQL keyword GO had to be parsed out, but i could not find a workaround for other cases. If anybody has worked on this and faced similar problems, and have a solution please respond. Thank you
August 19, 2003 at 5:49 pm
I've been meaning to look into this more, just havent gotten there! I think the easiest way would be to split the code into chunks using Go as the delimiter, then execute each separately. Alternatively (and probably easier) is to write to disk, then execute with isql or osql.
Andy
August 20, 2003 at 7:36 am
I have to agree with splittin the script on the 'GO' commands and executing each one as a seperate SQL statement. Doing in that way, you can review each of the responses from the SQL server for errors and abort the rest of the processing if necessary.
August 21, 2003 at 1:43 pm
Thanks Andy and Eric for your replies. I have been using this way to execute my SQL Scripts. But the SQL DMO seems to have problem with the word GO appearing anywhere in the SQL Script. Consider this for example, I have the following T-SQL statement:
IF (@@ERROR<>0) BEGIN GOTO END_TRANSACTION END
This is typical statement found in many stored procedures. The SQL DMO seems to have a problem with the GO in the keyword GOTO. It gives a error as Invalid syntax near keyword TO. So what can one do in those situations, obviously the way we do it now will not work.
September 3, 2003 at 5:02 pm
I have run into similar problems in using 'GO' in pass-through SQL in ADO. We would execute a CREATE PROC statement with others separated by 'GO', and get the same error. We have not found a way around it, so if anyone has found a solution, I am anxious to hear of it.
MTFBWY...
MTFBWY...
September 10, 2003 at 10:20 am
Are you sure you didn't strip out all GO's and in this case leaving the TO without the GO ? Your error message leads me to believe this is what's happening. I've not had issues with any scripts with GOTO's in them when I execute via DMO. I just had to write a routine that basically splits anywhere there is a GO with a space before and a space after the word GO and the word GO on it's own line. Probably could've gotten more sophisticated, but it got the job done.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply