Copy-DBAAgentJob Failing Using "servername\instancename,port" as Destination

[TheSQLGuru]

Verified issue does not already exist?

Yes

What error did you receive?

WARNING: [13:13:33][Copy-DbaAgentJob] Error occurred while establishing connection to AAEXTTESTDB | Object reference not set to an instance of an object.

Steps to Reproduce

# provide your command(s) executed pertaining to dbatools
# please include variable values (redacted or fake if needed) for reference

Copy-DbaAgentJob -Destination "servername\instancename,portnumber" -DisableOnDestination -Source aatestdb

Are you running the latest release?

Yes

Other details or mentions

I note that the server I am calling this from is Win 2012.

If I use -EnableException, here is the message:

Object reference not set to an instance of an object. At C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\dbatools\internal\functions\flowcontrol\Stop-Function.ps1:252 char:9

•     throw $records[0]
  

•     ~~~~~~~~~~~~~~~~~
  

  • CategoryInfo : OperationStopped: (aatestdb:String) [], Exception
  • FullyQualifiedErrorId : dbatools_Copy-DbaAgentJob

What PowerShell host was used when producing this error

Windows PowerShell ISE (powershell_ise.exe)

PowerShell Host Version

Name Value

---

PSVersion 3.0
WSManStackVersion 3.0
SerializationVersion 1.1.0.1
CLRVersion 4.0.30319.42000
BuildVersion 6.2.9200.22199
PSCompatibleVersions {1.0, 2.0, 3.0}
PSRemotingProtocolVersion 2.2

SQL Server Edition and Build number

source: 11.0.6251.0 destination: 13.0.1745.2

.NET Framework Version

.NET Framework 4.7.3468.0

[andreasjordan]

The error "Error occurred while establishing connection to AAEXTTESTDB" looks like it is coming from the source and not the destination. Is that correct? Is $jobs = Get-DbaAgentJob -SqlInstance aatestdb working?

[TheSQLGuru]

this works, and lists out all 13 agent jobs on aaexttestdb: Get-DbaAgentJob -SqlInstance aaexttestdb

this still throws the given error: Copy-DbaAgentJob -Destination "atsqlext\aaexttestdb,14340" -Source aaexttestdb

WARNING: [13:12:18][Copy-DbaAgentJob] Error occurred while establishing connection to aaexttestdb | Object reference not set to an instance of an object.

that same step also fails if I do a full migration with Start-DBAMigration

[andreasjordan]

Have you tried to first create the connections and then use them?

$source = Connect-DbaInstance -SqlInstance aaexttestdb
$destination = Connect-DbaInstance -SqlInstance "atsqlext\aaexttestdb,14340"
Copy-DbaAgentJob -Destination $destination -Source $source

[TheSQLGuru]

Same error

[TheSQLGuru]

Here is the top section of code:

begin {
    if ($Source) {
        try {
            $InputObject = Get-DbaAgentJob -SqlInstance $Source -SqlCredential $SourceSqlCredential -Job $Job -ExcludeJob $ExcludeJob
        } catch {
            Stop-Function -Message "Error occurred while establishing connection to $Source" -Category ConnectionError -ErrorRecord $_ -Target $Source
            return
        }
    }

So it seems it hits the TRY to set $InputObject to the get-dbaagentjob call. if I run that exact call it is successful!! WTF?

[TheSQLGuru]

Could it be related to this:

[DbaInstanceParameter]$Source

?? That seems to be the only thing that I am not actually testing by calling the Get-DBAAgentJob line explicitly

[andreasjordan]

This is the normal way in all commands. Will try to reproduce in my lab tomorrow...

[andreasjordan]

Ok, I read the original issue again. You run this with PowerShell 3 on a windows server 2012? I don't have a lab for that, with current PowerShell and OS the command runs fine. Do you have a box with current OS level where you can install dbatools and try again? If not, I'm not able to help you with this issue.

[TheSQLGuru]

I do not have access to an environment with a higher level powershell/OS that can execute the necessary operations to perform this migration.

I will stop trying to use dbatools for this evolution, and switch to other means.

Thanks for your time!

[andreasjordan]

@potatoqualitee - Do you have a windows server 2012 with PS3 to test this? I have no idea where the problem is.

[potatoqualitee]

I do and will check, well I have win7 with psv3.

@TheSQLGuru can you also try without the instance name and just use the port? from my testing and experience, seems like it's not required

[potatoqualitee]

PS C:\Users\cl.AD> Copy-DbaAgentJob -Source sql2005 -Destination "sqlcs\nothing,1433"
WARNING: [22:35:32][Copy-DbaAgentJob] Error occurred while establishing connection to sql2005 | Object reference not
set to an instance of an object.
PS C:\Users\cl.AD> Get-DbatoolsError


CategoryInfo          : OperationStopped: (sql2005:PSObject) [Write-Error], Exception
ErrorDetails          : Object reference not set to an instance of an object.
Exception             : System.Exception: Object reference not set to an instance of an object. --->
                        System.NullReferenceException: Object reference not set to an instance of an object.
                           at
                        System.Management.Automation.PipelineOps.CheckAutomationNullInCommandArgumentArray(Object[]
                        objArray)
                           at System.Management.Automation.Interpreter.FuncCallInstruction`2.Run(InterpretedFrame
                        frame)
                           at
                        System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame
                        frame)
                           at
                        System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame
                        frame)
                           --- End of inner exception stack trace ---
FullyQualifiedErrorId : dbatools_Copy-DbaAgentJob,Stop-Function
InvocationInfo        : System.Management.Automation.InvocationInfo
PipelineIterationInfo : {0, 1}
PSMessageDetails      :
ScriptStackTrace      : at Stop-Function, C:\Users\cl.AD\Documents\WindowsPowerShell\Modules\dbatools\internal\function
                        s\flowcontrol\Stop-Function.ps1: line 260
                        at Copy-DbaAgentJob<Begin>,
                        C:\Users\cl.AD\Documents\WindowsPowerShell\Modules\dbatools\functions\Copy-DbaAgentJob.ps1:
                        line 112
                        at <ScriptBlock>, <No file>: line 1
TargetObject          : sql2005

i can repo but its not even failing on the named instance, it's failing on any. this'll be rough but I'll look when i get a moment. Till then you can export then import with our export commands, perhaps

[potatoqualitee]

Works:

[Microsoft.SqlServer.Management.Smo.Agent.Job[]]$InputObject = $InputObject = Get-DbaAgentJob -SqlInstance sql2005 -SqlCredential $null -Job $Job -ExcludeJob $ExcludeJob

Also works

[DbaInstanceParameter]$Source = "sql2005"
[Microsoft.SqlServer.Management.Smo.Agent.Job[]]$InputObject = Get-DbaAgentJob -SqlCredential $null -SqlInstance $Source -Job $Job -ExcludeJob $ExcludeJob

I'll probably have to ask twitter, this i weird.

[potatoqualitee]

btw @andreasjordan - if youre ever interested, the win7 azurevm is ideal for psv3 testing

[TheSQLGuru]

OOOH! I wasn't even aware of the pipe-able Export-DBAScript function!

I'm sorry I broke things, but I am happy that it wasn't me doing something stoopid! 🙃

Best of luck finding a root cause and coming up with an easy-to-implement fix!

Kevin G. Boles SQL Server Consultant SQL Server MVP 2007 - 2012 Indicium Resources, Inc. @TheSQLGuru

On Mon, Jan 17, 2022 at 3:38 PM Chrissy LeMaire @.***> wrote:

PS C:\Users\cl.AD> Copy-DbaAgentJob -Source sql2005 -Destination "sqlcs\nothing,1433" WARNING: [22:35:32][Copy-DbaAgentJob] Error occurred while establishing connection to sql2005 | Object reference not set to an instance of an object. PS C:\Users\cl.AD> Get-DbatoolsError

CategoryInfo : OperationStopped: (sql2005:PSObject) [Write-Error], Exception ErrorDetails : Object reference not set to an instance of an object. Exception : System.Exception: Object reference not set to an instance of an object. ---> System.NullReferenceException: Object reference not set to an instance of an object. at System.Management.Automation.PipelineOps.CheckAutomationNullInCommandArgumentArray(Object[] objArray) at System.Management.Automation.Interpreter.FuncCallInstruction`2.Run(InterpretedFrame frame) at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame) at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame) --- End of inner exception stack trace --- FullyQualifiedErrorId : dbatools_Copy-DbaAgentJob,Stop-Function InvocationInfo : System.Management.Automation.InvocationInfo PipelineIterationInfo : {0, 1} PSMessageDetails : ScriptStackTrace : at Stop-Function, C:\Users\cl.AD\Documents\WindowsPowerShell\Modules\dbatools\internal\function s\flowcontrol\Stop-Function.ps1: line 260 at Copy-DbaAgentJob<Begin>, C:\Users\cl.AD\Documents\WindowsPowerShell\Modules\dbatools\functions\Copy-DbaAgentJob.ps1: line 112 at <ScriptBlock>, <No file>: line 1 TargetObject : sql2005

i can repo but its not even failing on the named instance, it's failing on any. this'll be rough but I'll look when i get a moment. Till then you can export then import with our export commands, perhaps

— Reply to this email directly, view it on GitHub https://github.com/dataplat/dbatools/issues/8037#issuecomment-1014903804, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAVGSY3XPBYCMJ6CLBCD253UWSD4PANCNFSM5K23VGPQ . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

You are receiving this because you were mentioned.Message ID: @.***>

[potatoqualitee]

The Export scripts are awesome! You should check out Export-DbaInstance, it's fabulous for Disaster Recovery https://dbatools.io/dr

Also, I figured out that this works in PSv3, so weird.

PS> Get-DbaAgentJob -SqlInstance sql2005 | Copy-DbaAgentJob -Destination sqlcs

Type      Name    Status  Notes
----      ----    ------  -----
Agent Job testjob Skipped Job is dependent on login AD\cl

[github-actions[bot]]

🚧🚨 This issue is being marked as stale due to 90 days of inactivity. If you would like this issue to remain open:

• Verify the issue/bug is reproduced in the latest version of the module
• Verify the environmental info provided is still accurate
• Add any additional steps you followed to reproduce if necessary 🚨🚧 ⌛️ This issue will be closed in 30 days ⌛️

[andreasjordan]

I am closing this issue now.