• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 754
  • Last Modified:

Converting script to run in DTS.

I have a script works correctly when run in the Query Analyzer, but have problems converting it to run in DTS.  I understand that GO is not supported in DTS.  One posting suggested removing them, but that creates a new problem.
The script is listed below.  It selects data from tables on different instances of SQL server and the data is exported to Excel.

With to GO's removed I get an error 'Could not find 'ServerDocs' in sysservers.  Execute sp_addlinkedserver to add the server.  Any suggestion on how I can get this to run in DTS.


Exec sp_addlinkedserver
@server='ServerDocs',
@srvproduct='',
@provider='MSDASQL',
@provstr='DRIVER={SQL Server};SERVER=xx.x.xx.xx;UID=sa;Initial Catalog=X1SSS'
GO

Exec sp_addlinkedsrvlogin
@rmtsrvname='ServerDocs',
@rmtuser='xxxx',
@rmtpassword='xxxxx'
GO

SELECT
Location,
PO_Num AS "P/O",

AgeGroup=
    Case
      when DATEDIFF(day, indexdb.dbo.FROM_UNIXTIME(F_CreateTime), getdate()) >= 0
      and DATEDIFF(day, indexdb.dbo.FROM_UNIXTIME(F_CreateTime), getdate()) <= 30
          then '0 - 30'

      when DATEDIFF(day, indexdb.dbo.FROM_UNIXTIME(F_CreateTime), getdate()) >= 30
      and DATEDIFF(day, indexdb.dbo.FROM_UNIXTIME(F_CreateTime), getdate()) <= 60
          then '30 - 60'

      when DATEDIFF(day, indexdb.dbo.FROM_UNIXTIME(F_CreateTime), getdate()) >= 60
      and DATEDIFF(day, indexdb.dbo.FROM_UNIXTIME(F_CreateTime), getdate()) <= 90
          then '60 - 90'

      when DATEDIFF(day, indexdb.dbo.FROM_UNIXTIME(F_CreateTime), getdate()) >= 90
      and DATEDIFF(day, indexdb.dbo.FROM_UNIXTIME(F_CreateTime), getdate()) <= 120
          then '90 - 120'

      when DATEDIFF(day, indexdb.dbo.FROM_UNIXTIME(F_CreateTime), getdate()) >= 120
          then '120 + '
    end,

DATEDIFF(day, indexdb.dbo.FROM_UNIXTIME(F_CreateTime), getdate()) AS DaysOld,

Vendor_Name AS Vendor,
Invoice_Num AS Invoice,

isNULL(cast(E_INA55 as varchar(50)), '') AS "Inv. Date",      
isNULL(cast(E_INA56 as varchar(50)), '') AS "Inv. Amount",      
isNULL(cast(E_NAME as varchar(50)), '') AS FileNetID    

FROM [indexdb].[f_sw].[XWQueue1_234]
LEFT OUTER JOIN [ServerDocs].[X1SSS].[dbo].[ITEM]
ON Invoice_Num = E_INA55 AND PO_Num = E_INA56
ORDER BY Location, PO_Num, DaysOld DESC
GO

Exec sp_droplinkedsrvlogin [ServerDocs], 'xxxx'
GO

Exec sp_dropserver [ServerDocs]
GO
0
dc900
Asked:
dc900
1 Solution
 
ewahnerCommented:
remove the linkedserver stuff and replace your LEFT OUTER JOIN with this:

LEFT OUTER JOIN OPENROWSET('SQLOLEDB','ip address';'username';'password','SELECT * from X1SSS.dbo.ITEM') as some_alias

Who needs the stinkin DTS... :-)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now