instcat is used to update the catalog SPs which get the database schemas for various things so would have something to do with the area you have problems.
Are both servers sp2?
What do you mean when you say 'write any select query from one server to another' - a query over a linked server?
I would suspect that you have ended up with incompatible servers in that case or one has some corruption - maybe a failed service pack. The best solution is probably to make sure all servers are at the same level.Yes. Both servers are having SQL 7 SP2 and MDAC 2.5 SP1 version (This config is forced to keep since our major clients are having same version). both service packs didn't resulted any errors during installation.
I am executing a select statement from server 1 to server 2
for ex. following query running from server 2. the database restored a copy from server 2.
select col1, col2 from server1.database.dbo.tablename
if I execute from server 1 the following query is working fine.
select col1, col2 from server2.database.dbo.tablename
Please let me know if you have any answer. Thanks for your kind help.
Showing posts with label update. Show all posts
Showing posts with label update. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Linked server problem
Hi All,
I have set up linked server and ..Server A & Server B
from Server B..I select from SERVER A and Update on SERVER B. It workd great
but when i put trigger on SERVER B on the same table I'm updating it gives
me below error...I have checked all basic setting like DTC & logins and ever
ything looks fine
This is not even distributed tran as I'm not updating two DB as same time ju
st selectting from one and updating other
ERROR
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::JoinTra
nsaction returned 0x8004d00a].
Any help AppreciatedDo you have Distrubuted transaction coordinator running?|||Yes, DTS is running..I have checked all basic stuff
I have set up linked server and ..Server A & Server B
from Server B..I select from SERVER A and Update on SERVER B. It workd great
but when i put trigger on SERVER B on the same table I'm updating it gives
me below error...I have checked all basic setting like DTC & logins and ever
ything looks fine
This is not even distributed tran as I'm not updating two DB as same time ju
st selectting from one and updating other
ERROR
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::JoinTra
nsaction returned 0x8004d00a].
Any help AppreciatedDo you have Distrubuted transaction coordinator running?|||Yes, DTS is running..I have checked all basic stuff
linked server problem
I am running trigger with update on a linked server in SQL 2005 SP2. It is
running very smooth and when I turn on the firewall, the trigger action is
giving error and failed to take action. Is this a Big NO between firewall
and Linked server? Is there a way to solve this problem? Thanks.You simply need to ensure the appropriate ports are open on the firewall, at
least between the two servers of concern. A simple packet capture while the
trigger is firing should inform you as to what needs to be opened up. At a
minimum it should be TCP 1433 and probably 1434. I don't know if other's
are involved in remote proc executions or not.
TheSQLGuru
President
Indicium Resources, Inc.
" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>running very smooth and when I turn on the firewall, the trigger action is
>giving error and failed to take action. Is this a Big NO between firewall
>and Linked server? Is there a way to solve this problem? Thanks.
>|||What is the mechanism to capture the packet? Thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:ec5p9z1gHHA.1220@.TK2MSFTNGP03.phx.gbl...
> You simply need to ensure the appropriate ports are open on the firewall,
> at least between the two servers of concern. A simple packet capture
> while the trigger is firing should inform you as to what needs to be
> opened up. At a minimum it should be TCP 1433 and probably 1434. I don't
> know if other's are involved in remote proc executions or not.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
> news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>
running very smooth and when I turn on the firewall, the trigger action is
giving error and failed to take action. Is this a Big NO between firewall
and Linked server? Is there a way to solve this problem? Thanks.You simply need to ensure the appropriate ports are open on the firewall, at
least between the two servers of concern. A simple packet capture while the
trigger is firing should inform you as to what needs to be opened up. At a
minimum it should be TCP 1433 and probably 1434. I don't know if other's
are involved in remote proc executions or not.
TheSQLGuru
President
Indicium Resources, Inc.
" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>running very smooth and when I turn on the firewall, the trigger action is
>giving error and failed to take action. Is this a Big NO between firewall
>and Linked server? Is there a way to solve this problem? Thanks.
>|||What is the mechanism to capture the packet? Thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:ec5p9z1gHHA.1220@.TK2MSFTNGP03.phx.gbl...
> You simply need to ensure the appropriate ports are open on the firewall,
> at least between the two servers of concern. A simple packet capture
> while the trigger is firing should inform you as to what needs to be
> opened up. At a minimum it should be TCP 1433 and probably 1434. I don't
> know if other's are involved in remote proc executions or not.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
> news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>
linked server problem
I am running trigger with update on a linked server in SQL 2005 SP2. It is
running very smooth and when I turn on the firewall, the trigger action is
giving error and failed to take action. Is this a Big NO between firewall
and Linked server? Is there a way to solve this problem? Thanks.
You simply need to ensure the appropriate ports are open on the firewall, at
least between the two servers of concern. A simple packet capture while the
trigger is firing should inform you as to what needs to be opened up. At a
minimum it should be TCP 1433 and probably 1434. I don't know if other's
are involved in remote proc executions or not.
TheSQLGuru
President
Indicium Resources, Inc.
" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>running very smooth and when I turn on the firewall, the trigger action is
>giving error and failed to take action. Is this a Big NO between firewall
>and Linked server? Is there a way to solve this problem? Thanks.
>
|||What is the mechanism to capture the packet? Thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:ec5p9z1gHHA.1220@.TK2MSFTNGP03.phx.gbl...
> You simply need to ensure the appropriate ports are open on the firewall,
> at least between the two servers of concern. A simple packet capture
> while the trigger is firing should inform you as to what needs to be
> opened up. At a minimum it should be TCP 1433 and probably 1434. I don't
> know if other's are involved in remote proc executions or not.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
> news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>
running very smooth and when I turn on the firewall, the trigger action is
giving error and failed to take action. Is this a Big NO between firewall
and Linked server? Is there a way to solve this problem? Thanks.
You simply need to ensure the appropriate ports are open on the firewall, at
least between the two servers of concern. A simple packet capture while the
trigger is firing should inform you as to what needs to be opened up. At a
minimum it should be TCP 1433 and probably 1434. I don't know if other's
are involved in remote proc executions or not.
TheSQLGuru
President
Indicium Resources, Inc.
" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>running very smooth and when I turn on the firewall, the trigger action is
>giving error and failed to take action. Is this a Big NO between firewall
>and Linked server? Is there a way to solve this problem? Thanks.
>
|||What is the mechanism to capture the packet? Thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:ec5p9z1gHHA.1220@.TK2MSFTNGP03.phx.gbl...
> You simply need to ensure the appropriate ports are open on the firewall,
> at least between the two servers of concern. A simple packet capture
> while the trigger is firing should inform you as to what needs to be
> opened up. At a minimum it should be TCP 1433 and probably 1434. I don't
> know if other's are involved in remote proc executions or not.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
> news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>
linked server problem
I am running trigger with update on a linked server in SQL 2005 SP2. It is
running very smooth and when I turn on the firewall, the trigger action is
giving error and failed to take action. Is this a Big NO between firewall
and Linked server? Is there a way to solve this problem? Thanks.You simply need to ensure the appropriate ports are open on the firewall, at
least between the two servers of concern. A simple packet capture while the
trigger is firing should inform you as to what needs to be opened up. At a
minimum it should be TCP 1433 and probably 1434. I don't know if other's
are involved in remote proc executions or not.
--
TheSQLGuru
President
Indicium Resources, Inc.
" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>running very smooth and when I turn on the firewall, the trigger action is
>giving error and failed to take action. Is this a Big NO between firewall
>and Linked server? Is there a way to solve this problem? Thanks.
>|||What is the mechanism to capture the packet? Thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:ec5p9z1gHHA.1220@.TK2MSFTNGP03.phx.gbl...
> You simply need to ensure the appropriate ports are open on the firewall,
> at least between the two servers of concern. A simple packet capture
> while the trigger is firing should inform you as to what needs to be
> opened up. At a minimum it should be TCP 1433 and probably 1434. I don't
> know if other's are involved in remote proc executions or not.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
> news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>>running very smooth and when I turn on the firewall, the trigger action is
>>giving error and failed to take action. Is this a Big NO between firewall
>>and Linked server? Is there a way to solve this problem? Thanks.
>
running very smooth and when I turn on the firewall, the trigger action is
giving error and failed to take action. Is this a Big NO between firewall
and Linked server? Is there a way to solve this problem? Thanks.You simply need to ensure the appropriate ports are open on the firewall, at
least between the two servers of concern. A simple packet capture while the
trigger is firing should inform you as to what needs to be opened up. At a
minimum it should be TCP 1433 and probably 1434. I don't know if other's
are involved in remote proc executions or not.
--
TheSQLGuru
President
Indicium Resources, Inc.
" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>running very smooth and when I turn on the firewall, the trigger action is
>giving error and failed to take action. Is this a Big NO between firewall
>and Linked server? Is there a way to solve this problem? Thanks.
>|||What is the mechanism to capture the packet? Thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:ec5p9z1gHHA.1220@.TK2MSFTNGP03.phx.gbl...
> You simply need to ensure the appropriate ports are open on the firewall,
> at least between the two servers of concern. A simple packet capture
> while the trigger is firing should inform you as to what needs to be
> opened up. At a minimum it should be TCP 1433 and probably 1434. I don't
> know if other's are involved in remote proc executions or not.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
> news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>>running very smooth and when I turn on the firewall, the trigger action is
>>giving error and failed to take action. Is this a Big NO between firewall
>>and Linked server? Is there a way to solve this problem? Thanks.
>
Friday, March 23, 2012
Linked Server Issues
Good Morning All,
I am receiving the following error message when attempting to update tables
with a trigger that call a stored procedure via a linked server. If I copy
and paste the execute statement from the triggerinto query analyzer it
completes without error.
Server: Msg 7391, Level 16, State 1, Procedure UpdateUnicenterTextFiles,
Line 5
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].
Any suggestions about what is causing this and/or how I can correct it would
be greatly appreciated.
Thanks in advance,
Elecia
Are You using any distributed transations
go to administrative tools\component
services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
Check if you have Enable XA transactions and allow remote clients and
Noauthntication Required
Options
Thanks,
Saradhi
|||Thanks I will check that. I am also looking at the possibility of it being a
firewall issue and that maybe some of the necesary ports may be blocked.
Elecia
"saradhi" wrote:
> Are You using any distributed transations
>
> go to administrative tools\component
> services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
> Check if you have Enable XA transactions and allow remote clients and
> Noauthntication Required
> Options
> Thanks,
> Saradhi
>
I am receiving the following error message when attempting to update tables
with a trigger that call a stored procedure via a linked server. If I copy
and paste the execute statement from the triggerinto query analyzer it
completes without error.
Server: Msg 7391, Level 16, State 1, Procedure UpdateUnicenterTextFiles,
Line 5
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].
Any suggestions about what is causing this and/or how I can correct it would
be greatly appreciated.
Thanks in advance,
Elecia
Are You using any distributed transations
go to administrative tools\component
services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
Check if you have Enable XA transactions and allow remote clients and
Noauthntication Required
Options
Thanks,
Saradhi
|||Thanks I will check that. I am also looking at the possibility of it being a
firewall issue and that maybe some of the necesary ports may be blocked.
Elecia
"saradhi" wrote:
> Are You using any distributed transations
>
> go to administrative tools\component
> services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
> Check if you have Enable XA transactions and allow remote clients and
> Noauthntication Required
> Options
> Thanks,
> Saradhi
>
Linked Server Issues
Good Morning All,
I am receiving the following error message when attempting to update tables
with a trigger that call a stored procedure via a linked server. If I copy
and paste the execute statement from the triggerinto query analyzer it
completes without error.
Server: Msg 7391, Level 16, State 1, Procedure UpdateUnicenterTextFiles,
Line 5
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].
Any suggestions about what is causing this and/or how I can correct it would
be greatly appreciated.
Thanks in advance,
--
EleciaAre You using any distributed transations
go to administrative tools\component
services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
Check if you have Enable XA transactions and allow remote clients and
Noauthntication Required
Options
Thanks,
Saradhi|||Thanks I will check that. I am also looking at the possibility of it being a
firewall issue and that maybe some of the necesary ports may be blocked.
--
Elecia
"saradhi" wrote:
> Are You using any distributed transations
>
> go to administrative tools\component
> services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
> Check if you have Enable XA transactions and allow remote clients and
> Noauthntication Required
> Options
> Thanks,
> Saradhi
>
I am receiving the following error message when attempting to update tables
with a trigger that call a stored procedure via a linked server. If I copy
and paste the execute statement from the triggerinto query analyzer it
completes without error.
Server: Msg 7391, Level 16, State 1, Procedure UpdateUnicenterTextFiles,
Line 5
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].
Any suggestions about what is causing this and/or how I can correct it would
be greatly appreciated.
Thanks in advance,
--
EleciaAre You using any distributed transations
go to administrative tools\component
services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
Check if you have Enable XA transactions and allow remote clients and
Noauthntication Required
Options
Thanks,
Saradhi|||Thanks I will check that. I am also looking at the possibility of it being a
firewall issue and that maybe some of the necesary ports may be blocked.
--
Elecia
"saradhi" wrote:
> Are You using any distributed transations
>
> go to administrative tools\component
> services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
> Check if you have Enable XA transactions and allow remote clients and
> Noauthntication Required
> Options
> Thanks,
> Saradhi
>
Linked Server Issues
Good Morning All,
I am receiving the following error message when attempting to update tables
with a trigger that call a stored procedure via a linked server. If I copy
and paste the execute statement from the triggerinto query analyzer it
completes without error.
Server: Msg 7391, Level 16, State 1, Procedure UpdateUnicenterTextFiles,
Line 5
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].
Any suggestions about what is causing this and/or how I can correct it would
be greatly appreciated.
Thanks in advance,
--
EleciaAre You using any distributed transations
go to administrative tools\component
services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
Check if you have Enable XA transactions and allow remote clients and
Noauthntication Required
Options
Thanks,
Saradhi|||Thanks I will check that. I am also looking at the possibility of it being a
firewall issue and that maybe some of the necesary ports may be blocked.
--
Elecia
"saradhi" wrote:
> Are You using any distributed transations
>
> go to administrative tools\component
> services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
> Check if you have Enable XA transactions and allow remote clients and
> Noauthntication Required
> Options
> Thanks,
> Saradhi
>sql
I am receiving the following error message when attempting to update tables
with a trigger that call a stored procedure via a linked server. If I copy
and paste the execute statement from the triggerinto query analyzer it
completes without error.
Server: Msg 7391, Level 16, State 1, Procedure UpdateUnicenterTextFiles,
Line 5
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].
Any suggestions about what is causing this and/or how I can correct it would
be greatly appreciated.
Thanks in advance,
--
EleciaAre You using any distributed transations
go to administrative tools\component
services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
Check if you have Enable XA transactions and allow remote clients and
Noauthntication Required
Options
Thanks,
Saradhi|||Thanks I will check that. I am also looking at the possibility of it being a
firewall issue and that maybe some of the necesary ports may be blocked.
--
Elecia
"saradhi" wrote:
> Are You using any distributed transations
>
> go to administrative tools\component
> services\Mycomputer\Properties\Msdtc \SecurityConfiguratin
> Check if you have Enable XA transactions and allow remote clients and
> Noauthntication Required
> Options
> Thanks,
> Saradhi
>sql
Wednesday, March 7, 2012
Linked Server : SQL Server 2000
Hi,
When I try to update a table on my local server by
referring to another table in the the linked server I get
a message "Server: Msg 7391, Level 16, State 1, Line 1
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]."
Any ideas would be appreciated as I am running short of
time.
Regards,
VenugopalVenugopal,
Is the Distributed Transaction Coordinator running? On both servers? And
is it configured according to:
http://support.microsoft.com/defaul...kb;en-us;329332
A discussion of linked servers can be found in the article "Linked Servers
and Distributed Transactions"
http://tinyurl.com/2xnsv
Russell Fields
"Venugopal" <vemuriv@.hotmail.com> wrote in message
news:9e1c01c3eac1$61eb1590$a601280a@.phx.gbl...
When I try to update a table on my local server by
referring to another table in the the linked server I get
a message "Server: Msg 7391, Level 16, State 1, Line 1
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]."
Any ideas would be appreciated as I am running short of
time.
Regards,
VenugopalVenugopal,
Is the Distributed Transaction Coordinator running? On both servers? And
is it configured according to:
http://support.microsoft.com/defaul...kb;en-us;329332
A discussion of linked servers can be found in the article "Linked Servers
and Distributed Transactions"
http://tinyurl.com/2xnsv
Russell Fields
"Venugopal" <vemuriv@.hotmail.com> wrote in message
news:9e1c01c3eac1$61eb1590$a601280a@.phx.gbl...
quote:
> Hi,
> When I try to update a table on my local server by
> referring to another table in the the linked server I get
> a message "Server: Msg 7391, Level 16, State 1, Line 1
> 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]."
> Any ideas would be appreciated as I am running short of
> time.
> Regards,
> Venugopal
Friday, February 24, 2012
Linked server & transaction connection
Hi all,
I've a local server and a linked server, which I need to update both
servers' tables.
But I encountered the error "Can't start more transaction in a session'.
Following is the structure of the code in the program:
----
Begin Transaction
Insert local_server_A.table
Update linked_server_B.table
Commit Transaction
If Error
Rollback Transaction
I know the problem is the transaction control can only exist in one
connection.
The linked server is treated as another connection.
Is there any way to workaround for multi connection in a transaction control
?
Thanks in advance,
KristeHi
Read about distribution transactions in the BOL.
"whiteegg" <whiteegg@.discussions.microsoft.com> wrote in message
news:AA6E6C4B-5C3F-476E-AACE-D8AE2EC86388@.microsoft.com...
> Hi all,
> I've a local server and a linked server, which I need to update both
> servers' tables.
> But I encountered the error "Can't start more transaction in a session'.
> Following is the structure of the code in the program:
> ----
> Begin Transaction
> Insert local_server_A.table
> Update linked_server_B.table
> Commit Transaction
> If Error
> Rollback Transaction
> I know the problem is the transaction control can only exist in one
> connection.
> The linked server is treated as another connection.
> Is there any way to workaround for multi connection in a transaction
control?
> Thanks in advance,
> Kriste|||Sorry, I read thr' BOL on MS DTC and replication etc. but I still can't find
solution how can this be applied just between linked servers. Can you provid
e
more info. on this?
"Uri Dimant" wrote:
> Hi
> Read about distribution transactions in the BOL.
>
> "whiteegg" <whiteegg@.discussions.microsoft.com> wrote in message
> news:AA6E6C4B-5C3F-476E-AACE-D8AE2EC86388@.microsoft.com...
> control?
>
>
I've a local server and a linked server, which I need to update both
servers' tables.
But I encountered the error "Can't start more transaction in a session'.
Following is the structure of the code in the program:
----
Begin Transaction
Insert local_server_A.table
Update linked_server_B.table
Commit Transaction
If Error
Rollback Transaction
I know the problem is the transaction control can only exist in one
connection.
The linked server is treated as another connection.
Is there any way to workaround for multi connection in a transaction control
?
Thanks in advance,
KristeHi
Read about distribution transactions in the BOL.
"whiteegg" <whiteegg@.discussions.microsoft.com> wrote in message
news:AA6E6C4B-5C3F-476E-AACE-D8AE2EC86388@.microsoft.com...
> Hi all,
> I've a local server and a linked server, which I need to update both
> servers' tables.
> But I encountered the error "Can't start more transaction in a session'.
> Following is the structure of the code in the program:
> ----
> Begin Transaction
> Insert local_server_A.table
> Update linked_server_B.table
> Commit Transaction
> If Error
> Rollback Transaction
> I know the problem is the transaction control can only exist in one
> connection.
> The linked server is treated as another connection.
> Is there any way to workaround for multi connection in a transaction
control?
> Thanks in advance,
> Kriste|||Sorry, I read thr' BOL on MS DTC and replication etc. but I still can't find
solution how can this be applied just between linked servers. Can you provid
e
more info. on this?
"Uri Dimant" wrote:
> Hi
> Read about distribution transactions in the BOL.
>
> "whiteegg" <whiteegg@.discussions.microsoft.com> wrote in message
> news:AA6E6C4B-5C3F-476E-AACE-D8AE2EC86388@.microsoft.com...
> control?
>
>
Monday, February 20, 2012
linked server
hello
we have a job which will update the data in one server and insert data in another server.for this we created a linked server and wrote a procedure to update and insert the data in both the servers. when we are executing the procedure manually it is working fine. but when we tried to execute the procedure through a local job. job execution is getting failed and encountered the following error
Msg 18456, Sev 14: Login failed for user '\'. [SQLSTATE 28000]
can u please suggest a proper solution for this. we already gave all permissions and roles to both the users.
thank you.Hi Karraaruna!
You probably get problem with user-right. Your sql-servers and specially sql agents should not be running on local account. You must give the sql-server (with agents) a nt-account that has user-rights on the other machine.
Becuse when the sql-agent start the job, then the job is personated with the account of sql-agent. With other word, the next machine see the other machine as a user.
/Mada|||Mada, almost. NT accounts/authentication will only work if both servers are using Active Directory. If you are not using Active Directory then on your linked server proerties window select the Security tab, and set up a login mapping from the NT account your SQL Agent uses to a SQL Server authenticated id on the remote server.|||Hi Paul Young!
Active Directory or not, if you give a nt-account "user-rights" on a another sql-server it will be able to execute an stored procedure. Its no different then any other user.
It works...
/Mada|||Agreed, however the original question was about executing a stored procedure on a linked server. If you are using an NT Authenticated ID when you login and are not using Active Directory the linked server will not be able to authenticate the user unless you mapp the NT Authenticated ID to a server authenticated id.
we have a job which will update the data in one server and insert data in another server.for this we created a linked server and wrote a procedure to update and insert the data in both the servers. when we are executing the procedure manually it is working fine. but when we tried to execute the procedure through a local job. job execution is getting failed and encountered the following error
Msg 18456, Sev 14: Login failed for user '\'. [SQLSTATE 28000]
can u please suggest a proper solution for this. we already gave all permissions and roles to both the users.
thank you.Hi Karraaruna!
You probably get problem with user-right. Your sql-servers and specially sql agents should not be running on local account. You must give the sql-server (with agents) a nt-account that has user-rights on the other machine.
Becuse when the sql-agent start the job, then the job is personated with the account of sql-agent. With other word, the next machine see the other machine as a user.
/Mada|||Mada, almost. NT accounts/authentication will only work if both servers are using Active Directory. If you are not using Active Directory then on your linked server proerties window select the Security tab, and set up a login mapping from the NT account your SQL Agent uses to a SQL Server authenticated id on the remote server.|||Hi Paul Young!
Active Directory or not, if you give a nt-account "user-rights" on a another sql-server it will be able to execute an stored procedure. Its no different then any other user.
It works...
/Mada|||Agreed, however the original question was about executing a stored procedure on a linked server. If you are using an NT Authenticated ID when you login and are not using Active Directory the linked server will not be able to authenticate the user unless you mapp the NT Authenticated ID to a server authenticated id.
linked server
I use a trigger which I update a linked server and this is working very good in SQL 2000. But after I migrate the database to SQL 2005. The trigger is not working with linked server any more. I remember I need to start the distributed transaction coordinator in service manager. But I couldn't find anything similar to this in SQL 2005. Is it somewhere I need to start? I got an error message like following:
Msg 7391, Level 16, State 2, Procedure EAI_ADM_STUDENT_ins_upd_trg, Line 804
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "10.2.0.119" was unable to begin a distributed transaction.
Please help. Thanks.Try this. our db guy had a hell of a time figuring this out :)
Log into the machine named in the error message via termserv.
Start, Run, type 'dcomcnfg.exe'
This brings up the security manager.
Click Component Services/Computers/My Computer folder
Right click My Computer and select properties.
Pick the MSDTC tab.
Click the Security Configuration Button
Click Network DTC Access checkbox.
Click 'Allow Inbound', 'Allow Outbound' checkboxes.|||Thank. I tried it and unfortunately it still doesn't work.|||KBA http://support.microsoft.com/kb/306212 fyi in thsi case.|||Sorry. I have read the articles. But it doesn't help. The problem is not fixed.|||Have you checked Linked Server Properties?
There are Server Options
If you switch:
RPC = true
RPC Out = true
on both servers, you should have not any problems.
TSQL equivalent of above is:
USE [master]
GO
EXEC master.dbo.sp_serveroption @.server=N'MyServerName', @.optname=N'rpc', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'MyServerName', @.optname=N'rpc out', @.optvalue=N'true'
GO|||Sorry. Tried it but not working.
Msg 7391, Level 16, State 2, Procedure EAI_ADM_STUDENT_ins_upd_trg, Line 804
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server "10.2.0.119" was unable to begin a distributed transaction.
Please help. Thanks.Try this. our db guy had a hell of a time figuring this out :)
Log into the machine named in the error message via termserv.
Start, Run, type 'dcomcnfg.exe'
This brings up the security manager.
Click Component Services/Computers/My Computer folder
Right click My Computer and select properties.
Pick the MSDTC tab.
Click the Security Configuration Button
Click Network DTC Access checkbox.
Click 'Allow Inbound', 'Allow Outbound' checkboxes.|||Thank. I tried it and unfortunately it still doesn't work.|||KBA http://support.microsoft.com/kb/306212 fyi in thsi case.|||Sorry. I have read the articles. But it doesn't help. The problem is not fixed.|||Have you checked Linked Server Properties?
There are Server Options
If you switch:
RPC = true
RPC Out = true
on both servers, you should have not any problems.
TSQL equivalent of above is:
USE [master]
GO
EXEC master.dbo.sp_serveroption @.server=N'MyServerName', @.optname=N'rpc', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'MyServerName', @.optname=N'rpc out', @.optvalue=N'true'
GO|||Sorry. Tried it but not working.
Linked Server
I have an oracel linked server that I use openqueryset statements to read the oracle tables.
However, I want to update some data in a couple of these oracle tables. The linked server is setup using a readonly user. I'd like to be able to call an Oracle Set Role to grant me update capability and then perform the update.
Can anyone help me out with some possibilities?
See SQL Server 2005 Books Online
OPENQUERY (Transact-SQL)
This is a KB error article but it has useful information on using OPENQUERY
http://support.microsoft.com/kb/q270119/
|||Well... does not really apply. What I am thinking about doing is calling an oracle stored procedure that grants the linked server account update privledges on the table. Then submit an openquery statement to do the update.
However, I am not sure if the privledge granted from the prcale stored procedure will still be in effect when the second openquery statement is issued.
The reason why I am doing this is for security reasons to not allow a linked server "default" update privledges on the Oracle database.
|||In case anyone needs to do this... or something similar... I have figured it out:
On Oracle server, create the Oracle Package and Procedure:
create or replace
PACKAGE BODY Call_SEC_ROLE_Package
AS
PROCEDURE sec_roles
(ReturnVal OUT SqlReturnTbl)
is
begin
dbms_session.set_role('apply_security identified by secure_pass');
ReturnVal(1) := '1';
end sec_roles;
end Call_SEC_ROLE_Package;
On SQL Server create your linked server and then call the Oracle stored procedure by:
SELECT *
FROM OPENQUERY
(LinkServerName, '{CALL Call_SEC_ROLE_Package.sec_roles({resultset 1, ReturnVal})}')
go
Subscribe to:
Posts (Atom)