How many days in a month for a given date?

Posted: December 10, 2010 | Author: | Filed under: SQL Server | Leave a comment

DECLARE @givenDate  DATETIME 

SET @givenDate= ’12/11/2010′ 

 SELECT  Datediff(DAY, Dateadd(mm, Datediff(mm, 0, @givenDate), 0), Dateadd(mm, D atediff(mm, 0, @givenDate) + 1, 0))

Shrink log files for DNN databases

Posted: December 6, 2010 | Author: | Filed under: DotNetNuke (DNN), SQL Server 2005 | Leave a comment

Here is code snippet for how to shrink log files in either full or simple recovery mode. I have a condition check to deal with only DNN web databases.

EXEC

 sp_MSForEachDB 

–DNN has a table named as Version

‘ IF EXISTS (select 1 from [?].information_schema.tables where

table_name=”Version”)

BEGIN

IF EXISTS (SELECT 1 FROM master..sysdatabases

WHERE databasepropertyex (name,”Recovery”)=”FULL” )

begin

USE [?]

BACKUP LOG [?] WITH TRUNCATE_ONLY

dbcc shrinkfile(2)

dbcc shrinkdatabase(0)

end

IF EXISTS (SELECT 1 FROM master..sysdatabases

WHERE databasepropertyex (name,”Recovery”)=”SIMPLE” )

begin

USE [?]

CHECKPOINT

dbcc shrinkfile(2)

dbcc shrinkdatabase(0)

end

END

‘

Reference:

http://www.karaszi.com/SQLServer/info_dont_shrink.asp

http://consultingblogs.emc.com/jamesrowlandjones/archive/2008/07/15/sql-server-database-recovery-model-how-do-you-set-yours.aspx

DNN Sercurity Setting Locked Issue (DNN 5.5.0 and DNN 5.6.0)

Posted: December 2, 2010 | Author: | Filed under: DotNetNuke (DNN) | Tags: , | Leave a comment

When you create a child portal from a template in DNN 5.5.0 and DNN 5.6.0 (maybe more versions), you may run into an issue: you cannot modify any page’s security settings any more. They are locked!

You may find the dicussion from DotNetNuke forum at this link and a script from Chris Hammod:

http://www.dotnetnuke.com/Resources/Forums/tabid/795/forumid/108/threadid/358090/scope/posts/threadpage/3/Default.aspx

Here is another SQL script to fix the issue:

UPDATE

 

p

SET

 

p.AdministratorRoleId = d.AdministratorRoleId

,

 

p

.RegisteredRoleId = d.RegisteredRoleId 

FROM

 

 

(

SELECT

 

PortalID, [Administrators] AS AdministratorRoleId

,

 

[Registered Users] AS RegisteredRoleId FROM (SELECT PortalID, RoleID, RoleName FROM {databaseOwner}{objectQualifier}Roles ) src

PIVOT

 

 (MIN(RoleID) FOR RoleName IN ([Administrators],[Registered Users]))

pvt 

 

)

 

d INNER JOIN {databaseOwner}{objectQualifier}Portals p ON d.PortalID=p.PortalID

You can run the above script from your DNN site under HOST>SQL Run as Script (make sure your have a backup copy of your database).

If you want to run the script from SSMS, here is the copy without {databaseOwner}{objectQualifier}:

UPDATE

 

p

SET

 

p.AdministratorRoleId=d.AdministratorRoleId

,

 

p

.RegisteredRoleId =d.RegisteredRoleId

FROM

 

( SELECT PortalID, [Administrators] as AdministratorRoleId

,

 

[Registered Users] as

RegisteredRoleId  

FROM (SELECT PortalID, RoleID, RoleName from roles ) src

PIVOT

 

(MIN(RoleID) FOR RoleName IN ([Administrators],[Registered Users]))pvt

)

 

d INNER JOIN dbo.Portals p ON d.PortalID=p.PortalID