Exception_Access_Violation Error?

Hi all,
Our production server had a raid failure, which we fixed overnight.
This is where the problem started, at least i think.
Now all of a sudden(after the server was restarted) i am getting
following error, when one of the stored procedure gets executed
(I have more then 200 Stored Procedure and they are all working fine)
It does not throw error everytime it gets executed, but randomly it
will fail.
"SqlDumpExceptionHandler: Process 233 generated fatal exception
c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this
process" this is the error message i get from exception.
"Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
Access Violation occurred reading address 00000148 " This is the
exception Code in Dump File.
This error also creats a dump file in \MSSQL\Log and logs into SQL
Error Log.
I also did DBCC CHECKDB suggested by many people on Internet and
everything seems fine, in terms of data corruption.
CHECKDB found 0 allocation errors and 0 consistency errors in database
'XXXX'.
I even tried dropping and recreating the stored procedure, but same
result and no luck.
My question is why all of a sudden or server re-start would trigger
this errors?
By the way I am using VB.Net 2003 and SQL Server 2000 and Windows 2000
is hte OS on DB Server.
My only fear is that our SQL server does not have latest service pack,
but it never had even before the raid failure and all the stored
procedure were working fine.
I restored the backup of this Server(SS 2000) and restored it on SQL
Server 2005. I tried executing the the same report and it works on SQL
Server 2005.
I dont understand whats happening over here and information dump files
doesent make any sense to me.
Any ideas or suggestion will be highly appreciated.
MiteshCan you post the SP text?
I appreciate you state that it never happened before, but as we all know,
the world of IT and databases is a very strange place...
Note - You said you weren't running the latest SP. What version are you
running, as a similar issue was noted as a bug and resolve in SP4... see
link below.
http://support.microsoft.com/?kbid=892451
Immy
<MVChauhan@.googlemail.com> wrote in message
news:1151590907.627466.321680@.75g2000cwc.googlegroups.com...
> Hi all,
> Our production server had a raid failure, which we fixed overnight.
> This is where the problem started, at least i think.
> Now all of a sudden(after the server was restarted) i am getting
> following error, when one of the stored procedure gets executed
> (I have more then 200 Stored Procedure and they are all working fine)
> It does not throw error everytime it gets executed, but randomly it
> will fail.
> "SqlDumpExceptionHandler: Process 233 generated fatal exception
> c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this
> process" this is the error message i get from exception.
> "Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> Access Violation occurred reading address 00000148 " This is the
> exception Code in Dump File.
>
> This error also creats a dump file in \MSSQL\Log and logs into SQL
> Error Log.
> I also did DBCC CHECKDB suggested by many people on Internet and
> everything seems fine, in terms of data corruption.
> CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'XXXX'.
> I even tried dropping and recreating the stored procedure, but same
> result and no luck.
> My question is why all of a sudden or server re-start would trigger
> this errors?
> By the way I am using VB.Net 2003 and SQL Server 2000 and Windows 2000
> is hte OS on DB Server.
> My only fear is that our SQL server does not have latest service pack,
> but it never had even before the raid failure and all the stored
> procedure were working fine.
>
> I restored the backup of this Server(SS 2000) and restored it on SQL
> Server 2005. I tried executing the the same report and it works on SQL
> Server 2005.
> I dont understand whats happening over here and information dump files
> doesent make any sense to me.
>
> Any ideas or suggestion will be highly appreciated.
>
>
> Mitesh
>|||Hi
Here is the Stored Procedure
/*
*/
CREATE PROCEDURE dbo.sp_CS_InvoiceGPMReport_New
@.FromDt datetime,
@.ToDt datetime,
@.NC varchar(4),
@.RemoveInvoice char(500),
@.Return_Net money output,
@.Return_BC money output,
@.Return_Profit money output,
@.Return_S1_Net money output,
@.Return_S1_BC money output,
@.Return_S1_Profit money output,
@.Return_TotalProfit money output,
@.Return_TotalProfit_LC money output
AS
Declare @.Return_Profit_LC money
Declare @.Return_S1_Profit_LC money
set @.Return_Profit_LC =0
set @.Return_S1_Profit_LC=0
set @.RemoveInvoice=rtrim(@.RemoveInvoice)
--For A ################################### Everything Except S1 and
Other Charges -- Invoices
Declare @.TotalNet_Inv money
Declare @.TotalBC_Inv money
Declare @.TotalProfit_Inv money
Declare @.TotalProfit_Inv_LC money
set @.TotalNet_Inv =0
set @.TotalBC_Inv =0
set @.TotalProfit_Inv =0
set @.TotalProfit_Inv_LC =0
Declare @.Str as varchar(50)
--Gets Total Net, Total Base Cost ,Total Profit for Invoices
--print cast( len(@.RemoveInvoice) as char)
if len(@.RemoveInvoice)=0
begin
SELECT @.TotalNet_Inv= SUM(InvoiceItem.Net) ,
@.TotalBC_Inv= SUM(InvoiceItem.[Base Cost] * InvoiceItem.Quantity) ,
@.TotalProfit_Inv= SUM(InvoiceItem.Net) - SUM(InvoiceItem.[Base Cost]
* InvoiceItem.Quantity),
@.TotalProfit_Inv_LC= SUM(InvoiceItem.Net) - SUM(InvoiceItem.[Last
Cost] * InvoiceItem.Quantity)
FROM InvoiceItem,InvoiceStatus
WHERE InvoiceItem.[Invoice No] = InvoiceStatus.[Invoice No]
And (InvoiceStatus.[Date] between @.FromDt and @.ToDt)
AND (InvoiceStatus.Type = 'Invoice')
AND (InvoiceStatus.[Nominal Code] = @.NC)
AND InvoiceItem.[Stock Code] NOT IN ('NOWARRANTY','ACC/HOLD', 'CASH',
'SURCHARGE', 'D/CRD', 'TRANSURG', 'NOCHRGE', 'B/CHQ', 'C/CRD',
'P/CHQ','S1')
end
if len(@.RemoveInvoice)>0
begin
-- print ' invoie to remove =true'
SELECT @.TotalNet_Inv= SUM(InvoiceItem.Net) ,
@.TotalBC_Inv= SUM(InvoiceItem.[Base Cost] * InvoiceItem.Quantity) ,
@.TotalProfit_Inv= SUM(InvoiceItem.Net) - SUM(InvoiceItem.[Base Cost]
* InvoiceItem.Quantity),
@.TotalProfit_Inv_LC= SUM(InvoiceItem.Net) - SUM(InvoiceItem.[Last
Cost] * InvoiceItem.Quantity)
FROM InvoiceItem,InvoiceStatus
WHERE InvoiceItem.[Invoice No] = InvoiceStatus.[Invoice No]
And (InvoiceStatus.[Date] between @.FromDt and @.ToDt)
AND (InvoiceStatus.Type = 'Invoice')
AND (InvoiceStatus.[Nominal Code] = @.NC)
AND InvoiceItem.[Stock Code] NOT IN ('NOWARRANTY','ACC/HOLD', 'CASH',
'SURCHARGE', 'D/CRD', 'TRANSURG', 'NOCHRGE', 'B/CHQ', 'C/CRD',
'P/CHQ','S1')
AND InvoiceStatus.[Invoice No] Not In (Select Intvalue from
dbo.CsvToInt (@.RemoveInvoice))
end
if @.TotalNet_Inv is null
set @.TotalNet_Inv =0.0
if @.TotalBC_Inv is null
set @.TotalBC_Inv =0.0
if @.TotalProfit_Inv is null
set @.TotalProfit_Inv =0.0
if @.TotalProfit_Inv_LC is null
set @.TotalProfit_Inv_LC =0.0
--For B ################################### Everything Except S1 and
Other Charges -- Credit Notes
Declare @.TotalNet_CN money
Declare @.TotalBC_CN money
Declare @.TotalProfit_CN money
Declare @.TotalProfit_CN_LC money
set @.TotalNet_CN =0
set @.TotalBC_CN=0
set @.TotalProfit_CN =0
set @.TotalProfit_CN_LC =0
--Gets Total Net, Total Base Cost ,Total Profit for Credit Note
if len(@.RemoveInvoice)=0
begin
SELECT @.TotalNet_CN= SUM(InvoiceItem.Net) ,
@.TotalBC_CN= SUM(InvoiceItem.[Base Cost] * InvoiceItem.Quantity) ,
@.TotalProfit_CN= SUM(InvoiceItem.Net) - SUM(InvoiceItem.[Base Cost]
* InvoiceItem.Quantity),
@.TotalProfit_CN_LC= SUM(InvoiceItem.Net) - SUM(InvoiceItem.[Last
Cost] * InvoiceItem.Quantity)
FROM InvoiceItem,InvoiceStatus
WHERE InvoiceItem.[Invoice No] = InvoiceStatus.[Invoice No]
And (InvoiceStatus.[Date] between @.FromDt and @.ToDt)
AND (InvoiceStatus.Type = 'Credit Note')
AND (InvoiceStatus.[Nominal Code] = @.NC)
AND InvoiceItem.[Stock Code] NOT IN ('NOWARRANTY','ACC/HOLD', 'CASH',
'SURCHARGE', 'D/CRD', 'TRANSURG', 'NOCHRGE', 'B/CHQ', 'C/CRD',
'P/CHQ','S1')
end
if len(@.RemoveInvoice)>0
begin
--print ' invoie to remove =true'
SELECT @.TotalNet_CN= SUM(InvoiceItem.Net) ,
@.TotalBC_CN= SUM(InvoiceItem.[Base Cost] * InvoiceItem.Quantity) ,
@.TotalProfit_CN= SUM(InvoiceItem.Net) - SUM(InvoiceItem.[Base Cost]
* InvoiceItem.Quantity),
@.TotalProfit_CN_LC= SUM(InvoiceItem.Net) - SUM(InvoiceItem.[Last
Cost] * InvoiceItem.Quantity)
FROM InvoiceItem,InvoiceStatus
WHERE InvoiceItem.[Invoice No] = InvoiceStatus.[Invoice No]
And (InvoiceStatus.[Date] between @.FromDt and @.ToDt)
AND (InvoiceStatus.Type = 'Credit Note')
AND (InvoiceStatus.[Nominal Code] = @.NC)
AND InvoiceItem.[Stock Code] NOT IN ('NOWARRANTY','ACC/HOLD', 'CASH',
'SURCHARGE', 'D/CRD', 'TRANSURG', 'NOCHRGE', 'B/CHQ', 'C/CRD',
'P/CHQ','S1')
AND InvoiceStatus.[Invoice No] Not In (Select Intvalue from
dbo.CsvToInt (@.RemoveInvoice))
end
if @.TotalNet_CN is null
set @.TotalNet_CN =0.0
if @.TotalBC_CN is null
set @.TotalBC_CN =0.0
if @.TotalProfit_CN is null
set @.TotalProfit_CN =0.0
if @.TotalProfit_CN_LC is null
set @.TotalProfit_CN_LC =0.0
Declare @.TotalNet money
Declare @.TotalBaseCost money
Declare @.TotalProfit money
Declare @.TotalProfit_LC money
set @.TotalNet =0
set @.TotalBaseCost=0
set @.TotalProfit =0
--################################### Totals of Everything Except S1
and Other Charges -- (Invoices - Credit Note)
set @.TotalNet = @.TotalNet_Inv - @.TotalNet_CN
set @.TotalBaseCost = @.TotalBC_Inv - @.TotalBC_CN
set @.TotalProfit = @.TotalProfit_Inv - @.TotalProfit_CN
set @.TotalProfit_LC = @.TotalProfit_Inv_LC - @.TotalProfit_CN_LC
--For C ################################### Only S1 -- Invoices
Declare @.TotalNet_S1_Inv money
Declare @.TotalBC_S1_Inv money
Declare @.TotalProfit_S1_Inv money
Declare @.TotalProfit_S1_Inv_LC money
set @.TotalNet_S1_Inv =0
set @.TotalBC_S1_Inv =0
set @.TotalProfit_S1_Inv =0
set @.TotalProfit_S1_Inv_LC =0
SELECT @.TotalNet_S1_Inv= SUM(InvoiceItem.Net) ,
@.TotalBC_S1_Inv= SUM(InvoiceItem.[Base Cost] * InvoiceItem.Quantity)
,
@.TotalProfit_S1_Inv= SUM(InvoiceItem.Net) - SUM(InvoiceItem.[Base
Cost] * InvoiceItem.Quantity),
@.TotalProfit_S1_Inv_LC= SUM(InvoiceItem.Net) - SUM(InvoiceItem.[Last
Cost] * InvoiceItem.Quantity)
FROM InvoiceItem INNER JOIN InvoiceStatus ON InvoiceItem.[Invoice No]
= InvoiceStatus.[Invoice No]
WHERE (InvoiceStatus.[Date] Between @.FromDt and @.ToDt)
AND (InvoiceStatus.[Nominal Code] = @.NC)
AND (InvoiceItem.[Stock Code] = 'S1') AND (InvoiceStatus.Type =
'Invoice')
if @.TotalNet_S1_Inv is null
set @.TotalNet_S1_Inv =0.0
if @.TotalBC_S1_Inv is null
set @.TotalBC_S1_Inv =0.0
if @.TotalProfit_S1_Inv is null
set @.TotalProfit_S1_Inv =0.0
if @.TotalProfit_S1_Inv_LC is null
set @.TotalProfit_S1_Inv_LC =0.0
--For D ################################### Only S1 -- Credit Notes
Declare @.TotalNet_S1_CN money
Declare @.TotalBC_S1_CN money
Declare @.TotalProfit_S1_CN money
Declare @.TotalProfit_S1_CN_LC money
set @.TotalNet_S1_CN =0.0
set @.TotalBC_S1_CN =0.0
set @.TotalProfit_S1_CN =0.0
set @.TotalProfit_S1_CN_LC =0.0
SELECT @.TotalNet_S1_CN= SUM(InvoiceItem.Net) ,
@.TotalBC_S1_CN= SUM(InvoiceItem.[Base Cost] * InvoiceItem.Quantity) ,
@.TotalProfit_S1_CN= SUM(InvoiceItem.Net) - SUM(InvoiceItem.[Base
Cost] * InvoiceItem.Quantity),
@.TotalProfit_S1_CN_LC= SUM(InvoiceItem.Net) -
SUM(InvoiceItem.[Last Cost] * InvoiceItem.Quantity)
FROM InvoiceItem INNER JOIN InvoiceStatus ON InvoiceItem.[Invoice No]
= InvoiceStatus.[Invoice No]
WHERE (InvoiceStatus.[Date] Between @.FromDt and @.ToDt) AND
(InvoiceStatus.[Nominal Code] = @.NC) AND
(InvoiceItem.[Stock Code] = 'S1') AND (InvoiceStatus.Type = 'Credit
Note')
if @.TotalNet_S1_CN is null
set @.TotalNet_S1_CN =0.0
if @.TotalBC_S1_CN is null
set @.TotalBC_S1_CN =0.0
if @.TotalProfit_S1_CN is null
set @.TotalProfit_S1_CN =0.0
if @.TotalProfit_S1_CN_LC is null
set @.TotalProfit_S1_CN_LC =0.0
Declare @.TotalNet_S1 money
Declare @.TotalBaseCost_S1 money
Declare @.TotalProfit_S1 money
Declare @.TotalProfit_S1_LC money
set @.TotalNet_S1 =0
set @.TotalBaseCost_S1 =0
set @.TotalProfit_S1 =0
set @.TotalProfit_S1_LC =0
--################################### Only S1 -- ( Invoices - Credit
Notes)
set @.TotalNet_S1= @.TotalNet_S1_Inv - @.TotalNet_S1_CN
set @.TotalBaseCost_S1= @.TotalBC_S1_Inv - @.TotalBC_S1_CN
set @.TotalProfit_S1= @.TotalProfit_S1_Inv - @.TotalProfit_S1_CN
set @.TotalProfit_S1_LC = @.TotalProfit_S1_Inv_LC - @.TotalProfit_S1_CN_LC
--print 'Invoice Figures ' + cast(@.TotalNet as varchar) + ' , ' +
cast(@.TotalBaseCost as varchar) + ' , ' + cast(@.TotalProfit as
varchar)
--print 'S1 Figures '+ cast(@.TotalNet_S1 as varchar) + ' , ' +
cast(@.TotalBaseCost_S1 as varchar) + ' , ' + cast(@.TotalProfit_S1 as
varchar)
--################################### Returning Values to Front End
set @.Return_Net =@.TotalNet
set @.Return_BC =@.TotalBaseCost
set @.Return_Profit =@.TotalProfit
set @.Return_S1_Net =@.TotalNet_S1
set @.Return_S1_BC = @.TotalBaseCost_S1
set @.Return_S1_Profit =@.TotalProfit_S1
set @.Return_TotalProfit =@.Return_Profit+@.Return_S1_Profit
set @.Return_TotalProfit_LC =@.TotalProfit_LC + @.TotalProfit_S1_LC
--print 'Invoice Figures ' + cast(@.Return_Net as varchar) + ' , ' +
cast(@.Return_BC as varchar) + ' , ' + cast(@.Return_Profit as varchar)
GO
Mitesh
Immy wrote:
> Can you post the SP text?
> I appreciate you state that it never happened before, but as we all know,
> the world of IT and databases is a very strange place...
> Note - You said you weren't running the latest SP. What version are you
> running, as a similar issue was noted as a bug and resolve in SP4... see
> link below.
> http://support.microsoft.com/?kbid=892451
> Immy
>|||just a small sp then!? ;)
Well it doesn't look like you meet the criteria for the bug in the SP4 fix,
but you never know.
Unless you are still experiencing H/W related issues intermittently, then
you're in for a bit of a long run of troubleshooting.
Are you in a position to upgrade the SP?
<MVChauhan@.googlemail.com> wrote in message
news:1151594703.309172.251400@.d56g2000cwd.googlegroups.com...
> Hi
> Here is the Stored Procedure
>
> /*
> */
> CREATE PROCEDURE dbo.sp_CS_InvoiceGPMReport_New
> @.FromDt datetime,
> @.ToDt datetime,
> @.NC varchar(4),
> @.RemoveInvoice char(500),
> @.Return_Net money output,
> @.Return_BC money output,
> @.Return_Profit money output,
>
> @.Return_S1_Net money output,
> @.Return_S1_BC money output,
> @.Return_S1_Profit money output,
>
> @.Return_TotalProfit money output,
> @.Return_TotalProfit_LC money output
>
> AS
> Declare @.Return_Profit_LC money
> Declare @.Return_S1_Profit_LC money
> set @.Return_Profit_LC =0
> set @.Return_S1_Profit_LC=0
> set @.RemoveInvoice=rtrim(@.RemoveInvoice)
>
> --For A ################################### Everything Except S1 and
> Other Charges -- Invoices
> Declare @.TotalNet_Inv money
> Declare @.TotalBC_Inv money
> Declare @.TotalProfit_Inv money
> Declare @.TotalProfit_Inv_LC money
> set @.TotalNet_Inv =0
> set @.TotalBC_Inv =0
> set @.TotalProfit_Inv =0
> set @.TotalProfit_Inv_LC =0
> Declare @.Str as varchar(50)
> --Gets Total Net, Total Base Cost ,Total Profit for Invoices
> --print cast( len(@.RemoveInvoice) as char)
> if len(@.RemoveInvoice)=0
> begin
> SELECT @.TotalNet_Inv= SUM(InvoiceItem.Net) ,
> @.TotalBC_Inv= SUM(InvoiceItem.[Base Cost] * InvoiceItem.Quantity) ,
> @.TotalProfit_Inv= SUM(InvoiceItem.Net) - SUM(InvoiceItem.[Base Cost]
> * InvoiceItem.Quantity),
> @.TotalProfit_Inv_LC= SUM(InvoiceItem.Net) - SUM(InvoiceItem.[Last
> Cost] * InvoiceItem.Quantity)
> FROM InvoiceItem,InvoiceStatus
> WHERE InvoiceItem.[Invoice No] = InvoiceStatus.[Invoice No]
> And (InvoiceStatus.[Date] between @.FromDt and @.ToDt)
> AND (InvoiceStatus.Type = 'Invoice')
> AND (InvoiceStatus.[Nominal Code] = @.NC)
> AND InvoiceItem.[Stock Code] NOT IN ('NOWARRANTY','ACC/HOLD', 'CASH',
> 'SURCHARGE', 'D/CRD', 'TRANSURG', 'NOCHRGE', 'B/CHQ', 'C/CRD',
> 'P/CHQ','S1')
>
> end
> if len(@.RemoveInvoice)>0
> begin
> -- print ' invoie to remove =true'
> SELECT @.TotalNet_Inv= SUM(InvoiceItem.Net) ,
> @.TotalBC_Inv= SUM(InvoiceItem.[Base Cost] * InvoiceItem.Quantity) ,
> @.TotalProfit_Inv= SUM(InvoiceItem.Net) - SUM(InvoiceItem.[Base Cost]
> * InvoiceItem.Quantity),
> @.TotalProfit_Inv_LC= SUM(InvoiceItem.Net) - SUM(InvoiceItem.[Last
> Cost] * InvoiceItem.Quantity)
> FROM InvoiceItem,InvoiceStatus
> WHERE InvoiceItem.[Invoice No] = InvoiceStatus.[Invoice No]
> And (InvoiceStatus.[Date] between @.FromDt and @.ToDt)
> AND (InvoiceStatus.Type = 'Invoice')
> AND (InvoiceStatus.[Nominal Code] = @.NC)
> AND InvoiceItem.[Stock Code] NOT IN ('NOWARRANTY','ACC/HOLD', 'CASH',
> 'SURCHARGE', 'D/CRD', 'TRANSURG', 'NOCHRGE', 'B/CHQ', 'C/CRD',
> 'P/CHQ','S1')
> AND InvoiceStatus.[Invoice No] Not In (Select Intvalue from
> dbo.CsvToInt (@.RemoveInvoice))
>
> end
>
> if @.TotalNet_Inv is null
> set @.TotalNet_Inv =0.0
> if @.TotalBC_Inv is null
> set @.TotalBC_Inv =0.0
> if @.TotalProfit_Inv is null
> set @.TotalProfit_Inv =0.0
> if @.TotalProfit_Inv_LC is null
> set @.TotalProfit_Inv_LC =0.0
> --For B ################################### Everything Except S1 and
> Other Charges -- Credit Notes
> Declare @.TotalNet_CN money
> Declare @.TotalBC_CN money
> Declare @.TotalProfit_CN money
> Declare @.TotalProfit_CN_LC money
> set @.TotalNet_CN =0
> set @.TotalBC_CN=0
> set @.TotalProfit_CN =0
> set @.TotalProfit_CN_LC =0
>
> --Gets Total Net, Total Base Cost ,Total Profit for Credit Note
> if len(@.RemoveInvoice)=0
> begin
> SELECT @.TotalNet_CN= SUM(InvoiceItem.Net) ,
> @.TotalBC_CN= SUM(InvoiceItem.[Base Cost] * InvoiceItem.Quantity) ,
> @.TotalProfit_CN= SUM(InvoiceItem.Net) - SUM(InvoiceItem.[Base Cost]
> * InvoiceItem.Quantity),
> @.TotalProfit_CN_LC= SUM(InvoiceItem.Net) - SUM(InvoiceItem.[Last
> Cost] * InvoiceItem.Quantity)
> FROM InvoiceItem,InvoiceStatus
> WHERE InvoiceItem.[Invoice No] = InvoiceStatus.[Invoice No]
> And (InvoiceStatus.[Date] between @.FromDt and @.ToDt)
> AND (InvoiceStatus.Type = 'Credit Note')
> AND (InvoiceStatus.[Nominal Code] = @.NC)
> AND InvoiceItem.[Stock Code] NOT IN ('NOWARRANTY','ACC/HOLD', 'CASH',
> 'SURCHARGE', 'D/CRD', 'TRANSURG', 'NOCHRGE', 'B/CHQ', 'C/CRD',
> 'P/CHQ','S1')
> end
> if len(@.RemoveInvoice)>0
> begin
> --print ' invoie to remove =true'
> SELECT @.TotalNet_CN= SUM(InvoiceItem.Net) ,
> @.TotalBC_CN= SUM(InvoiceItem.[Base Cost] * InvoiceItem.Quantity) ,
> @.TotalProfit_CN= SUM(InvoiceItem.Net) - SUM(InvoiceItem.[Base Cost]
> * InvoiceItem.Quantity),
> @.TotalProfit_CN_LC= SUM(InvoiceItem.Net) - SUM(InvoiceItem.[Last
> Cost] * InvoiceItem.Quantity)
> FROM InvoiceItem,InvoiceStatus
> WHERE InvoiceItem.[Invoice No] = InvoiceStatus.[Invoice No]
> And (InvoiceStatus.[Date] between @.FromDt and @.ToDt)
> AND (InvoiceStatus.Type = 'Credit Note')
> AND (InvoiceStatus.[Nominal Code] = @.NC)
> AND InvoiceItem.[Stock Code] NOT IN ('NOWARRANTY','ACC/HOLD', 'CASH',
> 'SURCHARGE', 'D/CRD', 'TRANSURG', 'NOCHRGE', 'B/CHQ', 'C/CRD',
> 'P/CHQ','S1')
> AND InvoiceStatus.[Invoice No] Not In (Select Intvalue from
> dbo.CsvToInt (@.RemoveInvoice))
>
> end
>
> if @.TotalNet_CN is null
> set @.TotalNet_CN =0.0
> if @.TotalBC_CN is null
> set @.TotalBC_CN =0.0
> if @.TotalProfit_CN is null
> set @.TotalProfit_CN =0.0
> if @.TotalProfit_CN_LC is null
> set @.TotalProfit_CN_LC =0.0
>
> Declare @.TotalNet money
> Declare @.TotalBaseCost money
> Declare @.TotalProfit money
> Declare @.TotalProfit_LC money
>
> set @.TotalNet =0
> set @.TotalBaseCost=0
> set @.TotalProfit =0
> --################################### Totals of Everything Except S1
> and Other Charges -- (Invoices - Credit Note)
> set @.TotalNet = @.TotalNet_Inv - @.TotalNet_CN
> set @.TotalBaseCost = @.TotalBC_Inv - @.TotalBC_CN
> set @.TotalProfit = @.TotalProfit_Inv - @.TotalProfit_CN
> set @.TotalProfit_LC = @.TotalProfit_Inv_LC - @.TotalProfit_CN_LC
>
> --For C ################################### Only S1 -- Invoices
> Declare @.TotalNet_S1_Inv money
> Declare @.TotalBC_S1_Inv money
> Declare @.TotalProfit_S1_Inv money
> Declare @.TotalProfit_S1_Inv_LC money
> set @.TotalNet_S1_Inv =0
> set @.TotalBC_S1_Inv =0
> set @.TotalProfit_S1_Inv =0
> set @.TotalProfit_S1_Inv_LC =0
>
> SELECT @.TotalNet_S1_Inv= SUM(InvoiceItem.Net) ,
> @.TotalBC_S1_Inv= SUM(InvoiceItem.[Base Cost] * InvoiceItem.Quantity)
> ,
> @.TotalProfit_S1_Inv= SUM(InvoiceItem.Net) - SUM(InvoiceItem.[Base
> Cost] * InvoiceItem.Quantity),
> @.TotalProfit_S1_Inv_LC= SUM(InvoiceItem.Net) - SUM(InvoiceItem.[Last
> Cost] * InvoiceItem.Quantity)
> FROM InvoiceItem INNER JOIN InvoiceStatus ON InvoiceItem.[Invoice No]
> = InvoiceStatus.[Invoice No]
> WHERE (InvoiceStatus.[Date] Between @.FromDt and @.ToDt)
> AND (InvoiceStatus.[Nominal Code] = @.NC)
> AND (InvoiceItem.[Stock Code] = 'S1') AND (InvoiceStatus.Type =
> 'Invoice')
>
> if @.TotalNet_S1_Inv is null
> set @.TotalNet_S1_Inv =0.0
> if @.TotalBC_S1_Inv is null
> set @.TotalBC_S1_Inv =0.0
> if @.TotalProfit_S1_Inv is null
> set @.TotalProfit_S1_Inv =0.0
> if @.TotalProfit_S1_Inv_LC is null
> set @.TotalProfit_S1_Inv_LC =0.0
> --For D ################################### Only S1 -- Credit Notes
> Declare @.TotalNet_S1_CN money
> Declare @.TotalBC_S1_CN money
> Declare @.TotalProfit_S1_CN money
> Declare @.TotalProfit_S1_CN_LC money
> set @.TotalNet_S1_CN =0.0
> set @.TotalBC_S1_CN =0.0
> set @.TotalProfit_S1_CN =0.0
> set @.TotalProfit_S1_CN_LC =0.0
>
> SELECT @.TotalNet_S1_CN= SUM(InvoiceItem.Net) ,
> @.TotalBC_S1_CN= SUM(InvoiceItem.[Base Cost] * InvoiceItem.Quantity) ,
> @.TotalProfit_S1_CN= SUM(InvoiceItem.Net) - SUM(InvoiceItem.[Base
> Cost] * InvoiceItem.Quantity),
> @.TotalProfit_S1_CN_LC= SUM(InvoiceItem.Net) -
> SUM(InvoiceItem.[Last Cost] * InvoiceItem.Quantity)
> FROM InvoiceItem INNER JOIN InvoiceStatus ON InvoiceItem.[Invoice No]
> = InvoiceStatus.[Invoice No]
> WHERE (InvoiceStatus.[Date] Between @.FromDt and @.ToDt) AND
> (InvoiceStatus.[Nominal Code] = @.NC) AND
> (InvoiceItem.[Stock Code] = 'S1') AND (InvoiceStatus.Type = 'Credit
> Note')
> if @.TotalNet_S1_CN is null
> set @.TotalNet_S1_CN =0.0
> if @.TotalBC_S1_CN is null
> set @.TotalBC_S1_CN =0.0
> if @.TotalProfit_S1_CN is null
> set @.TotalProfit_S1_CN =0.0
> if @.TotalProfit_S1_CN_LC is null
> set @.TotalProfit_S1_CN_LC =0.0
>
> Declare @.TotalNet_S1 money
> Declare @.TotalBaseCost_S1 money
> Declare @.TotalProfit_S1 money
> Declare @.TotalProfit_S1_LC money
>
> set @.TotalNet_S1 =0
> set @.TotalBaseCost_S1 =0
> set @.TotalProfit_S1 =0
> set @.TotalProfit_S1_LC =0
> --################################### Only S1 -- ( Invoices - Credit
> Notes)
> set @.TotalNet_S1= @.TotalNet_S1_Inv - @.TotalNet_S1_CN
> set @.TotalBaseCost_S1= @.TotalBC_S1_Inv - @.TotalBC_S1_CN
> set @.TotalProfit_S1= @.TotalProfit_S1_Inv - @.TotalProfit_S1_CN
> set @.TotalProfit_S1_LC = @.TotalProfit_S1_Inv_LC - @.TotalProfit_S1_CN_LC
> --print 'Invoice Figures ' + cast(@.TotalNet as varchar) + ' , ' +
> cast(@.TotalBaseCost as varchar) + ' , ' + cast(@.TotalProfit as
> varchar)
> --print 'S1 Figures '+ cast(@.TotalNet_S1 as varchar) + ' , ' +
> cast(@.TotalBaseCost_S1 as varchar) + ' , ' + cast(@.TotalProfit_S1 as
> varchar)
>
> --################################### Returning Values to Front End
>
> set @.Return_Net =@.TotalNet
> set @.Return_BC =@.TotalBaseCost
> set @.Return_Profit =@.TotalProfit
> set @.Return_S1_Net =@.TotalNet_S1
> set @.Return_S1_BC = @.TotalBaseCost_S1
> set @.Return_S1_Profit =@.TotalProfit_S1
> set @.Return_TotalProfit =@.Return_Profit+@.Return_S1_Profit
> set @.Return_TotalProfit_LC =@.TotalProfit_LC + @.TotalProfit_S1_LC
> --print 'Invoice Figures ' + cast(@.Return_Net as varchar) + ' , ' +
> cast(@.Return_BC as varchar) + ' , ' + cast(@.Return_Profit as varchar)
> GO
>
>
> Mitesh
> Immy wrote:
>|||Yes i can change/upgrade the stored procedures.
Only thing that i am worried is corruption of physical memory.
Mitesh
Immy wrote:
> just a small sp then!? ;)
> Well it doesn't look like you meet the criteria for the bug in the SP4 fix
,
> but you never know.
> Unless you are still experiencing H/W related issues intermittently, then
> you're in for a bit of a long run of troubleshooting.
> Are you in a position to upgrade the SP?
> <MVChauhan@.googlemail.com> wrote in message
> news:1151594703.309172.251400@.d56g2000cwd.googlegroups.com...|||Sorry - I meant Service Pack!
<MVChauhan@.googlemail.com> wrote in message
news:1151597105.343170.162830@.d56g2000cwd.googlegroups.com...
> Yes i can change/upgrade the stored procedures.
> Only thing that i am worried is corruption of physical memory.
> Mitesh
>
> Immy wrote:
>|||Well I can put on service pack, but this problem was not there. It just
seems weird to have
a problem out of blue.
Mitesh
Immy wrote:
> Sorry - I meant Service Pack!
> <MVChauhan@.googlemail.com> wrote in message
> news:1151597105.343170.162830@.d56g2000cwd.googlegroups.com...