Hi,
Thanks for taking the time to read this.

I am trying to set a global variable equal to a rs. I loop through the
values and insert a comma, so my global variable value looks like
(#####,#####),
However, when it's fed to the source sql, it doesn't work. Now if I only
have a single value in my variable it works just great.

Can I not do this?
Thanks for any thought's.
BCL

So you build your statement right?
Like what does the resultant statement look?

Follow this article

Global Variables and SQL statements in DTS
(http://www.sqldts.com/default.aspx?205



"BCL" <BCL (AT) discussions (DOT) microsoft.com> wrote

So you build your statement right? yes
Like what does the resultant statement look?
Select * where field in ?

My GV = 12345,23456
"Allan Mitchell" wrote:

As a guide, if you cannot use a local variable in SQL, then it will not work
with global variables in DTS, so this
would fail-

DECLARE @Fred varchar(100)
SET @fred = 'ddd,ggg'
SELECT * FROM table WHERE field IN @Fred

To solve this in SQL you would dynamic SQL. The link Alan posted is the
equivalent in DTS

"BCL" <BCL (AT) discussions (DOT) microsoft.com> wrote

Hummm......
I'm thinking I'm just not getting this, or I'm not explaining very clearly.
Just a side note, when I do run the dts, if I have only a single value it
does work.

Ok from the begining....
1) I connect to a local db and populate a gv with 12345,23456
2) next i connect to an external db and grab data using my gv in the where
clause.
select * from externaldb where field in (?)
3) Hope my data is in the local db.

Am I not explaining clearly ?
Why would it work if my gv = 12345 but not when it = 12345,23456 ?

Thanks for any thought's.
I went to the link, read the samples, but am still confused as to why it
works sometimes, but not others?


"Darren Green" wrote:

I don't think you can do this the way you are trying.

To test, I defined a global parameter (type of string to allow comma) with
value 1,2 Now if you look at SQL Profiler when running the package you
will see:

exec sp_executesql N'select * from jobs where job_id in (@P1)', N'@P1
smallint', 12

The 1,2 value was converted to a number, in this case 12.
Even when working with character comparisons globalparam = 'White,Green'
you can see that this doesn't work because it needs quotes around the
individual fields.

exec sp_executesql N'select * from authors where au_lname in (@P1)', N'@P1
varchar(40)', 'White,Green'

You could write an sp to parse the data into a table, one value per row,
then change your select statement to join with that table.

--
Message posted via http://www.sqlmonster.com