Repeated Alert E-mails

Mar 10, 2008

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

Thanks
Pam

View 3 Replies


ADVERTISEMENT

Search Repeated Record And Give Suggestion As Most Repeated One

Sep 4, 2007

I have problem, i wanted a query which will search the duplicate and then give suggestionmost repeated word
Table containing the records like below








ID 
Movie Name 
New Name

1
Spider Man 
Spider Man

2
Spider Man 2 
Spider Man

3
Spider Man 3 
Spider Man

4
Spider Man UK 
Spider Man

5
Spider Man USA 
Spider Man

6
New Spider Man 
Spider Man

7
Spider Man Black 
Spider Man

8
Spider Man Part 1 
Spider Man

9
Spider Man Part 2 
Spider Man

10
Spider Man I 
Spider Man

11
Spider Man III 
Spider Man

12
Spider Man Part II 
Spider Man
My manufacturer send me the data in this format and i have to allocate there new name
to do some comparison
I wanted to make this process automatic.
what i mean is i need a query which will give me a repeated records  along with suggestion
as new name.
I am fully confident that you  guys will help me out from this problem.
Looking forward

View 9 Replies View Related

Mails

Jan 10, 2007

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

I am getting run time error. Dont know why ?

Any configurations to be made ??



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

Function Main()
dim sch

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

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

cdoConfig.fields.update

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

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

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

Set cdoMessage = Nothing
Set cdoConfig = Nothing


Main = DTSTaskExecResult_Success
End Function


View 5 Replies View Related

CPU Utilization Alert Through WMI Alert

Aug 13, 2015

Can you use the below query to get CPU high utilisation alert purposes for both named and default instance? or, do I need to make any changes here (@wmi_namespace=N'.ROOTCIMV2' ) ?

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'CPU_WM_Utilization_Check',
@message_id=0,
@severity=0,

[code]....

View 2 Replies View Related

Auto E-mails

Jan 23, 2004

Hello,

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

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

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

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

Thanks,
Tracey

View 3 Replies View Related

Mails Whereby Sql 2005

Feb 18, 2008

Hi everyone
i get an error messgae running the following code

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

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

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

View 3 Replies View Related

Formating The Output Of Mails

Nov 16, 2001

Hello,

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

Thanks in Advance for the help

Rani

View 2 Replies View Related

Is It Possible To Extract Mails From Outlook Into

Feb 21, 2008

sql 2005 table ?
Hi everyone,

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

thanks

View 2 Replies View Related

SQL Server Agent - E-Mails

Jul 23, 2005

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

View 2 Replies View Related

Plain Text Mails

Apr 19, 2007

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

View 5 Replies View Related

Subscription E-mails To Other Domains

Apr 3, 2008

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

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

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

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


Any idea what I may be missing?


View 1 Replies View Related

Error In Sending Mails !

Jan 9, 2007

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

Here is the code :



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

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



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

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


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


Main = DTSTaskExecResult_Success
End Function






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


Error Source : Microsoft Data Transformation Services (DTS) Package

Error Description : Error Code: 0

Error Source= CDO.Message.1

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





Error on Line 27





How to solve it ?

View 5 Replies View Related

Mails Are Stuck In The Queue Folder

Jul 23, 2005

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

View 1 Replies View Related

Sending Mails Through SQL Server 2005

Aug 1, 2007



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

ALTER PROCEDURE [dbo].[sp_SMTPMail]

@SenderName varchar(100),

@SenderAddress varchar(100),

@RecipientName varchar(100),

@RecipientAddress varchar(100),

@Subject varchar(200),

@Body varchar(8000),

@MailServer varchar(100) = 'localhost'

AS



SET nocount on



declare @oMail int --Object reference

declare @resultcode int



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



if @resultcode = 0

BEGIN

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

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

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



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



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

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





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



EXEC sp_OADestroy @oMail

END



SET nocount off


The error is

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


Can any one help me please

View 1 Replies View Related

Sending Mails In HTML Format !

Jan 9, 2007

In the DTS, using SEND MAIL TASK option !

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

How to do this ?

View 1 Replies View Related

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

Feb 18, 2008

Hi everyone
i get an error messgae running the following code

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

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


The error says:

quote:

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



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

It works
Anybody know why ?
Thanks

View 11 Replies View Related

Need Help To Create SSIS Package To Send Mails

Sep 25, 2006

Hi All,

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

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

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

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

Dhananjay

View 1 Replies View Related

Using ASP Mail To Send Mails From MSSQL 2000 Server

Mar 13, 2004

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

View 3 Replies View Related

Integration Services :: How To Create SSIS Package With Mails

Aug 17, 2015

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

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

View 3 Replies View Related

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

Apr 30, 2013

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

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

Log file shows following message-

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

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

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

View 7 Replies View Related

REPEATED RECORDS

Feb 21, 2007

Why this SQL procedure gives contiguous repeated records ( 3 or 4 times ) ?
 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ALTER PROCEDURE GetProductsOnPromotInDep
(@DepartmentID INT)
AS
SELECT   Product.ProductID, Title
FROM Product INNER JOIN  
(ProductCategory INNER JOIN
(Category INNER JOIN Department    ON Department.DepartmentID = Category.DepartmentID)
  ON ProductCategory.CategoryID = Category.CategoryID)
  ON Product.ProductID = ProductCategory.ProductID
WHERE Category.DepartmentID = @DepartmentID
AND ProductCategory.CategoryID = Category.CategoryID
AND Product.ProductID = ProductCategory.ProductID
AND Product.OnPromotion = 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

View 2 Replies View Related

Repeated Data

Dec 21, 2004

If I have a student table with 3 columns ID, FirstName and LastName
ID- FirstName -LastName
-------------------------
1 Tom Hanks
2 Jerry Thomas

and I have a subject table with two columns StudentID and Subject. StudentID is a foreign key to the student table.
StudentID - Subject
------------------------
1 History
1 Biology
2 History

If my query is
select distinct student.id, student.fname, student.lname, student.subject from student
left outer join subject on student.id = subject.studentid

then I get
1 Tom Hanks History
1 Tom Hanks Biology.

Is there a way I could get
1 Tom Hanks History
null null null Biology

--> meaning I don't want to repeat data??

thanks
Teena

View 1 Replies View Related

Database Mail Sends Test Mails Successfully, But Sp_send_dbmail Fails

Apr 15, 2008



Hi all,

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

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


EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Test Mail',

@recipients = 'test@test.com',

@body = 'Sent by sp_send_dbmail',

@Subject = 'SQL Server Email Test Email';


It gives the following errors.

(from the above script)


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


(from the test mail)


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


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

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

View 2 Replies View Related

Repeated Character In String

Jun 19, 2006

How can find out the repeated character in each row value of spacific column

View 1 Replies View Related

Rows Repeated With Same Data

Dec 17, 2013

For the below mentioned query their is repetition of rows with the same data.

SELECT srs.prod_area as PA,
srs.art as ArtNo,
b.adsc_unicode as ArtName,
cast(case when a.unit <> '2' then c.avsx/10 else c.avsx/1000 end as integer(2)) as AwsMHS,
cast(srs.estimate/10 as integer(1)) as AwsSRSThisWeek,

[Code] ....

View 4 Replies View Related

Display Repeated Records

Mar 5, 2014

finding the solution for the below query?It displays repeated records.

select distinct ku.username,rro.role_name,rp.resource_type_code,kr.region_name,kc.currency_name,
fcr.cost_rule_id RULE,fcr.rate current_rate,pp.project_name,kou.org_unit_name ORG_UNIT
from

[code]....

View 2 Replies View Related

SELECT The Repeated-same Rows

Feb 5, 2008

Hi!

I am beginner on using SQL.
How can I select the repeated rows (five or more times) on a table which have the same ID's but different updated date. I do not need to group all the rows with the same ID,s but rows which are repeated many times according to the required reports needed.

Below are some information regarding tables and views:

VIEW name= dbo.tblCCInvoicesH
COLUMN ID= ConsumerID
COLUMN DATE= ReadingDate

Thank you in advance...

meti

View 1 Replies View Related

SQL Way To Suppress Repeated Values

Feb 26, 2007

This is a problem I usually solve with procedural code, but I am wondering how/if it could be done with SQL queries.

A simple one to many query like:

Select inv.invnbr, inv.freight, invline.quantity, invline.partnbr, invline.cost from inv inner join invline on inv.id = invline.InvID

Returns something like:

invnbr freight quantity partnbr cost

100 50 3 abc 50
100 50 6 def 65
100 50 10 ghi 70

Is there way I can rewrite the query, or add a subquery such that the result set would be:

invnbr freight quantity partnbr cost



100 50 3 abc 50

100 0 6 def 65

100 0 10 ghi 70

Eg, the freight value, which comes from the one/header table, would show up in only one of the lines for invnbr 100?

Many thanks
Mike Thomas

View 9 Replies View Related

Excluding Repeated Values In A Sum

May 21, 2007

The scenario is as follows:

I have rows coming from the db including:
Contract Number, Contract Name, owner and Actions associated with each contract.

SQL statement brings back:
Contract Number Contract Name Sales Owner Action
1234 123453 $50 Neil x
1234 123453 $50 Bob y
534232 5464634211 $30 Harry z

The problem is that each contract can have multiple actions associated with it...
There ideal output would be:

Contract Number Contract Name Sales Owner Action
1234 123453 $50 Neil x
Bob y
534232 5464634211 $30 Harry z

Total: $80

Basically I need to hide and not include repeated items based on a contract number... one idea I had was creating a group based on contract number and then display info in the header and then only owner and actions in the detail section.. The problem is Totals... how can I can it to avoid count the duplicated values..

Any help would be greatly appreciated.

Thanks,
Neil

View 4 Replies View Related

Need To Avoid Repeated Data In A DataGrid

Apr 15, 2004

Hi there :)

I am developing a system for my uni course and I am stuck a little problem...

Basically its all about lecturers, students modules etc - A student has many modules, a module has manu students, a lecturer has many modules and a module has many lecturers.

I am trying to get a list of lecturers that run modules associated with a particular student. I am able to get a list of the appropriate lecturers, but some lecturers are repeated because they teach more than one module that the student is associated with.

How can I stop the repeats?

Heres my sql select code in my cs file:

string sqlDisplayLec = "SELECT * FROM student_module sm, lecturer_module lm, users u WHERE sm.user_id=" + myUserid + "" + " AND lm.module_id = sm.module_id " + " AND u.user_id = lm.user_id ";
SqlCommand sqlc2 = new SqlCommand(sqlDisplayLec,sqlConnection);
sqlConnection.Open();
lecturersDG.DataSource = sqlc2.ExecuteReader(CommandBehavior.CloseConnection);
lecturersDG.DataBind();

And here is a pic of my Data Model:
Data Model Screenshot

Any ideas? Many thanks :) !

View 1 Replies View Related

Removing Rows With Repeated Data

Sep 7, 2005

I am running a query on multiple tables and the data I get back consists of several repeated rows but with one column different. I want to take out those repeated rows and for the column that is different join that data and separate it by a comma. Can this be done?

Ex.
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 717
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 610
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 310

So i would like this data to come up as:
Cindy Lair 111 Drury Circle Harrisburg Pennsylvania 717,610,310

View 7 Replies View Related

A SQL Procedure To STOP Repeated Info

Feb 4, 2007

In the web site that I am building ( in C# language ), a hypothetic customer who would buy something would be redirected to a Secure payments company where he would make the payment and then the company would send back to my web site, information about this transaction.

My program would then save this info in a Microsoft SQL database. The problem is that this company uses to send the same info several times repeatedly and I do not want to store the same info more than once.

So I want a SQL procedure where it takes the invoice number of the customer ( contained in its string of info ) and looks inside a table to see if it was already stored there. If it is there ( or not ), it would return a value, which could be false/true or 0/1 so my program could use this value to save a new info or not and then activate ( or not ) some related tasks.

I am still learning SQL and I tried the below procedure but it is not working. Which alternative procedure could solve the problem ?

~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE PROCEDURE VerifyIfInvoiceExists
(@Invoice VARCHAR(50))
AS
SELECT COUNT(*) FROM IPN_received
WHERE Invoice = @Invoice
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

View 8 Replies View Related

SQL 7: One Column's Value Is Repeated Throughout Entire Result Set

Jul 20, 2005

I'm using the following query to look in a log file and show somestatistics. It counts the total number of views and the total numberof unique users who have viewed a particular item (the "id" and"title" fields are associated with the item).SELECT title, COUNT(id) AS NumberViews, COUNT(DISTINCT UID) ASNumberUniqueUsers, Type, idFROM ActivityLogWHERE dtTime >= 'Nov 1 2004 12:00AM' AND DtTime <= 'Nov 1 200512:00AM'GROUP BY Title, Type, hdnIdORDER BY TopViewed descThis works fine on SQL Server 2000 (our development machine), but onSQL Server 7 (our production machine), the title column has the samevalue for every row. The other columns show the correct values.If I remove the "ORDER BY" clause, then it works fine. If I removethe "COUNT(DISTINCT UID)" column, it works fine. If I totally removethe WHERE clause, it works fine.Any ideas? It seems like a bug since it works fine on sql2k. I'vetried adding OPTION (MAXDOP 1) to the end of the query, but thatdidn't help.We're using SQL Server 7.0 sp1, and my boss doesn't want to riskupgrading to sp4 because it might screw up all of our otherapplications. I looked through all of the sp2 through sp4 bug fixes,and I didn't see anything specifically mentioning this.Thanks.

View 2 Replies View Related







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