Print Page | Close Window

Error in SQL Connection

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=1257
Printed Date: 19 Apr 2024 at 1:26am


Topic: Error in SQL Connection
Posted By: jjcyeshua
Subject: Error in SQL Connection
Date Posted: 30 Aug 2007 at 3:49pm
Hi! guys,
 
Need help.. I connected ODBC so that i could write sql commands, but i get this error... any suggestion on how i could make this go away?
 
Error in compiling SQL Expression:
Database Connector Error: '42000:[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'select'.[Database Vendor Code:156]'.


Replies:
Posted By: BrianBischof
Date Posted: 30 Aug 2007 at 4:53pm
This is a pretty generic error message. Sounds like a typical SQL syntax error. I would copy the SQL into the Query Analyzer to get a better error message.

-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: jjcyeshua
Date Posted: 30 Aug 2007 at 4:59pm
Not sure what you mean run it using query analyzer. but the sql command that i wrote is working under sql server. It was just a simply select * , to make sure at its working. not entirely sure if this helps or even answer your comment... SOrry

Posted By: BrianBischof
Date Posted: 30 Aug 2007 at 5:58pm
Well, that is what I meant. The error message is too generic to find out what is wrong. I thought maybe you had a SQL syntax error. Are you sure that CR has permission to access all the necessary tables? 

-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: jjcyeshua
Date Posted: 30 Aug 2007 at 6:02pm
Yes, it can access all the tables. Whenever i drag and drop any tables into the report itself, it works fine. The tables shows as well as the data. Not sure if i'm typing it correctly or if CR uses different syntax. Any ideas why?

Posted By: BrianBischof
Date Posted: 30 Aug 2007 at 6:13pm
Crystal will use a different syntax for its own queries (use the View SQL Query option to see), but the SQL you enter is passed to the server untouched.

Also, I would create a new report with a very simple query in the Command object. Just "SELECT * FROM table" and see if it takes it. If so, add on incrementally from there to see what causes the error.

Can you post the Command object's SQL here just for kicks?

-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: BrianBischof
Date Posted: 30 Aug 2007 at 6:15pm
There is a tool that gets installed with MS SQL Server called SQL Profiler. You can use it to see the query that CR passes to SQL and see if something funny is happening behind the scenes.

-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: jjcyeshua
Date Posted: 31 Aug 2007 at 2:21pm

I think i may have posted my question in correctly (sorry again). Where am having problem is creating sql expression not sql command. When i tried creating an sql command, it works perfectly, but when i just create sql expressions thats when i get the error message.. Sorry for the confusion... and whenever I create an sql command. the option of creating an sql expression disappear..  thanks for the help again

Posted By: BrianBischof
Date Posted: 01 Sep 2007 at 1:49am
Oh. I see. Well, at least it makes more sense now.... Creating SQL Expressions is dependent upon your database and the database driver. If the SQL Expression isn't listed, then your driver doesn't support. Try using the ODBC driver for the database. This has better chance of supporting it.

-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>


Print Page | Close Window