dexweaver
asked on
Different results from stored procedure: Query Analyzer vs. ADO
I have a stored procedure that is creating a temporary table then providing a select query for output based off of the temporary table. A weird thing happened in Access when I tried to call the SP by a pass-through query: the procedure took a long time, then returned blank rows and zeroes where there should have been data.
I then opened the Query Analyzer and ran the SP manually. It returned the information as it was intended to do. Funny. I went back to Access and now the pass-through query was working normally. Arrgh! All that day I could not get the SP to break.
Next morning I tried to run the pass-through query in Access, and it puked on my shoes yet again. I decided not to "fix" it by running the SP in the Query Analyzer, but to instead experiment. I ran the same pass-through on 3 different machines including my computer with no success. I then blamed Access, my default whipping-boy program. Onward to VB and ADO!
Surely pulling in the SP through ADO and creating a simple CSV file (for use in Excel) would be the simplest thing to do, right? Same problem!!! Yet everytime I run it from the Query Analyzer, it works and fixes the pass-through and ADO connections.
I've rebooted the server, which is MS SQL Server 2000 running on Windows 2000 Server SP3. Thanks in advance!
dexweaver
I then opened the Query Analyzer and ran the SP manually. It returned the information as it was intended to do. Funny. I went back to Access and now the pass-through query was working normally. Arrgh! All that day I could not get the SP to break.
Next morning I tried to run the pass-through query in Access, and it puked on my shoes yet again. I decided not to "fix" it by running the SP in the Query Analyzer, but to instead experiment. I ran the same pass-through on 3 different machines including my computer with no success. I then blamed Access, my default whipping-boy program. Onward to VB and ADO!
Surely pulling in the SP through ADO and creating a simple CSV file (for use in Excel) would be the simplest thing to do, right? Same problem!!! Yet everytime I run it from the Query Analyzer, it works and fixes the pass-through and ADO connections.
I've rebooted the server, which is MS SQL Server 2000 running on Windows 2000 Server SP3. Thanks in advance!
dexweaver
Post the sp.....
Make sure that you have
SET NOCOUNT ON
as one of the first statement in the proc--if you don't "informational" messages get returned to the front-end programs messing them up..........
Make sure that you have
SET NOCOUNT ON
as one of the first statement in the proc--if you don't "informational" messages get returned to the front-end programs messing them up..........
ASKER
arbert:
I haven't run the SP in Query Analyzer at all today, so it is still "broken". I tried to run this with SET NOCOUNT ON and it did the same thing.
One thing I failed to mention is that this thing runs in under twenty seconds in Query Analyzer, but bombs out after 3-5 minutes any other way.
Here is the SP:
*************************
CREATE procedure
spBankReconciliation
@StartFundDate datetime,
@EndFundDate datetime
as
if isnull(object_id(N'[tempdb ].[dbo].[# CardTotals ]'),0) <> 0
drop table [#CardTotals]
select
SFB.[FundDate],
SFB.[RegisterDate],
SFB.[SettlementFileBatchID ],
SFB.[TerminalNumber],
ST.[StoreID],
sum((case
when SFDT.CardType = 'MC' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'MC' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'MC' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [MC],
sum((case
when SFDT.CardType = 'VI' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'VI' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'VI' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [VI],
sum((case
when SFDT.CardType = 'DB' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'DB' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'DB' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [DB],
sum((case
when SFDT.CardType = 'VY' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'VY' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'VY' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [VY],
sum((case
when SFDT.CardType = 'DC' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'DC' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'DC' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [DC],
sum((case
when SFDT.CardType = 'DS' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'DS' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'DS' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [DS],
sum((case
when SFDT.CardType = 'AE' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'AE' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'AE' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [AE],
sum((case
when SFDT.CardType = 'WX' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'WX' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'WX' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [WX],
sum((case
when SFDT.CardType = 'MM' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'MM' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'MM' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [MM],
sum((case
when SFDT.CardType = 'PS' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'PS' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'PS' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [SV]
into
#CardTotals
from
SettlementFile SF join
SettlementFileDivision SFDV on SF.SettlementFileID = SFDV.SettlementFileID join
SettlementFileMerchant SFM on SFDV.SettlementFileDivisio nID = SFM.SettlementFileDivision ID join
SettlementFileBatch SFB on SFM.SettlementFileMerchant ID = SFB.SettlementFileMerchant ID join
SettlementFileDetail SFDT ON SFB.SettlementFileBatchID = SFDT.SettlementFileBatchID join
Store ST on SFM.PaymentechMerchantNumb er = ST.PaymentechMerchantNumbe r
where
SFB.FundDate between @StartFundDate and @EndFundDate and
SFDT.RecordType in ('1','4') and
SFDT.TransactionVoidFlag not in ('R','V') and
SFB.RegisterDate is not null
group by
SFB.FundDate,
SFB.RegisterDate,
SFB.SettlementFileBatchID,
SFB.TerminalNumber,
ST.StoreID
select
StoreID,
FundDate,
count(SettlementFileBatchI D) as AllBatchCount,
sum(MC + VI + DB + VY + DC + DS + AE + WX + MM + SV) as AllBatchTotal,
sum(MC + VI + DB + VY + DC) as AllPaymentech,
sum(DS) AS AllDS,
sum(AE) AS AllAE,
sum(WX) AS AllWX,
sum(MM) AS AllMM,
sum(SV) AS AllSV,
sum(case
when FundDate = RegisterDate then 0
else 1 end) as PreEODBatchCount,
sum(case
when FundDate = RegisterDate then 0
else MC + VI + DB + VY + DS + AE + WX + MM + SV end) as PreEODBatchTotal,
sum(case
when FundDate = RegisterDate then 0
else MC + VI + DB + VY + DC end) as PreEODPaymentech,
sum(case
when FundDate = RegisterDate then 0
else DS end) as PreEODDS,
sum(case
when FundDate = RegisterDate then 0
else AE end) as PreEODAE,
sum(case
when FundDate = RegisterDate then 0
else WX end) as PreEODWX,
sum(case
when FundDate = RegisterDate then 0
else MM end) as PreEODMM,
sum(case
when FundDate = RegisterDate then 0
else SV end) as PreEODSV
from
#CardTotals
group by
StoreID,
FundDate
order by
StoreID,
FundDate
drop table
#CardTotals
GO
*************************
I haven't run the SP in Query Analyzer at all today, so it is still "broken". I tried to run this with SET NOCOUNT ON and it did the same thing.
One thing I failed to mention is that this thing runs in under twenty seconds in Query Analyzer, but bombs out after 3-5 minutes any other way.
Here is the SP:
*************************
CREATE procedure
spBankReconciliation
@StartFundDate datetime,
@EndFundDate datetime
as
if isnull(object_id(N'[tempdb
drop table [#CardTotals]
select
SFB.[FundDate],
SFB.[RegisterDate],
SFB.[SettlementFileBatchID
SFB.[TerminalNumber],
ST.[StoreID],
sum((case
when SFDT.CardType = 'MC' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'MC' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'MC' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [MC],
sum((case
when SFDT.CardType = 'VI' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'VI' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'VI' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [VI],
sum((case
when SFDT.CardType = 'DB' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'DB' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'DB' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [DB],
sum((case
when SFDT.CardType = 'VY' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'VY' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'VY' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [VY],
sum((case
when SFDT.CardType = 'DC' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'DC' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'DC' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [DC],
sum((case
when SFDT.CardType = 'DS' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'DS' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'DS' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [DS],
sum((case
when SFDT.CardType = 'AE' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'AE' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'AE' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [AE],
sum((case
when SFDT.CardType = 'WX' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'WX' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'WX' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [WX],
sum((case
when SFDT.CardType = 'MM' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'MM' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'MM' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [MM],
sum((case
when SFDT.CardType = 'PS' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'PS' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'PS' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [SV]
into
#CardTotals
from
SettlementFile SF join
SettlementFileDivision SFDV on SF.SettlementFileID = SFDV.SettlementFileID join
SettlementFileMerchant SFM on SFDV.SettlementFileDivisio
SettlementFileBatch SFB on SFM.SettlementFileMerchant
SettlementFileDetail SFDT ON SFB.SettlementFileBatchID = SFDT.SettlementFileBatchID
Store ST on SFM.PaymentechMerchantNumb
where
SFB.FundDate between @StartFundDate and @EndFundDate and
SFDT.RecordType in ('1','4') and
SFDT.TransactionVoidFlag not in ('R','V') and
SFB.RegisterDate is not null
group by
SFB.FundDate,
SFB.RegisterDate,
SFB.SettlementFileBatchID,
SFB.TerminalNumber,
ST.StoreID
select
StoreID,
FundDate,
count(SettlementFileBatchI
sum(MC + VI + DB + VY + DC + DS + AE + WX + MM + SV) as AllBatchTotal,
sum(MC + VI + DB + VY + DC) as AllPaymentech,
sum(DS) AS AllDS,
sum(AE) AS AllAE,
sum(WX) AS AllWX,
sum(MM) AS AllMM,
sum(SV) AS AllSV,
sum(case
when FundDate = RegisterDate then 0
else 1 end) as PreEODBatchCount,
sum(case
when FundDate = RegisterDate then 0
else MC + VI + DB + VY + DS + AE + WX + MM + SV end) as PreEODBatchTotal,
sum(case
when FundDate = RegisterDate then 0
else MC + VI + DB + VY + DC end) as PreEODPaymentech,
sum(case
when FundDate = RegisterDate then 0
else DS end) as PreEODDS,
sum(case
when FundDate = RegisterDate then 0
else AE end) as PreEODAE,
sum(case
when FundDate = RegisterDate then 0
else WX end) as PreEODWX,
sum(case
when FundDate = RegisterDate then 0
else MM end) as PreEODMM,
sum(case
when FundDate = RegisterDate then 0
else SV end) as PreEODSV
from
#CardTotals
group by
StoreID,
FundDate
order by
StoreID,
FundDate
drop table
#CardTotals
GO
*************************
I would start up SQL Server profiler and capture the SQL Statements that are sent when you run this from Access....I'm guessing maybe there are some problems with parms or something.
You should still include the SET NOCOUNT ON at the top of your proc.
You should still include the SET NOCOUNT ON at the top of your proc.
From the SQL BOL: "The user executing a SELECT statement with the INTO clause must have CREATE TABLE permission in the destination database". I think by default, everyone has rights to create temp tables, but make sure the user that is coming in through ADO isn't being denied access for any reason. Since you are using SQL Server 2000, I would highly recommend canning your temp table altogether and using a table variable. You have to declare the table variable first (no SELECT ... INTO), but you're supposed to do that anyway :).
DECLARE @CardTotals TABLE (...)
INSERT INTO @CardTotals (...)
SELECT ...
FROM SettlementFile SF
JOIN ...etc
DECLARE @CardTotals TABLE (...)
INSERT INTO @CardTotals (...)
SELECT ...
FROM SettlementFile SF
JOIN ...etc
ASKER
arbert:
I ran the profiler and found nothing of interest. Whether I run it during execution of the Access pass-through or the VB with ADO, I get only normal looking steps. It did show that the temp table creation took just 15 seconds shy of 5 minutes, so the problem lies there.
The NOCOUNT statement has been added to the procedure.
I ran the profiler and found nothing of interest. Whether I run it during execution of the Access pass-through or the VB with ADO, I get only normal looking steps. It did show that the temp table creation took just 15 seconds shy of 5 minutes, so the problem lies there.
The NOCOUNT statement has been added to the procedure.
"It did show that the temp table creation took just 15 seconds shy of 5 minutes, so the problem lies there."
Did you look at the parms that got passed--did they look correct? I'm still betting that there is a parm problem if you see the temp table being created, but it takes longer to build....
Did you look at the parms that got passed--did they look correct? I'm still betting that there is a parm problem if you see the temp table being created, but it takes longer to build....
ASKER
arbert:
The 2 parameters look correct. I pass a begin and end date using single quotes:
spBankReconciliation '03/21/2004','03/27/2004'
That is exactly how I call it, and exactly as it showed up in the profiler. In fact, I copied and pasted that line from the profiler.
The 2 parameters look correct. I pass a begin and end date using single quotes:
spBankReconciliation '03/21/2004','03/27/2004'
That is exactly how I call it, and exactly as it showed up in the profiler. In fact, I copied and pasted that line from the profiler.
ASKER
ctcampbell:
The permissions all look normal. I converted the SP to use a table variable. It did the same thing!!! The SP was changed to the following:
***********************
CREATE procedure
spBankReconciliation
@StartFundDate datetime,
@EndFundDate datetime
as
declare @CardTotals table(
[FundDate] [datetime] null ,
[RegisterDate] [datetime] null ,
[SettlementFileBatchID] [int] not null ,
[TerminalNumber] [char] (3) null,
[StoreID] [nvarchar] (4) not null,
[MC] [money] null,
[VI] [money] null,
[DB] [money] null,
[VY] [money] null,
[DC] [money] null,
[DS] [money] null,
[AE] [money] null,
[WX] [money] null,
[MM] [money] null,
[SV] [money] null)
insert into
@CardTotals
select
SFB.[FundDate],
SFB.[RegisterDate],
SFB.[SettlementFileBatchID ],
SFB.[TerminalNumber],
ST.[StoreID],
sum((case
when SFDT.CardType = 'MC' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'MC' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'MC' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [MC],
sum((case
when SFDT.CardType = 'VI' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'VI' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'VI' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [VI],
sum((case
when SFDT.CardType = 'DB' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'DB' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'DB' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [DB],
sum((case
when SFDT.CardType = 'VY' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'VY' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'VY' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [VY],
sum((case
when SFDT.CardType = 'DC' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'DC' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'DC' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [DC],
sum((case
when SFDT.CardType = 'DS' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'DS' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'DS' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [DS],
sum((case
when SFDT.CardType = 'AE' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'AE' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'AE' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [AE],
sum((case
when SFDT.CardType = 'WX' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'WX' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'WX' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [WX],
sum((case
when SFDT.CardType = 'MM' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'MM' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'MM' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [MM],
sum((case
when SFDT.CardType = 'PS' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'PS' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'PS' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [SV]
from
SettlementFile SF join
SettlementFileDivision SFDV on SF.SettlementFileID = SFDV.SettlementFileID join
SettlementFileMerchant SFM on SFDV.SettlementFileDivisio nID = SFM.SettlementFileDivision ID join
SettlementFileBatch SFB on SFM.SettlementFileMerchant ID = SFB.SettlementFileMerchant ID join
SettlementFileDetail SFDT ON SFB.SettlementFileBatchID = SFDT.SettlementFileBatchID join
Store ST on SFM.PaymentechMerchantNumb er = ST.PaymentechMerchantNumbe r
where
(SFB.FundDate between @StartFundDate and @EndFundDate) and
SFDT.RecordType in ('1','4') and
SFDT.TransactionVoidFlag not in ('R','V') and
SFB.RegisterDate is not null
group by
SFB.FundDate,
SFB.RegisterDate,
SFB.SettlementFileBatchID,
SFB.TerminalNumber,
ST.StoreID
select
StoreID,
FundDate,
count(SettlementFileBatchI D) as AllBatchCount,
sum(MC + VI + DB + VY + DC + DS + AE + WX + MM + SV) as AllBatchTotal,
sum(MC + VI + DB + VY + DC) as AllPaymentech,
sum(DS) AS AllDS,
sum(AE) AS AllAE,
sum(WX) AS AllWX,
sum(MM) AS AllMM,
sum(SV) AS AllSV,
sum(case
when FundDate = RegisterDate then 0
else 1 end) as PreEODBatchCount,
sum(case
when FundDate = RegisterDate then 0
else MC + VI + DB + VY + DS + AE + WX + MM + SV end) as PreEODBatchTotal,
sum(case
when FundDate = RegisterDate then 0
else MC + VI + DB + VY + DC end) as PreEODPaymentech,
sum(case
when FundDate = RegisterDate then 0
else DS end) as PreEODDS,
sum(case
when FundDate = RegisterDate then 0
else AE end) as PreEODAE,
sum(case
when FundDate = RegisterDate then 0
else WX end) as PreEODWX,
sum(case
when FundDate = RegisterDate then 0
else MM end) as PreEODMM,
sum(case
when FundDate = RegisterDate then 0
else SV end) as PreEODSV
from
@CardTotals
group by
StoreID,
FundDate
order by
StoreID,
FundDate
GO
***********************
The permissions all look normal. I converted the SP to use a table variable. It did the same thing!!! The SP was changed to the following:
***********************
CREATE procedure
spBankReconciliation
@StartFundDate datetime,
@EndFundDate datetime
as
declare @CardTotals table(
[FundDate] [datetime] null ,
[RegisterDate] [datetime] null ,
[SettlementFileBatchID] [int] not null ,
[TerminalNumber] [char] (3) null,
[StoreID] [nvarchar] (4) not null,
[MC] [money] null,
[VI] [money] null,
[DB] [money] null,
[VY] [money] null,
[DC] [money] null,
[DS] [money] null,
[AE] [money] null,
[WX] [money] null,
[MM] [money] null,
[SV] [money] null)
insert into
@CardTotals
select
SFB.[FundDate],
SFB.[RegisterDate],
SFB.[SettlementFileBatchID
SFB.[TerminalNumber],
ST.[StoreID],
sum((case
when SFDT.CardType = 'MC' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'MC' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'MC' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [MC],
sum((case
when SFDT.CardType = 'VI' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'VI' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'VI' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [VI],
sum((case
when SFDT.CardType = 'DB' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'DB' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'DB' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [DB],
sum((case
when SFDT.CardType = 'VY' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'VY' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'VY' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [VY],
sum((case
when SFDT.CardType = 'DC' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'DC' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'DC' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [DC],
sum((case
when SFDT.CardType = 'DS' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'DS' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'DS' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [DS],
sum((case
when SFDT.CardType = 'AE' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'AE' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'AE' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [AE],
sum((case
when SFDT.CardType = 'WX' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'WX' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'WX' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [WX],
sum((case
when SFDT.CardType = 'MM' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'MM' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'MM' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [MM],
sum((case
when SFDT.CardType = 'PS' and SFDT.TransactionCode = '06' then -1
when SFDT.CardType = 'PS' and SFDT.TransactionCode = '01' then 1
when SFDT.CardType = 'PS' and SFDT.TransactionCode = '03' then 1
else 0 end) * SFDT.TransactionAmount) as [SV]
from
SettlementFile SF join
SettlementFileDivision SFDV on SF.SettlementFileID = SFDV.SettlementFileID join
SettlementFileMerchant SFM on SFDV.SettlementFileDivisio
SettlementFileBatch SFB on SFM.SettlementFileMerchant
SettlementFileDetail SFDT ON SFB.SettlementFileBatchID = SFDT.SettlementFileBatchID
Store ST on SFM.PaymentechMerchantNumb
where
(SFB.FundDate between @StartFundDate and @EndFundDate) and
SFDT.RecordType in ('1','4') and
SFDT.TransactionVoidFlag not in ('R','V') and
SFB.RegisterDate is not null
group by
SFB.FundDate,
SFB.RegisterDate,
SFB.SettlementFileBatchID,
SFB.TerminalNumber,
ST.StoreID
select
StoreID,
FundDate,
count(SettlementFileBatchI
sum(MC + VI + DB + VY + DC + DS + AE + WX + MM + SV) as AllBatchTotal,
sum(MC + VI + DB + VY + DC) as AllPaymentech,
sum(DS) AS AllDS,
sum(AE) AS AllAE,
sum(WX) AS AllWX,
sum(MM) AS AllMM,
sum(SV) AS AllSV,
sum(case
when FundDate = RegisterDate then 0
else 1 end) as PreEODBatchCount,
sum(case
when FundDate = RegisterDate then 0
else MC + VI + DB + VY + DS + AE + WX + MM + SV end) as PreEODBatchTotal,
sum(case
when FundDate = RegisterDate then 0
else MC + VI + DB + VY + DC end) as PreEODPaymentech,
sum(case
when FundDate = RegisterDate then 0
else DS end) as PreEODDS,
sum(case
when FundDate = RegisterDate then 0
else AE end) as PreEODAE,
sum(case
when FundDate = RegisterDate then 0
else WX end) as PreEODWX,
sum(case
when FundDate = RegisterDate then 0
else MM end) as PreEODMM,
sum(case
when FundDate = RegisterDate then 0
else SV end) as PreEODSV
from
@CardTotals
group by
StoreID,
FundDate
order by
StoreID,
FundDate
GO
***********************
ASKER
I just recently deleted and re-created the SP. It fixed it for the first time, but then it reverted to its normal decrepit state on a second run.
I also was able to get it to puke from the Query Analyzer window once! This doesn't help with the trouble-shooting.
Another thing I notice is that the Profiler shows a recompile every other time this thing is run. Hope that tidbit can help.
Tonight I am going to update the server to SQL Server Service Pack 3a. It currently has no service packs applied. I'm running out of ideas. Help!
I also was able to get it to puke from the Query Analyzer window once! This doesn't help with the trouble-shooting.
Another thing I notice is that the Profiler shows a recompile every other time this thing is run. Hope that tidbit can help.
Tonight I am going to update the server to SQL Server Service Pack 3a. It currently has no service packs applied. I'm running out of ideas. Help!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The service pack made the problem go away. Even though I fixed the problem myself, I appreciate the input arbert.
dexweaver
dexweaver
it could be possible that your access connects to sp in different sessions?, just a suggestion though..