May 17, 2016 at 9:57 am
We never use GO at the end of SP in sql 2005 and we never had any issues from application.
Now we migrated to sql 2014 and we are getting errors in application. When we use GO at the end of SP it goes away.
Is that sometihng new in sql 2014?
May 17, 2016 at 10:03 am
No, not new at all.
GO is not a T-SQL keyword. It's a batch terminator, hence it designates (to Management Studio) where the batch ends. Since a stored procedure is the entire of a batch, the GO shows where the procedure ends, if there are any commands in the script file after the stored procedure definition.
It's been that way probably since there was a SQL Server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 17, 2016 at 10:05 am
dallas13 (5/17/2016)
We never use GO at the end of SP in sql 2005 and we never had any issues from application.Now we migrated to sql 2014 and we are getting errors in application. When we use GO at the end of SP it goes away.
Is that sometihng new in sql 2014?
The "GO" is a configurable SSMS end of batch/delimiter directive, has nothing to do with SQL or SQL Server versions. Probably there are more than one incompatible statements in the batch.
😎
May 17, 2016 at 10:06 am
So,
CREATE PROCEDURE Test1
AS
PRINT 'In Proc1'
EXEC Test1 -- still part of the stored procedure definition, hence causes recursion and an error once the nest limit is hit
VS
CREATE PROCEDURE Test1
AS
PRINT 'In Proc1'
GO
EXEC Test1 -- not part of the stored procedure definition
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 17, 2016 at 10:08 am
So it means something is wrong with query. How to find these incompatible statements? I ran upgrade advisor from 2005 to 2008 to 2012 to 2014 to check for any incompatible features but no luck.
May 17, 2016 at 10:17 am
Piling on
😎
-- this works
USE TEEST;
go
select object_id as GO
from sys.objects so
-- this doesn't
USE TEEST;
go
select object_id as
GO
from sys.objects so
The latter returns
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 'as'.
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'from'.
May 17, 2016 at 10:19 am
I would execute the Create Stored Procedure Separately from the EXEC SP.
Make sure that the SP is created successfully first.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 17, 2016 at 10:21 am
Thanks all but as I said application is not throwing any exception after I put GO. Also no syntax errors otherwise cant execute SP at all.Anyway thanks. I will go through all Sps and see it follows below syntax or not,
CREATE PROCEDURE dbo.test
AS
SELECT * FROM ABC
GO
GRANT EXECUTE
GO
May 17, 2016 at 10:27 am
dallas13 (5/17/2016)
Thanks all but as I said application is not throwing any exception after I put GO. Also no syntax errors otherwise cant execute SP at all.Anyway thanks. I will go through all Sps and see it follows below syntax or not,
CREATE PROCEDURE dbo.testAS
SELECT * FROM ABC
GO
GRANT EXECUTE
GO
Who are you Granting Execute to?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 17, 2016 at 10:35 am
dallas13 (5/17/2016)
So it means something is wrong with query. How to find these incompatible statements? I ran upgrade advisor from 2005 to 2008 to 2012 to 2014 to check for any incompatible features but no luck.
It's nothing to do with incompatible features. SQL 2005 behaved the same way, as did 2000, 7 and probably earlier.
I would guess that something's changed with how you're generating the scripts if you're suddenly having the problem now.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 17, 2016 at 12:28 pm
As someone mentioned above go take a look at your SSMS settings and batch terminators. I don't know for sure, but that might be the cause of the differences.
May 17, 2016 at 12:29 pm
/* double post - removing */
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply