error querying SQL Server from web page

We have a problem with an asp file retrieving data from SQL Server v7 databases i
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 b
backing up an existing database and restoring it with the new project names. Ther
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 th
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 additiona
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\restor
2. logging on to the SQL Server box directly and installing the new database rather than doing it
remotely with a SQL Server clien
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 the 2 queries in the asp file and omitted the IsNull function from both. The web page came up without a hitch
It appears that the code specifies the server name earlier in setting up a connection 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 queries. The developer who wrote 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 problem.