Posted by Erland Sommarskog on 10/02/15 11:44

Jchick (jchickering@gmail.com) writes:
> This outta be an easy one for someone. I have the following code in my
> trigger. I am trying to connect to a DSN that is pointing to an Access
> database (.mdb). However, I am not sure how to actually write the
> Select From statement (see "A DSN I MADE" in the code below).
>
> Any hints as to how I reference a DSN in the select statement?

You can use OPENROWSET you can specify a DSN, I believe, as you can
specify a provider_string.

However, Tom's suggestion of setting up a linked server is much better.
In that case you don't need any DSN.

> DECLARE @InvoiceNo VARCHAR(20)
> DECLARE @VendorName VARCHAR(50)
>
> SELECT @InvoiceNo = (select InvoiceNo from inserted)

Stop! A trigger fires once *per statement* and thus "inserted" can
hold many rows.

> SELECT @VendorName = (select VendorName from "A DSN I MADE" where
> InvoiceNo = @InvoiceNo)
>
> Update Invoices
> set VendorName = @VendorName
> where F_DocumentID = (select F_DocumentID from inserted)

So it would be:

UPDATE Invoices
SET VendorName = a.VendorName
FROM Invoices i
JOIN inserted ins ON i.F_DocumentID = ins.F_DocmentID
JOIN linkedstv...vendors v ON ins.InvoiceNo = v.InvoiceNo

Although this looks very funny. Assuming that InvoiceNo is the
primary key in Invoices, it's difficult for me to understand why
you would update other invoices than the one I inserted. But I don't
know the business problem, so I may be wrong.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

[Back to original message]