Parameters problem -- sql server dts

CBarry
11/30/2004 8:17:01 PM

I'm trying to create a transform data task using the following query:
select * from Code_Authorizations_phi where Authorization_pkey in (Select
pkey from Code_Authorizations where dir_pkey = ?)

When I click the 'parameters' button or 'Parse Query' button I get the
following error:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: Parameter Information cannot be derived from SQL
Statement with Sub-select Queries. Set parameter information before preparing
command.

Is there anyway that I can use a parameter as part of this sub-query?

Ed
11/30/2004 10:05:02 PM

I think the better way of doing it is to create a stored procedure
You may create a stored procedure and pass the paramemter and return the
result set like:

Create procedure ProcedureName(@CustomerID nvarchar(50)
as
Select * from customers where CustomerID in (Select customerid from Orders
where customerid = @CustomerID)

Then in the Execute SQL Task
Exec ProcedureName ?

Ed

[quoted text, click to view]

Allan Mitchell
12/1/2004 8:35:44 PM

You could of course use a Stored proc or function to do this but you can
also do this

Global Variables and SQL statements in DTS
(http://www.sqldts.com/default.aspx?205)

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server dts

sql server dts : Parameters problem