SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL GO statement


T-SQL GO statement

Author
Message
DugyC
DugyC
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1704 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"
Nils Gustav Stråbø
Nils Gustav Stråbø
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3759 Visits: 3575
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
Hardy21
Hardy21
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2806 Visits: 1399
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
Brigadur
Brigadur
Right there with Babe
Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)

Group: General Forum Members
Points: 773 Visits: 27695
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.
Hardy21
Hardy21
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2806 Visits: 1399
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
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65136 Visits: 13298
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 :blushSmile


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

MCSE Business Intelligence - Microsoft Data Platform MVP
CGSJohnson
CGSJohnson
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2795 Visits: 1686
I think that this functionality of adding a count to the GO command is only available in SQL 2005 and up.

Thanks...Chris
Nils Gustav Stråbø
Nils Gustav Stråbø
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3759 Visits: 3575
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 :blushSmile
I just tested, and SSIS actually honors the GO command. Nice to know. Thanks :-)
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19229 Visits: 12426
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
sknox
sknox
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4127 Visits: 2935
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search