Step template : SQL - Execute SQL Script with SQL or Windows Authentication throwing error.

Nishant Kumar's Avatar

Nishant Kumar

09 Nov, 2017 10:16 AM

I am try to run step template( SQL - Execute SQL Script with SQL or Windows Authentication ) to run sql query:

I am passing the required Server Instance Name however the script is still throwing error: Can you please have a look.

I am getting following error:
15:08:33 Error | Get-DBConnection : You cannot call a method on a null-valued expression.
15:08:33 Error | At C:\Octopus\Work\20171109093824-184-17\Script.ps1:79 char:22
15:08:33 Error | + $SqlServer = Get-DBConnection -serverInstance $serverInstance
15:08:33 Error | + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
15:08:33 Error | + CategoryInfo : InvalidOperation: (:) [Get-DBConnection], RuntimeException
15:08:33 Error | + FullyQualifiedErrorId : InvokeMethodOnNull,Get-DBConnection
15:08:34 Info | Exit code: 1

  1. Support Staff 1 Posted by Henrik Andersso... on 10 Nov, 2017 05:52 AM

    Henrik Andersson's Avatar

    Hi Nishant,

    Can you please send through a screenshot of how you've configured the step. Could you also send me an export of the step template?

    Thank you and best regards,
    Henrik

  2. 2 Posted by Nishant Kumar on 10 Nov, 2017 07:24 AM

    Nishant Kumar's Avatar

    Hi Henrik,

    I am passing my db server name for serverInstance name . Is that correct ?
    I am trying to run this script on powershell prompt as well. It gives me
    same error.
    [image: Inline image 1]

  3. Support Staff 3 Posted by Henrik Andersso... on 10 Nov, 2017 08:16 AM

    Henrik Andersson's Avatar

    Hi Nishant,

    If you try to access your DB server using SQL Server Managment Studio with that same instance name, does it work?

    You are using Integrated security in the step, does the user account that the Tentacle is running as have that access to the database?

    If you try to run the code below in a PowerShell script on the Tentacle, what is the output of $connection and does it give you a different error:

    $connection = (New-Object Microsoft.SqlServer.Management.Smo.Server("devdb07a.devspeedpay.com"))
    $connection
    $connection.Refresh()
    

    Thank you and best regards,
    Henrik

  4. 4 Posted by Nishant Kumar on 10 Nov, 2017 09:07 AM

    Nishant Kumar's Avatar

    Hi Henrik,

    The error i get is this:

    >> }
    PS C:\Users> $serverInstance = 'dbserver07a'
    PS C:\Users> $connection = (New-Object
    Microsoft.SqlServer.Management.Smo.Server($serverInstance))
    New-Object : Cannot find type [Microsoft.SqlServer.Management.Smo.Server]:
    verify that the assembly containing this
    type is loaded.
    At line:1 char:16
    + ... nnection = (New-Object Microsoft.SqlServer.Management.Smo.Server($ser
    ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo : InvalidType: (:) [New-Object],
    PSArgumentException
        + FullyQualifiedErrorId :
    TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

  5. Support Staff 5 Posted by Henrik Andersso... on 13 Nov, 2017 12:37 AM

    Henrik Andersson's Avatar

    Hi Nishant,

    Sorry, I forgot one line in that script. I've updated the script below:

    $serverInstance = "devdb07a"
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
    $connection = (New-Object Microsoft.SqlServer.Management.Smo.Server($serverInstance))
    $connection | gm
    

    Can you run that on one of the Tentacles that are in the Deployment role and send me the raw log the task.

    Thank you and best regards,
    Henrik

  6. 6 Posted by Nishant Kumar on 13 Nov, 2017 06:25 AM

    Nishant Kumar's Avatar

    Same error Henrik

    11:49:40 Error | New-Object : Cannot find type
    [Microsoft.SqlServer.Management.Smo.Server]: verify that the assembly
    containing this
    11:49:40 Error | type is loaded.
    11:49:40 Error | At
    C:\Octopus\Work\20171113061910-281-148\Script.ps1:3 char:16
    11:49:40 Error | + $connection = (New-Object
    Microsoft.SqlServer.Management.Smo.Server($serverInsta ...
    11:49:40 Error | +
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    11:49:40 Error | + CategoryInfo : InvalidType: (:)
    [New-Object], PSArgumentException
    11:49:40 Error | + FullyQualifiedErrorId :
    TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand
    11:49:41 Verbose | Updating manifest with output variables
    11:49:41 Verbose | Updating manifest with action evaluated variables
    11:49:41 Fatal | The remote script failed with exit code 1

  7. Support Staff 7 Posted by Henrik Andersso... on 13 Nov, 2017 06:45 AM

    Henrik Andersson's Avatar

    OK, it looks like you haven't got the SQL Server Management Objects installed on the server you are running the script on, see this page for information on how to install them.

    I hope that helps.

    Thank you,
    Henrik

Reply to this discussion

Internal reply

Formatting help / Preview (switch to plain text) No formatting (switch to Markdown)

Attaching KB article:

»

Attached Files

You can attach files up to 10MB

If you don't have an account yet, we need to confirm you're human and not a machine trying to post spam.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac