|
|
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
Navigation:
[Reply to this message]
|