SQL Server Job Problem

[apwestgarth]

Hi I have a problem with a job I need to run. I have pasted the sql script for the job at the bottom of this post. I am trying to carry out an insert, update and delete job on my SQL Server. The job is looking to update a "temporary" table on my live server from a view on a linked server. I can get this to run on my dev server on the same domain but cannot get my live server to run it.

The error I keep getting is:

on Step1:

[COLOR=Red Yellow]Executed as user: STMBC_DOM\webteam. Login failed for user 'STMBC_DOM\webteam'. [SQLSTATE 28000] (Error 18456). The step failed.,[/color]

Please can anyone help?

Andrew

-- Script generated on 21/04/2005 11:12
-- By: sa
-- Server: MY SERVER

BEGIN TRANSACTION            
  DECLARE @JobID BINARY(16)  
  DECLARE @ReturnCode INT    
  SELECT @ReturnCode = 0     
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1 
  EXECUTE msdb.dbo.sp_add_category @name = N'Database Maintenance'

  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id     
  FROM   msdb.dbo.sysjobs    
  WHERE (name = N'EducationTemp Sync')       
  IF (@JobID IS NOT NULL)    
  BEGIN  
  -- Check if the job is a multi-server job  
  IF (EXISTS (SELECT  * 
              FROM    msdb.dbo.sysjobservers 
              WHERE   (job_id = @JobID) AND (server_id <> 0))) 
  BEGIN 
    -- There is, so abort the script 
    RAISERROR (N'Unable to import job ''EducationTemp Sync'' since there is already a multi-server job with this name.', 16, 1) 
    GOTO QuitWithRollback  
  END 
  ELSE 
    -- Delete the [local] job 
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'EducationTemp Sync' 
    SELECT @JobID = NULL
  END 

BEGIN 

  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'EducationTemp Sync', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'Database Maintenance', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job steps
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Insert New Data', @command = N'/*
Insert records from source into destination where the record does not already exist
*/

INSERT INTO [MY SERVER].STMBC.dbo.EducationTemp
(	
	SCH_DFEE,	
	SCH_NAME, 		
	SCH_TO_DATE,
	ADR_NUMBER,	
	ADR_STREET,		
	ADR_LOCALITY,
	ADR_TOWN,	
	ADR_COUNTY,
	ADR_POSTCODE,
	SCH_TEL,	
	SCH_FAX,		
	SCH_WEB,		
	PTT_DESC, 		
	CONTACT_SNAME,
	CONTACT_INIT,	
	[Chair of Governors],
	SCHFIL_DESC,
	SCHFIL_FILE,		
	[School Type],
	ADR_UPRN,	
	ADR_GRIDREF_E,
	ADR_GRIDREF_N,		
	tblLU_SCH_TYPE, 		
	STAT_DESC,	
	STAT_ID,	
	PHASE_DESC,
	PHASE_ID)

	SELECT 
		SCH_DFEE,	
		SCH_NAME, 		
		SCH_TO_DATE,
		ADR_NUMBER,	
		ADR_STREET,		
		ADR_LOCALITY,
		ADR_TOWN,	
		ADR_COUNTY,
		ADR_POSTCODE,
		SCH_TEL,	
		SCH_FAX,		
		SCH_WEB,		
		PTT_DESC, 		
		CONTACT_SNAME,
		CONTACT_INIT,	
		[Chair of Governors],
		SCHFIL_DESC,
		SCHFIL_FILE,		
		[School Type],
		ADR_UPRN,	
		ADR_GRIDREF_E,
		ADR_GRIDREF_N,		
		tblLU_SCH_TYPE, 		
		STAT_DESC,	
		STAT_ID,	
		PHASE_DESC,
		PHASE_ID

 	FROM EDUCATION.IDR_IDEAR.FOUNDATIONAPPS.vw_USR_School_web_dets ED

	WHERE ED.SCH_DFEE NOT IN (SELECT DISTINCT SCH_DFEE FROM [MY SERVER].STMBC.dbo.EducationTemp)', @database_name = N'STMBC', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'Update Current Data', @command = N'UPDATE EducationTemp
SET 	SCH_DFEE = ED.SCH_DFEE,
	SCH_NAME = ED.SCH_NAME,
	SCH_TO_DATE = ED.SCH_TO_DATE,
	ADR_NUMBER = ED.ADR_NUMBER,
	ADR_STREET = ED.ADR_STREET,
	ADR_LOCALITY = ED.ADR_LOCALITY,
	ADR_TOWN = ED.ADR_TOWN,
	ADR_COUNTY = ED.ADR_COUNTY,
	ADR_POSTCODE = ED.ADR_POSTCODE,
	SCH_TEL = ED.SCH_TEL,
	SCH_FAX = ED.SCH_FAX,
	SCH_WEB = ED.SCH_WEB,
	PTT_DESC = ED.PTT_DESC,
	CONTACT_SNAME = ED.CONTACT_SNAME,
	CONTACT_INIT = ED.CONTACT_INIT,
	[Chair of Governors] = ED.[Chair of Governors],
	SCHFIL_DESC = ED.SCHFIL_DESC,
	SCHFIL_FILE = ED.SCHFIL_FILE,
	[School Type] = ED.[School Type],
	ADR_UPRN = ED.ADR_UPRN,
	ADR_GRIDREF_E = ED.ADR_GRIDREF_E,
	ADR_GRIDREF_N = ED.ADR_GRIDREF_N,
	tblLU_SCH_TYPE = ED.tblLU_SCH_TYPE,
	STAT_DESC = ED.STAT_DESC,
	STAT_ID = ED.STAT_ID,
	PHASE_DESC = ED.PHASE_DESC,
	PHASE_ID = ED.PHASE_ID
FROM [MY SERVER].STMBC.dbo.EducationTemp ET
INNER JOIN EDUCATION.IDR_IDEAR.FOUNDATIONAPPS.vw_USR_School_web_dets ED ON ED.SCH_DFEE = ET.SCH_DFEE AND
    (	ED.SCH_DFEE <> ET.SCH_DFEE OR 
	ED.SCH_NAME <> ET.SCH_DFEE OR
	ED.SCH_TO_DATE <> ET.SCH_TO_DATE OR
	ED.ADR_NUMBER <> ET.ADR_NUMBER OR
	ED.ADR_STREET <> ET.ADR_STREET OR
	ED.ADR_LOCALITY <> ET.ADR_LOCALITY OR
	ED.ADR_TOWN <> ET.ADR_TOWN OR
	ED.ADR_COUNTY <> ET.ADR_COUNTY OR
	ED.ADR_POSTCODE <> ET.ADR_POSTCODE OR
	ED.SCH_TEL <> ET.SCH_TEL OR
	ED.SCH_FAX <> ET.SCH_FAX OR
	ED.SCH_EMAIL_SEC <> ET.SCH_EMAIL_SEC OR
	ED.SCH_WEB <> ET.SCH_WEB OR
	ED.PTT_DESC <> ET.PTT_DESC OR
	ED.CONTACT_SNAME <> ET.CONTACT_SNAME OR
	ED.CONTACT_INIT <> ET.CONTACT_INIT OR
	ED.[Chair of Governors] <> ET.[Chair of Governors] OR
	ED.SCHFIL_DESC <> ET.SCHFIL_DESC OR
	ED.SCHFIL_FILE <> ET.SCHFIL_FILE OR
	ED.[School Type] <> ET.[School Type] OR
	ED.ADR_UPRN <> ET.ADR_UPRN OR
	ED.ADR_GRIDREF_E <> ET.ADR_GRIDREF_E OR
	ED.ADR_GRIDREF_N <> ET.ADR_GRIDREF_N OR
	ED.tblLU_SCH_TYPE <> ET.tblLU_SCH_TYPE OR
	ED.STAT_DESC <> ET.STAT_DESC OR
	ED.STAT_ID <> ET.STAT_ID OR
	ED.PHASE_DESC <> ET.PHASE_DESC OR
	ED.PHASE_ID <> ET.PHASE_ID 
	)
', @database_name = N'STMBC', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 3, @step_name = N'Delete Old Data', @command = N'/*
	Delete
*/

DELETE FROM [DEVT-WEB].STMBC.dbo.EducationTemp
	WHERE SCH_DFEE NOT IN (SELECT DISTINCT SCH_DFEE FROM EDUCATION.IDR_IDEAR.FOUNDATIONAPPS.vw_USR_School_web_dets)
', @database_name = N'STMBC', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 

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

  -- Add the job schedules
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Education Temp Sync Every Day On Hour at Ten Past the Hour', @enabled = 1, @freq_type = 4, @active_start_date = 20050415, @active_start_time = 1000, @freq_interval = 1, @freq_subday_type = 8, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the Target Servers
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' 
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

END
COMMIT TRANSACTION          
GOTO   EndSave              
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave:

Andrew Westgarth
Web Developer

 

[apwestgarth]

I've fixed it, I set a default user credentials to run the job when the user running it is not in the list.

Andrew Westgarth
Web Developer