BlogEngine.Net SQL Update Script

by Damiaan Peeters 2. January 2008 14:12

I am using a great tool called SQL Examiner. 

Using this tool i created a "update" script for BlogEngine 1.3.  (from version 1.2).

Replace the "yourdatabasename" by the name of your database.

[more]

 

/*
Script created by SQL Examiner 1.6.0.42 at 01/02/2008 13:43:38 
Upgrade SQL Database from 1.2 to version 1.3
*/
--step 1. Backup database
BACKUP DATABASE [yourDatabaseName] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\yourDatabaseName_db_200801021343.bak' WITH  INIT,  NOUNLOAD,  NAME = N'yourDatabaseName backup', NOSKIP, DESCRIPTION = N'SQL Examiner Backup', NOFORMAT
GO
USE [yourDatabaseName]
GO
SET NOCOUNT ON
SET NOEXEC OFF
SET ARITHABORT ON
SET XACT_ABORT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRAN
GO
--step 2: add columns to Table dbo.be_Categories----------------------------------------------------
GO
ALTER TABLE [dbo].[be_Categories] ADD 
[Description]	[nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 2 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 2 is completed with errors' SET NOEXEC ON END
GO
--step 3: alter column [CategoryName] of table [dbo].[be_Categories]--------------------------------
GO
ALTER TABLE [dbo].[be_Categories] ALTER COLUMN [CategoryName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 3 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 3 is completed with errors' SET NOEXEC ON END
GO
--step 4: alter column [Title] of table [dbo].[be_Pages]--------------------------------------------
GO
ALTER TABLE [dbo].[be_Pages] ALTER COLUMN [Title] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 4 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 4 is completed with errors' SET NOEXEC ON END
GO
--step 5: alter column [Description] of table [dbo].[be_Pages]--------------------------------------
GO
ALTER TABLE [dbo].[be_Pages] ALTER COLUMN [Description] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 5 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 5 is completed with errors' SET NOEXEC ON END
GO
--step 6: alter column [Keywords] of table [dbo].[be_Pages]-----------------------------------------
GO
ALTER TABLE [dbo].[be_Pages] ALTER COLUMN [Keywords] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 6 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 6 is completed with errors' SET NOEXEC ON END
GO
--step 7: alter column [Link] of table [dbo].[be_PingService]---------------------------------------
GO
ALTER TABLE [dbo].[be_PingService] ALTER COLUMN [Link] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 7 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 7 is completed with errors' SET NOEXEC ON END
GO
--step 8: dbo.be_PostCategory: add foreign key FK_be_PostCategory_be_Categories---------------------
GO
ALTER TABLE [dbo].[be_PostCategory] ADD CONSTRAINT [FK_be_PostCategory_be_Categories] FOREIGN KEY ([CategoryID]) REFERENCES [dbo].[be_Categories] ([CategoryID])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 8 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 8 is completed with errors' SET NOEXEC ON END
GO
--step 9: alter column [Title] of table [dbo].[be_Posts]--------------------------------------------
GO
ALTER TABLE [dbo].[be_Posts] ALTER COLUMN [Title] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 9 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 9 is completed with errors' SET NOEXEC ON END
GO
--step 10: alter column [Description] of table [dbo].[be_Posts]-------------------------------------
GO
ALTER TABLE [dbo].[be_Posts] ALTER COLUMN [Description] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 10 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 10 is completed with errors' SET NOEXEC ON END
GO
--step 11: alter column [Author] of table [dbo].[be_Posts]------------------------------------------
GO
ALTER TABLE [dbo].[be_Posts] ALTER COLUMN [Author] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 11 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 11 is completed with errors' SET NOEXEC ON END
GO
--step 12: alter column [Slug] of table [dbo].[be_Posts]--------------------------------------------
GO
ALTER TABLE [dbo].[be_Posts] ALTER COLUMN [Slug] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 12 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 12 is completed with errors' SET NOEXEC ON END
GO
--step 13: dbo.be_PostCategory: add foreign key FK_be_PostCategory_be_Posts-------------------------
GO
ALTER TABLE [dbo].[be_PostCategory] ADD CONSTRAINT [FK_be_PostCategory_be_Posts] FOREIGN KEY ([PostID]) REFERENCES [dbo].[be_Posts] ([PostID])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 13 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 13 is completed with errors' SET NOEXEC ON END
GO
--step 14: add index FK_CategoryID to table dbo.be_PostCategory-------------------------------------
GO
CREATE NONCLUSTERED INDEX [FK_CategoryID] ON [dbo].[be_PostCategory]([CategoryID]) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 14 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 14 is completed with errors' SET NOEXEC ON END
GO
--step 15: add index FK_PostID to table dbo.be_PostCategory-----------------------------------------
GO
CREATE NONCLUSTERED INDEX [FK_PostID] ON [dbo].[be_PostCategory]([PostID]) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 15 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 15 is completed with errors' SET NOEXEC ON END
GO
--step 16: alter column [Author] of table [dbo].[be_PostComment]------------------------------------
GO
ALTER TABLE [dbo].[be_PostComment] ALTER COLUMN [Author] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 16 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 16 is completed with errors' SET NOEXEC ON END
GO
--step 17: alter column [Email] of table [dbo].[be_PostComment]-------------------------------------
GO
ALTER TABLE [dbo].[be_PostComment] ALTER COLUMN [Email] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 17 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 17 is completed with errors' SET NOEXEC ON END
GO
--step 18: alter column [Website] of table [dbo].[be_PostComment]-----------------------------------
GO
ALTER TABLE [dbo].[be_PostComment] ALTER COLUMN [Website] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 18 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 18 is completed with errors' SET NOEXEC ON END
GO
--step 19: alter column [Comment] of table [dbo].[be_PostComment]-----------------------------------
GO
ALTER TABLE [dbo].[be_PostComment] ALTER COLUMN [Comment] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 19 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 19 is completed with errors' SET NOEXEC ON END
GO
--step 20: alter column [Country] of table [dbo].[be_PostComment]-----------------------------------
GO
ALTER TABLE [dbo].[be_PostComment] ALTER COLUMN [Country] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 20 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 20 is completed with errors' SET NOEXEC ON END
GO
--step 21: alter column [Ip] of table [dbo].[be_PostComment]----------------------------------------
GO
ALTER TABLE [dbo].[be_PostComment] ALTER COLUMN [Ip] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 21 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 21 is completed with errors' SET NOEXEC ON END
GO
--step 22: dbo.be_PostComment: add foreign key FK_be_PostComment_be_Posts---------------------------
GO
ALTER TABLE [dbo].[be_PostComment] ADD CONSTRAINT [FK_be_PostComment_be_Posts] FOREIGN KEY ([PostID]) REFERENCES [dbo].[be_Posts] ([PostID])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 22 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 22 is completed with errors' SET NOEXEC ON END
GO
--step 23: add index FK_PostID to table dbo.be_PostComment------------------------------------------
GO
CREATE NONCLUSTERED INDEX [FK_PostID] ON [dbo].[be_PostComment]([PostID]) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 23 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 23 is completed with errors' SET NOEXEC ON END
GO
--step 24: alter column [NotifyAddress] of table [dbo].[be_PostNotify]------------------------------
GO
ALTER TABLE [dbo].[be_PostNotify] ALTER COLUMN [NotifyAddress] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 24 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 24 is completed with errors' SET NOEXEC ON END
GO
--step 25: dbo.be_PostNotify: add foreign key FK_be_PostNotify_be_Posts-----------------------------
GO
ALTER TABLE [dbo].[be_PostNotify] ADD CONSTRAINT [FK_be_PostNotify_be_Posts] FOREIGN KEY ([PostID]) REFERENCES [dbo].[be_Posts] ([PostID])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 25 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 25 is completed with errors' SET NOEXEC ON END
GO
--step 26: add index FK_PostID to table dbo.be_PostNotify-------------------------------------------
GO
CREATE NONCLUSTERED INDEX [FK_PostID] ON [dbo].[be_PostNotify]([PostID]) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 26 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 26 is completed with errors' SET NOEXEC ON END
GO
--step 27: alter column [Tag] of table [dbo].[be_PostTag]-------------------------------------------
GO
ALTER TABLE [dbo].[be_PostTag] ALTER COLUMN [Tag] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 27 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 27 is completed with errors' SET NOEXEC ON END
GO
--step 28: dbo.be_PostTag: add foreign key FK_be_PostTag_be_Posts-----------------------------------
GO
ALTER TABLE [dbo].[be_PostTag] ADD CONSTRAINT [FK_be_PostTag_be_Posts] FOREIGN KEY ([PostID]) REFERENCES [dbo].[be_Posts] ([PostID])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 28 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 28 is completed with errors' SET NOEXEC ON END
GO
--step 29: add index FK_PostID to table dbo.be_PostTag----------------------------------------------
GO
CREATE NONCLUSTERED INDEX [FK_PostID] ON [dbo].[be_PostTag]([PostID]) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 29 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 29 is completed with errors' SET NOEXEC ON END
GO
--step 30: dbo.be_Settings: drop primary key PK_be_Settings-----------------------------------------
GO
ALTER TABLE [dbo].[be_Settings] DROP CONSTRAINT [PK_be_Settings]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 30 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 30 is completed with errors' SET NOEXEC ON END
GO
--step 31: alter column [SettingName] of table [dbo].[be_Settings]----------------------------------
GO
ALTER TABLE [dbo].[be_Settings] ALTER COLUMN [SettingName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 31 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 31 is completed with errors' SET NOEXEC ON END
GO
--step 32: alter column [SettingValue] of table [dbo].[be_Settings]---------------------------------
GO
ALTER TABLE [dbo].[be_Settings] ALTER COLUMN [SettingValue] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 32 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 32 is completed with errors' SET NOEXEC ON END
GO
--step 33: drop schema blog-dampee------------------------------------------------------------------
GO
DROP SCHEMA [blog-dampee]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 33 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 33 is completed with errors' SET NOEXEC ON END
GO
--step 34: dbo.be_Settings: add primary key PK_be_Settings------------------------------------------
GO
ALTER TABLE [dbo].[be_Settings] ADD CONSTRAINT [PK_be_Settings] PRIMARY KEY CLUSTERED ([SettingName])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 34 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 34 is completed with errors' SET NOEXEC ON END
GO
----------------------------------------------------------------------
IF @@TRANCOUNT > 0 BEGIN COMMIT TRAN PRINT 'Synchronization is successfully completed.' END
GO

BlogEngine.Net works with Windows Live Writer

by Damiaan Peeters 1. December 2007 12:40

Like a charm!  Just entered the blog address, my username and password.  Windows live writer discovered all the rest! 

image

The new look and features in Windows Live Writer (version 2008) are cool.  Let's see if BlogEngine.Net and Live writer can work with embedded images.

 

 

New place to blog

by Damiaan Peeters 30. November 2007 13:09

Yes...  And finally...

I am using nucleus for years, but because my webhosting company (speedpacket) is already 3 months not capable to put my website back online, i went for a whole new solution.  This new solution is BlogEngine.Net available on CodePlex

I'll try to import my old post, but nucleus doesn't support BlogML.  For now, my old posts (and domainname) will be unavailable...

Who.I.am

Certified Umbraco, .net and Azure developer, seo lover. Magician in my spare time.

Month List