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
>

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

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:
>> 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?
>
> <MVChau***@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:
> >> 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
> >>
> >

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:
>> 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?
>>
>> <MVChau***@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:
>> >> 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
>> >>
>> >
>

> Sorry - I meant Service Pack!
> <MVChau***@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:
> >> 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?
> >>
> >> <MVChau***@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:
> >> >> 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
> >> >>
> >> >
> >