EXECUTE

Question

EXECUTE

Lokesh Vij

SSChampion

Points: 10836
More actions
February 14, 2013 at 9:44 pm

#394190

Comments posted to this topic are about the item EXECUTE
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions
Link to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
February 14, 2013 at 11:36 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

At first line of query its written as Use Master, so all statement stating SELECT db_name() gives master, execute statement will not make any effect here.....

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 2

I thought EXECUTE statement will change the DB.

Learning for me.

--
Dineshbabu
Desire to learn new things..

ralm Hall of Fame Points: 3156 More actions · Answer 3

It will make effect Kapil. Try the below...

As in the explanation the scope is only within the EXECUTE statement.

USE master;

-- Query#1

SELECT DB_NAME() DatabaseName1;

EXECUTE ('USE AdventureWorks2008; SELECT DB_NAME() DatabaseName2');

-- Query#2

SELECT DB_NAME() DatabaseName2;

EXECUTE ('USE AdventureWorks2008; select count(*) from Sales.Customers; USE master; ');

-- Query#3

SELECT DB_NAME() DatabaseName3;

^{[font="Verdana"]Regards,
Rals[/font].}

Ken SSCoach Points: 17191 More actions · Answer 4

Nice question Lokesh. I never would have thought to try changing the database through and execute statement. Now I know what would happen had I tried!

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

THANKS RALS,

the query that you posted will make effect through EXECUTE but the EXECUTE statement that is post in the today's QOD is not making any effect it will always print 'Master' database

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Stuart Davies SSCoach Points: 19505 More actions · Answer 6

Thank for the question - easy way to end the week

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Toreador SSChampion Points: 11365 More actions · Answer 7

How long until the first complaint that the correct answer is None of the Above, because there's no such database as AdventureWorks2008 😉

Mighty SSCrazy Eights Points: 9063 More actions · Answer 8

Toreador (2/15/2013)
How long until the first complete that the correct answer is None of the Above, because there's no such database as AdventureWorks2008 😉

That was until your reply. 😀

But in fact it is the case if you don't have that database. It would have been better if tempdb was used, since that one is available on all servers.

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 9

Thanks for the question, Simply Nice 🙂

(we have like 250+ database on each principals where the structure of each database is same, only the data stored is different, hosted on multiple database server over different regions. I once wrote a query to fetch a specific set of records from one server on all databases and I used this "execute use db1" and the execute the underlying sql statement and then store them in the table variable.. the whole query was awful and then I changed in to one whole batch and then stored it in to the temp table and used the cursor to loop trough the next database and then execute it and then store in result... the complete execution took only 15 seconds to fetch records from all database..like some less than 600 records... I actually came to now here that executing of the "use" statement using "execute" does not changes the physical connection of the current session. memory recall 🙂 )

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

TomThomson SSC Guru Points: 104773 More actions · Answer 10

Toreador (2/15/2013)
How long until the first complaint that the correct answer is None of the Above, because there's no such database as AdventureWorks2008 😉

Surely no one will be quite that silly?

I just assumed that the idea was to assume it was run against SQL Server 2008 with that DB installed, since it's clear from the question and the answer options that one has to assume the database exists and that seems most likely to happen with an SQLS 2008 installation.

Of course the database name would have to change to AdventureWorks2008R2 for those statements to work on my laptop, but who cares - it's not as if I would actually want to run that code anyway!

Tom

Venkat Jonnalagadda Mr or Mrs. 500 Points: 510 More actions · Answer 11

an easy one to start my day...and its a friday!!!!!!!!!! 😀

___________________________________________________________________
If I can answer a question then anyone can answer it..trying to reverse the logic.. :hehe:

EL Jerry SSCertifiable Points: 7063 More actions · Answer 12

Great question to end the week. Thank you, Lokesh.

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

I think this is not quite correct:

Database context will not be changed while executing EXECUTE statements. The reason is changes in database context last only until the end of the EXECUTE statement.

Really should be more like:

The database context will only be changed within the context of the EXECUTE statement. It will have no effect on the context in which the EXECUTE statement is run.

Other than that, very good back to basics question. Surprised by the number of people that have missed the question so far.