Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Crystal report getting sql error

Posted on 2012-09-05
7
Medium Priority
?
1,430 Views
Last Modified: 2012-09-05
When I run my report I get the error
'cannot resolve the collation conflict between "SQL Latin1_General_CP1_CI_AS" and
"Latin1_General_BIN" in the equal to operation.

I did a 'Database Show SQL Query' and copy the query that runs the report.
I  pasted it in Query window of SQL server and format it for SQL select  statement.
I added ' collate Latin1_General_BIN' at the end of a join and I did not get an error.

My question is how would I add the 'collate Latin1_General_BIN' in the sql of the report
so it would run?
0
Comment
Question by:zachvaldez
  • 3
  • 3
7 Comments
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 38368562
I am not sure how to get round this in Crystal. Is there a good reason why the two tables have a different collation?

If there is no good reason you could change it on SQL by doing

ALTER TABLE table-name
ALTER COLUMN thejoincolumn varchar(10) COLLATE SQL Latin1_General_CP1_CI_AS

varchar(10) = whatever the datatype is
0
 

Author Comment

by:zachvaldez
ID: 38368628
I made both columns char(10) but I still get the same error
0
 
LVL 13

Accepted Solution

by:
Philip Pinnell earned 1060 total points
ID: 38368697
It is changing the collating of any columns that are compared in the sql that is important

ALTER TABLE table-name
ALTER COLUMN thejoincolumn varchar(10) COLLATE SQL Latin1_General_CP1_CI_AS
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Expert Comment

by:Jared_S
ID: 38368793
You would have to add the fields back on to your report, but you could try pasting your SQL query in as a new command in the database expert.
0
 

Author Comment

by:zachvaldez
ID: 38368851
I GOT IT!  Im working on it for two days!!! Thanks. Excellent
0
 

Author Closing Comment

by:zachvaldez
ID: 38368856
This is great!!
0
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 38369701
Thanks
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question