I've been experiencing a problem using the Transform Data Task to call
a parameterized stored procedure and output the results to a file.

I've searched the groups already but haven't found a solution. The
following has occurred on two separate SQL 2000 servers (at work -
where my real problem is - and on my home development machine).

I began on a Win XP Prof./SQL 2000 Developer Ed.(sp3) machine. It was
a brand new installation of SQL Server and @@version returned:

Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

In the Northwind database I created the following procedure (the
procedure itself is of no consequence, but I wanted something simple to
demonstrate):

create procedure procTemp
@tempParam varchar(10)
as
begin
select * from products
end

I then created a new DTS package and created a global string variable
called "testval" and set it's default value to "Hello" (without the
quotes).

I created a connection to the database (Microsoft OLEDB Provider for
SQL Server) and a Text file destination. I linked the two with a
Transform Data Task. In the Transform Data Task properties I defined a
SQL query of:
exec procTemp ?

I then clicked the parameters button and mapped "testval" to
Parameter1. Upon clicking "Preview" I received the following error:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: No value given for one or more required parameters

In case it was a service pack problem I installed the sp4 (result of
@@version below), but this hasn't made any difference:

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

I also tried replacing the "?" in the SQL query with a constant value
'Hello' and it worked fine.

Has anybody experienced this problem before and managed to find a way
around it, or am I doing something wrong?

Thanks in advance,

Darren

What happens when you run it?

The ? cannot be evaluated at design time because the design time expereince
is not as strong as runtime.

I have just recreated your proc and scenario and the proc works as expected
at runtime.



--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


<dhelsby (AT) hotmail (DOT) com> wrote

ah, I see what you mean. I have just created the Transform Data Task
again but without pressing "Preview" and when I moved to the
"Destination" tab it populated all the columns correctly. The DTS then
ran correctly.

I'll give it a go at work and hopefully it'll work there too!

Thanks for your help Allan.