Initialization script for MS SQL Server 2000

Apr 14, 2009 at 2:45 AM
I attempted to execute the DDL SQL initialization script (file: MSSQLSetup1.4.5.0.sql) on a MS SQL Server 2000 installation but it crashed in several places.

After examining the code, I suspected that the SQL statements were designed for use with Microsoft SQL Server 2005 or higher. I tried running the code on my personal machine, which has a copy of the free SQL Server Express 2005 installed and - sure enough - the database tables were created and initialized without complaint.

Does anyone have a copy of the DDL SQL initialization script that will work in MS SQL Server 2000?

(And, no, asking the client to upgrade to MS SQL 2005 is *not* an option)


Coordinator
Apr 14, 2009 at 5:09 AM
In case you weren't aware, BE 1.5 was just released a little earlier today.  Not sure if you want to install 1.4.5, or go up to 1.5.

To get the MSSQLSetup1.4.5.0.sql script to run on SQL 2000, there's just a few changes to make.

The most impactful change was for the data types of some columns.  In the original script, nvarchar(max) is used for some columns.  You might already know this, but this means there is no maximum to how much data can be stored in these columns.  The equivalent for this in SQL 2000 is 'ntext', but using ntext could cause problems since updating an ntext column requires using the WRITETEXT sql statement, rather than the standard UPDATE statement.  The nvarchar(max) data type from SQL 2005 replaces ntext -- ntext is now deprecated.

BE might not even use UPDATE statements for the nvarchar(max) columns -- I didn't look into it.  So for the script below, instead of using ntext, I used explicit data lengths for the nvarchar columns -- like 3000, 2900, etc.  The lengths I used are safe, and could possibly be increased a little bit.  The lengths can't be too long, or it will exceed the maximum allowable size for a row in SQL Server.  The maximum length is a little over 8,000 bytes, but this includes all the columns in a single row -- not just one column.  Each piece of text in a nvarchar column takes up 2 bytes, so the biggest nvarchar column you could create would be around nvarchar(4000), which would be 8000 bytes.  But since each table has more than just the one nvarchar column, I used lengths less than this.

For reference, here's the changes I made to it.

1. Search and replace.  Removed the line of text below.

WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

2. Search and replace.  Removed the line of text below.

WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

3. Search and replace.  Removed the line of text below.

TEXTIMAGE_ON [PRIMARY]

4. Changed the following nvarchar(max) columns to the following lengths

be_DataStoreSettings.Settings nvarchar(3700)
be_Pages.Description nvarchar(600)
be_Pages.PageContent nvarchar(2500)
be_Pages.Keywords nvarchar(400)
be_Posts.Description nvarchar(600)
be_Posts.PostContent nvarchar(2700)
be_Settings.SettingValue nvarchar(3700)
be_Profiles.SettingValue nvarchar(3700)
be_PostComment.Comment nvarchar(2800)

**** The final script is below.



/****** BlogEngine.NET 1.4.5 SQL Setup Script ******/

/****** Object:  Table [dbo].[be_Categories]    Script Date: 12/22/2007 14:14:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[be_Categories](
    [CategoryID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_be_Categories_CategoryID]  DEFAULT (newid()),
    [CategoryName] [nvarchar](50) NULL,
    [Description] [nvarchar](200) NULL,
    [ParentID] [uniqueidentifier] NULL,
 CONSTRAINT [PK_be_Categories] PRIMARY KEY CLUSTERED
(
    [CategoryID] ASC
)
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[be_DataStoreSettings]    Script Date: 06/28/2008 19:29:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[be_DataStoreSettings](
    [ExtensionType] [nvarchar](50) NOT NULL,
    [ExtensionId] [nvarchar](100) NOT NULL,
    [Settings] [nvarchar](3700) NOT NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[be_Pages]    Script Date: 12/22/2007 14:15:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[be_Pages](
    [PageID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_be_Pages_PageID]  DEFAULT (newid()),
    [Title] [nvarchar](255) NULL,
    [Description] [nvarchar](600) NULL,
    [PageContent] [nvarchar](2500) NULL,
    [Keywords] [nvarchar](400) NULL,
    [DateCreated] [datetime] NULL,
    [DateModified] [datetime] NULL,
    [IsPublished] [bit] NULL,
    [IsFrontPage] [bit] NULL,
    [Parent] [uniqueidentifier] NULL,
    [ShowInList] [bit] NULL,
 CONSTRAINT [PK_be_Pages] PRIMARY KEY CLUSTERED
(
    [PageID] ASC
)
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[be_PingService]    Script Date: 12/22/2007 14:15:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[be_PingService](
    [PingServiceID] [int] IDENTITY(1,1) NOT NULL,
    [Link] [nvarchar](255) NULL,
 CONSTRAINT [PK_be_PingService] PRIMARY KEY CLUSTERED
(
    [PingServiceID] ASC
)
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[be_Posts]    Script Date: 12/22/2007 14:16:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[be_Posts](
    [PostID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_be_Posts_PostID]  DEFAULT (newid()),
    [Title] [nvarchar](255) NULL,
    [Description] [nvarchar](600) NULL,
    [PostContent] [nvarchar](2700) NULL,
    [DateCreated] [datetime] NULL,
    [DateModified] [datetime] NULL,
    [Author] [nvarchar](50) NULL,
    [IsPublished] [bit] NULL,
    [IsCommentEnabled] [bit] NULL,
    [Raters] [int] NULL,
    [Rating] [real] NULL,
    [Slug] [nvarchar](255) NULL,
 CONSTRAINT [PK_be_Posts] PRIMARY KEY CLUSTERED
(
    [PostID] ASC
)
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[be_Settings]    Script Date: 12/22/2007 14:16:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[be_Settings](
    [SettingName] [nvarchar](50) NOT NULL,
    [SettingValue] [nvarchar](3700) NULL,
 CONSTRAINT [PK_be_Settings] PRIMARY KEY CLUSTERED
(
    [SettingName] ASC
)
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[be_Profiles]    Script Date: 06/28/2008 19:33:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[be_Profiles](
    [ProfileID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](100) NULL,
    [SettingName] [nvarchar](200) NULL,
    [SettingValue] [nvarchar](3700) NULL,
 CONSTRAINT [PK_be_Profiles] PRIMARY KEY CLUSTERED
(
    [ProfileID] ASC
)
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[be_StopWords]    Script Date: 06/28/2008 19:33:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[be_StopWords](
    [StopWord] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_be_StopWords] PRIMARY KEY CLUSTERED
(
    [StopWord] ASC
)
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[be_PostCategory]    Script Date: 12/22/2007 14:17:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[be_PostCategory](
    [PostCategoryID] [int] IDENTITY(1,1) NOT NULL,
    [PostID] [uniqueidentifier] NOT NULL,
    [CategoryID] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_be_PostCategory] PRIMARY KEY CLUSTERED
(
    [PostCategoryID] ASC
)
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[be_PostCategory]  WITH CHECK ADD  CONSTRAINT [FK_be_PostCategory_be_Categories] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[be_Categories] ([CategoryID])
GO
ALTER TABLE [dbo].[be_PostCategory] CHECK CONSTRAINT [FK_be_PostCategory_be_Categories]
GO
ALTER TABLE [dbo].[be_PostCategory]  WITH CHECK ADD  CONSTRAINT [FK_be_PostCategory_be_Posts] FOREIGN KEY([PostID])
REFERENCES [dbo].[be_Posts] ([PostID])
GO
ALTER TABLE [dbo].[be_PostCategory] CHECK CONSTRAINT [FK_be_PostCategory_be_Posts]
GO
/****** Object:  Table [dbo].[be_PostComment]    Script Date: 12/22/2007 14:17:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[be_PostComment](
    [PostCommentID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_be_PostComment_PostCommentID]  DEFAULT (newid()),
    [PostID] [uniqueidentifier] NOT NULL,
    [CommentDate] [datetime] NOT NULL,
    [Author] [nvarchar](255) NULL,
    [Email] [nvarchar](255) NULL,
    [Website] [nvarchar](255) NULL,
    [Comment] [nvarchar](2800) NULL,
    [Country] [nvarchar](255) NULL,
    [Ip] [nvarchar](50) NULL,
    [IsApproved] [bit] NULL,
 CONSTRAINT [PK_be_PostComment] PRIMARY KEY CLUSTERED
(
    [PostCommentID] ASC
)
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[be_PostComment]  WITH CHECK ADD  CONSTRAINT [FK_be_PostComment_be_Posts] FOREIGN KEY([PostID])
REFERENCES [dbo].[be_Posts] ([PostID])
GO
ALTER TABLE [dbo].[be_PostComment] CHECK CONSTRAINT [FK_be_PostComment_be_Posts]
GO
/****** Object:  Table [dbo].[be_PostNotify]    Script Date: 12/22/2007 14:17:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[be_PostNotify](
    [PostNotifyID] [int] IDENTITY(1,1) NOT NULL,
    [PostID] [uniqueidentifier] NOT NULL,
    [NotifyAddress] [nvarchar](255) NULL,
 CONSTRAINT [PK_be_PostNotify] PRIMARY KEY CLUSTERED
(
    [PostNotifyID] ASC
)
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[be_PostNotify]  WITH CHECK ADD  CONSTRAINT [FK_be_PostNotify_be_Posts] FOREIGN KEY([PostID])
REFERENCES [dbo].[be_Posts] ([PostID])
GO
ALTER TABLE [dbo].[be_PostNotify] CHECK CONSTRAINT [FK_be_PostNotify_be_Posts]
GO
/****** Object:  Table [dbo].[be_PostTag]    Script Date: 12/22/2007 14:17:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[be_PostTag](
    [PostTagID] [int] IDENTITY(1,1) NOT NULL,
    [PostID] [uniqueidentifier] NOT NULL,
    [Tag] [nvarchar](50) NULL,
 CONSTRAINT [PK_be_PostTag] PRIMARY KEY CLUSTERED
(
    [PostTagID] ASC
)
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[be_PostTag]  WITH CHECK ADD  CONSTRAINT [FK_be_PostTag_be_Posts] FOREIGN KEY([PostID])
REFERENCES [dbo].[be_Posts] ([PostID])
GO
ALTER TABLE [dbo].[be_PostTag] CHECK CONSTRAINT [FK_be_PostTag_be_Posts]
GO
/****** Object:  Table [dbo].[be_Users]    Script Date: 07/30/2008 21:55:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[be_Users](
    [UserID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](100) NOT NULL,
    [Password] [nvarchar](255) NOT NULL,
    [LastLoginTime] [datetime] NULL,
    [EmailAddress] [nvarchar](100) NULL,
 CONSTRAINT [PK_be_Users] PRIMARY KEY CLUSTERED
(
    [UserID] ASC
)
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[be_Roles]    Script Date: 07/30/2008 21:56:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[be_Roles](
    [RoleID] [int] IDENTITY(1,1) NOT NULL,
    [Role] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_be_Roles] PRIMARY KEY CLUSTERED
(
    [RoleID] ASC
)
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[be_UserRoles]    Script Date: 07/31/2008 12:26:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[be_UserRoles](
    [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [int] NOT NULL,
    [RoleID] [int] NOT NULL,
 CONSTRAINT [PK_be_UserRoles] PRIMARY KEY CLUSTERED
(
    [UserRoleID] ASC
)
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[be_UserRoles]  WITH CHECK ADD  CONSTRAINT [FK_be_UserRoles_be_Roles] FOREIGN KEY([RoleID])
REFERENCES [dbo].[be_Roles] ([RoleID])
GO
ALTER TABLE [dbo].[be_UserRoles] CHECK CONSTRAINT [FK_be_UserRoles_be_Roles]
GO
ALTER TABLE [dbo].[be_UserRoles]  WITH CHECK ADD  CONSTRAINT [FK_be_UserRoles_be_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[be_Users] ([UserID])
GO
ALTER TABLE [dbo].[be_UserRoles] CHECK CONSTRAINT [FK_be_UserRoles_be_Users]
GO
/****** Object:  Index [FK_PostID]    Script Date: 12/22/2007 14:18:36 ******/
CREATE NONCLUSTERED INDEX [FK_PostID] ON [dbo].[be_PostCategory]
(
    [PostID] ASC
)
GO
/****** Object:  Index [FK_CategoryID]    Script Date: 12/22/2007 14:19:19 ******/
CREATE NONCLUSTERED INDEX [FK_CategoryID] ON [dbo].[be_PostCategory]
(
    [CategoryID] ASC
)
GO
/****** Object:  Index [FK_PostID]    Script Date: 12/22/2007 14:19:45 ******/
CREATE NONCLUSTERED INDEX [FK_PostID] ON [dbo].[be_PostComment]
(
    [PostID] ASC
)
GO
/****** Object:  Index [FK_PostID]    Script Date: 12/22/2007 14:20:29 ******/
CREATE NONCLUSTERED INDEX [FK_PostID] ON [dbo].[be_PostNotify]
(
    [PostID] ASC
)
GO
/****** Object:  Index [FK_PostID]    Script Date: 12/22/2007 14:20:43 ******/
CREATE NONCLUSTERED INDEX [FK_PostID] ON [dbo].[be_PostTag]
(
    [PostID] ASC
)
GO
/****** Object:  Index [I_TypeID]    Script Date: 06/28/2008 19:34:43 ******/
CREATE NONCLUSTERED INDEX [I_TypeID] ON [dbo].[be_DataStoreSettings]
(
    [ExtensionType] ASC,
    [ExtensionId] ASC
)
GO
/****** Object:  Index [I_UserName]    Script Date: 06/28/2008 19:35:12 ******/
CREATE NONCLUSTERED INDEX [I_UserName] ON [dbo].[be_Profiles]
(
    [UserName] ASC
)
GO
/***  Load initial Data ***/
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('administratorrole', 'Administrators');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('alternatefeedurl', '');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('authorname', 'My name');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('avatar', 'combine');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('blogrollmaxlength', '23');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('blogrollupdateminutes', '60');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('blogrollvisibleposts', '3');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('contactformmessage', '<p>I will answer the mail as soon as I can.</p>');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('contactthankmessage', '<h1>Thank you</h1><p>The message was sent.</p>');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('culture', 'Auto');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('dayscommentsareenabled', '0');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('description', 'Short description of the blog');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('displaycommentsonrecentposts', 'True');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('displayratingsonrecentposts', 'True');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('email', 'user@example.com');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('emailsubjectprefix', 'Weblog');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('enablecommentsearch', 'True');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('enablecommentsmoderation', 'False');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('enablecontactattachments', 'True');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('enablecountryincomments', 'True');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('enablehttpcompression', 'True');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('enableopensearch', 'True');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('enablepingbackreceive', 'True');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('enablepingbacksend', 'True');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('enablerating', 'True');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('enablereferrertracking', 'False');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('enablerelatedposts', 'True');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('enablessl', 'False');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('enabletrackbackreceive', 'True');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('enabletrackbacksend', 'True');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('endorsement', 'http://www.dotnetblogengine.net/syndication.axd');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('fileextension', '.aspx');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('geocodinglatitude', '0');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('geocodinglongitude', '0');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('handlewwwsubdomain', '');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('iscocommentenabled', 'False');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('iscommentsenabled', 'True');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('language', 'en-GB');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('mobiletheme', 'Mobile');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('name', 'Name of the blog');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('numberofrecentcomments', '10');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('numberofrecentposts', '10');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('postsperfeed', '10');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('postsperpage', '10');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('removewhitespaceinstylesheets', 'True');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('searchbuttontext', 'Search');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('searchcommentlabeltext', 'Include comments in search');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('searchdefaulttext', 'Enter search term');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('sendmailoncomment', 'True');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('showdescriptioninpostlist', 'False');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('showlivepreview', 'True');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('showpostnavigation', 'True');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('smtppassword', 'password');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('smtpserver', 'mail.example.dk');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('smtpserverport', '25');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('smtpusername', 'user@example.com');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('storagelocation', '~/App_Data/');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('syndicationformat', 'Rss');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('theme', 'Standard');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('timestamppostlinks', 'True');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('timezone', '-5');
INSERT INTO be_Settings (SettingName, SettingValue)    VALUES ('trackingscript', '');

INSERT INTO be_PingService (Link) VALUES ('http://rpc.technorati.com/rpc/ping');
INSERT INTO be_PingService (Link) VALUES ('http://rpc.pingomatic.com/rpc2');
INSERT INTO be_PingService (Link) VALUES ('http://ping.feedburner.com');
INSERT INTO be_PingService (Link) VALUES ('http://www.bloglines.com/ping');
INSERT INTO be_PingService (Link) VALUES ('http://services.newsgator.com/ngws/xmlrpcping.aspx');
INSERT INTO be_PingService (Link) VALUES ('http://api.my.yahoo.com/rpc2 ');
INSERT INTO be_PingService (Link) VALUES ('http://blogsearch.google.com/ping/RPC2');
INSERT INTO be_PingService (Link) VALUES ('http://rpc.pingthesemanticweb.com/');

INSERT INTO be_StopWords (StopWord)    VALUES ('a');
INSERT INTO be_StopWords (StopWord)    VALUES ('about');
INSERT INTO be_StopWords (StopWord)    VALUES ('actually');
INSERT INTO be_StopWords (StopWord)    VALUES ('add');
INSERT INTO be_StopWords (StopWord)    VALUES ('after');
INSERT INTO be_StopWords (StopWord)    VALUES ('all');
INSERT INTO be_StopWords (StopWord)    VALUES ('almost');
INSERT INTO be_StopWords (StopWord)    VALUES ('along');
INSERT INTO be_StopWords (StopWord)    VALUES ('also');
INSERT INTO be_StopWords (StopWord)    VALUES ('an');
INSERT INTO be_StopWords (StopWord)    VALUES ('and');
INSERT INTO be_StopWords (StopWord)    VALUES ('any');
INSERT INTO be_StopWords (StopWord)    VALUES ('are');
INSERT INTO be_StopWords (StopWord)    VALUES ('as');
INSERT INTO be_StopWords (StopWord)    VALUES ('at');
INSERT INTO be_StopWords (StopWord)    VALUES ('be');
INSERT INTO be_StopWords (StopWord)    VALUES ('both');
INSERT INTO be_StopWords (StopWord)    VALUES ('but');
INSERT INTO be_StopWords (StopWord)    VALUES ('by');
INSERT INTO be_StopWords (StopWord)    VALUES ('can');
INSERT INTO be_StopWords (StopWord)    VALUES ('cannot');
INSERT INTO be_StopWords (StopWord)    VALUES ('com');
INSERT INTO be_StopWords (StopWord)    VALUES ('could');
INSERT INTO be_StopWords (StopWord)    VALUES ('de');
INSERT INTO be_StopWords (StopWord)    VALUES ('do');
INSERT INTO be_StopWords (StopWord)    VALUES ('down');
INSERT INTO be_StopWords (StopWord)    VALUES ('each');
INSERT INTO be_StopWords (StopWord)    VALUES ('either');
INSERT INTO be_StopWords (StopWord)    VALUES ('en');
INSERT INTO be_StopWords (StopWord)    VALUES ('for');
INSERT INTO be_StopWords (StopWord)    VALUES ('from');
INSERT INTO be_StopWords (StopWord)    VALUES ('good');
INSERT INTO be_StopWords (StopWord)    VALUES ('has');
INSERT INTO be_StopWords (StopWord)    VALUES ('have');
INSERT INTO be_StopWords (StopWord)    VALUES ('he');
INSERT INTO be_StopWords (StopWord)    VALUES ('her');
INSERT INTO be_StopWords (StopWord)    VALUES ('here');
INSERT INTO be_StopWords (StopWord)    VALUES ('hers');
INSERT INTO be_StopWords (StopWord)    VALUES ('his');
INSERT INTO be_StopWords (StopWord)    VALUES ('how');
INSERT INTO be_StopWords (StopWord)    VALUES ('i');
INSERT INTO be_StopWords (StopWord)    VALUES ('if');
INSERT INTO be_StopWords (StopWord)    VALUES ('in');
INSERT INTO be_StopWords (StopWord)    VALUES ('into');
INSERT INTO be_StopWords (StopWord)    VALUES ('is');
INSERT INTO be_StopWords (StopWord)    VALUES ('it');
INSERT INTO be_StopWords (StopWord)    VALUES ('its');
INSERT INTO be_StopWords (StopWord)    VALUES ('just');
INSERT INTO be_StopWords (StopWord)    VALUES ('la');
INSERT INTO be_StopWords (StopWord)    VALUES ('like');
INSERT INTO be_StopWords (StopWord)    VALUES ('long');
INSERT INTO be_StopWords (StopWord)    VALUES ('make');
INSERT INTO be_StopWords (StopWord)    VALUES ('me');
INSERT INTO be_StopWords (StopWord)    VALUES ('more');
INSERT INTO be_StopWords (StopWord)    VALUES ('much');
INSERT INTO be_StopWords (StopWord)    VALUES ('my');
INSERT INTO be_StopWords (StopWord)    VALUES ('need');
INSERT INTO be_StopWords (StopWord)    VALUES ('new');
INSERT INTO be_StopWords (StopWord)    VALUES ('now');
INSERT INTO be_StopWords (StopWord)    VALUES ('of');
INSERT INTO be_StopWords (StopWord)    VALUES ('off');
INSERT INTO be_StopWords (StopWord)    VALUES ('on');
INSERT INTO be_StopWords (StopWord)    VALUES ('once');
INSERT INTO be_StopWords (StopWord)    VALUES ('one');
INSERT INTO be_StopWords (StopWord)    VALUES ('ones');
INSERT INTO be_StopWords (StopWord)    VALUES ('only');
INSERT INTO be_StopWords (StopWord)    VALUES ('or');
INSERT INTO be_StopWords (StopWord)    VALUES ('our');
INSERT INTO be_StopWords (StopWord)    VALUES ('out');
INSERT INTO be_StopWords (StopWord)    VALUES ('over');
INSERT INTO be_StopWords (StopWord)    VALUES ('own');
INSERT INTO be_StopWords (StopWord)    VALUES ('really');
INSERT INTO be_StopWords (StopWord)    VALUES ('right');
INSERT INTO be_StopWords (StopWord)    VALUES ('same');
INSERT INTO be_StopWords (StopWord)    VALUES ('see');
INSERT INTO be_StopWords (StopWord)    VALUES ('she');
INSERT INTO be_StopWords (StopWord)    VALUES ('so');
INSERT INTO be_StopWords (StopWord)    VALUES ('some');
INSERT INTO be_StopWords (StopWord)    VALUES ('such');
INSERT INTO be_StopWords (StopWord)    VALUES ('take');
INSERT INTO be_StopWords (StopWord)    VALUES ('takes');
INSERT INTO be_StopWords (StopWord)    VALUES ('that');
INSERT INTO be_StopWords (StopWord)    VALUES ('the');
INSERT INTO be_StopWords (StopWord)    VALUES ('their');
INSERT INTO be_StopWords (StopWord)    VALUES ('these');
INSERT INTO be_StopWords (StopWord)    VALUES ('thing');
INSERT INTO be_StopWords (StopWord)    VALUES ('this');
INSERT INTO be_StopWords (StopWord)    VALUES ('to');
INSERT INTO be_StopWords (StopWord)    VALUES ('too');
INSERT INTO be_StopWords (StopWord)    VALUES ('took');
INSERT INTO be_StopWords (StopWord)    VALUES ('und');
INSERT INTO be_StopWords (StopWord)    VALUES ('up');
INSERT INTO be_StopWords (StopWord)    VALUES ('use');
INSERT INTO be_StopWords (StopWord)    VALUES ('used');
INSERT INTO be_StopWords (StopWord)    VALUES ('using');
INSERT INTO be_StopWords (StopWord)    VALUES ('very');
INSERT INTO be_StopWords (StopWord)    VALUES ('was');
INSERT INTO be_StopWords (StopWord)    VALUES ('we');
INSERT INTO be_StopWords (StopWord)    VALUES ('well');
INSERT INTO be_StopWords (StopWord)    VALUES ('what');
INSERT INTO be_StopWords (StopWord)    VALUES ('when');
INSERT INTO be_StopWords (StopWord)    VALUES ('where');
INSERT INTO be_StopWords (StopWord)    VALUES ('who');
INSERT INTO be_StopWords (StopWord)    VALUES ('will');
INSERT INTO be_StopWords (StopWord)    VALUES ('with');
INSERT INTO be_StopWords (StopWord)    VALUES ('www');
INSERT INTO be_StopWords (StopWord)    VALUES ('you');
INSERT INTO be_StopWords (StopWord)    VALUES ('your');

DECLARE @postID uniqueidentifier, @catID uniqueidentifier;

SET @postID = NEWID();
SET @catID = NEWID();

INSERT INTO be_Categories (CategoryID, CategoryName)
    VALUES (@catID, 'General');

INSERT INTO be_Posts (PostID, Title, Description, PostContent, DateCreated, Author, IsPublished)
    VALUES (@postID,
    'Welcome to BlogEngine.NET 1.4.5 using Microsoft SQL Server',
    'The description is used as the meta description as well as shown in the related posts. It is recommended that you write a description, but not mandatory',
    '<p>If you see this post it means that BlogEngine.NET 1.4.5 is running with SQL Server and the DbBlogProvider is configured correctly.</p>
    <h2>Setup</h2>
    <p>If you are using the ASP.NET Membership provider, you are set to use existing users.  If you are using the default BlogEngine.NET XML provider, it is time to setup some users.  Find the sign-in link located either at the bottom or top of the page depending on your current theme and click it. Now enter "admin" in both the username and password fields and click the button. You will now see an admin menu appear. It has a link to the "Users" admin page. From there you can change the username and password.</p>
    <h2>Write permissions</h2>
    <p>Since you are using SQL to store your posts, most information is stored there.  However, if you want to store attachments or images in the blog, you will want write permissions setup on the App_Data folder.</p>
    <h2>On the web </h2>
    <p>You can find BlogEngine.NET on the <a href="http://www.dotnetblogengine.net">official website</a>. Here you will find tutorials, documentation, tips and tricks and much more. The ongoing development of BlogEngine.NET can be followed at <a href="http://www.codeplex.com/blogengine">CodePlex</a> where the daily builds will be published for anyone to download.</p>
    <p>Good luck and happy writing.</p>
    <p>The BlogEngine.NET team</p>',
    GETDATE(),
    'admin',
    1);

INSERT INTO be_PostCategory (PostID, CategoryID)
    VALUES (@postID, @catID);
INSERT INTO be_PostTag (PostID, Tag)
    VALUES (@postID, 'blog');
INSERT INTO be_PostTag (PostID, Tag)
    VALUES (@postID, 'welcome');

INSERT INTO be_Users (UserName, Password, LastLoginTime, EmailAddress)
    VALUES ('Admin', '', GETDATE(), 'email@example.com');
INSERT INTO be_Roles (Role)
    VALUES ('Administrators');
INSERT INTO be_Roles (Role)
    VALUES ('Editors');
INSERT INTO be_UserRoles (UserID, RoleID)
VALUES (1, 1);

INSERT INTO be_DataStoreSettings (ExtensionType, ExtensionId, Settings)
VALUES (1, 'be_WIDGET_ZONE',
'<?xml version="1.0" encoding="utf-16"?>
<WidgetData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Settings>&lt;widgets&gt;&lt;widget id="d9ada63d-3462-4c72-908e-9d35f0acce40" title="TextBox" showTitle="True"&gt;TextBox&lt;/widget&gt;&lt;widget id="19baa5f6-49d4-4828-8f7f-018535c35f94" title="Administration" showTitle="True"&gt;Administration&lt;/widget&gt;&lt;widget id="d81c5ae3-e57e-4374-a539-5cdee45e639f" title="Search" showTitle="True"&gt;Search&lt;/widget&gt;&lt;widget id="77142800-6dff-4016-99ca-69b5c5ebac93" title="Tag cloud" showTitle="True"&gt;Tag cloud&lt;/widget&gt;&lt;widget id="4ce68ae7-c0c8-4bf8-b50f-a67b582b0d2e" title="RecentPosts" showTitle="True"&gt;RecentPosts&lt;/widget&gt;&lt;/widgets&gt;</Settings>
</WidgetData>');

GO


Apr 22, 2009 at 9:13 PM
This script does, indeed, run in MS SQL Server 2000 without any complaints. Thank you!

HOWEVER, now there is the problem that certain things will crash if too much data is supplied. For example, if someone attempts to post a comment that is longer than 2,800 characters, a run-time error will occur.

This has caused me to notice several shortcomings of BlogEngine:

1) Even when using XML files or Microsoft SQL Server 2005, the code should still validate that the supplied data is less than a certain maximum length. I'm sure that site administrators do not want people posting comments that are 999,999,999 characters long! There should be a mechanism, either in web.config or in the adminstration area, that allows you to alter maximum lengths for posts and comments, etc, and to show the visitor appropriate validation error messages.

2) When database errors occur, the code traps the error instead of letting the exception bubble-up. Normally, when I create a website from scratch, the first thing I do is write an error handler in the global.asax file which will email me details of any unhandled exceptions. This allows me to keep track of any 500 errors, even after development is over and the website is live to the public. By catching the exception and not allowing it to bubble up, I can't be notified when a database errors occurs.

3)  (This is more a criticism of AJAX in general) After, for example, a visitor inputs a comment, AJAX is used to post the comment to the back-end. But if the back-end crashes, no error messages are shown in the browser; The "Saving the Comment" spinning graphic just continues to spin indefinitely. This can make debugging and error reporting excruciatingly difficult, especially when you're relying on bug reports from non-programmers who don't have a clue how to ues their browser to see the results of an AJAX post-back (For those of you who don't know, use the Fiddler2 plug-in for IE). I'm not saying that you should be showing detailed debugging information to the visitor (although, in 'dev' mode that would be great), but something like a generic "Database query error occurred" message would be nice start.
Coordinator
Apr 22, 2009 at 9:30 PM
Edited Apr 22, 2009 at 9:32 PM
Thanks for getting back about the script.

I agree with your points # 2 and 3 ... logging errors is a good idea.  And letting users know that some problem has occurred rather than letting them just sit there is also a great idea.  Others have had problems with an error happening during an AJAX postback when a comment was being added, and the GIF just kept spinning.  Logging errors in the App_Data folder is my first thought -- except sometimes the reason a person is having an error in the first place is because they haven't yet enabled Write permissions on the App_Data folder when they're first setting up their blog!  Another possibility in addition to logging in the App_Data folder is for unhandled errors caught in global.asax or in an httpmodule to be displayed in the browser if the person is logged in (to prevent security details from leaking to hackers or visitors).

For # 1, with SQL 2005 and XML storage, for all intents and purposes, I don't think there is a limit on the post size.  In SQL 2005, it uses nvarchar(max) which means there is no limit, and with XML storage, maybe the limit the XML file containing the post could get to is 4 GB if you're on a 32 bit processor?  Not sure exactly.  A new setting could still be added in the settings area, like you suggested, but you might be the only one who needs it :)  At least this is the first time I remember seeing anyone run into this problem ... and that's just because you're on SQL 2000.  Something to consider, nevertheless.
Jun 28, 2009 at 6:46 PM

Hi Ben,

I had the same initial problem as paparazzi.  I executed MSSQLSetup1.4.5.0.sql from your response, which worked great. Thanks.

I'm using the code from 1.5.  I get the following error when I try to run it with 1.4.5.0, sql 2000 database:

 

Invalid column name 'ParentCommentID'.

Ben, from your comment above ("In case you weren't aware, BE 1.5 was just released a little earlier today.  Not sure if you want to install 1.4.5, or go up to 1.5.") I assume that I can run 1.5 with the SQL 2000 db, correct?  If so, what changes do  I need to make?

Thanks,

devilDroid

Coordinator
Jun 28, 2009 at 10:02 PM

In BE 1.5, there is a new column in the DB that wasn't there in 1.4.5.  In the BE 1.5 download, in the Setup\SQLServer folder, there is an upgrade script named MSSQLUpgradeTo1.5.0.0From1.4.5.0.sql.  If you run that script, it'll add the new column to your DB.

Jun 28, 2009 at 11:24 PM

devilDroid:

Just thought I'd let you know that I managed to get Blog Engine "working" using SQL Server 2000, but the site did not pass a security inspection (failed form validation during Quality Assurance testing). I considered re-programming large sections of the code, but in the end it was easier and cheaper for the client to upgrade to MS SQL 2005. This is especially true since Microsoft now allows you to download a free "Express Edition" version of SQL Server 2005.

It would have saved me a LOT of trouble if the documentation for Blog Engine had stated that the codebase was designed with SQL Server 2005 in mind.

Whomever is in charge of the website, please alter the online documention to clearly state that Blog Engine is only 100% compatible with SQL Server 2005.

 

Coordinator
Jun 28, 2009 at 11:59 PM

paparazzi:  While I understand your frustration, BE should be able to run on SQL 2000 without any modifications to BE.  Isn't that particular security inspection just something that your company has in place?  IOW, being able to pass that security inspection isn't an indicator of whether BE can be used with a SQL 2000 database.

Although it couldn't hurt to state BE is best run on SQL 2005 or higher, since the SQL script that comes with BE only runs on SQL 2005 or higher, I suspect most people would understand BE is designed to run on SQL 2005 or higher.

The BE codebase isn't actually designed to run on SQL 2005, SQL 2008, etc.  It includes an XML provider and a DB provider.  The DB provider is designed to run standard SELECT, UPDATE, INSERT and UPDATE statements against a database -- whether that be SQL Server, mySQL, VistaDB, Sqlite, Oracle, Access, etc.  The columns in each DB must be setup to use the best datatype that that DB offers and works with the standard SELECT, UPDATE, INSERT and DELETE statements.  SQL 2005/2008 uses nvarchar(max), mySql and Access uses 'longtext'.  SQL 2000 must use nvarchar.  The deprecated ntext data type cannot be used because UPDATE statements cannot be run against the ntext data type.  So nvarchar is the most appropriate datatype for SQL 2000.  And SQL 2000's nvarchar data type, in fact works.

Speaking of saving time, it would've saved me a lot of time in not having to create that modified script if you had said that having the client upgrade to SQL 2005 was an option.  You original post said upgrading was *not* an option.

Jun 29, 2009 at 5:20 AM

Hello Ben,

I was very thankful for your quick response with the SQL Server 2000 DDL Script.

As I said, I did get BE "working" with SQL Server 2000, but there is a difference between software "working", and software being robust and reliable even when confronted with unusual data. The real problem is that BE does not provide maximum-length validation for all inputs.

As you said, SQL Server 2005/2008 supports nvarchar(max) columns, which can handle up to 2^31 bytes, while SQL Server 2000 cannot. I suspect that many of the older and simpler databases such as Oracle 9i and Access suffer from the same restrictions. 

Part of standard quality assurance testing is to try to stuff an unusually large blog post into the database (say, 10,000 characters). With SQL 2000, this caused a run-time exception to occur, hence the failure of the QA test.

I started altering the code to perform data-length validation, but in the end it was faster and cheaper to get the client to upgrade the database. Plus, using SQL 2005 insures that in the future they can upgrade to newer versions of BE without any risk of my custom code conflicting with the new version. (Altering the source code of an open source software package always complicates future upgrades).

I wasn't going to mention it, but since you brought up the aspect of time, I'll let you know that using Blog Engine for this project caused me to get some egg on my face. The entire reason that I recommended that the client use BE was to save time while quickly adding some simple blogging capabilities to an existing website. But because of the issues with the DB it took a lot longer to deploy the project. The website was delivered much later than promised. Now that it's working, everyone is happy, but I think a little extra documentation could have saved me a whole lot of grief. 

On the Feature Table page of the documentation (http://www.dotnetblogengine.net/page/Feature-table.aspx) it says:

Data Storage: Provider based. XML or SQL Server, MySQL, VistaDB and many more

I don't think it would hurt anyone if this was clarified as, "Optimized for use with XML files or Microsoft SQL 2005/2008, but is provider based and can also be made to work with other brands such as...." 

Also, I do remember seeing some documentation that said that uploaded images must still be stored in the XML files, even if you're using a relational database (My site doesn't allow uploads, so I haven't spent much time looking at this part of the code). This causes a problem for anyone who is deploying on a load-balaced web server farm. Additionally, some hosting providers do not allow write-permission anywhere within the website (I know, I know, the app_data folder is a safe place for the web server to write files, but the hosting provider I was working with has a blanket security policy that the web application is not permitted to have write permission anywhere within the website files). However, these providers normally allow you to write to directories that are outside the web application. The problem with Blog Engine is that the path to the XML file (specified in the web.config) is a VIRTUAL PATH within the website, and it is filtered through the Server.MapPath method. If you attempt to access a path that is outside the website (ie: "../../../../my_data.xml" or even just "C:\my_data.xml"), you get an exception stating that MapPath cannot map to a path outside the web application. Classic Catch-22 situation.

So, in fact, now that I think about it, the full explanation for Data Storage Options is:

1) Ideal configuration is single, non-load balanced web server. Data is saved to XML files in app_data folder. Insure that you may add write-permission to the app_data folder.

2) If image uploads are not required, the data can be stored in a relational database, which permits the use of BE in a load-balanced, multiple web-server environment. The code has been optimized for Microsoft SQL Server 2005/2008.

3) The DB Provider class can be modified to work with any brand of database. Use at your own risk.

 

Jun 29, 2009 at 1:37 PM

Ah.  I saw the MSSQLUpgradeTo1.5.0.0From1.4.5.0.sql script but wrongly assumed that it was an update for people going from SQL 2000 to SQL 2005.  I'll try it now.  Thanks so much.

Coordinator
Jun 30, 2009 at 9:45 AM

Hi paparazzi,

Thanks for sharing your thoughts.  Sorry you couldn't deliver the product on time ... but I've been down that road too where something takes a lot more time than originally anticipated.

Uploaded images aren't stored in XML files, but they are stored as files in the App_Data folder.  I don't have much experience with load-balanced servers, but I *thought* that the file system is mirrored between load-balanced servers.  Meaning, if you add/edit/delete a file on one of the servers, those changes are mirrored on the other servers.

The one big issue people in web farms have had problems with when using BE, is that when new data enters the system (a new blog post, new comment, etc) only that one particular server in the web farm is aware of that new item.  This is because BE retrieves the data once from the data store (DB or XML) and keeps that data in memory, without requerying the data store.  So even though the new blog post may be saved to the DB or to the App_Data folder, the other servers in the farm never check the data store for new data, and continue to use the (old) cached data they each have in memory.

Anyhow, it's probably best you installed SQL 2005 since the express edition is free and it offers the nvarchar(max) data types among other new features.  You could have even gone to SQL 2008 which has been out since last August and SP1 is already available.

Jun 30, 2009 at 12:48 PM

It works!  Thanks so much.