Script Databases

  • Grant Fritchey

    SSC Guru

    Points: 396617

    There's a great article over at Simple-Talk[/url] on how to script out database objects using PowerShell. I decided to try it out.

    I can't get it to work. I keep getting an error. It doesn't output to the error log either. I had to discover it by running the script in debug mode in PowerGUI.

    It fails at this command

    $tableset = get-childitem $path-ErrorAction stop |

    with this error:

    Cannot call method. The provider does not support the use of filters.

    System.Management.Automation.PSNotSupportedException: Cannot call method. The provider does not support the use of filters.

    at System.Management.Automation.Provider.CmdletProvider.set_Context(CmdletProviderContext value)

    at System.Management.Automation.Provider.NavigationCmdletProvider.MakePath(String parent, String child, CmdletProviderContext context)

    at System.Management.Automation.SessionStateInternal.MakePath(CmdletProvider providerInstance, String parent, String child, CmdletProviderContext context)

    Any guidance?

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Grant Fritchey

    SSC Guru

    Points: 396617

    And while I'm on it, I've run Powershell scripts against SQL Server 2008 before. I created a silly little test script for placing a load on a copy of AdventureWorks and it ran just fine. I'm pretty sure I've got PowerShell installed & configured correctly.

    Here's the successful script:

    $val = 0;

    while ($val -lt 255)

    {$val++;

    Invoke-sqlcmd -Server "Myserver" -Database "AdventureWorks2008" -Query "SELECT * FROM Person.Address AS a WHERE city LIKE '' + CHAR($val) + '%'";

    Invoke-sqlcmd -Server "Myserver" -Database "AdventureWorks2008" -Query "SELECT * FROM production.Product AS p WHERE p.ProductNumber LIKE '' + CHAR($val) + '%'";

    $val2 = 59;

    while ($val2 -gt 57)

    {$val2--;

    Invoke-sqlcmd -Server "Myserver" -Database "AdventureWorks2008" -Query "SELECT * FROM Sales.SalesOrderHeader AS soh WHERE soh.SalesOrderNumber LIKE 'SO' + CAST($val2 AS VARCHAR) + '%'";

    }

    }

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Rajib Bahar

    SSC Eights!

    Points: 957

    Hello,

    I believe that statement is spread over two lines...

    $tableset =get-childitem $path -ErrorAction stop |

    where-object {$_.displayname -like $tables}

    The statement

    $tableset =get-childitem $path -ErrorAction stop |

    by itself won't work and caused issue at my end as well.

  • Grant Fritchey

    SSC Guru

    Points: 396617

    Ah... perfect. Thanks.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply