April 23, 2010 at 1:21 pm
I'm working on an Excel 2003 VBA script where several spreadsheets are opened, refreshed (they contain a SQL query and a VBA macro that massages the data obtained from SQL) and e-mailed to a user, all automatically.
The problem I'm running into is that when I run that script on my PC, the e-mailing part does not work because files are attached to the e-mails.
I think I know what the problem is: the script runs on my PC, SQL Server is on a server in our computer room, and I have an "authentication" problem, as described below, from the BOL, under "sp_send_dbmail", Remarks section:
"Database Mail uses the Microsoft Windows security context of the current user to control access to files. Therefore, users who are authenticated with SQL Server Authentication cannot attach files using @file_attachments. Windows does not allow SQL Server to provide credentials from a remote computer to another remote computer. Therefore, Database Mail may not be able to attach files from a network share in cases where the command is run from a computer other than the computer that SQL Server runs on."
What do I need to do to resolve this issue?
Obviously, I could install Excel on the SQL Server server and run everything locally on that machine, but I figure there has to be a way to get it done on my PC?
April 24, 2010 at 10:23 am
Set up a folder and file share on the DB Server for storing file attachments, ensure the SQL Server service account has read access to this folder.
As part of your procedure, copy the Excel files to this folder.
You can then attach these files to the email using the local path as part of your call to sp_send_dbmail.
April 26, 2010 at 8:59 am
Nothing like missing the obvious...
Thanks for your help!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply