Thursday, February 15, 2018

TSQL enhancements in recent SQL version


This new function helps to generate comma separated string from the contents of a column from several records in a single string value

It was previously possible through XML trick - 

select stuff((select ‘,’ + [name] as [text()] 
       from names for xml path()),1,1,)

which will be easily converted to below using string_agg

select string_agg(cast(Name as varchar(Max)), ‘,’) From User


This new function has been requested for a lot of developers for a long time.
Removing the empty spaces in a string always demanded the use of two functions, like this:
SELECT RTRIM(LTRIM( ‘     test    ‘)) AS Result;
This new function simplifies this task:
SELECT TRIM( ‘     test    ‘) AS Result;


Translate does the work of several replace functions, simplifying some queries. 

Using ‘Replace’ function the transformation would be like this:
select replace(replace(replace(‘[137.4, 72.3]’,‘[‘,‘(‘),‘,’,‘ ‘),‘]’,‘)’) as Point
Using the ‘Translate’ function the transformations becomes way simpler:
SELECT TRANSLATE(‘[137.4, 72.3]’ , ‘[,]’, ‘( )’) AS Point, 
       TRANSLATE(‘(137.4 72.3)’ , ‘( )’, ‘[,]’) AS Coordinates


The IIF function is another function Access supports. It accepts as first input a predicate, as second input an expression to return in case the predicate is true, and as third input an expression to return in case the input is false or unknown. Here’s an example for using the function:
SELECT IIF(1 = 2, 'a', 'b') AS iif_result; 





The CONCAT function concatenates the input values into a single result string.  The concatenation operator + yields a NULL on NULL input. The CONCAT function 
converts NULL inputs to empty strings before concatenation.


The FORMAT function allows you to format an input value to a character string based on a .NET format string. You can optionally indicate a culture when relevant. As an example, the following code formats the result of the GETDATE() function using the format string 'd' (meaning, short date pattern), in one case using US English culture, and in another Japanese:
SELECT FORMAT(GETDATE(), 'd', 'en-US') AS us, FORMAT(GETDATE(), 'd', 'ja-JP') AS jp

This function allows a lot of flexibility in formatting inputs. For example, the following code formats product IDs as 10-digit strings with leading zeros:
SELECT FORMAT(productid, '0000000000') AS strproductid, productname

FROM Production.Products;


One of the handiest T-SQL enhancements with SQL Server 2016 is the new DROP IF EXITS statement. It lets you check if an object exists before dropping it enabling you to skip wiring all those IF EXISTS blocks that we all write in out T-SQL scripts. You can see an example below.


STRING_SPLIT is another handy new function that makes it easy to parse strings based on a delimiter. The string can be a varchar, nvarchar, char, or nchar data type.
DECLARE @string varchar(100) = 'Richard, Mike, Mark'

SELECT value FROM string_split(@string, ',')


Likewise, the FOR JSON clause was add to enable you to convert tabular data to JSON. Unlike XML which has a native data type, the new JSON support is enabled by using the NVARCHAR data type. You can see an example of using the new FOR JSON clause in the following listing:
SELECT object_id, name FROM sys.tables FOR JSON PATH

JSON Functions

  • ISJSON -- The ISJSON function tests whether a string contains valid JSON.
  • JSON_VALUE -- The JSON_VALUE function extracts a scalar value from a JSON string.
  • JSON_QUERY -- The JSON_QUERY function extracts an object or an array from a JSON string.
  • JSON_MODIFY -- The JSON_MODIFY function updates the value of a property in a JSON string and returns the updated JSON string.

OPENJON Function

The OPENJSON table-value function (TVF) queries JSON text and can locate an array of JSON objects or iterate through the elements of array and generate a rowset from the results. You can see an example of using the OPENJSON function below.

SET @json = N'[ null, "string", 1, [true, false], ["a","b","c"], {"obj1":"obj2"} ]'; 


Sunday, October 8, 2017

Git Commands

git pull --rebase origin develop
git -rebase continue
git reset --hard;git clean -f -d;git pull;git checkout HEAD;git status;

Wednesday, January 20, 2016

Remove SSL from reporting service

To work around this
C:\Program Files\Microsoft SQL Server\MSRS10.\Reporting Services\ReportServer\rsreportserver.config

Change SecureConnectionLevel from 2 to 0

  (Change from current value to 0)
Here is what the values mean:
3 Most secure—Use SSL for absolutely everything.
2 Secure—Use SSL for rendering and methods that pass credentials but don't insist on it for all SOAP calls.
1 Basic Security—Accepts http but rejects any calls that might be involved in the passing of credentials.
0 Least Secure—Don't use SSL at all.

Tuesday, March 24, 2015

Add MVC into webforms project

Install below nuget pacakges

1) Microsoft ASP.NET MVC
2) WebGrease
3) Modify web.config or Add assembly of Abstractions, MVC and Routing 

4) Modify global.asax
Next you will need to add in the code for MVC triggers inside global.asax (create one if it does not exist)
Add the following lines after <%@ Application Language="C#" %>

<%@ Import Namespace="System.Web.Mvc" %> 
<%@ Import Namespace="System.Web.Routing" %>

Add the following after <script runat="server"

public void RegisterGlobalFilters(GlobalFilterCollection filters)
filters.Add(new HandleErrorAttribute());
public static void RegisterRoutes(RouteCollection routes)

new { controller = "Home", action = "Index", id = UrlParameter.Optional });}

add the following inside application_start


Wednesday, July 16, 2014

How to manually uninstall SQL if uninstalling from Add/Remove Programs fails

msiinv.exe is the most useful tool I came across.

One of the common uses of msiinv.exe is if someone is trying to install one of the recent beta builds of VS 2005 or .NET Framework 2.0 and the setup UI states that you are not allowed to install because a previous beta version of is on the machine and you must uninstall that first.  Sometimes after receiving this error, a user will look in Add/Remove Programs and the product that setup is complaining about is nowhere to be found, or there is an Add/Remove Programs entry for that product but trying to remove it claims that the product is not on the computer and asks if you would like to remove the entry from the Add/Remove Programs list.

these cases, you can use the following steps:

Download from the following location:

Extract the contents of to the folder c:\msiinv on your system
Click on the Start menu, choose Run, type cmd and click OK
Type this command:  c:\msiinv\msiinv.exe -p > c:\msiinv\msiinv_output.txt 

Note: This command must be run from a cmd prompt or it will not create a log file as expected.
These steps will create a text file named c:\msiinv\msiinv_output.txt with a list of each product that Windows Installer thinks is installed on the system.  Then you can open the text file in any text editor and search the list of products for the name of the product that setup told you to uninstall.  The output will look something like this (I am using an example from a machine that has .NET Framework 2.0 beta 2 installed):

Microsoft .NET Framework 2.0 Beta 2
 Product code: {7A1ADD0C-17F3-47B8-B033-A06E189C835D}
 Product state: (5) Installed.
 Package code: {856D48D2-6F94-466D-9732-534DB5854FB3}
 Version: 2.0.50215

Now we have the Windows Installer product code and we can use that to uninstall the product by running msiexec /x (make sure that you include the curly braces in this command line).  If the product is actually installed on your system you will see a progress screen and uninstall will complete.

Thursday, March 27, 2014

Rebuild Index

-- Ensure a USE statement has been executed first.



-- define index defrag rate for this run
DECLARE @fragrate float;
SET @fragrate = 30.0; -- set to do something if defrag rate HIGH

DECLARE @rbrirate float;
SET @rbrirate = 50.0; -- set to rebuild if defrag rate EXTREME

-- define table holding index information
DECLARE @indexinfo TABLE(objectid int, indexid int, partitionnum bigint, frag float)

DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
INSERT INTO @indexinfo
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > @fragrate AND index_id > 0
--and object_id = object_id('mlsdata');

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM @indexinfo;
OPEN partitions;

-- Loop through the partitions.
FETCH NEXT FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;

WHILE (@@fetch_status <> -1)
        IF (@@fetch_status <> -2)

SELECT @objectname = QUOTENAME(, @schemaname = QUOTENAME(
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;

SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE  object_id = @objectid AND index_id = @indexid;

SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < @rbrirate
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= @rbrirate
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

EXEC (@command);
PRINT N'Executed (Defrag ' + CAST(@frag AS varchar(12)) + '): ' + @command;
--PRINT @command;
-- Get the next record to process
        FETCH NEXT FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

Tuesday, September 10, 2013