SQL Database

Comments

• James B Redgate › Posts: 1,124 Silver 4

  January 15, 2013 9:06AM

  Thanks for your post.
  I'm not sure how easily this will work tbh, as I think SA always checks the existence of the database and if you don't have permission to do that, then it'll fail every time even though the database is there. It does this by executing:
  

  IF DB_ID('SmartAssembly') IS NULL CREATE DATABASE SmartAssembly
  

  
  Based on a profiler trace it then does the following, but it's probably easier and more reliable to set up your own instance (SQL Express will do)...
  

  CREATE TABLE Information(Version INT, ServerLicense VARCHAR(MAX), MapFolderNetworkPath VARCHAR(255))
  INSERT INTO Information(Version) VALUES(1)
  CREATE TABLE Builds (AssemblyID uniqueidentifier NOT NULL, ProjectID uniqueidentifier NOT NULL, LastAccessDate DATETIME, Released BIT, BuildDate DATETIME, BuildVersion VARCHAR(23))
  CREATE UNIQUE INDEX BuildAssemblyIDIndex ON Builds(AssemblyID)
  CREATE INDEX BuildProjectIDIndex ON Builds(ProjectID)
  CREATE TABLE ExceptionReports(ID uniqueidentifier NOT NULL, ProjectID uniqueidentifier NOT NULL, AssemblyID uniqueidentifier, UserID uniqueidentifier, CreationDate DATETIME, InsertionDate DATETIME, ExceptionType VARCHAR(255), ExceptionMessage VARCHAR(255), TypeName VARCHAR(255), MethodName VARCHAR(255), Unread BIT, Fixed BIT, Flag TINYINT, Data IMAGE, HasAttachment BIT)
  CREATE UNIQUE INDEX ExceptionReportsIDIndex ON ExceptionReports(ID)
  CREATE INDEX ExceptionReportsProjectIDIndex ON ExceptionReports(ProjectID)
  CREATE INDEX ExceptionReportsUserIDIndex ON ExceptionReports(UserID)
  CREATE TABLE [Names](ID int NOT NULL IDENTITY CONSTRAINT NamePrimaryKey PRIMARY KEY, Name VARCHAR(255) NOT NULL)
  CREATE UNIQUE INDEX NameIndex ON [Names](Name)
  CREATE TABLE Projects(ID uniqueidentifier NOT NULL, Name VARCHAR(255), CryptoKey VARCHAR(MAX), ProjectFileName VARCHAR(255))
  CREATE UNIQUE INDEX ProjectsIDIndex ON Projects(ID)
  exec sp_executesql N'UPDATE Information SET Version=@1',N'@1 int',@1=2
  SELECT Version FROM Information
  ALTER TABLE Information ADD SerialNumber VARCHAR(255)
  exec sp_executesql N'UPDATE Information SET Version=@1',N'@1 int',@1=3
  SELECT Version FROM Information
  CREATE UNIQUE CLUSTERED INDEX [ExceptionReportsIDIndex] ON [dbo].[ExceptionReports] ([ID]) WITH (DROP_EXISTING = ON)
  CREATE NONCLUSTERED INDEX [ExceptionReportsAssemblyIDIndex] ON [dbo].[ExceptionReports] ([AssemblyID], [CreationDate] DESC) INCLUDE ([ExceptionMessage], [ExceptionType], [Fixed], [Flag], [HasAttachment], [ID], [InsertionDate], [MethodName], [ProjectID], [TypeName], [Unread], [UserID])
  CREATE NONCLUSTERED INDEX [ExceptionReportsProjectIDIndex] ON [dbo].[ExceptionReports] ([ProjectID], [CreationDate] DESC) INCLUDE ([AssemblyID], [ExceptionMessage], [ExceptionType], [Fixed], [Flag], [HasAttachment], [ID], [InsertionDate], [MethodName], [TypeName], [Unread], [UserID]) WITH (DROP_EXISTING = ON)
  CREATE NONCLUSTERED INDEX [ExceptionReportsReadnessCreationDateIndex] ON [dbo].[ExceptionReports] ([Unread], [CreationDate] DESC) INCLUDE ([AssemblyID], [ExceptionMessage], [ExceptionType], [Fixed], [Flag], [HasAttachment], [ID], [InsertionDate], [MethodName], [ProjectID], [TypeName], [UserID])
  CREATE UNIQUE CLUSTERED INDEX [BuildAssemblyIDIndex] ON [dbo].[Builds] ([AssemblyID]) WITH (DROP_EXISTING = ON)
  exec sp_executesql N'UPDATE Information SET Version=@1',N'@1 int',@1=4
  CREATE TABLE [dbo].[Categories](	[ExceptionType] [varchar](255) NOT NULL,	[TypeName] [varchar](255) NOT NULL, [MethodName] [varchar](255) NOT NULL,	[Completeness] [int] NOT NULL,	[CompletionDate] [datetime] NULL,	[Username] [varchar](50) NULL,	[CategoryID] [int] NOT NULL,	[ProjectID] [uniqueidentifier] NULL, CONSTRAINT [category_def] UNIQUE (ProjectID, ExceptionType, TypeName, MethodName))
  ALTER TABLE ExceptionReports ADD Completeness tinyint NOT NULL DEFAULT 1
  UPDATE ExceptionReports SET Completeness=3 WHERE Flag=100
  exec sp_executesql N'UPDATE Information SET Version=@1',N'@1 int',@1=5
  ALTER TABLE [ExceptionReports] ALTER COLUMN [Data] VARBINARY(MAX)
  CREATE TABLE [Features](ID int NOT NULL IDENTITY CONSTRAINT FeaturePrimaryKey PRIMARY KEY, Name VARCHAR(255) NOT NULL)
  CREATE UNIQUE INDEX FeatureIndex ON [Features](Name)
  CREATE TABLE [Sessions](
                      ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
                      ProjectID UNIQUEIDENTIFIER FOREIGN KEY REFERENCES [Projects](ID) NOT NULL,
                      AssemblyID UNIQUEIDENTIFIER FOREIGN KEY REFERENCES [Builds](AssemblyID) NOT NULL,
                      UserID UNIQUEIDENTIFIER NOT NULL,
                      SessionDate DATETIME NOT NULL)
  CREATE TABLE [FeatureReports](
                      SessionID UNIQUEIDENTIFIER FOREIGN KEY REFERENCES [Sessions](ID) NOT NULL,
                      FeatureID INT FOREIGN KEY REFERENCES [Features](ID) NOT NULL,
                      UsageCount INT NOT NULL,
                      CONSTRAINT FeatureReportPrimaryKey PRIMARY KEY CLUSTERED (FeatureID, SessionID))
  exec sp_executesql N'UPDATE Information SET Version=@1',N'@1 int',@1=6
  ALTER TABLE [Sessions] ADD UserHostAddress varchar(255)
  ALTER TABLE [FeatureReports] ALTER COLUMN [UsageCount] bigint
  CREATE NONCLUSTERED INDEX [SessionsAssemblyIDIndex] ON dbo.Sessions (AssemblyID)
  CREATE NONCLUSTERED INDEX SessionsProjectIDIndex ON dbo.Sessions (ProjectID)
  ALTER TABLE dbo.FeatureReports DROP CONSTRAINT [FeatureReportPrimaryKey]
  ALTER TABLE dbo.FeatureReports ADD CONSTRAINT FeatureReportPrimaryKey PRIMARY KEY (SessionID, FeatureID)
  exec sp_executesql N'UPDATE Information SET Version=@1',N'@1 int',@1=7
  DROP INDEX dbo.Names.NameIndex
  ALTER TABLE [Names] ALTER COLUMN [Name] VARCHAR(255) COLLATE Latin1_General_CS_AS
  CREATE UNIQUE INDEX NameIndex ON [Names](Name)
  exec sp_executesql N'UPDATE Information SET Version=@1',N'@1 int',@1=8
  ALTER TABLE dbo.Builds ADD CONSTRAINT PK_Builds PRIMARY KEY NONCLUSTERED (AssemblyID ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ALTER TABLE dbo.Projects ADD CONSTRAINT PK_Projects PRIMARY KEY CLUSTERED (ID ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ALTER TABLE dbo.ExceptionReports ADD CONSTRAINT PK_ExceptionReports PRIMARY KEY NONCLUSTERED (ID ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  CREATE NONCLUSTERED INDEX [index_Sessions_All] ON [dbo].[Sessions] ([ProjectID] ASC, [UserID] ASC, [SessionDate] ASC, [ID] ASC, [AssemblyID] ASC, [UserHostAddress] ASC )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
  CREATE NONCLUSTERED INDEX [index_Sessions_AssemblyProjectUser] ON [dbo].[Sessions] ([AssemblyID] ASC, [ProjectID] ASC, [UserHostAddress] ASC, [UserID] ASC )INCLUDE ( [ID], [SessionDate]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
  CREATE NONCLUSTERED INDEX [index_BuildsAll] ON [dbo].[Builds] ([ProjectID] ASC, [AssemblyID] ASC, [BuildDate] ASC )INCLUDE ( [BuildVersion]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
  CREATE NONCLUSTERED INDEX [index_SessionsAssemblyID] ON [dbo].[Sessions] ([AssemblyID] ASC )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
  exec sp_executesql N'UPDATE Information SET Version=@1',N'@1 int',@1=9
  

  
  It then does an update to set the serial, and also the map folder path:
  

  exec sp_executesql N'UPDATE Information SET SerialNumber=@1, MapFolderNetworkPath=@2',N'@1 varchar(8000),@2 varchar(6)',@1='',@2='\\server\mapfolder'
  

  
  That's obviously done with parameter replacement so you'd need to amend that query accordingly.
  
  That seems to be all it does as far as a SQL trace shows. I've not tried manually running this myself and as I mentioned above, creating an instance you do have full access to is the recommended way - but these commands may be helpful if you do want to try creating it manually.

  Systems Software Engineer

  Redgate Software

  0 · Share on Twitter
• mikehalford Posts: 8

  January 16, 2013 5:57AM

  Thanks James.
  
  Not sure it will work if you check for a database with a specific name. I thought the connection string would specify the database name.
  
  Also not sure how I can manage the map folders on a remote server but will give it a go.

  0 · Share on Twitter