Thinking outside the box

Patron Saint of Lost Yaks
posts - 202, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories
<< How many logical and physical processors do you have? | Home | Find popular combos >>

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?

Print | posted on Thursday, December 04, 2008 4:58 PM | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

Feedback

Gravatar

# re: Strange happening

I tried it in SQL 2005 and SQL 2008, no issue (both displayed AdventureWorks)

That is quite strange..
12/4/2008 5:56 PM | Jerry Hung
Gravatar

# re: Strange happening

You sure you didnt exec master..sp_executesql @SQL by mistake?
12/4/2008 6:40 PM | Nathan Skerl
Gravatar

# re: Strange happening

In 2005 I can only reproduce if I use master..sp_executesql.
12/4/2008 9:39 PM | K. Brian Kelley
Gravatar

# 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?
12/5/2008 3:45 AM | Bill Graziano
Gravatar

# 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.
12/5/2008 8:02 AM | Peso
Gravatar

# 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".
12/10/2008 5:05 AM | Rajkumar Rajput
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET

Copyright © Peter Larsson