server Woes

Whilst experienced with SQL Server in general, I have had no exposure to
linked servers. I have, therefore, been "having a play" with a view to an
upcoming project.
The project will involve updating a database on an external server (another
company) via triggers based on that in our own company (update in realtime).
The databases will be different and on different networks thus replication
is out.
I have managed to link test servers together and expose only the tables on
each database based on the access permissions of the SQL logins used. I can
select data from each and I can also insert or update from one to the other.
The problem I have is when adding triggers to one table that changes data on
the other, the system either hangs or I get error messages. Below is coding
used for triggers etc along with the error message (I initially used just a
trigger but read somewhere that DTC does not react well to such, hence a
seperate sp that is called into play from within the trigger).
******************
TRIGGER:
create trigger trg_CustInserts
on tbl_Customer
for insert
as
declare @.Name varchar(100)
set @.Name = (select Forname+' '+Surname from inserted)
exec catdb.dbo.usp_CustomerInserts @.Name
STORED PROCEDURE:
create procedure usp_CustomerInserts @.Name varchar(100)
as
insert test.[Remote Database].dbo.tblUsers (UserName)
Select @.Name
COMMAND RUN TO POPULATE TABLE:
insert tbl_Customer (Forname,Surname)
values ('Johnny','Rotten')
ERROR MESSAGE:
Server: Msg 7391, Level 16, State 1, Procedure trg_CustInserts, Line 6
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 followed the instructions found on http://support.microsoft.com/kb/839279
in order to enable DTC etc but the problem remains.
Any advice would be appreciated.
Regards
DazSo you're willing to tie your database availability to the external
partner's database availability? If the partner database is down, your
trigger will fail causing downtime on your database. Have you considered a
more loosely coupled solution?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"news.microsoft.com" <Post2Group@.Only.com> wrote in message
news:OLBwmhQIHHA.1044@.TK2MSFTNGP02.phx.gbl...
> Whilst experienced with SQL Server in general, I have had no exposure to
> linked servers. I have, therefore, been "having a play" with a view to an
> upcoming project.
> The project will involve updating a database on an external server
> (another
> company) via triggers based on that in our own company (update in
> realtime).
> The databases will be different and on different networks thus replication
> is out.
> I have managed to link test servers together and expose only the tables on
> each database based on the access permissions of the SQL logins used. I
> can
> select data from each and I can also insert or update from one to the
> other.
> The problem I have is when adding triggers to one table that changes data
> on
> the other, the system either hangs or I get error messages. Below is
> coding
> used for triggers etc along with the error message (I initially used just
> a
> trigger but read somewhere that DTC does not react well to such, hence a
> seperate sp that is called into play from within the trigger).
> ******************
> TRIGGER:
> create trigger trg_CustInserts
> on tbl_Customer
> for insert
> as
> declare @.Name varchar(100)
> set @.Name = (select Forname+' '+Surname from inserted)
> exec catdb.dbo.usp_CustomerInserts @.Name
> STORED PROCEDURE:
> create procedure usp_CustomerInserts @.Name varchar(100)
> as
> insert test.[Remote Database].dbo.tblUsers (UserName)
> Select @.Name
> COMMAND RUN TO POPULATE TABLE:
> insert tbl_Customer (Forname,Surname)
> values ('Johnny','Rotten')
> ERROR MESSAGE:
> Server: Msg 7391, Level 16, State 1, Procedure trg_CustInserts, Line 6
> 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 followed the instructions found on
> http://support.microsoft.com/kb/839279
> in order to enable DTC etc but the problem remains.
> Any advice would be appreciated.
> Regards
> Daz
>|||Remus
I am just looking into possibilities of linked servers at the moment. The
goal in the trigger would be to check if the table is available on the
linked server first. If so, transfer the data, in not put it in a holding
area ready for transfer.
I really need to overcome my lack of knowledge regards linked servers and
the errors I am getting. In a nutshell, I am taking things one step at a
time.
Any ideas on how to overcome this annoying error would be useful to me.
Regards
Daz
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:OEjv3uUIHHA.1912@.TK2MSFTNGP03.phx.gbl...
> So you're willing to tie your database availability to the external
> partner's database availability? If the partner database is down, your
> trigger will fail causing downtime on your database. Have you considered a
> more loosely coupled solution?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "news.microsoft.com" <Post2Group@.Only.com> wrote in message
> news:OLBwmhQIHHA.1044@.TK2MSFTNGP02.phx.gbl...
>> Whilst experienced with SQL Server in general, I have had no exposure to
>> linked servers. I have, therefore, been "having a play" with a view to
>> an
>> upcoming project.
>> The project will involve updating a database on an external server
>> (another
>> company) via triggers based on that in our own company (update in
>> realtime).
>> The databases will be different and on different networks thus
>> replication
>> is out.
>> I have managed to link test servers together and expose only the tables
>> on
>> each database based on the access permissions of the SQL logins used. I
>> can
>> select data from each and I can also insert or update from one to the
>> other.
>> The problem I have is when adding triggers to one table that changes data
>> on
>> the other, the system either hangs or I get error messages. Below is
>> coding
>> used for triggers etc along with the error message (I initially used just
>> a
>> trigger but read somewhere that DTC does not react well to such, hence a
>> seperate sp that is called into play from within the trigger).
>> ******************
>> TRIGGER:
>> create trigger trg_CustInserts
>> on tbl_Customer
>> for insert
>> as
>> declare @.Name varchar(100)
>> set @.Name = (select Forname+' '+Surname from inserted)
>> exec catdb.dbo.usp_CustomerInserts @.Name
>> STORED PROCEDURE:
>> create procedure usp_CustomerInserts @.Name varchar(100)
>> as
>> insert test.[Remote Database].dbo.tblUsers (UserName)
>> Select @.Name
>> COMMAND RUN TO POPULATE TABLE:
>> insert tbl_Customer (Forname,Surname)
>> values ('Johnny','Rotten')
>> ERROR MESSAGE:
>> Server: Msg 7391, Level 16, State 1, Procedure trg_CustInserts, Line 6
>> 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 followed the instructions found on
>> http://support.microsoft.com/kb/839279
>> in order to enable DTC etc but the problem remains.
>> Any advice would be appreciated.
>> Regards
>> Daz
>|||What SQL versions are we talking about?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Dazza The Fat" <Post2Group@.Only.com> wrote in message
news:e$Eg$pVIHHA.1784@.TK2MSFTNGP06.phx.gbl...
> Remus
> I am just looking into possibilities of linked servers at the moment. The
> goal in the trigger would be to check if the table is available on the
> linked server first. If so, transfer the data, in not put it in a holding
> area ready for transfer.
> I really need to overcome my lack of knowledge regards linked servers and
> the errors I am getting. In a nutshell, I am taking things one step at a
> time.
> Any ideas on how to overcome this annoying error would be useful to me.
> Regards
> Daz
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
> wrote in message news:OEjv3uUIHHA.1912@.TK2MSFTNGP03.phx.gbl...
>> So you're willing to tie your database availability to the external
>> partner's database availability? If the partner database is down, your
>> trigger will fail causing downtime on your database. Have you considered
>> a more loosely coupled solution?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> HTH,
>> ~ Remus Rusanu
>> SQL Service Broker
>> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>>
>> "news.microsoft.com" <Post2Group@.Only.com> wrote in message
>> news:OLBwmhQIHHA.1044@.TK2MSFTNGP02.phx.gbl...
>> Whilst experienced with SQL Server in general, I have had no exposure to
>> linked servers. I have, therefore, been "having a play" with a view to
>> an
>> upcoming project.
>> The project will involve updating a database on an external server
>> (another
>> company) via triggers based on that in our own company (update in
>> realtime).
>> The databases will be different and on different networks thus
>> replication
>> is out.
>> I have managed to link test servers together and expose only the tables
>> on
>> each database based on the access permissions of the SQL logins used. I
>> can
>> select data from each and I can also insert or update from one to the
>> other.
>> The problem I have is when adding triggers to one table that changes
>> data on
>> the other, the system either hangs or I get error messages. Below is
>> coding
>> used for triggers etc along with the error message (I initially used
>> just a
>> trigger but read somewhere that DTC does not react well to such, hence a
>> seperate sp that is called into play from within the trigger).
>> ******************
>> TRIGGER:
>> create trigger trg_CustInserts
>> on tbl_Customer
>> for insert
>> as
>> declare @.Name varchar(100)
>> set @.Name = (select Forname+' '+Surname from inserted)
>> exec catdb.dbo.usp_CustomerInserts @.Name
>> STORED PROCEDURE:
>> create procedure usp_CustomerInserts @.Name varchar(100)
>> as
>> insert test.[Remote Database].dbo.tblUsers (UserName)
>> Select @.Name
>> COMMAND RUN TO POPULATE TABLE:
>> insert tbl_Customer (Forname,Surname)
>> values ('Johnny','Rotten')
>> ERROR MESSAGE:
>> Server: Msg 7391, Level 16, State 1, Procedure trg_CustInserts, Line 6
>> 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 followed the instructions found on
>> http://support.microsoft.com/kb/839279
>> in order to enable DTC etc but the problem remains.
>> Any advice would be appreciated.
>> Regards
>> Daz
>>
>|||SQL 2000
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:uyHdPxWIHHA.1248@.TK2MSFTNGP03.phx.gbl...
> What SQL versions are we talking about?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Dazza The Fat" <Post2Group@.Only.com> wrote in message
> news:e$Eg$pVIHHA.1784@.TK2MSFTNGP06.phx.gbl...
>> Remus
>> I am just looking into possibilities of linked servers at the moment.
>> The goal in the trigger would be to check if the table is available on
>> the linked server first. If so, transfer the data, in not put it in a
>> holding area ready for transfer.
>> I really need to overcome my lack of knowledge regards linked servers and
>> the errors I am getting. In a nutshell, I am taking things one step at a
>> time.
>> Any ideas on how to overcome this annoying error would be useful to me.
>> Regards
>> Daz
>> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
>> wrote in message news:OEjv3uUIHHA.1912@.TK2MSFTNGP03.phx.gbl...
>> So you're willing to tie your database availability to the external
>> partner's database availability? If the partner database is down, your
>> trigger will fail causing downtime on your database. Have you considered
>> a more loosely coupled solution?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> HTH,
>> ~ Remus Rusanu
>> SQL Service Broker
>> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>>
>> "news.microsoft.com" <Post2Group@.Only.com> wrote in message
>> news:OLBwmhQIHHA.1044@.TK2MSFTNGP02.phx.gbl...
>> Whilst experienced with SQL Server in general, I have had no exposure
>> to
>> linked servers. I have, therefore, been "having a play" with a view to
>> an
>> upcoming project.
>> The project will involve updating a database on an external server
>> (another
>> company) via triggers based on that in our own company (update in
>> realtime).
>> The databases will be different and on different networks thus
>> replication
>> is out.
>> I have managed to link test servers together and expose only the tables
>> on
>> each database based on the access permissions of the SQL logins used.
>> I can
>> select data from each and I can also insert or update from one to the
>> other.
>> The problem I have is when adding triggers to one table that changes
>> data on
>> the other, the system either hangs or I get error messages. Below is
>> coding
>> used for triggers etc along with the error message (I initially used
>> just a
>> trigger but read somewhere that DTC does not react well to such, hence
>> a
>> seperate sp that is called into play from within the trigger).
>> ******************
>> TRIGGER:
>> create trigger trg_CustInserts
>> on tbl_Customer
>> for insert
>> as
>> declare @.Name varchar(100)
>> set @.Name = (select Forname+' '+Surname from inserted)
>> exec catdb.dbo.usp_CustomerInserts @.Name
>> STORED PROCEDURE:
>> create procedure usp_CustomerInserts @.Name varchar(100)
>> as
>> insert test.[Remote Database].dbo.tblUsers (UserName)
>> Select @.Name
>> COMMAND RUN TO POPULATE TABLE:
>> insert tbl_Customer (Forname,Surname)
>> values ('Johnny','Rotten')
>> ERROR MESSAGE:
>> Server: Msg 7391, Level 16, State 1, Procedure trg_CustInserts, Line 6
>> 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 followed the instructions found on
>> http://support.microsoft.com/kb/839279
>> in order to enable DTC etc but the problem remains.
>> Any advice would be appreciated.
>> Regards
>> Daz
>>
>>
>|||Perfect Match Finder
http://www.max-online.biz/idevaffiliate/idevaffiliate.php?id=804
Online Web Promotion
http://www.max-online.biz/idevaffiliate/idevaffiliate.php?id=804
For further details email me at maxonline.sunil@.gmail.com