I need to export data from SQL server 2000 to SQL server 2005. These server are running on different graphical locations. The exdate needs to be done on a daily basis. The data to be exported will contain following objects:
3. Stored Procs.
Following technologies didnt work for me:
1. Replcation- This didnt work as it locks the table on SQL server 2000. This database requires a lot of DML procedures running. In addition new tables and objects are constantly added. Replication would not accomodate any of this.
2. Log Shipping: I understand that log shipping is not possible from SQL server 2000 to SQL server 2005. This was then confimed on the blog of SQL Server team.
3. DTS: I cannot run a DTS procedure from SQL server 2000 to SQL Server 2005. The error I get is "unable connect to this server you must use sql server management studio or sql server management objects". I installed SSMO on SQL server 2000 but it didnt succeed at all.
I welcome your suggestions and probably solutions to my problem.