SQL Query Problem - SQL Server Forums

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 Development (2000)

SQL Query Problem

Author

Topic

kwilliams

194 Posts

Posted - 2004-11-05 : 09:51:49

Server 1: Internal server for intranet site
Server 2: External server for public internet site

I have a SQL query located on an ASP front-end with JScript syntax. I use SQL Server 2000 for the creation & maintenance of the back-end databases that this data comes from.

This query has worked fine for a few years, and then a couple of days ago it stopped pulling results on the ASP page. But when I take that query directly into the Query Analyzer, it works fine. And when I test it with Dreamweaver's Query Analyzer in the page, it also works fine. The correct data is in the database dable. So I have no idea why it's not pulling up on the page.

It might help to know that another page uses a similar, yet much simpler query to pull from the same DB table with no problems. Also, we recently moved the external website to a new web server that runs Windows 2003, Server Pack 2 with IIS 6.0. Server 1 and Server 2 trade jobs between each other to publish information to our internet and intranet sites.

I noticed right off that it was very coincidental that this problem started when we moved the external website to Server 2, but since these errors are on Server 1, I can't see how that move would have caused this. I've included the queries below for the working and not-working pages. Any suggestions about whether this move could have caused these problems, or any other ideas would be greatly appreciated. Thanks.

NOT WORKING QUERY:

<%
var rsAppResults1B = Server.CreateObject("ADODB.Recordset");
rsAppResults1B.ActiveConnection = MM_strConn_Login_STRING;
rsAppResults1B.Source = "SELECT Creation_Date  FROM dbo.Employ_Jobs";
rsAppResults1B.CursorType = 0;
rsAppResults1B.CursorLocation = 2;
rsAppResults1B.LockType = 3;
rsAppResults1B.Open();
var rsAppResults1B_numRows = 0;
%>
<%
var oldCD = (rsAppResults1B.Fields.Item("Creation_Date").Value);
var revisedCD = oldCD.substring(0,10);
%>
<%
var rsAppResults1C__varToday = "%";
if (String(Session("Today")) != "undefined" && 
    String(Session("Today")) != "") { 
  rsAppResults1C__varToday = String(Session("Today"));
}
%>
<%
Session("Date") = (rsAppResults1B.Fields.Item("Creation_Date").Value);
var date = Session("Date");
Session("Year") = date.substring(6,11);
Session("Day") = date.substring(3,4);
%>
<%
var rsAppResults1C = Server.CreateObject("ADODB.Recordset");
rsAppResults1C.ActiveConnection = MM_strConn_Login_STRING;
rsAppResults1C.Source = "SELECT EA.App_ID, EJ.App_ID, EA.Last_Name, EA.First_Name, EA.Last_Update_Date, EJ.Job_ID, EJ.Creation_Date  FROM dbo.Employ_Jobs AS EJ JOIN dbo.Employment_App AS EA ON EJ.App_ID = EA.App_ID  WHERE EJ.Creation_Date LIKE '%"+ rsAppResults1C__varToday.replace(/'/g, "''") + "%'  ORDER BY EJ.Seqno DESC";
rsAppResults1C.CursorType = 0;
rsAppResults1C.CursorLocation = 2;
rsAppResults1C.LockType = 3;
rsAppResults1C.Open();
var rsAppResults1C_numRows = 0;
%>

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

WORKING QUERY:

<%
var rsAppResults1B = Server.CreateObject("ADODB.Recordset");
rsAppResults1B.ActiveConnection = MM_strConn_Login_STRING;
rsAppResults1B.Source = "SELECT Creation_Date  FROM dbo.Employment_App";
rsAppResults1B.CursorType = 0;
rsAppResults1B.CursorLocation = 2;
rsAppResults1B.LockType = 3;
rsAppResults1B.Open();
var rsAppResults1B_numRows = 0;
%>
<%
Session("Date") = (rsAppResults1B.Fields.Item("Creation_Date").Value);
var date = Session("Date");
Session("Year") = date.substring(6,11);
Session("Day") = date.substring(3,4);
%>
<%
var rsAppResults1C = Server.CreateObject("ADODB.Recordset");
rsAppResults1C.ActiveConnection = MM_strConn_Login_STRING;
rsAppResults1C.Source = "SELECT *  FROM dbo.Employment_App  ORDER BY Seqno DESC";
rsAppResults1C.CursorType = 0;
rsAppResults1C.CursorLocation = 2;
rsAppResults1C.LockType = 3;
rsAppResults1C.Open();
var rsAppResults1C_numRows = 0;
%>

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-05 : 09:59:01

and it is not working because ????? the data is simply wrong or it gives an error ???? and the error message you are getting is ?????

since you are getting a SQL statement on the fly in your web page, have you tried just returning it on the page so you can look at it? (i.e. -- no database connect at all, just write the string to the HTML). Do this and show us what the SQL you are tyring to execute is. (hopefully you have already done this to help troubleshoot).

- Jeff

kwilliams

194 Posts

Posted - 2004-11-05 : 10:09:22

I always troubleshoot first, and post questions to this forum when I become lost as to a logical and working solution. Here are my answers to the questions you posed:

quote:
and it is not working because ?????

On the ASP front-end page, no data shows up. The page is just blank.

quote:
the data is simply wrong or it gives an error ????

There's no error message; the page simply doesn't display any data, as if there's no results from the query. But there are results from the query when tried directly in SQL Server's Query Analyzer, and Dreamweaver's Query Analyzer from the ASP front-end page.

quote:
and the error message you are getting is ?????

Again, no error message. Just no data.

quote:
since you are getting a SQL statement on the fly in your web page, have you tried just returning it on the page so you can look at it? (i.e. -- no database connect at all, just write the string to the HTML). Do this and show us what the SQL you are tyring to execute is. (hopefully you have already done this to help troubleshoot).

As I stated in my original post, I tried this query directly within SQL Server's Query Analyzer, and it pulled up the correct results. Here's the query that I tried within the QA:

SELECT EA.App_ID, EJ.App_ID, EA.Last_Name, EA.First_Name, EA.Last_Update_Date, EJ.Job_ID, EJ.Creation_Date
FROM dbo.Employ_Jobs AS EJ JOIN dbo.Employment_App AS EA ON EJ.App_ID = EA.App_ID
WHERE EJ.Creation_Date LIKE '11/5/2004%'
ORDER BY EJ.Seqno DESC

NOTE: The only difference between this query and the front-end query is that I changed the '%varToday%' to today's date. Any suggestions.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-05 : 10:21:48

the query you have posted is not what was generated by your code. your code puts a '%' before the date in the creation date clause.

Don't cut and paste from your CODE and troubleshoot that way -- cut and paste from the RESULTS that your code generates, again by just printing them to the web page. What you have given us could not possibly have been generated from the code you provided. how do you know your date variable is being REPLACE'd properly and successfully into that SQL unless you look at the results actually generated by your code?

Does this make sense?

- Jeff

kwilliams

194 Posts

Posted - 2004-11-05 : 10:27:18

I figured out what the problem is after doing some more testing within the QA. The old server ran SQL Server 7, which had a date format of "11/05/2004", and the new server runs SQL Server 2k which has a date format of "11/5/2004". So that's why the query pulled no results. So I'm going to change the format of all of the old dates, and everything should work fine from now on. It's amazing that something so small can have such a large affect. Thanks for your help.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-05 : 10:31:42

for date compare use yyyymmdd = 20040525.
there should be no problem.

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-05 : 10:44:38

you should probably never use LIKE with dates. everything must get converted to varchar and you cannot use indexes. use a range:

where CreationDate >= @Date and < (@Date+1)

That will be much more efficient, and not prone to errors like the one you have. your situation is EXACTLY why people shouldn't use convert() or LIKE with dates in criteria, but rather use actual datetime values when comparing. (in addition to performance)

if you had written it to use a range, this would never have happened.

(not to mention this is also why you should use stored procedures)

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources