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 Alter A Report that has already been deployed to my Report Manager

I have some OLAP reports in which I have to alter the MDX statements each month, in the VS2005 report designer. They are deployed to the web and they contain Descriptions and Subscriptions which I create once they get to the web. The way I am currently updating them each month is by editing the reports in VS2005 and re-deploying them to the Reports Server. I then have to add the descriptions again and create new Subscriptions because the orig reports are deleted when the new ones, with the same name are deployed. There has to be a way that I can just alter the existing reports in the web environment and not have to recreate the Subscriptions each month. Any help would be appreciated.

If you go to the actual report that you want to update in report manager and go to the general tab there is a button that says update that you can select and then upload the new version. Then all of your descriptions and your history will remain and you just upload the new version of the report.

Properties-General-Update-Do not forget to hit apply when finished choosing rdl file.

Can Data Extension built with VS2005 work with RS2000 ?

For various reasons our next release of our software needs to continue to support SQL 2000 and Reporting Services 2000. We are using a Data Extension written in C#.

Can we build a data extension in VS2005, using .Net 2.0 and have it work with RS2000. A quick test did not work.

Is this a valid / supported configuration ?

If it is, what steps, modification to the various configuration files would I need to make to get it to work ?

Thanks

Richard

No this won't work. RS 2000 will only work with .NET 1.1. Since data extensions will get loaded into the same app. domain, a RS 2000 data extension has to be built with .NET 1.1 / VS 2003.

-- Robert

|||What about the other way around? Will RS2005 Support extensions I built with RS2000?|||

You should update the references (e.g. RS 2005: MS.RS.Interfaces.dll) and recompile the data extension with VS 2005.

-- Robert

Can Data Extension built with VS2005 work with RS2000 ?

For various reasons our next release of our software needs to continue to support SQL 2000 and Reporting Services 2000. We are using a Data Extension written in C#.

Can we build a data extension in VS2005, using .Net 2.0 and have it work with RS2000. A quick test did not work.

Is this a valid / supported configuration ?

If it is, what steps, modification to the various configuration files would I need to make to get it to work ?

Thanks

Richard

No this won't work. RS 2000 will only work with .NET 1.1. Since data extensions will get loaded into the same app. domain, a RS 2000 data extension has to be built with .NET 1.1 / VS 2003.

-- Robert

|||What about the other way around? Will RS2005 Support extensions I built with RS2000?|||

You should update the references (e.g. RS 2005: MS.RS.Interfaces.dll) and recompile the data extension with VS 2005.

-- Robert

Can ASP.net 2.0 be connected with SQL Server 2000?

using vs2005 to build web pages of asp.net 2.0, The database is SQL Server 2000,.But I always fail to connect.

using the following configuration in web.config:

<appSettings>
<add key="DSN_student" value="server=(local);uid=admin;pwd=123456;database=network_course"/>
</appSettings>

I also failed to connect using the following configuration in web.config:

<add name="network_courseConnectionString1" connectionString="Data Source=(local);Initial Catalog=network_course;User ID=admin;Password=123456;"
providerName="System.Data.SqlClient" />

I am a beginner from China and eager to get answers! Thank you!

Can you post the error you got and the code?

Regards

|||To answer your question, yes. You can use VS2005 with SQL Server 2000. Looking at your second connection string seems like it should work. You might add "Integrated Security=false" in the connect string attributes, but I believe that is the default.

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.