Tag Archives: SQL Server

Service Broker implementation between two tables in one database

Service Broker implementation between two tables in one database

SBDemoI have already provided small demonstration of how service broker works at “Small demonstration of Service broker for beginners” along with list of articles related to service broker from the concept of service broker to its implementation and definition of important part of Service Broker so I will not those concept here in this article, If you wish, you can have a look at those articles, here is the list:

  • 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)

Let us now start implementing Service Broker for two tables in one database.

Let me explain the business logic which will be used in this article.

Sales people in Company “Extreme Trading” (ET) goes to client site and accept the order from client. Sales people may use iPad, iPhone or any other smart phone, tablet, laptop or other portable device to insert order of client.

There is a rule in company that any order shouldn’t have same client id and project id. If same client give order for same project, it should be added in original order. Original order should be edited. It shouldn’t insert new order id for same client, project combination.

Well, we can define composite primary key or put some validation or check constraint etc. but I don’t want to debate on this rule.

If any duplicate order comes, it should be accepted from the sales people but later on it should insert original order id along with new order id in one table so back office team can handle it and sales person could be free immediately without even waiting for validation gets completed.

Demonstrating this exercise, we will need one database along with two sample table before we start implementing actual service broker.

--create database.
CREATE DATABASE ExtremeAdviceSBDemo
GO

--making broker enable for database.
ALTER DATABASE ExtremeAdviceSBDemo SET ENABLE_BROKER
ALTER DATABASE ExtremeAdviceSBDemo SET TRUSTWORTHY ON;
ALTER AUTHORIZATION ON DATABASE::ExtremeAdviceSBDemo TO [sa];
GO

USE ExtremeAdviceSBDemo
GO

--two sample table for demonstration
CREATE TABLE OrderDetail
(
OrderID INT IDENTITY(1,1),
ClientID VARCHAR(10),
ProjectID VARCHAR(50),
OrderDate DATETIME DEFAULT GETDATE()
)
GO

CREATE TABLE OrderHistory
(
ID INT IDENTITY(1,1),
OrderID INT,
DuplicateWith INT,
CheckDate DATETIME DEFAULT GETDATE()
)
GO

Now, let us start service broker code. I will not explain each block of code here, if you wish, you can refer my earlier articles given in first paragraph here.

--Create Message Type
CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Request] VALIDATION = WELL_FORMED_XML
CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Response] VALIDATION = WELL_FORMED_XML
GO

--Create Contract
CREATE CONTRACT [EXTREME://EX/Contract/Order]
(
[EXTREME://EX/Message/Order/Request] SENT BY INITIATOR,
[EXTREME://EX/Message/Order/Response] SENT BY TARGET
);
GO

--Create Initiator queue reader SP
CREATE PROC [OrderInitiatorQueueReader]
AS
SET NOCOUNT ON;
DECLARE              @XML XML,
@MessageBody VARBINARY(MAX),
@MessageTypeName SYSNAME,
@ConversationHandle UNIQUEIDENTIFIER,
@Response XML

BEGIN TRY

WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION
WAITFOR (
RECEIVE TOP(1)
@MessageTypeName = message_type_name,
@MessageBody = message_body,
@ConversationHandle = [conversation_handle]
FROM dbo.OrderInitiatorQueue
), TIMEOUT 60000

IF (@@ROWCOUNT = 0 AND @@TRANCOUNT>0)
BEGIN
ROLLBACK TRANSACTION
BREAK
END

IF @MessageTypeName = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' BEGIN
END CONVERSATION @ConversationHandle;
END
COMMIT TRANSACTION
END
END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
DECLARE @msg VARCHAR(MAX)
SELECT @msg = ERROR_MESSAGE()
DECLARE @line INT, @proc VARCHAR(MAX)
SELECT @line = ERROR_LINE(), @proc = ERROR_PROCEDURE()
SELECT @msg = 'proc: ' + @proc + '; line: ' + CAST(@line as varchar) + '; msg: ' + @msg

END CATCH
GO

--Create Initiator Queue
CREATE QUEUE OrderInitiatorQueue
WITH STATUS = ON,
ACTIVATION(
PROCEDURE_NAME = OrderInitiatorQueueReader,
MAX_QUEUE_READERS = 1,
EXECUTE AS OWNER
);
GO

-- Create actual business logic SP
CREATE PROCEDURE [CheckOrderMessage]
(
@data XML
)
AS
BEGIN TRY

DECLARE @MessageType VARCHAR(50)
,@OrderID INT
,@ClientID VARCHAR(10)
,@ProjectID VARCHAR(50)

SELECT  @MessageType = @Data.value('(OrderMessage/Header/@MessageType)[1]','VARCHAR(50)'),
@OrderID = @data.value('(OrderMessage/Header/@OrderID)[1]','INT'),
@ClientID = @Data.value('(OrderMessage/Header/@ClientID)[1]','VARCHAR(10)'),
@ProjectID = @data.value('(OrderMessage/Header/@ProjectID)[1]','VARCHAR(50)')

SELECT @MessageType = ISNULL(@MessageType,'')

IF @MessageType = 'OrderHistory' AND @OrderID IS NOT NULL BEGIN
DECLARE @OrderIDOld INT
,@ClientIDOld VARCHAR(10)
,@ProjectIDOld VARCHAR(50)

SELECT @ClientIDOld=ClientID, @ProjectIDOld=ProjectID, @OrderIDOld=OrderID
FROM OrderDetail
WHERE ClientID=@ClientID AND ProjectID=@ProjectID AND OrderID<>@OrderID

IF(@OrderIDOld IS NOT NULL)
BEGIN
INSERT INTO OrderHistory (OrderID,DuplicateWith)
SELECT @OrderIDOld,@OrderID
END
RETURN
END
END TRY

BEGIN CATCH
DECLARE @msg VARCHAR(MAX)
SELECT @msg = ERROR_MESSAGE()
END CATCH
RETURN
GO

--Create Target queue reader SP
CREATE PROC [OrderTargetQueueReader]
AS
SET NOCOUNT ON;

DECLARE              @XML XML,
@MessageBody VARBINARY(MAX),
@MessageTypeName SYSNAME,
@ConversationHandle UNIQUEIDENTIFIER,
@Response XML

BEGIN TRY
WHILE (1 = 1)
BEGIN
WAITFOR (
RECEIVE TOP(1)
@MessageTypeName = message_type_name,
@MessageBody = message_body,
@ConversationHandle = [conversation_handle]
FROM dbo.OrderTargetQueue
), TIMEOUT 60000

IF (@@ROWCOUNT = 0)
BEGIN
BREAK
END
SET @XML = CAST(@MessageBody AS XML);
IF @MessageTypeName = 'EXTREME://EX/Message/Order/Request'
BEGIN
--PRINT 'executing CheckOrderMessage @XML'
EXECUTE CheckOrderMessage @XML

--PRINT 'sending response'
SELECT @xml = 'OK';
SEND ON CONVERSATION @ConversationHandle
MESSAGE TYPE [EXTREME://EX/Message/Order/Response](@xml);
--PRINT 'ending cnversation'
END CONVERSATION @ConversationHandle;
END
END
END TRY
BEGIN CATCH
DECLARE @msg VARCHAR(MAX)
SELECT @msg = ERROR_MESSAGE()
DECLARE @line INT, @proc VARCHAR(MAX)
SELECT @line = ERROR_LINE(), @proc = ERROR_PROCEDURE()
SELECT @msg = 'proc: ' + @proc + '; line: ' + CAST(@line as varchar) + '; msg: ' + @msg
SELECT @msg
END CATCH
GO

--Create Target Queue
CREATE QUEUE OrderTargetQueue
WITH STATUS = ON,
ACTIVATION(
PROCEDURE_NAME = OrderTargetQueueReader,
--You can change queue reader number based on your queue load
MAX_QUEUE_READERS = 1,
EXECUTE AS OWNER
);
GO

--------------Create SP which will be called from app
--or trigger or by any other way
--and activate service broker
CREATE PROCEDURE [SendOrderRequestMessage]
(
@data AS XML
)
AS
SET XACT_ABORT OFF;
BEGIN TRY
BEGIN TRANSACTION

DECLARE @DialogHandle UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @DialogHandle
FROM SERVICE [EXTREME://EX/Service/Order/Initiator]
TO SERVICE N'EXTREME://EX/Service/Order/Target'
ON CONTRACT [EXTREME://EX/Contract/Order]
WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @DialogHandle
MESSAGE TYPE [EXTREME://EX/Message/Order/Request](@data);
--END CONVERSATION @DialogHandle
COMMIT ;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
DECLARE @msg VARCHAR(MAX);
SELECT @msg = ERROR_MESSAGE();
PRINT @msg
END CATCH;
GO

--Create Initiator Service
CREATE SERVICE [EXTREME://EX/Service/Order/Initiator]  ON QUEUE [OrderInitiatorQueue] ([EXTREME://EX/Contract/Order])
GO

--Create Target Service
CREATE SERVICE [EXTREME://EX/Service/Order/Target]  ON QUEUE [OrderTargetQueue] ([EXTREME://EX/Contract/Order])
GO

Now, we will define one trigger which will call SB to check whether order is duplicate or not every time we insert new record (not calling trigger for Update, you can do it if it is your business need)

-------service broker call from trigger.
CREATE TRIGGER trg_OrderDetail_Ins
ON  OrderDetail
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Data XML
SELECT @Data =
(
SELECT
'OrderHistory' AS 'Header/@MessageType',
OrderID AS 'Header/@OrderID',
ClientID AS 'Header/@ClientID',
ProjectID AS 'Header/@ProjectID'
FROM
(SELECT * FROM inserted) AS T
FOR XML PATH(''), ROOT('OrderMessage'), TYPE
)

EXEC [SendOrderRequestMessage] @Data
END
GO

Now, we are ready with service broker architecture and ready to test whether SB is working or not.

--Testing SB by inserting one record in OrderDetail Table
--which will execute Trigger and trigger will activate SB
INSERT INTO OrderDetail(ClientID,ProjectID)
SELECT 'TC01','NY Healthcare'
GO

--you will get one record which we have inserted above.
SELECT * FROM OrderDetail
GO

--you will get no record in OrderHistory table
--as we have not inserted duplicate record yet.
SELECT * FROM OrderHistory
GO

--inserting same record again so SB will detect it as duplicate order
--that will be inserted in ORderHistory table
INSERT INTO OrderDetail(ClientID,ProjectID)
SELECT 'TC01','NY Healthcare'
GO

--you will get one record in OrderHistory table
--as we have inserted duplicate record
SELECT * FROM OrderHistory
GO

I have not handled Error properly in CATCH block of Stored Procedure but just printed error message on screen because error handling is out of scope of this article, If you want to handle Error properly in Stored Procedure, have a look at my earlier article 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.

Deal with size of Transmission Queue table sysxmitqueue in MSDB database

Deal with size of Transmission Queue table sysxmitqueue in MSDB database

I have explained earlier that how to purge transmission queue of service broker, Today I come up with one more solution related to transmission queue. I have received one question sometime back on Facebook page of my blog.

Reader has asked me that his MSDB database size is huge (87 GB) and even after shrinking, he wasn’t able to decrease the size.

I have sent him one query (find that query here) to execute in his MSDB database which returns the information about the size of table so he did that and I came to know “sysxmitqueue” table in MSDB database is consuming approx 85GB space.

Sysxmitqueue system table is used for transmission queue. As I have explained in my earlier article at “how to purge transmission queue of service broker”, close the open conversation with that script or execute following command in database.

ALTER DATABASE [msdb] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
GO

Please note that, the solution I am giving here is based on my personal experience, before executing this command in your live production environment, think twice whether this is feasible for your environment or not.

BTW, I have written some other articles related to service broker, if you are directly landed to this article and new to Service broker, have a look at following articles which covers Service Broker concept from scratch.

  • 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)

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.

Purge target Transmission queue of Service Broker in SQL Server

Purge target Transmission queue of Service Broker in SQL Server

Last Sunday I have got very interesting question from one of my blog reader and before we discuss his question and solve it, let me give you some basic understanding regarding how message traverse between Initiator and Target Queue in very simple word by eliminating deep technicality.

When any message comes to service broker, message has to pass through various way including validation until it reaches to target queue and get processed.

Transmission Queue plays an important role in whole service broker architecture. Transmission Queue works as a “postman” between Initiator Queue and Target Queue.

Let me give you this understanding in simple words, without including deep technical explanation.

Suppose we have two server “S1” and “S2”. We have service broker implemented between these two servers. S1 is an initiator and S2 is a target. S1 sends message to process to S2 and S2 replies back to S1 via Transmission Queue.

Here is simple explanation in brief wording.

  • S1 send message to process to S2 via Transmission Queue and starts conversation dialog.
  • S2 receive message from S1’s Transmission Queue and acknowledge the same to S1 via S2’s Transmission Queue
  • Now, S2 process the message and again acknowledge the same to S1 via S2’s Transmission Queue
  • Finally message get processed and S2 send acknowledge of the same via Transmission Queue to S1 to finally close the conversation dialog
  • When S1 closes the conversation, it acknowledge the same to S2 and message gets deleted from the transmission queue of S2.

Please note that, our task get completes in first three steps whereas fourth & fifth steps are formality to close open conversation in S1 & S2.

I have written articles to watching the message count currently stays inside different queues.

  • Find Service Broker Queue Count in SQL Server (Click Here)
  • Get Transmission queue row count in Service Broker (Click Here)

One of the blog reader has implemented the functions I have given in both of the above articles and found that his initiator and target queue always having few hundreds message in peak business hours and become even zero many times but his transaction queue in target server (S2 in our example) having millions of message unprocessed. He wanted me to help him in dealing with that huge queue.

Obviously I don’t have access to his server so I have to guess after getting certain information from him. I have asked him to execute following query in his S2 server ‘s database where he has setup service broker and transmission queue showing millions of records.

SELECT
'MessageNeedsToClose' AS ActionToTake,
COUNT(*) AS RowsCount
FROM
sys.transmission_queue
WHERE
message_body IS NULL
AND is_end_of_dialog = 1
AND enqueue_time < getdate() - 7
UNION ALL
SELECT
'MessageToKeep' AS ActionToTake,
COUNT(*) AS RowsCount
FROM
sys.transmission_queue
WHERE
message_body IS NOT NULL
GO

He sent me approx million+ records in “MessageNeedsToClose” and less then hundred count comes in “MessageToKeep”.

SELECT query before the UNION ALL clause checks where the row in transmission queue is having any message inside it or not. If Message_Body is NULL then there is no message at all. If “is_end_of_dialog” is 1 then that message is just an acknowledgment nothing else. So if there is no message to process in message_body and if message is just an acknowledgment, we can probably close it to fix the high number of queue one time, if message is already closed from initiator side.

Reader has already told me that transmission queue on initiator side (in server S1) always stays below 1000 and become zero also so there is no chance that the million message we have seen in S2 is still there open in S1.

Since I didn’t have access to his server to investigate further and the messages we have in target transmission queue is not a real business process, we can close that dialog. If I would have access to server, I would run profiler for broker in S1 as well as in S2 to see what is happening with that big transmission queue but the reader was already convinced and wanted to settled down by closing the conversation of dialog.

I have written one customized Stored procedure for him to create on Server S2 in the database where he has Service Broker setup.

CREATE PROCEDURE [CleanTransmissionQueueByEndingDialog]
AS
SET NOCOUNT ON
BEGIN
BEGIN TRY
DECLARE @ConvHandle UNIQUEIDENTIFIER

DECLARE conv CURSOR FOR

SELECT
S2TransQueue.conversation_handle
FROM
sys.conversation_endpoints AS S2ConvEnd WITH(NOLOCK)
INNER JOIN
sys.transmission_queue S2TransQueue WITH(NOLOCK)
ON
S2TransQueue.conversation_handle = S2ConvEnd.conversation_handle
AND S2TransQueue.message_body is null
AND S2TransQueue.is_end_of_dialog = 1
AND S2TransQueue.enqueue_time < getdate() - 7

--I HIGHLY recomment uncomment the following WHERE clause to check
--where same message is alive in initiator end or not
--if it is alive, we shouldn't close that message.

--WHERE NOT EXISTS(
-- SELECT 1
-- --S1 is a Linked Server, you have to replace it with your linked server of Initiator
-- --"DatabaseName" should be replace by your database name where you have setup
-- --Service Broker in Initiator Server.
-- FROM S1.DatabaseName.sys.conversation_endpoints S1ConvEnd with(nolock)
-- WHERE S1ConvEnd.conversation_id = S2ConvEnd.conversation_id
--)

OPEN conv
FETCH NEXT FROM conv INTO @ConvHandle

WHILE @@FETCH_STATUS = 0 Begin
END CONVERSATION @ConvHandle WITH cleanup
FETCH NEXT FROM conv into @ConvHandle
PRINT @ConvHandle
END

CLOSE conv
DEALLOCATE conv

END TRY

BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
END
GO

Please note that I have investigated this issue without accessing live server of blog reader. Whatever suggestions I have given here is going to affect your live production environment if you are executing this script in production environment so please give a second thought and do it at your own risk.

I have written some other articles related to service broker, if you are directly landed to this article and new to Service broker, have a look at following articles which covers Service Broker concept from scratch.

  • 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)

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.

Importance of Service Broker in SQL Server

Importance of Service Broker in SQL Server

Sooner or later every business process needs some asynchronous processes to cater the business needs and keep their application free from the heavy processes which can be tedious for the user if it directly processed from the application. It may decrease the performance of the application.

There are many asynchronous processes (like MSMQ, Web services, WCF and many more) but Service Broker is one of my favorite if I have setup of SQL Server for the application.

Service broker manages the queue as a database object and hence we get native support of SQL Server so that if we take a backup of SQL Server database, queue will be backed up with it.

Let me give you some situation where I have recently set up Service Broker.

I was working with one home health care management system of New York. Many agencies working with that SAAS (Software As A Service) product and we used to find thousands of concurrent user actively using that application in business hours. Every Wednesday each agency used to generate Invoice and billing for the insurance company as well as for staff because Friday is a payment day so invoice, pay slip etc. should be ready and calculated on Wednesday.

We used to get heavy database & network pressure at that time, out application keeps hanging and it shows time out error to many of the agencies too which is very annoying. We used to help them by creating invoices/bills manually directly from the backend but it is not good to approach client like this so we have finally decided to process all these calculations in form of service broker, as soon as client generates the request, we call Service broker and as soon as Service Broker completes process, it used to send an email to client so that they can get their bills/invoices from the application without making a pressure in application and getting timeout error.

This is really a power of asynchronous message processing.

I would like to share one more business requirement which forced me to set up service broker recently.

I was in involved in one project of one of the environmental laboratory in New Jeary, USA where they used to perform test for Air, Soil and Water. They have many different types of test used to perform on different types of samples. They define a special way to perform the test based on government norms as well as client’s demand. That special way (chemical to process the test) needs a combination of many different chemicals and other required stuff. Once they define this way, they named it “Recipe”. One Recipe may have many different chemicals as well as other Recipe too. While generating the report of Recipe, we needs to go infinite inside the content of Recipe as it may be single chemical or other Recipe and that other Recipe could be created from other Recipe. This Recipe report and calculation take too much time and hence intranet based .NET web application used to give time out error so many times and user gets frustrate so in this situation we have decided to use Service Broker so that user can put request for Recipe report from .NET application, Service Broker completes the process and send an email to user about completion.

This is how Services broker can be used. I will come up with one more article based on this topic very soon with the introduction of important part of Service Broker… stay tuned!!!

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.

Importance of transaction in SQL Server

Importance of transaction in SQL Server

Recently I have written few articles on error handling (list given in the last paragraph of this article) topic whereas I have used Transaction in my TSQL script there and have promised blog reader that I will come up with more details and explanation of transaction. Finally today I thought to write about transaction.

Transaction is one of the most important part for developer to be master in development. We might have cases where we are doing more then one combination of INSERT / UPATE / DELETE (DML statements) in TSQL code block. It is quite possible that any of the DML operation failed in-between execution and we want to revert back any DML changes made prior from that failure to keep consistency and integrity. Transaction comes to your help for this need and makes your database ACID (Atomicity, Consistency, Isolation and Durability) compliant (Click here to know more about ACID.).

According to me, there should be Begin Transaction, Commit Transaction and ROLLBACK Transaction with each TSQL block if it is using DML statements, no matter whether code block has one or more DML operation going.

Transaction creates locking so it is also advisable to keep your transaction as short as possible to avoid locking and blocking issue.

Let us see how we can use transaction in TSQL code block. We will need to table with some data for demonstration. Let us create two table and define foreign key between them.

CREATE TABLE tblEmployeeDetail
(
ID INT IDENTITY(1,1) CONSTRAINT pk_tblEmployeeDetail_Id PRIMARY KEY
,EmployeeName Varchar(100)
,Designation VARCHAR(50)
,City Varchar(20)
)
GO

CREATE TABLE tblEmployeeSalary
(
ID INT IDENTITY(1,1)
,EmployeeID INT CONSTRAINT fk_tblEmployeeDetail_id FOREIGN KEY REFERENCES tblEmployeeDetail(ID)
,Salary INT NOT NULL
)
GO

INSERT INTO tblEmployeeDetail
SELECT 'Ritesh Shah','DBA','Ahmedabad' UNION ALL
SELECT 'Teerth Shah','Developer','Ahmedabad'
GO

INSERT INTO tblEmployeeSalary
SELECT 1,50000 UNION ALL
SELECT 2,45000
GO

Execute following UPDATE statements which will ROLLBACK transaction when error come across.

--We are now going to update designation in EmployeeDetail table
--and update the salary. if there is an issue in any of the UPDATE statement
--both table should be in the previous state
BEGIN TRY
BEGIN TRANSACTION
UPDATE tblEmployeeDetail SET Designation='DBA Manager' WHERE ID=1

--giving NULL value though it is not allowed in SALARY field (as it is NOT NULL field)
--it will generate an error and the Designation in tblEmployeeDetail table will also revert back
UPDATE tblEmployeeSalary SET Salary=NULL WHERE EmployeeID=1
COMMIT TRANSACTION
END TRY
BEGIN CATCH
declare @trancount int;
set @trancount = @@trancount;
IF @trancount>0
BEGIN
ROLLBACK;
THROW
END
END CATCH
GO

--Confirm whether any record is changed or not
SELECT * FROM tblEmployeeDetail
SELECT * FROM tblEmployeeSalary
GO

Here is the error I have received while executing UPDATE code block.

1Error

Please note that This code should work in older version of SQL Server eg: SQL Server 2008 or 2008 R2 as well. You have to use RAISERROR instead of THROW statement.

If you are interested to read error handling article I have written previously, have a look at list below:

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)

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.

Create custom error message by sys.sp_addmessage in SQL Server 2012

Create custom error message by sys.sp_addmessage in SQL Server 2012

error

Social media is a wonderful way to share knowledge. I have my own Facebook page where I used to share my knowledge. Yesterday I see one message in my Facebook inbox. One SQL Server community member asked me whether it is possible to create our own error messages in SQL Server or not. He told me that after reading two of my previous articles (Error handling with “THROW” command in SQL Server 2012 and List of Errors and severity level in SQL Server with catalog view sysmessages) related to error handling, he has looked at catalog view “Master.DBO.SysMessages” and he wanted to define his own custom error messages.

Microsoft have given control to us to create custom messages as per our need and system stored procedure “sys.sp_addmessage” helps us to do this.

Let us look at one example for this:

--I want to add error with error number 50001
--let us check whether any error is having same number or not
--in my case, I don't find any error with 50001 number
select * from master.dbo.sysmessages where error ='50001';

--adding error message with 50001 number and severity 16
--if record duplicate records comes, we can execute this error
EXEC sys.sp_addmessage 50001, 16, '%s is already there in table %s.';
GO

--now we will get one row for default language (in my case it is 1033 which is US English)
--with 50001 number
select * from master.dbo.sysmessages where error ='50001';

Now, we have one custom message ready with us, let us check it by creating one sample table with some sample data row.


--now testing the error message whether it is working fine or not.
BEGIN TRY
DECLARE @Name VARCHAR(50),@City AS VARCHAR,@Count INT
SET @Name='Rajan Shah'
SET @City='Mumbai'

SELECT @Count=COUNT(1) FROM TestCustomError WHERE Name=@Name

--In this case, I have considered that Name column should be unique
--there may or may not be Primary or Unique Key defined
--but we can test it in business logic
--there may be argument for this approach but I just wanted to show
--whether custom error is working or not.
IF @Count<=0
BEGIN
BEGIN TRANSACTION
INSERT INTO TestCustomError (Name,City)
SELECT @Name,@City
COMMIT TRANSACTION
END
ELSE
BEGIN
DECLARE @ErrMessage varchar(500) = FORMATMESSAGE(50001, @Name, 'TestCustomError');
THROW 50001, @ErrMessage, 1;
END
END TRY

BEGIN CATCH
THROW;
END CATCH
GO

Scope of this article was to show how to add custom error message and check it. I have kept one loophole by not putting ROLLBACK anywhere in the code which itself is an interesting topic and out of the scope of this article. I will cover this point very soon.

Please note that This code should work in older version of SQL Server eg: SQL Server 2008 or 2008 R2 as well. You have to use RAISERROR instead of THROW statement.

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.

List of Errors and severity level in SQL Server with catalog view sysmessages

List of Errors and severity level in SQL Server with catalog view sysmessages

Few days back I have written “Error handling with “THROW” command in SQL Server 2012”. After reading this article, one of the regular blog readers has asked me question why I have used 16 severities for the error?

This is really interesting question so I thought to answer him in the form of this blog post so that everyone who is unaware with error messages in SQL Server can be benefited.

16 is a default severity level and used for most user exception and that was the reason I have used it. You can get a list of error messages along with its severity level and error number from following catalog view.


SELECT *

FROM master.dbo.sysmessages

WHERE

error=8134 -- error message number I got in previous article

AND msglangid = 1033; --language selection, 1033 represents US english

 

There are total number of 10542 error message comes by default for language id 1033 (US English). Error messages given in total 22 language so 10542 error * repeated 22 times = 231924 rows in sysmessages.

Generally we have sixteen different severity level in SQL Server 2012 and each severity represents criticalness of an error. You can get a list of severity from the following TSQL.


SELECT DISTINCT severity

FROM master.dbo.sysmessages;

Here is the brief description of different severity.

Severity level 0-10: These are just information message not actual error.

Severity level 11 to 16: These are errors caused due to user mistakes. We have tried to divide value by 0 in previous article and hence we got severity error 16.

Severity Level 17: This severity indicates that an operation making SQL Server out of resources or exceeding defined limit. That may be disk space or lock limit.

Severity Level 18: This error represents nonfatal internal software error.

Severity Level 19: This error represents some non-configurable internal limit has been exceeded and the current batch process is terminated. To be very frank, I have not seen this severity practically in my life.

Severity Level 20: This severity indicates current statement has encountered a problem and because of this severity level client connection with SQL Server will be disconnected.

Severity Level 21: This severity indicates that you have encountered a problem that affects all processes in the current database.

Severity Level 22: This error indicates problem with database table or index. It may be corrupt or damaged.

Severity Level 23: This error indicates problem with database integrity which may be fixed by DBCC command.

Severity Level 24: This error indicates problem with the hardware of SQL Server. Need to check disk drive and related hardware extensively.

Older version of SQL Server had Severity Level 25 as well but it is unexpected system error and doesn’t list in SQL Server 2012’s sysmessages catalog view.

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.

Script backup of replication setup of SQL Server by SSIS and SMO

Script backup of replication setup of SQL Server by SSIS and SMO

DBA should ever ready for Disaster recovery and provide high availability. It is not enough for DBA to take backup of database, set mirroring/replication of database. There are lot outside the database too, which is needed for failover or for preparing another server instead of regular server.

I have already provided way to “script all SQL Server Job automatically by SSIS and SMO”, “Automatic script backup of email operator in SQL Server by SSIS and SMO”, “Backup Linked Server script in SQL Server by SSIS” and “Automatic script backup of mail profile in SQL Server by SSIS”. Today I come up with article which generates the script for replication publisher, subscriber, article etc. in SQL Server instance by SSIS and SMO.

Replication is one of the important High Availability and disaster recovery option for DBA which is being used by application load balancing too. When I failover (manually or automatically) the server, I suppose to have same replication and hence I used to script it with me so that I can use the latest script in an emergency situation.

Let us now create one SSIS package which uses SMO script to generate the script of replication along with its article of SQL Server. I used to keep weekly schedule for this SSIS package in SQL Server Job Agent so that I have latest script of replication every week.

Let us now create new SSIS project and start creating package.

Have one “Script Task” in your package and double click on the “Script Task” so that you can get “Script Task Editor”. Click on “Edit Script” button from the “Script Task Editor” and you will get script windows where you can write down C# script (by default you get C# script editor).

ReplicationBackupScriptTask

You have to add reference for following name spaces.

Microsoft.SQLServer.ConnectionInfo

Microsoft.SQLServer.Management.Sdk.Sfc

Microsoft.SqlServer.Smo

Microsoft.SQLServer.Replication .NET Programming Interface

Apart from that, have following extra namespaces in “NameSpaces” region in your script window.


using Microsoft.SqlServer.Management.Smo.Agent;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Sdk.Sfc;

using Microsoft.SqlServer.Replication;

using System.IO;

using System.Collections.Specialized;

Now here is the code you have to place in your “Main” method of script window.


// TODO: Add your code here

string ServerName;

string UserName;

string Password;

string FolderDate;

string NetworkPath;

ServerName = &quot;ServerName&quot;;

UserName = &quot;Login&quot;;

Password = &quot;Password&quot;;

FolderDate = DateTime.Now.ToShortDateString().Replace('\\','_').Replace('/','_');

NetworkPath = @&quot;D:\&quot;+ ServerName.Replace('\\', ' ').ToString() + @&quot;\Replicaiton\&quot;+FolderDate+&quot;\\&quot;;

ServerConnection conn = new ServerConnection();

conn.LoginSecure = false;

conn.Login = UserName;

conn.Password = Password;

conn.ServerInstance = ServerName;

Server srv = new Server(conn);

System.IO.Directory.CreateDirectory(NetworkPath);

ReplicationServer RS = new ReplicationServer(conn);

try

{

foreach (ReplicationDatabase RD in RS.ReplicationDatabases)

{

if (RD.HasPublications)

{

foreach (TransPublication TP in RD.TransPublications)

{

TextWriter tw = new StreamWriter(NetworkPath + &quot;\\&quot; + TP.Name.ToString() + &quot;.sql&quot;);

tw.Write(TP.Script(ScriptOptions.Creation | ScriptOptions.IncludeAll ^ ScriptOptions.IncludeReplicationJobs));

tw.Close();

}

}

}

}

catch (Exception eh)

{

//MessageBox.Show(eh.ToString());

}

Dts.TaskResult = (int)ScriptResults.Success;

Once you setup proper path as well as credential of your SQL Server Instance, you are ready to go. Run package manually to check whether it works or not and then schedule it in SQL Server Job or in Windows Task to run weekly or as per your requirement.

Here is the screen capture I have received after I ran it manually.

ScriptBackupReplicationPublisherSSISPackageRun

We have many different server/instance and need script for all email operator in from all server and hence I have had loop for all our database server and script task under the loop so that one package connect to each server, take a script of all operator and save it to two different network location.

One of my team member and enthusiast SQL geek Mr. Nirav Gajjar (F) has helped me to develop this package and test it thoroughly.

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.

Backup Linked Server script in SQL Server by SSIS

Backup Linked Server script in SQL Server by SSIS

DBA should ever ready for Disaster recovery and provide high availability. It is not enough for DBA to take backup of database only, set mirroring/replication of database. There are lot outside the database too, which is needed for failover or for preparing another server instead of regular server.

I have already provided way to “script all SQL Server Job automatically by SSIS and SMO”, “Automatic script backup of email operator in SQL Server by SSIS and SMO” and “Automatic script backup of mail profile in SQL Server by SSIS”. Today I come up with article which generates the script for all Linkedserver in SQL Server instance by SSIS.

As long as possible, I, personally, try to avoid linked server but even in some scenario DBA used to use linked server because of any reason (don’t want to start debate of whether to use linked server or not). while making a plan for failover or for creating another server/instance of one of the production server, DBA has to keep Linked Server in mind otherwise some package, view, SP etc. can break in new server/instance if it is uses linked server.

Let us now create one SSIS package to generate the script of all Linked Server from SQL Server. I used to keep monthly schedule for this SSIS package in SQL Server Job Agent so that I have latest script of linked server every month.

Let us now create new SSIS project and start creating package.

Have one “Data Flow Task” in your package and double click on that.

1DataFlowTask

Once you double click on “Data Flow Task” you will be moved to “Data Flow” tab, right beside “Control Flow” tab above the “Data Flow Task”. Have one “OLE DB Source” task in “Data Flow” tab. Double click on “OLE DB Source” task, click on “New” button to create connection with your database and have following “SQL Command”.

SET FMTONLY OFF
Declare @SQL Varchar(Max)
Set @SQL =
'Declare @LinkedServer Table ( LinkedServer Varchar(Max) )
Declare @status smallint,
@server sysname,
@srvid smallint,
@srvproduct nvarchar(128),
@allsetopt int,
@provider nvarchar(128),
@datasrc nvarchar(4000),
@location nvarchar(4000),
@provstr nvarchar(4000),
@catalog sysname,
@netname varchar(30),
@srvoption varchar(30),
@loclogin varchar(30),
@rmtlogin varchar(30),
@selfstatus smallint,
@rmtpass sysname,
@passwordtext nvarchar(128),
@i int,
@lsb tinyint,
@msb tinyint,
@tmp varbinary(256)

select @allsetopt=number from master.dbo.spt_values
where type = ''A'' and name = ''ALL SETTABLE OPTIONS''

declare d cursor for
SELECT srvid,srvstatus, srvname, srvproduct, providername, datasource, location, providerstring, catalog, srvnetname
from master..sysservers where srvid > 0 open d

fetch next from d into @srvid, @status, @server, @srvproduct, @provider, @datasrc, @location, @provstr, @catalog, @netname

SET NOCOUNT ON

while (@@FETCH_STATUS<>-1)
begin
Insert Into @LinkedServer values(''--------------------'' + @Server + ''--------------------'')
If @status in (64,65)
Begin
Insert Into @LinkedServer values( ''sp_addserver'')
Insert Into @LinkedServer values('' @server = ''''''+ @server + '''''''')
Insert Into @LinkedServer values('' GO'')
If @status = 64
Begin
Insert Into @LinkedServer values( ''sp_serveroption'')
Insert Into @LinkedServer values( '' @server = ''''''+ @server + '''''','')
Insert Into @LinkedServer values( '' @optname = ''''rpc'''','')
Insert Into @LinkedServer values( '' @optvalue = ''''false'''''')
Insert Into @LinkedServer values('' GO'')
End
exec (''declare r cursor for select l.name, r.remoteusername from sysremotelogins r join sysservers s on r.remoteserverid = s.srvid join syslogins l
on r.sid = l.sid where s.srvname = ''''''+ @server + '''''''') open r fetch next from r into @loclogin, @rmtlogin
while (@@FETCH_STATUS<>-1)
begin
Insert Into @LinkedServer values( ''sp_addremotelogin'')
Insert Into @LinkedServer values('' @remoteserver = ''''''+ @server + '''''','')
Insert Into @LinkedServer values('' @loginame = ''''''+ @loclogin + '''''','')
Insert Into @LinkedServer values('' @remotename = ''''''+ @rmtlogin + '''''''')
Insert Into @LinkedServer values('' GO'')

fetch next from r into @loclogin, @rmtlogin end close r deallocate r
End
Else
Begin
If exists (select * from tempdb..sysobjects where name like ''#tmpsrvoption%'')
Begin
drop table #tmpsrvoption
End

Create Table #tmpsrvoption ( srvoption varchar(30) )

insert #tmpsrvoption
select v.name from master.dbo.spt_values v, master.dbo.sysservers s
where srvid = @srvid and (v.number & s.srvstatus)=v.number and (v.number & isnull(@allsetopt,4063)) <> 0 and v.number not in (-1, isnull(@allsetopt,4063))
and v.type = ''A'' Insert Into @LinkedServer values( ''sp_addlinkedserver'')

Insert Into @LinkedServer values('' @server = ''''''+ @server + '''''''')
Insert Into @LinkedServer values('', @srvproduct = '''''' + @srvproduct + '''''''')
If @srvproduct <> ''SQL Server''
Begin
Insert Into @LinkedServer values( '', @provider = '''''' + isnull(@provider,''NULL'') + '''''''')
Insert Into @LinkedServer values('', @datasrc = '''''' + isnull(@datasrc,''NULL'') + '''''''')
Insert Into @LinkedServer values('', @location = '''''' + isnull(@location,''NULL'') + '''''''')
Insert Into @LinkedServer values('', @provstr = '''''' + isnull(@provstr,''NULL'') + '''''''' )
Insert Into @LinkedServer values('', @catalog = '''''' + isnull(@catalog,''NULL'') + '''''''')
End

Insert Into @LinkedServer values( '' GO'')
Insert Into @LinkedServer values( ''sp_serveroption'')
Insert Into @LinkedServer values('' @server = ''''''+ @server + '''''','')
Insert Into @LinkedServer values('' @optname = ''''rpc'''','')
Insert Into @LinkedServer values('' @optvalue = ''''false'''''')
Insert Into @LinkedServer values('' GO'')
Insert Into @LinkedServer values(''sp_serveroption'')
Insert Into @LinkedServer values('' @server = ''''''+ @server + '''''','')
Insert Into @LinkedServer values('' @optname = ''''rpc out'''','')
Insert Into @LinkedServer values('' @optvalue = ''''false'''''')
Insert Into @LinkedServer values('' GO'')
Insert Into @LinkedServer values(''sp_serveroption'')
Insert Into @LinkedServer values('' @server = ''''''+ @server + '''''','')
Insert Into @LinkedServer values('' @optname = ''''data access'''','')
Insert Into @LinkedServer values('' @optvalue = ''''false'''''')
Insert Into @LinkedServer values('' GO'')

declare s cursor for SELECT srvoption from #tmpsrvoption open s
fetch next from s into @srvoption while (@@FETCH_STATUS<>-1)
begin
Insert Into @LinkedServer values( ''sp_serveroption'')
Insert Into @LinkedServer values('' @server = ''''''+ @server + '''''','')
Insert Into @LinkedServer values('' @optname = ''''''+ @srvoption + '''''','')
Insert Into @LinkedServer values('' @optvalue = ''''true'''''')
Insert Into @LinkedServer values('' GO'')

fetch next from s into @srvoption
End

close s
deallocate s

If exists (select * from tempdb..sysobjects where name like ''#tmplink%'')
Begin
drop table #tmplink
End

create table #tmplink ( rmtserver sysname, loclogin sysname null, selfstatus smallint, rmtlogin sysname null )

insert #tmplink exec (''sp_helplinkedsrvlogin ''''''+ @server + '''''''')

declare ll cursor for select loclogin, selfstatus, rmtlogin from #tmplink order by rmtlogin open ll fetch next from ll

into @loclogin, @selfstatus, @rmtlogin while (@@FETCH_STATUS<>-1)

begin If (@selfstatus = 1 and @loclogin is null)

Begin

Insert Into @LinkedServer values( ''sp_addlinkedsrvlogin'')

Insert Into @LinkedServer values('' @rmtsrvname = ''''''+ @server + '''''','')

Insert Into @LinkedServer values('' @useself = ''''true'''''')

Insert Into @LinkedServer values('' GO'')

End

Else If (@selfstatus = 1 and @loclogin is not null)
Begin
Insert Into @LinkedServer values(''sp_addlinkedsrvlogin'')
Insert Into @LinkedServer values('' @rmtsrvname = ''''''+ @server + '''''','')
Insert Into @LinkedServer values('' @useself = ''''true'''','')
Insert Into @LinkedServer values('' @locallogin = ''''''+ @loclogin + '''''','')
Insert Into @LinkedServer values('' @rmtuser = NULL,'')
Insert Into @LinkedServer values('' @rmtpassword = NULL'')
Insert Into @LinkedServer values('' GO'')
End Else If (@selfstatus = 0 and @rmtlogin is null)
Begin
Insert Into @LinkedServer values(''sp_addlinkedsrvlogin'')
Insert Into @LinkedServer values('' @rmtsrvname = ''''''+ @server + '''''','')
Insert Into @LinkedServer values('' @useself = ''''false'''','')
Insert Into @LinkedServer values('' @locallogin = NULL,'')
Insert Into @LinkedServer values('' @rmtuser = NULL,'')
Insert Into @LinkedServer values('' @rmtpassword = NULL'')
Insert Into @LinkedServer values('' GO'')
End Else If (@selfstatus = 0)
Begin
exec (''declare password cursor for select l.password from master..sysservers s join master..syslogins l on s.srvid = l.sid
where s.srvname = ''''''+ @server + '''''' and l.name = ''''''+ @rmtlogin + '''''''')

open password fetch next from password into @rmtpass while @@fetch_status = 0
begin
Insert Into @LinkedServer values(''sp_addlinkedsrvlogin'')
Insert Into @LinkedServer values('' @rmtsrvname = ''''''+ @server + '''''','')
Insert Into @LinkedServer values('' @useself = ''''false'''','')

If (@loclogin is null)
Begin
Insert Into @LinkedServer values('' @locallogin = NULL,'')
End
Else
Begin
Insert Into @LinkedServer values('' @locallogin = ''''''+ @loclogin + '''''','')
End

If (@rmtlogin is null)
Begin
Insert Into @LinkedServer values('' @rmtuser = NULL,'')
End
Else
Begin
Insert Into @LinkedServer values('' @rmtuser = ''''''+ @rmtlogin + '''''','')
End

If (@rmtpass is null)
Begin
Insert Into @LinkedServer values('' @rmtpassword = NULL'')
End
Else
Begin
Insert Into @LinkedServer values('' @rmtpassword = ''''''+ @rmtpass + '''''''')
End

Insert Into @LinkedServer values('' GO'')

fetch next from password into @rmtpass
end
close password

deallocate password
End

fetch next from ll into @loclogin, @selfstatus, @rmtlogin End close ll deallocate ll
End

If @netname <> @server
Begin
Insert Into @LinkedServer values( ''sp_setnetname'')
Insert Into @LinkedServer values('' @server = ''''''+ @server + '''''','')
Insert Into @LinkedServer values('' @network_name = ''''''+ @netname + '''''''')
End

fetch next from d into @srvid,@status, @server, @srvproduct, @provider, @datasrc, @location, @provstr, @catalog, @netname
End close d deallocate d Select [LinkedServer] From @LinkedServer'

Exec (@SQL)

Here is the screen capture of “OLE DB Source” property.

2PropertyofDataflow

Once you are done with “OLE DB Source” task, take one “Flat File Destination” task and connect it with “OLE DB Source” task. Double click on “Flat File Destination” to set its property.

Click on “New” button to create “Flat File Connection”, I am going to give path of blank file “LinkedServer.sql” which I already had. You can keep blank SQL file in your destination.

33FileDestination

Click on “Ok” button from “Flat File Connection Manager Editor” and go to “Mappings” tab in “Flat File Destination Editor” to confirm whether our “LinkedServer” column from “OLE DB Source” is mapped with “Flat File” or not then click on “OK” button again and execute package. It should run successfully if credentials and path given are right.

FinalExecutionBackupLinkedServerScriptSSISPackage

We have many different server/instance and need script for all Linked Server from all server and hence I have had loop for all our database server and “Data Flow task” under the loop so that one package connect to each server, take a script of all mail profile and save it to location I have specified.

Note: If you have set password for your linked server, this script won’t decrypt password and give it to you. You have to manually change it in the script this SSIS package will create.

One of my team member and enthusiast SQL geek Mr. Nirav Gajjar (F) has helped me to develop this package and test it thoroughly.

I have written few more Linked Server related articles in past, have a look if you are interested.

  • Create Linked server with Excel 2007 worksheet in SQL Server 2005 (Click Here)
  • Linked Server in SQL Server 2005 from ACCESS 2007 (click Here)
  • Linked Server Error 7303- Cannot initialize the data source object of OLE DB provider (Click Here)
  • Linked Server is not configured for data access Error: 7411 in SQL Server 2005(Click Here)
  • Error Fix: Msg 7391, Level 16, State 2, Line 37 The operation could not be performed because OLE DB provider “SQLNCLI10″ for linked server “LinkServerName” was unable to begin a distributed transaction. (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.