MS-SQL store procedure trigered problem

[juliyas]

Please help,
I have store procedure in SQL server, which use the open query.
When i run the job which triggered the store procedure by ODBC stage i get the error :"
IF9MIF_CCP_CallProc..MSSQL_SP.CheckStatusSp: DSD.BCIOpenR call to SQLExecDirect failed.
Statement was:{call Phone_Directory.dbo.Update_IVR(0)}
SQLSTATE=01000, DBMS.CODE=7300
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].
SQLSTATE=37000, DBMS.CODE=7391
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver][SQL Server]The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction."
Can somebody help me?

[roy]

Hi,
Does this SP run ok from the sql editor?
ask your DBA if they can shed any light on what might generate theese error codes.
what does the SP do?
does it require special privilages?
could it be you can't run the SP if you'r already inside a transaction?

theese are the questions I can think of right now with the info you supplied so far.

IHTH,

[sudharsanan]

How are you running this store procedure?.. does the user has permission's to execute the procedure.. confrim this from your DBA..also give us some info on wht does that store procedure doing...

[MTCHONG]

Please help,
I have store procedure in SQL server, which use the open query.
When i run the job which triggered the store procedure by ODBC stage i get the error :"
IF9MIF_CCP_CallProc..MSSQL_SP.CheckStatusSp: DSD.BCIOpenR call to SQLExecDirect failed.
Statement was:{call Phone_Directory.dbo.Update_IVR(0)}
SQLSTATE=01000, DBMS.CODE=7300
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].
SQLSTATE=37000, DBMS.CODE=7391
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver][SQL Server]The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction."
Can somebody help me?

We have (few years later) the same kind of problem with the provider OLE DB 'SQLOLEDB' . Did anybody find the solution ? We are trying to call a stored procedure on a server which is doing an openquery to an another sql server (select statement only). The stored procedure succeeds running in SQL server if no BEGIN TRANSACTION is written into it. When we add a begin transaction the error is the same one that we had in Datastage director. We tried the statement SET REMOTE_PROC_TRANSACTIONS OFF in many ways but it's still not working. Do someone know how to write several statements in the 'SQL defined ' of the object ODBC like SET blabla ; CALL my_proc; ? I have a syntax error. So do we have to modify one database parameter in order to enable distributed transactions ? Any idea ? Thanks.