I have two computers both running Windows Server 2003 and SQL Server 2000. I
have a trigger on a table located on the first computer which includes a line
of code that inserts into a table on the second computer. The two computers
are set up as linked servers (without loopback).
The code used to work so that when you insert into the table of the first
computer, the trigger ran properly and inserted into the second computer.
However, after I installed SP3 for SQL Server 2000 to fix another bug, the
trigger stopped working properly. Every time i try to run the code, it
generates this error message:
Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
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].
I have not touched any of the code, so something in terms of the setup must
have changed so that it doesn't work anymore, how ever I have no idea what is
wrong.
After reading the many threads about this I have checked the following items:
1) DTC is running properly on both computers
2) Setup within the component services with Network DTC Access and
NetworkService for log on Account are all correct
3) I call SET XACT_ABORT ON before the insert statement
4) The servers are not on a cluster
5) The server names are correct based on checking with @.@.servername
6) The servers can talk to each other by name and not just IP Addresses.
7) The line of code that fails in the trigger works properly when it is run
on the second computer
8) The line of code that fails in the trigger also works properly when
copied and pasted into a stored procedure of the first computer and run over
the linked server
9) Lastly, the line of code is an insert call, but when I change it to a
select call within the trigger for testing, it also produces the same error.
So essentially I can run the code as a stored procedure but not as a trigger.
If you need any other information, please let me know. Please help me fix
this problem. Thank you.
See if any of these links help. It may be a problem with MS DTC being
disabled in WIN 2003 by default.
http://support.microsoft.com/default...b;en-us;817064
http://support.microsoft.com/?id=827805
http://support.microsoft.com/default.aspx?kbid=329332
"Zwyatt" wrote:
> I have two computers both running Windows Server 2003 and SQL Server 2000. I
> have a trigger on a table located on the first computer which includes a line
> of code that inserts into a table on the second computer. The two computers
> are set up as linked servers (without loopback).
> The code used to work so that when you insert into the table of the first
> computer, the trigger ran properly and inserted into the second computer.
> However, after I installed SP3 for SQL Server 2000 to fix another bug, the
> trigger stopped working properly. Every time i try to run the code, it
> generates this error message:
> Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
> 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].
> I have not touched any of the code, so something in terms of the setup must
> have changed so that it doesn't work anymore, how ever I have no idea what is
> wrong.
> After reading the many threads about this I have checked the following items:
> 1) DTC is running properly on both computers
> 2) Setup within the component services with Network DTC Access and
> NetworkService for log on Account are all correct
> 3) I call SET XACT_ABORT ON before the insert statement
> 4) The servers are not on a cluster
> 5) The server names are correct based on checking with @.@.servername
> 6) The servers can talk to each other by name and not just IP Addresses.
> 7) The line of code that fails in the trigger works properly when it is run
> on the second computer
> 8) The line of code that fails in the trigger also works properly when
> copied and pasted into a stored procedure of the first computer and run over
> the linked server
> 9) Lastly, the line of code is an insert call, but when I change it to a
> select call within the trigger for testing, it also produces the same error.
> So essentially I can run the code as a stored procedure but not as a trigger.
> If you need any other information, please let me know. Please help me fix
> this problem. Thank you.
Showing posts with label located. Show all posts
Showing posts with label located. Show all posts
Friday, March 30, 2012
linked server problem with error message 7391
I have two computers both running Windows Server 2003 and SQL Server 2000.
I
have a trigger on a table located on the first computer which includes a lin
e
of code that inserts into a table on the second computer. The two computers
are set up as linked servers (without loopback).
The code used to work so that when you insert into the table of the first
computer, the trigger ran properly and inserted into the second computer.
However, after I installed SP3 for SQL Server 2000 to fix another bug, the
trigger stopped working properly. Every time i try to run the code, it
generates this error message:
Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
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].
I have not touched any of the code, so something in terms of the setup must
have changed so that it doesn't work anymore, how ever I have no idea what i
s
wrong.
After reading the many threads about this I have checked the following items
:
1) DTC is running properly on both computers
2) Setup within the component services with Network DTC Access and
NetworkService for log on Account are all correct
3) I call SET XACT_ABORT ON before the insert statement
4) The servers are not on a cluster
5) The server names are correct based on checking with @.@.servername
6) The servers can talk to each other by name and not just IP Addresses.
7) The line of code that fails in the trigger works properly when it is run
on the second computer
8) The line of code that fails in the trigger also works properly when
copied and pasted into a stored procedure of the first computer and run over
the linked server
9) Lastly, the line of code is an insert call, but when I change it to a
select call within the trigger for testing, it also produces the same error.
So essentially I can run the code as a stored procedure but not as a trigger
.
If you need any other information, please let me know. Please help me fix
this problem. Thank you.See if any of these links help. It may be a problem with MS DTC being
disabled in WIN 2003 by default.
http://support.microsoft.com/defaul...kb;en-us;817064
http://support.microsoft.com/?id=827805
http://support.microsoft.com/default.aspx?kbid=329332
"Zwyatt" wrote:
> I have two computers both running Windows Server 2003 and SQL Server 2000.
I
> have a trigger on a table located on the first computer which includes a l
ine
> of code that inserts into a table on the second computer. The two compute
rs
> are set up as linked servers (without loopback).
> The code used to work so that when you insert into the table of the first
> computer, the trigger ran properly and inserted into the second computer.
> However, after I installed SP3 for SQL Server 2000 to fix another bug, the
> trigger stopped working properly. Every time i try to run the code, it
> generates this error message:
> Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
> 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 th
e
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> I have not touched any of the code, so something in terms of the setup mus
t
> have changed so that it doesn't work anymore, how ever I have no idea what
is
> wrong.
> After reading the many threads about this I have checked the following ite
ms:
> 1) DTC is running properly on both computers
> 2) Setup within the component services with Network DTC Access and
> NetworkService for log on Account are all correct
> 3) I call SET XACT_ABORT ON before the insert statement
> 4) The servers are not on a cluster
> 5) The server names are correct based on checking with @.@.servername
> 6) The servers can talk to each other by name and not just IP Addresses.
> 7) The line of code that fails in the trigger works properly when it is r
un
> on the second computer
> 8) The line of code that fails in the trigger also works properly when
> copied and pasted into a stored procedure of the first computer and run ov
er
> the linked server
> 9) Lastly, the line of code is an insert call, but when I change it to a
> select call within the trigger for testing, it also produces the same erro
r.
> So essentially I can run the code as a stored procedure but not as a trigg
er.
> If you need any other information, please let me know. Please help me fix
> this problem. Thank you.
I
have a trigger on a table located on the first computer which includes a lin
e
of code that inserts into a table on the second computer. The two computers
are set up as linked servers (without loopback).
The code used to work so that when you insert into the table of the first
computer, the trigger ran properly and inserted into the second computer.
However, after I installed SP3 for SQL Server 2000 to fix another bug, the
trigger stopped working properly. Every time i try to run the code, it
generates this error message:
Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
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].
I have not touched any of the code, so something in terms of the setup must
have changed so that it doesn't work anymore, how ever I have no idea what i
s
wrong.
After reading the many threads about this I have checked the following items
:
1) DTC is running properly on both computers
2) Setup within the component services with Network DTC Access and
NetworkService for log on Account are all correct
3) I call SET XACT_ABORT ON before the insert statement
4) The servers are not on a cluster
5) The server names are correct based on checking with @.@.servername
6) The servers can talk to each other by name and not just IP Addresses.
7) The line of code that fails in the trigger works properly when it is run
on the second computer
8) The line of code that fails in the trigger also works properly when
copied and pasted into a stored procedure of the first computer and run over
the linked server
9) Lastly, the line of code is an insert call, but when I change it to a
select call within the trigger for testing, it also produces the same error.
So essentially I can run the code as a stored procedure but not as a trigger
.
If you need any other information, please let me know. Please help me fix
this problem. Thank you.See if any of these links help. It may be a problem with MS DTC being
disabled in WIN 2003 by default.
http://support.microsoft.com/defaul...kb;en-us;817064
http://support.microsoft.com/?id=827805
http://support.microsoft.com/default.aspx?kbid=329332
"Zwyatt" wrote:
> I have two computers both running Windows Server 2003 and SQL Server 2000.
I
> have a trigger on a table located on the first computer which includes a l
ine
> of code that inserts into a table on the second computer. The two compute
rs
> are set up as linked servers (without loopback).
> The code used to work so that when you insert into the table of the first
> computer, the trigger ran properly and inserted into the second computer.
> However, after I installed SP3 for SQL Server 2000 to fix another bug, the
> trigger stopped working properly. Every time i try to run the code, it
> generates this error message:
> Server: Msg 7391, Level 16, State 1, Procedure UserSongAddition, Line 20
> 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 th
e
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> I have not touched any of the code, so something in terms of the setup mus
t
> have changed so that it doesn't work anymore, how ever I have no idea what
is
> wrong.
> After reading the many threads about this I have checked the following ite
ms:
> 1) DTC is running properly on both computers
> 2) Setup within the component services with Network DTC Access and
> NetworkService for log on Account are all correct
> 3) I call SET XACT_ABORT ON before the insert statement
> 4) The servers are not on a cluster
> 5) The server names are correct based on checking with @.@.servername
> 6) The servers can talk to each other by name and not just IP Addresses.
> 7) The line of code that fails in the trigger works properly when it is r
un
> on the second computer
> 8) The line of code that fails in the trigger also works properly when
> copied and pasted into a stored procedure of the first computer and run ov
er
> the linked server
> 9) Lastly, the line of code is an insert call, but when I change it to a
> select call within the trigger for testing, it also produces the same erro
r.
> So essentially I can run the code as a stored procedure but not as a trigg
er.
> If you need any other information, please let me know. Please help me fix
> this problem. Thank you.
Monday, March 12, 2012
Linked server connection to MS Access db across the network failed
I have attempted to create a linked server on Server1 to an Access db on
Server2.
These servers are located on the same network.
The account running the SQL Server services on Sever 1 is a domain admin
account
I can open the file via explorer across the network using the same services
account.
The provider name I used is Microsoft.Jet.OLEDB.4.0.
The data source I used is \\(ip address)\c$\…\accessdb.mdb
I have tried with and without a security context of admin with no password.
I received the following error…
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file '\\(server)\c$\…\ accessdb.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
I realize this an issue of permissions. I am just not sure where the failure
is taking place, what the cause is, or even how to analyze it.
Also I am able to create the linked server if the access database is located
on Server1 with SQL Server. However this is not the scenario I am trying to
solution.
Please advise.
Thanks.
As I've referred in other posts ensure you've using correct version of Jet,
refer to KBA http://support.microsoft.com/kb/818182 and
http://support.microsoft.com/default.aspx/kb/241267 link.
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"IT4CABLETV" wrote:
> I have attempted to create a linked server on Server1 to an Access db on
> Server2.
> These servers are located on the same network.
> The account running the SQL Server services on Sever 1 is a domain admin
> account
> I can open the file via explorer across the network using the same services
> account.
> The provider name I used is Microsoft.Jet.OLEDB.4.0.
> The data source I used is \\(ip address)\c$\…\accessdb.mdb
> I have tried with and without a security context of admin with no password.
> I received the following error…
> Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: The Microsoft Jet database engine cannot
> open the file '\\(server)\c$\…\ accessdb.mdb'. It is already opened
> exclusively by another user, or you need permission to view its data.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
> I realize this an issue of permissions. I am just not sure where the failure
> is taking place, what the cause is, or even how to analyze it.
> Also I am able to create the linked server if the access database is located
> on Server1 with SQL Server. However this is not the scenario I am trying to
> solution.
> Please advise.
> Thanks.
Server2.
These servers are located on the same network.
The account running the SQL Server services on Sever 1 is a domain admin
account
I can open the file via explorer across the network using the same services
account.
The provider name I used is Microsoft.Jet.OLEDB.4.0.
The data source I used is \\(ip address)\c$\…\accessdb.mdb
I have tried with and without a security context of admin with no password.
I received the following error…
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file '\\(server)\c$\…\ accessdb.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
I realize this an issue of permissions. I am just not sure where the failure
is taking place, what the cause is, or even how to analyze it.
Also I am able to create the linked server if the access database is located
on Server1 with SQL Server. However this is not the scenario I am trying to
solution.
Please advise.
Thanks.
As I've referred in other posts ensure you've using correct version of Jet,
refer to KBA http://support.microsoft.com/kb/818182 and
http://support.microsoft.com/default.aspx/kb/241267 link.
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"IT4CABLETV" wrote:
> I have attempted to create a linked server on Server1 to an Access db on
> Server2.
> These servers are located on the same network.
> The account running the SQL Server services on Sever 1 is a domain admin
> account
> I can open the file via explorer across the network using the same services
> account.
> The provider name I used is Microsoft.Jet.OLEDB.4.0.
> The data source I used is \\(ip address)\c$\…\accessdb.mdb
> I have tried with and without a security context of admin with no password.
> I received the following error…
> Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: The Microsoft Jet database engine cannot
> open the file '\\(server)\c$\…\ accessdb.mdb'. It is already opened
> exclusively by another user, or you need permission to view its data.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
> I realize this an issue of permissions. I am just not sure where the failure
> is taking place, what the cause is, or even how to analyze it.
> Also I am able to create the linked server if the access database is located
> on Server1 with SQL Server. However this is not the scenario I am trying to
> solution.
> Please advise.
> Thanks.
Linked server connection to MS Access db across the network failed
I have attempted to create a linked server on Server1 to an Access db on
Server2.
These servers are located on the same network.
The account running the SQL Server services on Sever 1 is a domain admin
account
I can open the file via explorer across the network using the same services
account.
The provider name I used is Microsoft.Jet.OLEDB.4.0.
The data source I used is \\(ip address)\c$\…\accessdb.mdb
I have tried with and without a security context of admin with no password.
I received the following error…
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine can
not
open the file '\\(server)\c$\…\ accessdb.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
I realize this an issue of permissions. I am just not sure where the failure
is taking place, what the cause is, or even how to analyze it.
Also I am able to create the linked server if the access database is located
on Server1 with SQL Server. However this is not the scenario I am trying to
solution.
Please advise.
Thanks.As I've referred in other posts ensure you've using correct version of Jet,
refer to KBA http://support.microsoft.com/kb/818182 and
http://support.microsoft.com/default.aspx/kb/241267 link.
--
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"IT4CABLETV" wrote:
> I have attempted to create a linked server on Server1 to an Access db on
> Server2.
> These servers are located on the same network.
> The account running the SQL Server services on Sever 1 is a domain admin
> account
> I can open the file via explorer across the network using the same service
s
> account.
> The provider name I used is Microsoft.Jet.OLEDB.4.0.
> The data source I used is \\(ip address)\c$\…\accessdb.mdb
> I have tried with and without a security context of admin with no password
.
> I received the following error…
> Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: The Microsoft Jet database engine c
annot
> open the file '\\(server)\c$\…\ accessdb.mdb'. It is already opened
> exclusively by another user, or you need permission to view its data.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
> I realize this an issue of permissions. I am just not sure where the failu
re
> is taking place, what the cause is, or even how to analyze it.
> Also I am able to create the linked server if the access database is locat
ed
> on Server1 with SQL Server. However this is not the scenario I am trying t
o
> solution.
> Please advise.
> Thanks.
Server2.
These servers are located on the same network.
The account running the SQL Server services on Sever 1 is a domain admin
account
I can open the file via explorer across the network using the same services
account.
The provider name I used is Microsoft.Jet.OLEDB.4.0.
The data source I used is \\(ip address)\c$\…\accessdb.mdb
I have tried with and without a security context of admin with no password.
I received the following error…
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine can
not
open the file '\\(server)\c$\…\ accessdb.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
I realize this an issue of permissions. I am just not sure where the failure
is taking place, what the cause is, or even how to analyze it.
Also I am able to create the linked server if the access database is located
on Server1 with SQL Server. However this is not the scenario I am trying to
solution.
Please advise.
Thanks.As I've referred in other posts ensure you've using correct version of Jet,
refer to KBA http://support.microsoft.com/kb/818182 and
http://support.microsoft.com/default.aspx/kb/241267 link.
--
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"IT4CABLETV" wrote:
> I have attempted to create a linked server on Server1 to an Access db on
> Server2.
> These servers are located on the same network.
> The account running the SQL Server services on Sever 1 is a domain admin
> account
> I can open the file via explorer across the network using the same service
s
> account.
> The provider name I used is Microsoft.Jet.OLEDB.4.0.
> The data source I used is \\(ip address)\c$\…\accessdb.mdb
> I have tried with and without a security context of admin with no password
.
> I received the following error…
> Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: The Microsoft Jet database engine c
annot
> open the file '\\(server)\c$\…\ accessdb.mdb'. It is already opened
> exclusively by another user, or you need permission to view its data.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
> I realize this an issue of permissions. I am just not sure where the failu
re
> is taking place, what the cause is, or even how to analyze it.
> Also I am able to create the linked server if the access database is locat
ed
> on Server1 with SQL Server. However this is not the scenario I am trying t
o
> solution.
> Please advise.
> Thanks.
Subscribe to:
Posts (Atom)