Attach Detach System Databases

Hi. We are migrating databases from a server with
Standard edition (2000) to Enterprise Edition (2000).
Trying to find out if there is any difference in system
databases between these versions. For example, can we
move the master database from the Standard instance to
the Enterprise instance without any hangups. Thanks.
You can move the system databases from one Edition of SQL Server to another
Edition.
Rand
This posting is provided "as is" with no warranties and confers no rights.
sql

Attach Detach System Databases

Hi. We are migrating databases from a server with
Standard edition (2000) to Enterprise Edition (2000).
Trying to find out if there is any difference in system
databases between these versions. For example, can we
move the master database from the Standard instance to
the Enterprise instance without any hangups. Thanks.You can move the system databases from one Edition of SQL Server to another
Edition.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Attach Detach System Databases

Hi. We are migrating databases from a server with
Standard edition (2000) to Enterprise Edition (2000).
Trying to find out if there is any difference in system
databases between these versions. For example, can we
move the master database from the Standard instance to
the Enterprise instance without any hangups. Thanks.You can move the system databases from one Edition of SQL Server to another
Edition.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Attach Db With Same Files As Existing Db

In SQL Server 2000, I would like to attach a Database that is the file of the Database on another system. I made a copy of my Client's Database at his site. Now I would like to attach it to my server and then copy the data from my Client's DB to mine.

The only problem is they have the same file names, so when I try to attach the copy I get an error message. Is there any way to change the name of the data file and log file that a database points to so that I can attach it to my Server?

Thanks in advance for your help!Look up RESTORE DATABASE in Books Online. You need to use the WITH MOVE option.

RESTORE DATABASE database
WITH
MOVE 'logical data file' TO 'somenewfile.mdf',
MOVE 'logical log file' TO 'somenewfile.ldf'

You can find the name of the files by using: RESTORE FILELISTONLY FROM DISK = 'file.bak'

Attach database to SQL2005 from old MSDE database

I have a medical records system, SoapWare v4.90, that uses MSDE (SQL2000) databases. Due to the 2gb limitation, I am trying to migrate over to SQL 2005 (Standard or Express) which the company says works fine. The SoapWare has a datamanager that allows me to log in to the MSDE instance, detach the SoapWare databases from msde (as well as do backups, etc) which I can confirm are detached.

Then I log back into a SQL2005 database instance using the datamanager and try to attach the database. This is what their pictured instructions demonstrate. However, I get an error:

Database 'sw_charts' cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery.

Or... is there a way to attach the databases to SQLExpress manually?

Help pls?The instance of SQL Express needs to be run as "Local system" which is not the default. Once this is changed, the SQL Express database attaches to the old MSDE SQL 2000 database just fine, and the program appears to run without problems.

Thanks to anyone looking at my post, and hopefully, this will help others.

David|||1> use master
2> go

** close the database johandb

1>exec sp_dboption N'johandb',N'autoclose',N'false'
2>go

1>checkpoint
2>go

** detach database johan
1> exec sp_detach_db johandb, true
2> go

** attach with files

1> exec sp_attach_db @.dbname = 'johandb',
2> @.filename1='d:\mssql7\data\johandb_data.mdf',
3> @.filename2='d:\mssql7\data\johandb_log.ldf'
4> go

Regards,
Johan|||Does anyone use Lytec and SOAPware together?

MD in Texassql

Attach an aspnet or sql2005 database on a sql2000 server

Hello

I want to attach an ASPNETDB database (generated by the asp.net login wizard system), which is a sql2005 .mdf database I think, to our SQL2000 server.

I tried to attach the file directly ; then to attach it to a sql2005 express server, "turning it" into a sql2000 database via the properties, then make a backup, then restore the backup on the 2000 server ; nothing worked out !
One of the method I tried (I don't remember which one) told me it could'nt read the sysindexes table ; of course, because there isn't such table on sql2005 databases.

So, is there a way to attach a SQL2005 database (so with no or few system tables) to a SQL2000 server (which requires those tables), or to re-generate the ASPNETDB in the SQL2000 format ?
If I re-create the syssomething tables on the SQL2005 database, will it work that simply ?

At the very last, I may have to install SQL express on the server. If there is already a SQL2000 server on the machine, will it cohabit with no problem ? Is the SQL express server ready for a production server with light or medium load ?

Thanks a lot, and sorry for so many questions ! :)

Hi,

"I want to attach an ASPNETDB database (generated by the asp.net login wizard system), which is a sql2005 .mdf database I think, to our SQL2000 server."

-That′s not working, the format is different to SQL Server 2k5, there is no backward compatibility.

""turning it" into a sql2000 database"

-Making the compatibility level to 2000 doesn't mean that the database is 2000 ready, it just behaves on a SQL2k5 machine like a SQL 2k database.

You will either have to script the object and the data out or bcp the data out or use any wizard to transfer the objects to the other SQL 2000 Server machine.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Ok, and if I export the database structure as a SQL script, will it just work ?

And for the SQL2005 Express server, is it usable along with the SQL2000 server, and does it fits for a ligt-load production server ?

thanks !
|||

Hi,

sure as long as you don′t use any new features of SQL Server 2005 and you take care of the difference of schema and owner you should be fine.

SQL 2k and SQL2k5X is working together. It can be also used as a light load production server (whatever that means in your case :-) ), it is only limited by the limitations of SQL Server Express, no query governor or anything else.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

ASPNETDB.mdf "...already in use..." Error and Broader Questions

When you create and host an ASP.Net 2.0 site on your local computer and then configure .Net 2.0's drop-in user registration and management system it creates a SQL file named ASPNETDB.mdf with a whole bunch of tables and stored procedures (…as most of you know). If you add your own custom tables to that database file and then try to call those tables you get an error that says that ASPNETDB.mdf is already in use. One way to get around this is to create a separate database in the form of another ".mdf" file and then put all of your own custom tables in it. You avoid the "...already in use..." errors that way but all your user accounts and the primary key structure that identifies them are in the original ASPNETDB.mdf file. This makes it impossible to do primary/foreign key relationships between those tables and those in the new separate database you created. It's kind of a catch-22 situation...unless I'm missing something that relates to releasing the ASPNETDB.mdf file from use whenever it is called so that additional tables and queries against that database file can be made without the "…already in use…" error cropping up. I am wondering if this problem is because the ASPNETDB.mdf file is not a "real" SQL database and as such imposes multiple-connectivity limitations such as those I am seeing. If this is true, migrating to a real SQL database would alleviate this? Finally, from a broader security and scalability standpoint…what are the best practices relating to use of the ASPNETDB.mdf database for all your custom tables? Should an additional database be created for all my application's custom tables (leading to the primary/foreign key problems) or should the additional tables be put into the ASPNETDB.mdf file (with some way of working around the "…already in use…" error)? A long-winded and broad question…thanks in advance for any responses.

MDCragg

The default membership provider already has an open connection to the file, which seems like the reason why you can't make your own connection. Perhaps you could extend this provider to keep its current functionality, but add your own requirements to access the other tables, as well. The default provider is the System.Web.Security.SqlMembershipProvider class. After you create this, set it as the default provider in your web.config.

Sample on how to configure a membership provider in web.config:http://msdn2.microsoft.com/en-us/library/44w5aswa.aspx

|||

That article tells how to merge the membership provider tables and stored procedures into my own database. I will try this to see if it solves the problem. I am at least a little bit doubtful if it will though because it seems like it will just shift the same persistent and exclusive connectivity problem I am having from the current user account database to my own database.

|||

This seems to have worked. I installed SQL Server Management Studio Express. I wasn't able to navigate to the existing custom database that I had created so I copied it to the default directory that SQL Server MSE utilizes. I was able to connect to it there and thus "attach" it to my PC's SQL host. Once that was done I was able to use the aspnet_regsql.exe utility to populate that database with all of .Net's Membership tables, views, stored procedures, etc. I copied the database back to the App_Data folder. I adjusted all the Membership entries in the web.config folder to "point to" the custom database instead of the ASPNETDB.mdf file (which I removed). I did some additional tweaking with things such as connection strings. Then I launched the site and everything seemed to work. I am able to connect to the .Net Membership tables as well as all my own tables...all of which exist in the one custom database file.

So, this is fixed although I don't know what the difference was between the ASPNETDB.mdf file and the custom ".mdf" file that I created. I'm sure there is a setting or two somewhere in the database instance, the connection, or something or other that led to the difficulty.

aspnet_regsqlcache tool not found in my system

hi i am going to install the aspnet_regsqlcache tool in my system to create the notification tables for my database. while i am istalling in the .net framework command prompt, it tells like this,

"'aspnet_regsqlcache' is not recognized as an internal or external command"

what is the problem in my system...please any one help to solve this issues..

thanks in advance

The utility is now called aspnet_regsql.exe. It can be found here C:\WINDOWS\Microsoft.Net\Framework\v2.0.50727.

This was also discussed on the Wrox forum (in case you read about this in a book). http://p2p.wrox.com/TopicIndex/21996.htm

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

aspnet_regiis on remote system

Sure, the subject line seems easy enough. But is there a way that I can run this tool against my local SQL 2k5 server and script out the objects it creates to try and set up the tables and what-not on a remote server that doesn't accept remote connections? Or is there maybe a pre-built script somewhere that you can point me to?

I've tried to grab the stored procs and tables under the aspnet schema and script them out but it's not working correctly. Still can't run my login form against it.

Thanks

Hello,

I think you mean aspnet_regsql.exe? If you run it on your own computer like this: aspnet_regsql.exe /? you will see all available options. In it you will read that it's possible to generate an SQL script file. You can then use this script file to create the tables.

|||

Yes, I did mean the aspnet_regsql.exe. I gen'ed the script. THanks...

Asp.net queries with sqlserver2000

Sir,i am running .net1.1 on my system with sqlserver as my database.I am devaloping an e-trading project in
ASP.NET with VB.net.My problem is that,I am not getting the correct syntax to query the database with
SELECT(with WHERE clause)in vb.net.
Please help me.Please show your code and give the EXACT details of the error, inclusing message.

ASP.net app error after changing sa password

Hello,
I changed the sa password and now i get the error message below. I
cannot find the old password and do not know how to change it in the
system to allow it to succelfully authenticate again.
I'm running iis on a local network with an asp app. It tries to
connect to the sql database for the customer info.
Any help would be much appreciated. jho
error:microsoft OLE Provider for SQL Server (0x80040e4D) Login failed
for user 'sa'Hi
You should not be using sa as a login for a web application, the account
will be in the connection string which could be in one of several locations
see http://msdn2.microsoft.com/en-us/library/aa302392.aspx for more.
John
"jho" <jhogan0101@.yahoo.com> wrote in message
news:2c959016-a208-44f3-a148-702eddd12032@.v29g2000hsf.googlegroups.com...
> Hello,
> I changed the sa password and now i get the error message below. I
> cannot find the old password and do not know how to change it in the
> system to allow it to succelfully authenticate again.
> I'm running iis on a local network with an asp app. It tries to
> connect to the sql database for the customer info.
> Any help would be much appreciated. jho
> error:microsoft OLE Provider for SQL Server (0x80040e4D) Login failed
> for user 'sa'|||Hi
You should not be using sa as a login for a web application, the account
will be in the connection string which could be in one of several locations
see http://msdn2.microsoft.com/en-us/library/aa302392.aspx for more.
John
"jho" <jhogan0101@.yahoo.com> wrote in message
news:2c959016-a208-44f3-a148-702eddd12032@.v29g2000hsf.googlegroups.com...
> Hello,
> I changed the sa password and now i get the error message below. I
> cannot find the old password and do not know how to change it in the
> system to allow it to succelfully authenticate again.
> I'm running iis on a local network with an asp app. It tries to
> connect to the sql database for the customer info.
> Any help would be much appreciated. jho
> error:microsoft OLE Provider for SQL Server (0x80040e4D) Login failed
> for user 'sa'|||Hi
You should not be using sa as a login for a web application, the account
will be in the connection string which could be in one of several locations
see http://msdn2.microsoft.com/en-us/library/aa302392.aspx for more.
John
"jho" <jhogan0101@.yahoo.com> wrote in message
news:2c959016-a208-44f3-a148-702eddd12032@.v29g2000hsf.googlegroups.com...
> Hello,
> I changed the sa password and now i get the error message below. I
> cannot find the old password and do not know how to change it in the
> system to allow it to succelfully authenticate again.
> I'm running iis on a local network with an asp app. It tries to
> connect to the sql database for the customer info.
> Any help would be much appreciated. jho
> error:microsoft OLE Provider for SQL Server (0x80040e4D) Login failed
> for user 'sa'|||On Jan 13, 3:39=A0pm, "John Bell" <jbellnewspo...@.hotmail.com> wrote:
> Hi
> You should not be using sa as a login for a web application, the account
> will be in the connection string which could be in one of several location=s
> see =A0http://msdn2.microsoft.com/en-us/library/aa302392.aspxfor more.
> John"jho" <jhogan0...@.yahoo.com> wrote in message
> news:2c959016-a208-44f3-a148-702eddd12032@.v29g2000hsf.googlegroups.com...
>
> > Hello,
> > I changed the sa password and now i get the error message below. I
> > cannot find the old password and do not know how to change it in the
> > system to allow it to succelfully authenticate again.
> > I'm running iis on a local network with an asp app. It tries to
> > connect to the sql database for the customer info.
> > Any help would be much appreciated. jho
> > error:microsoft OLE Provider for SQL Server (0x80040e4D) Login failed
> > for user 'sa'- Hide quoted text -
> - Show quoted text -
I am trying to change to nt authentication but it seems like it has no
effect when i change it in the "logins" section in sql enterprise
manager and iis. FYI i am a network guy who knows very little about
dbs' and sql but trying. I was initially trying to access the db from
a network pc when i changed or maybe enabled the sa password to gain
access for a mail merge. How do i undo this?|||Hi
You would need to change the connection string in your ASP page to be
trusted authentication.
John
"jho" <jhogan0101@.yahoo.com> wrote in message
news:4b58f408-4194-4b19-bc8d-a60f46d5eb2e@.s8g2000prg.googlegroups.com...
On Jan 13, 3:39 pm, "John Bell" <jbellnewspo...@.hotmail.com> wrote:
> Hi
> You should not be using sa as a login for a web application, the account
> will be in the connection string which could be in one of several
> locations
> see http://msdn2.microsoft.com/en-us/library/aa302392.aspxfor more.
> John"jho" <jhogan0...@.yahoo.com> wrote in message
> news:2c959016-a208-44f3-a148-702eddd12032@.v29g2000hsf.googlegroups.com...
>
> > Hello,
> > I changed the sa password and now i get the error message below. I
> > cannot find the old password and do not know how to change it in the
> > system to allow it to succelfully authenticate again.
> > I'm running iis on a local network with an asp app. It tries to
> > connect to the sql database for the customer info.
> > Any help would be much appreciated. jho
> > error:microsoft OLE Provider for SQL Server (0x80040e4D) Login failed
> > for user 'sa'- Hide quoted text -
> - Show quoted text -
I am trying to change to nt authentication but it seems like it has no
effect when i change it in the "logins" section in sql enterprise
manager and iis. FYI i am a network guy who knows very little about
dbs' and sql but trying. I was initially trying to access the db from
a network pc when i changed or maybe enabled the sa password to gain
access for a mail merge. How do i undo this?

ASP.NET and IS

Hi,

I am new to IS.

How do I use ASp.NET to execute an IS package by invoking a SQL Server stored procedure? Is this a good idea in terms of system performance?

Thanks for the help!!

anyone?|||Hi IS Dude,

This is a good link to get you started. I've used the method involving the web service and it seems to work well.

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

Hope this helps,

Grant

ASP.net 2.0 Users (aspnet_Users table) create constraint problem

Hi all,
I am using ASP.NET 2.0 Membership system, which is driving me nuts. I
have my users, which I create correctly. Then I have a table "Models"
which contains models, created by different users. So I obviously need
to create a relationship between the users and the models. I wanted at
first to create a relationship like I'm used to do it, that is to add
an int field to my Model table that would be a reference on the primary
key of my Primary Table. However, as the primary key on the
aspnet_Users table is a uniqueidentifier, I decided to add a field
model_username instead. Then, I tried to create a relationship between
the aspnet_Users.UserName row and Model.model_username, but sql server
won't let me create it, I don't know why.
What is supposed to be the correct way to implement a constraint with
asp.net 2.0 users? I want to disallow the deletion of a user if he has
created a model.
Thank you,
ibiza
On 9 Mar 2006 11:41:46 -0800, ibiza wrote:

> However, as the primary key on the
> aspnet_Users table is a uniqueidentifier, I decided to add a field
> model_username instead.
Why? I assume you mean you added a field model_username to your new table
(not to the asp.net users table), right? If so, did you make sure it's the
same type and size as the aspnet username field (ie nvarchar(256))?
Also, primary keys must be unique, so if you can have mulitple models per
user, you need to have some kind of sequence number or other value in
addition to username to create your primary key.

> Then, I tried to create a relationship between
> the aspnet_Users.UserName row and Model.model_username, but sql server
> won't let me create it, I don't know why.
What is the error you are getting? It seems to work for me.

> What is supposed to be the correct way to implement a constraint with
> asp.net 2.0 users? I want to disallow the deletion of a user if he has
> created a model.
I'd create a uniqueidentifier in your table, and then create a foreign key
constraint on that. This is guaranteed unique, even if another user is
created with the same name.

ASP.net 2.0 Users (aspnet_Users table) create constraint problem

Hi all,
I am using ASP.NET 2.0 Membership system, which is driving me nuts. I
have my users, which I create correctly. Then I have a table "Models"
which contains models, created by different users. So I obviously need
to create a relationship between the users and the models. I wanted at
first to create a relationship like I'm used to do it, that is to add
an int field to my Model table that would be a reference on the primary
key of my Primary Table. However, as the primary key on the
aspnet_Users table is a uniqueidentifier, I decided to add a field
model_username instead. Then, I tried to create a relationship between
the aspnet_Users.UserName row and Model.model_username, but sql server
won't let me create it, I don't know why.
What is supposed to be the correct way to implement a constraint with
asp.net 2.0 users? I want to disallow the deletion of a user if he has
created a model.
Thank you,
ibizaOn 9 Mar 2006 11:41:46 -0800, ibiza wrote:
> However, as the primary key on the
> aspnet_Users table is a uniqueidentifier, I decided to add a field
> model_username instead.
Why? I assume you mean you added a field model_username to your new table
(not to the asp.net users table), right? If so, did you make sure it's the
same type and size as the aspnet username field (ie nvarchar(256))?
Also, primary keys must be unique, so if you can have mulitple models per
user, you need to have some kind of sequence number or other value in
addition to username to create your primary key.
> Then, I tried to create a relationship between
> the aspnet_Users.UserName row and Model.model_username, but sql server
> won't let me create it, I don't know why.
What is the error you are getting? It seems to work for me.
> What is supposed to be the correct way to implement a constraint with
> asp.net 2.0 users? I want to disallow the deletion of a user if he has
> created a model.
I'd create a uniqueidentifier in your table, and then create a foreign key
constraint on that. This is guaranteed unique, even if another user is
created with the same name.

ASP.net 2.0 Users (aspnet_Users table) create constraint problem

Hi all,
I am using ASP.NET 2.0 Membership system, which is driving me nuts. I
have my users, which I create correctly. Then I have a table "Models"
which contains models, created by different users. So I obviously need
to create a relationship between the users and the models. I wanted at
first to create a relationship like I'm used to do it, that is to add
an int field to my Model table that would be a reference on the primary
key of my Primary Table. However, as the primary key on the
aspnet_Users table is a uniqueidentifier, I decided to add a field
model_username instead. Then, I tried to create a relationship between
the aspnet_Users.UserName row and Model.model_username, but sql server
won't let me create it, I don't know why.
What is supposed to be the correct way to implement a constraint with
asp.net 2.0 users? I want to disallow the deletion of a user if he has
created a model.
Thank you,
ibizaOn 9 Mar 2006 11:41:46 -0800, ibiza wrote:

> However, as the primary key on the
> aspnet_Users table is a uniqueidentifier, I decided to add a field
> model_username instead.
Why? I assume you mean you added a field model_username to your new table
(not to the asp.net users table), right? If so, did you make sure it's the
same type and size as the aspnet username field (ie nvarchar(256))?
Also, primary keys must be unique, so if you can have mulitple models per
user, you need to have some kind of sequence number or other value in
addition to username to create your primary key.

> Then, I tried to create a relationship between
> the aspnet_Users.UserName row and Model.model_username, but sql server
> won't let me create it, I don't know why.
What is the error you are getting? It seems to work for me.

> What is supposed to be the correct way to implement a constraint with
> asp.net 2.0 users? I want to disallow the deletion of a user if he has
> created a model.
I'd create a uniqueidentifier in your table, and then create a foreign key
constraint on that. This is guaranteed unique, even if another user is
created with the same name.