Invoke-SqlCMD error

Welcome Forums General PowerShell Q&A Invoke-SqlCMD error

Viewing 0 reply threads
  • Author
    • #6382
      Topics: 1562
      Replies: 0
      Points: 1
      Rank: Member

      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

      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

      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’

      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.
      <br />$Instance = Get-Content &quot;c&#58;\SQL_SERVERS\2008_JOBS_TO_UPDATE&#46;txt&quot;<br />$inputquery = @&quot;<br /> EXEC msdb&#46;dbo&#46;sp_update_jobstep<br />     @job_name = N'DatabaseBackup - USER_DATABASES - DIFF',<br />     @step_id = 1,<br />     @command = N'sqlcmd -E -S `$(ESCAPE_SQUOTE(SRVR)) -d DBA_MAINT -Q &quot;EXECUTE &#091;dbo&#093;&#46;&#091;DatabaseBackup&#093; @Databases = ''USER_DATABASES'', @Directory = NULL, @BackupType = ''DIFF'', @Verify = ''Y'', @CleanupTime = 72, @ChangeBackupType=''Y'',@CheckSum = ''Y''&quot; -b'<br />&quot;@<br />$Instance | <br /> ForEach-Object {<br /> $server = New-Object -TypeName Microsoft&#46;SqlServer&#46;Management&#46;Smo&#46;Server -ArgumentList $_ <br /> Invoke-Sqlcmd -ServerInstance $_ -Query $inputquery<br /> }<br />

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

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

Viewing 0 reply threads
  • The topic ‘Invoke-SqlCMD error’ is closed to new replies.