Re: Referencing a Stored Proc on Linked Server

#1

OK

Are you using SQL Server 2000?

If yes then read the value from your staging table into a global variable
using the ExecuteSQL task
You can then reuse this in your SourceSQLStatement for the datapump task

SELECT * from dbo.Source WHERE Date_Added > ? or Date_Modified > ?

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"HaveMercy" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Quote:

Disclaimer - I'm a relative DTS newbie, so be gentle

Scenario - I am sourcing data from a SQL DB on one server into a staging
database in SQL Server on another physical server. For my incremental

updates I am pulling only updated and new records since the last successful
update. My LastSuccessfulUpdate times are stored in an audit table in the
staging database. I have a stored procedure for the LastSuccessfulUpdate
lookup. The stored proc is defined on the staging server as well as the DTS
package. My DTS package links the source and target connections using a
transform data task.

Quote:

I am trying to use the following simple script to select the new and
updated records. This code is in the Transform Data task in the SQL Query

pane of the Source tab.

Quote:

--------------------------------------
DECLARE @UpdateDate DATETIME
EXECUTE @UpdateDate = update_lookup 'TableName', 'DBName'

SELECT dbo.SourceTable.*
FROM dbo.SourceTable
WHERE
(Date_Added > @UpdateDate) OR (Date_Modified > @UpdateDate)
--------------------------------------

Problems/Questions:
1. The stored proc is not recognized because it is on the target server
and this code is relative to the source server. Is there a way to specify

the complete path to a stored proc (including the physical server)?

Quote:

2. I tried defining a source connection using the linked server that I
have defined on the target database. However, the connection settings only

seem to recognize actual SQL Server names, not linked servers. Is there any
way to do this (and if so, would it solve my problem)?

Quote:

3. I tried using the Lookup tab in the transform data task to read the
UpdateDate into a global var, and then reference that global var in the sql

query on the Source tab. How does one reference a global variable in a SQL
query? Is this a feasible approach?

Quote:

I suspect that what I am trying to do is pretty standard stuff. What is
the best way to implement this using DTS since there are 3 tables involved

and 1 is on a different SQLserver and physical server than the other 2?

Quote:

Thanks for any tips or ideas.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Referencing a Stored Proc on Linked Server

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: Referencing a Stored Proc on Linked Server - 03-06-2004 , 06:14 PM