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 6:49 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
Excellent question. Finally one without any trick and one where I actually learned something. I can actually find a use for this feature.
Post #1025120
Posted Tuesday, November 23, 2010 6:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:08 PM
Points: 5,469, Visits: 23,464
Hugo Kornelis
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).


Using SQL 2000 - Query Anayser
Fails with error message "Incorrect syntax near '100'."

Using SSM 2005 to connect to same SQL 2000 server
Executes and returns correct answer.


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1025127
Posted Tuesday, November 23, 2010 7:38 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, November 04, 2013 4:02 PM
Points: 659, Visits: 474
Awesome!!
Post #1025170
Posted Tuesday, November 23, 2010 7:46 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:07 AM
Points: 3,765, Visits: 3,582
Interesting question, thanks. I had no idea you could do this.
Post #1025179
Posted Tuesday, November 23, 2010 8:30 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 3,150, Visits: 1,900
A nice question. I walked through the problem and got the right answer but the part with the temp table name # concerned me so I had to run it anyway. Interesting that # is a valid table name.
Post #1025216
Posted Tuesday, November 23, 2010 8:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 2,919, Visits: 2,511
Mike, good question. I guessed at the answer and learned something new.

Thanks



Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Post #1025220
Posted Tuesday, November 23, 2010 8:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:08 PM
Points: 5,469, Visits: 23,464
Want some more ...
CREATE the temptable as ##
Run the insert code ... do NOT close that instance of SSMS.
Perform the SELECT * FROM ## statement .. right it returns the correct number of rows

Open a second instance of SSMS
Then perform ONLY the SELECT * FROM ## statement in this second instance of SSMS

And yes, you do get the same number of rows returned, Intriguing indeed.


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1025237
Posted Tuesday, November 23, 2010 9:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:05 PM
Points: 5,793, Visits: 8,002
bitbucket-25253 (11/23/2010)
Hugo Kornelis
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).


Using SQL 2000 - Query Anayser
Fails with error message "Incorrect syntax near '100'."

Using SSM 2005 to connect to same SQL 2000 server
Executes and returns correct answer.


Thanks for running the test and confirming what I expected, Ron.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1025251
Posted Tuesday, November 23, 2010 9:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:05 PM
Points: 5,793, Visits: 8,002
bitbucket-25253 (11/23/2010)
Want some more ...
CREATE the temptable as ##
Run the insert code ... do NOT close that instance of SSMS.
Perform the SELECT * FROM ## statement .. right it returns the correct number of rows

Open a second instance of SSMS
Then perform ONLY the SELECT * FROM ## statement in this second instance of SSMS

And yes, you do get the same number of rows returned, Intriguing indeed.

Why intriguing? Table names starting with ## are considered to be global temporary tables. They're available to all sessions, and are only discarded when no sessions use them anymore.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1025252
Posted Tuesday, November 23, 2010 9:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 17, 2014 3:24 PM
Points: 60, Visits: 173
This could be handy. It'll freak some folks out, too.
Post #1025276
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse