Re: converting from Oracle to SQL Server

From: cuneyt <member_at_dbforums.com>
Date: Mon, 25 Nov 2002 09:47:53 +0000
Message-ID: <2083826.1038217673_at_dbforums.com>

thanx to all folks, I solved the problem by using stored procedure, That sql

statement is also to use with ASP so I am writing whole solution here.

set cnn=server.createobject("AdoDB.connection")

strC="Provider=SQLOLEDB.1;Password=[YOUR_SQL_SERVER_PASSWORD];Persist  Security
Info=True;User ID=[YOUR_SQL_SERVER_USERID];Initial

Catalog=[YOUR_SQL_SERVER_DATABASE_NAME];Data Source=[YOUR_SQL_SERVER_COMPUTER]"

cnn.open strC

'creating store procedure that you can use instead of "start with", "connect

by" and "prior"

SQLStr1="CREATE PROCEDURE expand (_at_current char(20)) AS SET NOCOUNT ON DECLARE _at_lvl int, @line char(20) CREATE TABLE #stack (item char(20), lvl int) INSERT

INTO #stack VALUES (_at_current, 1) SELECT @lvl = 1 WHILE @lvl > 0 BEGIN IF EXISTS (SELECT * FROM #stack WHERE lvl = _at_lvl) BEGIN SELECT @current = item FROM #stack WHERE lvl = _at_lvl SELECT @line = space(@lvl - 1) + @current PRINT _at_line

DELETE FROM #stack WHERE lvl = _at_lvl AND item = @current INSERT #stack SELECT

LEVEL_CODE, _at_lvl + 1 FROM T_NBS_LEVELS WHERE PARENT_CODE = @current IF

_at__at_ROWCOUNT > 0 SELECT @lvl = @lvl + 1 END ELSE SELECT @lvl = @lvl - 1 END" 'executing this stored procedure in MS SQL SERVER SQLStr2="execute expand N'0'"

'removing procedure
SQLStr3="DROP PROCEDURE expand "

'remove the procedure if it's not necessary to store it in your database, but

if you want to keep it, just create it in MS SQL SERVER then only use execute

statement in your code. When you create it using codes and then if you do not

remove it, it will give an error since it'll try to create existing procedure

each time.

'executing in ASP

cnn.execute(sqlstr1)
cnn.execute(sqlstr2)
cnn.execute(sqlstr3)

Originally posted by Karsten Farrell
> cuneyt wrote:
> > I have this Oracle Sql statement but I need to convert it to SQL
> Server
> > Statement.
> > "SELECT LPAD('-', 2*(LEVEL-1),'-') || DESCRIPTION
> DESCRIPTION,
> > LEVEL_CODE
> > FROM T_NBS_LEVELS"SQLStr=SQLStr&"
> > START WITH PARENT_CODE = 0"SQLStr=SQLStr&"
> > CONNECT BY PRIOR LEVEL_CODE=PARENT_CODE "
> >
> > Sql Server gives Run time error message which is about LPAD is
> not
> > recognized etc...
> >
> > Also does anybody know a tool that converts Oracle Sql
> statements to SQL
> > Server Statement?
> >
> > --
> > Posted via
> http://dbforums.com/http://dbforums.com
> Found this from Google ("Oracle to SQL Server: Migration Traps"):

>

> http://asia.cnet.com/itmanager/netadmin/0,39006400,39043720,00.-
> htm"]http://asia.cnet.com/itmanager/netadmin/0,39006400,39043720,00.h-
> tm[/url]

>
Covers some functions, working with numbers, concatenation, etc.

--
Posted via http://dbforums.com

Received on Mon Nov 25 2002 - 10:47:53 CET