Msg 7391 in SQL Server
So I’m setting up a stored procedure in SQL Server 2000 to access some data on an Oracle database. The DBAs have set up the linked server in SQL Server to the Oracle source with the name ‘DTEST’. I’m going to need to use the OPENQUERY method since I need the statement to execute in Oracle.
Here is my first attempt:
SET @sql_str =
'SELECT DISTINCT RS.POLICY_NUMBER,
, MAX(RS.POLICYHOLDER_NAME)
, MAX(RS.POLICYHOLDER_FEDERAL_TAX_ID)
, MAX(RS.GOVERNING_CLASS_CODE)
FROM EER.POLICYHOLDER_RISK_STATE RS
JOIN EER.POLICY_TRANSACTION_FACT TF ON RS.POLICYHOLDER_RISK_ST_KEY = TF.POLICYHOLDER_RISK_ST_KEY
JOIN EER.POLICY PD ON TF.POLICY_KEY = PD.POLICY_KEY
WHERE (PD.POLICY_EFF_DATE - 60 <= SYSDATE AND
PD.POLICY_EXP_DATE + 60 >= SYSDATE AND
PD.FINAL_TERM_DATE IS NULL)
GROUP BY RS.POLICY_NUMBER'
SET @sql_str = N'select * from OPENQUERY('+@env+', ''' + REPLACE(@sql_str, '''', '''''') + ''')'
INSERT INTO dbo.ValidPolicyHolders
exec (@sql_str)
@env is passed to the procedure, in this case it is ‘DTEST’
Compile the procedure and try to execute it and I get:
OLE DB error trace [OLE/DB Provider ‘MSDAORA’ ITransactionJoin::JoinTransaction returned 0x8004d01b].
Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider ‘MSDAORA’ was unable to begin a distributed transaction.
After a bunch of internet searching and checking by the DBAs I try this:
SET @sql_str = 'INSERT INTO dbo.ValidPolicyHolders
select * from OPENQUERY('+@env+', ''' + REPLACE(@sql_str, '''', '''''') + ''')'
exec (@sql_str)
And the procedure now performs as expected.
