OPENQUERY question

[Sylvor]

Hi,

I am trying to INSERT some data into a table on a linked server. To do this I am trying to use OPENQUERY / OPENROWSET.

I can successfully select data from tables on the linked server, but I cannot get the syntax correct on the insert. (The data to insert resides on the MSSQL server and this code is to be used in a trigger on one of the MSSQL tables)

Can anybody help me with this?

Thanks,
David

Dave Bennett

 

[Sylvor]

It may be useful for me to show my trigger code

CREATE TRIGGER HN_TRAN_STKMOVE
ON STK_MOVEMENTS
AFTER INSERT AS
IF (SELECT sm_status FROM INSERTED) = 'O' RETURN -- Ignore stock movements out (O)
IF (SELECT max(loc_usersort1) FROM stk_location INNER JOIN inserted on inserted.sm_location = stk_location.loc_code) !='HF-' RETURN -- Ignore movements into locations other than the finished goods location (HF-)
INSERT INTO TEMPTEST -- TEMPTEST is a local table in this sql db, but I want to change this to insert into the STOCKMOVE table in my linked server, called DBLINKED
SELECT code = sm_stock_code,  -- column names are the same in TEMPTEST and DBLINKED.STOCKMOVE
  qty = sm_quantity,
  cost = sm_costprice,
  updatedate = sm_date,
  batchref = (SELECT max(loc_usersort2) FROM stk_location INNER JOIN inserted on inserted.sm_location = stk_location.loc_code)
FROM INSERTED

Comments should indicate what I'm trying (failing) to achieve.

TIA,
David

Dave Bennett

 

[JamesLean]

If you have the linked server set up then it should just be:

INSERT server_name.db_name.owner_name.table_name (col_list)
SELECT ...

--James

 

[Sylvor]

Hi James,

I do have the linked server set up, but due to differences in how the schema rowsets are returned between SQL Server and the linked servers OLE DB Provider, the four-part syntax is not supported. I have no choice but to use OPENQUERY.

Any other ideas?
Thanks!
David

Dave Bennett

 

[JamesLean]

What database system are you linking to?
Can you show us what you've tried that is failing, along with the error?

--James

 

[Sylvor]

Hi James,

The database is called Advantage database, by Extended Systems.

I haven't specifically tried much yet, I've been playing around but I cannot work out how to an insert INTO the linked database FROM the SQL database, using a pass-through query.

If I try to do anything (even a select) using the four-part name, I get this error:

Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'Advantage.OLEDB.1'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.

Advantage knowledgebase says that 4-part names are not supported, so no surprises that it won't work.

I'm playing about with the insert query at the moment, trying to get something decent to post here.

Thanks,
David

Dave Bennett

 

[JamesLean]

Not knowing anything about Advantage db this is only a guess, but have you tried leaving out parts of the qualified name. I would start with the owner:

SELECT * FROM server.database.table

--you might need to leave in the spare dot
SELECT * FROM server.database..table

Or leave out the database:

SELECT * FROM server.owner.table
SELECT * FROM server..owner.table

--James