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
[quoted text, click to view] <dhelsby@hotmail.com> wrote in message
news:1118790655.883596.246230@g43g2000cwa.googlegroups.com...
> 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
>
