Archive for the ‘T SQL’ Category

SQL DBA – Stop multiple running SQL Jobs at once

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: ,

SQL Error – Cannot connect to xyz_sql_instance, due to remote firewall issue

After creating a new Azure Windows Server VM with SQL Server installed, I was not able to connect it from my PC via SSMS. Everytime I tried to connect I was getting following error:

Cannot connect to xyz_sql_instance.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provier, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
The network path was not found


–> I checked online and found following command to enable the port, but it ran into error:

C:\windows\system32>netsh firewall set portopening TCP 1433 “SQLServer”

“… netsh firewall” is deprecated; use “netsh advfirewall firewall” instead.


–> So, lets execute the new netsh advfirewall firewall command and see what options it has:

C:\windows\system32>netsh advfirewall firewall /?

The following commands are available:

Commands in this context:
? – Displays a list of commands.
add – Adds a new inbound or outbound firewall rule.
delete – Deletes all matching firewall rules.
dump – Displays a configuration script.
help – Displays a list of commands.
set – Sets new values for properties of a existing rule.
show – Displays a specified firewall rule.

To view help for a command, type the command, followed by a space, and then type ?.


–> Now with above options we are clear that we have to add a new inbound firewall rule, so we will check how can we use the add option:

C:\windows\system32>netsh advfirewall firewall add /?

The following commands are available:

Commands in this context:
add rule – Adds a new inbound or outbound firewall rule.


–> Let’s apply the add rule option and see more options:

C:\windows\system32>netsh advfirewall firewall add rule /?

Usage: add rule name=
[enable=yes|no (default=yes)]
[localport=0-65535|[,…]|RPC|RPC-EPMap|IPHTTPS|any (default=any)]
[remoteport=0-65535|[,…]|any (default=any)]
tcp|udp|any (default=any)]
[edge=yes|deferapp|deferuser|no (default=no)]


– Add a new inbound or outbound rule to the firewall policy.
– Rule name should be unique and cannot be “all”.
– If a remote computer or user group is specified, security must be
authenticate, authenc, authdynenc, or authnoencap.
– Setting security to authdynenc allows systems to dynamically
negotiate the use of encryption for traffic that matches
a given Windows Firewall rule. Encryption is negotiated based on
existing connection security rule properties. This option
enables the ability of a machine to accept the first TCP
or UDP packet of an inbound IPsec connection as long as
it is secured, but not encrypted, using IPsec.
Once the first packet is processed, the server will
re-negotiate the connection and upgrade it so that
all subsequent communications are fully encrypted.
– If action=bypass, the remote computer group must be specified when dir=in.
– If service=any, the rule applies only to services.
– ICMP type or code can be “any”.
– Edge can only be specified for inbound rules.
– AuthEnc and authnoencap cannot be used together.
– Authdynenc is valid only when dir=in.
– When authnoencap is set, the security=authenticate option becomes an
optional parameter.


Add an inbound rule with no encapsulation security for browser.exe:
netsh advfirewall firewall add rule name=”allow browser”
dir=in program=”c:\programfiles\browser\browser.exe”
security=authnoencap action=allow

Add an outbound rule for port 80:
netsh advfirewall firewall add rule name=”allow80″
protocol=TCP dir=out localport=80 action=block

Add an inbound rule requiring security and encryption
for TCP port 80 traffic:
netsh advfirewall firewall add rule
name=”Require Encryption for Inbound TCP/80″
protocol=TCP dir=in localport=80 security=authdynenc

Add an inbound rule for browser.exe and require security
netsh advfirewall firewall add rule name=”allow browser”
dir=in program=”c:\program files\browser\browser.exe”
security=authenticate action=allow

Add an authenticated firewall bypass rule for group
acmedomain\scanners identified by a SDDL string:
netsh advfirewall firewall add rule name=”allow scanners”
dir=in rmtcomputergrp= action=bypass

Add an outbound allow rule for local ports 5000-5010 for udp-
Add rule name=”Allow port range” dir=out protocol=udp localport=5000-5010 action=allow


–> OK, so this gives us more options and also samples at the end to construct our command. So we will us the above highlighted command to add port for our SQL Server, as shown below:

C:\windows\system32>netsh advfirewall firewall add rule name=”SQL Server Engine” protocol=TCP dir=in localport=1433 action=allow

C:\windows\system32>netsh advfirewall firewall add rule name=”SQL Server Browser” protocol=UDP dir=in localport=1434 action=allow

Now, I am able to connect remotely without any issues.

Categories: SQL Errors

SSMS error – The Visual Studio component cache is out of date. Please restart Visual Studio.

Yesterday while opening SSMS I got an error message popup which mentions:

The Visual Studio component cache is out of date. Please restart Visual Studio.


On checking in internet I found a similar issue logged in MS Connect, and the workaround was to cleanup the application folder in the Windows-Temp folder, which is located at following folder location:



This issue vanished after deleting the SSMS folder. This could be due to the Visual Studio component cache can’t be written or can’t be wiped while in use.

Categories: SQL Errors

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: ,

New built-in function CONCAT_WS() in SQL Server 2017

January 20, 2017 1 comment

In my previous posts I discussed new Functions introduced in SQL Server vNext (or 2018), like STRING_AGG(), TRIM(), TRANSLATE().

Here in this post I’ll discuss about one more new function i.e. CONCAT_WS(), here “_WS” means “With Separator”.

This is very similar to the existing CONCAT() function introduced back in SQL Server 2012, which concatenates a variable number of arguments or string values.

The difference is the new function CONCAT_WS() accepts a delimiter specified as the 1st argument, and thus there is no need to repeat the delimiter after very String value like in CONCAT() function.

Also the new CONCAT_WS() function takes care of NULL values and do not repeat the delimiter, which you can see in 2nd example below.


CONCAT_WS ( separator, argument1, argument1 [, argumentN]… )


–> Example #1:

With CONCAT_WS() we will use the delimiter just once and it concatenates the names separated by ‘-‘, and do not repeat the hyphen where the middle name is NULL.

USE [AdventureWorks2014]

	CONCAT_WS(' - ', FirstName, MiddleName, LastName) as FullName, 
	FirstName, MiddleName, LastName
FROM [Person].[Person]


–> Example #2:

With CONCAT() the delimiter needs to be used after every argument, it concatenates the names separated by ‘-‘, do repeats the hyphen where the middle name is NULL.

	CONCAT(FirstName, ' - ', MiddleName, ' - ', LastName) as FullName, 
	FirstName, MiddleName, LastName
FROM [Person].[Person]