You could do that kind of formatting at the front end.

We tried that option. With 250 mi rows, it bombed.

We do have lot of data. around 6-8 million rows in the tables that need to be altered.

I tried the new column-copy-drop-add-update-drop approach. It didnt work. Apparently the insert scripts...

Are you doing the data entry through the table directly or using some front end tool?

you can either put all the inserts in one transaction and check for errors after each T-SQL statement and return if there's an error or use a transaction for each.

Something...

Add the column using an ALTER TABLE syntax and set the IDENTITY property.

You could do it. But I wouldnt recommend it.

You would need to use Dynamic SQL and use sp_ExecuteSQL to execute the string.

Something along these lines:

DECLARE @sql nvarchar(500)

SET @sql = 'SELECT...

Its a good practice to SET NOCOUNT ON at the beginning of the stored proc and set it to OFF at the end so the warnings/messages can be suppressed.

Another thing you can do is, instead of binding the dataset to the datagrid, try binding a datareader.

....

dr = sqlcommand.executereader()

datagrid.datasource = dr

....

Or Perhaps you can also set it to dataset.tables(0)

Fix the stored proc to return only one record set. Check for all the unnecessary SELECT statements in the stored proc.

you need to do it in the front end application itselg.

The article is basic and good.

Regarding the VB.NET code posted, I would recommend opening the connection as late as possible and close as early as you can, than have...

I have had 2000 and 2005 on the same machine and no issues.

But in your case looks like you want to put 2005 on a prod 2000 machine? I...

CHARINDEX/PATINDEX can give you the position of a search string. check out BOL for more info.

you would need to do it in 2 steps. You cant SELECT and UPDATE at the same time.