Deal with message queue conversation with state CD in Service Broker

Deal with message queue conversation with state CD in Service Broker

Each side of a Service Broker conversation is represented by a conversation endpoint and sys.conversation_endpoints catalog view contains a row per conversation endpoint in the database. Generally when service broker accept record in message queue, it used to process message and remove it after closing by its own.

Recently at one of client site I have seen that messages are having “CD” state in sys.conversation_endpoints catalog view but it is not being removed from the message queue. Message queue was having approx half a million message with “CD” closed state so in order to remove those messages, I have created one small script to clean it up which might be helpful for you as well sometime.

[sourcecode language=”sql”]
CREATE PROCEDURE [CleanConversations]
AS
SET NOCOUNT ON
BEGIN
BEGIN TRY

DECLARE @handle UNIQUEIDENTIFIER

DECLARE conv CURSOR FOR SELECT a.conversation_handle FROM sys.conversation_endpoints a with(nolock)
WHERE state = ‘CD’

OPEN conv

FETCH NEXT FROM conv INTO @handle

WHILE @@FETCH_STATUS = 0 BEGIN

END CONVERSATION @handle WITH CLEANUP
FETCH NEXT FROM conv INTO @handle
PRINT @handle

END

CLOSE conv

DEALLOCATE conv

END TRY

BEGIN CATCH

DECLARE @msg VARCHAR(MAX)
SELECT @msg = ERROR_MESSAGE()
RAISERROR(‘Error in %s: %s’, 16, 1, ‘CleanConversations’, @msg)

END CATCH
END
GO
[/sourcecode]

After having the above stored procedure, you can run it and clear all conversation which are already closed. Generally message should be closed and removed by its own but once in a blue moon if you come across the situation which I have observed at my client site, you can use this SP.

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 Service Broker Queue Delay in SQL Server

Keep watch on Service Broker Queue Delay in SQL Server

We have very large setup of Service Broker enabled in our live databases. Out of which, some of the nightly business processes create a huge target queue which we suppose to finish during the night time itself. If we can’t cleanup all these queue in night time, it creates additional overhead during the day time when most of the customer uses our SaaS product which we can’t afford.

This is the reason I am using one very small script which gives me approximate (not exact) idea about how much time current queue will take to complete. If it is taking longer time then I can increase the threads to process it faster to get it done in night time. BTW, one needs to keep watch on locking if threads are blocking each other, you won’t get much benefit of increasing threads.

Note: I am using one user defined function “GetQueueCount” in the script below so if you don’t have function “GetQueueCount” than kindly create it first.

Here is the script I am using:

[sourcecode language=”sql”]
/*
EXECUTE CheckQueueDelay ‘ProviceYourQueueName’,100
*/
CREATE PROCEDURE CheckQueueDelay
(
@QueueName VARCHAR(100),
@MaxMin INT = 10
)
AS
DECLARE @cnt INT
SELECT @cnt = 1

DECLARE @a INT, @b INT, @tot INT

WHILE @cnt <= @MaxMin BEGIN
SELECT @a = rows FROM GetQueueCount() WHERE name = @QueueName
IF @a = 0 break;
WAITFOR DELAY ’00:01:00′
SELECT @b = rows FROM GetQueueCount() WHERE name = @QueueName
IF @b = 0 break;
SELECT @tot = @a – @b
RAISERROR (‘Processed %d rows in 1 minute’, 10, 1, @tot) WITH NOWAIT
SELECT @tot = @b / (@a – @b)
RAISERROR (‘Need %d minutes to process remaining %d rows’, 10, 1, @tot, @b) WITH NOWAIT
SELECT @cnt = @cnt + 1
END
GO
[/sourcecode]

Now, you can execute this stored procedure to see how much time would it take to complete the queue. If queue is processing and it is also getting new records at the same time, you might not have exact value.

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.

Milestone of 500+ article

Milestone of 500+ article

This post is 504th post in my blog. I have started sharing my knowledge in form of blog article three years back. Initially I, just, wanted to keep my script collection online so that I can find it easily as and when I need it but later on my blog has gained overwhelming response from the community.

I have started my blogging journey with “SQLHub.com” which was focusing on relational database only but later one I think to go beyond relational database and hence I have booked another domain, named “Extreme-Advice.com”.

I have started writing articles in “Extreme-Advice.com” 5 months back only. As soon as I have started “Extreme-Advice.com”, I found I had Alexa rank 1.8+ million world wide and 100K + in India but due to overwhelming response from the reader, I have reached to 19K+ rank in India and 160K+ world wide.

alexa

I don’t like to measure my blog performance by rank or by the hit I used to get. I would always like to know how many people I have helped so far and each email from the reader motivate me to work even harder as a blogger.

Whatever script I develop for my need, I used to share it with my blog reader. I share, mostly, those script which I use myself but out of all scripts some of the scripts are very useful and I would like to keep it handy so that I can easily find it out. Herewith, I am proving bookmark of those script and I hope you too find it useful and keep it handy, you never know, when shall you need it!!!

DBA Related Articles:

  • Query to Find missing job in SQL Server (Click Here)
  • Capture Schema Change in SQL Server to maintain history (Click Here)
  • Capture long running query in SQL Server (Click Here)
  • Analyze IO disk pressure in SQL Server (Click Here)
  • Transactions/Sec and Batch Requests/Sec performance counter in SQL Server (Click Here)
  • Find Disk performance with sys.Dm_io_virtual_file_stats in SQL Server (Click Here)
  • Find currently running session/process in SQL Server database/instance with help of sys.dm_exec_requests and sys.dm_exec_sessions (Click here)
  • use sys.dm_exec_requests and sys.dm_exec_sessions to find who are logged in to your SQL Server Instance (Click Here)
  • Find Pending IO with sys.dm_io_pending_io_requests and sys.Dm_io_virtual_file_stats in SQL Server (Click Here)
  • Find locked table name with duration and count in SQL Server (Click Here)
  • Find Blocking in SQL Server and use it to send an alert (Click Here)
  • Calculate Table & Schema size in SQL Server (Click Here)
  • Get row count for tables quickly in SQL Server (Click Here)
  • Find database restore date in SQL Server (Click Here)
  • Find Database backup history in SQL Server (Click Here)
  • Generate script to create and drop Primary Key and Foreign Key in SQL Server (Click Here)
  • “Max degree of parallelism” or “MAXDOP”, good guy or bad guy? (Click Here)
  • Find SQL Server Agent job ran on specific date with its status (Click Here)
  • Keep watch on your disk space in SQL Server 2008/2005 (Click Here)
  • Keep watch on your database file size in SQL Server 2008/2005 (Click Here)
  • Performance Counter in SQL Server (Click Here)
  • Repl. Pending Xacts performance counter and high value in SQL Server (Click Here)
  • Performance Counter in Microsoft Windows Server 2008 (Click Here)
  • Implementing physical Database Structure in SQL Server (Click Here)
  • Create windows login and user in all databases with dataReader and dataWriter role in SQL Server 2008 (Click Here)
  • Shrink log file for all database in SQL Server 2008/2005 (Click Here)
  • Find user in all database with Sp_MSforEachDB in SQL Server 2005 (Click Here)
  • NOLOCK hint in SQL Server, Good guy or bad guy? (Click Here)
  • LOG Parser – Wonderful yet under-appreciated tool to read log of windows, IIS (Click Here)
  • Read IIS log with LOG Parser in SQL-Like language (Click Here)
  • Add/Remove column in IIS log file (Click Here)
  • Auto IIS log reading with Log Parser (Click Here)
  • Find TCP/IP port of SQL Server 2012 (Click Here)
  • Monitor user login connected to SQL Server (Click Here)
  • Start and stop server side trace profiler automatically in SQL Server 2012 (Click Here)
  • Read SQL Server Profiler Trace files with SELECT statement (Click Here)
  • Search SQL Server Object (Click Here)
  • Mirror backup to take full backup at multiple location in SQL Server (Click Here)
  • Split SQL Server Full backup in multiple files for faster backup (Click Here)
  • Backup and Restore Filegroup in SQL Server (Click Here)
  • Search SQL Server Job based on keyword provided (Click Here)
  • CXPACKET wait stats in SQL Server (Click Here)
  • Script all SQL Server Job automatically by SSIS and SMO (Click Here)
  • List of Table in Filegroup with table size in SQL Server (Click Here)
  • Automatic script backup of email operator in SQL Server by SSIS and SMO (Click Here)
  • Automatic script backup of mail profile in SQL Server by SSIS and SMO (Click Here)
  • GRANT VIEW DEFINATION permission to all Stored Procedures in SQL Server (Click Here)
  • Backup Linked Server script in SQL Server by SSIS (Click Here)
  • Keep watch on heavy software usage directly in production server (Click Here)
  • Get list of failed SQL Server Agent job in SQL Server (Click Here)
  • Get list of SQL Server Jobs from multiple instance via SSIS package (Click Here)

Service Broker Articles:

  • Importance of Service Broker in SQL Server (Click Here)
  • Introduction of Service Broker in SQL Server (Click Here)
  • Enable Service Broker in SQL Server database (Click Here)
  • Small demonstration of Service broker for beginners (Click Here)
  • Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server (Click Here)
  • Find Service Broker Queue Count in SQL Server (Click Here)
  • Get Transmission queue row count in Service Broker (Click Here)
  • Purge target Transmission queue of Service Broker in SQL Server (Click Here)
  • Deal with size of Transmission Queue table sysxmitqueue in MSDB database (Click Here)
  • Service Broker implementation between two tables in one database (Click Here)
  • Service Broker Queue Activation and Thread in SQL Server (Click Here)

INDEX Related Articles:

  • Some basics about Index in SQL Server (Click Here)
  • Clustered Index in SQL Server (Click Here)
  • Nonclustered Index in SQL Server (Click Here)
  • Included Column Index with non clustered index in SQL Server 2005/2008/Denali (Click Here)
  • Filtered Index in SQL Server 2008/Denali (Click Here)
  • Cannot create index on view Msg 1940, Level 16, State 1, Line 1 (Click Here)
  • Calculate total size of clustered and non-clustered index for database in SQL Server 2008/2005 (Click Here)
  • Full Text Catalog and Full Text Index (Integrated Full Text Search – iFTS in SQL Server 2008 Part 2) (Click Here)
  • Do you know Index Statistics in SQL Server (Click Here)
  • Index Rebuild or Reorganize in SQL Server (Click Here)
  • Index Fillfactor in SQL Server (Click Here)
  • Find missing Index with DMVs in SQL Server 2005/2008/Denali (Click Here)
  • Find unused index in SQL Server 2005/2008/Denali (Click Here)
  • sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats in SQL Server 2005 (Click Here)
  • Move clustered index to different filegroup in SQL Server (Click Here)

Replication Related Articles:

  • 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)
  • Keep an eye on replication error in SQL Server (Click Here)

Mirroring Related Articles:

  • Setup Certificate mirroring in SQL Server 2008 R2 (Click Here)
  • Check mirroring status from mirroring monitor in SQL Server (Click Here)
  • Find mirroring status by querying sys.database_mirroring in SQL Server (Click Here)
  • Database maintenance in mirroring principal database in SQL Server (Click Here)
  • Mirroring SYNCHRONIZING status and DBMIRROR_DBM_EVENT wait type (Click Here)

Error Handling & Transaction Related Articles:

  • Introduction to Filestream in SQL Server 2012 (Click Here)
  • Error handling with “THROW” command in SQL Server 2012 (Click Here)
  • List of Errors and severity level in SQL Server with catalog view sysmessages (Click Here)
  • Create custom error message by sys.sp_addmessage in SQL Server 2012 (Click Here)
  • Importance of transaction in SQL Server (Click Here)
  • Capture and log error of TSQL code in SQL Server 2012 (Click Here)
  • SET option XACT_ABORT and SQL Server Transaction (Click Here)

 Filestream Related Articles:

  • Introduction to Filestream in SQL Server 2012 (Click Here)
  • Create table with Filestream column in SQL Server 2012 (Click Here)

Microsoft Windows / SQL Azure:

  • What is Cloud Computing? (Click Here)
  • Understanding Microsoft Cloud and its essential part (Click Here)
  • Setup development environment for Windows Azure (Click Here)
  • Create Database and Server in SQL Azure (Click Here)
  • SQL Server 2008 SSMS and lower can’t be connected to SQL Azure (Click Here)
  • DMV to monitor SQL Azure database/Server (Click Here)

Encryption – Decryption:

  • Encryption – Decryption in SQL Server 2008 Part 1 (Click Here)
  • Service Master Key (Encryption – Decryption in SQL Server 2008 Part 2) (Click Here)
  • Database Master Key (Encryption – Decryption in SQL Server 2008 Part 3) (Click Here)
  • Certificate (Encryption – Decryption in SQL Server 2008 Part 4) (Click Here)
  • Asymmetric Key (Encryption – Decryption in SQL Server 2008 Part 5) (Click Here)
  • Symmetric Key (Encryption – Decryption in SQL Server 2008 Part 6) (Click Here)
  • Backup Database with Encrypted Data and Restore it (Encryption – Decryption in SQL Server 2008 Part 7) (Click Here)

Full Text Search:

  • Introduction of iFTS (Integrated Full Text Search – iFTS in SQL Server 2008 Part 1) (Click Here)
  • Full Text Catalog and Full Text Index (Integrated Full Text Search – iFTS in SQL Server 2008 Part 2) (Click Here)
  • FREETEXT Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 3) (Click Here)
  • FREETEXTTABLE Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 4) (Click Here)
  • CONTAINS Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 5) (Click Here)
  • CONTAINSTABLE Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 6) (Click Here)

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 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.

[sourcecode language=”sql”]
CREATE TABLE Orders
(
ID INT IDENTITY(1,1),
ClientID VARCHAR(6),
TestDetails XML,
OrderDate DATETIME DEFAULT GETDATE()
)
GO
[/sourcecode]

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.

[sourcecode language=”sql”]
— 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
[/sourcecode]

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?

[sourcecode language=”sql”]
SELECT * FROM Orders
GO
[/sourcecode]

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.

[sourcecode language=”sql”]
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
[/sourcecode]

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.

[sourcecode language=”sql”]
CREATE TABLE Orders
(
ID INT IDENTITY(1,1),
ClientID VARCHAR(6),
TestDetails XML,
OrderDate DATETIME DEFAULT GETDATE()
)
GO
[/sourcecode]

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.

[sourcecode language=”sql”]
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
[/sourcecode]

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?

[sourcecode language=”sql”]
SELECT * FROM Orders
GO
[/sourcecode]

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.

[sourcecode language=”sql”]
CREATE TABLE Orders
(
ID INT IDENTITY(1,1),
ClientID VARCHAR(6),
TestDetails XML,
OrderDate DATETIME DEFAULT GETDATE()
)
GO
[/sourcecode]

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.

[sourcecode language=”sql”]
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
[/sourcecode]

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

[sourcecode language=”sql”]
SELECT * FROM Orders
GO
[/sourcecode]

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.

[sourcecode language=”sql”]
CREATE TABLE Orders
(
ID INT IDENTITY(1,1),
ClientID VARCHAR(6),
TestDetails XML,
OrderDate DATETIME DEFAULT GETDATE()
)
GO
[/sourcecode]

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:

[sourcecode language=”sql”]
–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
[/sourcecode]

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:

[sourcecode language=”sql”]
SELECT
*
FROM
Orders
WHERE
TestDetails.exist(‘(/TestDetails/Test[@Name=”Pesticide Group1”])’) = 1
GO
[/sourcecode]

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.

[sourcecode language=”sql”]
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
[/sourcecode]

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.