Dynamic filter in WHERE Clause with CASE_WHEN_THEN in SQL Server

Dynamic filter in WHERE Clause with CASE_WHEN_THEN in SQL Server   I have very often seen that people are so used to with traditional methods and hence never think in terms of set based programming, they used to write code with evergreen traditional method which people used to use in sequential language like C or many be ASP etc.   […]

Continue reading »

SQL Server Login permission to read queue message count of Service Broker

SQL Server Login permission to read queue message count of Service Broker   Few weeks back, I have written one article “Find Service Broker Queue Count in SQL Server”. One of the blog reader has created the UDF I have given in that article and tried to fetch value of that UDF from his web application to find the service […]

Continue reading »

Count active Thread for Service Broker Queue in SQL Server

Count active Thread for Service Broker Queue in SQL Server Since we have big service broker setup, it is very important to know for us about thread running for target queue. Target queue is the queue which suppose to do the work of actual business logic and we used to give proper naming convention to each queue. Suppose we have […]

Continue reading »

Error Fix – Replication subscriber does not exist on the server anymore

Error Fix – Replication subscriber does not exist on the server anymore Recently one of my client has faced weird situation related to SQL Server Replication. Their main database goes down so they started running mirroring database as a primary database, removed subscriber and distributor. Once they have setup primary server back properly, they have restored mirrored database backup to […]

Continue reading »

Copy and archive SQL Server database backup

Copy and archive SQL Server database backup Though you are working as SQL Server DBA, you have to think, at least sometime, beyond relational. We can schedule different types of backup, ie: full, differential, transaction etc., from within SQL Server but what if you want to set archiving policy also? There are so many different ways to accomplish this task […]

Continue reading »

Error Fix Database Mail XPs error in SQL Server

Error Fix Database Mail XPs error in SQL Server Today I have received one email from one of the blog reader since he was facing an issue in his .NET application which is related to SMTP email in SQL Server. Here is the error he was facing: SQL Server blocked access to procedure ‘dbo.sp_send_dbmail’ of component ‘Database Mail XPs’ because […]

Continue reading »

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 […]

Continue reading »
1 2 3 52