Red Gate forums :: View topic - SQL Database
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SmartAssembly 6
SmartAssembly 6 forum

SQL Database

Search in SmartAssembly 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
mikehalford



Joined: 22 Nov 2012
Posts: 7

PostPosted: Mon Jan 14, 2013 1:57 pm    Post subject: SQL Database Reply with quote

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
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1117
Location: My desk.

PostPosted: Tue Jan 15, 2013 3:06 pm    Post subject: Reply with quote

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:

Code:
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)...

Code:
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:

Code:

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.
Back to top
View user's profile Send private message
mikehalford



Joined: 22 Nov 2012
Posts: 7

PostPosted: Wed Jan 16, 2013 11:57 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group