SQL Server External Tools

December 26, 2018

When installing SQL Server and related components, there are several external tools that get installed as well. Here a few of the programs that show up under SQL Server (2016 or 2017) in the Start menu.

SQL Server External Tools:
Data Feed Publishing Wizard: Creates a linked server with a view that used the linked server to access the results of a SSIS data flow. Uses a data flow destination to create an endpoint-like interface. Uses Data Feed Publishing Components. add-on
Data Profile Viewer: View the XML output from a SSIS Data Profile task – Check for issues with data quality (Column Length, Column Pattern, Functional Dependency, etc.)
Database Engine Tuning Advisor: Uses a workload file, Plan Cache or Query Store to analyze a database and to make recommendations on changes to make for improved performance.
Deployment Wizard: SSIS project or package deployment
Execute Package Utility: Run SSIS packages
Import and Export Data (32 and 64 bit): Wizard for data imports and exports.
Installation Center: Add/Remove features, Repair, etc.
Project Conversion Wizard: Convert a SSIS package to the project deployment model.

Identity ID And Rollback

November 30, 2018

I was curious what would happen with inserting a values into a row with an identity values and then rolling back the transaction. Would the identity value created in the rolled back transaction be discarded?
Here’s a script to create a table with an identity column, with a few inserts and a rollback.

CREATE TABLE #IdentityTest(RecordID int not null identity(1,1), DisplayName varchar(20) not null);

INSERT INTO #IdentityTest(DisplayName) VALUES ('Record1');

INSERT INTO #IdentityTest(DisplayName) VALUES ('Record2');

INSERT INTO #IdentityTest(DisplayName) VALUES ('Record3');

SELECT * FROM #IdentityTest;

We can see that ID value 2 is generated for the 2nd insert, the transaction is rolled back, and the ID value 3 is used for the next value. So this will leave a gap in the identity values, the engine won’t try to fill the gap. This makes sense, especially in an environment where many processes would be hitting this table at the same time.

SQL Server 2019 CTP 2.0

October 31, 2018

SQL Server 2019 was announced recently, a preview version is now available.
SQL Server 2019 CTP 2.0

There’s also a preview edition of SSMS, version 18.0, that will work with SQL Server 2019.
SSMS 18.0

Brent Ozar has a short article listing items removed in SSMS 18. Database diagrams and T-SQL debugging are two of the big features removed.

Aaron Bertrand has a good article summarizing the new features on MSSQLTips

A more in depth overview of the available features in a Microsoft white paper (registration required).

A small change, but one I’m excited about, is notification of the column involved when getting the ‘string or binary data would be truncated’ error. John Sterrett has an article on this feature.

SQL Server Configuration Manager in Windows 10

September 16, 2018

I needed to set an instance setting for a SQL Server instance, so I went to open SQL Server Configuration Manager (I’m running Windows 10 and SQL Server 2017 Developer edition). I’m used to seeing this as a stand-alone option, but I didn’t see it listed with my available programs.
It turns out that this functionality is now found in MMC(Microsoft Management Console). It can be accessed from the Control Panel, under Administrative Tools, then Computer Management.
I would have never found this on my own, luckily I found this article:

Convert String To DateTime For A Specific Time Zone

August 31, 2018

I recently had a project where we were receiving a file with a date stamp in the file name, in the format yyyyMMddHHmm, so the date plus the hour and minute. The file was produced on the West Coast in Pacific time (I’m in the Eastern time zone). We want to store that date/time, where we store time as UTC.
Because of daylight saving time (DST), we can’t just add a set number of hours to the Pacific time to get UTC, unless we can determine if the input time in in standard time or DST. I’ve worked at places where we maintained a table with all of the start and end dates for DST, and would look up each time in order to convert the time.
Luckily, SQL Server has support for standard time and DST to determine which is appropriate for a specific time.
Here is the SQL code I ended up using.

DECLARE @input char(12) = '201808311200';
DECLARE @output datetimeoffset;

DECLARE @year int = CAST(SUBSTRING(@input, 1, 4) AS int);
DECLARE @month tinyint = CAST(SUBSTRING(@input, 5, 2) AS tinyint);
DECLARE @day tinyint = CAST(SUBSTRING(@input, 7, 2) AS tinyint);
DECLARE @hour tinyint = CAST(SUBSTRING(@input, 9, 2) AS tinyint);
DECLARE @minute tinyint = CAST(SUBSTRING(@input, 11, 2) AS tinyint);

SET @output = (DATETIMEFROMPARTS(@year, @month, @day, @hour, @minute, 0, 0)) AT TIME ZONE 'Pacific Standard Time';

SET @output = @output AT TIME ZONE 'UTC';

SELECT @output;

First, I parsed the input string into the separate date/time parts. Alternatively, We could have added formatting to the input string (Like 2018-08-31 12:00:00) and converted that directly to a datetime.
If we use the datetimeoffset data type, we can use the AT TIME ZONE statement to designate what time zone our time is in. In this case (with Pacific time), we’ll end up with an offset of -7:00. We’ll use ‘Pacific Standard Time’ even though DST is in effect, since the point of the functionality is for us to not have to make the DST determination.
You can query the sys.time_zone_info system table to see a list of the time zones that can be used with this statement.
We’ll make one last conversion to UTC to end up with the datetime that we’ll store.

While on the subject of time zones, here are a few things on UTC. UTC stands for Coordinated Universal Time, the acronym is a compromise between the English and French phrases. (Wikipedia).
People use UTC and GMT(Greenwich Mean Time) as synonyms, and the time for each in within fractions of a second, but there is a difference between the two. GMT is a time zone, and is based on astronomical observations. UTC is a time standard (not a time zone) as in based on atomic clocks.

Docker And SQL Server

July 29, 2018

Containers are a way to virtualize a single application, as opposed to virtualizing an entire server. We can run an application in this container without installing the product or worrying about all of the dependencies of that application. Everything needed to run an application is in the container.

Docker is the most popular platform for running and managing containers.

Prerequsites for Installing Docker:
To install Docker on Windows, we’ll need to have Microsoft Windows 10 Professional or Enterprise 64-bit(You can also run on Windows Server), we need a version that will support virtualization. We also need virtualization enabled on the machine (You’ll need to go into the BIOS to enable this).
We’ll need Hyper-V and Containers features enabled as well. We can do this in the Control Panel, or use Cortanta to search for ‘Turn Windows features on and off’. Make sure the Containers and the Hyper-V items are checked. The Docker installation will check to see if these features are enabled, and will give you the option to enable these features then, but setting these before hand will save you a reboot.
Notes that if Hyper-V is enabled, then Virtual Box will not work, if you have that installed.

Installing Docker:
The Docker Community Edition for Windows is a free download, but you’ll need to set up a Docker login.
The install is pretty straight forward. For my example, I checked to Use Windows containers instead of Linux containers.

Basic Docker Commands:
We can use the Command Prompt or the Powershell Window to run Docker command, I used Powershell for these examples.

docker --version

: This will return the version and build number for the Docker install.

docker --help

: This will give us a list of available Docker commands. We can also use this to get additional information on a specific command, for example if we wanted more information on the version command, we would run docker version –help.

Sample Docker Container:
We can run a sample Hello World container just to make sure everything is set up on running correctly. We can run the command:

docker run hello-world

This will pull down the Hello World image and run it. If everything is running correctly, you’ll see a message ‘Hello From Docker!’. If things aren’t set up correctly you’ll see an error message.
Initially I got an error:
“container xxx encountered an error during CreateContainer: failure in a Windows system call: No hypervisor is present on this system.”
I had let the Docker install enable the Containers and Hyper-V components for me. When I went to check on the options, The Hyper-V Platform option under Hyper-V was greyed out with the message “Hyper-v cannot be installed: Virtualization support is disabled in the firmware”. It turned out that Virtualization was turned off on my laptop, which prevented this option from being enabled. I didn’t get an error during the install when it trying to enable these features, and it took me a while to figure out the issues. So I would recommend running through the prereqs yourself, instead of relying on the install to do this.

Other Docker Commands:

docker images

: List all of the installed images.

docker rmi hello-world -f

: To remove an image (hello-world). The -f flag will force removal.

docker container ls

: List running containers

Running SQL Server:
Now to a more practical use. We can run SQL Server in Docker. A container for SQL Server Developer edition is available through Docker Hub . We can view some information on the container here.
We can run this command to download the container:

docker pull microsoft/mssql-server-windows-developer

The download is 4-5 gigs.
Once we have pulled the container down, we can start it up:

docker run -d -p 1433:1433 -e sa_password=WeakPW12 -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer

It will take a few seconds to start up. Once the container is running, we’ll see an ID printed to the console. Make a note of this ID.
So there are a few parameters that have been set in the run command. -d means detached, which means that the container runs in the background. -p (for publish) designates the port for the service, we’ll stick with the SQL Server default of port 1433. We list two ports, one for the container and another for the host. -e will set an environment variable, in this case we’ll indicate that we accept the user agreement. We’re also using -e to set the sa password for this session. At first, I set a very simple sa password, and then I had issues trying to connect to SQL Server. When creating the sa password, you’ll have to follow the complexity requirements for a sa password, you won’t get an error message for an invalid password. The sa password has a 8 character minimum, plus you’ll need at least 3 from upper case letters, lower case letters, numbers and special characters.
We can connect to our SQL Server container either with the SQLCMD command line tool, or with SSMS. For this example, we’ll use SSMS.
To connect with SSMS, we’ll need to get the IP address for our running SQL Server. Using the ID we got when starting the container, we’ll run this command:

docker inspect {ID}

You can also use the 12 character Container ID. We can get that ID by running:

docker ps

In the output, we’ll go to Network Settings / Network / IP Address , and make a note of this address.
To connect with SSMS, we’ll use the IP Address as the Server Name (include a comma then the port number if not using the default port). Select SQL Server Authentication, then use sa and the sa password we passed in when starting the container.
Once we’ve connected, we can use SSMS to create objects, or open a query window, just as we would with any SQL Server.

Once we’ve completed, we can stop the container passing in the Container ID for {ID}:

docker stop {ID}

Once we stop the container, we’ll lose whatever objects and data that we’ve set up. It is possible to map the container to use data and log files on the host machine, this page goes through the setup for that.

Get started with Docker
Windows Containers
Docker Documentation
SQL Server 2016 Express Edition in Windows containers

Incorrect syntax was encountered while parsing GO

June 30, 2018

I have a Powershell script I created to script out the views and stored procedures in a database. Usually I just use it to find all the references to a particular table or column, but in this case I wanted to run the procedure on another database. When running the script, I ran into this error:
“A fatal scripting error occurred.
Incorrect syntax was encountered while parsing GO.”

The error came from the SQL in this script. (The issue is dependent on formatting, I couldn’t get the format to come through in the WordPress page).

At first I was puzzled, everything looked fine to me. I did have the extra space in the front of the third line, so I deleted it and re-ran. Still with the same error. I knew that GO needed to be on its own line (unless I added a number to indicate I wanted the batch to run that many times).
I came across this post that described the same error. I didn’t have anything on the same line as the GO, but I decided to check my Powershell script. Instead if using a carriage return and line feed after the GO:

I had inadvertently used a `v (a vertical tab) instead of a `n. Error on my part, but easily corrected.
Instead of regenerating my script, I could also save a copy of the script from SSMS.
In the Save dialog, select the arrow next to the Save button and select Save With Encoding.
Under Line Endings, change ‘Current Setting’ to ‘Windows (CR LF)’, then click OK and then Save.