February 19, 2009 at 3:47 am
Hi All,
I have recently upgrade one of my SQL server to 2008
When I run SQL 2000 DTS Package using Copy SQL Server Objects from SQL 2000 to SQL 2008, it prompt error:
Microsoft SQL-DMO (ODBC - SQLState: 42000)
[Microsoft][ODBC SQL Server Driver][SQL Server] To connect to this server you must use SQL Server Management Studio or SQL Server Management Objects (SMO)
Any idea to resolve this error?
Thank you in advance
February 19, 2009 at 1:16 pm
If you want to load data from a SQL 2000 into a SQL 2008 try using the SQL 2008 Import/Export tools instead. In just doing a quick test on my personal servers (both 2000 and 2008) using the 2000 DTS export did not work, but the 2008 Import/Export wizard (which is essentially the same thing) worked just fine.
February 20, 2009 at 8:46 am
Are you trying to copy DTS packages or copy data?
If you are copying DTS packages, IMHO the best option is to export and import using DTSBACKUP2000. This will keep the visual layout and comments seen in DTS Designer. If you use standard Microsoft processes to export and import DTS packages you will loose the layout and comment information.
In order to install DTSBACKUP2000 on SQL Server 2008, you must first install DTS Designer, available in the SQL 2008 Feature Pack. Read the install instructions carefully, as you must copy certain files after the DTS Designer install is complete.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 21, 2009 at 2:26 am
Hi All,
Thank you for all of your replies.
I am trying to execute the DTS in SQL 2000 contains Copy Server Objects which has destination server is SQL 2008 (formerly was SQL 2000)
When execute, it prompt the error as describe in the post
If the DTS 2000 is using Connection, it work fine to copy data to SQL 2008. When come to Copy Server Objects, it will prompt error
There are mulitple tables to be copied to SQL 2008, therefore using Copy Server Objects is chosen
Is there any alternative solution to remain the DTS in SQL 2000 which can copy mulitple tables to SQL 2008?
Thank you in advance
February 23, 2009 at 9:15 am
You'll either have to
1. create an SSIS package that contains a Transfer SQL Server Objects task (not available in the Wizard)
or
2. Script the table CREATE statements in SQL 2000, run it in SQL 2008 to create the tables, then use the Import/Export Wizard to copy data from the SQL 2000 tables to the SQL 2008 tables.
Greg
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply