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.by Klaas at 2013-02-22 05:58:22
[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?
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?by badpig521 at 2013-02-22 06:12:03
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.
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:by badpig521 at 2013-02-22 07:21:55
[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
Anyone know how to get this to work with invoke-sqlcmd?by sqlchow at 2013-02-24 10:46:22
[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]
[quote="badpig521"]EXEC msdb.dbo.sp_update_jobstepby badpig521 at 2013-02-26 11:10:12
    @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
}
No, I still get Object reference not set to an instance of an object.