June 29, 2009 at 1:29 pm
Is there anyway to "downgrade" and transfer a SQL Server 2005 database to SQL Server 2000? According to my requirements, the source database (SQL Server 2005) and the target database (SQL Server 2000) need to be synchronized at least daily. Since the data contains private and sensitive information, the data transfer must be secure and encrypted. Both servers reside on different networks behind multiple firewalls.
I tried using the SQL Server database publishing wizard (http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en ), but it failed because some of the source database tables have a varchar(max) column defined. When I try to run the SQL Server 2005 output script in SQL Server 2000, it fails because varchar(max) does not exist.
Depending on the solution, I may be able to use another developer's program that will use SFTP and PGP to securely transfer data files.
June 29, 2009 at 1:43 pm
If u want to transfer data from 2005 to 2000, u will have to use SSIS or Sql Server import and export Wizard(Rt. Click on a database, Tasks -> import data).
June 29, 2009 at 1:47 pm
u can use Cast or Convert from Varchar(max) to Text data type
June 29, 2009 at 1:54 pm
Mayank Khatri (6/29/2009)
If u want to transfer data from 2005 to 2000, u will have to use SSIS or Sql Server import and export Wizard(Rt. Click on a database, Tasks -> import data).
I'm looking for an automated way to transfer data between the two servers. Unless I'm mistaken, using Sql Server import and export wizard will not accomplish this because this is a manual process.
If I use SSIS, could you elaborate on the details to accomplish this? I haven't used SSIS before except through the SQL Server import and export wizard.
June 29, 2009 at 1:57 pm
Mayank Khatri (6/29/2009)
u can use Cast or Convert from Varchar(max) to Text data type
Unfortuantely, doing that will make the solution "manual" instead of automated.
Actually, I was mistaken about my comments regarding the database publishing wizard. The wizard will not run (i.e., create a script) when the "SQL Server 2000" option is set. Creating a "SQL Server 2005" script and running it on SQL Server 2000 will fail for many other reasons besides the varchar(max) problem. The "SQL Server 2000" and "SQL Server 2005" scripts are not equivalent at all.
June 29, 2009 at 2:18 pm
Write an SSIS package, maybe using the transfer database task, or maybe broken down with transfer objects and data flows, then schedule that package daily (using SQL agent)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 29, 2009 at 2:20 pm
jlp3630 (6/29/2009)
Mayank Khatri (6/29/2009)
If u want to transfer data from 2005 to 2000, u will have to use SSIS or Sql Server import and export Wizard(Rt. Click on a database, Tasks -> import data).I'm looking for an automated way to transfer data between the two servers. Unless I'm mistaken, using Sql Server import and export wizard will not accomplish this because this is a manual process.
If I use SSIS, could you elaborate on the details to accomplish this? I haven't used SSIS before except through the SQL Server import and export wizard.
You can automate Import Export wizard by scheduling it to run as a job. In last step of running your wizard, it will ask u if u want to save those job steps as SSIS file or import/export wizard. Then u can use ur job scheduler to pick ur SSIS file to be run as a job.
June 29, 2009 at 2:26 pm
Mayank Khatri (6/29/2009)
jlp3630 (6/29/2009)
Mayank Khatri (6/29/2009)
If u want to transfer data from 2005 to 2000, u will have to use SSIS or Sql Server import and export Wizard(Rt. Click on a database, Tasks -> import data).I'm looking for an automated way to transfer data between the two servers. Unless I'm mistaken, using Sql Server import and export wizard will not accomplish this because this is a manual process.
If I use SSIS, could you elaborate on the details to accomplish this? I haven't used SSIS before except through the SQL Server import and export wizard.
You can automate Import Export wizard by scheduling it to run as a job. In last step of running your wizard, it will ask u if u want to save those job steps as SSIS file or import/export wizard. Then u can use ur job scheduler to pick ur SSIS file to be run as a job.
Do you have any recommendations on the output format? "Through the grapevine", I've been told that the import/export wizard has problems with flat files and Excel files. However, I'm never encountered that situation personally. Additionally, I am unable to create a "direct" connection between the source and target database due to firewall issues.
June 29, 2009 at 2:33 pm
For the output format, all I can say is u have to maintain a standard and strictly implement it. Yes, there are problems in file or excel transfer, but that problem comes in if someone changes the format of data(Data type or length or adds another column) and then u have to fix ur package to map source and destination again because metadata or ur package changes.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply