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 123»»»

What is the result of the following query Expand / Collapse
Author
Message
Posted Thursday, December 9, 2010 9:49 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 28, 2013 6:00 AM
Points: 103, Visits: 76
Comments posted to this topic are about the item What is the result of the following query
Post #1032830
Posted Thursday, December 9, 2010 10:03 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:21 AM
Points: 1,676, Visits: 1,757
This is a very good back to basics question, thank you.

The behaviour of the exec in this case is identical to the one of the stored proc where the temp table created inside is descoped once the proc bails out. In other words, if create temp table part were moved out then the temp table would be visible to the exec batch and after, i.e.

if object_id('tempdb.dbo.#tmp') is not null drop table #tmp;

create table #tmp (id int);

declare @sql varchar(2000);
set @sql = 'insert into #tmp(id) values (1);';
exec (@sql);

select * from #tmp;

results in

id
-----------
1

Oleg
Post #1032833
Posted Thursday, December 9, 2010 11:43 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
Good question but the answers are a bit... unfair.
The 2 answers:
Throw an error: Invalid object name '#tmp'.
Throw an error: Cannot find table #tmp inside TempDB.

Have the same effect (no #tmp table). So you would also have to know the wording of the error SQL would throw in order to get it right... without running it.

/T
Post #1032849
Posted Friday, December 10, 2010 12:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:39 AM
Points: 1,426, Visits: 1,841
Very good, basic question. Clearly signifies the importance of scoping things right, which most tend to forget when piling on tons of code.
Thank-you!


Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Post #1032858
Posted Friday, December 10, 2010 1:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 13,017, Visits: 10,800
Nice question, thanks.

(but it surprises me that apparently 78% -for the moment- know the exact error message!)




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 #1032869
Posted Friday, December 10, 2010 1:20 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
da-zero (12/10/2010)
Nice question, thanks.

(but it surprises me that apparently 78% -for the moment- know the exact error message!)


Could have done what i did... ran the code. Now i knew that it wouldnt find the #tmp table but didnt know the exact error message (like i care about the exact wording of an errormessage). Cheating... maybe maybe not... it depends

Like i wrote a bit up... the second error message about not being able to find #tmp shouldnt have been there.

/T
Post #1032870
Posted Friday, December 10, 2010 2:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:33 AM
Points: 6,020, Visits: 8,289
da-zero (12/10/2010)
(but it surprises me that apparently 78% -for the moment- know the exact error message!)

Well, I don't know about others, but for me this is one of the more common errors I see when developing. Or when copying and pasting code snippets from internet support forums. I would not be able to reproduce it out of the blue, but I did recognise the "invalid object name" error, and I have not ever seen a message that even resembles the "cannot find ... inside TempDB" one. (plus, that one is extremely unlikely, as the database for temporary object is called tempdb, not TempDB - but I guess only people who are in the habit of developing on a case sensitive instance would know that).



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1032881
Posted Friday, December 10, 2010 3:20 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
I got it right but I had to think about this one.

I thought the error was going to be caused by not having a space between the two commands.
Set @strSql = @strSql + 'Create table #tmp (id int)'
Set @strSql = @strSql + 'Insert into #tmp(id) values (1)'


This should produce the same as (Notice no spacing between two commands):
Set @strSQL = 'Create table #tmp (id int)Insert into #tmp(id) values (1)'

I knew this had to cause some error because there's no space or semicolon between the create and insert statements but didn't know the exact error that would be produced. I've never seen any of the other specific errors so I guessed correctly but for the wrong reason.
Post #1032904
Posted Friday, December 10, 2010 3:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:33 AM
Points: 6,020, Visits: 8,289
cengland0 (12/10/2010)
This should produce the same as (Notice no spacing between two commands):
Set @strSQL = 'Create table #tmp (id int)Insert into #tmp(id) values (1)'

I knew this had to cause some error

Except it doesn't.
If you copy/paste the above in an SSMS query window and hit execute, you get "Command(s) completed successfully."



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1032909
Posted Friday, December 10, 2010 6:10 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
Hugo Kornelis (12/10/2010)

Except it doesn't.
If you copy/paste the above in an SSMS query window and hit execute, you get "Command(s) completed successfully."

Exactly. I try to answer the questions without executing the code so I thought the missing space would cause an error.

Remember, there are so many trick QOTD's so I'm always looking for the trick. I thought I found the trick and it was that a space was missing. The lack of the space actually had nothing to do with the error but I thought it did so I selected the error message I thought would have been generated and got it right but for the wrong reason.
Post #1032962
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse