John McCormack's blogs

Search and replace inside SSIS package

3rd November 2016 By John McCormack 1 Comment

Search and replace inside SSIS package

I had the situation recently where I needed to search and replace a string inside an SSIS package. The reason for this was about 25 Execute SQL tasks within the package all included some hard coded T-SQL that pointed at a decommissioned network share. I needed to change the T-SQL in each task to point to a new network share. I know the package should have used a variable for this but I needed a quick fix.

If you realise that your SSIS package is simply just some XML which is made to look graphical by SSDT or BIDS, it becomes clear that this is a very simple process. It is just like doing a replace all in notepad which most people will have done many times. This is much easier and quicker than manually opening each and every Execute SQL Task and making each change manually.

Steps

Make a copy of your SSIS package (just in case)
Right click on the .dtsx file and choose Open with -> Notepad
In notepad, press Ctrl-H
1. Put the old value in Find What
2. Put the new value in Replace with
3. Click Replace All
Save your package
Open in SSDT or BIDS to confirm your changes have worked.

Error DReplay Could not find any resources appropriate for the specified culture or the neutral culture

13th October 2016 By John McCormack 3 Comments

Error DReplay Could not find any resources appropriate for the specified culture or the neutral culture

2016-10-12 08:26:15:865 Error DReplay Could not find any resources appropriate for the specified culture or the neutral culture. Make sure "Microsoft.SqlServer.Management.DistributedReplay.ExceptionTemplates.resources" was correctly embedded or linked into assembly "Microsoft.SqlServer.Management.DistributedReplay" at compile time, or that all the satellite assemblies required are loadable and fully signed.

This is a short post to hopefully save any readers some time. Like many people, I was receiving this error when trying to use distributed replay with SQL Server 2016. There are a number of causes but the error message can throw you off course. In my experience, you will get this error when the controller and client cannot communicate with each other.

How can you check if there is a communication issue?

Check that the SQL Server Distributed Replay Controller service is running on the controller machine you wish to use.
Ensure that the SQL Server Distributed Replay Client service is running on each client machine you wish to use.
- This was the cause of my problem. I had restarted the client machines but services were set to manual rather than automatic so had not restarted.

To quickly turn on the services without logging on to each server, you could run the command below. Just change the server name for your client server name.

SC \\Distributed_Replay_Client1 start "SQL Server Distributed Replay Client"
SC \\Distributed_Replay_Client2 start "SQL Server Distributed Replay Client"
SC \\Distributed_Replay_Client3 start "SQL Server Distributed Replay Client"
SC \\Distributed_Replay_Client4 start "SQL Server Distributed Replay Client"
SC \\Distributed_Replay_Client5 start "SQL Server Distributed Replay Client"

Check for this line in your dreplay client log file: Registered with controller “name_of_controller”. The name of controller is whatever you have used such as Distributed_Replay_Controller. You will find the file at C:\Program Files (x86)\Microsoft SQL Server\130\Tools\DReplayClient\Log for SQL Server 2016. If the service is running and the log does not say registered with controller, you have an issue.

How to fix the communication issue.

Assuming services are running and log does not say Registered with controller

Check your firewall has been opened up (on every client machine) to allow incoming connections for DReplayClient.exe. Also, ensure that incoming connections are allowed for DReplayController.exe on your controller machine. Check section 6 of the post-installation steps on this technet article for more info. If you prefer to use the GUI, open the snap in for Windows Firewall with Advanced Security, open command prompt or start, then type wf.msc
Ensure your DCOM settings have been changed to allow communication between the controller and client machines. DCOM is used for the RPC communications between controller and clients as well as controller and administration tool.

You should go through this msdn page and check you have done everything required. In my experience, the DCOM setting are something that DBAs do not generally mess with so you wouldn’t necessarily have thought to do this unless you saw it in the configuration guide.

Upgrading from SQL Server evaluation edition to enterprise edition

20th September 2016 By John McCormack Leave a Comment

Upgrading from SQL Server evaluation edition to enterprise edition

This 5 minute guide shows how to simply upgrade from SQL Server Evaluation edition to Enterprise edition. It’s very easy but I didn’t think it was intuitive. I spent a quite a while clicking around all the menu items at the top of management studio (SSMS) only to find I was looking in completely the wrong place. There’s a bit more to it than simply updating the product key.

How do you do it?

[Read more…]

JSON support for SQL Server 2016

14th July 2016 By John McCormack Leave a Comment

JSON and SQL Server 2016

This post is an introduction to the new JSON support for SQL Server 2016. The post is an entry level post, suitable for someone who is using JSON methods for the first time but who does have some knowledge of T-SQL and a basic understanding of what JSON is.

Background:

JSON support has been introduced into SQL Server for the first time in SQL Server 2016. It was a highly requested feature on Microsoft Connect. Since there are new methods added to SQL Server, I wanted to test them out and get to understand them.

The code:

This code will only work in SQL Server 2016 so you need to have an instance of 2016 available to use for the demo. You can also use Azure SQL Database. See announcement.

Database compatibility level 130 is required for OPENJSON function. Other functions are available in all levels. Note that you need to have new V12 server.

—

Step 1

Use an existing database or create a new one. I created one called Learning. Then create a table to use for the demo called Weather.

[sql]

USE Learning;

DROP TABLE IF EXISTS Weather; — This syntax is new too.
CREATE TABLE Weather
(
ID INT IDENTITY(1,1) PRIMARY KEY,
[Location] nvarchar (128),
DateInserted datetime DEFAULT GETDATE(),
WeatherDetail NVARCHAR(max) — No JSON data type, we just use NVARCHAR(max)
)

[/sql]

Step 2

Get a JSON source that you want to use. There are a number of free data sources available online. For the demo, I have used the local weather forecast in JSON format from Yahoo weather. https://developer.yahoo.com/weather/

Now that we have a table and some valid JSON, we want to insert the JSON text into the table we just created. Replace the values for Location and WeatherDetail in the code below with your own values with your own or simply just run the code I have provided below.

[sql]
INSERT INTO Weather ([Location],WeatherDetail)
VALUES
(‘Paisley’,
‘{
“query”: {
“count”: 1,
“created”: “2016-04-05T17:39:05Z”,
“lang”: “en-GB”,
“results”: {
“channel”: {
“units”: {
“distance”: “mi”,
“pressure”: “in”,
“speed”: “mph”,
“temperature”: “F”
},
“title”: “Yahoo! Weather – Paisley, Scotland, GB”,
“link”: “http://us.rd.yahoo.com/dailynews/rss/weather/Country__Country/*https://weather.yahoo.com/country/state/city-31342/”,
“description”: “Yahoo! Weather for Paisley, Scotland, GB”,
“language”: “en-us”,
“lastBuildDate”: “Tue, 05 Apr 2016 06:39 PM BST”,
“ttl”: “60”,
“location”: {
“city”: “Paisley”,
“country”: “United Kingdom”,
“region”: ” Scotland”
},
“wind”: {
“chill”: “48”,
“direction”: “270”,
“speed”: “18”
},
“atmosphere”: {
“humidity”: “63”,
“pressure”: “991.0”,
“rising”: “0”,
“visibility”: “16.1”
},
“astronomy”: {
“sunrise”: “6:34 am”,
“sunset”: “8:7 pm”
},
“image”: {
“title”: “Yahoo! Weather”,
“width”: “142”,
“height”: “18”,
“link”: “http://weather.yahoo.com”,
“url”: “http://l.yimg.com/a/i/brand/purplelogo//uh/us/news-wea.gif”
},
“item”: {
“title”: “Conditions for Paisley, Scotland, GB at 05:00 PM BST”,
“lat”: “55.842018”,
“long”: “-4.42209”,
“link”: “http://us.rd.yahoo.com/dailynews/rss/weather/Country__Country/*https://weather.yahoo.com/country/state/city-31342/”,
“pubDate”: “Tue, 05 Apr 2016 05:00 PM BST”,
“condition”: {
“code”: “30”,
“date”: “Tue, 05 Apr 2016 05:00 PM BST”,
“temp”: “52”,
“text”: “Partly Cloudy”
},
“forecast”: [
{
“code”: “12”,
“date”: “05 Apr 2016”,
“day”: “Tue”,
“high”: “53”,
“low”: “44”,
“text”: “Rain”
},
{
“code”: “11”,
“date”: “06 Apr 2016”,
“day”: “Wed”,
“high”: “47”,
“low”: “41”,
“text”: “Showers”
},
{
“code”: “30”,
“date”: “07 Apr 2016”,
“day”: “Thu”,
“high”: “53”,
“low”: “40”,
“text”: “Partly Cloudy”
},
{
“code”: “39”,
“date”: “08 Apr 2016”,
“day”: “Fri”,
“high”: “49”,
“low”: “39”,
“text”: “Scattered Showers”
},
{
“code”: “12”,
“date”: “09 Apr 2016”,
“day”: “Sat”,
“high”: “49”,
“low”: “41”,
“text”: “Rain”
},
{
“code”: “11”,
“date”: “10 Apr 2016”,
“day”: “Sun”,
“high”: “51”,
“low”: “40”,
“text”: “Showers”
},
{
“code”: “28”,
“date”: “11 Apr 2016”,
“day”: “Mon”,
“high”: “53”,
“low”: “41”,
“text”: “Mostly Cloudy”
},
{
“code”: “12”,
“date”: “12 Apr 2016”,
“day”: “Tue”,
“high”: “52”,
“low”: “42”,
“text”: “Rain”
},
{
“code”: “39”,
“date”: “13 Apr 2016”,
“day”: “Wed”,
“high”: “53”,
“low”: “41”,
“text”: “Scattered Showers”
},
{
“code”: “5”,
“date”: “14 Apr 2016”,
“day”: “Thu”,
“high”: “53”,
“low”: “40”,
“text”: “Rain And Snow”
}
],
“description”: “<![CDATA[<img src=\”http://l.yimg.com/a/i/us/we/52/30.gif\”/>\n \nCurrent Conditions:\n Partly Cloudy\n \n \nForecast:\n Tue – Rain. High: 53Low: 44\n Wed – Showers. High: 47Low: 41\n Thu – Partly Cloudy. High: 53Low: 40\n Fri – Scattered Showers. High: 49Low: 39\n Sat – Rain. High: 49Low: 41\n \n \n<a href=\”http://us.rd.yahoo.com/dailynews/rss/weather/Country__Country/*https://weather.yahoo.com/country/state/city-31342/\”>Full Forecast at Yahoo! Weather</a>\n \n \n(provided by <a href=\”http://www.weather.com\” >The Weather Channel</a>)\n \n]]>”,
“guid”: {
“isPermaLink”: “false”
}
}
}
}
}
}’
)

[/sql]

Step 3

Check your table contents by runnning [sql]SELECT * FROM [dbo].[Weather];[/sql]

Step 4 – ISJSON

The ISJSON built-in function is used to verify that your JSON text is properly formatted. The queries below select the values for Location and WeatherDetail from dbo.Weather and also the return value of the ISJSON function against these values. We can see that the value of Location is Paisley and that this is not a valid JSON value. We also see the value of WeatherDetail and that this IS a valid JSON value.

[sql]

SELECT [Location] FROM [dbo].[Weather];
SELECT ISJSON([Location]) as [ISJSON] FROM [dbo].[Weather];
SELECT WeatherDetail FROM [dbo].[Weather];
SELECT ISJSON(WeatherDetail) as [ISJSON] FROM [dbo].[Weather];

[/sql]

Step 5

Let’s insert another JSON value with fewer details into dbo.Weather. This will help us when we try to query it later.

[sql]
INSERT INTO Weather ([Location],WeatherDetail)
VALUES (‘Caleta de Fuste’,'{
“Forecasts”:[
{
“Forecast”:{
“Location”:”Caleta de Fuste”,
“Date”:”Apr 11 2016 12:00AM”,
“WeatherDetails”:”Dry”
}
},
{
“Forecast”:{
“Location”:”Caleta de Fuste”,
“Date”:”Apr 11 2016 3:00AM”,
“WeatherDetails”:”Dry”
}
},
{
“Forecast”:{
“Location”:”Caleta de Fuste”,
“Date”:”Apr 11 2016 6:00AM”,
“WeatherDetails”:”Cloud”
}
},
{
“Forecast”:{
“Location”:”Caleta de Fuste”,
“Date”:”Apr 11 2016 9:00AM”,
“WeatherDetails”:”Sunny”
}
},
{
“Forecast”:{
“Location”:”Caleta de Fuste”,
“Date”:”Apr 11 2016 12:00PM”,
“WeatherDetails”:”Sunny”
}
},
{
“Forecast”:{
“Location”:”Caleta de Fuste”,
“Date”:”Apr 11 2016 3:00PM”,
“WeatherDetails”:”Sunny”
}
},
{
“Forecast”:{
“Location”:”Caleta de Fuste”,
“Date”:”Apr 11 2016 6:00PM”,
“WeatherDetails”:”Windy”
}
}
]
}’
)

[/sql]

Let’s just check what we have in the table (and it’s validity) before we move on.

[sql]

SELECT *, ISJSON(WeatherDetail) as [ISJSON] FROM Weather;

[/sql]

Nice. Both fields contain valid JSON.

Step 6 – OPENJSON

We will use the table-valued funtion OPENJSON to parse JSON text and return the JSON objects into the rows and columns we know and love. OPENJSON transforms the array of JSON objects into a table, in which each object is represented as one row, and key/value pairs are returned as cells. Source: https://msdn.microsoft.com/en-gb/library/dn921897.aspx

[sql]

— As you will see, this query displays the JSON data as an easy to read table.
DECLARE @json nvarchar(max) = (SELECT WeatherDetail FROM Weather where ID = 2)
SELECT *
FROM OPENJSON (@json,N’$.Forecasts’)
WITH
(
Location NVARCHAR(64) N’$.Forecast.Location’,
Date datetime N’$.Forecast.Date’,
WeatherDetails NVARCHAR(MAX) N’$.Forecast.WeatherDetails’
)

[/sql]

Step 7 – FOR JSON

Let’s now create a new table and insert some data so we can understand how to use FOR JSON.

[sql]

DROP TABLE IF EXISTS Forecast;
CREATE TABLE Forecast
(
ID INT IDENTITY(1,1) PRIMARY KEY,
[Location] nvarchar (128),
[Date] datetime,
WeatherDetail NVARCHAR(max) — No JSON data type
)

— Insert the data which we wil work with next
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Paisley’,’2016-04-11 00:00:00.000′,’Light Rain’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Paisley’,’2016-04-11 03:00:00.000′,’Light Rain’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Paisley’,’2016-04-11 06:00:00.000′,’Heavy Rain’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Paisley’,’2016-04-11 09:00:00.000′,’Cloud’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Paisley’,’2016-04-11 12:00:00.000′,’Cloud’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Paisley’,’2016-04-11 15:00:00.000′,’Cloud’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Paisley’,’2016-04-11 18:00:00.000′,’Cloud’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Paisley’,’2016-04-11 21:00:00.000′,’Thunder’)
—
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Caleta de Fuste’,’2016-04-11 00:00:00.000′,’Dry’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Caleta de Fuste’,’2016-04-11 03:00:00.000′,’Dry’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Caleta de Fuste’,’2016-04-11 06:00:00.000′,’Cloud’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Caleta de Fuste’,’2016-04-11 09:00:00.000′,’Sunny’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Caleta de Fuste’,’2016-04-11 12:00:00.000′,’Sunny’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Caleta de Fuste’,’2016-04-11 15:00:00.000′,’Sunny’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Caleta de Fuste’,’2016-04-11 18:00:00.000′,’Windy’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Caleta de Fuste’,’2016-04-11 21:00:00.000′,’Windy’)

[/sql]

Step 8 FOR JSON AUTO

Use AUTO mode with the FOR JSON clause. (Formatted automatically based on structure of SELECT)

[sql]

— This select gives us our data in the familiar table format.
SELECT * FROM [dbo].[Forecast]
ORDER BY [Location],[Date]

— This select now formats our data as JSON
SELECT * FROM [dbo].[Forecast]
ORDER BY [Location],[Date]
FOR JSON AUTO;

[/sql]

Limitation: SSMS still uses the XML editor to show JSON so it doesn’t display it correctly when you click on it.

Due to this, a good tool to Format your JSON into a readable format if needed can be found at https://jsonformatter.curiousconcept.com/

Step 9 – FOR JSON PATH

Use PATH mode with the FOR JSON clause to maintain control over format. The name value pairs are generated by using this syntax in the select: AS [Forecast.Location]. The root node is named by the value we pass in after the word root: ROOT(‘Forecasts’)

[sql]

SELECT
[Location] AS [Forecast.Location],
CAST([Date] as nvarchar) AS [Forecast.Date],
WeatherDetail as [Forecast.WeatherDetails]
FROM [dbo].[Forecast]
ORDER BY [Location],[Date]
FOR JSON PATH, ROOT(‘Forecasts’);

[/sql]

This is much nicer when we paste the output into https://jsonformatter.curiousconcept.com/.

Comparing to XML functions

[sql]

SELECT * FROM [dbo].[Forecast]
ORDER BY [Location],[Date]
FOR XML AUTO;

SELECT
[Location] AS [Location],
CAST([Date] as nvarchar) AS [Date],
WeatherDetail as [WeatherDetails]
FROM [dbo].[Forecast]
ORDER BY [Location],[Date]
FOR XML PATH (‘forecast’), ROOT(‘Forecasts’);

[/sql]

What’s next?

This blog post is quite large and still only covers a small part of JSON support for SQL Server 2016. There are more built in functions such as JSON_VALUE, JSON_QUERY, & JSON_MODIFY which I hope to cover soon.

Useful resources

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/10/07/bulk-importing-json-files-into-sql-server/
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/09/22/openjson-the-easiest-way-to-import-json-text-into-table/
https://msdn.microsoft.com/en-gb/library/dn921897.aspx
https://blogs.msdn.microsoft.com/jocapc/2015/05/16/json-support-in-sql-server-2016/
https://jsonformatter.curiousconcept.com/

SQL server wait statistics query – With link to help

23rd May 2016 By John McCormack Leave a Comment

SQL Server Wait Types Library

Following the release of SQL Server Wait Types Library by Paul Randal via SQLSkills.com, I have updated my query which finds the most prolific wait types to include a link to the waits library for that particular wait type. The handy thing about the way Paul has named the posts is that they all end with the exact wait type name so this name can be pulled out from a query and appended to a fixed URL. This means we can copy the contents of the cell in the help_url column into our browser to find the entry in the SQL Server Wait Types Library.

More about the query

As sys.dm_os_wait_stats is reset when SQL server is restarted, it’s worth knowing the start time of the instance so you can get information on averages per minute or hour (or some other time scale). This is included in the query below. Please bear in mind that the contents of this DMV can be cleared out by running DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR); so server start time is not completely reliable as a measure for working out averages.

SQL server wait statistics query

[SQL]

— Declare variables
DECLARE
@sqlserver_start_time DATETIME,
@current_time DATETIME,
@mins INT,
@hours INT;

— Set variables
SET @sqlserver_start_time = (SELECT sqlserver_start_time FROM sys.dm_os_sys_info); — Needed to work when collection in dm_os_wait_stats started
SET @current_time = (SELECT SYSDATETIME());
SET @mins = (SELECT DATEDIFF(MINUTE,@sqlserver_start_time,@current_time));
SET @hours = (SELECT @mins/60 AS Integer);

— Uncomment PRINT statements for more info
— PRINT @sqlserver_start_time
— PRINT @current_time
— PRINT @mins
— PRINT @hours

SELECT TOP 10
wait_type,
wait_time_ms,
waiting_tasks_count,
waiting_tasks_count/@hours as waiting_tasks_count_PerHour,
waiting_tasks_count/@mins as waiting_tasks_count_PerMin,
wait_time_ms/waiting_tasks_count AS avg_wait_time_ms,
‘https://www.sqlskills.com/help/waits/’+wait_type as help_url — SQLSkills resource on waits. If web page not complete, find another source.
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC — Overall time spent waiting by wait type. (Could change to waiting_tasks_count DESC)

[/SQL]