SQL Database

mikehalfordmikehalford Posts: 8
edited January 16, 2013 5:57AM in SmartAssembly
We would like to use the SQL database option to store error reports.

Unfortunately the database we want to use is on a shared server and there for we do not have access to the master database we only have access to a database we own.

Is it possible to get hold of any script to create the required tables etc. in our database. Once this is done we will point the error reporting to the updated database.


Many Thanks

Comments

  • 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

  • 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.
Sign In or Register to comment.