If statement not working

  • Grant Fritchey

    SSC Guru

    Points: 395166

    I know I’m doing something silly & stupid, but I can’t figure out what it is.

    Here’s the, very simple, script I’m trying to run. Our backup jobs are named differently in SQL Server 2008 than they are in 2005 & 2000, so I need to deal differently with them. So, as you can see in the script, I’m checking the MajorVersion to see if it equals 10. If so, go to work, if not, don’t. There are servers from all three versions in the ServerList.txt file. I can connect to them all… everything is working but it’s skipping the 2008 servers. None of them show up as a hit.

    What the heck is up with this thing?

    [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | out-null

    foreach ($svr in Get-Content “c:\Scripts\ServerList.txt”)

    {

    $srv=New-Object “Microsoft.SqlServer.Management.Smo.Server” “$svr”

    if ($srv.Information.MajorVersion -eq “10”)

    {

    $srv.JobServer.Jobs | Where-Object { $_.Name -eq “Database Services Daily Compressed Backups”} | Format-Table name,lastrunoutcome,$svr.tostring() -autosize

    }

    else

    {

    #$srv.jobserver.jobs.Item(“Database_Services_30Min_LS_Log_Backups”).Start()

    $srv.JobServer.Jobs | Where-Object {$_.Name -eq “Database Services Daily LS_Backups”} | Format-Table name,lastrunoutcome,$svr.tostring() -autosize

    #| Where-Object {$_.Start(} #| where-object {$_.lastrunoutcome -eq “Failed” -and $_.isenabled -eq $TRUE} | format-table name,lastrunoutcome,lastrundate -autosize

    }

    }

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jeffrey Williams 3188

    SSC Guru

    Points: 87879

    The property you are trying to get to does not exist. Use $srv.VersionMajor instead of $srv.Information.MajorVersion.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Grant Fritchey

    SSC Guru

    Points: 395166

    I’m sorry. You’re right. That was just a typo I made after I copied & pasted. I’m using the correct property or I’d get an error. It’s just not returning the proper value and working through the IF logic and I don’t understand why.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jeffrey Williams 3188

    SSC Guru

    Points: 87879

    I tested that against one of my 2008 systems and it returned “10”. Try adding Write-Host $srv.VersionMajor before the if statement and see what is being returned from that server.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Grant Fritchey

    SSC Guru

    Points: 395166

    I’m entirely unsure of what I changed, but it’s working now. I feel like an idiot because it should have worked. It looks good now… thanks for the help.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jeffrey Williams 3188

    SSC Guru

    Points: 87879

    Been there done that – just remember, this is a ‘scripting’ language and as such does not tell you when you have made a typo. I have seen where it looks absolutely right – but I had a small typo and it just returns nothing.

    The other thing I noticed is that referencing a property that does not exist does not generate an error. It just doesn’t return a value – which can cause all kinds of fun debugging issues.

    Glad you got it working.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply