Solved

Help with PL/SQL to T-SQL conversion.

Posted on 2004-09-24
36
1,265 Views
Last Modified: 2010-07-27
Hi Guys, I need some in SQL conversion from Oracle to SQL Server...Here is the procedure in T-SQL..When I run the below SQL in SQL Server, it is going in infinite loop. When I click stop, I am getting the error .......
"Invalid length parameter passed to the substring function."
at the following line
SELECT @RoleID_in = CONVERT(NUMERIC(8, 2), SUBSTRING(@UserRoles_in, 1, CHARINDEX(',', @UserRoles_in) - 1))
-------------
DECLARE @objid_in INT
DECLARE @objclass_in INT
DECLARE @userid_in INT
DECLARE @userRoles_in VARCHAR(3000)
DECLARE @RoleID_in INT
DECLARE @cnt INT

DECLARE csr CURSOR FOR
SELECT * FROM objectACL
OPEN csr
WHILE (0 = 0)
BEGIN --(

fetch NEXT FROM csr INTO @objid_in, @objclass_in, @userid_in, @userRoles_in
IF (@@FETCH_STATUS = -1)
BREAK
SELECT @UserRoles_in = SUBSTRING(@UserRoles_in, 2, LEN(@UserRoles_in))
WHILE (0 = 0)
BEGIN --(
SELECT @RoleID_in = CONVERT(NUMERIC(8, 2), SUBSTRING(@UserRoles_in, 1, CHARINDEX(',', @UserRoles_in) - 1))
SELECT @cnt = COUNT(*) FROM nodetable WHERE objtype = 21 AND id = @RoleId_in
IF ( @cnt = 0 )
BEGIN
INSERT INTO error_report VALUES( 'ObjectACL' , '0' , 'UserRoles refering to Non-existing Role : ' + CAST(@RoleID_in AS VARCHAR) )
END
SELECT @UserRoles_in = SUBSTRING(@UserRoles_in, LEN(@RoleID_in) + 2, LEN(@UserRoles_in))
IF ( @UserRoles_in is null )
BEGIN
BREAK
END
END --)
END --)
close csr
DEALLOCATE csr
GO
------------------

Corresponding procedure in Oracle
---------------
declare
cursor csr is select * from objectACL;

objid_in number;
objclass_in number;
userid_in number;
userRoles_in varchar2(3000);
RoleID_in number;
cnt number;

begin
open csr;
loop
fetch csr into objid_in, objclass_in, userid_in, userRoles_in;
exit when csr%notfound;

UserRoles_in := substr(UserRoles_in, 2);

loop
RoleID_in := to_number(substr(UserRoles_in, 1, instr(UserRoles_in, ',')-1));
select count(1) into cnt from nodetable where objtype=21 and id=RoleId_in;
if (cnt =0) then
insert into error_report values ('ObjectACL', '0', 'UserRoles refering to Non-existing Role : '||RoleId_in);
end if;

UserRoles_in := substr(userRoles_in, length(RoleId_in)+2);

if (userRoles_in is null) then
exit;
end if;
end loop;
end loop;
close csr;
end;
/
-------------------
0
Comment
Question by:kingno1
  • 14
  • 9
  • 8
  • +1
36 Comments
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 12148192
SELECT @RoleID_in = CONVERT(NUMERIC(8, 2), SUBSTRING(@UserRoles_in, 1, CHARINDEX(',', @UserRoles_in) - 1))

If there is no match returned from the charindex, then your length would be 0-1 or -1, which is an invalid length. Thats my guess.

If it is, you can fix it easily with the abs function

SELECT @RoleID_in = CONVERT(NUMERIC(8, 2), SUBSTRING(@UserRoles_in, 1, abs(CHARINDEX(',', @UserRoles_in)) - 1))

If that doesnt fix it, I'd need to really see the data to know why its failing.
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 12148198
Wait, ignore that solution, since it wouldnt return waht you want. Try

SELECT @RoleID_in = CONVERT(NUMERIC(8, 2), SUBSTRING(@UserRoles_in, 1, case when (CHARINDEX(',', @UserRoles_in)> 1 - 1)>0 then  CHARINDEX(',', @UserRoles_in)> 1 - 1) else len(@userRoles_in) end)
0
 

Author Comment

by:kingno1
ID: 12148242
Hi, Thanks..
I get the error...
Server: Msg 170, Level 15, State 1, Line 22
Line 22: Incorrect syntax near '>'.
Server: Msg 156, Level 15, State 1, Line 36
Incorrect syntax near the keyword 'END'.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Expert Comment

by:mcp111
ID: 12148277
SELECT @RoleID_in = CONVERT(NUMERIC(8, 2), SUBSTRING(@UserRoles_in, 1, case when (CHARINDEX(',', @UserRoles_in - 1)>0 then  CHARINDEX(',', @UserRoles_in - 1) else len(@userRoles_in) end)
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 12148283
sorry, typo when I was cutting and pasting. hopefuly the other one works.

I dont know pl/sql to now if it uses cases, but if not and you want a quick explanation, let me know.
0
 

Author Comment

by:kingno1
ID: 12148321
Hi, I used the above syntax and got the error after I remove end at the end
----------
Server: Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'then'.
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 12148331
Let me check the brackets out.....okay, this should work

SELECT @RoleID_in = CONVERT(NUMERIC(8, 2), SUBSTRING(@UserRoles_in, 1, case when CHARINDEX(',', @UserRoles_in - 1)>0 then  CHARINDEX(',', @UserRoles_in - 1) else len(@userRoles_in) end)




0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 12148338
There was one extra bracket at the beginning.

Case statements are a bit like if then else for inline use. The format is

CASE
WHEN condition THEN value
WHEN condition THEN value
ELSE value
END

You dont need an else, but you do need the end. The problem in teh statemetn above wasnt with the end, it was the bracket before charindex that wasnt ended before the > symbol it didnt know what to do with the end or the then or anything else.
0
 

Author Comment

by:kingno1
ID: 12148351
Hmm, Looks like the stament is correct but, there is some syntax error..
CHRIS, can you please remove that extra bracket and post the statement if you dont mind..
Thanks a lot again.
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 12148368
SELECT @RoleID_in = CONVERT(NUMERIC(8, 2), SUBSTRING(@UserRoles_in, 1, case when (CHARINDEX(',', @UserRoles_in) - 1)>0 then  (CHARINDEX(',', @UserRoles_in) - 1) else len(@userRoles_in) end))


Tested in sql, all syntax errors are gone
0
 

Author Comment

by:kingno1
ID: 12148397
Awesome, the error is gone..After executing my SQL, it returned the error..
------------
Server: Msg 8114, Level 16, State 5, Line 20
Error converting data type varchar to numeric.
Here is my DDL..Any pointers please?
-------------
CREATE TABLE [dbo].[OBJECTACL] (
      [OBJID] [numeric](28, 0) NOT NULL ,
      [OBJCLASS] [numeric](28, 0) NOT NULL ,
      [USERID] [numeric](28, 0) NOT NULL ,
      [USERROLES] [varchar] (2000)  NULL
) ON [PRIMARY]
GO
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 12148405
The problem is because of data in our userroles field.

You're convert with the new case statement says to (in english)

take all characters up to the first comma (all characters if there is no comma) and convert them to a numeric value

This will work if your data supports it, for example the following values in userroles would work

1234,asefjs
1234556
1234,4565l
123.4,sedfwet

however these values would give you the error

123f,5
1234t
abvcd

Are there any rules for the format of the user roles column?
Will there always be a comma (we know there isnt or you wouldnt have had your first error)
Will the substring always return a numeric only string? Nope again.

If you need to, I can give you code to check to see if the value returned from the substring is numeric before you run the convert. What you need to do is specify what value you want returned if the substring isnt an acceptible value.

0
 

Author Comment

by:kingno1
ID: 12148423
All the values are
,9010,
,9010,9011,
However I see some NULL values too. There are no alphabets though.
0
 
LVL 7

Accepted Solution

by:
ChrisFretwell earned 125 total points
ID: 12148445
cant really see the null being a problem, but just in case.....let me think.....


SELECT @RoleID_in = case when @userroles_in is null then 0 else CONVERT(NUMERIC(8, 2), SUBSTRING(@UserRoles_in, 1, case when (CHARINDEX(',', @UserRoles_in) - 1)>0 then  (CHARINDEX(',', @UserRoles_in) - 1) else len(@userRoles_in) end)) end
0
 

Author Comment

by:kingno1
ID: 12148485
Hmm, Same error..
I dont see any other data except that.
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 12148529
Its data. its the only ting it can be. oh, if any of your numbers are more than can fit into numeric(8,2)?
0
 

Author Comment

by:kingno1
ID: 12148536
Can I send you my OBJECTACL and NODETABLE that were involved in this procedure?
If I can, what will be the email address.
Thanks a lot again.
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12148569
why don't you just do a convert to int instead of numeric(8,2)
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12148594
I looked at your entire code again.
You can just declare @role_id_in as varchar(20) instead of int.
Then you don't need the convert(numeric(8,2))
0
 

Author Comment

by:kingno1
ID: 12148607
Hi, I tried withconvert to  INT as CHRIS suggested and VARCHAR(20) as mcp111 suggested
Both are running but for a long time, after 7 minutes of run, I stopped the execution and it showed a lot of rows affected in the log.
Looks like it is going in a loop...
MCP, as you suggested, can you convert that that select statement without CONVERT if I declare roleid_in as varchar(20).
Thanks
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12148612
Yes, it would just be

SELECT @RoleID_in = SUBSTRING(@UserRoles_in, 1, case when (CHARINDEX(',', @UserRoles_in) - 1)>0 then  (CHARINDEX(',', @UserRoles_in) - 1) else len(@userRoles_in) end)
0
 

Author Comment

by:kingno1
ID: 12148618
Got the same error, converting data type to numeric at the very next statement..
SELECT @cnt  =  COUNT(*) FROM  nodetable WHERE       objtype  = 21 AND id  = @RoleId_in
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12148627
That means you have some non-numeric data
try this
if isnumeric( @RoleId_in)
    SELECT @cnt  =  COUNT(*) FROM  nodetable WHERE      objtype  = 21 AND id  = @RoleId_in

0
 

Author Comment

by:kingno1
ID: 12148639
Server: Msg 156, Level 15, State 1, Line 25
Incorrect syntax near the keyword 'SELECT'.
-----------------------------

WHILE (0 = 0)
            BEGIN --(
SELECT @RoleID_in = SUBSTRING(@UserRoles_in, 1, case when (CHARINDEX(',', @UserRoles_in) - 1)>0 then  (CHARINDEX(',', @UserRoles_in) - 1) else len(@userRoles_in) end)

                  --SELECT @RoleID_in  = CONVERT(NUMERIC(8, 2), SUBSTRING(@UserRoles_in, 1, CHARINDEX(',', @UserRoles_in) - 1))
                  
if isnumeric( @RoleId_in)
    SELECT @cnt  =  COUNT(*) FROM  nodetable WHERE      objtype  = 21 AND id  = @RoleId_in
0
 
LVL 9

Expert Comment

by:miron
ID: 12148658
-- replace
if (userRoles_in is null) then exit

-- with
if ( @UserRoles_in IS NULL OR rtrim(ltrim(@UserRoles_in)) = '' ) break

-- cheers
0
 

Author Comment

by:kingno1
ID: 12148668
Hi, Here is my whole procedure as of now..Can you please tell me where to replace?
---------------------
DECLARE @objid_in                               INT
DECLARE @objclass_in                              INT
DECLARE @userid_in                                INT
DECLARE @userRoles_in                             VARCHAR(3000)
DECLARE @RoleID_in                                varchar(20)
DECLARE @cnt                                      INT
      
DECLARE csr CURSOR FOR
      SELECT * FROM  objectACL
      OPEN csr
      WHILE (0 = 0)
      BEGIN --(
            
            fetch NEXT FROM csr INTO @objid_in, @objclass_in, @userid_in, @userRoles_in
            IF (@@FETCH_STATUS = -1)
            BREAK
            SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, 2, LEN(@UserRoles_in))
            WHILE (0 = 0)
            BEGIN --(
SELECT @RoleID_in = SUBSTRING(@UserRoles_in, 1, case when (CHARINDEX(',', @UserRoles_in) - 1)>0 then  (CHARINDEX(',', @UserRoles_in) - 1) else len(@userRoles_in) end)
if isnumeric( @RoleId_in)
    SELECT @cnt  =  COUNT(*) FROM  nodetable WHERE      objtype  = 21 AND id  = @RoleId_in

                  IF ( @cnt = 0 )
                  BEGIN
                        INSERT INTO  error_report VALUES( 'ObjectACL' , '0' , 'UserRoles refering to Non-existing Role : ' + CAST(@RoleID_in AS VARCHAR) )  
                  END
                     SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, LEN(@RoleID_in) + 2, LEN(@UserRoles_in))
                  IF ( @UserRoles_in is null )
                  BEGIN
                        BREAK
                  END
               END --)
      END --)
      close csr
DEALLOCATE csr
GO
0
 
LVL 9

Expert Comment

by:miron
ID: 12148675
DECLARE @objid_in                               INT
DECLARE @objclass_in                              INT
DECLARE @userid_in                                INT
DECLARE @userRoles_in                             VARCHAR(3000)
DECLARE @RoleID_in                                varchar(20)
DECLARE @cnt                                      INT
     
DECLARE csr CURSOR FOR
     SELECT * FROM  objectACL
     OPEN csr
     WHILE (0 = 0)
     BEGIN --(
         
          fetch NEXT FROM csr INTO @objid_in, @objclass_in, @userid_in, @userRoles_in
          IF (@@FETCH_STATUS = -1)
          BREAK
          SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, 2, LEN(@UserRoles_in))
          WHILE (0 = 0)
          BEGIN --(
SELECT @RoleID_in = SUBSTRING(@UserRoles_in, 1, case when (CHARINDEX(',', @UserRoles_in) - 1)>0 then  (CHARINDEX(',', @UserRoles_in) - 1) else len(@userRoles_in) end)
if isnumeric( @RoleId_in)
    SELECT @cnt  =  COUNT(*) FROM  nodetable WHERE      objtype  = 21 AND id  = @RoleId_in

               IF ( @cnt = 0 )
               BEGIN
                    INSERT INTO  error_report VALUES( 'ObjectACL' , '0' , 'UserRoles refering to Non-existing Role : ' + CAST(@RoleID_in AS VARCHAR) )  
               END
                  SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, LEN(@RoleID_in) + 2, LEN(@UserRoles_in))

--- replace here ==============>>>>>               IF ( @UserRoles_in is null )  <<<<<< ============== replace here
--==============>>>>>                 BEGIN
--==============>>>>>                      BREAK
--==============>>>>>                 END
             
             END --)
     END --)
     close csr
DEALLOCATE csr
GO
0
 

Author Comment

by:kingno1
ID: 12148686
Didnt help me
0
 
LVL 9

Expert Comment

by:miron
ID: 12148732
-- check if this still produces  error

DECLARE @objid_in                               INT
DECLARE @objclass_in                              INT
DECLARE @userid_in                                INT
DECLARE @userRoles_in                             VARCHAR(3000)
DECLARE @RoleID_in                                varchar(20)
DECLARE @cnt                                      INT
     
DECLARE csr CURSOR FOR
     SELECT * FROM  objectACL
     OPEN csr
     WHILE (0 = 0)
     BEGIN --(
         
          fetch NEXT FROM csr INTO @objid_in, @objclass_in, @userid_in, @userRoles_in
          IF (@@FETCH_STATUS = -1)
          BREAK
          SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, 2, LEN(@UserRoles_in))
          WHILE (0 = 0)
          BEGIN --(
                    SELECT @RoleID_in = SUBSTRING(@UserRoles_in, 1, CHARINDEX(',', @UserRoles_in+',') - 1))
                   if isnumeric( @RoleId_in)
                           SELECT @cnt  =  COUNT(*) FROM  nodetable WHERE      objtype  = 21 AND id  = @RoleId_in
                   else   -- need set the @cnt to ZERO! as per oracle procedure
                           set @cnt = 0
                   IF ( @cnt = 0 )
                   BEGIN
                                INSERT INTO  error_report VALUES( 'ObjectACL' , '0' , 'UserRoles refering to Non-existing Role : ' + CAST(@RoleID_in AS VARCHAR) )  
                   END
                  END
                  SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, LEN(@RoleID_in) + 2, LEN(@UserRoles_in))
              if ( @UserRoles_in IS NULL OR rtrim(ltrim(@UserRoles_in)) = '' ) break
             END --)
     END --)
     close csr
DEALLOCATE csr
GO
0
 
LVL 9

Expert Comment

by:miron
ID: 12148758
also, make cursor look more like sql server

DECLARE @objid_in                               INT
DECLARE @objclass_in                              INT
DECLARE @userid_in                                INT
DECLARE @userRoles_in                             VARCHAR(3000)
DECLARE @RoleID_in                                varchar(20)
DECLARE @cnt                                      INT
     
DECLARE csr CURSOR FOR
     SELECT * FROM  objectACL
     OPEN csr
      fetch NEXT FROM csr INTO @objid_in, @objclass_in, @userid_in, @userRoles_in    
     WHILE (@@FETCH_STATUS = 0 )
     BEGIN --(
          SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, 2, LEN(@UserRoles_in))
          WHILE (0 = 0)
          BEGIN --(
                    SELECT @RoleID_in = SUBSTRING(@UserRoles_in, 1, CHARINDEX(',', @UserRoles_in+',') - 1))
                   if isnumeric( @RoleId_in)
                           SELECT @cnt  =  COUNT(*) FROM  nodetable WHERE      objtype  = 21 AND id  = @RoleId_in
                   else   -- need set the @cnt to ZERO! as per oracle procedure
                           set @cnt = 0
                   IF ( @cnt = 0 )
                   BEGIN
                                INSERT INTO  error_report VALUES( 'ObjectACL' , '0' , 'UserRoles refering to Non-existing Role : ' + CAST(@RoleID_in AS VARCHAR) )  
                   END
                  END
                  SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, LEN(@RoleID_in) + 2, LEN(@UserRoles_in))
              if ( @UserRoles_in IS NULL OR rtrim(ltrim(@UserRoles_in)) = '' ) break
             END --)
     END --)
     close csr
DEALLOCATE csr
GO
0
 
LVL 9

Expert Comment

by:miron
ID: 12148761
-- correction

DECLARE @objid_in                               INT
DECLARE @objclass_in                              INT
DECLARE @userid_in                                INT
DECLARE @userRoles_in                             VARCHAR(3000)
DECLARE @RoleID_in                                varchar(20)
DECLARE @cnt                                      INT
     
DECLARE csr CURSOR FOR
     SELECT * FROM  objectACL
     OPEN csr
      fetch NEXT FROM csr INTO @objid_in, @objclass_in, @userid_in, @userRoles_in    
     WHILE (@@FETCH_STATUS = 0 )
     BEGIN --(
          SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, 2, LEN(@UserRoles_in))
          WHILE (0 = 0)
          BEGIN --(
                    SELECT @RoleID_in = SUBSTRING(@UserRoles_in, 1, CHARINDEX(',', @UserRoles_in+',') - 1))
                   if isnumeric( @RoleId_in)
                           SELECT @cnt  =  COUNT(*) FROM  nodetable WHERE      objtype  = 21 AND id  = @RoleId_in
                   else   -- need set the @cnt to ZERO! as per oracle procedure
                           set @cnt = 0
                   IF ( @cnt = 0 )
                   BEGIN
                                INSERT INTO  error_report VALUES( 'ObjectACL' , '0' , 'UserRoles refering to Non-existing Role : ' + CAST(@RoleID_in AS VARCHAR) )  
                   END
                  END
                  SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, LEN(@RoleID_in) + 2, LEN(@UserRoles_in))
              if ( @UserRoles_in IS NULL OR rtrim(ltrim(@UserRoles_in)) = '' ) break
             END --)
            fetch NEXT FROM csr INTO @objid_in, @objclass_in, @userid_in, @userRoles_in    
     END --)
     close csr
DEALLOCATE csr
GO
0
 
LVL 9

Expert Comment

by:miron
ID: 12148785
-- correction

DECLARE @objid_in                               INT
DECLARE @objclass_in                              INT
DECLARE @userid_in                                INT
DECLARE @userRoles_in                             VARCHAR(3000)
DECLARE @RoleID_in                                varchar(20)
DECLARE @cnt                                      INT
     
DECLARE csr CURSOR FOR
     SELECT * FROM  objectACL
     OPEN csr
      fetch NEXT FROM csr INTO @objid_in, @objclass_in, @userid_in, @userRoles_in    
     WHILE (@@FETCH_STATUS = 0 )
     BEGIN --(
          SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, 2, LEN(@UserRoles_in))
          WHILE (0 = 0)
          BEGIN --(
                    SELECT @RoleID_in = SUBSTRING(@UserRoles_in, 1, CHARINDEX(',', @UserRoles_in+',') - 1))
                   if isnumeric( @RoleId_in)
                           SELECT @cnt  =  COUNT(*) FROM  nodetable WHERE      objtype  = 21 AND id  = @RoleId_in
                   else   -- need set the @cnt to ZERO! as per oracle procedure
                           set @cnt = 0
                   IF ( @cnt = 0 )
                   BEGIN
                                INSERT INTO  error_report VALUES( 'ObjectACL' , '0' , 'UserRoles refering to Non-existing Role : ' + CAST(@RoleID_in AS VARCHAR) )  
                   END
                  SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, LEN(@RoleID_in) + 2, LEN(@UserRoles_in))
                  if ( @UserRoles_in IS NULL OR rtrim(ltrim(@UserRoles_in)) = '' ) break
             END --)
            fetch NEXT FROM csr INTO @objid_in, @objclass_in, @userid_in, @userRoles_in    
     END --)
     close csr
DEALLOCATE csr
GO
0
 
LVL 9

Assisted Solution

by:miron
miron earned 125 total points
ID: 12148822
-- correction

DECLARE @objid_in                               INT
DECLARE @objclass_in                              INT
DECLARE @userid_in                                INT
DECLARE @userRoles_in                             VARCHAR(3000)
DECLARE @RoleID_in                                varchar(20)
DECLARE @cnt                                      INT

DECLARE csr CURSOR FOR
     SELECT * FROM  objectACL
      OPEN csr
       fetch NEXT FROM csr INTO @objid_in, @objclass_in, @userid_in, @userRoles_in
      WHILE (@@FETCH_STATUS = 0 )
      BEGIN --(
           SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, 2, LEN(@UserRoles_in))
           WHILE (0 = 0)
           BEGIN --(
                     SELECT @RoleID_in = SUBSTRING(@UserRoles_in, 1, CHARINDEX(',', @UserRoles_in+',') - 1 )
                    if (  isnumeric( @RoleId_in ) = 1 )
                            SELECT @cnt  =  COUNT(*) FROM  nodetable WHERE      objtype  = 21 AND id  = @RoleId_in
                    else   -- need set the @cnt to ZERO! as per oracle procedure
                            set @cnt = 0
                    IF ( @cnt = 0 )
                    BEGIN
                                 INSERT INTO  error_report VALUES( 'ObjectACL' , '0' , 'UserRoles refering to Non-exing Role : ' + CAST(@RoleID_in AS VARCHAR) )
                    END
                   SELECT @UserRoles_in  = SUBSTRING(@UserRoles_in, LEN(@RoleID_in) + 2, LEN(@UserRoles_in))
                   if ( @UserRoles_in IS NULL OR rtrim(ltrim(@UserRoles_in)) = '' ) break
              END --)
             fetch NEXT FROM csr INTO @objid_in, @objclass_in, @userid_in, @userRoles_in
      END --)
      close csr
 DEALLOCATE csr

0
 
LVL 9

Expert Comment

by:miron
ID: 12156144
do you have any other questions
0
 

Author Comment

by:kingno1
ID: 12156615
Hi, guys, I really appreciate your help and input.
Have a question..
Do I still need to put the below statement. I didnt see this in oracle procedure.

 else   -- need set the @cnt to ZERO! as per oracle procedure
                            set @cnt = 0
0
 

Author Comment

by:kingno1
ID: 12156667
Never mind, I guess I need the above statements.
I will test this with some more cases just to make sure. Thanks a lot guys.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question