user1578080
user1578080

Reputation: 1

Code not being seen if I include it from a file

I'm writing some Powershell scripts to manage a SQL Server. When I create my SMO object, I do a test to see if I can list my databases. If so, then I assume I'm connected and call a function (via an included file) that has an Invoke-Sqlcmd that causes my database to disconnect or something. However, if I run the invoke command directly in the script, it works fine.

Looking at the provided code, my output is as follows:

I'm connected
$SqlServer is not contactable
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null 
$ErrorActionPreference = "Stop"    
Import-Module 'sqlps' -DisableNameChecking #load all of SMO

$ScriptDirectory = Split-Path -Path $MyInvocation.MyCommand.Definition - 
Parent
try {
    ("$ScriptDirectory\_HRbackup.ps1")
    ("$ScriptDirectory\_HRprinter.ps1")
    ("$ScriptDirectory\_HRbody.ps1")
} catch {
    Write-Host "Error while loading supporting PowerShell Scripts" 
} 

$ServerList = $env:COMPUTERNAME    
$SqlServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') 
$ServerList # add server list    
try {  
    $databases = $SqlServer.Databases

    if ($databases.Count -gt 0) {
        Write-Host "I am connected" 
        Backup-Html
    } else {
        $login = Get-Credential -Message 'Please prov#ide your SQL 
Credentials'

        $SqlServer.ConnectionContext.LoginSecure = $false
        $SqlServer.ConnectionContext.Login = $login.UserName
        $SqlServer.ConnectionContext.set_SecurePassword($login.Password)
        $SqlServer.ConnectionContext.connect()
    }
} catch {    
    $WarningPreference = 'continue'  
    Write-Warning "$SqlServer is not contactable"   
    #$SqlServer.ConnectionContext.Disconnect()  
} finally {    
    $SqlServer.ConnectionContext.Disconnect()    
} 

Here is the content of Backup-Html:

$Query = "select  top 5 a.server_name, a.database_name, 
 backup_finish_date, a.backup_size,
                CASE a.[type] -- Let's decode the three main types of 
 backup here
                    WHEN 'D' THEN 'Full'
                    WHEN 'I' THEN 'Differential'
                    WHEN 'L' THEN 'Transaction Log'
                    ELSE a.[type]
                END as BackupType
                -- Build a path to the backup
                ,'\\' + 
                -- lets extract the server name out of the recorded 
server and instance name
                CASE
                    WHEN patindex('%\%',a.server_name) = 0  THEN 
a.server_name
                    ELSE 
substring(a.server_name,1,patindex('%\%',a.server_name)-1)
                END 
                -- then get the drive and path and file information
                + '\' + replace(b.physical_device_name,':','$') AS 
 '\\Server\Drive\backup_path\backup_file'
                from msdb.dbo.backupset a join msdb.dbo.backupmediafamily 
 b
                    on a.media_set_id = b.media_set_id
                where a.database_name Like 'Easy%'
                order by a.backup_finish_date desc"

#Use SQLCmd to execute the query on the server
Invoke-Sqlcmd -ServerInstance $SQLServer -Query $Query

Upvotes: 0

Views: 76

Answers (0)

Related Questions