Search This Blog

Cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.


Cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.


Issue -Msg 468, Level 16, State 9, Line 32
Cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
Issue Description -While working with sql i was fetching records from two different databases based on join condition


select T.Column1,t1.column2 from  Test.dbo.table1 T
join Test1.dbo.Table1 T1 on T.id=t1.id


error

Msg 468, Level 16, State 9, Line 32
Cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

So i checked the collation of database using below command
SELECT CONVERT (nvarchar, SERVERPROPERTY('collation'));

If you want to see collation of all the Databases run below command

SELECT name, collation_name FROM sys.databases;

I checked both the databases were using different collation.

So here is the solution

Just add collate Database_default in your join condition as below

select T.Column1,t1.column2 from  Test.dbo.table1 T
join Test1.dbo.Table1 T1 on T.id COLLATE DATABASE_DEFAULT =t1.id COLLATE DATABASE_DEFAULT

Note: You can face this type of errors when your fetching records from different databases having different collation
even if you are using temp table you can also face the same issue as temp tables are stored in temp db database and if
your database collation doesn't match with temp db database it will throw above error.
Collation affect join conditions,where,functions and temporary tables.




Creating PowerView Report in Excel 2013

Creating PowerView Report in Excel 2013


Power View is an interactive data exploration, visualization, and presentation experience that 
encourages intuitive ad-hoc reporting. 

Power View is a feature of Microsoft Excel 2013, and of Microsoft SharePoint Server 2010
and 2013 as part of the SQL Server 2012 Service Pack 1 Reporting Services
 Add-in for Microsoft SharePoint Server Enterprise Edition.



 Power View and Power Pivot are available in the Office Professional Plus and Office 365
 Professional Plus editions, and in the standalone edition of Excel 2013.

 In order to getting Started with Powerview Sheets you have to follow certain things.
 1-Install Silver light.
 2-Turn On power view add-in from excel Options below are the steps.
 Go to File > Options > Add-Ins.
In the Manage box, click the drop-down arrow > COM Add-ins > Go.
Check the Power View check box > OK.


Once done above we will start with our first Power view Report.

1-Select a blank work book.
2-connect to a datasource.
Two ways to connect to Data source-First by Data Tab
second using PowerPivot
We will use powerpivot.
Click Powerpivot Tab(Turn On Same as Powerview throgh option-Add-Ins)
Manage
Home
From Database
Select a table or query (using adventurework2008 DB -Person Table)
Finish
Click on excel sheet in top

--Inserting a power view sheet
Insert tab
Powerview.Screenshot1

--Now here we start  power view where  we will show multiple things in a single Power view sheet.

you can drop any column in any place and show them in table,barchart or pie chart like in screenshot2.


check the boxes you want to show in Table in our case we will check
First Name and last name in table
first table done

Now click on empty space

In order to covert table in chart select table

above click on Design
Bar chart
if you want pie chart click on other chart
drop Person type and Email promotion field in table
It will convert the Table in chart as your need.


The magic of powerview is click on any bar it will filter the dashboard based on bar values.
You can also add filter by clicking on table or chart corners.



Once done save your Dashboard in Share point site.

Note:In order to view power view Report in share point site we have to configure the Power view in share point first then only we can view Powerview Dashboard and reports in SP site.



Scroll bar Issue in Excel Reports

Scroll bar Issue in Excel Reports

Issue1-Removing Scroll Bars from Excel Reports
Issue2-Removing Row Numbers and column Names

Issue Description-While Creating any Excel report and viewing them in browser were having Scrooll bars in it and it was showing Row numbers(1,2,3...) and Column Names(A,B,C...) which were not required

Issue Screensot.

If you see the above Screenshot It contain

Issue 1 Solution

The Reason of having Scroll Bars in Excel Reports is due to size of Spread sheet
Select the Cell which are unused in order to select all the unused Cells
click on first unused column
Press ctrl +Shift+down key+Right key
It will select all the Unused column
Right click
Hide
It will hide all the unused Rows and COlumnn
Once you will hide the unused cells scroll bars wont't appear in your report


Issue 2 Solution
In order to remove the Row Names and columnNames

Click on view Menu
Uncheck Heading

Save Report
View in browser

Report won't have scroll bars ,Rows and column Heading.


The Login is from an untrusted domain and cannot be used with Windows authentication

The Login is from an untrusted domain and cannot be used with Windows authentication


Issue-Login failed:The Login is from an untrusted domain and cannot be used with Windows authentication.Error 18542

Issue Description-This Issue comes when you have different system in same domain and you want to connect with SQL Database of one system to another.
Suppose you have two system a and b in same domain and from A system you want to access the databases  of B and you get the following error as in screenshot.



Due to this  error you will get if you are working with any reports or web applications where you are connecting using Windows Authentication you will get the same above issue..

So in order to fix this Issue below is the Solution.

Solution 
We will take above case where you want to access Database of B system .
Open B system
Ctrl R
type active Directory users and computers
It will show the active directory window
Add the user of A system User --eg x user in b system
by Right clicking User
New User -- --Screenshot.

Add user Name
Give Password
check the box password never expired
OK


Now connect you will be able to connect successfully  and your reports or web applications using B databases will work as we have added the A's user in B system to make login Trusted.

The data connection file .odc used in the workbook is not in a trusted location in Share point

The data connection file .odc used in the workbook is not in a trusted location in Share point.

Issue -The issue was like while opening excel report which were connected to external connection files
below error was coming while refeshing the Reports.


Solution-
In order to fix this error in Share point site add the doc library in Trusted Data connection libraries in Share point Central Administrator.

for eg you have a data connection lib with URL
http:demo\test\Dataconnection 
and you are all the connection files .ODC are stored in the above doc library which you are using in your excel reports, so in order to fix the above issues 

follow the below steps.


GO to SP -Central admin
-Application Managment
-Service Applications
-Manage Service Applications
-Excel Service Application

Add your Data connection doc lib url in two Places
Trusted File Location-Add-http:demo\test\Dataconnection
Trusted Data connection Libraries-Add-http:demo\test\Dataconnection
check box-Children trusted.

After that refresh the Report and report were working fine.

SSRS Reports with parameters are not running in Share Point 2013

SSRS Reports 2012 with parameters are not running in Share Point 2013

ISSUE-SSRS Reports 2012 with  parameters with multi Selection  are not running in Share Point 2013 

ISSUE Description- While working with SSRS Report 2012 created some Parmeterized Reports which were working fine in Report builder and BIDS but after deploying in Sharepoint 2013 Site the Reports with Multi Parameter fiters were not Running and the paramter values were getting reset and showing a Blank Page.

Solution-Researching on Issue we came to know that it  was a bug of microsoft.

SO in order to fix this issue you have to install Cumilative Update 5 of sql server 2012 service pack 1.

Below is the link to install CU5 updates of sql server 2012 SP 1.

After installing the CU5 updates of SQL Server 2012 Service pack 1 the Issue was fixed.