SharePoint Online - Power Shell

%3CLINGO-SUB%20id%3D%22lingo-sub-1992889%22%20slang%3D%22en-US%22%3ESharePoint%20Online%20-%20Power%20Shell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1992889%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20get%20items%20from%20SharePoint%20Online%20using%20Power%20Shell%20and%20want%20to%20insert%20to%20SQL%20server.%20I%20tried%20the%20following%20code%20and%20it%20is%20working.%20But%20I%20have%20a%20problem%20when%20I%20put%20that%20.ps%20file%20on%20Task%20Schedule.%20At%20that%20time%2C%20it%20is%20not%20working%20and%20not%20insert%20data%20in%20database%20table.%20If%20manual%20run%20%2C%20it%20is%20fine.How%20can%20I%20do%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%24siteUrl%20%3D%20%22https%3A%2F%2F-------------------%22%3B%3CBR%20%2F%3E%24listName%20%3D%20%22Test%22%3CBR%20%2F%3E%24userName%20%3D%20%22username%22%3B%3CBR%20%2F%3E%24password%20%3D%20%22password%22%3B%3CBR%20%2F%3E%24SecurePassword%20%3D%20%24password%20%7C%20ConvertTo-SecureString%20-AsPlainText%20-Force%3CBR%20%2F%3E%24ctx%20%3D%20New-Object%20Microsoft.SharePoint.Client.ClientContext(%24siteUrl)%3CBR%20%2F%3E%24credentials%20%3D%20New-Object%20Microsoft.SharePoint.Client.SharePointOnlineCredentials(%24userName%2C%20%24SecurePassword)%3CBR%20%2F%3E%24context%20%3D%20New-Object%20Microsoft.SharePoint.Client.ClientContext(%24siteUrl)%3CBR%20%2F%3E%24context.Credentials%20%3D%20%24credentials%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%24Query%3D%20%22%3CVIEW%20scope%3D%22'All%22%20items%3D%22%22%3E%3CBR%20%2F%3E%3CQUERY%3E%3CBR%20%2F%3E%3CORDERBY%3E%20%3CFIELDREF%20name%3D%22'ID'%22%20ascending%3D%22'TRUE'%22%3E%3C%2FFIELDREF%3E%3C%2FORDERBY%3E%3CBR%20%2F%3E%3C%2FQUERY%3E%3CBR%20%2F%3E%3CROWLIMIT%3E3000%3C%2FROWLIMIT%3E%3CBR%20%2F%3E%3C%2FVIEW%3E%22%3CBR%20%2F%3E%24ListItems%20%3D%20Get-PnPListItem%20-List%20%24listName%20-Query%20%24Query%3CBR%20%2F%3E%3CBR%20%2F%3E%24connection%20%3D%20New-Object%20System.Data.SqlClient.SqlConnection%3CBR%20%2F%3E%24connection.ConnectionString%20%3D%20%22Server%20%3D%20%3B%20Initial%20Catalog%3D%20%3BIntegrated%20Security%3DTrue%22%3CBR%20%2F%3E%24connection.Open()%3CBR%20%2F%3E%24null%3D%22%22%3CBR%20%2F%3E%23%23Delete%20your%20command%3C%2FP%3E%3CP%3E%24SqlCmd%20%3D%20%24connection.CreateCommand()%3CBR%20%2F%3E%24SqlCmd.CommandText%20%3D%20%22delete%20from%20tbl_aa%22%3CBR%20%2F%3E%24SqlCmd.ExecuteNonQuery()%3C%2FP%3E%3CP%3Eforeach(%24a%20in%20%24ListItems)%3CBR%20%2F%3E%7B%3CBR%20%2F%3E%24sqlVALID%3D%24a%5B'ID'%5D%3CBR%20%2F%3E%24sqlVAL1%3D%24a%5B'Title'%5D%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%24SqlCmd%20%3D%20New-Object%20System.Data.SqlClient.SqlCommand(%24insert_stmt%2C%24connection)%3CBR%20%2F%3E%24SqlCmd.Connection%20%3D%20%24connection%3C%2FP%3E%3CP%3E%23%23%20Create%20your%20command%3CBR%20%2F%3E%24SqlCmd%20%3D%20%24connection.CreateCommand()%3CBR%20%2F%3E%24SqlCmd.CommandText%20%3D%20%22INSERT%20INTO%20tbl_aa(%5BProduct%20ID%5D%2C%5BProduct%20Name%5D)%3CBR%20%2F%3EVALUES%20('%24sqlVALID'%2CNULLIF('%24sqlVAL1'%2C''))%22%3CBR%20%2F%3E%24SqlCmd.ExecuteNonQuery()%20%23%3CBR%20%2F%3E%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%24connection.Close()%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1992889%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EPowerShell%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

Hello,

 

I am trying to get items from SharePoint Online using Power Shell and want to insert to SQL server. I tried the following code and it is working. But I have a problem when I put that .ps file on Task Schedule. At that time, it is not working and not insert data in database table. If manual run , it is fine.How can I do it?

 

$siteUrl = "https://-------------------";
$listName = "Test"
$userName = "username";
$password = "password";
$SecurePassword = $password | ConvertTo-SecureString -AsPlainText -Force
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $SecurePassword)
$context = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)
$context.Credentials = $credentials


$Query= "<View Scope='All Items'>
<Query>
<OrderBy> <FieldRef Name='ID' Ascending='TRUE' /></OrderBy>
</Query>
<RowLimit>3000</RowLimit>
</View>"
$ListItems = Get-PnPListItem -List $listName -Query $Query

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = "Server = ; Initial Catalog= ;Integrated Security=True"
$connection.Open()
$null=""
##Delete your command

$SqlCmd = $connection.CreateCommand()
$SqlCmd.CommandText = "delete from tbl_aa"
$SqlCmd.ExecuteNonQuery()

foreach($a in $ListItems)
{
$sqlVALID=$a['ID']
$sqlVAL1=$a['Title']


$SqlCmd = New-Object System.Data.SqlClient.SqlCommand($insert_stmt,$connection)
$SqlCmd.Connection = $connection

## Create your command
$SqlCmd = $connection.CreateCommand()
$SqlCmd.CommandText = "INSERT INTO tbl_aa([Product ID],[Product Name])
VALUES ('$sqlVALID',NULLIF('$sqlVAL1',''))"
$SqlCmd.ExecuteNonQuery() #
}

 

$connection.Close()

 

 

 

 

0 Replies