SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Out of memory error when running stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

deonvisser
Starting Member

South Africa
16 Posts

Posted - 05/12/2014 :  22:45:21  Show Profile  Reply with Quote
Hi
I am stepping through table row by row and processing data. I am not using cursors for this process. Everything seems to be working fine and then I run out of memory.

What I am trying to do:
A column in a table contains values 013|014|015|016 etc. Each of these values separated by | should be in its own row and not in on row as I depicted. So I wrote some code to pull each number out row by row and write them as separate rows back to the table. The logic works 100% except that it bombs out with a memory error. I seems that memory is not being released after a statement is executed.

This is an example of the data
VoIS Local All Of RSA Virtual Card Acquiring no 0.28 Monday Friday 00:00 06:59 0101250|0121250|0211250|0311250|0411250|0431250|0511250

The procedure will work fine on a few lines but will bomb on thirty line.

This is the code that I run.....

-- THIS IS THE CONVERSION PROCESS TO REMOVE ALL THE | FROM THE DATA AN CREATE SEPERATE ROWS FOR EACH DIAL PREFIX
WHILE EXISTS (SELECT * FROM tbl_MitsolRate)

BEGIN
-- CHECK IF THE RATE TABLE ROW CONTAINS |
SELECT TOP(1)[Service Type], Origin, Destination, [Rate Per Minute], [Minimum Charge], [Time Based Rate], [Time Based From Day], [Time Based To Day],
[Time Based From Time], [Time Based To Time], [Destination Prefix List] FROM tbl_MitsolRate
IF @@ROWCOUNT > 0
BEGIN
INSERT INTO tbl_Temp_MitsolRate ([Service Type], Origin, Destination, [Rate Per Minute], [Minimum Charge], [Time Based Rate], [Time Based From Day], [Time Based To Day],
[Time Based From Time], [Time Based To Time], [Destination Prefix List])
SELECT TOP(1) [Service Type], Origin, Destination, [Rate Per Minute], [Minimum Charge], [Time Based Rate], [Time Based From Day], [Time Based To Day],
[Time Based From Time], [Time Based To Time], [Destination Prefix List] FROM tbl_MitsolRate
DELETE TOP(1) FROM tbl_MitsolRate

-- CHECK IF THE TEMP TABLE ROW CONTAINS |
WHILE EXISTS (SELECT * FROM tbl_Temp_MitsolRate)
BEGIN
SELECT [Service Type], Origin, Destination, [Rate Per Minute], [Minimum Charge], [Time Based Rate], [Time Based From Day], [Time Based To Day],
[Time Based From Time], [Time Based To Time], RTRIM(SubString([Destination Prefix List], 1, CharIndex('|', [Destination Prefix List])-1)) FROM qry_Mitsol_Temp
IF @@ROWCOUNT > 0
BEGIN
INSERT INTO tbl_New_MitsolRate ([Service Type], Origin, Destination, [Rate Per Minute], [Minimum Charge], [Time Based Rate], [Time Based From Day], [Time Based To Day],
[Time Based From Time], [Time Based To Time], [Destination Prefix List])
SELECT [Service Type], Origin, Destination, [Rate Per Minute], [Minimum Charge], [Time Based Rate], [Time Based From Day], [Time Based To Day],
[Time Based From Time], [Time Based To Time], RTRIM(SubString([Destination Prefix List], 1, CharIndex('|', [Destination Prefix List])-1)) FROM qry_Mitsol_Temp

UPDATE tbl_Temp_MitsolRate SET [Destination Prefix List] = RTRIM(SubString([Destination Prefix List], CharIndex('|', [Destination Prefix List])+ 1, LEN([Destination Prefix List])))
END
ELSE
BEGIN
INSERT INTO tbl_New_MitsolRate ([Service Type], Origin, Destination, [Rate Per Minute], [Minimum Charge], [Time Based Rate], [Time Based From Day], [Time Based To Day],
[Time Based From Time], [Time Based To Time], [Destination Prefix List])
SELECT [Service Type], Origin, Destination, [Rate Per Minute], [Minimum Charge], [Time Based Rate], [Time Based From Day], [Time Based To Day],
[Time Based From Time], [Time Based To Time], [Destination Prefix List] FROM qry_Mitsol_Temp1
DELETE FROM tbl_Temp_MitsolRate
END
END
END


Please can someone help. There may be a better way to code this?

nagino
Yak Posting Veteran

Japan
65 Posts

Posted - 05/13/2014 :  01:23:50  Show Profile  Reply with Quote
WITH WORK (
	[Service Type], Origin, Destination, [Rate Per Minute], [Minimum Charge], [Time Based Rate], [Time Based From Day], [Time Based To Day], 
	[Time Based From Time], [Time Based To Time], [Destination Prefix List], flg) AS (
SELECT
	[Service Type], Origin, Destination, [Rate Per Minute], [Minimum Charge], [Time Based Rate], [Time Based From Day], [Time Based To Day], 
	[Time Based From Time], [Time Based To Time], [Destination Prefix List], CHARINDEX('|', [Destination Prefix List])
FROM @tbl_MitsolRate
UNION ALL
SELECT
	[Service Type], Origin, Destination, [Rate Per Minute], [Minimum Charge], [Time Based Rate], [Time Based From Day], [Time Based To Day], 
	[Time Based From Time], [Time Based To Time], LEFT([Destination Prefix List], CHARINDEX('|', [Destination Prefix List]) - 1), 0
FROM Work WHERE 0 < flg
UNION ALL
SELECT
	[Service Type], Origin, Destination, [Rate Per Minute], [Minimum Charge], [Time Based Rate], [Time Based From Day], [Time Based To Day], 
	[Time Based From Time], [Time Based To Time], SUBSTRING([Destination Prefix List], CHARINDEX('|', [Destination Prefix List]) + 1, LEN([Destination Prefix List])), CHARINDEX('|', SUBSTRING([Destination Prefix List], CHARINDEX('|', [Destination Prefix List]) + 1, LEN([Destination Prefix List])))
FROM Work WHERE 0 < flg
)
INSERT INTO tbl_New_MitsolRate
SELECT
	[Service Type], Origin, Destination, [Rate Per Minute], [Minimum Charge], [Time Based Rate], [Time Based From Day], [Time Based To Day], 
	[Time Based From Time], [Time Based To Time], [Destination Prefix List]
FROM WORK WHERE flg = 0


-------------------------------------
From Japan
Sorry, my English ability is limited.
Go to Top of Page

deonvisser
Starting Member

South Africa
16 Posts

Posted - 05/13/2014 :  06:22:56  Show Profile  Reply with Quote
Thank you for your reply nagino.
Unfortunately I cannot get it to work. Lots of errors.
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
403 Posts

Posted - 05/13/2014 :  06:31:03  Show Profile  Reply with Quote
one way to split :

declare @vcString as varchar(500)
declare @xmlVar as xml
SET @vcString ='0101250|0121250|0211250|0311250|0411250|0431250|0511250'

SET @vcString = replace(@vcString,'|','</Row><Row>')
--print @vcString

SET @vcString='<Rows><Row>' + @vcString + '</Row></Rows>'

SET @xmlVar=CAST(@vcString as XML)
--print @vcString

SELECT
	t.u.value('.','varchar(50)')
FROM	
	@xmlVar.nodes('Rows/Row')t(u)



sabinWeb MCP
Go to Top of Page

Ifor
Aged Yak Warrior

574 Posts

Posted - 05/13/2014 :  07:25:25  Show Profile  Reply with Quote
It is normal to split delimited strings with a number/tally table (google this)
As you have not provided data, or even said what the primary key is, this is difficult to show and test.
Something like the following should work if you replace <tbl_MitsolRatePK> with the actual primary key.
With no test data, the details are up to you.

WITH N1(N) AS (SELECT N FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d(N))
,N2(N) AS (SELECT 1 FROM N1 a, N1 b)
,N4(N) AS (SELECT 1 FROM N2 a, N2 b) 
,N(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM N4)
--select * from N
,DestSplit1
AS
(
	SELECT <tbl_MitsolRatePK>
		,SUBSTRING('|' + [Destination Prefix List], N+1, 255) AS Dest
	FROM tbl_MitsolRate
                CROSS JOIN N
	WHERE SUBSTRING('|' + [Destination Prefix List], N.N, 1) = '|'
)
,DestSplit
AS
(
	SELECT <tbl_MitsolRatePK>
		,CASE
			WHEN CHARINDEX('|', Dest) > 0
			THEN LEFT(Dest, CHARINDEX('|', Dest) -1)
			ELSE Dest
		END AS Dest
	FROM DestSplit1
)
INSERT INTO tbl_New_MitsolRate ([Service Type], Origin, Destination, [Rate Per Minute], [Minimum Charge], [Time Based Rate], [Time Based From Day], [Time Based To Day], 
[Time Based From Time], [Time Based To Time], [Destination Prefix List])
SELECT S.[Service Type], S.Origin, S.Destination, S.[Rate Per Minute], S.[Minimum Charge], S.[Time Based Rate], S.[Time Based From Day]
,S.[Time Based To Day], S.[Time Based From Time], S.[Time Based To Time], D.Dest
FROM tbl_MitsolRate S
	JOIN DestSplit D
		ON S.<tbl_MitsolRatePK> = D.<tbl_MitsolRatePK>;


Edited by - Ifor on 05/13/2014 07:27:35
Go to Top of Page

deonvisser
Starting Member

South Africa
16 Posts

Posted - 05/13/2014 :  16:12:12  Show Profile  Reply with Quote
Hi All

Thanks for your replies. Ifor, this is an example of the data...

SERVICE ORIGIN DESTINATION DIAL PREFIX
VoIS Local All Of RSA Virtual Card Acquiring 0101250|0121250|0211250
VoIS National All Of RSA Virtual Card Acquiring 0103250|01271250|02150|03113
VoIS Int All Of RSA Virtual Card Acquiring 01016650|0121250

As you can see the table does not contain a primary key

The result I need is this

SERVICE ORIGIN DESTINATION DIAL PREFIX
VoIS Local All Of RSA Virtual Card Acquiring 0101250
VoIS Local All Of RSA Virtual Card Acquiring 0121250
VoIS Local All Of RSA Virtual Card Acquiring 0211250
VoIS National All Of RSA Virtual Card Acquiring 0103250
VoIS National All Of RSA Virtual Card Acquiring 01271250
VoIS National All Of RSA Virtual Card Acquiring 02150
VoIS National All Of RSA Virtual Card Acquiring 03113
VoIS Int All Of RSA Virtual Card Acquiring 01016650
VoIS Int All Of RSA Virtual Card Acquiring 0121250

I hope this makes sense?

Let me know if you need more info
Thank you for your assistance
Go to Top of Page

deonvisser
Starting Member

South Africa
16 Posts

Posted - 05/13/2014 :  16:17:51  Show Profile  Reply with Quote
Hmm, the Forum does not maintain my formatting. Basically

SERVICE ORIGIN DESTINATION DIAL PREFIX are the Col Names

As you can see, only the last col that contains the [DIAL PREFIX] has multiple Prefixes separated by |

Each of these prefixes must be split into new rows reatining the data of the other columns (SERVICE ORIGIN DESTINATION)
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 05/13/2014 :  16:46:08  Show Profile  Reply with Quote
quote:
Hmm, the Forum does not maintain my formatting

use code tags around formatted code to maintain the formatting:

[code]
formatted code
[/code]

read the faq for more options.


Be One with the Optimizer
TG
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 05/13/2014 :  17:40:17  Show Profile  Reply with Quote
quote:
Originally posted by deonvisser

Basically

SERVICE ORIGIN DESTINATION DIAL PREFIX are the Col Names

As you can see, only the last col that contains the [DIAL PREFIX] has multiple Prefixes separated by |

Each of these prefixes must be split into new rows reatining the data of the other columns (SERVICE ORIGIN DESTINATION)


This looks very simple and requires no looping at all. The final query would look something like this:

select yt.SERVICE
       , yt.ORIGIN
       , yt.DESTINATION
       , ca.outputCol as [prefix]
from   yourTable as yt
cross apply dbo.fn_split([DIAL PREFIX], '|') ca

dbo.fn_split() could be any "split function" Search for split functions or parse CSV for many options.

EDIT:
this is an article by Jeff Moden who thoroughly researches whatever he's writing about so I have no doubt that what he suggests will work fast and well. Although I have to admit I haven't done more than glance through this article yet.

Be One with the Optimizer
TG

Edited by - TG on 05/13/2014 17:46:01
Go to Top of Page

deonvisser
Starting Member

South Africa
16 Posts

Posted - 05/14/2014 :  02:37:52  Show Profile  Reply with Quote
Thank you for all your replies.

TG, this is what I have done


select [Service Type], Origin, Destination, [Rate Per Minute], [Minimum Charge], [Time Based Rate], [Time Based From Day], [Time Based To Day], 
	[Time Based From Time], [Time Based To Time], [Destination Prefix List], ca.outputCol as [prefix]
from   tbl_MitsolRate as yt
cross apply dbo.split([Destination Prefix List], '|') ca


CREATE FUNCTION [dbo].[SPLIT] 
   (  @DELIMITER VARCHAR(5), 
      @LIST      VARCHAR(MAX) 
   ) 
   RETURNS @TABLEOFVALUES TABLE 
      (  ROWID   SMALLINT IDENTITY(1,1), 
         [VALUE] VARCHAR(MAX) 
      ) 
AS 
   BEGIN
    
      DECLARE @LENSTRING INT 
 
      WHILE LEN( @LIST ) > 0 
         BEGIN 
         
            SELECT @LENSTRING = 
               (CASE CHARINDEX( @DELIMITER, @LIST ) 
                   WHEN 0 THEN LEN( @LIST ) 
                   ELSE ( CHARINDEX( @DELIMITER, @LIST ) -1 )
                END
               ) 
                                
            INSERT INTO @TABLEOFVALUES 
               SELECT SUBSTRING( @LIST, 1, @LENSTRING )
                
            SELECT @LIST = 
               (CASE ( LEN( @LIST ) - @LENSTRING ) 
                   WHEN 0 THEN '' 
                   ELSE RIGHT( @LIST, LEN( @LIST ) - @LENSTRING - 1 ) 
                END
               ) 
         END
          
      RETURN 
      
   END 


However it does not like the output col it fails.

Go to Top of Page

deonvisser
Starting Member

South Africa
16 Posts

Posted - 05/14/2014 :  04:21:35  Show Profile  Reply with Quote
Hi Guys

Anyone out there that can help. I am desperate to get this working and will even pay to get it working.

Thanks
Go to Top of Page

deonvisser
Starting Member

South Africa
16 Posts

Posted - 05/14/2014 :  05:46:29  Show Profile  Reply with Quote
Not to worry, I found something that did the job perfectly....


create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
returns @temptable TABLE (items varchar(MAX))       
as       
begin      
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return 
end;

INSERT INTO tbl_mitsolrate_new ([Service Type], Origin, Destination, [Rate Per Minute], [Minimum Charge], [Time Based Rate], [Time Based From Day], [Time Based To Day], 
                      [Time Based From Time], [Time Based To Time], [Destination Prefix List])
select t1.[service type], t1.Origin, t1.Destination, t1.[Rate Per Minute], t1.[Minimum Charge], t1.[Time Based Rate], t1.[Time Based From Day], t1.[Time Based To Day], 
                      t1.[Time Based From Time], t1.[Time Based To Time], s.items [Destination Prefix List]
from tbl_mitsolrate t1
outer apply dbo.split(t1.[Destination Prefix List], '|') s


Thank you for all your input. Much appreciated.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000