sqlserver2014
 
 
 
 
 
 
 
Now that SQL Server 2014 is out, it’s time to get busy and learn! Below you will find a series of resources that will help you get started. First get an evaluation copy (180-day trial) here Download SQL Server 2014

Learn & Experience
Get free training and guided experience on learning about SQL Server.

Community & Social Media

pass
Want to Connect, Learn, and Share? Join the SQL Server Community by becoming a PASS member here PASS – Professional Association SQL Server and find a Local PASS Chapter where you can share your passion and knowledge about Microsoft SQL Server and Business Intelligence with other enthusiasts.
 
 
 
 
 
A minimal list of “SQL Server Community” Twitter accounts worth following:

  • @SQLServer
  • @SQLTechCenter
  • @MicrosoftBI
  • @MSAzureCat
  • @SQLPass
  • @pass24hop
  • @SQLServerMag
  • Presentation slides from my session “Building Network Graphs with Tableau” at the Ottawa Tableau User Group Meetup – Thanks to everyone who attended.

    To build the network graph demo, here is the Excel Workbook with the sample data for download:
    Network Graph Sample Data (Microsoft Excel) – AsterixGraphData_Tableau.xls

    To implement network graphs in Tableau, Data preparation is key!

    tableau_ng_00

    Let’s quickly go through the steps and create a network graph with this very small sample dataset;

    To start, drag the “LineY” attribute to the Rows shelf and the “LineX” attribute on the Columns shelf. Set the Mark type to “Circle” this will render a basic scatterplot.

    tableau_ng_01

    Add the “CircleY” attribute to the Rows shelf as a Double Axis (right click on the Axis and click “Dual Axis”), and then synchronize the two Y axes (right click on the Axis, and click on “Synchronize Axis”)

    tableau_ng_02

    Next we need to format the Tableau Mark cards to show “Multiple Mark Types“. Go to the “CircleY” Mark card and set Mark type as “Pie“. Drag the “NodeName” attribute on Label and “ID” on Color on the Mark card – each pie slice represents a connection (transaction)

    tableau_ng_03

    Then cycle to the “LineY” Mark. Drag the “ID” and “Relationship” attributes on Detail on the Mark card. Set the Mark type to “Line”. Tableau will connect the dots… and Voilà! A simple Network Graph. You can resize the LineY series to make lines thinner.

    tableau_ng_04

    To further enhance your visualization you could add a summary table, provide filters, and modify the tooltips.

    tableau_ng_05

    Have fun!

    speaking
    Ottawa Tableau User Group – Tuesday, April 8, 2014 6:00 PM to 8:00 PM

  • Building Network Graphs with Tableau
  • Register to attend: http://www.meetup.com/OttawaTableauUserGroup/events/170869192/
    Join our community -> http://community.tableausoftware.com/groups/ottawa

    Groupe d’usagers SharePoint Québec – Section Gatineau – Wednesday, April 30, 2014 6:00 PM to 8:00 PM

  • Power BI
  • Register to attend: http://www.meetup.com/guspquebec/events/175352542/

    Ottawa SQL Server 2014 Day – Friday, May 9, 2014 8:00 AM to 4:30 PM

  • SQL Server 2014 Faster Insights from Any Data (Power BI) 1:30 PM
  • On the move with Big Data – SSIS, Pig and Sqoop 3:00 PM
  • Register to attend: http://www.meetup.com/ottawaitcommunity/events/173109682/

    This is event hosted by www.DevTeach.com in collaboration with Microsoft, Ottawa IT Community and the Ottawa PASS Chapter
    For more details -> http://www.devteach.com/community/

    Un léger décalage! mais mieux vaut tard que jamais.

    Il s’agit de sélectionner sur la carte ou dans la liste des détails pour voir les résultats par district.
    Fait intéressant à noter que plus d’une personne sur deux a voté dans le district de Limbour (9) soit 53.80%, ainsi le district Hull-Wright (8) a obtenu le plus faible taux de participation avec 32.70%. Le district du Plateau (4) remporte la palme pour le plus haut taux de bulletins rejetés avec 2.15% et le district de Buckingham (18) le plus bas 0.93%. Saviez-vous que le seul district dont Maxime Pedneaud-Jobin a échappé est celui de Hull-Wright (8)?

    I have been using different tools and techniques to output valid JSON from SQL Server Databases, specially from imported Open Data datasets in CSV format and data that I have scraped from different web sites. Most of the time it’s somewhat a painful and tedious process, switching back and forth from these tools, copying and pasting… to finally come up with something decent!

    So I decided to write something fast, lightweight, and useful that I could use directly from SQL Server Management Studio using T-SQL code and have outputted validated JSON.

    In a JSON file a value can be a string in double quotes, or a number, or true or false or null, or an object or an array, and these structures can be nested. (the script below does not handle nested structures)

    json

    For more detailed information on the JSON specification -> json.org

    An example; let’s start by creating a simple table named ‘Game’ and insert some data (looks familiar!).

    create table dbo.Game
    (
    	GameID			int,
    	GameDate		date,
    	GameTime		time,
    	GameDateTime	datetime,
    	Country			varchar(50),
    	City			varchar(50), 
    	Stadium			varchar(50), 
    	[Group]			varchar(20), 
    	Team01			varchar(50), 
    	Team02			varchar(50),
    	Result			varchar(5),
    	IsActive		bit
    )
    
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (1, '2014-06-12 17:00:00.000', '2014-06-12', '17:00:00.000', N'Brazil', N'Sao Paulo', N'Arena de Sao Paulo', N'Group A', N'Brazil', N'Croatia', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (2, '2014-06-13 13:00:00.000', '2014-06-13', '13:00:00.000', N'Brazil', N'Natal', N'Estadio das Dunas', N'Group A', N'Mexico', N'Cameroon', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (3, '2014-06-13 16:00:00.000', '2014-06-13', '16:00:00.000', N'Brazil', N'Salvador', N'Arena Fonte Nova', N'Group B', N'Spain', N'Netherlands', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (4, '2014-06-13 18:00:00.000', '2014-06-13', '18:00:00.000', N'Brazil', N'Cuiaba', N'Arena Pantanal', N'Group B', N'Chile', N'Australia', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (5, '2014-06-14 13:00:00.000', '2014-06-14', '13:00:00.000', N'Brazil', N'Belo Horizonte', N'Estadio Mineirao', N'Group C', N'Colombia', N'Greece', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (6, '2014-06-14 22:00:00.000', '2014-06-14', '22:00:00.000', N'Brazil', N'Recife', N'Arena Pernambuco', N'Group C', N'Côte d''Ivoire', N'Japan', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (7, '2014-06-14 16:00:00.000', '2014-06-14', '16:00:00.000', N'Brazil', N'Fortaleza', N'Estadio Castelao', N'Group D', N'Uruguay', N'Costa Rica', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (8, '2014-06-14 18:00:00.000', '2014-06-14', '18:00:00.000', N'Brazil', N'Manaus', N'Arena Amazonia', N'Group D', N'England', N'Italy', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (9, '2014-06-15 13:00:00.000', '2014-06-15', '13:00:00.000', N'Brazil', N'Brasilia', N'Estadio Nacional', N'Group E', N'Switzerland', N'Ecuador', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (10, '2014-06-15 16:00:00.000', '2014-06-15', '16:00:00.000', N'Brazil', N'Porto Alegre', N'Estadio Beira-Rio', N'Group E', N'France', N'Honduras', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (11, '2014-06-15 19:00:00.000', '2014-06-15', '19:00:00.000', N'Brazil', N'Rio de Janeiro', N'Estadio do Maracana', N'Group F', N'Argentina', N'Bosnia-Herzegovina', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (12, '2014-06-16 16:00:00.000', '2014-06-16', '16:00:00.000', N'Brazil', N'Curitiba', N'Arena da Baixada', N'Group F', N'Iran', N'Nigeria', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (13, '2014-06-16 13:00:00.000', '2014-06-16', '13:00:00.000', N'Brazil', N'Salvador', N'Arena Fonte Nova', N'Group G', N'Germany', N'Portugal', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (14, '2014-06-16 19:00:00.000', '2014-06-16', '19:00:00.000', N'Brazil', N'Natal', N'Estadio das Dunas', N'Group G', N'Ghana', N'USA', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (15, '2014-06-17 13:00:00.000', '2014-06-17', '13:00:00.000', N'Brazil', N'Belo Horizonte', N'Estadio Mineirao', N'Group H', N'Belgium', N'Algeria', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (16, '2014-06-17 18:00:00.000', '2014-06-17', '18:00:00.000', N'Brazil', N'Cuiaba', N'Arena Pantanal', N'Group H', N'Russia', N'Korea Republic', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (17, '2014-06-17 16:00:00.000', '2014-06-17', '16:00:00.000', N'Brazil', N'Fortaleza', N'Estadio Castelao', N'Group A', N'Brazil', N'Mexico', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (18, '2014-06-18 18:00:00.000', '2014-06-18', '18:00:00.000', N'Brazil', N'Manaus', N'Arena Amazonia', N'Group A', N'Cameroon', N'Croatia', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (19, '2014-06-18 16:00:00.000', '2014-06-18', '16:00:00.000', N'Brazil', N'Rio de Janeiro', N'Estadio do Maracana', N'Group B', N'Spain', N'Chile', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (20, '2014-06-18 13:00:00.000', '2014-06-18', '13:00:00.000', N'Brazil', N'Porto Alegre', N'Estadio Beira-Rio', N'Group B', N'Australia', N'Netherlands', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (21, '2014-06-19 13:00:00.000', '2014-06-19', '13:00:00.000', N'Brazil', N'Brasilia', N'Estadio Nacional', N'Group C', N'Colombia', N'Côte d''Ivoire', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (22, '2014-06-19 19:00:00.000', '2014-06-19', '19:00:00.000', N'Brazil', N'Natal', N'Estadio das Dunas', N'Group C', N'Japan', N'Greece', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (23, '2014-06-19 16:00:00.000', '2014-06-19', '16:00:00.000', N'Brazil', N'Sao Paulo', N'Arena de Sao Paulo', N'Group D', N'Uruguay', N'England', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (24, '2014-06-20 13:00:00.000', '2014-06-20', '13:00:00.000', N'Brazil', N'Recife', N'Arena Pernambuco', N'Group D', N'Italy', N'Costa Rica', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (25, '2014-06-20 16:00:00.000', '2014-06-20', '16:00:00.000', N'Brazil', N'Salvador', N'Arena Fonte Nova', N'Group E', N'Switzerland', N'France', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (26, '2014-06-20 19:00:00.000', '2014-06-20', '19:00:00.000', N'Brazil', N'Curitiba', N'Arena da Baixada', N'Group E', N'Honduras', N'Ecuador', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (27, '2014-06-21 13:00:00.000', '2014-06-21', '13:00:00.000', N'Brazil', N'Belo Horizonte', N'Estadio Mineirao', N'Group F', N'Argentina', N'Iran', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (28, '2014-06-21 18:00:00.000', '2014-06-21', '18:00:00.000', N'Brazil', N'Cuiaba', N'Arena Pantanal', N'Group F', N'Nigeria', N'Bosnia-Herzegovina', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (29, '2014-06-21 16:00:00.000', '2014-06-21', '16:00:00.000', N'Brazil', N'Fortaleza', N'Estadio Castelao', N'Group G', N'Germany', N'Ghana', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (30, '2014-06-22 18:00:00.000', '2014-06-22', '18:00:00.000', N'Brazil', N'Manaus', N'Arena Amazonia', N'Group G', N'USA', N'Portugal', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (31, '2014-06-22 13:00:00.000', '2014-06-22', '13:00:00.000', N'Brazil', N'Rio de Janeiro', N'Estadio do Maracana', N'Group H', N'Belgium', N'Russia', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (32, '2014-06-22 16:00:00.000', '2014-06-22', '16:00:00.000', N'Brazil', N'Porto Alegre', N'Estadio Beira-Rio', N'Group H', N'Korea Republic', N'Algeria', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (33, '2014-06-23 17:00:00.000', '2014-06-23', '17:00:00.000', N'Brazil', N'Brasilia', N'Estadio Nacional', N'Group A', N'Cameroon', N'Brazil', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (34, '2014-06-23 17:00:00.000', '2014-06-23', '17:00:00.000', N'Brazil', N'Recife', N'Arena Pernambuco', N'Group A', N'Croatia', N'Mexico', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (35, '2014-06-23 13:00:00.000', '2014-06-23', '13:00:00.000', N'Brazil', N'Curitiba', N'Arena da Baixada', N'Group B', N'Australia', N'Spain', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (36, '2014-06-23 13:00:00.000', '2014-06-23', '13:00:00.000', N'Brazil', N'Sao Paulo', N'Arena de Sao Paulo', N'Group B', N'Netherlands', N'Chile', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (37, '2014-06-24 16:00:00.000', '2014-06-24', '16:00:00.000', N'Brazil', N'Cuiaba', N'Arena Pantanal', N'Group C', N'Japan', N'Colombia', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (38, '2014-06-24 17:00:00.000', '2014-06-24', '17:00:00.000', N'Brazil', N'Fortaleza', N'Estadio Castelao', N'Group C', N'Greece', N'Côte d''Ivoire', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (39, '2014-06-24 13:00:00.000', '2014-06-24', '13:00:00.000', N'Brazil', N'Natal', N'Estadio das Dunas', N'Group D', N'Italy', N'Uruguay', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (40, '2014-06-24 13:00:00.000', '2014-06-24', '13:00:00.000', N'Brazil', N'Belo Horizonte', N'Estadio Mineirao', N'Group D', N'Costa Rica', N'England', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (41, '2014-06-25 16:00:00.000', '2014-06-25', '16:00:00.000', N'Brazil', N'Manaus', N'Arena Amazonia', N'Group E', N'Honduras', N'Switzerland', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (42, '2014-06-25 17:00:00.000', '2014-06-25', '17:00:00.000', N'Brazil', N'Rio de Janeiro', N'Estadio do Maracana', N'Group E', N'Ecuador', N'France', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (43, '2014-06-25 13:00:00.000', '2014-06-25', '13:00:00.000', N'Brazil', N'Porto Alegre', N'Estadio Beira-Rio', N'Group F', N'Nigeria', N'Argentina', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (44, '2014-06-25 13:00:00.000', '2014-06-25', '13:00:00.000', N'Brazil', N'Salvador', N'Arena Fonte Nova', N'Group F', N'Bosnia-Herzegovina', N'Iran', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (45, '2014-06-26 13:00:00.000', '2014-06-26', '13:00:00.000', N'Brazil', N'Recife', N'Arena Pernambuco', N'Group G', N'USA', N'Germany', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (46, '2014-06-26 13:00:00.000', '2014-06-26', '13:00:00.000', N'Brazil', N'Brasilia', N'Estadio Nacional', N'Group G', N'Portugal', N'Ghana', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (47, '2014-06-26 17:00:00.000', '2014-06-26', '17:00:00.000', N'Brazil', N'Sao Paulo', N'Arena de Sao Paulo', N'Group H', N'Korea Republic', N'Belgium', null, 1)
    insert dbo.Game (GameID, GameDateTime, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, Result, IsActive) 
    	values (48, '2014-06-26 17:00:00.000', '2014-06-26', '17:00:00.000', N'Brazil', N'Curitiba', N'Arena da Baixada', N'Group H', N'Algeria', N'Russia', null, 1)
    

    Now from this populated table and using the following script below SQLToJSON.sql let’s try to output some valid JSON.
    Two important variables; @TableViewName and @Columns.
    On line 17 we assign the value ‘Game’ to the @TableNameView and on line 20 a comma separated list of attributes ‘GameID, GameDateTime, Country, City, Stadium, [Group], Team01, Team02, Result’ to the @Columns variable.

    set nocount on
    declare @TableViewName	nvarchar(128),
            @Columns        nvarchar(max),
            @Columns_       nvarchar(max),
            @ColumnName     nvarchar(max),
            @SQLColumns     nvarchar(max),
            @SQLQuery       nvarchar(max),
            @SQLString      nvarchar(max),
            @LastRecord     int,
            @StartPos       int,
            @Length         int;
     
    declare @TableColumn table(TableColumn nvarchar(max));
    declare	@TableJSON table(ID int identity(1,1), JSON nvarchar(max));
     
    -- Input variable for Table or View
    set @TableViewName = 'Game';
     
    -- Input variable for columns, comma separated...
    set @Columns = 'GameID, GameDateTime, Country, City, Stadium, [Group], Team01, Team02, Result';
     
    set @Columns_ = @Columns;
     
    while len(@Columns) > 0
    begin
    	if right(rtrim(@Columns), 1) <> ','
    	begin
    		set @Columns = @Columns  + ','
        end
        set @StartPos = charindex(',', @Columns)
        if (@StartPos) < 0
        begin
    		set @StartPos = 0
            set @Length = (len(@Columns) - @StartPos - 1)
            if (@Length) < 0
            begin
    			set @Length = 0
            end
        end
        if (@StartPos) > 0
        begin          
    		set @ColumnName = ltrim(substring(@Columns, 1, @StartPos - 1))
            
    		-- Build string Column logic (core)
            set @ColumnName = '''"' + @ColumnName + '":''' + ' + case 
    							when isdate(isnull(' + @ColumnName + ', null)) = 1 then + ''"'' + coalesce(convert(nvarchar(30),' + @ColumnName + ', 121), ''null'') + ''"'' 
    							when isnumeric(isnull(' + @ColumnName + ', 0)) = 1 then coalesce(cast(' + @ColumnName + ' as nvarchar(max)), ''null'') 
    							when '+  @ColumnName + ' = ''true'' then ''true'' when ' + @ColumnName + ' = ''false'' then ''false'' 
    							else + ''"'' + coalesce(cast(' + @ColumnName + ' as nvarchar(max)), ''null'') + ''"'' end  + '','''
            set @Columns = substring(@Columns, @StartPos + 1, len(@Columns) - @StartPos)
        end
        else
        begin
    		set @ColumnName = @Columns
            set @Columns = ''
        end
        insert @TableColumn (TableColumn) values(@ColumnName)
    end;
     
    -- Take care of NULL columns
    select @SQLColumns = coalesce(@SQLColumns + ' + ','') + TableColumn from @TableColumn;
     
    --Remove the last five trailing characters (+ ',')
    set @SQLColumns = left(@SQLColumns, len(@SQLColumns) - 5);
     
    -- Prepare CTE statement
    set @SQLQuery = 'select' + ' ' + @Columns_ + ' ' + 'from ' +  @TableViewName;
    set @SQLString = 'with SQLtoJSON_CTE (' + @Columns_ + ')' + ' ' + 
    					'as (' + @SQLQuery + ')' + 'select ''{'' +' + @SQLColumns + '+ ''},''' + ' ' + 'from SQLtoJSON_CTE';
     
    -- Insert returned SQLtoJSON_CTE records
    insert into @TableJSON exec sp_executesql @SQLString;
     
    -- Get the ID of the last record
    select @LastRecord = max(ID) from @TableJSON;
     
    -- Remove the trailing comma (,) at the end of the last record
    update	@TableJSON
    set		JSON = left(JSON, len(JSON) - 1)
    where	ID = @LastRecord;
     
    -- Return Valide JSON
    select valid.JSON as 'ValidJSON' from (select '[' as 'JSON' union all select JSON from @TableJSON union all select ']' as 'JSON') valid;
    set nocount off
    

    Execute the above query and the output generates the following valid JSON.

    [
        {"GameID":1,"GameDateTime":"2014-06-12 17:00:00.000","Country":"Brazil","City":"Sao Paulo","Stadium":"Arena de Sao Paulo","[Group]":"Group A","Team01":"Brazil","Team02":"Croatia","Result":null},
        {"GameID":2,"GameDateTime":"2014-06-13 13:00:00.000","Country":"Brazil","City":"Natal","Stadium":"Estadio das Dunas","[Group]":"Group A","Team01":"Mexico","Team02":"Cameroon","Result":null},
        {"GameID":3,"GameDateTime":"2014-06-13 16:00:00.000","Country":"Brazil","City":"Salvador","Stadium":"Arena Fonte Nova","[Group]":"Group B","Team01":"Spain","Team02":"Netherlands","Result":null},
        {"GameID":4,"GameDateTime":"2014-06-13 18:00:00.000","Country":"Brazil","City":"Cuiaba","Stadium":"Arena Pantanal","[Group]":"Group B","Team01":"Chile","Team02":"Australia","Result":null},
        {"GameID":5,"GameDateTime":"2014-06-14 13:00:00.000","Country":"Brazil","City":"Belo Horizonte","Stadium":"Estadio Mineirao","[Group]":"Group C","Team01":"Colombia","Team02":"Greece","Result":null},
        {"GameID":6,"GameDateTime":"2014-06-14 22:00:00.000","Country":"Brazil","City":"Recife","Stadium":"Arena Pernambuco","[Group]":"Group C","Team01":"Côte d'Ivoire","Team02":"Japan","Result":null},
        {"GameID":7,"GameDateTime":"2014-06-14 16:00:00.000","Country":"Brazil","City":"Fortaleza","Stadium":"Estadio Castelao","[Group]":"Group D","Team01":"Uruguay","Team02":"Costa Rica","Result":null},
        {"GameID":8,"GameDateTime":"2014-06-14 18:00:00.000","Country":"Brazil","City":"Manaus","Stadium":"Arena Amazonia","[Group]":"Group D","Team01":"England","Team02":"Italy","Result":null},
        {"GameID":9,"GameDateTime":"2014-06-15 13:00:00.000","Country":"Brazil","City":"Brasilia","Stadium":"Estadio Nacional","[Group]":"Group E","Team01":"Switzerland","Team02":"Ecuador","Result":null},
        {"GameID":10,"GameDateTime":"2014-06-15 16:00:00.000","Country":"Brazil","City":"Porto Alegre","Stadium":"Estadio Beira-Rio","[Group]":"Group E","Team01":"France","Team02":"Honduras","Result":null},
        {"GameID":11,"GameDateTime":"2014-06-15 19:00:00.000","Country":"Brazil","City":"Rio de Janeiro","Stadium":"Estadio do Maracana","[Group]":"Group F","Team01":"Argentina","Team02":"Bosnia-Herzegovina","Result":null},
        {"GameID":12,"GameDateTime":"2014-06-16 16:00:00.000","Country":"Brazil","City":"Curitiba","Stadium":"Arena da Baixada","[Group]":"Group F","Team01":"Iran","Team02":"Nigeria","Result":null},
        {"GameID":13,"GameDateTime":"2014-06-16 13:00:00.000","Country":"Brazil","City":"Salvador","Stadium":"Arena Fonte Nova","[Group]":"Group G","Team01":"Germany","Team02":"Portugal","Result":null},
        {"GameID":14,"GameDateTime":"2014-06-16 19:00:00.000","Country":"Brazil","City":"Natal","Stadium":"Estadio das Dunas","[Group]":"Group G","Team01":"Ghana","Team02":"USA","Result":null},
        {"GameID":15,"GameDateTime":"2014-06-17 13:00:00.000","Country":"Brazil","City":"Belo Horizonte","Stadium":"Estadio Mineirao","[Group]":"Group H","Team01":"Belgium","Team02":"Algeria","Result":null},
        {"GameID":16,"GameDateTime":"2014-06-17 18:00:00.000","Country":"Brazil","City":"Cuiaba","Stadium":"Arena Pantanal","[Group]":"Group H","Team01":"Russia","Team02":"Korea Republic","Result":null},
        {"GameID":17,"GameDateTime":"2014-06-17 16:00:00.000","Country":"Brazil","City":"Fortaleza","Stadium":"Estadio Castelao","[Group]":"Group A","Team01":"Brazil","Team02":"Mexico","Result":null},
        {"GameID":18,"GameDateTime":"2014-06-18 18:00:00.000","Country":"Brazil","City":"Manaus","Stadium":"Arena Amazonia","[Group]":"Group A","Team01":"Cameroon","Team02":"Croatia","Result":null},
        {"GameID":19,"GameDateTime":"2014-06-18 16:00:00.000","Country":"Brazil","City":"Rio de Janeiro","Stadium":"Estadio do Maracana","[Group]":"Group B","Team01":"Spain","Team02":"Chile","Result":null},
        {"GameID":20,"GameDateTime":"2014-06-18 13:00:00.000","Country":"Brazil","City":"Porto Alegre","Stadium":"Estadio Beira-Rio","[Group]":"Group B","Team01":"Australia","Team02":"Netherlands","Result":null},
        {"GameID":21,"GameDateTime":"2014-06-19 13:00:00.000","Country":"Brazil","City":"Brasilia","Stadium":"Estadio Nacional","[Group]":"Group C","Team01":"Colombia","Team02":"Côte d'Ivoire","Result":null},
        {"GameID":22,"GameDateTime":"2014-06-19 19:00:00.000","Country":"Brazil","City":"Natal","Stadium":"Estadio das Dunas","[Group]":"Group C","Team01":"Japan","Team02":"Greece","Result":null},
        {"GameID":23,"GameDateTime":"2014-06-19 16:00:00.000","Country":"Brazil","City":"Sao Paulo","Stadium":"Arena de Sao Paulo","[Group]":"Group D","Team01":"Uruguay","Team02":"England","Result":null},
        {"GameID":24,"GameDateTime":"2014-06-20 13:00:00.000","Country":"Brazil","City":"Recife","Stadium":"Arena Pernambuco","[Group]":"Group D","Team01":"Italy","Team02":"Costa Rica","Result":null},
        {"GameID":25,"GameDateTime":"2014-06-20 16:00:00.000","Country":"Brazil","City":"Salvador","Stadium":"Arena Fonte Nova","[Group]":"Group E","Team01":"Switzerland","Team02":"France","Result":null},
        {"GameID":26,"GameDateTime":"2014-06-20 19:00:00.000","Country":"Brazil","City":"Curitiba","Stadium":"Arena da Baixada","[Group]":"Group E","Team01":"Honduras","Team02":"Ecuador","Result":null},
        {"GameID":27,"GameDateTime":"2014-06-21 13:00:00.000","Country":"Brazil","City":"Belo Horizonte","Stadium":"Estadio Mineirao","[Group]":"Group F","Team01":"Argentina","Team02":"Iran","Result":null},
        {"GameID":28,"GameDateTime":"2014-06-21 18:00:00.000","Country":"Brazil","City":"Cuiaba","Stadium":"Arena Pantanal","[Group]":"Group F","Team01":"Nigeria","Team02":"Bosnia-Herzegovina","Result":null},
        {"GameID":29,"GameDateTime":"2014-06-21 16:00:00.000","Country":"Brazil","City":"Fortaleza","Stadium":"Estadio Castelao","[Group]":"Group G","Team01":"Germany","Team02":"Ghana","Result":null},
        {"GameID":30,"GameDateTime":"2014-06-22 18:00:00.000","Country":"Brazil","City":"Manaus","Stadium":"Arena Amazonia","[Group]":"Group G","Team01":"USA","Team02":"Portugal","Result":null},
        {"GameID":31,"GameDateTime":"2014-06-22 13:00:00.000","Country":"Brazil","City":"Rio de Janeiro","Stadium":"Estadio do Maracana","[Group]":"Group H","Team01":"Belgium","Team02":"Russia","Result":null},
        {"GameID":32,"GameDateTime":"2014-06-22 16:00:00.000","Country":"Brazil","City":"Porto Alegre","Stadium":"Estadio Beira-Rio","[Group]":"Group H","Team01":"Korea Republic","Team02":"Algeria","Result":null},
        {"GameID":33,"GameDateTime":"2014-06-23 17:00:00.000","Country":"Brazil","City":"Brasilia","Stadium":"Estadio Nacional","[Group]":"Group A","Team01":"Cameroon","Team02":"Brazil","Result":null},
        {"GameID":34,"GameDateTime":"2014-06-23 17:00:00.000","Country":"Brazil","City":"Recife","Stadium":"Arena Pernambuco","[Group]":"Group A","Team01":"Croatia","Team02":"Mexico","Result":null},
        {"GameID":35,"GameDateTime":"2014-06-23 13:00:00.000","Country":"Brazil","City":"Curitiba","Stadium":"Arena da Baixada","[Group]":"Group B","Team01":"Australia","Team02":"Spain","Result":null},
        {"GameID":36,"GameDateTime":"2014-06-23 13:00:00.000","Country":"Brazil","City":"Sao Paulo","Stadium":"Arena de Sao Paulo","[Group]":"Group B","Team01":"Netherlands","Team02":"Chile","Result":null},
        {"GameID":37,"GameDateTime":"2014-06-24 16:00:00.000","Country":"Brazil","City":"Cuiaba","Stadium":"Arena Pantanal","[Group]":"Group C","Team01":"Japan","Team02":"Colombia","Result":null},
        {"GameID":38,"GameDateTime":"2014-06-24 17:00:00.000","Country":"Brazil","City":"Fortaleza","Stadium":"Estadio Castelao","[Group]":"Group C","Team01":"Greece","Team02":"Côte d'Ivoire","Result":null},
        {"GameID":39,"GameDateTime":"2014-06-24 13:00:00.000","Country":"Brazil","City":"Natal","Stadium":"Estadio das Dunas","[Group]":"Group D","Team01":"Italy","Team02":"Uruguay","Result":null},
        {"GameID":40,"GameDateTime":"2014-06-24 13:00:00.000","Country":"Brazil","City":"Belo Horizonte","Stadium":"Estadio Mineirao","[Group]":"Group D","Team01":"Costa Rica","Team02":"England","Result":null},
        {"GameID":41,"GameDateTime":"2014-06-25 16:00:00.000","Country":"Brazil","City":"Manaus","Stadium":"Arena Amazonia","[Group]":"Group E","Team01":"Honduras","Team02":"Switzerland","Result":null},
        {"GameID":42,"GameDateTime":"2014-06-25 17:00:00.000","Country":"Brazil","City":"Rio de Janeiro","Stadium":"Estadio do Maracana","[Group]":"Group E","Team01":"Ecuador","Team02":"France","Result":null},
        {"GameID":43,"GameDateTime":"2014-06-25 13:00:00.000","Country":"Brazil","City":"Porto Alegre","Stadium":"Estadio Beira-Rio","[Group]":"Group F","Team01":"Nigeria","Team02":"Argentina","Result":null},
        {"GameID":44,"GameDateTime":"2014-06-25 13:00:00.000","Country":"Brazil","City":"Salvador","Stadium":"Arena Fonte Nova","[Group]":"Group F","Team01":"Bosnia-Herzegovina","Team02":"Iran","Result":null},
        {"GameID":45,"GameDateTime":"2014-06-26 13:00:00.000","Country":"Brazil","City":"Recife","Stadium":"Arena Pernambuco","[Group]":"Group G","Team01":"USA","Team02":"Germany","Result":null},
        {"GameID":46,"GameDateTime":"2014-06-26 13:00:00.000","Country":"Brazil","City":"Brasilia","Stadium":"Estadio Nacional","[Group]":"Group G","Team01":"Portugal","Team02":"Ghana","Result":null},
        {"GameID":47,"GameDateTime":"2014-06-26 17:00:00.000","Country":"Brazil","City":"Sao Paulo","Stadium":"Arena de Sao Paulo","[Group]":"Group H","Team01":"Korea Republic","Team02":"Belgium","Result":null},
        {"GameID":48,"GameDateTime":"2014-06-26 17:00:00.000","Country":"Brazil","City":"Curitiba","Stadium":"Arena da Baixada","[Group]":"Group H","Team01":"Algeria","Team02":"Russia","Result":null}
    ]
    

    Observations:

  • Numerical values (int, decimal, float, money…) and in Exact or Approximate data type categories are not double quoted.
  • Character strings (char, varchar, nchar, nvarchar) are double quoted [script does not support text data type]
  • Datetime data type values are double quoted – more on that in following section
  • Nulls are not double quoted. It is not a value, per se: it is a value outside the domain of the variable and indicates missing or unknown data.
  • The exceptions…

    Now for date, datetime2, and time data types to render correctly you need to create a view to convert (cast) and assign these values accordingly.

    Did not want to deal with the the famous error message: Argument data type time is invalid for argument 1 of isdate function.
    As for the bit data type and anything that needs to to be represented with true or false I used the case statement…

    An example; Create the following view named ‘vw_GameJSON’

    create view vw_GameJSON
    as
    select	GameID, 
    		convert(varchar(30), GameDate, 121) as 'GameDate', 
    		left(cast(GameTime as varchar(16)), 12) as 'GameTime', 
    		Country, 
    		City, 
    		Stadium, 
    		[Group], 
            Team01, 
    		Team02, 
    		case 
    			when IsActive = 1 then 'true' 
    			when IsActive = 0 then 'false' 
    		end as 'IsActive'
    from	dbo.Game;
    

    Change the values for the following variables @TableViewName to ‘vw_GameJSON’ and ‘GameID, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, IsActive’ for @Columns.

    Make the following changes in the script;

    -- Input variable for Table or View
    set @TableViewName = 'vw_GameJSON';
     
    -- Input variable for columns, comma separated...
    set @Columns = 'GameID, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, IsActive';
    

    Execute the query and look at the generated output

    [
    {"GameID":1,"GameDate":"2014-06-12","GameTime":"17:00:00.000","Country":"Brazil","City":"Sao Paulo","Stadium":"Arena de Sao Paulo","[Group]":"Group A","Team01":"Brazil","Team02":"Croatia","IsActive":true},
    {"GameID":2,"GameDate":"2014-06-13","GameTime":"13:00:00.000","Country":"Brazil","City":"Natal","Stadium":"Estadio das Dunas","[Group]":"Group A","Team01":"Mexico","Team02":"Cameroon","IsActive":true},
    {"GameID":3,"GameDate":"2014-06-13","GameTime":"16:00:00.000","Country":"Brazil","City":"Salvador","Stadium":"Arena Fonte Nova","[Group]":"Group B","Team01":"Spain","Team02":"Netherlands","IsActive":true},
    {"GameID":4,"GameDate":"2014-06-13","GameTime":"18:00:00.000","Country":"Brazil","City":"Cuiaba","Stadium":"Arena Pantanal","[Group]":"Group B","Team01":"Chile","Team02":"Australia","IsActive":true},
    {"GameID":5,"GameDate":"2014-06-14","GameTime":"13:00:00.000","Country":"Brazil","City":"Belo Horizonte","Stadium":"Estadio Mineirao","[Group]":"Group C","Team01":"Colombia","Team02":"Greece","IsActive":true},
    .
    .
    .
    

    Notice the GameDate and GameTime attributes are now outputted as string and the IsActive value with true.

    I have since turned this script into a stored procedure that I named ‘sp_SQLToJSON’ and placed in the master database – I can now call this proc from any database and voilà!

    You can download the script here or from GitHub