SQL 2005 Stored Procedure runs in management studio but not when called by a trigger

doesn't sound like a sensible way to go , performing BCP and EMAIL from a trigger which is already using a large stored procedure..

he standard recomendation would be to put this processing into another asynchronous process... either initiated by the trigger , or probably better by the
initiating processes....

However Please post the trigger itself...
an example of the triggering code and data
as wll as the full stored procedure...

 

1) you don't seem to be creating a unique table/filename are you certain (via ;pcking etc) that no other processes could also be concurrently attempting to use the data?

2) you haven't shown any error checking code in the procedure...
    do you have any?

    do you know if any of the processing has been performed , at what point it stalled?

 

I have not added any error checking yet.  The trigger is below.

This stored procedure has grown up over time and become something of a frankenstein at this point.  I do not have a way to see if any of the code in the SP has run.  A third party software is writing a record to the DB.  When the value of the "state" = "done", the trigger calls the SP.

I tried having the original SP call the new code as a second SP but got the same behavior.

here is the trigger code:
BEGIN TRAN calcGLAdditions

IF UPDATE(state)
BEGIN
      DECLARE
            @co varchar(10),
            @procName varchar(30)

      SELECT @co = co
      FROM inserted

      SET @procName = 'spsi_' + @co + 'GLAdditions'

      IF EXISTS (
            SELECT name
            FROM sysobjects
            WHERE name = @procName AND
                  type = 'P')
      BEGIN

            DECLARE
                  @state varchar(20),
                  @jobClass varchar(20),
                  @process int,
                  @jobId varchar(255)

            SELECT
                  @state = state,
                  @jobClass = jobClass,
                  @jobId = job
            FROM inserted
      
            IF(@state = 'done' AND @jobClass = 'CalcGL')
            BEGIN
      
                  --SELECT
                        --@process = substring(props, charindex('&Process=', props) + 9, 10)
                  --FROM inserted
                        
                  SELECT
                        @process = dbo.MPI_Props_GetValue(props, 'Process')
                  FROM sjob
                  WHERE job = @jobId

                  EXEC @procName @process;
            END
      END
      ELSE
            PRINT 'No stored procedure: ' + @procName
END

COMMIT TRAN

Here is the full procedure:

BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
SET NOCOUNT ON;
Begin Tran GLAdditions;

DELETE FROM CJentries
WHERE process = @process and det IN('A','B','W') and co = '0068';
--Agency Checks
--***************************************************
--The credit entry for these checks is handled by the PointClick code lower down by posting the AGENCY transfer to cash clearing in the point click GL export.  
--Crediting cash here over states the credits in the final file so later, the code to populate the point click GL file ignores these entries.  
--They are left so the GL Journal Entry report is still in balance.

INSERT INTO CJEntries (co, entryDate, process, cd, det, detCode, Amount,
       gl1, gl2, gl3, gl4, gl5, gl6)
SELECT co, checkDate, process, 'C', 'A', id, amount, '1010-00','','','','',''
FROM CMiscCheckHist
WHERE process = @process and checkType = 'Agency' and co = '0068';

INSERT INTO CJEntries (co, entryDate, process, cd, det, detCode, Amount,
       gl1, gl2, gl3, gl4, gl5, gl6)
SELECT co, checkDate, process, 'D', 'A',  id, amount, '2050-00','','','','EMPLOYEE DEDUCT - ','CHILD SUPP'
FROM CMiscCheckHist
WHERE process = @process and checkType = 'Agency' and co = '0068';

--Billing
--***************************************************
INSERT INTO CJEntries (co, entryDate, process, cd, det, detCode, Amount,
       gl1, gl2, gl3, gl4, gl5, gl6)
SELECT co, (SELECT CheckDate FROM CCalendar WHERE CO = '0068' AND Process = @process) ,process, 'C', 'B', invoice,amount, '1010-00','','','','',''
FROM SInvoices WHERE co = '0068'
AND process = @process;

INSERT INTO CJEntries (co, entryDate, process, cd, det, detCode, Amount,
       gl1, gl2, gl3, gl4, gl5, gl6)
SELECT co, (SELECT CheckDate FROM CCalendar WHERE CO = '0068' AND Process = @process) ,process, 'D', 'B', invoice,amount, '4130-10','','','','PAYROLL ','PROCESSING FEES'
FROM SInvoices WHERE co = '0068'
AND process = @process;

--Workers comp
--****************************************************
--None

UPDATE CJentries
SET CalendarId = (SELECT calendarId FROM CCalendar WHERE process = @process AND co = '0068')
WHERE CJEntries.co = '0068'
AND process = @process
AND det IN('A','B','W');                  
--AND lastChangeUser <> 'Root';  
--SELECT co FROM CInfo WHERE co = '0068';

--Get Job Descriptions, client codes and client names and put in spsi_CJEntriesSummary_Jobs
--Not used by ramsey et al. 01/20/2010  DA


DELETE FROM spsi_CJEntriesSummary_Jobs WHERE co = '0068' AND process = @process

INSERT INTO spsi_CJEntriesSummary_Jobs(co, process,entrydate,calendarId,gl1,gl2,gl3,gl4,gl5,gl6,total, jobCode, jobDesc, ClientCode, ClientName)
SELECT co, process,entrydate,calendarId,gl1,gl2,gl3,gl4,gl5,gl6,total, '', '', '', ''
FROM spsi_CJEntriesSummary CJES
WHERE CJES.co = '0068' AND process = @process
--for this client jobCode is in gl3 and client data is in CDept2 and client code is the first 4 digits of the job code
UPDATE spsi_CJEntriesSummary_Jobs SET jobCode = '', ClientCode = '' WHERE co = '0068' AND process = @process
--set job description
UPDATE CJES_J SET jobDesc = description
FROM spsi_CJEntriesSummary_Jobs CJES_J INNER JOIN CJob ON CJES_J.co = CJob.co AND CJES_J.jobCode = CJob.jobCode
WHERE CJES_J.co = '0068' AND process = @process
--set client description
UPDATE CJES_J SET ClientName = name
FROM spsi_CJEntriesSummary_Jobs CJES_J INNER JOIN CDept2 ON CJES_J.co = CDept2.co AND CJES_J.ClientCode = CDept2.cc2
WHERE CJES_J.co = '0068' AND process = @process


---------------------------------------------------------------------------------
--This section creates entries for a custom GLExport for PointClick Nursing care software
DELETE FROM spsi_PointCLickFile WHERE co = '0068' AND process = @process

--pay Checks
INSERT INTO spsi_PointCLickFile(co,checkNumber,gl1,description,debit,credit,process,transactionDate,FiscalYear,FiscalPeriod)
SELECT co,checkNumber,'1010-00' as account,'Operating Bank Account' as Description,
0 as Debit, netCheck as credit,process,checkDate,YEAR(checkDate) as FiscalYear, CAST(MONTH(checkDate) as int) as fiscalPeriod
FROM EPayHist
WHERE co = '0068'
AND netCheck <> 0
AND process = @process

--GL Entries
INSERT INTO spsi_PointCLickFile(co,checkNumber,gl1,description,debit,credit,process,transactionDate,FiscalYear,FiscalPeriod)
SELECT co,'' as checkNumber,GL1, GL5 + GL6 as description,
CASE WHEN total >= 0 THEN total ELSE 0 END as debit,
CASE WHEN total < 0 THEN 0-total ELSE 0 END as credit,
process, entryDate as transactionDate,
YEAR(entryDate) as FiscalYear, CAST(MONTH(entryDate) as int) as fiscalPeriod
FROM spsi_CJEntriesSummary
WHERE co = '0068' AND process = @process
AND GL1 NOT IN('1010-00','1010-01')--Eliminates the cash entries since we are posting checks

--misc checks and transfers
INSERT INTO spsi_PointCLickFile(co,checkNumber,gl1,description,debit,credit,process,transactionDate,FiscalYear,FiscalPeriod)
SELECT co,0 as checkNumber,'1010-00' as GL1, id + checkType as  description,
CASE WHEN amount < 0 THEN 0-amount ELSE 0 END as debit,
CASE WHEN amount >= 0 THEN amount ELSE 0 END as credit,
process,checkDate as transactionDate, YEAR(checkDate) as FiscalYear, CAST(MONTH(checkDate) as int) as fiscalPeriod
 FROM CMiscCheckHist WHERE co = '0068' AND process = @process
AND checkType <> 'Agency'-- these will be picked up by the agency transfer. they are on garn service

/*----------------------------------------------------------------------------------------------
 --------------------------------------------
----Section to create custom GL output file and email it
--------------------------------------------
--check for existence of the temp table and drop if it is there
IF OBJECT_ID('Millennium.dbo.temp0068GLExport', 'U') IS NOT NULL
DROP TABLE temp0068GLExport;

--for testing
--DECLARE @process as int
--SET @process = 2010052801




DECLARE @co as varChar(10)
DECLARE @cmd as VarChar(4000)
DECLARE @filename as varChar(250)
DECLARE @fullFilePath as varChar(1000)

SET @co = '0068'
--Get results and dump in  a temp table

SELECT checknumber as ReferenceDescription, GL1 as account,Description,debit,credit ,
CONVERT(varChar,transactionDate,101) as EffectiveDate, FiscalYear,FiscalPeriod
INTO temp0068GLExport
FROM spsi_PointCLickFile WHERE co = @co AND process = @process
ORDER BY gl1

SET @filename = @co + '_' + CAST(@process as varChar(10)) + 'GLExport.csv'
SET @cmd = 'bcp Millennium.dbo.temp0068GLExport  out  \\spsisql\M3Shared\SPSI\' + @co + '\temp\' + @filename + ' -T  -f\\spsisql\M3Shared\SPSI\' + @co + '\FORMAT_' + @co + 'GLExport.fmt'
EXEC master.dbo.xp_cmdshell @cmd

--for testing
--DECLARE @co as varChar(10)
--DECLARE @process as int
--SET @co = '3201'
--SET @process = 2010052601

--DECLARE @filename as varChar(250)
--SET @filename = @co + '_' + CAST(@process as varChar(10)) + 'TrustOK401k.csv'
--DECLARE @cmd as VarChar(4000)

SET @fullFilePath = '\\spsisql\M3Shared\SPSI\' + @co + '\' + @filename
SET @cmd = 'COPY /B \\spsisql\M3Shared\SPSI\' + @co + '\0068GLHeader.txt+' + '\\spsisql\M3Shared\SPSI\' + @co + '\temp\' + @filename + ' ' + @fullFilePath
EXEC master.dbo.xp_cmdshell @cmd

DROP Table temp0068GLExport

--for testing
--DECLARE @co varChar(10)
--DECLARE @process int
--SET @co = '0068'
--SET @process = 2010052601

DECLARE @name varChar(250)
DECLARE @subject1 varchar(250)
DECLARE @body1 as varchar(1000)

SELECT @Name = name FROM CInfo WHERe co = @co
SET @subject1 = ' A payroll has been processed for Client# ' + @co + ' - ' + @name

SET @body1 = 'This is an automated message from Southwestern Payroll. Do not reply to this message.'
             + char(13) + 'For assistance contact support at 918-388-3320 or support@swpay.com' + char(13) + char(13)
             + 'A payroll has been processed for ' + @name + ' for process ' + CAST(@process as varChar(10))
             + char(13)
             + 'One or more files are attached to this email for your review.  If the payroll is reprocessed, you will receive this email again with the new data.' + char(13) + 'Thank you!'

  EXEC msdb..sp_send_dbmail
  @Profile_name = 'SQLReports',
  @recipients = 'darin@swpay.com',
  @subject =  @subject1,
  @body = @body1,
  @file_attachments = @fullFilePath

SELECT * FROM CInfo WHERE co = '0068';
COMMIT Tran GLAdditions;
END

which table is the trigger on?
do you reference that table in the procedure?


Your trigger is incorrectly written in that it doesn;t cater for multiple
update SQL (many rows being affected by the one statement).


i'd have thought that you should create a batch cmd file to execute both the
BCP and the copy ... that way you only have the one xp_cmdfile interaction to deal
with in the stored procedure.


are you still running in development or is this a production problem?

Thanks for any insight you can provide.  I realize how hard this could be without full knowledge of all the outside factors.  I am really hoping you can shed some light on what kind of evironmental factors would cause code to behave differently when it is run ad hoc versus inline.

The Trigger table is called SJob.  It is not referenced in the SP.  The design of the system using the SJOB table makes multiple inserts impossible in this scope.

I have considered making a batch file but I wasn't sure how that would change things or fix this issue. I am also considering wether this is a possible problem between a 64 bit machine interacting with a 32 bit environment.  I have considered running the batch file from the C:\windows\syswow64 as I have heard this provides a 32 bit environment on a 64 bit machine.

This code is in testing. One side effect is that the third party software which creates the SJob record gets hung up when  the state of the job record gets set to 'done' which is what fires the trigger.  then somehow it goes back to 'running' and then never completes, which causes dependent processes not to complete.

I have no doubt there are better ways to do many of the things we are doing, but I am not a full time developer, I wear many hats and sometimes just have to find a solution that 'works'.

I will research the way to fix the trigger as you suggest for future reference.  I only have 4 in production as we try to limit their use.

Thanks for your help.

i would suggest that you create a job to execute the stored procedure
and start that from within the trigger instead...

using EXEC msdb.dbo.sp_start_job N'YOURJOBNAME'

how long is the BCP and EMAIL taking to produce when you invoke the procedure directly?

do you know how the third party software is waiting for the status to change?

have you contacted the third party to see what they suggest for this requirement?

I will try this.  Can you elaborate as to why this would change things?

When I execute the code it runs virtually instantly.

The third party creates multiple "jobs" that need processing. some are dependent on data from others to work.  They sit in the table in an 'active' state until the dependency is satified by the controlling job finishing and being set to done, and then they are allowed to process.

The third party vendor does not support the use of triggers (even though they generally work fine)  so they won't be much help.  We are augmenting the functinality of the system to overcome some shortcomings.

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.