Category Archives: sql server

Update XML attribute value based on condition with XQUERY

Update XML attribute value based on condition with XQUERY

Recently I had one article which shows how to update XML attribute value which doesn’t had any checking/condition while update. One of the reader asked me how can he update value based on condition. Here is the solution for it.

CREATE TABLE Orders
(
ID INT IDENTITY(1,1),
ClientID VARCHAR(6),
TestDetails XML,
OrderDate DATETIME DEFAULT GETDATE()
)
GO

INSERT INTO Orders (ClientID,TestDetails)

SELECT ‘CHEM02′,'<TestDetails><Test Dept=”VOC” Name=”VOC MS Group1” /></TestDetails>’ UNION ALL

SELECT ‘ACCU01′,'<TestDetails><Test Dept=”VOC” Name=”SVOC Full” /></TestDetails>’ UNION ALL

SELECT ‘CBI001′,'<TestDetails><Test Dept=”VOC” Name=”PCB” /></TestDetails>’

GO

 

Now we will change attribute “Dept”’s value from “VOC” to appropriate value.

-- updating new attribute
UPDATE Orders
SET TestDetails.modify('
replace value of (/TestDetails/Test/@Dept)[1]
with (
if (/TestDetails/Test/@Name="SVOC Full") then
"SVOC"
else
"VOC"
)
')
GO

Let us now execute SELECT statement on Orders table to confirm that whether we have new valie in attribute “Dept” added inside “Test” element or not?

SELECT * FROM Orders
GO

I have few more article written on the subject of XML/XQuery, if you are interested, have a look at following list:

  • Update XML attribute value with XQUERY in SQL Server (Click Here)
  • XQuery in SQL Server to insert XML attribute in existing XML element (Click Here)
  • XQuery in SQL Server to delete XML element (Click Here)
  • WHERE clause on XML column in SQL Server table (Click Here)
  • Error Fix: Unable to show XML The following error happened There is an unclosed literal string (Click Here)
  • For XML PATH ROOT in SQL Server 2008 (Click Here)
  • Read typed XML in SQL Server 2008 (Click Here)
  • Update UnTyped XML data column in SQL Server 2008/2005 (Click Here)
  • Return comma separated value with For XML Path in SQL Server 2008/2005 (Click Here)
  • Read XML node on same level inSQL Server 2008/2005 (Click Here)
  • Load Relational XML data in SQL Server 2005 (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Query to Find missing job in SQL Server

Query to Find missing job in SQL Server

While managing multiple instance of SQL Server, it is little bit difficult to keep watch on every jobs running in every SQL Server Instances we are managing. I have created one monitoring application which suppose to be watched by at least one DBA 24*7. I have one section of “Missing Job” in my monitoring application so that whenever any job failed the schedule, one of the DBA get an alert and s/he can do needful.

Following is the TSQL script which I use to find missing job.

SET nocount ON

DECLARE @datetime VARCHAR(12)

SET @datetime = CONVERT(VARCHAR, Getdate(), 112)

CREATE TABLE #runningjobs
(
job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id SYSNAME COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL
)

INSERT INTO #runningjobs
EXECUTE master.dbo.Xp_sqlagent_enum_jobs
1,
'sa';


WITH cte
AS (SELECT [sJOB].[job_id] AS [JobID],
[sJOB].[name] AS [JobName],
CASE
WHEN [sJOBH].[run_date] IS NULL
OR [sJOBH].[run_time] IS NULL THEN NULL
ELSE Cast(Cast([sJOBH].[run_date] AS CHAR(8)) + ' '
+ Stuff( Stuff(RIGHT('000000' +
Cast([sJOBH].[run_time]
AS
VARCHAR(6
)), 6)
, 3,
0, ':'), 6, 0, ':') AS DATETIME)
END AS [LastRunDateTime],
CASE [sJOBH].[run_status]
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'Running'
END AS [LastRunStatus],
Stuff(Stuff(RIGHT('000000'
+ Cast([sJOBH].[run_duration] AS VARCHAR(6)),6), 3, 0, ':'),
6, 0, ':') AS [LastRunDuration (HH:MM:SS)],
[sJOBH].[message] AS [LastRunStatusMessage],
CASE [sJOBSCH].[nextrundate]
WHEN 0 THEN NULL
ELSE Cast(Cast([sJOBSCH].[nextrundate] AS CHAR(8))
+ ' '
+ Stuff( Stuff(RIGHT('000000' +
Cast([sJOBSCH].[nextruntime]
AS
VARCHAR(6))
, 6),
3, 0, ':'), 6, 0, ':') AS DATETIME)
END AS [NextRunDateTime],
sJob.enabled
FROM [msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN (SELECT [job_id],
Min([next_run_date]) AS [NextRunDate],
Min([next_run_time]) AS [NextRunTime]
FROM [msdb].[dbo].[sysjobschedules]
GROUP BY [job_id]) AS [sJOBSCH]
ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN (SELECT [job_id],
[run_date],
[run_time],
[run_status],
[run_duration],
[message],
Row_number()
OVER (
partition BY [job_id]
ORDER BY [run_date] DESC, [run_time] DESC
) AS
RowNumber
FROM [msdb].[dbo].[sysjobhistory]
WHERE [step_id] = 0) AS [sJOBH]
ON [sJOB].[job_id] = [sJOBH].[job_id]
AND [sJOBH].[rownumber] = 1)
SELECT 'Job Missing (' + @@SERVERNAME + ')' AS CounterName,
Count(*) AS Value
FROM cte
WHERE nextrundatetime >= Dateadd(dd, 0, Datediff(dd, 0, Getdate()))
AND nextrundatetime < Dateadd(minute, -30, Getdate())
AND ( nextrundatetime > lastrundatetime
OR lastrundatetime IS NULL )
AND enabled = 1
AND jobid NOT IN (SELECT job_id
FROM #runningjobs
WHERE running = 1)

DROP TABLE #runningjobs
GO

BTW, I have written few more articles related to SQL Server Job. If you are interested, have a look at following list:

  • Get list of SQL Server Jobs from multiple instance via SSIS package (Click Here)
  • Find SQL Server Agent job ran on specific date with its status (Click Here)
  • Search SQL Server Job based on keyword provided (Click Here)
  • Script all SQL Server Job automatically by SSIS and SMO (Click Here)
  • Get list of failed SQL Server Agent job in SQL Server (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Update XML attribute value with XQUERY in SQL Server

Update XML attribute value with XQUERY in SQL Server

Recently I have written article on Inserting attribute in existing XML Elements and Delete XML element. Today I am going to show how to update existing attribute value in XML elements.

CREATE TABLE Orders
(
ID INT IDENTITY(1,1),
ClientID VARCHAR(6),
TestDetails XML,
OrderDate DATETIME DEFAULT GETDATE()
)
GO

INSERT INTO Orders (ClientID,TestDetails)

SELECT ‘CHEM02′,'<TestDetails><Test Dept=”VOC” Name=”VOC MS Group1” /></TestDetails>’ UNION ALL

SELECT ‘ACCU01′,'<TestDetails><Test Dept=”VOC” Name=”SVOC Full” /></TestDetails>’ UNION ALL

SELECT ‘CBI001′,'<TestDetails><Test Dept=”VOC” Name=”PCB” /></TestDetails>’

GO

 

While making an initial entry of DEPT, I have provided “VOC” but now I felt I was wrong and I have to put “SVOC” instead of “VOC” and in this case I have to update existing value of “Dept” attribute. Here is the script which I would use to update attribute value without any checking/condition.

DECLARE @Dept VARCHAR(10)
SELECT @Dept = 'SVOC'

-- updating attribute
UPDATE Orders
SET TestDetails.modify('
replace value of (/TestDetails/Test/@Dept)[1]
with sql:variable("@Dept")
')
GO

Let us now execute SELECT statement on Orders table to confirm that whether we have new valie in attribute “Dept” added inside “Test” element or not?

SELECT * FROM Orders
GO

I have few more article written on the subject of XML/XQuery, if you are interested, have a look at following list:

  • XQuery in SQL Server to insert XML attribute in existing XML element (Click Here)
  • XQuery in SQL Server to delete XML element (Click Here)
  • WHERE clause on XML column in SQL Server table (Click Here)
  • Error Fix: Unable to show XML The following error happened There is an unclosed literal string (Click Here)
  • For XML PATH ROOT in SQL Server 2008 (Click Here)
  • Read typed XML in SQL Server 2008 (Click Here)
  • Update UnTyped XML data column in SQL Server 2008/2005 (Click Here)
  • Return comma separated value with For XML Path in SQL Server 2008/2005 (Click Here)
  • Read XML node on same level inSQL Server 2008/2005 (Click Here)
  • Load Relational XML data in SQL Server 2005 (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

XQuery in SQL Server to insert XML attribute in existing XML element

XQuery in SQL Server to insert XML attribute in existing XML element

Use of XML is being popular in SQL Server and it comes handy in many situation as I have explained in many of my past Xquery/XML related article. Today I come up with one more example which shows usage of XQuery to insert element from XML data.

CREATE TABLE Orders
(
ID INT IDENTITY(1,1),
ClientID VARCHAR(6),
TestDetails XML,
OrderDate DATETIME DEFAULT GETDATE()
)
GO

INSERT INTO Orders (ClientID,TestDetails)

SELECT ‘CHEM02′,'<TestDetails><Test Name=”VOC MS Group1” /></TestDetails>’ UNION ALL

SELECT ‘ACCU01′,'<TestDetails><Test Name=”SVOC Full” /></TestDetails>’ UNION ALL

SELECT ‘CBI001′,'<TestDetails><Test Name=”PCB” /></TestDetails>’

GO

 

Each test (i.e.: Voc MS Group1, SVOC Full) has one department which is not mentioned as an attribute in TestDetails node. Let us insert “Dept” attribute.

DECLARE @Dept VARCHAR(10)
SELECT @Dept = 'Volatile'

-- Adding new attribute
UPDATE Orders
SET TestDetails.modify('
insert attribute Dept {sql:variable("@Dept")} as first into
(/TestDetails/Test)[1]
')
GO

Let us now execute SELECT statement on Orders table to confirm that whether we have new attribute “Dept” added in “Test” element or not?

SELECT * FROM Orders
GO

I have few more article written on the subject of XML/XQuery, if you are interested, have a look at following list:

  • XQuery in SQL Server to delete XML element (Click Here)
  • WHERE clause on XML column in SQL Server table (Click Here)
  • Error Fix: Unable to show XML The following error happened There is an unclosed literal string (Click Here)
  • For XML PATH ROOT in SQL Server 2008 (Click Here)
  • Read typed XML in SQL Server 2008 (Click Here)
  • Update UnTyped XML data column in SQL Server 2008/2005 (Click Here)
  • Return comma separated value with For XML Path in SQL Server 2008/2005 (Click Here)
  • Read XML node on same level inSQL Server 2008/2005 (Click Here)
  • Load Relational XML data in SQL Server 2005 (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

XQuery in SQL Server to delete XML element

XQuery in SQL Server to delete XML element

Use of XML is being popular in SQL Server and it comes handy in many situation as I have explained in many of my past Xquery/XML related article. Today I come up with one more example which shows usage of XQuery to delete element from XML data based on the condition you have.

CREATE TABLE Orders
(
ID INT IDENTITY(1,1),
ClientID VARCHAR(6),
TestDetails XML,
OrderDate DATETIME DEFAULT GETDATE()
)
GO

INSERT INTO Orders (ClientID,TestDetails)
SELECT ‘CHEM02′,'<TestDetails><Test Name=”VOC MS Group1” /><Test Name=”Pesticide Group1” /></TestDetails>’ UNION ALL
SELECT ‘ACCU01′,'<TestDetails><Test Name=”SVOC Full” /><Test Name=”Pesticide Group1” /></TestDetails>’ UNION ALL
SELECT ‘CBI001′,'<TestDetails><Test Name=”PCB” /><Test Name=”Metals” /></TestDetails>’
GO

We can see that we have “TestDetails” as a root element in above given XML along with child elements “Test”. Suppose we have requirement to delete all elements which has test name “Pesticide Group1”, how can we do it with the help of XQuery?

Here is the answer:

--Update with XQuery to Delete element
UPDATE Orders SET
TestDetails.modify ('
delete
/TestDetails/Test[@Name cast as xs:string ? = "Pesticide Group1" cast as xs:string ?]')
GO

-- confirm result by querying Orders Table
SELECT * FROM Orders

I have few more article written on the subject of XML/XQuery, if you are interested, have a look at following list:

  • WHERE clause on XML column in SQL Server table (Click Here)
  • Error Fix: Unable to show XML The following error happened There is an unclosed literal string (Click Here)
  • For XML PATH ROOT in SQL Server 2008 (Click Here)
  • Read typed XML in SQL Server 2008 (Click Here)
  • Update UnTyped XML data column in SQL Server 2008/2005 (Click Here)
  • Return comma separated value with For XML Path in SQL Server 2008/2005 (Click Here)
  • Read XML node on same level inSQL Server 2008/2005 (Click Here)
  • Load Relational XML data in SQL Server 2005 (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

WHERE clause on XML column in SQL Server table

WHERE clause on XML column in SQL Server table

Xquery is one of the wonderful feature I appreciate most in SQL Server. Due to lack of knowledge, people doesn’t tend to use it. I always prefer XML in case of I have to pass multiple values in one parameter of stored procedure. It is even useful many time to store XML value along with our relational data in SQL Server table. I, personally, prefer it while capturing and storing performance related data.

Recently I have received on question in my facebook page where one member has shown me one table structure and ask how he can use XML data in WHERE clause of SELECT statement.

It is very easy to use “Exist” method to filter XML from the WHERE clause. Let us see one example:

CREATE TABLE Orders
(
ID INT IDENTITY(1,1),
ClientID VARCHAR(6),
TestDetails XML,
OrderDate DATETIME DEFAULT GETDATE()
)
GO

INSERT INTO Orders (ClientID,TestDetails)|
SELECT ‘CHEM02′,'<TestDetails><Test Name=”VOC MS Group1” /><Test Name=”Pesticide Group1” /></TestDetails>’ UNION ALL
SELECT ‘ACCU01′,'<TestDetails><Test Name=”SVOC Full” /><Test Name=”Pesticide Group1” /></TestDetails>’ UNION ALL
SELECT ‘CBI001′,'<TestDetails><Test Name=”PCB” /><Test Name=”Metals” /></TestDetails>’
GO


Now, we have one table ready with one XML column. We want only those records whose XML data is having value “Pesticide Group1”. We can create WHERE clause for that SELECT query like this:

SELECT
*
FROM
Orders
WHERE
TestDetails.exist('(/TestDetails/Test[@Name=''Pesticide Group1''])') = 1
GO

I have few more article written on the subject of XML, if you are interested, have a look at following list:

  • Error Fix: Unable to show XML The following error happened There is an unclosed literal string (Click Here)
  • For XML PATH ROOT in SQL Server 2008 (Click Here)
  • Read typed XML in SQL Server 2008 (Click Here)
  • Update UnTyped XML data column in SQL Server 2008/2005 (Click Here)
  • Return comma separated value with For XML Path in SQL Server 2008/2005 (Click Here)
  • Read XML node on same level inSQL Server 2008/2005 (Click Here)
  • Load Relational XML data in SQL Server 2005 (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Keep an eye on replication error in SQL Server

Keep an eye on replication error in SQL Server

Setting up replication is may be little easy but to keep a constant watch on the performance of the replication is one of the tough task to do. If setup of replication is proper and if you are having a proper hardware and infrastructure, you won’t face much issue. Since we are not facing much issue, we may feel hectic to keep constant watch on replication health and this is the time you may face some trouble in replication and you are not alert to handle that uncertain situation.

DBA can’t afford to be in this situation. Every DBA has to prepare their self ready to handle as much uncertain situation as possible. I have created one small query to run in “Distributor” database of replication which show me error message with publisher, subscriber name and exact date time if anything goes wrong with my replication.

I used to call following SELECT query in my monitoring application so that if any error comes, I get immediate update from the person who is manually monitoring my application in the company. “Prevention is always better then cure” but nobody is sure enough that after having prevention, there won’t be any situation arise which force us to have cure. This is the reason I keep adding things in my monitoring application though I used to take as much prevention as possible while setting up things.

SELECT
ma.publisher_db,
ma.publication,
ma.subscriber_db,
msre.time,
msre.error_text
FROM
MSrepl_errors msre
INNER JOIN
MSdistribution_history msh
ON
(msre.id = msh.error_id)
INNER JOIN
MSdistribution_agents ma
ON
(ma.id = msh.agent_id)
ORDER BY
msre.time DESC

Above SELECT query will return the result only, if we have any error in replication at the moment, otherwise you will get blank result set.

Over the time, I have written quite a few articles on the subject of “Replication”, If you wish to refer any of them, have a look at list given below:

  • Understanding Replication with implementation of Snapshot replication in SQL Server 2012 (Click Here)
  • Remove Replication from SQL Server 2012 (Click Here)
  • Error Fix – Cannot execute as the database principal(Click Here)
  • Setup Transaction Replication in SQL Server 2012 (Click Here)
  • Add new article in transactional replication in SQL Server 2012 (Click Here)
  • Delete article from transactional replication in SQL Server 2012 (Click Here)
  • Find highest latency in transactional replication in SQL Server 2012 (Click Here)
  • Be familiar with important terms of replication in SQL Server (Click Here)
  • Find Object participated in replication (Click Here)
  • Add table in transactional replication from script in SQL Server (Click Here)
  • Add stored procedure in transactional replication by script in SQL Server (click Here)
  • Forcefully remove replication publisher, subscriber and distributor in SQL Server (Click Here)
  • Move replicated database file to new location in SQL Server 2012 (Click Here)
  • Script backup of replication setup of SQL Server by SSIS and SMO (Click Here)
  • Insert Tracer Token to see replication latency (Click Here)
  • Generate script to add multiple table in replication as article (Click Here)
  • Find undistributed replication command in SQL Server (Click Here)
  • Keep watch on replication undistributed command in SQL Server via SSIS (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Point to consider when using SELECT over SET in SQL Server

Point to consider when using SELECT over SET in SQL Server

Few days back I got one stored procedure from one of the developer who told me that SP was working fine till now but today it is not functioning well. I have tried to look at the issue and logically it seems perfect as per the business logic we had and there is no reason for it to not to work properly.

After having one more review of TSQL code inside the SP, I have found that SELECT statement were used to assign the value to variable rather then SET statement and that was causing an issue. Personally I highly prefer to use SET over the SELECT if I have to assign value to variable.

Let me show you practically what kind of issue can happen if we use SELECT to assign value to variable.


DECLARE @Var VARCHAR(10)

SET @Var='1'
PRINT @Var

SET @Var=''
PRINT @Var

SET @Var='2'
PRINT @Var

PRINT '--SET Completed, SELECT Begin--'

DECLARE @Var1 VARCHAR(10)

SELECT @Var1='1' WHERE 1=1
PRINT @Var1

SELECT @Var1='2' WHERE 1=0
PRINT @Var1

SELECT @Var1='3' WHERE 1=1
PRINT @Var1

Here is the screen capture of the output of above query:

SET_SELECT

After seeing the result, can you assume what wrong has happened?

If you observer first three SET statement. You will find proper result. We have assigned value 1 then blank and then 2 to variable and those you can see printed in screen capture.

Now observer three SELECT statement. We have assigned “1” value in variable in first SELECT statement and when we print, we can see “1” printed for first SELECT in screen capture.

In second SELECT statement, we have assigned “2” but we have WHERE condition 1=0 which means there won’t be any value return by this query, not even NULL and now if you print variable, you can see value “1” is printed because SELECT is taking previously assigned value which is wrong. Now we have to make exercise to clean up previously assigned value if we want to use SELECT so better avoid SELECT and use SET.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Monitor SQL Server activity and setting by Server Dashboard report

Monitor SQL Server activity and setting by Server Dashboard report

Monitoring is one of the essential duty of any DBA. Many people used to purchase 3rd party monitoring tool of SQL Server and some people like me used to develop their own monitoring tool for customize requirement.

It is understood that every company neither afford 3rd party monitoring tool nor able to develop customize monitoring application, especially those companies who are very small in size and budget. SQL Server used to provide many wonderful as well as useful reports by default with SQL Server and one of that reports is “Server Dashboard”

Have a look at the “Server Dashboard” screen capture below which I have taken from my testing server which I used to use for all my SQL Server related testing and also use to write an article.

Dashboard

This dashboard report shows you important information such as:

“Configuration Details:” which shows Last start up time of SQL Server, Instance Name, SQL Server Product version and edition. One important is total number of schedule jobs in SQL Server Agent.

If there is special settings like Database Mail Xps, Min / Max server memeory etc., Dashboard report will show those details as well.

You can even track activity details like Total Active sessions/transaction and database along with Blocked Transaction and traces running if any.

It is very easy to open this report. Right click on SQL Server Instance Name in SSMS and following the screen capture below.

DashboardMenu

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Keep watch on replication undistributed command in SQL Server via SSIS

Keep watch on replication undistributed command in SQL Server via SSIS

In my earlier blog “Find undistributed replication command in SQL Server” I have exaplained the importance and usage of “sp_replmonitorsubscriptionpendingcmds” system stored procedure to find out undistributed command.

If we execute “sp_replmonitorsubscriptionpendingcmds”, you may get undistributed command one time manually but I always try to capture important information in my monitoring application so that If there is any issue, it comes to notice of our support people and they can inform DBA team immediately.

This is the requirement which triggered this article.

Here is my requirement and logic I want to implement.

I have one of the main production database (let us call this ProdDB) which is replicated to three different instance with same publication so we have three subscriber to ProdDB database. Out of these three databases, one database (let us call ProdDBSub1) is being used to display the report in our application which is being used by approx 12000 concurrent users.

Higher latency or huge undistributed command will provide wrong information to the client which is not at all acceptable and this is why I have captured latency of replication in my monitoring application (Find the script here) and also captured total undistributed command in my monitoring application, if undistributed command number keeps increasing for long time and doesn’t decrease, our support team member immediately call DBA to look at replication.

I have created one table in “DBAdb” database which has row for each publication/subscriber combination along with other important details. Each time I query “sp_replmonitorsubscriptionpendingcmds”, I used to store result in that table (update the current row only). I have this process in one SSIS package which is scheduled to run every 15 seconds .

Let us create some foundation in SQL Server before we start creating SSIS package.

--create new database, if it does not exists already
CREATE Database DBAdb
GO

--create one table which stored information for undistributed command
USE [DBAdb]
GO

CREATE TABLE [UndistributedCommands](
[Seq] [int] IDENTITY(1,1) NOT NULL,
[PublicationName] [varchar](100) NULL,
[SubscriberName] [varchar](100) NULL,
[PendingCommands] [int] NULL,
[PendingDuration] [int] NULL,
[LastRefreshed] [datetime] NULL,
[LastNonEmpty] [datetime] NULL
) ON [PRIMARY]
GO

--insert one record for your publication/subscriber.
--this record will be updated everytime for same publication/subscriber
INSERT INTO UndistributedCommands
SELECT 'ProdDBPub','ProdDBSub',0,0,GETDATE(),GETDATE()
GO

Now create one SSIS package and have three package level variable as shown in following screen capture.

1Variable

After having all three variables, take one “Execute SQL” Task in your package and use following query inside the task.

--system stored procedure to run in distribution database
execute sp_replmonitorsubscriptionpendingcmds
--replication publisher server
@publisher ='PubServer',
--replication publisher database
@publisher_db = 'ProdDBPub',
--replication publication name
@publication ='ProdDBSub',
--replication subscriber server
@subscriber ='SubServer',
--replication subscriber database
@subscriber_db ='ProdDBSub1',
--choose type of subscription you have
@subscription_type ='1' --0 for push and 1 for pull
GO

Here is the screen capture of “Execute SQL” Task:

2ExecuteSQL

System Stored Procedure “sp_replmonitorsubscriptionpendingcmds” will return result set and that should be mapped with one of the variable we have created above. Follow the screen capture.

Please note that, I have had database connection of distribution database of replication for above given screen capture.

3ExecuteSQL

Now, take one foreach loop container and iterate it with “Obj_Result” variable.

4ForEachLoop

Map two variables with the result set coming from “sp_replmonitorsubscriptionpendingcmds” in the Foreach Loop container itself:

5ForeachLoop

Now take one more execute SQL Task and kept it inside the foreach loop container. That “Execute SQL” task will have following query to update data in table.

DECLARE @PendingCMDCount BIGINT
DECLARE @ExstimatedProcessTime BIGINT

SET @PendingCMDCount = ?
SET @ExstimatedProcessTime =?

UPDATE
UndistributedCommands
SET
PendingDuration = @ExstimatedProcessTime ,
PendingCommands = @PendingCMDCount,
LastRefreshed = getdate()
WHERE
SubscriberName = 'ProdDBSub' AND PublicationName='ProdDBPub'

IF @PendingCMDCount <> 0
BEGIN
UPDATE
UndistributedCommands SET LastNonEmpty = getdate()
WHERE
SubscriberName = 'ProdDBSub' AND PublicationName='ProdDBPub'
END

6ExecuteSQL

Please note that this Execute SQL task is having connection with “DBAdb” database and my “UndistributedCommand” table is there and data from this table will come to my monitoring application.

We have collected two valued in two different variable in Foreach Loop Container which we will pass to “Execute SQL Task” inside the loop.

7ExecuteSQL

Now, you are having package ready to run.

8FinalPackage

Execute this package from SQL Server Job or from Windows Schedule task via .BAT file as per the frequency needed for your business logic, I used to keep it at 15 seconds. Display the rows of “UndistributedCommands” in monitoring application, if you have, if undistributed command is >0.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.