[dba-SQLServer] SQL error in Excel query to IBM DB2 data source

Fri Apr 29 13:33:47 CDT 2016

Thanks Scott,

All good ideas.  If I run the query without renaming the columns all is well.  It seems that MS Query does not like the original data source field names to be changed to something else.  

The only reason we found this out is because we tried to go back into a query and edit a date range.  

So. 	1.the query runs fine on all four of our PCs.
	2.  the error only occurs when one right clicks on the table in Excel and chooses Table, then Edit Query.  This launches the Microsoft Query window and displays the actual query.  
	3.  In the actual query in the Criteria field we try to adjust the value for the date.
	4. then when we click Query Now the error message pops up.
	5. By clicking the SQL button we can see the SQL statement and edit as you suggested but running the query still returns the same error.

NOW here's what's really weird:  On the four, identically set-up PCs we run this Excel sheet on -- only one can successfully change criteria and have the query work, the other three all can successfully use the spreadsheet, but cannot adjust any criteria in the query.

So the error message says that SQL doesn't like the single '' marks for the alias we want to use.  But I'm thinking that it might be some setting I'm missing in the ODBC administration, maybe something in the Conversions or CCSID?

Mike

-----Original Message-----
From: dba-SQLServer [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Scott Marcus
Sent: Friday, April 29, 2016 12:18 PM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] SQL error in Excel query to IBM DB2 data source

You have quote marks around ShipDate and Location. Remove those also.

If the above does not work then...

Try modifying the string without renaming any columns. If that works, then modify the name on just one column to see if that works (don't use quote marks). Add another column name change after each successful attempt. You may find that a column name is causing the issue.

-----Original Message-----
From: dba-SQLServer [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Zimmer, Michael
Sent: Friday, April 29, 2016 12:47 PM
To: Discussion concerning MS SQL Server <dba-sqlserver at databaseadvisors.com>
Subject: Re: [dba-SQLServer] SQL error in Excel query to IBM DB2 data source

Hi Gustav,

I've tried to remove the quotes with no results.  I don't know if putting in the quotes is hard coded into the query wizard or not, but removing them returns the exact same error.

I cannot find  a place in the ODBC administration to specifiy the SET QUOTED_IDENTIFIER which is an answer I say on the web somewhere.

IBM has a page about this problem with no resolution unfortunately.  http://www-01.ibm.com/support/docview.wss?uid=nas8N1017228

The other thing I tried is to change the encoding of the CCSID string from ASCII to Unicode, then Unicode2.  Again with no results.

This is a head scratcher.

-----Original Message-----
From: dba-SQLServer [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, April 29, 2016 11:40 AM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] SQL error in Excel query to IBM DB2 data source

Hi Mike

I guess the alias should be non-quoted:

    KMRL01.MRLDT AS ArrivalDate

/guess

-----Oprindelig meddelelse-----
Fra: dba-SQLServer [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Zimmer, Michael
Sendt: 29. april 2016 18:33
Til: dba-sqlserver at databaseadvisors.com
Emne: [dba-SQLServer] SQL error in Excel query to IBM DB2 data source

Hi all,

We have an odd situation:  A query using data from an ODBC connection runs fine on our Win 7 PCs with Excel '07.  However, the query can only be edited on one machine, on all others editing the query returns this error:

"SQL0104 - Token 'ArrivalDate' was not valid. Valid tokens: <IDENTIFIER>."

In the query we have a data field called "MRLDT" and we renamed it to "ArrivalDate" so that it is meaningful to the end users.

Here is the SQL statement for the query:
SELECT KMRL01.MPROD, KMRL01.MRLDT AS 'ArrivalDate', KMRL01.MRDTE AS 'ShipDate', right(MPRNT,6) AS 'Location', KMRL01.MQTY FROM MAN400.BPCSF9.KMRL01 KMRL01 WHERE (KMRL01.MPROD='750197') AND (KMRL01.MRDTE<=20160615)

No matter what I've tried, I cannot find a way to amend the SQL statement so that I can edit the arrival date ranges.

However, the spreadsheet as a whole runs fine on all of our PCs.  Just no editing of queries where we rename the column headings to a meaningful title.

Thanks

Mike

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

NOTICE:  This electronic mail transmission is for the use of the named individual or entity to which it is directed and may contain information that is privileged or confidential.  If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of any information contained herein is prohibited.  If you have received this electronic mail transmission in error, delete it from your system without copying or forwarding it, and notify the sender of the error by replying via email or calling TSS Technologies at (513) 772-7000, so that our address record can be corrected.
Any information included in this email is provided on an “as is” and “where as” basis, and TSS Technologies makes no representations or warranties of any kind with respect to the completeness or accuracy of the information contained in this email.

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com