SQL Server Performance

Update issue with DTS

Discussion in 'SQL Server DTS-Related Questions' started by Richard Beacroft, Dec 4, 2003.

  1. Richard Beacroft New Member

    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
  2. satya Moderator

    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
  3. Richard Beacroft New Member

    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
  4. satya Moderator

    So what was the error in working with LS and DTS setup?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page