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.

[sourcecode language=”sql”]

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
[/sourcecode]

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.

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

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.

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

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.

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

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.

Find undistributed replication command in SQL Server

Find undistributed replication command in SQL Server

I have written one article to find replication latency sometime back. If I find latency of 10 seconds or more for longer time then I always try to find the bottleneck and fix it because in the environment I am working, won’t afford big latency.

In finding the bottleneck, my first step would be to find how many commands are pending to apply in replicated database. To find undistributed command, you can use replication monitor which comes with SQL Server itself or use sp_replmonitorsubscriptionpendingcmds” system stored procedure which you can find in distributor database of replication.

 

[sourcecode language=”sql”]
–system stored procedure to run in distribution database
execute sp_replmonitorsubscriptionpendingcmds
–replication publisher server
@publisher =’PubServer’,
–replication publisher database
@publisher_db = ‘AdventureWorks2012′,
–replication publication name
@publication =’AdventureWorks2012Pub’,
–replication subscriber server
@subscriber =’SubServer’,
–replication subscriber database
@subscriber_db =’AdventureWorks2012ReplDB’,
–choose type of subscription you have
@subscription_type =’1′ –0 for push and 1 for pull
GO
[/sourcecode]

 

Here is the screen capture of output.

ReplicationPendingCommand

This screen capture shows that total number of 64 commands are waiting to apply from “AdventureWorks2012” database to “AdventureWorks2012ReplDB” and that would take 0 (<1) seconds.

I have written few more replication related articles, have a look if you are interested.

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

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

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Sharing IT tips at “Quick Learn

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

Get list of SQL Server Jobs from multiple instance via SSIS package

Get list of SQL Server Jobs from multiple instance via SSIS package

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 SSIS package to list SQL Server Job of all instance I used to manage and insert that list in one database so that anytime I have list ready if any one ask me about anything related to job.

If I have list of jobs with along with its schedule, I can query every SQL Server instance to check whether every job of the instance ran or not. If any job is not running, I can send automated email with list of “Missing Jobs”.

I will cover complete sequence in two article. This article will show you how to prepare a list of all SQL Server Job from multiple instance.

We might change schedule of certain jobs, we might add/remove some of the jobs so it is necessary to have perfect updated list of jobs so the SSIS package I am going to run in this article, would be scheduled to run everyday and gather the latest/updated list of jobs from all instance and insert it in one database of one instance.

Let us start creating foundation for this SSIS package.

I used to have one database called “DBAdb” in one of the instance of SQL Server and I used to collect DBA related data in “DBAdb” database only so that none of the production database becomes big due to DBA related activity. Apart from “DBAdb” database, we need two tables inside “DBAdb” database.

1.) SQLServerInstance: this table will have details of all SQL Server instance and this will be the base table for our package as our package read detail of SQL Server Instance from this table and collect the JOB details from that particular instance.

2.) JobScheduleDetails: This table will have detail of each job along with its schedule which are collected from the each instance we have listed in “SQLServerInstance” table.

I will have only one entry in “SQLServerInstance” table as I am preparing this article from my laptop but you can have as many entries as you want. Package will be created dynamically which will iterate for each server we list.

Here is the TSQL to create database and tables we have listed above.

[sourcecode language=”sql”]
CREATE DATABASE DBAdb
GO

USE [DBAdb]
GO

CREATE TABLE [dbo].[SQLServerInstances](
[Sid] [INT] IDENTITY(1,1) NOT NULL,
[ServerName] [VARCHAR](100) NULL,
[ConnectionString] [VARCHAR](1000) NULL,
[IsActive] [bit] NULL,
[GetJobSchedules] [bit] NULL
) ON [PRIMARY]

INSERT INTO SQLServerInstances
–you have to give your servername alongwith its connection string
SELECT ‘USER12\SQL2K12DEV’,’Data Source=USER12\SQL2K12DEV;Initial Catalog=msdb;Provider=SQLNCLI10.1;INTegrated Security=SSPI;Auto Translate=False;’,1,1
GO

–Create table in centralize "DBADb" database in one instance
–which will collect information of job from all servers.
USE [DBAdb]
GO

CREATE TABLE [dbo].[JobSchedulesDetails](
[Seq] [INT] IDENTITY(1,1) NOT NULL,
[ServerName] [VARCHAR](100) NULL,
[JobId] [uniqueidentifier] NULL,
[JobName] [VARCHAR](500) NULL,
[IsEnabled] [VARCHAR](10) NULL,
[Occurrence] [VARCHAR](200) NULL,
[JobSchedule] [VARCHAR](4000) NULL,
[FrequencyType] [VARCHAR](100) NULL,
[Frequency] [INT] NULL,
[StartTime] [VARCHAR](20) NULL,
[EndTime] [VARCHAR](20) NULL,
[StartDateTime] AS (CONVERT([DATETIME],(CONVERT([CHAR](8),getdate(),(112))+’ ‘)+[StartTime],0)),
[EndDateTime] AS (CONVERT([DATETIME],(CONVERT([CHAR](8),getdate(),(112))+’ ‘)+[EndTime],0)),
[CategoryId] [INT] NULL
) ON [PRIMARY]
GO
[/sourcecode]

Now we are ready with information we want so let us start creating SSIS package.

1.) Create three variables for the package as given in following screen capture.

1DeclareVariable

2.) Get one “Execute SQL Task” in “Control Flow” of your package.

2ExecuteTaskForServerList

I have used following query in “Execute SQL Task”.

[sourcecode language=”sql”]
SELECT
ServerName,
ConnectionString
FROM
dbo.SQLServerInstances
WHERE
IsActive = 1
AND
GetJobSchedules =1
[/sourcecode]

3.) Above query will return list of SQL Server Instance where we need to collect job details from so we are going to capture result set in one of the variable we have created. Go to “Result Set” tab in Execute SQL Task as given below and following the screen capture.

3ResultSetinVariable

4.)  Take one “Foreach Loop Container” and join it with previously created “Execute SQL Task”. set properties of Foreach task as given in screen capture.

4ForEachLoopFromVariable

5.) need to set other variables for data in Foreach task. see following screen capture.

5ForEachLoopVariableMapping

6.) Since we need latest and updated detail of JOB in master table, we are going to delete old job detail from the table before we insert new details via “Execute SQL Task”.

6DeleteOldRecordOfJobScheduleForServer

Here is the query I have used in above Execute SQL Task.

[sourcecode language=”sql”]
DELETE FROM DBAdb.dbo.JobSchedulesDetails
WHERE ServerName  = ?
[/sourcecode]

7.) Now take one “Data Flow” task and connect it with “Execute SQL Task” we created to delete data for old job detail before we capture fresh data.

7DataFlowTask

8.) As soon as you double click on newly created “Data Flow” task, you will be forwarded to “Data Flow” tab and you will have to have three task there. 1.) OLE DB Source 2.) Data Conversion 3.) OLE DB Destination.

8DataFlowInsight

OLEDB Source task here plays very crucial role. Following is the query I have used to read job details.

[sourcecode language=”sql”]
SELECT
cast(SERVERPROPERTY (‘servername’)  as nvarchar(200)) as ServerName,
a.job_id as JobId,
a.name as JobName,
a.Category_id as CategoryId,
case when a.Enabled = 1 then ‘Enable’ else ‘Disable’ end as JobStatus,
CASE [freq_type]
WHEN 1 THEN ‘One Time’
WHEN 4 THEN ‘Daily’
WHEN 8 THEN ‘Weekly’
WHEN 16 THEN ‘Monthly’
WHEN 32 THEN ‘Monthly – Relative to Frequency Interval’
WHEN 64 THEN ‘Start automatically when SQL Server Agent starts’
WHEN 128 THEN ‘Start whenever the CPUs become idle’
END [Occurrence]
, (CASE [freq_type]
WHEN 4 THEN ‘Occurs every ‘ + CAST([freq_interval] AS VARCHAR(3)) + ‘ day(s)’
WHEN 8 THEN ‘Occurs every ‘ + CAST([freq_recurrence_factor] AS VARCHAR(3))
+ ‘ week(s) on ‘
+ CASE WHEN [freq_interval] & 1 = 1 THEN ‘Sunday’ ELSE ” END
+ CASE WHEN [freq_interval] & 2 = 2 THEN ‘, Monday’ ELSE ” END
+ CASE WHEN [freq_interval] & 4 = 4 THEN ‘, Tuesday’ ELSE ” END
+ CASE WHEN [freq_interval] & 8 = 8 THEN ‘, Wednesday’ ELSE ” END
+ CASE WHEN [freq_interval] & 16 = 16 THEN ‘, Thursday’ ELSE ” END
+ CASE WHEN [freq_interval] & 32 = 32 THEN ‘, Friday’ ELSE ” END
+ CASE WHEN [freq_interval] & 64 = 64 THEN ‘, Saturday’ ELSE ” END
WHEN 16 THEN ‘Occurs on Day ‘ + CAST([freq_interval] AS VARCHAR(3))
+ ‘ of every ‘
+ CAST([freq_recurrence_factor] AS VARCHAR(3)) + ‘ month(s)’
WHEN 32 THEN ‘Occurs on ‘
+ CASE [freq_relative_interval]
WHEN 1 THEN ‘First’
WHEN 2 THEN ‘Second’
WHEN 4 THEN ‘Third’
WHEN 8 THEN ‘Fourth’
WHEN 16 THEN ‘Last’
END
+ ‘ ‘
+ CASE [freq_interval]
WHEN 1 THEN ‘Sunday’
WHEN 2 THEN ‘Monday’
WHEN 3 THEN ‘Tuesday’
WHEN 4 THEN ‘Wednesday’
WHEN 5 THEN ‘Thursday’
WHEN 6 THEN ‘Friday’
WHEN 7 THEN ‘Saturday’
WHEN 8 THEN ‘Day’
WHEN 9 THEN ‘Weekday’
WHEN 10 THEN ‘Weekend day’
END
+ ‘ of every ‘ + CAST([freq_recurrence_factor] AS VARCHAR(3))
+ ‘ month(s)’
END ) +
(CASE [freq_subday_type]
WHEN 1 THEN ‘ once at ‘
+ STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
WHEN 2 THEN ‘ every ‘
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ‘ Second(s) between ‘
+ STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
+ ‘ & ‘
+ STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
WHEN 4 THEN ‘ every ‘
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ‘ Minute(s) between ‘
+ STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
+ ‘ & ‘
+ STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
WHEN 8 THEN ‘ every ‘
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ‘ Hour(s) between ‘
+ STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
+ ‘ & ‘
+ STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
END ) as JobSchedule,

(CASE [freq_subday_type]
WHEN 1 THEN ‘Once’
WHEN 2 THEN ‘Second(s)’
WHEN 4 THEN ‘Minute(s)’
WHEN 8 THEN ‘Hour(s)’
END ) as ‘Every’,

(CASE [freq_subday_type]
WHEN 1 THEN NULL
WHEN 2 THEN CAST([freq_subday_interval] AS VARCHAR(3))
WHEN 4 THEN CAST([freq_subday_interval] AS VARCHAR(3))
WHEN 8 THEN CAST([freq_subday_interval] AS VARCHAR(3))
END ) as ‘Between’,

(CASE [freq_subday_type]
WHEN 1 THEN  STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
WHEN 2 THEN STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)

WHEN 4 THEN STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)

WHEN 8 THEN STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)

END ) as StartTime ,

(CASE [freq_subday_type]
WHEN 1 THEN STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
WHEN 2 THEN  STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
WHEN 4 THEN STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
WHEN 8 THEN STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
END ) as EndTime
FROM
[msdb].[dbo].[sysjobs] a
INNER JOIN [msdb].[dbo].[sysjobschedules] b
ON (b.job_id = a.job_id)
INNER JOIN [msdb].[dbo].[sysschedules] c
ON (c.schedule_id = b.schedule_id)
ORDER BY a.name
[/sourcecode]

9.) We may have one or multiple instance where we need to collect this information from so we have to connect our OLE DB Source Task dynamically with different server instance. First use the query I gave above and create regular SQL Server connection. Once you are done, go to property of that connection task to change the name and give it dynamic connection string for different server. We have dynamic connection string coming in Foreach Loop and we have mapped that connection string in “ConnectionString” variable we have created in very first steps above.

9DynamicConnection

10.) Connect one “Data Conversion” task with OLE DB Source and see the property I have set in following screen capture.

10DataConversion

Now, we have read job details from server. Now, this is time to insert that detail in “DBAdb” database so that we can use it for our purpose.

10.) set the property of “OLE DB Destination” after connecting it to “Data Conversion Task”.

11OLEDBDestination

11.) We have to map column from source and destination in “OLE DB Destination” task.

12Mapping

Now you are ready to run. Execute the package and see its effect. If the servers you have inserted in “SQLServerInstance” table is having SQL Server Job, you will find job details in your “[JobSchedulesDetails]” table.

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

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

All Program menu in task bar in Windows 8

All Program menu in task bar in Windows 8

I am using Microsoft operating system from the version “Windows 98” in the year of 2000 and by now, I am so used to with “Start” menu with “Programs” / “All Programs” option to see all the installed software. This is changed from the version “Windows 8”, though Window 8 have “Start” menu screen but it looks like “tiles” and I may need some time to habituate with this so meanwhile I though how can I get “All Programs” in Task bar and found the solution which I am going to show today.

Right click on “Task Bar” to open popup menu, go to Toolbars | New toolbar….

1NewToolbar

As soon as you will click on “New toolbar…”, you will get “Choose a folder” dialog box, type down following command in textbox of “Folder”.

[sourcecode language=”sql”]
%ProgramData%\Microsoft\Windows\Start Menu\Programs
[/sourcecode]

2SelectFolder

After copy/past the command given above, click on “Select Folder” button from dialog box and follow the default process. Once it is done, you can see the “All Program” menu in the task bar as shown in following screen capture.

3AllProgramMenu

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.

Shutdown button on start menu in Windows 8

Shutdown button on start menu in Windows 8

Alt+F4 is a best way to shutdown your Windows 8 but many people so used to with start menu shutdown button.I have found the trick to do that. You can see that I have my “Shutdown” button in following start menu.

1Windows8StartMenuShutDownButton

Let us see how did I get it.

First move to desktop area. Right click on Desktop so that you will get popup menu. click on New|Shortcut

2CreateShortcut

Now you will get dialog box where you can create shortcut from. You have to give shutdown command there “C:\Windows\System32\shudown.exe /s /t 0”

3ShortcutDialog

Give the name to your shortcut, I gave “Shutdown”.

4NameofShortcut

Now short cut button is created on desktop and you can even click on it to shutdown your windows8.  This button doesn’t give us feel like “Shutdown” :). let us change the icon image of this button but right click on that button and click on “Properties”

5ShortcutProperty

Once you will get properties, you can click on “Change Icon” button from following dialog box and select the image you like for “Shutdown” button.

6ChangeIcon

Once you will change the image, you have shutdown button on your desktop but you may like to have it on “Start” menu of windows8 so right click on button, click on “Pin to Start” option and you are done. You can get the same Shutdown icon on the start menu as given in first screen capture of this article.

7PintToStart

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.

Get list of failed SQL Server Agent job in SQL Server

failedJobGet list of failed SQL Server Agent job in SQL Server

When you are working with mission critical database environment, you have to closely keep a watch on every database operation being performed. SQL Server jobs are one of the very important critical things to watch at.

We might have jobs for some:

  • heavy aggregation or calculations
  • ETL processes
  • different kind of alerts and logs
  • any much more……..

We may have job running once in a day or many be scheduled to run for few times a day or week. No matter how and when job is running, it is prime duty of DBA to keep watch on each and every job. If any job get failed due to any reason then fix it and run it again.

If job can’t run for some or other reason, it might adversely affect business operation.

Now, question comes, how to keep watch on jobs? You may have many jobs running in SQL Server instance or you may have multiple sql server instance which is executing multiple jobs. I have created on small user defined function and one stored procedure which I execute in SQL Server database and it will give me result of failed job.

If I have recurring job and it failed once but if it runs successfully next time, SP will not give you that job name. Whenever you execute the stored procedure give below, it will check only last occurrence of job, if it is failed, SP will return it to draw your attention towards it.

[sourcecode language=”sql”]
CREATE FUNCTION [Conv_DateTime](@id int, @it int)
RETURNS DATETIME
AS
BEGIN
DECLARE @vt char(6), @d char(8), @dt datetime
SELECT @d = convert(varchar, @id)

IF len(@it) = 5
SET @vt = ‘0’ + convert(char,@it)
ELSE if len(@it) = 4
SET @vt = ’00’ + convert(char,@it)
ELSE if len(@it) = 3
SET @vt = ‘000’ + convert(char,@it)
ELSE if len(@it) = 2
SET @vt = ‘0000’ + convert(char,@it)
ELSE if len(@it) = 1
SET @vt = ‘00000’ + convert(char,@it)
ELSE if len(@it) = 6
SET @vt = convert(char,@it)
SELECT @dt = left (@d,4) + ‘-‘ + substring(@d,5,2) + ‘-‘ + right(@d,2) + ‘ ‘ + left(@vt,2) + ‘:’ + substring(@vt, 3,2) + ‘:’ + right(@vt, 2)
RETURN (@dt)
END
GO
[/sourcecode]

Once you are ready with function which will return exact time of when the job failed, we will call that function in following stored procedure.

[sourcecode language=”sql”]
CREATE PROCEDURE Getfailedjobsdetail
AS
BEGIN
SET NOCOUNT ON
SELECT CAST(Serverproperty(‘servername’) as varchar(100)) AS ServerName,
res.jobname AS JobName,
CONVERT(varchar(5),
DATEADD(ss,(CASE len(RunDuration)
WHEN 1 THEN RunDuration
WHEN 2 THEN RunDuration
WHEN 3 THEN (cast(Left(right(RunDuration,3),1) as int)*60) + (right(RunDuration,2))
WHEN 4 THEN (cast(Left(right(RunDuration,4),2) AS int)*60) + (right(RunDuration,2))
WHEN 5 THEN (cast(Left(right(RunDuration,5),1) AS int)*3600) + (cast(Left(right(RunDuration,4),2) AS int)*60) + right(RunDuration,2)
WHEN 6 THEN (cast(Left(right(RunDuration,6),2) AS int)*3600) + (cast(Left(right(RunDuration,4),2) AS int)*60) + right(RunDuration,2)
END ),
conv_datetime(run_date,run_time)),108)as FailedTime
FROM (SELECT Row_number()
OVER(
partition BY sj.name
ORDER BY run_time DESC) AS rnk,
sj.name AS JobName,
run_date,
run_time,
run_duration AS RunDuration,
run_status
FROM msdb.dbo.sysjobhistory sjh WITH (nolock)
INNER JOIN msdb.dbo.sysjobs sj WITH (nolock)
ON sjh.job_id = sj.job_id
WHERE sjh.step_id <> 0
AND run_date = CONVERT(VARCHAR(8), Getdate(), 112)) res
WHERE res.run_status = 0
AND res.rnk = 1
END
GO
[/sourcecode]

After having SP and Function both, you have to execute the stored procedure like this:

[sourcecode language=”sql”]
EXEC Getfailedjobsdetail
GO
[/sourcecode]

If any job is failed and that job is not ran again successfully, above SP will give you list for the same. I used to call above given SP from my monitoring web page, developed in asp.net, I have created. This web page execute SP in all the servers I have maintain and list Job name with Server name if it fails. As soon as support person see anything failed on that web page, they used to come to me to find the issue and fix it.

This is making my life very easy. Istn’t it!!!!!

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

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

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.

Error Fix: Unable to show XML The following error happened There is an unclosed literal string

Error Fix: Unable to show XML The following error happened There is an unclosed literal string

Recently I was working with huge data of XML in SQL Server. One of the row having XML string with approx 98000 rows inside. If I try to open that XML file by clicking on XML value return by my SELECT query, I used to get following error:

Unable to show XML. The following error happened:
There is an unclosed literal string. Line 1, position 2097154.

One solution is to increase the number of characters retrieved from the server for XML data. To change this setting, on the Tools menu, click Options.

Here is the screen capture of error:

XMLError

This error comes because of limitation set by default of 2MB for XML data. My data for that XML field was having approx 18MB. To open this big file, I have to change the SSMS configuration from Tools | Options.

XMLErrorFix

You can see that “Results to Grid” has option to retrieve maximum characters. For “Non XML Data:”, it is 65535 and for XML data, it is 2MB. My data was around 18MB so obviously I can’t get it open in SSMS. I have changed (temporary) it to “Unlimited” for a while, clicked on “OK” button. Close my current query window and execute query again. I got my XML field in SSMS and when I have clicked on that field, I got my XML file open.
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.

Quick list of DBCC (Database Console Commands) in SQL Server

Quick list of DBCC (Database Console Commands) in SQL Server

Database consistency check is a part of one of the important duty of DBA. I used to restore my each and every full database backup in one of the development server and execute DBCC command in that newly restored database. Once DBCC stops execution, I suppose to get an email with the status of this DBCC execution.

I have done automated process for all these steps, I will give more details about how to practically implement this process sometime later but today I wanted to show the list of some DBCC commands which are my favorite to check consistency of database.

Some of the DBCC commands given in following list may affect performance of SQL Server until it gets completed. If your database is large then some of the commands may take few hours for complete execution so be caution before running those commands in live environment, especially in business hours. As I have explained above, I used to restore my database backup somewhere and used to run DBCC command, not directly on production database, which is recommended method.

Anyway, let us have a look at different DBCC commands for different purpose.

Note: don’t run all commands together. Each DBCC command had different purpose and it is recommended to run each one of them separately and see behavior.

[sourcecode language=”sql”]
USE AdventureWorks2012
GO

–CHECK THE DATABASE ALLOCATION and PAGE STRUCTURE integrity
–If running CHECKDB, no need to run CHECKALLOC separately
DBCC CHECKDB;
GO

— Check the AdventureWorks2012 database without
–nonclustered indexes and extended logical checks.
DBCC CHECKDB (AdventureWorks2012, NOINDEX) WITH EXTENDED_LOGICAL_CHECKS;
GO

–Extended Logical Checks and Physical Only cannot be used together
DBCC CHECKDB (AdventureWorks2012, NOINDEX) WITH PHYSICAL_ONLY;
GO

–Checks the consistency of disk space allocation structures for a specified database.
–In our case, it is AdventureWorks2012
DBCC CHECKALLOC;
GO

–Just an estimation and no elaborated messages
DBCC CHECKALLOC WITH ESTIMATEONLY,NO_INFOMSGS

–Following command check only PRIMARY filegroup
DBCC CHECKFILEGROUP;
GO

–check specific filegroup and integrity of the physicalstructure of the page
DBCC CHECKFILEGROUP (1, NOINDEX) WITH PHYSICAL_ONLY;
GO

–Check data integirty for tables with different options for same table.
–execute each of the following three command separately and see the difference
DBCC CHECKTABLE (‘sales.SalesOrderDetail’) WITH ALL_ERRORMSGS
GO

DBCC CHECKTABLE (‘sales.SalesOrderDetail’) WITH ESTIMATEONLY
GO

DBCC CHECKTABLE (‘sales.SalesOrderDetail’, 1) WITH PHYSICAL_ONLY
GO

–Check for constraints violation on specified table or indexed view
DBCC CHECKCONSTRAINTS (‘sales.SalesOrderDetail’)
GO
[/sourcecode]

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.