July 26, 2011 at 2:52 pm
GilaMonster (7/26/2011)
opc.three (7/26/2011)
What does this return on SQL 2000?
SELECT DISTINCTcollation
FROM syscolumns
WHERE id = OBJECT_ID('SALES')
AND collation IS NOT NULL ;
Sales is the name of the database, not a table.
😛 OK...substitute SALES with the name of one of the tables that ends up failing CHECKDB on the 2005 side.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 26, 2011 at 2:55 pm
GilaMonster (7/26/2011)
Ninja's_RGR'us (7/26/2011)
Assuming the db is usable on sql 2000, wouldn't scripting the tables and redeploying on 2k5 (or even 2000), possibly after changing the collation to something else "save the day"?Yes, and that's what I was suggesting with 'script, export and recreate'
I was just about to post this question...
Would an in-place table swap have a chance to fix the corruption, assuming it was data that entered through the SQL 2000 service elevator and not through T-SQL? By in-place I mean:
1. run a SELECT...INTO to a new table in the same database
2. drop references to the existing table containing corruption
3. drop existing table
4. rename new table to existing table name
5. apply necessary constraints, references and indexes
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 26, 2011 at 3:04 pm
No idea. No idea what caused this, what's actually damaged on SQL 2000, what it will affect.
Maybe. Maybe not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 27, 2011 at 8:36 am
Thank you all.
Well I ran the upgrade adviser on SQL 2000 server and books online says
In earlier versions of SQL Server, system object and system type names are matched against the collation of the master database. In SQL Server 2005, system object names and system type names are automatically cast into the collation of the current database. Your script or application may fail if references to these objects are not exactly as shown in the catalog and the collation of the current database causes a mismatch. For example, the statement EXEC SP_heLP will fail if the current database has a case-sensitive collation.
July 27, 2011 at 8:45 am
Sqlsavy (7/27/2011)
Thank you all.Well I ran the upgrade adviser on SQL 2000 server and books online says
In earlier versions of SQL Server, system object and system type names are matched against the collation of the master database. In SQL Server 2005, system object names and system type names are automatically cast into the collation of the current database. Your script or application may fail if references to these objects are not exactly as shown in the catalog and the collation of the current database causes a mismatch. For example, the statement EXEC SP_heLP will fail if the current database has a case-sensitive collation.
Well ya, I'm on a case sensitive server and it's still like that for the case. The real question is how does that help you?
Have you considered a PSS call in case we missed something we don't know. Maybe this is something really simple to fix (assuming it's not corruption).
July 27, 2011 at 8:48 am
Sqlsavy (7/27/2011)
Thank you all.Well I ran the upgrade adviser on SQL 2000 server and books online says
In earlier versions of SQL Server, system object and system type names are matched against the collation of the master database. In SQL Server 2005, system object names and system type names are automatically cast into the collation of the current database. Your script or application may fail if references to these objects are not exactly as shown in the catalog and the collation of the current database causes a mismatch. For example, the statement EXEC SP_heLP will fail if the current database has a case-sensitive collation.
What does this return on SQL 2000?
SELECT DISTINCT
collation
FROM syscolumns
WHERE id = OBJECT_ID('SALES') -- substitute name of a table that fails CHECKDB on 2005
AND collation IS NOT NULL ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 27, 2011 at 8:51 am
Microsoft is looking into the issue now
July 27, 2011 at 8:53 am
Sqlsavy (7/27/2011)
Microsoft is looking into the issue now
Ok, keep us posted. I'd love to hear a happy ending on this one, even if there's little hope left ;-).
July 27, 2011 at 12:19 pm
Ninja's_RGR'us (7/27/2011)
Sqlsavy (7/27/2011)
Microsoft is looking into the issue nowOk, keep us posted. I'd love to hear a happy ending on this one, even if there's little hope left ;-).
Me too. I would also like to see what this returns on SQL 2000 😀
SELECT DISTINCT
collation
FROM syscolumns
WHERE id = OBJECT_ID('SALES') -- substitute name of a table that fails CHECKDB on 2005
AND collation IS NOT NULL ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 27, 2011 at 12:23 pm
opc.three (7/27/2011)
Ninja's_RGR'us (7/27/2011)
Sqlsavy (7/27/2011)
Microsoft is looking into the issue nowOk, keep us posted. I'd love to hear a happy ending on this one, even if there's little hope left ;-).
Me too. I would also like to see what this returns on SQL 2000 😀
SELECT DISTINCTcollation
FROM syscolumns
WHERE id = OBJECT_ID('SALES') -- substitute name of a table that fails CHECKDB on 2005
AND collation IS NOT NULL ;
Don't worry, 5th time is usually the one that sticks ;-).
July 27, 2011 at 12:29 pm
Ninja's_RGR'us (7/27/2011)
opc.three (7/27/2011)
Ninja's_RGR'us (7/27/2011)
Sqlsavy (7/27/2011)
Microsoft is looking into the issue nowOk, keep us posted. I'd love to hear a happy ending on this one, even if there's little hope left ;-).
Me too. I would also like to see what this returns on SQL 2000 😀
SELECT DISTINCTcollation
FROM syscolumns
WHERE id = OBJECT_ID('SALES') -- substitute name of a table that fails CHECKDB on 2005
AND collation IS NOT NULL ;
Don't worry, 5th time is usually the one that sticks ;-).
Phewww...I was beginning to think it would go unanswered...maybe the OP is just busy ATM working with Microsoft Support :hehe:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 28, 2011 at 3:41 am
Existing production SQL 2000 collation is Latin1_General_CI_AS and I'd also installed SQL 2005 with default collation which is Latin1_General_CI_AS.
Microsoft initially suggested me to rebuild the system database with SQL_Latin1_General_CP1_CI_AS collation but DBCC resulted in same errors
The issue was with the collation of columns. Two ways of doing it.
1.Change columns collation for each and every table to SQL_Latin1_General_CP1_CI_AS or
2.Drop and recreate all objects then it will have the collation updated as SQL_Latin1_General_CP1_CI_AS and then transfer the data
Is there any checklist that we must use after upgrading the database from SQL 2000 to SQL 2005 /SQL 2008?
Sorry for the delay in response I got really busy .
Thanks for all your help it's much appreciated
July 28, 2011 at 5:52 am
Busy repairing a corrupt DB? I think we can let it slide ;-).
Thanks for letting us know the way to repair that particular corruption. Hopefully it'll help someone else in the future.
The most obvious steps after the upgrade are to run checkdb, rebuild all indexes and update stats with full scan.
Of course you need to retest the whole application to make sure it still works.
You can expect some differences in performance. Some query errors in 2000 become apparent in 2k5 while they were "fine" in 2000. Also the query optimizer has changed in 2k5 so some things will act differently (not wrong, just different) and you may need to adjust a few queries because of that (just monitor for long running queries + IO & CPU intensive as well).
Of course transfer the jobs so that maintenance is transferred to the new server.
I'd also run the upgrade advisor on a full day trace (and try to capture end of month reports / procedures). The upgrade advisor will pick up many things that may need your attention, but it's not a 100% guarantee that it'll work.
July 28, 2011 at 7:08 am
Thanks a lot
July 28, 2011 at 8:41 am
Running the upgrade advisor against a full day's trace is a good call, especially if you have apps issuing embedded SQL...the more you can capture and analyze the better off you'll be. If you have a lot of stored procedures, functions, etc. also run the upgrade advisor against the database itself. There are some T-SQL changes that 2005 no longer supoprts that the advisor can help identify in your code before you migrate. The #1 that comes to mind are no more support for *= and =* in WHERE-clause-style joins. Those will have to go ahead of time, else you'll be stuck using 80 compat mode. Good luck!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply