Web Wiz Support and Community Forums : Upgrade script from Access to MSSQL

Upgrade script from Access to MSSQL : If you get this script working,...

Tue, 01 Aug 2006 16:36:04 +0000

Author: balearicjobs
Subject: 20848
Posted: 01 August 2006 at 4:36pm

If you get this script working, would you please be kind enough to post it up. I've tried several times to upgrade my forum from access to SQL but without any luck.

Thank you.

Upgrade script from Access to MSSQL : You could try using an SQL ALTER...

Mon, 31 Jul 2006 11:00:15 +0000

Author: WebWiz-Bruce
Subject: 20848
Posted: 31 July 2006 at 11:00am

You could try using an SQL ALTER query to alter the table so that the IDENT fields are set to an INT datatype during the transfer process, then Alter the tables back to IDENT data type after the process is complete.

Never tried this, but in theory I think it should work.

Upgrade script from Access to MSSQL : Hi Skeever. Thanks for the reply. I...

Sat, 29 Jul 2006 22:08:01 +0000

Author: Bj�rn Holm
Subject: 20848
Posted: 29 July 2006 at 10:08pm

Hi Skeever. Thanks for the reply.

I don't really need any better performance, as this is a one time job only, and this simple script works fine except for the mentioned problem.

Doesn't anyone know how to overwrite the autoincremented value from the IDENT column in a MSSQL table?

Upgrade script from Access to MSSQL : Why don´t you read it into a GetRows...

Fri, 28 Jul 2006 18:34:30 +0000

Author: Skeever
Subject: 20848
Posted: 28 July 2006 at 6:34pm

Why don�t you read it into a GetRows array and then create a simple stored procedure to take care of the insert part using SET IDENTITY_INSERT ON

See here for more info on the identity part. (http://www.sqlteam.com/item.asp?ItemID=8003)

That might give you some better performance.

Upgrade script from Access to MSSQL : Not sure if this is excactly the...

Fri, 28 Jul 2006 14:15:58 +0000

Author: Bj�rn Holm
Subject: 20848
Posted: 28 July 2006 at 2:15pm

Not sure if this is excactly the right place to post this question, but it is somewhat relevant to this forum I guess;

I have made a .asp-script that imports all data from WWF-v.8 access tables to MS-SQL WWF-v.8 tables. But I am not able to import the correct ID(used for primary key) for all tables (eg. author_id from the tblAuthor table). MSSQL refuses to let me use the imported value, and insists on priming a new value for these fields. This means that all relations between tables goes out the window.

Anyone knows how to overwrite the Ident column of the MSSQL-tables?

Heres an exampe of my code:

Do While Not NewsTableInn.EOF
  NewsTableUt.AddNew
  'NewstableUt.Fields("Cat_ID")               = NewstableInn.Fields("Cat_ID")   <----- This line causes error
  NewstableUt.Fields("Cat_Name")                = NewstableInn.Fields("Cat_Name")
  NewstableUt.Fields("Cat_Order")                 = NewstableInn.Fields("Cat_Order")
  NewsTableUt.Update
  NewsTableInn.MoveNext
Loop

If I get this to work, I can post the final script here if someone is interrested.

regards,

Bj�rn Holm