May 4, 2009

SQL SERVER: Read values from Comma Separated variable

As we have seen, How to generate Comma separated List in SQL. Today we know, how to get values from Comma separated column. Many times developers asked, How can I read comma separated values from variable? There are many ways to get solution for this. Lets discuss about the best way, I think so. We can use XML to read values from comma separated values. XML made our life easy.
 
Example:
I have created one procedure which has one parameter VARCHAR(100). This procedure contains the parameter having value like '1,5,6,20'. The requirement is: Update the records having ID = 1,5,6,20 (Any of them). This is the one way that we can prepare the SQL query.
 
Proposed Solution:
Convert VARCHAR variable to XML as follows:
SET @xmlIDs =    '
                    ' + REPLACE(@str, ',', '') + '' +
                ''
So this statement will generate XML from VARCHAR value as follows:

  1
  6
  7
  8
  20

So, Now this is the XML, which can be easily read in SQL SERVER (2005 and above) as:
SELECT x.v.value('.','INT')
FROM @xmlIDs.nodes('/IDs/ID') x(v)
This will give me result set as: ( as separate table)
image
So that’s it, Now I can easily use this result set in my query, to update the rows accordingly.
So my Procedure looks like:
CREATE PROC Test_ReadValuesFromCommaSeparatedVariable
    @str    VARCHAR(100)
AS

DECLARE @XmlIDs XML

SET @xmlIDs =    '
                    ' + REPLACE(@str, ',', '') + '' +
                ''

UPDATE    TableName
SET        Flag = 1
WHERE    ID IN(
                SELECT x.v.value('.','INT')
                FROM @xmlIDs.nodes('/IDs/ID') x(v)
            )
            
 
So, it is very easy to read values from Comma separated value.
 
Let me know if it helps you.

April 26, 2009

SQL SERVER - Query to compare number of Rows between different Databases



We have two databases in the same SQL server instance. Both of the databases are copy of the production database, so both contains same tables. We added some records in Test Database’s some tables to update some features. We have added data in many tables of test database. Now we need to also update Production DB with the updated data. To update Production Database, we need to make sure in which tables we have updated data and then we will check and update production database accordingly. We have many numbers of tables, and we have updated many tables, so its not possible for us to check it manually.

So, we need to make query to compare rows of each table with the another database tables, to find out which tables has different rows then the original database.

Solution:

As I need to solve this problem, I write a query which will give me Rows of each table in one Database. As I need to compare it with another database I write the following query to come out with the solution. 
Example:

I have two databases. Database A and Database B.

I need to make a report, which will give me details of each table and rows.

I made this Stored Procedure in Database A.

CREATE PROC CompareRowsBetweenDatabas
AS
WITH A AS(
SELECT
sch.name AS SchemaName,
st.Name AS TableName,
SUM(
CASE
WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows
ELSE 0
END
) AS Rows
FROM sys.partitions p
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.tables st ON st.object_id = p.Object_ID
INNER JOIN sys.schemas sch ON sch.schema_id = st.schema_id
GROUP BY st.name, sch.name

),
b as(
SELECT
sch.name AS SchemaName,
st.Name AS TableName,
SUM(
CASE
WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows
ELSE 0
END
) AS Rows
FROM B.sys.partitions p
INNER JOIN B.sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN B.sys.tables st ON st.object_id = p.Object_ID
INNER JOIN B.sys.schemas sch ON sch.schema_id = st.schema_id
GROUP BY st.name, sch.name
)
SELECT a.SchemaName, a.TableName, a.Rows,
b.SchemaName As BSchemaName, b.TableName AS BTableName, B.Rows AS BRows
FROM a
INNER JOIN b ON a.TableName = b.TableName
AND a.SchemaName = b.SchemaName

EXEC CompareRowsBetweenDatabase




This will give me results as I need.


Let me know if it helps you.




April 20, 2009

SQL SERVER: Get Result ORDER BY Time regardless Date on SQL DateTime column


Usually we save Time with Dates in DATETIME column.
Today, I came across situation, where I need to sort my result set by Time, regardless the Date.

Let's see Example

I have some sample data like this:

DECLARE @Data TABLE(dt DATETIME)
INSERT INTO @Data(dt)
SELECT '2008-12-05 04:00:00.000'
UNION ALL
SELECT '2008-12-10 10:00:00.000'
UNION ALL
SELECT '2009-03-01 08:00:00.000'
UNION ALL
SELECT '2009-03-02 07:15:00.000'
UNION ALL
SELECT '2009-03-10 08:50:00.000'
UNION ALL
SELECT '2008-12-31 23:00:00.000'
UNION ALL
SELECT '2009-05-01 21:10:00.000'

SELECT * FROM @Data


Now we need output like this:

Get Result Order BY Time regardless Date on DateTime column


I found very quick solution for this. You can create query as follows:


SELECT * 
FROM @Data
ORDER BY Convert(VARCHAR, dt,108)


Let me know if it helps you in any way.

April 15, 2009

SQL SERVER: Difference between OpenQuery and OpenRowSet

Today, one developer asked me what is the difference between OpenQuery and OpenRowSet.

Let me share this thing with all of you.

Syntax for both the command:

OPENQUERY ( linked_server ,'query' )

OPENROWSET
( 'provider_name' , 'datasource' ; 'user_id' ; 'password'
, { [ catalog. ] [ schema. ] object | 'query' }
)

Difference is:
OpenQuery uses a predefined linked server,
While OpenRowSet has to specify all the connection options. So with OpenRowSet you can query to your remote SQL server from local.

Else it's the same.

Let me know if it helps you in any way.

April 14, 2009

SQL SERVER: SQL Query To Find Most used Tables



We have very large database and today we want to search the tables which are used mostly.

Means tables which are used in Procedures, Constraints, Views, Triggers etc.

I know this is very strange requirement, but we need to do this.

So, I tried to make an query which will help me to find out the top most tables used in other objects as I mentioned

Let me share that sp with all of you:
SELECT TableName, COUNT(*) 
FROM (
Select Distinct
o.Name 'TableName',
op.Name 'DependentObject'
From SysObjects o
INNER Join SysDepends d ON d.DepId = o.Id
INNER Join SysObjects op on op.Id = d.Id
Where o.XType = 'U'
Group by o.Name, o.Id, op.Name
) x
GROUP BY TableName
ORDER BY 2 desc

So, I made my life easy, by using this. I can get the list if Tables which are used most.





Let me know if it helps you in any way.






April 2, 2009

SQL SEVER: How to make an Entry in Event Viewer using SQL SERVER

Today I came across the situation where I need to following scenario.

I need to make entry in Event Viewer when there is an error in Stored Procedure.

I wondered to do this, but finally I come up with the solution. SQL is much powerful.

SQL provides us to make an entry in Event Viewer by two ways:
1. using XP_LogEvent

2. By Raiserror WITH LOG

Lets first see the way using XP_LogEvent:

Here I have created one SP which will raise an error “Divide by zero error encountered.” as I tried to do “10 / 0”.

CREATE PROCEDURE TestEventViewer
AS
BEGIN TRY
    SELECT  10/0
END TRY
BEGIN CATCH
    PRINT ERROR_Message()
    DECLARE @msg VARCHAR(100)
    SELECT @msg = ERROR_Message()

    EXEC xp_logevent 60000, @msg, informational

END CATCH

Lets Execute this SP:

EXEC TestEventViewer

This will write entry in Event Viewer. Now open Event Viewer. You can find Event Viewer at Control Panel –> Administrative Tools –> Event Viewer.

You will get en entry of Error there.

171

So we can do this by Extended Stored Procedure: “xp_logevent”.

Let see the parameters of this Procedure.

First Parameter: “60000” is the Error Number

Second Parameter: “@msg” is the message to be displayed in Event Viewer.

Third Parameter: “informational” is the Error Level. It could be “informational”, “Error”, “Warning”.

Now, Lets see by another way By Raiserror WITH LOG:

It is the same way as we used Raiserror to Raise an Error.

CREATE PROCEDURE TestEventViewer
AS
BEGIN TRY
    SELECT  10/0
END TRY
BEGIN CATCH
    PRINT ERROR_Message()
    DECLARE @msg VARCHAR(100)
    SELECT @msg = ERROR_Message()

    RAISERROR(@msg, 11, 1) WITH LOG

END CATCH

Lets Execute this SP:

EXEC TestEventViewer.

So By these ways we can make an entry to Event Viewer.

Let me know if it helps you in any way

March 26, 2009

SQL SERVER: How much space occupied by Each Table with sp_MSforeachtable procedure

Today, I came across requirement where I need to perform an action on all of the tables within a database.

For example, How much space occupied by each table.

I found undocumented Procedure: sp_MSforEachTable in the master database.

The following script reports the space used and allocated for every table in the database.

USE AdventureWorks;
EXECUTE sp_MSforeachtable 'sp_spaceused [?]'

So, We can use sp_MSforeachtable procedure when we need to loop through each table.

Let me know if it helps you in any way.

March 22, 2009

SQL SERVER: Unable to Launch Activity Monitor. How to add VIEW SERVER STATE Permissions to launch Activity Monitor

Activity Monitor, is used view to current executing queries, Query status, Start Time, Host, Blocking or not, Database etc.

Last week, one of my developer has one issue. When he try to open Activity Monitor, System gives the following Error:

152

I found that this user need to get VIEW STATE Permissions, Lets see how to give this permission to the user.

We can give permission to user by this two ways:

1. T-SQL: GRANT VIEW SERVER STATE TO YourLogin

2. GUI:

Step 1: Right mouse click the SQL Server and go to Properties. You need to give GRANT to “View server State’ for that user.

151


Let me know it it helps you.

March 20, 2009

SQL SERVER: How to enable 'Ad Hoc Distributed Queries' SQL SERVER 2005

 

Frequently, we need to use OPENROWSET queries to connect to remote database servers. To enable this feature on SQL Server 2005, you should first configure the database to enable Ad Hoc Distributed Queries.

We can Enable this feature by two ways:

1. SQL Server Surface Area Configuration.

2. by sp_configure option.

Lets check with first way, by SQL Server Surface Area Configuration.

Open surface Area configuration, you will get this screen:

131

 

Click on second option, Surface Areas Configuration for Features. you will get this screen, where you need to check to Enable OPENROWSET and OPENDATASOURCE support.

132

 

Lets see second option to enable this feature with sp_configure option:

sp_configure

If you run this command, you will lists of SQL configuration settings. There are 14 items in the list in which 'Ad Hoc Distributed Queries' is not exist. To see this, we need to enable the 'show advanced options' configuration parameter.

You can enable advance options by:

sp_configure 'show advanced options',1

When we run this command we will get this message:

“Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.”

so we need to execute reconfigure command as:

reconfigure

so now if we run sp_configure again, we will get result set as follows:

133

Here, we can find that config_value for “Ad Hoc Distributed Queries” is “0”. We need to set it to 1 to enable this feature. so to do that we need to use following:

sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

so, now if we run sp_configure, we will get result as follows:

134 

Here, we can find that now config_value for “Ad Hoc Distributed Queries” is “1”.

That’s it, now you can use OPENROWSET and OPENDATASOURCE to connect with remote database without Linked server.

Let me know if it helps you in any way.

March 19, 2009

SQL SERVER: Find Duplicate Rows with Row_Number() SQL SERVER 2005


This is very known question to find Duplicate Rows from the Table and Delete Duplicate rows from the table
I came to know about "ROW_NUMBER()" of SQL SERVER 2005.
Lets see how we can use this feature:
There is one Table:
CREATE TABLE FindDuplicates(
EmpID INT IDENTITY(1,1),
EmpName VARCHAR(500)
)

INSERT some data into the table:
INSERT INTO FindDuplicates VALUES('A')
INSERT INTO FindDuplicates VALUES('A')
INSERT INTO FindDuplicates VALUES('B')
INSERT INTO FindDuplicates VALUES('C')
INSERT INTO FindDuplicates VALUES('C')
INSERT INTO FindDuplicates VALUES('D')
INSERT INTO FindDuplicates VALUES('D')

Now we have data like:
Find Duplicate Rows
Now, we need to Find the duplicate Rows from the table. So we need to get "A", "C" and "D".
Lets see the use of Row_Number():
;With CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY EmpName ORDER BY EmpName) As RowID,
EmpID,
EmpName
FROM FindDuplicates
)
SELECT *
FROM CTE
WHERE RowID > 1

Output will be like:
Find Duplicate Rows
So we can Delete Duplicate Rows as we have EmpID (Primary Key)
I Used:
PARTITION BY: which will find same rows and assign ID accordingly.
ORDER BY: In which order we want to assign ID. By Default its Ascending