I have recently encountered a problem in SSRS report. I was basically trying to export to excel from my report. First time I rendered report for a set of parameters and export to excel it worked fine. But subsequent rendering…
Find identity columns in a database
Here’s a way to find out the identity columns in a db/table SQL 2000 SELECT c.TABLE_NAME ,c.COLUMN_NAMEFROM INFORMATION_SCHEMA.COLUMNS cWHERE COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME),c.COLUMN_NAME,’IsIdentity’) = 1 SQL 2005/2008 SELECT OBJECT_NAME(Object_Id) AS Table_name, Name AS COLUMN_NAME FROM sys.identity_columns for getting identity columns in table just…
Cross server cross db object dependencies in SQL 2008
Finding cross server cross database object dependencies has become easier with SQL 2008. The new catalog view sys.sql_expression_dependencies it is now possible to find the entities that are dependent on other entity across server or across databases in same server.…
SELECT .. INTO table with IDENTITY function
Most often I’ve come across scenarios where we need to generate a unique valued column on the fly. From SQL 2005 onwards, we have the window functions like ROW_NUMBER() for doing this however for earlier versions one typical solution I’ve…
Some quick tips regarding datetime values
Until SQL 2008 we dont have a mechanism to store either date or time values alone. The only data type available is datetime which involves date as well as time part. Hence a common requirement that we face is strip…
Inscope operator in SQL Reporting Services
One of the very handy operator I’ve come across in SQL Reporting services is Inscope operator. Quite often we come across scenarios where we need to generate dynamic crosstabbing reports based on particular field data. One of most commonly used…
Behaviour of ORDER BY inside view definition
One of the most common question I see in SQL forums relates to use of ORDER BY in a view and retrieval of data from view not maintaining the suggested order. This article aims at giving a clear picture on…
Multipurpose Apply Operator
One of major features that I’ve been fascinated by in SQL Server 2005 has been the APPLY operator. This article discusses on some of the real time scenarios where you can make use of this feature. Scenario 1 – Running…