Update column data type from text/ntext and image to vachar(max)/Nvarchar(max) and Varbinary(max) for DOTNETNUKE (DNN) database

Posted: June 24, 2010 | Author: | Filed under: SQL Server 2005 | Leave a comment
I don’t know why DNN keeps the old data type without changing it for so many years.
If you want to go ahead to update text/ntext column to newer varchar(max)Nvarchar(max) syntax in SQL Server 2005/8, you can try out the script I found and modified from a blogger comment:

http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx

 

SELECT  table_name, column_name,

(

‘alter table [‘

+ table_name + ‘] ‘ +

‘alter column [‘

+ column_name +‘] ‘ +

case

when data_type = ‘image’ then ‘varbinary(max)’ else ‘ ‘ end + ‘ ‘ +

case

when data_type = ‘text’ then ‘varchar(max)’ else ‘ ‘ end + ‘ ‘ +

case

when data_type = ‘ntext’ then ‘nvarchar(max)’ else ‘ ‘ end + ‘ ‘ +

case

when is_nullable = ‘yes’ then ‘null’ else ‘not null’ end + ‘;’ + char(10) +

‘update [‘

+ table_name + ‘] set [‘ + column_name + ‘] = [‘ + column_name + ‘];’

+

char(10) +char(13) ) [sql]

into

#tmp

FROM

(select a.table_name, a.Column_name, a.data_type, a.is_nullable from INFORMATION_SCHEMA.COLUMNS a

inner

join INFORMATION_SCHEMA.TABLES b on a.table_Name=b.Table_Name

where

b.Table_type=‘BASE Table’) t

WHERE

data_type = ‘ntext’ or data_type = ‘text’ or data_type = ‘image’

while

exists(select 1 from #tmp)

begin

set

nocount on

declare

@sql nvarchar(max)

select

top 1 @sql = [sql] from #tmp

set

nocount off

exec

sp_executesql @sql

set

nocount on

delete

from #tmp where [sql] = @sql

end

–select [sql] from #tmp

–drop table #tmp

 

Link to Scott’s SEO tips with URL rewrite

Posted: June 13, 2010 | Author: | Filed under: SEO related | Leave a comment

Convert Month Number to Month Name

Posted: June 2, 2010 | Author: | Filed under: SQL Server | Leave a comment

declare @t table (num int)
insert into @t values (1)
insert into @t values (2)
insert into @t values (3)
insert into @t values (4)
insert into @t values (5)
insert into @t values (6)
insert into @t values (7)
insert into @t values (8)
insert into @t values (9)
insert into @t values (10)
insert into @t values (11)
insert into @t values (12)
insert into @t values (13)

SELECT DateName(month,Dateadd(month,num-1,0)) as MonthName
FROM @t

Convert Day of week number to day name

Posted: June 2, 2010 | Author: | Filed under: SQL Server | Leave a comment
declare @t table (weekdayNumber int)
insert into @t values (7)
insert into @t values (6)
insert into @t values (5)
insert into @t values (4)
insert into @t values (3)
insert into @t values (2)
insert into @t values (1)
SELECT DateName(WEEKDAY,Dateadd(WEEKDAY,weekdayNumber-1,0)) as WeekdayName
FROM @t