Search This Blog

Loading...

Friday, April 18, 2014

Issue with Power Pivot-An error occurred while processing table 'Query'. The current operation was cancelled because another operation in the transaction failed.

Issue with Power Pivot-An error occurred while processing table 'Query'. The current operation was cancelled because another operation in the transaction failed.


Issue :OLE DB or ODBC error: Dec  1 2012 12:00AM; 01000.
An error occurred while processing table 'Query'.
The current operation was cancelled because another operation in the transaction failed.

Issue Description:While Working with Excel Pivot Table The complex SQL query is working fine in SQL Management but when running the Same query in excel power pivot it was  giving the error.

The excel is validating and running  the query without any issue. but when saving the query throwing below error. 

OLE DB or ODBC error.
An error occurred while processing table 'Query'.
The current operation was cancelled because another operation in the transaction failed.


Reason:The Issue comes When working with SQL Complex queries and you are using an insert statement in your sql code.


Fix/Solution-Set Nocount On above the Insert Statement in the sql Code 

In my case i was inserting some records in temp table so added Set Nocount On above the Insert statement and No count Off after finishing Insert statement has fixed the issue.

--Below is the insert  Part of my SQL
WHILE @currct <= @Mct
       BEGIN
SET NOCOUNT ON
              INSERT INTO @MyTempTable1 (datetime, [ Role], Work)
                     SELECT DATEADD(MM, @currct, @FD) AS datetime
                           ,ru.[ Role]
                           , 0 AS Work
                           FROM Project AS ru
                         
              SET @currct = @currct + 1
              SET @CurDate = DATEADD (mm, 1, @CurDate)
 SET NOCOUNT Off
       END
  
   Adding Nocount on will fix the issue.

NOTE: If again  you will get the same error set NOCOunt on above your main Select query.

Friday, April 11, 2014

Add Row number to Select Query in SQL

You can add a row Number to sql Select query using Row Number Function 

Below is the table script with Example

CREATE TABLE #SQL
(
[Numbers] varchar(40)
)
INSERT INTO #SQL VALUES('One');
INSERT INTO #SQL VALUES('Two');
INSERT INTO #SQL VALUES('Three');
INSERT INTO #SQL VALUES('Four');
INSERT INTO #SQL VALUES('Five')

Case 1st -Showing Row Number in Select Query in SQL


select *,ROW_NUMBER()over( order by (select 1)) as Row_Num from #SQL

Output

Numbers Row_Num
One    1
Two    2
Three 3
Four 4
Five 5

Case 2nd Adding a Row Number column in Table

Just add an identity field column in the  table

alter table #sql
add Row_Num int Identity(1,1)

Select * from #SQL

Output

Numbers Row_Num
One    1
Two    2
Three 3
Four 4
Five 5

Thursday, April 10, 2014

Opening SSRS drill through Report in a new Tab or Window

Opening SSRS drill through Report in a new Tab or Window


In order to Open a SSRS Drill through Report in a New Tab or window we have to use Javascript.

Case first-Report in new Tab without fields or  Parameters
Opening Drill down Report in a new tab without parameters and fields

First you have to Select the Drill through values textbox
Properties
Text box Properties 
Action
Write below javascript code
Inside javascript function add URL of the drill through Report

 ="javascript:void(window.open('http://abc.com/Reports/Testing/Drilldowntest.rdl ','_blank'))"
 
   This will open your Report in a new tab
 
   Case second-Passing Fields and opening in new Tab
 
   When you are Passing the Fields values ie (Text box values to the drill through Report)
   --Replace Report Name and with your Report Name and field Name with your drill through Report Parameter Name
 
    ="javascript:void window.open(" &"'"& Globals!ReportServerUrl &"/Pages/ReportViewer.aspx?"
&Globals!ReportFolder &"/Drilldowntest.rdl
&Low="&Fields!Low.Value &"
 ','_blank')"

 DrilldownTest.rdl-Change to YOur REport Name
 Low-Replace it with Your Report drill through parameter Name
 Low.Value-Replace it your field Name

 Case Third Passing Fields as well as Parameters with IIF conditions and opening report in a new tab

  ="javascript:void window.open(" &"'"& Globals!ReportServerUrl &"/Pages/ReportViewer.aspx?"
&Globals!ReportFolder &"/Drilldowntest.rdl
&Low="&Fields!Low.Value &"
&High="&Fields!High.Value &"
&Project=" &IIf(Parameters!Project.Value="","A",Parameters!Project.Value) & "
 ','_blank')"

  DrilldownTest.rdl-Change to YOur REport Name
 Low-Replace it with Your Report drill through parameter Name
 Low.Value-Replace it your field Name
 Same with High
 Project-Replace it with your Child or drill through Report Paramter Name
 Project.value-Project is your Main Report Parameter Replace it with your Main Report Parameter.value


 Note the above all javascript is to open Report in new tab if you want to open report in new window name the window and adjust the Properties as below

  ="javascript:void window.open(" &"'"& Globals!ReportServerUrl &"/Pages/ReportViewer.aspx?"
&Globals!ReportFolder &"/Drilldowntest.rdl
&Low="&Fields!Low.Value &"
','RUWindow','left=370,top=180,width=940,height=300,status=no,menubar=no,scrollbars=no,location=yes'" & ")"

Tuesday, April 1, 2014

Rename failed for Database 'Testing_new'.

Rename failed for Database 'Testing_new'. 


Issue -Rename failed for Database 'Testing_new'
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)
Issue Description-While Renaming the Database in SQL using GUI get the above Issue

Solution-
To fix the above Issue
-Right click Database
-Properties
-Options
-Restrict Access
-Set to Single User
click ok

Once Done Rename the database 
by Right click
Rename
change the name to new Name-Testing_New
Once done 

Follow the same above process
-Right click Database
-Properties
-Options
-Restrict Access
-Set it back to Multi User
click ok

NOTE: we can also rename a database by  Running SP_RenameDB system Stored Proc.

--first parameter old db name second parameter new db name
exec SP_renamedb 'TestingNew' ,'Testing_New' 

Thursday, March 27, 2014

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.




Monday, March 24, 2014

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.



Saturday, March 22, 2014

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.