Menu
Does anyone know how to fetch Sharepoint List data using sql query based on the particular sharepoint user.
454
In Excel, open the Data tab and choose From Other Sources -> From Microsoft Query. Choose the SharePoint DSN. Select the option to use Query Wizard to create/edit queries. In the Query Wizard, expand the node for the table you would like to import into your spreadsheet.
Create a query that selects data from an SQL database table and appends it to a SharePoint list. Click on the Query Design button under the CREATE tab and select the SQL database table. Click on the Append button under the ribbon DESIGN tab to append data to the SharePoint list.
On the External Data tab, select More > SharePoint List. Specify the SharePoint site. Select Link to the data source by creating a linked table, and then click Next. Select the list you want to link to, and then click OK.
You can easily build a query by selecting options on the Basics tab. The Search Result Preview pane on the right hand side automatically displays the search results. In the Select a query list, choose a query by selecting a result source. Result sources specify what content to get search results from.
You cannot use should not really be using SQL to get data from SharePoint list. Most common way of doing this is custom solution where you can use CAML (SPQuery) to retrieve data for list items (created by the user for example) http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spquery.aspx or else you can expose the SharePoint services to get the same results http://msdn.microsoft.com/en-us/library/sharepoint/jj164060.aspx.
102
As long as it is SharePoint 2010 it is still relatively easy to pull from SQL as long as you have the list id GUID
See here
SELECT
ud.tp_ID
, ud.tp_ListId
, ud.tp_Author
, ud.nvarchar1
, ud.nvarchar2
, ud.nvarchar3
, ud.nvarchar4
, ud.nvarchar5
, ud.nvarchar6
, ud.nvarchar7
, ud.nvarchar8
, ud.nvarchar9
, ud.nvarchar10
, ud.nvarchar11
, ud.nvarchar12
, ud.*
FROM dbo.UserData ud
WHERE (ud.tp_ListId = '[{XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
