Connecting BDC to SQL WebService

Recently I've been experimenting with the Business Data Connector (BDC) technology available within MOSS. Resources on the Net are generally supportive and I've found that in general it works most of the time. Connecting to Database tables is well supported and although limited, connecting to WebServices is also possible. I have however come up with a limitation that I was unable to get around, that is connecting BDC to a SQL WebService or WCF Service as opposed to a standard ASPX.

Full WCF is simply not supported, however using the basicHTTP protocol can be accomplished with a little bit of text editing of the XML. I was however surprised that the SQL WebSerice caused such an issue for the BDC Editor.

Creating the WebService within SQL is a simple matter of running the following SQL against the NorthWind database:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetProductsProc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetProductsProc]
GO
CREATE PROC dbo.GetProductsProc
AS
SELECT
ProductID,
ProductName,
UnitPrice
FROM
Products
GO

DROP ENDPOINT GetProducts
GO

CREATE ENDPOINT GetProducts
STATE = STARTED
AS HTTP
(
PATH = '/Store',
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
CLEAR_PORT = 8045,
SITE = '*'
)
FOR SOAP
(
WEBMETHOD 'ProductsList'
(NAME='Northwind.dbo.GetProductsProc'),
BATCHES = DISABLED,
WSDL = DEFAULT,
LOGIN_TYPE = WINDOWS,
DATABASE = 'Northwind',
NAMESPACE = 'http://Northwind/Store'
)
GO

This provides an endpoint on the URL HTTP://localhost:8045/Store?wsdl.

Opening up the BCD Editor (available in the SharePoint SDK) and connecting to the WebService by clicking Add LOB System, Connect to WebService and type in the URL to your SQL WebService.





Click Add WebMethod from the right toolbar and drag the "ProductList" method on to the canvans and click OK. Choose the standard 'WebServiceLobSystem' System Name.






At this point we would create a Finder Instance within the /Entities/Entity0/Method/ProductList/Instances node and click Execute.

However unlike a standard webservice we get the error: AdapterObject is not of RootTypeDescriptor Type. Parameter name: adapterObject.















Following a long investigation I was unable to find a resolution to this issue. It is however all related to the fact that SQL is publishing the return values as an array of System.Objects and not an array of System.String. I'm hoping that the next version of the BCD Editor will work correctly.

Full Microsoft documentation for the BDC is available from :http://msdn.microsoft.com/en-us/library/ms563661.aspx