Sample Header Ad - 728x90

Error when scripting SQL View dbo.spt_values

0 votes
2 answers
199 views
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