SQL Database

.NET obfuscator and automated error reporting

Moderators: Luke Jefferson, melvyn.harbour, dom.smith

SQL Database

Postby mikehalford » Mon Jan 14, 2013 1:57 pm

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
mikehalford
 
Posts: 7
Joined: Thu Nov 22, 2012 1:32 pm

Postby james.billings » Tue Jan 15, 2013 3:06 pm

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: Select all
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: Select all
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: Select all
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.
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby mikehalford » Wed Jan 16, 2013 11:57 am

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.
mikehalford
 
Posts: 7
Joined: Thu Nov 22, 2012 1:32 pm


Return to SmartAssembly 6

Who is online

Users browsing this forum: No registered users and 0 guests