Executing a dynamic query without using sp_executeSql

Hi,
DECLARE @.PrdID VARCHAR (50)
SET @.PrdID = '1,2'
SELECT @.PrdID
SELECT ProductName FROM Product WHERE PtoductID IN (@.PrdID)
Where the data type of ProductCode is Int and ProductName is Varchar
When I executing this query I gets null even if the product table contains
product code 1 and 2 . and productName is not null.
Any one know how can execute this query without using Sp_ExecuteSql ?Hello, Shahi
See this excellent article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/arrays-in-sql.html
Razvan

Executing a dynamic query without using sp_executeSql

Hi,
DECLARE @.PrdID VARCHAR (50)
SET @.PrdID = '1,2'
SELECT @.PrdID
SELECT ProductName FROM Product WHERE PtoductID IN (@.PrdID)
Where the data type of ProductCode is Int and ProductName is Varchar
When I executing this query I gets null even if the product table contains
product code 1 and 2 . and productName is not null.
Any one know how can execute this query without using Sp_ExecuteSql ?
Hello, Shahi
See this excellent article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/arrays-in-sql.html
Razvan

Execute SQL Task: UDF not taking parameters

Hi,

I have an Execute SQL Task in my SSIS Package.
Now, this Execute SQL Task has the following query (Connection Type is OLE DB):

Code Snippet

SELECT dbo.udf_CommonDateTime_Get (GetDate(), ?) As User_Datetime

I want 2 things from this Task:
1) It should take the 2nd argument to the UDF from a variable.
2) It should store the value returned by this SELECT statement into another variable.

So, I go ahead and modify the Parameter Mapping for the Task. Here I add the Input variable name, Data type and I give the Parameter Name as 0.

I also modify the Result Set for the Task. Here, I specify the Result Name as User_Datetime and give the appropriate Variable Name.

I am getting an error here and I believe it is due to the input parameter. The UDF is not getting the 2nd argument correctly.

My questions:
1) Has the Execute SQL Task been designed to handle UDFs like this. If not, then where am I going wrong?
2) What is the work-around for this? I need to pass a parameter (variable) to the UDF.

Thanks in advance.

Regards,
B@.ns

The error message is:

Code Snippet

Execute SQL Task: Executing the query "SELECT dbo.udf_Common_DateTime_Get (GetDate(), ?)

As User_Datetime" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Set UserDateTime

As a workaround, you could you create a new variable to store your sql statement. Set the 'EvaluateAsExpression' property of the variable to 'true'. Then set the expression like this...

"SELECT dbo.udf_CommonDateTime_Get (GetDate()," + user::VariableNameHere + ") As User_Datetime"

Then in 'SourceVariable' property of the execute sql statement to 'Variable' and then choose the variable name.

|||

Hi Martin,

Thank you for the reply. At least it gives me some hope

Unfortunately, I am getting this error:

Code Snippet

The expression for variable "varQuery" failed evaluation. There was an error in the expression.

If I remove the user::VariableNameHere part, it works fine...

Any ideas?

Thanks again.

Regards,

B@.ns

|||

You replaced user::VariableNameHere with the actual name of your variable correct?

|||

Martin,

It worked!!

Thank you so much!!

I had to do this:

Code Snippet

"SELECT dbo.udf_CommonDateTime_Get(GETDATE(), " + (DT_WSTR, 1) @.[User::VariableName] + ") As UserDateTime"

The only thing worries me is that @.[User::VariableName] can be NULL.

I will have to handle that.

Thanks again.

Regard,

B@.ns

Execute SQL Task Error

I have a stored procedure that accepts an integer as
input and sends a value out as type varchar(600).

When I use Execute SQL task with ADO.Net or ADO Connection
Manager and assign proper values for the parameters and
their direction, the execution of the task returns ONLY 1
character (first character)for the output parameter.

Sounds like a bug since I can't set the string length
anywhere in the data type.

Any help/work arounds would be greatly appreciated it.

ThanksThis is a shot in the dark, but maybe a UCS-2 string (Microsoft's favorite type of Unicode encoding) is being used, and being interpreted as one of the Windows proprietary 8-bit encodings (eg, windows-1252), and the first character is in the ASCII set (eg, a regular letter), so the second byte of its UCS-2 representation is a zero byte, and this is truncating the string.
If you don't follow that, perhaps try changing the return value to nvarchar(600).
Note however, that we're successfully passing back varchar values via Execute SQL
tasks.
|||N.S. Which build you were on?
Another possibility is if you were on an earlier build, we had a known issue using ADO.Net connection in ExecuteSQLTask to get the full string value from out parameters. If that's the case for you, try upgrade to our CTP16 release and you should see the fix.

Thanks|||

I am on Version 8.0.50215.44. I'll upgrade to CTP 16. Thanks.

Execute SQL Task Error

Hi,

I have a For Loop Container which has Execute SQL Task. The following SQL is not working in it.

Input Parameters: Batch_ID, Class_ID both of type long in the parameter mapping dialog.

The result set is of type 'One Row' and direction is input

Result set is: NextBatchID>User::MinBatch_ID of type int

NextClassID->User::MinClass_ID of type int

The query is giving very generic error

[Execute SQL Task] Error: Executing the query "" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Code Snippet

DECLARE @.ClassID int
DECLARE @.BatchID int
SET @.BatchID = ?
SET @.ClassID = ?
SELECT MAX(T.Batch_ID) AS NextBatch_ID, MAX(T.Class_ID) AS NextClass_ID FROM
(Select TOP (10) BD.Batch_ID, BD.Class_ID,
ROW_NUMBER() OVER(ORDER BY Batch_ID, Class_ID)AS RowNum
From dbo.Batch_Data As BD
WHERE (BD.Batch_ID > @.BatchID) OR (BD.Batch_ID = @.BatchID AND BD.Class > @.ClassID)
ORDER BY Batch_ID, Class_ID) T
WHERE T.RowNum = 10

When I hardcode values the query works. With parameters it fails.

Any help/thought?

-Leo

I don't know if parameters are supported outside of the WHERE clause. I recommend you use an expression-based variable to build your query, then just have the Execute SQL Task retrieve the query from the variable.
|||

Hi,

We cannot use parameters other than WHERE cluase. Where can I find this and any other restrictions about the Parameters in BOL?

Thanks,

-Leo

|||

There are not ducumentes restrictions about that, * I think*.

Jay's suggestion is still valid; just use an expression to build the sql statement of the execute sql task. The expression will concatenate all the required variables at run time.

|||

The topic on the Execute SQL Task contains a wealth of information on the use of parameters:

http://msdn2.microsoft.com/en-us/library/ms141003.aspx

The rules that govern the use of parameters in SSIS are not SSIS rules, but come from the provider that is being used (and, of course, the database's dialect of SQL). So depending on the connection manager that you have chosen, you must observe the rules of SqlClient or ODBC or ADO or OLE DB for parameter usage.

-Doug

|||Hi, were you able to resolve the above issue? If yes, could you please educate me as to how? Thanks|||If you want to use paramters in a SQL statement outside of the WHERE clause, build it in an expression, as JayH suggested.

Execute SQL Task Error

Hi:

I am getting the following error message while trying to run a Execute SQL task with Variables in BIDS. My connection type is ADO.NET . My Variables defined are

Varout and Varin. Both are String type Variables. @.varout has a value set to "Category" and @.varin has a value set to "Test Category". I am using the expression

" Select * into " + @.[User::VarOut] + " FROM " + @.[User::Varin]

The expression eveluates correctly. The error I get when i run the package is:

Package Validation Error. Failed to lock Variable "Select * into TestCategory from category" for read access with error 0XC0010001. The Variable cannot be found. This occurs when an attempt is made to retrieve a variable from the variables collection on a container during the execution of package and the variable is not there. The Variable name may have changed or the Variable is not being created.

Can anyone please tell me what I am doing wrong or where do I need to look at?.

Thank you

AK

Can anyone please please give me a solution. This is really urgent. I found a KB describing a issue related to Script task but not sure if it is applicable to this issue. It involves applying a hot fix. Anyone from MS please comment.|||this link might help: http://msdn2.microsoft.com/en-us/library/ms141003.aspx|||

Thanks Duane. That Article helped. I was selecting the SQLSourcetype as Variable and that was throwing errors. I changed it and my package runs like a charm!!!.

Thanks again.

Execute SQL Task : Input and Output parameters in tsql stataments with ADO.NET connection type

Hi Everyone,

I haven't been able to successfully use the ADO.NET connection type to use both input and output parameters in an execute sql task containing just tsql statements (no stored procedure calls). I have successfully used input parameters on their own but when i combine it with output parameters it fails on the simplest of tasks.

I would really find it beneficial if you could use the flexibility of an ADO.NET connection type as the parameter marker and parameter name can be referenced anywhere throughout the sql statement in no particular order. The addition of an output parameter would really make it great!!

Thanks

What is the error you get?

have you search this forum and/or the web

http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=ADO.NET+parameters&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

|||When you say "output parameters" are you talking about using the result set feature?

Note that the result name should be 0, 1, 2, etc... when mapping to a variable name.|||For more than one result, Make sure that you are using the result set of "Full Result Set" then shred this result set using a for each loop, and assign each to a variable.|||

Hi,

By output what i am referring to is the direction of the paramter. What i am trying to do is pass an an input and an output parameter to an execute sql task with the ado.net connection type and to populate a variable in the package, User::Test2, with the value being returned by the output parameter.

ResultSet : None

SQLSourceType: DirectInput

SQLStatement:

Update dbo.table1

Set value = @.Test1

Set @.Test2 = 'Test 3'

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

The error i am getting is

Error: 0xC002F210 at Execute SQL Task 2, Execute SQL Task: Executing the query "

Update dbo.table_1

Set value = @.Test1

Set @.Test2 = 'Test 3'

" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 ("@.Test2"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks

|||

PK2000 wrote:

Hi,

By output what i am referring to is the direction of the paramter. What i am trying to do is pass an an input and an output parameter to an execute sql task with the ado.net connection type and to populate a variable in the package, User::Test2, with the value being returned by the output parameter.

ResultSet : None

SQLSourceType: DirectInput

SQLStatement:

Update dbo.table1

Set value = @.Test1

Set @.Test2 = 'Test 3'

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

The error i am getting is

Error: 0xC002F210 at Execute SQL Task 2, Execute SQL Task: Executing the query "

Update dbo.table_1

Set value = @.Test1

Set @.Test2 = 'Test 3'

" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 ("@.Test2"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks

Use the resultset feature to get output. I'm trying to figure out why you'd want to assign a literal value to an output parameter in a SQL task. There are easier ways of getting that populated. The way you've got it setup only has inbound parameters.|||

Hi Phil,

I will try the resultset feature.

Why i was assinging a literal value to an output parameter was only to test that it works, that is User::Test2 is assigned the value "Test3".

Thanks

|||

Hi Ryan,

I am not actually returning a resultset back. If possible, what i am trying to do is assign a value to @.Test2 in the sql statement which will map this value back to User::Test2. In the Parameter mapping i have declared the following:

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

Thanks

|||

PK2000 wrote:

Hi Ryan,

I am not actually returning a resultset back. If possible, what i am trying to do is assign a value to @.Test2 in the sql statement which will map this value back to User::Test2. In the Parameter mapping i have declared the following:

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

Thanks

You can't. Use the resultset feature to return a value from the SQL to a variable.|||

Thanks Phil and Ryan!

I was able to use a resultset and then shred the resultset using a foreach loop.

Execute SQL Task : Input and Output parameters in tsql stataments with ADO.NET connection ty

Hi Everyone,

I haven't been able to successfully use the ADO.NET connection type to use both input and output parameters in an execute sql task containing just tsql statements (no stored procedure calls). I have successfully used input parameters on their own but when i combine it with output parameters it fails on the simplest of tasks.

I would really find it beneficial if you could use the flexibility of an ADO.NET connection type as the parameter marker and parameter name can be referenced anywhere throughout the sql statement in no particular order. The addition of an output parameter would really make it great!!

Thanks

What is the error you get?

have you search this forum and/or the web

http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=ADO.NET+parameters&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

|||When you say "output parameters" are you talking about using the result set feature?

Note that the result name should be 0, 1, 2, etc... when mapping to a variable name.|||For more than one result, Make sure that you are using the result set of "Full Result Set" then shred this result set using a for each loop, and assign each to a variable.|||

Hi,

By output what i am referring to is the direction of the paramter. What i am trying to do is pass an an input and an output parameter to an execute sql task with the ado.net connection type and to populate a variable in the package, User::Test2, with the value being returned by the output parameter.

ResultSet : None

SQLSourceType: DirectInput

SQLStatement:

Update dbo.table1

Set value = @.Test1

Set @.Test2 = 'Test 3'

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

The error i am getting is

Error: 0xC002F210 at Execute SQL Task 2, Execute SQL Task: Executing the query "

Update dbo.table_1

Set value = @.Test1

Set @.Test2 = 'Test 3'

" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 ("@.Test2"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks

|||

PK2000 wrote:

Hi,

By output what i am referring to is the direction of the paramter. What i am trying to do is pass an an input and an output parameter to an execute sql task with the ado.net connection type and to populate a variable in the package, User::Test2, with the value being returned by the output parameter.

ResultSet : None

SQLSourceType: DirectInput

SQLStatement:

Update dbo.table1

Set value = @.Test1

Set @.Test2 = 'Test 3'

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

The error i am getting is

Error: 0xC002F210 at Execute SQL Task 2, Execute SQL Task: Executing the query "

Update dbo.table_1

Set value = @.Test1

Set @.Test2 = 'Test 3'

" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 ("@.Test2"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks

Use the resultset feature to get output. I'm trying to figure out why you'd want to assign a literal value to an output parameter in a SQL task. There are easier ways of getting that populated. The way you've got it setup only has inbound parameters.|||

Hi Phil,

I will try the resultset feature.

Why i was assinging a literal value to an output parameter was only to test that it works, that is User::Test2 is assigned the value "Test3".

Thanks

|||

Hi Ryan,

I am not actually returning a resultset back. If possible, what i am trying to do is assign a value to @.Test2 in the sql statement which will map this value back to User::Test2. In the Parameter mapping i have declared the following:

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

Thanks

|||

PK2000 wrote:

Hi Ryan,

I am not actually returning a resultset back. If possible, what i am trying to do is assign a value to @.Test2 in the sql statement which will map this value back to User::Test2. In the Parameter mapping i have declared the following:

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

Thanks

You can't. Use the resultset feature to return a value from the SQL to a variable.|||

Thanks Phil and Ryan!

I was able to use a resultset and then shred the resultset using a foreach loop.

Execute Sql Statement Stored in Table Type Variable

hi,

I Wrote one stored procedure in which i declare one variable as Table
then i stored in that table type variable
select
*
from
emp
so now my table contain one sql statement by 4 rows
now i want to execute the sql statement how (MS SQL SERVER 2000)

Actually my Query Contain more than 15,000 characters.hi
Just In the end of your Storedprodure type
print @.your SQl Statment|||

Quote:

Originally Posted by hisham123

hi,

I Wrote one stored procedure in which i declare one variable as Table
then i stored in that table type variable
select
*
from
emp
so now my table contain one sql statement by 4 rows
now i want to execute the sql statement how (MS SQL SERVER 2000)

Actually my Query Contain more than 15,000 characters.

Hi,
select the four rows in to four variables @.a,@.b,@.c,@.d,
remember while assigning you have to put the row values in single quotes
then execute the query by using this technic
[CODE]
exec(@.a+' '+@.b+' '+@.c+' '+@.d)

Execute batch file from a Job

How can I execute a batch file (upload.bat) from a Job.
I have the type of job as a Operating System Command, but
I don't know what to put on the Command box. I tried the
batch file path and file name, but it doesn't work.
Thanks,
Vicyou could try
cmd /c Your\Batch\File\Path\and\name.bat
"Vic" <vduran@.specpro-inc.com> wrote in message
news:03ec01c36055$3b8a9390$a501280a@.phx.gbl...
> How can I execute a batch file (upload.bat) from a Job.
> I have the type of job as a Operating System Command, but
> I don't know what to put on the Command box. I tried the
> batch file path and file name, but it doesn't work.
> Thanks,
> Vic|||Check out xp_cmdshell in BooksOnLine.
--
Andrew J. Kelly
SQL Server MVP
"Vic" <vduran@.specpro-inc.com> wrote in message
news:03ec01c36055$3b8a9390$a501280a@.phx.gbl...
> How can I execute a batch file (upload.bat) from a Job.
> I have the type of job as a Operating System Command, but
> I don't know what to put on the Command box. I tried the
> batch file path and file name, but it doesn't work.
> Thanks,
> Vic