SQL 2000: How do I put a global variable into a SQL Task statement?

Question

Post reply

SQL 2000: How do I put a global variable into a SQL Task statement?

d_george

SSCrazy

Points: 2462
More actions
April 20, 2007 at 2:54 pm

#68365

Hi
Inside my old school DTS...
I have a Connection to DB1.
I have an "Execute SQL Task" the statement is Execute sp_Bob @FName = 'Bob'
I have an "Dynamic Properties Task" in which I have created a global variable, gvFName with a value of "Bob"
I want the EST Statemtent to use the value of gvFName (obviously).
In the DPT I have played with with everything but no luck.
Can someone please, for the love of God, tell me what I need to do to get this to work.
Possibly some combination of manipluating the DPT and Statement itself.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 1

in the execute sql task, use sp_Bob @Fname = ?

may need to name the parameter if that isn't your only global variable.

I think the syntax would be sp_Bob @FName = ?gvFname

you will want to double check that second statement though, it's been a while since I used that syntax

d_george SSCrazy Points: 2462 More actions · Answer 2

Hi

Right on man, thanks a ton.

The first statement worked, the second did not.

"sp_Bob @FName = ?" works

"sp_Bob @FName = ?gvFName" does not work

Now I remember how happy I was to go to SSIS.

Out of curiosity, does anyone know what to do if you have more than 1 param?

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 3

More parameters means more ?'s. For example, if sp_Bob had two input parameters you would write:

sp_Bob ?, ?

Then you can map the parameters to GlobalVariables in the same way. In this case they would be listed as Parameter 1 and, unsurprisingly, Parameter 2.

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

d_george SSCrazy Points: 2462 More actions · Answer 4

How does it know what order gvOne and gvTWO should be placed in in the statement?

sp_Bob ? (gvTWO),? (gvOne)

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 5

It works on the principle of first in, first out. When you click on the Parameters button and assign the variables, the first ? will map to Parameter 1, the second to 2 and so on...

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

d_george SSCrazy Points: 2462 More actions · Answer 6

Paramters button?

FIFO - that's weak but expected.

Thanks

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 7

The Parameters button is one of the available buttons in the Execute Sql Task Properties dialog. Here you can map both input and output parameters.

Check out BOL for a (maybe) better description of the Execute sql task under Contents > Data Transformation Services > DTS Package Elements > DTS Tasks > Tasks that Copy and Manage Data.

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

d_george SSCrazy Points: 2462 More actions · Answer 8

d_george

SSCrazy

Points: 2462

April 23, 2007 at 6:17 am

#701909

Thanks again.

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 9

No probs. It just struck me, there's a good explanation of parameters at http://www.sqldts.com/234.aspx, a site that has been (and continues to be) very useful.

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]