8.0 compatibility question

All,
I am upgrading an instance of SQL 2000 to SQL 2005.
Two of my applications have databases in this instance. According to their
vendors, these two applications are not compatible with SQL 2005.
However, these databases, although running in SQL Server 2005, are using 8.0
compatibility mode. I learned that databases stay in their original
compatibility mode when upgraded to 2005.
So my question is - if these databases remain in 8.0 compatibility mode
after an upgrade, doesn't that mean they will still work as if they were
running on SQL 2000 once I upgrade to 2005? And doesn't that mean they WON'T
break, which would save me the torture of porting them to a separate named
2000 instance and maintaining that instance?
Please, if anyone has any insight on this, let me know."Ant-nee" <Antnee@.discussions.microsoft.com> wrote in message
news:51F67FB1-BF4D-46F0-B6FB-31DEF7EEDF86@.microsoft.com...
> All,
> I am upgrading an instance of SQL 2000 to SQL 2005.
> Two of my applications have databases in this instance. According to
> their
> vendors, these two applications are not compatible with SQL 2005.
> However, these databases, although running in SQL Server 2005, are using
> 8.0
> compatibility mode. I learned that databases stay in their original
> compatibility mode when upgraded to 2005.
> So my question is - if these databases remain in 8.0 compatibility mode
> after an upgrade, doesn't that mean they will still work as if they were
> running on SQL 2000 once I upgrade to 2005? And doesn't that mean they
> WON'T
> break, which would save me the torture of porting them to a separate named
> 2000 instance and maintaining that instance?
>
8.0 Compatibility Mode is designed to minimize upgrade problems with SQL
2000 applications, running them in an environment as similar to SQL 2000 as
possible. However you still need to work with your application vendor to
make sure that they support your configuration.
David|||Unfortunately no. When you upgrade to 2005 the database structure is
changed. The 8.0 compatibility mode just turns off some of the new
features - it doesn't leave the database in SQL 2000 format.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Ant-nee" <Antnee@.discussions.microsoft.com> wrote in message
news:51F67FB1-BF4D-46F0-B6FB-31DEF7EEDF86@.microsoft.com...
> All,
> I am upgrading an instance of SQL 2000 to SQL 2005.
> Two of my applications have databases in this instance. According to
> their
> vendors, these two applications are not compatible with SQL 2005.
> However, these databases, although running in SQL Server 2005, are using
> 8.0
> compatibility mode. I learned that databases stay in their original
> compatibility mode when upgraded to 2005.
> So my question is - if these databases remain in 8.0 compatibility mode
> after an upgrade, doesn't that mean they will still work as if they were
> running on SQL 2000 once I upgrade to 2005? And doesn't that mean they
> WON'T
> break, which would save me the torture of porting them to a separate named
> 2000 instance and maintaining that instance?
> Please, if anyone has any insight on this, let me know.

8.0 compatibility question

All,
I am upgrading an instance of SQL 2000 to SQL 2005.
Two of my applications have databases in this instance. According to their
vendors, these two applications are not compatible with SQL 2005.
However, these databases, although running in SQL Server 2005, are using 8.0
compatibility mode. I learned that databases stay in their original
compatibility mode when upgraded to 2005.
So my question is - if these databases remain in 8.0 compatibility mode
after an upgrade, doesn't that mean they will still work as if they were
running on SQL 2000 once I upgrade to 2005? And doesn't that mean they WON'T
break, which would save me the torture of porting them to a separate named
2000 instance and maintaining that instance?
Please, if anyone has any insight on this, let me know.
"Ant-nee" <Antnee@.discussions.microsoft.com> wrote in message
news:51F67FB1-BF4D-46F0-B6FB-31DEF7EEDF86@.microsoft.com...
> All,
> I am upgrading an instance of SQL 2000 to SQL 2005.
> Two of my applications have databases in this instance. According to
> their
> vendors, these two applications are not compatible with SQL 2005.
> However, these databases, although running in SQL Server 2005, are using
> 8.0
> compatibility mode. I learned that databases stay in their original
> compatibility mode when upgraded to 2005.
> So my question is - if these databases remain in 8.0 compatibility mode
> after an upgrade, doesn't that mean they will still work as if they were
> running on SQL 2000 once I upgrade to 2005? And doesn't that mean they
> WON'T
> break, which would save me the torture of porting them to a separate named
> 2000 instance and maintaining that instance?
>
8.0 Compatibility Mode is designed to minimize upgrade problems with SQL
2000 applications, running them in an environment as similar to SQL 2000 as
possible. However you still need to work with your application vendor to
make sure that they support your configuration.
David
|||Unfortunately no. When you upgrade to 2005 the database structure is
changed. The 8.0 compatibility mode just turns off some of the new
features - it doesn't leave the database in SQL 2000 format.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Ant-nee" <Antnee@.discussions.microsoft.com> wrote in message
news:51F67FB1-BF4D-46F0-B6FB-31DEF7EEDF86@.microsoft.com...
> All,
> I am upgrading an instance of SQL 2000 to SQL 2005.
> Two of my applications have databases in this instance. According to
> their
> vendors, these two applications are not compatible with SQL 2005.
> However, these databases, although running in SQL Server 2005, are using
> 8.0
> compatibility mode. I learned that databases stay in their original
> compatibility mode when upgraded to 2005.
> So my question is - if these databases remain in 8.0 compatibility mode
> after an upgrade, doesn't that mean they will still work as if they were
> running on SQL 2000 once I upgrade to 2005? And doesn't that mean they
> WON'T
> break, which would save me the torture of porting them to a separate named
> 2000 instance and maintaining that instance?
> Please, if anyone has any insight on this, let me know.

8.0 compatibility question

All,
I am upgrading an instance of SQL 2000 to SQL 2005.
Two of my applications have databases in this instance. According to their
vendors, these two applications are not compatible with SQL 2005.
However, these databases, although running in SQL Server 2005, are using 8.0
compatibility mode. I learned that databases stay in their original
compatibility mode when upgraded to 2005.
So my question is - if these databases remain in 8.0 compatibility mode
after an upgrade, doesn't that mean they will still work as if they were
running on SQL 2000 once I upgrade to 2005? And doesn't that mean they WON'
T
break, which would save me the torture of porting them to a separate named
2000 instance and maintaining that instance?
Please, if anyone has any insight on this, let me know."Ant-nee" <Antnee@.discussions.microsoft.com> wrote in message
news:51F67FB1-BF4D-46F0-B6FB-31DEF7EEDF86@.microsoft.com...
> All,
> I am upgrading an instance of SQL 2000 to SQL 2005.
> Two of my applications have databases in this instance. According to
> their
> vendors, these two applications are not compatible with SQL 2005.
> However, these databases, although running in SQL Server 2005, are using
> 8.0
> compatibility mode. I learned that databases stay in their original
> compatibility mode when upgraded to 2005.
> So my question is - if these databases remain in 8.0 compatibility mode
> after an upgrade, doesn't that mean they will still work as if they were
> running on SQL 2000 once I upgrade to 2005? And doesn't that mean they
> WON'T
> break, which would save me the torture of porting them to a separate named
> 2000 instance and maintaining that instance?
>
8.0 Compatibility Mode is designed to minimize upgrade problems with SQL
2000 applications, running them in an environment as similar to SQL 2000 as
possible. However you still need to work with your application vendor to
make sure that they support your configuration.
David|||Unfortunately no. When you upgrade to 2005 the database structure is
changed. The 8.0 compatibility mode just turns off some of the new
features - it doesn't leave the database in SQL 2000 format.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Ant-nee" <Antnee@.discussions.microsoft.com> wrote in message
news:51F67FB1-BF4D-46F0-B6FB-31DEF7EEDF86@.microsoft.com...
> All,
> I am upgrading an instance of SQL 2000 to SQL 2005.
> Two of my applications have databases in this instance. According to
> their
> vendors, these two applications are not compatible with SQL 2005.
> However, these databases, although running in SQL Server 2005, are using
> 8.0
> compatibility mode. I learned that databases stay in their original
> compatibility mode when upgraded to 2005.
> So my question is - if these databases remain in 8.0 compatibility mode
> after an upgrade, doesn't that mean they will still work as if they were
> running on SQL 2000 once I upgrade to 2005? And doesn't that mean they
> WON'T
> break, which would save me the torture of porting them to a separate named
> 2000 instance and maintaining that instance?
> Please, if anyone has any insight on this, let me know.

6.5 upgrade - pls help!

I am having problems upgrading from 6.5 to 2000 using the
upgrade wizard. We have a 6.5 server called medisec that
we want to upgrade to a 2000 server called medisec2000. I
wanted to test the upgrade so I made a copy of the 6.5
database from medisec server to another 6.5 server that I
called medisec2. I then ran the upgrade wizard from
medisec2 to medisec2000 which ran ok. I then wanted to
upgrade the 'live' medisec server so I deleted the
upgraded database and tried to run the upgrade wizard
again. I got an error "You have already started upgrading
from MEDISEC2. You can not consolidate MEDISEC into
MEDISEC2000. The export server name has been changed to
the server you already started to upgrade." It looks like
it doesn't like me trying to upgrade from a different sql
6.5 server as it thinks I am trying to consolidate from
multiple 6.5 servers. I am not doing this though and just
want to somehow 'reset' the test upgrade. I don't really
want to try reinstalling sql 2000 on medisec2000 as this
server is already running other live apps. Can I
somehow 'reset' the medisec2000 server so that I can run a
fresh upgrade from the other server'Nick,
Why not install another instance of SS2000 and upgrade to that instance.
Then detach the databases from your new instance and attach them to
MEDISEC2000.
After that, you can remove the temporary instance you used for your upgrade.
Russell Fields
"Nick Astles" <nick.astles@.coch.nhs.uk> wrote in message
news:116201c35b2e$65dd96f0$3501280a@.phx.gbl...
> I am having problems upgrading from 6.5 to 2000 using the
> upgrade wizard. We have a 6.5 server called medisec that
> we want to upgrade to a 2000 server called medisec2000. I
> wanted to test the upgrade so I made a copy of the 6.5
> database from medisec server to another 6.5 server that I
> called medisec2. I then ran the upgrade wizard from
> medisec2 to medisec2000 which ran ok. I then wanted to
> upgrade the 'live' medisec server so I deleted the
> upgraded database and tried to run the upgrade wizard
> again. I got an error "You have already started upgrading
> from MEDISEC2. You can not consolidate MEDISEC into
> MEDISEC2000. The export server name has been changed to
> the server you already started to upgrade." It looks like
> it doesn't like me trying to upgrade from a different sql
> 6.5 server as it thinks I am trying to consolidate from
> multiple 6.5 servers. I am not doing this though and just
> want to somehow 'reset' the test upgrade. I don't really
> want to try reinstalling sql 2000 on medisec2000 as this
> server is already running other live apps. Can I
> somehow 'reset' the medisec2000 server so that I can run a
> fresh upgrade from the other server'

6.5 upgrade

I am having trouble upgrading from SQL 6.4 to SQL 2000
using the wizard.
The wizard runs nicely until I get to 'Export and Import
via Named pipes', then I get the following message:
Export.exe on the remote machine was hung...
Export Exit Code: 259 - export.exe -CodePage 1252 -
DeviceType Pipe -AllTables yes -MasterPath
C:\MSSQL\DATA\MASTER.DAT -DatabaseName Comp2 -DevicePath
\\.\pipe\~cnvpipe0
Import Exit Code: -1 - ~cnvpipe0
Msg 4854, Level 21, State 1, Server RLINKDEST, Procedure ,
Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]****
Time:10-14-2003 10:27:21.125
Error return from function CreateFile
in file CnvPipe.cpp
Line=143
rc=5
Msg=Access is denied.
I have the same SA password on all accounts, and I have
set all ODBC drivers to connect in the same way, via named
pipes.
Please help!
Thank you
SusanneIf this is a 2 machine upgrade, then the SQL Server startup account on the
SQL 6.5 box may not have permission to the SQL Server 2000 machine.
Rand
This posting is provided "as is" with no warranties and confers no rights.

4GB sql file

I have a 4GB SQL db that is used for Backup Exec, but the file has
reached the maximum file size for the file system. I have tried
upgrading from 2000 to 2005 but I do not get any more options, such as
shrink or to do maintenance on the db through the Enterprise manager.
Is my install stuck on the msde license and won't let me install the
full blown sql server until I reboot the server? ThanksHi
If you have 4GB database and thinking to upgrare to SQL Server 2005 , you DO
NOT have MSDE ,MSDE has a limit 2GB
http://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-sql-server.html
<jooooooohn@.gmail.com> wrote in message
news:1191878238.775355.215520@.r29g2000hsg.googlegroups.com...
>I have a 4GB SQL db that is used for Backup Exec, but the file has
> reached the maximum file size for the file system. I have tried
> upgrading from 2000 to 2005 but I do not get any more options, such as
> shrink or to do maintenance on the db through the Enterprise manager.
> Is my install stuck on the msde license and won't let me install the
> full blown sql server until I reboot the server? Thanks
>|||jooooooohn@.gmail.com wrote:
> I have a 4GB SQL db that is used for Backup Exec, but the file has
> reached the maximum file size for the file system. I have tried
> upgrading from 2000 to 2005 but I do not get any more options, such as
> shrink or to do maintenance on the db through the Enterprise manager.
> Is my install stuck on the msde license and won't let me install the
> full blown sql server until I reboot the server? Thanks
It's not clear if you are hitting the limit of the FAT32 file system or
the limit of MSDE/SQL Express. If the file system is the problem, then
change it to NTFS. If you already use NTFS, then try to shrink the file
using OSQL.EXE. For example, you can try DBCC SHRINKFILE(1,1). Which is
the big file: the MDF (data file) or the LDF (log file) ?
For more information, see Books Online topics for DBCC SHRINKFILE and
DBCC SHRINKDATABASE.
--
Razvan Socol
SQL Server MVP

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 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
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
|||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/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.
|||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

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

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 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,
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 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|||On Feb 27, 11:58 pm, John Bell <jbellnewspo...@.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.|||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