Office 2013 MS Query ODBC incompatibility

Hello all,

I just experienced an incompatibility, if not a bug, with Office 2013 on Windows 7. The problem is related to Office 2013 Microsoft Query 15.0.4420.1017 (32-bit).

In Office 2013 Excel I do the following:

Data
-> Get External
-> from other sources
-> from MS Query -> Choose Data Source
-> Queries
-> Browse ...
-> open one of my .dqy queries Open
=> error message 37000

I created an ODBC trace file where the problem is obvious: The SQL statement is truncated to 255 characters, causing a syntax error on the ODBC backend (here, this is a proprietary ODBC driver, but this should not matter). See below.

Is this the right place to report that problem?

Thanks,
Roland

MSQRY32 9c8-4e4 ENTER SQLPrepare HSTMT 0x024F5C48 UCHAR * 0x009D5A48 [ 237] "SELECT customers.CUSTOMER_NAME, INVOICES.CUSTOMER_NO, Sum(INVOICES.AMOUNT) , INVOICES.|DESC| FROM toydb.customers customers, TOYDB.INVOICES INVOICES WHERE customers.CUSTOMER_NO = INVOICES.CUSTOMER_NO GROUP BY INVOICES.CUSTOMER_NO ORD" SDWORD 237 MSQRY32 9c8-4e4 EXIT SQLPrepare with return code -1 (SQL_ERROR) HSTMT 0x024F5C48 UCHAR * 0x009D5A48 [ 237] "SELECT customers.CUSTOMER_NAME, INVOICES.CUSTOMER_NO, Sum(INVOICES.AMOUNT) , INVOICES.|DESC| FROM toydb.customers customers, TOYDB.INVOICES INVOICES WHERE customers.CUSTOMER_NO = INVOICES.CUSTOMER_NO GROUP BY INVOICES.CUSTOMER_NO ORD" SDWORD 237 DIAG [37000] [Marxmeier][SQL/R ODBC Server]37000 - unexpected symbol (#1) syntax error near "ORD" Unexpected symbol or invalid SQL/R language statement. (-1) MSQRY32 9c8-4e4 ENTER SQLExecDirect HSTMT 0x024F5C48 UCHAR * 0x009D5A48 [ 237] "SELECT customers.CUSTOMER_NAME, INVOICES.CUSTOMER_NO, Sum(INVOICES.AMOUNT) , INVOICES.|DESC| FROM toydb.customers customers, TOYDB.INVOICES INVOICES WHERE customers.CUSTOMER_NO = INVOICES.CUSTOMER_NO GROUP BY INVOICES.CUSTOMER_NO ORD" SDWORD 237 MSQRY32 9c8-4e4 EXIT SQLExecDirect with return code -1 (SQL_ERROR) HSTMT 0x024F5C48 UCHAR * 0x009D5A48 [ 237] "SELECT customers.CUSTOMER_NAME, INVOICES.CUSTOMER_NO, Sum(INVOICES.AMOUNT) , INVOICES.|DESC| FROM toydb.customers customers, TOYDB.INVOICES INVOICES WHERE customers.CUSTOMER_NO = INVOICES.CUSTOMER_NO GROUP BY INVOICES.CUSTOMER_NO ORD" SDWORD 237 DIAG [37000] [Marxmeier][SQL/R ODBC Server]37000 - unexpected symbol (#1) syntax error near "ORD" Unexpected symbol or invalid SQL/R language statement. (-1)

April 8th, 2013 2:15pm

Hi,

Try to convert the Query File from DQY to QRY Format.

Follow the steps:

1. Open your .dqy-File in MSquery from Office 2010 or earlier and copy the SQL-Code
 2. Open MSQuery 2013, start with a new query from the same database, 1 table, no matter what.
 3. In the new 2013 Query open the SQL code and replace it with the one from your old query
 4. Save the result as QRY and not DQY File in the File/save menu

Done.

This QRY file is nw working fine with the MS Query from Office 2013.

Similar case here for reference:

http://social.technet.microsoft.com/Forums/en-US/officeitpro/thread/a6af0590-cf1d-43b7-8b59-0fe8e63f95b8/

Free Windows Admin Tool Kit Click here and download it now
April 9th, 2013 8:21am

Dear Jaynet,

Thank you for your help.

Regards,
Roland

April 9th, 2013 9:27am

Hello again,

The proposed workaround appears to me a bit inconvenient.

I found that the procedure below works as well to save the DQY file as QRY:

  1. in Excel 2013: Data -> Connections -> Add -> Browse for More...
  2. open the old *.dqy file
  3. click Properties... button and switch to the Definition tab

  a) use Edit Query... button and Save As *.qry in MS Query window
  b) or use Export Connection File... to save as office *.odc XML

Another quirk that we found in this context is that in Query the File Open dialog
does not show any QRY file if there is at least one DQY file in the selected directory.

Roland

Free Windows Admin Tool Kit Click here and download it now
April 10th, 2013 8:53am

Thank you for submitting this workaround, Roland.  I didn't have access to MSQry 2010 after uninstalling Office 2010 and installing Office 2013.

I would still like to see a Solution from MS that addresses the truncate issue instead of having to do a workaround, but your workaround is the best "solution" for now. :)

Thanks!

  • Edited by ChrisHug 8 hours 50 minutes ago clearer response
June 11th, 2015 2:03pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics