Skip to main content

Posts

Why SQL server reports are coming blank or user not able to see the data

Problem statement: Sometime while opening the SSRS reports in Browser, it shows blank data even if there is data present in the database or some functionality does not work properly. Solution: here are some points to check to identify the root cause of the problem: User Access on Reports : Check the access of the user on Reports User Access on Source data: Check the access of the user on Cube or database Browser: If the user is having the proper access on the Reports as well as the Cubes or Database then check, on what browser user is running the report. Internet Explorer: If the user is using a browser other then Internet Explorer, then ask the user to use the Internet Explorer to access the SSRS Reports. There are many functionality of SSRS Reports which are not supported by all the browsers. Below are link for more details: http://msdn.microsoft.com/en-us/library/ms156511(v=sql.105).aspx http://social.msdn.microsoft.com/Forums/sq...

Identity Column in SQL Table

Identity Column in the SQL Table: How it works in SQL table? How Many Identity column can be created on the SQL table? How to insert user defined value in the Identity column? What will be the next value for the Identity column after the user defined value insertion in the table? Solution: Drop table #temp ; --Drop the temp table if it is already present Create table #temp --Create the temp table with identity column ( A int identity ( 1 , 1 ), --identity column --B int Identity(10,10), -- we can not create two identity column on a able C varchar ( 10 ) ) --insert values into table insert into #temp ( c ) values ( 'a' ) insert into #temp ( c ) values ( 'b' ) insert into #temp ( c ) values ( 'c' ) Select * from #temp --set identity insert on ont the table so that we can insert a user defined value in the identity column SET IDENTITY_INSERT #temp on insert into #temp ( A , C ) values ( 5 , 'd' ) --set ide...

How to do Error Handling for Transaction using Try Catch Block

Issue : Some time we get requirements where we have to use transaction to do some DML operations to make sure the unit of work should be done completely. But there could be error while doing this. So, how we can cope up with errors to achieving this? Solution: We can use Transaction in the Try Catch block to make sure that the transaction either successful or rollback if there is any error. Below is an example for this: BEGIN TRY --Start the Try Block.. BEGIN TRANSACTION -- Start the transaction.. UPDATE [dbo] . [Table] SET salary = salary * 1.1 WHERE MID = 6 --DML operations to be done in the transaction COMMIT TRAN -- Transaction Success! END TRY --End of the Try Block BEGIN CATCH --Start the Catch Block IF @@TRANCOUNT > 0 --check the error count for the errors ROLLBACK TRAN --RollBack in case of Error --to raise the error message when there is an issue. Declare the variables and se the values for them DECLARE @Er...

How to update values in a column based on the values from other tables

How to update column values in table based on values from two tables: Some time we need to update values in a table based on the join from other table e.g. for a customer calculate and update data values for Order values in from Item Price and Order table. below is an idea for doing this... Create Main table to be updated: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo] . [DimCustOrderValue] ( [CustID] [int] NULL, [CustomerName] [nchar] ( 20 ) NULL, [OrderValue] [numeric] ( 18 , 2 ) NULL ) ON [PRIMARY] GO --Create other required tables: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo] . [DimItemOrder] ( [OrderId] [int] NULL, [ItemId] [int] NULL, [CustID] [int] NULL [OrderQuantity] [int] NULL, ) ON [PRIMARY] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo] . [DimItemPrice] ( [ItemID] [int] NULL, [ItemName] [nc...

How to Rename a SSRS Report Project Solution

How to Rename a SSRS Report Project Solution: Overview: As a developer many times we come across scenarios where we have created a Report Project solution with a specific name e.g. "Reports_Dev". after some time say when the development is complete, we might think to rename the same Project solution to "Reports_Production" Let’s see how to do it... Solution: 1.         Create a copy of the SSRS Project Solution 2.         Now find the following items: Solution Folder Name:  Reports_Development Files in this Folder:                 Reports_Development.sln  (this a .sln file)                 Reports_Development.suo  (this is a hidden .suo file)            ...

SSRS Reports - Rendering Format Support in SQL Server Reporting Services

SQL Server Reporting Services 2012 has added new rendering options for Excel and Word to support the office 2007 -2010: Now it Supports for new file formats: ·           Excel renders for  Excel 2007-2010 (*.xlsx) o      For more info check:  http://msdn.microsoft.com/en-us/library/dd255234(v=sql.110).aspx ·           Word render for  Word 2007-2010 (*.docx) o      For more info check:  http://msdn.microsoft.com/en-us/library/dd283105(v=sql.110).aspx Below are new features with this new file format support: 1.         Feature equivalence with existing word and excel renders 2.         ZIP-compressed files – now easy to export and share the files 3.         Larger worksheets in Excel ·        ...