unknown "Invalid attribute/option identifier" erro

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

unknown "Invalid attribute/option identifier" erro

Author

Topic

sugar
Starting Member

10 Posts

Posted - 2006-06-29 : 16:28:42

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.

Kristen
Test

22859 Posts

Posted - 2006-06-30 : 01:55:37

"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

sugar
Starting Member

10 Posts

Posted - 2006-06-30 : 12:47:53

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

Kristen
Test

22859 Posts

Posted - 2006-07-01 : 05:52:16

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources