I am using a Powershell script to generate scripts of my SQL database objects (Tables, Views, StoredProcedures etc.). It was working fine in testing and still works on 2 out of the 3 servers being included. Recently the one server began returning this error:
> Error: Exception calling "Script" with "1" argument(s): "The View
> '[dbo].[spt_values]' cannot be scripted as its data is not
> accessible.".
The Powershell script
$date_ = (date -f yyyyMMdd)
$ServerName = "ServerA" #If you have a named instance, you should put the name.
$path = "\\core.windows.net\home\Rep\SQL"+"$date_"
$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName
$IncludeTypes = @("Tables","StoredProcedures","Views","UserDefinedFunctions", "Triggers") #object you want do backup.
$ExcludeSchemas = @("sys","Information_Schema")
$so = new-object ('Microsoft.SqlServer.Management.Smo.ScriptingOptions')
$dbs=$serverInstance.Databases #you can change this variable for a query for filter yours databases.
foreach ($db in $dbs)
{
$dbname = "$db".replace("[","").replace("]","")
$dbpath = "$path"+ "\"+"$dbname" + "\"
if ( !(Test-Path $dbpath))
{$null=new-item -type directory -name "$dbname"-path "$path"}
foreach ($Type in $IncludeTypes)
{
$objpath = "$dbpath" + "$Type" + "\"
if ( !(Test-Path $objpath))
{$null=new-item -type directory -name "$Type"-path "$dbpath"}
foreach ($objs in $db.$Type)
{
if ($ExcludeSchemas -notcontains $objs.Schema )
{
$ObjName = "$objs".replace("[","").replace("]","")
$OutFile = "$objpath" + "$ObjName" + ".sql"
$objs.Script($so)+"GO" | out-file $OutFile
}
}
}
}
The View causing the error is dbo.spt_values. It's in System Views in the Master database. The same View is on my other two servers and does not cause an error. I checked the Properties and cannot find a difference between the servers that work and the one that does not. I tried restarting SQL services to see if maybe something was blocking access to it, but there was no improvement. The Powershell is being executed from a Powershell component in an SSIS package.
Asked by Don
(377 rep)
Feb 9, 2024, 07:50 PM
Last activity: Jun 29, 2025, 06:03 PM
Last activity: Jun 29, 2025, 06:03 PM