2008-10-05

Event ID: 6398 6482 7076 SharePoint memory is corrupt.


Event ID: 6398 6482 7076
Attempted to read or write protected memory. This is often an indication that other memory is corrupt.


I read a lot of posts saying this was caused by SharePoint and fixed with SP1, and/or an issues with .net 2.0 fixed with .net 2.1. Both are incorrect for my scenario; my servers were completely up to date and all of a sudden I started getting these errors.
I also read that restarting the Services Timer would resolve the issue. All of these are incorrect. The issue is caused by IIS (Adsiis.dll & Wadsiis.dll) and >2 AD users trying to access the same process. None of my dev machines have exhibited these errors because none use AD accounts.


Fix: http://support.microsoft.com/kb/946517/en-us



2008-08-18

Programmatically creating a Rich Text Field in a SharePoint List

Sounds easy enough, but it wasn't immediately obvious.



The below creates a list, adds a Rich Text field with full HTML and adds it to the default view.




27   SPWeb oWeb = null;


28   oWeb = oSite.RootWeb;


29   Guid gList;


30   SPList oList = null;


31   gList = oWeb.Lists.Add("My List", "Some description", SPListTemplateType.GenericList);


32   oWeb.Update();


33   oList = oWeb.Lists[gList];


34   SPFieldMultiLineText oFldBody = (SPFieldMultiLineText)oList.Fields[oList.Fields.Add("Body", SPFieldType.Note, true)];


35   oFldBody.Description = "Replaceable Parameters: {password}, {userid}, {usergroups}, {usersite}, {siteadmin}";


36   oFldBody.RichText = true;


37   oFldBody.RichTextMode = SPRichTextMode.FullHtml;


38   oFldBody.Update();


39   oList.Update();


40 


41   SPView oView = oList.DefaultView;


42   SPViewFieldCollection oViewFields = oView.ViewFields;


43   oViewFields.Add(oFldBody);


44   oView.Update();


2008-08-14

SharePoint Copy list item to multiple sites - Feature

There doesn't seem to be any OOTB solution to copy list items to > 1 other site... so I wrote a quick aspx page and added it to a feature/solution which I would like to share with you.



The APX page just loops through the Web collection and checks for the list and if it's available enables a checkbox so you can copy the item.


The feature adds a context menu to a list item with a link to the page.


There's nothing compiled so it's easy to edit (just open the solution file).



Feature link: Download

To Do: Optimize sorting DataView/DataTable, add Doc Library support, add a FeatureActivated/Deactivated to add ASPX page to sitemap, add option to create list if it doesn't exist and change to a dll/code behind.


Keywords: SPGridView, SPList, SPListItem, Copy, Add, Deploy, Feature, context menu.


Notes: I haven't tested this in a production environment

Screenshots:



2008-08-06

Filtering SharePoint List Libraries with Web Part Filters



Sometimes you need to filter data (ListViews/DataViews/Other web parts) based on some criteria be it the persons name, their email, a specific date range or from a parameter.

Microsoft provide a number of these Web Parts called filters. The problem is they only provide these in the Enterpri$e ver$ion.


So I made my own and I'm sharing them with you....


ray1.net Web Part Filters feature is comprised of 6 web parts.





  1. ray1.net Content Connector. This is a simple Literal Control that renders the value of a connection, or the text you specify. You can use it with, say the UserFilter and render some text that says "Welcome to my site UserFilter value!"

  2. ray1.net Filter Web Part: Current Page. This web part provides a filter connection that send information about the current page. The page needs to be in a list. You can use this to filter content based on who created the page or the file size or the modified date.


  3. ray1.net Filter Web Part: Date. This web part provides a filter connection that sends information about a selected date or date range. Say you had a DataView web part that sent a parameter to an SQL stored procedure.


  4. ray1.net Filter Web Part: QueryString. This filter accepts a QueryString parameter and connects to another web part. Pretty simple really, name the filter the same as the querystring parameter and your done.

  5. ray1.net Filter Web Part: User Name. This web part provides a User Name connection filter. This web part makes this feature incompatible with WSS v3, as I use the "Microsoft.Office.Server.UserProfiles.UserProfileManager" class.


  6. ray1.net Filter Web Part: Web ID. This web part provides a filter connection that sends the Web ID. Another simple one, but useful... Say you have a bunch of sites using the same template and you wanted to filter an SQL query.


All the filters accept multiple connections, and this is what they look like in edit mode.





This is what they look like normally





Download link: ray1.net Web Part Filters

To do: I could add a Text Filter and maybe change the Content Connector to accept n number of connections.



Keywords: Filter, web part, listview, SPSlicerBaseWebPart, ToolPart, IWebPartField, GetConnectionInterface, Connection Consumer Provider


2008-06-16

Report Server - SharePoint: IReportViewerMessages or could not be found

If you have Report Server running in a virtual directory under a SharePoint web app.



Error: The type Microsoft.SharePoint.Portal.Analytics.UI.ReportViewerMessages, Microsoft.SharePoint.Portal, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c does not implement IReportViewerMessages or could not be found.



Cause: Look in your SharePoint web.config file you'll see
<add key="ReportViewerMessages" value="Microsoft.SharePoint.Portal.Analytics.UI.ReportViewerMessages, Microsoft.SharePoint.Portal, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" />.



Fix: I looked at the Microsoft.SharePoint.Portal.Analytics Namespace and couldn't find a UI.ReportViewerMessages, so I just removed it from the web.config and I haven't experienced any issues.

You can also remove the setting from your ReportServer web.config's just add:

<appSettings>
<remove key="ReportViewerMessages" />

</appSettings>

2008-06-10

Report Server Error: The request failed with HTTP status 400: Bad Request


This also applies to the error message: Microsoft.ReportingServices.UI.Global+RSWebServiceWrapper+CantCommunicateWithReportServerException: The report server is not responding. Verify that the report server is running and can be accessed from this computer.

This took me a little while to figure out, but my initial suspicion was that it was related to the fact I was using it in a Host Header site, ei: I had changed lmhosts or I had multiple web sites using port 80.


Cause:

Report server uses the machine name when setting the config files, but when it tries to reference the url it can't be found because it's in another web site.
In my case the server was called MOSSDEV, and the report server virtual directories were in a web site called premisys.


Fix:


  1. Open "RSWebApplication.config" in the "Report Manager Virtual Directory".
    Right click the virtual directory icon in inetmgr, choose open.
    Default is: C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager

  2. Enter the full Report Server URL in the <ReportServerUrl> node.
    Eg:
    <ReportServerUrl>http://premisys/ReportServer</ReportServerUrl>

  3. Delete the <ReportServerVirtualDirectory> value but not the node.

  4. Open "rsreportserver.config" in the "Report Server Virtual Directory".
    Default is: C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer
  5. Change the <urlroot>http://<servername>/ReportServer</urlroot>
    to match your URL.

2008-05-26

Customizing the sharepoint search control


There are a couple ways of doing this.

  1. Modify ..\12\TEMPLATE\FEATURES\ContentLightup\Controls\searchArea.xml

  2. Modify ..\12\TEMPLATE\CONTROLTEMPLATES\searcharea.ascx

  3. Create a new feature and control

  4. Create a new feature that changes parameters for "SmallSearchInputBox"


I believe option 4 to be the best as it uses Microsoft.SharePoint.PortalWebControls.SearchBoxEx class and you don't need to create a new control.

There are a large number of properties you can use to customize the control and you should find the one you're looking for
microsoft.sharepoint.portal.webcontrols.searchboxex_properties.


So how do we customize it?
Create a new feature.


  1. Create a new directory anywhere eg: C:\RPSearchFeature

  2. Create a new manifest.xml in C:\RPSearchFeature with the following:
    <?xml version="1.0" encoding="utf-8"?>
    <Solution
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:xsd=
    "http://www.w3.org/2001/XMLSchema"
    SolutionId=
    "GENERATE_A_GUID"
    ResetWebServer=
    "True"
    xmlns=
    "http://schemas.microsoft.com/sharepoint/">

    <FeatureManifests>
    <FeatureManifest Location="PortalSearch\feature.xml" />
    </FeatureManifests>
    </Solution>


  3. Create "C:\RPSearchFeature\PortalSearch\feature.xml" with:
    <?xml version="1.0" encoding="utf-8"?>
    <Feature Id="7095f774-1efa-4879-b074-ff211f5559c7"
    Title="Small Portal Search"
    Description="Smaller search bar without scopes"
    Version="12.0.0.0"
    Hidden="FALSE"
    Scope="Web"
    DefaultResourceFile="core"
    xmlns="http://schemas.microsoft.com/sharepoint/">

    <ElementManifests>
    <ElementManifest Location="elements.xml"/>
    </ElementManifests>
    </Feature>


  4. Create "C:\RPSearchFeature\PortalSearch\elements.xml" with:
    <?xml version="1.0" encoding="utf-8" ?>
    <Elements xmlns="http://schemas.microsoft.com/sharepoint/">
    <Control
    Id=
    "SmallSearchInputBox"
    Sequence=
    "25"
    ControlClass=
    "Microsoft.SharePoint.Portal.WebControls.SearchBoxEx"
    ControlAssembly=
    "Microsoft.SharePoint.Portal, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c">

    <Property Name="SearchResultPageURL">/search/results.aspx</Property>
    <Property Name="FrameType">None</Property>
    <Property Name="DropDownMode">HideDD_NoScope</Property>
    <Property Name="TextBoxWidth">140</Property>
    <Property Name="ShowAdvancedSearch">false</Property>
    </Control>
    </Elements>



  5. Wrap the feature up in to a solution "SearchFeature.wsp" with cabpack


  6. Deploy the solution.


  7. Enable the feature (Scope is web, but you can change it to site if that suits you).


The instruction that will enable these parameters over the default is the sequence number. The default sequence is 99, so anything below 99 will load first. The parameters I'm using set the "SearchResultPageURL", hide the scope drop down and remove the advance search link.


2008-05-21

Application Event Log: Unable to connect publishing custom string handler for output caching.

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:
  1. Open the web.config file (D:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\ReportServer\web.config)

  2. Locate the <httpModules> node, if it's not there create it below the <httpHandlers> node.

  3. 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.


2008-04-27

{61738644-F196-11D0-9953-00C04FD919C1} Local Activation SharePoint

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



  1. Win + R C:\WINDOWS\system32\Com\comexp.msc

  2. Expand Component Services > Computers > My Comp > DCOM Config

  3. Right Click IIS WAMREG admin Service

  4. Select Properties > Security

  5. Launch and Activation Permissions "Customize" > Edit

  6. You need to grant the "user" rights.

  7. 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".


2008-04-06

SharePoint Calculated Fields Use Excel Formulas.

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

FunctionDescription
DATEReturns the serial number of a particular date
DATEVALUEConverts a date in the form of text to a serial number
DAYConverts a serial number to a day of the month
DAYS360Calculates the number of days between two dates based on a 360-day year
EDATEReturns the serial number of the date that is the indicated number of months before or after the start date
EOMONTHReturns the serial number of the last day of the month before or after a specified number of months
HOURConverts a serial number to an hour
MINUTEConverts a serial number to a minute
MONTHConverts a serial number to a month
NETWORKDAYSReturns the number of whole workdays between two dates
NOWReturns the serial number of the current date and time
SECONDConverts a serial number to a second
TIMEReturns the serial number of a particular time
TIMEVALUEConverts a time in the form of text to a serial number
TODAYReturns the serial number of today's date
WEEKDAYConverts a serial number to a day of the week
WEEKNUMConverts a serial number to a number representing where the week falls numerically with a year
WORKDAYReturns the serial number of the date before or after a specified number of workdays
YEARConverts a serial number to a year
YEARFRACReturns the year fraction representing the number of whole days between start_date and end_date

Information functions

FunctionDescription
CELLReturns information about the formatting, location, or contents of a cell
ERROR.TYPEReturns a number corresponding to an error type
INFOReturns information about the current operating environment
ISBLANKReturns TRUE if the value is blank
ISERRReturns TRUE if the value is any error value except #N/A
ISERRORReturns TRUE if the value is any error value
ISEVENReturns TRUE if the number is even
ISLOGICALReturns TRUE if the value is a logical value
ISNAReturns TRUE if the value is the #N/A error value
ISNONTEXTReturns TRUE if the value is not text
ISNUMBERReturns TRUE if the value is a number
ISODDReturns TRUE if the number is odd
ISREFReturns TRUE if the value is a reference
ISTEXTReturns TRUE if the value is text
NReturns a value converted to a number
NAReturns the error value #N/A
TYPEReturns a number indicating the data type of a value

Logical functions

FunctionDescription
ANDReturns TRUE if all of its arguments are TRUE
FALSEReturns the logical value FALSE
IFSpecifies a logical test to perform
IFERRORReturns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
NOTReverses the logic of its argument
ORReturns TRUE if any argument is TRUE
TRUEReturns the logical value TRUE

Math and trigonometry functions

FunctionDescription
ABSReturns the absolute value of a number
ACOSReturns the arccosine of a number
ACOSHReturns the inverse hyperbolic cosine of a number
ASINReturns the arcsine of a number
ASINHReturns the inverse hyperbolic sine of a number
ATANReturns the arctangent of a number
ATAN2Returns the arctangent from x- and y-coordinates
ATANHReturns the inverse hyperbolic tangent of a number
CEILINGRounds a number to the nearest integer or to the nearest multiple of significance
COMBINReturns the number of combinations for a given number of objects
COSReturns the cosine of a number
COSHReturns the hyperbolic cosine of a number
DEGREESConverts radians to degrees
EVENRounds a number up to the nearest even integer
EXPReturns e raised to the power of a given number
FACTReturns the factorial of a number
FACTDOUBLEReturns the double factorial of a number
FLOORRounds a number down, toward zero
GCDReturns the greatest common divisor
INTRounds a number down to the nearest integer
LCMReturns the least common multiple
LNReturns the natural logarithm of a number
LOGReturns the logarithm of a number to a specified base
LOG10Returns the base-10 logarithm of a number
MDETERMReturns the matrix determinant of an array
MINVERSEReturns the matrix inverse of an array
MMULTReturns the matrix product of two arrays
MODReturns the remainder from division
MROUNDReturns a number rounded to the desired multiple
MULTINOMIALReturns the multinomial of a set of numbers
ODDRounds a number up to the nearest odd integer
PIReturns the value of pi
POWERReturns the result of a number raised to a power
PRODUCTMultiplies its arguments
QUOTIENTReturns the integer portion of a division
RADIANSConverts degrees to radians
ROMANConverts an arabic numeral to roman, as text
ROUNDRounds a number to a specified number of digits
ROUNDDOWNRounds a number down, toward zero
ROUNDUPRounds a number up, away from zero
SERIESSUMReturns the sum of a power series based on the formula
SIGNReturns the sign of a number
SINReturns the sine of the given angle
SINHReturns the hyperbolic sine of a number
SQRTReturns a positive square root
SQRTPIReturns the square root of (number * pi)
SUBTOTALReturns a subtotal in a list or database
SUMAdds its arguments
SUMIFAdds the cells specified by a given criteria
SUMIFSAdds the cells in a range that meet multiple criteria
SUMPRODUCTReturns the sum of the products of corresponding array components
SUMSQReturns the sum of the squares of the arguments
SUMX2MY2Returns the sum of the difference of squares of corresponding values in two arrays
SUMX2PY2Returns the sum of the sum of squares of corresponding values in two arrays
SUMXMY2Returns the sum of squares of differences of corresponding values in two arrays
TANReturns the tangent of a number
TANHReturns the hyperbolic tangent of a number
TRUNCTruncates a number to an integer

Statistical functions

FunctionDescription
AVEDEVReturns the average of the absolute deviations of data points from their mean
AVERAGEReturns the average of its arguments
AVERAGEAReturns the average of its arguments, including numbers, text, and logical values
AVERAGEIFReturns the average (arithmetic mean) of all the cells in a range that meet a given criteria
AVERAGEIFSReturns the average (arithmetic mean) of all cells that meet multiple criteria.
BETADISTReturns the beta cumulative distribution function
BETAINVReturns the inverse of the cumulative distribution function for a specified beta distribution
BINOMDISTReturns the individual term binomial distribution probability
CHIDISTReturns the one-tailed probability of the chi-squared distribution
CHIINVReturns the inverse of the one-tailed probability of the chi-squared distribution
CHITESTReturns the test for independence
CONFIDENCEReturns the confidence interval for a population mean
CORRELReturns the correlation coefficient between two data sets
COUNTCounts how many numbers are in the list of arguments
COUNTACounts how many values are in the list of arguments
COUNTBLANKCounts the number of blank cells within a range
COUNTIFCounts the number of cells within a range that meet the given criteria
COUNTIFSCounts the number of cells within a range that meet multiple criteria
COVARReturns covariance, the average of the products of paired deviations
CRITBINOMReturns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value
DEVSQReturns the sum of squares of deviations
EXPONDISTReturns the exponential distribution
FDISTReturns the F probability distribution
FINVReturns the inverse of the F probability distribution
FISHERReturns the Fisher transformation
FISHERINVReturns the inverse of the Fisher transformation
FORECASTReturns a value along a linear trend
FREQUENCYReturns a frequency distribution as a vertical array
FTESTReturns the result of an F-test
GAMMADISTReturns the gamma distribution
GAMMAINVReturns the inverse of the gamma cumulative distribution
GAMMALNReturns the natural logarithm of the gamma function, Γ(x)
GEOMEANReturns the geometric mean
GROWTHReturns values along an exponential trend
HARMEANReturns the harmonic mean
HYPGEOMDISTReturns the hypergeometric distribution
INTERCEPTReturns the intercept of the linear regression line
KURTReturns the kurtosis of a data set
LARGEReturns the k-th largest value in a data set
LINESTReturns the parameters of a linear trend
LOGESTReturns the parameters of an exponential trend
LOGINVReturns the inverse of the lognormal distribution
LOGNORMDISTReturns the cumulative lognormal distribution
MAXReturns the maximum value in a list of arguments
MAXAReturns the maximum value in a list of arguments, including numbers, text, and logical values
MEDIANReturns the median of the given numbers
MINReturns the minimum value in a list of arguments
MINAReturns the smallest value in a list of arguments, including numbers, text, and logical values
MODEReturns the most common value in a data set
NEGBINOMDISTReturns the negative binomial distribution
NORMDISTReturns the normal cumulative distribution
NORMINVReturns the inverse of the normal cumulative distribution
NORMSDISTReturns the standard normal cumulative distribution
NORMSINVReturns the inverse of the standard normal cumulative distribution
PEARSONReturns the Pearson product moment correlation coefficient
PERCENTILEReturns the k-th percentile of values in a range
PERCENTRANKReturns the percentage rank of a value in a data set
PERMUTReturns the number of permutations for a given number of objects
POISSONReturns the Poisson distribution
PROBReturns the probability that values in a range are between two limits
QUARTILEReturns the quartile of a data set
RANKReturns the rank of a number in a list of numbers
RSQReturns the square of the Pearson product moment correlation coefficient
SKEWReturns the skewness of a distribution
SLOPEReturns the slope of the linear regression line
SMALLReturns the k-th smallest value in a data set
STANDARDIZEReturns a normalized value
STDEVEstimates standard deviation based on a sample
STDEVAEstimates standard deviation based on a sample, including numbers, text, and logical values
STDEVPCalculates standard deviation based on the entire population
STDEVPACalculates standard deviation based on the entire population, including numbers, text, and logical values
STEYXReturns the standard error of the predicted y-value for each x in the regression
TDISTReturns the Student's t-distribution
TINVReturns the inverse of the Student's t-distribution
TRENDReturns values along a linear trend
TRIMMEANReturns the mean of the interior of a data set
TTESTReturns the probability associated with a Student's t-test
VAREstimates variance based on a sample
VARAEstimates variance based on a sample, including numbers, text, and logical values
VARPCalculates variance based on the entire population
VARPACalculates variance based on the entire population, including numbers, text, and logical values
WEIBULLReturns the Weibull distribution
ZTESTReturns the one-tailed probability-value of a z-test

Text functions

FunctionDescription
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
CLEANRemoves all nonprintable characters from text
CODEReturns a numeric code for the first character in a text string
CONCATENATEJoins several text items into one text item
DOLLARConverts a number to text, using the $ (dollar) currency format
EXACTChecks to see if two text values are identical
FIND, FINDBFinds one text value within another (case-sensitive)
FIXEDFormats a number as text with a fixed number of decimals
JISChanges half-width (single-byte) English letters or katakana within a character string to full-width (double-byte) characters
LEFT, LEFTBReturns the leftmost characters from a text value
LEN, LENBReturns the number of characters in a text string
LOWERConverts text to lowercase
MID, MIDBReturns a specific number of characters from a text string starting at the position you specify
PHONETICExtracts the phonetic (furigana) characters from a text string
PROPERCapitalizes the first letter in each word of a text value
REPLACE, REPLACEBReplaces characters within text
REPTRepeats text a given number of times
RIGHT, RIGHTBReturns the rightmost characters from a text value
SEARCH, SEARCHBFinds one text value within another (not case-sensitive)
TConverts its arguments to text
TEXTFormats a number and converts it to text
TRIMRemoves spaces from text
UPPERConverts text to uppercase
VALUEConverts a text argument to a number

2008-03-23

AJAX, Web Services, WSDL and SharePoint

You may at some stage have to work for a client who's very particular about security, what web parts can be used and even whether sharepoint designer can be used.

Recently I had the opportunity to work for one such client who wanted considerable customization, but without the use of SPD, creating a custom web part was out of the question, the sharepoint servers were on another continent, and I was given contribute permissions one a subsite. Like I've said many times before I hate telling somebody something can't be done.

The customizations involved cross site permissions checking, aggregating, site meta data displaying and performing actions on new/edit forms for particular content types. The only option I could think of was talking to sharepoint's web services with IE's XMLHttpRequest object.

Some people think that AJAX is some fancy new'ish library that enables you to do clientside post backs; well it is and it isn't... it's been around since the turn of the millennium and most of the AJAX tool kits/libraries have a lot fluff for browser compatibility, and if your not working in an environment where you need to support multiple browsers I wouldn't bother with any pre-packaged library/toolkit.


All you need is to instantiate an HttpRequest object, IE7 and FF have it built in so you can call methods without creating the ActiveXObject.



So how is it done?

1.- Assign a variable to the Request object

2.- Open a connection to the web service

3.- Post some data to the service



4.- Capture the reply




5.- Do some formatting







That simple, but there's more to it...

The web service is going to expect the post in some form, so most have a WSDL (Web Service Definition Language) you can query to build your post. SharePoints web services lay it all out for you to just cut & paste pretty much.

The web service is also going to send back the data in some form, usually XML. You can use javascript to interpret the XML string, but all browsers come with an XML parser. So you load the XML from the web service in to an XML DOM object, and now you can loop through, and extract the data. Look here for available methods: http://msdn2.microsoft.com/en-us/library/ms757828(VS.85).aspx

You're probably here because you want code. Here's fireWS.zip a web page you can run on your local machine that will query the WSDL, or you can put the source in a content query web part and run it from any page in SharePoint. Looking at its source should give you a good idea of how to query the WSDL, loop through nodes and construct the XML post.

Below the break down of the page and bear in mind that this is a demo and the code can be refactored... Originally it was a Javascript class, so all you would do was call new fireWS(constuc webservice), but I've split it out to individual functions and used global variables instead of properties.




Start off by creating a couple of global variables to share among the functions:

var sXmlDomVer='MSXML2.DOMDocument.3.0',oWsdlXml, oSoapXml, oSoapBody, oSoapOp;

The below instantiates a request object, and takes the web service url and the xml string to post. If you want to support other browsers add some checks here.

function fireWS(sWSUrl, sXml){
var oXmlHttp=false;
if(!oXmlHttp && typeof XMLHttpRequest!='undefined') {
try {oXmlHttp = new XMLHttpRequest();}
catch(e){oXmlHttp=false;}
}
if(!oXmlHttp && window.createRequest) {
try {oXmlHttp = window.createRequest();}
catch(e){oXmlHttp=false;}
}
if(!oXmlHttp){
try{oXmlHttp = window.createRequest();}
catch(e){oXmlHttp = new ActiveXObject('Microsoft.XMLHTTP');}
}
if(!oXmlHttp){alert('Sorry, you\'re browser doesn\'t support this control')}
if(!sXml){method='GET'}
else{method='POST'}
oXmlHttp.open(method, sWSUrl, false);
oXmlHttp.setRequestHeader('Content-Type','text/xml; charset=utf-8');
oXmlHttp.send(sXml);
document.getElementById('headers').innerText = oXmlHttp.statusText +' '+ oXmlHttp.getAllResponseHeaders();
if (oXmlHttp.readyState==4){
return oXmlHttp.responseText;
}
}


This function parses an XML string and returns an XMLDOM object. If you want to support other browsers add some checks here.

function xmlParse(str){
oXmlDom = new ActiveXObject(sXmlDomVer)
oXmlDom.async = false;
oXmlDom.loadXML(str);
if (oXmlDom.parseError.errorCode != 0){
var myErr = oXmlDom.parseError;
if(document.getElementById('debug').checked){alert('Error parsing xml ' + myErr.reason +'\n\n'+ str)}
}
return oXmlDom;
}


This function is used to search for a paraticular node.

function getMyNode(obj, nodeName, attribute, attributeVal){
try{objXml = obj.getElementsByTagName(nodeName);}
catch(e){}
if(objXml.length == 0){objXml = obj.getElementsByTagName(nodeName.substring(nodeName.indexOf(':')+1,nodeName.length));}
if(objXml.length == 0){return false}
if(attribute != null && objXml){
for(var i=0; i<objXml.length; i++){if(objXml[i].getAttribute(attribute) == attributeVal){return objXml[i];}}
return false;
}
return objXml;
}


This function is used to add a node to another node. Looks like I wanted to be able to define the position to add it in, then I changed my mind.

function addMyNode(oSoap, type, nodeName, parentNode, v, ns){
var newNode = oSoap.createNode(type, nodeName, ns);
try{
switch(type){
case 1:
if(v){newNode.text = v};
return parentNode.appendChild(newNode);
case 2:
if(v){newNode.text = v};
return parentNode.attributes.setNamedItem(newNode);
default:
return parentNode.appendChild(newNode);
}
}
catch(e){
if(document.getElementById('debug').checked){alert('Error creating node "'+ nodeName +'"\ntype '+ type +'\nv '+ v +'\n\n'+ e.description)}
}
}


This removes childnodes so you can run a new query.

function removeChildNodes(oXml){
for(var i=0;i<oXml.childNodes.length;i++){
oXml.removeChild(oXml.childNodes.item(i));
}
}


This is for adding table rows to the SOAP properties table.

function renderPropsTbl(sProp,oProp){
if(document.getElementById('tblConnProps')!=null){
var oTbl = document.getElementById('tblConnProps');
}
else{
var oTbl = document.createElement('table');
oTbl.id = 'tblConnProps';
document.getElementById('divConnProps').appendChild(oTbl);
}
var newRow = oTbl.insertRow(oTbl.rows.length);
newRow.id = newRow.uniqueID;
var newCell;
newCell = newRow.insertCell(0);
newCell.id = newCell.uniqueID;
newCell.innerText = sProp;
newCell = newRow.insertCell(1);
newCell.id = newCell.uniqueID;
newCell.appendChild(oProp);
}


This is for removing table rows from the SOAP properties table.

function removePropsTbl(iTR){
var tblConnProps = document.getElementById('tblConnProps');
for(var i=tblConnProps.rows.length-1;i>=iTR; i--){tblConnProps.deleteRow(i);}
}


This for finding the bindings in the WSDL, I should really figure out an intelligent way of finding out the namspac, but instead I just use wsdl.

function getWdslBinding(url){
var oProps = document.getElementById('divConnProps');
oProps.innerHTML = '';
oWsdlXml = xmlParse(fireWS(url +'?WSDL'));
var oWsdlBindings = getMyNode(oWsdlXml, 'wsdl:binding');
if(oWsdlBindings){
var oBindings = document.createElement('select');
oBindings.id = 'selBinding';
oBindings.attachEvent('onchange',getWdslOperations);
for(var i=0; i<oWsdlBindings.length; i++){
if(oWsdlBindings[i].attributes.length > 0){
var newopt = document.createElement('option');
newopt.value = oWsdlBindings[i].getAttribute('name');
newopt.text = oWsdlBindings[i].getAttribute('name');
oBindings.options.add(newopt);
}
}
renderPropsTbl('Binding Port:',oBindings);
getWdslOperations();
}
}


This needs a bit of work but it finds the operations for the selected binding, and it sorts the operations for aestetics.

function getWdslOperations(){
try{removePropsTbl(1)}
catch(e){}
var oWsdlOps = getMyNode(oWsdlXml,'wsdl:binding','name',document.getElementById('selBinding').value);
if(getMyNode(oWsdlOps,'soap12')){sSoapVer = 'soap12';sSoapNS = 'http://www.w3.org/2003/05/soap-envelope';}
else{sSoapVer = 'soap';sSoapNS = 'http://schemas.xmlsoap.org/soap/envelope/';}
oSoapXml = xmlParse('<' + sSoapVer +':Envelope xmlns:'+ sSoapVer +'="'+ sSoapNS +'"/>');
oSoapBody = addMyNode(oSoapXml, 1, sSoapVer +':Body', oSoapXml.documentElement, '', sSoapNS);
oWsdlOps = getMyNode(oWsdlOps,'wsdl:operation');
if(oWsdlOps){
var oOps = document.createElement('select');
oOps.id = 'selOps';
oOps.attachEvent('onchange',getWdslOpElements);
arrWsdlOps = new Array();
for(var i=0; i<oWsdlOps.length; i++){
if(oWsdlOps[i].attributes.length > 0){
arrWsdlOps.push(oWsdlOps[i].getAttribute('name'));
}
}
arrWsdlOps.sort();
for(var i=0; i<arrWsdlOps.length; i++){
var newopt = document.createElement('option');
newopt.value = arrWsdlOps[i]; newopt.text = arrWsdlOps[i];
oOps.options.add(newopt);
}
renderPropsTbl('Operation:',oOps);
getWdslOpElements();
}
}


This gets the elements for an operations and adds an input fields.

function getWdslOpElements(){
try{
removePropsTbl(2);
document.getElementById('result').innerText = '';
oSoapBody.removeChild(oSoapOp);
}
catch(e){}
var sOp = document.getElementById('selOps').value;
oWsdlOpElem = getMyNode(oWsdlXml,'s:element','name',sOp);
oSoapOp = addMyNode(oSoapXml, 1, sOp, oSoapBody, '', oWsdlXml.documentElement.getAttribute('targetNamespace'));
if(oWsdlOpElem.hasChildNodes()){
oWsdlOpParams = getMyNode(oWsdlOpElem,'s:element');
for(var i=0; i<oWsdlOpParams.length; i++){
if(oWsdlOpParams[i].attributes.length > 0){
var sOpParamType = oWsdlOpParams[i].getAttribute('type'), sOpParamName = oWsdlOpParams[i].getAttribute('name'), iInputType;
if(oWsdlOpParams[i].hasChildNodes()){iInputType = 1;}
else{
if(sOpParamType.indexOf('tns:')>-1){
var oTNSOpParams = getMyNode(oWsdlXml, 's:simpleType', 'name', sOpParamType.replace('tns:',''));
if(oTNSOpParams){
oTNSOpParams = getMyNode(oTNSOpParams, 's:enumeration');
iInputType = 2;
}
else{iInputType = 1;}
}
else{iInputType = 0;}
}
switch(iInputType){
case 1:
var oOpPN = document.createElement('textarea');
oOpPN.cols = 60;oOpPN.rows = 3;
sOpParamType = 'XML Schema';
break;
case 2:
var oOpPN = document.createElement('select');
for(var iT=0; iT<oTNSOpParams.length; iT++){
if(oTNSOpParams[iT].attributes.length > 0){
var newopt = document.createElement('option');
newopt.value = oTNSOpParams[iT].getAttribute('value');
newopt.text = oTNSOpParams[iT].getAttribute('value');
oOpPN.options.add(newopt);
}
}
break;
default:
var oOpPN = document.createElement('input');
oOpPN.size = 80;
}
oOpPN.id = 'soapp'+ sOpParamName;
renderPropsTbl(sOpParamName +' ('+ sOpParamType +'):',oOpPN);
var oOpParam = addMyNode(oSoapXml, 1, sOpParamName, oSoapOp, '',oWsdlXml.documentElement.getAttribute('targetNamespace'));
}
}
renderPropsTbl('',document.createElement('<INPUT TYPE="Button" NAME="buildSoap" VALUE="Fire Soap" onclick="buildSoap();" class="btn">'));
}
}


This loops through the form and builds the SOAP post.

function buildSoap(){
var soapFlds = document.getElementById('tblConnProps').getElementsByTagName('*');
for(var i=0;i<soapFlds.length;i++){
if(soapFlds[i].value != '' && soapFlds[i].id.indexOf('soapp')>-1){
if(soapFlds[i].type == 'textarea'){
try{
var oFldXml = xmlParse(soapFlds[i].value);
oFldXml.namespaceURI = oOpParam.namespaceURI;
var oOpParam = oSoapOp.selectSingleNode(soapFlds[i].id.replace('soapp',''));
removeChildNodes(oOpParam);
oOpParam.appendChild(oFldXml.documentElement);
}
catch(e){
try{
var oOpParam = oSoapOp.selectSingleNode(soapFlds[i].id.replace('soapp',''));
var oXmlDom = new ActiveXObject(sXmlDomVer)
oXmlDom.async = false;
oXmlDom.loadXML('<'+ soapFlds[i].id.replace('soapp','') +' xmlns="'+ oOpParam.namespaceURI +'">'+ soapFlds[i].value +'</'+ soapFlds[i].id.replace('soapp','') +'>');
oSoapOp.replaceChild(oXmlDom.documentElement, oOpParam);
}
catch(e){alert('Error in buildSoap(): '+ e.description);}
}
}
else{
try{
var oOpParam = oSoapOp.selectSingleNode(soapFlds[i].id.replace('soapp',''));
oOpParam.text = soapFlds[i].value;
}
catch(e){}
}
}
}
fireSoap(oSoapXml.xml,document.getElementById('result'))
}


The rest just renders the output.

function fireSoap(soap, oResDisp){
var sUrl = document.getElementById('url').value + document.getElementById('asmx').options[document.getElementById('asmx').selectedIndex].text;
var sRtnXml = fireWS(sUrl, soap);
document.getElementById('soap').innerText = oSoapXml.xml.replace(/></gi,'>\n<');;
//document.getElementById('soap').parentElement.style.display = 'block';
getMethodXml(sUrl);
if(sRtnXml.indexOf('z:row')>-1){
var row = xmlParse(sRtnXml).getElementsByTagName('z:row');
var sAtt = '<table border="1">';
if(row[0].attributes.length > 0){
sAtt += '<tr>';
for(var i=0; i < row[0].attributes.length; i++){sAtt += '<th>'+ row[0].attributes[i].name +'</th>';}
sAtt += '</tr>';
}
for(var i=0; i<row.length; i++){
if(row[i].attributes.length > 0){
sAtt += '<tr>';
for(var ii=0; ii < row[i].attributes.length; ii++){sAtt += '<td>'+ row[i].attributes[ii].value +'</td>';}
sAtt += '</tr>';
}
}
sAtt += '</table>';
oResDisp.innerHTML = sAtt;
}
oResDisp.innerText = sRtnXml.replace(/></gi,'>\n<');oResDisp.parentElement.style.display = 'block';
}


Once you start playing around with this you'll soon realize it's power and flexibility, and you could even save yourself the effort of registering more dll's on your server. The holly grail would be to create Http End Points for all your SQL data.

If "Web page security validation" is enable you will not be able to do any add/edit/delete actions, so you're limited to listing. This is also the case with serverside web service calls unless you explicitly "allowunsafeupdates".

This will only work if you download the html file (fireWS.zip) and run it locally with reduced permissions OR reduce your internet zone IE permissions and run it online fireWS.htm.

Usage Example:
  1. Enter service URL: http://tb.ohchr.org/
  2. Choose web service eg: /_vti_bin/Lists.asmx
  3. Operation: GetListCollection
  4. Fire Soap
  5. You should now see the reply...
Now you have the list GUIDs so you can do some more stuff.

Like:
  1. Operation: GetList
  2. listName: {17B611E5-4BF5-48C0-8926-E614A8126197}
  3. Fire Soap


Cool, we've got all the field names.

Let's try a list query...
  1. Operation: GetListItems
  2. listName: {17B611E5-4BF5-48C0-8926-E614A8126197}
  3. query: <Query><Where><Contains><FieldRef Name="ConventionCode" /><Value Type="Text">C</Value></Contains></Where></Query>
  4. viewFields: <ViewFields><FieldRef Name="ID" /><FieldRef Name="Title" /><FieldRef Name="ConventionCode" /><FieldRef Name="ConventionOrder" /><FieldRef Name="Convention" /><FieldRef Name="Author" /></ViewFields>
  5. rowLimit: 7
  6. Fire Soap

As you can see it's a easy... I haven't tried all the web services as this is just a curiosity for me.



Web services: http://msdn2.microsoft.com/en-us/library/ms445292.aspx