Database Mail Attachments Filling Up The System Drive!
Apr 30, 2007
Hi all,
Has anyone run into this issue before?
My company uses database mail extensively for alerting and the like, and most emails sent have attachments. We've put in place procedures for removing emails older than a month from the MSDB database, however we've now had a client server completely run out of the disk space due to attachments being stored in the C:Documents and SettingsUserLocal SettingsTemp folder!?!
When I check the sysmail_delete_mailitems_sp sproc, it only removes entries in the MSDB tables, and doesn't remove these temporary files in the temp directory.
Does anyone know why SQL Server stores them in the temp directory of the service account user, as well as how we can remove them (apart from manually deleting the files), as we need to have an automated process for cleaning up emails, and at the moment SQL Server 2005 doesn't handle this very well.
I've checked this on our test server as well, and we're getting the same there, and this server is running the latest SP2 + hot fixes.
The script is working fine if i manually provided file path to @file_attachments. it is showing error i passes the variable @Loc3 to @file_attachments. Below is the error if i passes the variable to @file_attachments parameter.
(1 row(s) affected) (1 row(s) affected) (1 row(s) affected) Msg 22051, Level 16, State 1, Line 0 Attachment file 'E:DatabaseTannaFinanceReportDetail_Reimbursement_1_15_Aug2015.csv is invalid.
I have a database [CarlosDB] that currently has it's .MDF on E: and I need to move the x2 .NDF data files off C: to E:data using a single T-SQL statement:
Looking at the file configuration above, what would be the most logical way as a DBA / SQL Server 2014 Std to move the NDF files to live w/ the MDF file using:
I have a DTS that creates multiple .zip files in a folder. Each zip file has a different name. My goal is to attache all the zips to a single email in a Send Mail Task. I have tried to do so with something like "C:Folder*.zip", but have had no luck. Any suggestions?
I was wondering if anyone has extended the standard CDOSYS Mail Stored Procedure (SP) to allow it to send the results of a query as an attachment?
I have set up a SP for CDOSYS Mail as outlined in the following link: http://support.microsoft.com/default.aspx?id=kb;de;312839&sd=tech
Currently I am using the old SQL Mail (xp_SendMail). But due to the problems with losing the MAPI connection and other limitations, I have been forced to find another solution. Using SQL Mail, I was able to add a query parameter and attach the results of the query to the email. I need to have the same functionality in CDOSYS Mail
I need to email a report that contains a date in the report name. Can I build the file name with script and use a variable in the attachments field on the send mail task. Any advice or suggestions would be appreciated.
Does anyone know what would cause SQL MAil or Exchange to save all messages and some 1K att*.txt files to the WINNT directory? This is filling up the hard drive on a production system so it's kind of urgent.
I have system database and user database file are present in G,H and W drive.The process is going to be - copy data from G to S, H to T, W to U. Rename G to X, H to Y and W to Z. Rename S to G, T to H and U to W. Reboot the servers. The original G, H and W will then be X, Y and Z. The old S will be the new G, old T will be H and old U will be W. My question is that after doing this whether my SQL server will start or not
Installed SQL Server 2000 Enterprise trial a week ago on XP Pro. Installed new Seagate 80G HD; used Seagate's utility to copy old C: to new drive as new boot drive. All seems to work fine, except, when booting up, SQL server doesn't start. When I try to start it manually I get the following:
Could not start SQLSERVER service on the local computer. Error 3: the system cannot find the specified path.
It's so frustrated that I dont really knwo what is going on and why is that and I have tried ages to try to figure it out and nothing really helps.
I have already moved the data files of tempdb database to a drive with enough space (many GB space left still), but then again I got the problem which run out all of the system drive space when I run a query against a large table? Why is that? And how to figure it out?
Please help me and thanks a lot in advance for your kind advices and help and I am looking forward to hearing from you shortly.
Any time you try to run a report now it crashes trying to create or find a temp file in a wierd path... exception is below... this happens for any request to the report server at all! all we changed was we removed a drive that was no longer used on the server! This path doesnt even exist! any idea where this path is stored or how to fix it? thanks!
Exception Information:System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.IO.DirectoryNotFoundException: Could not find a part of the path 'c:windowssystem32inetsrv\%SYSTE~1Tempxonfiwn.tmp'. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access) at System.CodeDom.Compiler.TempFileCollection.EnsureTempNameCreated() at System.CodeDom.Compiler.TempFileCollection.AddExtension(String fileExtension, Boolean keepFile) at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources) at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(CompilerParameters options, String[] sources) at System.CodeDom.Compiler.CodeDomProvider.CompileAssemblyFromSource(CompilerParameters options, String[] sources) at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, XmlSerializerCompilerParameters xmlParameters, Evidence evidence) at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, XmlSerializerCompilerParameters parameters, Assembly assembly, Hashtable assemblies) at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence) at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[] mappings, Evidence evidence) at System.Web.Services.Protocols.SoapServerType..ctor(Type type, WebServiceProtocols protocolsSupported) at System.Web.Services.Protocols.SoapServerProtocol.Initialize() at System.Web.Services.Protocols.ServerProtocol.SetContext(Type type, HttpContext context, HttpRequest request, HttpResponse response) at System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type, HttpContext context, HttpRequest request, HttpResponse response, Boolean& abortProcessing) --- End of inner exception stack trace --- at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at Reschini.Reporting2005.ReportService.ReportingService2005.LogonUser(String userName, String password, String authority)
Hi there I need to fill database table randomly with 5 ordered numbers such as 43566 , 78578 , 92565 , .. to gain approximately 100000 row of a table . Is there a query ?
Being a very novice SQL Server administrator, I need to ask the experts a question.
How do I go about moving a database from 1 drive to another? The source drive (C is local to the server, but the target drive (E is on a Storage Area Network (SAN), although it is still a local drive for the server. I want to move the database from C: to E:. Can someone provide me with instructions?
I have phrased this question here once before but no one could answer it, so please let me know if you think you know what the problem is.
I have a Test Environment on Server2 restored from the production environment on Server1. Production is publishing to a subscriber on Server3. The problem is that even though I have truncate on checkpoint = TRUE set for the Test environment, the log fills up (all the way, not even truncate transaction with no_log fixes it).
The log size is 750MB, with a data portion of about 3GB. Is this somehow related to the fact that SQL Server still thinks the tables in the test environment are marked for replication? I have noticed it believes this becuase I am not allowed to drop tables on Test, even though Test is not even set up as a publishing database.
Other information: 1) The recovery interval on Server2 is 3 minutes. 2) I am frustrated
Hi all, I am using system.net.mail namespace to send the mails.My problem is can i send the mail to more than one people using a single MailAddress object.Below is the code i am working with..MailAddress addressFrom = new MailAddress("admin@ecanarys.com", "admin"); string[] strTemplateNames = Regex.Split(strTemplateAdded.Trim(','), ",");SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnString"].ToString()); conn.Open();for (int i = 0; i < strTemplateNames.Length; i++) {SqlCommand cmd = new SqlCommand(); cmd = conn.CreateCommand();cmd.CommandText = "select emailID from Users where UserName in(select distinct Approvers from ApproversList where template='" + strTemplateNames[i].ToString() + "')"; SqlDataReader dr;string strTo = ""; dr = cmd.ExecuteReader();while (dr.Read()) {strTo = dr[0].ToString() + ";"; } dr.Close();MailMessage mail = new MailMessage(); mail.To.Add(new MailAddress(strTo));-------//Exception: The string is not in the specified formatmail.Subject = "This is a testmail."; mail.Body = "This is a mail which is send to the approvers for the following templates.";SmtpClient client = new SmtpClient();client.Host = "100.100.110.198"; client.Send(mail); mail.Dispose(); } conn.Close();
Lokendra writes "I have configured the Database mail profile and account in Sql Server 2005 but the mail is not sending and showing the following error message:
Error,235,The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2007-03-05T15:16:07). Exception Message: Cannot send mails to mail server. (Mailbox name not allowed. The server response was: Sorry<c/> that domain isn't in my list of allowed rcpthosts.). ),3000,90,,3/5/2007 3:16:07 PM,sa
but while in the same mail set up in previous instance of sql server 2005 the message was sending very well. After installing new instance of sql server 2005 the problem is arising.
Anybody can tell me that what I can do so that i can send mail using the SMTP databasemail account."
Our company use yahoo business mail server for our corporate mails. I know that I can configure database mail with SMTP. But when I try to configure database mail account with yahoo bizmail, I cannot do that. It gets configured but when I test it it doesn't send any mails. Do I need to have any special condiguration for this. SMTP address is smtp.bizmail.yahoo.com. Also I have specified the Authentication using my user name and password. Please help
I have the following code on a form: <asp:SqlDataSource ID="building_a" runat="server" ConnectionString="<%$ ConnectionStrings:vol1ConnectionString4 %>" SelectCommand="SELECT [E_MAIL] FROM [Sheet4$] WHERE ([BUILDING] LIKE '%' + @BUILDING+ '%')" OnSelecting="building_a_Selecting"> <SelectParameters> <asp:Parameter DefaultValue="a" Name="BUILDING" Type="String" /> </SelectParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="building_b" runat="server" ConnectionString="<%$ ConnectionStrings:vol1ConnectionString4 %>" SelectCommand="SELECT [E_MAIL] FROM [Sheet4$] WHERE ([BUILDING] LIKE '%' + @BUILDING+ '%')" OnSelecting="building_b_Selecting"> <SelectParameters> <asp:Parameter DefaultValue="b" Name="BUILDING" Type="String" /> </SelectParameters></asp:SqlDataSource> <asp:TextBox ID="subj" runat="server" Width="500px">Subject</asp:TextBox><br /><br /> <asp:TextBox ID="messg" runat="server" Rows="20" TextMode="MultiLine" Width="500px">Message</asp:TextBox><br /><br /> <asp:Button ID="bldga" runat="server" Text="Building A" OnClick="Button1_Click" /><br /><br /><asp:Button ID="bldgb" runat="server" Text="Building B" OnClick="Button2_Click" /><br /><br /> <asp:Label ID="resultLabel1" runat="server" ForeColor="red"></asp:Label> And I have the following code behind:protected void Button1_Click(object sender, EventArgs e) {SmtpClient smtpClient = new SmtpClient(); MailMessage message = new MailMessage(); try {MailAddress fromAddress = new MailAddress("me@mydomain.com", "My Name");MailAddress toAddress = new MailAddress("them@theirdomain.com"); message.From = fromAddress; message.To.Add(toAddress); message.Subject = subj.Text;message.IsBodyHtml = false; message.Body = messg.Text;smtpClient.Host = "myhost"; smtpClient.Send(message);resultLabel1.Text = "Email sent!"; }catch {resultLabel1.Text = "Send failure"; } } The queries get e-mail addresses from a SQL database for all people who work in a particular building. The buttons will send an email if I give a To address. How do I write the code so that clicking on the "Building A" button will send the e-mail to the addresses from the query results individually? Any ideas?
I have some problems with sending Mail over System.Net.Mail ... I have made a C# Class which sends mail, and it works fine. Now I have added this assembly to SQL Server 2005, I made the SQL function and so on.
When I try to run it I get the following message: Msg 50000, Level 16, State 1, Procedure SendMail, Line 114 Request for the permission of type 'System.Net.Mail.SmtpPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
I want to send a simple mail using DATABASE MAIL feature in SQL SERVER 2005.
I've defined a public profile. I've enabled Database Mail stored procedures through the Surface Area Configuration .
but I can't send a mail with sp_send_dbmail stored procedure in 'msdb' database .
when I execute sp_send_dbmail in the Managment Studio the message is "Mail queued" but the mail is not sent.
Could it be related to Service Broker?Because the Surface Area Configuration indicates:'this inctance does not have a Service Broker endpoint'.If so, how should I make an endpoint?
here is the log file after executing sp_send_dbmail:
1) "DatabaseMail process is started"
2) "The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2007-03-08T00:49:29). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it)."
The DatabaseMail90.exe is triggred ,so the mail is transfered to the mail queue but DatabaseMail90.exe couldn't give the mail to SMTP server.The promlem is what should I do to make DatabaseMail90.exe able to connect to the server?
I am able to run SSIS packages as SQL Server Agent jobs with a Control Flow items "File system task", if I move a file (test.txt) from a drive (c on the server (where SQL Agent jobs run) to a subdirectory on the same drive. But, if I try to move a file on a network drive, the package fail.
I created the db with the attached script and I am able to access ituntil I reboot the server. I've tried enabling flag 1807 via the SQLserver service and the startup parameters of the instance. In allcases the database always come up suspect after a reboot. There wasone instance where I was able to recover, but I am not sure how thathappened.Does anyone have an idea of how I can reboot the server without thedatabase becomming suspect?USE MASTERGODBCC TRACEON(1807)GO--DBCC TRACEOFF(1807)--DBCC TRACESTATUS(1807)GOCREATE DATABASE ReadyNAS ON( NAME = ReadyNAS_Data,FILENAME = '\NAS1NASDiskSQL ServerReadyNASReadyNAS_Data.mdf',SIZE = 100MB,MAXSIZE = 20GB,FILEGROWTH = 20MB)LOG ON ( NAME = ReadyNAS_Log,FILENAME = '\NAS1NASDiskSQL ServerReadyNASReadyNAS_Log.ldf',SIZE = 20MB,MAXSIZE = 100MB,FILEGROWTH = 10MB)
I have been trying to use openrowset with a shared drive, and even though the share has "full control" permissions granted to "everyone" and the accout that SQL runs under has been granted explicit full control permissions I am unable to open the file which itself has no security on it.
Can I not use a \ path and only use mapped drives?
Thanks
below works...
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:5People.xls', [Sheet1$])
below doesn't work...
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\cluster02FileManager5People.xls', [Sheet1$])
I have TFS installed on one machine, and the SQL Server database on another. I made the unfortunate mistake of installing SQL Server to the OS drive (C : ), so the TFS database is writing to this drive. How can I switch this to write to another drive (e.g. E:)?
Hello, I need help with the setup of my sql server express 2005 and more specifically, with the location of the database files. Normally, database files are located on the c: drive. In my situation, being in a high school environnement, I need to have the database files on the network drive ... lets call it g:drive. The reason why it cannot be on c:drive is because this drive is protected using DeepFreeze. Now, I was able to change the location of the new drive as I went into the properties window of the sql server instance but when I try to create this new database, sql gives me grief ;-| I am from the group of people that thinks that to anything, there is a solution and am hoping that to this problem, there is hope of a solution. Can anyone help? Thank you
I have a 'need an opinion' question. I've always installed the system databases (master, msdb, model, tempdb) on the root drive (c:) with the sql application. Then I was thinking, the only things that really should be on the root drive is the sql app itself (and any other required apps). So...my question is, do y'all install the master, etc on a separate drive other than the root, ie d: or e:? The reason I ask is that if the tempdb database has alot of activity, and the database resides on the root drive, could it affect sql performance?
Just wondering. I just started working in a 'virgin' sql environment (setting up sql servers from scratch), and want to have sql installed in the best possible way for performance.
