DTS will not send attachment using sp_send_cdosysmail

I have created a DTS that exports the results of a query to an excel
sheet and the delivers the xls to a group of users.
The DTS will send the the email but not the attachment; however, if I
execute the same statement using QA, the attachment works fine. I have
the DTS authenticating to the location of the file using xp_cmdshell
earlier in the process.
Below is the SQL statement, I am using:
DECLARE @.Body varchar(4000)
DECLARE @.EmailUsers VARCHAR(1024)
DECLARE @.Attmt VARCHAR(1024)
SELECT @.Body = 'Attached is the most recent CustCD customer
listing.'
SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
FROM EmailUsers
WHERE (CustCD = 1)
SELECT @.Attmt='\\Server1\C$\c3.xls'
EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
XLS',@.Body, @.Attmt
Does anyone have any ideas?
Thanks,
ChrisAssuming that the connection in your Execute SQL Task uses a database in
which the sp_send_cdosysmail is not defined, you might want to try to use a
three-part name for calling the sp_send_cdosysmail e.g. EXEC
master.dbo.sp_send_cdosysmail ...
-- Oskar
"ChrisP" wrote:
> I have created a DTS that exports the results of a query to an excel
> sheet and the delivers the xls to a group of users.
> The DTS will send the the email but not the attachment; however, if I
> execute the same statement using QA, the attachment works fine. I have
> the DTS authenticating to the location of the file using xp_cmdshell
> earlier in the process.
> Below is the SQL statement, I am using:
> DECLARE @.Body varchar(4000)
> DECLARE @.EmailUsers VARCHAR(1024)
> DECLARE @.Attmt VARCHAR(1024)
> SELECT @.Body = 'Attached is the most recent CustCD customer
> listing.'
> SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
> FROM EmailUsers
> WHERE (CustCD = 1)
> SELECT @.Attmt='\\Server1\C$\c3.xls'
> EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
> XLS',@.Body, @.Attmt
> Does anyone have any ideas?
> Thanks,
> Chris
>|||If the suggestion below does not help you could try to set on the logging for
the package and check if the UNC path is indeed accessible by executing for
example EXEC master.dbo.xp_cmdshell 'dir \\Server1\C$\'
-- Oskar
"Oskar" wrote:
> Assuming that the connection in your Execute SQL Task uses a database in
> which the sp_send_cdosysmail is not defined, you might want to try to use a
> three-part name for calling the sp_send_cdosysmail e.g. EXEC
> master.dbo.sp_send_cdosysmail ...
> -- Oskar
> "ChrisP" wrote:
> > I have created a DTS that exports the results of a query to an excel
> > sheet and the delivers the xls to a group of users.
> > The DTS will send the the email but not the attachment; however, if I
> > execute the same statement using QA, the attachment works fine. I have
> > the DTS authenticating to the location of the file using xp_cmdshell
> > earlier in the process.
> > Below is the SQL statement, I am using:
> > DECLARE @.Body varchar(4000)
> > DECLARE @.EmailUsers VARCHAR(1024)
> > DECLARE @.Attmt VARCHAR(1024)
> > SELECT @.Body = 'Attached is the most recent CustCD customer
> > listing.'
> > SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
> > FROM EmailUsers
> > WHERE (CustCD = 1)
> > SELECT @.Attmt='\\Server1\C$\c3.xls'
> > EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
> > XLS',@.Body, @.Attmt
> >
> > Does anyone have any ideas?
> > Thanks,
> > Chris
> >
> >