Users in SharePoint Groups Using SQL

The query below will give you a list of users that belong to SharePoint 2007 groups. You can comment out and add DISTINCT to summarize the list if needed.

— Query to get all the SharePoint groups in a site collection
SELECT DISTINCT u.tp_Login, g.Title AS GroupTitle, w.FullUrl, w.SiteId, w.Id, w.Title, g.Description
FROM Groups g INNER JOIN
Webs w ON g.SiteId = w.SiteId
INNER JOIN GroupMembership gm ON gm.GroupId = g.ID
INNER JOIN UserInfo u ON u.tp_ID = gm.MemberId

Display Alert on .Net Page After Postback

I have a C# page that is writing some records to a database when the user clicks a button. The user wanted a popup confirmation that the records were written successfully. So I added a HiddenField to the page and wrote some simple javascript to check the value of the HiddenField and display the value of the field if it’s not blank.

I added this to the ASPX page just after the <Form> tag:
<asp:HiddenField ID=”MsgTxt” runat=”server” Value=”” />
<script language=”javascript” type=”text/javascript”>
   var AlertMsg = document.getElementById(‘<%=MsgTxt.ClientID%>’).value;
    if (!AlertMsg == ”) {
        alert(AlertMsg);
    }
</script>

I added this to the button that writes the records to the database:
   MsgTxt.Value = “Message”;

I added this to the Page_Load to clear the variable so it’s only displayed once:
    if (Page.IsPostBack)
    {
        MsgTxt.Value = “”;
    }

That should do it. It’s a simple change to display any type of alert message after a postback.

Disable a List Field When Adding/Editing in 2010

SharePoint 2010 now uses InfoPath 2010 forms for adding and editing list items. You can customize these forms to meet your needs which includes changing the order of the fields, removing fields and hiding fields for certain users. In this example I’ll show you how to disable a field if the user is not added to a custom security list. This allows you to control who has ability to maintain the data in a specific field.

1. Create a new custom list called “Secure Users”
2. Add a Person column called “User”
3. Change the column to display the field called “User name”
4. Go to your primary list where you want to disable the field and click “Customize Form” on the ribbon
5. The following steps are in InfoPath 2010
6. Add a new data source for the Secure Users custom list
7. Add a Form Load rule to filter the Secure Users data source on the current user
1. Add an Action Rule to “Set a field’s value”
2. Select the User field in the list of queryFields for the Secure Users data source
3. Set the value to the function userName()
4. Add another rule to query the list with the new filter
8. Add a rule to the appropriate controls you want disabled
1. Select the field and add a new formatting rule
2. Set condition – Select AccountID in the Secure Users dataFields
3. Select “Number of occurrences of AccountID” in the “Select:” dropdown
4. Select “is equal to” and enter zero as the value
5. Select “Disable this control”
9. Quick Publish the form and you’re done!

You’ve created a form that checks the Secure Users list for the current logged in user. If the user is not in the list the selected field will be disabled.

Silver Lining Computing

I’ve taken some time off from my blog to start a SharePoint Consulting company called Silver Lining Computing. We specialize in SharePoint implementation and customization and other related IT projects. Check out our website at http://www.silverliningcomputing.com.

Error Copying SharePoint Files in Windows Explorer

I was trying to copy files from a site on one SharePoint server farm to a site on a SharePoint server farm outside the firewall. The source site was using HTTP and the destination was HTTPS. I opened both document libraries in Windows Explorer and copied/pasted the files to the destination. The copy would create the folders but throw an error on every file, “an error occurred copying some or all of your files”. 

I noticed I had IE 6 installed so I installed IE 8 and everything now works perfectly…

Failed to get value of the “Approval Status”

The full error message reads “Failed to get value of the “Approval Status” column from the “Moderation Status” field type control”. This error has started occuring on document libraries containg web part pages that were upgraded from SPS 2003. The Hidden property on the Approval Status field has somehow been set to false. The Hidden property should be True.

I found this fix. Download a utility called SharePoint Manager 2007 from this site.
Run the application on a SharePoint server.
Go to View – Object Model and make sure “Full” is selected.
Browse to the library in the treeview.
Open the list of Fields and select the Approval Status field.
Change the Hidden property to True.
If the property is already set to True then change it to false and save it. Then change it back to True and save again.

Mass Deleting Document Version History

Version history takes a huge amount of disk space
The following SQL will delete the version history for a specific doc or set of docs
BE VERY CAREFUL! Remove the first line and run a SELECT statement first so you don’t delete the wrong versions!

Set the DirName = to the path to the doc library
Set the leafname = to the name of the file

delete from alldocversions
from alldocversions v inner join alldocstreams s on s.id = v.id
inner join alldocs d on d.id = s.id
where DirName = ‘FolderPath’ and leafname like ‘a%’

Here’s a cursor procedure to delete selected files

SET NOCOUNT ON
declare @dirname varchar(100)
declare @leafname varchar(255)

DECLARE complex_cursor CURSOR FOR
    select distinct DirName, LeafName
 from alldocversions v inner join alldocstreams s on s.id = v.id
 inner join alldocs d on d.id = s.id
 where DirName like ‘Folderpath%’ and leafname like ‘c%’;
OPEN complex_cursor;
FETCH NEXT FROM complex_cursor INTO @dirname, @leafname
WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT ‘Deleting ‘ + @dirname + ‘, ‘ + @leafname

 DELETE FROM alldocversions
 FROM alldocversions v inner join alldocstreams s on s.id = v.id
 inner join alldocs d on d.id = s.id
 where DirName = @dirname and leafname = @leafname

 FETCH NEXT FROM complex_cursor INTO @dirname, @leafname
END
CLOSE complex_cursor;
DEALLOCATE complex_cursor;

SET NOCOUNT OFF

403 Forbidden and Forms Authentication with MOSS

We have a public MOSS site setup to use Forms Authentication for protected content. When the user hits files in certain document libraries, the user is automatically redirected to the login page and then returned to the document after a successful login. However, for some users, when they click a link to view a protected file they are not redirected to the login page and a “403 Forbidden” error message is displayed in Internet Explorer. This behavior does not occur in non-IE browsers (Firefox, Safari).

After some research I found this article. It looks like the culprit is a Microsoft add-in for Office Live.

Fix: Uninstall the application called “Microsoft Office Live Add-in 1.3”. That solved the problem right away. No system reboot or browser restart necessary…

MOSS Search Indexer “Access is Denied”

Have a MOSS farm the a web front-end and a separate search indexer. After installing some patches the indexer started failing with an Access is Denied message. MOSS configures a dedicated index machine by adding an entry to the HOSTS file that points the MOSS sites to the indexer’s IP address. If you try to connect to your MOSS site on the Indexer’s desktop you will unable to connect.

The patches are KB960803 and KB963027. You can read the notes here.

Following the instructions for Method 1 in the article fixed the problem and indexer was able to access and index the content again. The fix is a small registry entry that allows local connections to other host names.

“Unable to load client print control” in Reporting Services 2005

The user gets a dialog stating “Unable to load client print control” when clicking the print button on a Reporting Services 2005 report.

There much written about this and what Microsoft update broke this so I won’t repeat it here. (Google – reporting services “Unable to load client print control”)

The bottom line is that there’s an update that added a “kill-bit” associated to the ActiveX printing control used by Reporting Services. There’s an update to Reporting Services that supposedly fixes this http://www.microsoft.com/downloads/details.aspx?FamilyID=82833f27-081d-4b72-83ef-2836360a904d&DisplayLang=en

OR you can uninstall these 2 updates KB956803 & KB956391

OR you can just delete the registry entry causing the problem
Copy the red text below into a file with a REG file extension and double-click it

Windows Registry Editor Version 5.00

[-HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\ActiveX Compatibility\{FA91DF8D-53AB-455D-AB20-F2F023E498D3}]