SQL Statement failure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

SQL Statement failure

Author

Topic

johncogan
Starting Member

20 Posts

Posted - 2009-01-09 : 05:47:52

Hi all

My one clients website has a process which is run once a day in the evening, it is initiated through a user button click.

Essentially the process in a nutshell generates an Excel Spreadsheet containing market data figures for the day, saves the workbook to the local drive and then queries the Sql DB for a list of email addresses. Once it get the email address it then goes through a loop emailing the Excel file to said addresses.

We are only talking of about 40 addresses here, nothing huge.

Now normally this process runs fine without any hic-cups but last night the browser dump an error to the user when he tried to run this process, namely:

quote:
CDO.Message.1 error '8004020c'

At least one recipient is required, but none were found.

/excel_export.asp, line 344

Related ASP code (In case its relevant)

quote:

call createconn
Set oRSGetEmailAddresses = Server.Createobject("ADODB.Recordset")
sqlGetEmailAddresses = "Select EmailAddress FROM ClientUser where Excel_byEmail = 1"
Set oRSGetEmailAddresses = oConn.Execute(sqlGetEmailAddresses)

If Not oRSGetEmailAddresses.EOF then
WHILE(NOT oRSGetEmailAddresses.EOF)
EmailAddress = oRSGetEmailAddresses("EmailAddress")

Set objMail = Server.CreateObject("CDO.Message")

objMail.To = EmailAddress
objMail.From = "xwy@xwy.com"
objMail.Subject = "My subject"
objMail.AddAttachment filepath
objMail.TextBody = "Attached are the Assessments for " & tdDay & "/" & tdMonth & "/" & Year(Date)

if objMail.Send then

else
'// Message sending failure, do nothing
end if

Set objMail = Nothing
oRSGetEmailAddresses.MoveNext
Wend
End If
oRSGetEmailAddresses.close
set oRSGetEmailAddresses = nothing

Looking at the code I extracted the SQL statement and ran it on the server and it returned email addresses but the error seems to suggest that when the ASP code did the query that nothing was return and as such CDONTS had an issue with that.

I have checked the systems event viewer and the Sql logs but nothing out of the ordinary is showing.

My question basically is this. What are the main culprits for the failure of a Sql statement to return a recordset without data when the data does exist on the system.

The System logs and Sql logs show nothing that is anything obvious or out of the ordinary as far as I can see. Not been a System Admin though I may have missed something.

I Realise this is a "how long is a piece of string" question but any pointers as to what to investigate further to try track down this issue please would be a great help.

Server load at the time was not out of the ordinary.

The system is as follows:
Sql 2005 (Sql System info below)

quote:
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 6.0.3790.3959
Microsoft .NET Framework 2.0.50727.1433
Operating System 5.2.3790

Windows Server 2003 Standard Edition SP2
4 Gb of RAM
Code is done in ASP Classic (Vbscript)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-01-09 : 06:13:36

Try and run this query in sql server management studio and see if it returns any blank rows or something. If any of the rows returned are not valid email addresses your script will fail...

Query: Select EmailAddress FROM ClientUser where Excel_byEmail = 1

- Lumbago

johncogan
Starting Member

20 Posts

Posted - 2009-01-09 : 06:34:36

hi Lumbago

Thanks for your reply.

I did run the query and all email addresses are 100% fine.

The error given actually means that the recordset was empty and that CDONTs required at least one email address to be able to send anything. This means that the sql query returned nothing from the code even thoguh there is data available when looking on the database.

Rgds
John

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources