Need help creating statement using OPENQUERY AND INSERT
I'm trying to Insert data from a linked server connection into one of my tables in the sql database. it seems to be giving me an error saying column cant be found. It only does this when I put the Where clause in the statement. I dont have the server in front of me but this is how my statement looks.
Insert into WorkList (DSK)
Select *
From OPENQUERY (SCH, 'Select Desk_ID from public.ACCOUNT Where Desk_ID = LA1')
Hello Mike11d11
Please try to send thedetails,like , Remote server type, error message, structure of the table...etc., so that problem can be solved fast.
Form your query it seem to be your attribute Desk_ID is of type varchar or so, then you must include the single quote around it
like
Insert into WorkList (DSK)
Select *
From OPENQUERY (SCH, 'Select Desk_ID from public.ACCOUNT Where Desk_ID = ''LA1''')
Hope this will solve your problem
Gurpreet S. Gill
This is using a linked server connected by ODBC connection. When I leave the where clause out of the statement below I get it to work perfectly. Even when I put criteria for another field it works, but not when I use "Where Desk_ID = "B01"" or if I use "Where Desk_ID = B01". Both give me the error below. When I look at the linked server details for the table it has the name which is "ACCOUNT", the schema which is "public", the catalog is blank, and the type is "user".
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [Attunity][Attunity Connect Driver][A033] The column B01 was not found.
]
This is what the help menu shows for the error. I must have some sort of syntax error but dont know any other way to input it.
Error 7321
Severity Level 16
Message Text
An error occurred while preparing a query for execution against OLE DB provider '%ls'. %ls
Explanation
Indicates a possible syntax error in the pass-through query's query string parameter.
Action
Verify that the query string is free of syntax errors (with respect to the query language supported by the OLE DB provider).
I think it might be the single " ' " that is missing around the specific desk_id. But for some reason it wont let me put the " ' " around the desk 'L01', says Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'L01'.
Do I need to put special characters around the ' to get it to read correctly inside the OPENQUERY statement.
figured it out, the specific desk needed to have 2 " ' " apostrophe's and not quotes around the desk. it had to look like
''L01'' Thanks for the help.
this is what my first reply says
if you need to put single quote with in a quoted string, you need to put the two single quotes, that is what i had shown.Just check my reply i had two single quotes + matching value + then again two single quotes, not double quots around the matching .
this will make more cleare, just copy paste this code
SELECT
'This is in the ''single quotes'' and this is in in the "double quotes" '
