Re: trggering replication jobs from within a DTS package

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 10/13/04 

Date: Wed, 13 Oct 2004 18:46:00 +0100

Shall I tell you what I do?

I have a table on the destination server that has a trigger. I insert the
name of the job I want to start on the remote server and the trigger
concatenates this to the sp_start_job and fires the orrect job. 

-- 
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.Konesans.com
"Nico" <Nico@discussions.microsoft.com> wrote in message 
news:F16C2921-E935-4D09-9C8D-C51623078CB8@microsoft.com...
> Thanx for the quick reply.....I got that one working....have another 
> problem
> though.....
>
> I cannot execute a remote sp from an agent job.  I get the folloing error.
> Job 'nicotestjob' : Step 1, 'execute nicotest' : Began Executing 
> 2004-10-13
> 10:33:13
> Msg 7410, Sev 16: Remote access not allowed for Windows NT user activated 
> by
> SETUSER. [SQLSTATE 42000]
>
> Any advice ?
>
> "Allan Mitchell" wrote:
>
>> look up sp_start_job in BOL.  You could use this in an ExecuteSQL task
>>
>> -- 
>>
>> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
>> www.SQLDTS.com - The site for all your DTS needs.
>> www.Konesans.com
>>
>>
>> "Nico" <Nico@discussions.microsoft.com> wrote in message
>> news:E181EF39-DA01-4C0A-8448-C3595EE27F2E@microsoft.com...
>> > Is it possible to trigger a replication job from witin a DTS Package?
>> > I want to do the following
>> > execute SP1
>> > on complation
>> > execute replication job 1
>> > on completion execute sp2
>> > on complation execute replication job 1
>> > oncomplation execute sp1
>> >
>> > The replication job is already set up and scheduled.  The reason I want 
>> > to
>> > do this is because the different steps are dependent on each other and 
>> > you
>> > have to be very carefull id you schedule them as you're not always sure
>> > how
>> > long they will execute, and they're all dependent on the complation of 
>> > the
>> > previous job
>>
>>
>>

Relevant Pages

  • Re: Calling remote Stored Procedures
    ... problem when trying to get data to replicate from the Subscriber back ... trigger was failing to execute the Update ... execute a Stored Procedure from Server to the client ...
    (microsoft.public.sqlserver.server)
  • Re: Logon triggers
    ... EXECUTE AS LOGIN ='loginname' ... I am looking at logon triggers, but I understand that if the trigger fails ... will have access to the server. ...
    (microsoft.public.sqlserver.security)
  • How to capture context switching in a trigger and rollback / rever
    ... Also this can be traced using profiler / server side trace. ... If there is a way to fire a trigger when someone is executing "EXECUTE AS", ...
    (microsoft.public.sqlserver.security)
  • Re: File Upload - Security Issues
    ... uploaded and the user could upload any or all of these in theory. ... There is no one product that can give you 100% security, ... > Code doesn't execute in local memory space unless remote user has rights ... > You don't have MS Office installed on the server. ...
    (microsoft.public.scripting.vbscript)
  • [NEWS] Multiple Vulnerabilities in Oracle Database (Trigger, Extproc, Wrapped Procedures, PL/SQL Inj
    ... The following security advisory is sent to the securiteam mailing list, and can be found at the SecuriTeam web site: http://www.securiteam.com ... Multiple vulnerabilities were discovered in the Oracle database server. ... Oracle Trigger Abuse ... written in PL/SQL and execute with the privileges of the definer/owner. ...
    (Securiteam)