SharePoint 2007 - People Picker

A user gets an error when trying to search on the People Picker: 
Error: "There was an error in callback"

Check permissions on the Registry Key:
HKLM\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0\Secure

And make sure there is a Binary Value:

AppCredentialKey

And that the value is correct.

Unable to properly communicate with the workflow service

SharePoint 2013

It has been a long time since my last blog post. I have been busy. :-) Well I am back again and thought I would share my latest discovery on the SharePoint 2013 planet.

Problem:
User clicks on List Item - Workflow, and receives error:

Unable to properly communicate with the workflow service.

The server which hosts the Workflow Manager doesn't reveal any errors in the Event Log, ULS Logs or the IIS Logs, but on other WFE servers the error pop's up in the ULS log:

UserAgent not available, file operations may not be optimized.
    at Microsoft.SharePoint.SPFileStreamManager.CreateCobaltStreamContainer(SPFileStreamStore spfs, ILockBytes ilb, Boolean copyOnFirstWrite, Boolean disposeIlb) ......

Failed to retrieve the workflow instance because the connection timed out with exception: System.TimeoutException: The HTTP request has timed out after 20000 milliseconds. ---> System.Net.WebException: The request was aborted: The request was canceled. 
    at System.Net.HttpWebRequest.EndGetResponse(IAsyncResult asyncResult)  ......



You would not say it has something to do with the BlobCache, well I didn't. Anyway, to resolve:

Solution:

Open Powershell and run the following commands:

Add-PSSnapin microsoft.sharepoint.powershell -ErrorAction SilentlyContinue 

$webApp = Get-SPWebApplication "" 

[Microsoft.SharePoint.Publishing.PublishingCache]::FlushBlobCache($webApp)

*AND* remember to do an IISRESET on all your servers.

Thanks to David:
http://davidmsterling.blogspot.co.za/2015/02/sharepoint-uls-error-useragent-not.html

Powershell and SQL for getting Database info

Part of monitoring a SharePoint farm is to make sure there is enough space available for the content databases.

If your organization is anything like mine, segregation of duties are very important. Meaning the SQL DBA's do the SQL stuff, and we the SharePoint Admin, do the SharePoint stuff. As a SharePoint Administrator, I must say, I need to know a bit more about the SQL part of the SharePoint Farm.

So, I don't have access to the SQL Server, and I do not really need it. I just need to pull some stats and that is it. I used PowerShell and SQL Queries to get the info I need.

The info I require is:
 - All Databases on the server
 - Stats specific to each database: Size of DB, used space, and free space available. On which Drive the database files reside as well as the Log files.

The method I chose works in my environment and I am sure there are tools and other/better ways to do the same thing.


1. The PowerShell Session would need to be started with an account access to the SQL Server instance.
2. I want to save the info to CSV files for analysis in Excel.

##########################################################################
# Login to farm with Service Account
##########################################################################





$credential = New-Object System.Management.Automation.PsCredential("YourServiceAccount", (ConvertTo-SecureString "yourPassword" -AsPlainText -Force))




start-process powershell.exe -Credential $credential -NoNewWindow -ArgumentList "Start-Process powershell.exe .\SQLStats.ps1 -verb runas"









sqlFunctions.ps1
function Invoke-SQL {
      param(
      [string] $dataSource = ".\SQLEXPRESS",
      [string] $database = "MasterData",
      [string] $sqlCommand = $(throw "Please specify a query."),
      [bool] $export = $false,
      [string] $filename = "data.csv"
      )


      $connectionString =     "Data Source=$dataSource; " +
                              "Integrated Security=SSPI; " +
                              "Initial Catalog=$database"

      $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
      $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
      $connection.Open()

      $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
      $dataset = New-Object System.Data.DataSet
      $adapter.Fill($dataSet) | Out-Null

      $connection.Close()
      $dataSet.Tables

      if($export)
      {
            Remove-Item $filename
            foreach($table in $dataset.Tables)
            {
                  #$table | export-csv $filename -notypeinformation -append
                  $table | ConvertTo-Csv -NoTypeInformation `
                         | select -Skip 1 `
                         | Out-File $filename -Append
            }
      }

}


#I added these queries as functions. Not the right way, but anyway:

#This SQL Query runs a Stored Procedure on the SQL Server for each DB

function getDBStatsQuery()
{
      $query = "exec sp_msforeachdb
      'use [?];
      select DB_NAME() AS DbName,
      physical_name,
      CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) AS Status,
      CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')) AS Recovery,
      CONVERT(varchar(20),Type) AS Type,
      SUM(size)/128.0 AS File_Size_MB,
      SUM(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,
      SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB
      from sys.database_files group by type, physical_name' "
      $query
}




#This SQL Query runs a Stored Procedure to get the available free space for all Drives on the SQL Server

function getOSDriveQuery()
{
      $query = "EXEC master..xp_fixeddrives"
      $query
}







SQLStats.ps1

# Script to get Database and Log sizes

# Have to run script with user that has correct permissions on SQL
Write-host "Running as: " ([Security.Principal.WindowsIdentity]::GetCurrent().Name)



#Include SQL Functions
. .\sqlFunctions.ps1





### Start of Queries
$dataSource = "sqlsever/instance" #SQL Server and Instance
$database = "Master" #Database
$outDataFileName = "infoData.csv"
$outDriveFileName = "infoSQLDrives.csv"





#Run Query
Write-host "Invoking SQL Query to retrieve Database stats..."
$results = $(Invoke-SQL -DataSource $dataSource -Database $database -SQLCommand $(getDBStatsQuery) -Export $true -FileName $outDataFileName )





#Get Drive Space available on Server
Write-host "Invoking SQL Query to retrieve OS Drives free space..."
$drives = $(Invoke-Sql -DataSource $dataSource -Database $database -sqlCommand $(getOSDriveQuery) -Export $true -FileName $outDriveFileName)




Write-host "Done..."

This PowerShell Script creates two CSV Files. One for the Databases and one for the Drives on the SQL Server.


Hope it helps someone.

Azure - Deleting VNet

Trying to delete a Virtual Network in Azure

Powershell command:

Note this removes all Virtual Networks from your active Azure Subscription
Remove-AzureVNetConfig

But then:

Remove-AzureVNetConfig : BadRequest : Cannot delete or modify virtual network while in use 'xxxxx'.

I do not have Virtual Machines as I removed them all. But then I noticed the VNetwork has a Gateway assigned to it.

So remove the gateway before removing the VNetwork.

Delete the Gateway

By the way, the Powershell command for deleting a VNet Gateway:

Remove-AzureVNetGateway -VNetName "SPAutoVNet"



Push!

This blog just received a serious push into the future. I have been busy for the past 3 or so months with SharePoint 2013 and Azure Cloud Services.


Leaving the old technology behind and going forward, where I have not been yet!


While I believe Cloud technology has a serious place in the future (as it currently does), it has room for improvement.


On this blog I will post about my stumbles and falls with SharePoint 2013 and Azure.


Here we (or I) go!

SharePoint 2007 and InfoPath Form List

Strange thing happened the other day. A user phones and in a panic ask's "All our data is GONE on our list!" Stop the drama!

Anyway, here is the problem:

A Form Library lost all of its custom columns. Nobody deleted it. Opening the form in Infopath showed that the fields were still in the form. But the view did not show it. Not even in the library settings could you see the fields.

By luck, I think, I replicated the problem on our Application Support server.

Saving the List as a template, removed all the form columns in the list. The columns may have been removed from the view and list, but the DATA was still there.

So, here is the solution: Republish the form to the library. Open each item and view the properties. Then that will repopulate the fields .

If you experienced this, please leave a comment.

Who deleted that Web site?

Sometimes Web sites get deleted. If you want to track down who did it, you can check the IIS logs or check the Content DB.

The query to run on the SQL DB is:

SELECT [Id]
      ,[SiteId]
      ,[EventTime]
      ,[ListId]
      ,[ItemId]
      ,[ItemName]
      ,[ItemFullUrl]
      ,[EventType]
      ,[ModifiedBy]
      ,[TimeLastModified]
      ,[EventData]
      ,[FormattedFragment]
  FROM [].[dbo].[EventLog]
where itemfullurl like '%//%' and EventType = '4'

In the LAST row (by Date), the Column ModifiedBy will be the person that deleted the site. Just double check that the ItemFullUrl has ...'Lists/Site Owner/' ... in it. EventType 4 means 'deleted'.

I do not take responsibility for unfair dismissal. ;-)

It is however likely the person that deleted the site.

Copy/Move a Web Site to another Site Collection

Now I am sure all of us in the SharePoint community had to move or copy a site's library and/or lists to another site collection. There are various reasons for this, in our case it was because of governance.

Long story short:
- One content database
- One site collection
- Multitude of web sites.
- Limit on content database size of 100GB (now there is a whole discussion about what size a content database should be and could be, but let's not go there now).

Mission:
- Move a web site (subsite) out of the current site collection to a new site collection in it's own content database.

The easy part is to create the Content Database, add it to the Web Application, create the new blank Site Collection in the Content Database, set the properties of the Content Database to only 1 Site Collection.

If you are lucky, the owners of the Web Site did not implement Workflow, Lookup lists or something else(Features) that can complicate the move.

Then you would probably use STSADM command line and do your stuff.

In my case the users implemented Lookup lists/fields. While this is nice to use in SharePoint, and you should try do design your site with this in mind. It reallllly makes it difficult to move. So that is why governance/design at a higher level is important. Then you *don't* have to move the site.

Anyway, no use crying over spilled milk.

A lookup field is a field that gets a value from another field in another list. You could have multiple lookup fields that uses multiple other lists.

Say you have a MAIN List:

MAIN List
Field A, Field B, Lookup Field C (Lookup Value from LIST 1, Field Z)

LIST 1
ID Field A, Field B, Field Z
1: Value 1, Value 2, Value FFFF
2: Value 2, Value 5, Value AAAA
3: Value 6, Value 1, Value LLLL

You add a record to MAIN:
Value A, Value B, Value LLLL

The lookup field's value in the MAIN List is stored as such: "RowId;#Value", i.e "3;#Value LLLL".

So to move this is not simple. You have to create the Lookup Lists in the Target Site. Import the Values from the Source site Lookup List. Do this for every lookup field. Try to resolve circular Look Ups.

When your Lookup Lists are created and populated then you can start to copy a list with the lookup FIELDS to the target site.

1. Open Site
2. Open Web Site
3. Open List
4. Iterate over List Items
5. Iterate over Item Versions
5. Iterate over Item Fields and copy across to -> TargetSite/WebSite/List/Item/Field
6. If the Field is a Lookup,
6.1 Try to get the Row ID from the Target Lookup List by matching the Source field Value to the arget List Field
6.2 Update the Item[FIELD] to the New ID, and Same Value.

Easy huh? Not by a long shot. It is complicated and it is easy to get confused. Maybe if I have time I will develop my program to be user friendly and so that it can be used for any site.

Don't hold your breath though.

Change .Net Framework for specific Apps IIS 6

Whenever I need to change the .NET settings for a site.

1. Run aspnet_regiis -lk from any .NET framework folder to list your current settings to help you determine which sites should remain using .NET 1.1. If you know there is a .NET 1.1 site, but it is not explicitly listed by this command, then it is inheriting from the root W3SVC/.

2. For all .NET 1.1 sites not explicitly listed by the previous command, you will need to force them to use .NET 1.1:

- Determine the Identifier ID of the site(s) which you want to force to use .NET 1.1. (Through the IIS 6 Manager, you can determine the Identifier of a site by clicking the "Web Sites" folder on the left side of the tool. On the right side, all your sites will be listed, and the Identifier column shows the ID.)
- From the .NET 1.1 framework folder, run aspnet_regiis -sn W3SVC//ROOT/ where is the ID of the site which you want to force to use .NET 1.1.

3. Finally, change the root W3SVC/ to use .NET 2.0 so that all newly created sites will inherit from the root and default to use .NET 2.0. To change the root, from the .NET 2.0 framework folder, run aspnet_regiis -sn W3SVC/.

You can run aspnet_regiis -lk again to verify your settings.

Remember?

O to rember is wonderful. To forget is well, I forget.

Remember: When you have a farm with multiple servers, or the host name(s) differ from the computer name, you have to disable the loopback check or Specify the Host names in the registry.

See: http://support.microsoft.com/kb/896861

O how wonderful it is to remember.

SQL Tips

Need to manage a lot of Databases on various different drives?

Use this query to get information re. Databases on your SQL Server:

select db.dbid
,db.name
,db.filename as DBFileName
,logFile.filename as LogFileName
,convert(decimal(18,2),(db.size*8)/1024.0) as DBSize
,convert(decimal(18,2),(logFile.size*8)/1024.0) as LogFileSize
,d.compatibility_level
,d.recovery_model_desc
From (select dbid,name,filename,size
from sys.sysaltfiles safDBFiles
where safDBFiles.groupid>0) db,
(select dbid,name,filename,size
from sys.sysaltfiles safLogFiles
where safLogFiles.groupid = 0) logFile,
sys.databases d
where logFile.dbid = db.dbid
and
d.database_id = db.dbid

How to identify which w3wp.exe process is which Application Pool

Have no worries, Microsoft has given us the exact tool for the situation.
IISApp.vbs lists all the applications, their PID and their App Pool name.

The script is already placed in systemroot\system32 on Windows Server 2003 so simply go to your Command Prompt and type in iisapp.vbs (the .vbs is optional) and you'll have an instant list of all the App Pool information you've always wanted to know.

You may need to type cscript iisapp.vbs instead if CScript isn't your default WSH script host.

Strange DB Timeouts on SharePoint

For some reason, some of the SharePoint servers could not connect to the SQL Server. Some of the servers did not have a problem. The previous day everything was working, and nothing changed.

I could ping and telnet to the SQL Server, but still had the error.

I ran the following command to clear out the ARP Cache. That fixed the problem:

netsh interface ip delete arpcache


Now that I mention it, the network department had some issues.... mmmm.

User information not updated on SharePoint

When a user's full name or part there of, then the profile is not updated on SharePoint.
To fix, run:

stsadm -o migrateuser -oldlogin -newlogin

If you get the following error:

New user account does not have valid SID history.

Then run the command:

stsadm -o migrateuser -oldlogin -newlogin -igonoresidhistory

Last step, do an incremental import of user profiles on SharedServices.

Alerts partially not working

Some users on certain sites do not get alerts. Another bloggers post helped me.

Basically you do the following:

stsadm -o updatealerttemplates -url http://testserver -f "c:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\XML\alerttemplates.xml" -LCID 2057

I only used:

stsadm -o updatealerttemplates -url http://testserver

It worked for me.

ERROR: This Page has been modified since you opened it. You must open the page again.

ERROR: This Page has been modified since you opened it. You must open the page again. Refresh Page. This error was reported on only one site collection. This site collection was in it's own content database, over 100GB. Four web front ends and all reported the same error. Professor Google reported only custom pages and page layouts etc. Our problem occured on items that was out of the box, it occured on viewing lists, adding web parts to pages etc.
Further investigation showed that scheduled maintenance over the weekend did not take place. So the content database was defragmented, not a lot, but it was.
SharePoint SP2 was supposed to do the defragmentation online, which it didn't. After running rebuilding the indexes, the problem was solved. So this error has something to do with the indexes on your content database. I suspect the bigger your content datbase, the more you are likely to get this error if the indexes were not rebuild.

ERROR: request not found in the TrackedRequests. We might be creating and closing webs on different threads

When users entered information in a standard MOSS Form, the values of the fields were not saved. It was empty. Even though the fields are required. Automatic workflow is kicked off, but no values in the fields.

In the ULS Logs I found this error:

ERROR: request not found in the TrackedRequests. We might be creating and closing webs on different threads. ThreadId = 26, Free call stack = at Microsoft.SharePoint.SPRequestManager.Release(SPRequest request) ....

I searched all over on the internet and could not find a solution. This Form and Workflow is standard OOB and was not created using SharePoint Designer.

To complicate it further, I have 4 Web Front Ends, and only 1 was giving this issue. So I started at the Web.Config file.

The web.config files differed on the "working" WFE's and the "broken" WFE. The solution is quite simple. In this part of the web.config:


<pages enablesessionstate="false" enableviewstate="true" enableviewstatemac="true" validaterequest="false" pageparserfiltertype="Microsoft.SharePoint.ApplicationRuntime.SPPageParserFilter, Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" asynctimeout="7">


The enableViewState and EnableViewStateMac was set to "false" (on the "broken" WFE). So I changed both values to "true". Because that was the value on the "working" WFE's.

Walla! Problem solved.

Event 7888 and 5553

I have been getting these errors in my event log:


Event Type: Error
Event Source: Office SharePoint Server
Event Category: Office Server General
Event ID: 7888
Date: 2010/07/02
Time: 10:01:41 AM
User: N/A
Computer: xxxxxxxxxxxxxxxx
Description:
A runtime exception was detected. Details follow.
Message: Cannot insert duplicate key row in object 'dbo.UserMemberships' with unique index 'CX_UserMemberships_RecordId_MemberGroupId_SID'.
The statement has been terminated.

Techinal Details:
System.Data.SqlClient.SqlException: Cannot insert duplicate key row in object 'dbo.UserMemberships' with unique index 'CX_UserMemberships_RecordId_MemberGroupId_SID'.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.Office.Server.Data.SqlSession.ExecuteNonQuery(SqlCommand command)
at Microsoft.Office.Server.UserProfiles.WSSSynchSqlSession.SynchExecuteNonQuery(SqlCommand cmd, Boolean throwOnFail)
at Microsoft.Office.Server.UserProfiles.WSSSynchSqlSession.SynchExecuteNonQuery(SqlCommand cmd)
at Microsoft.Office.Server.UserProfiles.SiteSynchronizer.WriteChangeLogConsumed()
at Microsoft.Office.Server.UserProfiles.SiteSynchronizer.Synch()
at Microsoft.Office.Server.Diagnostics.FirstChanceHandler.ExceptionFilter(Boolean fRethrowException, TryBlock tryBlock, FilterBlock filter, CatchBlock catchBlock, FinallyBlock finallyBlock)

and


Event Type: Error
Event Source: Office SharePoint Server
Event Category: User Profiles
Event ID: 5553
Date: 2010/07/02
Time: 10:01:41 AM
User: N/A
Computer: V058MOSFFF002
Description:
failure trying to synch site ac379cbb-a2da-4637-ae11-fb511f07a1dc for ContentDB f788b294-67ca-499f-94d8-508f2946512c WebApp e971af36-c174-4c4f-9b6b-a0ee8b628274. Exception message was Cannot insert duplicate key row in object 'dbo.UserMemberships' with unique index 'CX_UserMemberships_RecordId_MemberGroupId_SID'.
The statement has been terminated..


To fix it in my case (we have MOSS SP2 installed), we ran the following commands:

stsadm -o sync -listolddatabases 5
* This will list the database giving the problem and you will be able to match the database ID with the one in the Event Log *

stsadm -o sync -listolddatabases 0
* This command list all databases that are being synchronised and the last time they were synchronised *


stsadm -o sync -synctiming M:5
* This command set the synctiming to occur every 5 minutes, so we can check our progress with the sync *

stsadm -o sync -deleteolddatabases 0
* This command will clear out the sync information for ALL the databases, don't be alarmed it will be back soon *

Give it a few hours and periodically check your event log and run this command:

stsadm -o sync -listolddatabases 0
* The command lists the databases that are sync'd *

Problem fixed....

Event: 2423

I recently had this Warning in my Event Log:

Event Type: Warning
Event Source: Windows SharePoint Services 3 Search
Event Category: Gatherer
Event ID: 2423
Date: 2010/03/23
Time: 09:09:04 AM
User: N/A
Computer: xxxxxxxxxxxxxx
Description:
The update cannot be started because all of the content sources were excluded by crawl rules,
or removed from the index configuration.

Context: Application 'Search index file on the search server', Catalog 'Search'

Details:
Unspecified error
(0x80004005)


I checked the Search Administration, and found that all my content sources were "indexing" but no indexing were taking place. I could not pause the crawling, and nothing was being added to the Crawl Logs.

I then restarted the "Windows Services SharePoint Search" service, and the "Office SharePoint Server Search".

I could then pause the various Content Sources, and Indexing started.

Thats it!

Deleting a Site Collection Administrator

I searched every where (almost) on the Net for an answer to this. Adding a secondary site collection administrator is very easy:

stsadm -o siteowner -url http://xxxx/sites/sitename -ownerlogin someuser -secondarylogin someuser2

Walla second site collection administrator added. If you have more than two site collection administrators you can add another by using this command:

stsadm -o adduser -url http://xxxx/sites/sitename -userlogin someuser3 -useremail someuser3@email.com -role "Full Control" -username "someuser3Name Surname" -siteadmin

Now what do you do when you want to remove Someuser3 from the Site Collection Administrators?

Quite easy:

1. Make a note of the user for the SecondaryLogin of the site collection
2. Add the someuser3 to the secondarylogin for the site. This will remove the previous secondarylogin for the site, and add someuser3 as the secondarylogin.
3. Add the user from step 2 (previous secondarylogin) back to the site.

This will remove someruser3 from the site collection administrators for the site collection.