GLOBAL VARIABLES AND DTS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

GLOBAL VARIABLES AND DTS

Author

Topic

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-08 : 16:18:56

I have defined two global variables for a dts package.
They are @start and @end

The transformation allows me to assign global variables to parameter1 and parameter2 when I use a call to a stored procedure as the source.

The call looks like such

{CALL dbo.MyStoredProc (?,?)}

However when I try to see a preview it says:
"No value given for one or more parameters"

I made sure that I assigned values to the global variables under the package properties.

As anyone run into this problem before?

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-10-08 : 16:44:13

When you set up the transformation did you click on the 'Parameters'
button in the transformation? This will give you a Parameter Mapping
screen where you can assign your global variables to the parameters.

SKIBUM
Starting Member

32 Posts

Posted - 2002-10-08 : 16:48:50

On the Data Transformation Source Tab that displays the call above, there should be a button called 'Parameters' to assign values to the Global Variables to the parameters. I've done this before and it works fine.

1fred
Posting Yak Master

158 Posts

Posted - 2002-10-08 : 16:49:56

Try not to name them with @, never seen that in any books so maybe there is a reason....? This is the only thing I could see, be sure to look under parameters in your sql task, and verify that there is your 2 variables there.

Else why don't you just create a SQL task and type:
exec dbo.MyStoredProc ?,?
This has better chance to work!

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-08 : 17:01:45

Tiger:
I did click on parameters and set them up in the transformation.
I assigned start to parameter1 and end to parameter2

SKIBUM:
I did set up Global Variables under the tab you mention

1fred:
I tried removing the @ from start and end and rewriting the transformation source to :
exec dbo.MyStoredProc ?,?

Still gives me the same error message.

MyStoredProc expects both parameters as DateTime

I have the global variables setup as follows:

Name Type Value
StartDate Date 10/6/2002
EndDate Date 10/12/2002

I have the parameters setup as follows:

Input Global Variables Parameters
StartDate Parameter1
EndDate Parameter2

Edited by - ValterBorges on 10/08/2002 17:08:34

1fred
Posting Yak Master

158 Posts

Posted - 2002-10-09 : 10:42:31

should the date value be between ''
'10/6/2002'
'10/12/2002'

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-09 : 11:59:52

Yeah.
Thats Sunday to Saturday.

It works for Execute Task But Not For Transformation Task.

I'll have to massage the data in an execute task and then use a
transform task to ship it out.

Edited by - ValterBorges on 10/09/2002 13:57:25

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources