It returns the total number of connections that includes both successful and failed made to SQL Server since it is started or restarted. Honestly, I have not used this variable anywhere and still could not really able to figure out a use case as well. Readers can share your thoughts if there are any.
@@MAX_CONNECTIONS
It returns the maximum number of simultaneous connections allowed to a SQL Server. Maximum user connections allowed by SQL Server by default is 32,767.
If you enjoyed this blog post, please share it with your friends!
Today, I have come across a requirement to generate the sql script for SQL Server Jobs (only few out of thousands). Most of us are familiar with an approach by right click on the sql job and choose ‘Script Job as’ to script out individually, but this is such a tedious job if you have couple of sql jobs to be scripted.
This post explains another simpler way using “Object Explorer Details“. It allows you to select multiple jobs to be scripted as below.
Choose Object Explorer Details Menu from View main menu in SSMS.
Select “SQL Server Agent” -> “Jobs” in Object explorer which lists all the SQL Agent Jobs in Details.
Select the jobs (ALL or FEW) you want to script and then right click to select the scripting option.
The above steps will create all the selected jobs in a single query window as per the selection.
If you enjoyed this blog post, please feel free to share it with your friends!
CONCAT_WS is a new function introduced from SQL Server 2017. CONCAT_WS denotes concatenate the strings with a separator. The syntax of CONCAT_WS is as below:
CONCAT_WS(separator,string1,string2.....stringN)
Prior to SQL Server 2017, we will have to use “+” operator to concatenate and carefully place the separator, that really adds lots of frustration to any developer (At least, I hate this in my initial coding days). CONCAT_WS is really a good winner in such use cases. In one of my use case, we wanted to send data from one table to external system, which works on BizTalk, as a pipe separated text file format. Earlier, we had to use carefully do lots of checks at bot SQL Server and BizTalk sides to avoid data issues like NULL handling etc. This function reduces a lots of work in such cases.
Few Points to be considered:
This function skips NULL value. That means we will have to explicitly handle NULL value columns if the column needs to be considered in the output.
The maximum number of parameters allowed is 254.
This function implicitly converts all arguments to string type before concatenation.
This function requires minimum a separator parameter and at least other two string parameters.
This post is to explain a quick and brilliant way of removing attributes from (X)HTML tags in SQL Server. The solution was given by one of my good friend | guru Yitzhak Khabinsky (in), would like to share it for the benefit of community.
Few interesting ways:
–Delete the style attributes
Declare @tbl TABLE (ID INT PRIMARY KEY Identity(1,1), xhtmlData xml);
Insert into @tbl (xhtmlData) Values
('<p><span style="font-size: 18px;"><strong>Hello World</strong></span></p>'),
('<p> </p>'),
('<h2 style="font-size: 24px; text-align: center;">123456</h2>'),
('<p> </p>'),
('<p class="next-steps-intro" attr= "" style="margin-bottom: 0;"><span style="font-size: 18px;">
<strong>You are at home:</strong></span></p>')
Select * From @tbl
update @tbl set xhtmlData.modify('delete //@*');
Select * From @tbl
–Delete the style attributes ONLY for span Tag
Select * From @tbl
update @tbl SET xhtmlData.modify('delete //span/@style');
Select * From @tbl
–Delete multiple attributes for a Tag
Select * From @tbl
update @tbl SET xhtmlData.modify('delete /p/@*[local-name()=("class","attr")]');
Select * From @tbl
I had spend almost an hour with Yitzhak and shared many stuffs around xml/SQL Server etc. In fact, when I contacted , it was a mid night for him and he immediately came for a skype discussion. There are some really interesting things he shared and I requested Yitzhak to share all of them in a form of a blog which would really be a great asset to SQL Server/any tech communities.
With that to conclude this post, we would like to enhance xml support further in SQL Server future releases and to request you all to vote for the below feedback for the same.
REPLICATE() function repeats a string to the number of times its specified in the second parameter.
SELECT REPLICATE('SQL | ',2)
REVERSE in SQL Server
REVERSE() function reverses a string passed.
SELECT REVERSE ('SQL Server')
Reverse is a very powerful SQL String function that uses many places. I used this function in one of my earlier post on checking palindrome in SQL Server as below.
SQLZealots 