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: Sunday, April 13, 2014 3:51 AM
Points: 31, Visits: 144
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:30 PM
Points: 41,531, Visits: 34,448
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: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
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: 2 days ago @ 10:37 PM
Points: 3,306, Visits: 2,351
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
Best practices on how to ask questions
Post #1414146
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse