How to update a user variable in Dynamic SQL in Oledb Source Components

I created 2 variables

1) @[user::var] of type int,initialize with 0 and updated at runtime

2) @[user:sSQL] of type string and value is Select column1 from table1 where column1 > @[user::var] and I set the property EvaluateAsExpression to true

I am using @[user::sSQL] in an Oledb source component,when I run my package I validate that @[user::var] gets updated but the updated value does not pass through to the @[user::sSQL] rather @[user::sSQL] still has 0 instead of the updated value.

How do I make the dynamic sql to use the updated @[user::var]?

I will greatly appreciate a quick response.



more ▼

asked Apr 14, 2010 at 12:40 PM in Default

Ed Donald gravatar image

Ed Donald
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort oldest

Hi Ed,

When you have the variable and the expression, have you validated the expression in the expression builder?

Also, could you include the exact text that is in your variable? In my experience there should be double qoutes around the text and the variable should be outside those quotes, like this;

"Select column1 from table1 where column1 > " + @[user::var]

Also, make sure that the variable user::var is in scope

more ▼

answered Apr 15, 2010 at 01:30 AM

Daniel Ross gravatar image

Daniel Ross
2.9k 11 13 14

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 14, 2010 at 12:40 PM

Seen: 2252 times

Last Updated: Apr 14, 2010 at 12:40 PM