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()
