jwcolby
jwcolby at colbyconsulting.com
Wed Oct 6 06:49:58 CDT 2010
Stuart, We created a dynamic SQL statement to try and do it all in one shot and it timed out after an hour (no big surprise there) but I decided to just break it down into 100 field chunks and do it that way. Each chunk took under an hour. Interestingly, SQL Server threw an error each time, it appears to be related to the length of the string though not certain about that. The SQL statement looks as follows and every time SQL threw an error about the very first case (IBS_IRRITABLE_BOWEL_SYNDROME_IN_HH in this case) however it appears that SQL Server did in fact process the SQL statement, if I go look for spaces in that set of fields I don't find any. UPDATE _DataHSID_UpdSpaces.dbo.tblHSID SET IBS_IRRITABLE_BOWEL_SYNDROME_IN_HH = case IBS_IRRITABLE_BOWEL_SYNDROME_IN_HH when ' ' then NULL else IBS_IRRITABLE_BOWEL_SYNDROME_IN_HH end, IMPOTENCE_ED_IN_HH = case IMPOTENCE_ED_IN_HH when ' ' then NULL else IMPOTENCE_ED_IN_HH end, MENOPAUSE_IN_HH = case MENOPAUSE_IN_HH when ' ' then NULL else MENOPAUSE_IN_HH end, etc. Because of the error being thrown (and apparently unhandled in my code) I am just now processing the last set of fields, however I should be finished in another hour. MUCH better than 10 days. ;) We use SMO everywhere in our programming these days, to get database, table and field names from SQL Server. We use it to back up and restore databases, attach and detach databases etc. Quite a powerful library in C# / .NET. John W. Colby www.ColbyConsulting.com On 10/5/2010 8:14 AM, Stuart McLachlan wrote: > Don't know, but my gut feeling is that a single pass has got to be way more efficient that 540 > of them. > > Why not try it on a subset of the records and see how long it takes. > >