executing SP

I have stored procedure with parameters.
Can I exec stored procedure somehow with result set of select statement, for
example:
exec dbo.myProcedure (select par1,par2,par3 FROM myTable)
Or I must declare each parameter:
declare @.par1 int,@.par2 int,@.par3 int
SELECT @.par1= par1,@.par2=par2,@.par3=par3 FROM myTable
and then exec my procedure:
exec dbo.myProcedure @.par1,@.par2,@.par3
In real example I have a lot of columns and declaring many of them just to
execute another SP is not so pleasent.
lp,S>> Can I exec stored procedure somehow with result set of select statement,
No, a SELECT statement returns a set of rows. A Stored procedure cannot take
a set of rows for its parameter -- it has to be scalar values. So you have
to explicitly assign individual variables to pass them as parameters.
Anith|||To add to what Anith said, build yourself a query from the
information_schema.columns view to build the parm list, especially if you do
this often.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"simon" <simon.zupan@.iware.si> wrote in message
news:SPGNe.1586$cE1.227654@.news.siol.net...
>I have stored procedure with parameters.
> Can I exec stored procedure somehow with result set of select statement,
> for example:
> exec dbo.myProcedure (select par1,par2,par3 FROM myTable)
>
> Or I must declare each parameter:
> declare @.par1 int,@.par2 int,@.par3 int
> SELECT @.par1= par1,@.par2=par2,@.par3=par3 FROM myTable
> and then exec my procedure:
> exec dbo.myProcedure @.par1,@.par2,@.par3
> In real example I have a lot of columns and declaring many of them just to
> execute another SP is not so pleasent.
> lp,S
>|||Hi
If you want to create the procedure you could run the query as a SELECT INTO
statement (possibly with WHERE 1=0 to stop any rows being returned!) you
will get a table with the column names and datatypes. This can be scripted
in the object browser into a window and edited (you may want to remove
collations and add @. to the names!)
If you already have the procedure definition then look at
INFORMATION_SCHEMA.COLUMNS as already suggested.
John
"simon" <simon.zupan@.iware.si> wrote in message
news:SPGNe.1586$cE1.227654@.news.siol.net...
>I have stored procedure with parameters.
> Can I exec stored procedure somehow with result set of select statement,
> for example:
> exec dbo.myProcedure (select par1,par2,par3 FROM myTable)
>
> Or I must declare each parameter:
> declare @.par1 int,@.par2 int,@.par3 int
> SELECT @.par1= par1,@.par2=par2,@.par3=par3 FROM myTable
> and then exec my procedure:
> exec dbo.myProcedure @.par1,@.par2,@.par3
> In real example I have a lot of columns and declaring many of them just to
> execute another SP is not so pleasent.
> lp,S
>

Executing Oracle Stored Procedure with output parameters using ADO.NET connection

I am a bit confused by an issue that I am having with executing an Oracle stored procedure (with an output parameter) using an ADO.NET connection object. I am able to get this working using an OLEDB connection, but I have no idea why the ADO.NET connection doesn't work. (Bug, by design, or my ignorance?) Actually, I can even get this to work if I use the .NET Providers for OLE DB\Microsoft OLE DB Provider for ORACLE if we set the connectionType to ADO.NET. This is the error that I am receiving:

[Execute SQL Task] Error: Executing the query "pkg_utility_read.test_out_var " failed with the following error: "The OracleParameterCollection only accepts non-null OracleParameter type objects, not SqlParameter objects.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

It is also worth mentioning that the ORACLE stored procedure has an out parameter with a NUMBER datatype which I think maps to the ADO.NET Int32 datatype. I guess OLE DB datatypes are more closely mapped to ORACLE datatypes. In OLE DB you can set the parameter to double and the ORACLE stored procedure to NUMBER and it works.

Any help on this would be most appriciated.

hi Jason,

I got the same error. What driver are you using? I use .NET provider Oracle client data provider.

Can you also tell me how did you successfully invoke the stored proc using OLEDB for Oracle?

I have an sp with 1 input parameter of type Number and 14 output parameters some of type Number and some Varchar2.

Can you tell me the sql statement syntax to use and the parameter mapping to use. I know that the parameter name starts with 0,1,2...

Please help

thanks.

sandeep

sql

Executing Oracle Stored Procedure via SQL Server 2000 Linked Serve

All,
I am trying to execute a simple Oracle stored procedure from SQL Server
2000. The SP has no input or output parameters. I have created the linked
server correctly as I am able to execute Oracle functions just fine. I have
tried the following two methods to execute the SP, SKELETON:
{Call ORADB..MARCO.SKELETON()}
Execute ORADB..MARCO.SKELETON
Both methods result in the same error listed below:
Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'SKELETON' on remote server 'ORADB'.
[OLE/DB provider returned message: One or more errors occurred during
processing of command.]
[OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.]
Any ideas on how I can get this working properly?
Thanks,
MarcoHere is an old post by Umachandar. See if it helps:
http://tinyurl.com/7dxrr
-oj
"marco" <marco@.discussions.microsoft.com> wrote in message
news:792C4015-008A-420A-B307-EAFC6BAC49EE@.microsoft.com...
> All,
> I am trying to execute a simple Oracle stored procedure from SQL Server
> 2000. The SP has no input or output parameters. I have created the
> linked
> server correctly as I am able to execute Oracle functions just fine. I
> have
> tried the following two methods to execute the SP, SKELETON:
> {Call ORADB..MARCO.SKELETON()}
> Execute ORADB..MARCO.SKELETON
> Both methods result in the same error listed below:
> Server: Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'SKELETON' on remote server 'ORADB'.
> [OLE/DB provider returned message: One or more errors occurred during
> processing of command.]
> [OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.]
>
> Any ideas on how I can get this working properly?
> Thanks,
> Marco
>

Execute Stored Procedure via Execute SQL Task

Do you know of a bug in the June CTP of SSIS where you cannot, using an Execute SQL Task, execute a stored procedure with parameters via an OLE DB connection? For example, one combination I tried was in the SQL Statement, I have:

EXEC dbo.DimBuild ?,?

And in the parameter mapping, I added two date user variables, both with parameter name ‘?’

--

I also tried

EXEC dbo.DimBuild sd, ed

And in the parameter mapping, I added two date user variables, one with a parameter name ‘sd’ and the other ‘ed’

--

I tried many other combinations as well. The error I would get would say “parameter name unrecognized”. [Execute SQL Task] Error: Executing the query "dbo.DimBuild sd, ed" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Is there something wrong with my syntax? Interestingly, I tried executing the stored procedure using an ADO.Net connection, with similar parameter mappings, and it worked just fine.

Thanks,

- Joel

I was banging my head against the wall trying to figure this out also. I finally created a simple DTS package with an Execute SQL task running a parameterized query. When I migrated it to SSIS, I found the parameter name was 0.

I modified the package I was working on to use parameter names 0 and 1 (with "exec sproc ?, ?" as the query) and it succeeded.

Don't you just love the great documentation?|||I know the documentation lacks on this. In this post http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=57637 Kirk has given us indication which Parameters can be used with which connection manager etc.

Execute Stored Procedure Using Select Statement As Parameters

Hi,
I'm trying to run a SP with a select statement. Example below:
Stored procedure: uspI_InsertUsername (@.Username VARCHAR(50))
SQL Statement: EXEC uspR_InsertUsername SELECT Username FROM TempUser
Will this work?
--
Thanks,
Jig PatelYou can store the result of the query to a temporary variable and execute th
e
sp using the result
declare @.user VARCHAR(50)
select @.user = Username
FROM TempUser
EXEC uspR_InsertUsername @.user
it will work when
select Username
FROM TempUser
returns only one user, otherwise you will have to declare @.user as a table
variable and loop through it to execute the procedure for each row
Let me know if it helps
"Jig" wrote:

> Hi,
> I'm trying to run a SP with a select statement. Example below:
> Stored procedure: uspI_InsertUsername (@.Username VARCHAR(50))
> SQL Statement: EXEC uspR_InsertUsername SELECT Username FROM TempUser
> Will this work?
> --
> Thanks,
> Jig Patel|||Could you be more specific on what you want to reach.

Execute Stored Procedure option missing from Object Explorer

The option when you right click on a stored procedure to Execute where
it then pops up a nice helper dialog to fill out the parameters is
missing in my SQL Server Management Studio console. The funny thing is
that it was there at one time when I had upgraded from MSDE to SQL
Server Express and installed the SSMS Express. When I got a hold of
and installed the full version of the studio tools on top of that,
stuff was not working correctly like diagrams and books online. So I
uninstalled everything and then reinstalled SQL Server Express and SQL
Server Tools from the SQL Server 2005 Developer Edition DVD. Now that
nice feature is missing from SMSS. Does anyone know how I can get that
option back?Hi Mark
On my RTM version of SQL Server 2005 (Developer Edition) there is an
"Execute Stored Procedure..." option when you right click the procedure whic
h
will allow you to do this, there is also the "EXECUTE to.." option on the
"Script Stored Procedure as" menu. I am not sure what is available in
Microsoft SQL Server Management Studio Express.
John
"Mark" wrote:

> The option when you right click on a stored procedure to Execute where
> it then pops up a nice helper dialog to fill out the parameters is
> missing in my SQL Server Management Studio console. The funny thing is
> that it was there at one time when I had upgraded from MSDE to SQL
> Server Express and installed the SSMS Express. When I got a hold of
> and installed the full version of the studio tools on top of that,
> stuff was not working correctly like diagrams and books online. So I
> uninstalled everything and then reinstalled SQL Server Express and SQL
> Server Tools from the SQL Server 2005 Developer Edition DVD. Now that
> nice feature is missing from SMSS. Does anyone know how I can get that
> option back?
>|||Thanks for the reply. I have the RTM version of SQL Server 2005
Developer Edition installed (just the tools, not the engine). You have
the open "Execute Stored Procedure..." and its missing on my
installation and we have the same version installed. Anyone know how
to get the option back?|||I just noticed something. I was connected to a SQL Server 2000
instance and I noticed that the option "Execute Stored Procedure.." was
available when I right clicked on a stored procedure. So I opened a
connection to my local SQL Server 2005 Express instance and the option
is not there. There must be something different in the configuration
of the database that I cannot see.|||Hi
The databases I used were upgrade from SQL 2000. Have you check that your
are granted execute permission to this procedure?
John
"Mark" wrote:

> I just noticed something. I was connected to a SQL Server 2000
> instance and I noticed that the option "Execute Stored Procedure.." was
> available when I right clicked on a stored procedure. So I opened a
> connection to my local SQL Server 2005 Express instance and the option
> is not there. There must be something different in the configuration
> of the database that I cannot see.
>|||This is happening on all my stored procedures. I am logged on using
Windows authentication and I am an administrator. And I can execute it
by writing a query or using the Script Stored Procedure As... EXECUTE
to. But for grins, I granted explicit permissions for me to execute a
stored procedure. It did not work. Thanks for the suggestion.
Anyone else?|||Hi
The only other suggestion I can think of is to try a fresh install on a
different machine.
John
"Mark" wrote:

> This is happening on all my stored procedures. I am logged on using
> Windows authentication and I am an administrator. And I can execute it
> by writing a query or using the Script Stored Procedure As... EXECUTE
> to. But for grins, I granted explicit permissions for me to execute a
> stored procedure. It did not work. Thanks for the suggestion.
> Anyone else?
>|||Mark (mark@.staffordcastle.com) writes:
> This is happening on all my stored procedures. I am logged on using
> Windows authentication and I am an administrator. And I can execute it
> by writing a query or using the Script Stored Procedure As... EXECUTE
> to. But for grins, I granted explicit permissions for me to execute a
> stored procedure. It did not work. Thanks for the suggestion.
> Anyone else?
Unfortunately, this sounds completely strange to me. Particularly when you
say that you see EXECUTE when you connect to an SQL 2000 Server. It is as
if Mgmt Studio had a per-server configuration for what is to be in the
menus. But I have never heard of such a setting.
I suggest that you log a bug at
http://lab.msdn.microsoft.com/ProductFeedback/. It is not likely that
it will lead to anything, as I guess that you are not able to produce
a reproducible scenario. (Yes, it's reproducible on your machine, but
your machine is not in Redmond.) But maybe if you are lucky, someone
knows about the secret switch.
If possible, it would be interesting to hear what happens if you connect
to a second SQL 2005 instance.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

execute stored procedure (with parameters) with an "exec" command

Hi everybody, I would like to know if it's possible to execute a stored procedure, passing it parameters, using not CommandType.StoredProcedure value of sqlcommand, but CommandType.Text.

I tried to use this:
sqlCmd.CommandType = CommandType.Text
sqlCmd.Parameters.Add(sqlPar)
sqlCmd.ExecuteNonQuery()

With this sql command:
"exec sp ..."

I wasn't able to make it to work, and I don't know if it's possible.

Another question:
if it's not possible, how can I pass a Null value to stored procedure?
This code:
sqlPar = new SqlParameter("@.id", SqlDbType.Int)
sqlPar.Direction = ParameterDirection.Output
cmd.Parameters.Add(sqlPar)

sqlPar = new SqlParameter("@.parent_id", DBNull)
cmd.Parameters.Add(sqlPar)

doesn't work, 'cause I get this error:
BC30684: 'DBNull' is a type and cannot be used as an expression.

How can I solve this?
Bye and thanks in advance.

P.S. I would prefer first method to call a stored procedure ('cause I could call it with 'exec sp null' sql command, solving the other problem), but obviusly if it's possible...=)

Sorry for grammatical mistakes.It's DBNull.Value|||Try:

sqlPar = new SqlParameter("@.parent_id", DBNull.Value)|||Perfect, it works, but now I have another problem.
I get this error:
This SqlTransaction has completed; it is no longer usable.

When I run Commit or Rollback code.|||Are you committing the transaction twice accidentally?|||No, I'm sure.
The only "strange" thing I do it's to call 2 different functions in my class to begin and commit/rollback transaction.|||Then you will have to show some code...|||'CConn is my class
CConn.BeginTransaction()

Dim arParameters as new ArrayList()

Dim sqlParOutput = new SqlParameter("@.id", SqlDbType.Int)
sqlParOutput.Direction = ParameterDirection.Output
arParameters.Add(sqlParOutput)

Dim sqlPar = new SqlParameter("@.parent_id", DBNull.Value)
arParameters.Add(sqlPar)

objTransaction = CConn.ExecuteNonQuery("sp", arParameters, false)

if objTransaction is nothing then objTransaction = CConn.ExecuteNonQuery("...")

if objTransaction is nothing then
CConn.CommitTransaction()
else
CConn.RollbackTransaction()
end if

The Begin, Commit and Rollback functions in CConn class simply call the same functions of SqlConnection object.
The ExecuteNonQuery function override standard function.|||This helps sort of not at all. We do not know what your class is doing. Is the return from CConn.ExecuteNonQuery a transaction object? What is the significance of objTransaction being nothing?|||Sorry, you're right.
Here the class methods:

 Public function BeginTransaction() as SqlTransaction
 Me.Conn = New SqlConnection(Me.sConnStr)
 Me.Conn.Open()
 'Start a local transaction
 Me.myTransaction = Conn.BeginTransaction()
 end function
 Public sub CommitTransaction()
 Me.myTransaction.Commit()
 Me.Conn.Close()
 end sub
 Public function RollbackTransaction() 'transaction as SqlTransaction)
 Me.myTransaction.Rollback()
 Me.Conn.Close()
 end function
 Public function ExecuteNonQuery(ByVal SQL As String, optional sqlParameters as ArrayList = nothing, optional toClose as boolean = true) as object
 Dim objReturn as object
 if Me.Conn is nothing then
 Me.Conn = New SqlConnection(Me.sConnStr)
 Me.Conn.Open()
 else
 if Me.Conn.State <> ConnectionState.Open then Me.Conn.Open()
 end if
 Dim sqlCmd As New SqlCommand(SQL, Me.Conn)
 'Must assign transaction object to Command object for a pending local transaction
 if not Me.myTransaction is nothing then sqlCmd.Transaction = Me.myTransaction
 Try
 if not sqlParameters is nothing then
 sqlCmd.CommandType = CommandType.StoredProcedure
 Dim sqlPar as SqlParameter
 for each sqlPar in sqlParameters
 sqlCmd.Parameters.Add(sqlPar)
 next
 else
 sqlCmd.CommandType = CommandType.Text
 end if
 sqlCmd.ExecuteNonQuery()
 Catch e As Exception
 objReturn = e
 End Try
 'must be clean up?
 if toClose then
 Me.Conn.Close()
 sqlCmd.Dispose()
 Me.Conn.Dispose()
 end if
 return objReturn
 End function

Execute Stored Proc from OLE DB Destination

is is posible to execute a stored proc (with parameters) from an OLE DB Destination ?

reason we are trying this is cos

our current setup is an OLE DB Command doing the first database update and then passing over to an OLE DB Destination that does the second update. There is error handling coming off the OLE DB Command to a Script component that passes to an OLE DB Destination.

we are having a problem getting the error reporting working from the OLE DB Command - the updates work fine - but not getting any updates of the error database when there is an error.

have got the error reporting fine on the OLE DB Destination.

if we can execute the stored proc from the OLE DB Destination, we will then do both updates via one stored procedure executed by the OLE DB Destination.

thx

m

n.b. think the updating of the error logs from the OLE DB Command used to work - but cant get it to work now ?!!!!?

No, you can't. Why not use another OLE DB command?|||

cos we cant get the error logging to work from the OLE DB Command

and we want to know if any records dont make it

m

|||Well, why can't you get error logging to work? Any, ahem, error messages you can provide us with?|||

phil

thx for this

we couldn't get it to work and could not understand why (followed and re-followed all of the instructions)

today

new member on project, new PC, new SQL installation (SP1), works fine ?

so

am uninstalling SQL from my machine and re-installing it (back to what it was - SP1) - and will see

m

Execute SSIS package from a ASN.NET 2.0

Dear Friends,

I have a SSIS project (You can see in my blog) with the main parameters, StartDate and EndDate.

How can I refresh this parameters? Where I save it?

Use a table in database with the fields Startdate and Enddate, and link it to my variables in SSIS?

Use the package configuration of SSIS?

Give me some tips!

regards!

How often are they updated? If they change frequently, I'd store it in a database, and use an Execute SQL task to populate variables in my package. If they don't change often, I'd use configurations.|||

jwelch,

Change each day... I will run this package one or two time per day, and the parameters receive the startdate and enddate. For almost the case, because I will import data for each date, the startdate will be Currentdate-1 and the enddate will be CurrentDate, but could be changed for some cases by the user administrator.

The user administrator sometimes would require to import dates for a different interval of dates...

Do you thinks is better to save in a table in database?

Thanks!

|||Yes, that's how I've implemented similiar functionality in the past.

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 passing parameters to a restore command

Hi,

I'm very new to SSIS and I’m trying to do the following in a SQL task

RESTORE DATABASE @.DatabaseName FROM DISK = @.Backup WITH FILE = 1, MOVE @.OldMDFName TO @.NewMDFPath, MOVE @.OldLDFName TO @.NewLDFPath, NOUNLOAD, REPLACE, STATS = 10

I'm using an OLE DB connection and I have mapped user variables to the various parameter names. Unfortunately when i test the above command it fails on must declare the scalar variable "@.DatabaseName". How can i get my values to be substituted into the command?

Many thanks

Martin

The best way is to build your SQL statement in a variable via expressions and then use that variable as the SQL source for the Execute SQL task.|||I don't think you can parameterize a RESTORE DATABASE command like that.

The way I'd do it is to create a new parameter named SqlStatement or something of similar meaning. This parameter's EvaluateAsExpression property will be True and the Expression property will be: "RESTORE DATABASE " + @.[User::DatabaseName] + " FROM DISK = ...". Then set the SQLSourceType of your Execute SQL Task to Variable and specify SqlStatement as the variable.

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 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 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 : 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 store_procedure using XMLs data as parameters

Iwant to learn how can achieve as the subject say, thanks!Hope this link helps :http://eramseur.blogspot.com/2005/11/xml-integration-in-sql-server-2005.html
|||hi Eramseur, I can not open the link you give, pls help to double check, tks!|||Click Here!|||

Try the thread below for SQL Server 2000 XML related articles and download the SQL Server 2005 BOL (books online) for SQL Server 2005 XML stored proc code sample because XML is native in SQL Server 2005. Hope this helps.

http://forums.asp.net/1118260/ShowPost.aspx

http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en

Execute same statement for each value in result set

Hi everyone,

My brain refuses to remember the (undocumented?) stored procedure I'm
thinking of. It takes at least two parameters: a sql statement to
execute, and a table name (or something of that nature).

Then, for each value in the table, it executes the sql statement and
passes the value as a parameter.

Can anybody refresh my memory? The functionality may be slighly
different than described, but the principle is the same. Thanks very
much...

-JoeNow that I think about it, rather than a table name, I believe one of
the parameters may be statement that returns a 1 column resultset, as
it would probably make more sense.|||xp_execresultset

More info here:
http://www.rac4sql.net/xp_execresultset.asp

The usual warnings apply about the use of undocumented features.

--
David Portas
SQL Server MVP
--|||Indeed... Thanks a lot...

Execute Process Task Arguments

HI,

Is it possible to provide variables ( multiple variables ) in the arguments parameters of the Execute Process Task?

Thanks

Shafiq

You can use multiple command arguements in one task by using spaces to delimit arguements.

Thanks,
Loonysan

|||To use variables in the process arguments, use Expressions tab and define an expression for Arguments property. You can use multiple variables there.

Execute Process Task

Hi,

We have an SSIS Execute Process Task which calls an executable along with the required parameters.

When we run this package, it intermittently gives the error as shown below in red

Executing "ppscmd.exe" "StagingDB /Server http://SERVERNAME:46787 /path OSB_FY08.Planning.dimensionECFuncArea /Operation LoadDataFromStaging" at "", The process exit code was "1" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:05:08 PM Finished: 5:08:40 PM Elapsed: 212.203 seconds. The package execution failed. The step failed.

We are not able to debug this issue. We had a look at the logging information as well but we are not getting any information on this issue.

How can we resolve this issue ?

Any help on this would be highly appreciated.

Thanks & Regards

Joseph Samuel

You are getting the error simply because the process you are exeuting is returning some error code (non-zero). If you want to simply ignore the error, you can set the "ForceExecutionResult" property.|||

If you want to capture the error from PPSCMD, redirect the output to a file. See this post for details (you have to call it from cmd.exe):

http://blogs.msdn.com/michen/archive/2007/08/02/redirecting-output-of-execute-process-task.aspx