473,581 Members | 2,567 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.
Jul 20 '05 #1
1 39144
"Marcus" <ma****@automin t.co.uk> wrote in message
news:MP******** *************** *@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=e...40cpmsftngxa07

Simon
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
18466
by: Steve Thorpe | last post by:
Hi have have two linked SQL Servers and I am trying to get things working smootly/quickly. Should I be using 'BEGIN TRANSACTION' or 'BEGIN DISTRIBUTED TRANSACTION' ? Basicly, these SPs update a local table and a remote table in the same transaction. I cant have one table updated and not the other. Please dont say replicate the tables...
0
2448
by: DotNetJunkies User | last post by:
I am writing a distributed transaction code. My current scenario include a client database(Suppose client- having 4 main database) which can be installed anywhere which would connect to a public database placed at ISP datacentre(Suppose Server- having 2 database). this server database is added to client as linked server through vb.net interface....
0
2591
by: Raja | last post by:
I have a Linked Server i.e. my SQL Server 2000 is Linked to the Oracle Database. The Link is perfect and dont have any problems in accessing the data from SQL Server 2000. The only problem i am facing is when i am trying to initiate a Distributed transaction. A simple Insert Query is working fine but when i try to perform the same through a...
2
11727
by: Elvira Zeinalova | last post by:
Hei, We have 2 MS SQL SERVER 2000 installed on 2 different servers (2 separated machines). I am triing to connect them så that when one row is added to the table in the database in main server - then the same row is added to the same table in the second server database. I made the insert trigger on the table in the first server ( the second...
1
6769
by: ammar_fake | last post by:
Hello! I have an Oracle linked server connected through MSDAORA. Linked server queries work perfectly - the "openquery" ones as well as the 4-part-named ones. The problem I have is with embedding the queries within SQL Server triggers. Trigger: CREATE TRIGGER tgTest ON .
0
3009
by: gshawn3 | last post by:
Hi, I am having a hard time creating a Trigger to update an Oracle database. I am using a SQL Server 2005 Express database on a Win XP Pro SP2 desktop, linked to an Oracle 10g database on a remote Windows 2003 server. Both machines are on the same domain and very close physically (<1ms ping). I have set up the Oracle linked server in...
0
2664
by: na | last post by:
My ASP.Net 2.0 web site has a Data.xsd schema. I just added a table adapter to the collection and configured it to use an existing SQL 2000 stored proc. When I tried to preview data from the table adapter, I got "SQLOLEDB was unable to begin a distributed transaction" error. The stored proc joins SQL 2000 tables via three linked servers. ...
2
9245
by: rbg | last post by:
Hi, On My local SQL server I have added a linked server to another SQL server (remoteserver) in another Windows NT Domain. When I run this code select count(*) from remoteserver.mosaics.dbo.Location This works fine. However when I use
0
1782
by: Matik | last post by:
Hi, MSSQL 2000 booth servers. Booth running DTC. Now, the client application, is starting in DB1 a procedure. The connection open to db, is within the transation opened from client. In the SP, I'm starting the transaction as well, with savepoint.
0
7857
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8137
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8299
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7890
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6545
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3799
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3813
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1397
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1127
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.