Need a little advice on fixing a client's DB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Need a little advice on fixing a client's DB

Author

Topic

nzmike
Starting Member

21 Posts

Posted - 2007-04-20 : 20:16:50

I have a client who runs a SQL Server 2000 database behind ThumbsPlus 7 (an image management package) and they recently decided to move the database itself and the underlying image repository to a new server.
Unfortunately they are not clued up on SQL server so they managed to pretty much scr*w it up so they have asked me to help them fix it.

Whilst the database (more-or-less) works OK, they have ended up with a few tables with lots of rows where the primary key = null. (This came about as they started messing with the design of tables and ticked "Allow nulls" on everything and now when new rows are added from TP7 they seem to have null ID's). Luckily the damage is pretty confined and I know I can fix it but I need to be able to generate new primary key IDs (ONLY for the rows that are null) on a few tables.

So my question is how can I do this automatically via a script? I want to say something like:

startingID = 12345;
for each row where id = null
row.id = startingID
startingID += 1
next

Would I do this sort of thing in a cursor or is there a better, faster way to assign new ID's to just certain rows? At the moment, because of they changes they made to the tables, I can do this as there is no real integrity (until I turn it back on after my fixes).

I'm more of a developer than a DBA but I know I can fix their DB - I just need a bit of help automating it!

This is a pretty urgent job as they can't use their DB until I fix it so any help or advice would be greatly appreciated.

Mike

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-21 : 00:49:44

how many rows approxly do you have like that that are NULL?

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Kristen
Test

22859 Posts

Posted - 2007-04-21 : 01:35:11

What I would do is:

SELECT [T_ID] = IDENTITY(int, 12345678, 1), PKColumn1, PKColumn2
INTO #MyTempTable
FROM MYTable
WHERE MyID IS NULL

UPDATE U
SET MyID = [T_ID]
FROM MyTable AS M
JOIN #MyTemp AS T
ON T.PKColumn1 = M.PKColumn1
AND T.PKColumn2 = M.PKColumn2

where "12345678" is the value of the next ID number you want to assign

It does require that you have one, or more, columns which in combination uniquely reference a row - i.e. PKColumn1 + PKColumn2 in my example.

"lots of rows where the primary key = null"

By definition a primary key cannot be NULL

Kristen

nzmike
Starting Member

21 Posts

Posted - 2007-04-21 : 03:08:19

Thanks Kristen - I appreciate the help.

I'm aware that PK's are meant to be unique but in their attempts to fix it themselves my client altered all sorts of things which I think probably included taking the PK off the ID columns in the table and ticking "allow nulls" on every column in every table... I'm quite surprised they have any database at all!!

Unfortunately the main table in question doesn't have another PK that I can use but there are only about 600 rows with the problem so I extracted those to Excel, added a column with the new ID value for each row and then (via a simple formula) created an update statement based on the physical path and file name of the image which luckily will always be unique.... not the most DBA-like way to do it I know, but I just need to get them back up and running ASAP so I think this method will work OK. Once I've manually fixed the orphaned rows with my update statements I will then go through and turn off the allow nulls and recreate the Pk's, constraints and indexes they have managed to destroy.

Cheers,

Mike

Kristen
Test

22859 Posts

Posted - 2007-04-21 : 05:46:07

"the physical path and file name of the image which luckily will always be unique"

Well if you want to avoid the XLS export/import/"Join" my route should work using "physical path" as PKColumn1 and "file name" as PKColumn2

(I expect you realise that, but I mention it just in case it lost something in translation along the way!!)

Kristen

nzmike
Starting Member

21 Posts

Posted - 2007-04-21 : 20:34:51

Yep, good point - perhaps I will look at doing it the way you suggested then.

Thanks again for the help.

Mike

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources