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
ORDER BY gl1
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