| Mike R 2006-02-26, 10:47 am |
| Hi All,
How can I change the provider of the SQL adapter to us MSDASQL instead of SQLOLEDB??? What is the format of the connection string?
Because.....
I have a simple test on a SBS2003 box. The server is called crmserver.
My T-SQL:
USE master
GO
EXEC sp_addlinkedserver
'MYLINK',
'',
'SQLOLEDB',
NULL,
NULL,
'DRIVER={SQL Server};SERVER=crmserver;Integrated Security=SSPI'
GO
begin distributed transaction
SELECT * FROM [MYLINK].pubs.dbo.authors
commit tran
GO
EXEC sp_dropserver 'MYLINK'
I then get this message:
Server: Msg 7391, Level 16, State 1, Line 3
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].
However, If change the provider to MSDASQL - it works.
I have (I think), followed the article about changing the DTC to "No Authentication Required" in the component services.
Could someone point me in the correct direction please? I need this to work with SQLOLEDB.
Thanks Mike
|