SQL

Blog posts tagged 'SQL'

Microsoft SQL Server on a Ubuntu Linux VM

Apr 15 17

As part of a blog post on the LEDS stack (Linux Nginx Dotnet, SQL Server) I’m putting together, I wanted to see how easy it is to install SQL Server on a Ubuntu VM, running on Digital Ocean.
Turns out, very easy.

I went with a Ubuntu  16.04.2 x64 box, with 4GB of RAM
The docs state that you need at least 3.25GB of memory to run SQL Server on Linux.

digital_ocean_ubuntudigital_ocean_4gb_ram

Once my droplet had created, I SSH’d into it.

First, we need to import the public repository GPG keys, and register the Microsoft SQL Server Ubuntu repository

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | sudo tee /etc/apt/sources.list.d/mssql-server.list

Then, it’s a simple

sudo apt-get update
sudo apt-get install -y mssql-server

And SQL Server is installed in around 2-4 minutes.

Next up, it tells you the command you’ll need to run:

sql_linux_complete_setup

Which is

sudo/opt/mssql/bin/mssql-conf setup

for those copying and pasting.

Once you’ve accepted the license terms (you do accept them, don’t you?) and chosen a secure SA password, setup is complete.

You can verify the service is running with:

systemctl status mssql-server

ms_sql_linux_service_running

Back in Windows…
To test out your new SQL Server install on Linux, you can connect to it using SSMS (Windows)
I’m currently using SQL Server Management Studio – Release Candidate (17.0 RC3)
(I tested it with 2014, and it worked fine.)

Note:
Port 1433 was open on my VM, but it may be blocked with your firewall!

Restoring the Northwind database backup.

Ok, next let’s get a database up and running on our Linux SQL Server.
I downloaded the Northwind database backup to my local machine.

I used SCP to copy the backup of the Northwind database:

scp Northwind.bak.zip [email protected]:/var/opt/mssql

This copies my Northwind.bak.zip file into /var/opt/mssql on the remote server.

I’m using a mac, so SCP is installed already.
If you’re on Windows, you’ll need to use something like WinSCP

Since I uploaded a zipped backup file, I’ll need to unzip it.

If the unzip command isn’t already installed on your system, then run:

sudo apt-get install unzip

Then it’s just a simple

unzip Northwind.bak.zip

Next, with SSMS locate the backup file, and restore in the usual way:

ms_sql_linux_service_restore_bak

Note: SSM puts “C:\” in front of your path in the location bar
This can be ignored, but for some reason it does need to be there.

And there we have it. The Northwind database, restored on to a SQL Server instance, running on Linux, on a non-Microsoft cloud.

ms_sql_linux_northwind_restored

Upgrading from Navision 4 to Navision 5 – Database Error

Sep 16 09

This is a tad of a messy blog post, so apologies for that, however it’s mainly for my own reference.

Here at Crocus, we run Navision to handle product inventory, orders, and the like…
We wanted to upgrade from version 4, to version 5.

After following the upgrade instructions, I got this error:

Msg 8662, Level 16, State 0, Line 1
Cannot create the clustered index "VSIFTIDX" on view "Nav5.dbo.Crocus Live$OrderRequestDetail$VSIFT$1" because the view references an unknown value (SUM aggregate of nullable expression). Consider referencing only non-nullable values in SUM. ISNULL() may be useful for this.

Annoying.

There wasn’t anything obviously wrong.
So I opened up SQL Profiler, and re-ran the update process in the Navision client, to capture the exact SQL queries that were causing the error….

I saw it created a view called Crocus Live$OrderRequestDetail$VSIFT$1

Heres the create view script:

It was failing on the following query:

Problem was:

Some of the columns specified as “boolean” within Navision, were in fact NULLABLE tinyint columns within SQL Server.

I suspect that this was due to some legacy version of SQL not supporting bit columns, or something along those lines…

Anyway, none of the values in the column (close to a million rows within the table) was null, so I changed the datatype to BIT and set NOT NULL. I did this for each of the columns in the table which should of been a boolean (and were specified in the CREATE VIEW statement (in my case was Amount, AmountIncludingVAT and PaidFor)

This immediately solved the “Cannot create the clustered index” problem

Copying tables across databases

Apr 24 09

So, you’ve got a requirement to move or copy a table from one database to the other? Including data?

Use SELECT INTO.

For example, we have a database called “MyDatabase”
We also have another database – “MyNewDatabase”

MyDatabase contains the table “Customers” – with a bunch of data that we want to preserve.

You need to move this into MyNewDatabase?

Here is an example of the query:
[sql]SELECT * INTO MyNewDatabase.dbo.Customers
FROM MyDatabase.dbo.Customers[/sql]
You could of course predicate your query, include where clauses, select only a few columns etc.

Check out this post on W3Schools for more information, and how to use this in the same database (for temp. backups for example)