Script Databases

Question

Post reply

Script Databases

Grant Fritchey

SSC Guru

Points: 397721
More actions
October 22, 2008 at 8:42 am

#383190

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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

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

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Rajib Bahar Ten Centuries Points: 1007 More actions · Answer 2

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.

Rajib Bahar
http://www.rajib-bahar.com
http://www.twitter.com/rajib2k5
http://www.youtube.com/icsql

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software