Avoid getting messages while executing Stored Procedure

Question

Avoid getting messages while executing Stored Procedure

Karthiart

Ten Centuries

Points: 1070
More actions
January 30, 2013 at 2:36 am

#268033

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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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 SSC Guru Points: 182279 More actions · Answer 2

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 SSC Guru Points: 287026 More actions · Answer 3

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