Re: Using a parameter in a subquery
From: Chad Ehret (ChadEhret_at_discussions.microsoft.com)
Date: 07/15/04
- Next message: Allan Mitchell: "Re: Scheduled DTS package doesn't run"
- Previous message: René de Leeuw: "Scheduled DTS package doesn't run"
- In reply to: Darren Green: "Re: Using a parameter in a subquery"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 15 Jul 2004 06:19:01 -0700
Using the INNER JOIN works great! In fact, it's such a simple and obvious solution that I'm pretty much embarrassed that I didn't come up with it on my own. Thanks.
"Darren Green" wrote:
> In message <6F856C8E-4B8F-43D0-AC5C-39BC7B90BE50@microsoft.com>, Chad
> Ehret <Chad@Ehret.?.microsoft.com.invalid> writes
> >I'm working on DTS package to copy all data related to a particular
> >client from a production server (SQLServer 2000), to a development
> >server (also SQL Server 2000). I've set up the package on the
> >destination server. I'm trying to set up DTSDataPump tasks to copy
> >subsets of the client information to the development server using a
> >queries with a single parameter as the source. Where I run into
> >trouble is when I have to have that parameter as part of a
> >SUBQUERY...DTS apparently doesn't using parameters in subqueries...I
> >haven't been able to figure out a way around this yet.
> >
> >Sample SQL:
> >SELECT *
> >FROM tblClaimActions
> >WHERE ClaimID IN (SELECT ClaimID FROM tblClaims WHERE ClientID = ?)
> >
> >If I replace the "?" with a value (e.g. 153), it works fine for pulling
> >the Actions related to all claims for client 153.
> >
> >There are several tables I have to do this sort of thing for, so I'd
> >like to be able to set up a global variable, map the query parameter(s)
> >to that variable in each task. This will allow me to change the target
> >ClientID in one place (the global variable) and transfer the only the
> >data I'm after.
> >
>
> There are a couple of options that may work-
>
> Firstly you could re-write this particular query to use an inner join
> instead which may allow you to use the parameter place holder.
>
> Another option focuses on the fact that the design-time validation is
> stricter than the run-time, so you can fool the designer into setting up
> the parameter mapping on some simple SQL and then swap in the real code
> later. This may or may not work in this situation, but the technique is
> described in the later part of this article-
> Global Variables and Stored Procedure Parameters
> (http://www.sqldts.com/default.aspx?234)
>
> Finally you could just use the old method, of coding the parameter in
> the SQL statement, but maintain the entire statement dynamically. See-
> Global Variables and SQL statements in DTS
> (http://www.sqldts.com/default.aspx?205)
>
> --
> Darren Green (SQL Server MVP)
> DTS - http://www.sqldts.com
>
> PASS - the definitive, global community for SQL Server professionals
> http://www.sqlpass.org
>
>
- Next message: Allan Mitchell: "Re: Scheduled DTS package doesn't run"
- Previous message: René de Leeuw: "Scheduled DTS package doesn't run"
- In reply to: Darren Green: "Re: Using a parameter in a subquery"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
