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

Avoid getting messages while executing Stored Procedure Expand / Collapse
Author
Message
Posted Wednesday, January 30, 2013 2:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 4:37 AM
Points: 33, Visits: 152
Hi,

When i execute an sp am getting the below message.

"Database name 'tempdb' ignored, referencing object in tempdb."


In this sp am creating and dropping some temp tables. I understood, because of dropping the temp tables am getting this message. But since it has the loop involved, am getting the message for n number of times.

Can any one advice me on this how to stop getting this warning kind of message which is no longer useful for me.

Regards,
Karthik


Regards,
Karthik.
SQL Developer.
Post #1413527
Posted Wednesday, January 30, 2013 3:14 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:17 PM
Points: 42,450, Visits: 35,505
Post the code.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1413544
Posted Thursday, January 31, 2013 5:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 7,081, Visits: 12,574
SELECT 1 AS a INTO #tmp;
GO
DROP TABLE tempdb..#tmp;

SELECT 1 AS a INTO #tmp
GO
DROP TABLE tempdb.dbo.#tmp

SELECT 1 AS a INTO #tmp
GO
SELECT * FROM tempdb..#tmp
-- look in messages tab

Modify your code so it does not fully-qualify references to temp tables. Post your code if you need help with identifying where or how to do that.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1414044
Posted Thursday, January 31, 2013 7:40 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: Friday, July 18, 2014 2:05 PM
Points: 3,943, Visits: 2,957
First, I would just refer to a temp table by its name and not fully qualify it.

Second, it you're in a loop, you may not need to create and drop the table over and over again. It may be better to create it at the start of the procedure, clear it out at the end of each iteration, then drop it at the end of the procedure. Use "set statistics time on" to examine performance implications.

I know these are generalities, but I would need to see the code to offer any specific suggestions.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1414146
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse