SQL Azure – Azure Automation Hybrid Worker Setup and RunAs Account

The highs and lows.

You’re flying high – you’ve created a stored procedure that you’ve managed to reference in your automation account within a powershell runbook and you’ve got it nicely churning over on an extremely regular basis. You want to run it daily or weekly but as a test you run it hourly just to ensure that it works before releasing it to production. A day passes ok and you think wow this is really great. Then you forget about it and come back to it a week later. You find that somehow it stopped working at 1 am on Tuesday.

What happened????

Sorry you got hit by a block resulting from dynamic IP change on the runbook in the Automation Account – DAMN.
(there is probably a good reason for having a dynamic IP on automation accounts but damn it was really going too well)

So after quite a lot of investigation I found those in the know seemed to be saying the solution is.
Azure Automation linked to a Hybrid Worker

I kept investigating this and I kept getting directed to Microsoft documentation which although really extensive was just not helping me really get my head around things. It wasn’t until I found Travis Roberts that things started to click… I link to three of his videos below.

So the downsides of Hybrid Worker setup..

  • Despite being the recommended solution configuration is complicated.
  • You need to create a VM with a static IP just to run the hybrid worker (and then grant firewall access to the databaseserver for that VM)
  • That VM needs to have 2 cores and 4Gb of Ram (yes unfortunately not the smallest VM so you could be looking at $25 a month charge)
  • You need to set up log analytics
  • It is recommended that log analytics is in the SAME Azure location as the sample database BUT not all Azure locations have log analytics set up… Use UK South! Hands up who has already set up their server and just realised it is in a location that doesn’t allow log analytics.
  • So I was really struggling with this after my runbook unexpectedly started failing. After several days of reading and searching I found Travis Roberts. Hallelujah for MVPs.

    So I haven’t done it yet but I think the procedure can all be defined in here.

    1. Setting up an Azure VM with a fixed IP (It can be any VM but Azure is good for me)
    Setup VM with static IP
    The server you setup will need to have internet connectivity with SSL outbound on port 443

    2. Setting up Azure Log Analytics a step by step guide
    Setting up Azure Log Analytics a step by step guide

    3. Azure Automation Setup and Linking Log Analytics to your Automation account
    Set up Azure Automation and link it to Log Analytics

    4. Configuring an Azure Automation Hybrid Runbook Worker to run your runbook.
    Installation of Hybrid Runbook Worker
    Includes information about Runbook Worker Group and
    RunAsAccount
    You add credentials to the Runbook Worker Group

    Firstly a big thank you to Travis Roberts for putting out those videos he has loads of Azure Centric content on his youtube channel which you can find here

    CAVEAT I haven’t implemented this myself properly and at the time of writing(December 2021) those videos are a couple of years old now. At the start of one of the Videos Travis indicates that things are changing quickly in the space and expect that there has been changes since these videos. Lets face it this should really be easier to do Azure is just frustrating their users with this complicated configuration.

    SQL Azure – Using Azure Automation and Runbooks to Schedule an Azure SQL Stored Procedure

    Its vital to be able to run stored procedures on a schedule it can be extremely important for doing daily housekeeping.

    This site was where I learned how to do this.
    How to Schedule an Azure SQL Database’s Stored Procedure

    Runbooks sit in Azure Automation and a Runbook consists of a script that performs a task.

    Runbooks can be written in Python or Powershell. See the last part of this post for an important additional issue.

    "Data Source=01server.database.windows.net;Initial Catalog=database;Integrated Security=False;User ID=01userid;Password=JK12jl423;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False"
    
    # Instantiate the connection to the SQL Database
    $sqlConnection = new-object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = "Data Source=servername.database.windows.net;Initial Catalog=database;Integrated Security=False;User ID=userid;Password=password;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False"
    
    $sqlConnection.Open()
    Write-Output "Azure SQL database connection opened"
    
    # Define the SQL command to run
    $sqlCommand = new-object System.Data.SqlClient.SqlCommand
    $sqlCommand.CommandTimeout = 120
    $sqlCommand.Connection = $sqlConnection
    
    Write-Output "Issuing command to run stored procedure"
    
    # Execute the SQL command
    $sqlCommand.CommandText= 'exec [dbo].[storedProcedure]'
    $result = $sqlCommand.ExecuteNonQuery()
    Write-Output "Stored procedure execution completed"
    
    # Close the SQL connection
    $sqlConnection.Close()
    
    Write-Output "Run completed"
    

    Honestly the hardest part is getting the code setting up the scheduling after that is relatively easy.

    WARNING : Runbooks have dynamic IPs so although you can set grant access to an IP your runbook will only run for a certain period before it stops working unless you either have a Hybrid Runbook Worker setup OR you allow Azure services and resources to access this server (which isn’t recommended) The next post I will do is my initial notes on how to configure a Hybrid Runbook Worker.

    SQL AZURE – Group records or widgets in Sets of a Specific Number

    Useful if you have factory products that are being packed into sets of a specfic number. In our case sixes.

    Swap out to a different amount if you need to. The last group in the record set will have a remainder level if the recordset is not perfectly divisible by the set integer.

    SELECT pkid,
    floor(((ROW_NUMBER() OVER(ORDER BY pkid))-1)/6)+1 as grpNum
    FROM t002caskfill
    

    Here is a screenshot of the above code being run on a table.

    SQL Azure – Dynamic SQL – Using a parameterized Stored Procedure to Create a View

    There are situations where you need to create SQL on the fly from a record that you are currently dealing with. This is sometimes referred to as Dynamic SQL. Here I start investigating creating a dynamically created SQL view using a stored procedure. In my case I was doing this so that I could create some particularly valuable values.

    Here we have a table called T0001Persons that has the following three fields

    PKID
    Firstname
    DateCreated

    How do we create a stored procedure that will Create a view

    USE yourdatabasename
    GO
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER   PROCEDURE [dbo].[auditselection]
    (@firstname nvarchar(15),
    @Dcreate datetime)
    AS
    BEGIN
    SET NOCOUNT ON
    
    EXEC ('CREATE or ALTER VIEW v01 AS SELECT dbo.T0001Persons.PKID,
     dbo.T0001Persons.Firstname, 
    dbo.T0001Persons.DateCreated
    FROM
    dbo.T0001Persons WHERE dbo.T0001Persons.Firstname=' + '''' 
    + @firstname + '''' + ' AND dbo.T0001Persons.DateCreated < 
    ' + '''' + @Dcreate + '''' +
    '')
    END
    

    and then an example execution is

    EXEC auditselection 'Mark', '2021-10-03'
    

    Using the script as facility in SSMS we can identify the query that has been created by EXEC auditselection 'Mark', '2021-10-03'.

    SELECT dbo.T0001Persons.PKID, 
    dbo.T0001Persons.Firstname, dbo.T0001Persons.DateCreated
    FROM
    dbo.T0001Persons WHERE dbo.T0001Persons.Firstname='Mark'AND dbo.T0001Persons.DateCreated<'Oct  3 2021 12:00AM'
    

    Several things should be noted here

    Within the EXEC statement we use a single quote to escape the string and then a plus sign followed by the parameter to insert the parameter into the SQL that will be executed. A single quote is then used again to escape the code to go back into the string.

    But what if the SQL we want to run needs to include a single quote (which is often the case). I tried escaping the the string with a single quote and then a plus sign and then a Unicode Character references (only tried 39) surrounding a single quote but that didn’t really work.

    What did work was to have four single quotes with two pluses on either side.

    + '''' +

    Here a single quote escapes the code and starts the string again. Two single quotes together are interpreted as a single quote (clearly some kind of special interpretation is happening in the background) and then the last single quote escapes the string back into code.

    From a discussion on Stackoverflow I also found the option to SET QUOTED_IDENTIFIER switch.

    If escaping your single quote with another single quote isn’t working for you (like it didn’t for one of my recent REPLACE() queries), you can use SET QUOTED_IDENTIFIER OFF before your query, then SET QUOTED_IDENTIFIER ON after your query.

    SET QUOTED_IDENTIFIER OFF;
    
    UPDATE TABLE SET NAME = REPLACE(NAME, "'S", "S");
    
    SET QUOTED_IDENTIFIER ON;
    -- set OFF then ON again
    

    Next up I will be passing parameters to parameterized stored procedure via PHP Runner where the parameters are taken from an onscreen record.

    PHPRunner create an event to pass parameters to a stored procedure

    This looks promising
    Passing one of the field values as a parameter:

    $sql = DB::PrepareSQL( "EXEC StoredProcNameHere ':1'", $values["FieldName"] );
    DB::Exec( $sql );
    

    and I have discovered that the :1 is considered a placeholder for the following variable and so passing two variables would come in as the following assuming both first and second parameters are text values. If :1 was the target of an integer instead of ‘:1’ we would have :1.

    $sql = DB::PrepareSQL( "EXEC auditselection ':1',':2'", $values["FieldName1"], $values["FieldName2"]);
    DB::Exec( $sql );
    

    This is php here the single quotes DO NOT escape the string. They will form part of the string that will be executed which is what I want.
    The string is not escaped until the double quotes. Confusingly two single quotes look EXACTLY the same as a double quote but they are completely different. If you use your cursor you can see that the double quote is a single character which is interpreted completely differently by the php engine.

    The following link may be important as well in determining what can be achieved
    Grid row Javascript API

    SQL Azure – Convert UTC to your local time

    Not unsurprisingly SQL Azure GetDate() stores datetime as a UTC value.

    If your application is hosted in a specific country it is likely you will wish to return that value to the local user in the local time.
    Here I host in the UK and want to display in the UK so my choice is simple enough.

    The default CONVERT() statement in the SQL Azure library will do this for you.

    An example of the statement with parameters is here (slightly confusingly here I have the field name as a reserved word but just ensure that your field name is in the square brackets)

    CONVERT(datetime, 
    SWITCHOFFSET([datetime], 
    DATEPART(TZOFFSET, 
    [datetime] AT TIME ZONE 'GMT Standard Time'))) 
    as UKTime

    And here is that statement included in a full query.

    And here is an SQL statement to determine all time zones currently observing daylight saving on your server.
    This query can also be used to identify what parameter you should have for your convert statement.

    SELECT
      name,
      current_utc_offset
    FROM sys.time_zone_info
    WHERE is_currently_dst = 1;
    

    SQL Azure – Trying to Alter Computed Column definition

    The situation – I have a computed column that I want to alter in SQL Azure

    After googling away it turns out you cannot alter a computed column what you have to do is drop it and then re-create it again..

    Bit disappointing – I then thought maybe I can just alter the function – Foiled AGAIN – that MIGHT have been possible but for the fact I needed to introduce an additional parameter into the function and that won’t work as I have to alter the parameter definition in the column definition which again is an alteration of the column..

    Oh well

    You might want to take a minute to compose yourself / make sure you’ve got a backup and then I guess go for it!

    ALTER TABLE dbo.t002table1
    	DROP COLUMN DistillPeriod
    ALTER TABLE dbo.t002table1 ADD DistillPeriod AS ([dbo].[calculatematuration]([t002table1].[dateoffill],getdate(),[t002table1].[disgorgedate]));
    

    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