Hi there,
We are having quite a bit of trouble with the report server failing to send emails out using sql database mail.
The report sever creates the pdf files fine and as far as I can tell the permissions on the folder is ok however we get the following error each time which seems to be coming from the database mail component.
Error sending email:
SQLSTATE = 42000
Microsoft OLE DB Provider for SQL Server
C:\impftp\report\SystemTableListing_14032.pdf is not a valid file or the process is unable to access the file.
The report server application runs as the same user account as the SQL server service account with both on the same box. I have tried using a network share and a local path for the reports folder but both have the same issue.
The tessituraservices application pool runs on a different server under the applicationpoolidentity and I have tried allowing the machine account of this server to access the reports folder but the same issue occurs.
Strangely it broke after we moved the live DB to another SQL server but I got it working however it stopped on the weekend and I have no idea why it won't work.
Any help would be much appreciated
Thanks
Mike Cornthwaite
Hi Mike
as the report server is on your DB, you aren't accessing via the network, so that takes one headache away. (You probably know this already) If you were, SQL would need to be running as a domain user, and that domain user would need to have read/write file permissions on the unc share/security.
If SQL Engine is running as a windows user, this shouldn't be needed. If SQL is running as a system account; For folder access, take a note of the security (so you can put it back if it makes no difference) in c:\impftp and c:\impftp\report and add Group: EVERYONE to c:\impftp and subfolders.
My last suggestion (which worked for us when we moved servers) is to put a delay in the SP that does the mailing. When we moved to a new server, the process ran too quickly, so the file was still being written to the disk when the SP was trying to mail it out. We ended up changing the SP and *TEMPORARILY* adding an XP_CMDSHELL "dir" on that folder and storing the output in a new table. This showed the file didn't exist yet. I believe we put a 2 or 5 second delay in the stored procedure to give the report server chance to finish writing the file before attempting to mail it.
Hope this is of some use
Wayne