Help me in converting Oracle procedure to SQL Server

Hi All

Can any one help me in converting this from WHICH IN ORACLE to MS SQL SERVER

sqlplus -s $UserId/$PassWord@.$DataBase <<EOSQL> $LogFile (This is the Connection String)

set serveroutput on
Declare
tempCnt Number:=0;
totDelCnt Number:=0;
Begin
Loop
$DelStmt
tempCnt := tempCnt+ SQL%ROWCOUNT ;
totDelcnt := totDelCnt+ SQL%ROWCOUNT ;

If SQL%NOTFOUND Then
Exit;
End if;

If tempCnt >= 50000 Then
Commit ;
tempCnt:=0 ;
End if ;

End Loop;
Commit ;
dbms_output.put_line('No of Recs Deleted From $TableName: '|| totDelcnt);
End;
/
exit
EOSQLWhat is the sql for your delete statement, and can you briefly explain what the code is supposed to do?|||Thanks

We started moving all the procedures we have ( in oracle) to SQL Server

So this is the first one.

Other Procedures we have the rest of the required statements|||Uhmm....okay.

You're welcome?

Now what?|||Hi All

Can any one help me in converting this from WHICH IN ORACLE to MS SQL SERVER

sqlplus -s $UserId/$PassWord@.$DataBase <<EOSQL> $LogFile (This is the Connection String)

set serveroutput on
Declare
declare
tempCnt Number:=0;
@.tempCnt numeric
set @.tempCnt = 0
totDelCnt Number:=0;
Begin
Loop
$DelStmt
tempCnt := tempCnt+ SQL%ROWCOUNT ;
totDelcnt := totDelCnt+ SQL%ROWCOUNT ;

If SQL%NOTFOUND Then
if @.@.rowcount = 0
Exit;
End if;

If tempCnt >= 50000 Then
Commit ;
tempCnt:=0 ;
End if ;

End Loop;
Commit ;
dbms_output.put_line('No of Recs Deleted From $TableName: '|| totDelcnt);
End;
/
exit
EOSQL

How are you going to connect to sql server from a UNIX shell script ? Is there a unix client for sql server that I do not know about ?|||Hi All

Can any one help me in converting this from WHICH IN ORACLE to MS SQL SERVER

sqlplus -s $UserId/$PassWord@.$DataBase <<EOSQL> $LogFile (This is the Connection String)

set serveroutput on
Declare
declare
tempCnt Number:=0;
@.tempCnt numeric
set @.tempCnt = 0
totDelCnt Number:=0;
Begin
Loop
$DelStmt
tempCnt := tempCnt+ SQL%ROWCOUNT ;
totDelcnt := totDelCnt+ SQL%ROWCOUNT ;

If SQL%NOTFOUND Then
if @.@.rowcount = 0
Exit;
End if;

If tempCnt >= 50000 Then
Commit ;
tempCnt:=0 ;
End if ;

End Loop;
Commit ;
dbms_output.put_line('No of Recs Deleted From $TableName: '|| totDelcnt);
End;
/
exit
EOSQL

How are you going to connect to sql server from a UNIX shell script ? Is there a unix client for sql server that I do not know about ? (only kidding)|||What is the sql for your delete statement, and can you briefly explain what the code is supposed to do?

He's deleting rows in blocks of 50000, so he does not hose his rollback segment(s). This is a sh/ksh script though. He will need to convert it to a DOS cmd file, unless he is using MKS or something like that in Windows.|||Try;

declare @.count integer
set @.count = ( select count(*) from <tablename> )

truncate table <tablename>
--or delete * from <tablename>
print 'Deleted '+cast(@.count as varchar(20))+' rows'

Why are you deleting in blocks of 50000??

just a thought. . . . .

GKramer
The Netherlands|||Try;

declare @.count integer
set @.count = ( select count(*) from <tablename> )

truncate table <tablename>
--or delete * from <tablename>
print 'Deleted '+cast(@.count as varchar(20))+' rows'

Why are you deleting in blocks of 50000??

just a thought. . . . .

GKramer
The Netherlands

His rollback segment(s) are probably not that large, and he is probably low on DASD.|||His rollback segment(s) are probably not that large, and he is probably low on DASD.

DASD...good lord man, your mainframe is showing

As far as converting Oracle Packages and Sprocs, it will not be a straight conversion.

What for example do you plan to do with all those Cursors?

Convert them into SQL Server Cursors?

There is no such thing in SQL server as reference Cursors...they just push out a SELECT to a result set.

And what is the front end application code?