3701 Errors after installing SP2
in [Server Configuration]
|
Prev: Error in maintenance plan after SP2
Next: Cannot bulk load. Invalid column number in the format file
From: Barb on 27 Feb 2007 13:51 Environment: SQL Server 2005 Enterprise Edition SP2 Windows 2003 R2 x64 Enterprise 80+ Databases, all upgraded to compatibility 90 Since upgrading to SP2 for SQL Server 2005, my scheduled jobs based on Maintenance Plans which include Statistics Updates, Index Rebuilding and Index Reorganization tasks have reported failure. The failed job history indicates the following: “The Execute method on the task returned error code 0x8007000E (Exception of type 'System.OutOfMemoryException' was thrown.).” Although the job fails, I'm not convinced that it's not also completing its tasks. A trace on the activity of the SQL Agent while the jobs are running indicates that these jobs are properly executing their Alter Table statements without incident and the duration of execution seems to be roughly the same as before. The above seemed like an unsatisfactorily vague error, so I decided to run a bit more extensive trace to see exactly what errors were being raised while the job was run. Doing so revealed the following error: Event Class: User Error Message; ApplicationName: Microsoft SQL Server Management Studio; Error: 3701; TextData: “Cannot drop the table '#tmp_sp_db_vardecimal_storage_format', because it does not exist or you do not have permission.” This error did not occur prior to the upgrade to SP2, and I've noticed similar errors being trapped in traces run on my other SQL Servers post-SP2 when trying to do multi-database maintenance plans. I have expanded my trace to trap all of the RPC and SQL textdata created by the SQL Agent when running my jobs, looking in particular for the text '#tmp_sp_db_vardecimal_storage_format'. I found on several occasions (at least 4 times for each database altered within the job) that a “drop table #tmp_sp_db_vardecimal_storage_format” was being called within the maintenance plan without any corresponding create table statement. Though I don't doubt that my maintenance plans are succeeding at some level, I'm bothered by all of these unnecessary 3701 errors raised by the “drop table #tmp_sp_db_vardecimal_storage_format” statements. I believe that this is a bug in the post-SP2 Management Studio code and wonder if there's some kind of workaround to avoid this, or some patch on the near horizon. Thanks, Barb
From: John Bell on 28 Feb 2007 02:58 Hi Barb "Barb" wrote: > Environment: > > SQL Server 2005 Enterprise Edition SP2 > > Windows 2003 R2 x64 Enterprise > > 80+ Databases, all upgraded to compatibility 90 > > > > Since upgrading to SP2 for SQL Server 2005, my scheduled jobs based on > Maintenance Plans which include Statistics Updates, Index Rebuilding and > Index Reorganization tasks have reported failure. The failed job history > indicates the following: “The Execute method on the task returned error code > 0x8007000E (Exception of type 'System.OutOfMemoryException' was thrown.).” > Although the job fails, I'm not convinced that it's not also completing its > tasks. A trace on the activity of the SQL Agent while the jobs are running > indicates that these jobs are properly executing their Alter Table statements > without incident and the duration of execution seems to be roughly the same > as before. > > > > The above seemed like an unsatisfactorily vague error, so I decided to run a > bit more extensive trace to see exactly what errors were being raised while > the job was run. Doing so revealed the following error: Event Class: User > Error Message; ApplicationName: Microsoft SQL Server Management Studio; > Error: 3701; TextData: “Cannot drop the table > '#tmp_sp_db_vardecimal_storage_format', because it does not exist or you do > not have permission.” > > > > This error did not occur prior to the upgrade to SP2, and I've noticed > similar errors being trapped in traces run on my other SQL Servers post-SP2 > when trying to do multi-database maintenance plans. I have expanded my trace > to trap all of the RPC and SQL textdata created by the SQL Agent when running > my jobs, looking in particular for the text > '#tmp_sp_db_vardecimal_storage_format'. I found on several occasions (at > least 4 times for each database altered within the job) that a “drop table > #tmp_sp_db_vardecimal_storage_format” was being called within the maintenance > plan without any corresponding create table statement. > > > > Though I don't doubt that my maintenance plans are succeeding at some level, > I'm bothered by all of these unnecessary 3701 errors raised by the “drop > table #tmp_sp_db_vardecimal_storage_format” statements. I believe that this > is a bug in the post-SP2 Management Studio code and wonder if there's some > kind of workaround to avoid this, or some patch on the near horizon. > > > Thanks, > Barb vardecimal is a new datatype introduced in SP2. Have you tried to run sp_db_vardecimal_storage_format http://msdn2.microsoft.com/en-us/library/bb326653.aspx John
From: delashmutt on 28 Feb 2007 15:56
On Feb 27, 11:58 pm, John Bell <jbellnewspo...(a)hotmail.com> wrote: > Hi Barb > > > > > > "Barb" wrote: > > Environment: > > > SQL Server 2005 Enterprise EditionSP2 > > > Windows 2003 R2 x64 Enterprise > > > 80+ Databases, all upgraded to compatibility 90 > > > Since upgrading toSP2for SQL Server 2005, my scheduled jobs based on > > Maintenance Plans which include Statistics Updates, Index Rebuilding and > > Index Reorganization tasks have reported failure. The failed job history > > indicates the following: "The Execute method on the task returned error code > > 0x8007000E (Exception of type 'System.OutOfMemoryException' was thrown.)." > > Although the job fails, I'm not convinced that it's not also completing its > > tasks. A trace on the activity of the SQL Agent while the jobs are running > > indicates that these jobs are properly executing their Alter Table statements > > without incident and the duration of execution seems to be roughly the same > > as before. > > > The above seemed like an unsatisfactorily vague error, so I decided to run a > > bit more extensive trace to see exactly what errors were being raised while > > the job was run. Doing so revealed the following error: Event Class: User > > Error Message; ApplicationName: Microsoft SQL Server Management Studio; > > Error: 3701; TextData: "Cannot drop the table > > '#tmp_sp_db_vardecimal_storage_format', because it does not exist or you do > > not have permission." > > > This error did not occur prior to the upgrade toSP2, and I've noticed > > similar errors being trapped in traces run on my other SQL Servers post-SP2 > > when trying to do multi-database maintenance plans. I have expanded my trace > > to trap all of the RPC and SQL textdata created by the SQL Agent when running > > my jobs, looking in particular for the text > > '#tmp_sp_db_vardecimal_storage_format'. I found on several occasions (at > > least 4 times for each database altered within the job) that a "drop table > > #tmp_sp_db_vardecimal_storage_format" was being called within the maintenance > > plan without any corresponding create table statement. > > > Though I don't doubt that my maintenance plans are succeeding at some level, > > I'm bothered by all of these unnecessary 3701 errors raised by the "drop > > table #tmp_sp_db_vardecimal_storage_format" statements. I believe that this > > is a bug in the post-SP2Management Studio code and wonder if there's some > > kind of workaround to avoid this, or some patch on the near horizon. > > > Thanks, > > Barb > > vardecimal is a new datatype introduced inSP2. Have you tried to run > sp_db_vardecimal_storage_formathttp://msdn2.microsoft.com/en-us/library/bb326653.aspx > > John- Hide quoted text - > > - Show quoted text - If you run a User Error Trace and look for 3701 errors, they pop up not only when the packages are being run, but also when you're trying to create Maint. jobs in SQL Server Management Studio. Whether sp_db_vardecimal_storage_format is enabled or disabled for a database makes no difference. |