This is a baffling situation:

I have a Transform Data Task in DTS (SQL 2000 SP2). The source for
the task calls a simple stored procedure with 2 parameters: @CID int
and @accesstablename varchar(50).

If I pass literals for both parameters...i.e.

exec ap_ds_getstagingdata 141, 'Points'

....the query runs fine. I can click Preview and the results appear.

However, if I use a global variable and parameterize the
statement...e.g.

exec ap_ds_getstagingdata ?, 'Points'

....I get a "No value given for one or more required parameters" error
upon clicking Preview, and if I advance to Transformations I get no
columns on the source side because the SP is using dynamic SQL.

The global variable ConversionID *is* defined, and has a default value
of 141.

Things I've tried to no avail:
1) Change the type of ConversionID from Integer to Int
2) Change the type of ConversionID from Integer to String
3) Reverse the order of the parameters in the SP, and likewise in the
call to the SP
4) Delete and retype the value of ConversionID, just in case there are
invalid characters hiding in there somewhere.
5) Completely delete and recreate the ConversionID global variable
6) Try a simple select statement instead of the SP. If I use "select
* from conversionlog where conversionid = ?", the result is the same
error when clicking on Preview, but the columns are at least visible
in the Transformations tab.

I'm stumped. Is this a known bug? Or can anyone see where I'm going
wrong here?

-Jonathan Van Matre

Mine seems to work

Stored proc is

CREATE PROCEDURE TwoPassInParams @Param1 int, @param2 varchar(50)
AS
SET NOCOUNT ON
GO

Statement is

exec TwoPassInParams ?, 'DTS'


What SP level are you using?

I am using

Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)


You can always set the parameter from code so actually pass a constant like
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.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Jonathan Van Matre" <jvanmatre (AT) builderhomesite (DOT) com> wrote

I'm on SP2--see above. The full version is

Microsoft SQL Server 2000 - 8.00.679 (Intel X86)
Aug 26 2002 15:09:48
Copyright (c) 1988-2000 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Unfortunately, there's not much chance of service-packing the server
right now, unless we can establish that this is a known bug fixed by
the SP. I dug around in the Microsoft knowledge base and didn't find
any relevant bug references.

I'll try your method, but I don't think I'm wrong in expecting it
should work the way I'm doing it.

-Jonathan Van Matre


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

No you are not wrong in assuming it should work the way you say because as
you can see it works for me.

Can you run my test proc and see if it works?

I seem to remember others have had problems like this but cannot recall the
way they worked around it. It may have been they used the method I
describe.



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
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


"Jonathan Van Matre" <jvanmatre (AT) builderhomesite (DOT) com> wrote

On 9 Apr 2004 08:09:02 -0700, jvanmatre (AT) builderhomesite (DOT) com (Jonathan
Van Matre) wrote:

I have worked at very conservative companies before (including a
nuclear engineering firm that was still on Windows 95 in 2000) so I
know how it can be difficult to convince people to upgrade. However,
I would strongly suggest you upgrade now. SP3a has been out for more
than 6 months. A lot of times there are fixes that are not listed.