SQL User Error on Exec Stored Procedure

Posted on 2007-08-09
Last Modified: 2013-11-05
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
Question by:davie455
    LVL 6

    Expert Comment

    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.

    Author Comment

    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.

    LVL 6

    Accepted Solution

    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.


    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!

    Author Closing Comment

    Apologies for the delay, DantheDBA seemed the closest but in truth I have not looked at this one for a while.

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Suggested Solutions

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now