Hello world!

Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!

Query SharePoint Data for Reporting From Multiple Related Lists

This example uses common table expressions which not all reporting programs are compatible with.

–Improvement Suggestions Guid = D4F12011-8D63-4927-B7E4-251CD0848CEE
–Review Guid = FC63874A-6656-4644-BAAE-E1DBFC2A8455
–More efficient code but cannot be run in certain reporting programs
;with ctedata(ImprovementAuthor, ImprovementTitle, ImprovementStatus, ImprovementProblem, tp_id) as
(
    select (select u1.tp_title from <DB>.dbo.userinfo u1 join <DB>.dbo.alluserdata d3 on
        (u1.tp_id = d2.tp_author and d2.tp_siteid = u1.tp_siteid) where d3.tp_guid = d2.tp_guid) as ImprovementAuthor,
        nvarchar1 as ImprovementTitle, nvarchar3 as ImprovementStatus,
        cast(rtrim(replace(ltrim(replace(cast(d2.nText2 as varchar(255)), ‘<div>’, ‘ ‘))
            , ‘</div>’, ‘ ‘)) as varchar(max)) as ImprovementProblem,
        tp_id
    from <DB>.dbo.alluserdata d2
    where tp_listid = ‘D4F12011-8D63-4927-B7E4-251CD0848CEE’
)
select nvarchar1 as Title, nvarchar3 as AffectedArea, nvarchar4 as ProjectShowStoppers, nvarchar5 as ProejctedSavings,
    nvarchar6 as CommitteeDecision, nvarchar7 as Status,
    SupportAndResistance = Case when (cast(nText2 as varchar(255)) = ‘<div></div>’ or cast(nText2 as varchar(255)) = ‘<div>&nbsp;</div>’) then ‘N/A’ else
        (rtrim(replace(ltrim(replace(cast(nText2 as varchar(255)), ‘<div>’, ‘ ‘)), ‘</div>’, ‘ ‘))) end,
    Notes = case when (cast(nText3 as varchar(255)) = ‘<div></div>’ or cast(nText3 as varchar(255)) = ‘<div>&nbsp;</div>’) then ‘N/A’ else
        (rtrim(replace(ltrim(replace(cast(nText3 as varchar(255)), ‘<div>’, ‘ ‘)), ‘</div>’, ‘ ‘))) end,
    int1 as ImprovementID,
    (select u.tp_title from <DB>.dbo.userinfo u join <DB>.dbo.alluserdata d1 on
        (u.tp_id = d1.int2 and d1.tp_siteid = u.tp_siteid) where d1.tp_guid = d.tp_guid) as Reviewer,
    (select u.tp_title from<DB>.dbo.userinfo u join <DB>.dbo.alluserdata d1 on
        (u.tp_id = d1.int3 and d1.tp_siteid = u.tp_siteid) where d1.tp_guid = d.tp_guid) as ProjectBelt,
    (select u.tp_title from <DB>.dbo.userinfo u join <DB>.dbo.alluserdata d1 on
        (u.tp_id = d1.int4 and d1.tp_siteid = u.tp_siteid) where d1.tp_guid = d.tp_guid) as ProjectSponsor,
    float1 as ProjectDuration, c.ImprovementAuthor, c.ImprovementTitle, c.ImprovementStatus, c.ImprovementProblem,
    cast (rtrim(replace(ltrim(replace(cast(nText4 as varchar(255)), ‘<div>’, ‘ ‘))
        , ‘</div>’, ‘ ‘)) as varchar(max)) as CommitteeNotes,
    tp_Modified as LastModDate
from <DB>.dbo.alluserdata d join ctedata c on c.tp_id = d.int1
where tp_listid = ‘FC63874A-6656-4644-BAAE-E1DBFC2A8455’ and d.tp_id = @ID

Use Javascript to Show and Hide Items in SharePoint Forms

Many times as SharePoint developers we are asked to customize forms to show/hide items in the form.  Using a Javascript checkbox and some scripting I have been able to accomplish this.  With SharePoint forms it is important to make sure the form item being hidden is not a required field. 

Code:

<H3>
     Check if there are show stoppers. <input id="ShowStopperCheckBox" type="checkbox" onclick="ShowStopper()"/>
</H3>

 

<script type="text/javascript">
function ShowStopper()
{
    if(document.getElementById("ShowStopperCheckBox").checked==true)
        document.getElementById("ShowStop").style.display = ‘block’;
    else
        document.getElementById("ShowStop").style.display = ‘none’;
}
</script>

Log File Backup Script

Situation: 

You want to automate the backup and clearing of log files for a server

 

Script: 

 strComputer = "."       
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate, (Backup, Security)}!\\" _
        & strComputer & "\root\cimv2")
Set colLogFiles = objWMIService.ExecQuery _
    ("Select * from Win32_NTEventLogFile where LogFileName=’Application’")
For Each objLogfile in colLogFiles
     OutputFile = "L:\SystemLogs" & "Application "
     OutputFile = OutputFile & Day(Now) & "-" & month(now) & "-" & year(now)
     OutputFile = OutputFile & ".evt"
    errBackupLog = objLogFile.BackupEventLog(OutputFile)
    If errBackupLog = 0 Or errBackupLog = 183 Then
         objLogFile.ClearEventLog()
    Else
        Wscript.Echo "The Application event log could not be backed up."
    End If
Next

 

Credit:  I apologize for not posting the credit for this script as it was several months ago that I found it.  For the person that did create it, you did a fantastic job!!!

Using UNC path with SharePoint over SSL

Situation: 

The SharePoint site is SSL (https) and cannot be accessed via UNC

 

Resolution:

There are three important parts with this resolution.  First, WebDAV needs to be enabled in IIS for the Front End Web Server.  Second, the Web Client Service needs to be running on the computer.  Third, when accessing via UNC you must use "@ssl" after the "site name" (\\<www.site.com>@ssl\pages)  Onc important note for this solution is this will only work for Vista or later computers, not XP. 

 

 

SharePoint Portal Authentication Prompts

Situation: 

1. User is already logged into domain computer and is prompted for credentials when they log into the SharePoint portal
2. User opens office document from SharePoint portal and gets prompted multiple times for authentication

Resolution:

One of two problems could be causing these problems; however, both should be in place.  The site needs to be added a specific internet zone such as "Trusted Sites" or "Local Intranet" and then the group policy for those sites needs to be changed to forward the logon authentication to the application.  If the site has a . in the url IE will automatically put it into the Internet zone unless it is specifically set into another zone. 

 

 If the zones are locked down the following is the correct place to add the keys in the registry:  [HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\Domains\<key>]  The DWord values could be either http, https or * with a 2 value for the "Trusted Sites" zone.

 

The local group policy for "Trusted Sites" is:  Change Group/Local computer policy for Trusted Sites Zone – Logon Options to “Enabled” and Automatic Logon with Current Username and Password.  Policy Location:  gpedit.msc –> Computer Configuration –> Administrative Templates –> Windows Components –> Internet Explorer –> Internet Control Panel –> Security Page –> Trusted Sites Zone