converting sql 2000 script to sql 7. gurus... please help!

We have a customer who got their site developed somewhere else. Well,
this host sent me all of the files along with a sql script to create
all the tables, sps, views, etc. I tried running it on our customer
sql box which is sql 7 and I got a number of errors. When I ran it on
my own machine which is developer 2000, it works fine. So, I've found
the main 3 lines that are causing the problems. Is there a way to
convert these lines to some syntax that is compatible with sql 7?

error 1: Incorrect syntax near the keyword 'ON'.
offending line: ON DELETE CASCADE ON UPDATE CASCADE

error 2: 'scope_identity' is not a recognized function name.
offending line: select @.access_id=scope_identity()

error 3: Incorrect syntax near the keyword 'level'.
offending line: select a.*,b.name level,c.username

I tried on error 3 doing "select a.*, b.name AS level, c.username" but
it still gave the same error.On 5 Aug 2004 06:06:49 -0700, geoff wrote:

>We have a customer who got their site developed somewhere else. Well,
>this host sent me all of the files along with a sql script to create
>all the tables, sps, views, etc. I tried running it on our customer
>sql box which is sql 7 and I got a number of errors. When I ran it on
>my own machine which is developer 2000, it works fine. So, I've found
>the main 3 lines that are causing the problems. Is there a way to
>convert these lines to some syntax that is compatible with sql 7?
>error 1: Incorrect syntax near the keyword 'ON'.
>offending line: ON DELETE CASCADE ON UPDATE CASCADE
>error 2: 'scope_identity' is not a recognized function name.
>offending line: select @.access_id=scope_identity()
>error 3: Incorrect syntax near the keyword 'level'.
>offending line: select a.*,b.name level,c.username
>I tried on error 3 doing "select a.*, b.name AS level, c.username" but
>it still gave the same error.

Hi Geoff,

Yes, it can be done.

re error 1: cascading foreign key constraints are new in SQL Server 2000.
To convert toi SQL Server 7.0, you'll have to remove the foreign key
("REFERENCES") constraint completely and replace it with your own checking
routines in triggers. I believe Books Online has some examples on this; if
not and you do need further assistance, let me know.

re error 2: I usually try to avoid identity so I'm not very experiences
with this, but I believe you can replace scope_identity() with either
IDENNT_CURRENT or @.@.IDENTITY. Do read the info in Books Online for
possible differences!

re error 3: level was a reserved keyword up to SQL Server 7.0. You either
have to usa another column alias, or enclose it in [brackets] or
"quotation marks": select a.*, b.name AS [level], c.username

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)