Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 729
  • Last Modified:

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
0
davie455
Asked:
davie455
  • 2
  • 2
1 Solution
 
DantheDBACommented:
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.
0
 
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.

Thanks
0
 
DantheDBACommented:
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!
0
 
davie455Author Commented:
Apologies for the delay, DantheDBA seemed the closest but in truth I have not looked at this one for a while.
0
 
gsbajwaCommented:
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now