Importing a store's data from SQL 2005 to SQL 2000?

[DFresh]

Hello All,

I've been using a development store on an SQL 2005 database. We've managed to import historical data in from our previous eCommerce solution. We would like to use the hosting at AbleCommerce to host our store. We would like to export the data from the development server (orders, users, products, etc.) into the hosted solution using the Dataport utility. I'm told that this wont work since we'll be exporting data from a 2005 database and importing to a 2000 database. Has anyone ran into this problem. Is there a workaround? I'd hate to reinvent the wheel twice on this and attempt to import the historical data again (to a 2000 DB). I have all the original order data in a .csv format. I see that the Dataport handles CSVs but does not import line items on orders. However, the complete store upload (in XML) does. Any thoughts?

Thanks in advance!
-Doug

[jmestep]

Is the SQL2005 database running in SQL2000 compatibility mode? You could try that.
If I were in your shoes, I would just test the import and see if it works. Backup the database, import, if things are wrong then restore the old database backup.

[Logan Rhodehamel]

My initial reaction - the dataport utility is the best bet. Could it be used to export the entire store from the 2005 database, then re-import to a 2000 database? (I have not worked on the dataport project so I can't answer, but someone else might chime in.)

I toyed with some conversion utilities yesterday, but I keep running into trouble where we have used 2005 specific field features like nvarchar(max). The converters are not sure how to downgrade this. Going down that road might run into more obstacles, and I'd only consider it if the dataport doesn't do the job.

[jmestep]

You might want to try MS Database Publishing wizard. I had to use it to get a backup of a store since the shared hoster didn't allow downloads of backups. You can script your development database, then publish it to a shared hoster. If you are going to use Able to host, they don't allow direct database access for security reasons, but they should run the script for you- I have had them run scripts in the past to add fields to a database table.
The wizard has an option to select the type of target database to script for.
Download link is here:
http://www.microsoft.com/downloads/deta ... laylang=en

[Logan Rhodehamel]

You might want to try MS Database Publishing wizard.

This is one of the utilities I tried. Because we have used nvarchar(max) fields in the 2005 schema, the publishing wizard doesn't know how to downgrade this for SQL 2000. It may also have trouble with some of the cascading delete rules we have in place that in 2000 would have been handled with triggers.

I haven't invested further research because I am hopeful the dataport utility would work for this purpose.