connecting to two adp projects error-

  • Thread starter Thread starter Brent
  • Start date Start date
B

Brent

ADO error: [OLE/DB provider returned message: New transaction cannot enlist
in the specified tansaction coordinator.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned Ox8004d00a].
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction

I got this error when using an MS Access Data Project to perform a query
that called two separate MSSQL databases, one of which was linked to the
other (I connected to one, then used "link tables" to get to the other).
How can I fix this?
 
Is the second database located on the same server? If so, create a
stored procedure in one database that initiates the transaction by
using the fully-qualified name of the resource in the second database:
databasename.schemaname.tablename. You can also create a view in
database1 if all you are doing is browsing data.

-mary
 
Mary
I think this answer relates to my problem. An ADP "DR" is built on a
database "Requests" on server A. Also on server A is a second database
"Users". At startup the ADP opens a second connection cnnUsers to retrieve
the User's ID based on the Windows Logon name.
If I understand you correctly you say to used a stored procedure in
"Requests" to retrieve the ID from "Users"?
The problem I am experiencing is that the second connection seems to work
for some users but not others but the error is different. I have been chasing
a security issue.
Terry

Mary Chipman said:
Is the second database located on the same server? If so, create a
stored procedure in one database that initiates the transaction by
using the fully-qualified name of the resource in the second database:
databasename.schemaname.tablename. You can also create a view in
database1 if all you are doing is browsing data.

-mary

ADO error: [OLE/DB provider returned message: New transaction cannot enlist
in the specified tansaction coordinator.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned Ox8004d00a].
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction

I got this error when using an MS Access Data Project to perform a query
that called two separate MSSQL databases, one of which was linked to the
other (I connected to one, then used "link tables" to get to the other).
How can I fix this?
Click to expand...
Click to expand...
 
I don't understand exactly what you are trying to do (what's a "DR"?),
but in general what you want to do is to perform as much work as
possible on the server instead of making multiple calls back and forth
from the client application. So whatever you can encapsulate inside of
a stored procedure is going to cut down on miscellaneous errors being
returned that you then have to handle in Access. It's also expensive
to have a whole bunch of calls going across the wire. You can
implement error handling to take care of all problems inside of the
T-SQL code in your stored procedure.

-mary

Mary
I think this answer relates to my problem. An ADP "DR" is built on a
database "Requests" on server A. Also on server A is a second database
"Users". At startup the ADP opens a second connection cnnUsers to retrieve
the User's ID based on the Windows Logon name.
If I understand you correctly you say to used a stored procedure in
"Requests" to retrieve the ID from "Users"?
The problem I am experiencing is that the second connection seems to work
for some users but not others but the error is different. I have been chasing
a security issue.
Terry

Mary Chipman said:
Is the second database located on the same server? If so, create a
stored procedure in one database that initiates the transaction by
using the fully-qualified name of the resource in the second database:
databasename.schemaname.tablename. You can also create a view in
database1 if all you are doing is browsing data.

-mary

ADO error: [OLE/DB provider returned message: New transaction cannot enlist
in the specified tansaction coordinator.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned Ox8004d00a].
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction

I got this error when using an MS Access Data Project to perform a query
that called two separate MSSQL databases, one of which was linked to the
other (I connected to one, then used "link tables" to get to the other).
How can I fix this?
Click to expand...
Click to expand...
Click to expand...
 
DR is the name of the App/ADP filename but got your drift.
We have an ADP which is linked to one database (Requests) but which has to
make a call on startup to a second database (Users) for extended user
identification. The problem has been found and, as originally suspected, it
was a Privileges issue
Your point of handling action on the server is taken.
Thanks
Terry

Mary Chipman said:
I don't understand exactly what you are trying to do (what's a "DR"?),
but in general what you want to do is to perform as much work as
possible on the server instead of making multiple calls back and forth
from the client application. So whatever you can encapsulate inside of
a stored procedure is going to cut down on miscellaneous errors being
returned that you then have to handle in Access. It's also expensive
to have a whole bunch of calls going across the wire. You can
implement error handling to take care of all problems inside of the
T-SQL code in your stored procedure.

-mary

Mary
I think this answer relates to my problem. An ADP "DR" is built on a
database "Requests" on server A. Also on server A is a second database
"Users". At startup the ADP opens a second connection cnnUsers to retrieve
the User's ID based on the Windows Logon name.
If I understand you correctly you say to used a stored procedure in
"Requests" to retrieve the ID from "Users"?
The problem I am experiencing is that the second connection seems to work
for some users but not others but the error is different. I have been chasing
a security issue.
Terry

Mary Chipman said:
Is the second database located on the same server? If so, create a
stored procedure in one database that initiates the transaction by
using the fully-qualified name of the resource in the second database:
databasename.schemaname.tablename. You can also create a view in
database1 if all you are doing is browsing data.

-mary

ADO error: [OLE/DB provider returned message: New transaction cannot enlist
in the specified tansaction coordinator.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned Ox8004d00a].
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction

I got this error when using an MS Access Data Project to perform a query
that called two separate MSSQL databases, one of which was linked to the
other (I connected to one, then used "link tables" to get to the other).
How can I fix this?
Click to expand...
Click to expand...
Click to expand...
 
Good morning of France
Via Mac Book.

Laurent of Taverny city


Le 7/08/07 1:22, dans (e-mail address removed),
« Terry » said:
DR is the name of the App/ADP filename but got your drift.
We have an ADP which is linked to one database (Requests) but which has to
make a call on startup to a second database (Users) for extended user
identification. The problem has been found and, as originally suspected, it
was a Privileges issue
Your point of handling action on the server is taken.
Thanks
Terry

Mary Chipman said:
I don't understand exactly what you are trying to do (what's a "DR"?),
but in general what you want to do is to perform as much work as
possible on the server instead of making multiple calls back and forth
from the client application. So whatever you can encapsulate inside of
a stored procedure is going to cut down on miscellaneous errors being
returned that you then have to handle in Access. It's also expensive
to have a whole bunch of calls going across the wire. You can
implement error handling to take care of all problems inside of the
T-SQL code in your stored procedure.

-mary

Mary
I think this answer relates to my problem. An ADP "DR" is built on a
database "Requests" on server A. Also on server A is a second database
"Users". At startup the ADP opens a second connection cnnUsers to retrieve
the User's ID based on the Windows Logon name.
If I understand you correctly you say to used a stored procedure in
"Requests" to retrieve the ID from "Users"?
The problem I am experiencing is that the second connection seems to work
for some users but not others but the error is different. I have been
chasing
a security issue.
Terry

:

Is the second database located on the same server? If so, create a
stored procedure in one database that initiates the transaction by
using the fully-qualified name of the resource in the second database:
databasename.schemaname.tablename. You can also create a view in
database1 if all you are doing is browsing data.

-mary

ADO error: [OLE/DB provider returned message: New transaction cannot
enlist
in the specified tansaction coordinator.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned Ox8004d00a].
The operation could not be performed because the OLE DB provider
'SQLOLEDB'
was unable to begin a distributed transaction

I got this error when using an MS Access Data Project to perform a query
that called two separate MSSQL databases, one of which was linked to the
other (I connected to one, then used "link tables" to get to the other).
How can I fix this?
Click to expand...
Click to expand...
Click to expand...

Envoyé avec Microsoft Entourage 2004 pour Mac - Version d'évaluation.
 
Mac! Never

Utilisateur de la version d' é valuation said:
Good morning of France
Via Mac Book.

Laurent of Taverny city


Le 7/08/07 1:22, dans (e-mail address removed),
« Terry » said:
DR is the name of the App/ADP filename but got your drift.
We have an ADP which is linked to one database (Requests) but which has to
make a call on startup to a second database (Users) for extended user
identification. The problem has been found and, as originally suspected, it
was a Privileges issue
Your point of handling action on the server is taken.
Thanks
Terry

Mary Chipman said:
I don't understand exactly what you are trying to do (what's a "DR"?),
but in general what you want to do is to perform as much work as
possible on the server instead of making multiple calls back and forth
from the client application. So whatever you can encapsulate inside of
a stored procedure is going to cut down on miscellaneous errors being
returned that you then have to handle in Access. It's also expensive
to have a whole bunch of calls going across the wire. You can
implement error handling to take care of all problems inside of the
T-SQL code in your stored procedure.

-mary

On Sun, 5 Aug 2007 06:34:00 -0700, Terry

Mary
I think this answer relates to my problem. An ADP "DR" is built on a
database "Requests" on server A. Also on server A is a second database
"Users". At startup the ADP opens a second connection cnnUsers to retrieve
the User's ID based on the Windows Logon name.
If I understand you correctly you say to used a stored procedure in
"Requests" to retrieve the ID from "Users"?
The problem I am experiencing is that the second connection seems to work
for some users but not others but the error is different. I have been
chasing
a security issue.
Terry

:

Is the second database located on the same server? If so, create a
stored procedure in one database that initiates the transaction by
using the fully-qualified name of the resource in the second database:
databasename.schemaname.tablename. You can also create a view in
database1 if all you are doing is browsing data.

-mary

ADO error: [OLE/DB provider returned message: New transaction cannot
enlist
in the specified tansaction coordinator.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned Ox8004d00a].
The operation could not be performed because the OLE DB provider
'SQLOLEDB'
was unable to begin a distributed transaction

I got this error when using an MS Access Data Project to perform a query
that called two separate MSSQL databases, one of which was linked to the
other (I connected to one, then used "link tables" to get to the other).
How can I fix this?
Click to expand...
Click to expand...

Envoyé avec Microsoft Entourage 2004 pour Mac - Version d'évaluation.
Click to expand...
 
Ah, yes, cross-database ownership chaining kicks in. There are
different server-side behaviors depending on which version of SQLS you
are using, starting with SQLS 2000 SP3. search "cross-database" or
"ownership chain" for more info.

-mary

DR is the name of the App/ADP filename but got your drift.
We have an ADP which is linked to one database (Requests) but which has to
make a call on startup to a second database (Users) for extended user
identification. The problem has been found and, as originally suspected, it
was a Privileges issue
Your point of handling action on the server is taken.
Thanks
Terry

Mary Chipman said:
I don't understand exactly what you are trying to do (what's a "DR"?),
but in general what you want to do is to perform as much work as
possible on the server instead of making multiple calls back and forth
from the client application. So whatever you can encapsulate inside of
a stored procedure is going to cut down on miscellaneous errors being
returned that you then have to handle in Access. It's also expensive
to have a whole bunch of calls going across the wire. You can
implement error handling to take care of all problems inside of the
T-SQL code in your stored procedure.

-mary

Mary
I think this answer relates to my problem. An ADP "DR" is built on a
database "Requests" on server A. Also on server A is a second database
"Users". At startup the ADP opens a second connection cnnUsers to retrieve
the User's ID based on the Windows Logon name.
If I understand you correctly you say to used a stored procedure in
"Requests" to retrieve the ID from "Users"?
The problem I am experiencing is that the second connection seems to work
for some users but not others but the error is different. I have been chasing
a security issue.
Terry

:

Is the second database located on the same server? If so, create a
stored procedure in one database that initiates the transaction by
using the fully-qualified name of the resource in the second database:
databasename.schemaname.tablename. You can also create a view in
database1 if all you are doing is browsing data.

-mary

ADO error: [OLE/DB provider returned message: New transaction cannot enlist
in the specified tansaction coordinator.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned Ox8004d00a].
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction

I got this error when using an MS Access Data Project to perform a query
that called two separate MSSQL databases, one of which was linked to the
other (I connected to one, then used "link tables" to get to the other).
How can I fix this?
Click to expand...
Click to expand...
Click to expand...
 
SQL Server 2000 with all SPs
Thanks Mary

Mary Chipman said:
Ah, yes, cross-database ownership chaining kicks in. There are
different server-side behaviors depending on which version of SQLS you
are using, starting with SQLS 2000 SP3. search "cross-database" or
"ownership chain" for more info.

-mary

DR is the name of the App/ADP filename but got your drift.
We have an ADP which is linked to one database (Requests) but which has to
make a call on startup to a second database (Users) for extended user
identification. The problem has been found and, as originally suspected, it
was a Privileges issue
Your point of handling action on the server is taken.
Thanks
Terry

Mary Chipman said:
I don't understand exactly what you are trying to do (what's a "DR"?),
but in general what you want to do is to perform as much work as
possible on the server instead of making multiple calls back and forth
from the client application. So whatever you can encapsulate inside of
a stored procedure is going to cut down on miscellaneous errors being
returned that you then have to handle in Access. It's also expensive
to have a whole bunch of calls going across the wire. You can
implement error handling to take care of all problems inside of the
T-SQL code in your stored procedure.

-mary

On Sun, 5 Aug 2007 06:34:00 -0700, Terry

Mary
I think this answer relates to my problem. An ADP "DR" is built on a
database "Requests" on server A. Also on server A is a second database
"Users". At startup the ADP opens a second connection cnnUsers to retrieve
the User's ID based on the Windows Logon name.
If I understand you correctly you say to used a stored procedure in
"Requests" to retrieve the ID from "Users"?
The problem I am experiencing is that the second connection seems to work
for some users but not others but the error is different. I have been chasing
a security issue.
Terry

:

Is the second database located on the same server? If so, create a
stored procedure in one database that initiates the transaction by
using the fully-qualified name of the resource in the second database:
databasename.schemaname.tablename. You can also create a view in
database1 if all you are doing is browsing data.

-mary

ADO error: [OLE/DB provider returned message: New transaction cannot enlist
in the specified tansaction coordinator.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned Ox8004d00a].
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction

I got this error when using an MS Access Data Project to perform a query
that called two separate MSSQL databases, one of which was linked to the
other (I connected to one, then used "link tables" to get to the other).
How can I fix this?
Click to expand...
Click to expand...
Click to expand...
 
Then cross-database ownership chaining is off by default. See
http://msdn2.microsoft.com/en-us/library/aa196729(SQL.80).aspx, which
describes the situation and how to work around it. Also see Erland
Sommarskog's web site - he does a great job of explaining what's going
on and what your options are.
http://www.sommarskog.se/grantperm.html#ownershipchaining

-mary

SQL Server 2000 with all SPs
Thanks Mary

Mary Chipman said:
Ah, yes, cross-database ownership chaining kicks in. There are
different server-side behaviors depending on which version of SQLS you
are using, starting with SQLS 2000 SP3. search "cross-database" or
"ownership chain" for more info.

-mary

DR is the name of the App/ADP filename but got your drift.
We have an ADP which is linked to one database (Requests) but which has to
make a call on startup to a second database (Users) for extended user
identification. The problem has been found and, as originally suspected, it
was a Privileges issue
Your point of handling action on the server is taken.
Thanks
Terry

:

I don't understand exactly what you are trying to do (what's a "DR"?),
but in general what you want to do is to perform as much work as
possible on the server instead of making multiple calls back and forth
from the client application. So whatever you can encapsulate inside of
a stored procedure is going to cut down on miscellaneous errors being
returned that you then have to handle in Access. It's also expensive
to have a whole bunch of calls going across the wire. You can
implement error handling to take care of all problems inside of the
T-SQL code in your stored procedure.

-mary

On Sun, 5 Aug 2007 06:34:00 -0700, Terry

Mary
I think this answer relates to my problem. An ADP "DR" is built on a
database "Requests" on server A. Also on server A is a second database
"Users". At startup the ADP opens a second connection cnnUsers to retrieve
the User's ID based on the Windows Logon name.
If I understand you correctly you say to used a stored procedure in
"Requests" to retrieve the ID from "Users"?
The problem I am experiencing is that the second connection seems to work
for some users but not others but the error is different. I have been chasing
a security issue.
Terry

:

Is the second database located on the same server? If so, create a
stored procedure in one database that initiates the transaction by
using the fully-qualified name of the resource in the second database:
databasename.schemaname.tablename. You can also create a view in
database1 if all you are doing is browsing data.

-mary

ADO error: [OLE/DB provider returned message: New transaction cannot enlist
in the specified tansaction coordinator.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned Ox8004d00a].
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction

I got this error when using an MS Access Data Project to perform a query
that called two separate MSSQL databases, one of which was linked to the
other (I connected to one, then used "link tables" to get to the other).
How can I fix this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top