Formating The Output Of Mails

Nov 16, 2001

Hello,

I'm using xp_sendmail in the sp and in @query I do select * from table1.
The result also page,s
The formate is not at all readable and even if @attach_results = 'TRUE'
In the text file its not formatted as it sends mail to my manager I want it to be formatted and looks good. Can anyone help me or Is there any way to get the results in readable format.

Thanks in Advance for the help

Rani

View 2 Replies


ADVERTISEMENT

FORMATING THE SQL OUTPUT

May 17, 2001

Hi!

I have couple of questions. Anybody can help me.

1. How can I format the output in T-SQL coming like as one can do in SQL*PLUS (ORACLE)?

2. I decalerd a Cursor and using that It is generating the output. I jut want to dispaly only 100 rows, its mean that I want to control the output.

Please give your feedback

View 1 Replies View Related

Conditionally Formating Subtotal Output

May 22, 2007

I created a subtotal for my column group by right-clicking on the group and selecting subtotal. I would like to add the condition to the subtotal, that if the value is less than 0 to print zero and not the negative subtotal amount. Is there a way to do this? I haven't been able to find a way to add an expression to the greyed out subtotal field. Thanks.

View 1 Replies View Related

Formating Of Columns In Output Of A SQL Statement In Query Analyzer

Jan 16, 2004

Hi guys

I want to format the result of a SQL Statement carried out in the query analyzer. Example:

suppose that you have this table:

col1 col2
------------------------- -----------------------
abcdefg bdbsjjdasjdh
bdfjsjdf hasdasjdasj
jhsdjhd asjdhashdas
hasjdhj ahsjdhajshdj

and I want this output:

col1 col2
---------- ----------------
abcdefg bdbsjjdasjdh
bdfjsjdf hasdasjdasj
jhsdjhd asjdhashdas
hasjdhj ahsjdhajshdj

View 7 Replies View Related

Mails

Jan 10, 2007

I have the following code to send email using DTS and CDO objects.

I am getting run time error. Dont know why ?

Any configurations to be made ??



'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
dim sch

sch = "http://schemas.microsoft.com/cdo/configuration/"
Set cdoConfig =CreateObject("CDO.Configuration")

cdoConfig.Fields.Item(sch & "sendusing") =2
cdoConfig.Fields.Item(sch & "smtpserver") = MPBAKOREX01.corp.mphasis.com"

cdoConfig.fields.update

Set cdoMessage =CreateObject("CDO.Message")
Set cdoMessage.Configuration = cdoConfig
cdoMessage.From = "shanmuga.r@mphasis.com"

cdoMessage.To = "shanmuga.r@mphasis.com"

cdoMessage.Subject = "Sample CDONTS NewMail"
cdoMessage.HTMLBody="<html><b>rajaganapathy</b></html>"
msgbox "before "
cdoMessage.Send
msgbox "Send"

Set cdoMessage = Nothing
Set cdoConfig = Nothing


Main = DTSTaskExecResult_Success
End Function


View 5 Replies View Related

Auto E-mails

Jan 23, 2004

Hello,

I have a SQL server though a hosting company and I am trying to send autoemails using xp_sendmail. The permissions were set and I used the following command to test it.

EXEC master.dbo.xp_sendmail
@recipients='tracey@yahoo.com',@subject='test',@me ssage='testing
sql stored procedure'

It gave me a message saying "Mail sent" but there none in my e-mail box.

How do I set yp the SQL Mail server, right? Please help. I don't know what is happening.

Thanks,
Tracey

View 3 Replies View Related

Mails Whereby Sql 2005

Feb 18, 2008

Hi everyone
i get an error messgae running the following code

DECLARE @mailist VARCHAR(max)
SET @mailist=''
SELECT TOP 1 @mailist=@mailist + email +';'
FROM
email.dbo.mytable
SET @mailist=STUFF(@mailist,LEN(@mailist),1,'')
SET @mailist='''' + @mailist + ''''

EXEC msdb.dbo.sp_send_dbmail
@profile_name='my_mail_profile',
@recipients=@mailist,
@subject='mysubject',
@body_format='html',
@body=
'
<html>
<head>
<title>
problems with send_dbmail
</title>
</head>
<body>
this is not working
</body>
</html>
'

The error says:
quote:Syntax error in parameters or arguments. The server response was: 5.5.4 Invalid Address)
At the other hand, if i assign a straightforward @recipints address such as
EXEC msdb.dbo.sp_send_dbmail
@profile_name='my_mail_profile',
@recipients=my@mail.com
It works
Anybody know why ?
Thanks

View 3 Replies View Related

Is It Possible To Extract Mails From Outlook Into

Feb 21, 2008

sql 2005 table ?
Hi everyone,

I need to assemble information from my outlook account into a database table. Is it possible to insert "title" of a set of mails recieved into a table ? sender's name ? recipient's name? body? etc...

thanks

View 2 Replies View Related

SQL Server Agent - E-Mails

Jul 23, 2005

Hi FolksCould anyone please tell me if there is a maximum number of E-Mailsthat SQL Server Agent can send at any one time ?I'm thinking of using it to send out matches from the database thatregistered users have previously specified.ThanksDavid

View 2 Replies View Related

Repeated Alert E-mails

Mar 10, 2008

Hi
I have created a Percent Log Used Alert with a threshold of 85% and am getting e-mailed with Database Mail, the problem is that I continue to get e-mailed repeatedly with the same e-mail until I disable the Alert. Is there any way to have it just e-mail it once?

Thanks
Pam

View 3 Replies View Related

Plain Text Mails

Apr 19, 2007

How can I set the default body_format in database mail. I would like to send a warning with plain text format (from the alerts), that i'll get on my mobile phone, but the message's format is html... always...and I don't get the sms-s.

View 5 Replies View Related

Subscription E-mails To Other Domains

Apr 3, 2008

I am testing the e-mail subscriptions to my hotmail account in anticipation of sending e-mail reports to our business partners. I have made the following to the RSreportserver.config file:

<PermittedHosts>
<HostName>mydomain.com</HostName>
<HostName>hotmail.com</HostName>
</PermittedHosts>

I then stopped and restarted the reporting services service. However, the test subscription fails with the following message:

The e-mail address of one or more recipients is not valid.


Any idea what I may be missing?


View 1 Replies View Related

Error In Sending Mails !

Jan 9, 2007

I am trying to send email thro DTS using Activex Script.

Here is the code :



'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
const SMTP_SERVER = "MPBAKOREX01.corp.mphasis.com"



set iMsg = CreateObject("CDO.Message")
set iConf = CreateObject("CDO.Configuration")
Set Flds = iConf.Fields

With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPickup
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTP_SERVER
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 90
.Update
End With


With iMsg
Set .Configuration = iConf
.To = "ausrg@yahoo.com"
.From = "shanmuga.r@mphasis.com"
.Subject = "TEST"
.HTMLBody = "jfldsajfldk;sajf ;lksadjf;lkasdjlfkjasdlkfjlasdkj flkdsajflsadjf ljasdlf a"
.Send
End With


Main = DTSTaskExecResult_Success
End Function






When i am executing this , i am getting the following error :


Error Source : Microsoft Data Transformation Services (DTS) Package

Error Description : Error Code: 0

Error Source= CDO.Message.1

Error Description: The "SendUsing" configuration value is invalid.





Error on Line 27





How to solve it ?

View 5 Replies View Related

Mails Are Stuck In The Queue Folder

Jul 23, 2005

Hello All,I wrote one code for sending mail by using Asp.Net+C#, My code is asfollows.private void btnSend_Click(object sender, System.EventArgs e){try{MailMessage email = new MailMessage();if(rblHtml.Checked){email.BodyFormat=MailFormat.Html;}if(rblText.Checked){email.BodyFormat=MailFormat.Text;}email.From=txtSender.Text;email.To=txtReceiver.Text;email.Subject=txtSubject.Text;email.Body=txtBody.Text;string attach1=null;if(opdAttachment1.PostedFile !=null){HttpPostedFile ulFile=opdAttachment1.PostedFile;int nFileLen=ulFile.ContentLength;if(nFileLen>0){strFileName=Path.GetFileName(opdAttachment1.Posted File.FileName);strFileName="attachment/"+strFileName;opdAttachment1.PostedFile.SaveAs(Server.MapPath(st rFileName));MailAttachment attach=newMailAttachment(Server.MapPath(strFileName));email.Attachments.Add(attach);attach1=strFileName;}}SmtpMail.SmtpServer="127.0.0.1";SmtpMail.Send(email);if(attach1 !=null){File.Delete(Server.MapPath(attach1));}lblMessage.Text="Your email has been sent successfully";catch(Exception ex){lblMessage.Visible=true;lblMessage.ForeColor=Color.Red;lblMessage.Text=ex.ToString();}When i press "SEND" button, the mail is delivered and i receive message"Your email has been sent successfully". But the mail is not deliver totarget destination.The Mail is stored in "C:Inetpubmailrootqueue" folder. After sometime, it is autometically stored in "Badmail" folder.I use Windows 2000 Professional+.net 1.1 version.I need some solution ... I am relatively new in .net .. Can anybodyhelp me please..

View 1 Replies View Related

Sending Mails Through SQL Server 2005

Aug 1, 2007



Hi,
I have a need to shoot and automated mail at regular intervals to clients. I got the following code for sending email from SQL Server but get an error.

ALTER PROCEDURE [dbo].[sp_SMTPMail]

@SenderName varchar(100),

@SenderAddress varchar(100),

@RecipientName varchar(100),

@RecipientAddress varchar(100),

@Subject varchar(200),

@Body varchar(8000),

@MailServer varchar(100) = 'localhost'

AS



SET nocount on



declare @oMail int --Object reference

declare @resultcode int



EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT



if @resultcode = 0

BEGIN

EXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost', @mailserver

EXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @SenderName

EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress', @SenderAddress



EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, @RecipientName, @RecipientAddress



EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject

EXEC @resultcode = sp_OASetProperty @oMail, 'BodyText', @Body





EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL



EXEC sp_OADestroy @oMail

END



SET nocount off


The error is

SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.


Can any one help me please

View 1 Replies View Related

Sending Mails In HTML Format !

Jan 9, 2007

In the DTS, using SEND MAIL TASK option !

I want to send the message in html format. When i receive in outlook 2003, it should display in html format.

How to do this ?

View 1 Replies View Related

Mails I Try To Send Whereby Sql Server 2005 And Fail To Do So

Feb 18, 2008

Hi everyone
i get an error messgae running the following code

DECLARE @mailist VARCHAR(max)
SET @mailist=''
SELECT TOP 1 @mailist=@mailist + email +';'
FROM
email.dbo.mytable
SET @mailist=STUFF(@mailist,LEN(@mailist),1,'')
SET @mailist='''' + @mailist + ''''

EXEC msdb.dbo.sp_send_dbmail
@profile_name='my_mail_profile',
@recipients=@mailist,
@subject='mysubject',
@body_format='html',
@body=
'
<html>
<head>
<title>
problems with send_dbmail
</title>
</head>
<body>
this is not working
</body>
</html>
'


The error says:

quote:

Syntax error in parameters or arguments. The server response was: 5.5.4 Invalid Address)



At the other hand, if i assign a straightforward @recipints address such as
EXEC msdb.dbo.sp_send_dbmail
@profile_name='my_mail_profile',
@recipients=my@mail.com

It works
Anybody know why ?
Thanks

View 11 Replies View Related

Need Help To Create SSIS Package To Send Mails

Sep 25, 2006

Hi All,

I am new to SQL Server 2005, I need Help to create a SSIS package for the below taks,
My task is Refer a Column [Status] in the Logtable which is used to maintain the logs for the running processes, When ever any Rows with the "FAIL" status in Status column occurs in the LogTable then get the Respective ErrorRowNumber and get the Details of the Error from other 2, 3 tables which will be a simple SQL statements, when you get the Error Details then mail these details to the given Email id.

For this I need a help In creating a SSIS package which will continuously check the Status and if any FAIL status occurs then the Error details need to mail to mail id which are stored in the config files of SSIS package.

If any one has better idea to send the mails then please let me know.

if any online tutorial is available that will also help.
Thanks in advance

Dhananjay

View 1 Replies View Related

Using ASP Mail To Send Mails From MSSQL 2000 Server

Mar 13, 2004

Hello,
I am using a script to send mails from MSSQL 2000 server. The script is trying to use the ASPMail object is located within a SQL Job or DTS package in the SQL database. It is not being run through an ASP page. The ASPMail object works fine if running in an ASP page running on win2003 server. It DOES NOT work when trying to create the object within SQL ActiveX VB script running on mssql server. If I am using this script in a job and use Microsoft's Enterprise Manager it only tells me that the job failed. If I try to use this script in a DTS package I get the error: 'Can not create object ("SMTPsvg.Mailer"). If I use a third database manager I get the following error: 'Execute permission denied on object xp_ServiceControl database' Please let me know what is causing this and how to fix it. The script goes as below
'*************************************************
' Visual Basic ActiveX Script
'*************************************************
Dim Mailer
Set Mailer = CreateObject("SMTPsvg.Mailer")
Mailer.FromName = "Webmaster"
Mailer.FromAddress = "webmaster@domainname.com"
Mailer.RemoteHost = "mail.domainname.com"
Mailer.AddRecipient "user", "mailid@domainname.com"
Mailer.Subject = "Test ASP Mail"
Mailer.BodyText = "Test"
x = Mailer.SendMail
Set Mailer = Nothing
Any help is appreciated
Regards

View 3 Replies View Related

Integration Services :: How To Create SSIS Package With Mails

Aug 17, 2015

I am new to SSIS. I have a requirement that from a flat file need to import the data into SQL Server DB(SQl Server2008r2).

1.When the file doesn't found in dir need to send a mail.
2.error reading And writing ,on which record the error occurred and capture the error details and need to send mail.
3. In success also need to send a mail.

View 3 Replies View Related

Cannot Send Mails To Mail Server (The Operation Has Timed Out)

Apr 30, 2013

I have configured database mail in sql server 2008 r2 on my local machine.

While testing I have observed that some of mails are not getting triggered.

Log file shows following message-

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 7 (2013-04-30T10:23:57).

Exception Message: Cannot send mails to mail server. (The operation has timed out.)

I have written on SP for sending mails which sends 94 mail to different ail address out of which only 35 are getting delivered.

View 7 Replies View Related

Database Mail Sends Test Mails Successfully, But Sp_send_dbmail Fails

Apr 15, 2008



Hi all,

I am having a few problems with Database Mail and wondered if anyone could assist. It sends test mails fine, but when I run the following script:

(I've changed my email address in this post to test@test.com they are accurate in the scripts.)


EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Test Mail',

@recipients = 'test@test.com',

@body = 'Sent by sp_send_dbmail',

@Subject = 'SQL Server Email Test Email';


It gives the following errors.

(from the above script)


mailitem_id = 16
profile_id = 2
recipients = test@test.com
copy_recipients = NULL
blind_copy_recipients = NULL
subject = SQL Server Email Test Email
body = Sent by sp_send_dbmail
body_format = TEXT
importance = NORMAL
sensitivity = NORMAL
file_attachments = NULL
attachment_encoding = MIME
query = NULL
execute_query_database = NULL
attach_query_result_as_file = 0
query_result_header = 1
query_result_width = 256
query_result_separator =
exclude_query_output = 0
append_query_error = 0
send_request_date = 2008-04-15 10:50:03.827
send_request_user = COMPANYTest.Test
sent_account_id = NULL
sent_status = failed
sent_date = 2008-04-15 10:50:04.000
last_mod_date = 2008-04-15 10:50:04.513
last_mod_user = sa


(from the test mail)


mailitem_id = 11
profile_id = 2
recipients = test@test.com
copy_recipients = NULL
blind_copy_recipients = NULL
subject = Database Mail Test
body = This is a test e-mail sent from Database Mail on UKDEVSQL1
body_format = TEXT
importance = NORMAL
sensitivity = NORMAL
file_attachments = NULL
attachment_encoding = MIME
query = NULL
execute_query_database = NULL
attach_query_result_as_file = 0
query_result_header = 1
query_result_width = 256
query_result_separator =
exclude_query_output = 0
append_query_error = 0
send_request_date = 2008-04-15 09:51:46.530
send_request_user = COMPANYTest.Test
sent_account_id = 4
sent_status = sent
sent_date = 2008-04-15 09:51:46.000
last_mod_date = 2008-04-15 09:51:46.610
last_mod_user = sa


sysmail_event_log gives this error for mail item 16 (the scripted email):

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2008-04-15T10:11:41). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Requested action not taken: message refused). )

View 2 Replies View Related

DateTime Formating.

Oct 5, 2006

I am trying to query a calendar table that has the [start] datetime of an event and then a [title] of the event. I am trying to display them as;* 8:00 AM Get to work* Independance daysome of the events have a start time and some only have a date. the ones that do not have a start time have a start time of 12:00:00 How do I query the two fields and only show the start time & title of the events that have a start time and just the title for the ones that do not have a start time.. I've tried this...select Event = (CONVERT(CHAR(8),start,8) +' ' + title) from Calendar where CONVERT(CHAR(8),start,8) <> '12:00:00'It does display the start and event title but only for the ones that have a start time. And how do I get the 8:00 AM format for the time? Thanks for any help.Mark

View 3 Replies View Related

Formating Date

Aug 6, 2002

How do I get the following format using SQL server ( SQL QUERY ??)
yymmddhh.

In Oracle I can do simple...

Select to_char(datecolumn, 'YYMMDDHH') from tablename;

RESULT > 02080603
-- YEAR, MONTH, DATE, HOUR.

What is that I need to do to get the same result on sql server.?

tks

View 1 Replies View Related

Text Formating

Nov 9, 2006

i have this:
\FILInternalBuisnessArea*.*
\FILInternalBuisnessArea
\FILInternalBuisnessArea*.qvw
\FILInternalBuisnessArea empfileDB.mdb
\FILInternalPublic Area*.*
\FILInternalPublic Area
\FILInternalPublic Area*.xls
\FILInternalPublic Areaacro*.doc
\FILInternalPublic Areaeconomi*.xls
\FILInternalPublic AreaeconomiGroup01FileToSave*.xls
\FILInternalPublic AreaeconomiGroup01Unit01FileToSave*.xls

And i want to strip away whatever is after the last '' so the output should look like this
\FILInternalBuisnessArea
\FILInternalBuisnessArea
\FILInternalBuisnessArea
\FILInternalBuisnessArea
\FILInternalPublic Area
\FILInternalPublic Area
\FILInternalPublic Area
\FILInternalPublic Area
\FILInternalPublic Areaeconomi
\FILInternalPublic AreaeconomiGroup01
\FILInternalPublic AreaeconomiGroup01unit01

datatype = varchar (500)

thx alot in advance

//Mr

View 7 Replies View Related

Formating The Record

Apr 7, 2008

Hi all,
i am using sql server 2005.
let me explain the scenario.
I have to built a report where I have to find out no of call made by an executive to a perticular customer during a year

my table record look like

empid custid product date and other field
em111 iimran aaaa 01-04-2008 ........
em111 iimran bbbb 01-04-2008 ........
em111 iimran cccc 01-04-2008 ........
em111 xxxxxx aaaa 01-04-2008 ........
em111 xxxxxx 2222 01-04-2008 ........
em111 iimran aaaa 02-04-2008 ........
em111 iimran bbbb 02-04-2008 ........
em111 iimran cccc 02-04-2008 ........
em111 xxxxxx aaaa 02-04-2008 ........
em111 xxxxxx 2222 02-04-2008 ........

.
problem is it has to implement in this manner

Srno custname jan feb mar apr may june july aug sep oct nov dec
1 iimran 0 4 0 5 6 7 0 3 5 7 3 1

please suggest some idea.


Please Help

View 1 Replies View Related

Formating A String In Sql

May 5, 2008

Ok so here is an explanation of what i need. I have a table that has emails in it. I need to get those emails to show up as verticle line of names. these will then be sent out via email so i have the following line of code to put them into a string:

select @emails1 = isnull (@emails1 + '; ','' + char(13))+ char(13) + email from emaillist

I have tried putting Char(13) in multiple areas trying to get line break. but the email sends the list in a straight line.

can anyone help me. also if you need more info let me know i will give it to you.

example of desired output
xxxx@dgfasg.com
sfgasg@asgahi.com
saghna@asogh.com

View 20 Replies View Related

Formating Problem

Dec 18, 2007

hi all
can anybody tell me how to get double underline for the text box value in reporting services
am using sql server reporting services 2000

View 3 Replies View Related

Condtional Formating

Oct 26, 2007



Hi all,

This expression gives me an error anyone know why?


=IIF(Fields!M.Value = 0,"Average",(MonthName(Fields!M.Value)))

Thanks

View 4 Replies View Related

Drill Down Formating

May 22, 2006

Does anyone know what code I'd use in an expression to conditionally format based on if the drill down is expanded or collapsed?

Thanks.

in an
in-an
inane
Inna
Ian
Edit...
Revert to "inan"conditionally
conditional
conditionals
conditionality
Edit...
Revert to "conditionaly"

View 3 Replies View Related

Formating Datetime

Mar 14, 2008

I have a decimal time field that I would like to convert to "hh:mms"

If I try to use =cdate(Fields!Avg_Visit_Duration.Value) I get #Error in my result. The error message is:

Conversion from type 'Double' to type 'Date' is not valid.

0.0044612654 should be shown as 00:06:25. I can do this very easy in SSAS or Excel... SSRS is giving me fits.

edit: made question clearer. nlm

View 5 Replies View Related

Date Formating - Military

Apr 13, 2007

Hello all,
I am trying to import an excel spreadsheet that has a specific date column. The column is saved as military time. For example, one of the records is:
 04-12-2007 09:30
 But, when i import the data into sql 2005, the above date is displayed as:
2007-04-12 09:30
 I need it to display like the same way that it's being displayed in the excel spreadsheet.
My field in sql is nvarchar(100) and it still doesn't work.
 
Any ideas?Thanks in advance.

View 3 Replies View Related

Date Formating For Summarization

Oct 1, 2003

How do I format a date field to monthly and/or yearly only in order to summarize totals in another field.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved