Output sub query as CSV when Joining tables (one to many relationship)

This will show you how to get something like this from a table join and then I’ll explain how the SQL works.

Name csv
UK London,Manchester,Birmingham
USA New Your,Washington,Kentucky
France Paris,Leon,Frejus

Suppose we have two tables

-- Create base table
CREATE TABLE [dbo].[TableA](
[id] [int] NOT NULL,
[Name] [varchar](50) NULL
)

-- Create secondary table with foreign ley
CREATE TABLE [dbo].[TableB](
[fk_tableA] [int] NOT NULL,
[Detail] [varchar](50) NULL,
)

Lets put countries in the first one and cities in the second

-- Create test data (countries)
INSERT INTO TableA (id,Name) VALUES (1,'UK')
INSERT INTO TableA (id,Name) VALUES (2,'USA')
INSERT INTO TableA (id,Name) VALUES (3,'France')

-- Create secondary test data (cities)
INSERT INTO TableB (fk_tableA,Detail) VALUES (1,'London'),(1,'Manchester'),(1,'Birmingham')
INSERT INTO TableB (fk_tableA,Detail) VALUES (2,'New Your'),(2,'Washington'),(2,'Kentucky')
INSERT INTO TableB (fk_tableA,Detail) VALUES (3,'Paris'),(3,'Leon'),(3,'Frejus')

Now lets read the data from these tables in the usual way. That is: joined using INNER JOIN and output as a big multi row list.

-- Show the data linked in the data in usual way
SELECT A.Name, B.Detail
FROM TableA A
INNER JOIN TableB B ON B.fk_tableA = A.id
ORDER BY A.Name, B.Detail

Name Detail
France Frejus
France Leon
France Paris
UK Birmingham
UK London
UK Manchester
USA Kentucky
USA New Your
USA Washington

What if we want to see this instead

Name csv
UK London,Manchester,Birmingham
USA New Your,Washington,Kentucky
France Paris,Leon,Frejus

We can join the tables using this weird query with XML

SELECT
A.Name
,STUFF(SELECT ',' + CAST(B.detail AS VARCHAR(MAX))
FROM TableB B WHERE B.fk_tableA = A.id
FOR XML PATH('')),1,1,'') AS csv
FROM TableA A
GROUP BY
A.id, A.Name

Why does this work?

Well obviously the outer query just gets the list of country names and groups them by name so ignore that

Looking at just the inner query with the STUFF and XML removed

SELECT ',' + CAST(B.detail AS VARCHAR(MAX)) FROM TableB B WHERE B.fk_tableA = X

For each value of X this simply gets “, value of detail” so where X=id if UK we get…

,London
,Manchester
,Birmingham

FOR XML PATH(''))

simply wraps this in to a single line of text. The ” bit prevents the surrounding XML data node

“,London,Manchester,Birmingham”

Actually it goes into an automatically named xml column that SQL can easily convert to a string if it needs to

for xml path

Note that the text has a leading comma which we do not want. We are going to get rid of this with the stuff but before we do that we need the xml converted to varchar as stuff is a string function.

CAST(B.detail AS VARCHAR(MAX))

Now we have string “,London,Manchester,Birmingham” so get rid of the leading comma

Some of you may ask “Why did we not use SubString?”
Substring requires a length argument and in this case we do not know what this is. We could do it this way but we may get unpredictable results including string truncation. e.g. the following will work fine.
SUBSTRING( ',London,Manchester,Birmingham' ,2,100)

but this would be disastrous
SUBSTRING( ',London,Manchester,Birmingham' ,2,10)

By using Stuff we are simply inserting nothing in to position 1 and replacing 1 character which is the comma

i.e. ‘,London,Manchester,Birmingham’ becomes ‘London,Manchester,Birmingham’

So now the outer query handles the grouping in to the master data presented by the base table and the subquery substitutes the value of the outer column in to the inner query.

Its quite simple really but I think the combination of statements as a whole is a little daunting, particularly the XMl bit that throws people in to believing they are using XML in some mysterious way which although you are, you are really just using a feature of the conversion to XML (smashing data in to a single unit) and no actual XML is involved.

Here’s the code as a single block that you can copy and paste in to SQL Management Studio. Just create a new test database, open a new query, cut, paste and run this entire script to make the tables and show the output.

Complete example code

———————————————————

-- Create base table
CREATE TABLE [dbo].[TableA](
[id] [int] NOT NULL,
[Name] [varchar](50) NULL
)
-- Create secondary table with foreign ley
CREATE TABLE [dbo].[TableB](
[fk_tableA] [int] NOT NULL,
[Detail] [varchar](50) NULL,
)

— Create test data (countries)
INSERT INTO TableA (id,Name) VALUES (1,’UK’)
INSERT INTO TableA (id,Name) VALUES (2,’USA’)
INSERT INTO TableA (id,Name) VALUES (3,’France’)

— Create secondary test data (cities)
INSERT INTO TableB (fk_tableA,Detail) VALUES (1,’London’),(1,’Manchester’),(1,’Birmingham’)
INSERT INTO TableB (fk_tableA,Detail) VALUES (2,’New Your’),(2,’Washington’),(2,’Kentucky’)
INSERT INTO TableB (fk_tableA,Detail) VALUES (3,’Paris’),(3,’Leon’),(3,’Frejus’)

— Show data
SELECT * FROM TableA
SELECT * FROM TableB

— Show the data linked in the data in usual way
SELECT A.Name, B.Detail
FROM TableA A
INNER JOIN TableB B ON B.fk_tableA = A.id
ORDER BY A.Name, B.Detail

SELECT A.Name
,STUFF((
SELECT ‘,’ + CAST(B.detail AS VARCHAR(MAX))
FROM TableB B WHERE B.fk_tableA = A.id
FOR XML PATH(”)),1,1,”) AS csv
FROM TableA A
GROUP BY A.id, A.Name

———————————————————

Posted in Uncategorized | Leave a comment

Creating and Migrating an encrypted database.

This is a simple tutorial on how to…

  • Create a database
  • Setting up encryption
  • Add encrypted data to a table
  • Migrate the encrypted database to another server

The tutorial is supplied without warranty and David Bridge and David Bridge Technology Limited accept no responsibility for its use. If you wish to follow the code here then you must do so on a test machine in a controlled and safe environment.

Creation and population

To start with we will create a new database called “EncryptionTest” and add an employee table. This employee table is going to hold sensitive information about each employee’s income. We do not want unauthorised people to read or alter this information. Without encryption the sensitive data would be stored in an integer or string column but for encryption we need to store the data in a binary column.

The size of the binary column will depend on the size of the source data but it’s not a 1 to 1 relationship: You need to experiment to determine the size of the varbinary column (max 8,000 bytes). For the following example varbinary(300) will be way more than enough as it’s just going to store an integer.

CREATE DATABASE [EncryptionTest]
GO

CREATE TABLE [dbo].[Employee](
[id] [int] NULL,
[Name] [varchar](50) NULL,
[Salary] [varbinary](300) NULL
) ON [PRIMARY]
GO

Note that the table does not know that the data within it is encrypted; it’s just basic binary data. We are going to use a function to encrypt the data before we put it in to the binary column. If we simply write data to the column then the data will be stored as binary and can also be read as binary

To put data into the varbinary column we need to create a MASTER encryption key and to do this we need a master password. The following is not a good password but it’s good for an example.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyMasterPassword'

We also need to provide a subject name for the certificate that will be used to encrypt the data. Again, not a good name for a production system.

CREATE CERTIFICATE cert_sk_admin WITH SUBJECT = 'Admin key';

The Master password and the subject for the certificate are very important. If you lose then there will be no way to retrieve data from the database so you need to keep them very safe both from loss and prying eyes. Write them down on a piece of paper and lock them away in a sealed envelope in a secure fireproof safe and treat them like the crown jewels!

We now need to create another symmetric key that will be used to encrypt the data. This key will be encrypted using the certificate that we just created so there is no visible security on this key as the certificate was encrypted and stored in the database. This means that the code we will create later to read, write and update the table will not need to contain any sensitive data, just a reference to the stored certificate: Clever eh?

CREATE SYMMETRIC KEY sk_admin
WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE cert_sk_admin;

As I mentioned earlier the table simply contains a binary column that knows nothing about the encryption. To store encrypted data in the column we open the database symmetric key then call a function that will encrypt the data. Then we simply write the encrypted data in to the binary column.

The encryption function encryptbykey takes the GUID of a locally stored symmetric key as its first parameter followed by the data to be encrypted. The guid can be obtained by calling the key_guid() function, passing in the name of the key.

Let’s populate the employee table with encrypted Salary details

open symmetric key sk_admin
decryption by certificate cert_sk_admin;

insert into employee (id, name, salary)
values (1, 'A' , encryptbykey(key_guid('sk_admin'), '50000', 1, '1'))
insert into employee (id, name, salary)
values (2, 'B', encryptbykey(key_guid('sk_admin'), '50000', 1, '2'))
insert into employee (id, name, salary)
values (3, 'C', encryptbykey(key_guid('sk_admin'), '50000', 1, '3'))

And now let’s read the table using a simple select statement that will display the binary data

SELECT *
FROM [EncryptionTest].[dbo].[Employee]

Id Name Salary
1 A 0x0013CFB6E4F55745BFAE11A22DEF9969010000004E20F5770518E0AD4…
2 B 0x0013CFB6E4F55745BFAE11A22DEF996901000000EA49FC95777D6A0A1…
3 C 0x0013CFB6E4F55745BFAE11A22DEF9969010000009E62C1816144EF6CEF…

Although all of the Salaries are the same value (50k), the encrypted data is different for each row stored. This is because each row uses a row specific constant (row authenticator) in the encryption process. In the above case this row constant is the value of id but it could be anything that you either store with the row or that could be stored elsewhere but is specific to the row (i.e. employee NI, payroll number etc.); as long as we can pass the data to the encryptbykey function. The function is using the same symmetric key that we created above to perform the encryption but is performing the encryption with a modifier that is specific to the data being stored. You do not have to use a row authenticator: That magic number 1 in the example is the flag that states we are using one here.

Note that the function call to key_guid is being called for each row. This is not efficient. If updating in a loop or updating several rows like above, it is better to call the function once and store its value.

DECLARE @key uniqueidentifier = key_guid('sk_admin')
insert into employee (
id
, name
, salary
)
values (
1
, 'A'
, encryptbykey(@key, '50000', 1, '1')
)

Reading the encrypted data

To decrypt we simply use a decryption function with the same symmetric key. Note that the decryptbykey function does not require us to specify the key. You have to open the key before you call the function.

OPEN SYMMETRIC KEY sk_admin
DECRYPTION BY CERTIFICATE cert_sk_admin;
SELECT
Id
, Name
, CONVERT(VARCHAR(10),
DECRYPTBYKEY(Salary, 1, CONVERT(VARCHAR(5), id))
) as Salary
FROM Employee

Id            Name    Salary

1              A             50000

2              B             50000

3              C             50000

Now we have encrypted data in a table which we can read, update and add to as long as we have access to the symmetric key and authenticator that was used to encrypt the data, which in turn required that we can access the certificate that is stored in the same database which itself is encrypted by the master key, also stored in the same database.

Migration

Any backup of the database will contain the Keys and the certificate but it is a good idea to back up the keys and certificate manually and store them in a secure location.

Note that you DO NOT have to restore any keys or certificates to the target server when migrating a database which contains encrypted data. The database master key and certificate are contained in the backup file but they are encrypted so they are also useless to another server.

The correct way to migrate a database with encrypted data is to use a temporary migration symmetric key. The following code is going to show you how to do this

NOTE: Very important

Contrary to what you might read on the internet you do not have to drop the service master key on the source database before backing it up. DO NOT DO THAT!

Also you do not have to MIGRATE the service master key to the migration server. In fact this would be a silly thing to do. DO NOT DO THAT EITHER!

Check this before starting…

You may not be able to do the following using a local SQL Server management console connected to a remote source server due to windows permissions regarding file access rights on the source server and therefore might have to RDP to the machine and do it locally.

Overview of migration

SQL can encrypt data as many times as you ask it to using a different method each time and possibly multiple passwords. When SQL tries to read encrypted data it will decrypt the database master key using the service master key by default. If this fails it will try to decrypt it using the SQL Server Service account.

When you open a migrated encrypted database the system will attempt to decrypt the database master key using the service master key on the migration server and this key is likely different to the source server’s key so the decryption will fail. When this happens the migration server will try to open the database master key using the SQL service account. If it is different to the source server (common) then this will also fail. As sql failed to open the database master key then you will get the following message.

Msg 15581, Level 16, State 3, Line 1
Please create a master key in the database or open the master key in the session before performing this operation.

Ignore the “create a master key” bit as this is not the issue (read the “or” bit of the message): The database contains a master key but SQL cannot decrypt it using its service master key (if one exists, which it nearly always does).

Note: You do not need to change any settings for certificates or symmetric keys as these are already in the backup and they are fine. You do need to update the database master key though. Do NOT drop any keys! You just need to open the database master key using the migration password that you used to create it and then drop the encryption by the remote server’s service master key and then re-encrypt by the local server’s service master key.

NOTE: Do not misread that last bit: I did NOT say to drop any key – I said drop the “encryption” of the database master key by the service master key!

Following this simply close all keys (including the master) and then re-open the symmetric key used for querying the data and it will all work fine. This is because SQL is now able to decrypt the certificate and use it.

Note that when SQL decrypts the new database master key successfully with the newly applied service master key then it will also update its SQL Service encryption method too.

Migration process – step by step

If the remote server’s database master key does not have password encryption or you do not know the password but have access to the source server then you will need to add a migration password to the source database and then back it up.

 

Method in English

  1. On the source server
  2. Add password encryption first if not already exists.
  3. Back up the database
  4. Remove the migration password encryption if you created it in step 1a
  5. On the target machine
  6. Restore the database to the target server and select it
  7. Open the database master key using the migration password (1a)
  8. Drop the service master key from the encryption methods
  9. Add the service master key to the encryption methods (this will use local)

Method in Code

At the source server – backup

Add migration password and backup the database

alter master key add encryption by password = 'Migrat10n';
GO

Backup the database

BACKUP DATABASE [DBName]
TO DISK = N'folder\DBfilename.bak' WITH NOFORMAT, NOINIT
,  NAME = N'DBName -Full Database Backup', SKIP, NOREWIND, NOUNLOAD
,  STATS = 10
GO

Migration server restore.

RESTORE DATABASE [DBName]

FROM  DISK = N'folder\filename.bak' WITH  FILE = 1
,  MOVE N'DBName' TO N'folder\filename.mdf'
,  MOVE N'DBName_log' TO N'folder\filename.ldf'
,  NOUNLOAD
,  STATS = 10
GO

Now if you try to read the data you will see it complaining about the master key being missing or closed

open symmetric key sk_admin decryption by certificate cert_sk_admin;

select
Id
, Name
, convert(varchar(10), decryptbykey(Salary, 1, convert(varchar(5), id))) as Salary
from Employee

Msg 15581, Level 16, State 3, Line 1
Please create a master key in the database or open the master key in the session before performing this operation.

This is is because, by default, SQL Server will try to decrypt the key using the local service master key and that key is not capable of doing that. We need to update it.

Remember that when you backed up the data you created a new password encryption of the master key using the password ‘Migrat10n’? You can open the master key with that password.

open master key DECRYPTION BY PASSWORD = 'Migrat10n';

Test this works by running the select statement again after opening the key this way. It should work (but once you close it SQL will revert to trying to open it with the service key again).

You must now drop the encryption by service master key (legacy from the old server) and add it back in again for this server’s master key like this…

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
GO

ALTER MASTER KEY ADD  ENCRYPTION BY SERVICE MASTER KEY
GO

Now test that it works by closing all keys (this also closes the database master key)

close all symmetric keys

then run the select again, first opening the key with the certificate (i.e. without any password)

open symmetric key sk_admin decryption by certificate cert_sk_admin;

select
Id
, Name
, convert(varchar(10), decryptbykey(Salary, 1, convert(varchar(5), id))) as Salary
from Employee

Success!

Id            Name    Salary

1              A             50000

2              B             50000

3              C             50000

Additional recommended backup of certificate (not required)

backup master key to file = N'folder\filename.mkey' encryption by password = 'Migrat10n';
GO


backup certificate cert_sk_Admin
to file = 'folder\filename.cert'
with private key (
file = 'folder\filename.skey'
,encryption by password = ' Migrat10n');
GO

Posted in Uncategorized | Leave a comment

How to create empty guid in SQL Server – c# Guid.Empty() equivalent

In dotnet you can create a new Guid by using the static Guid class like this

(c# syntax)

Guid g = Guid.NewGuid();

And you can create an empty guid like this

Guid g = Guid.Empty;

In SQL Server you can create a new guid like this

DECLARE @g uniqueidentifier = newid()

select @g

But SQL Server does not have a function for creating an empty guid.

One way (a poor way) of achieving this is to create one from a string.

DECLARE @g uniqueidentifier = '00000000-0000-0000-0000-000000000000'

But if you get this wrong then an error will occur and its not easy to see why. E.g.

DECLARE @g uniqueidentifier = '00000000-0000-0000-0000-00000000000'

(hint: its got a 0 missing)

A safer way is to cast 0 to a binary and then the result to a uniqueidentifier

Declare @g uniqueidentifier = cast(cast(0 as binary) as uniqueidentifier)

SELECT @g

I hope this helps someone out there to produce safer, more reliable and readable code.

If you use this often and really want to make your code more readable then encapsulate this in a function.

 

-- =============================================
-- Author     : David Bridge
-- Create date: June 2014
-- Description: Returns an empty guid
-- =============================================
CREATE FUNCTION GuidEmpty()
RETURNS uniqueidentifier
AS
BEGIN
RETURN cast(cast(0 as binary) as uniqueidentifier)
END

Then use

select dbo.GuidEmpty()

Posted in Developer stuff, SQL Stuff | Tagged , , , , , | 1 Comment

MOTOROLA Universal Bluetooth Keyboard & Mouse – pc use – missing backslash

I bought a Motorola droid bluetooth keyboard which is supposed to be for a mobile phone or tablet (Xoom) but it has no backslash on it which is annoying when you connect it to a pc (which obviously you can or I would not be writing this. No really, I am using it to write this).

motorola bluetooth keyboard

Now this is no ordinary 103 key QUERTY keyboard. I wanted a small, black, pretty, and quality hideaway keyboard for a Windows 8 box that I use with a TV in the bedroom. Most of the time the pc gets used for NETFLIX but sometimes gaming and a bit of surfing. It’s plugged in to a 40 inch LED TV so it is kind of cool, especially when you factor in the specs. The pc is a tiny Gigabite Brix that is about 4 inches square and an inch tall with 16GB memory and 250GB SSD. Its a great gaming rig for Asphalt 8, ideal for Netflix and it boots into windows from cold in about 3 seconds. It shuts down in about the same time too.

Anyway, digression over. The keyboard is normally tucked away in a draw or under the telly so I wanted a small Bluetooth keyboard and mouse and opted for the Motorola Droid.

Its great but does not have a backslash. Here’s how to get one….

 

Find and hold “Alt Gr” to the right of the SPACE bar and click the # key (also has the pipe “|” character on it). That’s it!

Another top tip is the Euro symbol – €

To get this also use the “Alt Gr” key in combination with the $ key.

 

Now this may not be a SQL or code tip but if like me you like your gadgets and thing a dainty travel size but useful keyboard is a good idea then this could be something that will help you when you use that pc for something other than a bit of gaming. And its always good to know how to get the euro sign up isn’t it.

Dave

 

 

Posted in Hardware stuff, Other stuff | Tagged , , , , , , , , , , , | 2 Comments

SSRS report print button missing in IE11

If you use reporting services though a report viewer control then you might be missing the print button when viewing in IE11.

SSRS_NoPrintButton

To fix this, install the latest reportwiewer.exe.

This will return the print button but annoyingly it also changes the colour scheme and toolbar layout. It’s actually a nicer colour scheme but it might not fit with your page in both size and colour.

SSRS_NoPrintButton_restored

Direct download location=

http://www.microsoft.com/en-us/download/confirmation.aspx?id=35747

or if you don’t trust links, go to http://www.microsoft.com

and search for “MICROSOFT® REPORT VIEWER 2012 RUNTIME

Posted in Developer stuff, SQL Stuff | Tagged , , , , | Leave a comment

SSRS report hangs in IE11

If your SSRS report viewer’s “Loading” popup message never goes away (the report never renders) then chances are you have a reference to an image that does not exist in your report.

SSRS_Loading

I got this because I have a report template that I use for many customers and I like to show the customer’s log with an image with expression like

=Parameters!Customer.Value & ".jpg"

Which then requires that I copy a jpeg file called customername.jpg to my reports folder. If I did not have the logo, which was common for new customers then the logo was just blank but they Microsoft released IE11 and reports without a logo failed to render at all.

I have not found a way to fix this as there is no native IfFileExists() function (reports do not have access to the file system directly).

I tried updating the report viewer control to the latest version and this fixes other issues like the print button not working properly but it does not appear to fix this missing image issue.

You could move the jpegs to another place like a database or create a CLR extension but that’s a bit of overkill for a single browser issue (that shouldn’t be your responsibility to fix).

Workaround is therefore to simply make sure the file exists

Note:

The reports render fine in other browsers with the Report Viewer control!

The reports render fine when using SSRS directly without the Report Viewer control. So this is definitely a Report Viewer issue.

Posted in Developer stuff, SQL Stuff | Tagged , , , , , | 1 Comment

SSAS / Excel 2010 – An error was encountered in the transport layer

Connecting to SQL Server Analysis Services from a remote domain / login

When connecting to SSAS from a different remote domain / login using Excel 2010 where you have set up a windows user at the SSAS server, provided adequate permissions at the SSAS server to read the cube etc. but still cannot connect from a local Excel the issue is probably on account of the different domains / logins and a lack of trust between the servers.

All of your settings are correct, its probably just the windows being over cautious.

I found two workarounds for this

  • Create a local user with the same login details. Login as that user and then connect (not ideal)
  • Connect using the runas command and include the /netonly switch

A little more info on the second method….

  1. Navigate to excel in Windows explorer (“C:\Program Files (x86)\Microsoft Office\Office14”)
  2. hold down the right shift key then right click the explorer window and choose Open command window here
  3. now paste this in to the window “runas /netonly /user:REMOTEDOMAIN\USERNAME EXCEL
  4. You will be prompted for a password. Enter it and press the ENTER key
  5. Excel will load.

You will now be able to connect to the Analysis Services on the remote machine with the same details as your excel session will be trusted.

If this has fixed your problem you might like to create a shortcut.

Simply create a standard shortcut to EXCEL and then edit the command to this

C:\Windows\System32\runas.exe /netonly /user:DOMAIN\USER "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE"

 

NOTE: This also works for other programs exhibiting the same behaviour.
The reason this works is that you are allowing the remote server to trust your connection. Obviously you will still need all of the appropriate permissions and ports open etc. so this article has assumed that you have done this already.

Posted in Developer stuff, SQL Stuff | Tagged , , , , , , | 6 Comments