OLE DB error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

OLE DB error

Author

Topic

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-03-10 : 23:23:04

Hi, I am executing dinamic sql to select data from database A and insert into database B within same server. It is running in
testing server, but when I move it to production server, I get error (in red). Why could this happen and how to resolve it??

The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].


SET @nsql = 'INSERT INTO ' + quotename(@ArchiveSvr) + '.' + @ArchiveDBName + '.' + @ObjOwnerTemp + '.' + @TableNameTemp +
' SELECT * FROM ' + quotename(@ActiveSvr) + '.' + @ActiveDBName + '.' + @ObjOwner + '.' + @TableName +
'  WHERE ' + @DateRef + ' < ''' + CONVERT(VARCHAR(10),@RecordMaxDt,112) + ''' SELECT @RowCount = @@RowCount';
								
EXEC sp_executesql @nsql, N'@RowCount INT OUTPUT', @RowCount = @RowCount OUTPUT;

SELECT @RowCount [@@RowCount]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-11 : 07:10:05

WHere were you trying to execute this? In Query Analyser or from some application?

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-03-11 : 07:14:00

Hi,

try placing ; before SELECT @ROWCOUNT = @@ROWCOUNT IN @Nsql
OR remove that from @nsql and after executing @nsql
asign value SELECT @RowCount = @@RowCount

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-03-11 : 10:57:46

quote:
Originally posted by visakh16

WHere were you trying to execute this? In Query Analyser or from some application?

I am executing this in .Net

I found that when I change my query from

INSERT INTO [SERVER1\INSTANCE1].DATABASE1.dbo.TABLE1
SELECT * FROM [SERVER1\INSTANCE1].DATABASE2.dbo.TABLE2

INSERT INTO DATABASE1.dbo.TABLE1
SELECT * FROM DATABASE2.dbo.TABLE2

i will not get the error. I tested this on 4 servers, the first code success running in 3 servers, but fail in 1 server with error ....it is strange!! I am searching the info for few days but nothing helps!!

System.Data.SqlClient.SqlException: The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources