How to query both SQL Database and SharePoint from a SQL Stored Procedure/SQL Reporting Services

makara.tan@ato.gov.au — Mon, 18 May 2009 07:01:20 GMT

Hi,

Do you have any information on how to call a

SharePoint Web Service from SQL Server CLR Function (written in VB.NET)?

What I am trying to do is:

XYZ is a SQL Server Database 2005 and ABC is a SharePoint v3.

XYZ has aID, bname and cStatus columns

ABC has owsID, owsNameOwner

On SQL Reporting 2005, I am trying to query both XYZ and ABC like:-

Select aID, bName, cStatus,

(select owsID from ABC as ABC1 where ABC1.owsID=XYZ.aID) as IDCode,

(select owsNameOwner from ABC as ABC2 where ABC2.owsID=XYZ.aID) as NameOwner

from XZY

My SQL Report will look like this:

aID bName Cstatus IDCode NameOwner

1 aaaa Failed 1 John B

2 bbbb Successful 2 Mary V

...

Thanks.

Regards,

Mak

.NET Tips: CLR