Can i host a SQL2005 EE DB on SQL2000 engine ?

Hi,
I'd like to host a small website created using VS2005 EE with a company that offers just SQL2000 DB support.
My question, can i use the DB created by VS site manager as a DB to the site, if not, is there any alternatives.
Thanks.

hi,

SQL Server 2005 databases can not be attached/managed/used by SQL Server 2000 edtions.. the internal structures and metadata are different.. you can obvioulsy upgrade a SQL Server 2000 database to SQL Server 2005, but not the contrary..

you can "handle" that scripting out the database objects (obviously only the SQL Server 2000 supported ones) cleaning out eventual 2005 features and recreate the database on the SQL Server 2000 instance running those scripts... you can then BCP out (and "in", in the destination instance) eventual pre-loaded required data..

or, you can use the wizards provided by SSIS (not available with SQL Server Express and SQL Server Management Studio Express, but with full SQL Server 2005 edition only) to to "copy" the required database(s) to the 2000 edition instance..

regards

|||

The SQL team just released a Database Publishing Wizard that might meet your needs. The wizard makes it easy to script out your development database, even directly from VS. You can then run the script on your hosted site to recreate the database and the data. You should be able to run the script on both 2005 and 2000 systems.

You can find a tutorial on using the wizard in this blog post along with a link to where you can download the wizard.

Mike

|||

Hi all,

There's an easier way. What you can do it run the sp_dbcmptlevel stored procedure and set the version of your database to 80 (i.e. SQL Server 2000) You can then use this db (obviously without any SQL Server 2005 features) on a SQL Server 2000 engine. Here's more about the sp_dbcmptlevel stored procedure :

http://msdn2.microsoft.com/en-us/library/ms178653.aspx

Regards,

Amol A. Vaidya.

|||

hi Amol,

nope, this is not an option... you can not move a SQL Server 2005 database to a SQL Server 2000 instance even if modifying the compatibility level...

regards

|||

Hi Andrea,

Yeah you are right on that. The formats for SQL Server 2005 Database are different from that of SQL Server 2000 database and sp_dbcmptlevel does not downgrade to that format. I mixed this up with the other-way-round scenario that you can use a SQL Server 2000 database as-is (i.e. locked up with formats and features restricted to SQL Server 2000) by setting the sp_dbcmptlevel to 80 on a SQL Server 2005 Engine but not the other way round.

Thanks for pointing that out,

Amol.

|||

Hi Amol,

I think you still might be confused about the dbcmptlevel. Setting it to 80 has nothing to do with file format ever. When you attach a SQL 2000 database to SQL 2005 it is always converted to the 2005 file format. The dbcmptlevel will cause SQL 2005 to run commands as if it were SQL 2000, but the file format is always upgraded.

Mike

sql

can i create "Package" in sql2005 like in oracle

hi all.

can i create "Package" in sql2005 like i have in oracle, inorder to keep several s.proc's organized ?

how?

You could create Schema and place all desired stored procedures into with schema. As result you could call by YourSchema.usp_YourStoredProcedure.

Can I access sql 2000 from machine having sql2005

Dear All,

I am developing a network application in asp.net. The database is in local machine having sql 2005. But the user database is already existent. So I am accessing that database which is sql2000. does it give any problem while connecting from sql2005 machine to sql2000. If yes it is giving me error as follows:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)

connection string is :

"Data Source=192.168.1.16,1433;Network Library=DBMSSOCN;Initial Catalog=mycatalog;User ID=myuserid;Password=mypwd;"

Please correct me if I am wrong.

Thanks and Regards.

Fazal

This indicates that your TCP/IP was not enabled or your sql server was not listening on the appropriate port.

|||

Go to "Microsoft SQL Server 2005" in programm files => onfiguration Tools =>SQL Server Surface Area Configuration =>first option=>Remote Connections

and here allow remote connection = Use both TCP/IP and name pipes => OK

After that you must restart SQL Server 2005 in order to apply the modification and try againa

Popa IUlia

_____________________

MCP.MCAD.MCSD

Can any body help me for creating user datatype

I am new to sql2005... how can i create user defined datatype containing different types
ex : address with
name as varchar.
id as integeryou can find the details regarding your question here or here
Hope that solves your problem.

can a SQL2005 DB be hosted on a SQL2000 server ?

Hi,
I'd like to host a small website created using VS2005 EE with a company that offers just SQL2000 DB support.
My question, can i use the DB created by VS site manager as a DB to the site, if not, is there any alternatives.
Thanks.in general, this won't work. what you are asking for is forward compatibility, which is impossible to deliver in practice. it's like expecting apps written for Vista to work on windows 95. If you are developing for 2000, you should use the 2000 tools.

on the other hand, as long as your db doesn't use any 2005 features not present in 2000 (such as CLR procs, xml columns, etc) then your DDL scripts should execute ok.

Calling user defined function from other server

I have UDF in a database on SQL2000 server. Is it possible to call this UDF from other server (SQL2005)? I did setup a linked server to SQL2000

Call to the following function returns an error:

Msg 207, Level 16, State 1, Line 1

Invalid column name 'srv2000'.

select [srv2000].db_test.dbo.F_TEST()

You cannot call remote UDFs in SQL Server right now. So you will have to use pass-through query using OPENQUERY to call the UDF or EXECUTE AT in SQL Server 2005.

-- SQL Server 2000/2005

select i from openquery([srv2000], 'select db_test.dbo.F_TEST() as i')

-- SQL Server 2005

exec('select db_test.dbo.F_TEST() as i') at [srv2000]

-- Or

declare @.i int
exec('select ? = db_test.dbo.F_TEST() ', @.i output) at [srv2000]
select @.i

|||Thanks for help!