Incorrect result set displayed in UI

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)

Incorrect result set displayed in UI

Author

Topic

venkath
Posting Yak Master

202 Posts

Posted - 2007-12-30 : 09:48:20

Hi all

I am facing a weird issue in production environment.

Recently We moved our production environment to New server with good hardware configuration.

Frequently, we push some DB changes like (DML statements, DDL statements) to proudction. when ever the db changes are push to this new prod environment, in the application UI the wrong data is being displayed but in query analyzer, if we execute the same query that pull the data from database the result set is fine.Interesting things are, the same is tested on test server before push, but we don't see this problem in test server.we tested the same push on old production server also, we have no issues there as well. we did not have this issue on our old production server before.

The problem will go away if we do the reindex on all the tables and we see the correct result set through application.

I would like to know the reason and permanent solution to fix this issue.

Following are the defferences between old and new production servers

Old Prod server

----------------------

OS - Win 2000 SP2

RAM - 3GB

SQL 2000

----------------

Productversion - 8.00.760

Productlevel - SP3

edition - Enterprise

AWE - Not enabled.

application s/w - ASP

New Prod server

------------------------

OS - Win 2003 SP1

RAM - 32 GB

SQL 2000

--------------

Productversion - 8.00.2040

Productlevel - SP4

edition - Enterprise

AWE - enabled.

Max Server Memory - 30GB

application s/w - ASP

I would appreciate if any one can help with the above issue.

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-30 : 10:37:13

quote:
in query analyzer, if we execute the same query that pull the data from database the result set is fine

This would seem to indicate that the problem isn't in the database. What type of application is it? Does it support caching in any way? I would suggest using sql profiler (when the problem is occuring) to confirm the database call is being made and what specific call it is.

Be One with the Optimizer
TG

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-30 : 15:38:38

Seems app still use cached execution plas, better to update statistics after mess data modification.

venkath
Posting Yak Master

202 Posts

Posted - 2007-12-31 : 07:37:57

It is a webapp and it is located on different server than DB server. It is a ASP(presentation layer) and VB DLLs(backend) application. while pushing the db changes to production, the webapp is stopped and restarted after db changes are applied to prod db.

We are getting the correct result set in query analyzer. only after doing reindexing , the issue is getting fixed on Application UI.

Thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-31 : 09:26:11

out of curiosity, what is the nature of the "incorrect results"? Is it that the latest changes in table structure is not reflected, ie: missing columns? Did you heck the event log for any sql errors that may not be making it to the user?

One thing to look for is if you are using views defined as "select * from ...". You may need to simply redefine your views as part of your release package. You should be using an explicit column list rather than "select *" by the way.

Be One with the Optimizer
TG

venkath
Posting Yak Master

202 Posts

Posted - 2007-12-31 : 09:45:59

Applicatin user changes a Product version from 3.x to 4.x and press NEXT button. in the next screen , it is supposed to show 4.x but still showing 3.x as its product version.

This is being fixed after doing the reindexing on these tables.there onwards no further issues. but this is a pain as it is happening when ever we push db changes to production db.

Thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-31 : 10:29:38

quote:
Originally posted by venkath

Applicatin user changes a Product version from 3.x to 4.x and press NEXT button. in the next screen , it is supposed to show 4.x but still showing 3.x as its product version.

Something still sounds fishy...
Where does the next screen get the version info to report?

Is this sort of a "wizard" where the user can choose to upgrade to 4.x (or not)? When the NEXT button is pushed what happens to change the user's version to whatever they selected?

Be One with the Optimizer
TG

donpolix
Yak Posting Veteran

97 Posts

Posted - 2007-12-31 : 12:06:07

Do you use full text indexing?

Donn Policarpio

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-31 : 23:47:16

Full text index needs repopulation instead of reindex.

venkath
Posting Yak Master

202 Posts

Posted - 2008-01-01 : 05:36:09

quote:
Originally posted by TG

quote:
Originally posted by venkath

Applicatin user changes a Product version from 3.x to 4.x and press NEXT button. in the next screen , it is supposed to show 4.x but still showing 3.x as its product version.
Something still sounds fishy...
Where does the next screen get the version info to report?

Is this sort of a "wizard" where the user can choose to upgrade to 4.x (or not)? When the NEXT button is pushed what happens to change the user's version to whatever they selected?

Be One with the Optimizer
TG

Yes, while going to next screen , it calls a procedure in Database, to display the details regarding the version we have selected in the previous page. But it still shows the old production in the next page. When you do the reindex the problem go away until next production push.

One more difference i forgot to mention is
Database server is on Win 2003 and IIS server(Webapp) is on Win 2000,
Could this be a root cause to the above issue.

Thanks.

donpolix
Yak Posting Veteran

97 Posts

Posted - 2008-01-03 : 12:38:13

quote:
Originally posted by rmiao

Full text index needs repopulation instead of reindex.

Yes, I understand full text needs repopulation.
His case is unusual, and I was just having a hunch he might be using full text indexing and he might be referring to repopulation instead of reindex..if he can post the stored proc that the next page is calling out, it may help.

Donn Policarpio

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources