mysql - 从计划任务执行 Powershell 脚本不会在数据库中创建表

Question

我正在做一个小项目，我想执行一个执行另一个脚本的 powershell 脚本。当我自己执行主脚本时，它可以正常工作，删除表并创建新的，而当我执行“计划任务”脚本时，它会更新第一个表（播放列表）并且不会删除它，只是将其内容加倍. 而第二张表（歌曲）它甚至没有删除或放入任何内容。

这是我的计划任务脚本：

$Trigger= New-ScheduledTaskTrigger -At 04:18pm -Daily
$User= "NT AUTHORITY\SYSTEM"
$Action= New-ScheduledTaskAction -Execute "PowerShell.exe" -Argument 
"C:\Users\Matej\Desktop\getAllData.ps1"
Register-ScheduledTask -TaskName "StartupScript_PS" -Trigger $Trigger -User $User - 
Action $Action -RunLevel Highest –Force

这是我的主要脚本：

Invoke-SqlCmd -ServerInstance 'localhost' -inputfile 
C:\Users\Matej\Desktop\SQLQuery.sql
function insertToDb($line, $PlaylistId){

$position = $line.ToString().Split(',').Get(0)
$songName = $line.ToString().Split(',').Get(1)
$artistName = $line.ToString().Split(',').Get(2)
$link = $line.ToString().Split(',').Get(4)

$PlaylistId = $PlaylistId.Replace('"',"")
$songName = $songName.Replace('"',"")
$artistName = $artistName.Replace('"',"")
$songName = $songName.Replace("'","")
$artistName = $artistName.Replace("'","")

$convertedId = [int]($PlaylistId)

$insertSongQuery = "
    INSERT INTO SONG
        (position,name,artist,link,playlist_id)
    VALUES
        ('$position',N'$songName',N'$artistName','$link','$convertedId')
    GO
"
Write-Output $songName
if($line.ToString().Split(',').Length -eq 5)
{
 Invoke-SQLcmd -ServerInstance 'localhost' -query $insertSongQuery -U vikiUser - 
 Password 1234 -Database ProjectDb
}

}

function getPlaylist{
$shortName = $PSItem.ToString().Split(',').Get(0);
$name = $PSItem.ToString().Split(',').Get(1);
$insertquery = "
 INSERT INTO PLAYLIST
       (name, type) 
 VALUES 
       ('$name','country') 
 GO 
"
$getPlaylistIdQuery = "
    SELECT ID
    FROM PLAYLIST 
    WHERE PLAYLIST.name = '$name'
    GO
 "
 Invoke-SQLcmd -ServerInstance 'localhost' -query $insertquery -U vikiUser -Password 
 1234 -Database ProjectDb 
 $convertedid =(Invoke-SQLcmd -ServerInstance 'localhost' -query $getPlaylistIdQuery - 
 U 
 vikiUser -Password 1234 -Database ProjectDb )| ConvertTo-Csv | Select-Object -Skip 2


 (Invoke-WebRequest -Uri 
 https://spotifycharts.com/regional/$shortName/daily/latest/download).Content > 
 out.csv

 cat out.csv | Select-Object -Skip 2 | select -First 15 | ForEach-Object {insertToDb 
  $_ 
 $convertedid}


 }


 cat C:\Users\Matej\Desktop\countries.txt | ForEach-Object {getPlaylist($_)}

这是我的 SQL 脚本：

IF DB_ID('ProjectDb') IS NOT NULL
BEGIN
use ProjectDb;
DROP TABLE SONG;
DROP TABLE PLAYLIST;
END

ELSE
BEGIN
CREATE DATABASE ProjectDb;
END

 GO

    CREATE TABLE PLAYLIST(
    ID int IDENTITY(0,1) PRIMARY KEY,
    name nvarchar(255),
    type nvarchar(255),
     );
    
     CREATE TABLE SONG(
    ID int IDENTITY(0,1) PRIMARY KEY,
    position nvarchar(255),
    name nvarchar(255),
    artist nvarchar(255),
    link nvarchar(255),
    playlist_id int NOT NULL FOREIGN KEY REFERENCES PLAYLIST(ID)
    );
    
    

标签： mysqlsqldatabasepowershell