"Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in Reporting Services

Question

Dear All,

I'm having an odd problem and I'm stuck on what else to do, I have a stored procedure which runs the Report in Reporting Services. The Stored Procedure Executes fine in SQL Server Engine but when I try to run the Stored Procedure in Reporting Services it throws the following error:

• An
  error occurred during client rendering.
  
  • An
    error has occurred during report processing.
    
    • Query
      execution failed for dataset 'DataSet2'.
      
      • Cannot
        resolve the collation conflict between "Latin1_General_CI_AS" and
        "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

My understanding is that if there are no issues when the Stored Procedure is executed then it should be fine in Reporting Services, as the Data is coming directly from SQL Server; unless I need to do something else in Reporting Services?

Thanks in advance!

Answer 1

Dear All,

I'm having an odd problem and I'm stuck on what else to do, I have a stored procedure which runs the Report in Reporting Services. The Stored Procedure Executes fine in SQL Server Engine but when I try to run the Stored Procedure in Reporting Services it throws the following error:

An error occurred while executing the query.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. (Microsoft SQL Server Report Designer)

My understanding is that if there are no issues when the Stored Procedure is executed then it should be fine in Reporting Services, as the Data is coming directly from SQL Server; unless I need to do something else in Reporting Services?

Thanks in advance!

 

Answer 2

Can you refer to this link and try using the COLLATE database_default keyword in your joins?

http://road-blogs.blogspot.com/2010/06/collation-conflict-error.html

If it doesnt work, can you paste your SP code?

---

Cheers,
Jason
P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

Answer 3

Thanks for the reply!

As I said, the Query works fine when I run it in SQL Server engine but the problem is when I try to run it on reporting Services; which is why I'm confused!

Answer 4

can you tell how you running this sproc in your report? also, check the parameter section and see if you passing the correct param. in report server, are you using shared datasource or connection string? check and make sure your Dataset2 in report server pointing to the right datasource.

---

Think out of the box

Answer 5

Hi There - I think the issue here may be that the database context (which determines the collation used by your query) is different between when you are executing the procedure in the report vs when you are executing elsewhere, which I assume is SQL Server Management Studio.

RS should not otherwise be doing anything differently from any other SQLClient application like SQL Server Management Studio. So you might want to double check that the default database in your report's datasource is the same as the current database when you're conecting with Management Studio.

It may be helpful to add a new data set in your report to execute the query below, and run it in Management Studio as well, if they are different, my theory should be correct.
 
Select SERVERPROPERTY(N'Collation') as 'Collation', db_name() as 'DBName'

Thanks, Dean

---

This posting is provided "AS IS" with no warranties, and confers no rights.