|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL Server 2005 and GO issuewhich is a very strange occurance. We can open a stored procedure that has been saved and at the top of the SP we have the following: USE DatabaseName GO Followed by if exist statment etc etc. Well we get the following error for this SP that works fine running it in 2000. Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'GO'. However IF we type the exact same stored procedure from scratch the complie works fine and there is not issue. This is the 3rd time we have ran accross this particular issue with the final 2005 release of MS SQL Server 2005. Also this issue has occured on different boxes with different configurations. > which is a very strange occurance. We can open a stored procedure that Just so you know, in SQL Server 2000 and in SQL Server 2005, the stored > has > been saved and at the top of the SP we have the following: > > USE DatabaseName > GO procedure ends right here. GO is not valid T-SQL and will not be considered part of the procedure; it will be used to end the CREATE PROCEDURE statement. Anything after that will just run, you can verify this by checking sp_helptext ... I have explained this here before, but my template stored procedure looks like this, which prevents an errant GO from being assumed to be a part of the procedure: CREATE PROCEDURE dbo.name -- @Params AS BEGIN SET NOCOUNT ON; -- code END GO Can you reproduce this with a dead-simple proc and post what Management Studio (which I assume is
what we are talking about) generates and how you make SSMS generate that (where you click, etc). I have difficulties following exactly what is happening. I have a hunch that SSMS can generate incorrect line feed and/or return after or before the GO, but without seeing something (reproducing it), I can't really say. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "JosephPruiett" <JosephPrui***@discussions.microsoft.com> wrote in message news:62AC98C1-3D31-465C-AF1B-579227B0EB73@microsoft.com... > Ok gentelemen we have come accross a little issue using the 2005 platform > which is a very strange occurance. We can open a stored procedure that has > been saved and at the top of the SP we have the following: > > USE DatabaseName > GO > > Followed by if exist statment etc etc. > > Well we get the following error for this SP that works fine running it in > 2000. > > Msg 170, Level 15, State 1, Line 1 > Line 1: Incorrect syntax near 'GO'. > > > However IF we type the exact same stored procedure from scratch the complie > works fine and there is not issue. This is the 3rd time we have ran accross > this particular issue with the final 2005 release of MS SQL Server 2005. > Also this issue has occured on different boxes with different configurations. I assume that this is a script that is used to create a procedure, since
USE is not allowed in a procedure. GO is simply a batch separator that only applies to Query Analyzer and SSMS query tool, not valid SQL (or even T-SQL), so a script running from anywhere else would error on the GO. You don't say how your running it when it fails in sql2005, but if you use the same failing method against sql2000, it should fail as well, with the same error. JosephPruiett wrote: Show quote > Ok gentelemen we have come accross a little issue using the 2005 platform > which is a very strange occurance. We can open a stored procedure that has > been saved and at the top of the SP we have the following: > > USE DatabaseName > GO > > Followed by if exist statment etc etc. > > Well we get the following error for this SP that works fine running it in > 2000. > > Msg 170, Level 15, State 1, Line 1 > Line 1: Incorrect syntax near 'GO'. > > > However IF we type the exact same stored procedure from scratch the complie > works fine and there is not issue. This is the 3rd time we have ran accross > this particular issue with the final 2005 release of MS SQL Server 2005. > Also this issue has occured on different boxes with different configurations. > GO is simply a batch separator that only applies to Query Analyzer and SSMS query tool, not valid Good thinking, Trey. Perhaps the OP takes the script and runs it all through his own app...> SQL (or even T-SQL), so a script running from anywhere else would error on the GO. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Trey Walpole" <treypole@newsgroups.nospam> wrote in message news:u6DDTjmAGHA.912@TK2MSFTNGP11.phx.gbl... >I assume that this is a script that is used to create a procedure, since USE is not allowed in a >procedure. > > GO is simply a batch separator that only applies to Query Analyzer and SSMS query tool, not valid > SQL (or even T-SQL), so a script running from anywhere else would error on the GO. > > You don't say how your running it when it fails in sql2005, but if you use the same failing method > against sql2000, it should fail as well, with the same error. > > JosephPruiett wrote: >> Ok gentelemen we have come accross a little issue using the 2005 platform which is a very strange >> occurance. We can open a stored procedure that has been saved and at the top of the SP we have >> the following: >> >> USE DatabaseName >> GO >> >> Followed by if exist statment etc etc. >> >> Well we get the following error for this SP that works fine running it in 2000. >> >> Msg 170, Level 15, State 1, Line 1 >> Line 1: Incorrect syntax near 'GO'. >> >> >> However IF we type the exact same stored procedure from scratch the complie works fine and there >> is not issue. This is the 3rd time we have ran accross this particular issue with the final 2005 >> release of MS SQL Server 2005. Also this issue has occured on different boxes with different >> configurations.  |
|||||||||||||||||||||||
Other interesting topics