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

Mits

Hi Mits,

I had the same problem with a stored procedure I made one simple change too. All I did was change a parameter of type nvarchar(4000) to type ntext. This caused the same error as what your getting, where the SP worked before.

Two things worked for me:

1) Use the EXEC function and ecapulate your SQL query in quotes. Example:

CREATE PROCEDURE [usp_GroupPermissions_Assert]
(
@.GroupID int,
@.PermissionName nvarchar(255),
@.PermissionValue ntext
)
AS

SET NOCOUNT ON

EXEC('
UPDATE
PERMISSIONS
SET
PERMISSIONS.PERMISSIONVALUE = ''' + @.PermissionValue + '''
FROM
PERMISSIONS_PACKAGES_GROUPS PERMISSIONS
WHERE
AND PERMISSIONS.PERMISSIONNAME = ''' + @.PermissionName + '''
AND PERMISSIONS.GROUPS_ID = ''' + @.GroupID + '''
ELSE
')

SET NOCOUNT OFF

GO

2) Install Service Pack 4.

Hope this helps.

Chris Nillissen

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

Exception handling in Triggers.

If we can get away from the trigger for a second.
One of the least documented aspects on how transactions
work are to do with the process id that its running from.
So if you open up QA do transaction mock statement, open
up another window in QA and do rollback you will get an
error, why, becasue the process in the second window is
different to the first.
The reason why this is important is that a rollback as you
found out will effect EVERY transaction in that process
even though it maybe nested. Try this yourself, you will
get some very interesting answers.
So to get back to your question.
SQL Server will automatically rollback transactions if the
error is serious enough.
If there is no transaction then there will be no
transaction count, there is a handy little global variable
called @.@.TRANCOUNT you may want to look at. So if that is
set to anything but a 0 it will automatically rollback the
transaction irrespective on whether it is done in trigger
or something else.
From application programs then it depends on the error. If
you had a connection object and you application (not SQL)
failed then it would be up to your application to repair
the DB by sending it up a rollback.
If you application using the same connection as the begin
transaction sent some SQL that caused an error then yes it
would automatically roll back.
Anyway that it. I sugest you have a play on QA.
If you have any questions then don't hesitate to email me
on peternolan67REMOVETHIS@.hotmail.com (though I am a bit
busy tonight)
Peter
"You can always count on Americans to do the right thing -
after they've tried everything else."
Winston Churchill

>--Original Message--
>Can anybody point me to a good text about
>exception handling in triggers (errors and rollbacks) ?
>From 'Inside' :
>ROLLBACK (because of a fatal error or an
>explicit ROLLBACK command), the entire batch is aborted.
>
>As I read this :
>From within a trigger any FK or relational constraint
>violation results in a fatal error aborting the complete
>batch (and complete transaction).
>Is this correct ?
>What if there was no transaction start ?
>In the QA, does the QA supply a transaction
>(increase the transaction count) if no transaction
>was started ?
>When sending several statements, it appears
>that all statements are rolled back. (Are handled
>as one transaction).
>Does something similar happen when doing
>similar statements from application programs ?
>Thanks for your attention,
>ben brugman.
>
>.
>
My email is
stbraslenscap@.lenscaphiscom.nl
(Both lenscaps should be removed).
I tried to reply to your mail, but that bounced,
so now in the thread.
thanks in advance,
ben brugman
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:4ff601c49104$6e437d70$a301280a@.phx.gbl...[vbcol=seagreen]
> If we can get away from the trigger for a second.
> One of the least documented aspects on how transactions
> work are to do with the process id that its running from.
> So if you open up QA do transaction mock statement, open
> up another window in QA and do rollback you will get an
> error, why, becasue the process in the second window is
> different to the first.
> The reason why this is important is that a rollback as you
> found out will effect EVERY transaction in that process
> even though it maybe nested. Try this yourself, you will
> get some very interesting answers.
> So to get back to your question.
> SQL Server will automatically rollback transactions if the
> error is serious enough.
> If there is no transaction then there will be no
> transaction count, there is a handy little global variable
> called @.@.TRANCOUNT you may want to look at. So if that is
> set to anything but a 0 it will automatically rollback the
> transaction irrespective on whether it is done in trigger
> or something else.
> From application programs then it depends on the error. If
> you had a connection object and you application (not SQL)
> failed then it would be up to your application to repair
> the DB by sending it up a rollback.
> If you application using the same connection as the begin
> transaction sent some SQL that caused an error then yes it
> would automatically roll back.
> Anyway that it. I sugest you have a play on QA.
> If you have any questions then don't hesitate to email me
> on peternolan67REMOVETHIS@.hotmail.com (though I am a bit
> busy tonight)
> Peter
> "You can always count on Americans to do the right thing -
> after they've tried everything else."
> Winston Churchill
>
>

Exception handling in Triggers.

If we can get away from the trigger for a second.
One of the least documented aspects on how transactions
work are to do with the process id that its running from.
So if you open up QA do transaction mock statement, open
up another window in QA and do rollback you will get an
error, why, becasue the process in the second window is
different to the first.
The reason why this is important is that a rollback as you
found out will effect EVERY transaction in that process
even though it maybe nested. Try this yourself, you will
get some very interesting answers.
So to get back to your question.
SQL Server will automatically rollback transactions if the
error is serious enough.
If there is no transaction then there will be no
transaction count, there is a handy little global variable
called @.@.TRANCOUNT you may want to look at. So if that is
set to anything but a 0 it will automatically rollback the
transaction irrespective on whether it is done in trigger
or something else.
From application programs then it depends on the error. If
you had a connection object and you application (not SQL)
failed then it would be up to your application to repair
the DB by sending it up a rollback.
If you application using the same connection as the begin
transaction sent some SQL that caused an error then yes it
would automatically roll back.
Anyway that it. I sugest you have a play on QA.
If you have any questions then don't hesitate to email me
on peternolan67REMOVETHIS@.hotmail.com (though I am a bit
busy tonight)
Peter
"You can always count on Americans to do the right thing -
after they've tried everything else."
Winston Churchill

>--Original Message--
>Can anybody point me to a good text about
>exception handling in triggers (errors and rollbacks) ?
>From 'Inside' :
>ROLLBACK (because of a fatal error or an
>explicit ROLLBACK command), the entire batch is aborted.
>
>As I read this :
>From within a trigger any FK or relational constraint
>violation results in a fatal error aborting the complete
>batch (and complete transaction).
>Is this correct ?
>What if there was no transaction start ?
>In the QA, does the QA supply a transaction
>(increase the transaction count) if no transaction
>was started ?
>When sending several statements, it appears
>that all statements are rolled back. (Are handled
>as one transaction).
>Does something similar happen when doing
>similar statements from application programs ?
>Thanks for your attention,
>ben brugman.
>
>.
>My email is
stbraslenscap@.lenscaphiscom.nl
(Both lenscaps should be removed).
I tried to reply to your mail, but that bounced,
so now in the thread.
thanks in advance,
ben brugman
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:4ff601c49104$6e437d70$a301280a@.phx.gbl...[vbcol=seagreen]
> If we can get away from the trigger for a second.
> One of the least documented aspects on how transactions
> work are to do with the process id that its running from.
> So if you open up QA do transaction mock statement, open
> up another window in QA and do rollback you will get an
> error, why, becasue the process in the second window is
> different to the first.
> The reason why this is important is that a rollback as you
> found out will effect EVERY transaction in that process
> even though it maybe nested. Try this yourself, you will
> get some very interesting answers.
> So to get back to your question.
> SQL Server will automatically rollback transactions if the
> error is serious enough.
> If there is no transaction then there will be no
> transaction count, there is a handy little global variable
> called @.@.TRANCOUNT you may want to look at. So if that is
> set to anything but a 0 it will automatically rollback the
> transaction irrespective on whether it is done in trigger
> or something else.
> From application programs then it depends on the error. If
> you had a connection object and you application (not SQL)
> failed then it would be up to your application to repair
> the DB by sending it up a rollback.
> If you application using the same connection as the begin
> transaction sent some SQL that caused an error then yes it
> would automatically roll back.
> Anyway that it. I sugest you have a play on QA.
> If you have any questions then don't hesitate to email me
> on peternolan67REMOVETHIS@.hotmail.com (though I am a bit
> busy tonight)
> Peter
> "You can always count on Americans to do the right thing -
> after they've tried everything else."
> Winston Churchill
>
>
>

Excellent case/example

Not bad (again) -- to say the least.
I agree, the data in the example is simple and joining it as XML is
overkill, but your example has several finesses and techniques and is as suc
h
complete and ready to use for larger @.Left and @.Right nodes() that can be
joined (assuming they're @.LeftXPath and @.RightXPath nodes() have parallel
structures). The @.* variables refer to your last example.
This example is also interesting as it uses flexible dynamic SQL, has nifty
debugging, and can inspire variation.
This thread, i think is nearly finished, but of course - any comments
concerning performance consideration/optimisations are welcome. Or if any
misunderstanding hase cropt up in my coments here.
I am also including in the below a way to match elements from two comma
delimited strings. The example below just uses nvarchar. It is by far less
flexible than your example, but can be used where no injection detection is
needed and for more simple data.
CREATE FUNCTION [dbo].[IsNameInString]
(
@.LeftIems nvarchar(max),
@.RightItems nvarchar(max)
)
RETURNS bit
AS
BEGIN
DECLARE @.aLeftItemToCheck nvarchar(100),
@.aRightItem nvarchar(100),
@.TempList nvarchar(max),
@.Pos int,
@.aPos int,
@.Count int
SET @.LeftIems = LTRIM(RTRIM(@.LeftIems))+ ','
SET @.Pos = CHARINDEX(',', @.LeftIems, 1)
IF REPLACE(@.LeftIems, ',', '') <> ''
BEGIN
WHILE @.Pos > 0
BEGIN
SET @.aLeftItemToCheck = ''
SET @.aLeftItemToCheck = LTRIM(RTRIM(LEFT(@.LeftIems, @.Pos - 1)))
IF @.aLeftItemToCheck <> ''
BEGIN
Set @.TempList = ''
Set @.aPos = 0
SET @.TempList = LTRIM(RTRIM(@.RightItems))+ ','
SET @.aPos = CHARINDEX(',', @.TempList, 1)
IF REPLACE(@.TempList, ',', '') <> ''
BEGIN
WHILE @.aPos > 0
BEGIN
SET @.aRightItem = LTRIM(RTRIM(LEFT(@.TempList, @.aPos - 1)))
IF @.aRightItem <> ''
Begin
If @.aRightItem = @.aLeftItemToCheck
BEGIN
RETURN 1
End
End
SET @.TempList = RIGHT(@.TempList, LEN(@.TempList) - @.aPos)
SET @.aPos = CHARINDEX(',', @.TempList, 1)
END
END
END
SET @.LeftIems = RIGHT(@.LeftIems, LEN(@.LeftIems) - @.Pos)
SET @.Pos = CHARINDEX(',', @.LeftIems, 1)
END
END
RETURN 0
END
Regards,
Paul
"Marc Gravell" wrote:

> tidied, removed table-var, and added injection detection
> DROP PROC XmlJoin
> GO
> Create PROC XmlJoin
> (
> -- inputs
> @.Left xml, @.LeftXPath nvarchar(50),
> @.Right XML, @.RightXPath nvarchar(50) = NULL,
> -- outputs
> @.Result xml OUTPUT,
> -- xml options
> @.Path nvarchar(50) = 'node', @.Row nvarchar(50) = '', @.Root
> nvarchar(50) = 'xml',
> -- query options
> @.Distinct bit = 0,
> @.Debug bit = 0
> )
> AS
> BEGIN
> DECLARE @.Query nvarchar(max)
> -- verify inputs
> SET @.LeftXPath = ISNULL(@.LeftXPath, '')
> SELECT @.RightXPath = ISNULL(@.RightXPath, @.LeftXPath),
> @.Path = ISNULL(@.Path, N'node'),
> @.Row = ISNULL(@.Row, N''), @.Root = ISNULL(@.Root, N'')
> IF CHARINDEX(N'''', @.LeftXPath+@.RightXPath+@.Path+@.Row+@.Root)
> 0
> BEGIN
> RAISERROR ('Invalid character',16,1)
> RETURN
> END
> -- build command
> SET @.Query = N'
> SELECT @.Result = (
> SELECT ' + CASE @.Distinct WHEN 1 THEN N'DISTINCT ' ELSE N'' END
> + 'l.n.value(''.'',''varchar(20)'') AS [' + @.Path + N']
> FROM @.Left.nodes(''' + @.LeftXPath + N''') l(n)
> INNER JOIN @.Right.nodes(''' + @.RightXPath + N''') r(n)
> ON l.n.value(''.'',''varchar(20)'') =
> r.n.value(''.'',''varchar(20)'')
> FOR XML PATH(''' + @.Row + N'''), ROOT(''' + @.Root + N''')
> )'
> -- debug?
> IF @.Debug = 1
> BEGIN
> PRINT @.Query
> SELECT @.Left AS [@.Left], @.Right AS [@.Right]
> END
> -- exec
> EXEC sp_ExecuteSQL @.Query,
> N'@.Left xml, @.Right xml, @.Result xml OUTPUT',
> @.Left, @.Right, @.Result OUTPUT
> END
> GO
> SET NOCOUNT ON
> DECLARE @.Result xml
> EXEC XmlJoin
> @.Left =
> '<roles><role>N12</role><role>N12</role><role>N13</role><role>S39</role><r
ole>S14</role></roles>',
> @.LeftXPath = 'roles/role',
> @.Right = '<roles><role>N12</role><role>S39</role></roles>',
> @.Result = @.Result OUTPUT,
> @.Path = 'role', @.Root = 'roles', @.Debug = 1
> SELECT @.Result
>
>Regarding performance of the xml code; I guess there are two aspects
here... sp_ExecuteSQL benefits in that it uses the query cache, so any
uses of the same xpaths (with different xml) have the chance to share
a query plan to reduce recompilation. Of course, xml performance is a
black art, and the usage of value() pretty-much precludes a lot of
indexing - but as a general-purpose utility it does the job.
Performance-critical code should run against tables (not variables)
which can have XML indexing applied; and queries should be moved
*inside* the xml query (via sql:parameter) so that the xml indexing
can be used - but that isn't what we are discussing... just an aside.
The CSV stuff is interesting; the parsing approach that you've used
will probably work, but I'd be rather tempted to cobble together a
table function (UDF) that splits a /single/ delimted string, and call
it twice (perhaps into @.tables or #tables, or perhaps just "as is") -
this will let SQL Server do what it does best: set based operations.
Besides which, a simple "split this string" is so useful for day-to-
day operations that such a function should exist in every database; it
also demonstrates a level of "do one thing well; then join 'em
together" re-use.
Actually, it *really* annoys me that MS haven't added an optimised
method for delimited string splitting, rather than having to write it
in TSQL which simply isn't ideal for the job; I know you can call CLR
code in 2005/2008, but I doubt that it is worth the overhead of
loading the runtime... I've resisted CLR/SQL code so far, and it just
seems so... unnecessary!
Example code for a join (once you have the UDF written - apols for the
name; not my choice ;-p)
DECLARE @.Left varchar(max), @.Right varchar(max)
SELECT @.Left = 'a,b,d,c,g,h',
@.Right = 'h,b,e,b'
SELECT l.Value
FROM SplitMaxArray_udf(@.Left,',',1,1) l
INNER JOIN SplitMaxArray_udf(@.Right,',',1,1) r
ON r.Value = l.Value
or if you just want to check for a single match you could use EXISTS.
You can get UDF TSQL for CSV->table easily enough on t'net, so I won't
bother posting that unless prompted.
Marc|||> Actually, it *really* annoys me...
*especially* since you can now pass xml down to the database and
parse /that/ easier than anything... but delimited values, nope...
DIY.
Sorry; </rant>
Marc|||It might look something like this:
DECLARE @.Left varchar(max), @.Right varchar(max)
SELECT @.Left = 'a,b,h,h,d,c,g,h',
@.Right = 'h,b,e,b'
declare @.csv varchar(max)
select @.csv = coalesce(@.csv + ',' , '') +
l.aValue
FROM SplitMaxArray_udf(@.Left,',',1) l
INNER JOIN
(select distinct r.aValue
from SplitMaxArray_udf(@.Right,',',1) r) ro
ON ro.aValue = l.aValue
select @.csv
For the SplitMaxArray_udf UDF, it might go something like this:
IF OBJECT_ID (N'dbo.SplitMaxArray_udf') IS NOT NULL
BEGIN
DROP FUNCTION dbo.SplitMaxArray_udf
END
GO
CREATE FUNCTION SplitMaxArray_udf
(
-- Add the parameters for the function here
@.Array nvarchar(1000),
@.Separator char(1)= ',',
@.Debug bit = 0
)
RETURNS @.ValueTable table
(
-- Add the column definitions for the TABLE variable here
aValue nvarchar(50)
)
AS
BEGIN
IF(@.Separator is NULL or @.Separator = '')
BEGIN
set @.Separator = ','
END
declare @.separator_position int
declare @.array_value varchar(1000)
set @.Array = @.Array + @.Separator
declare @.percentageSep char(3)
set @.percentageSep = '%' + @.Separator + '%'
while patindex(@.percentageSep , @.Array) <> 0
begin
select @.separator_position = patindex(@.percentageSep , @.Array)
select @.array_value = left(@.array, @.separator_position - 1)
Insert @.ValueTable
Values (@.array_value)
select @.array = stuff(@.Array, 1, @.separator_position, '')
end
RETURN
END
GO
Regards,
Paul
"Marc Gravell" wrote:

> Regarding performance of the xml code; I guess there are two aspects
> here... sp_ExecuteSQL benefits in that it uses the query cache, so any
> uses of the same xpaths (with different xml) have the chance to share
> a query plan to reduce recompilation. Of course, xml performance is a
> black art, and the usage of value() pretty-much precludes a lot of
> indexing - but as a general-purpose utility it does the job.
> Performance-critical code should run against tables (not variables)
> which can have XML indexing applied; and queries should be moved
> *inside* the xml query (via sql:parameter) so that the xml indexing
> can be used - but that isn't what we are discussing... just an aside.
> The CSV stuff is interesting; the parsing approach that you've used
> will probably work, but I'd be rather tempted to cobble together a
> table function (UDF) that splits a /single/ delimted string, and call
> it twice (perhaps into @.tables or #tables, or perhaps just "as is") -
> this will let SQL Server do what it does best: set based operations.
> Besides which, a simple "split this string" is so useful for day-to-
> day operations that such a function should exist in every database; it
> also demonstrates a level of "do one thing well; then join 'em
> together" re-use.
> Actually, it *really* annoys me that MS haven't added an optimised
> method for delimited string splitting, rather than having to write it
> in TSQL which simply isn't ideal for the job; I know you can call CLR
> code in 2005/2008, but I doubt that it is worth the overhead of
> loading the runtime... I've resisted CLR/SQL code so far, and it just
> seems so... unnecessary!
> Example code for a join (once you have the UDF written - apols for the
> name; not my choice ;-p)
> DECLARE @.Left varchar(max), @.Right varchar(max)
> SELECT @.Left = 'a,b,d,c,g,h',
> @.Right = 'h,b,e,b'
> SELECT l.Value
> FROM SplitMaxArray_udf(@.Left,',',1,1) l
> INNER JOIN SplitMaxArray_udf(@.Right,',',1,1) r
> ON r.Value = l.Value
> or if you just want to check for a single match you could use EXISTS.
> You can get UDF TSQL for CSV->table easily enough on t'net, so I won't
> bother posting that unless prompted.
> Marc
>|||Please allow me to connect to two things i mentioned in previous mails to
this thread, and it has to do with XML/XPath context, and change friendly
XPath through dynamic SQL.
By doing an SQL inner join you essentially where able to join XML from two
separate XML contexts.
I had difficulty achieving this with FLOWR using SQL Server. For example
(something like the below)
for $LeftItem doc(@.Left)/roles,
for $RightItem doc(@.Right)/roles
where $LeftItem/[role = $RightItem/child::*]
return $LeftItem/role
This would have been fine, but SQL Server doesn't support the doc()
function.
My question here, would be, I guess, how otherwise to (currently with SQL
Server) to connect/compare/join two XML contexts?
Another avenue i thought of before I saw your inner join was to concantenate
the two xmls that i would like to compare, and then access the "two" parts
through two separate Xpaths. By concantenating the two XMLs (@.Left and
@.Right) i would (subsequently) only need to use one XML context.
The next thing is concerning the dynmaic SQL and the need to dynamically set
the Xpath. One reason for this is because the XML datamodel (in the databas
e
XML column) might and probably will change. By being able to set the Xpath
dynmically we are better (there is no guarantee with data models) better to
meet change much more flexibly.
I needed to write the above to things, as i think, they (OK, - only
partially) answer the "why" question to this thread.
--
Regards,
Paul
"Marc Gravell" wrote:

> *especially* since you can now pass xml down to the database and
> parse /that/ easier than anything... but delimited values, nope...
> DIY.
> Sorry; </rant>
> Marc
>