3701 Errors after installing SP2

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 c
ode
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 statement
s
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 trac
e
to trap all of the RPC and SQL textdata created by the SQL Agent when runnin
g
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 maintena
nce
plan without any corresponding create table statement.
Though I don’t doubt that my maintenance plans are succeeding at some leve
l,
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 th
is
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,
BarbHi 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 completin
g its
> tasks. A trace on the activity of the SQL Agent while the jobs are runnin
g
> indicates that these jobs are properly executing their Alter Table stateme
nts
> without incident and the duration of execution seems to be roughly the sam
e
> 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 whil
e
> the job was run. Doing so revealed the following error: Event Class: Us
er
> 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 d
o
> 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-SP
2
> when trying to do multi-database maintenance plans. I have expanded my tr
ace
> to trap all of the RPC and SQL textdata created by the SQL Agent when runn
ing
> my jobs, looking in particular for the text
> ‘#tmp_sp_db_vardecimal_storage_format'
. I found on several occasions (a
t
> least 4 times for each database altered within the job) that a “drop tab
le
> #tmp_sp_db_vardecimal_storage_format” was being called within the mainte
nance
> plan without any corresponding create table statement.
>
> Though I don’t doubt that my maintenance plans are succeeding at some le
vel,
> I’m bothered by all of these unnecessary 3701 errors raised by the “dr
op
> 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 so
me
> 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|||On Feb 27, 11:58 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Barb
>
>
> "Barb" wrote:
>
>
>
>
>
>
>
>
> vardecimal is a new datatype introduced inSP2. Have you tried to run
> sp_db_vardecimal_storage_formathttp://msdn2.microsoft.com/en-us/library/bb
326653.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.|||Hi,
Have you found any solution to this as I am also facing the similar problem
after updrading to SP2. Please let me know the solution as I am completely
stuck at this point.
MS