Trigger Question

[Jchick]

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

============sta rt 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)

[Tom Moreau]

Re: Trigger Question

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

EXEC sp_addlinkedser ver
@server = 'MyLinkedServer ',
@provider = 'Microsoft.Jet. OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'C:\MSOffice\Ac cess\Samples\No rthwind.mdb'
GO

SELECT *
FROM MyLinkedServer. ..Employees

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"Jchick" <jchickering@gm ail.com> wrote in message
news:1144454684 .937047.134340@ v46g2000cwv.goo glegroups.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

============sta rt 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)

[Erland Sommarskog]

Re: Trigger Question

Jchick (jchickering@gm ail.com) writes:[color=blue]
> 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?[/color]

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.
[color=blue]
> DECLARE @InvoiceNo VARCHAR(20)
> DECLARE @VendorName VARCHAR(50)
>
> SELECT @InvoiceNo = (select InvoiceNo from inserted)[/color]

Stop! A trigger fires once *per statement* and thus "inserted" can
hold many rows.
[color=blue]
> 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)[/color]

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...ven dors 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@sommarsk og.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

[Jchick]

Re: Trigger Question

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

[Erland Sommarskog]

Re: Trigger Question

Jchick (jchickering@gm ail.com) writes:[color=blue]
> I'll take a close look at linked servers at Tom's suggestion. By the
> way, does BOL refer to Microsoft's books online?[/color]

Yes.
[color=blue]
> 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.[/color]

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@sommarsk og.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