Calling Sybase stored proc

Question

Calling Sybase stored proc

scottpod

SSChasing Mays

Points: 653
More actions
December 29, 2008 at 7:49 am

#125309

trying to pipe over records from oracle over to sybase. Here is the setup.
1. data reader source -> recordset destination -> saved as SSIS object variable
2. foreach loop to cycle through the object variable and call an SSIS Execute SQL Task passing in parms to call a sybase stored proc using exec oracle_to_sybase ?,?,?,? format.
Tried all kinds of combinations between OLE DB and ADO.net connections. I would like to use ADO.net for the Oracle retrieve though......
Error message I get with ADO.net retrieve and Native OLE DB \Sybase execution of the stored proc.
Execute SQL Task: Executing the query "exec oracle_to_sybase ?,?,?,?" failed with the following error: "Error processing one or more parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
This does work if I hard code the parameters too......
Any help is appreciated thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Rather than using a query with parameters (?), use an expression to build the query.

I am not sure which Sybase OLEDB driver you are using, but the couple I have used in the past did not fully support parameterized queries.

scottpod SSChasing Mays Points: 653 More actions · Answer 2

thanks for the response.

I'm fairly new to SSIS......so here i tried the following.

on the execute sql task editor 'expressions' option I added the following under SqlStatementSource

"exec oracle_to_sybase @[User::metercode], @[User::eff_date], @[User::phv], @[User::change_user]"

Now as far as the Sql Statement properties in the General option......do I get rid of the sql statement and anything else? What about the parameters option....do I still leave these in?

I my error message from running it this way.....

Execute SQL Task: Executing the query "exec phv_to_sybase @[User::metercode], @[User::eff_date], @[User::phv], @[User::change_user]" failed with the following error: "The given type name was unrecognized". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Sybase driver version 4.20.00.67

thanks!

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

The expression looks ok.

Hard-code a value into the SQL Statement (it will use this to figure out the meta-data) in the source component and remove the parameter information.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

I would recommend you add an annotation to your project to note that you have used an expression. Nothing in the SSIS designer really helps you find them and they are easy to forget.

You can also download BIDSHelper (search for it on google). It is an add-in for bids and one of it's features is to tag components that have expressions with a purple triangle.

scottpod SSChasing Mays Points: 653 More actions · Answer 5

I was running in ADO.Net and changed it back to OLE DB....and this is the error message after I got rid of the parameters...and hardcoded the SQL Statement.

Execute SQL Task: Executing the query "exec phv_to_sybase @[User::metercode], @[User::eff_date], @[User::phv], @[User::change_user]" failed with the following error: "[Native Error code: 102]

[DataDirect ADO Sybase Provider] Incorrect syntax near '['.

Thanks again.

scottpod SSChasing Mays Points: 653 More actions · Answer 6

also why does the SQL Statement on the general properties stay there when you have the expression? Or is it because it is tied to the SqlStatementSource expression property? I am confused ...?

thx.

scottpod SSChasing Mays Points: 653 More actions · Answer 7

Update to this thread:

Looks like you can't call a sybase stored proc with parameters using OLEDB.

But you can using ODBC. Create a dsn.......and this was how I did it successfully.

Dim ConnString As String = "dsn=sybdev;uid=*;pwd=*"

Dim Conn As Odbc.OdbcConnection

Dim Reader As Odbc.OdbcDataReader

'Open Connection

Conn = New Odbc.OdbcConnection(ConnString)

Conn.Open()

Dim Cmd As New Odbc.OdbcCommand()

Cmd.Connection = Conn

Cmd.CommandText = "{CALL phv_to_sybase (?,?,?,?)}"

Cmd.CommandType = CommandType.StoredProcedure

'add parameter(s)

odbcParm1.ParameterName = "@parm"

odbcParm1.DbType = DbType.String

odbcParm1.Direction = ParameterDirection.Input

odbcParm1.Value = parmvalue

Cmd.Parameters.Add(odbcParm1)

Cmd.ExecuteNonQuery()

The key is Cmd.CommandText = "{CALL phv_to_sybase (?,?,?,?)}"

You have to have with the curly braces ...(!?!!?!!).

Hope this saves someone out there some future headaches!