Peter Larsson Blog

Patron Saint of Lost Yaks

Strange happening

I had a strange scenario today and I can't reproduce it.

I changed current database to adventureworks and ran following code

DECLARE     @SQL NVARCHAR(200)

SET         @SQL = 'SELECT  DB_NAME()'

EXEC        sp_executesql @SQL
EXEC        (@SQL)

The sp_executesql statement returned "master" and exec statement returned "adventureworks".
Anyone knows why?

Legacy Comments


Jerry Hung
2008-12-04
re: Strange happening
I tried it in SQL 2005 and SQL 2008, no issue (both displayed AdventureWorks)

That is quite strange..

Nathan Skerl
2008-12-04
re: Strange happening
You sure you didnt exec master..sp_executesql @SQL by mistake?

K. Brian Kelley
2008-12-04
re: Strange happening
In 2005 I can only reproduce if I use master..sp_executesql.

Bill Graziano
2008-12-05
re: Strange happening
Do you have a proxy account setup that might have a different default database? Do they both use the same proxy account? Can you run a trace that captures some detail?

Peso
2008-12-05
re: Strange happening
The thing is that I can't reproduce it now, but yesterday while in same connection I could reproduce it over and over again. When I finally closed the query window and opened a new one, I couldn't reproduce it.

I have checked my query log for SSMSToolsPack, and no, the sp_executesql did not have "master.." prefix.

I can get the same result now when using "master.." in front of sp_executesql (thanks nathan and brian).

Let's put this aside in the "freak accidents" drawer.

Rajkumar Rajput
2008-12-10
re: Strange happening
Are u sure the db had been changed when u exeuted the query? Because i checked both statement and giving the same result "Adventureworks".