Author Posts

January 1, 2012 at 12:00 am

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.