I have a linked server to an AS/400 using the IMBDASQL OLEDB provider. I am avoiding hard-coding library names so the linked server's connection string includes:
Default Collection=;Naming Convention=1;Library List=LIB1,LIB2,LIB3.
Based on this post. I can SELECT and DELETE from unqualified tables, e.g.
select t1.* from openquery(linked_server, 'select col1 from mytable') as t1
delete from openquery(linked_server, 'select col1 from mytable')
but the table name must be qualified when inserting like this
insert into openquery(linked_server, 'select col1 from mylib.mytable')`
or it will fail with this error
OLE DB provider "IBMDASQL" for linked server "linked_server" returned message "SQL0104: Token / was not valid. Valid tokens: <IDENTIFIER>. Cause . . . . . : A syntax error was detected at token /. Token / is not a valid token. A partial list of valid tokens is <IDENTIFIER>. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token /. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.". Msg 7343, Level 16, State 2, Line 83 The OLE DB provider "IBMDASQL" for linked server "linked_server" could not INSERT INTO table "[IBMDASQL]".
How can I perform an insert without qualifying the table name with the library name?