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