Stored Procedure Fails Only When Called By Agent

From: Erik Nelson (cybermud_at_excite.com)
Date: 05/06/04

  • Next message: John Celery: "merge subscriptions expiring" 
    Date: Thu, 6 May 2004 14:54:34 -0500

     I have three stored procedures that do the flowing tasks:
    1. connect to and add logins for 12 remote (WAN) SQL servers

    2. Drop data on the local server and re-add it table at a time from each
    remote server, one at a time, for 5 tables.

    3. Disconnect from the sites.

    These stored procedures work fine when run from Query Analyzer, but when I
    schedule them to run automatically via an agent job, the job fails on
    procedure #2 which copies the data. I get an error message of "Msg 17, Sev
    16: SQL Server does not exist or access denied. [SQLSTATE 42000]" in the
    error log, which seems like a rights issue to me, but I don't know where to
    look to correct it. Can anyone point me to any documentation that will
    explain how the agent executes jobs?

    Thanks,

    Erik

  • Next message: John Celery: "merge subscriptions expiring"

    Relevant Pages

    • Webservice SQL logon fails on production IIS server
      ... I recently created some intranet web sevices to expose stored procedures on ... our SQL Servers. ... When I move the services to production IIS servers, the database connection ...
      (microsoft.public.dotnet.framework.webservices)
    • After MS Critical updates, getting sa errors - HELP
      ... I just put the blaster patches on our SQL servers ... Not associated with a trusted SQL Server Connection ... We have a whole bunch of stored procedures on a certain ... database that give the trusted SQL error when trying to ...
      (microsoft.public.sqlserver.security)
    • After MS Critical updates, getting sa errors - HELP
      ... I just put the blaster patches on our SQL servers ... Not associated with a trusted SQL Server Connection ... We have a whole bunch of stored procedures on a certain ... database that give the trusted SQL error when trying to ...
      (microsoft.public.sqlserver.security)
    • Help on code in a SP
      ... I'm currently auditing some SQL servers to have an overview on what's ... running/existing in the different databases. ... I found User's Stored Procedures in MSDB databases ...
      (microsoft.public.sqlserver.server)
    • Re: RWW and Sql Enterprise Manager
      ... If you are simply looking at managing these SQL Servers ... It's just like running secure RDP on the remote machine. ... MCP MCDBA MCAD MCSD MCT MCTS MCITP:DBA ... Have you tried using ASP Enterprise Manager? ...
      (microsoft.public.windows.server.sbs)