I have been trying to export an sql2000 database to sql2005 with no luck. I continuosly get the error
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I have looked upon all support material and have installed the latest sql2005 service pack, but this has not fixed the problem. a simple query as:
select * from [transaction] ta,transactionentry tr
where tr.transactionnumber=ta.transactionnumber and
glacctid=6 and ta.[time]> '3/01/2006'
would produce this fatal error.
the current version I am using is:
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
This problem is happening on some of the most important tables in the database.
the error log contains the following:
2006-06-06 11:43:32.59 spid54 ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0001.txt
2006-06-06 11:43:32.59 spid54 SqlDumpExceptionHandler: Process 54 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2006-06-06 11:43:32.59 spid54 * *******************************************************************************
2006-06-06 11:43:32.59 spid54 *
2006-06-06 11:43:32.59 spid54 * BEGIN STACK DUMP:
2006-06-06 11:43:32.59 spid54 * 06/06/06 11:43:32 spid 54
2006-06-06 11:43:32.59 spid54 *
2006-06-06 11:43:32.59 spid54 *
2006-06-06 11:43:32.59 spid54 * Exception Address = 0136B4F0 Module(sqlservr+0036B4F0)
2006-06-06 11:43:32.59 spid54 * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
2006-06-06 11:43:32.59 spid54 * Access Violation occurred writing address FFFFFFFC
2006-06-06 11:43:32.59 spid54 * Input Buffer 260 bytes -
2006-06-06 11:43:32.59 spid54 * select * from [transaction] tr,transactionentry te where tr.
2006-06-06 11:43:32.59 spid54 * transactionnumber = te.transactionnumber and glacctid = 6
2006-06-06 11:43:32.59 spid54 *
2006-06-06 11:43:32.59 spid54 *
2006-06-06 11:43:32.59 spid54 * MODULE BASE END SIZE
2006-06-06 11:43:32.59 spid54 * sqlservr 01000000 02BA7FFF 01ba8000
2006-06-06 11:43:32.59 spid54 * ntdll 7C800000 7C8BFFFF 000c0000
2006-06-06 11:43:32.59 spid54 * kernel32 77E40000 77F41FFF 00102000
2006-06-06 11:43:32.59 spid54 * MSVCR80 78130000 781CAFFF 0009b000
2006-06-06 11:43:32.59 spid54 * msvcrt 77BA0000 77BF9FFF 0005a000
I have followed instructions on some support issues posted but none have helped. If anyone has any solution to this problem PLEASE HELP!!!!.
Thanks.
Run DBCC CHECKDB for your database.|||In order to verify the integrity of the database I had run this utility, but it did not find any problems in it. I have also tried different approaches for the database port including restoring a backup created on sql2000, detaching from sql2000 and attaching in sql2005, verifying the sql2005 configuration options for backwards compatibilty mode. None of these seem to work.
I also applied the hotfix referred in BUG #412387 (http://support.microsoft.com/kb/910416/en-us ), which is basically the problem I am having, nonetheless, my problem persists.
Anyone know if there is an additional patch or hotfix to solve this issue?
|||I think to be able to execute these queries in SQL 2005, you need to rewrite with latest standards. So your query would be
select *
from [transaction] ta
join transactionentry tr
on tr.transactionnumber = ta.transactionnumber
and glacctid=6
and ta.[time]> '3/01/2006'
or if you can no afford to that, I think it will work if you set database in 80 compatibility mode
satya
|||The compatibility level is already set in 80 compatibility mode. I tried your suggestions but the problem persists.
Anyway, how can a database that is currently working perfectly on sql2000 will be generating such errors if it is in a correct compatibility level on sql2005?
take the following example:
UPDATE liquidations
SET ov60 = 0.0
WHERE ov60 > 0
Even in this simple update query I get the error. What bothers me is that the DBCC utility doesnt report any errors.
|||You have hit a bug in the server. First, you need to eliminate any database corruption, OS errors, driver issues and see if it resolves the problem. Also, please check the same query on a different server. Also, try to install the rollup for service pack 1 also and see if it fixes the problem. If these fail then you willl have to call CSS to better troubleshoot and resolve the problem.|||You have been correct. After verifying all possible factors (which were OK). I called the Microsoft SQL Support Team for Help. Here is the workaround for this specific error.
1) There is a bug, which is currently being worked, related to Hypothetical Clustered Indexes created by the Index Tuning wizard which causes some corruption on the system tables. See the following link for more information: http://support.microsoft.com/kb/293177
2) Prior to exporting the sql200 database, you must first run the following script:
DECLARE @.strSQL nvarchar(1024)
DECLARE @.objid int
DECLARE @.indid tinyint
DECLARE ITW_Stats CURSOR FOR SELECT id, indid FROM sysindexes WHERE
name LIKE 'hind_%' ORDER BY name
OPEN ITW_Stats
FETCH NEXT FROM ITW_Stats INTO @.objid, @.indid
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
SELECT @.strSQL = (SELECT case when INDEXPROPERTY(i.id, i.name,
'IsStatistics') = 1 then 'drop statistics [' else 'drop index [' end +
OBJECT_NAME(i.id) + '].[' + i.name + ']'
FROM sysindexes i join sysobjects o on i.id = o.id
WHERE i.id = @.objid and i.indid = @.indid AND
(INDEXPROPERTY(i.id, i.name, 'IsHypothetical') = 1 OR
(INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 AND
INDEXPROPERTY(i.id, i.name, 'IsAutoStatistics') = 0)))
Print(@.strSQL)
FETCH NEXT FROM ITW_Stats INTO @.objid, @.indid
END
CLOSE ITW_Stats
DEALLOCATE ITW_Stats
go
3) Copy the result of this query on the results pane and run in another query analizer window. Remember that this has to be run in the sql2000 instance. If no results are shown, go to step 4.
4) Remove all replication from database. Run the following command:
sp_removedbreplication 'your_database_name'
go
5) make sure that there are no replication tables present on the database. If they exist, you must remove all replication objects from the database. To verify this, here are the names of the replication tables:
drop table msdynamicsnapshotjobs
drop table msdynamicsnapshotviews
drop table msmerge_errorlineage
drop table msmerge_altsyncpartners
drop table msmerge_contents
drop table msmerge_delete_conflicts
drop table msmerge_genhistory
drop table msmerge_replinfo
drop table msmerge_tombstone
drop table msrepl_identity_range
drop table msreplication_subscriptions
drop table mssubscription_agents
go
you also have to verify for stored procedures, views and triggers. They are easily identifiable because they usually have a long numeric value in their names such as the following examples:
-- drop replication related stored procs
-- verify manually, (these are just a sample...)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ins_118D3CDAB9354253C1D8BE1E0E774B32]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_ins_118D3CDAB9354253C1D8BE1E0E774B32]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_sel_118D3CDAB9354253C1D8BE1E0E774B32]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_sel_118D3CDAB9354253C1D8BE1E0E774B32]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_upd_118D3CDAB9354253C1D8BE1E0E774B32]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_upd_118D3CDAB9354253C1D8BE1E0E774B32]
GO
-- views
drop view dbo.ctsv_118D3CDAB935425385100B9FB9E2B271
go
--
drop view dbo. tsvw_118D3CDAB935425385100B9FB9E2B271
go
-- get list of replication triggers ...
-- remove manually.
select [name] from sysobjects where type = 'tr' and left([name],4) = 'del_'
select [name] from sysobjects where type = 'tr' and left([name],4) = 'upd_'
select [name] from sysobjects where type = 'tr' and left([name],4) = 'ins_'
select [name] from sysobjects where type = 'tr' and left([name],4) = 'del_'
-- triggers --
drop trigger upd_BE75F55A44E944438D239CB8E2F22022
drop trigger upd_5C20E317FEAA460490BC3B35019C6A20
drop trigger upd_118D3CDAB935425385100B9FB9E2B271
drop trigger upd_72966E932FB24345AC2EE1DAF1D4A1C5
go
drop trigger ins_BE75F55A44E944438D239CB8E2F22022
drop trigger ins_5C20E317FEAA460490BC3B35019C6A20
drop trigger ins_118D3CDAB935425385100B9FB9E2B271
drop trigger ins_72966E932FB24345AC2EE1DAF1D4A1C5
go
drop trigger del_BE75F55A44E944438D239CB8E2F22022
drop trigger del_5C20E317FEAA460490BC3B35019C6A20
drop trigger del_118D3CDAB935425385100B9FB9E2B271
drop trigger del_72966E932FB24345AC2EE1DAF1D4A1C5
go
6) run checkcatalog to verify consistency.
dbcc checkcatalog
go
7) Now run db.
dbcc checkdb
go
8) you're set if all is OK!, now do backup and restore at sql2005 Server Instance.
if the restore is not successfull due to replication problems as stated in the following article:
http://msdn2.microsoft.com/en-us/library/ms151782.aspx
you have to bring online the restored database and run the following:
sp_removedbreplication
go
-- if it fails run again:
sp_removedbreplication
go
9) after restore on sql2005 do a reindex...
use 'your_database_name'
go
-- now make complete database reindex.
DECLARE @.table_name varchar(1000)
declare c1 cursor for SELECT name
FROM sysobjects
WHERE xtype = 'U'
open c1
fetch next from c1 into @.table_name
while @.@.Fetch_Status = 0
begin
DBCC DBREINDEX (@.table_name, '')
fetch next from c1 into @.table_name
end
close c1
deallocate c1
GO
-- after this, run the following:
dbcc checkdb
go
this should cover this export issue. I have followed these instructions and the issue was solved.
Crames.
No comments:
Post a Comment