Attaching MDF file to a Database

Below is the query to attach a .mdf file to a database

CREATE DATABASE AdventureWorks2008DWR2 ON

( FILENAME = N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf')

FOR ATTACH

GO

Query to get list of packages in a Integration Server

Below is the SQL query to get the list of packages deployed to an Integration Server:

WITH ChildFolders

AS

(

    SELECT PARENT.parentfolderid, PARENT.folderid, PARENT.foldername,

        CAST('' AS SYSNAME) AS RootFolder,

        CAST(PARENT.foldername AS VARCHAR(MAX)) AS FullPath,

        0 AS Lvl

    from msdb.dbo.sysssispackagefolders PARENT

    WHERE PARENT.parentfolderid IS NULL

    UNION ALL

    SELECT CHILD.parentfolderid, CHILD.folderid, CHILD.foldername,

        CASE ChildFolders.Lvl

            WHEN 0 THEN CHILD.foldername

            ELSE ChildFolders.RootFolder

        END AS RootFolder,

        CAST(ChildFolders.FullPath + '/' + CHILD.foldername AS VARCHAR(MAX))

            AS FullPath,

        ChildFolders.Lvl + 1 AS Lvl

    FROM msdb.dbo.sysssispackagefolders CHILD

        inner join ChildFolders ON ChildFolders.folderid = CHILD.parentfolderid

)

SELECT F.RootFolder, F.FullPath, P.name AS PackageName,

    P.[description] AS PackageDescription, P.packageformat, P.packagetype,

    P.vermajor, P.verminor, P.verbuild, P.vercomments,

    CAST(CAST(P.packagedata AS VARBINARY(MAX)) AS XML) AS PackageData

FROM ChildFolders F

    inner join msdb.dbo.sysssispackages P on P.folderid = F.folderid

ORDER BY F.FullPath ASC, P.name ASC;

Convert Seconds to hours, minute, seconds (hh:mm:ss) in MDX

WITH MEMBER [Measures].[TimSpent (HH:MM:SS)] AS '[Measures].[Timespent]/ 86400', FORMAT_STRING = "hh:mm:ss"

SELECT [Assignment].[Element].[Element] ON ROWS, {[Measures].[Timespent], [Measures].[TimSpent (HH:MM:SS)]} ON COLUMNS

FROM [Activity]