| Author |
Topic |
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
 Posted - 2008-10-13 : 06:03:45
|
| Hi pals,I have dynamic sql for which i am using the OLEDB src connection Access Mode as "SqL command" and i am trying to map the parameter wherever iam using the ? (i.e. am trying to map the Global Variables)"Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable"For this reason, i have declared one more global varaiable "SrcSQL" and modified 2 properties of the variable "EvaluateAsExpression : True and in the Expression property i am building the SELECT query"But even then , i have ended up with the below error."may be truncated if it exceeds the maximum length of 4000 characters.The expression could have a result value that exceeds the maximum size of a DT_WSTR.A truncation occurred during evaluation of the expression. "Is there any workaround or any alternative solution for this, because we may build the sql's sizing more than 4000 characters.This is urgent.Can anyone suggest me on this regard.Thank You. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-13 : 06:18:31
|
| did you try using (DT_STR) |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-13 : 06:21:33
|
| another way is to put the logic inside a UDF with parameters for taking the variable value and then make sql string like'SELECT fields... FROM YourUDF WHERE field =' + [@User::Parameter] |
|
|
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
Posted - 2008-10-13 : 08:58:29
|
| How can we convert DT_WSTR TO DT_STR Datatype?I am using a global varible say (SQLQuery)Go to Properties of the Variable. EvaluateAsExpression Property : True and set the dynamic sql inside the Expression : "SELECT .................."and if am buiding a query having length more than 4000 characters, am getting the Error DT_WSTR cannot exceed 4000 characters.How do we convert this long string into DT_STR? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-13 : 09:39:31
|
quote:
Originally posted by frank.svs
How can we convert DT_WSTR TO DT_STR Datatype?I am using a global varible say (SQLQuery)Go to Properties of the Variable. EvaluateAsExpression Property : True and set the dynamic sql inside the Expression : "SELECT .................."and if am buiding a query having length more than 4000 characters, am getting the Error DT_WSTR cannot exceed 4000 characters.How do we convert this long string into DT_STR?
did you try my second method. i think its much better as it does not have any restrictions of length |
|
|
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
Posted - 2008-10-14 : 05:51:34
|
| Yes!I have implemented the logic inside a stored procedure.Do we have any other alternative solution for this?Can i know the reason behind why this restriction has been enforced(4000 chars)?Thank You! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-14 : 08:41:37
|
quote:
Originally posted by frank.svs
Yes!I have implemented the logic inside a stored procedure.Do we have any other alternative solution for this?Can i know the reason behind why this restriction has been enforced(4000 chars)?Thank You!
i told you to use udf not procedure since it cant take metadata value only from udf (column info). if using procedure, you may need to add this info yourselves which is a bit of a pain. |
|
|
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
Posted - 2008-10-20 : 03:03:01
|
| Thank You so much! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-20 : 04:26:28
|
quote:
Originally posted by frank.svs
Thank You so much!
welcome  |
|
|
|
|
Src Query Truncation Error in the DataFlow Task