January 28, 2009 at 12:19 pm
Ok, so here's what we found:
1. We shrink a SQL 2000 database
2. We then take a backup of a database from a SQL Server 2000 server.
3. we move the database to a SQL 2005 DB server and restore it to the SQL 2005 DB Server
4. We then update the compatilility to version 9.0
5. We update the statistics on our DB
6. We then resynce any SIDS/users once it's copied and restored
Issue:
We have a stored procedure (existing) that does the following:
select a bunch of case statements to build on (c.*)
from information_schema.columns C
INNER JOIN SYSCOLUMNS SC ON SC.NAME = C.COLUMN_NAME AND SC.ID = '927394423'
LEFT OUTER JOIN SYSCOMMENTS ON SYSCOMMENTS.ID = SC.ID AND SYSCOMMENTS.NUMBER = C.ORDINAL_POSITION
-- INNER JOIN SWLURefColDataType DT ON DT.Description = C.DATA_TYPE
WHERE C.table_name = ISNULL('lisah_test3', C.table_name)
AND C.COLUMN_NAME = ISNULL('MergePlace1Place2', C.COLUMN_NAME)
AND TABLE_SCHEMA = 'SWLookup'
Results:
This brings back 10 columns in the table we're looking for. In the old server SQL 2000 everything is matching up. Especially for a column called MP1P2.
In SQL 200 DB
Information_schema.columns - field - ordinal_position - value - 13
Syscomments - field - Number - value - 13
syscolumns - field - colid - value - 13
It's good so far. But we find out our application won't work because on the new SQL 2005 server this is what happened.
In SQL 200 DB
Information_schema.columns - field - ordinal_position - value - 10
Syscomments - field - Number - value - 13
syscolumns - field - colid - value - 13
It appears that because of our move, it reset all of the ordinal_positions of this database on the new server so it's looking and updated it knowing that there are only truly 10 columns. Now the Syscomments and syscolumns tables are out of sync. Their values for this field 'MP1P2' are not updated when we migrated the database.
Question for help:
Does anyone have experience with this happening when they did a database move from either one server to another or from one version of SQL 2000 to another SQL 2005? Is there a service patch or Something that fixes this issue? We have several other applications with stored procedures that are built this way similarly and we will have a huge issue going forward trying to even migrate to SQL 2005 at this point.
Any help is appreciated at this point.
:hehe:
January 28, 2009 at 12:25 pm
I can't answer the exact question you asked but I believe many would agree with me about what caused the problem. It's bad practice to have a hard coded "system id" in a stored procedure.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
January 28, 2009 at 12:38 pm
I do want to say, I was not here when anything was done and would agree with anyone that it's not good practice to be using the system tables in this way. I've always been told to stay away from this type of thing.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply