Author Posts

July 21, 2015 at 7:17 pm

Hi,

I am trying to script all Full Text Catalogs in all user databases with the below script. However the script hangs indefinitely. It does so because of this line:
$script = dir | %{$_.Script($so)
If I remove it it runs.
I believe this is due to the fact that some databases don't have any FT catalogs, so dir command does not know how to handle it.

How can I workaround this problem?

Thanks.


#DECLARE TIMESTAMP FOR THE FILES
$timestamp = Get-Date -Format yyyy-MM-dd
$Srv = 'myserver\myinstance'
SL SQLSERVER:\SQL\"$Srv"\Databases

$Query = "select name from master.sys.databases (nolock) where database_id > 5 order by name"
$DBList = Invoke-Sqlcmd -query $Query

foreach($item in $DBList) {
$DB = $item.name
$File = "02-FT-Catalogs-" + $DB + ".sql"
$so = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$so.IncludeDatabaseContext = 1
SL SQLSERVER:\SQL\"$Srv"\Databases\"$DB"\FullTextCatalogs
$script = dir | %{$_.Script($so)
}

July 21, 2015 at 7:22 pm

Sorry, worked it out, there was a missing bracket in the command:
It should be:
$script = dir | %{$_.Script($so)}