SQL Server Performance Forum – Threads Archive

Update issue with DTS

Background:
We have created a nice way of dynamically connecting to all the appropriate sites from a single location, using a stored procedure that calls a DTS package. This stored procedure reads from a table holding all connection details for each site and sets up all appropriate global variable values used for connecting to the desired database and site. Select statements run against the target sites in this manner work. Question:
We would like to create a SQL 2000 DTS package that carries out inserts and/or updates a target SQL 6.5 databse site. The rows to be inserted and those to be updated need to be determined. Problem Encountered:
The problem we have is that all target sites are SQL 6.5 and there is a linked server issue between SQL 2000 and SQL 6.5 where the 4 part reference (i.e: server.database.user.table) exceeds 28 characters. We cannot change the names of the target sites server, database or table. Help?
Does anyone know how we can go about this?
Any help would be greatly appreciated. Regards,
Rik

---

Instead of taking DTS help why not create SQL scripts and run on target 6.5 servers using ISQL or OSQL. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

DTS appears to be only usefull for inserting and appending data. As a result, we have resulted to writing an application to pass the information requests to the desired sites. Regards,
Rik

---

So what was the error in working with LS and DTS setup? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

]]>