SQL Server Data retrieval problem

Hi All,

I developed an ASP application with SQL Server 7. The first two year it
worked fine. After we used different computer as server, we started having
data retrieval problem. When the problem happens, all WebPages with SELECT
statement only can get one record which is supposed to be 30 or 50 records.
However, the problem does happen very often. It could be 2 -3 times per day.
The coding sample is :

<%

'Connect the Database
Set Conn=Server.CreateObject("ADODB.Connection")
Conn.Open "DSN=DSNName;UID=sa;PWD=;database=branch"

'SQL Command
SQL="select branchname, branchid from branch order by branchid"
'run SQL Command
Set RS=Conn.Execute(SQL)


'Appear result

Response.write "

Re: SQL Server Data retrieval problem

"Wilton Yuan" wrote in message
news:OzFOOn79EHA.2788@TK2MSFTNGP15.phx.gbl...
> Hi All,
>
> I developed an ASP application with SQL Server 7. The first two year it
> worked fine. After we used different computer as server, we started having
> data retrieval problem. When the problem happens, all WebPages with SELECT
> statement only can get one record which is supposed to be 30 or 50
> records.
> However, the problem does happen very often. It could be 2 -3 times per
> day.
> The coding sample is :
>
> <%
>
> 'Connect the Database
> Set Conn=Server.CreateObject("ADODB.Connection")
> Conn.Open "DSN=DSNName;UID=sa;PWD=;database=branch"
>
> 'SQL Command
> SQL="select branchname, branchid from branch order by branchid"
> 'run SQL Command
> Set RS=Conn.Execute(SQL)
>
>
> 'Appear result
>
> Response.write "

Re: SQL Server Data retrieval problem

Wilton Yuan wrote:
> Hi All,
>
> I developed an ASP application with SQL Server 7. The first two year
> it worked fine. After we used different computer as server, we

Which server? The database server? or the web server?

> started having data retrieval problem. When the problem happens, all
> WebPages with SELECT statement only can get one record which is
> supposed to be 30 or 50 records.

What do you do to fix this when it happens? Does it just go away by itself?

> However, the problem does happen
> very often. It could be 2 -3 times per day.

While I agree with what John said, I'm not really sure he addressed your
problem. He may have. Your problem may go away if you take his suggestions.
But, I've never seen your symptoms caused by any of the issues he talked
about. Lots of OTHER symtpoms, sure, but not these particular symptoms.

I think you're going to have to use SQL Profiler to moniter this application
so you can verify what sql statements are being sent to the database when
the symptoms occur. You can set it up to write its output to a file which
you can analyze offline.

Bob Barrows.
PS. You may wish to investigate other, more efficient techniques for getting
your data out of the recordset object. Looping through recordsets, while
providing a lot of functionality, is not very efficient:
http://www.aspfaq.com/show.asp?id=2467
Your code snippet does not indicate that you are utilizing any of the
functionality that makes recordset looping the correct technique to use.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: SQL Server Data retrieval problem

John Blessing & Bob Barrows:

Thanks you very much, I am changing the application by using DSN-less
connection. I hope it could get rid of this problem.

Wilton






"John Blessing" wrote in message
news:34him3F4c7131U1@individual.net...
> "Wilton Yuan" wrote in message
> news:OzFOOn79EHA.2788@TK2MSFTNGP15.phx.gbl...
> > Hi All,
> >
> > I developed an ASP application with SQL Server 7. The first two year it
> > worked fine. After we used different computer as server, we started
having
> > data retrieval problem. When the problem happens, all WebPages with
SELECT
> > statement only can get one record which is supposed to be 30 or 50
> > records.
> > However, the problem does happen very often. It could be 2 -3 times per
> > day.
> > The coding sample is :
> >
> > <%
> >
> > 'Connect the Database
> > Set Conn=Server.CreateObject("ADODB.Connection")
> > Conn.Open "DSN=DSNName;UID=sa;PWD=;database=branch"
> >
> > 'SQL Command
> > SQL="select branchname, branchid from branch order by branchid"
> > 'run SQL Command
> > Set RS=Conn.Execute(SQL)
> >
> >
> > 'Appear result
> >
> > Response.write "

Re: SQL Server Data retrieval problem

Hi,
By John's suggestion, I changed the connection to DSN-less connection
and changed user and passowrd. Unfortunately,
this problem is still there.

There are more details about this problemfor Bob Barrows:
1. The problem happens since we use a new computer as Server whcih has
SQL Server, Web Server all together.

2. When the problem happens, we just use Refresh button to refresh this
page, it will disappear. However, if we refresh one more time, it
happens again.

3. I used SQL Profiler to moniter all T-SQL statement. There are some
strange thing. The T-SQL statement shows
select * select * from table1 where the real T-SQL statement is select *
from tale. But, there are only few such records.

What is that?

Thanks in advance


Wilton






*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Re: SQL Server Data retrieval problem

Wilton Yuan wrote:
> Hi,
> By John's suggestion, I changed the connection to DSN-less connection
> and changed user and passowrd. Unfortunately,
> this problem is still there.
>
> There are more details about this problemfor Bob Barrows:
> 1. The problem happens since we use a new computer as Server whcih has
> SQL Server, Web Server all together.
>
> 2. When the problem happens, we just use Refresh button to refresh
> this page, it will disappear. However, if we refresh one more time, it
> happens again.

I've never seen this.

>
> 3. I used SQL Profiler to moniter all T-SQL statement. There are some
> strange thing. The T-SQL statement shows
> select * select * from table1 where the real T-SQL statement is
> select * from tale. But, there are only few such records.
>
> What is that?
>

There's probably an error building your dynamic sql statement. You now have
a starting place to look for the problem.

For one thing, it's bad programming practice to use "Select *" (commonly
referred to as "selstar"): http://www.aspfaq.com/show.asp?id=2096

For another, you should be using stored procedures instead of dynamic sql,
which will avoid these errors entirely.:
http://www.aspfaq.com/show.asp?id=2201

Although aspfaq is a great resource, Aaron and I have differing opinions on
the best technique to run stored procedures. Here's my take on the subject:
http://tinyurl.com/jyy0

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"