Are the sort order set up the same way on both servers? You can use the stored procedure sp_helpsort to see what the servers's sort order and character set is. That could be one reason why you are getting different results between the servers.
William -----Original Message----- From: [EMAIL PROTECTED] [mailto:SQL-List@;houseoffusion.com] Sent: Friday, October 25, 2002 8:33 AM To: [EMAIL PROTECTED] Subject: SQL-List V1 #74 SQL-List Fri, 25 Oct 2002 Volume 1 : Number 74 In this issue: RE: MS SQL 7 vs MS SQL 2000 ---------------------------------------------------------------------- Date: Fri, 25 Oct 2002 12:46:39 +0800 From: "Kay Smoljak" <[EMAIL PROTECTED]> Subject: RE: MS SQL 7 vs MS SQL 2000 Message-ID: <004101c27be1$81df0550$[EMAIL PROTECTED]> Sorry... just to clarify, the database structure, data in the database, operating system, web server, and everything else I can possible think of are identical. It's just the version of sql server that's different. ______________________________________________________ Kay Smoljak Web Developer PerthWeb Pty Ltd Level 9/105 St George's Tc - Perth - Western Australia Ph: (08) 9226 1366 Fax: (08) 9226 1375 www.perthweb.com.au developer.perthweb.com.au > -----Original Message----- > From: Kay Smoljak [mailto:kay@;perthweb.com.au] > Sent: Friday, 25 October 2002 11:59 AM > To: SQL > Subject: MS SQL 7 vs MS SQL 2000 > > > Hi guys, > > This is driving me nuts... the following query works in MS > SQL 7 (production server) and MS SQL 2000 (development > server), but returns the results in different order on each. > The results get put into a huge JavaScript object for related > text boxes and other tricky processing, so the ordering > really matters. It works on the production server won't work > on the development server (wrong way around I know!), and the > only difference is the order of the returned records - I have > run them in side by side windows in Enterprise Manager. I > tried adding ASC and DESC after each > ORDER BY clause, and while it makes a difference to the > order, the two servers are still not returning the same set. > > Does anyone know how I can make them return the same > recordset in the same order? > > Here's the query: > SELECT DISTINCT > Event.pkEvent AS val1, > Event.txtTitle AS disp1, > EventDate.dtEvent AS disp2, > EventDate.pkEventDate AS val2, > TicketType.pkTicketType AS val3, > TicketType.txtTicketType AS disp3, > TicketType.numTicketPrice AS disp4, > TicketType.txtTicketDesc AS disp5 > FROM > Event INNER JOIN EventDate ON > Event.pkEvent = dbo.EventDate.fkEvent > INNER JOIN EventTicketTypeLink ON > dbo.Event.pkEvent = EventTicketTypeLink.fkEvent > INNER JOIN TicketType ON > EventTicketTypeLink.fkTicketType = TicketType.pkTicketType > WHERE > (Event.dtDeleted IS NULL) > AND > (EventDate.dtDeleted IS NULL) > AND > (TicketType.dtDeleted IS NULL) > AND > (EventTicketTypeLink.dtDeleted IS NULL) > ORDER BY > Event.txtTitle DESC, > EventDate.dtEvent DESC, > TicketType.txtTicketType DESC > > > Thanks in advance, > Kay. > > ______________________________________________________ > Kay Smoljak Web Developer PerthWeb Pty Ltd > > Level 9/105 St George's Tc - Perth - Western Australia > Ph: (08) 9226 1366 Fax: (08) 9226 1375 > www.perthweb.com.au developer.perthweb.com.au ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/sql Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm ------------------------------ End of SQL-List V1 #74 **********************
