When I am executing the following sendmail code,it is apending the results to the mail.Actually, I want the results to be sent as attachment,in .txt format.
Any help is greatly appreciated.
exec master..xp_sendmail
@recipients='Tpolasp@hotmail.com',
@query='c2usa..sp_report_daily_pc',
@attach_results='true',
@subject='Daily fastquotes'
Thanks.
When I am executing the following sendmail code,it is apending the results to the mail.But I want the results to be sent as attachment,in .txt format.
exec master..xp_sendmail
@recipients='Tpolasp@hotmail.com',
@query='c2usa..sp_report_daily_pc',
@attach_results='true',
@subject='Daily fastquotes'
Interestingly,even if I use @attachments option by giving a specific file name,then also it is sending the mail in which the contents of the attached file are appended to the mail!
Is there anything that I shall configure somewhere to make it behave properly?
I am using 7.0 standard edition with no SP.Any help is greatly appreciated.
Thanks.
--------------------------------------------
Declare @filename char(22)
Declare @cmd char(100)
Select @filename = '"' + 'D:Feedsest_' + substring(convert(char(12), getdate(), 12), 3, 4) + substring(convert(char(12), getdate(), 12), 1, 2) + '"'
Select @cmd = "xp_sendmail @recipients = 'test@test.com'
, @attachments = " + @filename + " "
exec(@cmd)
Go
--------------------------------------------
Above T-SQL step is causing the following error:
Server: Msg 103, Level 15, State 7, Line 6
The identifier that starts with 'xp_sendmail @recipients = 'test@test.com'
is too long. Maximum length is 128.
Any help??Thanks.
I have a step like this: I am getting the following error:How to code @attachments in quotes?? thanks.
The name specified is not recognized as an
internal or external command, operable program or batch file.
(2 row(s) affected)
Here is my T_SQL step:
Declare @filename char(22)
Declare @cmd char(100)
Select @filename = 'D:Feedsest_' + substring(convert(char(12), getdate(), 12), 3, 4) + substring(convert(char(12), getdate(), 12), 1, 2)
EXEC xp_cmdshell @recipients = 'test@test.com'
, @attachments = @FileName
Hi
I'd like to access to a SendMail object with an ActiveXScript.
I access it by the Tasks property of the package object.
I can retrieve the name or the description but I cannot access to the specific properties of the SendMail object (fileAttachments, ccline, ...)
How can I do it ?
THanks
hi
how to sendmail in sql2000
hi,
at the end of my ssis package flow, i want to invoke a sendmail task.
now, when configuring the sendmail service, i see i can only enter the name of my smtpserver. however, i see the task fails, probably because a user and a password is missing (i have it, but don't see where to type it in the SSIS).
(i want to use the smtp server of my internet provider)
anyone can help?
I have a sendmail task following a data flow task. How can I set "To" attribute on the sendmail task before running it ? for example, read a config table and detemrine who the mail should be sent to...
View 1 Replies View RelatedI have setup SQL Mail on a SQL Server 2000 (service pk 3a) with outlook 2002.
SQL Mail give me the success message when I test the profile.
When I send a test message to an operator from SQL Agent it works fine.
When I send a message from the Outlook 2002 client (logged in as the Service-SQL domain account), I can send a message without a problem.
However, when I send an xp_sendmail message (xp_sendmail @recipients='email@address',@message='test',@subje ct='test') it tells me 'Mail Sent' in the result window, but the email never arrives or shows up in the 'sent items' folder in outlook 2002.
Anyone else have this issue.
Thanks - Rick
For example the result of an Execute T-SQL Statement task.
This task does not have a hook-up for variable assignment, the SendMail can take in a variable. How do you pass the result of EXEC T-SQL statement be assigned to the variable in the SendMail task?
Hello All,
I have an Execute SQL Task and a SendMail in the control flow of my package.
If the Execute SQL Task inserts records in the Database, I want the SendMail task to be executed.
But if Execute SQL Task does not inserts even a single record in the Database, I dont want the SendMail task to be executed.
How can I achieve this...
Thanks,
Kapadia Shalin P.
Does exchangeServer accept in SendMail task?
only SMTP?
who knows?
For internal mail server , it's only via exchange server.
Hi, with some help today I was able to get my stored procedure runningand have the results emailed to me. However, this is how its showingup:Accounting_Year WK_IN_FYEAR LocationGL_AccountCol Data Difference--------------- ----------- ------------------------------------------------------------------ -------------------- --------------------2007 49 Test1500-001-2587872.0200 -2587872.0200 .00002007 49 Test2500-001-3344713.5000 -3344713.5000 .00002007 49 Test3500-001Is there anyway to line them up side by side properly? When i have twocolms selected the format comes out ok. Thanks for all the helpagain!Here is the sp:CREATE PROCEDURE [dbo].[spEmailVariance](@SubjectLine as varchar(500),@EmailRecipient VARCHAR(100))ASDECLARE @strBody varchar(5000)set @SubjectLine = 'Weekly Flash Update'SET @strBody ='Select statement'exec master.dbo.xp_sendmail@recipients= 'XX@XXXX.com',@subject= @SubjectLine,@query = @strbodyRETURNGO
View 2 Replies View RelatedHow do I Convey Information using Notification - can someone please explain?
View 4 Replies View RelatedHi all--I am writing a DTS package looking to back up all databases in sequence from a specific instance of SQL Server. I am calling CDOSYS in a stored procedure from support.microsoft.com to send email upon failure. I have two types of SQL tasks I am writing into the DTS package:
1. backup database <database> to disk = '<drive>:<path>.bak' with init
2. Upon failure of a particular database to back up to file, execute the following SQL task:
declare @srvname varchar(128),
@db_name varchar(128),
@msg nvarchar(4000)
SELECT @srvname = @@servername
select @db_name = db_name()
set @msg = 'Hi--check your database backups. The ' + @db_name + ' database backup on ' + @srvname + ' has failed.
Thanks--SQL Server';
SELECT @srvname, @db_name, @msg
(Many thanks to Whitney Weaver and SQL_Menace for help with sections of this code)
Upon success of #1 and/or #2, go on to the next database, repeating cycle of the SQL tasks above for the next database in the task. For example, this logic goes like:
1. Back up master database; if fails, send email in SQL task; if either task is successful, goto database #2;
2. Back up model database; ...<etc>.
My problem is the current database set in @db_name. I apparently need to reset this variable for each database that gets backed up, and I can't seem to get these set as values to be passed from SQL Task #1 to SQL Task #2. Any suggestions on how I might achieve this?
- Jonathan
Hi,
I have a sendmail task in my ssis package. In the From field, if i hardcode my from and to address as user@company.com it works fine and mail is sent. But i want to read the from and to values from a database table. SO i defined the package level variables and getting the values into the variables. SO at From if i specify the variable name it gives a compiel time error"the address in th from line is mal formed. it is either missing @ character or not valid".
how do i specify teh variable for from address? I feel, may be i can use expression, but i don't know how to do that.
Can anyone help me with this?
Thanks in advance,
I am trying to send a csv file with 15000 records via the database mail in SQL Server 2014. The problem is that when I open my email the csv only contains 209 records. I have tried the same thing in SQL Server 2012 and it works as expected - it sends the 15000 records in the csv.
I have tested this on several sql servers with 2014 edition on them, and I have the same issue on all of them. The query breaks off at different points on each sever - for example one of them breaks off at 209 records as i said above, another one at 307. The last record always gets truncated at the same place. The csv attachment size it's about 64 kb - which is well below the 4MB limit i've configured the database Maximum File Size bytes parameter.
What i am doing basically is creating a job that is meant to execute a stored procedure and send the results in a csv in an email. The stored procedure is something like:
select col1, col2, col3
from table A1
where col1 > 1000 order by col1
I have multiple sequence containers in my package. I only want to have one sendmail task for the failure/completion of the package. If I put the sendmail task in the last sequence container and the first seqence fails, the sendmail task will not be reached and therefore, no email will be sent out.Is there a way to have one sendmail task for all the sequence containers and allow it to send mail regardless of what sequence fails/completes?
View 4 Replies View Relatedif I have the following code:
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.SendMailTask;
class TestSendMailTask
{
public static void Main()
{
Package pkg = new Package();
ConnectionManager smtpCM;
smtpCM = pkg.Connections.Add("SMTP");
smtpCM.Name = "SMTP Connection Manager";
smtpCM.ConnectionString = "smtphost";
Executable exe = pkg.Executables.Add("STOCKendMailTask");
TaskHost thSendMailTask = (TaskHost)exe;
{
thSendMailTask.Properties["SmtpConnection"].SetValue(thSendMailTask, "SMTP Connection Manager");
thSendMailTask.Properties["ToLine"].SetValue(thSendMailTask, "someone1@example.com");
thSendMailTask.Properties["CCLine"].SetValue(thSendMailTask, "someone2@example.com");
thSendMailTask.Properties["BCCLine"].SetValue(thSendMailTask, "someone3@example.com");
thSendMailTask.Properties["FromLine"].SetValue(thSendMailTask, "someone4@example.com");
thSendMailTask.Properties["Priority"].SetValue(thSendMailTask, MailPriority.Normal);
thSendMailTask.Properties["FileAttachments"].SetValue(thSendMailTask, "C:\test_image.jpg");
thSendMailTask.Properties["Subject"].SetValue(thSendMailTask, "Testing the SendMail Task");
thSendMailTask.Properties["MessageSourceType"].SetValue(thSendMailTask, SendMailMessageSourceType.DirectInput);
thSendMailTask.Properties["MessageSource"].SetValue(thSendMailTask, "This is only a test.");
}
DTSExecResult valResults = pkg.Validate(pkg.Connections, pkg.Variables, null, null);
if (valResults == DTSExecResult.Success)
{
pkg.Execute();
}
}
}
-------
How do I make it a workable package so it compiles , w/ javadoc style comments and instructions, so, other people can use it?
This morning I can not connect to our SQL Server 7.0 whatever from client or server. The error message which I list below:
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++
A connection could not be estabished to server--Timeout expired
Please verfy SQL Server is running and check your SQL Server registration properties and try again.
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++
We use windows NT authentication. We did not do any change on NT. The SQL Server daily schedule job usally stoped at 10:00AM, but today from the Window NT Task Manager, we can see that the SQL Server is still running untill now.
Please help!!!
hi, I have settup up sql mail and did the following:
1. created an E-mail account and configured Out look by creating a pop3 mail profile. tested it by sending and receiving mail, that is ook
2. I Created one domain account for MSsqlserver and Sql Agent service. both services use same account and start automatically in the control panel-services
3. I used the profile that I created in outlook to test the sql mail but got an error:
Error 22030 : A MAPI error ( error number:273) occurred: MapiLogon Ex Failed due to MAPI
Error 273: MAPI Logon Failed
I really do not know what went wrong. I followed the steps from bol and still having a problem. Am I missing something.
I do have a valid email account
I do have a valid domain account
I tested outlook using the email account and it worked. so why sql server does not recognise MAPI.
My next question, How to configure MAPI in Sql server if what I did was wrong.
Hi, I have 2 windows 2000 server in cluster with sql server 2000 enterprise edition installed.
I have activated the Server-Requested Encryption by using the sql server network utility (Force Protocol Encryption). After this, I have stoped sql server service. But I can't start it at this moment.
The error is:
19015: The encrypton is required but no available certificat has been found.
Please help me to start sql server.
Thanks.
Michel
Hello,
I am facing a huge problem in my sql server database using access as a front end.The main problem is trying to execute queries "views" ,since they reside on sql server now,and using variables or parameters in reports and forms to filter on this query.
Ex.
how can the following be implemented using the same query but in sql server?
Access
------
SELECT MAT_Charts.YYYYMM
FROM MAT_Charts
WHERE ((([Area_Code] & "-" & [GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key]))
GROUP BY MAT_Charts.YYYYMM;
It is specifically this statement in which I am interested:
[GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key]))
Thank you very much for your concern.
Hi everybody,
I have a batch which needs to be run every day night at 2:00 am.I Using At command but it is not working. if u have idea, please let me know
I AM USING THE FOLLOWING COMMAND :
AT 2:00AM C:SCHED.BAT
Thanks in advance
Krishna
hi all
its urgent really urgent
please reply soon
I am getting error in sysindexes when i run dbcc checkdb on a production db.
the error is Server: Msg 8928, Level 16, State 1, Line 1
please help me to remove this
all the options of dbcc checkdb as well as table are not helping me
thanks in advance
Hi Guys
i write a thread before asking for help with reading an uploaded csv file,
i have my code, it reads the csv file and currently displays it in a datagrid but what i actually want is to take the read information and import it into my sql express db.
Heres the code<script runat="server">
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
If (IsPostBack) Then
Grid1.Visible = True
Else
Grid1.Visible = False
End If
End Sub
Sub UploadButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
'Save the uploaded file to an "Uploads" directory
' that already exists in the file system of the
' currently executing ASP.NET application.
' Creating an "Uploads" directory isolates uploaded
' files in a separate directory. This helps prevent
' users from overwriting existing application files by
' uploading files with names like "Web.config".
Dim saveDir As String = "Data"
' Get the physical file system path for the currently
' executing application.
Dim appPath As String = Request.PhysicalApplicationPath
' Before attempting to save the file, verify
' that the FileUpload control contains a file.
If (FileUpload1.HasFile) Then
Dim savePath As String = appPath + saveDir + FileUpload1.FileName
' Call the SaveAs method to save the
' uploaded file to the specified path.
' This example does not perform all
' the necessary error checking.
' If a file with the same name
' already exists in the specified path,
' the uploaded file overwrites it.
FileUpload1.SaveAs(savePath)
' Notify the user that the file was uploaded successfully.
UploadStatusLabel.Text = "Your file was uploaded successfully."
Else
' Notify the user that a file was not uploaded.
UploadStatusLabel.Text = "You did not specify a file to upload."
End If
End Sub
Sub DisplayButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:inetpubwwwrootMerlinLocalPostOfficeAppData;Extended Properties=""text;HDR=NO;FMT=Delimited"""
Dim objConn As New OleDbConnection(sConnectionString)
objConn.Open()
Dim objCmdSelect As New OleDbCommand("SELECT * FROM csv.txt", objConn)
Dim objAdapter1 As New OleDbDataAdapter()
objAdapter1.SelectCommand = objCmdSelect
Dim objDataset1 As New DataSet()
objAdapter1.Fill(objDataset1, "csv.txt")
Grid1.DataSource = objDataset1.Tables(0).DefaultView
Grid1.DataBind()
objConn.Close()
End Sub
</script>
My csv file does not have headers so the default value is F1, F2, F3 F4, F5,F6, F7, my database has the following columns, ID,AddressLine1,AddressLine2,AddressLine3,AddressLine4,AddressLine5,AddressLine6,Postcode
I need to know how to import the information direct into the db rather than displaying it on the page, ive tried but im really new to this and cant get it to work. I cant use DTS or bulk insert as the server this will go on doesnt have sql on it, the db is an MDF file so is transportable with the app.
Thanks for your help
Hello
I'm trying to execute the following
INSERT INTO @dbName.dbo.AP_TransMain SELECT * FROM Inserted WHERE Pay_Id=Inserted.Pay_Id
but its giving me an error becuase of @dbName which is a variable decleared as the followingDECLARE @dbName as varchar(100)
the Database name is assigned to @dbName
but i can't take the value of @dbName to combine it to the rest of the statment
INSERT INTO @dbName.dbo.AP_TransMain SELECT * FROM Inserted WHERE Pay_Id=Inserted.Pay_Id
what should i do ! please help :(
Hi, I am trying to make a backup of my database using Sql-DMO
When I do the backup on my server, my code runs fine. But when I backup from across the network from another computer, the database is saved on the server and not on the computer from where I am executing my application. How set SQLl-DMO to backup on the computer from where I am?
here is my code (found on the net)... Any suggestions greatly appreciated!
Cursor = Cursors.WaitCursor
'create an instance of a server class
Dim my_srv As SQLDMO._SQLServer = New SQLDMO.SQLServerClass
'connect to the server
my_srv.Connect("servername", "userid", "password")
'create a backup class instance
Dim my_backup As SQLDMO.Backup = New SQLDMO.BackupClass
'set the backup device = files property
my_backup.Devices = my_backup.Files
'set the files property to the File Name text box
my_backup.Files = Me.txtFilePath.Text
'set the database to the chosen database
my_backup.Database = "MYDB"
'perform the backup
my_backup.SQLBackup(my_srv)
MsgBox("Database successfully backed up.", MsgBoxStyle.Information)
Cursor = Cursors.Default
How can i insert a word document or an a image file into a column of image datatype using T-SQL.
View 2 Replies View RelatedHello,
I've a bactch file that is ran by the user and it looks for files and if it doesnot exist it waits for it and when it exsits, through isql it truncates the table and bcp in the file. In bcp files the loginname used in isql has dbo rights to truncate the table and the batch file also has password. The user who runs the script can also view the batch file and can know the password, Is there any way I could rewrite it so that the user cann't view the password or other way like SQL DMO or DTS package
Any help is appreciated
Thanks
Rea
I need to FTP files to another drive on the network. Do I need to setup an FTP server on the other computer where I will send file to?
Thanks!!
Ravi.
Greetings!!!
Can any one tell me what is the database log size for a datafiles (tables and Indexes 500MB)
On What basis database log size is estimated for the Database Creation of 500MB ?
On What percentage will be the dump file size and log file size with respect to Database file size??
Can any body help!!!!!!!!!!
Thanks in Advance
A Junior DBA
Hello,
I've a table which has clustered index on one column and I tried to run begin transaction and then 2000 insert statements and then commit transaction, the transaction took 15 min and then it hung up the table, I could not access the table I had to stop the process, the only info I received was the table had 4517 locks, I've no clue how it got so many locks, there was db backup running as I had scheduled 15 min backup. could anyone help me that inside each open transaction how many insert statement is advisible and how many maximum locks does sql server works on a table and what could have gone wrong in my situation.
Thanks
Rea