|
|
| -000MJ 2006-11-28, 12:12 am |
| I tried to run a procedure SQL2005 and it gives me the following error:
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.
Can anyone please tell me how to fix? Thanks.
| |
| Uri Dimant 2006-11-28, 5:16 am |
| Have you enabled remote connection on SQL Server 2005?
Do you have ping to the server?
"-000MJ" <mj@yahoo.com> wrote in message
news:%23J%234$4qEHHA
.2328@TK2MSFTNGP02.phx.gbl...
>I tried to run a procedure SQL2005 and it gives me the following error:
>
> 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.
>
> Can anyone please tell me how to fix? Thanks.
>
>
| |
| -000MJ 2006-11-28, 5:16 am |
| I did try to do the manual update query on a table in my server using script
to the linked server and it works. But it only not works in this procedure.
Strange!
"Uri Dimant" <urid@iscar.co.il> wrote in message
news:OfQ%235drEHHA.5028@TK2MSFTNGP03.phx.gbl...
> Have you enabled remote connection on SQL Server 2005?
> Do you have ping to the server?
>
>
> "-000MJ" <mj@yahoo.com> wrote in message
> news:%23J%234$4qEHHA
.2328@TK2MSFTNGP02.phx.gbl...
>
>
| |
| Uri Dimant 2006-11-28, 5:16 am |
| How do you call stored procedure? from an appliaction?
"-000MJ" <mj@yahoo.com> wrote in message
news:O6jlWHsEHHA.3660@TK2MSFTNGP06.phx.gbl...
>I did try to do the manual update query on a table in my server using
>script to the linked server and it works. But it only not works in this
>procedure. Strange!
>
> "Uri Dimant" <urid@iscar.co.il> wrote in message
> news:OfQ%235drEHHA.5028@TK2MSFTNGP03.phx.gbl...
>
>
| |
| John Bell 2006-11-28, 5:16 am |
| Hi
Did you explicitly start a distributed transaction in the stored procedure?
John
"-000MJ" wrote:
> I did try to do the manual update query on a table in my server using script
> to the linked server and it works. But it only not works in this procedure.
> Strange!
>
> "Uri Dimant" <urid@iscar.co.il> wrote in message
> news:OfQ%235drEHHA.5028@TK2MSFTNGP03.phx.gbl...
>
>
>
| |
| -000MJ 2006-11-29, 12:12 am |
| I have used this:
set xact_abort on
begin distributed tran
Is this what you mean?
"John Bell" < jbellnewsposts@hotma
il.com> wrote in message
news:D17738A3-6BCB-48BC-A9DE- 8992E52AEAF8@microso
ft.com...[color=darkred]
> Hi
>
> Did you explicitly start a distributed transaction in the stored
> procedure?
>
> John
>
> "-000MJ" wrote:
>
| |
| -000MJ 2006-11-30, 5:17 am |
| I am really stuck on this problem. Can anyone please help? Thanks.
"-000MJ" <mj@yahoo.com> wrote in message
news:uNoWeD3EHHA.3396@TK2MSFTNGP02.phx.gbl...
>I have used this:
>
> set xact_abort on
>
> begin distributed tran
>
> Is this what you mean?
>
>
>
> "John Bell" < jbellnewsposts@hotma
il.com> wrote in message
> news:D17738A3-6BCB-48BC-A9DE- 8992E52AEAF8@microso
ft.com...
>
>
| |
| -000MJ 2006-11-30, 5:17 am |
| Actually let me elaborate more. This procedure is 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? Please help. Thanks.
"-000MJ" <mj@yahoo.com> wrote in message
news:uNoWeD3EHHA.3396@TK2MSFTNGP02.phx.gbl...
>I have used this:
>
> set xact_abort on
>
> begin distributed tran
>
> Is this what you mean?
>
>
>
> "John Bell" < jbellnewsposts@hotma
il.com> wrote in message
> news:D17738A3-6BCB-48BC-A9DE- 8992E52AEAF8@microso
ft.com...
>
>
| |
| -000MJ 2006-11-30, 7:14 pm |
| Can anyone please help?
"-000MJ" <mj@yahoo.com> wrote in message
news:O44nqqEFHHA.4432@TK2MSFTNGP03.phx.gbl...
> Actually let me elaborate more. This procedure is 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? Please help. Thanks.
>
>
> "-000MJ" <mj@yahoo.com> wrote in message
> news:uNoWeD3EHHA.3396@TK2MSFTNGP02.phx.gbl...
>
>
| |
| John Bell 2006-11-30, 7:14 pm |
| Hi
Check out http://msdn2.microsoft.com/en-us/library/ms143698.aspx
John
"-000MJ" wrote:
> Actually let me elaborate more. This procedure is 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? Please help. Thanks.
>
>
> "-000MJ" <mj@yahoo.com> wrote in message
> news:uNoWeD3EHHA.3396@TK2MSFTNGP02.phx.gbl...
>
>
>
| |
| John Bell 2006-11-30, 7:14 pm |
| Hi
Yes, is this code in the procedure? How are you running the procedure?
John
"-000MJ" wrote:
> I have used this:
>
> set xact_abort on
>
> begin distributed tran
>
> Is this what you mean?
>
>
>
> "John Bell" < jbellnewsposts@hotma
il.com> wrote in message
> news:D17738A3-6BCB-48BC-A9DE- 8992E52AEAF8@microso
ft.com...
>
>
>
| |
| -000MJ 2006-12-01, 12:12 am |
| I checked the DTC service and it is running already. So I guess this is not
the cause. Can anyone please help? Thanks.
"John Bell" < jbellnewsposts@hotma
il.com> wrote in message
news:3F8901A6-BFC4-4B9D-83D3- C62E7C4762DD@microso
ft.com...[color=darkred]
> Hi
>
> Check out http://msdn2.microsoft.com/en-us/library/ms143698.aspx
>
> John
>
> "-000MJ" wrote:
>
| |
| -000MJ 2006-12-01, 12:12 am |
| Does this has anything to do with security configuration in MSDTC tab in my
computer properties? Thanks.
"-000MJ" <mj@yahoo.com> wrote in message
news:OufHPvOFHHA.1188@TK2MSFTNGP06.phx.gbl...
>I checked the DTC service and it is running already. So I guess this is not
>the cause. Can anyone please help? Thanks.
>
>
> "John Bell" < jbellnewsposts@hotma
il.com> wrote in message
> news:3F8901A6-BFC4-4B9D-83D3- C62E7C4762DD@microso
ft.com...
>
>
| |
| John Bell 2006-12-01, 7:13 pm |
| Hi
Look in the services applet as descripted by the link I posted.
John
"-000MJ" wrote:
> Does this has anything to do with security configuration in MSDTC tab in my
> computer properties? Thanks.
>
> "-000MJ" <mj@yahoo.com> wrote in message
> news:OufHPvOFHHA.1188@TK2MSFTNGP06.phx.gbl...
>
>
>
| |
| Bill Gate 2006-12-03, 7:14 pm |
| Guys, please help this gentleman here.
"John Bell" < jbellnewsposts@hotma
il.com> wrote in message
news:9F56C207-FDC5-45D0-B103- 9966ECC62AAA@microso
ft.com...[color=darkred]
> Hi
>
> Look in the services applet as descripted by the link I posted.
>
> John
>
> "-000MJ" wrote:
>
| |
| 00MichaelJordan 2006-12-05, 12:12 am |
| Would that help if I post the source code here?
"John Bell" < jbellnewsposts@hotma
il.com> wrote in message
news:9F56C207-FDC5-45D0-B103- 9966ECC62AAA@microso
ft.com...[color=darkred]
> Hi
>
> Look in the services applet as descripted by the link I posted.
>
> John
>
> "-000MJ" wrote:
>
| |
| 00MichaelJordan 2006-12-05, 12:12 am |
| In fact, before this message there is another one:
OLE DB provider "SQLNCLI" for linked server "VTTDB1" returned message "No
transaction is active.".
Then
Msg 7391, Level 16, State 2, Procedure EAI_ADM_STUDENT_ins_
upd_trg,
Hope this make a difference. Thanks.
"John Bell" < jbellnewsposts@hotma
il.com> wrote in message
news:9F56C207-FDC5-45D0-B103- 9966ECC62AAA@microso
ft.com...[color=darkred]
> Hi
>
> Look in the services applet as descripted by the link I posted.
>
> John
>
> "-000MJ" wrote:
>
| |
| John Bell 2006-12-05, 5:16 am |
| Hi
It would help if you posted ddl, the message you have indicates that the
transansaction has not been started, or has already finished, or has timed
out...
This may help: http://tinyurl.com/yzxljt
Can you update the remote server within the code of the procedure when the
trigger is disabled?
John
"00MichaelJordan" wrote:
> In fact, before this message there is another one:
>
> OLE DB provider "SQLNCLI" for linked server "VTTDB1" returned message "No
> transaction is active.".
>
> Then
>
> Msg 7391, Level 16, State 2, Procedure EAI_ADM_STUDENT_ins_
upd_trg,
>
> Hope this make a difference. Thanks.
>
>
> "John Bell" < jbellnewsposts@hotma
il.com> wrote in message
> news:9F56C207-FDC5-45D0-B103- 9966ECC62AAA@microso
ft.com...
>
>
>
| |
| 00MichaelJordan 2006-12-06, 12:12 am |
| I found that if I put the code in trigger, it gives error when trigger takes
action. But if I put it as a store procedure and when I run the sp, it works
with the same code. Here is the code for your review. Please help. Thanks.
Trigger Code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER trigger [dirxml4stu].& #91;EAI_ADM_STUDENT_
ins_upd_trg] on
[dirxml4stu].& #91;EAI_ADM_STUDENT]
for insert, update
as
set xact_abort on
update VTTDB1.databasename.tablename set entercode = 'ML' where link = 861;
Store Procedure code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter procedure [dbo].[testsp]
as
set xact_abort on
update VTTDB1.databasename.tablename set entercode = 'ML' where link = 861;
"John Bell" < jbellnewsposts@hotma
il.com> wrote in message
news:27C00E52-D03F-465A-8170- 014BEE2903E2@microso
ft.com...[color=darkred]
> Hi
>
> It would help if you posted ddl, the message you have indicates that the
> transansaction has not been started, or has already finished, or has timed
> out...
> This may help: http://tinyurl.com/yzxljt
>
> Can you update the remote server within the code of the procedure when the
> trigger is disabled?
>
> John
>
> "00MichaelJordan" wrote:
>
| |
| John Bell 2006-12-06, 5:16 am |
| Hi
Do you have the code that inserts/updates EAI_ADM_STUDENT?
John
"00MichaelJordan" wrote:
> I found that if I put the code in trigger, it gives error when trigger takes
> action. But if I put it as a store procedure and when I run the sp, it works
> with the same code. Here is the code for your review. Please help. Thanks.
>
> Trigger Code:
>
> set ANSI_NULLS ON
>
> set QUOTED_IDENTIFIER ON
>
> GO
>
> ALTER trigger [dirxml4stu].& #91;EAI_ADM_STUDENT_
ins_upd_trg] on
> [dirxml4stu].& #91;EAI_ADM_STUDENT]
>
> for insert, update
>
> as
>
> set xact_abort on
>
> update VTTDB1.databasename.tablename set entercode = 'ML' where link = 861;
>
>
>
> Store Procedure code:
>
> set ANSI_NULLS ON
>
> set QUOTED_IDENTIFIER ON
>
> GO
>
> alter procedure [dbo].[testsp]
>
> as
>
> set xact_abort on
>
> update VTTDB1.databasename.tablename set entercode = 'ML' where link = 861;
>
>
>
>
>
> "John Bell" < jbellnewsposts@hotma
il.com> wrote in message
> news:27C00E52-D03F-465A-8170- 014BEE2903E2@microso
ft.com...
>
>
>
| |
| 00MichaelJordan 2006-12-06, 7:12 pm |
| Here you go,
update VTTDB1.dbname.tablename set birthplace = 'ZZ' where link = '123';
"John Bell" < jbellnewsposts@hotma
il.com> wrote in message
news:A60E10C3-ED72-4176-960F- FF500EE59143@microso
ft.com...[color=darkred]
> Hi
>
> Do you have the code that inserts/updates EAI_ADM_STUDENT?
>
> John
>
> "00MichaelJordan" wrote:
>
| |
| 00MichaelJordan 2006-12-07, 12:12 am |
| Could this be a bug in SQL 2005? Do we need to report it to Microsoft? Does
anyone actually works with trigger and link server things and it works
successfully? I mean it works good in SQL 2000 but all the sudden doesn't
work in SQL 2K5. I got some suspicion. Please let me know. THanks.
"John Bell" < jbellnewsposts@hotma
il.com> wrote in message
news:A60E10C3-ED72-4176-960F- FF500EE59143@microso
ft.com...[color=darkred]
> Hi
>
> Do you have the code that inserts/updates EAI_ADM_STUDENT?
>
> John
>
> "00MichaelJordan" wrote:
>
| |
| John Bell 2006-12-07, 5:17 am |
| Hi
You code doesn't change EAI_ADM_STUDENT. If you started an explicit
distributed transaction does it work ie
BEGIN DISTRIBUTED TRANSACTION
update dbo.EAI_ADM_STUDENT set birthplace = 'ZZ' where link = '123';
END DISTRIBUTED TRANSACTION
Also you are missing the schema in the four part name
update VTTDB1.databasename.[schema].tablename set entercode = 'ML' where
link = 861
John
"00MichaelJordan" wrote:
> Here you go,
>
> update VTTDB1.dbname.tablename set birthplace = 'ZZ' where link = '123';
>
>
> "John Bell" < jbellnewsposts@hotma
il.com> wrote in message
> news:A60E10C3-ED72-4176-960F- FF500EE59143@microso
ft.com...
>
>
>
| |
| John Bell 2006-12-08, 5:15 am |
| Hi
What accounts is SQL Server and DTC running under on each machine? Have you
tried DTCPing?
http://www.microsoft.com/downloads/...&DisplayLang=en
John
"00MichaelJordan" wrote:
> Could this be a bug in SQL 2005? Do we need to report it to Microsoft? Does
> anyone actually works with trigger and link server things and it works
> successfully? I mean it works good in SQL 2000 but all the sudden doesn't
> work in SQL 2K5. I got some suspicion. Please let me know. THanks.
>
> "John Bell" < jbellnewsposts@hotma
il.com> wrote in message
> news:A60E10C3-ED72-4176-960F- FF500EE59143@microso
ft.com...
>
>
>
| |
| 00MichaelJordan 2006-12-11, 12:12 am |
| Hi John,
SQL server account used to logon is one of the administrator
account and I used NT Authority\NetworkSer
vice as DTC logon account .
"John Bell" < jbellnewsposts@hotma
il.com> wrote in message
news:280C1369-FE8E-45EF-8581- 105F6D349596@microso
ft.com...[color=darkred]
> Hi
>
> What accounts is SQL Server and DTC running under on each machine? Have
> you
> tried DTCPing?
> http://www.microsoft.com/downloads/...&DisplayLang=en
>
> John
>
> "00MichaelJordan" wrote:
>
| |
| John Bell 2006-12-12, 5:17 am |
| Hi
Change these to be domain accounts and see what happens. Certainly for SQL
Server the Network Service account is not recommended from
http://msdn2.microsoft.com/en-us/li...acc
ounts
"Microsoft recommends that you not use the Network Service account for the
SQL Server or the SQL Server Agent services."
John
"00MichaelJordan" wrote:
> Hi John,
>
> SQL server account used to logon is one of the administrator
> account and I used NT Authority\NetworkSer
vice as DTC logon account .
>
>
> "John Bell" < jbellnewsposts@hotma
il.com> wrote in message
> news:280C1369-FE8E-45EF-8581- 105F6D349596@microso
ft.com...
>
>
>
|
|
|
|