Sp_send_cdosysmail - Parameter Inside Body Of Email

Jul 23, 2005

I currently have a web form posting back to a SQL table using a Stored
Procedure. Part of this SP is that it pulls data from another table
and inserts a new row into the registration table.

I want to have a trigger on the registration table that will fire when
the row is inserted which will use the sp_send_cdosysmail sproc to send
an e-mail to the user.

However, I want to be able to include the value of one of the fields
within the BODY of the message. I can't find a way to include
parameters/variables within the Body of a message using
sp_send_cdosysmail and it's driving me nuts.

Here's what I have in a sproc (not a trigger) that executes
sp_send_cdosysmail...I currently pass a parameter for the "To" e-mail
address and that works fine.

_________________________________________________
EXEC sp_send_cdosysmail
'fromemailaddress@testcompany.com',
@stremail, <--This is the Parameter passed for the "To" e-mail addy
-->

'Test Subject',
'Test Body,
Additional Text
Additional Text
<--THIS IS WHERE I WANT TO PUT THE PARAMETER-->
Additional Text
Additional Text'

__________________________________________________ _


Is there any way to do this?

The sp_send_cdosysmail I used is the standard MS one..Here it is for
reference:


Thanks for any help offered!

Elliot



CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000)

/************************************************** *******************

This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/d...s_messaging.asp

************************************************** *********************/
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)

--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/d...n_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','1'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value',
'SMTPServer'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.
IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END

-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

View 2 Replies


ADVERTISEMENT

Placing A Web Link In An Email Body

Nov 28, 2007

I'm generating emails using sp_send_dbmail. Everything works perfectly except for one thing. In the body of the email I need to show a link to a web page (eg http://myweb/login.aspx).

The problem is that the received email shows the "link" as plain text, ie it is not a clickable link. I've tried adding char(13) (and char(10) and both) after the link text but that doesn't help.

Is there a way to make the link text a real link when received by Outlook? (All recipients will be using Outlook if that helps).

Thanks,

John

View 6 Replies View Related

Insert Trigger Not Getting Row Data For Email Body

Oct 18, 2007

hello,
need help with a simple trigger i have been working on. the trigger automatically sends me an email out when a record is inserted, how ever i can't seem to get the row column data into the email. The part i do not understand is that I get the row column data  information in the email if I update the row.
This is for 2005 SQL
Any direction would be greatly appreaciated
OneIDesigned
 

View 5 Replies View Related

SSIS Send Email Html Body

Apr 4, 2008

Hi, I need help please.

I want to send an HTML Email

I have an Htm file that i want to use as my email body on Send Mail Task
MessageSourceType: File Connection
MessageSource: Email.htm

It sends the email with the file in the body but not in HTML format
it outputs the HTML tags + data
eg: <span style="font-size:9.0pt;">Period: 2008.04</span>

I used the same file in a DTS package & that works fine.

Please Assist!

Regards

View 2 Replies View Related

Sending Email From An HTML Template As The Body

Aug 28, 2006

We have a DTS package that sends smtp email from an ActiveX script task. The body of the emails are in template files that we read in and then replace values relating to the customer.

I am looking for suggestions of handling this process from an SSIS package. This is what our existing code does.



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

Function Main()
'********************************************************************************************
' Email results
'********************************************************************************************
Dim oRS, objMessage, fTemplate

Set fso = CreateObject("Scripting.FileSystemObject")

Set oRS =DTSGlobalVariables("ToBeNotified").Value
oRS.MoveFirst

Do While NOT oRS.EOF
Set objMessage = CreateObject("CDO.Message")
Set objMessage2 = CreateObject("CDO.Message")

objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMessage2.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = DTSGlobalVariables("RemoteSMTPServer").Value
objMessage2.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = DTSGlobalVariables("RemoteSMTPServer").Value

'Server port (typically 25)
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMessage2.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

'Type of authentication, NONE, Basic (Base64 encoded), NTLM
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
objMessage2.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

'Your UserID on the SMTP server
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = "CustomerService"
objMessage2.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = "CustomerService"

'Your password on the SMTP server
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "cslogin"
objMessage2.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "cspassword"

objMessage.Configuration.Fields.Update
objMessage2.Configuration.Fields.Update

objMessage.Sender = DTSGlobalVariables("EmailFrom").Value
objMessage2.Sender = DTSGlobalVariables("EmailFrom").Value

objMessage.From = DTSGlobalVariables("EmailFrom").Value
objMessage2.From = DTSGlobalVariables("EmailFrom").Value

objMessage.To = oRS.Fields("ContactEmail").value
' objMessage.To = "me@ourmail.com"

objMessage2.To = DTSGlobalVariables("EmailBCC").Value
' objMessage2.To = "me@ourmail.com"


If oRS.Fields("Rejected").value = 1 Then
'Rejection Message
objMessage.Subject = "electronic request notification"

If oRS.Fields("ProductType").value = "Fixed" Then
Set htmlTextStream = fso.OpenTextFile (DTSGlobalVariables("EmailTemplateRejectedFixed").Value)
Else
Set htmlTextStream = fso.OpenTextFile (DTSGlobalVariables("EmailTemplateRejectedVariable").Value)
End If

Else
'Successful results
objMessage.Subject = "electronic confirmation #" + CSTR(oRS.Fields("ConfirmationId").value)
objMessage2.Subject = "Customer request for hard copy of contract"

'Add Terms and Conditons document
termDocument = DTSGlobalVariables("TermDocumentsRepository").Value + CSTR(oRS.Fields("TermDocument").Value) + ".pdf"

If fso.FileExists(termDocument) Then
objMessage.AddAttachment termDocument
objMessage2.AddAttachment termDocument
End If

If oRS.Fields("ProductType").value = "Fixed" Then
Set htmlTextStream = fso.OpenTextFile (DTSGlobalVariables("EmailTemplateSuccessFixed").Value)
Else
Set htmlTextStream = fso.OpenTextFile (DTSGlobalVariables("EmailTemplateSuccessVariable").Value)
End If

End If

htmlText = htmlTextStream.ReadAll
htmlText = Replace( htmlText , "[OurLogo]" , DTSGlobalVariables("EmailLogo").Value)
htmlText = Replace( htmlText , "[CustomerName]" , oRS.Fields("ContactFirstName").value + " " + oRS.Fields("ContactLastName").value)

If oRS.Fields("Rejected").value = 0 Then
htmlText = Replace( htmlText , "[ConfirmNumber]" , oRS.Fields("ConfirmationId").value)
htmlText = Replace( htmlText , "[OrderDate]" , oRS.Fields("SalesDate").value)
htmlText = Replace( htmlText , "[ProductName]" , oRS.Fields("ProductType").value)
htmlText = Replace( htmlText , "[Months]" , oRS.Fields("TermMonths").value)
htmlText = Replace( htmlText , "[Price]" , oRS.Fields("SalesPrice").value)
htmlText = Replace( htmlText , "[Units]" , oRS.Fields("SalesPriceUnit").value)
htmlText = Replace( htmlText , "[StartDate]" , oRS.Fields("FlowStartDate").value)
htmlText = Replace( htmlText , "[AccountNumber]" , oRS.Fields("AccountNumber").value)
End If

objMessage.HTMLBody = htmlText
objMessage2.HTMLBody = htmlText


objMessage.Send


If oRS.Fields("SendHardCopy").value = True AND oRS.Fields("Rejected").value = 0 Then

' Attach Instructions for Hard Copy delivery
instructionDocument = DTSGlobalVariables("TermDocumentsRepository").Value + "Instructions.txt"

set file = fso.CreateTextFile(instructionDocument, true)
file.WriteLine("The customer has requested a hard copy of their contract to be sent to them.")
file.WriteLine("")
file.WriteLine("Instructions:")
file.WriteLine("1) Print the email")
file.WriteLine("2) Print the attached Terms & Conditions document")
file.WriteLine("3) Mail both items to the address below")
file.WriteLine("")
file.WriteLine("Customer Address:")
file.WriteLine(oRS.Fields("ContactFirstName").value + " " + oRS.Fields("ContactLastName").value)
file.WriteLine(oRS.Fields("BillingAddress1").value)
If oRS.Fields("BillingAddress2").value <> "" Then
file.WriteLine(oRS.Fields("BillingAddress2").value)
End If
file.WriteLine(oRS.Fields("BillingCity").value + ", " + oRS.Fields("BillingState").value + " " +oRS.Fields("BillingZip").value)
file.Close
set file=nothing

objMessage2.AddAttachment instructionDocument

fso.DeleteFile(instructionDocument)

objMessage2.Send
End If

set objMessage = nothing
set objMessage2 = nothing

oRS.MoveNext
Loop

Main = DTSTaskExecResult_Success
End Function

All suggestions are appreciated.

SK

View 1 Replies View Related

Sending File Contents In The Body Of The Email With Xp_sendmail

Jul 23, 2005

I would like to send the contents of a file using xp_sendmail howeverI do not want the file contents to be an attachment.I have no problem sending the file as an attachement.Can anybody give me an xp_sendmail example of how to do this.The results of a query can easily appear in the body of the email butall myattempts to include the contents of a file in the body of the emailhave not worked.TIA

View 1 Replies View Related

How To Send Sql/stored Procedure Output In The Body Of The Email.

Nov 3, 2007

Everyday morning I email the sql query/stored procedure output results to the users, I was wondering if I can use some kind of t-sql code or DTS packages so that I can automate this process where I want to send the sql/stored proc results in the body of the email.

View 7 Replies View Related

How To Incorporate A Table Field Into The Email Message Body Nto As An Attachment?

Oct 5, 2005

Hello everyone,

Please i need your help...

I dont know how to place the field 'strTitle and datBorrowed " in my email? Not as an attachment though....Just write it in the mail as part of message body...

I use this SQL select statement to retrieve the strTitle and datBorrowed fields

strSQL += @"Select replace(strtitle,'[Original Book] - ',''), datBorrowed from tblBooks where convert(varchar(10),datBorrowed,101) = convert(varchar(10),(getdate() - 1),101) ORDER BY strTitle asc";


Now, I have the following code to write the email

static void SendTest()
{

int iEmailLanguage = 0;
MailMessage objMail;
objMail = new MailMessage();
objMail.From = MAIL_FROM;
objMail.To =MAIL_TO;
objMail.Subject = "Books Borrowed Yesterday";
objMail.Body = Dict.GetVal(iEmailLanguage, "EMAIL_MESSAGE");
objMail.Attachments.Add(new MailAttachment(strAttachment));
SmtpMail.SmtpServer = SSMTP_SERVER;
SmtpMail.Send(objMail);
}


And the body of the email is this......


Dict.AddVal(0, "EMAIL_MESSAGE", "*** This e-mail is automatically generated. ***" +
"*** PLEASE DO NOT REPLY TO THIS E-MAIL. ***" +
"" +
"Books Borrowed Yesterday are:" +

"" +
"" +
"Thank you," +
"" +
"eLibrarian" +
"" +
"================================================== ===============" +
"" +
"This e-mail is automatically generated by the Library system." +
"Please do not reply.");



i need to put or wedge the data i got from the SQL Statement into this or after the line "Books Borrowed Yesterday are:" +

So how should i do this?

View 3 Replies View Related

Transact SQL :: Carriage Return In Email Body Within A Select Statement

Jul 2, 2015

I am trying to insert a carriage return in the select statement after the web link where I had highlighted code in bold. When I insert a record into the table, I receive the email with the message body is in single line.I need the result to look like this in the message body:

ALTER TRIGGER [dbo].[SendNotification]
ON [dbo].[TicketsHashtags]
FOR INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from

[code]....

View 2 Replies View Related

Integration Services :: Code To Show StartDateTime And EndDateTime In Body Of Email In SSIS

Nov 23, 2015

Code to show the package StartDateTime and EndDateTime in body of the email in ssis send email task.

View 3 Replies View Related

T-SQL (SS2K8) :: SSIS - Extract Data From Table And Insert In Message Body And Email To User

Jun 25, 2014

What I am trying to do, Extract the data from SQL table and Insert in Email Body and email to user. I got good article on Internet, I follow all steps as it is, but still I am getting error.

Here is the link : [URL] ....

But I am getting Error:

Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.
at System.Text.StringBuilder.AppendFormat(IFormatProvider provider, String format, Object[] args)
at System.String.Format(IFormatProvider provider, String format, Object[] args)
at ST_7f59d09774914001b60a99a90809d5c5.csproj.ScriptMain.Main()

[Code] ....

View 4 Replies View Related

Cannot Find Sp_send_cdosysmail

Nov 14, 2006

Hello, everyone:

I want to send email by sp_send_cdosysmail, but I got error,

Server: Msg 2812, Level 16, State 62, Line 3
Could not find stored procedure 'sp_send_cdosysmail'.

Even though I run the query under master database. Is it not available in SQL Server 2000?

This is my query:

declare @Body varchar(4000)
select @Body = 'This is a Test Message'
exec sp_send_cdosysmail 'someone@example.com','someone2@example.com','Test of CDOSYS',@Body


Any help will be appreciated.

Thanks

ZYT

View 1 Replies View Related

Passing A Query To Sp_send_cdosysmail

Mar 31, 2004

I am looking for any help or hints on how to pass a query into or run a query as sp_send_cdosysmail as the body.

I need some way other than using an attachment to return the results of a query from sp_send_cdosysmail as the body. Any good points in a direction would help!

View 4 Replies View Related

Dynamic SQL && Parameter Inside A SP Not Working

Nov 5, 2006

When I run the query below on my DB, it gives the following error:
Server: Msg 8114, Level 16, State 5, Procedure partstlist ...Error converting data type varchar to float.
Only after I replace the      " + @d + " section with a float numnber such as 39020.5 does it work fineIt's reported that my temporary table named #tbl doesn't exist within the scope of the environment that the EXEC(UTE) command operates.Someone said that I couldn't combine dynamic SQL and temporary tables or table variables...and that I would have to use a global temp table "##tbl" or a permanent table.
So here I have appearantly a "DYNAMiC SQL & TEMPORARY TABLE" problem. That's sure.
But I don't have much knowledge and experience with SQL tables and have difficulty forming a global temp table "##tbl" or a permanent table.and I have to employ the datetime variable dynamically @d inside the Dynamic SQL string.
So what kind of a code should I use here to properly combine Dynamic SQL string with my dynamic datetime variable? I'd be grateful if you could post your code suggestions so that I can try them on my DB ..
alter PROCEDURE partslist@no INT,@dt DATETiMEASDECLARE @s VARCHAR(5000)DECLARE @d floatSET @d = CONVERT (float, @dt2)create TABLE #tbl(pageindex int IDENTITY(1,1) PRIMARY KEY , prt_name varchar(50), prt_country varchar(50) , prt_date smalldatetime, partID int , prt_cat varchar(50),prt_product_type nvarchar(10), producer_company nvarchar(50), producer_city nvarchar(50), prt_released DATETIME, dvalue float, datevalue varchar(26) )BEGiNSET ANSI_WARNINGS OFFSET ROWCOUNT @noSELECT @s = 'INSERT INTO #tbl SELECT prt_name, Countries.Val AS ''prt_country'' , prt_date, partID , partsCategories.Val AS ''prt_cat'' , partsProductTypes.Val AS ''prt_product_type'' , producers.producer_company AS ''producer_company'' , producers.producer_city AS ''producer_city'' ,  prt_released , CONVERT (float, prt_released) , CONVERT (varchar(26) , prt_released, 109) FROM producer_parts  JOIN partsProductTypes ON (producer_parts.prt_product_type = partsProductTypes.ID) JOIN partsCategories ON (producer_parts.prt_cat = partsCategories.ID)JOIN Countries ON (producer_parts.prt_country = Countries.ID) JOIN producers ON (producer_parts.producerID = producers.producerID) WHERE prt_visible = 1  AND CONVERT (float, prt_released) > ' + @d + ' AND CONVERT (float, prt_released) < 39025.5ORDER BY prt_released DESC SELECT * FROM  #tbl'EXEC (@s)SET ROWCOUNT 0ENDGO partslist 10 , '10.10.2006 10:10:10' -- This is just to test the SP.
This code may not be syntatically correct, it's a re-edited version of my code which works fine in my DB.

View 2 Replies View Related

Code Inside! --&> How To Return The @@identity Parameter Without Using Stored Procedures

Oct 30, 2005

Hi.here is my code with my problem described in the syntax.I am using asp.net 1.1 and VB.NETThanks in advance for your help.I am still a beginner and I know that your time is precious. I would really appreciate it if you could "fill" my example function with the right code that returns the new ID of the newly inserted row. 
Public Function howToReturnID(ByVal aCompany As String, ByVal aName As String) As Integer
'that is the variable for the new id.Dim intNewID As Integer
Dim strSQL As String = "INSERT INTO tblAnfragen(aCompany, aName)" & _                                    "VALUES (@aCompany, @aName); SELECT @NewID = @@identity"
Dim dbConnection As SqlConnection = New SqlConnection(connectionString)Dim dbCommand As SqlCommand = New SqlCommand()dbCommand.CommandText = strSQL
'Here is my problem.'What do I have to do in order to add the parameter @NewID and'how do I read and return the value of @NewID within that function howToReturnID'any help is greatly appreciated!'I cannot use SPs in this application - have to do it this way! :-(
dbCommand.Parameters.Add("@aFirma", aCompany.Trim)dbCommand.Parameters.Add("@aAnsprAnrede", aName.Trim)
dbCommand.Connection = dbConnection
TrydbConnection.Open()dbCommand.ExecuteNonQuery()
'here i want to return the new ID!Return intNewID
Catch ex As Exception
Throw New System.Exception("Error: " & ex.Message.ToString())
Finally
dbCommand.Dispose()dbConnection.Close()dbConnection.Dispose()
End Try
End Function

View 7 Replies View Related

SQL Auto Send Email Based On Parameter

May 5, 2008

I would like some help conceptualizing how to acomplish this task. My client has requested the ASP.NET web/SQL Server application send an email to one of our locations based on whether that location complied (via the web app) to each week's report. Here's how the flow would go:
The location checks the webpage weekly and selects their compliance to each record on the report. (They could overlook one of the records and not make a selection.)  The SQL db would then query the compliance table and create a report of those locations with at least one "non-compliance."  Then the Db would send a reminder email to each location on that report. The Db tables have the email address info assigned to each location, so I can join that to the compliance log table.
Can this be done? I am using SQL Server Management Studio Express and Visual Web Developer (VB.net).
Thank you for your ideas,SMc

View 7 Replies View Related

Do GetDate() Inside SQL Server OR Do System.DateTime.Now Inside Application ?

Sep 12, 2007

For inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value
OR
to do System.DateTime.Now in the application and then insert it in the table?
I figure even small differences would be magnified if there is moderate traffic, so every little bit helps.
Thanks.

View 9 Replies View Related

EXEC Inside CASE Inside SELECT

Nov 16, 2007

I'm trying to execute a stored procedure within the case clause of select statement.
The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant.

@val1 and @val2 are passed in


CREATE TABLE #TEMP(
tempid INT IDENTITY (1,1) NOT NULL,
myint INT NOT NULL,
mybool BIT NOT NULL
)

INSERT INTO #TEMP (myint, mybool)
SELECT my_int_from_tbl,
CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0
FROM dbo.tbl
WHERE tbl.val2 = @val2


SELECT COUNT(*) FROM #TEMP WHERE mybool = 1


If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that.

Any suggestions?

View 8 Replies View Related

Differentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.

May 26, 2008

Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?

What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.

However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.

Looking forward for replies from expert here. Thanks in advance.

Note: Hope my explaination here clearly describe my current problems.

View 4 Replies View Related

Reporting Services :: Data Driven Email Subscription With Different Email Per Report Page

Jul 6, 2015

I have a report that gets sends out through a subscription and sometimes the report has multiple pages and all those pages appear within one email.Is it possible to set the subscription in such a way that an email is sent per page when the subscription executes.

View 2 Replies View Related

Dbmail Doesn't Rely On IIS SMTP, How To Set Bounced Email Redirect Email Etc.? Thanks

May 4, 2007

Under IIS SMTP I can set bounced email redirect etc. how to do that with dbmail, the idea is I can get the list of bounced emails somewhere so I can create a report.



Any idea?



thanks

View 2 Replies View Related

Help Split List Of Email Add Comma For Evry Email

May 12, 2008

need help
split list of email add comma for evry email
i have tabe "tblLogin" and in this table i have field emall
like this

emall
-----------------------------------------
aaa@hhhh.mm
nnn@hhhh.mm
mmm@hhhh.mm

need to do ilke this



Code Snippet
@list_email = (SELECT emall FROM tblLogin)

--------------------------i get this
-----------------------@list_email=aaa@hhhh.mm ; nnn@hhhh.mm ; mmm@hhhh.mm

@recipients = @list_email










Code Snippet

IF EXISTS( SELECT * FROM [db_all].[dbo].[taliB] )



BEGIN

DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)

SET @xml =CAST(( SELECT

FirstName AS 'td','',

LastName AS 'td','' ,

Date_born AS 'td','' ,

Age AS 'td','' ,

BirthdayToday AS 'td','' ,

BirthdayThisWeek AS 'td'

FROM [Bakra_all].[dbo].[taliB] ORDER BY LastName FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html><H1 align=center>aaaaaaaaaaaaaaaaaaaaaa</H1><body ><table border = 1 align=center dir=rtl>

<tr>

<td>name</td>

<td>fname</td>

<td>date</td>

<td>age</td>

<td>aaaaaaaaa</td>

<td>bbbbbbbbbbbbbbb</td>

</tr>'

SET @body = @body + @xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail

@recipients =N'rrr@iec.co.il',

@copy_recipients='rrrrr@iec.co.il',

@body = @body,

@body_format ='HTML',

@subject ='ggggggggggggggggggggg',

@profile_name ='ilan'

END

ELSE

print 'no email today'

View 1 Replies View Related

Anyone Else Get No Body To Msgs After 10/28?

Oct 28, 1998

Anyone else get no body to msgs after 10/28?
Why admin peoples?

View 1 Replies View Related

Gap Between Header And Body

Jul 17, 2007

Hello every body,

I am facing a smal formatting issue. the issue i have 7 text boxes attached with each other in the header to show the week days ,i.e Sunday,Monday,Tuesday,Wednesday,Thursay,Friday and Saturday

In Body,i have a matrix which will extend to have 7 columns for the weekdays.

Every thing is fine,Except a small gap between header and footer where i want the 7 text boxes to act as column header names.

I tried with various options like removing the header and placing the text boxes in Body followed by the matrix. 2) placing the seven column headers in a dummy table and attaching that with matrix,



But i am unable to remove the gap between them. if any one of you had experienced the same problem or knows how to solve.it's really a great help



Thank you,

View 3 Replies View Related

Can Any Body Tell Me What Wrong In My Sql Statement

Feb 22, 2007

Hi,
can any body tell me what is wrong in my sql statement
 SELECT title, price,
Budget = CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END
FROM titles
it gives me this error
Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '>'.
 
but when i use somthing like that i will works fine
SELECT
Budget


i am using sql server 2000
 

View 2 Replies View Related

Can Any Body Tell My Why This Page Does&#180;t Uppdate Into Sql

May 31, 2004

can any body tell my why this page does´t uppdate into my sql
i get no error but it does´t uppdate the records :(


<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<head>
<title>Updating a Row of Data with Validation</title>
<style type="text/css">
#editPanel {
width:90%; padding:10; background:khaki;
border:thin outset}
#posted {
background:lightgreen; border:thin inset}
</style>

<script language="C#" runat="server">
/* *********** Data base config ************ */

string ServerName = "xxx"; // SQL server name
string Database = "xxx"; // SQL Database Name
string user = "xxx"; // SQL User name
string Pass = "xxx"; // SQL Password


/* *********** define DataBase Sql/Access data ************ */

string TheID;

protected void Page_Load ( object src, EventArgs e )
{
string ServerName = "xxx"; // SQL server name
string Database = "xxx"; // SQL Database Name
string user = "xxx"; // SQL User name
string Pass = "xxx"; // SQL Password


SqlConnection myConn = new SqlConnection("server=" + ServerName + ";database=" + Database + ";UID=" + user + ";PWD=" + Pass + ";");
SqlCommand myCmd = new SqlCommand ("SELECT * FROM cars ORDER BY MessageDate desc",myConn);
myCmd.Connection = myConn;
TheID = Request.QueryString [ "TheID" ] ;
if ( !IsPostBack )
{
if ( TheID == null )
{
bindGrid ( );
loadPanel.Visible = true;
editPanel.Visible = false;
postPanel.Visible = false;
}
else
{
getMessage ( Request.QueryString [ "TheID" ] );
loadPanel.Visible = false;
editPanel.Visible = true;
postPanel.Visible = false;
}
}
else
{
loadPanel.Visible = false;
editPanel.Visible = false;
postPanel.Visible = true;
}
}

public void bindGrid ( )
{
string ServerName = "xxx"; // SQL server name
string Database = "xxx"; // SQL Database Name
string user = "xxx"; // SQL User name
string Pass = "xxx"; // SQL Password

SqlConnection myConn = new SqlConnection("server=" + ServerName + ";database=" + Database + ";UID=" + user + ";PWD=" + Pass + ";");
SqlCommand myCmd = new SqlCommand ("SELECT TOP 10 * FROM cars",myConn);
SqlDataReader rdr;

myCmd.CommandText = "SELECT TOP 10 * FROM cars";

myConn.Open();

rdr = myCmd.ExecuteReader ( );
myGrid.DataSource = rdr;
myGrid.DataBind ( );
rdr.Close();
myConn.Close();

/*
myCmd.CommandText = "SELECT * FROM cars";
myConn.Open();

myGrid.DataSource=myCmd.ExecuteReader ( );
myGrid.DataSource = myCmd.ExecuteReader ( CommandBehavior.CloseConnection );
myGrid.DataBind ( );
*/
}

public void getMessage ( String TheID )
{
string ServerName = "xxx"; // SQL server name
string Database = "xxx"; // SQL Database Name
string user = "xxx"; // SQL User name
string Pass = "xxx"; // SQL Password

SqlConnection myConn = new SqlConnection("server=" + ServerName + ";database=" + Database + ";UID=" + user + ";PWD=" + Pass + ";");
SqlCommand myCmd = new SqlCommand ( );
SqlDataAdapter myAdapter = new SqlDataAdapter ("select * from cars where ID=" + TheID, myConn );
DataTable msgDetails = new DataTable ( );
myAdapter.Fill ( msgDetails );
DataRowView myRow = msgDetails.DefaultView [ 0 ] ;

msgFastnumer.Value = myRow [ "Fastnumer" ].ToString ( );
msgTegund.Value = myRow [ "Tegund" ].ToString ( );
editPanel.DataBind ( );
}

public void updateMessage ( object src, EventArgs e )
{
string ServerName = "xxx"; // SQL server name
string Database = "xxx"; // SQL Database Name
string user = "xxx"; // SQL User name
string Pass = "xxx"; // SQL Password

SqlConnection myConn = new SqlConnection("server=" + ServerName + ";database=" + Database + ";UID=" + user + ";PWD=" + Pass + ";");
SqlCommand myCmd = new SqlCommand ( );

myCmd.Connection = myConn;

SqlDataAdapter myAdapter = new SqlDataAdapter ("select * from cars where ID=" + TheID, myConn );
DataTable msgDetails = new DataTable ( );
myAdapter.Fill ( msgDetails );
DataRowView myRow = msgDetails.DefaultView [ 0 ] ;

SqlDataReader rdr;

if ( Page.IsValid )
{
myCmd.CommandText = "UPDATE cars SET Fastnumer=@Fastnumer, Tegund=@Tegund where id=" + TheID;

msgFastnumer.Value = myRow [ "Fastnumer" ].ToString ( );
msgTegund.Value = myRow [ "Tegund" ].ToString ( );

myCmd.Parameters.Add ( "@Fastnumer", SqlDbType.NVarChar ).Value = msgFastnumer.Value;
myCmd.Parameters.Add ( "@Tegund", SqlDbType.NVarChar ).Value = msgTegund.Value;

myConn.Open ( );
rdr = myCmd.ExecuteReader ( );
myGrid.DataSource = rdr;
myGrid.DataBind ( );
rdr.Close();
myConn.Close ( );

}
bindPostPanel ( TheID );
}

public void bindPostPanel ( String TheID )
{
string ServerName = "xxx"; // SQL server name
string Database = "xxx"; // SQL Database Name
string user = "xxx"; // SQL User name
string Pass = "xxx"; // SQL Password

SqlConnection myConn = new SqlConnection("server=" + ServerName + ";database=" + Database + ";UID=" + user + ";PWD=" + Pass + ";");
SqlCommand myCmd = new SqlCommand ( );
SqlDataReader rdr;
myCmd.CommandText = "SELECT * FROM cars WHERE Id='" + TheID + "'";

myCmd.Connection = myConn;

myConn.Open();

rdr = myCmd.ExecuteReader ( CommandBehavior.SingleRow );
listDetails.DataSource = rdr;
listDetails.DataBind ( );
rdr.Close();
myConn.Close();

/*
myConn.Open ( );
listDetails.DataSource = myCmd.ExecuteReader ( CommandBehavior.SingleRow );
listDetails.DataBind ( );
myConn.Dispose ( );
*/




}
</script>
</head>
<body>
<div class="header"><h3>ADO.NET Primer:
<span class="hilite">Editing an Existing Record</span></h3>
</div>
<hr size="1" width="90%">
<br>
<center>
<asp:panel id="loadPanel" runat="server">
<h5>Select a Record to Edit</h5>
<asp:datagrid id="myGrid" runat="server" width="90%" cellpadding="5" gridlines="vertical" bordercolor="black"
borderwidth="1" font-size="8pt" backcolor="ghostwhite" alternatingitemstyle-backcolor="lightgray"
autogeneratecolumns="false">
<headerstyle backcolor="darkslategray" forecolor="khaki" height="25" font-bold />
<columns>
<asp:boundcolumn headertext="Date" datafield="Innsett" dataformatstring="{0:d}" />
<asp:hyperlinkcolumn headertext="Fastnumer" datanavigateurlfield="id" datanavigateurlformatstring="aspxtest.aspx?TheID={0}"
datatextfield="Fastnumer" />
<asp:boundcolumn headertext="Tegund" datafield="Tegund" />
</columns>
</asp:datagrid>
<p align="center">Back to top</p>
</asp:panel>







<asp:panel id="editPanel" runat="server">
<h5>To save changes to the database, press Update.</h5>
<form runat="server">
<input type="hidden" id='msgDate' runat="server">
<table width="85%" cellspacing="1" cellpadding="3" border="0">
<col width="40%" align="right">
<col width="60%">
<tr>
<td>Fastnumer:</td>
<td><input id="msgFastnumer" runat="server">
<asp:requiredfieldvalidator runat="server" controltovalidate="msgFastnumer" errormessage="Sender's name cannot be blank. "
display="none" /></td>
</tr>
<tr>
<td>Your email address:</td>
<td><input id="msgEmail" runat="server">
<asp:regularexpressionvalidator runat="server" controltovalidate="msgEmail" validationexpression="^[w-]+@[w-]+.(com|net|org|edu|mil)$"
errormessage="Please enter a valid e-mail address. " display="none" /></td>
</tr>
<tr>
<td>Tegund:</td>
<td><input id="msgTegund" runat="server"></td>
</tr>
<tr>
<td>Your message:</td>
<td><textarea id="msgBody" rows="5" cols="35" runat="server"></textarea></td>
</tr>
</table>
<p>
<input type="submit" value="Update" runat="server" onServerClick="updateMessage">
<input type="reset" value="Cancel" onClick="self.location.replace ( 'aspxtest.aspx' ) "></p>
<asp:validationsummary runat="server" displaymode="SingleParagraph" showmessagebox="true" showsummary="false" />
</form>
</asp:panel>
<asp:panel id="postPanel" runat="server">
<h5>Thank you for editing your comments. This record has been updated.</h5>
<asp:datalist id="listDetails" width="85%" runat="server">
<itemtemplate>
<table id="posted" width="100%" cellspacing="1" cellpadding="5" border="0">
<col width="35%" align="right">
<tr>
<td>Date:</td>
<td><%# DataBinder.Eval ( Container.DataItem, "Innsett", "{0:d}" ) %></td>
</tr>
<tr>
<td>Name:</td>
<td><%# ( ( IDataRecord ) Container.DataItem ) [ "Fastnumer" ] %></td>
</tr>
<tr>
<td>Tegund:</td>
<td><%# ( ( IDataRecord ) Container.DataItem ) [ "Tegund" ] %></td>
</tr>
</table>
</itemtemplate>
</asp:datalist>
<p>
Back to Edit View
Select Another Record
</p>
</asp:panel>
</center>
<br>
<hr size="1" width="90%">
</body>
</html>

View 6 Replies View Related

The Body Of The Report Disappeared

Feb 6, 2008


Hello,
I was modifying some standard reports of one planning program, by downloading .rdl file from report server and creating a new report project. I have done so many times.
Last weak in one of those new reports only header of the report was seen in the report server as I thought I had made some kind of mistake tried to look for it, and after some time the problem fixed itself and I blamed everything on one mixed up connection between the tables in the report, because after removing it the problem fixed itself, but now I think that was just a coincidence.
This weak after working fine for about a weak, 6 reports appeared as only headers and footers, they worked fine yesterday and no changes were made to the reports, but in the morning only the header is seen.
The reports that are affected are the 4 new ones, and 2 original ones, they are all dealing with the same date just different grouping and sorting.
Does anyone know how to fix this problem?

Darius

View 1 Replies View Related

Body Values In Header And Header Values In Body

May 23, 2007

Hi,



I want to display a value from db in the header section. I have read a couple of information that I should place the value in a in-visible text field an reference it in the header with the ReportItems. This works great with the first page but on the second page the header information are empty (I think because the Textfield is on Page 1 not Page 2)... So is there a way to accomplish that for all pages? Not only the first page....



My second one, hope you don't mind that I post it in the same thread, is:



My Report needs to display the total size of pages in the body. I did not realy found anything useful where I can retrieve from the total size of pages of my reports....



thanks for any help



f.

View 3 Replies View Related

Page Number In Body

Aug 2, 2007

how to get page number in body of report

View 9 Replies View Related

Xp_sendmail @subject Appears In Body

Nov 12, 2004

My @subject in an xp_sendmail job is appearing in the email subject where it's supposed to, but also as the first line of the body. Does anyone know of a way to disable this behavior?

View 3 Replies View Related

Insert A Variable Into HTML Body?

Oct 18, 2013

I have created a .bat file for my sqlcmd

my sqlcmd includes a .sql file that builds up my email with a HTML body.

Is it possible to insert a variable (@customername) into the HTML body?

I am already using @maillist and select statement to create my customer email address's.

View 5 Replies View Related

Body BackgroundImage Not Displayed In Browser

Oct 5, 2006

I have been trying to set up reports with a background image. The background displays correctly on the preview tab in Visual Studio, but is not visible when deployed to the report server and rendered in Internet Explorer.

However, if the report is printed, or exported to PDF or Excel, the background displays correctly.

It does not seem to make any difference if the image is external or embedded. Also, the problem only seems to affect the Body properties; a background image attached to a table, for example, seems to display correctly.

Has anybody else seen this behavior? I have not done extensive testing, but every report that I have tried seems to have the same problem. Is there a setting someplace that I am missing, or is this perhaps a bug in IE?

View 3 Replies View Related

Why I Am Not Able To Change The Size Of Report Body

May 3, 2007

Hi, all experts here,

Thanks a lot for your kind attention.

I am on the layout page, and click on the body, going to the body property dialogue, there is a size property there, I changed its width and height, but it is not changed at all? I mean once I save it, it is back to its orginal size? Why is that? Any advices for that?

Hope my question is clear.

Thanks a lot in advance for your help.

And I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

View 1 Replies View Related







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