Web Wiz Forums - Upgrade script from Access to MSSQL

Print Page | Close Window

Upgrade script from Access to MSSQL

Printed From: Web Wiz Forums
Category: Web Wiz Web App Support Forums
Forum Name: Web Wiz Forums
Forum Description: Support forum for Web Wiz Forums application.
URL: https://forums.webwiz.net/forum_posts.asp?TID=20848
Printed Date: 11 April 2026 at 10:08am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Upgrade script from Access to MSSQL

Posted By: Bj�rn Holm
Subject: Upgrade script from Access to MSSQL
Date 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

Replies:

Posted By: Skeever
Date 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.

Posted By: Bj�rn Holm
Date 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?

Posted By: WebWiz-Bruce
Date 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.

-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting

Posted By: balearicjobs
Date 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.

-------------
Thanks for your help!!!
Kind Regards
Rob,
http://www.balearic-jobs.com" rel="nofollow - Balearic Jobs
Helping young people find seasonal work in the Balearic Islands, including vacancie