SQL Server – <CharlieDigital/>

April 4, 2014

FluentNHibernate and SQL Date Generation

So you’d like your SQL entry to have a system generated date/time, eh? Here is a sample table:

CREATE TABLE [dbo].[AuditLog] (
	Id int IDENTITY(1,1) PRIMARY KEY,
	EventUtc datetime2(7) DEFAULT(SYSUTCDATETIME()) NOT NULL,
	EventOffsetUtc datetimeoffset(7) DEFAULT(SYSDATETIMEOFFSET()) NOT NULL,
	EntityContextUid uniqueidentifier,
	EntityContextName nvarchar(256),
	EntityContextType varchar(128),
	UserLogin nvarchar(128),
	EventName varchar(128),
	AppContext varchar(64),
	EventData nvarchar(max),
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

1

2

3

4

5

6

7

8

9

10

11

12

CREATE TABLE [dbo].[AuditLog] (

Id int IDENTITY(1,1) PRIMARY KEY,

EventUtc datetime2(7) DEFAULT(SYSUTCDATETIME()) NOT NULL,

EventOffsetUtc datetimeoffset(7) DEFAULT(SYSDATETIMEOFFSET()) NOT NULL,

EntityContextUid uniqueidentifier,

EntityContextName nvarchar(256),

EntityContextType varchar(128),

UserLogin nvarchar(128),

EventName varchar(128),

AppContext varchar(64),

EventData nvarchar(max),

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

To spare you hours dealing with this error:

System.Data.SqlTypes.SqlTypeException:
   SqlDateTime overflow. Must be between
   1/1/1753 12:00:00 AM and 12/31/9999
   11:59:59 PM.

1

2

3

4

System.Data.SqlTypes.SqlTypeException:

SqlDateTime overflow. Must be between

1/1/1753 12:00:00 AM and 12/31/9999

11:59:59 PM.

What you need to do is to use the following mapping for...

SQL Query for Multi-Values In An Encoded String

Self Note / SQL Server

July 26, 2013

SQL Query for Multi-Values In An Encoded String

Consider a table with an textual column that encodes multi-values like so:

1|US;2|CA;3|MX

1	1\|US;2\|CA;3\|MX

How can we query for all rows using an OR criteria? For a single value, it’s quite easy by searching for the string (in this case, an...

FluentNHibernate vs. Code First in EF 4.1

.Net / Dev / SQL Server

June 22, 2011

FluentNHibernate vs. Code First in EF 4.1

First, having been in the SharePoint space exclusively for so long now, I have to admit: it’s been a while since I’ve had to deal with non-SharePoint data access. I don’t know if I miss it or not 😀 I’ve...

SQL Server

June 5, 2009

T-SQL MERGE: My New Friend

As I was catching up on SQL 2008 this morning, I spent some time working with the T-SQL MERGE command, new in SQL 2008. Most of the blogs and examples online are a bit terse, so I decided to write...

SQL Server

April 24, 2008

FOR XML Needs More Love

I’m constantly amazed by the number of developers who have never worked with FOR XML EXPLICIT and the new FOR XML PATH. If I were designing data access, it would be my go-to commands for building queries for complex data...

Dev / Rants / SQL Server

September 27, 2007

Dynamic SQL: Yea or Nay?

I’ve always been on the side of stored procedures in the classic debate over the merits of dynamic SQL. In reality, I can only think of one good scenario where dynamic SQL at the application layer should be used: programmatic batch...

Working With SQL Server Compact Edition 2005

.Net / Dev / SQL Server

August 21, 2007

Working With SQL Server Compact Edition 2005

One interesting issue that I just solved involved how to specify the location of the database file for a SQL Server Compact Edition 2005 connection string in a .Net add-in for Microsoft Office. You see, when the add-in starts, it...

Updating Reporting Services 2000 Connection Info

SQL Server

April 17, 2007

Updating Reporting Services 2000 Connection Info

If you’re running Reporting Services 2000 and you accidentally change the password or remove the original account used by Reporting Services, you will have to update this information. With Reporting Services 2005, there’s a nice GUI utility that you can use to...

Dev / SharePoint / SQL Server

January 8, 2007

ContentTypeIds In WSS3

In WSS3, if you execute the following SQL:

<span style="font-family: Courier;">    <span style="color: #008000;">SELECT</span>
        <span style="color: #000000;">ContentTypeId</span>
    <span style="color: #008000;">FROM </span>
        <span style="color: #000000;">ContentTypes</span>
</span>

1

2

3

4

5

SELECT

ContentTypeId

FROM

ContentTypes

You will notice that the content types are represented in the output as hex. If you take a look at the table definition, you’ll see that the actual data type of...

ASP.Net 2.0 / Life / SQL Server

November 18, 2005

Fun in Philly

Wow, I had a lot more fun in Philly than I thought I would have. Back up a bit. I had originally registered for the Microsoft launch event in Philly as a participant a few weeks back. Last week, Rich,...

Category: SQL Server

FluentNHibernate and SQL Date Generation

SQL Query for Multi-Values In An Encoded String

FluentNHibernate vs. Code First in EF 4.1

T-SQL MERGE: My New Friend

FOR XML Needs More Love

Dynamic SQL: Yea or Nay?

Working With SQL Server Compact Edition 2005

Updating Reporting Services 2000 Connection Info

ContentTypeIds In WSS3

Fun in Philly

Recent Posts

Recommended

Calendar

Browse by Category

Browse Archives

Meta