SQL Server Job Problem
-
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:
Executed as user: STMBC_DOM\webteam. Login failed for user 'STMBC_DOM\webteam'. [SQLSTATE 28000] (Error 18456). The step failed.,
Please can anyone help?
Andrew
CODE
-- 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
-
Are you sure that your live server SQLAgent account has necessary permisions to reach outside resources? If this job works on one server but doesn't on the other then there is large likelihood that the running account doesn't have some necessary permission to accomplish that
Hope this helps
-
Thanks I looked at the job and added a default user account for the job to run if the user running the job is not in the list and now it's running.
Cheers
Andrew
-
Glad to help. Sometimes a minor detail can stultify a huge job...
Cheers
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply