Query to Get Information About Enterprise Calendars

If your Project server environment has enterprise calendars you can collection information about all the calendars from project server database.

The Query below collects information from MSP_Calendars, MSP_Calendars_Data, MSP_Projects tables of project server data and show information about each calendar separately.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[INTECHCalenders]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)

    drop table [dbo].[INTECHCalenders]

    print ‘table deleted’

GO

SELECT DISTINCT dbo.MSP_CALENDARS.CAL_NAME, dbo.MSP_CALENDARS.Cal_UId

INTO INTECHCalenders

FROM dbo.MSP_CALENDARS INNER JOIN

dbo.MSP_PROJECTS ON dbo.MSP_CALENDARS.PROJ_ID = dbo.MSP_PROJECTS.PROJ_ID CROSS JOIN

dbo.MSP_CALENDAR_DATA

WHERE (dbo.MSP_PROJECTS.PROJ_ID = 207)

Delete from INTECHCalenders Where CAl_name is NULL

DECLARE @Cal_UId varchar(10)

DECLARE CAL_Cursor CURSOR FOR

SELECT CAL_UID FROM INTECHCalenders

OPEN CAL_Cursor

FETCH FROM CAL_Cursor

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH CAL_Cursor INTO @Cal_UId

    SELECT dbo.MSP_CALENDAR_DATA.CAL_UId, dbo.MSP_CALENDAR_DATA.CD_WORKING, dbo.MSP_CALENDAR_DATA.CD_FROM_DATE, dbo.MSP_CALENDAR_DATA.CD_TO_DATE,

dbo.MSP_CALENDAR_DATA.CD_TO_TIME1, dbo.MSP_CALENDAR_DATA.CD_FROM_TIME1, dbo.MSP_CALENDAR_DATA.CD_FROM_TIME2,

dbo.MSP_CALENDAR_DATA.CD_TO_TIME2, dbo.MSP_CALENDAR_DATA.CD_FROM_TIME3, dbo.MSP_CALENDAR_DATA.CD_TO_TIME3,

dbo.MSP_CALENDAR_DATA.CD_FROM_TIME4, dbo.MSP_CALENDAR_DATA.CD_TO_TIME4, dbo.MSP_CALENDAR_DATA.CD_FROM_TIME5,

dbo.MSP_CALENDAR_DATA.CD_TO_TIME5

    FROM dbo.MSP_CALENDAR_DATA

    WHERE dbo.MSP_CALENDAR_DATA.CAL_UID = @Cal_UId

    ORDER BY CD_TO_TIME1 ASC

END

CLOSE CAL_Cursor

DEALLOCATE CAL_Cursor

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[INTECHCalenders]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)

    drop table [dbo].[INTECHCalenders]

    print ‘table deleted’

GO

Advertisements

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