Re: Trigger to populate table or database

Jchick (jchickering@xxxxxxxxx) writes:
I'm on to new problems. Trying to learn how to set up the trigger to
send to an Access database's table. I was playing with this idea some
time ago and I believe you got me on the path to Linked Servers. So,
I've got a linked server to an Access database, one table with the
exact same 'fields'. I still want it to write only if the record is
unique. I'm trying to use the suggestions you gave me earlier:

CREATE TRIGGER [Populate AccessTable] ON [dbo].[ClientFile]

AFTER INSERT AS

INSERT mastertable (ProspectName)

SELECT i.ProspectName
FROM inserted i
WHERE NOT EXISTS (SELECT * FROM mastertbl.m WHERE m.ProspectName =
i.ProspectName)

In theory, just replace "mastertbl" with LINKEDSERVER...mastertbl. Maybe
you will have to put something between the dots, Access is not my realm.

But I would not be surprised if it fails, there is always a lots and
ifs and buts with linked server.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • RE: Linked server 7302 error
    ... button for each of the 'Access Permissions' ... the startup account for the SQL Server service is in the ... Microsoft Online Partner Support ... Under the linked server security tab, ...
    (microsoft.public.sqlserver.connect)
  • Re: Microsoft Excell Connection
    ... Use your linked server to Access then as the source by adding a SQL Server ... You should be able to add an Access Connection to a secured Access MDB ... properties enter the location of your system database. ...
    (microsoft.public.sqlserver.dts)
  • Re: Linked Server: How to check if server exists?
    ... The root problem is described in the Books Online topic Batches. ... Rick Byham, SQL Server Books Online ... linked server don't run on those development machines not having that linked ...
    (microsoft.public.sqlserver.connect)
  • Re: SQL Server 2000 linked server problem
    ... The delegated Kerberos authentication does not work stably. ... of SQL Server 2000 to fix some known bugs on the SQL Server side. ... the both two SQL Server instances (local and linked server), ... If Windows Firewall or other third party firewall is used, ...
    (microsoft.public.sqlserver.connect)
  • Re: SQL Server 2000 linked server problem
    ... if we are able to connect the linked server ... successfully on the SQL Server itself but fails to connect from the remote ... client, the most common cause of this behavior is the delegation problem. ... If Windows Firewall or other third party firewall is used, ...
    (microsoft.public.sqlserver.connect)
Loading