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


ADVERTISEMENT

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

Upload Email Attachment Into Table

Aug 28, 2007

We receive emails every day that we would like to load into a table in sql 2005. Anyone know a way to do this?

View 6 Replies View Related

Subscribe To Two Reports, One Body, One Attachment

Oct 10, 2007



I have a lengthy report and a summary viewof this report. I'dlike for the summary view to be sent out in a subscription as the message body and have the detailed report be sent out as a PDF attachment. I know you can't do this in the default subscription UI, but I was wondering if there was a programmatic way to make this happen?

Thanks,
-p

View 1 Replies View Related

Message Type And Message Body..............

Nov 17, 2006

Hello,

I am having trouble specifying a message body that is valid. I mean for the client to send. If I leave it as null then everything is ok but if I create a memorystream and add a line of text it reports back it did not pass validation. I do not understand this and am not sure what to do. I need to send a message based on a code and text but do not know the format of the body that is allowed. The code I am refering to comes out of HelloWord_CLR because that is what I am formating my sample after. I call it the same way it calls the return message done in ServiceProc. I need to know the message format including body since this does not seem to work. A sample of the call is bellow.

// Create an empty request message

string Msg = "Hello";

MemoryStream body = new MemoryStream(Encoding.ASCII.GetBytes(Msg));

Message request = new Message("Request", body);

Thanks,

Scott Allison...

View 1 Replies View Related

How To Use T-sql To Email And Attachment

Aug 14, 2006

I am new to sql and want to write a stored procedure to email a database group an attachment of a report that was created. Can someone please point me in the write direction thanks.

View 4 Replies View Related

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

Email Attachment Problem

Sep 11, 2006

Hi all,
I create a subscription to send the report (in pdf format) to users once every week. The subscription works, all users manage to receive the email but some of them do not have the pdf file attached to the email. Anyone know what is wrong??

Daren

View 3 Replies View Related

Dynamic Email Attachment

Apr 18, 2008



I have a ETL job that generate a text file ( with timestamp. ABCfile041208.txt).

and I would like to create a send email task to send this file as an attachement.

How can I do this? the file name is going to change everyday..

Can you show me some example?

View 5 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

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 StoredProcedure. Part of this SP is that it pulls data from another tableand inserts a new row into the registration table.I want to have a trigger on the registration table that will fire whenthe row is inserted which will use the sp_send_cdosysmail sproc to sendan e-mail to the user.However, I want to be able to include the value of one of the fieldswithin the BODY of the message. I can't find a way to includeparameters/variables within the Body of a message usingsp_send_cdosysmail and it's driving me nuts.Here's what I have in a sproc (not a trigger) that executessp_send_cdosysmail...I currently pass a parameter for the "To" e-mailaddress 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 TextAdditional Text<--THIS IS WHERE I WANT TO PUT THE PARAMETER-->Additional TextAdditional Text'__________________________________________________ _Is there any way to do this?The sp_send_cdosysmail I used is the standard MS one..Here it is forreference:Thanks for any help offered!ElliotCREATE 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************************************************** *********************/ASDeclare @iMsg intDeclare @hr intDeclare @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.aspEXEC @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', @ToEXEC @hr = sp_OASetProperty @iMsg, 'From', @FromEXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @BodyEXEC @hr = sp_OAMethod @iMsg, 'Send', NULL-- Sample error handling.IF @hr <>0select @hrBEGINEXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUTIF @hr = 0BEGINSELECT @output = ' Source: ' + @sourcePRINT @outputSELECT @output = ' Description: ' + @descriptionPRINT @outputENDELSEBEGINPRINT ' sp_OAGetErrorInfo failed.'RETURNENDEND-- Do some error handling after each step if you have to.-- Clean up the objects created.EXEC @hr = sp_OADestroy @iMsgGO

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

SSIS - Read Email Attachment

Jun 20, 2008

Good Afternoon,

Everyday I receive an email with an Excel file attachment. Which has to be imported into a SQL database. Can I use SSIS to download the file and import into SQL table?

Thanks In Advance.

View 17 Replies View Related

How To Automatically Save Email Attachment

Feb 19, 2015

I get an email daily with an attachment. I want to automatically save the attachment to a server. I managed to get a rule on outlook to save the attachment in a folder on my computer. But I want it saved to a folder on a server. How can I do this?

View 4 Replies View Related

Import Data From Email Attachment

Aug 28, 2007

This ones way beyond me, I don't even know where to start. basically we get an email every day with an attachment. The attachment is a csv file. I'd like to import that file into a table in sql. Anyone have any ideas?

View 3 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

Mail Message Body With Data From Two Tables

Oct 27, 2013

I need to set a trigger so that an email is generated when a row is inserted into the ABC_DB_OnlineFormsConfirm table. The message will include data not only from that table, but from a related table called ABC_DB_OnlineForms. The common field between the two is FormID. This is related to an online account application process where the user has the option of completing it in one session, or saving it and coming back at a later time. Once the user has completed the first screen and clicks continue, a record is written to ABC_DB_OnlineForms. At the very end, when he clicks “Save and Complete”, a row is written to the ABC_DB_OnlineForms table. I would like the body of the email to include three separate lines, as follows. The first two pieces of data come from the OnlineForms table and the third from OnlineFormsConfirm. The field names are userid, IRSBackup and ConfirmationCode.

UserID:
Subject to IRS Backup Withholding?:
Confirmation Code:

What I have below results in an email body that contains the confirmation code all by itself. If I switch @body=@MessageBody to @body=@UserID or @body=@Backup, it will report the correct single piece of data, but I have no idea how to get all three pieces at once as separate lines including the leading descriptor (UserID:, Confirmation Code, etc.).

CREATE TRIGGER SendEmail
ON [ABC].[dbo].[ABC_DB_OnlineFormsConfirm]
AFTER INSERT
AS
Begin
Declare @MessageBody varchar(100)

[code]...

View 7 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

Xp_sendmail Problem - Body Message Breaking Onto New Line..

Sep 13, 2005

Created a SP that uses system function XP_SENDMAIL. I wantto be able to send a HYPERLINK in the email. TheHYPERLINK is created dynamically and generally long in length (exceedsdefault width of 80 characters) and when rendered in the email is splitacross 2 lines. The problem is that when you click on the link itdisregards the part of the link that has been split onto the linebelow.Does anyone know a solution to this - how to extend the width of theemail to wider that 80 characters so that the link is not split over 2lines? I know that you can use the @width parameter when placing themessage in an attatchment, however I want the link to be placed in thebody of the email and not in an attachment.Any help is much appreciated..

View 2 Replies View Related

Export Query Results To Excel File And Add As Attachment In Email

Jan 2, 2014

Is there a way to export query results to an excel fie and add that file as an attachment in the email? All this has to be done using SQL query and it needs to be automated. My coworker tried using Openrowset and BCP, but it is not working.

View 3 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

SQL 2012 :: Send Binary File Stored In Server As Email Attachment?

Apr 26, 2014

Is there a way to send binary file stored in SQL Server as email attachment without downloading it to the file system?

View 1 Replies View Related

Best Way To Send Email From A Stored Procedure (dynamically Changing Paramenters And Attachment)

Jul 23, 2005

Hello everyone,I need advice of how to accomplish the following:Loop though records in a table and send an email per record. Emailrecipient, message text and attachment file name - that's all changesrecord by record.Is it doable from a stored procedure (easily I mean, or am I better offwriting a VB app)? There are so many options of sending mail from SQLserver - CDONTS, SQL MAIL TASK, xp_sendmail. What's easier to implementand set up?Thanks a lot!!!(links and fragments of sample code would be greatlyappreciated)Larisa

View 2 Replies View Related

Integration Services :: Download A Specific Email Attachment From Outlook 2013

Jun 9, 2015

I have outlook 2013 installed on my machine, I want to automate the download of an attachment which I receive on daily basis from noreply@test.com. I have created a rule in outlook to reroute these mails in a specific folder named Received_Test.Many who try to climb it fail and never get to try again. The fall breaks them.

View 3 Replies View Related

Reporting Services :: Data Driven Subscription With Dynamic Email Attachment File Name

Feb 11, 2010

I have report which accepts a card number and fromdate and todate as parameters to the report. This report needs to be sent on a quarterly basis to each of the customer mail id to which their card number is linked. I am getting all this information from a database and sending as an attachment to the customer. Now I would need making these filename's which are attached to be dynamic based on the input parameters.

In datadriven subscriptions, the option of include report has only true or false values and another option was to take from a database. I tried putting the dynamic file name in the database and getting the value from the database but no success, the subscription itself is failing here. I guess I am doing something wrong here by binding the report name from the value which I am getting from DB to the actual report name.

If report name = Mytransactions, and the parameters passed are Card = 123,fromdate = 1/1/2010,todate = 31/3/2010.

Now in the attachment the file name should be something like "Mytransactions_123_January1st2010_March31st2010".
How to make the filename dynamic.

View 9 Replies View Related

Preferred Method To Incorporate Common Table Expression (CTE) Functionality

Feb 26, 2007

What is the best approach to utilize a recursive CTE (Common Table Expression) to filter a resultset?  The CTE function is used in all application queries to limit recursively the final resultset based on a provided hierarchical organization identifier. i.e. join from some point in the organization chart on down based on an organization id. I would prefer that the query could be run real-time. i.e. not having to persist the prediction portion of the results to a sql relational table and  then limiting the persisted results based on the CTE function. 

It appears that I can use a linked server to access the prediction queries directly from SQL Server (link below). I believe that I might also be able to deploy a CTE recursive function within a .net assembly to the Analysis Server but I doubt that recursive functionality is availalble without a linked SQL Server.
Executing prediction queries from the relational server
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx
 
 

 

View 1 Replies View Related

Transact SQL :: Convert Output Into Excel And Schedule Automate Job That Runs Every Friday And Send Email With Attachment

Nov 3, 2015

Here below is the perfect query i made which is working fine and giving me the sql output but just only need is how to convert to excel and automate the job scheduling so that it run on everyday and send the mail with attachment .

SELECT DN, cn, displayName, mail, objectClass, sAMAccountName, Company, givenName, sn
FROM
(
SELECT DN, cn, displayName, mail, objectClass, sAMAccountName, Company, givenName, sn, 1 [ordering] FROM alpha.dbo.DCADFeed
where sAMAccountName collate SQL_Latin1_General_CP1_CI_AS in

[Code] ....

View 4 Replies View Related

Transact SQL :: Validate Each Field Of Every Row In Table Then Send Email

Aug 5, 2015

I have some records (approx 100K - 500K) in my table (Name, DOB, Zip, Phone). I want to validate each record and every field as given below.

Name - Should not have numerics
DOB - Should be in yyyy-mm-dd format, should be < 1995
Zip - Should be 5 char zip, allowed 3 or 4 char zip for some States
Phone - Should have 10 numeric digits, should be in xxxx-xxxx-xx format.

After validating these rules, If the record is valid then send email saying valid record, if invalid then send email with validation failure details like which column failed which validation rule suppose DOB might not be in specified format or it might be > 2000 all these details.

How to validate all the records in bulk without any loop or cursor.

View 10 Replies View Related

ODBC/OLEDB Failure Accessing MS Access Attachment Field In Query

Mar 12, 2008

We are experiencing failures when accessing a datatype="Attachments" field in a query in an MS Access 2007 database using ACE ODBC or OLEDB drivers.


We are using an MS Access 2007 database
ACE ODBC/OLEDB drivers installed (i.e. Office 2007, which installs these drivers)
DB contains a table with a field of type "Attachments" (which is new in MS Access 2007)
DB contains a query that selects the fields of the above table

Using ACE ODBC or ACE OLEDB drivers to display the table works fine. The "Attachments" field is displayed as the file name of the attachment(s).

However, using either ACE ODBC or ACE OLEDB to display the query (i.e. NOT the table) results in either incorrect results or unexpected failures of the ODBC/OLEDB drivers.


Error using ODBC (using "ODBC Test"):
=============================
select * from Query1 does not give an error, but displays a "1" for the Attachment field.
select Attachments from Query1 gives the following error:
stmt: szSqlState = "HY000", *pfNativeError = -3087, *pcbErrorMsg = 97,
*ColumnNumber = -2, *RowNumber = -2
MessageText = "[Microsoft][ODBC Microsoft Access Driver] Reserved error (|);
there is no message for this error."


Error using OLEDB (using "RowSetViewer"):
================================
select * from Query1 gives the following error:
Interface: Unknown
Result: 0x0004001 = E_NOTIMPL
FormatMessage: Not implemented
File: F:DepotSQLVaultmdac28sdkSamplesoledb
owsetviewerSDKobji386CRowset.cpp
Line: 616

select Attachments from Query1 gives the following error:
Interface: IID_ICommand
Result: 0x0004005 = E_FAIL
IErrorInfo: [0x0000f3f1] Unspecified error?
File: F:DepotSQLVaultmdac28sdkSamplesoledb
owsetviewerSDKobji386CCommand.cpp
Line: 439


If it would help to analyze the problem, I have a folder containing all pertinent files (bare-bone database, tools, instructions) to reproduce this. I could attach it as a ZIP file, if requested.

Thanks,

Joe

View 1 Replies View Related

Email Being Sent, But No Message

Jul 20, 2005

I have setup an email notifications system, that basically takes eachrow from a table and sents out an email according to the data in thatrow. The emails get sent, with the subject being filled as expected.Only problem is that sometimes there is no message.Here is the stored procedure that is being called every hour to sendthe emails:CREATE PROCEDURE dbo.RemindersSendEmails AS--CursorDECLARE RemindersCursor CURSOR FORSELECT *FROM RemindersTodaysAndUnsent--Values for cursorDECLARE@I_Reminder_ID bigint,@I_Notice_ID bigint,@V_Reminder_Text varchar(250),@SDT_Reminder_Date smalldatetime,@V_Email varchar(50),@I_Reminder_Type bigint,@SDT_Reminder_Sent smalldatetime,@I_Attempts_Made int,@V_Notice_Type varchar(50),@I_Notice_Period int,@V_Period_Description varchar(50),@I_Project_ID bigint,@V_Notice_Ref varchar(10)--values for sending the mailDECLARE @NEWLINE varchar(2)OPEN RemindersCursorFETCH NEXT FROM RemindersCursorINTO @I_Reminder_ID, @I_Notice_ID, @V_Reminder_Text,@SDT_Reminder_Date, @V_Email, @I_Reminder_Type,@SDT_Reminder_Sent, @I_Attempts_Made, @V_Notice_Type,@I_Notice_Period, @V_Period_Description,@I_Project_ID, @V_Notice_Ref--INTO @I_Reminder_ID, @I_Notice_ID, @V_Reminder_Text,@SDT_Reminder_Date, @V_Email, @I_Reminder_Type,--@SDT_Reminder_Sent, @I_Attempts_Made, @V_Notice_Type,@I_Notice_Period, @V_Period_Description,--@I_Project_ID, @V_Notice_RefSET @NEWLINE = char(10)--PRINT 'start'WHILE @@FETCH_STATUS = 0BEGINDECLARE @EmailMessage varchar(6000), @Subject varchar(100), @StatusintSET @Subject = RTRIM(CONVERT(varchar(8), @I_Reminder_ID)) + ' NoticeAlert - Project ' + RTRIM(CONVERT(varchar(8), @I_Project_ID)) + 'Notice Ref ' + RTRIM(@V_Notice_Ref)SET @EmailMessage = 'Project: ' + RTRIM(CONVERT(varchar(8),@I_Project_ID)) + @NEWLINE +'Notice: ' + RTRIM(@V_Notice_Ref) + @NEWLINE +'Notice Type: ' + RTRIM(@V_Notice_Type) + ' - ' +RTRIM(@V_Period_Description) + @NEWLINE +'Reminder: ' + RTRIM(@V_Reminder_Text) + @NEWLINE + @NEWLINE +'Reminder date: ' + CONVERT(varchar(11), @SDT_Reminder_Date) +@NEWLINE +'Reminder sent: ' + CONVERT(varchar(11), GETDATE()) + @NEWLINE +'Email sent to: ' + @V_Email + @NEWLINE +'Number of attempts made at sending this email (once every hour): '+ CONVERT(varchar(4), @I_Attempts_Made)--@I_Reminder_ID, @I_Notice_ID, @V_Email, @I_Reminder_Type,@I_Notice_Period,PRINT 'subject = ' + @SubjectPRINT 'message = ' + @EmailMessageSET @V_Email = LTRIM(RTRIM(@V_Email))EXEC @Status = master..xp_sendmail @recipients = @V_Email,@message = @EmailMessage,@subject = @Subject--PRINT 'XXXXXXXXXXXXXXXXXXXXXX status = ' + CONVERT(varchar(2),@Status)--If send mail is a successIF (@Status = 0)BEGINUPDATE RemindersSET SDT_Reminder_Sent = GETDATE(), I_Attempts_Made =@I_Attempts_Made + 1WHERE I_Reminder_ID = @I_Reminder_IDEND--Else send mail failedELSEBEGINUPDATE RemindersSET I_Attempts_Made = @I_Attempts_Made + 1WHERE I_Reminder_ID = @I_Reminder_IDEND-- Get the next reminderFETCH NEXT FROM RemindersCursorINTO @I_Reminder_ID, @I_Notice_ID, @V_Reminder_Text,@SDT_Reminder_Date, @V_Email, @I_Reminder_Type,@SDT_Reminder_Sent, @I_Attempts_Made, @V_Notice_Type,@I_Notice_Period, @V_Period_Description,@I_Project_ID, @V_Notice_RefEND--PRINT 'End'CLOSE RemindersCursorDEALLOCATE RemindersCursorGO

View 2 Replies View Related

Email Any SQL Error Message

Sep 7, 2000

If I want any SQL error message created inside a database to send an error
message is the xp_sendmail the way to go?

View 3 Replies View Related







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