none
How to Create SharePoint list from SQL Query RRS feed

  • Question

  • Hi all,

    How to create SPO list from SQL Query (view)? I don.t want to use BCS , because I want to attach workflow and add few calculated column etc. Due BCS limitation, I want to sync it by any other way. Is this possible custom way?

    Monday, December 2, 2019 7:02 PM

All replies

  • I don't think there is any possible way of creating a list in SharePoint online by using SQL Query. Instead you could use a CSOM script to create a List.

    ############################################################################################################################################ 
    #Script that allows to create a new list in a SharePoint Online Site 
    # Required Parameters: 
    #  -> $sUserName: User Name to connect to the SharePoint Online Site Collection. 
    #  -> $sPassword: Password for the user. 
    #  -> $sCSOMPath: CSOM Assemblies Path. 
    #  -> $sSiteUrl: SharePoint Online Site Url. 
    #  -> $sListName: Name of the list we are going to create. 
    #  -> $sListDescription: List description. 
    ############################################################################################################################################ 
     
    $host.Runspace.ThreadOptions = "ReuseThread" 
     
    #Definition of the function that allows to create a new view in a SharePoint Online list 
    function Create-NewListSPO 
    { 
        param ($sCSOMPath,$sSiteUrl,$sUserName,$sPassword,$sListName,$sListDescription) 
        try 
        {    
            #Adding the Client OM Assemblies         
            $sCSOMRuntimePath=$sCSOMPath +  "\Microsoft.SharePoint.Client.Runtime.dll"         
            $sCSOMPath=$sCSOMPath +  "\Microsoft.SharePoint.Client.dll"              
            Add-Type -Path $sCSOMPath          
            Add-Type -Path $sCSOMRuntimePath        
     
            #SPO Client Object Model Context 
            $spoCtx = New-Object Microsoft.SharePoint.Client.ClientContext($sSiteUrl) 
            $spoCredentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($sUserName, $sPassword)   
            $spoCtx.Credentials = $spoCredentials       
     
            Write-Host "----------------------------------------------------------------------------"  -foregroundcolor Green 
            Write-Host "Creating List $sListName in $sSiteUrl !!" -ForegroundColor Green 
            Write-Host "----------------------------------------------------------------------------"  -foregroundcolor Green         
     
            $spoWeb=$spoCtx.Web 
            $spoListCreationInformation=New-Object Microsoft.SharePoint.Client.ListCreationInformation 
            $spoListCreationInformation.Title=$sListName 
            #https://msdn.microsoft.com/EN-US/library/office/microsoft.sharepoint.client.listtemplatetype.aspx 
            $spoListCreationInformation.TemplateType=[int][Microsoft.SharePoint.Client.ListTemplatetype]::GenericList 
            $spoList=$spoWeb.Lists.Add($spoListCreationInformation) 
            $spoList.Description=$sListDescription 
            $spoCtx.ExecuteQuery() 
     
            Write-Host "----------------------------------------------------------------------------"  -foregroundcolor Green 
            Write-Host "Lsita $sListName created in $sSiteUrl !!" -ForegroundColor Green 
            Write-Host "----------------------------------------------------------------------------"  -foregroundcolor Green   
            $spoCtx.Dispose() 
        } 
        catch [System.Exception] 
        { 
            Write-Host -ForegroundColor Red $_.Exception.ToString()    
        }     
    } 
     
    #Required Parameters 
    $sSiteUrl = "https://<O365Domain>.sharepoint.com/<SPO_Site>" 
    $sUserName = "<O365User>@<O365Domain>.onmicrosoft.com"  
    $sListName= "<SPO_List_Name>" 
    $sListDescription="<List Description>" 
    #$sPassword = Read-Host -Prompt "Enter your password: " -AsSecureString   
    $sPassword=ConvertTo-SecureString "<SPO_Password>" -AsPlainText -Force 
    $sCSOMPath="<SPO_Path>" 
     
    Create-NewListSPO -sCSOMPath $sCSOMPath -sSiteUrl $sSiteUrl -sUserName $sUserName -sPassword $sPassword -sListName $sListName -sListDescription $sListDescription 
     

    Below article for your reference:

    https://gallery.technet.microsoft.com/office/How-to-create-a-List-in-a-2ff75523

    Thanks & Regards,

     

    sharath aluri

    Monday, December 2, 2019 7:46 PM
  • Hi,

    If you don't want to use BCS, we have to use custom code to achieve it.

    If you use Azure SQL database and want to create SharePoint Online list with data from table in Azure SQL database, we can get the data from sql table using PowerShell, create a list in SharePoint Online and add list items using PnP PowerShell. Create a windows scheduler task to run the PowerShell. The following articles with PowerShell script for your reference.

    How to query an Azure SQL database with PowerShell

    Create SharePoint Online list using New-PnPList

    Creating SharePoint List Item Using PnP PowerShell

    Best Regards,

    Dennis


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Tuesday, December 3, 2019 7:54 AM
    Moderator