Category: SSMS

SQL Azure – a short note on BAK and Bacpac files

I note that for local SQL Express and I believe enterprise SQL Server there is the additional option of creating a Backup in SQL Express. I also note that from my reading Baks are considered preferable to bacpac files because they have enforced ACID compliance and that for large databases that are constantly being used they are recommended to bacpac files. SQL Azure doesn’t allow BAK file backups through SSMS from what I can see so if this is an issue for you consider temporarily disconnecting front ends from the database while a bacpac is conducted. If you need a bak file for some reason you can attach locally to a SQL Server instance and from there take a bak file.

Something to be aware of..

See this link for further information
Blobeater blog post

ASP Net Runner – Run a SQL Azure Stored Procedure from the web client

Firstly create the stored procedure you wish to run in Azure SQL

Here’s a simple procedure which triggers and update query that inserts records into table 34 from a view based on a flag in the table saying transform is 1 it then runs another query to set the transform field to 0 to prevent the insert happening again the next time.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [dbo].[transformemail]
AS 
SET NOCOUNT ON;
INSERT INTO t034 (pkid,body1,eplanno,money4,trandate,payref,wlcref,tranid,email,updated)
SELECT pkid,body1,eplanno,money4,trandate,payref,wlcref,tranid,email,updated 
FROM v031;
UPDATE t032email
SET transform = 0
WHERE t032email.transform = 1;
GO

Firstly navigate to the form you wish to put the code into and insert a button into that form.

Next using your mouse select the button and on the right hit the button marked Edit button code

This reveals the Button Properties window dialogue

Please note there are three tabs at the top here marked

Client Before Server and Client After – we will be placing code in all of them so take note

In the Client Before tab I enter.

params["txt"] = "Transformation";
ajax.setMessage("Sending request to server...");
 // Uncomment the following line to prevent execution of "Server" and "Client After" events.
 // return false;

In the Server tab I enter

tDAL.CustomQuery ("EXEC dbo.transformemail");
result["txt"] = parameters["txt"].ToString() + " complete";

And in the Client after tab I enter

// Put your code here.
var message = result["txt"] + " !!!";
ajax.setMessage(message);

Now build the project and when you hit the button on the web client your procedure will be run on the server.

SQL Azure – TSQL Script – Address table

Designed to be used in conjunction with previous post Persons table. Will allow for multiple addresses for individuals. Country field to draw from Country table but I would not foreign key the value to country table identity but reference it to the actual name. Personal naming convention to name foreign keys after the pkid[tablename] I find that works well for me and seems very logical. I have a large system at work which simply names foreign keys fkid can be difficult to find what table it refers to resulted in me thinking hard about a naming convention for foreign keys.
I like pkid[tableprefix]. I usually leave it at that have expanded below because its a single script and may or may not be used with an actual persons t0001 table.

/****** Object:  Table [dbo].[t0002address]    Script Date: 01/12/2020 08:12:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[t0002address](
	[pkid] [int] IDENTITY(1,1) NOT NULL,
	[flatfloorno] [nvarchar](20) NULL,
	[housebuildingno] [nvarchar](20) NULL,
	[streetname] [nvarchar](50) NULL,
	[throughstreet] [nvarchar](50) NULL,
	[locality] [nvarchar](50) NULL,
	[towncity] [nvarchar](50) NULL,
	[districtcountyregionstate] [nvarchar](50) NULL,
	[country] [nvarchar](50) NULL,
	[postcode] [nvarchar](50) NULL,
	[dateentry] [date] NULL,
	[dateexit] [date] NULL,
	[currenta] [bit] NULL,
	[currentset] [date] NULL,
	[pkidt0001people] [int] NULL,
	[updated] [date] NULL,
	[created] [date] NULL,
 CONSTRAINT [PK_t0002address] PRIMARY KEY CLUSTERED 
(
	[PKID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[t0002address] ADD  CONSTRAINT [DF_t0002address_updated]  DEFAULT (getdate()) FOR [updated]
GO

ALTER TABLE [dbo].[t0002address] ADD  CONSTRAINT [DF_t0002address_created]  DEFAULT (getdate()) FOR [created]
GO

SQL Azure – TSQL Script – Persons table

A TSQL script to create a table to act as list of customers , application user list and for lookups for particular lookups.

The automatestart and automatefinish will allow the automation of entry to an application starting and ending on specific dates.

/****** Object:  Table [dbo].[t0001people]    Script Date: 29/11/2020 12:39:05 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[t0001people](
    [pkid] [int] IDENTITY(1,1) NOT NULL,
    [title] [nvarchar](10) NULL,
    [firstname] [nvarchar](50) NULL,
    [middlename] [nvarchar](50) NULL,
    [surname] [nvarchar](50) NULL,
    [gender] [nvarchar] (1) NULL,
    [dateofbirth] [date] NULL,
    [username] [nvarchar](50) NULL,
    [email01] [nvarchar](100) NULL,
    [email02] [nvarchar](100) NULL,
    [mobile01] [nvarchar](20) NULL,
    [mobile01intcode] [nvarchar](4) NULL,
    [mobile02] [nvarchar](20) NULL,
    [mobile02intcode] [nvarchar](4) NULL,
    [landline] [nvarchar](20) NULL,
    [userpassword] [nvarchar](50) NULL,
    [clientlk] [bit] NULL,
    [userlk] [bit] NULL,
    [securitypermission] [nvarchar](50) NULL,
    [automatestart] [date] NULL,
    [automatefinish] [date] NULL,
    [nationalinsuranceno] [nvarchar](20) NULL,
    [pkidt0001mother] [int] NULL,
    [pkidt0001father] [int] NULL,
    [updated] [datetime] NULL,
    [created] [datetime] NULL,
 CONSTRAINT [PK_t0001people] PRIMARY KEY CLUSTERED 
(
    [pkid] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[t0001people] ADD  CONSTRAINT [DF_t0001people_updated]  DEFAULT (getdate()) FOR [updated]
GO
 
ALTER TABLE [dbo].[t0001people] ADD  CONSTRAINT [DF_t0001people_created]  DEFAULT (getdate()) FOR [created]
GO

SQL Azure – TSQL Script – Country File Lookup Table (with data accurate November 2020)

I’ve written about this in a previous post. I had a client that has customers all over the world and I wanted to align and enforce validation of the correct countrys as stock and product may need to be posted. Its unusual to have an attribute that is both finite and universal between systems – The list is taken from the international recognized ISOcode standard.(November 2020)

I use this as a lookup field in addresses for people

/****** Object:  Table [dbo].[t039country]    Script Date: 29/11/2020 11:41:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[t039country](
	[pkid] [int] IDENTITY(1,1) NOT NULL,
	[isocode] [nvarchar](3) NULL,
	[country] [nvarchar](50) NULL,
	[include] [bit] NULL,
 CONSTRAINT [PK_t039country] PRIMARY KEY CLUSTERED 
(
	[pkid] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[t039country] ON 

INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (1, N'AD', N'Andorra', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (2, N'AE', N'United Arab Emirates', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (3, N'AF', N'Afghanistan', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (4, N'AG', N'Antigua and Barbuda', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (5, N'AI', N'Anguilla', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (6, N'AL', N'Albania', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (7, N'AM', N'Armenia', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (8, N'AO', N'Angola', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (9, N'AQ', N'Antarctica', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (10, N'AR', N'Argentina', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (11, N'AT', N'Austria', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (12, N'AU', N'Australia', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (13, N'AW', N'Aruba', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (14, N'AX', N'Aland Islands', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (15, N'AZ', N'Azerbaijan', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (16, N'BA', N'Bosnia and Herzegovina', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (17, N'BB', N'Barbados', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (18, N'BD', N'Bangladesh', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (19, N'BE', N'Belgium', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (20, N'BF', N'Burkina Faso', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (21, N'BG', N'Bulgaria', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (22, N'BH', N'Bahrain', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (23, N'BI', N'Burundi', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (24, N'BJ', N'Benin', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (25, N'BL', N'Saint Barts', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (26, N'BM', N'Bermuda', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (27, N'BN', N'Brunei Darussalam', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (28, N'BO', N'Bolivia Plurinational State of', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (29, N'BQ', N'Bonaire Sint Eustatius and Saba', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (30, N'BR', N'Brazil', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (31, N'BS', N'Bahamas', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (32, N'BT', N'Bhutan', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (33, N'BV', N'Bouvet Island', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (34, N'BW', N'Botswana', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (35, N'BY', N'Belarus', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (36, N'BZ', N'Belize', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (37, N'CA', N'Canada', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (38, N'CC', N'Cocos (Keeling) Islands', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (39, N'CD', N'Congo the Democratic Republic of the', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (40, N'CF', N'Central African Republic', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (41, N'CG', N'Congo', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (42, N'CH', N'Switzerland', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (43, N'CI', N'Cote dIvoire', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (44, N'CK', N'Cook Islands', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (45, N'CL', N'Chile', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (46, N'CM', N'Cameroon', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (47, N'CN', N'China', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (48, N'CO', N'Colombia', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (49, N'CR', N'Costa Rica', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (50, N'CU', N'Cuba', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (51, N'CV', N'Cape Verde', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (52, N'CW', N'Curacao', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (53, N'CX', N'Christmas Island', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (54, N'CY', N'Cyprus', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (55, N'CZ', N'Czech Republic', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (56, N'DE', N'Germany', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (57, N'DJ', N'Djibouti', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (58, N'DK', N'Denmark', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (59, N'DM', N'Dominica', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (60, N'DO', N'Dominican Republic', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (61, N'DZ', N'Algeria', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (62, N'EC', N'Ecuador', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (63, N'EE', N'Estonia', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (64, N'EG', N'Egypt', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (65, N'EH', N'Western Sahara', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (66, N'ER', N'Eritrea', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (67, N'ES', N'Spain', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (68, N'ET', N'Ethiopia', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (69, N'FI', N'Finland', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (70, N'FJ', N'Fiji', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (71, N'FK', N'Falkland Islands (Malvinas)', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (72, N'FO', N'Faroe Islands', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (73, N'FR', N'France', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (74, N'GA', N'Gabon', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (75, N'GB', N'United Kingdom', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (76, N'GB', N'United Kingdom Northern Ireland', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (77, N'GD', N'Grenada', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (78, N'GE', N'Georgia', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (79, N'GF', N'French Guiana', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (80, N'GG', N'Guernsey', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (81, N'GH', N'Ghana', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (82, N'GI', N'Gibraltar', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (83, N'GL', N'Greenland', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (84, N'GM', N'Gambia', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (85, N'GN', N'Guinea', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (86, N'GP', N'Guadeloupe', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (87, N'GQ', N'Equatorial Guinea', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (88, N'GR', N'Greece', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (89, N'GS', N'South Georgia and the South Sandwich Islands', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (90, N'GT', N'Guatemala', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (91, N'GW', N'Guinea-Bissau', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (92, N'GY', N'Guyana', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (93, N'HK', N'Hong Kong S.A.R. China', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (94, N'HM', N'Heard Island and McDonald Islands', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (95, N'HN', N'Honduras', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (96, N'HR', N'Croatia', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (97, N'HT', N'Haiti', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (98, N'HU', N'Hungary', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (99, N'ID', N'Indonesia', NULL)
GO
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (100, N'IC', N'Spain Canary Islands', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (101, N'IE', N'Ireland Republic', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (102, N'IL', N'Israel', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (103, N'IM', N'Isle of Man', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (104, N'IN', N'India', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (105, N'IO', N'British Indian Ocean Territory', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (106, N'IQ', N'Iraq', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (107, N'IR', N'Iran Islamic Republic of', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (108, N'IS', N'Iceland', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (109, N'IT', N'Italy', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (110, N'JE', N'Jersey', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (111, N'JM', N'Jamaica', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (112, N'JO', N'Jordan', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (113, N'JP', N'Japan', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (114, N'KE', N'Kenya', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (115, N'KG', N'Kyrgyzstan', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (116, N'KH', N'Cambodia', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (117, N'KI', N'Kiribati', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (118, N'KM', N'Comoros', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (119, N'KN', N'Saint Kitts and Nevis', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (120, N'KP', N'Korea Democratic Peoples Republic of', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (121, N'KR', N'Korea Republic of', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (122, N'KW', N'Kuwait', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (123, N'KY', N'Cayman Islands', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (124, N'KZ', N'Kazakhstan', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (125, N'LA', N'Lao Peoples Democratic Republic', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (126, N'LB', N'Lebanon', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (127, N'LC', N'Saint Lucia', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (128, N'LI', N'Liechtenstein', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (129, N'LK', N'Sri Lanka', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (130, N'LR', N'Liberia', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (131, N'LS', N'Lesotho', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (132, N'LT', N'Lithuania', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (133, N'LU', N'Luxembourg', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (134, N'LV', N'Latvia', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (135, N'LY', N'Libyan Arab Jamahiriya', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (136, N'MA', N'Morocco', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (137, N'MC', N'Monaco', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (138, N'MD', N'Moldova Republic of', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (139, N'ME', N'Montenegro', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (140, N'MF', N'Saint Martin (French part)', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (141, N'MG', N'Madagascar', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (142, N'MK', N'Macedonia the former Yugoslav Republic of', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (143, N'ML', N'Mali', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (144, N'MM', N'Myanmar', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (145, N'MN', N'Mongolia', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (146, N'MO', N'Macau S.A.R. China', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (147, N'MQ', N'Martinique', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (148, N'MR', N'Mauritania', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (149, N'MS', N'Montserrat', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (150, N'MT', N'Malta', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (151, N'MU', N'Mauritius', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (152, N'MV', N'Maldives', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (153, N'MW', N'Malawi', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (154, N'MX', N'Mexico', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (155, N'MY', N'Malaysia', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (156, N'MZ', N'Mozambique', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (157, N'NA', N'Namibia', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (158, N'NC', N'New Caledonia', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (159, N'NE', N'Niger', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (160, N'NF', N'Norfolk Island', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (161, N'NG', N'Nigeria', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (162, N'NI', N'Nicaragua', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (163, N'NL', N'Netherlands', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (164, N'NO', N'Norway', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (165, N'NP', N'Nepal', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (166, N'NR', N'Nauru', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (167, N'NU', N'Niue', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (168, N'NZ', N'New Zealand', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (169, N'OM', N'Oman', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (170, N'PA', N'Panama', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (171, N'PE', N'Peru', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (172, N'PF', N'French Polynesia', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (173, N'PG', N'Papua New Guinea', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (174, N'PH', N'Philippines', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (175, N'PK', N'Pakistan', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (176, N'PL', N'Poland', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (177, N'PM', N'Saint Pierre and Miquelon', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (178, N'PN', N'Pitcairn', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (179, N'PS', N'Palestine', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (180, N'PT', N'Portugal', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (181, N'PY', N'Paraguay', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (182, N'QA', N'Qatar', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (183, N'RE', N'Reunion', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (184, N'RO', N'Romania', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (185, N'RS', N'Serbia', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (186, N'RU', N'Russian Federation', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (187, N'RW', N'Rwanda', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (188, N'SA', N'Saudi Arabia', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (189, N'SB', N'Solomon Islands', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (190, N'SC', N'Seychelles', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (191, N'SD', N'Sudan', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (192, N'SE', N'Sweden', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (193, N'SG', N'Singapore', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (194, N'SH', N'Saint Helena Ascension and Tristan da Cunha', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (195, N'SI', N'Slovenia', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (196, N'SJ', N'Svalbard and Jan Mayen', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (197, N'SK', N'Slovakia', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (198, N'SL', N'Sierra Leone', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (199, N'SM', N'San Marino', NULL)
GO
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (200, N'SN', N'Senegal', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (201, N'SO', N'Somalia', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (202, N'SR', N'Suriname', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (203, N'SS', N'South Sudan', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (204, N'ST', N'Sao Tome and Principe', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (205, N'SV', N'El Salvador', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (206, N'SX', N'Sint Maarten (Dutch part)', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (207, N'SY', N'Syrian Arab Republic', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (208, N'SZ', N'Swaziland', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (209, N'TC', N'Turks and Caicos Islands', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (210, N'TD', N'Chad', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (211, N'TF', N'French Southern Territories', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (212, N'TG', N'Togo', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (213, N'TH', N'Thailand', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (214, N'TJ', N'Tajikistan', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (215, N'TK', N'Tokelau', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (216, N'TL', N'Timor-Leste', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (217, N'TM', N'Turkmenistan', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (218, N'TN', N'Tunisia', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (219, N'TO', N'Tonga', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (220, N'TR', N'Turkey', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (221, N'TT', N'Trinidad and Tobago', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (222, N'TV', N'Tuvalu', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (223, N'TW', N'Taiwan', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (224, N'TZ', N'Tanzania United Republic of', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (225, N'UA', N'Ukraine', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (226, N'UG', N'Uganda', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (227, N'US', N'United States', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (228, N'UY', N'Uruguay', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (229, N'UZ', N'Uzbekistan', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (230, N'VA', N'Holy See (Vatican City State)', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (231, N'VC', N'Saint Vincent and the Grenadines', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (232, N'VE', N'Venezuela Bolivarian Republic of', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (233, N'VG', N'Virgin Islands British', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (234, N'VN', N'Vietnam', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (235, N'VU', N'Vanuatu', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (236, N'WF', N'Wallis and Futuna', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (237, N'WS', N'Samoa', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (238, N'YE', N'Yemen', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (239, N'YT', N'Mayotte', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (240, N'ZA', N'South Africa', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (241, N'ZM', N'Zambia', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (242, N'ZW', N'Zimbabwe', NULL)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (243, N'GB', N'United Kingdom Scotland', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (244, N'GB', N'United Kingdom England', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (245, N'GB', N'United Kingdom Wales', 1)
INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (246, N'PRI', N'Puerto Rico', 1)
SET IDENTITY_INSERT [dbo].[t039country] OFF

I’ve just thought I should add a international dialing code field

SQL Azure – Export Individual Tables Using SQL Server Management Studio

Connect to SQL Azure through SQL Server Management Studio

Select the database that contains the table you wish to script

Right click on the database and select Generate Scripts

The Generate Scripts dialog should appear choose

Choose Objects
Select specific database objects

Hit Next and set the location of where you wish the file to go

Review the summary dialog to ensure you have everything correct

Next you want to decide whether you want to include data or not in the script file

Hit the advanced button and scroll down to Types of data to script

If you are wanting data to be included select schema and data

Hit the Next button and review your configuration

Hit Next and it will start the process of exporting your table with or without data

The dialog will update to show status of script generation

You will now be able to navigate to the script and open it in an editor of your choice to double check that things look ok.

Here I show a script that includes data – note you can’t see all of the insert information because the script is rather wide but I assure you it is there at the bottom.

SQL Azure – TSQL User Defined Function – Pull out Long Dates from NVARCHAR(MAX)

Dates that match the pattern 2 numbers a single space the month a single space then a 4 digit year this will be good for any dates following this format from 1000 AD to 9999AD with a NVARCHAR(MAX) field note it will only pick up the last date in a text field.

Sourced adapted and tested from Stack Overflow

*Please note the square brackets c square brackets string is being omitted for months containing the letter c (I believe it relates to html and the copyright symbol I can’t place it anywhere in my post). You will need to add it back in to those months that contain c. I have therefore replaced square brackets c square brackets with [k]

January

Create function [dbo].[m01returnjandates](@fieldtosearch as nvarchar(max))
 Returns nvarchar(15)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retjandate as date
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-14
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,15) like '[0-9][0-9][ ][J][a][n][u][a][r][y][ ][0-9][0-9][0-9][0-9]')
        set @retjandate= substring(@fieldtosearch,@loop,15)
    set @loop=@loop+1
    end
Return @retjandate
End

===
February

Create function [dbo].[m02returnfebdates](@fieldtosearch as nvarchar(max))
 Returns nvarchar(16)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retfebdate as date
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-15
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,16) like '[0-9][0-9][ ][F][e][b][r][u][a][r][y][ ][0-9][0-9][0-9][0-9]')
        set @retfebdate= substring(@fieldtosearch,@loop,16)
    set @loop=@loop+1
    end
Return @retfebdate
End

====
March

Create function [dbo].[m03returnmardates](@fieldtosearch as nvarchar(max))
 Returns nvarchar(13)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retmardate as date
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-12
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,13) like '[0-9][0-9][ ][M][a][r][k][h][ ][0-9][0-9][0-9][0-9]')
        set @retmardate= substring(@fieldtosearch,@loop,13)
    set @loop=@loop+1
    end
Return @retmardate
End

===
April

Create function [dbo].[m04returnaprdates](@fieldtosearch as nvarchar(max))
 Returns nvarchar(13)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retaprdate as date
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-12
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,13) like '[0-9][0-9][ ][A][p][r][i][l][ ][0-9][0-9][0-9][0-9]')
        set @retaprdate= substring(@fieldtosearch,@loop,13)
    set @loop=@loop+1
    end
Return @retaprdate
End

===
May

Create function [dbo].[m05returnmaydates](@fieldtosearch as nvarchar(max))
 Returns nvarchar(11)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retmaydate as date
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-10
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,11) like '[0-9][0-9][ ][M][a][y][ ][0-9][0-9][0-9][0-9]')
        set @retmaydate= substring(@fieldtosearch,@loop,11)
    set @loop=@loop+1
    end
Return @retmaydate
End

===
June

Create function [dbo].[m06returnjundates](@fieldtosearch as nvarchar(max))
 Returns nvarchar(12)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retjundate as date
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-11
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,12) like '[0-9][0-9][ ][J][u][n][e][ ][0-9][0-9][0-9][0-9]')
        set @retjundate= substring(@fieldtosearch,@loop,12)
    set @loop=@loop+1
    end
Return @retjundate
End

===
July

Create function [dbo].[m07returnjuldates](@fieldtosearch as nvarchar(max))
 Returns nvarchar(12)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retjuldate as date
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-11
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,12) like '[0-9][0-9][ ][J][u][l][y][ ][0-9][0-9][0-9][0-9]')
        set @retjuldate= substring(@fieldtosearch,@loop,12)
    set @loop=@loop+1
    end
Return @retjuldate
End

===
August

Create function [dbo].[m08returnaugdates](@fieldtosearch as nvarchar(max))
 Returns nvarchar(14)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retaugdate as date
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-13
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,14) like '[0-9][0-9][ ][A][u][g][u][s][t][ ][0-9][0-9][0-9][0-9]')
        set @retaugdate= substring(@fieldtosearch,@loop,14)
    set @loop=@loop+1
    end
Return @retaugdate
End

===
September

Create function [dbo].[m09returnsepdates](@fieldtosearch as nvarchar(max))
 Returns nvarchar(17)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retsepdate as date
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-16
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,17) like '[0-9][0-9][ ][S][e][p][t][e][m][b][e][r][ ][0-9][0-9][0-9][0-9]')
        set @retsepdate= substring(@fieldtosearch,@loop,17)
    set @loop=@loop+1
    end
Return @retsepdate
End

===
October

Create function [dbo].[m10returnoctdates](@fieldtosearch as nvarchar(max))
 Returns nvarchar(15)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retjoctdate as date
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-14
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,15) like '[0-9][0-9][ ][O][k][t][o][b][e][r][ ][0-9][0-9][0-9][0-9]')
        set @retoctdate= substring(@fieldtosearch,@loop,15)
    set @loop=@loop+1
    end
Return @retoctdate
End

===
November

Create function [dbo].[m11returnnovdates](@fieldtosearch as nvarchar(max))
 Returns nvarchar(16)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retnovdate as date
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-15
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,16) like '[0-9][0-9][ ][N][o][v][e][m][b][e][r][ ][0-9][0-9][0-9][0-9]')
        set @retnovdate= substring(@fieldtosearch,@loop,16)
    set @loop=@loop+1
    end
Return @retnovdate
End

===
December

Create function [dbo].[m12returndecdates](@fieldtosearch as nvarchar(max))
 Returns nvarchar(16)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retdecdate as date
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-15
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,16) like '[0-9][0-9][ ][D][e][k][e][m][b][e][r][ ][0-9][0-9][0-9][0-9]')
        set @retdecdate= substring(@fieldtosearch,@loop,16)
    set @loop=@loop+1
    end
Return @retdecdate
End

===
And the inefficent example TSQL

SELECT dbo.T032email.pkid,
ISNULL(dbo.m01returnjandates(dbo.T032email.body),
    ISNULL(dbo.m02returnfebdates(dbo.T032email.body),
        ISNULL(dbo.m03returnmardates(dbo.T032email.body),
            ISNull(dbo.m04returnaprdates(dbo.T032email.body), 
				ISNULL(dbo.m05returnmaydates(dbo.T032email.body),
					ISNULL(dbo.m06returnjundates(dbo.T032email.body),
					ISNULL(dbo.m07returnjuldates(dbo.T032email.body),
					ISNull(dbo.m08returnaugdates(dbo.T032email.body,					 
                                        ISNULL(dbo.m09returnsepdates(dbo.T032email.body),
					ISNULL(dbo.m10returnoctdates(dbo.T032email.body),
	ISNULL(dbo.m11returnnovdates(dbo.T032email.body), dbo.m12returndecdates(dbo.T032email.body)
				))))))))))) as trandate FROM T032email;

SQL Azure – TSQL User Defined Function – Pull out money from NVARCHAR(MAX)

I think this one is pretty dirty but here goes.

Again it was adapted from StackOverflow thread
How to get part of string that matches with regular expression in SQL Server

Continuing the theme of identifying substrings in NVARCHAR(MAX) field here is a set of Functions that can be used identify and separate out money in an email body which has been uploaded into a NVARCHAR(MAX) field. looks for the pattern of 2 numbers after a dot and certain numerals before a dot and steps down from hundreds of thousands of pounds to tens of pounds. Note the order is important of the final query as each shorter pattern is a subset the next longer pattern.

First important to strip out the commas in the body as this will skip any currencies which have been formatted with commas.

UPDATE T032email
SET body = REPLACE(body,',','');

WARNING It should be noted that this is very unoptimised code and even on small sets combining the functions in a nested ISNULL SQL really takes time to run.

Firstly a function that looks for the pattern 100000.00 ie 999k to 100k.

CREATE FUNCTION [dbo].[ReturnMoneyDecimal6](@fieldtosearch as nvarchar(max))
 Returns varchar(9)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retemoney6 as decimal(9,2)
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-8
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,9) like '[0-9][0-9][0-9][0-9][0-9][0-9][.][0-9][0-9]')
        set @retemoney6 = substring(@fieldtosearch,@loop,9)
    set @loop=@loop+1
    end
Return @retemoney6
END

Next a function that looks for the pattern 10000.00 ie 99k to 10k.

CREATE function [dbo].[ReturnMoneyDecimal5](@fieldtosearch as nvarchar(max))
 Returns varchar(8)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retemoney5 as decimal(8,2)
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-7
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,8) like '[0-9][0-9][0-9][0-9][0-9][.][0-9][0-9]')
        set @retemoney5 = substring(@fieldtosearch,@loop,8)
    set @loop=@loop+1
    end
Return @retemoney5
END

Next a function that looks for the pattern 1000.00 ie 9k to 1k.

CREATE function [dbo].[ReturnMoneyDecimal4](@fieldtosearch as nvarchar(max))
 Returns varchar(7)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retemoney4 as decimal(7,2)
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-6
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,7) like '[0-9][0-9][0-9][0-9][.][0-9][0-9]')
        set @retemoney4 = substring(@fieldtosearch,@loop,7)
    set @loop=@loop+1
    end
Return @retemoney4
END

Next a function that looks for the pattern 100.00 ie 999 to 100.

CREATE function [dbo].[ReturnMoneyDecimal3](@fieldtosearch as nvarchar(max))
 Returns varchar(6)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retemoney3 as decimal(6,2)
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-5
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,6) like '[0-9][0-9][0-9][.][0-9][0-9]')
        set @retemoney3 = substring(@fieldtosearch,@loop,6)
    set @loop=@loop+1
    end
Return @retemoney3
END

Lastly a function that looks for the pattern 10.00 ie 99 to 10.

CREATE function [dbo].[ReturnMoneyDecimal2](@fieldtosearch as nvarchar(max))
 Returns varchar(5)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retemoney2 as decimal(5,2)
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-4
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,5) like '[0-9][0-9][.][0-9][0-9]')
        set @retemoney2 = substring(@fieldtosearch,@loop,5)
    set @loop=@loop+1
    end
Return @retemoney2
END

And then here is the quite horribly inefficient query that can be used to run all functions and seems to do the job.

You know your searchstrings better than me but any text with multiple decimal substrings will be an issue!!!

Which is still a few million times quicker than any person!

SELECT dbo.T032email.pkid,
ISNULL(dbo.ReturnMoneyDecimal6(dbo.T032email.body),
	ISNULL(dbo.ReturnMoneyDecimal5(dbo.T032email.body),
		ISNULL(dbo.ReturnMoneyDecimal4(dbo.T032email.body),
			ISNull(dbo.ReturnMoneyDecimal3(dbo.T032email.body), dbo.ReturnMoneyDecimal2(dbo.T032email.body)
				)))) as money4 from T032email;

SQL Azure – TSQL User Defined Function – Cleaning a Field of Specified Characters

Stack overflow sourced, adapted and personally tested code
How to strip all non-alphabetic characters from string in SQL Server

In an earlier post I was having issues as some email addresses I was pulling out from a field were captioned in <> using the following will remove those characters prior to identifying string patterns..

Remember this pulls them out and then compacts the resulting string this may or many not be what you are looking for. I have adapted from the Stack Overflow discussion to include characters I don’t want rid of.

CREATE Function [dbo].[RemoveNonAlphaCharacters](@Temp nvarchar(max))
Returns nvarchar(max)
AS
Begin

    Declare @KeepValues as nvarchar(50)
    Set @KeepValues = '%[^a-z0-9/@ £$+=?.\!]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

And running the example code we get..

SELECT dbo.RemoveNonAlphaCharacters('abcdefghijklmnopqrstuvwxyz1234567890 !"£$()_+=-{}[]:@~;\|<>?./') as txtCorrected

We get

WARNING Please note ^ % & and * ‘ ” – are reserved TSQL characters and including them in the function appears to break the logic that I wish to see in the pattern replacement.

SQL Azure – TSQL User Defined Function – Separate multiple emails from NVARCHAR(MAX) field

Stack overflow sourced, adapted and personally tested code
Extract email address from string using tsql

A continuation of working with strings in TSQL specifically linked to emails.

Firstly create the following Function

CREATE FUNCTION [dbo].[fnFindPatternLocation]
(
    @string NVARCHAR(MAX),
    @term   NVARCHAR(MAX)
)
RETURNS TABLE
AS
    RETURN 
    (
        SELECT pos = Number - LEN(@term) 
        FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@string, Number, 
        CHARINDEX(@term, @string + @term, Number) - Number)))
        FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
        FROM sys.all_objects) AS n(Number)
        WHERE Number > 1 AND Number <= CONVERT(INT, LEN(@string))
        AND SUBSTRING(@term + @string, Number, LEN(@term)) = @term
    ) AS y);

Then create a View of what you are interested in as follows.. Note here I am taking out the carriage return as my subsequent query doesn’t like them and in emails they frequently exist.

CREATE VIEW [dbo].[v001] as SELECT pkid, REPLACE(body, CHAR(13) + CHAR(10),' ') as body1 from t001email

Then run the newly created View through a query.

SELECT pkid, body1, pos, SUBSTRING(body,beginningOfEmail,endOfEmail-beginningOfEmail) AS email
FROM v001
CROSS APPLY (SELECT pos FROM dbo.fnFindPatternLocation(body1, '@')) AS A(pos)
CROSS APPLY (SELECT CHARINDEX(' ',body1 + ' ', pos)) AS B(endOfEmail)
CROSS APPLY (SELECT pos - CHARINDEX(' ', REVERSE(SUBSTRING(body, 1, pos))) + 2) AS C(beginningOfEmail)

Couple of things here
Multiple emails will be picked out and placed as separate records so if there a string that reads

This is a sentence with two emials first@gmail.com and a second second@gmail.com

it will return
first@gmail.com
second@gmail.com

If an email starts the field then this will NOT work after finding the @ symbol it will count forward and fail to find a space and so set space before to Null it will then return just the domain of the email. I will be looking to fix this at some point.

Secondly if the emails within the field contain contiguous special html characters such as < or > these will be picked up and inculded as if they are part of the email addresses.

We can fix this by scanning through the varchar(max) field and stripping out special characters.

NOTE : If you are working with email bodies carriage returns will also screw up the above query in which case consider running the field through some kind of replace view with similar syntax as

CREATE VIEW v002 as SELECT pkid, REPLACE(body, CHAR(13) + CHAR(10),' ') as txtBodyWithoutReturns from t001email

SQL Azure – TSQL User Defined Function – Separate Defined Length String from NVARCHAR(MAX) Field

Stack overflow sourced, adapted and personally tested code
How to get part of a string that matches with a regular expression

The following function arose out of a desire to find Eplanning Scotland planning references numbers which follow the format of 9 numerals a dash and then 3 numerals within a NVARCHAR(MAX) field type. The characteristics of the string was that it is always the same length.

In SSMS select the New Query button

Create function [dbo].[ReturnEplanningNumberFull](@fieldtosearch as nvarchar(max))
 Returns nvarchar(13)
 as
 begin
 declare @length as int 
 declare @loop as int
 declare @reteplan as varchar(13)
 set @loop =1
 set @length = len(@fieldtosearch)

 while @loop<=@length-12
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,13) like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][-][0-9][0-9][0-9]')
        set @reteplan = substring(@fieldtosearch,@loop,13)
    set @loop=@loop+1
    end
Return @reteplan
end

And then to create a View using this you can write something like ; 

CREATE VIEW v026eplanrefs AS 
SELECT dbo.THETABLE.pkid, 
dbo.ReturnEplanningNumberFull(dbo.THETABLE.FIELDCONTAINSREFERENCE) as eplanno 
FROM dbo.THETABLE;

I subsequently altered this to identify the first 9 digits as this is sufficent to uniquely identify eplanning records.

CREATE function [dbo].[ReturnEplanningNumberShort](@fieldtosearch as nvarchar(max))
 Returns nvarchar(9)
 as
 begin
 declare @length as int 
 declare @loop as int
 declare @reteplanshort as nvarchar(9)
 set @loop =1
 set @length = len(@fieldtosearch)

 while @loop<=@length-8
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,9) like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
        set @reteplanshort = substring(@fieldtosearch,@loop,9)
    set @loop=@loop+1
    end
Return @reteplanshort
end

SQL Azure – Reset Primary Key Identity/Index to Zero after Deleting all records

I was faced with a situation where I needed to set up an index for widgets but I wanted those to start at zero. I made some attempts to import information and deleted those records from the table several times and was faced with a situation where users might see the index occasionally. As a result I wanted the index to start from 1 and at least at the start go up to the approximate number of widgets.

Its a quick search using google to get this but wanted to reset the identity on a blank table.

In SSMS / navigate to the database and open a new query window

DBCC CHECKIDENT (dbo.t001table, RESEED,0);
GO

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 = 'Bluedanube99';
ALTER ROLE db_owner ADD MEMBER rocketengineapplication;

Note SQL Azure requires passwords to be ‘sufficiently complicated’ at the time of writing this seemed to be
The default Azure password complexity rules: minimum length of 8 characters, minimum of 1 uppercase character, minimum of 1 lowercase character, minimum of 1 number.

And to drop the login
Go in through SSMS

Security / Users / the users should be listed where you can select and choose DELETE

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)

I note that for local SQL Express and I believe enterprise SQL Server there is the additional option of creating a Backup in SQL Express. I also note that from my reading baks are considered preferable to bacpac files because they have enforced ACID compliance and that for large databases that are constantly being used they are recommended to bacpac files. SQL Azure doesn’t allow BAK file backups through SSMS from what I can see so if this is an issue for you consider temporarily disconnecting front ends from the database while a bacpac is conducted. If you need a bak file for some reason you can attach locally to a SQL Server instance and from there take a bak file.

Something to be aware of..

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"

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.

Setting up a Blank SQL Server Spatially enabled Table using Microsoft SQL Server Management Studio 2008R2 Express and displaying it in QGIS 2.8.1

Programs used;

1- SQL Server 2008R2 Express
2- SQL Server Management Studio 2008R2 Express
3- QGIS

The example uses UK national grids coordinates to create a Triangle Polygon in a SQL Server Table

I’ve previously written that while we’ve had spatially enabled SQL Server for over 5 years I constantly come across line of business applications that although using SQL Server have not and do not intend to spatially enable the application. This is undoubtedly because of the difficulty in re-designing legacy systems actively in use and because the benefits although significant are not generally requested by all but the most knowledgable of colleagues.

While I understand this legacy system reasoning spatially enabled databases are the future so its just a matter of when and not if an application will require alteration. Understanding it in this context makes it really a requirement to start seriously planning for its inclusion.

Developerers creating new applications however should always consider spatially enabling relevant tables from the start even if it is not specked by the client/colleague. It being so much easier to spend a couple of minutes future proofing the schema of a new born database rather than hours trying to retrofit a live in production back end.

Firstly it’s important to understand what a geodatabase in SQL Server actually is.
Really it is a normal database which has one table that has a field that has a geometry or geography value type. In this example I will use desktop QGIS 2.8.1 to display the resulting geometry but any other digital mapping package that can link to SQL Server could be used. SQL Server also has a very rudimentary Mapping Display but you will need something better if you want to manipulate boundaries visually.

Many digital mapping products have plugins that will create Geodatabases and tables however I haven’t seen one for QGIS. I really wanted to be able to create spatial SQL tables on my own without recourse to paid tools directly in SQL Server Management Studio. So here’s my method of creating blank polygon table whose geometry is ready to be read and edited in QGIS or any other digital mapping system just using SQL Server Management Studio Express 08R2.

Steps
1. Create a new Table
2. Ensure the table has an identity Key that increments
3. Create a geometry column
4. Write a query that updates the geometry column

UPDATE T001Sites SET Coordinates=geometry::STGeomFromText(‘POLYGON((301804 675762,295789 663732,309581 664870,301804 675762))’,27700)

You will note that there are four coordinates here (each coordinate being a pair of numbers )
The first coordinate and last are the same this is required by SQL to ensure that the polygon is closed.

The 27700 number is the Spatial Reference System Identifier (SRID) – it is a unique value used to unambiguosly identify projecttion. 27700 is the unique identifier for the United Kingdom National Grid and represents the coordinates my example refer to. The spatial reference identification system is defined by the European Petroleum Survey Group (EPSG) standard which is a set of standards developmed for cartography surveying etc and owned by the Oil and Gas Producers Group list here; http://www.epsg-registry.org/

The above coordinates display a triangle in West Lothian near Edinburgh

5. Set up the connection to SQL Server Instance

Ensure the box marked “Only look in the geometry_columns metadata table” checkbox is unchecked. By default this is checked and if the geometry_columns table does not exist you will get an error message.

QGIS-SSMS-Connection

6. Display the table and edit as appropriate.

Select the table then hit the Add button

QGIS-SSMS-TableReadyforDisplay

And here is the SQL Server table in QGIS ready to be added to or edited.
QGISshowingSQLServerPolygon

Attaching Databases to SQL Server 08R2 Express

jigsaw2
It should be noted that the following although the easiest way to get a new database into an instance it should not be used in a production environment. In fact doing so may get you sacked. If experimenting though this method should be fine. If in doubt seek help as in a production environment you would want to look through all the code before attaching anything into an instance.

Go to SQL server management studio and on the Databases tree
Right click and select
Attach…..

Attach database window should appear which will allow you to use the Add… button to navigate to the
\Data\ subdirectory where all the sql server databases are held.

IMPORTANT – prior to loading a file in the database will have needed to have been DETACHED and you should always move anything mdf file that you are wanting to put into a database into the data subdirectory.
This ordinarily is done by going to database in question scrolling down to the database and right clicking on the database

Tasks > – Detach…

WARNING
If a database has not been detached properly it may NOT be possible to re-attach the database this is of course a security feature. So experimenting with simple moving files about will not work…

The listed code below does it at command line but the above works in SSMS

The default location for databases in SQL 08R2 Express is
C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA

The default location for database in SQL 2012 is
C:\Program Files\Microsoft SQL Server\MSSQL11.[InstanceName\MSSQL\DATA