Wednesday, March 21, 2012

from SQL 2005 to SQL 2000


Is there an easy way to import table data from SQL 2005 to SQL 2000?
Here's the story:
I want to use SQL 2005 b/c I like varchar(max) over TEXT and several other features. However there is another developer with a SQL 2000 DB who needs to be able to pull data from my tables. If I use something like varchar(max), xml, etc what would the other developer need to do to import my data?
I know that going in the reverse direction is possible, pushing data from a SQL 2005 DB to a SQL 2000 ( http://msdn2.microsoft.com/en-us/library/ms191212.aspx )
I'm mostly a programmer slowly turning into a dba so please excuse me if I'm missing something obvious.
Thanks.
--Alex

Alex,

If you want to transfer data from SQL 2005 database into SQL 2000 database BCP as you pointed out is the only option. You can use bcp from SQL 2000 installation and it will see datatypes that are compatible with SQL 2000. varchar(max) and xml I believe will be seen as text.

If you want to access data from SQL 2005 instance on SQL 2000 instance (without moving the data between instances) you can do so with linked servers with the same restriction as above (new datatypes will be seen as old ones on SQL 2000).

Regards,
Boris.

No comments:

Post a Comment