{"id":710,"date":"2014-03-21T18:53:11","date_gmt":"2014-03-21T18:53:11","guid":{"rendered":"http:\/\/elbsolutions.com\/projects\/?p=710"},"modified":"2022-02-03T11:25:02","modified_gmt":"2022-02-03T17:25:02","slug":"get-sharepoint-list-access-sql-server","status":"publish","type":"post","link":"https:\/\/elbsolutions.com\/projects\/get-sharepoint-list-access-sql-server\/","title":{"rendered":"Get Sharepoint List Access from SQL Server"},"content":{"rendered":"

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

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.<\/p>\n

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<\/a> and the guy who suggested it is WAAAY down the comments list on this article<\/a> (look for Don Vineyard’s comment). Note that IMEX=1 means read only.<\/p>\n

\r\nselect '1',*\r\nfrom\r\nOPENROWSET ('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};',\r\n 'SELECT * FROM LIST')\r\n\r\nselect '2',*\r\nfrom OPENROWSET ('Microsoft.ACE.OLEDB.12.0','WSS;IMEX=1;RetrieveIds=Yes;DATABASE=http:\/\/mysite.com\/somedirectory\/anotherdirectory\/;LIST={1d2d13c4-08cd-4068-8cc1-58b5d6d739e0};'\r\n, 'SELECT * FROM LIST')\r\n<\/pre>\n
Now that is quite basic. OPENROWSET does not accepta dynamic string so …<\/p>\n
\r\n\r\nDECLARE @listnumber VARCHAR(100) = '2719FBBB%2D1C7F%2D4D64%2DAF55%2DC3E3A83378A8' --note I copied and pasted this from the URL\r\n\/* convert the url version to something usable - lets be lazy and let the computers do th work - make a function for this of course *\/\r\nSET @listnumber = REPLACE(@listnumber,'%7B','')\r\nSET @listnumber = REPLACE(@listnumber,'%2D','-')\r\nSET @listnumber = REPLACE(@listnumber,'%7b','')\r\nSET @listnumber = REPLACE(@listnumber,'%2d','-')\r\n\r\nPRINT @listnumber\r\n\r\nDECLARE\r\n@user VARCHAR(50) = 'DOMAIN\\my.username'\r\n, @password VARCHAR(50) = 'my_password'\r\n, @database VARCHAR(255) = 'http:\/\/mysite.com\/my_share\/point_root\/;LIST={' + @listnumber + '}'\r\n, @longSQL NVARCHAR(MAX)\r\n\r\n\/* Make the select statement being careful to use two single quotes '' when one is needed *\/\r\nSET @longSQL = N'select ''1'',*\r\nfrom\r\nOPENROWSET (''Microsoft.ACE.OLEDB.12.0'',''WSS;IMEX=1;RetrieveIds=Yes;User id=' + @user + ';Password=' + @password + ';DATABASE=' + @database + ';'',\r\n''SELECT * FROM LIST'')'\r\n\r\nPRINT @longSQL\r\n\r\nEXEC sp_executesql @longSQL\r\n\r\n<\/pre>\n
 <\/p>\n
As for getting it to be a linked server .. this article is a start that I still have to investigate …<\/p>\n
http:\/\/social.msdn.microsoft.com\/Forums\/sqlserver\/en-US\/8d5d2945-90ab-4a78-9a10-ec77daf52cd3\/sharepoint-linked-server?forum=sqldatabaseengine<\/a><\/p>\n
Others I have to vet are:<\/p>\n