Interesting SQL Queries

• Query to find Procedures that contains a particular string in MS SQL 2005

select O.name from sysComments C join sysObjects O on O.id = C.id where O.xtype = 'P' and C.text like '%string%'

• Query to find all the user-defined tables in a specified database

USE db;
GO
SELECT *
FROM sys.tables;
GO

• Query to find find all the tables and indexes that are partitioned

USE db;
GO
SELECT SCHEMA_NAME(o.schema_id) AS schema_name,OBJECT_NAME(p.object_id) AS table_name,i.name AS index_name,p.partition_number,rows
FROM sys.partitions AS p
INNER JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.partition_schemes ps ON i.data_space_id=ps.data_space_id
INNER JOIN sys.objects AS o ON o.object_id = i.object_id
ORDER BY index_name, partition_number;
GO

• Query to find all the statistics on a specified object

USE db ;
GO
SELECT name AS statistics_name, stats_id,auto_created,user_created,no_recompute
FROM sys.stats
WHERE object_id = OBJECT_ID('');
GO

• Query to find all the statistics and statistics columns on a specified object

USE db;
GO
SELECT s.name AS statistics_name, c.name AS column_name, sc.stats_column_id
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc
ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
INNER JOIN sys.columns AS c
ON sc.object_id = c.object_id AND c.column_id = sc.column_id
WHERE s.object_id = OBJECT_ID('');
GO

Reporting Services 2005 Problems

I was creating some SSRS reports (both in Server and local mode) as part of my project work and i need to admit that i had some tough times for some of the issues.

• First of all I had issues in installing SSRS 2005 in my Windows Vista Premium OS and after googling it for some time, I came across a fact that IT IS NOT POSSIBLE TO INSTALL SSRS IN WINDOWS VISTA HOME PREMIUM edition. Refer following URL to install in other versions of vista.
  http://msdn2.microsoft.com/en-us/library/bb630430.aspx
• I had some class object that would be created and needed to populate in reports. For example, I had Employee object that had his project details, which is collection of Project object. To display project details in the report, I should create subreport to display Project details and would embed that in main Employee Report.
• When there is nested hierarchy of objects that needs to be displayed in Report, then there is syntax to be followed to do so. Let's take a scenario of Employee has Address object, Address object has City object that has name attribute. To display CityName in the Report, we need to follow syntax like "=Fields!Employee.Value.Address.City.Name". I lost some time in learning it.
• Another is to display the particular field in the Report Header that would repeat in all pages. For example, there is Employee object that has Employee Id that needs to be printed in all the pages of Employee Report. It is not possible to refer the field in the Header directly. There are some roundabouts to do that. The way I chose is to Create a report parameter and set the value at run time to have it populated.
• Very good resource for SSRS Tips.
  http://msdn2.microsoft.com/en-us/library/bb395166.aspx

Message Maintanance Architecture.

Here is my article I wrote some timc back about Message Maintanance Architecture for Web Application. Have a glance and let me know about any short falls or enhancements. I would like to have some comments on this.

http://www.codeproject.com/KB/cs/messagehandling.aspx

Microsoft - Tech Mela - Jan 2008

Visit following link to have a look at latest Microsoft Tech Mela Presentations and Videos.

http://msdn2.microsoft.com/hi-in/bb735929.aspx

Some of the videos are below par addressing very low level issues. For example, Peroframance and security video on web applications. Expected more than the content. Anyways, good refresher course.