Johan_1975 - Thursday, January 19, 2017 2:56 AM

Sue_H - Tuesday, January 17, 2017 12:14 PM

Johan_1975 - Tuesday, January 17, 2017 10:42 AM

Hi,
I am migrating SQL from 2008R2 to 2014, and I met a problem I never had with Openquery, based upon a DB2OLEDB Linked Server.
I'm tryng to execute this request in SQL Server 2008R2, and it works fine :

SELECT *
    FROM OPENQUERY(DISTANTSERVER, 'SELECT [myColumn]
    FROM DistantSchema.Mytable')

But when I try it in SQL Server 2014, I got this error :

OLE DB provider "DB2OLEDB" for linked server "DISTANTSERVER" returned message "ILLEGAL SYMBOL "[DISTINCT ALL * + - NEXTVAL PREVVAL ROW ( USER CURRENT". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: SQLSTATE: 42601, SQLCODE: -104".
Msg 7321, Niveau 16, État 2, Ligne 1
An error occurred while preparing the query "SELECT [myColumn]
    FROM DistantSchema.Mytable" for execution against OLE DB provider "DB2OLEDB" for linked server "DISTANTSERVER".

I searched a bit and found out that removing square brackets could fix it, but I would prefer keep the original syntax (in order to not rewrite all my queries).
Any ideas would be helpful 🙂
Thanx!

Do you have different DB2 OLE DB providers/drivers on the different servers? The error you are getting back is a DB2 error related to using illegal characters, incorrect qualifications, along those lines. Not sure how it's actually suppose to work in DB2 but you would want to make sure you are using the correct syntax for DB2 when you are doing Openquery.

Sue

Thanks a lot Sue!
Indeed as I thought I didn't have the same version on the both servers, I tried to  uninstall DB2OLEDB Provider but without success, then to repair it without more success.
Nevertheless, when I reboot the server, My queries in OpenQuery seem to behave correctly, I don't know how or why.
I'll try to check that in the next days (and maybe avoid OpenQuery for a more sympathetic 4 parts name).
Thanx a lot again, you put me on the right way 🙂