I'm working on creating a custom table for storing log data for a module we have, and have ran into an issue with the create routine. I've been following this tutorial, and I've implemented it as shown in the example. The issue that I'm running into is, the database is coming back with an error stating that varchar isn't supported, while the generated SQL that I've created does not have a varchar included:
if OBJECT_ID('dbo.tblLoggerErrorsDataStoreBigTable', 'U') is null
create table [dbo].[tblLoggerErrorsDataStoreBigTable]
([pkId] bigint not null,
[Row] int not null default(1) constraint CH_tblLoggerErrorsDataStoreBigTable check ([Row]>=1),
[StoreName] nvarchar(128) not null,
[ItemType] nvarchar(512) not null,
[LogId] int not null,
[Created] datetime null,
[Author] nvarchar(100) null,
[LogInfo] nvarchar(max) null,
constraint [PK_tblLoggerErrorsDataStoreBigTable] primary key clustered([pkId],[Row]),
constraint [FK_tblLoggerErrorsDataStoreBigTable_tblBigTableIdentity] foreign key ([pkId])
references [tblBigTableIdentity]([pkId])); IF NOT EXISTS(SELECT * FROM sys.indexes WHERE Name = 'IDX_tblLoggerErrorsDataStoreBigTable_LogId')
CREATE NONCLUSTERED INDEX [IDX_tblLoggerErrorsDataStoreBigTable_LogId]
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON); IF NOT EXISTS(SELECT * FROM sys.indexes WHERE Name = 'IDX_tblLoggerErrorsDataStoreBigTable_Author')
CREATE NONCLUSTERED INDEX [IDX_tblLoggerErrorsDataStoreBigTable_Author]
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
The goal here is to be able to insert log records into this table as needed for reporting purposes. I'm trying to create a four column table with reference to the DDS so that I can leverage a cleaner query call vs storing data directly in the primary BigTable. The above fails out when I call GetOrCreateStore() on my IDynamicData object.
Any direction or info on what I may be doing wrong here, would be great. Thanks.
May I ask why you want to build a custom BigTable, rather than making a custom table and interact with it using Dapper or EntityFramework?
If you will only be using your custom BigTable for a single purpose (logging) I believe that there will much trouble than it is worth.
Another thing, Sean.
I don't know which kind of logs you are going to store, or how you are going to use or retrieve them.
But if you are going to store many raw log entries in that table, performance may not be as good as you expect. Maybe you would be interested in logging to Azure Application Insights instead of a custom log table. If you need info and sample code have a look at my blog post on the topic.
In short, it is free (or very cheap), cloud-based, very fast at logging and its querying/analytics part is very powerful.