from test environment to web hosting company

I must be missing something, and its starting to fusterate me. Bear with me here.

I created a site for a ...client I guess you'd call it, and made this really slick newletter generator thing. The people from the web enter in their info, and if they want, they sign up for a newsletter -- all tied into a db, 1 table, 4 stored procedures, REALLY simple stuff. They insisted I used a certain webhost which, on paper, looks like it will fit the bill. I'm starting to question that.

On top of the newsletter thing, I created an aspnetdb for the "administration" side of it for her to log into and send out the newsletter so total, there's 2 dbs in the app_data folder. Locally, it works GREAT and on my test box (iis6) that is running 2k5 express. The webhost runs sql2k in (what I consider) a bastardized way. Can't use the management studio, can't use anything except a really weak web-based interface which adds to my fusteration.

Anyway, my questions : 1, is there a way to make the mdf files work with sql2k without having to re-do the whole thing and 2, if I have to redo it, does anyone have an example connection string that might help out?

Fubarian:

Can't use the management studio, can't use anything except a really weak web-based interface which adds to my fusteration.

Did you mean you can only access the SQL2K instance through web? If you have login to the SQL2K, you can register the SQL2K intance in Management Studio by using your account.

1. From your description I understand your website is using attached database file under your app_data. As I know the database you're using should be version 9.0, that means it can be used in SQL2005 or SQLExpress instance, but not SQL2K instance. You can check the version by right clieck the databasefile in Server Explorer->New query-> execute 'SELECT @.@.version'. So you have to transfer the tables in the database files into a database on the SQL2K instance. You can do this by using Import/Export Wizard.

2. The 2nd thing you need to do is change the connection string to point to the SQL2K instance. You can refer to this post:

http://forums.asp.net/thread/1281242.aspx

From SQL Server 2005 to 2000

Hi all.

I'm doing some testing database on my computer that I've installed SQL server 2005. But the server at the company I'm working with has SQL Server 2000.

What do I need to do to convert my database file in 2005 into 2000 so that I can add it in the company's server?

TIA.

you can not "convert" SQL Server 2005 database to SQL Server 2000 database. The otherway round is possible.

Madhu

|||

While you cannot 'convert' the SQL 2005 database to a SQL 2000 format, you can script out the tables, views, functions, stored procedures, etc., then after checking those scripts for compatibility, you can run the scripts on SQL 2000 and re-create the database.

|||It would be advisable to change the compatability level of the 9.0 database to 8.0.|||Thank you all for replying.

One question, how to change the compatibility level?
|||

It would be nice if it were so simple as changing the compatibility level.

The physical files are different between SQL 2000 and SQL 2005, and to my knowledge, there is no current mechanism to 'downgrade' a database.

I think that you are left with my earlier suggestion about scripting out the database objects, and running those scripts in SQL 2000 to recreate a SQL 2000 database. Perhaps these links will help with scripting:

DDL -Script Database to File
http://www.wardyit.com/blog/blog/archive/2006/07/21/133.aspx
http://www.sqlteam.com/publish/scriptio
http://www.aspfaq.com/etiquette.asp?id=5006
http://www.codeproject.com/dotnet/ScriptDatabase.asp
http://www.nigelrivett.net/DMO/DMOScriptAllDatabases.html
http://rac4sql.net/qalite_main.asp

DDL –Script Data to file (Database Publishing Wizard)
http://www.microsoft.com/downloads/details.aspx?FamilyID=29B4FFD8-AC3A-4481-B352-9B185619A901

|||Thanks for all the links, most helpful!

However, the last link didn't point to a website. Was it this:
http://www.microsoft.com/downloads/details.aspx?FamilyID=56e5b1c5-bf17-42e0-a410-371a838e570a&DisplayLang=en
that you want to link to?
|||

use system sp sp_dbcmptlevel

Eg: EXEC sp_dbcmptlevel 'AdventureWorks', '80';

Madhu

|||Hi all,

Taken into considerations of all your suggestions, I'm compiling my step-by-step of 'downgrading' my database from version 2005 to 2000:

1) Set the compatibility of database to SQL server 2000:
EXEC sp_dbcmptlevel 'myDatabase', 80

2) Next, I will do this:
Open SQL Server Management Studio, and connect.
Right-click on myDatabase, choose 'Script Database As > CREATE To > New query editor window.
Save as .sql

Would it be advisable to script my tables one by one? But then I'll have to run the script in order of which table is created first based on relationships. Would scripting the whole database in one go takes care of the order of creating tables?
Any suggestions on what else I need to consider before/after doing this?

TIA.
|||

in this case there is no need to degrade the copatibility level. You can script all the objects into a single file and the run the script in 2000. by this step your schema will be ready. Now you need to improt the data using import/export or ssis or simple insert into statment after configuring linked server.

Madhu

|||Hello Madhu,

I've tried right-click on my database and Script Database to a new query window but there are only 60-70 lines and I didn't see any CREATE TABLE anywhere. So I would assume that I need to right-click on each table and Script table to a different .sql file for each table?

Eera
|||I probably script my database the wrong way. Sorry for giving you the wrong description earlier. Now, when I right-click my database, point to Tasks and chose 'Generate Scripts' and the scripts generated looks more promising.

Just started using SQL server this week.
|||

how many tables are there in this database ?

Madhu

|||There are (only) 8 tables in my database.

I have yet to research and find out the process of importing data using the method that u mentioned.
|||

if its only 8 table then the best method will be

(a) Configure LinkedServer in 2000 for SQL Server 2005

(b) use your own script like Insert into tablename select *from <LinkedserverName>.databasename.owner.tablename

Madhu

Friendly DataSet Table Names

I have a stored procedure that returns a set of tables based on data in a table for a company. These tables are being used to create drop down lists for criteria selection for the client. We have a Javascript based control that will make use of these based on the control name. So... ideally, I would like the stored procedure to return tables named "EmployeeID", "ResourceName", so that I can accurately name the controls when they are being created. The data that is returned is not static, so, for example, client 1 may see the EmployeeID drop down, while client 2 may not.

I searched and didnt see anyone that was able to accomplish this in this way, but wanted to post something here before I moved forward in a different direction.

I am thinking that I will likely return an extra table with the field names and their corresponding tables (i.e. "EmployeeID"/"Table0", "ResourceName"/"Table1", etc...)

Thanks,

Josh

Have you tried giving alias to the returned tables (result sets) as well as columns? For example:

create proc sp_RtnAlias
as
select EmployeeID EID from Employees as EmployeeID
go

Then you can access the result set using EmployeeID.EID

Hope I haven't misundstood you.

|||

Here's what I do... and I haven't decided if it is cheesey or not...

declare

@.tbltable(Ordinalintidentity(1,1),tableName varchar(50))

/* Insert a row into the temp table for each table returned by the query*/

INSERTINTO @.tblVALUES('Prospects')

INSERTINTO @.tblVALUES('AccountExecs')

INSERTINTO @.tblVALUES('Regions')

INSERTINTO @.tblVALUES('Products')

INSERTINTO @.tblVALUES('ProspectDescriptors')

INSERTINTO @.tblVALUES('Steps')

INSERTINTO @.tblVALUES('ProspectTypes')

INSERTINTO @.tblVALUES('VendorTypes')

INSERTINTO @.tblVALUES('Clients')

-- Index table

SELECT Ordinal, TableName

FROM @.Tbl

ORDERBY Ordinal

The rest of the sproc is a series of selects in the same order as they appear in the index table. I have method in the program that loops through the table objects and assigns each one the appropriate name from the index table. Note that the index table does not contain a reference to itself and can be disposed of when the other table names have been assigned...