none
PowerShell script using Get-ChildItem works with Invoke-Sqlcmd when called from Visual Studio Code but not from SQL Server RRS feed

  • Question

  • This works in Visual Studio Code:

    $dir = "\\FileServer\Folder"
    $server = "SqlServerInstance"
    $database = "MyDb"
    $databasePattern = $database + "*.bak"
    $latestFile = Get-ChildItem -Path $dir $databasePattern | Sort-Object LastAccessTime -Descending | Select-Object -First 1
    $backupLocation = $dir + '\' + $latestFile.name
    $dataFileLocation = "Z:\SQL2016\Data\MyDb.mdf"
    $logFileLocation = "Z:\SQL2016\Logs\Files\MyDb.ldf"
    $sql = @"
    USE master

    ALTER DATABASE
    $database SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    RESTORE DATABASE
    $database
    FROM DISK = N'$backupLocation'
    WITH FILE = 1,
         MOVE N'MyDb' TO N'$dataFileLocation',
         MOVE N'MyDb_log' TO N'$logFileLocation',
         NOUNLOAD, REPLACE, STATS = 5
    ALTER DATABASE $database SET MULTI_USER
    "@
    Invoke-Sqlcmd -ServerInstance $server -Query $sql -querytimeout 0

    From a SQL Server PowerShell prompt, it fails:

    Get-ChildItem : Cannot call method. The provider does not support the use of filters.
    At line:5 char:15
    + $latestFile = Get-ChildItem -Path $dir $databasePattern | Sort-Object ...
    +               ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotImplemented: (:) [Get-ChildItem], PSNotSupportedException
        + FullyQualifiedErrorId : NotSupported,Microsoft.PowerShell.Commands.GetChildItemCommand

    Executing the same code from a SQL Server job fails with:

    A job step received an error at line 5 in a PowerShell script. The corresponding line is '$latestFile = Get-ChildItem -Path $dir $databasePattern | Sort-Object LastAccessTime -Descending | Select-Object -First 1  '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot call method. The provider does not support the use of filters.  '.  Process Exit Code -1.  The step failed.

    If Get-ChildItem is removed and the file hardcoded, it will work both from a SQL Server PowerShell command prompt and as a job.

    $latestFile = "\\FileServer\Folder\MyDb.bak"

    How can the SQL Server environment be changed to allow all of the PowerShell commands to work? Pattern matching to find the most recent file is needed, perhaps using a different technique, so that the script works every day without modification. The backup file name changes every day making hardcoding unacceptable.


    Thursday, June 13, 2019 6:47 PM

Answers

  • Then SQL must not treat $_.Name as a string when doing the compare. I added .ToString to force it. Try this.

    "File names we found"
    Get-ChildItem Microsoft.PowerShell.Core\FileSystem::$dir | foreach {$_.Name.ToString()}
    "File names we found that match $databasePattern"
    Get-ChildItem Microsoft.PowerShell.Core\FileSystem::$dir | where-Object {$_.Name.ToString() -like $databasePattern} | foreach {$_.name.ToString()}
    $latestFile = Get-ChildItem Microsoft.PowerShell.Core\FileSystem::$dir | where-Object {$_.Name.ToString() -like $databasePattern} | Sort-Object LastAccessTime -Descending | Select-Object -First 1
    
    
    

    • Proposed as answer by MotoX80 Sunday, June 16, 2019 2:46 AM
    • Marked as answer by John Paul Cook Sunday, June 16, 2019 2:48 AM
    Saturday, June 15, 2019 2:14 PM

All replies

  • Try the answer from this post.

    https://stackoverflow.com/questions/39102234/powershell-invoke-sqlcmd-get-childitem-cannot-call-method-the-provider-does-n

    • Edited by MotoX80 Thursday, June 13, 2019 6:57 PM
    Thursday, June 13, 2019 6:57 PM
  • I have read that post several times this week. I greatly appreciate the time and effort the respondents put into sharing their knowledge. I respect them as technically capable individuals who are generous with their time.

    I still am unsuccessful so I posted here instead of there hoping for help from others.

    Thursday, June 13, 2019 7:06 PM
  • Do you get the same error if you use this for line 5?

    $latestFile = Get-ChildItem Microsoft.PowerShell.Core\FileSystem::$dir -Filter $databasePattern | Sort-Object LastAccessTime -Descending | Select-Object -First 1
    

    I apologize, being retired I no longer have access to SQL server to test this. That suggestion works for me when I query my temp directory.

     

    Thursday, June 13, 2019 7:31 PM
  • Your code works from a regular PowerShell command prompt.

    When executed from a SQL Server PowerShell prompt, it fails with the following error:

    Invoke-Sqlcmd : Cannot open backup device '\\FileServer\Folder\'. Operating system error 123(The filename, directory name, or volume label syntax is incorrect.).

    The output looks like this:

    RESTORE DATABASE MyDb
    FROM DISK = N'\\FileServer\Folder\'

    I need Get-ChildItem to work with wildcards from a SQL Server PowerShell prompt.


    Thursday, June 13, 2019 7:36 PM
  • Try letting get-childitem read all the file names and then filter.

    $latestFile = Get-ChildItem Microsoft.PowerShell.Core\FileSystem::$dir | where-Object {$_.Name -like $databasePattern} | Sort-Object LastAccessTime -Descending | Select-Object -First 1
    

    Thursday, June 13, 2019 8:26 PM
  • It works from a regular PowerShell prompt and finds the latest file.

    From a SQL Server PowerShell prompt, it does not find the latest file or any file.

    The output looks like this:

    RESTORE DATABASE MyDb
    FROM DISK = N'\\FileServer\Folder\'

    Thursday, June 13, 2019 8:32 PM
  • I see that you added the error 123. At my prior employer we configured the SQL Agent service to run as an Active Directory account. Do you do the same? (Or can you configure the job to run as an AD account?) Does that account have access to \\server\share?    
    Thursday, June 13, 2019 8:32 PM
  • A proxy has been tried and didn't help. With a hardcoded path and file name specified, the job works just fine running with a SQL Server Agent account. That proves it has sufficient privilege. Change it to use Get-ChildItem and it fails. The problem can clearly be seen when executing all of the various code samples from a SQL Server PowerShell prompt. Get-ChildItem isn't returning anything when invoked from SQL Server.

    Thursday, June 13, 2019 8:41 PM
  • Can you read from the C: drive? Maybe test access like this? Using your names of course. 

    $dir = 'C:\Windows'
    $test1 = Get-ChildItem $dir
    "Test 1 found {0} objects." -f $test1.count 
    $test2 = Get-ChildItem Microsoft.PowerShell.Core\FileSystem::$dir
    "Test 2 found {0} objects." -f $test2.count 
    $dir = '\\server\share'
    $test3 = Get-ChildItem $dir
    "Test 3 found {0} objects." -f $test3.count 
    $test4 = Get-ChildItem Microsoft.PowerShell.Core\FileSystem::$dir
    "Test 4 found {0} objects." -f $test4.count
    Test-NetConnection -ComputerName server -CommonTCPPort SMB
     

     
    Thursday, June 13, 2019 8:59 PM
  • From native PowerShell

    $test1 = Get-ChildItem $dir
    "Test 1 found {0} objects." -f $test1.count
    $test2 = Get-ChildItem Microsoft.PowerShell.Core\FileSystem::$dir
    "Test 2 found {0} objects." -f $test2.count
    $dir = '\\FileServer\Folder'
    $test3 = Get-ChildItem $dir
    "Test 3 found {0} objects." -f $test3.count
    $test4 = Get-ChildItem Microsoft.PowerShell.Core\FileSystem::$dir
    "Test 4 found {0} objects." -f $test4.count
    Test-NetConnection -ComputerName FileServer -CommonTCPPort SMB
    Test 1 found 123 objects.
    Test 2 found 123 objects.
    Test 3 found 16 objects.
    Test 4 found 16 objects.

    ComputerName     : FileServer
    RemoteAddress    : 10.123.1.234
    RemotePort       : 445
    InterfaceAlias   : Ethernet 2
    SourceAddress    : 10.145.61.67
    TcpTestSucceeded : True

    From SQL Server PowerShell

    PS SQLSERVER:\SQL\SqlServerInstance\Databases\MyDb> $dir = 'C:\Windows'
    PS SQLSERVER:\SQL\SqlServerInstance\Databases\MyDb> $test1 = Get-ChildItem $dir
    PS SQLSERVER:\SQL\SqlServerInstance\Databases\MyDb> "Test 1 found {0} objects." -f $test1.count
    Test 1 found 123 objects.
    PS SQLSERVER:\SQL\SqlServerInstance\Databases\MyDb> $test2 = Get-ChildItem Microsoft.PowerShell.Core\FileSystem::$dir
    PS SQLSERVER:\SQL\SqlServerInstance\Databases\MyDb> "Test 2 found {0} objects." -f $test2.count
    Test 2 found 123 objects.
    PS SQLSERVER:\SQL\SqlServerInstance\Databases\MyDb> $dir = '\\FileServer\Folder'
    PS SQLSERVER:\SQL\SqlServerInstance\Databases\MyDb> $test3 = Get-ChildItem $dir
    Get-ChildItem : Cannot find path '\\FileServer\Folder' because it does not exist.
    At line:1 char:10
    + $test3 = Get-ChildItem $dir
    +          ~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : ObjectNotFound: (\\FileServer\Folder:String) [Get-ChildItem], ItemNotFoundException
        + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetChildItemCommand
    PS SQLSERVER:\SQL\SqlServerInstance\Databases\MyDb> "Test 3 found {0} objects." -f $test3.count
    Test 3 found 0 objects.
    PS SQLSERVER:\SQL\SqlServerInstance\Databases\MyDb> $test4 = Get-ChildItem Microsoft.PowerShell.Core\FileSystem::$dir
    PS SQLSERVER:\SQL\SqlServerInstance\Databases\MyDb> "Test 4 found {0} objects." -f $test4.count
    Test 4 found 9 objects.
    PS SQLSERVER:\SQL\SqlServerInstance\Databases\MyDb> Test-NetConnection -ComputerName FileServer -CommonTCPPort SMB

    ComputerName     : FileServer
    RemoteAddress    : 10.123.1.234
    RemotePort       : 445
    InterfaceAlias   : Ethernet 2
    SourceAddress    : 10.145.61.67
    TcpTestSucceeded : True


    Thursday, June 13, 2019 9:15 PM
  • Great! That proves that you can read the files from SQL.

    So I would expect either of these commands to work.

     
    $latestFile = Get-ChildItem $dir | where-Object {$_.Name -like $databasePattern} | Sort-Object LastAccessTime -Descending | Select-Object -First 1
    $latestFile = Get-ChildItem Microsoft.PowerShell.Core\FileSystem::$dir | where-Object {$_.Name -like $databasePattern} | Sort-Object LastAccessTime -Descending | Select-Object -First 1
    

    If it still doesn't find a file, then maybe there is a problem matching the names. 

    "File names we found"
    Get-ChildItem $dir | foreach {$_.Name}
    "File names we found that match $databasePattern"
    Get-ChildItem $dir | where-Object {$_.Name -like $databasePattern} | foreach {$_.name}
    $latestFile = Get-ChildItem $dir | where-Object {$_.Name -like $databasePattern} | Sort-Object LastAccessTime -Descending | Select-Object -First 1
    

     
    Thursday, June 13, 2019 9:38 PM
  • It doesn't prove that. The post was made in two chunks.

    The first chunk shows it working from regular PowerShell.

    The second chunk shows it failing from SQL Server PowerShell.

    Thursday, June 13, 2019 9:47 PM
  • You posted the first chunk which is what I saw. You had no explanation of how you tested it. Looked like a success to me. 

    Since test 4 found 9 objects, try it like this.

    "File names we found"
    Get-ChildItem Microsoft.PowerShell.Core\FileSystem::$dir | foreach {$_.Name}
    "File names we found that match $databasePattern"
    Get-ChildItem Microsoft.PowerShell.Core\FileSystem::$dir | where-Object {$_.Name -like $databasePattern} | foreach {$_.name}
    $latestFile = Get-ChildItem Microsoft.PowerShell.Core\FileSystem::$dir | where-Object {$_.Name -like $databasePattern} | Sort-Object LastAccessTime -Descending | Select-Object -First 1
    

    Thursday, June 13, 2019 10:20 PM
  • The following output appears after executing from a regular PowerShell command prompt:

    File names we found that match MyDb*.bak
    MyDb_20190613.bak
    MyDb_20190614.bak

    If executed from a SQL Server PowerShell prompt, those statements do not appear.

    Friday, June 14, 2019 9:05 PM
  • What about "File names we found"? Did that list off all of the files in the directory? 

    Run these commands from a SQL Powershell prompt and let me know the output.

    'mydb1.bak' -like '*.bak' 'mydb1.bak' -like 'mydb*.bak' 'mydb1.bak' -match '.bak' 'mydb1.bak' -match 'mydb' 'mydb1.bak' -match '(mydb).*.*(\.bak)' 'MYDB1.bak' -match '(mydb).*.*(\.bak)'


    Saturday, June 15, 2019 3:11 AM
  • File names we found always appears and is followed by all of the files. This is true even at a SQL Server PowerShell command prompt.

    PS C:\Users\CookJ> 'mydb1.bak' -like '*.bak'
    'mydb1.bak' -like 'mydb*.bak'
    'mydb1.bak' -match '.bak'
    'mydb1.bak' -match 'mydb'
    'mydb1.bak' -match '(mydb).*.*(\.bak)'
    'MYDB1.bak' -match '(mydb).*.*(\.bak)'
    True
    True
    True
    True
    True
    True

    PS SQLSERVER:\SQL\MyServer\Databases\MyDb> 'mydb1.bak' -like '*.bak'
    >> 'mydb1.bak' -like 'mydb*.bak'
    >> 'mydb1.bak' -match '.bak'
    >> 'mydb1.bak' -match 'mydb'
    >> 'mydb1.bak' -match '(mydb).*.*(\.bak)'
    >> 'MYDB1.bak' -match '(mydb).*.*(\.bak)'
    True
    True
    True
    True
    True
    True

    Saturday, June 15, 2019 3:19 AM
  • Then SQL must not treat $_.Name as a string when doing the compare. I added .ToString to force it. Try this.

    "File names we found"
    Get-ChildItem Microsoft.PowerShell.Core\FileSystem::$dir | foreach {$_.Name.ToString()}
    "File names we found that match $databasePattern"
    Get-ChildItem Microsoft.PowerShell.Core\FileSystem::$dir | where-Object {$_.Name.ToString() -like $databasePattern} | foreach {$_.name.ToString()}
    $latestFile = Get-ChildItem Microsoft.PowerShell.Core\FileSystem::$dir | where-Object {$_.Name.ToString() -like $databasePattern} | Sort-Object LastAccessTime -Descending | Select-Object -First 1
    
    
    

    • Proposed as answer by MotoX80 Sunday, June 16, 2019 2:46 AM
    • Marked as answer by John Paul Cook Sunday, June 16, 2019 2:48 AM
    Saturday, June 15, 2019 2:14 PM
  • Your latest suggestion worked at a SQL Server PowerShell prompt. Thank you. I will later post how it works when called from a job.

    PS SQLSERVER:\SQL\MyServer\Databases\MyDb> ...snipped for brevity...
    >>
    File names we found
    DoNotWantThis_20180101.bak
    MyDb_20190613.bak
    MyDb_20190614.bak
    NotThisOne_20181212.bakFile names we found that match MyDb*.bak
    MyDb_20190613.bak
    MyDb_20190614.bak

    Saturday, June 15, 2019 4:03 PM
  • Good to see that you sorted out your issue. But any chance you are looking for a way to restore databases from a folder, try the below option as well.

    https://sqlzealots.com/2016/05/25/t-sql-script-to-restore-backup-files-from-a-folder-in-sql-server/


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]  [LinkedIn]

    Saturday, June 15, 2019 6:33 PM
  • Thank you, MotoX80, for your enduring help. The SQL Server job successfully backed up the database using the PowerShell script modified according to your instructions.

    Yes, SQLZealots, I am restoring databases from a folder. Your well thought out script uses xp_cmdshell which conflicts with corporate security policies.


    Saturday, June 15, 2019 8:52 PM
  • You're welcome. Glad to help you and expand my experience. 
    Sunday, June 16, 2019 1:17 PM