Invoke-SqlCMD error

This topic contains 0 replies, has 1 voice, and was last updated by  Forums Archives 5 years, 10 months ago.

  • Author
    Posts
  • #6382

    by badpig521 at 2013-02-22 05:08:10

    I am getting this error when trying to use Invoke-SqlCmd: Object reference not set to an instance of an object.

    [code2=powershell]$Instance = Get-Content "c:\SQL_SERVERS\2008_JOBS_TO_UPDATE.txt"
    $inputfile = "c:\SCRIPTS\Maintenance\CURRENT\UPDATE_JOB_STEP.sql"
    $Instance |
    ForEach-Object {
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $_
    Invoke-Sqlcmd -ServerInstance $_ -InputFile $inputfile
    }[/code2]

    It will work fine if I use a simple query such as SELECT @@VErsion but it will not accept an input file. Any ideas?

    by Klaas at 2013-02-22 05:58:22

    It depends on the content of the .txt file. With Get-Content every line will be one string. Will this be accepted by the -Serverinstance parameter?
    The $_ contains the current object, which is the textline as a string.
    Just as a test, you can add a line to write the current object to the screen and check if it contains what you expect.
    I also don't see why you populate a variable $server since you don't use it afterwards.

    by badpig521 at 2013-02-22 06:12:03

    Good Point Klaas! The txt file is working fine, I was able to put in a variable for a query. I used a simple query SELECT @@Version and I was able to get the version for each server. I then did the same thing to the sql file I am using as inputfile and was able to get the version . So, it has something to do with the language in the sql file. In my SQL file, I am using sp_update_jobstep. This seems to be the issue but it runs fine in SQL.

    by Klaas at 2013-02-22 06:21:27

    So it's SQL problem then. Maybe a permissions issue? Or you end up in the wrong database, do you use the three part name to call the sp?

    by badpig521 at 2013-02-22 06:28:14

    Permissions are good. I am calling a stored proc:
    [code2=sql]EXEC msdb.dbo.sp_update_jobstep
    @job_name = N'DatabaseBackup – USER_DATABASES – DIFF',
    @step_id = 1,
    @command = N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBA_MAINT -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = ''USER_DATABASES'', @Directory = NULL, @BackupType = ''DIFF'', @Verify = ''Y'', @CleanupTime = 72, @ChangeBackupType=''Y'',@CheckSum = ''Y''" -b'[/code2]

    I am guessing the @command is throwing off invoke-sqlcmd

    by badpig521 at 2013-02-22 07:21:55

    Anyone know how to get this to work with invoke-sqlcmd?

    [code2=sql]sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBA_MAINT -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = ''USER_DATABASES'', @Directory = NULL, @BackupType = ''DIFF'', @Verify = ''Y'', @CleanupTime = 72, @ChangeBackupType=''Y'',@CheckSum = ''Y''" -b'[/code2]

    by sqlchow at 2013-02-24 10:46:22

    [quote="badpig521"]EXEC msdb.dbo.sp_update_jobstep
        @job_name = N'DatabaseBackup – USER_DATABASES – DIFF',
        @step_id = 1,
        @command = N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBA_MAINT -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = "USER_DATABASES", @Directory = NULL, @BackupType = "DIFF", @Verify = "Y", @CleanupTime = 72, @ChangeBackupType="Y",@CheckSum = "Y"" -b'[/quote]

    What happens when you put the contents of your file in a here-string and execute? Also, the "$" in the SQL command has to be escaped. Try it out and let me know.

    $Instance = Get-Content "c:\SQL_SERVERS\2008_JOBS_TO_UPDATE.txt"
    $inputquery = @"
    EXEC msdb.dbo.sp_update_jobstep
        @job_name = N'DatabaseBackup - USER_DATABASES - DIFF',
        @step_id = 1,
        @command = N'sqlcmd -E -S `$(ESCAPE_SQUOTE(SRVR)) -d DBA_MAINT -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = ''USER_DATABASES'', @Directory = NULL, @BackupType = ''DIFF'', @Verify = ''Y'', @CleanupTime = 72, @ChangeBackupType=''Y'',@CheckSum = ''Y''" -b'
    "@
    $Instance |
    ForEach-Object {
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $_
    Invoke-Sqlcmd -ServerInstance $_ -Query $inputquery
    }

    by badpig521 at 2013-02-26 11:10:12

    No, I still get Object reference not set to an instance of an object.

You must be logged in to reply to this topic.