PLEASE TELL ME WHAT TO DO NEXT _ 2000 instead of 2005

Hi all.

My webhost prepared a server for me with sql 2000 instead of sql 2005.
Will my asp.net 2005 application work there?
I generated a script to export my sql server 2005 objects
Lots of failures appeard.

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name =
N'aspnet_WebEvent_FullAccess' AND type = 'R')
CREATE ROLE [aspnet_WebEvent_FullAccess]
GO

This is the error:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'ROLE'.

The object is part of the asp.net security/login components - I did not create these
objects myself!

Please tell me what to do next........

TIA
Guy

You should not have any problem from 2005 to 2000, however looks like you didn't install the whole database as does not understand ROLE

|||

Dear Al

THanks for the reply

Please explain more... what should I do......?????

When I generated the script I chose "export all objects"

Guy

|||

There is an .sql file, can you install that?

|||

You need to be certain that the scripts are exported for SQL Server 2000.

When you go to "generate scripts" (right click on DB, Tasks->Generate Scripts), after you choose the DB and pick to export all objects, you come across a long list of options. One is called "Script for Server Version". That needs to be set to SQL Server 2000. By default it is set fo SQL Server 2005. If you do not do this syntactic differences will throw many, many errors.

As long as your database doesn't use anything that is 2005 specific, you should be able to run the script.

|||Tony is right, a lot of things have been changed in SQL2005, such as system objects, some T-SQL syntax, etc. For example in the script we can see sys.database_principals, which is a view in SQL2005 but does not exist in SQL2000; and CREATE ROLE is a new command in SQL2005, but invalid in SQL2000, in SQL2000 you need to use sp_addrole.