Wednesday 23 December 2009

Steps for Setting up an SAP Enterprise Service to be consumed from an external application such as SharePoint

  1. Go to SAP GUI
  2. Run transaction /nsproxy. This will show what services are available to expose as endpoints, including your own custom created services (otherwise you have to download or create it).
  3. In transaction /nsproxy, search for your service by clicking the binoculars icon e.g. the EmployeeBasicDataByEmployeeQueryResponse_In Service in the Enterprise Service repository. Alternatively you can browse for the Service you are looking for (as pictured above).
  4. Note that there are no service endpoints defined if you go to the WSDL tab when viewing the service details.
  5. Go to transaction /nsoamanager in SAP GUI
  6. Go to the “Application And Scenario Communication” Tab ->; “Single Service Administration”.
  7. Search for the name of the service as found in sproxy (e.g. EmployeeBasicDataByEmployeeQueryResponse_In)
  8. Select the Service in the search results and click “Apply Selection”. Notice that there are no Bindings Listed if you click on “Display Selected Binding’s WSDL”
  9. Click the "Configurations" tab and Click the "Create Service" button. Enter a service name e.g. EmployeeBasicDataByEmployeeQueryResponse_In_Service, and a description

  10. Also enter a binding name e.g. EmployeeBasicDataByEmployeeQueryResponse_In_Binding, and Click “Apply Settings”
  11. A new set of tabs should appear at the bottom of your screen. Enter details for the provider security
  12. Click “Save”
  13. The service is not activated yet. Even if you try and open the service with SOAP UI at this stage, you will just get errors such as "“Logon Error Message. What has happened? URL http://servername:serverport/sap/bc/srt/xip/sap/ecc_employeebasicdbyemployeeqr/120/employeebasicdatabyemployeequery/employeebasicdatabyemployeequeryresponse_in_binding call was terminated because the corresponding service is not available."
  14. To activate the service, go to transaction /nsicf in SAP GUI
  15. Search for the service e.g. put “EMPLOYEE*” in the service name. This should find your service e.g. “EmployeeBasicDataByEmployeeQuery”
  16. Right click on the service at the bottom level and click Activate, and click on the “Yes” button (yes it is odd that there are 2 – I don’t know the difference).
  17. You should be able to call the web service through SOAP UI or any other web services client or tool (e.g. Visual Studio .NET). Make sure you enter a username and password under the “Auth” tab of the request (if using SOAP UI 3.0).
  18. DONE!

Monday 21 December 2009

CX_SY_CONVERSION_NO_DATE_TIME:XSLT exception. Value DD/MM/YYYY is not a valid date in accord with the XML format for ABAP

If you get the following error when calling a SAP BAPI via a web service from .NET:

CX_SY_CONVERSION_NO_DATE_TIME:XSLT exception.An error occurred when deserializing in the simple transformation program /1BCDWB/WSS2B813A2D0E86D9A4A67.Value 22/12/2009 1:00:00 PM is not a valid date in accord with the XML format for ABAP

Then the date type may not have been inferred correctly if you are using the .NET XSD.exe tool (e.g. if the field has a default in an SAP BAPI, it may not be able to infer the correct date type and just treats the date as a string.).

To fix this problem you can simply do a date conversion to the correct format (e.g. via the DateTime.ToString("yyyy-MM-dd"). The format it is expecting in ABAP is yyyy-MM-dd e.g. 2009-12-30.

Thursday 17 December 2009

CX_ST_MATCH_ELEMENT:XSLT exception.System expected element 'MyTableName' SOAP Fault when calling a SAP BAPI remotely via Web Service

If you receive the following error when calling a BAPI in SAP (e.g. from a .NET WCF client or using SOAPUI):

CX_ST_MATCH_ELEMENT:XSLT exception.System expected element 'MyTableName'

Then you have not initialized your variables correctly. In SOAPUI (http://www.soapui.org/), you cannot remove the elements even though instances are marked as "Zero or many". Instead, you have to pad it with empty elements. You cannot have zero elements in the table (even though the XSD seemingly permits it) otherwise the SAP Web service will spit the dummy.





By the same token, you must initialize all your arrays/lists in .NET:
e.g.

try
{
CostObjectSearchService.Z_Bapi_Cost_Object_FindClient client = new CostObjectSearchService.Z_Bapi_Cost_Object_FindClient();
CostObjectSearchService.ZBapiCostObjectFind request = new CostObjectSearchService.ZBapiCostObjectFind();

request.ICompanyCode = "2000";
request.ISpendType = "P";
request.ICostObject = costObjectMatchString;

//Return Object initialization (for Asset, Cost Centre or Work Breakdown Structure (WBS)
// - otherwise returns error from SAP Web Service
// XSLT CX_ST_MATCH_ELEMENT:XSLT exception.System expected element 'MyTableName'
request.TTypeAAsset = new CostObjectSearchService.Zfanla[0];
request.TTypeOkhCostCentre = new CostObjectSearchService.Zfcsks[0];
request.TTypePWbs = new CostObjectSearchService.Zfprps[0];
CostObjectSearchService.ZBapiCostObjectFindResponse response = client.ZBapiCostObjectFind(request);

Wednesday 16 December 2009

SAP ABAP and Case sensitivity of the LIKE clause Versus the CS (Contains String) Keyword

I had an issue today with a custom BAPI (using SAP's proprietary language ABAP) that would just NOT return values that I knew were in the PRPS.POST1 field. It turned out that it was a case sensitivity problem - as the LIKE clause in SAP is case sensitive. Typically users don't care about case at all and expect that a search for "Test" will return all upper case and lower case variations (e.g. Test, TEst, TEST, etc).

Unfortunately there is no equivalent of the UPPER() statement in standard SQL Syntax. There are 2 workarounds that I'm aware of:
  1. If you are lucky, there is a field with the same name defined for text fields which has all upper case characters and suffixed with "U". For example, the upper case equivalent of POST1 is the POSTU field. You can then do case insensitive searches on the field by forcing your input parameter to upper case.
  2. Use the CS (Contains String) Function instead of the LIKE clause within your select.....endselect

See below for details of a real-world example of how to use the CS clause for case-insensitive searches:

The below custom BAPI function accepts a spend type (e.g. Capital Expenditure (CAPEX), Operational Expenditure (OPEX)) company code parameter and a wildcard search field and then populates different objects based on the spend type. For example, a Spend type of OPEX will return a list of Cost Centres into the T_TYPE_OKH_COST_CENTRE Table as an output, a spend type of Asset will return a list of assets into the the T_TYPE_A_ASSET structure in the output.

Sample Search BAPI Code (ABAP)


FUNCTION Z_BAPI_COST_OBJECT_FIND.
*"----------------------------------------------------------------------
*"*"Local Interface:
*" IMPORTING
*" VALUE(I_SPEND_TYPE) TYPE EKPO-KNTTP
*" VALUE(I_COMPANY_CODE) TYPE PRPS-PBUKR
*" VALUE(I_COST_OBJECT) TYPE CHAR_50
*" TABLES
*" T_TYPE_P_WBS STRUCTURE ZFPRPS
*" T_TYPE_OKH_COST_CENTRE STRUCTURE ZFCSKS
*" T_TYPE_A_ASSET STRUCTURE ZFANLA
*" EXCEPTIONS
*" EXCEPTION
*"----------------------------------------------------------------------

tables: prps, csks, anla.
data: projdesc(50) TYPE C.

*The below statements use a CS statement rather than the like clause of SQL
*because LIKE is case sensitive in SAP - unfortunately there is no equivalent
* of UPPER() same as in Native SQL Server.

* Get Cost Object of type WBS
* and POSKI LIKE I_COST_OBJECT."
* We need to do a cast
if I_SPEND_TYPE = 'P'.
select * from PRPS
where BELKZ = 'X'
and PBUKR = I_COMPANY_CODE.
* and ( POSID LIKE I_COST_OBJECT or POST1 LIKE I_COST_OBJECT ).
move PRPS-POST1 to projdesc.

if PRPS-TADAT IS INITIAL and
( PRPS-POSID CS I_COST_OBJECT
or projdesc CS I_COST_OBJECT ).
move PRPS-PSPNR to T_TYPE_P_WBS-PSPNR.
move PRPS-POSID to T_TYPE_P_WBS-POSID.
move PRPS-POST1 to T_TYPE_P_WBS-POST1.
append T_TYPE_P_WBS.
endif.
endselect.

* Get Cost Object of type Cost Centre for Retail, Office and Industrial
elseif I_SPEND_TYPE = 'O'.
select * from CSKS
where BUKRS = I_COMPANY_CODE
and ( ABTEI = 'RETAIL'
or ABTEI = 'OFFICE'
or ABTEI = 'INDUSTRIAL'
or ABTEI = '12' ).

if CSKS-NAME1 CS I_COST_OBJECT
or CSKS-KOSTL CS I_COST_OBJECT.

move CSKS-KOKRS to T_TYPE_OKH_COST_CENTRE-KOKRS.
move CSKS-KOSTL to T_TYPE_OKH_COST_CENTRE-KOSTL.
move CSKS-DATBI to T_TYPE_OKH_COST_CENTRE-DATBI.

append T_TYPE_OKH_COST_CENTRE.
endif.

endselect.

* Get Cost Object of type Cost Centre for Marketing
elseif I_SPEND_TYPE = 'K'.
select * from CSKS
where BUKRS = I_COMPANY_CODE
* and ABTEI = 'MARKETING'.
and ABTEI = '12'.

*Conditionally
if CSKS-NAME1 CS I_COST_OBJECT
or CSKS-KOSTL CS I_COST_OBJECT.
move CSKS-KOKRS to T_TYPE_OKH_COST_CENTRE-KOKRS.
move CSKS-KOSTL to T_TYPE_OKH_COST_CENTRE-KOSTL.
move CSKS-DATBI to T_TYPE_OKH_COST_CENTRE-DATBI.
append T_TYPE_OKH_COST_CENTRE.
endif.
endselect.

* Get Cost Object of type Cost Centre for Head Office
elseif I_SPEND_TYPE = 'H'.
select * from CSKS
where BUKRS = I_COMPANY_CODE
and ( ABTEI = 'CORPORATE'
or ABTEI = '12' ).

move CSKS-KOKRS to T_TYPE_OKH_COST_CENTRE-KOKRS.
move CSKS-KOSTL to T_TYPE_OKH_COST_CENTRE-KOSTL.
move CSKS-DATBI to T_TYPE_OKH_COST_CENTRE-DATBI.

append T_TYPE_OKH_COST_CENTRE.

endselect.

* Get Cost Object of type Asset
elseif I_SPEND_TYPE = 'A'.
select * from ANLA
where BUKRS = I_COMPANY_CODE
and ( ANLKL = '13000000'
or ANLKL = '14000000' ).

if ANLA-ANLN1 CS I_COST_OBJECT or
ANLA-TXT50 CS I_COST_OBJECT.
move ANLA-BUKRS to T_TYPE_A_ASSET-BUKRS.
move ANLA-ANLN1 to T_TYPE_A_ASSET-ANLN1.
move ANLA-ANLN2 to T_TYPE_A_ASSET-ANLN2.

append T_TYPE_A_ASSET.
endif.
endselect.

endif.

ENDFUNCTION.


Additional Notes

1) Spaces DO matter around parentheses - the following will NOT work:

INCORRECT:
select * from PRPS
where BELKZ = 'X'
and PBUKR = I_COMPANY_CODE
and (POSKI LIKE I_COST_OBJECT or POST1 = I_COST_OBJECT).
endselect.

CORRECT:
The following is correct syntax
select * from PRPS
where BELKZ = 'X'
and PBUKR = I_COMPANY_CODE
and ( POSKI LIKE I_COST_OBJECT or POST1 = I_COST_OBJECT ).
endselect.



2) All statements should end with a period. (ie similar to the use of the semicolon as a line delimiter in C#)

Thursday 10 December 2009

InfoPath 2007 - Conditional values in InfoPath using XPath

I had a question today regarding Infopath and making conditional statements in Xpath:

Question

Hi Guys,
I was wondering if any of you have tried doing an if then else using xpath.
What I am trying to do is use a formula on an infopath form control.
This is what I want
If(field1 = “x”)
Then “1”
Else “2”
Ive had a look at some forums and you can do this with xpath2.0 but im using infopath2007 which I think uses xpath1.0
The work around seems to be something like this which I cant seem to get working either.
Concat(substring("1", 1, field1=”x” * string-length("1")), substring("2", 1, not(field1=”x”) * string-length("2")))
Any ideas would be great.
thanks


Answer

There are 2 options for this:
  1. You can use Conditional Statements within Rules e.g. on a text box to set the value conditionally in another field (much simpler than Xpath workarounds)
  2. You can use Xpath to give conditional results (effectively an "if" or "iif"). To implement the example in the question above, you could put this into an expression box (make sure "Xpath(Advanced)" is checked on):

    concat(substring("1", 1, (my:FieldA = "x") * string-length("1")), substring("2", 1, (not(my:FieldA = "x")) * string-length("2")))

The problem with the Xpath in the original question was that the parentheses were not in the correct positions. For more info on option 2, you can see http://blogs.msdn.com/infopath/archive/2006/11/27/conditional-default-values.aspx

Wednesday 2 December 2009

"The service implementation selected is invalid." when attempting to create a web service in SAP Netweaver Developer Studio

I'm currently trying to get the SAP universal worklist APIs to provide information to SharePoint via a web service (to ensure there is only a single task list for users to see). To this end, I needed to create a web service hosted on the SAP BPM server.

When attempting to create a Java Web service (e.g. a bottom up service, based on a java bean service implementation) in Netweaver Developer Studio (NWDS), you might receive the following cryptic error message:


"The service implementation selected is invalid."

It is as clear as mud - but the issue here is that the class name of the service implementation is lower case. To resolve the issue, just refactor the class with a right click on the offending java file and change the first letter to upper case. Quite painful if you ask me - they really need a better explanation than that in their user interface!


Fix it with a refactor:


Thursday 26 November 2009

Exposing the SAP Netweaver CE 7.1 BPM Universal Worklist (UWL) through an iView and the Object Tag Viewer Web Part in SharePoint 2007

SAP iViews are one of many mechanisms by which you can surface SAP content into a SharePoint site. This post describes how you can show the SAP Universal Worklist (UWL) in SharePoint through a custom web part called the Object Tag Page Viewer.

As desribed in an MS white paper ( http://download.microsoft.com/download/B/F/2/BF2C3AAD-BC64-496C-B3BC-72ADD9617E2C/iView%20Integration%20with%20Microsoft%20Office%20SharePoint%20Server.pdf):

"There are scenarios where an end user would require logging in to SAP GUI or SAP Portal to perform some of their commonly performed operations. It would be convenient if these operations were performed from a portal like Microsoft Office SharePoint Server Portal (MOSS) which an end user might be using more frequently. In such scenarios the best point of integration is to display SAP iViews or Web Dynpro iViews in MOSS. MOSS out of the box provides two web parts that can be used for displaying pages from portals/web sites outside MOSS. These are Page Viewer Web Part and iView Web Part. When configuring SAP iView or Web Dynpro iViews, these web parts can go in to an infinite refresh loop with some iViews. In order to overcome this issue, there is a need to develop a custom web part that would display the SAP iView. The Object Tag Page Viewer Web Part is an approach that allows MOSS portals to display these iViews. "

While the document above covers the creation of the custom web part, there are a few issues with it:

  • a) It doesn't cover how you set up or consume something like the SAP UWL (The UWL is basically the same thing as the Task list in SharePoint)
  • b) The code in the whitepaper is not complete.

The Object Tag Page Viewer Web Part (Code)

using System;
using System.Runtime.InteropServices;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Serialization;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;
using System.ComponentModel;

namespace Microsoft
{
///
/// Created as per pdf Document "SAP iView Integration with Microsoft Office SharePoint 2007"
///

[Guid("a9c26185-1970-4f29-b45c-edad343938c1")]
public class ObjectTagPageViewer : System.Web.UI.WebControls.WebParts.WebPart
{
const int heightWidth = 150;

#region Properties
/// /// The URL to display in the Object Tag ///
private string pageUrl = "http://";

[Personalizable(), WebBrowsable(true), Browsable(true), Category("Custom Property"), DefaultValue("http://"), WebPartStorage(Storage.Personal),
FriendlyName("Page Url"), Description("Default page URL such as http://www.live.com")]
public string PageUrl { get { return pageUrl; } set { pageUrl = value; } }

#endregion

///
///
///

///
protected override void OnPreRender(EventArgs e)
{
if (base.Height.IsEmpty)
{
base.Height = heightWidth;
}
base.OnPreRender(e);
}

protected override void Render(HtmlTextWriter output)
{
if (String.Compare(pageUrl, "http://") == 0 String.IsNullOrEmpty(pageUrl))
{
output.WriteBreak();
output.WriteLine(@"To link to content, <a id="'ObjectTagPageViewer_" href="javascript:MSOTlPn_ShowToolPane2Wrapper("> output.WriteBreak();
}
else
{
if (Uri.IsWellFormedUriString(pageUrl, UriKind.Absolute))
{
string objectTagID = "outputObject_" + base.UniqueID;
output.AddAttribute(HtmlTextWriterAttribute.Id, objectTagID, false);
output.AddAttribute(HtmlTextWriterAttribute.Name, objectTagID, false);
output.AddAttribute(HtmlTextWriterAttribute.Width, "100%", false);
output.AddAttribute(HtmlTextWriterAttribute.Height, "100%", false);
output.AddAttribute(HtmlTextWriterAttribute.Type, "text/html", false);
output.AddAttribute("data", pageUrl, false);
output.RenderBeginTag(HtmlTextWriterTag.Object);
output.RenderEndTag();
}
else
{
output.WriteBreak();
output.WriteLine("Please enter a valid Absolute Page Url ");
output.WriteBreak();

}

}
}

public ObjectTagPageViewer()
{
}

}
}

Setup of iView Web Part to show the SAP UWL

  1. Go to http://insert_ce_server_name_here/irj/portal and log in.
  2. Go to Content Administration -> Portal Content -> Content Provided by SAP -> End User Content -> Standard Portal Users -> iViews -> com.sap.netweaver.bc.uwl.iviews -> Universal Worklist
  3. Right Click - > Preview
  4. Use the url that shows in the new preview window and put it into an Object Tag Page Viewer Web Part that you’ve added to a SharePoint Web Part page (as per the Microsoft Whitepaper)
  5. Et voila! SAP UWL now shows in SharePoint. Clicking on tasks opens them up into another window - from where the user can action the task.
  6. Tasks now open up in a new window for actioning:

Thursday 19 November 2009

Deploying SharePoint 2007 Custom Content Types using Inheritance with a SharePoint Feature

I had the following question sent to me this morning regarding the creation of custom content types:

"I need to create custom content types in MOSS 2007 and want to know the best way to do it. I want to achive the inheritance such as: My Core Content Type --- > Generic Content Type -- > News Content Type"

You will find that Microsoft themselves use a feature as the mechanism to deploy all of the out of the box (OOB) content types. You can see this file at:
"C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\FEATURES\ctypes\ctypeswss.xml".

In fact, the feature.xml file that Microsoft uses is in the same directory. This uses a standard schema, which can be found on MSDN here http://msdn.microsoft.com/en-us/library/ms463449.aspx

Which brings me to the content type inheritance part of the question.

The critical part to understand when creating the feature is that inheritance in content types is defined by the Ctype ID. If you go to the Content Type Gallery (for your site/site collection) and create a new content type, you can see this ctype in the URL when you hover over the name of your new content type in the gallery list. For example, when I click on the content type I just created, the link was to this location:
http://servername/_layouts/ManageContentType.aspx?ctype=0x0100E00AAE6F9A33274BBC67EACB89D61FFA&Source=%2F%5Flayouts%2Fmngctype%2Easpx

When I create a new content type that inherits from the other content type, then I just append a 01 to it and it inherits from the parent type. i.e. the Content Type Id is
ctype=0x0100E00AAE6F9A33274BBC67EACB89D61FFA01
A "grandchild" of the parent type would be
ctype=0x0100E00AAE6F9A33274BBC67EACB89D61FFA0101
and so on.



In summary, to answer the question:

  1. Create a feature to deploy your content types (using WSPBuilder or your preferred tool).
  2. In the elements file, specify your content type id (ctype) and fields which should be displayed.
  3. Give the wsp file to SharePoint administrators to Deploy the file.
  4. Done!
Additional Notes/References
  1. Another option which allows more control and full programmatic flexibility would be to create a feature receiver which then allows you to run code against the SharePoint object model to perform additional checks in the OnFeatureActivated/OnFeatureInstalled event
  2. As a best practice, Content types should not be modified by a feature once they have been installed - as manual changes to the content type may be overwritten.
  3. One of the most illuminating articles on this topic can be found here:
    http://blogs.msdn.com/eigilm/archive/2007/09/04/inherit-content-types.aspx

Tuesday 17 November 2009

Where to find the SAP Universal Worklist (UWL) API jar files (bc.uwl.service.api_api.jar, bc.uwl.service.api_core.jar, com.sap.security.api.jar)

Apparently with Netweaver Developer Studio (NWDS) 7.0, the UWL API files for accessing the Universal Worklist in SAP were included with the install. However, in NWDS 7.1, it appears that the following files are no longer included:

  • bc.uwl.service.api_api.jar
  • bc.uwl.service.api_core.jar
  • com.sap.security.api.jar

To obtain these files, you need to go to the SAP patches site at http://service.sap.com/patches.

Then navigate to:

"Support Packages and Patches - Entry by Application Group" ->
"SAP Netweaver" ->
"SAP Netweaver" (yes the same menu item twice) ->
"SAP EHP1 FOR SAP NW CE 7.1" ->
"Entry by Component" ->
"BPM Portal Integration" ->
"UWL COLL PROCESS ENGINE 7.11" ->
Download one of the patch files e.g. UWLJWF03_0-20002656.SCA


Then once downloaded, you can just import the SCA file into your project through Netweaver Developer studio or you can rename the file to a zip and extract the jar files you need.

Thursday 12 November 2009

WCF Fix-The HTTP request is unauthorized with client authentication scheme 'Negotiate'. The authentication header received from the server was 'NTLM'.

I thought I'd done a post on this error previously, but I double checked google and I obviously hadn't.

The Problem
When calling WSS / SharePoint web services (such as Lists.asmx) via WCF, you will normally get this error if you leave the settings as configured by the “Add Service Reference Wizard” :

“The HTTP request is unauthorized with client authentication scheme 'Negotiate'. The authentication header received from the server was 'NTLM'.”

The Solution

You must specify a non-anonymous impersonation level for your ClientCredentials. Just specifying a username and password for your WCF Service reference's ClientCredentials.UserName.UserName and ClientCredentials.UserName.Password is not sufficient to resolve the problem.

In particular (When SharePoint server is on different domain):

ServiceReference1.ListsSoapClient client = new ServiceReference1.ListsSoapClient();
client.ClientCredentials.Windows.ClientCredential = new System.Net.NetworkCredential("username", "password", "domain");
client.ClientCredentials.Windows.AllowedImpersonationLevel = TokenImpersonationLevel.Identification;
client.GetListCollection();


Of course, when on same domain, don’t have to pass in the Windows.ClientCredential information. You can also set the above values in app.config configuration elements rather than code, but I won't cover that here.

You can use (with descending levels of security):
System.Security.Principal.TokenImpersonationLevel.Identification
System.Security.Principal.TokenImpersonationLevel.Impersonation
System.Security.Principal.TokenImpersonationLevel.Delegation

Details on these impersonation levels can be found at: http://msdn.microsoft.com/en-us/library/system.security.principal.tokenimpersonationlevel.aspx

Config Changes


<configuration>
<system.serviceModel>
<bindings>
<basicHttpBinding>
<binding name="ListsSoap">
<security mode="TransportCredentialOnly">
<transport clientCredentialType="Ntlm" proxyCredentialType="None"
realm="" />
<message clientCredentialType="UserName" algorithmSuite="Default" />
</security>
</binding>
</basicHttpBinding>
<client>
<endpoint address="http://servername/_vti_bin/Lists.asmx"
binding="basicHttpBinding" bindingConfiguration="ListsSoap"
contract="ServiceReference1.ListsSoap" name="ListsSoap1" />
</client>
</system.serviceModel>
</configuration>

Saturday 31 October 2009

DDK's Top 3rd Party Applications and Utilities

I thought before I move to Windows 7 Ultimate and blow away Vista 32, I'd make a note of all the utilities I use the most (as a reminder to install them on the new OS!):

  1. All SysInternal Applications (http://technet.microsoft.com/en-us/sysinternals/bb795535.aspx), especially Process Monitor - to find out which processes are locking which files, what registry entries are used by an application. Autoruns is also one of my favourites.
  2. Tera Copy (allows resume if you file copy fails) - http://www.codesector.com/teracopy.php
  3. Fullshot Image Capture (http://www.inbit.com/fullshot.html) - I use this every day at work.
  4. Scooter Beyond Compare (http://www.scootersoftware.com/)
  5. ImgBurn - For all image burning needs e.g. Techned ISO images when installing Windows 7 (http://www.imgburn.com/)
  6. Lutz Roeder's .NET Reflector (http://www.red-gate.com/products/reflector/)
  7. Avast Antivirus - I've found this to pick up viruses that Norton misses on corporate networks (http://www.avast.com/eng/download-avast-home.html)
  8. VLC - Video Lan (http://www.videolan.org/) - wave your codec issues goodbye.
  9. HighJackThis (Antispyware) - http://free.antivirus.com/hijackthis/
  10. SpyBot Search and Destroy (Antispyware) - http://www.spybot.info/
  11. Firefox Mozilla, with Firebug and Download Helper and PDF download Addons
  12. Microsoft Virtual PC
  13. Microsoft Expression Studio
  14. Visual Studio 2008
  15. Type Mock
  16. NUnit
  17. MyGeneration (free code generation tool)
  18. Eclipse
  19. WinRAR
  20. uTorrent
  21. PowerISO - for mounting ISO images
  22. WSP Builder (for SharePoint deployment)
  23. CCleaner (aka Crap Cleaner)
  24. Visual Color Picker 2.0
  25. Notepad++ (as notepad replacement) - I hate having no undo!
  26. Paint .NET
  27. GIMP (another great free image manipulation program)
  28. Filezilla FTP Client
  29. Quest Toad for SQL Server
  30. Windows Live Messenger
  31. SharePoint branding tool http://brandingtool.codeplex.com/. This helps with the re-application of themes across existing sites as described here http://www.heathersolomon.com/blog/archive/2008/01/30/SharePoint-2007-Design-Tip-Import-your-CSS-for-SharePoint-Themes.aspx

I'm sure I've forgotten a few - but that will have to do for now!

Friday 30 October 2009

SharePoint 2007 vs SAP DMS vs SAP KM vs OpenText for Document Management

Differences between SAP DMS and SharePoint 2007


With SharePoint 2007 having such an incredible level of growth and popularity in the CMS, Portals and Collaboration space, questions arise as to whether the document management system (DMS) that comes with SAP should be used at all if both co-exist in the same enterprise environment. Here are some of the advantages as I see them when comparing the 2 systems:

SharePoint 2007 Advantages
  1. Familiarity to MS Office users and reduced training
  2. MS Office Integration e.g. Check in, check out and update document metadata from within Word/Excel/etc itself - without Install of SAP Easy DMS
  3. Offline capabilities i.e. checkout to "SharePoint Drafts" folder so you can work from home without a connection - without SAP Easy DMS
  4. Licensing - Licensing models are different - no SAP licenses required - esp if most users are not using SAP.

SAP DMS Advantages

  1. No Additional Licenses required if all users are using SAP
  2. Direct link of document to Business Entities directly without specifying additional metadata (e.g. supporting documentation for invoice) - this is sometimes a more natural linkage.

As desribed in this Cap Gemini blog on the subject - "Will we get to the point where companies just have their SAP system as a backend database and have SharePoint vNext (SharePoint 2014) as the integration platform? "
(From http://www.capgemini.com/technology-blog/2009/02/will_sharepoint_14_become_sap.php)

[UPDATE 2nd November 2009 - 1]
It has been pointed out to me that a comparison between SAP KM and SharePoint 2007 would be a more relevant comparison.

SAP KM Advantages

  1. Doesn't have requirement for client (like SAP Document Management System (DMS) does)
  2. Can preview CAD and other documents within the Web UI directly, unlike SharePoint
  3. Closer integration with SAP Business Data versus SharePoint

[UPDATE 2nd November 2009 - 2]
SAP Document Management offering with OpenText

One of my Oakton colleagues brought this to my attention - SAP now has a partnership deal with OpenText.

"The last option (out of SharePoint/DMS & KM/OpenText) is really powerful but comes at a price as OpenText is a leader in Document Management and integrates into SharePoint seamlessly. For large organisation with strong records management requirements with industrial class DM requirements option 3 would be a good choice, hoping that OpenText has done all the heavy lifting in terms of SAP Integration."

Wednesday 14 October 2009

SAP - Creating and Updating Vendor Master Records through SAP API

As an ERP, you would think that programatically creating a basic Vendor record in SAP would be one of the basic, easily accessible functions right?

WRONG! (Thunderclap ensues...)

When creating technical specifications for a vendor create function being called from SAP BPM, I tried to find how to programmatically create a Vendor. Unfortunately, this area is not well documented (like many areas of SAP) so I had to do the usual exhaustive/brute force search for the correct Function Module or Class Calls to make. This is what I found:

  1. There is a BAPI in SAP called "BAPI_VENDOR_CREATE" that I found through standard transaction /nbapi - however if you look at the definition it doesn't actually have any parameters. This effectively makes it useless. Obviously it is for internal use!

  2. The SAP Enterprise Service (BusinessPartnerDataManagementSupplierIn) is not comprehensive at all - it has a create, but not an update. Seems like quite an oversight to me... I am reluctant to use the enterprise service (which is the "Best Practice" Method of making updates to SAP) for the "Create Vendor" call, but another API set for the Update of that same Vendor. My search continues...
  3. Perhaps it is available as a Class, not a BAPI or Enterprise Service.... so I went to transaction /nse80 (Repository Browser) and did several exhaustive searches on SAP classes. I eventually found a few possible candidates - and nailed it down to the "Vendor Maintenance API" - VMD_EI_API, which has an "INITIALIZE" Method and a "MAINTENANCE" method which allows for the create and the update of vendors. Voila!

    Using the Repository Browser to nail down that Vendor Create API:

Found the needle in the proverbial haystack at the bottom of the search list (VMD_EI_API - the SAP Vendor Maintenance API):

Here is the model we used to call this from web services (done via SAP PI to simplify mapping):


UPDATE (14 Oct 2009) : Obviously other people have been having the same issue trying to find the API - as per my post on the SAP Developer Network (SDN) - https://forumsa.sdn.sap.com/thread.jspa?messageID=8283585#8283585


SAP - How to find which SAP Classes, Interfaces and BAPIs write to a particular SAP Table in SAP GUI

  1. In SAP GUI, go to /nse11 and enter your table name (e.g. Table "LFA1" for SAP Vendors).
  2. Click display to display the table (this is just transaction SE11_OLD)
  3. Just choose Programs, Classes/Interfaces and click Execute. You will be provided with a full list of the BAPIs, classes and interfaces that reference that table.
  4. Alternatively you can use transaction /nse37 to search for BAPIs that have a name similar to the table or the functionality they expose (e.g. *VENDOR*).

Emulating SAP GUI in InfoPath 2007 - How to determine how extended lookup fields in the SAP GUI are populated & using the InfoPath SLP Addon

I've recently been creating browser based InfoPath forms as a frontend to SAP to make it a bit more palatable from the end user perspective. Most of the built-in forms in SAP GUI have a lookup mechanism that allows users lookup related data and pick a particular record. e.g. relate the Requesting Employee to a particular Purchase Order.


However, I had 2 issues in doing this:

  1. InfoPath doesn't have the ability to pop up a lookup form to permit the search for lookup information when creating records via a picker (as opposed to a dropdown with 1000s of records in it).
  2. It isn't obvious where the data comes from in the SAP GUI - it can be from SAP tables, views or function calls.


Resolution 1 - InfoPath Form:


For creating lookups/pickers for large datasets I used the SLP Addon for InfoPath 2007 (at http://hermansberghem.blogspot.com/2007/11/free-download-slp-infopath-extension.html). This allows lookup screens to be implemented outside the InfoPath form (it's not really feasible to have 1000s of SAP records show up in an InfoPath dropdown). I then created SharePoint Custom Application Pages which made calls to SAP Enterprise Services and to BAPIs exposed as web services to populate the lookup forms. This worked much better than I expected.


Resolution 2 - Determining Datasources of Lookup fields in SAP :

To work out how some of the equivalent lookup forms were populated in SAP, you can either cross your fingers and trawl through all the foreign key relationships and structures OR you can simply do the following:

  1. In SAP GUI, go to your transaction that does the lookup (e.g. Project Create Transaction /ncj01).
  2. Click on the field that does the lookup e.g. Person Responsible for the Project and click on the lookup helper button

  3. Press F1 for help when on the lookup screen

  4. Click on the Technical Information Icon on the Help Screen
  5. There you have it – the table name for the lookup - TCJ04:
  6. Double-click on the table name in the Technical Information form to View the Table Definition
  7. If you want, use transaction /nse16 to view the data and click Execute to confirm that the lookup information is the same as presented in the SAP GUI.

SAP Transaction and BAPI Knowledgebase

The SAP GUI is the default application for updating and viewing SAP data. While it is aimed at business and power users, it is quite an inaccesable rich client windows application with limited search functionality and the requirement to know many SAP "transaction codes" off the top of your head.

To help me (and you) a little, below is a list of some of the most useful transactions available in the SAP GUI Tool (pictured below). I've had to use them all more than once in my current SharePoint to SAP Integration Project and especially in writing combined SAP/SAP BPM/SharePoint technical specifications:

SAP Transactions (enter through SAP GUI transaction area in the top left hand side)

Note that you can prefix a transaction with /o (instead of /n) which will open the transaction up in a new window in the SAP GUI e.g. /ose11 for the ABAP dictionary
  1. /nse11 = ABAP Dictionary (for maintenance of tables and table data)
  2. /nse16 = Data Browser for Tables
  3. /nbapi = official UI for looking for built-in BAPIs with full documentation.
  4. /nse37 = Function Builder (for searching for custom BAPIs not in /nbapi)
  5. /ose24 - to find SAP classes (not BAPIs). Look here if you cannot find a BAPI that will do the job e.g. Vendor creation
  6. /soamanager = SOA manager
  7. /nsicf = Maintain web services
  8. /nswf_bam = Business Activity Monitoring (for SAP BPM)
  9. /nse80 = object explorer
  10. /nse10 = Transport Organizer (to see what is in a transport)
  11. /nXK02 = Change Vendor
  12. /nxk01 = create vendor
  13. /nme23n = Official Display Purchase Order, click “Other purchase order”
  14. /ncj03 = Display Project (NB. typically, any business object transaction ending in 01 is for create, 02 is update, 03 is for display)
  15. /h - turn on ABAP debugging (so you can work out what SAP GUI screens are doing in the background). Also allows you to write to tables directly with a workaround.
  16. /ncat2 - Timesheet entry
  17. /ncat7 - Transfer to Controlling (FICO).
  18. /ncats_da - Display Working Times
  19. /npa30 - Maintain HR Master Data.
  20. /nsu01 - User maintenance - so you can unlock users in SAP
  21. /nsxmb_moni - (For PI Server) - Integration Monitoring
Commonly Used Tables
  1. LFA1 - Vendor Master
  2. LFM1 - Vendor master record purchasing organization data
  3. EKKO - Purchasing Document Header
  4. EKPO - Purchasing Document Item (aka Line Items in your Purchase Order)
  5. T016 - List of Industries
  6. T000 - Clients
  7. T001 - Company Codes
  8. T005 - Countries
  9. T005 - Unit of Measurement (e.g. EA, Square Metres)
  10. PROJ - Projects
  11. PRPS - Work Breakdown structure (WBS) - basically project line items
  12. SKA1 - General Ledger Account master (aka Chart of Accounts)
Enterprise Services Additional Notes
  1. AccountDeterminationExpenseGroupCode is synonymous with GL (General Ledger) Code when working with Purchase Order Enterprise services.

    The SAP GUI - definitely not an intuitive application

Tuesday 6 October 2009

SAP Web Dynpro Java - Adding a new Item row to an existing model object

As part of a proof of concept (PoC) for one of my clients, I had to create a purchase order form which had multiple items against it - using an Adobe Form in Adobe Lifecycle Forms Designer. Some articles suggested that a JavaScript client side call (e.g. Table1.Row1.instanceManager.addInstance(1); would work - but in Adobe Document Services, this would never render for me - even if I forced the render to the most recent version of Adobe (9.0). It was suggested by SAP consultants that we should just be doing a full post back to the server and adding items in the server side Java code instead.

For adding items in a dynamic table like this on the server side (using the SAP Purchase Order Create Web Service (PurchaseOrderCreateRequestConfirmation_In, SAP ECC604), see below:


public void onActionAddItemRow(com.sap.tc.webdynpro.progmodel.api.IWDCustomEvent wdEvent )
{
//@@begin onActionAddItemRow(ServerEvent)

// Here we get the current parent node of the model from the view
// context to which we want to add the item row

com.sap.demo.jrd.readpobyws.wd.readpowsmodel.PurchaseOrder poN = wdContext.currentPurchaseOrderElement().modelObject();

// Here we create a new, empty instance of the item row casting
// to the correct model class by using the associated model property
// of the parent node
// Note that at this point the instance is not yet bound to the item

com.sap.demo.jrd.readpobyws.wd.readpowsmodel.Item itemN = new Item((ReadPOWSModel) poN.associatedModel());

// Here we create an instance of a subnode of Item using the same
// approach
com.sap.demo.jrd.readpobyws.wd.readpowsmodel.SHORT_Description descN = new SHORT_Description((ReadPOWSModel) poN.associatedModel());

// If you wish to set default values in the leaf elements this is how
descN.set$0023SimpleContent("New item");
itemN.setID("99");

// Here we put the subnode for the Description into the parent Item
itemN.setDescription(descN);

// Here we create an instance of the subnode for the Accounting Coding
// Block Assignment using the same approach
com.sap.demo.jrd.readpobyws.wd.readpowsmodel.PurOrdERPItmByAcctgCodgBlkDistrRsp_SAcctgCodgBlkAssgmt aCBAsN = new PurOrdERPItmByAcctgCodgBlkDistrRsp_SAcctgCodgBlkAssgmt((ReadPOWSModel) poN.associatedModel());

// If you wish to set default values this is the example:
aCBAsN.setAccountDeterminationExpenseGroupCode("0000112000");

// Here we create an instance of the subnode for the Accounting Coding
// Block Distribution, i.e. the parent of the ACB Assignment
com.sap.demo.jrd.readpobyws.wd.readpowsmodel.PurOrdERPIdRsp_SAcctgCodgBlkDistr aCBDN =
new PurOrdERPIdRsp_SAcctgCodgBlkDistr((ReadPOWSModel) poN.associatedModel());

// Here we put the subnode for the ACB Assignment into the
// parent ACB Distribution
aCBDN.addAccountingCodingBlockAssignment(aCBAsN);

// Here we put the subnode ACB Distribution into the parent item
itemN.setAccountingCodingBlockDistribution(aCBDN);

// Finally – once the substructure has been built, we add the item
// to the purchase order node – i.e. set the item as a new row
poN.addItem(itemN);


//@@end
}

Friday 18 September 2009

SAP to Microsoft .NET Integration - Fixes required to WSDL after referencing SAP BAPI WSDLs to work with .NET Service References in Visual Studio 2008

After referencing a Web Service that is based on a SAP BAPI (as opposed to a SAP Enterprise Service), you may need to save and modify the WSDL so to remove reserved words in the file. Otherwise, you will get compile-time errors from the Service Reference generated code such as:


An object reference is required for the non-static field, method, or property 'SharePointCustomLookup.CostCentreSearchService.Bapiret2.System.get'


Solution/Fix:
1. Save the WSDL from the HTTP location to your local machine (you can get this WSDL location if it is already set up from transaction /soamanager in SAP GUI). Open up the wsdl in your fave text editor.
2. Replace all instances of “System” with “SYSTEM” (To avoid conflicts with the System namespace in .NET)
3. Replace all instances of “parameters” with “parameter” so that parameters are read correctly by Visual Studio 2008.
4. Update your service reference to use the locally modified WSDL (which should have the correct endpoints to the web services on your SAP PI/ECC Server )

These modifications to the WSDL is not required for all BAPI Web Service calls - only ones which exhibit the compile-time issue.




One other important thing to note is that if your BAPI has an input parameter, then you have to instantiate the parameter AND all the lists and arrays which make up that input parameter - otherwise you won't get a return result.

e.g. For calling a Project Definition GetList BAPI, you have to instantiate parameter and the Project Definition List with a zero-sized array - otherwise you will NOT get any values back - and yet receive no error.


//You must instantiate the input parameter and any lists/arrays within that
//parameter for any results to be returned
Z_BAPI_PROJECTDEF_GETLISTClient client = new Z_BAPI_PROJECTDEF_GETLISTClient();
ProjectdefGetlist parameter = new ProjectdefGetlist();
parameter.ProjectDefinitionList = new Bapiprexp[0];
ProjectdefGetlistResponse response = client.ProjectdefGetlist(parameter);

Fix For SharePoint 2007 Workflow - "WorkflowValidationFailedException: The workflow failed validation."

If your workflow fails and goes to a status of "Failed on Start (retrying)", and you have declarative rule conditions in your workflow (or RuleSets), it is likely that your workflow cannot be loaded due to a validation error like the one below:

Engine RunWorkflow: System.Workflow.ComponentModel.Compiler.WorkflowValidationFailedException: The workflow failed validation.

FULL EXCEPTION FROM ULS:
08/31/2009 11:54:56.68 w3wp.exe (0x10DC) 0x047C Windows SharePoint Services Workflow Infrastructure 936l High Engine RunWorkflow: System.Workflow.ComponentModel.Compiler.WorkflowValidationFailedException: The workflow failed validation. at System.Workflow.Runtime.WorkflowDefinitionDispenser.ValidateDefinition(Activity root, Boolean isNewType, ITypeProvider typeProvider) at System.Workflow.Runtime.WorkflowDefinitionDispenser.LoadRootActivity(Type workflowType, Boolean createDefinition, Boolean initForRuntime) at System.Workflow.Runtime.WorkflowDefinitionDispenser.GetRootActivity(Type workflowType, Boolean createNew, Boolean initForRuntime) at System.Workflow.Runtime.WorkflowRuntime.InitializeExecutor(Guid instanceId, CreationContext context, WorkflowExecutor executor, WorkflowInstance workflowInstance) at System.Workflow.Runtime.WorkflowRuntime.Load(Guid key, CreationContext con... 08/31/2009 11:54:56.68* w3wp.exe (0x10DC) 0x047C Windows SharePoint Services Workflow Infrastructure 936l High ...text, WorkflowInstance workflowInstance) at System.Workflow.Runtime.WorkflowRuntime.GetWorkflowExecutor(Guid instanceId, CreationContext context) at System.Workflow.Runtime.WorkflowRuntime.InternalCreateWorkflow(CreationContext context, Guid instanceId) at System.Workflow.Runtime.WorkflowRuntime.CreateWorkflow(Type workflowType, Dictionary`2 namedArgumentValues, Guid instanceId) at Microsoft.SharePoint.Workflow.SPWinOeHostServices.Send(SPWinOeWorkflow winoeworkflow, SPWorkflowEvent e) at Microsoft.SharePoint.Workflow.SPWinOeEngine.RunWorkflow(Guid trackingId, SPWorkflowHostService host, SPWorkflow workflow, Collection`1 events, TimeSpan timeOut)



The Fix:

As described in http://blog.hhebnes.no/?tag=/workflows, the fix is to:

Edit the Visual Studio project file (using notepad) and add the second line below:

<Import Project="$(MSBuildBinPath)\Microsoft.CSharp.targets" />
<Import Project="$(MSBuildExtensionsPath)\Microsoft\Windows Workflow Foundation\v3.5\Workflow.Targets" />

Then reload the project and redeploy the wsp and everything will hopefully work. I suspect this might be an issue with WSPBuilder and the project it creates as it doesn't create .NET 3.5 projects by default.

Wednesday 16 September 2009

InfoPath 2007 - Issue when adding Web Reference in Code Behind to SAP Enterprise Service


There is a bug when adding Web References to secured WSDL files in InfoPath 2007 Code (in Visual Studio Tools for Applications aka VSTA). For example, I received the following error when adding a Web eference to a SAP enterprise service:

Unable to cast COM object of type 'System.__ComObject' to interface type 'EnvDTE.IVsExtensibility'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{3C536122-57B1-46DE-AB34-ACC524140093}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

The simple fix is to save the wsdl file to your local file system e.g. C:\mySavedWSDL.wsdl) and use that wsdl file as a reference rather than pointing to the secured HTTP location for your WSDL source.

Friday 28 August 2009

Calling a SAP PI Web Service (using WCF bindings) from a SharePoint 2007 Workflow

There are 2 rules when calling a SAP PI web service from a SharePoint workflow:
  1. You have to use custom bindings to get authentication to work.
  2. After you've added your web service via the "Add Service" dialog, you have to copy the all of the contents under into the SharePoint web.config - as per http://blogs.msdn.com/sharepointdesigner/archive/2008/11/02/calling-a-wcf-service-from-a-sharepoint-workflow-using-visual-studio.aspx. This is because the contents of app.config are (obviously) not accessible by the SharePoint Workflow runtime - you have to use the web.config instead.

Point 1 - Custom Bindings

If you try to call a web service in SAP PI which requires credentials, none of the normal authentication methods seem to work. You will always get the following error in WCF.

The HTTP request is unauthorized with client authentication scheme 'Anonymous'.
The authentication header received from the server was 'Basic realm="XISOAPApps"




To fix this, you need to use the following custom bindings rather than the normal basicHttpBinding, like so:





<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<system.serviceModel>
<bindings>
<customBinding>
<binding name="CustomHttpTransportBinding">
<textMessageEncoding maxReadPoolSize="64" maxWritePoolSize="16"
messageVersion="Soap11" writeEncoding="utf-8">
<readerQuotas maxDepth="10000000" maxStringContentLength="10000000"
maxArrayLength="67108864" maxBytesPerRead="65536" maxNameTableCharCount="100000" />
</textMessageEncoding>
<httpTransport authenticationScheme="Basic" bypassProxyOnLocal="false"
hostNameComparisonMode="StrongWildcard" keepAliveEnabled="false"
proxyAuthenticationScheme="Basic" realm="XISOAPApps" useDefaultWebProxy="true" />
</binding>
</customBinding>
</bindings>
<client>
<endpoint address="http://servername:50000/XISOAPAdapter/MessageServlet?channel=:INTEGRATION_SERVER_DPI:SOAPSenderPOCreate&version=3.0&Sender.Service=INTEGRATION_SERVER_DPI&Interface=http://zz"
binding="customBinding" bindingConfiguration="CustomHttpTransportBinding"
contract="PurchaseOrderProcessingService.PurchaseOrderProcessing_Out"
name="PurchaseOrderProcessing_OutPort" />
<endpoint address="http://servername:50000/XISOAPAdapter/MessageServlet?channel=:INTEGRATION_SERVER_DPI:SOAPSenderPORelease&version=3.0&Sender.Service=INTEGRATION_SERVER_DPI&Interface=http://zz"
binding="customBinding" bindingConfiguration="CustomHttpTransportBinding"
contract="PurchaseOrderReleaseService.PurchaseOrderRelease_Out"
name="PurchaseOrderRelease_OutPort" />
<endpoint address="http://servername:50000/XISOAPAdapter/MessageServlet?channel=:INTEGRATION_SERVER_DPI:SOAPSenderGOS_URL_CREATE&version=3.0&Sender.Service=INTEGRATION_SERVER_DPI&http://zz"
binding="customBinding" bindingConfiguration="CustomHttpTransportBinding"
contract="PurchaseOrderAttachUrlService.MI_GOS_URL_CREATE_Syn_Out"
name="MI_GOS_URL_CREATE_Syn_OutPort" />
</client>
</system.serviceModel>
</configuration>

Point 2 - Web Service Configuration Settings
(from http://blogs.msdn.com/sharepointdesigner/archive/2008/11/02/calling-a-wcf-service-from-a-sharepoint-workflow-using-visual-studio.aspx)

  1. Open web.config in VS for editing. You can find the file in the "\inetpub\wwwroot\wss\VirtualDirectories\80" directory, where 80 corresponds to the port of your SharePoint application.
  2. Copy the "System.ServiceModel" element from app.config into web.config. If you already have a "System.ServiceModel” element, you’ll need to merge the and elements in manually.
  3. To complete your changes, open a windows command window (Start/Run/cmd.exe) and type "iisreset", which will cycle the sharepoint web application, so it can pick up the web.config changes.

InfoPath 2007 - Design Checker Error "Binding a non-repeating control to a repeating field or group is not supported by InfoPath Forms Services."

For read-only purposes, I had expression boxes in my InfoPath 2007 form (browser-enabled) that referred to (via XPath) repeating items within the form - but using an XPath sum(). Even though sum always returns a single value, InfoPath (erroneously) detects the use of the repeating table (i.e. I am trying to sum the total purchase order items). It then shows the following error in the Design Checker:

Binding a non-repeating control to a repeating field or group is not supported by InfoPath Forms Services. To fix this problem, remove the control or replace it with a repeating control, such as repeating section or table."


I automatically assumed that this would be the same as other "errors" and would prevent the publication and activation of this form. This is not the case. In fact, the Design Checker should really should show as a warning, as you can safely ignore the "error" and publish anyway.




Monday 17 August 2009

Setting Up Single Sign On for SharePoint 2007 for Integration with SAP

I'm currently integrating SAP with SharePoint for a client at the moment. One of the requirements for the set up of iView web parts is to set up SSO beforehand. Problem is, some of the articles I looked at miss some important steps or have some incorrect information. I'm not showing the full set of instructions here as it is a very long list. See http://download.microsoft.com/download/b/c/1/bc1939d9-638f-4053-b602-4258f18bb683/CB_045_Interoperability%20between%20SAP%20NetWeaver%20Portal%20and%20Microsoft%20SharePoint%20Server%202007.pdf for the comprehensive listing of steps. While this PDF file is good, see step 6 "Physically log into the box as the SSO account" as it is not mentioned in this PDF or the books I've seen. Also see my section "For the Set up of the Trusted Hosts File" for another correction to the PDF document.

For the set up of the SSO Service
  1. Create a new domain service account for running SSO service. E.g. MYDOMAIN\svc_sso
  2. Add that account to local admins on the box
  3. Give the account system admin permissions on the SQL Box
  4. Change SSO service's Identity to the new service account from the Servicesconsole (Admin Tools->Services), change to automatic start and start the service up
  5. Add SSO service account as a farm administrator by using "Update farm administrator's group" link from share point central admin.
  6. Physically log into the box as the SSO account (you cannot just do a Login as a different user in Central admin – it won’t work. Presumably a bug in MOSS.) NOTE THIS IS AN IMPORTANT Step (otherwise you may get errors such as “Login Failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’” or “You do not have the rights to perform this operation.” In the Manage Server Settings section of SSO setup)
  7. Open up Central admin, and you should now be able to enter and set the SSO account information.Remove the account from farm admins.


For the Set up of the Trusted Hosts File
Also note that instructions say that you have to set up the trusted hosts file or the sample in
c:\Program Files\Microsoft Office Servers\12.0\Config\TrustedSAPHosts.config or
c:\Program Files\Microsoft Office Servers\12.0\Config\TrustedSAPHosts.sample.xml as per
http://technet.microsoft.com/en-us/library/cc262461.aspx

This is not correct - the file is actually in in C:\Program Files\Microsoft Office Servers\12.0\Config\

Wednesday 12 August 2009

SQL Server Integration Services 2008 - Fix for when SSIS Script Tasks Freeze or Run indefinitely without Returning a Result

As you may have seen in my previous posts, I've been having some interesting adventures with SQL 2008 SSIS packages of late. However, the most obscure one hit me today which meant that all Script tasks would run endlessly without error if the code hit ANY exception at all. The problem would happen even when the exception was fully handled within a try...catch blog.

I tried several things like:
  1. Remove all code except for a throw new Exception() - SSIS Script task STILL freezes
  2. Change the project target back to .NET 2.0 from 3.5 (after removing all code) - SSIS Script task STILL freezes
  3. Remove all variables from the Script Task properties - VOILA!

    I eventually worked out that a Package Level variable that i'd defined named "DBConnectionString" would stop exceptions from being bubbled up to the parent package and freeze the task when it was marked as either a Read-Write Variable and a Read-Only variable in the script task properties. I removed this variable definition from the script task properties and all exceptions started to bubble up correctly.

    I tried to reproduce the issue in a completely new package with no success (on the production 64 bit environment and my 32 bit dev machine). It doesn't appear to be related to the variable name or the value of that variable.

    This is just a warning to all those who encounter this unfortunate Script task freezing issue in SSIS. It may also have the appearance of being in an infinite loop as the whole package just sits there and will NEVER complete.

Monday 10 August 2009

SQL Server Integration Services - How to check for the existence of an Excel Worksheet in SSIS

This code snippet shows you how to check for the existence of a worksheet in an Excel file. I had to do this because the data collection spreadsheet I was importing from would not add a $Warnings worksheet if there were no warnings for a particular set of data. The code below relies on the GetSchema() method of OLEDB connections to obtain schema information.


public class ScriptMain
{
public void Main()
{
string fileToTest;
string tableToTest;
string connectionString;
OleDbConnection excelConnection;
DataTable excelTables;
string currentTable;

fileToTest = Dts.Variables["ExcelFile"].Value.ToString();
tableToTest = Dts.Variables["ExcelTable"].Value.ToString();

Dts.Variables["ExcelTableExists"].Value = false;
if (File.Exists(fileToTest))
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + fileToTest + ";Extended Properties=Excel 8.0";
excelConnection = new OleDbConnection(connectionString);
excelConnection.Open();
excelTables = excelConnection.GetSchema("Tables");
foreach (DataRow excelTable in excelTables.Rows)
{
currentTable = excelTable["TABLE_NAME"].ToString();
if (currentTable == tableToTest)
{
Dts.Variables["ExcelTableExists"].Value = true;
}
}
}

Dts.TaskResult = (int)ScriptResults.Success;

}
}


For more information, see http://technet.microsoft.com/en-us/library/ms403358.aspx#example1

Tuesday 21 July 2009

Fix SSIS 2008 Error - The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data."

When importing a datasource from a flat file today (a csv), (which ran through a Derived Column Transformation and then a Data Conversion Transformation before hitting SQL), I hit the following error in SQL Server Integration Services 2008 (SSIS 2008):

"Data conversion failed while converting column .....The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data."

The dataviewers and error output SSIS debugging techniques didn't show anything unusual in the data as it was transformed. In addition, this problem only occurred on my float columns, and only when the data in the columns is blank. I did some quick checks to see if there were a few extra spaces in some of the fields and did a TRIM() for the derived column - but the same error kept coming up. I even enabled "Ignore truncation" and it still didn't work.

Information: 0x402090DE at ImportTemplateData, Flat File Source [2917]: The total number of data rows processed for file "C:\WorkforceProfileInitialImport\Q2_2008_162_Data.csv" is 1050.
Error: 0xC02020C5 at ImportTemplateData, Data Conversion [4034]: Data conversion failed while converting column "[8c Override Census Period FTE]" (2050) to column "Copy of [8c Override Census Period FTE]" (5338). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at ImportTemplateData, Data Conversion [4034]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "Copy of [8c Override Census Period FTE]" (5338)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "Copy of [8c Override Census Period FTE]" (5338)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
Error: 0xC0047022 at ImportTemplateData, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion" (4034) failed with error code 0xC0209029 while processing input "Data Conversion Input" (4035). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Information: 0x40043008 at ImportTemplateData, SSIS.Pipeline: Post Execute phase is beginning.


The underlying issue was that it was trying to convert these blank strings (from the flat file source datatype) into the float datatype and was thereforce constantly failing. The fix is to tick on the "Retain null values from the source as null values in the data flow" and the package then started to run successfully for those columns which had just blank values.

Friday 10 July 2009

Fix - SQL Reporting Services 2008 - An error occurred during local report processing. Access Denied with HRESULT 0x80030005


While attempting to preview a report developed by another user (after getting it out of Visual Source Safe), I was confronted by the following error:

An error occurred during local report processing. Access Denied. (Exception from HRESULT: 0x80030005 (STG_E_ACCESSDENIED))

I created another report exactly the same and it seemed to work fine - so there was some other kind of caching issue causing this error.

After investigating, I noticed that the REPORTNAME.rdl.data file (which is a binary file and is how SSRS reports cache their preview information) - was read-only. SSRS was trying to write to the file and consequently getting the access denied errors. This was because the rdl.data file had erroneously been put into source control.

The fix was to remove it from source control (as it should be generated as each user previews the report), and mark the local file as read only.

Of course it would be much nicer (and simpler to troubleshoot the issue) if this particular "Access Denied" error message was more descriptive and actually told me *which* file it was trying to access.