asked on

SQL User Error on Exec Stored Procedure

Hi,
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

DantheDBA

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.

davie455

ASKER

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.

Thanks

ASKER CERTIFIED SOLUTION

DantheDBA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

davie455

ASKER

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

gsbajwa

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.