SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Avoid getting messages while executing Stored Procedure


Avoid getting messages while executing Stored Procedure

Author
Message
Karthiart
Karthiart
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 186
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)

Group: General Forum Members
Points: 227273 Visits: 46336
Post the code.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40188 Visits: 14413
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
Ed Wagner
Ed Wagner
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: General Forum Members
Points: 49555 Visits: 10844
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search