MS SQL Azure – Conditional Computed Column Definition Using CASE

Setting aside for the moment the rights and wrongs of using persisted columns sometimes it is just a great way to add some automation to your database and make it clearer for the user.

But what if you want to add a conditional computed column for rows based on another value within that row. Here’s something I worked out.

ALTER TABLE ProjectManagement
ADD FutureorPast AS CAST
(
CASE
WHEN TargetDate > GetDate() or TargetDate is Null THEN 'FUTURE' WHEN TargetDate <= GetDate() THEN 'PAST'
ELSE ''
END as nvarchar(6)
)
GO

MS SQL Azure – CONCAT_WS Working with addresses and nicely formatting separated fields

I recently came across a very useful function that I am told was introduced in SQL Server 2017 called CONCAT_WS
I have never used it in any other application other than SQL Server I hope it exists in MYSQL and PostgreSQL

This will join together a series of fields with chosen separators and by combining it with NULLIF can be used to remove spaces and format address nicely.
For applications that will at some point need addresses either for post or for information this function allows the display of addresses in a format the most clearly reflects the requirements of most postal systems. Needless to say it is likely that most of my systems will at some point transition to the use of this function.

Here is a link to Microsofts documentation on the function CONCAT_WS

Firstly let me review what I am starting to standardise on with regard to address fields which should be a balance between enough detail to store any address on the planet but not so much that it is overly complicated. I’ve expanded their descriptions somewhat. In my experience someone will have created an excel spreadsheet to start the process of recording information, often they standardise on column names such as address01/02/03 etc. If that breakdown exists then I have indicated the fields that I would normally expect to map those fields too.

flatno-buildingdivision
houseno-buildno
housename-buildname
streetfirst-address01
streetsecond
locality-address02
towncity-address03
stateoradminlocality
postcode-address04
country-address05

And here is an example of the code implemented.

CREATE VIEW v001formattedaddress AS SELECT pkid,
email,
CONCAT_WS(' ',NULLIF(dbo.t001addresstable.firstname,' '), NULLIF(dbo.t001addresstable.surname,' ')) AS fullname,
CONCAT_WS(CHAR(13) + CHAR(10),
NULLIF(dbo.t001addresstable.flatno, ' '), 
NULLIF(dbo.t001addresstable.houseno,' '), 
NULLIF(dbo.t001addresstable.housename,' '),
NULLIF(dbo.t001addresstable.streetfirst,' '),
NULLIF(dbo.t001addresstable.streetsecond,' '),
NULLIF(dbo.t001addresstable.locality,' '),
NULLIF(dbo.t001addresstable.towncity,' '),
NULLIF(dbo.t001addresstable.stateoradminlocality,' '),
NULLIF(dbo.t001addresstable.postcode,' '),
NULLIF(dbo.t001addresstable.country,' '),
REPLICATE('-', 30) ) AS addressconcat FROM dbo.t001addresstable;

Additionally on reflection for a recent project I made up a list of countries that covers most in the world. For my project I put an include field next to them to allow system administrators to include whether these would be visible in the drop down. Clearly while overtime more and more countries may be added there I would expect it to be years or possibly decades before some of the values of the smaller nations are needed. (For my particular application anyway)

I standardised on the following the 2 digit codes are the ISO Country code standard

AD Andorra
AE United Arab Emirates
AF Afghanistan
AG Antigua and Barbuda
AI Anguilla
AL Albania
AM Armenia
AO Angola
AQ Antarctica
AR Argentina
AT Austria
AU Australia
AW Aruba
AX Aland Islands
AZ Azerbaijan
BA Bosnia and Herzegovina
BB Barbados
BD Bangladesh
BE Belgium
BF Burkina Faso
BG Bulgaria
BH Bahrain
BI Burundi
BJ Benin
BL Saint Barthélemy
BM Bermuda
BN Brunei Darussalam
BO Bolivia, Plurinational State of
BQ Bonaire, Sint Eustatius and Saba
BR Brazil
BS Bahamas
BT Bhutan
BV Bouvet Island
BW Botswana
BY Belarus
BZ Belize
CA Canada
CC Cocos (Keeling) Islands
CD Congo, the Democratic Republic of the
CF Central African Republic
CG Congo
CH Switzerland
CI Cote d’Ivoire
CK Cook Islands
CL Chile
CM Cameroon
CN China*
CO Colombia
CR Costa Rica
CU Cuba
CV Cape Verde
CW Curaçao
CX Christmas Island
CY Cyprus
CZ Czech Republic
DE Germany
DJ Djibouti
DK Denmark
DM Dominica
DO Dominican Republic
DZ Algeria
EC Ecuador
EE Estonia
EG Egypt
EH Western Sahara
ER Eritrea
ES Spain
ET Ethiopia
FI Finland
FJ Fiji
FK Falkland Islands (Malvinas)
FO Faroe Islands
FR France
GA Gabon
GB United Kingdom
GB United Kingdom Northern Ireland
GD Grenada
GE Georgia
GF French Guiana
GG Guernsey
GH Ghana
GI Gibraltar
GL Greenland
GM Gambia
GN Guinea
GP Guadeloupe
GQ Equatorial Guinea
GR Greece
GS South Georgia and the South Sandwich Islands
GT Guatemala
GW Guinea-Bissau
GY Guyana
HK Hong Kong SAR China
HM Heard Island and McDonald Islands
HN Honduras
HR Croatia
HT Haiti
HU Hungary
ID Indonesia
IC Spain Canary Islands
IE Ireland Republic
IL Israel
IM Isle of Man
IN India
IO British Indian Ocean Territory
IQ Iraq
IR Iran, Islamic Republic of
IS Iceland
IT Italy
JE Jersey
JM Jamaica
JO Jordan
JP Japan
KE Kenya
KG Kyrgyzstan
KH Cambodia
KI Kiribati
KM Comoros
KN Saint Kitts and Nevis
KP Korea, Democratic People’s Republic of
KR Korea, Republic of
KW Kuwait
KY Cayman Islands
KZ Kazakhstan
LA Lao People’s Democratic Republic
LB Lebanon
LC Saint Lucia
LI Liechtenstein
LK Sri Lanka
LR Liberia
LS Lesotho
LT Lithuania
LU Luxembourg
LV Latvia
LY Libyan Arab Jamahiriya
MA Morocco
MC Monaco
MD Moldova, Republic of
ME Montenegro
MF Saint Martin (French part)
MG Madagascar
MK Macedonia, the former Yugoslav Republic of
ML Mali
MM Myanmar
MN Mongolia
MO Macau SAR China
MQ Martinique
MR Mauritania
MS Montserrat
MT Malta
MU Mauritius
MV Maldives
MW Malawi
MX Mexico
MY Malaysia
MZ Mozambique
NA Namibia
NC New Caledonia
NE Niger
NF Norfolk Island
NG Nigeria
NI Nicaragua
NL Netherlands
NO Norway
NP Nepal
NR Nauru
NU Niue
NZ New Zealand
OM Oman
PA Panama
PE Peru
PF French Polynesia
PG Papua New Guinea
PH Philippines
PK Pakistan
PL Poland
PM Saint Pierre and Miquelon
PN Pitcairn
PS Palestine
PT Portugal
PY Paraguay
QA Qatar
RE Reunion
RO Romania
RS Serbia
RU Russian Federation
RW Rwanda
SA Saudi Arabia
SB Solomon Islands
SC Seychelles
SD Sudan
SE Sweden
SG Singapore
SH Saint Helena, Ascension and Tristan da Cunha
SI Slovenia
SJ Svalbard and Jan Mayen
SK Slovakia
SL Sierra Leone
SM San Marino
SN Senegal
SO Somalia
SR Suriname
SS South Sudan
ST Sao Tome and Principe
SV El Salvador
SX Sint Maarten (Dutch part)
SY Syrian Arab Republic
SZ Swaziland
TC Turks and Caicos Islands
TD Chad
TF French Southern Territories
TG Togo
TH Thailand
TJ Tajikistan
TK Tokelau
TL Timor-Leste
TM Turkmenistan
TN Tunisia
TO Tonga
TR Turkey
TT Trinidad and Tobago
TV Tuvalu
TW Taiwan
TZ Tanzania United Republic of
UA Ukraine
UG Uganda
US United States
UY Uruguay
UZ Uzbekistan
VA Holy See (Vatican City State)
VC Saint Vincent and the Grenadines
VE Venezuela Bolivarian Republic of
VG Virgin Islands, British
VN Vietnam
VU Vanuatu
WF Wallis and Futuna
WS Samoa
YE Yemen
YT Mayotte
ZA South Africa
ZM Zambia
ZW Zimbabwe

MS SQL Azure – Creating contained users – SQL Authentication – DACPAC and BACPAC import

In every database engine it is important to create logins that enforce security around your database and that can be maintained.
Additionally if you are working for a client you may wish to transfer this database at some point in the future to the client.

In SQL Azure users can be created against the master database in the instance and the role can then be transferred to individual databases.

Fine but there may be circumstances where you want to isolate roles to individual databases so that when they are moved the roles move with them and are not left in the master database.
The following sets out an example of how to set up a Contained database in SQL Azure along with something extra you have to think about when re-importing to an SQL Server instance.

Using your sysadmin account connect to the database you wish to add a user to and run;

CREATE USER rocketengineapplication WITH PASSWORD = 'bluedanube';
ALTER ROLE db_owner ADD MEMBER rocketengineapplication;

Now developers could use this password and username to login to the database and do most of what is required without having any privileges to the SQL Server and if you ever transfer the database the role will pass with the database.

Here is a link to built in database roles
SQL Database Roles

Secure a single or pooled database in SQL Azure

and here is a useful query that can be run to identify the users and roles that a particular database has. This allows you to check what users are on a database and what are there roles.

SELECT u.name AS UserName, u.type_desc AS UserType, r.name AS RoleName
FROM sys.database_principals AS u
LEFT JOIN sys.database_role_members AS rm ON rm.member_principal_id = u.principal_id
LEFT JOIN sys.database_principals AS r ON r.principal_id = rm.role_principal_id
WHERE
    u.type NOT IN('R', 'G')
ORDER BY
      UserName
    , RoleName;

Note that when deploying or importing data tier applications to for instance SQL Express versions by default contained database authentication is deactivated and must be activated.

To do this connect to the local sql express instance and highlight the Databases on the left hand side then run the following code

sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO

DACPAC (structure only) and BACPAC (data and structure) import should now be possible locally!

This will specifically assist with the following error message which I was getting when I tried to import the database back into a local machine.

TITLE: Microsoft SQL Server Management Studio
------------------------------

Could not deploy package.
Error SQL72014: .Net SqlClient Data Provider: Msg 12824, Level 16, State 1, Line 5 The sp_configure value 'contained database authentication' must be set to 1 in order to alter a contained database. You may need to use RECONFIGURE to set the value_in_use.
Error SQL72045: Script execution error. The executed script:
IF EXISTS (SELECT 1
FROM [master].[dbo].[sysdatabases]
WHERE [name] = N'$(DatabaseName)')
BEGIN
ALTER DATABASE [$(DatabaseName)]
SET FILESTREAM(NON_TRANSACTED_ACCESS = OFF),
CONTAINMENT = PARTIAL
WITH ROLLBACK IMMEDIATE;
END

Error SQL72014: .Net SqlClient Data Provider: Msg 5069, Level 16, State 1, Line 5 ALTER DATABASE statement failed.
Error SQL72045: Script execution error. The executed script:
IF EXISTS (SELECT 1
FROM [master].[dbo].[sysdatabases]
WHERE [name] = N'$(DatabaseName)')
BEGIN
ALTER DATABASE [$(DatabaseName)]
SET FILESTREAM(NON_TRANSACTED_ACCESS = OFF),
CONTAINMENT = PARTIAL
WITH ROLLBACK IMMEDIATE;
END

(Microsoft.SqlServer.Dac)

MS SQL Azure – Computed Columns

It can be really nice to create a computed column and add it to the table rather than adding it in a field

This would work well using the function listed in the previous post where I automatically calculate the age of trees.

Add Computed Column to SQL Azure Table

ALTER TABLE dbo.t001trees ADD treeage AS (dbo.functionyearmonthday(dbo.t001trees.plantdate, GETDATE()));

This will appear in the table and look like it is an actual field but it is calculated and will not keep the figures in the table unless you specify persistance

see the above link for further reading on this topic

MS SQL Azure – TSQL to name the age between dates in text

It is relatively easy to calculate the number of either years, months days hours or seconds between two dates using the native DATEDIFF built in function which comes with SQL.

e.g.

SELECT dbo.t001trees.pkid, 
dbo.t001trees.plantdate, 
DATEDIFF(Year, dbo.t001trees.plantdate, GETDATE()) as treeage 
from dbo.t001trees;

But here is a function that will spell it out into a string that reads something like
2 days
1 month 2 days
2 years 1 month 2 days

CREATE OR ALTER FUNCTION dbo.functionyearmonthday
(
@datefrom Date,
@dateto Date
)
RETURNS varchar(100)
as
BEGIN
DECLARE @date1 DATETIME, @date2 DATETIME, @result VARCHAR(100);
DECLARE @years INT, @months INT, @days INT;

SET @date1 = @datefrom
SET @date2 = @dateto

SELECT @years = DATEDIFF(yy, @date1, @date2)
IF DATEADD(yy, -@years, @date2) < @date1
SELECT @years = @years-1
SET @date2 = DATEADD(yy, -@years, @date2)

SELECT @months = DATEDIFF(mm, @date1, @date2)
IF DATEADD(mm, -@months, @date2) < @date1
SELECT @months=@months-1
SET @date2= DATEADD(mm, -@months, @date2)

SELECT @days=DATEDIFF(dd, @date1, @date2)
IF DATEADD(dd, -@days, @date2) < @date1
SELECT @days=@days-1
SET @date2= DATEADD(dd, -@days, @date2)

SELECT @result= ISNULL(CAST(NULLIF(@years,0) AS VARCHAR(10)) + ' years ','')
+ ISNULL(' ' + CAST(NULLIF(@months,0) AS VARCHAR(10)) + ' months ','')
+ ISNULL(' ' + CAST(NULLIF(@days,0) AS VARCHAR(10)) + ' days','')

RETURN @result;
END

And if you would like to call the function from another query here is an example

SELECT dbo.functionyearmonthday(dbo.t001trees.plantdate, GETDATE()) as treeage FROM dbo.t001trees

That is enough for most people but it can be expanded to include hours minutes seconds and milliseconds which could be useful if you need more precision it can be seen that the DATEDIFF native function is included extensively within this function.

CREATE OR ALTER FUNCTION dbo.functiontimeperiodmoreprecision
(
@datefrom Date,
@dateto Date
)
RETURNS varchar(100)
as
BEGIN
DECLARE @date1 DATETIME, @date2 DATETIME, @result VARCHAR(100);
DECLARE @years INT, @months INT, @days INT,
@hours INT, @minutes INT, @seconds INT, @milliseconds INT;

SET @date1 = @datefrom
SET @date2 = @dateto

SELECT @years = DATEDIFF(yy, @date1, @date2)
IF DATEADD(yy, -@years, @date2) < @date1
SELECT @years = @years-1
SET @date2 = DATEADD(yy, -@years, @date2)

SELECT @months = DATEDIFF(mm, @date1, @date2)
IF DATEADD(mm, -@months, @date2) < @date1
SELECT @months=@months-1
SET @date2= DATEADD(mm, -@months, @date2)

SELECT @days=DATEDIFF(dd, @date1, @date2)
IF DATEADD(dd, -@days, @date2) < @date1
SELECT @days=@days-1
SET @date2= DATEADD(dd, -@days, @date2)

SELECT @hours=DATEDIFF(hh, @date1, @date2)
IF DATEADD(hh, -@hours, @date2) < @date1
SELECT @hours=@hours-1
SET @date2= DATEADD(hh, -@hours, @date2)

SELECT @minutes=DATEDIFF(mi, @date1, @date2)
IF DATEADD(mi, -@minutes, @date2) < @date1
SELECT @minutes=@minutes-1
SET @date2= DATEADD(mi, -@minutes, @date2)

SELECT @seconds=DATEDIFF(s, @date1, @date2)
IF DATEADD(s, -@seconds, @date2) < @date1
SELECT @seconds=@seconds-1
SET @date2= DATEADD(s, -@seconds, @date2)

SELECT @milliseconds=DATEDIFF(ms, @date1, @date2)

SELECT @result= ISNULL(CAST(NULLIF(@years,0) AS VARCHAR(10)) + ' years,','')
+ ISNULL(' ' + CAST(NULLIF(@months,0) AS VARCHAR(10)) + ' months,','')
+ ISNULL(' ' + CAST(NULLIF(@days,0) AS VARCHAR(10)) + ' days,','')
+ ISNULL(' ' + CAST(NULLIF(@hours,0) AS VARCHAR(10)) + ' hours,','')
+ ISNULL(' ' + CAST(@minutes AS VARCHAR(10)) + ' minutes and','')
+ ISNULL(' ' + CAST(@seconds AS VARCHAR(10))
+ CASE
WHEN @milliseconds > 0
THEN '.' + CAST(@milliseconds AS VARCHAR(10))
ELSE ''
END
+ ' seconds','')

RETURN @result
END

MS SQL Azure – Group child records by parent in order

Lets say we have two tables

A site parent table T001 consisting of two fields
PKID
Sitename

And a planning application child table T002 consisting of three fields
PKID
PKIDT001
PlanRef

We have the following records in the parent table T001
1 – Site01Green
2 – Site02Red
3 – Site03Blue

And the following related children in the planning application table T002;
1 – 1 – 2019/098
1 – 1 – 2018/032
1 – 2 – 2017/987
1 – 3 – 2015/100
1 – 3 – 2014/456
1 – 3 – 2014/657

And we would like to write a query that would create a list combining the two tables showing all the planning applications for each site listed in order.

This is the list we are aiming at
1 – 2018/032,2019/098
2 – 2017/987
3 – 2014/456,2014/657,2015/100

Using SQL Server 2017 or later (including SQL Azure) the following will work

CREATE VIEW [dbo].[View01Sitesandtheirplanapps] AS SELECT PKIDT001, STRING_AGG(PlanRef,',') WITHIN GROUP (ORDER by PlanRef ASC) as PlanRefs
From dbo.T002
GROUP BY PKIDT001;

This can then be combined in a separate view that will include the sitename should it be required – this is a tremendous example the structure of which I will be using often.

SQL SERVER – Create TSQL Trigger to add date to existing record after INSERT or UPDATE

I came across an interesting issue with a web application I have been trying to create. DEFAULTS in SQL Server didn’t seem to be registering when new records were inserted through the web application.

How come? Surely the web application is not bypassing requirements set out by the database.

What I believe is happening is that the web application is explicitly supplying a null value to the dateupdated field and because of this the default value is NOT applied by SQL Server. You are therefore in the strange position of creating a trigger on a column after insert. Normally you would never really do this as that’s specifically what default is for.

Here’s code to create the trigger on the SQL Server that will do just this.

CREATE TRIGGER TR_moddate
On T0001PersonData
AFTER INSERT, UPDATE
AS
BEGIN
UPDATE T0001PersonData SET DATEUPDATED=GETDATE()
WHERE PKID IN (SELECT PKID FROM INSERTED)
END

Some explanation – This updates the field DateUpdated within the table T0001PersonData after an update or an insert. If you have default on a column this may mean that it updates to default then to null then to trigger so you may wish to remove the default on the column. Alternatively you may wish to get the front end to update to the current date. I guess this could have performance issues at scale but my application is pretty small beer so I am happy with this at the moment. I think I prefer to have it at database level as well. It should be noted that INSERTED is actually a ALIAS table created by SQL Server and held in memory that allows for reference to pull out items before they are registered in the database. Leaving out the where clause may have resulted in the application updating the dateupdated column for all records anytime an update or insert was performed. BE WARNED

MS SQL Azure – SQL to Select Distinct list of Child Records based on a Maximum or Minimum Child Field Value

Following on from the previous post I wanted to know the syntax for SQL to do the same but in SQL Azure.

SELECT * FROM dbo.T02AttendanceGame T1    
WHERE Attendance = (
   SELECT max(Attendance)
   FROM dbo.T02AttendanceGame T2
   WHERE T1.FKID=T2.FKID
);

I created the same tables that I created for the MS Access example with the same field names but within a SQL Azure database.

Here’s the same SQL but then creating a view called rather unimaginatively ‘View01’

CREATE VIEW VIEW01 AS SELECT * FROM dbo.T02AttendanceGame T1    
WHERE Attendance = (
   SELECT max(Attendance)
   FROM dbo.T02AttendanceGame T2
   WHERE T1.FKID=T2.FKID
);

MS SQL Azure – Take Complete Backup of Azure Database (Structure and Data)

SQL Azure as part of the service offers a number of differing back up options as standard – however you may wish to take additional backups which for instance you can load onto a local version of SQL Server (Express or Enterprise). Here’s a quick rundown of one method of doing this.

Firstly open up your version of SQL Server Management Studio and navigate to the instance and database that you wish to backup

Highlight the database in this case DB001 and right click -Select Tasks and Export Data-Tier Application

Choose an appropriate location to put the backup file

Review the summary of items that will be backed up and then hit Finish

There will be a run down of how the export has worked

And just a check to see the exported file in the directory

For more information on SQL Azure see here
Mikhail Shilkov Azure and programming blog

Upload Shape Files into SQL Azure using OGR2OGR – Explanation of MS SQL Azure Connection String to be placed within OGR2OGR Command Line

Lets say you have a SQL Azure Server with the following parameters

SQL Azure Instance : azureinstance1
Database name within Instance : TouristDB1
Your User Name is : tom
Password is : Edinburgh

The SQLAzure connection string would be
MSSQL:Server=tcp:azureinstance1.database.windows.net;Database=TouristDB1;
Uid=tom@azureinstance1.database.windows.net;Pwd=Edinburgh;

and the full OGR2OGR to import Command Line Instruction for a shape file called Monuments.shp would be..

ogr2ogr -overwrite -f MSSQLSpatial "MSSQL:Server=tcp:azureinstance1.database.windows.net;Database=TouristDB1;
Uid=tom@azureinstance1.database.windows.net;Pwd=Edinburgh;" "C:\Monuments.shp"

VBA Function to Create Table of Import strings using OGR2OGR targeting a SQL Server

Do you have many shape files you wish to import into a local SQL Server Database so that you can display them in QGIS or serve them on Geoserver?
Here’s a short function I wrote that will take a table called T0001OpenStreetMapLayers with fields PKID/Name/Directory/Type/Flag – and produce OGR strings that can then be used to load them into a local SQL Server / SQL Express or SQL Azure

For this to be useful you will need
A version of QGIS
A local SQL Server copy (in this case SQL Server Express)
A database within your copy called OpenStreetMap
All shape files in the same directory
You will also need to figure out how to get all those shape files into the table T0001OpenStreetMapLayers table
A starting database with 2 tables
T0001OpenStreetMapLayers with populated fields PKID/Name/Directory/Type/Flag
T0002OGRStrings blank table with fields PKID/CommandLine – This is where all the Command Line Strings will be stored

Public Function CreateTableOGR2OGRString()

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim db As DAO.Database
Dim O2O As String
Dim LCounter As Integer
Dim strQuote As String
Set db = CurrentDb
strQuote = Chr$(34)


LCounter = 1
While LCounter < 3000
LCounter = LCounter < 3000

Set rs1 = CurrentDb.OpenRecordset("SELECT T0001OpenStreetMapLayers.PKID, T0001OpenStreetMapLayers.Name, T0001OpenStreetMapLayers.Directory, T0001OpenStreetMapLayers.Type, T0001OpenStreetMapLayers.Flag FROM T0001OpenStreetMapLayers WHERE (((T0001OpenStreetMapLayers.Type)=1) AND ((T0001OpenStreetMapLayers.Flag)=0 Or (T0001OpenStreetMapLayers.Flag) Is Null));")
O2O = "ogr2ogr -append -f MSSQLSpatial " & strQuote & "MSSQL:server=DESKTOP-JECT7QO\SQLEXPRESS;database=OpenStreetMap;trusted_connection=yes" & strQuote & " " & strQuote & rs1!Directory & rs1!Name & ".shp" & strQuote & ""


rs1.Edit
rs1!Flag = 1
rs1.Update
rs1.MoveNext
rs1.Close

Set rs2 = CurrentDb.OpenRecordset("T0002OGRStrings")
With rs2
.AddNew
rs2!CommandLine = O2O
rs2.Update
rs2.Close
End With
Wend
End Function

For SQL Azure target databases replace the yellow connection string with something resembling;

MSSQL:Server=tcp:azureinstance1.database.windows.net;Database=TouristDB1;
Uid=tom@azureinstance1.database.windows.net;Pwd=Edinburgh;

There are multiple methods of finding the name of your SQL Instance – Ignoring the fact that you won’t be able to connect to it if you don’t know it – Within SSMS you can right click on the instance and look to properties but the name itself is usually in the instance path of SSMS as well.

MS SQL Azure to MS Access – Using VBA to Dump Azure Tables into MS Access Tables

The first thing you need to get sorted when moving to SQL Azure is having the ability to get your information out and safe if needs be. When experimenting with MS Azure and for applications that don’t have sensitive information it is nice to have that information available in an easily accessible format. Here are a series of functions that will copy Azure Tables linked to database into local MS Access tables with the prefix ZCOPY.

The starting point in this should be an MS Access database that should be linked to your SQL Azure Database. Only those tables that are linked will be copied. Remember the 2GB limit on Access.

I think I have got all the functions here that are required to make it work and include the complete module at the bottom but first I will breakdown the modules and list describe what each of the functions do.

First create a table to store the list of tables in the Azure Database

Public Function CreateTableT0001AzureTablesGlobal()
 
     Dim dbs As Database
     Set dbs = CurrentDb
 
        dbs.Execute "CREATE TABLE T0001AzureTablesGlobal " _
        & "(PKID AUTOINCREMENT, " _
        & "AzureTableName CHAR CONSTRAINT PKID " _
        & "PRIMARY KEY);"
   
End Function

Now Create a Function that will hold the SQL that takes the tables and makes them locally.

Public Function CreateTableT0002SQL()
 
     Dim dbs As Database
     Set dbs = CurrentDb
 
        dbs.Execute "CREATE TABLE T0002SQL " _
        & "(PKID AUTOINCREMENT, " _
        & "SQL MEMO CONSTRAINT PKID " _
        & "PRIMARY KEY);"
 
   
End Function

A function that allows for stepping through the table

Public Function AddByteColumn(TblName As String, FieldName As String)
'Just use byte data type as only going to use this for a flag

DoCmd.RunSQL "AlTER TABLE [" & TblName & "] ADD COLUMN " & FieldName & " BYTE;"

End Function

Step through the Linked Azure Tables and poupulate table T001 with their names

Public Function CreateandPopulateListofDBOTableNames()

'These will typically be the names of the SQL Server tables this should work both with SQL Server and SQL Azure

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim rstList As DAO.Recordset

'Call CreateTableT0001AzureTablesGlobal

Set rstList = CurrentDb.OpenRecordset("T0001AzureTablesGlobal")
Set db = CurrentDb

For Each tdf In db.TableDefs
    ' ignore system and temporary tables and tables starting with T - personal choice option
    If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*" Or tdf.Name Like "T*") Then
       With rstList
       .AddNew
       rstList!AzureTableName = tdf.Name
       rstList.Update
      End With
    End If
    
Next

Set tdf = Nothing
Set db = Nothing

End Function

The next function is required to strip out additional spaces in names

Public Function FindXReplaceY(FixTable As String, FixColumn As String, X As String, Y As String) As Variant

Dim strSQL As String

strSQL = "UPDATE [" & FixTable & "] SET [" & FixTable & "].[" & FixColumn & "] = REPLACE([" & FixColumn & "]," & Chr$(34) & X & Chr$(34) & "," & Chr$(34) & Y & """);"

DoCmd.RunSQL strSQL

End Function

We can now write the VBA that will write the make table SQL that once run will put one make table query into the maketableSQL table for each Azure table.

Public Function CreateMakeTableSQL()

On Error GoTo Err_CreateMakeTableSQL
Dim rstSQL As DAO.Recordset
Dim rstSQLx As DAO.Recordset
Dim dbc As DAO.Database
Dim SQLStringAdd As String
Dim LCounter As Long

Set dbc = CurrentDb

LCounter = 1
While LCounter < 9000
LCounter = LCounter + 1
Set rstSQL = CurrentDb.OpenRecordset("SELECT T0001AzureTablesGlobal.PKID, T0001AzureTablesGlobal.AzureTableName, T0001AzureTablesGlobal.XFLag1 FROM T0001AzureTablesGlobal WHERE (((T0001AzureTablesGlobal.XFLag1) Is Null));")

SQLStringAdd = "SELECT * INTO COPY" & rstSQL!AzureTableName & " FROM " & rstSQL!AzureTableName & ";"

Set rstSQLx = CurrentDb.OpenRecordset("T0002SQL")
With rstSQLx
.AddNew
rstSQLx!SQL = SQLStringAdd
rstSQLx.Update
rstSQLx.Close
End With

With rstSQL
rstSQL.Edit
rstSQL!XFLag1 = 1
rstSQL.Update
rstSQL.MoveNext
rstSQL.Close
End With

Wend

Exit_CreateMakeTableSQL:
    Exit Function

Err_CreateMakeTableSQL:
Select Case Err.Number
 Case 3021
   Resume Exit_CreateMakeTableSQL
  Case Else
  Resume Exit_CreateMakeTableSQL
  End Select
 
End Function

And finally Run all the queries

Public Function RunQueriesFromTable2(SQLSource As String)

DoCmd.SetWarnings False

Dim StartTime As Date
Dim EndTime As Date
Dim rstZ As DAO.Recordset
Dim strSQL2 As String

StartTime = Now()

Set rstZ = CurrentDb.OpenRecordset(SQLSource)

Do Until rstZ.EOF

strSQL2 = rstZ!SQL
DoCmd.RunSQL strSQL2
rstZ.MoveNext

Loop

DoCmd.SetWarnings True

EndTime = Now()

MsgBox "Finished ALL SQL queries! Process started at " & StartTime & " and finished at " & EndTime

End Function

And a script to pull all of this together

Public Function GetAzureScript()

DoCmd.SetWarnings False
Call CreateTableT0001AzureTablesGlobal
Call CreateandPopulateListofDBOTableNames
Call FindXReplaceY("T0001AzureTablesGlobal", "AzureTablename", " ", "")
Call FindXReplaceY("T0001AzureTablesGlobal", "AzureTablename", Chr(10), "")
Call AddByteColumn("T0001AzureTablesGlobal", "XFLag1")
Call CreateTableT0002SQL
Call CreateMakeTableSQL
Call FindXReplaceY("T0002SQL", "SQL", " ", "")
Call FindXReplaceY("T0002SQL", "SQL", Chr(10), "")
Call FindXReplaceY("T0002SQL", "SQL", "SELECT*INTOCOPY", "SELECT * INTO ZCOPY")
Call FindXReplaceY("T0002SQL", "SQL", "FROM", " FROM ")
Call RunQueriesFromTable("T0002SQL")
DoCmd.SetWarnings True

End Function

The complete module

Option Compare Database
Option Explicit

Public Function GetAzureScript()

DoCmd.SetWarnings False
Call CreateTableT0001AzureTablesGlobal
Call CreateandPopulateListofDBOTableNames
Call FindXReplaceY("T0001AzureTablesGlobal", "AzureTablename", " ", "")
Call FindXReplaceY("T0001AzureTablesGlobal", "AzureTablename", Chr(10), "")
Call AddByteColumn("T0001AzureTablesGlobal", "XFLag1")
Call CreateTableT0002SQL
Call CreateMakeTableSQL
Call FindXReplaceY("T0002SQL", "SQL", " ", "")
Call FindXReplaceY("T0002SQL", "SQL", Chr(10), "")
Call FindXReplaceY("T0002SQL", "SQL", "SELECT*INTOCOPY", "SELECT * INTO ZCOPY")
Call FindXReplaceY("T0002SQL", "SQL", "FROM", " FROM ")
Call RunQueriesFromTable2("T0002SQL")
DoCmd.SetWarnings True

End Function

Public Function CreateandPopulateListofDBOTableNames()

'These will typically be the names of the SQL Server tables this should work both with SQL Server and SQL Azure

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim rstList As DAO.Recordset

'Call CreateTableT0001AzureTablesGlobal

Set rstList = CurrentDb.OpenRecordset("T0001AzureTablesGlobal")
Set db = CurrentDb

For Each tdf In db.TableDefs
    ' ignore system and temporary tables and tables starting with T - personal choice option
    If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*" Or tdf.Name Like "T*") Then
       With rstList
       .AddNew
       rstList!AzureTableName = tdf.Name
       rstList.Update
      End With
    End If
    
Next

Set tdf = Nothing
Set db = Nothing

End Function

Public Function FindXReplaceY(FixTable As String, FixColumn As String, X As String, Y As String) As Variant

Dim strSQL As String

strSQL = "UPDATE [" & FixTable & "] SET [" & FixTable & "].[" & FixColumn & "] = REPLACE([" & FixColumn & "]," & Chr$(34) & X & Chr$(34) & "," & Chr$(34) & Y & """);"

DoCmd.RunSQL strSQL

End Function

Public Function CreateTableT0001AzureTablesGlobal()
 
     Dim dbs As Database
     Set dbs = CurrentDb
 
        dbs.Execute "CREATE TABLE T0001AzureTablesGlobal " _
        & "(PKID AUTOINCREMENT, " _
        & "AzureTableName CHAR CONSTRAINT PKID " _
        & "PRIMARY KEY);"
 
   
End Function

Public Function CreateTableT0002SQL()
 
     Dim dbs As Database
     Set dbs = CurrentDb
 
        dbs.Execute "CREATE TABLE T0002SQL " _
        & "(PKID AUTOINCREMENT, " _
        & "SQL MEMO CONSTRAINT PKID " _
        & "PRIMARY KEY);"
 
   
End Function

Public Function AddByteColumn(TblName As String, FieldName As String)
'Just use byte data type as only going to use this for a flag

DoCmd.RunSQL "AlTER TABLE [" & TblName & "] ADD COLUMN " & FieldName & " BYTE;"

End Function

Public Function CreateMakeTableSQL()

On Error GoTo Err_CreateMakeTableSQL
Dim rstSQL As DAO.Recordset
Dim rstSQLx As DAO.Recordset
Dim dbc As DAO.Database
Dim SQLStringAdd As String
Dim LCounter As Long

Set dbc = CurrentDb

LCounter = 1
While LCounter < 9000
LCounter = LCounter + 1
Set rstSQL = CurrentDb.OpenRecordset("SELECT T0001AzureTablesGlobal.PKID, T0001AzureTablesGlobal.AzureTableName, T0001AzureTablesGlobal.XFLag1 FROM T0001AzureTablesGlobal WHERE (((T0001AzureTablesGlobal.XFLag1) Is Null));")
SQLStringAdd = "SELECT * INTO COPY" & rstSQL!AzureTableName & " FROM " & rstSQL!AzureTableName & ";"

Set rstSQLx = CurrentDb.OpenRecordset("T0002SQL")
With rstSQLx
.AddNew
rstSQLx!SQL = SQLStringAdd
rstSQLx.Update
rstSQLx.Close
End With

With rstSQL
rstSQL.Edit
rstSQL!XFLag1 = 1
rstSQL.Update
rstSQL.MoveNext
rstSQL.Close
End With

Wend

Exit_CreateMakeTableSQL:
    Exit Function

Err_CreateMakeTableSQL:
Select Case Err.Number
 Case 3021
   Resume Exit_CreateMakeTableSQL
  Case Else
  Resume Exit_CreateMakeTableSQL
  End Select
 
End Function

Public Function RunQueriesFromTable2(SQLSource As String)

DoCmd.SetWarnings False

Dim StartTime As Date
Dim EndTime As Date
Dim rstZ As DAO.Recordset
Dim strSQL2 As String

StartTime = Now()

Set rstZ = CurrentDb.OpenRecordset(SQLSource)

Do Until rstZ.EOF

strSQL2 = rstZ!SQL
DoCmd.RunSQL strSQL2
rstZ.MoveNext

Loop

DoCmd.SetWarnings True

EndTime = Now()

MsgBox "Finished ALL SQL queries! Process started at " & StartTime & " and finished at " & EndTime

End Function

MS Access front end – MS SQL Azure back end Link to Configuration Set UP FMS Group

This is an excellent article on linking MS Access to SQL Azure – which is just great if you are wanting to use your VBA skills direct on your SQL Database.

FMS Professional Solutions Group – Luke Chung MVP article

It should be noted that the Primary Key will need to be set to Integer in the SQL Database rather than Big Int otherwise it will appear as #DELETED# when you go in and view the data. This may not be an issue going forward for some users of Office 365 Access 2016 as Microsoft are introducing support for Big Int in MS Access. Congratulations to Microsoft on that small but important change.

Upload XLS information to a specific table or new table within an SQL Azure Database

I have started experimenting with Microsoft Azure if you haven’t already you can get a free experimentation account here;

Microsoft Azure Trial account with £125 credit

This gets your registered on Microsoft’s cloud and after a free trial period you will be able to continue with a Pay as You go Account which depending on the services that you go for can start at very cheap rates.

In order for this to work you will need the following
1) Microsoft Azure account
2) An SQL Azure Database
3) SQL Server Management Studio downloaded and installed on the machine you will be uploading from, this can be obtained from SSMS download link be warned its over 800mb. Here I use SSMS 2016
4) Know your server name this is generally [Yourname].database.windows.net
5) Login and Password (I use SQL Server Authentication)

Testing things out I have been using the Web Apps Service to run a website and connect to an SQL Azure Database – both on the cheapest options.

What makes the website particularly cheap is that it can be stopped and started and by paying for it by the minute you can really get a powerful demonstration sites up and running and stop them immediately after the demonstration for very little money.

So after having created an Azure SQL database (Microsoft Create Azure Database Tutorial)I wanted to get a decent number of records into it. Which would be the starting position when taking on most work.
Here I use the Lichfield Planning Application information previously referred to in this post QGIS Import. What I did was take the 45,000 records of planning applications from the shape file. I did this by opening up the dbf file of the shape collection in Excel 2003 and then saving it in excel format. This will be used later to import into the database.

Having your excel file ready the next step is to open up SQL Server Management Studio and connect to your Azure Database. The parameters with regard to username and servername will have been setup when you created your Azure database it is important that when you create your Azure database you somehow record these details.

Next highlight the database – in my case this was DB001 and right click to get tasks.

At this point you enter the import wizard windows dialog boxes and having passed the opening welcome screen , a screen that can be turned off for subsequent navigations, you should hit your first screen that allows you to define the format of the file that should be imported.

The next step is about the only one that is slightly confusing – you are given a number of different options for the target – for me SQL Server Native Client 11.0 worked for me.

Now using the previous parameters specific to your database server and your database name complete the next dialog.

The next dialog asks you whether you want to copy all information or want to write a query to filter the information to be imported. For my example I chose the all import item. Here I select the database and then I am able to see the from and too destinations.

If you wish to import into an existing table use the drop down to select tables from the database – if you wish to import into a new table you can type in the name of the new table within the square brackets.

Here I create a new table called T010Test and import and then continue through the import wizard dialogs until on completion of import you should see a similar screen to that below. It is possible to go into the edit mappings if you are copying into a table that already exists. This will give you a preview showing to what extent the mapping will be successful and how the fields map. You may wish to alter the names of columns to match your target table at this point.

Microsoft Azure – moving to the cloud

I must confess I do love MS Access and I have a number of projects that I suspect will always remain as are – these are ones that are highly complex but very personal that I really don’t need to share. Even some projects that because of their sensitivity I would never want to share but despite the difficulty of designing really user friendly user interfaces on the web the ability to distribute your applications to everyone in the world is a very powerful attractor which is quite clearly going to be a game changer.

I have therefore started to experiment with data driven online applications and so far I am impressed.

To get started you will first want to sign up with an Azure account.
Azure Portal Sign In

I can see in the future I will probably be using Access as a desktop platform for writing queries and personal content curation with SQL Azure for projects where I need to communicate with others linked through the apps service in the Azure portal.