Unanswered: Help!, sql server 2000 vs Navision db!
ive been tasked with pulling out data from a navision database via odbc using sql server 2000. Ive taken the approach of using a linked server to the navision data source and trying to pull data out of it using the "openquery" method.
Unfortunately, depending on the consistency of the date based columns im having real problems successfully running queries against the navision db.
Apparantly, if your "external" database (in my case the Navision db) has date columns with year values < 1753 (which is the lowest the sqlserver/odbc combo to the Navision database can deal with) any query attempting to pull that date field results in the error : "ERROR converting data type DBTYPE_DBDATE to datetime".
Now as i understand it, when using "OPENQUERY", the actual "query code" contained in the method is executed on the external database then pulled back to sql server. On that basis, I am trying to clean up a potentially bad date field by coding the following :
SELECT * FROM OPENQUERY(NAVISION_DB, 'SELECT "Verification Completed" FROM "FDR Client" WHERE "Verification Completed" > ''01/01/1753''');
As you can see, if successful, this query will pull back a reduced data set, which is fine. But, because Navision allows blanks in table/column names (and as whoever designed this database decided to follow suit) I have to surround references to values with blanks in with double-quotes. The statement i have shown above wont even compile properly and its driving me BONKERS!!
I have a sneaky suspicion the problem revolves around the combination of double quotes/single quotes that the statement has.
I know I am close to getting it right as the following DOES work :
SELECT * FROM OPENQUERY(NAVISION_DB, 'SELECT "Verification Completed" FROM "FDR Client" WHERE "Verification Completed" is NULL');
Anyone out there who can help, it really seems difficult to talk to Navision using sqlserver 2000/linked servers !!