It's pretty simple to add XPS indexing/support to a SharePoint 2010 search, but even so some people struggle. So just to make things real easy for you, I created a PowerShell script.
What you need to do:
- Logon to the server that's running SharePoint Server Search 14
- Copy the PowerShell script from below (some of it runs under right navigation)
- Paste it in to a text file > save as whatever.ps1.
- Run the PowerShell script.
Import-Module servermanager
$xps = Get-WindowsFeature XPS-Viewer
If ($xps.Installed -eq $FALSE)
{
Add-WindowsFeature XPS-Viewer
}
If ((Get-PsSnapin |?{$_.Name -eq "Microsoft.SharePoint.PowerShell"})-eq $null)
{
$PSSnapin = Add-PsSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue | Out-Null
}
Write-Host -ForegroundColor White "- Adding registry values ..."
New-Item -path "HKLM:\SOFTWARE\Microsoft\Office Server\14.0\Search\Setup\Filters\.xps"
New-ItemProperty "HKLM:\SOFTWARE\Microsoft\Office Server\14.0\Search\Setup\Filters\.xps" -Name "Extension" -value "xps" -PropertyType String -Force | Out-Null
New-ItemProperty "HKLM:\SOFTWARE\Microsoft\Office Server\14.0\Search\Setup\Filters\.xps" -Name "FileTypeBucket" -value "1" -PropertyType dword -Force | Out-Null
New-ItemProperty "HKLM:\SOFTWARE\Microsoft\Office Server\14.0\Search\Setup\Filters\.xps" -Name "MimeTypes" -value "application/xps" -PropertyType String -Force | Out-Null
New-Item -path "HKLM:\SOFTWARE\Microsoft\Office Server\14.0\Search\Setup\ContentIndexCommon\Filters\Extension\.xps"
New-ItemProperty -path "HKLM:\SOFTWARE\Microsoft\Office Server\14.0\Search\Setup\ContentIndexCommon\Filters\Extension\.xps" -Name "(Default)" -value "{1E4CEC13-76BD-4ce2-8372-711CB6F10FD1}" -PropertyType MultiString -Force | Out-Null
Get-SPEnterpriseSearchServiceApplication | New-SPEnterpriseSearchCrawlExtension "xps"
Stop-Service "OSearch14"
Start-Service "OSearch14"
Issue: The My Site of [USER] is scheduled for deletion.
The My Site of [USER] is scheduled for deletion in 3 days. As their manager you are now the temporary owner of their site. This temporary ownership gives you access to the site to copy any business-related information you might need. To access the site use this URL: http://mysites/personal/[USER]
Cause:
- User profile deleted
- User profile re-imported
- User profile has its Personal site property missing
There's a Timer Job that runs every hour called My Site Cleanup Job. This job among other things looks at dbo.Profile_DeletedUsers and My Sites that don't have a current user profile association.
When it finds a My Site without a user profile association it adds the site to the dbo.MySiteDeletionStatus table in the Profile DB.
Fix:
- Ensure the User Profile has the site associated with his account
The following query will give you profiles with a My Site.
SELECT PropertyVal, p.*
FROM dbo.UserProfileValue v
inner join dbo.UserProfile_Full p on p.RecordID=v.RecordID
where PropertyID=22 and PropertyVal is not null
If the account isn't there you need to go to: Central Administration > Manage User Profiles and add the URL to the Personal site property.
- Delete the MySiteDeletionStatus record.
This table has a NotificationStatus column with values 1-3 calculated based on the Created column.
1 = 14 days til deletion email, 2 = 3 days til deletion and 3 = 1 day til deletion.
This last step is the only crucial one.
BTW: This theoretically will leave the Db in an unsupported state, because we're not supposed to edit or even query SP DB's directly... so don't tell anyone and no one will know.
Issue:
Log Name: Application
Source: Microsoft-SharePoint Products-SharePoint Foundation
Date: 15/07/2011 12:00:31 AM
Event ID: 5586
Task Category: Database
Level: Error
Keywords:
User: ray
Computer: SPS2010
Description:
Unknown SQL Exception 2812 occurred. Additional error information from SQL Server is included below.
Could not find stored procedure 'proc_UpdateStatisticsNVP'.
Cause:
After the June 2011 CU I started getting this error every night in the logs.
A quick search through \Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\SQL
showed [dbo].[proc_UpdateStatistics] was calling this proc.
So I assume where ever I find [dbo].[proc_UpdateStatistics] I should also find [dbo].[proc_UpdateStatisticsNVP]
Fix:
The fix requires that each DB that has [dbo].[proc_UpdateStatistics] also have [dbo].[proc_UpdateStatisticsNVP] the following SQL Query will help you identify the DB's:
exec sp_msforeachdb 'if exists(select name from [?].sys.objects where type = ''P'' and name like ''%proc_UpdateStatistics%'') AND not exists(select name from [?].sys.objects where type = ''P'' and name like ''%proc_UpdateStatisticsNVP%'') select ''?'''
This will give you the DB's that have [dbo].[proc_UpdateStatistics] but don't have [dbo].[proc_UpdateStatisticsNVP] then all you need to do is create the proc using an existing one...
Hello my friend!
Me and you obviously have something in common; we've experience the joy of configuring SharePoint 2010's User Profile Synchronization Service.
You've read all the other blog post's and now you're here. There isn't much I can tell you that you probably haven't read... most of the issues are caused by not following Microsoft instructions: http://technet.microsoft.com/en-us/library/ee721049.aspx#StartUPSSProc, and the fact that Forefront Identity Manager (FIM) is a PoS :)
One of the most important lines is:
After the User Profile Synchronization service is started, the farm account is no longer required to be an administrator on the synchronization server. To improve the security of your SharePoint Server installation, remove the farm account from the Administrators group on the synchronization server.
That means the account HAS to be in the local Administrators group before you can start it.
Other things to take in to consideration:
- In a multi-server farm "Network Service" and SP account which is starting the User Profile Synchronization Service should also have (Component Services) Local Launch and Activation rights on:
- Forefront Identity Manager Controller Object {36574FCB-E5F2-4C55-AA06-146B2B8FBD95}
- Forefront Identity Manager Management Agents {10B6A600-6EE8-44F3-AC02-2CA42B08A2B5}
- Forefront Identity Manager Script Host Loader {76032766-22CF-497D-BA0D-4F0583F05D15}
- Forefront Identity Manager Synchronization Service {835BEE60-8731-4159-8BFF-941301D76D05}
- A quick way to make sure your account has rights to launch FIM Synchronization Service is to login using the account and run C:\Program Files\Microsoft Office Servers\14.0\Synchronization Service\UIShell\miisclient.exe
- Verify there is only 1 ForefrontIdentityManager Trusted Root Certificate
Winows Key + R (run): mmc > Ctrl + M > Certificates > Add > Computer Account > Local
If there are more delete ALL of them and restart the service from Central Admin.
- Verify that both ForeFront Identity Manager services are set to Automatic and the Log On As identity is your SP account.
- Make sure the SP account is an owner or at the very least FIM_SynchronizationService of the User Profile Sync DB's
- Give the "Network Service" account read/execute rights to "C:\Program Files\Microsoft Office Servers\14.0" directory.
- Check the Application Pool is running and/or Recycle it.
It will be the App Pool which is running "C:\Program Files\Microsoft Office Servers\14.0\webservices\Profile"
- Try reinstalling FIM it's in your SP2010 install
SPS2010\Global\Ppl\pplwfe.msi
There's an update in the December 2010 CU "pplwfe-x-none.msp"
- If you have set NetBiosDomainNamesEnabled on the User Profile Service application to True you may get Error: Unable to process Create message when you try a new Configure Synchronization Connection if you installed the SP2010 December 2010 CU; in this case you're stuffed and it's broken until MS release the Feb 2011 CU.
To enable netbios names read: http://blogs.msdn.com/b/spses/archive/2010/04/01/sharepoint-2010-provisioning-user-profile-synchronization.aspx
- User Profile Synchronization Service suck on "Starting"
Try running the "ProfileSynchronizationSetupJob" timer job, then check the Timer Job Status page... chance are the Progress is 0%
I've had this issue and I went home came back the next day and it was done.
- ProfileSynchronizationSetupJob is stuck on "Pausing"... Delete the Timer service cache.
Read: http://support.microsoft.com/kb/939308/en-us
- If you've tried all of the above it's now time to do what you've been holding out on... yup trawl through the damn logs.
- Read: http://blogs.msdn.com/b/spses/archive/2010/12/02/guide-to-user-profile-service-application-upa-part-2-setting-up-the-user-profile-service-application.aspx
- Enable verbose logging for the Microsoft.ResourceManagement.Service.exe and SharePoint Portal Server > User Profiles
- Don't be afraid of restarting the server... with any luck it might all just work after.
After disabling page security verification on the web application (CA > Manage web applications > General Settings > Security validation is: Off ) Silverlight stopped working when trying to create Sites/Lists.
Error
An unhandled exception occured in the Silverlight Application
At this stage the only fix is to enable page security verification.
Issue:
Session "OfficeSearch14HealthSession" failed to start with the following error: 0xC0000035
Cause:
The Search Health Monitoring - Trace Events job can't access a Health Monitoring resource.
The offending class is Microsoft.Office.Server.Search.Monitoring.TraceDiagnosticsProvider this job is run every minute and if it conflicts with another job accessing the same resource it will fail.
The following PS command will return Jobs scheduled to run < every 5 minutes:
Get-SPTimerJob | Where {$_.IsDisabled -eq $FALSE -and $_.Schedule.Description -eq "Minutes" -and $_.Schedule.Interval -lt "5"} | Foreach-Object{$_.PSObject.Properties | Select-Object Name,Value;}
Fix:
Changed the Search Health Monitoring - Trace Events job schedule to every 2 minutes, if you really want to have clean logs.
Update: I'm working on the fix but it seems related to current SP log file.
I was recently introduced to what is now a critical tool. The ULS Viewer http://code.msdn.microsoft.com/ULSViewer
The ULSViewer tool performs various actions against the data from ULS log files in order to better analyze it. ULSViewer allows the user to:
- Highlight data of importance to the user on the fly
- Bookmark log entries
- Append logs to other logs in order to track trends
- Hide unimportant data
- Only view critical log entries by sorting data by severity
- Write rules to prompt the user when certain events occur
- View your data in a spreadsheet instead of the text file ULS generates
- Monitor remote machines logs that are running ULS services
- Open multiple logs at the same time in order to compare log files.
- Open logs files from multiple machines at the same time.
I was setting up a new SPS 2010 environment when I got:
Error
The given key was not present in the dictionary.
Troubleshoot issues with Microsoft SharePoint Foundation.
Correlation ID: 59994675-c867-4246-8fd3-56f6a70087c9
Date and Time: 5/22/2010 12:07:50 PM
It's highly unlikely (read impossible) that you will receive the same error as Correlation ID's are unique. To understand a little more about them read this: ILM 2007 Design Concepts
So how do you fix your problem after you receiving an error.
My initial strategy was to check the event log for a more detailed description because I was setting up an SPS service... that search was fruitless so I checked the sharepoint logs.
Steps to resolve:
- Go to C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS
- Find the log that was modified around the time of the error
- Search for your Correlation ID.
My error is below and it basically means there's something missing from the AD account I used to run the service which MS think should be there... well guess what, yours truly used a security group instead of a User account.
05/22/2010 12:07:48.63 w3wp.exe (0x2408) 0x277C SharePoint Foundation Runtime tkau Unexpected System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary. at System.ThrowHelper.ThrowKeyNotFoundException() at System.Collections.Generic.Dictionary`2.get_Item(TKey key) at Microsoft.SharePoint.Utilities.SPUtility.GetUserPropertyFromAD(SPWebApplication webApplicaiton, String loginName, String propertyName) at Microsoft.SharePoint.Administration.SPManagedAccount.GetUserAccountControl(String username) at Microsoft.SharePoint.ApplicationPages.AdminConfigServices.ValidateUserLogon(Object sender, ServerValidateEventArgs e) at System.Web.UI.WebControls.CustomValidator.OnServerValidate(String value) at System.Web.UI.WebControls.BaseValidator.Validate() at System.Web.UI.Page.Validate() at System.Web.UI.WebControls.Button.Ra... 59994675-c867-4246-8fd3-56f6a70087c9
01/27/2009 16:23:40.23
w3wp.exe (0x0114)
0x00E8
CMS
Publishing Cache
6p4o
Warning
Unable to connect publishing blob caching. Web.Config is not set up correctly. Cache is not valid. WebId is '1303494830', Url is 'http://vmmossdev:8088/_themes/myTheme/viewheadergrad_mt.gif'.
You may see the above warning in your event log, if the "BlobCache" configsection is declared without been defined... well I did, and I google and could find a single valid explanation.
The Fix: Just add a node in the node like...
<BlobCache location="C:\blobCache" path="\.(gif|jpg|png|css|js)$" maxSize="10" enabled="false" />
After adding the Reports Services virtual directories to a sharepoint site/web application you see the below in the event log.
Event Type: Error
Event Source: Office SharePoint Server
Event Category: Publishing Cache
Event ID: 5785
Date: 21/05/2008
Time: 3:01:52 PM
User: N/A
Computer:
Description:
Unable to connect publishing custom string handler for output caching. IIS Instance Id is '1957155781', Url is 'http://localhost/ReportServer/ReportService2005.asmx'.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Cause: microsoft.sharepoint.publishing.publishinghttpmodule is trying to cache an unmanaged path.
Fix: Remove this httpmodule from the ./ReportServer virtual directory web.config.
Steps:
- Open the web.config file (D:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\ReportServer\web.config)
- Locate the <httpModules> node, if it's not there create it below the <httpHandlers> node.
- Add the following <remove name="PublishingHttpModule" />.
Everytime you call a report or the reportserver URL/web service SharePoint tries to turn caching on and it's not a managed path it throws an error, so we need to remove the module in the reportserver.
Event Type: Error
Event Source: DCOM
Event Category: None
Event ID: 10016
Date: 27/04/2008
Time: 9:35:28 AM
User: VMDomain\SPConfigAcct
Computer: VMDEV
Description:
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{61738644-F196-11D0-9953-00C04FD919C1}
to the user VMDomain\SPConfigAcct SID (S-1-5-21-2183741768-1351022156-4028051374-1111). This security permission can be modified using the Component Services administrative tool.
The event kind of tells you how to fix it: "This security permission can be modified using the Component Services administrative tool".
But first we need to figure out what 61738644-F196-11D0-9953-00C04FD919C1 is;
Win + R > regedit > search = IIS WAMREG admin Service
- Win + R C:\WINDOWS\system32\Com\comexp.msc
- Expand Component Services > Computers > My Comp > DCOM Config
- Right Click IIS WAMREG admin Service
- Select Properties > Security
- Launch and Activation Permissions "Customize" > Edit
- You need to grant the "user" rights.
- My user is a SharePoint Config account and as such belongs to the IIS_WPG group, but is also the sole member of WSS_RESTRICTED_WPG group so using the safest security I'm going to use WSS_RESTRICTED_WPG until more errors appear.
Because it's not going to need to lauch or activate remotely I just need to enable "Local".
I was recently tasked with resolving a date issue for a Global Portal. People outside the US were complaining about the date format (MM/DD/YYYY)... I've long been a proponent of ISO dates (YYYY-MM-DD) and 28 day months... OK maybe 28 day months wouldn't be so fun for savants to calculate.
Anyway the solution was obviously to include the month name. My first reaction was to change FLDTYPES.xml, then to create a custom field type, and finally it soon became obvious a calculated field should do the trick.
So I searched a minute for a formula to render the date with the month name in the middle... I didn't find any so I created it.
It is a piece of cake to do it in VB and C#, but what does a calculated field provide? As it turns out quite a lot. Everybody knows your classic [Today], LTrim, etc... but what I didn't know it that it supports a lot of Excel functions (not the VBA ones).
Before we get to the list of functions, here's how to write the month name formula:
=TEXT([DateField],"dd") &"/"& TEXT([DateField],"mmm") &"/"& TEXT([DateField],"yyyy")
or
=DAY([DateField]) &"/"& TEXT([DateField],"mmm") &"/"& YEAR([DateField])
or better
=IF(NOT(ISBLANK([DateField])),DAY([DateField])&" "&TEXT([DateField],"mmm")&" "&YEAR([DateField]),"")
Now here's probably what your here for, a list of SharePoint Calculated Field/Column functions:
source (http://office.microsoft.com/en-us/excel/HP100791861033.aspx)
Date and time functions
| Function | Description |
|---|
| DATE | Returns the serial number of a particular date |
| DATEVALUE | Converts a date in the form of text to a serial number |
| DAY | Converts a serial number to a day of the month |
| DAYS360 | Calculates the number of days between two dates based on a 360-day year |
| EDATE | Returns the serial number of the date that is the indicated number of months before or after the start date |
| EOMONTH | Returns the serial number of the last day of the month before or after a specified number of months |
| HOUR | Converts a serial number to an hour |
| MINUTE | Converts a serial number to a minute |
| MONTH | Converts a serial number to a month |
| NETWORKDAYS | Returns the number of whole workdays between two dates |
| NOW | Returns the serial number of the current date and time |
| SECOND | Converts a serial number to a second |
| TIME | Returns the serial number of a particular time |
| TIMEVALUE | Converts a time in the form of text to a serial number |
| TODAY | Returns the serial number of today's date |
| WEEKDAY | Converts a serial number to a day of the week |
| WEEKNUM | Converts a serial number to a number representing where the week falls numerically with a year |
| WORKDAY | Returns the serial number of the date before or after a specified number of workdays |
| YEAR | Converts a serial number to a year |
| YEARFRAC | Returns the year fraction representing the number of whole days between start_date and end_date |
Information functions
| Function | Description |
|---|
| CELL | Returns information about the formatting, location, or contents of a cell |
| ERROR.TYPE | Returns a number corresponding to an error type |
| INFO | Returns information about the current operating environment |
| ISBLANK | Returns TRUE if the value is blank |
| ISERR | Returns TRUE if the value is any error value except #N/A |
| ISERROR | Returns TRUE if the value is any error value |
| ISEVEN | Returns TRUE if the number is even |
| ISLOGICAL | Returns TRUE if the value is a logical value |
| ISNA | Returns TRUE if the value is the #N/A error value |
| ISNONTEXT | Returns TRUE if the value is not text |
| ISNUMBER | Returns TRUE if the value is a number |
| ISODD | Returns TRUE if the number is odd |
| ISREF | Returns TRUE if the value is a reference |
| ISTEXT | Returns TRUE if the value is text |
| N | Returns a value converted to a number |
| NA | Returns the error value #N/A |
| TYPE | Returns a number indicating the data type of a value |
Logical functions
| Function | Description |
|---|
| AND | Returns TRUE if all of its arguments are TRUE |
| FALSE | Returns the logical value FALSE |
| IF | Specifies a logical test to perform |
| IFERROR | Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula |
| NOT | Reverses the logic of its argument |
| OR | Returns TRUE if any argument is TRUE |
| TRUE | Returns the logical value TRUE |
Math and trigonometry functions
| Function | Description |
|---|
| ABS | Returns the absolute value of a number |
| ACOS | Returns the arccosine of a number |
| ACOSH | Returns the inverse hyperbolic cosine of a number |
| ASIN | Returns the arcsine of a number |
| ASINH | Returns the inverse hyperbolic sine of a number |
| ATAN | Returns the arctangent of a number |
| ATAN2 | Returns the arctangent from x- and y-coordinates |
| ATANH | Returns the inverse hyperbolic tangent of a number |
| CEILING | Rounds a number to the nearest integer or to the nearest multiple of significance |
| COMBIN | Returns the number of combinations for a given number of objects |
| COS | Returns the cosine of a number |
| COSH | Returns the hyperbolic cosine of a number |
| DEGREES | Converts radians to degrees |
| EVEN | Rounds a number up to the nearest even integer |
| EXP | Returns e raised to the power of a given number |
| FACT | Returns the factorial of a number |
| FACTDOUBLE | Returns the double factorial of a number |
| FLOOR | Rounds a number down, toward zero |
| GCD | Returns the greatest common divisor |
| INT | Rounds a number down to the nearest integer |
| LCM | Returns the least common multiple |
| LN | Returns the natural logarithm of a number |
| LOG | Returns the logarithm of a number to a specified base |
| LOG10 | Returns the base-10 logarithm of a number |
| MDETERM | Returns the matrix determinant of an array |
| MINVERSE | Returns the matrix inverse of an array |
| MMULT | Returns the matrix product of two arrays |
| MOD | Returns the remainder from division |
| MROUND | Returns a number rounded to the desired multiple |
| MULTINOMIAL | Returns the multinomial of a set of numbers |
| ODD | Rounds a number up to the nearest odd integer |
| PI | Returns the value of pi |
| POWER | Returns the result of a number raised to a power |
| PRODUCT | Multiplies its arguments |
| QUOTIENT | Returns the integer portion of a division |
| RADIANS | Converts degrees to radians |
| ROMAN | Converts an arabic numeral to roman, as text |
| ROUND | Rounds a number to a specified number of digits |
| ROUNDDOWN | Rounds a number down, toward zero |
| ROUNDUP | Rounds a number up, away from zero |
| SERIESSUM | Returns the sum of a power series based on the formula |
| SIGN | Returns the sign of a number |
| SIN | Returns the sine of the given angle |
| SINH | Returns the hyperbolic sine of a number |
| SQRT | Returns a positive square root |
| SQRTPI | Returns the square root of (number * pi) |
| SUBTOTAL | Returns a subtotal in a list or database |
| SUM | Adds its arguments |
| SUMIF | Adds the cells specified by a given criteria |
| SUMIFS | Adds the cells in a range that meet multiple criteria |
| SUMPRODUCT | Returns the sum of the products of corresponding array components |
| SUMSQ | Returns the sum of the squares of the arguments |
| SUMX2MY2 | Returns the sum of the difference of squares of corresponding values in two arrays |
| SUMX2PY2 | Returns the sum of the sum of squares of corresponding values in two arrays |
| SUMXMY2 | Returns the sum of squares of differences of corresponding values in two arrays |
| TAN | Returns the tangent of a number |
| TANH | Returns the hyperbolic tangent of a number |
| TRUNC | Truncates a number to an integer |
Statistical functions
| Function | Description |
|---|
| AVEDEV | Returns the average of the absolute deviations of data points from their mean |
| AVERAGE | Returns the average of its arguments |
| AVERAGEA | Returns the average of its arguments, including numbers, text, and logical values |
| AVERAGEIF | Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria |
| AVERAGEIFS | Returns the average (arithmetic mean) of all cells that meet multiple criteria. |
| BETADIST | Returns the beta cumulative distribution function |
| BETAINV | Returns the inverse of the cumulative distribution function for a specified beta distribution |
| BINOMDIST | Returns the individual term binomial distribution probability |
| CHIDIST | Returns the one-tailed probability of the chi-squared distribution |
| CHIINV | Returns the inverse of the one-tailed probability of the chi-squared distribution |
| CHITEST | Returns the test for independence |
| CONFIDENCE | Returns the confidence interval for a population mean |
| CORREL | Returns the correlation coefficient between two data sets |
| COUNT | Counts how many numbers are in the list of arguments |
| COUNTA | Counts how many values are in the list of arguments |
| COUNTBLANK | Counts the number of blank cells within a range |
| COUNTIF | Counts the number of cells within a range that meet the given criteria |
| COUNTIFS | Counts the number of cells within a range that meet multiple criteria |
| COVAR | Returns covariance, the average of the products of paired deviations |
| CRITBINOM | Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value |
| DEVSQ | Returns the sum of squares of deviations |
| EXPONDIST | Returns the exponential distribution |
| FDIST | Returns the F probability distribution |
| FINV | Returns the inverse of the F probability distribution |
| FISHER | Returns the Fisher transformation |
| FISHERINV | Returns the inverse of the Fisher transformation |
| FORECAST | Returns a value along a linear trend |
| FREQUENCY | Returns a frequency distribution as a vertical array |
| FTEST | Returns the result of an F-test |
| GAMMADIST | Returns the gamma distribution |
| GAMMAINV | Returns the inverse of the gamma cumulative distribution |
| GAMMALN | Returns the natural logarithm of the gamma function, Γ(x) |
| GEOMEAN | Returns the geometric mean |
| GROWTH | Returns values along an exponential trend |
| HARMEAN | Returns the harmonic mean |
| HYPGEOMDIST | Returns the hypergeometric distribution |
| INTERCEPT | Returns the intercept of the linear regression line |
| KURT | Returns the kurtosis of a data set |
| LARGE | Returns the k-th largest value in a data set |
| LINEST | Returns the parameters of a linear trend |
| LOGEST | Returns the parameters of an exponential trend |
| LOGINV | Returns the inverse of the lognormal distribution |
| LOGNORMDIST | Returns the cumulative lognormal distribution |
| MAX | Returns the maximum value in a list of arguments |
| MAXA | Returns the maximum value in a list of arguments, including numbers, text, and logical values |
| MEDIAN | Returns the median of the given numbers |
| MIN | Returns the minimum value in a list of arguments |
| MINA | Returns the smallest value in a list of arguments, including numbers, text, and logical values |
| MODE | Returns the most common value in a data set |
| NEGBINOMDIST | Returns the negative binomial distribution |
| NORMDIST | Returns the normal cumulative distribution |
| NORMINV | Returns the inverse of the normal cumulative distribution |
| NORMSDIST | Returns the standard normal cumulative distribution |
| NORMSINV | Returns the inverse of the standard normal cumulative distribution |
| PEARSON | Returns the Pearson product moment correlation coefficient |
| PERCENTILE | Returns the k-th percentile of values in a range |
| PERCENTRANK | Returns the percentage rank of a value in a data set |
| PERMUT | Returns the number of permutations for a given number of objects |
| POISSON | Returns the Poisson distribution |
| PROB | Returns the probability that values in a range are between two limits |
| QUARTILE | Returns the quartile of a data set |
| RANK | Returns the rank of a number in a list of numbers |
| RSQ | Returns the square of the Pearson product moment correlation coefficient |
| SKEW | Returns the skewness of a distribution |
| SLOPE | Returns the slope of the linear regression line |
| SMALL | Returns the k-th smallest value in a data set |
| STANDARDIZE | Returns a normalized value |
| STDEV | Estimates standard deviation based on a sample |
| STDEVA | Estimates standard deviation based on a sample, including numbers, text, and logical values |
| STDEVP | Calculates standard deviation based on the entire population |
| STDEVPA | Calculates standard deviation based on the entire population, including numbers, text, and logical values |
| STEYX | Returns the standard error of the predicted y-value for each x in the regression |
| TDIST | Returns the Student's t-distribution |
| TINV | Returns the inverse of the Student's t-distribution |
| TREND | Returns values along a linear trend |
| TRIMMEAN | Returns the mean of the interior of a data set |
| TTEST | Returns the probability associated with a Student's t-test |
| VAR | Estimates variance based on a sample |
| VARA | Estimates variance based on a sample, including numbers, text, and logical values |
| VARP | Calculates variance based on the entire population |
| VARPA | Calculates variance based on the entire population, including numbers, text, and logical values |
| WEIBULL | Returns the Weibull distribution |
| ZTEST | Returns the one-tailed probability-value of a z-test |
Text functions
| Function | Description |
|---|
| ASC | Changes full-width (double-byte) English letters or katakana within a character string to half-width (single-byte) characters |
| CHAR | Returns the character specified by the code number |
| CLEAN | Removes all nonprintable characters from text |
| CODE | Returns a numeric code for the first character in a text string |
| CONCATENATE | Joins several text items into one text item |
| DOLLAR | Converts a number to text, using the $ (dollar) currency format |
| EXACT | Checks to see if two text values are identical |
| FIND, FINDB | Finds one text value within another (case-sensitive) |
| FIXED | Formats a number as text with a fixed number of decimals |
| JIS | Changes half-width (single-byte) English letters or katakana within a character string to full-width (double-byte) characters |
| LEFT, LEFTB | Returns the leftmost characters from a text value |
| LEN, LENB | Returns the number of characters in a text string |
| LOWER | Converts text to lowercase |
| MID, MIDB | Returns a specific number of characters from a text string starting at the position you specify |
| PHONETIC | Extracts the phonetic (furigana) characters from a text string |
| PROPER | Capitalizes the first letter in each word of a text value |
| REPLACE, REPLACEB | Replaces characters within text |
| REPT | Repeats text a given number of times |
| RIGHT, RIGHTB | Returns the rightmost characters from a text value |
| SEARCH, SEARCHB | Finds one text value within another (not case-sensitive) |
| T | Converts its arguments to text |
| TEXT | Formats a number and converts it to text |
| TRIM | Removes spaces from text |
| UPPER | Converts text to uppercase |
| VALUE | Converts a text argument to a number |
A client asked me to highlight a row based on the value of a status field... If figured easy, just use a dataview wp, but no, they want to be able to maintain it without using SPDesigner. I always like to give clients what they want, and I wasn't about to tell them to start changing the dataview in notepad... The "proper" way to enable HTML would probably be to create a bew custom field type, but since we need a formula this is not a trivial task; there is however a file which controls the way fields are rendered (see previous post).
We need to modify this to stop sharepoint encoding the HTML. This method has zero performance impact and only takes a few minutes.
To do this the first step is to open \Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\XML\FLDTYPES.XML
There are a couple of ways to change FLDTYPES.XML that will stop it encoding HTML; the one I've chosen is to look for a hidden tag in the value returned by a calculated field.
You can change any field type, but I need to change the calculated field type as I need to use conditional formating based on a formula.
- Locate <FieldName="TypeName">Calculated</Field> in FLDTYPES.XML.
- Move down to the <RenderPattern Name="DisplayPattern"> node.
- Find the <Default> node.
- Add your logic between the <Default> and </Default> nodes.
You can use various functions and properties to enable your logic, please see http://msdn2.microsoft.com/en-us/library/ms439798.aspx for a list. In my case I just needed an If Else as I didn't want to impact on existing calculated fields, so I opted for the <IfSubString> function, as follows:
<IfSubString>
<Expr1><![CDATA[<.RP>]]></Expr1>
<Expr2><Column/></Expr2>
<Then><HTML><Column/></HTML></Then>
<Else><Column HTMLEncode="TRUE" AutoHyperLink="TRUE" AutoNewLine="TRUE"/></Else>
</IfSubString>
The above basically means if the data in the column contains <.RP> then just render the contents. Otherwise do the MOSS default.
You could of course complicate the above and use a switch with <GetFileExtension> like:
<Switch>
<Expr><GetFileExtension><Column/></GetFileExtension></Expr>
<Case Value="RP>">
<HTML><Column/></HTML>
</Case>
<Default>
<Column HTMLEncode="TRUE" AutoHyperLink="TRUE" AutoNewLine="TRUE"/>
</Default>
</Switch>
The above has the benfit of allowing different logic for different scenarios.
The final step is to create a calculated field that returns <.RP>. Any record with this tag will enable the logic, and since <.RP> is an unknown tag browsers won't render it.
Using <GetFileExtension> you need to make sure your <.RP> is at the end.
The calculated field formula will look something like:
=IF([Active],"<.RP><b style='color:0000ff'>","<.RP><b style='color:ff0000'>") & [Title] &" "& [Column1] &" "& [Column2] &"</b>"
Look here for formulas http://msdn2.microsoft.com/en-us/library/bb862071.aspx
Now just create a view with just your calculated field and you have a list highlighting active records (with limited sorting)... but you could always just create a calculated field for each.
OK now you've probably realized your filtering has HTML... the fix for that is to disable filtering if the column has your tag... create a variable ( <SetVar Scope="Request">) in your logic and set it, then check if it's been set outside <RenderPattern> set <Field Name="Filterable">FALSE</Field>. I haven't figured out how to render the heading as it displays without using Javascript... I could write this up one days. Notes: CAML is case sensative, and you'll need to restart IIS for any change to FLDTYPES.XML.
FldTypes.xml Each front-end Web server in a deployment of Windows SharePoint Services has one FLDTYPES.XML file located in the Local_Drive:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\XML folder that is used during site or list creation to define how field types are rendered in the different modes for viewing list data. File Format The following excerpt outlines the format of FLDTYPES.XML: Xml <FieldTypes>
<FieldType>
<Field Name="TypeName">Counter</Field>
<Field Name="TypeDisplayName">$Resources:core,fldtype_counter;</Field>
<Field Name="InternalType">Counter</Field>
<Field Name="SQLType">int</Field>
<Field Name="ParentType"></Field>
<Field Name="UserCreatable">FALSE</Field>
<Field Name="Sortable">TRUE</Field>
<Field Name="Filterable">TRUE</Field>
<RenderPattern Name="HeaderPattern">
...
</RenderPattern>
<RenderPattern Name="DisplayPattern">
...
</RenderPattern>
<RenderPattern Name="EditPattern">
...
</RenderPattern>
<RenderPattern Name="NewPattern" DisplayName="NewPattern">
...
</RenderPattern>
<RenderPattern Name="PreviewDisplayPattern">
...
</RenderPattern>
<RenderPattern Name="PreviewEditPattern">
...
</RenderPattern>
<RenderPattern Name="PreviewNewPattern">
...
</RenderPattern>
</FieldType>
<FieldType>
...
</FieldType>
...
</FieldTypes>
RenderPattern elements define how an item is displayed in each of the possible modes for viewing list data. These modes include the header patterns used at the top of each list in the toolbar, the modes used in forms for displaying, editing, or creating items, and preview rendering patterns used by a Web-editing application that is compatible with Windows SharePoint Services, such as Microsoft Office SharePoint Designer 2007. To create a custom field that derives from a base field type, you can add a field definition to the Schema.xml file of a custom list definition Feature. For a programming task that shows how to add a field to a custom list definition, see How to: Create a Custom List Definition. For information about creating a custom field type, see Custom Field Types.
The reality of working with sharepoint is that you need to use a virtual pc running win2003/2008.
My preference has always been for VMWare, and VMWare 6.0 really wipes the floor with MS Virtual PC.
Unfortunately sometimes I am forced to work with VPC, and one of the things that really bugged me was a jerky cursor... searching google didn't help much so I figured it would be something to do with the graphics (after changing a reg setting to increase resolution of the mouse and noticing other jerkiness), and sure enough I eventually found it.
Fix: On a VPC with Win2003 go to Display Properties > Settings > Advanced > Troubleshoot slide the hardware acceleration all the way to Full.
A Web part/InfoPath form with managed code causes the above error.
Cause: DLL's that access databases require at least the WSS_Medium security policy in the web.config file.
If you receive a security message from the web part, it's usually the trust element in the web.config file.
You could also have a dll outside the GAC that's trying to access some part of the SharePoint Object Model.
Fix:
There's a couple of ways to resolve this issue.
- Put you dll in the GAC. I don't like putting limited use web parts in there and managed code for InfoPath doesn't like it.
- Open wss_mediumtrust.config & wss_minimaltrust.config usually (C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\config\) look in your web.config file for the exact path.
Find in wss_mediumtrust.config:
<SecurityClass Name="SqlClientPermission" Description="System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
Copy and paste it in to the <SecurityClasses> node of wss_minimaltrust.config.
In the PermissionSet section of this configuration file, add the following:
Find in wss_mediumtrust.config: <IPermission class="SqlClientPermission" version="1" Unrestricted="true"/>
Copy and paste it in to the a <PermissionSet> node of wss_minimaltrust.config. That about covers it.
- You could also set the trust level to "wss_mediumtrust" or create a custom trust level. Google it...
A web.config modification definition is expressed as a set of commands that, when processed by the web.config manipulator in Windows SharePoint Services, changes the state of web.config. You can string together a set of these commands to ensure that they apply the desired tags and attributes within web.config. Each modification is expressed as an object in the administrative object model.
Use the WebConfigModifications property of the SPWebApplication or SPWebService class to get the collection of web.config modifications either in the Web application or in all Web applications within the Web service.
Here's a console app as POC.
using System;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
class Program {
private const string ScriptResourceHandler = @"<add verb=""GET,HEAD"" path=""FOO.bar"" type=""System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions"" validate=""false""/>"
static void Main(string[] args)
{
SPSite siteCollection = new SPSite("http://localhost");
SPWebApplication webApp = siteCollection.WebApplication;
SPWebConfigModification modification = new SPWebConfigModification();
modification.Path = "configuration/system.web/httpHandlers"
modification.Name = "Example"
modification.Value = value;
modification.Owner = "ExampleOwner"
modification.Sequence = 0;
modification.Type = SPWebConfigModification.SPWebConfigModificationType.EnsureChildNode;
webApp.WebConfigModifications.Add(modification);
// .Remove doesn't remove it from the web.config, BTW... :(
webApp.Farm.Services.GetValue<SPWebService>().ApplyWebConfigModifications();
}
}
|
|