Skip to content

Problems with NHibernate when using new database and copying from old database

May 25, 2012

This week, I had been mainly looking at the database of MS SQL in particular focus with data structures and how it ties with NHibernate. The reason is because in NHibernate, when I simply used INSERT INTO AND SELECT, it showed this error SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

Therefore my intentions were to copy the data structure from the old database to the new database in order to eliminate this NHibernate error (which affected non date time columns in other tables aswell!), and clear the data in the new database, setting the index of every database table to 1. Easy said than done right, almost!

Here is the script I wrote to clear the data: – 

USE [database_new]
/****** Script for SelectTopNRows command from SSMS  ******/
/* Disable Constraints & Triggers */
exec sp_MSforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINTS ALL’
exec sp_MSforeachtable ‘ALTER TABLE ? DISABLE TRIGGER ALL’

/* Perform delete operation on all table for cleanup */
exec sp_MSforeachtable ‘DELETE?’

/* Enable Constraints & Triggers */
exec sp_MSforeachtable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
exec sp_MSforeachtable ‘ALTER TABLE ? ENABLE TRIGGER ALL’

/* Reset Identity on Tables with identity column */
exec sp_MSforeachtable ‘IF OBJECT PROPERTY (OBJECT_ID(“?”), “TableHasIdentity”) = 1
BEGIN DBCC CHECKIDENT(“?”,RESEED,0) END’

(however it didn’t set it to index 1 for every table, and had to set it manually using the last bit of code on each table.)

 

Advertisements

From → Uncategorized

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: