Upgraded from SQL2K to SQL2005

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 2005 Forums

Transact-SQL (2005)

Upgraded from SQL2K to SQL2005

Author

Topic

sanjivus
Starting Member

16 Posts

Posted - 2007-09-19 : 14:23:30

We are running on Windows 2003 Ent + SP2. We recently upgraded from SQL 2000 ENT SP4 to SQL 2005 Ent + SP2.

We have inhouse developed .Net web based application. Everything seems to be running fine but some of the webpages data is not sorted in proper order.

Any thoughts ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-19 : 14:30:37

Could you post the SQL code that is retrieving the data?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-19 : 14:31:00

Have you set the compatibility level to 9?

E 12°55'05.25"
N 56°04'39.16"

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-09-19 : 14:39:45

do you use an ORDER BY clause in your SELECT?

sanjivus
Starting Member

16 Posts

Posted - 2007-09-19 : 14:47:29

tkizer: it is a stored procedure and nothing really has changed in there.

Peso: Database is in SQL Server 2000 (80) compatibility mode.

Collation: SQL_Latin1_General_CP1_Cl_AS

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-19 : 14:55:06

But could you post it anyway? It's possible that SQL Server 2000 let you get away with something that 2005 won't.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sanjivus
Starting Member

16 Posts

Posted - 2007-09-19 : 15:14:15

Here is the code...

[Code]
USE [S2T_Test]
GO
/****** Object: StoredProcedure [dbo].[Pay_GetPaymentDetails] Script Date: 09/19/2007 15:02:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER procedure [dbo].[Pay_GetPaymentDetails]
(
@DistID int,
@InvNo int,
@StartDate datetime,
@EndDate datetime,
@UnpaidAlso varchar(5),
@SortBy int = 1
)

as
begin

SET @UnpaidAlso = LTRIM( RTRIM ( @UnpaidAlso ))

IF ( @SortBy = 1 )
BEGIN

SELECT
Distributor.BusinessName, CONVERT(VARCHAR,RefNumber) as InvNo,
ReceivePmt.TotalAmount as InvAmt , Memo,
ReceivePmt.TxnDate as InvDttm,null as paiddttm,
-UnusedPayment as BalanceAmt ,'0.00' as 'PaidAmt','0.00' as 'CreditAmt',
null as CreatedBy, CONVERT(VARCHAR,ReceivePmt.RefNumber) AS qbinvno, ReceivePmt.TxnId, ReceivePmt.TxnDate AS DueDttm, 'P' as TxnType, ReceivePmt.DistID,
Distributor.Address1,Distributor.Address2,Distributor.Address3,Distributor.ActConfirmFax,Distributor.ActPhone
FROM
ReceivePmt INNER JOIN Distributor ON ReceivePmt.DistID = Distributor.DistID
WHERE
ReceivePmt.distid = distributor.distid -- and invoice.invdttm between @Startdate and dateadd(day,1,@EndDate) and invoice.invamt > 0
and
( ReceivePmt.distid = @DistID OR @DistID = 0 )
and
( ReceivePmt.TxnDate between @Startdate and dateadd(day,1,@EndDate) )
and
( UnusedPayment <> 0 )
and
( Deleted <> 'H' )

UNION ALL

SELECT
Distributor.BusinessName, CONVERT(VARCHAR, invoice.InvNo) as InvNo,
invoice.InvAmt , Memo,
invoice.InvDttm,null as paiddttm,
BalanceAmt ,'0.00' as 'PaidAmt','0.00' as 'CreditAmt',
null as CreatedBy, CONVERT(VARCHAR, invoice.qbinvno) as qbinvno ,Invoice.TxnId, DueDttm,TxnType,Invoice.DistID,
Distributor.Address1,Distributor.Address2,Distributor.Address3,Distributor.ActConfirmFax,Distributor.ActPhone
FROM
invoice,Distributor
WHERE
invoice.distid = distributor.distid -- and invoice.invdttm between @Startdate and dateadd(day,1,@EndDate) and invoice.invamt > 0
AND
( invoice.distid = @DistID OR @DistID = 0 )
AND
( invoice.qbinvno = @InvNo OR @InvNo = 0 )
AND
( invoice.invdttm between @Startdate and dateadd(day,1,@EndDate) )
AND
( @UnpaidAlso = 'TRUE' OR (Invoice.PaidFlg = 'N' AND balanceamt <> 0) )
AND
( invoice.InvType = 'D' OR invoice.InvType = 'A' OR Invoice.InvType = 'F' OR Invoice.InvType = 'N' OR Invoice.InvType = 'C' )

ORDER BY
Invoice.InvDttm ASC, Invoice.QBInvNo ASC

END
ELSE
BEGIN
SELECT
Distributor.BusinessName, CONVERT(VARCHAR,RefNumber) as InvNo,
ReceivePmt.TotalAmount as InvAmt , Memo,
ReceivePmt.TxnDate as InvDttm,null as paiddttm,
-UnusedPayment as BalanceAmt ,'0.00' as 'PaidAmt','0.00' as 'CreditAmt',
null as CreatedBy, CONVERT(VARCHAR,ReceivePmt.RefNumber) AS qbinvno, ReceivePmt.TxnId, ReceivePmt.TxnDate AS DueDttm, 'P' as TxnType, ReceivePmt.DistID,
Distributor.Address1,Distributor.Address2,Distributor.Address3,Distributor.ActConfirmFax,Distributor.ActPhone
FROM
ReceivePmt INNER JOIN Distributor ON ReceivePmt.DistID = Distributor.DistID
WHERE
ReceivePmt.distid = distributor.distid -- and invoice.invdttm between @Startdate and dateadd(day,1,@EndDate) and invoice.invamt > 0
and
( ReceivePmt.distid = @DistID OR @DistID = 0 )
and
( ReceivePmt.TxnDate between @Startdate and dateadd(day,1,@EndDate) )
and
( UnusedPayment <> 0 )

UNION ALL

SELECT
Distributor.BusinessName, CONVERT(VARCHAR, invoice.InvNo) as InvNo,
invoice.InvAmt , Memo,
invoice.InvDttm,null as paiddttm,
BalanceAmt ,'0.00' as 'PaidAmt','0.00' as 'CreditAmt',
null as CreatedBy,
CONVERT(VARCHAR, invoice.qbinvno) as qbinvno, Invoice.TxnId, DueDttm,TxnType,Invoice.DistID,
Distributor.Address1,Distributor.Address2,Distributor.Address3,Distributor.ActConfirmFax,Distributor.ActPhone
FROM
invoice,Distributor
WHERE
invoice.distid = distributor.distid -- and invoice.invdttm between @Startdate and dateadd(day,1,@EndDate) and invoice.invamt > 0
AND
( invoice.distid = @DistID OR @DistID = 0 )
AND
( invoice.qbinvno = @InvNo OR @InvNo = 0 )
AND
( invoice.invdttm between @Startdate and dateadd(day,1,@EndDate) )
AND
( @UnpaidAlso = 'TRUE' OR (Invoice.PaidFlg = 'N' AND balanceamt <> 0) )
AND
( invoice.InvType = 'D' OR invoice.InvType = 'A' OR Invoice.InvType = 'F' OR Invoice.InvType = 'N' )

ORDER BY
Invoice.DueDttm ASC , Invoice.QBInvNo ASC
END
end
[/code]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-19 : 15:17:56

I think you need to put your SELECT/UNION ALLs into a derived table and then ORDER BY on the derived table.

SELECT ...
FROM (SELECT ... FROM ... UNION ALL SELECT ... FROM ...) t
ORDER BY ...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sanjivus
Starting Member

16 Posts

Posted - 2007-09-19 : 16:25:16

Is there any reason, for this not to work?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-19 : 16:26:43

Yes. SQL Server 2005.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources