Monday, March 26, 2012
Need help
I tried to update a record on one server and there is an update
trigger to to update another record in another server . But I got this error
and no record is updated. Can anyone please advise me the solution and the
cause? Thanks.
The error message as follow:
update action start
Server: Msg 7391, Level 16, State 1, Procedure something_ins_upd_trg, Line
328
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].Hi Eric,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you encounter error message 7391 when
executing query on Linked Server. If I have misunderstood your concern,
please feel free to point it out.
First of all, please make sure you are not encounter the scenario as the
following KB described.
You may receive a 7391 error message in SQL Server 2000 when you run a
distributed transaction against a linked server after you install Windows
Server 2003 or Windows XP Service Pack 2
http://support.microsoft.com/kb/839279
Also, make sure you have made MS DTC correctly configured for network access
You receive error 7391 when you run a distributed transaction against a
linked server
http://support.microsoft.com/kb/329332/en-us
Secondly, I have noticed you mentioned "there is an update trigger to to
update another record in another server". If you are executing the query
that won't active the trigger, will it also reproduce this error message?
Are you executing the stored procedure in a transtion? If you execute the
stored procedure directly, will it reproduce this error message?
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for your help, Michael. I am running this trigger on Win2000 SP3 or
SP4. If I remove the trigger there is no error at all and it does involve
store procedure. The following KB apply for Win 2003 only, right? How do
you configure the MS DTC?
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:Tk7Ahv$8FHA.1236@.TK2MSFTNGXA02.phx.gbl...
> Hi Eric,
> Welcome to use MSDN Managed Newsgroup!
> From your descriptions, I understood you encounter error message 7391 when
> executing query on Linked Server. If I have misunderstood your concern,
> please feel free to point it out.
> First of all, please make sure you are not encounter the scenario as the
> following KB described.
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Windows
> Server 2003 or Windows XP Service Pack 2
> http://support.microsoft.com/kb/839279
> Also, make sure you have made MS DTC correctly configured for network
> access
> You receive error 7391 when you run a distributed transaction against a
> linked server
> http://support.microsoft.com/kb/329332/en-us
> Secondly, I have noticed you mentioned "there is an update trigger to to
> update another record in another server". If you are executing the query
> that won't active the trigger, will it also reproduce this error message?
> Are you executing the stored procedure in a transtion? If you execute the
> stored procedure directly, will it reproduce this error message?
> If you have any questions or concerns, don't hesitate to let me know. We
> are always here to be of assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>|||Hi Eric,
Thanks for your reply.
Does your two server on the same domain or on the trusted domains? Does the
linked server is also SQL Server or other database? Execute the query
directly on the destination server, will the query work and active the
trigger?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
Monday, March 19, 2012
Need assistance on conditional update Trigger
I need some help here in creating a conditional update trigger. The purpose of this trigger would check to see if a contact already exist in the database on an insert and update only the fields that are null.
So How would I compare each field from the CONTACTS Table against my INSERTED Table?
Inserted.FirstName (COMPARE) Contacts.Firstname
Inserted.LastName (COMPARE) Contacts.LastName
Inserted.Email (COMPARE) Contacts.Email
I will be using the email address as the check for the duplicate record and if a duplicate is found... Instead of not allowing the insert I want to compare the existing record and update any fields that are NULL in Contacts with Inserted.
I have no idea on how to compare all of the fields.
Any help appreciated.
sadler_david@.yahoo.com
You could do the following in an INSTEAD OF trigger:
create trigger merge_contact
instead of insert
on dbo.Contacts
as
begin
update dbo.Contacts
set FirstName = coalesce(FirstName, i.FirstName),
LastName = coalesce(LastName, i.LastName)
from inserted as i
where i.Email = dbo.Contacts.Email
insert into dbo.Contacts (FirstName, LastName, Email)
select i.FirstName, i.LastName, i.Email
from inserted as i
where not exists(select * from dbo.Contacts as c
where c.Email = i.Email)
end
Ideally, you need to perform the operations in serializable transaction isolation level to avoid duplicate inserts.
Need an example of a trigger.
I'm a newbie to the MS SQL db engine, and I'd like to have some help with Transact-SQL. I'd like to know the code to create a trigger to do the following. I have two tables, one references the other. I'd like the database to automatically delete the records from table2 when the record that they reference in table1 is deleted. Much thanks.Try this
create trigger dbo.trigger_deleterecs on table1
for delete
as
begin
declare @.refcolumn datatypehere
select @.refcolumn = deleted.refcolumn from deleted
delete from table2 where table2refcolumn = @.refcolumn
end|||how about:
create trigger dbo.trigger_deleterecs on table1
for delete
as
begin
delete from table2 where refcolumn in (select refcolumn from deleted)
end
or
create trigger dbo.trigger_deleterecs on table1
for delete
as
begin
delete t2
from table2 t2
join deleted d on t2.refcolumn = d.refcolumn
end
by using either of these you will delete all records when multipule records are deleted.
Monday, March 12, 2012
Need advise on Insert trigger
I am trying to create a insert trigger that gets a value from the new record and use that to get additional data from other tables and update the new record
this is howfar i came:
create trigger trUpdateGEOData
on BK_Machine
after insert
as
update BK_Machine
set BK_Machine.LOC_Street = GEO_Postcode.STraatID, BK_Machine.Loc_City = GEO_Postcode.PlaatsID
from BK_Machine join GEO_Postcode on BK_Machine.loc_postalcode = GEO_Postcode.postcode, inserted
where BK_Machine.MachineID = Inserted.MachineID and BK_Machine.Loc_Postalcode = GEO_Postcode.postcode and BK_Machine.LOC_Doornumber <= GEO_Postcode.van and
BK_Machine.LOC_Doornumber <= GEO_Postcode.tem
Trigger runs fine but doesn't do a thing probably it can't find the machineid i think,
can someone help me?
Cheers WimmoSo sorry for posting such a stupid mistake from my hazy view today!!
< should be >
Trying to get awake today, sorry for the disturbance
Cheers Wimmo|||Cleaned up your code:create trigger trUpdateGEOData
on BK_Machine
after insert
as
update BK_Machine
set BK_Machine.LOC_Street = GEO_Postcode.STraatID,
BK_Machine.Loc_City = GEO_Postcode.PlaatsID
from BK_Machine
inner join GEO_Postcode on BK_Machine.loc_postalcode = GEO_Postcode.postcode
and BK_Machine.LOC_Doornumber <= GEO_Postcode.van
and BK_Machine.LOC_Doornumber <= GEO_Postcode.tem
inner join inserted on BK_Machine.MachineID = Inserted.MachineID
Now, it strikes me that the inner join on GEO_Postcode could return no records, in which case no BK_Machine records would be updated. It could also potentially return more than one record, in which case you would get unpredictable results for your update.|||Thanx Blindman,
What do you mean with the inner join: is it when there are more records inserted at the same time with the same machineid?
Wimmo
Friday, March 9, 2012
Need a trigger based on a row
I need to create a trigger to update a FLAG column by row modification. The table likes,
Col1 FLAG
a 0
b 0
c 0
d 0
e 0
If "a" in Col1 is changed, the "0" in same row (First row) should be changed to '1'. Other FLAG values should not be changed. The same rule for other row.
Anyhelp will be approciated.
Thanks a lot
ZYTIf col1 is a primary key, this is relatively easy. If you don't have a primary key, I don't know how to do it. Can you "fill in the blanks" a bit on your requirements?
-PatP|||Col1 doesn't have to be a primary key, but you do need a primary key. Put this in your trigger:
Update YourTable
set FLAG = 1
from YourTable
inner join Deleted on YourTable.PrimaryKey = Deleted.PrimaryKey
where YourTable.Col1 <> Deleted.Col1|||So what happens when the code that launches the trigger is something like:UPDATE yourtable SET col1 = 'a'All of the rows except for one change. Without a PK, things get ugly really fast from a SQL perspective, and I'd guess that the business rules would go berzerk!
-PatP|||All of the rows except for one change.-PatP
I think that is what he would want. All of the rows except one were modified, so set the flags on all but one record.
But yes, as I mentioned in my post, he is going to need a primary key on the table.