System Center Blog

    by Mikael Perhult (SCCMx)

    Browsing Posts in SQL

    When moving SQL Files within same server but different disks/volumes the SQL Task Detach/Attach can be used.

    However must DB owner be verified to be set to ‘sa’ and the following command must be run.

    ALTER DATABASE CM_PS1 SET TRUSTWORTHY ON

    Otherwise is there errors in SMS Provider Log and SMS Policy Provider log.

     

    By creating a separate query with the Deployment ID it is simpler to follow status of a running OSD Task Sequence:

     

    (select stat.*, ins.*, att1.*, att1.AttributeTime from SMS_StatusMessage as stat left join SMS_StatMsgInsStrings as ins on stat.RecordID = ins.RecordID left join SMS_StatMsgAttributes as att1 on stat.RecordID = att1.RecordID inner join SMS_StatMsgAttributes as att2 on stat.RecordID = att2.RecordID where att2.AttributeID = 401 and att2.AttributeValue = “PS12001E” and stat.SiteCode = “PS1” and att2.AttributeTime >= ##PRM:SMS_StatMsgAttributes.AttributeTime## order by att1.AttributeTime desc)

     

    select stat.*, ins.*, att1.*, att1.AttributeTime
    from SMS_StatusMessage as stat
    left join SMS_StatMsgInsStrings as ins on stat.RecordID = ins.RecordID
    left join SMS_StatMsgAttributes as att1 on stat.RecordID = att1.RecordID
    inner join SMS_StatMsgAttributes as att2 on stat.RecordID = att2.RecordID
    where att2.AttributeID = 401 and att2.AttributeValue = "PS120004"
    and stat.SiteCode = "PS1" and att2.AttributeTime >= ##PRM:SMS_StatMsgAttributes.AttributeTime##
    order by att1.AttributeTime desc
    
    

    By running this query can source path, Inastall and Uninstall command being showed from a SQL query:

     

    SELECT [CI_ID]

    ,[CI_UniqueID]

    ,[SDMPackageDigest]

    ,SDMPackageDigest.value(‘declare namespace p1=”http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest”;

    (p1:AppMgmtDigest/p1:DeploymentType/p1:Title)[1]’,‘nvarchar(max)’)AS DTTitle

    ,SDMPackageDigest.value(‘declare namespace p1=”http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest”;

    (p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/@Technology)[1]’,‘nvarchar(max)’)AS DTTechnology

    ,SDMPackageDigest.value(‘declare namespace p1=”http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest”;

    (p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:Contents/p1:Content/p1:Location)[1]’,‘nvarchar(max)’)AS DTContentLocation

    ,SDMPackageDigest.value(‘declare namespace p1=”http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest”;

    (p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:InstallAction/p1:Args/p1:Arg)[1]’,‘nvarchar(MAX)’)AS Install,

    SDMPackageDigest.value(‘declare namespace p1=”http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest”;

    (p1:AppMgmtDigest/p1:DeploymentType/p1:Installer/p1:UninstallAction/p1:Args/p1:Arg)[1]’,‘nvarchar(MAX)’)AS Uninstall

    FROM[v_ConfigurationItems]

    WHERE CIType_ID = 21

     

    Good link for removing a DP from SCCM database:

    http://itminutes.net/?p=202

     

    SELECT*

    FROM v_UpdateInfo INNERJOIN v_UpdateContents ON

    v_UpdateInfo.CI_ID = v_UpdateContents.CI_ID INNERJOIN v_UpdatePrograms ON

    v_UpdateContents.ContentCI_ID = v_UpdatePrograms.UpdateID INNERJOIN v_Package ON

    v_UpdatePrograms.PackageID = v_Package.PackageID

    where v_Package.PackageID =‘XXX000YY’and v_UpdateInfo.ModelName =‘Site_FEECBF49-05EA-435B-9EC7-3356BB4B2837/SUM_e71ef51d-c577-4e59-9b87-31967547f41c’

    ORDERBY v_UpdateInfo.ArticleID

    SELECTDISTINCT v_ClientAdvertisementStatus.AdvertisementID, v_Advertisement.AdvertisementName, dbo.v_Package.PkgSourcePath

    FROM dbo.v_Package INNERJOIN

    dbo.v_Advertisement ON dbo.v_Package.PackageID = dbo.v_Advertisement.PackageID RIGHTOUTERJOIN

    dbo.v_ClientAdvertisementStatus ON v_Advertisement.AdvertisementID = v_ClientAdvertisementStatus.AdvertisementID

    WHERE (v_ClientAdvertisementStatus.LastStateName =‘succeeded’)AND(v_ClientAdvertisementStatus.LastStatusTime >DATEADD(day, 90,GETDATE()))

    ORDERBY dbo.v_Advertisement.AdvertisementName

     

    SELECTCOUNT(*)ASCount, v_ClientAdvertisementStatus.AdvertisementID, v_Advertisement.AdvertisementName

    FROM v_ClientAdvertisementStatus LEFTOUTERJOIN

    v_Advertisement ON v_Advertisement.AdvertisementID = v_ClientAdvertisementStatus.AdvertisementID

    WHERE (v_ClientAdvertisementStatus.LastStateName =‘succeeded’)AND(v_ClientAdvertisementStatus.LastStatusTime >DATEADD(day, 30,GETDATE()))

    GROUPBY v_ClientAdvertisementStatus.AdvertisementID, v_Advertisement.AdvertisementName

    ORDERBYCountDESC

     

    -- expand recursive folder structure
    WITH FolderHierarchy (ContainerNodeID, RootContainerNodeID) AS (
        	SELECT
    			P.ContainerNodeID,
    			P.ParentContainerNodeID
        	FROM
    			vSMS_Folders P
    		WHERE
    			P.ParentContainerNodeID = 0
        UNION ALL
        	SELECT
    			C.ContainerNodeID,
    			C.ParentContainerNodeID
        	FROM
    			vSMS_Folders C
    		INNER JOIN
    			FolderHierarchy R
    		ON
    			C.ParentContainerNodeID = R.ContainerNodeID
    )
    
    SELECT 
    	P.PkgID,
    	P.Name	
    FROM
    	FolderHierarchy FH
    	JOIN
    		vFolderMembers FM
    	ON
    		FM.ContainerNodeID = FH.ContainerNodeID
    	JOIN
    		vSMS_ContentPackage P
    	ON
    		P.SecurityKey = FM.InstanceKey
    WHERE
    	FH.RootContainerNodeID = (SELECT ContainerNodeID FROM vSMS_Folders WHERE Name ='Production' and ObjectType = 6000)

    This script will retreive all applications under a root folder and subfolder hierarchy.

     

    The followinf sql query lists all Distribution Points that belongs to a Distribition Point Group

    SELECT     vDistributionPoints.ServerName, vDistributionPoints.SMSSiteCode, vDistributionPoints.IsPXE, vDistributionPoints.SupportUnknownMachines, 
                          vDistributionPoints.PreStagingAllowed, vDistributionPoints.IsPullDP, vDistributionPoints.IsProtected, vDistributionPoints.DPDrive, vDistributionPoints.MinFreeSpace, 
                          vSMS_DPGroupInfo.Name AS DPGroup
    FROM         vDistributionPoints INNER JOIN
                          v_DPGroupMembers ON vDistributionPoints.NALPath = v_DPGroupMembers.DPNALPath INNER JOIN
                          vSMS_DPGroupInfo ON v_DPGroupMembers.GroupID = vSMS_DPGroupInfo.GroupID
    WHERE     (vSMS_DPGroupInfo.Name LIKE '%windows%' and vDistributionPoints.SMSSiteCode = 'P01')
    ORDER BY vDistributionPoints.ServerName

    When using a SQL Server specific port and not being able to open SQL browser UDP port 1434 through firewall the Linked Server can fail between SQL Servers in SCCM 2012. For example between a Secondary Site and a Primary Site.

    Cause:

    This is due to the Linked Server object in SQL is not being setup using the custom portnumber.
    This can be seen by opening SQL Mgmt Studio, go to Server Objects, Linked Servers, Providers. Right click the Linked Server object and select “Test Connection”, it shows [Failed] with an error description.

    Symptom:

    In the logfile rcmctrl.log is this shown.

    ERROR: Exception message: [SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. ~~OLE DB provider “SQLNCLI10” for linked server “SCCMSQL.lab.local\SCCM_PSS” returned message “Login timeout expired”.~~OLE DB provider “SQLNCLI10” for linked server “SCCMSQL.lab.local\SCCM_PSS” returned message “A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.”.~~OLE DB provider “SQLNCLI10” for linked server “SCCMSQL.lab.local\SCCM_PSS” returned message “Invalid connection string attribute”.]

    Solution:
    Re-create Linked Server object adding the custom sql port.

    # Right click and select Script Linked Server as DROP and CREATE to – New Query Editor Window.

    # Change and add the custom port:
    EXEC master.dbo.sp_addlinkedserver @server = N’SCCMSQL.lab.local\SCCM_PSS’, @srvproduct=N’Any’, @provider=N’SQLNCLI10′, @datasrc=N’SCCMSQL.lab.local,13334\SCCM_PSS’, @provstr=N’Data Source=SCCMSQL.lab.local,13334\SCCM_PSS;Integrated Security=SSPI;Persist Security Info=false;Encrypt=Yes;TrustServerCertificate=No;’

    # Verify and Run the script.

    # Right click the Linked Server object and select “Test Connection” – It now shows [OK].

    # Restart SMS Executive and SMS Component.

    Verify the changed settings will take effect in rcmctrl.log.
    Launching 2 sprocs on queue ConfigMgrDRSQueue and 0 sprocs on queue ConfigMgrDRSSiteQueue…
    There are 2 Drs Activations sprocs running…
    Running configuration ConfigureLinkedServers..
    Found 1 servers that needs to be linked…
    Processing Replication success…