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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
22859 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
22859 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.11 seconds. Powered By: Snitz Forums 2000