SQL User Error on Exec Stored Procedure

I am running SQL Server 2005 Express in my production environment, but for development I use SQL Server 2005 Standard to enable me to run profiler etc.

There are a number of errors I am trying to work out, and although I appreciate most are simply info messages, because I run the cut down express edition on my production server I really want to be able to rely on the perfmon SQL error counters to alert me to problems, and these info messages just confuse matters.

SQL User Error 1:
Cannot drop the table '#tmp_sp_db_vardecimal_storage_format', because it does not exist or you do not have permission.

This occurs frequently in the trace logs on my dev machine, and although I can't be sure it's happening outside of management studio or even at all on my live server....the trace show's it attempting to execute at least 4 times on most stored procedures.  There is very little information available on this error it seems.  Any ideas?

SQL User Error 2 & 3:
Changed database context to 'master'. (or whatever database)
Changed language setting to us_english.

So I know this is normal behaviour but can I turn off these Errors somehow?  I would need to turn them off with confidence that the implications of doing so are understood.

In addition I will also be posting a question on Sort Warnings if anybody is interested.

Many Thanks
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

In regards to Error 1: The stored procedure is attempting to drop a temp table '#tmp_sp_db_vardecimal_storage_format'. This is probably an issue with the way the stored procedure was written, either it is trying to delete a temp table that does not exist (i.e. it had already been dropped).
Or, it is trying to delete a temp table that was created by another session. Standard temp tables can only be used or modified by the session that created them. The table will automatically drop when the session ends but (for good reason) many programmers will put in explicit drop statements.

Errors 2, 3: These are not really errors, they are informational messages. Depending on which logs you are seeing these on, you may be able to restrict them to not display informational messages, check the logging settings of the particular aspect of SQL Server you are looking at.
davie455Author Commented:
Thanks DantheDBA,

'#tmp_sp_db_vardecimal_storage_format' seems to be an 'own use' temp table created, managed and dropped by SQL Server for it's own purposes.   No user Stored procedure is calling it directly.

As for the info messages, I think I am going to resign myself to putting up with them.  I'm spending too much time on this unimportant stuff out of paranoia at missing genuine errors.

I did some searching and there is really not much out there on this. I am assuming it is created by sp_db_vardecimal_storage_format which is new with SP2.

See: http://msdn2.microsoft.com/en-us/library/bb326653.aspx

This feature is only available in Enterprise and Developer editions though. I am wondering if for some reason a proc in your Standard edition is calling it but it is not there. Check what version your developers are using...not all features in Developer edition are available in Standard edition.

This may be an issue to pursue with MS...or try updating to the newest service pack and see if they slid a fix in.

Good Luck!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
davie455Author Commented:
Apologies for the delay, DantheDBA seemed the closest but in truth I have not looked at this one for a while.
One of the ways to fix the errors was to grant the user, under whose account the error is generated, the SA access  and the error goes away.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.