Crystal Sql Designer

[kashyap]

Hi

When I write a query in Crystal Sql Designer
and run it, the query works fine but the length of all column is one.
If a simple query which has 2 fields in a table
like select custid, custname from customers gives 2
columns with length 1.
When I make a report using this query it gives me the same
result with length of columns 1.
I am using crystal report 7.0
Please help me to solve the problem

Thanks in advance

Kashyap
shah_kashyap@rediffmail.com

 

[kenhamady]

Something is wrong with your configuration.
What database are you connecting to?
What is the SQL statement from the last tab?
Can you test another SQL based tool to see if you get these columns correctly? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[msloan]

I too am getting this error. I am connecting to a SQL Server 7.0 DB via an ODBC connection. When I put the SQL statement into a view, the results are fine. And just for fun, I stuck the SQL into Access - no problems here either.

Any ideas?

Thanks

Matt

 

[kenhamady]

This happens with any SQL statement?
Can you post an example?

I have never seen this, and I haven't found anything about it in the knowledge base. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[msloan]

Yes, this happens with any SQL statement that I try to execute.

Here is a sample:

SELECT tblVendor."fldPostKey", tblVendor."fldVendorName",
tblVendor."fldVendorAddress1", tblVendor."fldVendorAddress2",
tblVendor."fldVendorCity", tblVendor."fldVendorCountyState",
tblVendor."fldVendorPostalCode", tblVendor."fldCountryKey",
tblVendor."fldVendorPhoneNo", tblVendor."fldVendorFaxNo",
tblVendor."fldVendorInternetAddress", tblVendor."fldVendorMobile",
tblVendor."fldVendorContact", tblVendor."fldVendorEFTSwift",
tblVendor."fldVendorSpecialInfo", tblVendor."fldVendorRetired",
tblCurrencySymbol."fldCurrencySymbolCode",
tblCountry."fldCountryName"
FROM ((tblVendor LEFT JOIN tblCurrency ON tblVendor.fldCurrencyKey = tblCurrency.fldCurrencyKey) LEFT JOIN tblCurrencySymbol ON tblCurrency.fldCurrencySymbolKey = tblCurrencySymbol.fldCurrencySymbolKey) LEFT JOIN tblCountry ON tblVendor.fldCountryKey = tblCountry.fldCountryKey

Thanks

Matt

 

[kenhamady]

This is a weirdy.
Did this ever work before or is this your first try with this?
Can you try it on a different PC?
The only other thing I could suggest is a reinstall of CR.

It could be a conflict between CR and your ODBC driver. Anything unique about your configuraton? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[msloan]

This is my first day with CR. I have tried to dexecute this on another pc, but came up with the same results as on mine.

To the best of my knowledge, there isn't anything fancy about the configuration. I created the ODBC and use it on many other applications with no problems.

I was hoping to avoid a reinstall, but at this point it might be worth a try.

 

[kenhamady]

If it happens on two different installs, then a reinstall of CR may not do it. What version and edition of CR? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[msloan]

we are using 7.0.0.0 and I believe that the edition is 7.0.1.192

 

[JustineB]

Hi!
This is a bit of a long shot, but I had problems with the type of SQL that Crystal generated.
Please find part of a document that I wrote outlining the problems and how they were fixed. This occurred with version 6 when we upgraded from version 5 (about 4years ago) so may not be relevant now.
The problems that we encountered were a series of error messages (depending on the OS)

"Machines using the P2SODBC shipped with Crystal Reports 6 have been translating the report SQL to ANSI format which is not very flexible (resulting in the error above). Machines using the P2SODBC version: 5.0.0.20 interpret the report SQL to transact-SQL which works.

Syntax type which works:

SELECT
salesorders."son", salesorders."sostate",
salesitems."sona", salesitems."sonitem"
FROM
"db"."dbo"."salesorders" salesorders,
"db"."dbo"."salesitems" salesitems
WHERE
salesorders."son" *= salesitems."sona"

Syntax type which doesn’t work:

SELECT
salesorders."son",salesorders."sostate",
salesitems."sona", salesitems."sonitem"
FROM
{ oj "db"."dbo"."salesorders" salesorders LEFT OUTER JOIN
"db"."dbo"."salesitems"
salesitems ON
salesorders."son" = salesitems."sona"}

Note the use of “LEFT OUTER JOIN”. If this type of syntax appears in the SQL of a report which doesn’t work, then try changing the version of your P2SODBC to: 5.0.0.20"

 

[kenhamady]

Justine has a valid point, and it never hurts to try updatign a dll.

However, the reason that I didn't suggest this is since the SQL is valid in the other apps I don't think the problem stems from CR generating bad SQL.

Are you using CR 7 professional or standard? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[msloan]

I believe it is CR professional.

Any suggestions on where to get the dll and how to update it? I have never done anything with dlls.

 

[kenhamady]

I think you can download it from the Crytsal web site. Search for it by name in their KB. And confirm that you are using professional. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/