We help IT Professionals succeed at work.
Get Started

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

DarinAlred asked
Last Modified: 2012-05-09
I have a lengthy stored procedure that is called by a trigger.  It has been working a long time.  I recently added some code to the procedure to create a file using bcp and then email the file using sp_send_dbmail.  When I run the stored procedure in SQL management studio it works great.  When I create the condition to make the trigger fire and call the SP it fails.

What could cause this different behavior?  I have changed nothing about the way the SP is called, and I appended this additional code into the existing stored procedure:

----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

SET @filename = @co + '_' + CAST(@process as varChar(10)) + 'GLExport.csv'
SET @cmd = 'bcp MyDB.dbo.temp0068GLExport  out  \\server\directory\' + @co + '\temp\' + @filename + ' -T  -f\\server\directory\' + @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)) + 'tesFile.csv'
--DECLARE @cmd as VarChar(4000)

SET @fullFilePath = '\\server\directory\' + @co + '\' + @filename
SET @cmd = 'COPY /B \\server\directory\' + @co + '\GLHeader.txt+' + '\\server\directory\' + @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 data file has been processed for Client# ' + @co + ' - ' + @name

SET @body1 = 'This is an automated message from A Big Company. Do not reply to this message.'
             + char(13) + 'For assistance contact support at 999-123-4567or support@mycompany.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 data 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@mycompany.com',
  @subject =  @subject1,
  @body = @body1,
  @file_attachments = @fullFilePath
Watch Question
Top Expert 2011
This problem has been solved!
Unlock 1 Answer and 10 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE