If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information:
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply).
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information:
My SQL Server is choking (syntax error) when I use sp_report SalesbyItemDetail with parameters like DateFrom = {d'2006-01-01'}. DateMacro parameters work fine. What syntax is expected for custom dates?
Thanks for the tip, Tom. Unfortunately, it didn't work out. I tried various kinds of date formats (hyphens, slashes, without, between double quotes, year first). No matter how I format the date condition, I get:
An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'. [OLE/DB provider returned message: [QODBC] Expected lexical element not found: <simpleterm>] OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare returned 0x80040e14].
What <simpleterm> lexical element is QODBC looking for?
Have a look at the "Review QODBC Messages" log in the QODBC Setup for what SQL statements are actually being sent from MS SQL Server to QODBC and adjust acccordingly.
Tom, in reference to my previous post. In all failed instances the QODBC message is:
IsAService: False SQLOptimize_OpenOptimizeDBHandle prepare: SELECT "type", "tbl_name" FROM "SQLite_MASTER" file is encrypted or is not a database
However, at the same time, I am able to query through the VB Demo, for instance. So it is not a DB/file availability issue. I also cross-checked a standard query with DateMacro and it works through the linked server. This lets me conclude that the date syntax cannot be digested. I, by the way, also ran into problems with WHERE clauses in SQL Server, which caused a similar error, but saying it couldn't find the column (e.g. the Account name I wanted to filter by).
Hello Tom, switching off the Optimizer didn't help. While specific time ranges are nice to have, not being able to query specific accounts would not make QODBC through a Linked Server a feasible option for us. Short of programming an external ETL process, is there something else we can check?
What I don't have are the actual queries you are running. Please give me the examples that work using VB Demo and then the same ones as openqueries that don't work for me to look at.
This is the query that works in VB Demo app: sp_report SalesByItemDetail show Account, Text, Blank, TxnType, Date, RefNumber, Memo, Name, Quantity, UnitPrice, Amount parameters DateMacro = 'LastQuarter' where Account = 'Maintenance Renewal'
The error below is thrown by this query in the SQL Linked Server: SELECT * FROM OPENQUERY (Quickbooks, 'sp_report SalesByItemDetail show Account, Text, Blank, TxnType, Date, RefNumber, Memo, Name, Quantity, UnitPrice, Amount parameters DateMacro = LastQuarter where Account = "Maintenance Renewal"')
OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: [QODBC] Column not found: Maintenance Renewal] OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare returned 0x80004005: ].
It doesn't matter, by the way, if an account name is used in the WHERE clause that wouldn't require quotes like Services. To demonsrate that the Linked Server connection works, I attach a screenshot of a query that doesn't filter by an account:
