Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

T-SQL GO statement Expand / Collapse
Author
Message
Posted Tuesday, November 23, 2010 3:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 4:28 AM
Points: 1,248, Visits: 779
Hardy21 (11/23/2010)
Try
INSERT INTO # 
SELECT 'ASDF'
GO 0

GO <ZERO> --- SQL doesn't throw any complilation error but when you execute the code, status bar should display: "Query completed with errors".


Nope, doesn't like the 0 (zero) either.

I think it is the old version of SQL... further investigation does seem to suggest it.


_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"
Post #1024988
Posted Tuesday, November 23, 2010 3:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, June 6, 2014 7:58 AM
Points: 1,837, Visits: 3,420
It's also worth mentioning that GO is not a T-SQL statement. It is a command only recognized by SSMS, sqlcmd and osql, so it can't be used in any T-SQL code that is not executed in any of the three applications mentioned. In other words, SQL Server does not know what GO is.

From BOL:
"SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO."

Ref http://msdn.microsoft.com/en-us/library/ms188037.aspx
Post #1024990
Posted Tuesday, November 23, 2010 4:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:39 PM
Points: 1,015, Visits: 1,289
DougieCow (11/23/2010)
Hardy21 (11/23/2010)
Try
INSERT INTO # 
SELECT 'ASDF'
GO 0

GO <ZERO> --- SQL doesn't throw any complilation error but when you execute the code, status bar should display: "Query completed with errors".


Nope, doesn't like the 0 (zero) either.

I think it is the old version of SQL... further investigation does seem to suggest it.

I have executed the code against SQL Server 2008 R2.


Thanks
Post #1025001
Posted Tuesday, November 23, 2010 4:17 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, March 10, 2014 9:39 AM
Points: 579, Visits: 27,690
Hardy21 (11/23/2010)
DougieCow (11/23/2010)
Hardy21 (11/23/2010)
Try
INSERT INTO # 
SELECT 'ASDF'
GO 0

GO <ZERO> --- SQL doesn't throw any complilation error but when you execute the code, status bar should display: "Query completed with errors".


Nope, doesn't like the 0 (zero) either.

I think it is the old version of SQL... further investigation does seem to suggest it.

I have executed the code against SQL Server 2008 R2.


I have also executed on SQL Server 2008 R2. It gave "Query completed with errors" but no error message.
Post #1025009
Posted Tuesday, November 23, 2010 4:26 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:39 PM
Points: 1,015, Visits: 1,289
Brigadur (11/23/2010)
Hardy21 (11/23/2010)
DougieCow (11/23/2010)
Hardy21 (11/23/2010)
Try
INSERT INTO # 
SELECT 'ASDF'
GO 0

GO <ZERO> --- SQL doesn't throw any complilation error but when you execute the code, status bar should display: "Query completed with errors".


Nope, doesn't like the 0 (zero) either.

I think it is the old version of SQL... further investigation does seem to suggest it.

I have executed the code against SQL Server 2008 R2.


I have also executed on SQL Server 2008 R2. It gave "Query completed with errors" but no error message.

Correct.
Also if you execute the:
select * from # 
GO 0

then also it should display: "Query completed with errors" in status bar, and doesn't display any data in grid due to code is not executed.


Thanks
Post #1025015
Posted Tuesday, November 23, 2010 5:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:20 PM
Points: 13,252, Visits: 10,133
Nils Gustav Stråbø (11/23/2010)
It's also worth mentioning that GO is not a T-SQL statement. It is a command only recognized by SSMS, sqlcmd and osql, so it can't be used in any T-SQL code that is not executed in any of the three applications mentioned. In other words, SQL Server does not know what GO is.


Hmm. I seem to recall that I have used GO in Execute SQL Tasks in SSIS without a problem. (Or does that fall in the 3 categories you mentioned, because I only know the first one )




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1025056
Posted Tuesday, November 23, 2010 5:24 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:43 AM
Points: 1,542, Visits: 1,327
I think that this functionality of adding a count to the GO command is only available in SQL 2005 and up.

Thanks...Chris
Post #1025062
Posted Tuesday, November 23, 2010 6:09 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, June 6, 2014 7:58 AM
Points: 1,837, Visits: 3,420
da-zero (11/23/2010)
Nils Gustav Stråbø (11/23/2010)
It's also worth mentioning that GO is not a T-SQL statement. It is a command only recognized by SSMS, sqlcmd and osql, so it can't be used in any T-SQL code that is not executed in any of the three applications mentioned. In other words, SQL Server does not know what GO is.


Hmm. I seem to recall that I have used GO in Execute SQL Tasks in SSIS without a problem. (Or does that fall in the 3 categories you mentioned, because I only know the first one )
I just tested, and SSIS actually honors the GO command. Nice to know. Thanks
Post #1025090
Posted Tuesday, November 23, 2010 6:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:59 AM
Points: 5,916, Visits: 8,165
Nice question, fairly basic. I agree that the # trickery is a bit unnecessary.

Minor bitching about the title and explanation:

1) GO is not a T-SQL statement. It's a batch seperator that most clients recognise and honor. But if you write your own C# client and have it send "GO" to SQL Server, you'll get a syntax error message, as SQL Server does not recognise GO as a valid keyword.

2) A very minor distinction - because GO is processed by the client, GO 100 will not simply execute the batch 100 times; it will send it 100 times. The result will be the same, but you get more network traffic and more parse and compile time. If you want to save on those resources, write a single batch with the logic to execute the statement 100 times.
DECLARE @i int = 1;
WHILE @i <= 100
BEGIN;
INSERT (...);
SET @i += 1;
END;


DougieCow (11/23/2010)
(...)
when I did actually try to run the code it didn't seem to like the "100"... I got...

(1 row(s) affected)

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '100'.


The code is identical... I'm running this on a SQL Server 2000 box, but I don't think that matters...

The server does not matter, the client does. You need at least the SSMS version that ships with SQL Server 2005. It should work against any version server, as far as I know (but I never tried this).



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1025101
Posted Tuesday, November 23, 2010 6:47 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:55 PM
Points: 1,293, Visits: 1,645
da-zero (11/23/2010)
I like the point the question tried to make, namely using the GO statement as an instrument to loop, but I do not like the trickery with the # name. At first, I did not even notice the GO 100 statement, as I was focusing on the table name. So I'm confused what this question actually tried to do: teach us about GO n (mission accomplished), or about the various options for table names or was it just to trick people into choosing the wrong answer?


Agreed. That's why, for me, this question falls into the "good, but with incomplete explanation" category: I don't mind a question with 2 or more points to make, but you've got to make those points clear in your explanation. Add the explanation that # is an acceptable table name, and a resource explaining why, and this would be a good question.
Post #1025118
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse