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 RepliesHi, 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
I thought I could just copy over some asp.net code like: System.Web.Mail.MailMessage mailMessage = new System.Web.Mail.MailMessage();
But VS2005 doesn't seem to want me touching System.Web.Mail.
Any ideas?
Thanks,
Allen
Hi,
I am trying to write a stored procedure in Sql Server that should send an email containing a query result everyday at 2:00 PM. How can I do this?? Im trying to use xp_sendmail but this gives me the following error:
Could not find stored procedure 'xp_sendmail'.
Plz let me know if there is any easy way to handle this.
Thanks a lot
I have an store procedure and I want to build an email with this store procedure to email me How can I use the email command to incorporate into my sql SP?
View 4 Replies View RelatedHi Everybody,
I am trying to setup a stored procedure that runs through a Reminders table and sends an email to users based on DateSent field being smaller than todays date. I have already setup the stored procedure to send the email, just having trouble looping through the recordset.
Code Snippet
CREATE PROCEDURE [dbo].[hrDB_SendEmail]
AS
BEGIN
DECLARE @FirstName nvarchar(256),
@LastName nvarchar(256),
@To nvarchar(256),
@ToMgr nvarchar(256),
@Subject nvarchar(256),
@Msg nvarchar(256),
@DateToSend datetime,
@Sent nvarchar(256),
@ReminderID int,
@RowCount int,
@Today datetime,
@Result nvarchar(256)
-- Get the reminders to send
SELECT
@ReminderID = r.intReminderID,
@DateToSend = r.datDateToSend,
@FirstName = e.txtFirstName,
@LastName = e.txtLastName,
@To = e.txtEmail,
@Subject = t.txtReminderSubject,
@Sent = r.txtSent
FROM
(auto_reminders r INNER JOIN employee e ON r.intEmployeeID = e.intEmployeeID) INNER JOIN ref_reminders t ON r.intReminderType = t.intReminderTempID
WHERE
(((r.datDateToSend)<20/12/09) AND
((r.txtSent)='False'))
-- Send the Emails
WHILE(LEN(@To) > 0)
BEGIN
EXEC @Result = sp_send_cdosysmail @To, @ToMgr, @Subject, @Msg
END
-- Mark the records as sent
IF @Result = 'sp_OAGetErrorInfo'
BEGIN
SELECT @Sent = 'Error'
END
ELSE
BEGIN
SELECT @Sent = 'True'
END
UPDATE auto_reminders
SET
auto_reminders.txtSent = @Sent, auto_reminders.datDateSent = @Today
WHERE
intReminderID = @ReminderID
END
GO
From the code you can probably tell I am new to writing stored procedures, so I apologise for any obvious errors. My major problems are :-
how to loop through each record
how to get todays date
whether the struture of the procedure is correct
Also, if you think there is an easier way or a better method, please suggest it. I am open to any suggestions you may have,
Thanks in advance
Ben
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 Relatedhi need help how to send an email from database mail on row update
from stored PROCEDURE multi update
but i need to send a personal email evry employee get an email on row update
like send one after one email
i use FUNCTION i get on this forum to use split from multi update
how to loop for evry update send an single eamil to evry employee ID send one email
i update like this
Code Snippet
:
DECLARE @id nvarchar(1000)
set @id= '16703, 16704, 16757, 16924, 17041, 17077, 17084, 17103, 17129, 17134, 17186, 17190, 17203, 17205, 17289, 17294, 17295, 17296, 17309, 17316, 17317, 17322, 17325, 17337, 17338, 17339, 17348, 17349, 17350, 17357, 17360, 17361, 17362, 17366, 17367, 17370, 17372, 17373, 17374, 17377, 17380, 17382, 17383, 17385, 17386, 17391, 17392, 17393, 17394, 17395, 17396, 17397, 17398, 17400, 17401, 17402, 17407, 17408, 17409, 17410, 17411, 17412, 17413, 17414, 17415, 17417, 17418, 17419, 17420, 17422, 17423, 17424, 17425, 17426, 17427, 17428, 17430, 17431, 17432, 17442, 17443, 17444, 17447, 17448, 17449, 17450, 17451'
UPDATE s SET fld5 = 2
FROM Snha s
JOIN dbo.udf_SplitList(@id, ',') split
ON split.value = s.na
WHERE fld5 = 3
now
how to send an EMAIL for evry ROW update but "personal email" to the employee
Code Snippet
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =''
@copy_recipients='www@iec.com',
@body = @body,
@body_format ='HTML',
@subject ='test',
@profile_name ='bob'
END
ELSE
print 'no email today'
TNX
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] ....
Hi guys,I hope somebody can help as I am trying to write a procedure in SQL that will be an email reminder sent to users on every 5 th or the month if they don't submit data before.
I am quite new to sql and procedures in particular so I was hoping somebody can help.
I need to:
1.Check the day of the month, if not 5th do nothing
2.Get the date of the prevoious month (get current date - 1 month,set day to 1)this is the funny bit.
I have a field DateEntered,but users only select the month and the year on the acctual page,but when submitted it gets written as a full date and defaults to the 1st of the month.
3.Get the list of hospitals that haven't got data for the previous month
4.Email the hospitals.
DECLARE @returnDay int;
DECLARE @DateEntered datetime;
SELECT @returnDay = DatePart(day,GetDate())
If @returnDay = 5 (syntax error near 5)
BEGIN
SELECT @DateEntered = GetDate() - 30
Print DatePart(month, @DateEntered)
I was hoping somebody could look at this and help me.
thanks
hi there, i m newbie in using the ms sql server.i wouldlike to learn how to write the store procudure.i hope u all can guide me on this.
i have one table with data
appid int (PK)
leaveappid int (FK)
firstapproval int
firstapproval email varchar,
2nd approval int
2nd approval email varchar
resutl firstapproval
result secondapproval.
i would like to do a automatic send mail function where: the first mail will send to the firstapproval email.if after 2 days, the firstapproval no response(the result firstapproval is empty) then the email will automatic send to the second approval. how can i write the SP for this case? i really have no idea to start since i duno how to write the SP syntax and dont have the concept too.
hope can hear from u all soon.
thanx
Hi,I'm not sure if this is possible as i've googled everywhere, but i have aselect query that returns a customer record with their associated salesorders. I would like to automate a process which sends an email reminder toeach customer in the database, that has outstanding orders. This emailreminder should have the results of the query regarding their account.The table structure are as follows.---------------------Customer_tbl---------------------CustomerIDAccountNoNameEmailAddress---------------------Order_tbl---------------------OrderIDCustomerIDReferenceAmountDateOutstanding_flgCan anyone help?Sen.
View 2 Replies View RelatedIs 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 RelatedHi ,
I'm just new in this SQL 2005, and I do not reallly sure the subject is right or not but as example in this link below
http://msdn2.microsoft.com/en-us/library/ms190307.aspx
I want updated to few of person of any changes in database just by sending to their emails in every 2 hours as an example. I go through the example given but I do not know the step how to run stored procedures. The Information that I want to give to them is like as:
Date From : 23/02/2008
Date To: 24/02/2008
Number of user : 3
My draft table is like this
Sequence_No Submitted_Dt Name
-------------------- ------------------- ------------------------
1 2/21/2008 4:16:45 PM John
2 2/22/2008 4:16:45 PM Dean
3 2/23/2008 4:16:45 PM Rick
4 2/24/2008 4:16:45 PM Van
thanks to all of your corcern to help me
Regards;
The idea is user upload MHT file, I just send out email to others, with the content in the MHT as the conentent of the email.
thanks
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
Hi there,
I want to set the body of the mail which i need to send to a variable. What is the best variable to use for that since the message can be quite large and Im not sure whether string is the best option since I see there is an Object variable type but I have not Object variable types before.
Any opinions on this?
Cheers
Mike
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
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 RelatedWe 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
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 RelatedHello 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?
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]....
I am working on an OLAP modeled database.
I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).
I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.
The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.
What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?
I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.
Thanks in advance for any assistance you can provide.
Code to show the package StartDateTime and EndDateTime in body of the email in ssis send email task.
View 3 Replies View RelatedWhat 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] ....
I have created some stored procedures that I wish to share with another user in another database. How can I extract this code, other than cut and paste, since I have quite a few? Is it possible to duplicate database object "stored procedures"? How about a script that would recreate them in the target database?
Any help would be appreciated.
Thanks!
Does anyone know how i can send an email from within a stored procedure ? Can i simply use xp_sendmail ?
thanks
i want to know how can i send emails from SQL stored procedure? is it possible?
View 3 Replies View RelatedHi
I am new to C# . I have a stored procedure which takes 4 parameters
GetSearchComplaint( Comp_ID , strViolator, strSts, FromDate, ToDate), These parameters can be null.
And i have 5 textboxes from which i send the parameters.
I am validating the input like this :System.Nullable<int> Comp_ID;
if ((txtsrchCompID.Text).Trim() == "")
{Comp_ID = null;
}else if ((txtsrchCompID.Text).Trim() == "")
{
try
{int i = int.Parse(txtsrchCompID.Text);
Comp_ID =i;
catch
{mesage += "Complaint ID is not valid";
}
}
When i run this i get this error ---'Use of unassigned local variable 'Comp_ID'
I get the same error for FromDate(DateTime) and ToDate(DateTime) . but not for string variables strViolator and strSts.
How do i pass the null value to the stored procedure? pls help..
Hi there,
My requirement is to send more than one GUID to the stored procedure.
How can I do that? If you can give me an example that will be great.
Kind regards,
Ricky
I am tring to use a stored procedure to run a function POST some data through HTTP, but failed. Here are the codes I used. Would appreciate if anyone can help me about that. Thank you very much.
Database Stored Procedure:
CREATE PROCEDURE [dbo].[USP_HTTP_POST]
@URL NCHAR(255),
@CONTENT NCHAR(1024),
@Result bit OUTPUT
AS
BEGIN
/***** Call Function to POST *****/
set @Result = NULL
EXECUTE @Result = [dbo].[udfHttpPost]
@URL,
@Content
END
Database Function:
CREATE FUNCTION [dbo].[udfHttpPost]
(@Url [nchar](255),
@Content [nchar](1024))
RETURNS [bit] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SqlServices].[SqlServices.HttpServices].[HttpPost]
VC# Assembly Code
using System;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.Security;
using System.Security.Permissions;
using System.IO;
using Microsoft.SqlServer.Server;
namespace SqlServices
{
public class HttpServices
{
[SqlFunction(DataAccess = DataAccessKind.None)]
public static bool HttpPost(string Url, string Content)
{
Url = Url.TrimEnd();
Content = Content.TrimEnd();
string rawOutput = "";
//Get Access Right to Web
WebPermission p = new WebPermission(NetworkAccess.Connect,Url);
p.Assert();
//Prepare the WebRequest
WebRequest Req = (WebRequest)WebRequest.Create(Url);
Req.Timeout = 60000;
Req.Method = "POST";
Req.ContentLength = Content.Length;
Req.ContentType = "application/x-www-form-urlencoded";
StreamWriter PostWriter = new StreamWriter(Req.GetRequestStream());
PostWriter.Write(Content);
PostWriter.Close();
WebResponse Resp = Req.GetResponse();
StreamReader sr = new StreamReader(Resp.GetResponseStream());
rawOutput = sr.ReadToEnd();
sr.Close();
return true;
}
}
}
System Exception:
A .NET Framework error occurred during execution of user defined routine or aggregate 'udfHttpPost':
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.CheckNReturnSO(PermissionToken permToken, CodeAccessPermission demand, StackCrawlMark& stackMark, Int32 unrestrictedOverride, Int32 create)
at System.Security.CodeAccessSecurityEngine.Assert(CodeAccessPermission cap, StackCrawlMark& stackMark)
at System.Security.CodeAccessPermission.Assert()
at SqlServices.HttpServices.HttpPost(String Url, String Content)
I have a situation that I need to accomodate as follows:
Customer account table as a bit value as to whether to send an email
For each customer with a true value set I need to run a query against their products table to check whether the reorder value is less than current stock.
if the recordcount for this query is > 0 then I need to send the customer an email using the address in the customer file.
This process needs to be run once per week.
Can anybody offer advice, samples as to resolve this problem
Thanks