SQL Maintenance Job - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

SQL Maintenance Job

Author

Topic

sukh007
Starting Member

2 Posts

Posted - 2012-04-11 : 09:38:26

Hi Guys

Hope someone can help.

I have a SQL Merge script below that will import/update records in a table based on a matching rule. This runs as expected when I run this SQL script in Management Studio.

MERGE erica_staging.dbo.appointments AS APP
Using(
select
fac.activityid, fac.activitypartyid, fac.scheduledstart, fac.scheduledend,
fa.ccx_postcode, fa.location, fa.ccx_typename, fa.statecodename, fa.subject, fac.partyidname, FUR.ccx_repcode
from mscrm_mscrm.dbo.FilteredActivityParty FAC inner join mscrm_mscrm.dbo.FilteredAppointment FA on FAC.activityid = FA.activityid left join mscrm_mscrm.dbo.Filteredccx_userrepcode FUR on FAC.partyid = FUR.ccx_userid
where participationtypemask = 5 and partyobjecttypecode = 8)
as MSCRMApp (activityid, activitypartyid, scheuledstart, scheduledend, postcode, location, AppType, statusname, subjectname, RepName, RepCode)
ON
(
APP.activitypartyid = MSCRMApp.activitypartyid
)
WHEN NOT MATCHED THEN
INSERT (activityid, activitypartyid, scheduled_start, scheduled_end, postcode,
location, appointment_type, status, subject, rep_name, rep_code, process_flag,
crmdtm)
VALUES (MSCRMApp.activityid, MSCRMApp.activitypartyid, MSCRMApp.scheuledstart,
MSCRMApp.scheduledend, MSCRMApp.postcode, MSCRMApp.location, MSCRMApp.AppType,
MSCRMApp.statusname, MSCRMApp.subjectname, MSCRMApp.RepName, MSCRMApp.RepCode,
'Y', GETDATE())
WHEN MATCHED THEN
UPDATE
SET APP.activityid = MSCRMApp.activityid,
APP.scheduled_start = MSCRMApp.scheuledstart,
APP.scheduled_end = MSCRMAPP.scheduledend,
APP.postcode = MSCRMAPP.postcode,
APP.location = MSCRMAPP.location,
APP.appointment_type = MSCRMApp.AppType,
APP.status = MSCRMApp.statusname,
APP.subject = MSCRMApp.subjectname,
APP.Rep_Name = MSCRMAPP.RepName,
APP.Rep_Code = MSCRMAPP.RepCode,
APP.Process_flag = 'Y',
APP.CRMDTM = GETDATE();

I created a SQL job and added the SQL script as a step (only 1 step in the job). When I run the job manually it succeeds with no errors however nothing happens to my database as it should import the records. As I said if I run this script outside of the job then it imports/updates the records however nothing happens when run as a job even though I get no errors.

Thanks
Sukh

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2012-04-12 : 08:16:13

Please confirm the database selected in the job configuration and in the script are the same...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources