Trigger Question

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?

Thanks in advance

Chick

============start of trigger===========

CREATE TRIGGER [Populate Customer Name] ON [dbo].[Invoices]

FOR INSERT, UPDATE

AS

DECLARE @InvoiceNo VARCHAR(20)
DECLARE @VendorName VARCHAR(50)

SELECT @InvoiceNo = (select InvoiceNo from inserted)
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)

Check out "linked servers" in the BOL.  You can do something like:

EXEC sp_addlinkedserver
@server = 'MyLinkedServer',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc =  'C:\MSOffice\Access\
Samples\Northwind.mdb'
GO

SELECT *
FROM MyLinkedServer...Employees

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON   Canada
.
"Jchick" <jchickering@gmail.com> wrote in message
news:1144454684.937047.134340@v46g2000cwv.googlegroups.com...
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?

Thanks in advance

Chick

============start of trigger===========

CREATE TRIGGER [Populate Customer Name] ON [dbo].[Invoices]

FOR INSERT, UPDATE

AS

DECLARE @InvoiceNo VARCHAR(20)
DECLARE @VendorName VARCHAR(50)

SELECT @InvoiceNo = (select InvoiceNo from inserted)
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)

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

My intent with the trigger is this: I have an Document Management
System that is used to manage Invoices. The indexes (fields) are held
in a SQL server. When the key index field is entered, I'd like to go
out to an ODBC database  and retrieve the related fields.  The database
is not Access, I think it is JDA or something - it is ODBC and we can
connect to it using a DSN. It has all the fields necessary based on
Invoice Number. I just want to pull those fields into the SQL table
when an invoice number is entered or changed.

I'll take a close look at linked servers at Tom's suggestion. By the
way, does BOL refer to Microsoft's books online?

And if the database is just a connection via DSN, I suppose that
datasrc path must be changed. Guess I need to do some serious reading.

Thanks for the replies!!!

Chick

Jchick (jchickering@gmail.com)  writes:
> I'll take a close look at linked servers at Tom's suggestion. By the
> way, does BOL refer to Microsoft's books online?

Yes.

> And if the database is just a connection via DSN, I suppose that
> datasrc path must be changed. Guess I need to do some serious reading.

A DSN is just a pre-stored connection string, I believe. I never fully
understood or liked DSNs. They just add complexity to the system, in my
opinion.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx