The query below returns most of the data from the SharePoint list that my customers want:
SELECT *
FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0',
'WSS;IMEX=1;RetrieveIds=Yes;
User id=abc1;Password=xxxxxxx;
DATABASE=http://abcdefg/HijkLMno/;LIST=Whatever;',
'SELECT * FROM LIST') B
The issue is I need to join the local SharePoint User IDs from the UserInfo table. How do I construct the DATABASE= string and use OPENROWSET to get the ID, names, etc...?
PS I must use OPENROWSET; Please do not suggest i query these tables directly as I cannot.