Archive for the ‘SQL Tips’ Category

SQL Tips – Issues with BCP when you have two SQL Server instances

June 9, 2018 1 comment

I observed one thing here with BCP (Bulk Copy Program), when you have 2 versions of SQL Server installed on you PC or Server. I had SQL Server 2014 & 2016 installed on one of my DEV server.

So if you are executing Query from SQL 2016 instance, it was inserting records in SQL 2014 instance:

exec master..xp_cmdshell ‘BCP AdventureWorks2014.Person.Address2 IN d:\PersonAddressByQuery.txt -T -c’


But even if you use BCP 2016 version, it was still inserting in SQL 2014 instance:

exec master..xp_cmdshell ‘C:\”Program Files”\”Microsoft SQL Server”\”Client SDK”\ODBC\130\Tools\Binn\BCP.exe AdventureWorks2014.Person.Address2 IN d:\PersonAddressByQuery.txt -T -c’


On inquiring a bit I came to know that by default the BCP command points to the Default instance of SQL Server, no matter from which instance you are executing the Query. MSDN BoL

So, you have to provide parameter to connect to server. If not specified, it would go to default instance

So your BCP command should go with the “-S” option, like this:

exec master..xp_cmdshell ‘C:\”Program Files”\”Microsoft SQL Server”\”Client SDK”\ODBC\130\Tools\Binn\BCP.exe AdventureWorks2014.Person.Address2 IN d:\PersonAddressByQuery.txt -T -c -S DBServerName/InstanceName

Categories: SQL Tips Tags: ,

SQL DBA – Stop multiple running SQL Jobs at once

June 14, 2017 2 comments

Just like my previous post on Enable/Disable multiple SQL Jobs [link], here I will show how can we stop multiple jobs running by SQL Agent at once.

–> Although you can very well Stop a SQL Job in SSMS by simple right-clicking on it. But if you have multiple SQL Jobs running, to stop each of them will be a cumbersome and time taking task.

I will show how this can be done by a T-SQL query:

USE msdb;

-- Stop Multiple running SQL Jobs at once:

SELECT @dynSql += N' msdb.dbo.sp_stop_job @job_name = ''' + + N'''' 
			+ CHAR(10) + CHAR(13)
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobactivity AS ja 
ON ja.job_id = j.job_id
WHERE ja.start_execution_date IS NOT NULL
AND ja.stop_execution_date IS NULL

PRINT @dynSql;

Simply Copy-Paste the the above Dynamically generated SQL Script and Execute it, it will Stop all Running SQL Jobs at once.

Categories: DBA Stuff, SQL Tips Tags: ,

Get Row Count of Insert/Update/Delete records in MERGE Statement – MSDN TSQL forum

March 29, 2017 Leave a comment

–> Question:

How can I get the numbers of records affected in the Merge statement, INSERT,UPDATE,DELETE separately and store it in a variable so I can get it in the application side?

Thanks !

–> My Answer:

You need to use OUTPUT clause with MERGE statement, try this:

DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));

MERGE tblTarget AS Target
USING (SELECT Col1,Col2 FROM tblSource) AS Source
ON (Target.Col1 = Source.Col1)

	UPDATE SET target.Col2 = source.Col2 -- Need to get affected rows here

	INSERT (Col1,Col2) VALUES (Col1,Col2); -- Need to get affected rows here

OUTPUT $action INTO @SummaryOfChanges; --< check here

-- Get the count of Insert/Update/Delete'd records from the below table variable
SELECT Change, COUNT(*) AS CountPerChange
FROM @SummaryOfChanges
GROUP BY Change;

For more info on MERGE and OUTPUT clause statement check this link:

Categories: SQL Tips Tags: ,

Custom Query to check Frequency, Interval and Time of multiple Job’s in SQL Server

March 22, 2016 1 comment

In my [previous post] I discussed about the system SPs that we can use to get details of a SQL Job, like its Name, ID, Description, Schedule, etc. But what if you need to check these details of various SQL Jobs?

Here in this post we will see how we check the Job Schedule of various SQL Jobs, like:

1. Enable/Disable
2. Frequency (Once, Daily, Weekly, Monthly, etc.)
3. Interval (Specific time, Seconds, Minutes, Hours, etc.)
4. Start Date and Time
5. Description

… by using some system views, like:

– sysJobs
– sysJobsShedules
– sysSchedules

	 'Job ID' = j.job_id
	,'Job Name' =
	,'Job Enabled' = case j.enabled
			when 1 then 'Yes'
			else 'No'
	,'Frequency' = case s.freq_type 
		when 1 then 'One time only'
		when 4 then 'Daily'
		when 8 then 'Weekly'
		when 16 then 'Monthly'
		when 32 then 'Monthly, relative to freq_interval'
		when 64 then 'Runs when the SQL Server Agent service starts'
		when 128 then 'Runs when the computer is idle'
	,'Interval' = case s.freq_subday_type
		when 1 then 'At specified time'
		when 2 then CAST(freq_subday_interval AS VARCHAR(3)) + ' Seconds'
		when 4 then CAST(freq_subday_interval AS VARCHAR(3)) + ' Minutes'
		when 8 then CAST(freq_subday_interval AS VARCHAR(3)) + ' Hours'
	,'Start_Date' = substring(convert(varchar(15),active_start_date),1,4) 
		+ '/' + substring(convert(varchar(15),active_start_date),5,2) + '/' 
		+ substring(convert(varchar(15),active_start_date),7,2)
		+ ' ' + 
		case len(active_start_time)
			when 1 then cast('00:00:0' + right(active_start_time,2) as char(8))
			when 2 then cast('00:00:' + right(active_start_time,2) as char(8))
			when 3 then cast('00:0' 
					+ left(right(active_start_time,3),1)  
					+':' + right(active_start_time,2) as char (8))
			when 4 then cast('00:' 
					+ left(right(active_start_time,4),2)  
					+':' + right(active_start_time,2) as char (8))
			when 5 then cast('0' 
					+ left(right(active_start_time,5),1) 
					+':' + Left(right(active_start_time,4),2)  
					+':' + right(active_start_time,2) as char (8))
			when 6 then cast(Left(right(active_start_time,6),2) 
					+':' + Left(right(active_start_time,4),2)  
					+':' + right(active_start_time,2) as char (8))
	,'Schedule Enabled' = case s.enabled
            when 1 then 'Yes'
            else 'No'
    ,'Schedule Desc' =
from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobschedules js
on j.job_id = js.job_id
inner join msdb.dbo.sysschedules s
on s.schedule_id = js.schedule_id
order by

SQL Jobs 03

I’ve created the above query by collating references form forums and MSDN BoL, you can tweak and modify this query as well as per your requirements.

Using system SPs to get details of SQL Jobs, Steps, Commands, Schedule, Last run details, etc

March 21, 2016 3 comments

Sometimes it get difficult or time-taking to get each and every SQL Job details, its schedule and other information. Also checking all these information in SSMS is time taking specially when your machine is running a client, and the server is located in some remote place. On the other side if you get some handy system Queries you can fire them in SSMS and get the desired results immediately, without browsing too much on heavy GUI tools.

Here are some of the System Stored Procedure queries that can be used to check the SQL Job details, like:

1. Job Details:
– Job ID/Name/Description
– Category
– Date Created/Modified
– Last/Next run Date/Time
– Current execution status

2.Job Steps
– Step ID/Names
– SQL Commands in each step
– On Success/Fail action
– Last run Duration/Retries/Date/Time

3. Job Schedule:
– Schedule ID/Name/Description
– Enable/Disable Status
– Job Frequency/Interval
– Job Start Date/Time
– Next run Date/Time

–> sp_help_job: system SP gives you all these information in 4 different result sets as shown in the image below. You can pass Job-Name or Job-ID as parameter to the system SP.

exec msdb.dbo.sp_help_job @job_name = 'Acquire_Source_Data'
-- OR --
exec msdb.dbo.sp_help_job @job_id = 'ad8252d8-35d8-4c1f-b603-db0dfebd5e27'

SQL Jobs 01

–> sp_help_jobstep & sp_help_jobschedule: these 2 SPs also gives similar information provided by above SP (sp_help-job), but with single result-set. So you can execute them independently and use them in your logic.

exec msdb.dbo.sp_help_jobstep @job_id = 'ad8252d8-35d8-4c1f-b603-db0dfebd5e27'

exec msdb.dbo.sp_help_jobschedule @job_id = 'ad8252d8-35d8-4c1f-b603-db0dfebd5e27'

SQL Jobs 02