error querying SQL Server from web page

We have a problem with an asp file retrieving data from SQL Server v7 databa
ses in
our test environment. The asp file opens a recordset to get a list of active
project
databases from another database table. The asp file loops through the record
set and
builds two SQL strings using the loop's current project database name to ret
rieve
counts from each project database. The counts are displayed in a table on a
web page.
Each SQL string is used to open a recordset against the loop's current proje
ct database.
The asp file worked fine until recently. Two new project databases were crea
ted by
backing up an existing database and restoring it with the new project names.
There
is a flag that we turn on to activate a project database. As soon as the new
projects
were turned on, the asp file failed with OLE DB error 0x80040E21. The error
message
indicated that the failure occurred when trying to open the first of the two
recordsets
associated with the two SQL strings that were being created. When we turned
off the new
projects, then everything worked fine again.
We started a trace in SQL Server and found that the SQL string being execute
d changed
when we added one of the new projects.
The SQL string for the old projects looked like:
N'SELECT Sum(StudentCount) FROM server.projectdb.dbo.Sheets WHERE...
in which I have used 'server' to represent the SQL Server name and 'projectd
b' to
represent the project database name. The server name is always the same; onl
y the
project database name changes.
When one of the new projects was reached in the loop, the SQL string started
out the
same as above. But at some point the query changes to:
N'select * from SELECT Sum(StudentCount) FROM server.projectdb.dbo.Sheets WH
ERE...
The query tanks and so does the asp file. A developer searched for postings
relating to the
OLE DB error and saw something about NULLs. Since it was possible for the qu
ery to return NULL,
he tried changing the query in the asp file to:
N'SELECT Sum(isNull(StudentCount, 0)) FROM server.projectdb.dbo.Sheets WHERE
..
This fixed the problem as the query succeeded for all of the project databas
es, old and new.
The question is why this fixed the problem and how did the original query pi
ck up the additional
text "select * from". Because -
1. Upon checking the project databases, there were old project databases tha
t returned NULLs to the
queries. These project databases didn't have a problem when queried by the o
riginal asp file.
2. The new project databases were loaded with data so that they didn't retur
n NULLs. They still
caused the old asp file to fail.
3. In case the problem was with NULLs returned by an old project database pr
eceding or following
the new project database we varied which project databases were turned on. T
his didn't affect the
results at all as the asp file always failed when it came to a new project d
atabase.
So the isNull function somehow cleared up the problem even though the proble
m doesn't appear to
relate to whether NULLs are returned by the original queries.
Other things we tried that didn't help -
1. scripting the database install rather than backup\restore
2. logging on to the SQL Server box directly and installing the new database
rather than doing it
remotely with a SQL Server client
The web server and the database server are both running Windows 2000 Server
5.0.2195 SP4 Build 2195.
The SQL Server version is 7.00.624.
Can someone shed some light on this?That is a very interesting problem... I am curious, does the problem go
away if you do NOT use the 4 part name... SQL may do some strange things
when using the 4-part name because the data could be coming from a linked
server...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Pat Hurst" <anonymous@.discussions.microsoft.com> wrote in message
news:98D3FE8E-D08A-4C8D-8614-8261C877687A@.microsoft.com...
> We have a problem with an asp file retrieving data from SQL Server v7
databases in
> our test environment. The asp file opens a recordset to get a list of
active project
> databases from another database table. The asp file loops through the
recordset and
> builds two SQL strings using the loop's current project database name to
retrieve
> counts from each project database. The counts are displayed in a table on
a web page.
> Each SQL string is used to open a recordset against the loop's current
project database.
> The asp file worked fine until recently. Two new project databases were
created by
> backing up an existing database and restoring it with the new project
names. There
> is a flag that we turn on to activate a project database. As soon as the
new projects
> were turned on, the asp file failed with OLE DB error 0x80040E21. The
error message
> indicated that the failure occurred when trying to open the first of the
two recordsets
> associated with the two SQL strings that were being created. When we
turned off the new
> projects, then everything worked fine again.
> We started a trace in SQL Server and found that the SQL string being
executed changed
> when we added one of the new projects.
> The SQL string for the old projects looked like:
> N'SELECT Sum(StudentCount) FROM server.projectdb.dbo.Sheets WHERE...
> in which I have used 'server' to represent the SQL Server name and
'projectdb' to
> represent the project database name. The server name is always the same;
only the
> project database name changes.
> When one of the new projects was reached in the loop, the SQL string
started out the
> same as above. But at some point the query changes to:
> N'select * from SELECT Sum(StudentCount) FROM server.projectdb.dbo.Sheets
WHERE...
> The query tanks and so does the asp file. A developer searched for
postings relating to the
> OLE DB error and saw something about NULLs. Since it was possible for the
query to return NULL,
> he tried changing the query in the asp file to:
> N'SELECT Sum(isNull(StudentCount, 0)) FROM server.projectdb.dbo.Sheets
WHERE...
> This fixed the problem as the query succeeded for all of the project
databases, old and new.
> The question is why this fixed the problem and how did the original query
pick up the additional
> text "select * from". Because -
> 1. Upon checking the project databases, there were old project databases
that returned NULLs to the
> queries. These project databases didn't have a problem when queried by the
original asp file.
> 2. The new project databases were loaded with data so that they didn't
return NULLs. They still
> caused the old asp file to fail.
> 3. In case the problem was with NULLs returned by an old project database
preceding or following
> the new project database we varied which project databases were turned on.
This didn't affect the
> results at all as the asp file always failed when it came to a new project
database.
> So the isNull function somehow cleared up the problem even though the
problem doesn't appear to
> relate to whether NULLs are returned by the original queries.
> Other things we tried that didn't help -
> 1. scripting the database install rather than backup\restore
> 2. logging on to the SQL Server box directly and installing the new
database rather than doing it
> remotely with a SQL Server client
> The web server and the database server are both running Windows 2000
Server 5.0.2195 SP4 Build 2195.
> The SQL Server version is 7.00.624.
> Can someone shed some light on this?|||May I congratulate you on your prescience. I removed the server name from th
e 2 queries in the asp file and omitted the IsNull function from both. The w
eb page came up without a hitch.
It appears that the code specifies the server name earlier in setting up a c
onnection object. This connection is used to open the 2 query recordsets so
I'm not sure why the server name was used in specifying the table in the que
ries. The developer who wro
te it is out of the office currently so I can't ask him.
Still I can't help wondering why only the two new databases created a proble
m.|||After tracking down a developer with access to the production system, I was
able to find out that production uses 2 database servers. The primary holds
the system's database and some of the project databases. The other holds a f
ew of the project databases
. The two are linked in SQL Server. So that's the rationale for four part na
mes.
Our test environment can only afford one database server so a four part name
is unnecessary as the projects are all on the server that the web page conn
ects to.