Archive for February, 2009

h1

SQL SERVER: Get column values as comma seperated list using XML PATH() instead of UDF’s using SQL COALESCE

02/28/2009

Today I have the following situation, where I need to display all related data in comma seprated list.

Till today we are using scalar function to display Comma separated list with select statement. That scalar function use the COALESCE() to make comma separated list. Today I found wonderful solution to display comma separated list without scalar function. Let see that.

Scenario:

I have Table like:

CREATE TABLE #test(
field1 VARCHAR(5), field2 VARCHAR(5)
)

Lets insert some data in this table:

INSERT INTO #test
SELECT '001','AAA'
UNION ALL
SELECT '001','BBB'
UNION ALL
SELECT '002','CCC'
UNION ALL
SELECT '003','DDD'
UNION ALL
SELECT '004','EEE'
UNION ALL
SELECT '004','FFF'
UNION ALL
SELECT '004','GGG'

So now my table has Data like:

Get Comma separated List

Get Comma separated List

I want output like:

Get Comma separated List

Get Comma separated List

I come up with very good solution. Let me share with all of you:

Get Comma separated List

Get Comma separated List

My Output will be:

Get Comma separated List

Get Comma separated List

Please make comments, if this helps you in any way

h1

SQL SERVER: REPLICATE function

02/26/2009

Today i read SQLAuthority.com, and I found that one developer
has this issue. Lets see that problem as well as the solution
for the same.

There is one numeric column. User needs to make
sure that all data should be of same size.

We need to display as:

17.00 TO 17.00
2.00 TO 02.00
8.17 TO 08.17

So I found REPLICATE function of SQL SERVER, to fix this.

How to use REPLICATE function?

REPLICATE (“string that you want TO append” ,”INTEGER
VALUE” )

FIRST parameter, i need TO
SET the CHARACTER, which
will append it BEFORE the NUMBER.

SECOND parameter, how many
times this CHARACTER should be ADD TO the NUMBER.

Example:
DECLARE @t AS NUMERIC(8,2)
SELECT @t = 08.2
SELECT Cast(Replicate(0,6-Len(@t)) AS VARCHAR(5)) + Cast(@t AS VARCHAR(5))

Here I specify that there should be 5 length. In this case 8.23 has four digit, so this will add one “0” to this number.

We can append any character by just changing the First Parameter, String value.

Let me know your suggestions

h1

SQL SERVER How to Get Stored Procedure Names used by Reports, SQL Reporting Services

02/21/2009

We are using SQL Reporting Services for Reports.

Today we need to identify Stored Procedures used by Reports, so we want to transfer those SPs to Replication server.
I found very good query on Jacob Sebastian Blog.

;WITH xmlnamespaces (
    default
    'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
    'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
    NAME AS reportname,
    q.value('@Name[1]', 'VARCHAR(50)') AS datasetname,
    x.value('DataSourceName[1]', 'VARCHAR(50)') AS datasourcename,
    x.value('CommandText[1]', 'VARCHAR(50)') AS spname
FROM
(
    SELECT NAME,
    CAST(CAST(content AS varbinary(MAX)) AS xml) AS reportxml
    FROM reportserver.dbo.catalog
) a
CROSS apply reportxml.nodes('/Report/DataSets/DataSet') d(q)
CROSS apply q.nodes('Query') r(x)
h1

How to find all places where object (Table/view/Text etc..) is used

02/16/2009

Recently I have one change in a table schema. To make this change I need to be sure that it should not raise an error. To make suare that I need to get all the places where I used this table in whole DB and my DB is too big.

So I triued for that and come with the solution, that I need to share with all of you.

I wrote one query which will give me all the objects in which i have used the TEXT that i want to search.

Query for SQL 2005:

SELECT DISTINCT o.name
FROM sysobjects o
INNER JOIN syscomments c ON c.Id = o.Id
WHERE category = 0 AND c.text like ‘%’ + ‘<TableName>’ + ‘%’
ORDER BY o.name

So it save my life. I found all the objects where I used that table and fix if needed. Let me know if this can help you !

h1

Get Weekday of Date – SQL Server

02/08/2009

Today i come to the situation where I need to display Dayname like Sunday, Monday,Tuesday etc.. fot the dates from the SQL Server. I found the syntax for that:

SELECT DATENAME(dw,GETDATE())

We can use this too: SELECT DATENAME(weekday,GETDATE())