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 2000 Forums
 SQL Server Administration (2000)
 Intermittent 80040e14 Errors

Author  Topic 

robfrompgmx
Starting Member

13 Posts
Posted - 2006-05-18 : 17:42:43
For some background, I am the owner of PGMx.com. Our server/software was set up by someone who doesn't specialize in MS servers, which has resulted in a number of problems with the server over the last year, from being hijacked by spammers, to viruses/trojans, etc.

Our new programmer is currently in the process of rebuilding a new server completely from scratch because we think that likely these problems might be happening because the security of the server has been compromised in the past.

The issue is an intermittent problem that is causing problems for our clients so they are unable to navigate through our site and/or place an order. It doesn't happen all of the time, so we can't reproduce the problem to troubleshoot. According to our logs, we are currently getting around one thousand 500 errors every single day, so it is a pretty big issue.

I would appreciate any assistance to help get this issue resolved. The lion's share of the errors are happening with our powerleveling.asp page, but this could just be because it is often a primary entry point to our subpages, here is a sample log entry:

2006-05-18 00:05:31 38.119.84.27 GET /powerleveling.asp serverid=692&gameid=16&name=Alliance&ParentServerId=460&tab=powerleveling|65|80040e14|Line_1:_Incorrect_syntax_near_'='. 80 - 68.194.79.25 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+.NET+CLR+1.1.4322) ASPSESSIONIDCQTBQABD=BIGMLNGBNFAAPLAPBAJLMFFI;+clncom=107771534295186041147910730292;+lcnone=107771534295186041147910730292;+__utma=232959135.935738674.1147910724.1147910724.1147910724.1;+__utmb=232959135;+__utmc=232959135;+__utmz=232959135.1147910724.1.1.utmccn=(organic)|utmcsr=google|utmctr=WoW+accounts+for+sale|utmcmd=organic;+ASPSESSIONIDCQTBQABD=CIGMLNGBDAFCMCNLDPEDEJLL http://pgmx.com/gameslist.asp/gameid/16/serverid/460/tab/ 500 0 0

I have also noticed that bots are getting these errors as well, which is not good, because then we won't be indexed by the search engines:

2006-05-18 00:04:39 38.119.84.27 GET /powerleveling.asp serverid=1215&gameid=19&name=Good&ParentServerId=1138&tab=powerleveling|65|80040e14|Line_1:_Incorrect_syntax_near_'='. 80 - 194.224.199.50 noxtrumbot/1.0+(crawler@noxtrum.com) - http://www.pgmx.com/EverQuest_2/United_States 500 0 64

As you can see, it is reporting line 65 from powerleveling.asp as causing the problem, but it is intermittent. The actual code from this portion of the file is the following, line 65 is bold:

strSp_SQL="user_show_parentname "& serverid
set objrs = dbConn.execute(strSp_SQL)
IF not objrs.EOF Then
ServerName = Objrs("Name")
END IF
dm.closeconnection(Objrs)

strSp_SQLRegionName="user_show_Regionname @ParentserverId="&parentserverId
set objrs = dbConn.execute(strSp_SQLRegionName)
IF not objrs.EOF Then
RegionName = Objrs("Name")
END IF

strSp_SQL="admin_select_game "&gameid
set objrs = dbConn.execute(strSp_SQL)
IF not objrs.EOF Then
GameName= Objrs("Name")
CurrencyName = Objrs("Currency")
END IF

I would appreciate any help to get to the bottom of this problem asap. This issue is costing us thousands of dollars, and we need to get it taken care of.

If we can find someone who is really good at resolving these types of issues, we might consider a part-time consulting agreement for help with future issues as well.

Thanks

*** Some things I forgot to mention, that might affect this:

1. We are using ISAPI rewrites
2. I noticed that there is an "option" table, which I know is a reserved name, and it happens to be called by powerleveling.asp. Would this cause an intermittent problem like this?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2006-05-18 : 17:58:45
Are you certain that parentserverId contains what you expect?
strSp_SQLRegionName="user_show_Regionname @ParentserverId="&parentserverId


If not, you could be sending a SQL command to the server that looks like this, and that would produce the error you are seeing.
user_show_Regionname @ParentserverId=


Since you are getting so many errors, you should be able to find what is actually being sent to the server by using the SQL Server Profiler to see what it is getting.




CODO ERGO SUM
Go to Top of Page

robfrompgmx
Starting Member

13 Posts
Posted - 2006-05-18 : 18:18:49
Ok, I am running a trace now. I also noticed that I could reproduce this error by removing &parentserverId

What is strange is that, as you can see in the URL/query, ParentServerID is included in the URL, so it doesn't make sense that the value is not making it.

Is there something specific I should be looking for in the trace, to see exactly what is happening when these 80040e14 errors are created?
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2006-05-18 : 18:44:48
Just look for the TEXT of the command being sent to SQL Server.

It sounds like the command being sent to SQL Server is incorrect.
If that is the case, then the problem is in the application.


CODO ERGO SUM
Go to Top of Page

robfrompgmx
Starting Member

13 Posts
Posted - 2006-05-18 : 19:04:45
This is the type of thing I am seeing, it looks like this every time:

exec sp_reset_connection
exec user_show_parentname 1563
exec user_show_Regionname @ParentserverId=
select * from user_show_Regionname @ParentserverId=

exec sp_reset_connection
exec user_show_parentname 678
exec user_show_Regionname @ParentserverId=
select * from user_show_Regionname @ParentserverId=


Although I am having a hard time linking the trace info to specific errors.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-05-18 : 19:20:02
quote:

exec user_show_Regionname @ParentserverId=
select * from user_show_Regionname @ParentserverId=


These aren't valid queries. The application code will need to be corrected to fix this. Buying a new server will not fix this.

They should look more like this:
exec user_show_Regionname @ParentserverId=SomeValueGoesHere
select * from user_show_Regionname where ParentserverId = @ParentserverId


Tara Kizer
aka tduggan
Go to Top of Page

robfrompgmx
Starting Member

13 Posts
Posted - 2006-05-18 : 19:32:13
But if this is an intermittent problem, does this still mean it is a coding error? This is a problem that I cannot reproduce by going through our website myself (PGMx.com). According to the logs this error is happening as soon as someone clicks on the server links, which links to the powerleveling.asp page, but I can't get the error to happen.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-05-18 : 19:34:13
The queries aren't valid T-SQL. The queries are being passed by your application.

You might want to add some debug code to your application to help troubleshoot this.

Tara Kizer
aka tduggan
Go to Top of Page

robfrompgmx
Starting Member

13 Posts
Posted - 2006-05-18 : 20:08:09
One thing I have noticed is that when Regionname is actually successful, it looks like this:

exec sp_reset_connection
exec user_show_Regionname 460
exec admin_select_game 16
exec user_show_server @gameid=16
exec user_show_subserver @serverid=460
exec user_show_fraction @fraction=1562
etc (listing servers)

But, when it is not successfull, it looks like this:

exec sp_reset_connection
exec user_show_parentname 956
exec user_show_Regionname @ParentserverId=
select * from user_show_Regionname @ParentserverId=


So it appears that when user_show_parentname is executed first, it causes user_show_Regionname to fail.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-05-18 : 20:12:19
quote:

exec sp_reset_connection
exec user_show_Regionname 460
exec admin_select_game 16
exec user_show_server @gameid=16
exec user_show_subserver @serverid=460
exec user_show_fraction @fraction=1562


Those are all valid T-SQL statements.

quote:

exec user_show_Regionname @ParentserverId=
select * from user_show_Regionname @ParentserverId=


These are not valid T-SQL statements. 80040e14 is a syntax error, so we're definitely on the right track. So you'll need to figure out where in your application the problem exists.

One thing to check in debug mode is whether you are setting parentserverId correctly. In some cases you aren't, which is why you have the problem. Perhaps you are intending to send a NULL to @ParentserverId.





Tara Kizer
aka tduggan
Go to Top of Page

robfrompgmx
Starting Member

13 Posts
Posted - 2006-05-18 : 20:44:30
I do have some programming backround, but I have never actually had any classes in SQL. What is the purpose of including @ParentserverId= in the ASP code?

The two stored procedures are similar:

CREATE PROCEDURE user_show_parentname

@ParentId int

AS

Select * from Server where serverId = @ParentID AND levelid = 1

GO

CREATE PROCEDURE user_show_Regionname

@ParentserverId int

AS

Select * from Server where serverId = @ParentserverID

GO

Because the procedures are very similar and user_show_parentname is working, and user_show_Regionname is NOT, I decided to change:

rSp_SQLRegionName="user_show_Regionname @ParentserverId="&parentserverId

to

rSp_SQLRegionName="user_show_Regionname "&parentserverId

similar to the user_show_parentname code.

It hasn't broken the site, but I will have to give it some to see if any new 80040e14 errors pop up. So far no new ones for the last 17 mins, which is a good sign.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2006-05-18 : 21:12:14
This is just going to hide the problem:
rSp_SQLRegionName="user_show_Regionname "&parentserverId


If parentserverId is empty, the stored procedure call will run, but produce no output. The way is was before wiil actually tell you if you have errors in your call. The way you have it now is just hiding your problem.

Not sure if I can explain it any more. Your application code has some problem that is causing you to make invalid database calls because parentserverId is empty when it shouldn't be.

This is not a problem with the database server or the server side code.



CODO ERGO SUM
Go to Top of Page

robfrompgmx
Starting Member

13 Posts
Posted - 2006-05-18 : 21:19:27
You are right, it is just producing a new error now

65|80040e10|Procedure_'user_show_Regionname'_expects_parameter_'@ParentserverId'__which_was_not_supplied.
Go to Top of Page

robfrompgmx
Starting Member

13 Posts
Posted - 2006-05-18 : 22:47:04
I think we are making some progress now. Our programmer isn't blaming the server anymore, realizing that maybe there is something wrong with the code - there is alot of it...

Thanks for the info and mentioning the profiler, it will help me to point our programmer in the right direction to get rid of any 500 errors.
Go to Top of Page

robfrompgmx
Starting Member

13 Posts
Posted - 2006-05-19 : 00:54:33
We have gone through the code and fixed all the problems I think. For some reason during the sessions some values would become null, so we implemented a check for this to refresh the value when this happens.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site