Sending Out An SOS

Apr 1, 2008

Thanks in advance...
What I have to do is take orders and generate a report that gives the daily totals of the orders The dates will be on the vertical and the subclass titles on the horizontal: Output

date SO PS Total
1/1/08 5 4 9
1/2/08 3 2 5
1/3/08 5 4 9

mtd 13 10 23
ytd 13 10 23

The SO orders for example can have a diff status each day and I will
need to count this .
1 - how would I pull the orders that I want. I have pulled all credit work orders in but I only want the ones from 2008. Then once I pull them in how do I get the daily info., ie what records fell into the categories for that day. Do I need a loop, cursor, case statement? I also need to count the # of any give group as above. I am new at this and really do not know where to proceed.

see table below
CREATE TABLE #CREDIT_WO (
WO_Noint,
WO_Status varchar (1),
WO_Classvarchar (30),
WO_Subclassvarchar (30),
WO_Create_Datedatetime,
WO_Sch_Datedatetime,
WO_Post_Datedatetime,
WO_Statevarchar (2)
)
INSERT INTO #CREDIT_WO
(WO_NO, WO_Status, WO_Subclass, WO_Create_Date, WO_Sch_Date, WO_Post_Date, WO_State)
SELECT wowo_wono, wowo_status, wtyp_wo_class, wowo_wo_date, wowo_sch_date, wowo_post_date, wowo_state
FROM WO_WO with(nolock)
inner join wo_type with (nolock)
on wowo_wo_type = wtyp_wo_type
WHERE wo_class = 'Credit'
and subclass in ('so', 'ps')
and wowo_wo_date between @Start_date and @End_date

View 4 Replies


ADVERTISEMENT

Sending Mail Through SP

Mar 28, 2008

I have written a stored procedure which should send mail from the stored procedure in sql server 2005. Here is the SP
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo
ALTER Procedure [dbo].[sp_SMTPMail]
@SenderName varchar(100),@SenderAddress varchar(100),@RecipientName varchar(100),@RecipientAddress varchar(100),@Subject varchar(200),@Body varchar(8000),@MailServer varchar(100) = 'smtp.mail.tspl.com'
AS
SET nocount on
declare @oMail int --Object referencedeclare @resultcode int
EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT
if @resultcode = 0BEGINEXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost', @mailserverEXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @SenderNameEXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress', @SenderAddress
EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, @RecipientName, @RecipientAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @SubjectEXEC @resultcode = sp_OASetProperty @oMail, 'BodyText', @Body
EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL
EXEC sp_OADestroy @oMailEND
SET nocount offexec sp_smtpmail 'ramana','venkataramanaiah.uppalapati@tspl.com','rajan','ramana.u@gmail.com','test','This is a sample message'
when i execute the SP by giving the following input parameters like
exec <spname> 'rajan', 'rajan.crp@tspl.com', 'venkat', 'venkat@tspl.com', 'test', 'Test Message' 
It ran successfully but the recepient venkat@tspl.com did'nt receive any mail from rajan.crp@tspl.com
I used the following commands for configuring OLE Automation Procedures
exec sp_configure 'Show

View 4 Replies View Related

Sending Reminders

May 20, 2008

Hi all,
In my web application, I need to send reminders to a list of persons in that database to fill out a form , once in every month.
Suppose a person have not filled the form, then agaian afetr 15 days  need to send a reminder only to him . And if he doesnt fill it again I need to sned another reminder after  10 days . ...
In one word .. i need to snooze that person who have not complted the form continously and the end of month, even if doesn't complete, I need to tell him tto contact the Admin.
How should I do it in my application. ?
How to schedule a task periodically???
Please Help.
Your Help will be very much Appreciated.
Thanks in advance.

View 6 Replies View Related

Sending SQL Email

Jun 20, 2005

I have developed a B2B Application which is successfully running, now the problem is that my client says that he need a process which sends report of total orders on daily bases.
 
I am using SQL Server and think that SQL Mail is a good idea, I can find many articles about which needs Outlook for that which is much difficult on the web server. Remember I need to send email only not to receive.
 
Thanks, any reply will be highly appreciated.
 

View 1 Replies View Related

Sending Mail

Apr 27, 2000

I am having a problem with SqlServer 7.0 sending mail when the sqlserver account is not logged into the machine and mail is open. If I log off of the box and an alert sends mail it just sits in the outbox of the mail client until I log in. Is this the way it is supposed to be or am I doing something wrong. I am using the Inbox as my client.

Thanks

Tom Goltl

View 1 Replies View Related

Sending An Email From Within An SP

Jan 8, 2001

The following SP adds a record to a table. I want to then send an email from within this SP to notify someone that their login is blocked. I can get their email address easily with a simple select but how does the xp_sendmail procedure actually fit in ?? I've tried adding it in just before the raiserror part but it doesn't work ? The raiserror part is used to trigger an alert to notify me but i need to also notify the client. Can anyone see what i am doing wrong please. Many thanks.

CREATE PROCEDURE usp_log_BlockedLogin_ins

@LoginName varchar(50),
@IP_Address varchar(15),
@Failed_Pswd varchar(50)

AS

BEGIN

DECLARE @LoginID int

SET @LoginID = (SELECT fa_loginID FROM tbl_Login WHERE fs_LoginName = @LoginName)

INSERT into tbl_Login_Audit_Trail
(
fi_LoginID,
fs_IP_Address,
fd_DateTime,
fb_Blocked,
fs_Failed_LoginName,
fs_Failed_Password,
fb_FailedAttempt
)

VALUES
(
@LoginID,
@IP_Address,
current_timestamp,
1,
NULL,
@Failed_Pswd,
1
)
END

BEGIN
RAISERROR (50001,10,1) WITH LOG -- this is used to trigger an alert
END
GO

View 1 Replies View Related

Sending Email

Mar 9, 2001

I want to send an email to all the addresses that are contained in a table. I have tried using xp_sendmail, but I just get an error saying that xp_sendmail cannot be found.
Any ideas???

Many Thanks

View 1 Replies View Related

Sending Email

Apr 25, 2008

Ok I am going to try and explain this best as possible.
I have a report that gets everyones name who has not filled in attendance for a class. what they want is when the report is ran an email gets sent out. so I have written the sql to create a temp table that will get the distinct email.... so lets say the table consists of

jacob.ostop@synergit.com
xxxx@yyyy.com
gagsag@shsna.com


what i need to do now is get those three address and add them to a string that would be in the format of

jacob.ostop@synergit.com ; xxxx@yyyy.com ; gagsag@shsna.com

so that i can put that in the TO field of my email.
my problem is that i dont know how to get them each 1 at a time and put in semi colons..

can someone please help me!

View 1 Replies View Related

Help Sending Email

Jun 23, 2006

My code below works fine when run from my pc (changed all the values forobvious reasons). The code is placed inside a DTS task via VBS scripting.But when I try to run directly from the server where sqlserver is installed,the script fails.I have SMTP running, but there is no outlook installed.Can someone please advise what I am missing.ThanksBobSet objEmail = CreateObject("CDO.Message")objEmail.From = "send@test.com"objEmail.To = "receive@test.com"objEmail.Subject = "TEST SUBJECT"objEmail.AddAttachment "\server est.csv"objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "SERVER_NAME"objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "username"objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "userpwd"objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25objEmail.Configuration.Fields.UpdateobjEmail.Sendset objEmail = nothing

View 3 Replies View Related

Sending Messages With ADO.NET 1.0

Mar 22, 2006

Hi, after some tooling round, googling, hair-pulling etc i have some prototypes of a simple C#.NET 1.1 console app which can now post a message to my yukon SB service i set up. It calls an internal activation stored proc to store the message in a simple table.
The problem i had was in the connection string "... Asynchronous Processing=true". The run-time throws an exception moaning about the Asynchronous Processing=true bit. So i just omitted it completely... Seems to work fine and as expected. I can post a message, my app exits, and the dummy stored proc (which i deliberately made enter a loop updating the table thousands of times to slow it down and lock the table) completed several seconds later proving the asychronousity of it (i issued a "select count(*) from X" query to wait for the write lock to be released, so i knew when the stored proc had finished and released its lock).

So, i guess my question is, firstly what is the "Asynchronous Processing=true" bit for in the connection string if we can just leave it out? And can anyone see any issues with what ive done?

View 3 Replies View Related

Sending Null Value

Mar 6, 2007

Hello. A question please. How can I send a null value from vb.net to a datetime field of Sqlserver ?, This fields allows null values.

Thanks...

View 1 Replies View Related

Sending SQL Mail

Jul 22, 2006

Hi,

I am using SQL Server 2005. I have configured my email on Database Mail. when I am trying to send email using xp_sendmail I am getting problem error as:

Msg 17925, Level 16, State 1, Line 0

xp_startmail: failed with mail error 0x80040111

I am using following sql statement

master.dbo.xp_startmail 'myaccount','mypwd'

master.dbo.xp_sendmail 'tomail1@mail.co.in','test'

where myaccount and mypwd is my password on my email profile.'tomail1@mail.co.in' is the destination email address..

thanks

View 15 Replies View Related

Sending Automated Sql Emails

Mar 12, 2007

Hi
I am using asp.net framwork 2 with sql 2005 express edition. Can anyone please provide me any sample of sending automated emails (like welcoming people when they register to my website, or if their personal details have been updated....). or any e-book/website ref will do. I am not very expert in sql/db and smtp (or other email) stuffs. So please help me.
With thanks

View 6 Replies View Related

Sending Databases/tables ?

Dec 21, 2007

Hello,
Let's say I created some tables in SQL server using the designer.  Tables -> Right click -> New Table ->... How would I send someone else the script for creating that table?  Or how would I send somebody else the tables?
Thanks.

View 3 Replies View Related

Sending E-mail On SP Action

Jun 18, 2008

Hello all,
I've run into a situation which I'm not sure which option to use. I have a web-based app that needs to send an email to a user once an auction has ended (the user being the winning bidder). The backend of the app is SQL 2005. I'm trying to figure out how to get this to work but I'm not sure if I should be using Database Mail or if I should be using a windows service on the web server that pings the database to check if the auction has ended and if the user has been notified. Can anyone shed some light as to which way I should be doing this? Additionally, I'm planning on formatting the style of the mail message so it'll need to be in HTML format and not plain text.
Any help is greatly appreciated.
Regards,
Axel

View 17 Replies View Related

Sending Data To Excel

Apr 4, 2001

I want to send data to a excel spreadsheet from SQL server 7.
I know that I can create a odbc dsn which points to a table or a view on SQL Server. That works well.
Is there any way to point to a stored procedure.
I want to pull data to a spreadsheet based on a stored procedure.

Any suggestions ?, Is it possible ?

View 5 Replies View Related

Sending Mail Through SQL Server

May 9, 2001

I need help regarding how to send mails through SQL server. Let me give you some more details: I am having a table where in all the e-mail id's are stored so i need to write a stored procedure that will pick up the email id from the table and it has to send the mail once you run the stored procedure.
Any help in this regard will really help...pls help me out...

View 1 Replies View Related

Restarting SQL And Sending An Email

Dec 30, 1999

If SQL is restarted for any reason, I need SQL server to send emails to others letting them know that the server is has restarted once it comes back up.

Is there an Error # that I can build an Alert on? Ideas???

Thank you,
tw

View 1 Replies View Related

Problem Sending Mail

Aug 25, 2000

Hi,

I have few DTS package schedule on my SQL Server 7. When the DTS fail, it's supposed to send an e-mail to me. But, for an unknown reason, the server send me some e-mail to report the failure of the DTS and suddenly...nothing. The server stop sending e-mail to report the failure of the DTS package. I decided to restart the Server and... i receive over 300 e-mails that i was supposed to receive 3 week ago about the failure of the DTS package. It's not the first time that this problem occur and everytime i need to restart the server, now i am tired of that.

Does anyone have see this problem before?

Martin

View 1 Replies View Related

Sending Email From SQL Server

Nov 29, 2000

Does anyone know how to send emails from within a trigger without the use of a mail server. I know that xp_sendmail can be used if a mail server, such as MS Exchange Server is available and SQL Server is set up as an e-mail client. However is there a way to send mail to the outside world without the use of a mail server? (Perhaps there is a third party product or extended stored procedure that can give me this ability?)

Many thanks for any response.

View 1 Replies View Related

Sending Email From Sqlserver 7.0

Mar 13, 2000

Hello,

I would like to send email from Sqlserver 7.0, and I don't have a MS exchange server(I have a smtp server).
Do I need to have MS exchange server and if not, how can I get around this?
Can Outlook express be of any use?

Thanks,
yi

View 1 Replies View Related

Sending Email To Users

Sep 5, 2005

Hi,

I have a table or two in a MS SQL DB. One table has a document name, when it was last uploaded and how often it should be looked at/reviewed.

e.g.

name.doc
01/09/2005
Weekly
1

fish.doc
01/08/2005
Daily
2

Each document is associated with a user (another table) so in the above example 1 and 2 refer to theprimary key of the 'admin' table to get a user.

I want to be able to automatically send an email to users notifying them to check their documents on whatever interval they have set.

I hope this make sense!

JJ

View 1 Replies View Related

Sending SQL Data To Access

Feb 22, 2005

I've created a "data warehouse" for the Access users in my area. I take
data from SQL Server, and write it to a network access DB using DTS.
I don't want to link to the tables, I really need to put the data in an access
format. The problem I'm having is that I'm appending the data and not overwriting it. Is it possible to truncate those table before importing?
Do I have to use ActiveX Script to accomplish this?

Thanks
Mark

View 4 Replies View Related

Sending Emails To A Database

Feb 14, 2006

I'm new to programming and to databases so apologies if this sounds like a stupid question.

I have a html page with a simple javascript function that uses the href mailto tag to send an email to a normal email address. I was wondering if it would be posssible to send the email directly to an SQL Server database, without having to pass through an intermediate parsing app. It would mean assigning an email address to the database. Is this possible with SQL Server? I know there would still be problems with parsing the email content to the appropriate fields, but would the sending of the email to the database in itself be possible?

I haven't much experience using databases and I don't currently have access to the SQL Server Express database that will be used to store the email content, so I'm not sure how difficult an operation this would be. I've looked on the internet but I haven't been able to find anything. I was hoping someone here might have done something similar, either with SQL Server or with any other database.

Thanks in advance.

View 2 Replies View Related

Sending Data From One Database To Another

Jan 18, 2007

DBMS = SQL server 2005Web programming language = ASP.NETHiI have database 1 sat on server 1.I would like to move selected tuples from specific tables into database 2 sat on server 2.What do i need to research to try and find a method of doing this?Is the correct approach to move the selected data into a 'recordset' and if so how can i send this record set to database 2?many thanksBil

View 14 Replies View Related

Problem Sending Email

May 14, 2007

I'm trying to send email through Integration Services (based on when a package has been executed successfully) however I get this error message:

SSIS package "CopyEmailTable.dtsx" starting.
Error: 0xC002F304 at Send Mail Task, Send Mail Task: An error occurred with the following error message: "Failure sending mail.".
Task failed: Send Mail Task
SSIS package "CopyEmailTable.dtsx" finished: Success.

Sending email via Mgmt Studio works fine, but not through Integ Services.

Any help in this is greatly appreciated!

View 2 Replies View Related

Sending Email On Insert

May 21, 2007

Hi. I found this article on 4GuysFromRolla (http://www.4guysfromrolla.com/webtech/tips/t051706-1.shtml) about sending an email when the database is updated. Well, I need to take this example a bit further. We are allowing people to enter their email address on our site to receive email updates on their case. I have all of that working in the database. But I don't know how to modify their example to loop through a listing of email addresses who want to receive an email when the case has been updated. Does anybody know how I can accomplish this? Thanks for all your help!

View 1 Replies View Related

Problem In Sending Mail

Jun 9, 2008

Hai to all.,
Im trying to send mail from Sql server but my efforts were ended in vain can any one help me on this.,
I have done the coding lik this..
step 1:
use master
go
sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'Database Mail XPs',1
go
reconfigure
go

Step 2:
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Admin', -- Account Name on SMTP (User Defined)
@description = 'Mail Account for Database Mail',-- Account mail description. (User Defined)
@email_address = 'chandrubrave@gmail.com',-- Mail sender E-mail address (User Defined)
@display_name = 'Admin', -- Mail Sender display name, which displays, when mail received by recipient (User Defined)
@username= '', -- SMTP Sever User Name
@password='', -- SMTP Server Password
@mailserver_name = '190.160.110.50' -- SMTP Server Address'.

step 3:
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'New profile',
@description = 'Profile used for database mail'

Step 4:
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'New profile',
@account_name = 'New Admin',
@sequence_number = 1

Step 5:
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'New profile',
@principal_name = 'public',
@is_default = 1 ;

Step 6:
declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' My First Database Email '
EXEC msdb.dbo.sp_send_dbmail @recipients='chandramohanc@zylog.co.in',
@subject = 'My Mail Test',
@body = @body1,
@body_format = 'HTML' ;

I have got a response like --Mail queued.

But i dint get any mail and also in msdb.dbo.sysmail_event_log table have got an entry with description fails ..
Sorry for posting such a long post..

Thanks In advance

View 2 Replies View Related

Sending SMS From Sql Server 2005

Jun 18, 2008

Hi

How can i send sms to mobile phone from sql server 2005.
Is it possible!

Regards
Js.Reddy

View 1 Replies View Related

Sending Email Sp_OAsetproperty

Jun 18, 2008

i'm using

EXEC @hr = sp_OASetProperty @object, 'HTMLBody', 'some text'

for sending email from my sql sever.

how can i make my content dynamic. So instead of 'some text' i would like to have data (results) from database; eg.: select * from table1.

is this possible anyhow?

thank you in advance

View 1 Replies View Related

Sending Mail Issues

Feb 24, 2007

Hi All,
I'm trying to send mail from sql server I searched the net and i have some things needs calrification and help

What is the difference between sqlmail and sql agent mail ?

is it possible to use and configure them with out having OutLook installed on the server?

when creating a mail profile to be used by SQL mail this profile must be created under the account that runs SQL SERVER Service .. must this account be authenticated under Exchange server if i connect to it ??

to connect to Microsoft Exchange Server can i use the XPSMTP or i have to use SQL MAIL ??

Too much questions i Know but i appreciate your help any way

View 3 Replies View Related

Sending Mail From Sql Server?

Jun 11, 2007

Hi,

i want to send Mail from sql server.which means that when i execute an query a mail will send to mentioned e-mail.is it any predefined function is there?.if,yes,can u explain Detail.

Regards
Umapathy

View 2 Replies View Related

Sending Mail In Blocks

Jun 12, 2007

I need to find a way to send mail in blocks of 100 and 500 at intervals of 120 seconds. Does anyone know of any code for this?

Thanks!

Etymon

View 4 Replies View Related







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