Distributed transaction problem

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 2000 Forums

Transact-SQL (2000)

Distributed transaction problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-06-28 : 10:42:46

nauman writes "Hi everybody,

When i run this query

Select * into #user_tbl from OpenDataSource('SQLOLEDB','DataSource=DBServer;User ID=sa;Password=sa').USERDB.dbo.user_tbl

from a Query Analyzer window it runs fine.

However when i use the same statement in a stored procedure using dynamic sql like this:

Create Procedure pdsW_config_user

@from_db_server varchar(256)
,@from_db_name varchar(256)
,@from_db_user varchar(256)
,@from_db_pwd varchar(256)

DECLARE @strSQL varchar(8000),
@err int,@from_db varchar(1500)

Select @from_db='OpenDataSource('+char(39)+'SQLOLEDB'+char(39)+','+char(39)+'DataSource='+@from_db_server+';'
+'User ID='+@from_db_user+';Password='+@from_db_pwd+char(39)+').'+@from_db_name+'.dbo.'

BEGIN TRANSACTION CONFIG

SELECT @strSQL = 'insert into #user_tbl select * from '+@from_db+'User_tbl'

EXEC (@strSQL)

COMMIT TRANSACTION CONFIG

GO

I get this error:

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].

The server has windows 2000 sp4 and sql 2000 sp4.

what could be the reason for this? and what is the solution to it?

nauman"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources