SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 unknown "Invalid attribute/option identifier" erro
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

sugar
Starting Member

USA
10 Posts

Posted - 06/29/2006 :  16:28:42  Show Profile
hi, i have a trigger in mssql wich should update a table in a msaccess database using the OPENROWSET, it is activated after an update action in the 'Item' table of the MSSQL

this is the trigger:

/* Trigger body */
UPDATE OPENROWSET('Microsoft.Jet.OLEDB.4.0','\\nas1\workware\OrderManager\MyData.mdb';'Admin';'********',Inventory)
SET Barcode = Inserted.ItemLookupCode
FROM Item, Inserted
WHERE LocalSKU IN (
SELECT SubDescription3 FROM Item WHERE ItemLookupCode = Inserted.ItemLookupCode)


and this is the error it is returning me:

[OLE/DB provider returned message: [Microsoft][ODBC Microsoft Access Driver]Invalid attribute/option identifier]


Special Notes:
* The query was tested with good results, i used this for testing:

/* Trigger body */
UPDATE OPENROWSET('Microsoft.Jet.OLEDB.4.0','\\nas1\workware\OrderManager\MyData.mdb';'Admin';'********',Inventory)
SET Barcode = Item.ItemLookupCode
FROM Item
WHERE LocalSKU IN (
SELECT SubDescription3 FROM Item WHERE ItemLookupCode = '10155A54')

* The mssql server daemon has full access to the "\\nas1" device.
* The error its produced when I update the "ItemLookupCode" field in the real word, it means in the sofware we're using at the office, its called Microsoft Retail Mangamentet System (RMS)

NOTE: Datatypes are the same, as I said query worked fine outisde of the 'Microsoft Retail Mangamentet System', and its structure its prety clean sql.

Thanks in advance.

Edited by - sugar on 06/29/2006 17:41:21

Kristen
Test

United Kingdom
22415 Posts

Posted - 06/30/2006 :  01:55:37  Show Profile
"i have a trigger in mssql wich should update a table in a msaccess database"

OMG !

But now no doubt you will tell me that there is a good reason for not doing this some other way?

Kristen
Go to Top of Page

sugar
Starting Member

USA
10 Posts

Posted - 06/30/2006 :  12:47:53  Show Profile
well, Im trying to do this because we have a store, and there are a POS manager, and a Warehouse manager, there are 2 different applications from 2 different sofware vendors, they must have almos the same data, and in order to keep the syncronization of the barcodes from both applications, i created that trigger, so when you change a barcode in the RMS, it should automatically update the sabe barcode in the msaccess database too.

or what more can i do?

thanks in advance
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/01/2006 :  05:52:16  Show Profile
I have a violent dislike of Triggers doing things "outside of SQL server" because it increases the risk that they will fail, and often in a way that has unexpected knock-on consequences.

For this type of scenario I would use the trigger to put data in a "staging table", and then have a scheduled task (every minute if necessary) process the data in the staging table into the other system.

That process can do something like:

(you need to add a BatchNo column to the staging table, which the trigger should leave NULL when it inserts new rows)

SELECT @intNextBatchNo = COALESCE(MAX(BatchNo), 0) + 1
FROM MyStagingTable

UPDATE MyStagingTable
SET BatchNo = @intNextBatchNo
WHERE BatchNo IS NULL

INSERT INTO ... AccessDatabase ...
SELECT *
FROM MyStagingTable
WHERE BatchNo = @intNextBatchNo

(You might need something a bit slicker that can discover what the last batch is that was sent to Access, and transmit all data with higher batch numbers - so if the current batch fails it will become part of the re-transmit attempt next time round - sort of FailSafe if you like!)

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000