Get Sharepoint List Access from SQL Server

Posted on by

Well, here I go – how to natively (or naively at this point – I just started this) manipulate Sharepoint Lists from SQL Server.

I did it! It is not using a linked server (I will get to the bottom of that though) but using an OPENROWSET works. This means that the user needs to have insert and select privileges – but there is a way around that too – by making a role/user internal to the dB and running the stored proc run (EXECUTE AS) that role/user. To get that to work takes some tinkering – another article will come soon regarding that.

Basically – the onerous method would show that the two statements below work on an integrated networked environment – this was done FROM Sql Server (SSIS). Connection strings for sharepoint are from this site and the guy who suggested it is WAAAY down the comments list on this article (look for Don Vineyard’s comment). Note that IMEX=1 means read only.

[sql]
select ‘1’,*
from
OPENROWSET (‘Microsoft.ACE.OLEDB.12.0′,’WSS;IMEX=1;RetrieveIds=Yes;User id=MYDOMAIN\username;Password=*********;DATABASE=http://mysite.com/somedirectory/anotherdirectory/;LIST={1d2d13c4-08cd-4068-8cc1-58b5d6d739e0};’,
‘SELECT * FROM LIST’)

select ‘2’,*
from OPENROWSET (‘Microsoft.ACE.OLEDB.12.0′,’WSS;IMEX=1;RetrieveIds=Yes;DATABASE=http://mysite.com/somedirectory/anotherdirectory/;LIST={1d2d13c4-08cd-4068-8cc1-58b5d6d739e0};’
, ‘SELECT * FROM LIST’)
[/sql]

Now that is quite basic. OPENROWSET does not accepta dynamic string so …

[sql]

DECLARE @listnumber VARCHAR(100) = ‘2719FBBB%2D1C7F%2D4D64%2DAF55%2DC3E3A83378A8′ –note I copied and pasted this from the URL
/* convert the url version to something usable – lets be lazy and let the computers do th work – make a function for this of course */
SET @listnumber = REPLACE(@listnumber,’%7B’,”)
SET @listnumber = REPLACE(@listnumber,’%2D’,’-‘)
SET @listnumber = REPLACE(@listnumber,’%7b’,”)
SET @listnumber = REPLACE(@listnumber,’%2d’,’-‘)

PRINT @listnumber

DECLARE
@user VARCHAR(50) = ‘DOMAIN\my.username’
, @password VARCHAR(50) = ‘my_password’
, @database VARCHAR(255) = ‘http://mysite.com/my_share/point_root/;LIST={‘ + @listnumber + ‘}’
, @longSQL NVARCHAR(MAX)

/* Make the select statement being careful to use two single quotes ” when one is needed */
SET @longSQL = N’select ”1”,*
from
OPENROWSET (”Microsoft.ACE.OLEDB.12.0”,”WSS;IMEX=1;RetrieveIds=Yes;User id=’ + @user + ‘;Password=’ + @password + ‘;DATABASE=’ + @database + ‘;”,
”SELECT * FROM LIST”)’

PRINT @longSQL

EXEC sp_executesql @longSQL

[/sql]

 

As for getting it to be a linked server .. this article is a start that I still have to investigate …

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/8d5d2945-90ab-4a78-9a10-ec77daf52cd3/sharepoint-linked-server?forum=sqldatabaseengine

Others I have to vet are:

Links So Far: