Linked Server Problem

Hi!

I have written a Perl Program, which works on a MS SQL SERVER 2000 database. It should import some data from an Oracle8i database. so i created a linked server and a linked server login. There were no problems when i created these things. But if i execute a "select ..."-Statement at the linked server(Oracle), i get the following message:

"[Microsoft][ODBC SQL Server Driver][SQL Server]Die Operation konnte nicht ausgefhrt werden, da der OLE DB-Provider 'MSDAORA' keine verteilte Transaktion beginnen konnte. (SQL-42000)
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB-Fehlertrace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b]. (SQL-01000)(DBD: st_execute/SQLExecute err=-1),ERROR = -1"

But the select statement, i used is ok. i started it in the Query Analyzer and everything was fine! This is the statement:

"select * from openquery(SRCLINKWORLD,'SELECT COUNT(*) AS ANZAHL FROM DBADMIN.ABSCHLUSSABR')"

Does anyone know , what the problem is?

NickWell...I know very little German (it is German isn't it), but what login did you use to connect with?

Is it a remote login in Oracle?

This is purely a shot in the dark...|||Strange SELECT you use,

why

"select * from openquery(SRCLINKWORLD,'SELECT COUNT(*) AS ANZAHL FROM DBADMIN.ABSCHLUSSABR')"

instead of

SELECT COUNT(*) AS ANZAHL FROM DBADMIN.ABSCHLUSSABR

Linked Server and unable to begin a distributed transaction

I have a database containing my own tables and data and I wanted to
be able to query this against an accountancy program which has an ODBC
driver. This was never a problem with MS Access and Jet but I hit Jet's
limitations and have moved to SQL.
Creating my own SQL database was no problem, but I was unsure of the
best way to be able to be able to have my SQL tables and my accountancy
software tables appearing in the same Access front end.
I created a linked server to the accountancy program. This was
successful in that I could see all the tables below the linked server in
enterprise manager.
My problem occurs when I try to bring the data from these tables
into my SQL database.
I create a new view in my database:-

SELECT *
FROM OPENQUERY(SAGE_SERVER, 'SELECT * FROM STOCK')

(My linked server is called 'SAGE_SERVER' and I am trying to retrieve
all columns from the STOCK table.)

I then try to save this view and get the following errors.

ODBC Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'MSDASQL' was unable
to begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB Error Trace[OLE/DB
Provider 'MSDASQL' ITransactionJoi JoinTransaction returned 0x8004d00a].

Thanks in advance,

Marcus Thornton."Marcus" <marcus@.automint.co.uk> wrote in message
news:MPG.19748ac86a8335b3989681@.192.168.1.50...
> I have a database containing my own tables and data and I wanted to
> be able to query this against an accountancy program which has an ODBC
> driver. This was never a problem with MS Access and Jet but I hit Jet's
> limitations and have moved to SQL.
> Creating my own SQL database was no problem, but I was unsure of the
> best way to be able to be able to have my SQL tables and my accountancy
> software tables appearing in the same Access front end.
> I created a linked server to the accountancy program. This was
> successful in that I could see all the tables below the linked server in
> enterprise manager.
> My problem occurs when I try to bring the data from these tables
> into my SQL database.
> I create a new view in my database:-
> SELECT *
> FROM OPENQUERY(SAGE_SERVER, 'SELECT * FROM STOCK')
> (My linked server is called 'SAGE_SERVER' and I am trying to retrieve
> all columns from the STOCK table.)
> I then try to save this view and get the following errors.
> ODBC Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> could not be performed because the OLE DB provider 'MSDASQL' was unable
> to begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB Error Trace[OLE/DB
> Provider 'MSDASQL' ITransactionJoi JoinTransaction returned 0x8004d00a].
> Thanks in advance,
> Marcus Thornton.

You might find this post (and the rest of the thread) useful - it has some
quite detailed information on troubleshooting distributed transaction
errors:

http://groups.google.com/groups?hl=...%40cpmsftngxa07

Simon

Linked Server & SQL Auth

I am stuck... I am trying to use a linked server in sql 2005 (xls sheet), it works great using windows authentication but since the program will not be running locally i would prefer to use sql auth...

when i log in using the sql name i have created. it only has connect & execute permissions on the main database (as it should), but i also need to query this linked server, when i try to query it i get authentication failed. how do i fix that?

the xls is on a fat32 partition and can be moved to an ntfs partition with out any problems.

thanks in advance...not sure if this is the problem, but have you granted read permissions to the database (e.g. db_datareader role)?|||no i have not, i thought that would give to much power to that user and allow it access to other databases..if that is false please let me know..|||The db_datareader database role is for only within the database it is granted. So you would only be granting read access to tables within the specified database that the public role does not already allow read access to.|||i do not have these options on a linked server so... what is next.