Archive for the ‘SQL Server’ Category

Dundas control is powerful 3rd party tool to develop report using SQL Server Reporting Service.

Microsoft declared that they integrated Dundas Control with SQL Server Reporting Service 2008 but in reality I found that I had to install Dundas control in my SQL Server Report Service 2008 server to make my reports working, which is little disappointing.

Another disappointing thing is, you need to manually setup the configuration files every time you deploy your reports in a new server even after installing Dundas Control.

If you see the following error “The Dundas Chart for Reporting Services report item is unavailable” even after installing Dundas Control for SQL Server reporting Service you need to do the following things to make it working.

1. Modify C:\Program Files\Microsoft SQL Server\MSSQL.x\Reporting Services\ReportServer\rsreportserver.config

2. Modify C:\Program Files\Microsoft SQL Server\MSSQL.x\Reporting Services\ReportServer\rssrvpolicy.config

3. Remember if you use any external dll files for your report you need to add the reference manually too in C:\Program Files\Microsoft SQL Server\MSSQL.x\Reporting Services\ReportServer\rssrvpolicy.config

You will get the config code for all the configuration here http://support2.dundas.com/Default.aspx?article=1107, so just copy paste the configurations in you server’s configuration files and restart the SQL Server Report Service. Hope now your reports with Dundas controls are working now. happy reporting 🙂

Read Full Post »

Where column IN(@parameter)

I faced problem when i tried to send parameter in a store procedure which parameter is located in the ‘IN’ of the query. first i written a query ‘select * from users where in IN(1,2,4)’ — worrls nicely, then i tried to use store procedure to parametarize data i have written a store procedure look like that

create proc getuser( @ids as varchar(50) ) AS select * from users where in IN(@ids) — not working. why will it work you are giving parameter as varchar but you are give id as integer, what you want to do change parameter type? it is no a solution too as you will send all id once through the parameter.

So the solution is

create proc proc_test
(@ids  nvarchar(100))
declare @sql  as nvarchar(1000)
set @sql = N’SELECT * FROM JoinPool WHERE ID IN (‘ + @ids + ‘)’
EXECUTE sp_executesql @sql

exec proc_test ‘1,2,3,5’  — will return result 🙂

Read Full Post »

I had a dastabase restored from anohter PC. Now when I database open database I am getting tables available in the database but when i click on database diagram it gives me error.


I have got a good solution in technet. Please go through the link to getsolve the problem


Read Full Post »

sometime we may face problem to change password of sa using sql server management studio. that becasue by default this is not set to SQL server and windows authentication mode. Here the solution to do it step by step.

1.Open the SQL Server express management studio
2.Connect to SQL Server using windows authentication
3.Right click the server name and choose properties
4.Go to security tab. Change server authentication to “SQL Server and Windows Authentication mode”
5.Click OK and restart SQL Server
6.Go to SQL Server studio management express
7.Expand the server and choose security and expand logins
8.Right click on SA, from properties modify the password and confirm password

This changed pass should work.

Read Full Post »

case in sql(a nice sql query)

This is a nice sql query, I was afraid with a table and finally i got WITH CASE which helped me to implement confition in query. nice……..

SELECT CASE WHEN price IS NULL THEN ‘Not yet priced’ WHEN price = 10 and price THEN ‘Very Reasonable Title’ WHEN price >= 10 and price <= 20 THEN ‘Coffee Table Title’ ELSE ‘Expensive book!’ END AS “Price Category”, CONVERT(varchar(20), title) AS “Shortened Title” FROM pubs.dbo.titles ORDER BY price

Read Full Post »

important in query

Some time we want not to get null values returning from our query and to protect this need to use ISNULL. hrre is an example fo using ISNULL.
ISNULL((SELECT name FROM employee WHERE id = employeeSalary.eID), ”)
Name of the employee id returnig from this query but if name is null it return a space.
Sometime we need to cast a field here is an example of casting value
(SELECT RTrim(CAST(day AS char(3))) + ‘-‘ + RTrim(CAST(month AS char(3))) + ‘-‘ + RTrim(CAST(year AS char(3))) FROM employee AS date
it cast and concate day month and year

Read Full Post »