0

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_"
 
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$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.

9
  • Are you able to run SELECT * FROM dbo.spt_values on the problematic server?
    – J.D.
    Commented Feb 9 at 20:05
  • I am. I compared the row counts to a 'good' server and the row counts are the same on both servers.
    – Don
    Commented Feb 9 at 20:36
  • Are you scripting the master database? On the servers where the error does not occur, is the view actually scripted out? Does this query return 1 on all 3 servers? USE mastere;SELECT OBJECTPROPERTY(OBJECT_ID('dbo.spt_values'), 'IsMSShipped');
    – Dan Guzman
    Commented Feb 9 at 23:59
  • 1
    Why do you need to script this anyway? Commented Feb 11 at 12:12
  • 1
    This is an ms shipped object so you don't need to script it out yourself as source code Commented Feb 13 at 2:18

2 Answers 2

0

If none of the attempts workout, may be you could try drop and re-create the view and see if that is going to resolve.

#Script to drop and recreate the view spt_values
$sql = @"
    USE master
    GO
    DROP VIEW IF EXISTS [dbo].[spt_values]
    GO

    CREATE VIEW dbo.spt_values
    AS
    SELECT name COLLATE DATABASE_DEFAULT AS name,
            number,
            type COLLATE DATABASE_DEFAULT AS type,
            low,
            high,
            status
    FROM sys.spt_values;
    GO

    EXEC sp_MS_marksystemobject 'spt_values';
    GO
    GRANT SELECT ON spt_values TO PUBLIC;
    GO
 "@

 #Make a DAC connection to the problematic instance.
 $c = Connect-DbaInstance -SqlInstance 'YourSqlInstanceName' -DedicatedAdminConnection

 #Execute the script 
 $c | Invoke-DbaQuery -Query $sql

 #Close the connection
 $c.ConnectionContext.Disconnect()

3
  • @ S.D. - tried the drop and create w/ GRANT SELECT as suggested, but same result. Also tried after executing the Marksystemobject with same result.
    – Don
    Commented Feb 13 at 0:52
  • Interesting, that data access error you are getting makes me think of checking/comparing the permissions on the account is being used for scripting. Does the account has same level of permissions on successful instance vs unsuccessful instance?
    – S.D.
    Commented Feb 14 at 3:52
  • 1
    I think you are correct. I've looked through the permissions on SQL server and they appear the same. However, I deployed the job to the actual server and it runs fine on the server itself. I did notice an additional authentication prompt for this server that does not occur on the others. I'm thinking it is a network access issue. But I have resolved by running the job on the server itself.
    – Don
    Commented Feb 15 at 19:58
0

I believe it is a network related issue with permissions. Have not been able to identify the root cause, but resolved by deploying and running from the server itself. I've recently bumped into another issue on this server specifically that requires me to elevate permissions when trying to map to a network drive.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.