SQL Union/ASP problem

[EddySR]

Hi, I've got a major headache with a SQL Union statement.

Basically I'm running a SQL Union statement in ASP that is not returning the first few unions. But if I run the SQL in enterprise manager it runs fine. I can't find anything on the web that even remotely describes this problem so I'm really hoping someone here can answer it for me.

The ASP code is as follows:

DSNtemp = "Provider=SQLOLEDB;Data Source=" & data_source & ";database=" & database & ";uid=" & uid & ";pwd=" & pwd & ";"
	
set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.ConnectionTimeout = 15
MyConn.CommandTimeout =  10
MyConn.Mode = 1 'adModeRead
MyConn.Errors.Clear
Err.Clear

if MyConn.state = 0 then
   MyConn.Open DSNtemp
end if

set rsSetup = Server.CreateObject("ADODB.Recordset")

rsSetup.CursorLocation = 3
rsSetup.CacheSize = 20
rsSetup.open searchSQL, MyConn, 1, 1

if rsSetup.RecordCount > 0 then
   rsSetup.MoveFirst
   rsSetup.PageSize = 20
   TotalPages = rsSetup.PageCount
   rsSetup.AbsolutePage = CurPage
   searchdata = rsSetup.getrows
   searchnumcols = ubound(searchdata,1)
   searchnumrows = ubound(searchdata,2)

   FOR searchrowcounter = 0 TO searchnumrows
      <output results>
   NEXT
end if

And an example of the generated SQL is:

SELECT TOP (500) link_id, searchorder FROM
(
(SELECT link_id, '0' AS searchorder FROM links AS S WHERE (LINK_ADDRESS LIKE '%Level 5, 12 Pitt STREET%' OR LINK_ADDRESS LIKE '%Level 5, 12 Pitt ST%') AND (LINK_STATE = 'NSW') AND (LINK_CITY LIKE '%Sydney%') AND (LINK_ZIP = '2000'))
UNION
(SELECT link_id, '1' AS searchorder FROM links AS S WHERE (LINK_ADDRESS LIKE '%Pitt STREET%' OR LINK_ADDRESS LIKE '%Pitt ST%') AND (LINK_STATE = 'NSW') AND (LINK_CITY LIKE '%Sydney%') AND (LINK_ZIP = '2000'))
UNION
(SELECT link_id, '2' AS searchorder FROM links AS S WHERE (LINK_ADDRESS LIKE '%Pitt%') AND (LINK_STATE = 'NSW') AND (LINK_CITY LIKE '%Surry Hills%') AND (LINK_ZIP = '2000'))
UNION
(SELECT link_id, '3' AS searchorder FROM links AS S WHERE (LINK_STATE = 'NSW') AND (LINK_CITY LIKE '%Sydney%') AND (LINK_ZIP = '2000'))
UNION
(SELECT link_id, '4' AS searchorder FROM links AS S WHERE (LINK_STATE = 'NSW') AND (LINK_CITY LIKE '%Sydney%'))
UNION
(SELECT link_id, '5' AS searchorder FROM links AS S WHERE (LINK_ZIP = '2000'))
UNION
(SELECT link_id, '6' AS searchorder FROM links AS S WHERE (LINK_STATE = 'NSW'))
) AS searchresults
ORDER BY searchorder ASC

ASP will only display results from where searchorder equals 2???

[EddySR]

Ok, thats the weirdest thing ever. When I copied the SQL statement from enterprise manager and run it in ASP it works fine. I then matched the ASP generated SQL statement to enterprise manager version of statement "to the character" and it still doesn't work. What the heck! ???

[dickersonka]

sure you are hitting the same database?

also, is it a long running query? if so, might want up your limits

[EddySR]

I figured it out last night. Its a bit embarrassing! All it was that some of the generated values had consecutive whitespaces. It doesn't show up in html or pasted into a text editor because the browser automatically strips extra whitespace. It wasn't until I exported it with a urlencode function that I realized my mistake. Duh!

Thanks anyway