I am working on a project which will need to integrate a workflow solution based on MS SQL Server 7 and a content management system based on SharePoint 2001 Portal Server.
What I am trying to do is to run a distributed query combining data from SQL Server and SharePoint 2001 Portal Server, from within a connection to the SQL Server.
At this stage I am trying to make things work, so I first tried with OPENROWSET, something like this:
SELECT * FROM
'http://servername/workspace'; ''; '',
FROM SCOPE (''DEEP TRAVERSAL OF "/workspace/"'')
WHERE CONTAINS (''keywords'')')
This gives me the following error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAIPP.DSO' reported an error.
[OLE/DB provider returned message: Unspecified error]
If I omit the two empty strings for username and password then I get the following error:
Server: Msg 7373, Level 16, State 2, Line 1
Could not set the initialization properties for the OLE DB provider 'MSDAIPP.DSO'.
[OLE/DB provider returned message: Errors occurred]
If I omit the URL, process locks up - my later investigation discovered that the provider is actually prompting for an URL, which of course has catastrophic consequences when running on the server :-)
I also tried to add the linked server, but this essentially results with omitted URL, and any request to this linked server locking up ...
Anyone has had any previous experience with this? Searching the web did not help much, even the mighty Google returned 0 matches for 'sp_addlinkedserver MSDAIPP'.