Create SQL Stored Procedure to perform DTS Function Lookup

We are currently running a DTS job nightly that backs up a table from an IBM Universe DB to a local SQL DB to be queried (via a vbscript file) throughout the day.  The source table is very large and we have no control over the database.  Our DTS job is starting to fail, and the error states that it can't create a temp table on the Universe DB.

I would ideally like to create a simple Stored Procedure that does the lookup to the source Universe Database ad hoc instead of requiring a nightly backup.  This way I can just run an ADODB Query from VBScript that calls a SQL Stored Procedure.  The SP then does the DTS lookup into the source table and returns the ResultValue.  Our Lookup Value is an identity field, so I will always be returning a single result back.

The actual lookup is very simple:
select ResultValue from TransactionLog where LookupValue = '12345'

What is the best way to accomplish this?  I don't have an ODBC connection that allows me to connect to the source database (like D3 Raining Data), but I am open to that if I can find a way to do it.

I'm very comfortable with writing SQL Queries, but I've never written a DTS job (except this one created with the GUI).

Thank you.

Who is Participating?
Racim BOUDJAKDJIConnect With a Mentor Database Architect - Dba - Data ScientistCommented:
<<What is the best way to accomplish this?>>
From what you describe there is no need for DTS...Simply create a liked server pointing out directly at the IBM table and call the select the same way you would with a normal table except that the syntax indicates the full schema path instead of only the table name...Ex: assuming your linked server is called IBMDB2
select ResultValue from TransactionLog where LookupValue = '12345', just call
select ResultValue from [IBMDB2].[SchemaName].[owner. TransactionLog where LookupValue = '12345'

Hope this helps...
All Courses

From novice to tech pro — start learning today.