Posts

Showing posts from September, 2009

Convert SQL types in destination to SQL type in source

Another little script I needed for today. Feel free to expand on the CASE statement for your own types (which I will do should I need to run this again). /* Will transform destination table's column types to match the source database's (when column names match) codesnippet:83d07e82-3866-4877-849f-0ae06322b9fb */ declare @destination varchar(50) declare @source varchar(50) ---- Configuration ----- set @destination = 'PlanImport' set @source = 'Plan' ------------------------ declare @i int declare @count int declare @sql varchar(8000) declare @columnName varchar(8000) declare @columnType varchar(8000) declare @columns table (id int identity(1,1), columnName varchar(50), columnType varchar(50) ) -- insert source columns that are in destination insert into @columns select source.[Name], (CASE source_types.[Name] WHEN 'decimal' THEN source_types.[Name] + '(' + cast(source.[precision] as varchar(50)) + ',' + cast(source.[scal

Convert literal SQL 'NULL' to real NULL

Pretty straight forward. Had to do this today & thought I'd share the solution I came up with. /* Will turn literal "NULL" into SQL NULL in any table (for all columns) codesnippet:25616582-ced6-4e59-ab4c-25d2128cc4b6 */ declare @tableName varchar(50) ---- Configuration ----- set @tableName = 'PlanImport' ------------------------ declare @i int declare @count int declare @sql varchar(8000) declare @columnName varchar(8000) declare @columns table (id int identity(1,1), columnName varchar(50)) insert into @columns select [Name] from sys.columns where object_id = object_id(@tableName) and system_type_id in (select system_type_id from sys.types where [name] like '%varchar%' or [name] like '%text%' ) set @i = 1 set @count = (select max(id) from @columns) while(@i <= @count) begin set @columnName = (SELECT [columnName] FROM @columns WHERE [ID] = @i) set @sql = 'UPDATE [' + @tableName + '] SET [' + @columnName + ']

Fluent NHibernate - Incorrect syntax near the keyword 'Group'

I have to share this. I spent a good amount of time searching around Fluent NHibernate for a way to escape columns in the generated SQL while still using AutoMapping. My initial configuration looked like: Fluently.Configure() .Database(MsSqlConfiguration.MsSql2005.ConnectionString( c => c.Is(ConfigurationManager.ConnectionStrings["Something"].ConnectionString))) .Mappings(x => x.AutoMappings.Add(AutoPersistenceModel.MapEntitiesFromAssemblyOf () .WithSetup(convention => { convention.FindIdentity = p => p.Name == p.DeclaringType.Name + "Id"; convention.GetComponentColumnPrefix = type => type.Name + "Id"; } ) .BuildSessionFactory() This works great, but I had a property in my class (SomeType) that is called Group. This was causing the SqlException: Incorrect syntax near the keyword 'Group' (others would be like: Incorrect syntax near the keyword 'User' or Incorrect syntax near the keyword