SQL Server Agent - stored procedure

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Jay Hawkinson (Jay.Hawkinson_at_beldencdt.com)
Date: 09/09/04 

Date: Thu, 9 Sep 2004 07:28:20 -0700

Good morning.

I have a stored procedure which I run from SQL Server
Agent. This stored procedure, basically, INSERTS a row
into a database, and if a duplicate key exists, it puts it
in a DUPS table.

The problem is that though Query Analyzer will continue
when it hits an error (and allows me to do my error
processing), SQL Server Agent crashes out. Is there anyway
to have SQL Server Agent behave the same way? Is there a
threshold of errors count?

Code snippet wise it looks like this:

INSERT INTO BELDEN_CDB.IWORK.BFGTCKT VALUES (
                @bfgtckt ,
                @bfgitem ,
                @bfgqty ,
                @bfgloc ,
                @bfgload ,
                @bfgused ,
                @bfglot)
SET @sqlcode = @@error
IF @sqlcode in (2601, 2607, 2627)
   BEGIN
     SET @dups = @dups + 1
     INSERT INTO BELDEN_CDB.IWORK.BFG_Dups VALUES (
                        @bfgtckt ,
                        @bfgitem ,
                        @bfgqty ,
                        @bfgloc ,
                        @logged )
   END

Jay Hawkinson

Relevant Pages

  • Re: Alternative to Dynamic SQL?
    ... We have a single user per database. ... You use SQL Server logins for security as opposed to Integrated Security? ... DBA....and Tony was the developer and Tony wanted a pagination query, ... You can use a stored procedure to paginate database-side as well, ...
    (microsoft.public.sqlserver.programming)
  • Re: using sp_ as a naming convention for stored procedures
    ... System stored procedures are created and stored in the master ... database and have the sp_ prefix. ... from any database without having to qualify the stored procedure name fully ... SQL Server always looks for a stored procedure ...
    (microsoft.public.sqlserver.programming)
  • Re: Debug stored procedures with VB6
    ... > I can't see in the sql server analyser a tool to debug a stored procedure. ... > "Val Mazur" a écrit dans le message de ... >>>>> My database is installed locally. ...
    (microsoft.public.vb.database.ado)
  • RE: ASP.NET/Linked Server connection problem
    ... Destroy security and open the database for hackers by reducing security ... Wrap your work in a stored procedure that your connecting user account ... Create a custom ETL application to move the data. ... > I am trying to create/use a SQL Server Linked Server definition from ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Facing the same problem
    ... My stored procedure is being activated from a Service Broker ... remote server is denied because the current security context is not ... The linked server is a SQL server, but not one where I have any ... ALTER DATABASE db SET TRUSTWORTHY ...
    (microsoft.public.sqlserver.security)