{"action":"created","issue":{"url":"https://api.github.com/repos/serilog/serilog-sinks-mssqlserver/issues/22","repository_url":"https://api.github.com/repos/serilog/serilog-sinks-mssqlserver","labels_url":"https://api.github.com/repos/serilog/serilog-sinks-mssqlserver/issues/22/labels{/name}","comments_url":"https://api.github.com/repos/serilog/serilog-sinks-mssqlserver/issues/22/comments","events_url":"https://api.github.com/repos/serilog/serilog-sinks-mssqlserver/issues/22/events","html_url":"https://github.com/serilog/serilog-sinks-mssqlserver/issues/22","id":151300558,"number":22,"title":"Cannot seem to get entries written to my logging table.....","user":{"login":"natiki","id":4738140,"avatar_url":"https://avatars.githubusercontent.com/u/4738140?v=3","gravatar_id":"","url":"https://api.github.com/users/natiki","html_url":"https://github.com/natiki","followers_url":"https://api.github.com/users/natiki/followers","following_url":"https://api.github.com/users/natiki/following{/other_user}","gists_url":"https://api.github.com/users/natiki/gists{/gist_id}","starred_url":"https://api.github.com/users/natiki/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/natiki/subscriptions","organizations_url":"https://api.github.com/users/natiki/orgs","repos_url":"https://api.github.com/users/natiki/repos","events_url":"https://api.github.com/users/natiki/events{/privacy}","received_events_url":"https://api.github.com/users/natiki/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/serilog/serilog-sinks-mssqlserver/labels/question","name":"question","color":"cc317c"}],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":3,"created_at":"2016-04-27T05:16:10Z","updated_at":"2016-05-04T02:08:32Z","closed_at":"2016-05-04T02:08:32Z","body":"Hi,\r\n\r\nMy environment is:\r\n\r\n- .NET 4.61\r\n- Serilog 1.5.14\r\n- Serilog.Sinks.MSSqlServer 3.0.52\r\n- SQL Server Microsoft SQL Server 2012 - 11.0.5343.0 (X64) \r\n\r\nApologies for the complex example, but I am dropping SQL server logging into an already configured system so need to get it to work as it is already configured.\r\n\r\nThe current Serilog configuration is working to both Email as well as UDP, so I am pretty confident that the issue is not with the serilog side of things, but rather with my (mis)configuration of the SQL server sink. I scaffold things as follows:\r\n\r\n```SQL\r\nCREATE TABLE [DonoLog] (\r\n\r\n   [Id] int IDENTITY(1,1) NOT NULL,\r\n   [Message] nvarchar(max) NULL,\r\n   [MessageTemplate] nvarchar(max) NULL,\r\n   [Level] nvarchar(128) NULL,\r\n   [TimeStamp] datetimeoffset(7) NOT NULL,  -- use datetime for SQL Server pre-2008\r\n   [Exception] nvarchar(max) NULL,\r\n   [Properties] xml NULL,\r\n   [LogEvent] nvarchar(max) NULL,\r\n   [ApplicationHostName] nvarchar(max) NULL,\r\n   [ApplicationName] nvarchar(max) NULL,\r\n   [ApplicationVersion] nvarchar(max) NULL,\r\n   [LogVersion] nvarchar(max) NULL,\r\n   [DateTimeLocal] datetime NULL,\r\n   [DateTimeUtc] datetime NULL,\r\n   [ExceptionPayload] nvarchar(max) NULL,\r\n   [LevelRange] int NULL,\r\n   [Location] nvarchar(max) NULL,\r\n   [StackTrace] nvarchar(max) NULL,\r\n   [UserDataKind] nvarchar(max) NULL,\r\n   [UserData] nvarchar(max) NULL\r\n\r\n   CONSTRAINT [PK_Logs] \r\n     PRIMARY KEY CLUSTERED ([Id] ASC) \r\n     WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,\r\n           ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) \r\n     ON [PRIMARY]\r\n\r\n) ON [PRIMARY];\r\n```\r\n\r\n```c#\r\n                LoggerConfiguration loggerConfiguration = new LoggerConfiguration()\r\n                    .Enrich.WithProperty(\"ApplicationHostName\", aParams.ApplicationHostName)\r\n                    .Enrich.WithProperty(\"ApplicationName\", aParams.ApplicationName)\r\n                    .Enrich.WithProperty(\"ApplicationVersion\", aParams.ApplicationVersion)\r\n                    .Enrich.WithProperty(\"LogVersion\", aParams.LogVersion)\r\n                    .MinimumLevel.Verbose();\r\n\r\n                    ColumnOptions columnOptions = new ColumnOptions\r\n                                                  {\r\n                                                      AdditionalDataColumns = new Collection<DataColumn>\r\n                                                                              {\r\n                                                                                  new DataColumn {DataType = typeof(string), ColumnName = \"ApplicationHostName\"},\r\n                                                                                  new DataColumn {DataType = typeof(string), ColumnName = \"ApplicationName\"},\r\n                                                                                  new DataColumn {DataType = typeof(string), ColumnName = \"ApplicationVersion\"},\r\n                                                                                  new DataColumn {DataType = typeof(string), ColumnName = \"LogVersion\"},\r\n                                                                                  new DataColumn {DataType = typeof(DateTime), ColumnName = \"DateTimeLocal\"},\r\n                                                                                  new DataColumn {DataType = typeof(DateTime), ColumnName = \"DateTimeUtc\"},\r\n                                                                                  new DataColumn {DataType = typeof(string), ColumnName = \"ExceptionPayload\"},\r\n                                                                                  new DataColumn {DataType = typeof(int), ColumnName = \"LevelRange\"},\r\n                                                                                  new DataColumn {DataType = typeof(string), ColumnName = \"Location\"},\r\n                                                                                  new DataColumn {DataType = typeof(string), ColumnName = \"StackTrace\"},\r\n                                                                                  new DataColumn {DataType = typeof(string), ColumnName = \"UserDataKind\"},\r\n                                                                                  new DataColumn {DataType = typeof(string), ColumnName = \"UserData\"}\r\n                                                                              }\r\n                                                  };\r\n\r\n                    loggerConfiguration.WriteTo.MSSqlServer(\"Data Source=ozdevsvr003;Initial Catalog=Logging;Integrated Security=True\", \"DonoLog\", LogEventLevel.Verbose, 50, null, null, false,\r\n                        columnOptions);\r\n```\r\n\r\nand perform a write with:\r\n\r\n```c#\r\n                ILogger ctx = aParams.Logger\r\n                                     .ForContext(\"DateTimeLocal\", aParams.Entry.DateTimeUtc.ToLocalTime())\r\n                                     .ForContext(\"DateTimeUtc\", aParams.Entry.DateTimeUtc)\r\n                                     .ForContext(\"ExceptionPayload\",\r\n                                         JsonConvert.SerializeObject(aParams.Entry.Exception, Formatting.None, new JsonSerializerSettings {ReferenceLoopHandling = ReferenceLoopHandling.Ignore}))\r\n                                     .ForContext(\"LevelRange\", aParams.Entry.LevelRange)\r\n                                     .ForContext(\"Location\", aParams.Entry.Location)\r\n                                     .ForContext(\"StackTrace\", aParams.Entry.StackTrace)\r\n                                     .ForContext(\"UserDataKind\", aParams.Entry.UserDataKind)\r\n                                     .ForContext(\"UserData\", aParams.Entry.UserData);\r\n\r\n     //Do the write\r\n    ctx.Verbose(aParams.Entry.Message);\r\n```\r\n\r\nI have SeriLog logging any issues via SelfLog.Out but that reports nothing. When tracing the SQL being sent to SQL Server I see the following SQL:\r\n\r\n```SQL\r\ninsert bulk DonoLog ([Message] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, [MessageTemplate] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, [Level] NVarChar(128) COLLATE SQL_Latin1_General_CP1_CI_AS, [TimeStamp] DateTimeOffset(7), [Exception] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, [Properties] Xml, [ApplicationHostName] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, [ApplicationName] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, [ApplicationVersion] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, [LogVersion] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, [DateTimeLocal] DateTime, [DateTimeUtc] DateTime, [ExceptionPayload] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, [LevelRange] Int, [Location] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, [StackTrace] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, [UserDataKind] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, [UserData] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS)\r\n```\r\n\r\nBut nothing appears in the table. So if I am reading this correctly there is potentially no data being sent to SQL Server, or perhaps the Trace Tool does not show the binary data? As I said at the start the other sinks I have configured (email & UDP) do indeed show the logged item.\r\n\r\nHow do I proceed from here to determine why my data is not being written into the database?"},"comment":{"url":"https://api.github.com/repos/serilog/serilog-sinks-mssqlserver/issues/comments/216722988","html_url":"https://github.com/serilog/serilog-sinks-mssqlserver/issues/22#issuecomment-216722988","issue_url":"https://api.github.com/repos/serilog/serilog-sinks-mssqlserver/issues/22","id":216722988,"user":{"login":"natiki","id":4738140,"avatar_url":"https://avatars.githubusercontent.com/u/4738140?v=3","gravatar_id":"","url":"https://api.github.com/users/natiki","html_url":"https://github.com/natiki","followers_url":"https://api.github.com/users/natiki/followers","following_url":"https://api.github.com/users/natiki/following{/other_user}","gists_url":"https://api.github.com/users/natiki/gists{/gist_id}","starred_url":"https://api.github.com/users/natiki/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/natiki/subscriptions","organizations_url":"https://api.github.com/users/natiki/orgs","repos_url":"https://api.github.com/users/natiki/repos","events_url":"https://api.github.com/users/natiki/events{/privacy}","received_events_url":"https://api.github.com/users/natiki/received_events","type":"User","site_admin":false},"created_at":"2016-05-04T02:08:32Z","updated_at":"2016-05-04T02:08:32Z","body":"Hi,\r\n\r\nI am getting different behavior depending on whether I am running this in our MVC app (it works) or our Test Runners (doesn't work). Unfortunately this is where the complexities start. Our test runners setup transactions in a particular isolation, and due to the ASYNC nature of the underlying code for this sink the life cycle in the test runner is totally different to our MVC production code.\r\n\r\nI downloaded the master branch and stepped through the code and while I found the following I don't believe it is conclusive as I ran it as part of our unit test runner which has a specific transaction isolation configured (see above), has a totally different lifecycle to how we use it in MVC etc.\r\n\r\nHowever for completeness.....\r\n\r\n- MyCode: Call ILogger.Verbose() \r\n- MSQL Sink Code:  Stepping code leads to highlighted foreach statement \r\n\r\n![image](https://cloud.githubusercontent.com/assets/4738140/15003501/e15c6668-11ef-11e6-953f-db83c4ec954d.png)\r\n- Next step brings me to MyCode:\r\n\r\n![image](https://cloud.githubusercontent.com/assets/4738140/15003539/32013aa8-11f0-11e6-97ef-5b6f4a762355.png)\r\n\r\n- At this point in the test runner things are close to finishing the test and hence forth transactions rolled up and so on. However internally the sink is still doing \"its thing\" and ultimately calling async connections and writes.\r\n\r\nSo I cannot see anything fundamentally wrong with the sink code I am going to close this issue until I have done some more research on my end to see if I can narrow this down until I can conclusively provide a test harness replicating what I am seeing.\r\n\r\n"}}