January 17, 2017 at 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!
Johan Jezequel
** Sorry for my english, I promess you I do my best **
January 17, 2017 at 12:14 pm
Johan_1975 - Tuesday, January 17, 2017 10:42 AMHi,
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
January 19, 2017 at 2:56 am
Sue_H - Tuesday, January 17, 2017 12:14 PMJohan_1975 - Tuesday, January 17, 2017 10:42 AMHi,
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 🙂
Johan Jezequel
** Sorry for my english, I promess you I do my best **
January 19, 2017 at 3:18 am
Johan_1975 - Thursday, January 19, 2017 2:56 AMSue_H - Tuesday, January 17, 2017 12:14 PMJohan_1975 - Tuesday, January 17, 2017 10:42 AMHi,
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 🙂
I've a feeling that using square brackets ([]) for identifiers is proprietary to SQL Server. Have you tried double quotes ("") instead? That said, I don't know why it suddenly start working again after you reboot.
John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply