SSMS – Scripting – Job – Error – “msdb.dbo.sp_add_jobserver” – “is already targeted at server”

 

Background

Using SSMS, Scripted SQL Server Job so that I can apply it against other SQL Servers, but ran into error.

Scripting

Script SQL Server Agent Job

scriptJob_20180612_0128PM.PNG

 

Error

Error Message


Msg 14269, Level 16, State 1, Procedure msdb.dbo.sp_add_jobserver, Line 101 [Batch Start Line 8]
Job 'SharePublicly' is already targeted at server 'LAB'.

Troubleshooting

When a job is scripted, the code generated includes a portion that invokes sp_add_jobserver.

The sp_add_jobserver procedure binds the job to the targeted server.


EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
         @job_id = @jobId
       , @server_name = N'(local)'

IF (@@ERROR  0 OR @ReturnCode  0)
      GOTO QuitWithRollback

Remediation

Outline

  1. Check msdb system tables
    • dbo.sysjobservers
    • dbo.systargetservers
  2. If matching records
    • Here are the tables that needs to be considered
      • dbo.sysjobservers.jobName ( @jobID )
      • dbo.systargetservers.serverName ( @servername)
    • Conditions satisfied
      • Local variable @skipWhenJobExist is enabled ( set to 1 )
  3. Action
    • Skip invoking msdb.dbo.sp_add_jobserver

Code Snippet


DECLARE @jobId   BINARY(16)
DECLARE @jobName sysname
DECLARE @serverName sysname
DECLARE @serverNameLocal sysname
declare @commit bit
declare @skipWhenJobExist bit

set @jobName = 'SharePublicly';
set @servername = N'(local)'
set @serverNameLocal = N'(local)'

--set @commit = 0
set @commit = 1

set @skipWhenJobExist = 1
--set @skipWhenJobExist = 0

/*
	Check if Job is already attached to Target Server
*/
if not exists
(

	select
			  [src] = 'dbo.sysjobservers'
			, tblSJS.* 

	from   dbo.sysjobservers tblSJS

	left outer join dbo.systargetservers tblSTS
			on tblSJS.server_id = tblSTS.server_id

	where  tblSJS.job_id = @jobId

	and  

		(

			(
					 ( tblSJS.server_id = 0)
				 and ( @servername = @serverNameLocal )

			)

			or
			(
					 ( tblSJS.server_id = tblSTS.server_id)
				 and ( tblSTS.server_name = @serverName )
			)

		)
	and (@skipWhenJobExist = 1)
)
begin

	EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
			  @job_id = @jobId
			, @server_name = @serverName

	IF (@@ERROR  0 OR @ReturnCode  0)
	begin
		GOTO QuitWithRollback
	end

end

Source Control

GitHub

DanielAdeniji/SQLServerScriptingAgentErrorJobAlreadyTargeted
Link

 

Sql Server Agent – Step – “SQL Server Integration Services Package” – Error – “Errors were detected in the command line arguments…”

Background

Trying to edit the parameters on a SQL Server Agent Job, but ran into a hard wall.

 

Error Message

Error

Image

Textual

TITLE: Microsoft SQL Server Management Studio

Errors were detected in the command line arguments, please make sure all arguments are set correctly. (SqlManagerUI)

 

Advanced Information

Upon clicking the “Advanced Information” we will click the message pasted below.

Image

Textual

===================================

Errors were detected in the command line arguments, please make sure all arguments are set correctly. (SqlManagerUI)

——————————
Program Location:

at Microsoft.SqlServer.Management.SqlManagerUI.DTSJobSubSystemDefinition.Microsoft.SqlServer.Management.SqlManagerUI.IJobStepPropertiesControl.Save(JobStepData data, Boolean isSwitching)
at Microsoft.SqlServer.Management.SqlManagerUI.JobStepProperties.OnGatherUiInformation(RunType runType)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.OnGatherUiInformation(RunType runType)

Scenario

This problem is reproducible this way.

  1. Start Sql Server Management Studio ( SSMS )
  2. Connect to SQL Server Instance
  3. Access SQL Server Agent \ Jobs \ [Job Name]
  4. Edit a Job Step whose type is “SQL Server Integration Services Package
  5. There are two tabs, Package and Configuration
  6. Access the “Configuration” Tab
  7. Within the “Configuration” Tab there are three Tabs.  The Tabs are “Parameters”, “Connection Managers”, and Advanced
  8. Select the “ExcelConnectionManager_ConnectionString” parameter
    • Click on “ellipse” button
    • The “Edit Literal Value for Execution” window appears
    • Access the “value” textbox and make cosmetic changes
    • Click the OK button
  9. The error, “Errors were detected in the command line arguments, please make sure all arguments are set correctly. (SqlManagerUI)“, aforementioned appears

 

Quick Points

  1. Other Parameters can be modified
    • This error only occurs when we tried amending the Excel File Connection String
    • We were able to modify other parameters; such as SQL Server and SMTP Server
  2. Connection Manager
    • All Connections ( Excel, SQL Server, and SMTP) can be successfully modified via the Connection Manager Tab

 

Honorable Mention

  1. Cody Konior
    • Agent job errors when running an SSIS package
      Published On :- 2015-August-2nd
      Link

 

SQL Server Versions

Here are the versions that exhibit this issue.

SQL Server Engine

Tabulated

Version Version Literal Version#
SQL Server v2014
Microsoft SQL Server 2014 (SP2-CU4) (KB4010394) – 12.0.5540.0 (X64) 12.0.5540.0

 

SQL Server Client

SQL Server Management Studio ( SSMS )

Tabulated

Version Version Literal Version#
SQL Server v2017
v 17.2 14.0.17177.0
SQL Server v2014
SQL Server 2014 SP2 CU10 12.0.5571.0

 

Image

SQL Server v2017

SQL Server v2014

SQL Server Agent – Error – “Failed to initialize sqlcmd library with error number -2147467259”

Background

Experienced error running SQL Server Agent job that we are developing.

Error

Error Image

Error Message

Executed as user: LAB\mssql. @profileName :- DBA Mail [SQLSTATE 01000] (Message 0) @recipientsTo :- daniel@lab.org [SQLSTATE 01000] (Message 0) @subject :- Privilege Users on HRDB [SQLSTATE 01000] (Message 0) Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050) @body :- [SQLSTATE 01000] (Error 0). The step failed.

Explanation

  1. The error message states “Failed to initialize sqlcmd library with error number -2147467259.
  2. Unfortunately the error message is not precise

 

TroubleShooting

SQL Server Profiler

Trace Definition

Events Selection

Image

Trace Captured

Image

Explanation

  1. Event
    • Event Class :- User Error Message
    • TextData :- The EXECUTE permission was denied on the object ‘sp_DBRoleMembersList’, database ‘master’, schema ‘dbo’.
    • Application Name :- SQLCMD
    • Error :- 229

SQL Server Agent

SQL Server Agent Account

Let us get SQL Server Agent Account

SQL


select 
        tblSDSS.[servicename]
      , tblSDSS.[service_account]
from   sys.dm_server_services tblSDSS

Output

Explanation

  1. We have the service accounts that we are using for the SQL Server Engine and Agent

Remediation

Grant “SQL Server Agent” service account execute permission on targeted object.

SQL Server Agent – Message – “Failed to notify…”

Background

I have been tending to a job that often fails, and wanted to add email notification, for the last couple of weeks.

It is bad that enough that the job itself fails, but worse no email.

 

Monday Morning

Monday Morning is often a good day of the week to make use of another carefree weekend and see where the error lies.

Error Message

Image

 

Textual

Message
The job succeeded. The Job was invoked by Schedule 25 (Every Day). The last step to run was step 12 (Say Completed). NOTE: Failed to notify ‘Daniel Adeniji’ via email.

 

TroubleShooting

SQL Server Agent Error Log

here is what is logged in log file for SQL Server Agent.

Please note we are referencing SQL Server Agent log files, and not SQL Server Engine.

Image

Textual

[476] Database Mail is not enabled for agent notifications. Cannot send e-mail

 

Remediate

Access SQL Server Agent Properties and set SQL Mail…

GUI

Unset

When Unset

Here is how things look before configuring SQL Server Agent Mail Profile

 

Set

Us setting SQL Server Agent Mail Profile

Script

Read SQL Server Agent Properties

Code


declare @useDatabasemail     int
declare @DatabaseMailProfile nvarchar(255)
declare @AgentMailType       int
declare @ServiceStartMode    int
declare @ServiceAccount      nvarchar(512)
declare @AgtGroup            nvarchar(512)

exec master.dbo.xp_instance_regread 
          N'HKEY_LOCAL_MACHINE'
        , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
        , N'UseDatabaseMail'
        , @param = @useDatabasemail OUT
        , @no_output = N'no_output'

exec master.dbo.xp_instance_regread 
          N'HKEY_LOCAL_MACHINE'
        , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
        , N'DatabaseMailProfile'
        , @param = @DatabaseMailProfile OUT
        , @no_output = N'no_output'

exec master.dbo.xp_instance_regread 
          N'HKEY_LOCAL_MACHINE'
        , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
        , N'UseDatabaseMail'
        , @param = @AgentMailType OUT
        , @no_output = N'no_output'



--NOTE: When setting the the services start value, 2 == auto-start, 3 == Don't auto-start
EXEC master.sys.xp_instance_regread 
          'HKEY_LOCAL_MACHINE'
        , 'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT'
        , N'Start'
        , @ServiceStartMode OUTPUT



--declare @ServiceAccount nvarchar(512)
EXEC master.sys.xp_instance_regread
           'HKEY_LOCAL_MACHINE'
         , 'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT'
         , N'ObjectName'
         , @ServiceAccount OUTPUT


exec master.dbo.xp_instance_regread 
              N'HKEY_LOCAL_MACHINE'
            , N'SOFTWARE\Microsoft\MSSQLServer\Setup'
            , N'AGTGroup'
            , @AgtGroup OUTPUT

select 
          [@use_databasemail] = @useDatabasemail
        , [@DatabaseMailProfile] = @DatabaseMailProfile      
        , [@AgentMailType] = @AgentMailType
        , [@ServiceStartMode] = @ServiceStartMode
        -- NOTE: When setting the the services start value, 2 == auto-start, 3 == Don't auto-start
        , [@ServiceStartModeLiteral] 
                = case  
                        when @ServiceStartMode=2  then 'Auto Start'
                        when @ServiceStartMode=3  then 'Don''t Auto Start'
                  end   
        , [@ServiceAccount] = @ServiceAccount
        --, [@AgtGroup] = @AgtGroup

Output

Set SQL Server Agent Properties

Code


USE [msdb]
GO

set nocount on
set XACT_ABORT on
go

declare @sqlInstance                sysname
declare @databaseMailProfileDesired sysname

declare @FORMAT_MESSAGE_SET         nvarchar(600)
declare @message                    nvarchar(600)

declare @databaseMailProfile nvarchar(255)
declare @AgentMailType       int
declare @ServiceStartMode    int
declare @ServiceAccount      nvarchar(512)
declare @AgtGroup            nvarchar(512)

declare @commit              bit

set @commit =1
set @sqlInstance = cast(SERVERPROPERTY('servername') as sysname)
set @databaseMailProfileDesired = N'DBAMail'

set @FORMAT_MESSAGE_SET = 'On %s, setting database mail profile to %s'

/*
    Access Registry (SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent') 
        a) Read Database Mail Profile
*/
exec master.dbo.xp_instance_regread 
          N'HKEY_LOCAL_MACHINE'
        , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
        , N'DatabaseMailProfile'
        , @param = @databaseMailProfile OUT
        , @no_output = N'no_output'

print '@DatabaseMailProfile :' + isNull(@DatabaseMailProfile, '')

begin tran
    

    if  
        ( @DatabaseMailProfile is null )
    begin

        exec master.dbo.xp_sprintf      
                  @message output
                , @FORMAT_MESSAGE_SET
                , @sqlInstance
                , @databaseMailProfileDesired

        print @message

        /*
            Set SQL Sql Server Agent Properties
        */
        EXEC msdb.dbo.sp_set_sqlagent_properties 
                  @email_save_in_sent_folder=1
                , @databasemail_profile=@databaseMailProfileDesired
                , @use_databasemail=1
                , @alert_replace_runtime_tokens=1


    end

while (@@TRANCOUNT > 0)
begin

    if (@commit = 1)
    begin

        print 'commit tran'

        commit tran

    end
    else
    begin

        print 'rollback tran'

        rollback tran

    end
end

GO

Output

Reset SQL Server Agent Properties

To revert changes, please run the script below.


USE [msdb]
GO

EXEC msdb.dbo.sp_set_sqlagent_properties
          @email_profile=N'' 
        , @databasemail_profile=N''
        , @use_databasemail  =0
GO


Listening

Listening to …

Jazmine Sullivan – In Love With Another Man
Link

 

SQL Server Agent – Output File Name – Tokenization

Background

I have always been perplexed by the fact that Sql Server Management Studio does not allow us to indicate that we desire timestamp file names for SQL Server Agent Jobs Logging.

SSMS

SQL Server Agent

Job Step Properties

Job Step Properties – Advanced

Initial
Image

Explanation
  1. Output file

 

Usage

Yesterday, I found out that timestamps are actually supported.

General – Image

Advanced – Image

Advanced – Textual

E:\Microsoft\SQLServer\SQLServerAgent\Log\DatabaseBackupFiles_$(ESCAPE_SQUOTE(JOBID))$(ESCAPE_SQUOTE(STEPID))$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt

 

Payload

cleanupFilesBak.cmd

Script


@echo off

REM How to use multiple commands in batch using forfiles command
REM https://stackoverflow.com/questions/23318731/how-to-use-multiple-commands-in-batch-using-forfiles-command

setlocal

set "_folderBase=G:\Microsoft\SQLServer\Backup"
set "_SQLInstance=%computername%"
set "_folderTarget=%_folderBase%\%_SQLInstance%\"

set "_fileExt=*.bak"

set "_daysPast=1"

set _appPgm=echo 

REM Added period (.) after folder name for /p argument
if not exist "%_folderTarget%." mkdir "%_folderTarget%"

forfiles /P "%_folderTarget%." /s  /m %_fileExt% /d -%_daysPast% /c ^"cmd /c ^
%_appPgm% ^
  file name is @PATH ^
  file size is @FSIZE ^
  file date is @FDATE ^
^"

endlocal

Output

Explanation

  1. DatabaseBackupFiles_[JobID]_[stepID]_[DateYYYYMMDD]_[timeHHMMSS].txt

 

Tokens

Token Description Version Active
A-DBN Active Database name
(A-SVR) SQL Server Name
(JOBNAME) The name of the Job Not active as of v2014/SP2
(STEPNAME) The name of the step Not active as of v2014/SP2
(DATE) Current date (in YYYYMMDD format).
(INST) Instance name. For a default instance, this token will have the default instance name: MSSQLSERVER.
(JOBID) Job ID
(MACH) Computer name
(SQLDIR) The directory in which SQL Server is installed. By default, this value is C:\Program Files\Microsoft SQL Server\MSSQL.
(SQLLOGDIR) Replacement token for the SQL Server error log folder path – for example, $(ESCAPE_SQUOTE(SQLLOGDIR)).
(STEPID) Step ID.
(SRVR) Name of the computer running SQL Server. If the SQL Server instance is a named instance, this includes the instance name.
(TIME) Current time (in HHMMSS format).
(STRTTM) Current time (in HHMMSS format).
(STRTDT) The date (in YYYYMMDD format) that the job began executing.

 

Dedicated

Dedicated to Ola Hallengren as I found out about this functionality through his script.

And, also to Dave Benham.

Dave has a nice explanation on how to escape commands in batch files and thus span multiple lines.

His explanation is here and it is in response to a Stackoverflow question.  The Question is titled “How to use multiple commands in batch using forfiles command“.

 

Listening

Listening to “Drifting Spirits” @ Teavolve.

Link

References

  1. Docs / SQL / SSMS / Agent
    • Use Tokens in Job Steps
      Link
  2. Thomas Lind
    • SQL Agent job logging with tokens
      Link

 

SQL Server Agent- Execution Error – “Failed to execute IS server package because of error 0x80131904”

 

Background

I have a couple of SSIS packages to deploy from QA to Production.

Deploy the SSIS packages and ran the SQL Server Agent jobs creations, as well.

Ran into errors and one of them is referenced below.

 

Error

Image

Textual

Date        8/9/2017 3:37:21 PM
Log        Job History (DataRefresh_pp_dem_PROD_to_DEV)

Step ID        1
Server        P-D-RED
Job Name        DataRefresh_ppsivr_dem_PROD_to_DEV
Step Name        DataRefresh_dem_PROD_to_DEV
Duration        00:00:01

Message
Executed as user: LAB-DOM\svcProxy. Microsoft (R) SQL Server Execute Package Utility  Version 12.0.5000.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  3:37:22 PM  Failed to execute IS server package because of error 0x80131904. Server: p-db-red, Package path: \SSISDB\SSISDB\DataRefrehPackage_Ppsivr_Dem_PROD_To_DEV\DataRefresh_Dem.dtsx, Environment reference Id: NULL.  Description: Login failed for user ‘LAB-DOM\svcProxy’.  Source: .Net SqlClient Data Provider  Started:  3:37:22 PM  Finished: 3:37:22 PM  Elapsed:  0.562 seconds.  The package execution failed.  The step failed.

 

Remediation

For us the problem was that the script we used for creating the SQL Server Agent job was hardcoded to look for the SSIS package in the originating SQL Instance’s SSISDB database.

The fix is to change the Server’s text box text to have the deployed SQL Instance name or simply the generic SQL Server Instance Identifier (local); especially if it is the default SQL Instance.

 

JobStepProperties_2A_20170809_0421PM (BrushedUp)