Detaching DB From MSDE And Attaching To SQL Express Using SQLDMO.
Dec 19, 2007
All,
I wanted to to detach a DB say MyDB from MSDE and wanted to attach it to SQL Express.
It could able to Detach the DB from MSDE,but attachment to SQL 2005 is failing. When I verified MSDE Instance through Managment studio "MyDB" wasnot there ; means it got detached.I couldnt find the DB in SQL 2005. When I tried manually through command prompt I was able to attach "MyDB" to SQL2005.
Please help me out!!
Here is the code which I have written.
if SUCCEEDED(hr = CoCreateInstance (CLSID_SQLDMOServer, NULL, CLSCTX_INPROC_SERVER,
IID_ISQLDMOServer, (LPVOID*)&pSQLServer))
{
pSQLServer->SetLoginSecure (TRUE);
SQLDMO_LPCSTR DB = OLESTR("MyDB");
//Connect to MSDE First
if SUCCEEDED(hr = pSQLServer->Connect (szDbServer, szUserName, szPassword))
{
pSQLServer->DetachDB(DB,lpszResult); //Detach the DB
pSQLServer->DisConnect(); //Disconnect MSDE
LPBSTR lpszResult2 = NULL;
long lNumDB;
//Connect to SQL 2005
if SUCCEEDED(hr = pSQLServer->Connect (szSQLExpDbServer, szUserName, szPassword))
{
//LPCOLESTR lpo = A2COLE(sTemp);
//sTemp.operator LPCTSTR
pSQLServer->GetDatabaseCount (&lNumDB);
SQLDMO_LPCSTR files = OLESTR("C:\Program Files\Microsoft SQL Server\MSSQLData\MyDB.mdf,C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDB_log.ldf");
pSQLServer->AttachDB(DB,files,lpszResult2); // Attach the DB to SQL 2005
pSQLServer->DisConnect(); // Disconnect SQL 2005}
View 1 Replies
ADVERTISEMENT
Sep 5, 2007
I have SQL Express 2005 SP2 installed in my Vista Home Premium. I am using SQL Server Management Studio Express to manage .mdf.
When I click "Add..." to attach .mdf database, I have this prompt error message :
The server principal "My-PCMyName" is not able to access the database "model" under the current security context. (Microsoft SQL Server, Error: 916)
I have elevated the rights, "Run as administrator", of my SQL Server Management Studio Express, but still having problem.
I also encounter security error when detaching the existing .mdf :
The EXECUTE permission was denied on the object 'sp_detach_db', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Please advice. Thanks.
View 8 Replies
View Related
Dec 10, 2006
Hi,
Im not new to SQL Server but I am new to v.2005. We have done a class project and to finish up I am creating schemas, logins and different user roles and permissions.
I now need to detach the DB and send it to our team leader for submission. I'm scared to do this because I don't know if all the logins and passwords I've created will appear in it's new location. Will they be there in the new server's security folder just as I created them or will they dissapear into the ether?
Signed:
Sweating very much
It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity
View 7 Replies
View Related
Jun 18, 2004
Why does it take time to detach or attach a Db sometimes ?
View 4 Replies
View Related
Jul 20, 2005
Q1.)In SQL Server 2000, is it always possible to use copies of thedata and transaction log files of a database from one server toreattach to a new database on another server, or even to the sameserver, without first detaching from the existing database?Books Online says that detaching a database closes the data and logfiles cleanly. I read some where that some times, it is possible toattach without detaching, if the data and log file are in stablestate.This is what I tried - Tried to copy Northwind.mdf and ldf files usingwindows explorer and it gave an error message that the files are inuse. I shutdown the server and now I could easily copy them. Afterthat I renamed these files and successfully attached them as a newdatabase.Q2.)Is it possible to do the same with large production databases?Q3.) Please explain what goes on during detaching /attaching process.Thanks in advanceRavi
View 1 Replies
View Related
Apr 9, 2008
I am currently having a problem in the implementation of a Database for my C#.net winform application.
Look at the 2 diagrams below:
Diagram 1 : A typical databse design where it usees only 1 mdf file.
Diagram 2: A new proposed approach to implement the Database with multiple mdf files of the same schema!!
In the new proposal of the DB, each mdf files is kept separately and an additional module is required to attach and detach these databases as and when required. The purpose: the performance of all actions on the small data is not affected by the size of the large data
How does this 2 methods measure up in terms of resource usage, performance, etc??
Need comments and views. Thanks.
View 9 Replies
View Related
Mar 22, 2008
I hope this is the right forum for this question.
I am working with an asp.net project in Visual Web Developer 2005 Express. This is a starter kit project. There is a database that came with the project that I want to open up in the SQL Server 2005 Express management studio so that I can modify it.
I have detached the db in the VWD environment.But, I do not see this db in the db folder with in the SQL Server studio's IDE. I am assuming that I have to attach the database to the SQL Server management studio in order to work with it. Is this the correct way to do this type of thing? Is there a better way of doing it(a stored proc like mentioned below)?
I also have a question about the icons that pertain to this situation. I have noticed two variations of the same icon. I want to be sure I am interpretting these correctly. I am assuming that the icon that is a cylinder representing a db with the little
electrical plug coming out from behind it means that the db is attached. There is another version of this icon that has a red "x" over the plug. I am assuming that this means it is detached. Is this correct?
I have attached and detached dbs in Sql Server Express. Is the process the same for doing this with a project in Visual Web Developer? Is there a stored proc that I can use to detach from the SQL Server 2005 environment after I have made a database and attach this to my project in VWD in one step?
-thanks in advance
View 4 Replies
View Related
Jul 3, 2006
I'm trying to attach a MSDE 2000 database to SQL Server 2005 Express.
I simply issue sp_detach_db XXX where XXX is the name of my MSDE database. Then sp_attach_db or create database ... with attach and both way gets the following message:
Event ID. : 3415
Description: Database 'XXX' cannot be upgraded because it is
read-only or has read-only files. Make the database or files writeable, and
rerun recovery.
What does it mean?
Thanks for any help,
Peter
View 4 Replies
View Related
Dec 5, 2007
Hi
I have to migrate a SQL2000 database to SQL2005, I tried to use CopyDatabase Wizard (CDW) but invariably stopped all processes (may be for permissions, but I really I don€™t know) so I decided to detach de database, copy files to new server and attach it to SQL2005, it apparently works well, except for the fact that Schemas where created with the name of the users of SQL2000.
Is that correct?
Could it affect the performance or any other thing?
Is correct to do what I did?
Could be another problem in the recently attached database?
(Compatibility, data loss, corrupted structures, collation or something like that)
The server I am using is in a domain, it has windows server 2003 R2 SP2
@@Vesion returns
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
And in the source server with windows 2000 SP4
@@Vesion returns
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Thank you in advance
Any comment will be greatly appreciated
JAPonce
View 4 Replies
View Related
Apr 15, 2007
I've been using my DotNetNuke database for weeks on my local PC with SQL Server Express. It attaches as a "User Instance". Basically, I am using all the DotNetNuke defaults. Today I decided to attach the DotNetNuke database in SQL Server Management Studio Express and peek at the tables. I attached the database; then I detached it when I was done. This all went well. But when I tried to open my DotNetNuke web site again I now get a message that it cannot connect. Attaching and detaching the database in SQL Server Management Studio obviously changed something about the database, but I don't know what. My connection string to the database has not changed.
The specific error displayed in the browser is:
ERROR: Could not connect to database specified in connectionString for SqlDataProvider
In event viewer is this message:
Exception information:
Exception type: SqlException
Exception message: Cannot open user default database. Login failed.
Login failed for user 'NT AUTHORITYNETWORK SERVICE'.
My connection string is this:
<add name="SiteSqlServer" connectionString="Data Source=.SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Database.mdf;" providerName="System.Data.SqlClient" />
Any suggestions about how I can correct this?
View 3 Replies
View Related
Oct 14, 2004
Hi all;
I created a DB in MS SQL Server 2000.. (has Tables, SPs, Views..etc.) and I want to attached to a customer that has ONLY MSDE?
How?
Also, how can my customer make Backup & Restore from his MSDE? (BTW: can I do that in code (VB.NET/ASP.NET)??
Thanks in advanced!
View 1 Replies
View Related
Jun 14, 2007
I am having trouble attaching a db to ms sql express 2005.I have the db in a folder deep down in my drive, within an asp.net website project. For some wierd reason i can't drill down more than two levels to get to the DB Anyone have any ideas why? Ilan
View 3 Replies
View Related
Nov 23, 2006
Hi,
I have an mdf without an ldf. This was originally a sql 2000 db, since it was not working, I created a blank SQL Express DB and restored a backup over it.
I then detached and deleted the ldf as I used to do with MSDE.
The database will not attach on most machines (file access error for the ldf) even with attach_single_file_db
When I try to attach it on another machine of mine (with sp1 installed) it attaches fine. could this be something SP1 related?
View 1 Replies
View Related
Aug 4, 2006
I'm trying to attach a database to sql express with the following code from the management tool. I keep getting an error message. An someone explain what I'm doing wrong.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '<'.
IF NOT EXISTS(
SELECT *
FROM sys.databases
WHERE name = N'<northwind.mdb, I4V0Y6SQLEXPRESS, northwind>'
)
CREATE DATABASE <database_name, sysname, your_database_name>
ON PRIMARY (FILENAME = '<c:my documentsmy websmyweb3,,C:Program filesMicrosoft SQL ServerMSSQL.1MSSQLDataorthwind.MDF>')
FOR ATTACH
GO
Ira
View 1 Replies
View Related
Apr 14, 2007
Howdy Folks,
I have a site on WS2003, IIS 6 installed on c:.
I put the site in a folder on d:. Nothing else is on d: except the web site.
I added a new website in IIS and pointed to the site on d:.
Using the default connectionString in machine.config, I am unable to connect to ASPNETDB.MDF. Therefors, I can not log in.
In SQL Server Management Studio Express, I attached ASPNETDB.MDF. I then am able to log in.
Does ASPNETDB.MDF have to be manually attched like this when the web site is located on a drive othr than where IIS is installed and running?
Thanks
View 6 Replies
View Related
Mar 16, 2007
Hi,
Im working in a proyect where i need to attach a mdf database to the client's SQL express instance. How can i accomplish during installation and without getting the user involved.
Any help will be greatly appretiated
View 3 Replies
View Related
Apr 18, 2006
Hello,
I am using the full version of sql server 2005 management studio to attempt to attach sql express mdf files to sql server 2005.
I was using the databases from an ASP.NET commerce starter kit in sql express. I want to update to sql server 2005, so i detached the dbs.
i deleted the sql express management studio ctp and installed sql server 2005 from the free 6 month trial available online.
I then attempted to attach the sql express files to sql server 2005 and get the following error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Attach database failed for Server '<servername>'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Could not find row in sysindexes for database ID 9, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
Could not open new database 'C:INETPUBWWWROOT<location>ASPNET.MDF'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476
I also tried with the original mdf files i had downloaded without making any changes.
any help here?
thank you,
K
View 1 Replies
View Related
Apr 11, 2007
Hi there,
I have created an .iss file which when compiled creates a setup.exe file on a cd rom. Within the .iss I have extracted the sqlexpress files out so that it will install straight from disk to the users machine rather than from the .msi file. All this has been successful so far....
My problem is:- I want to attach a database to the an instance of sqlexpress on the users machine. I have detached it from SQL Server 2000 and copied it over to the cd. The setup file copies these over the users hard drive. I have looked through many forums etc and the words that keep popping up are:-
sqlcmd.exe
template.ini
type into the command prompt.
Sorry if I'm a little ignorant but I am not sure how to go about these. How do I open the command prompt on the users machine from CD? Do I have to create something to do this?
Any information would be appreciated, any guidance or scripts would be great.
Regards
Sam
View 2 Replies
View Related
Mar 21, 2007
Hi,
I need to install a customed SQL server express instance on the client's computer by using the Command prompt installation. I've already defined the security settings and everything i need but i was wondering if is possible to attach a mdf database during the installation. Perhaps there is a paramenter i can include in the in the ini file.
Thank you
View 6 Replies
View Related
Jun 29, 2007
Hi,
I've created a database in sql server 2005, but now I need to detach this database and attach it in sql express. Is this possible? I keep getting an error that my database is readonly. Any help would be greatly appreciated.
thanks in advance.
View 3 Replies
View Related
Oct 18, 2006
If I create a database in SQL Express and then copy the MDF/LDF files and attach them to a machine running SQL Server 2005 Standard - will this new database have SQL Express limitations?
Are there good reasons not to do this and create the database in SQL Server Standard using scripts generated from the SQL Express instance instead?
Basically, I'm setting up a merge replication between Standard and SQL Express for a common database and I figure attaching a copy of the database created in SQL Express is the fastest way to get this started.
View 3 Replies
View Related
Oct 9, 2006
Hey all,
Cool place! Has anyone deployed SQL Express silently using one click and an attached a smart client DB from within the app ... would love to see some of the best practices or horror stories! Just kidding... :) I'm about to deploy a smart client using SQL Express and could use some tips from someone who has been there.
Thanks in advance,
Bill
View 7 Replies
View Related
Sep 14, 2006
Are there any concerns for upgrading from MSDE to Express? Anything that has to be done to the database itself?
I will be uninstalling MSDE completely so I can reuse the instance name to minimize issues with other programs already setup for it. This means that I will be installing Express, basically, clean on the machine. What I am worried about is anything that needs to happen to the db itself, changing compatability levels and such.
Thank you in advance for any help.
View 1 Replies
View Related
Mar 21, 2006
Hi ....I am a developer and building an enterprise level application for general purpose. I need to include a database in that. I am working in Visual Studio .NET 2003 having Framwork 1.1 installed. Now what should I do? is it necessary to have visual studio .NET 2005 and Framwork 2.0 to work with SQL Server Express? can any one help?
Amir
View 1 Replies
View Related
Oct 30, 2006
I know this may have been asked in the past, and I've tried to search the forum, but didn't find any clear responses.
The question is about MSDE and SQL Express being "Free".
Can MSDE or SQL Server express be used for the backend DB for an application for a private (for profit) company? A COTS that we are planning to puchase can work with either commercial version of SQL server or MSDE / Express. Any input would be appreciated.
Thank you,
View 4 Replies
View Related
Mar 9, 2007
We have a product on the market, with 100k or so installs that use MSDE as its database (desktop application in .net). Our number 1 complaint is massive installation problems with MSDE.
Now, we are stuck a bit - first, we need to release a Vista Compatible version - which means no MSDE. We also need to migrate users from MSDE to SQL Express, with no data loss (we deal with financial information, and people tend to have zero tolerance for data loss here).
So, I have large scale reservations for two reasons - first, Microsoft's track record in supporting and making MSDE in the past has not been stellar.
With that said - are the multiple installation failure issues fixed in SQL Express? Did MS make the effort to get SQL Express to install on Win2k/XP/Vista improved? Are their benchmark figures out there regarding SQL Express install failure rates?
Additionally, can I get some information regarding MSDE to SQL Express upgrades done during for existing customers?
jason
View 1 Replies
View Related
Jan 11, 2007
I need to know if I will run into problems with sql express on a sql 2000 server. I get the impression that my isp is using 200 server just by lookng at the connection string. Is there a addin I could use if needed I seen a post mentioning the publishing wizard cpt but not much after is it released yet. and then there is the web data admnistrator for msde will that work with sql express.One other question can I reinstall sql and change the authentication process from windows to user name and password.
DKB
View 2 Replies
View Related
Feb 25, 2006
Hi - I have an MSDE database running on my server - I'd like to port my asp.net 1.1 app to asp.net2, and make use of SQL 2005 Express.How do I transfer my data from an MSDE database to SQL Server Express, keeping intact all of my unique ID fields, indexes, and SPs (I don't have any functions or views)Thanks for any help,Mark
View 4 Replies
View Related
Mar 7, 2008
Not sure where to poswt this but I have a question. Is it possible to downgrade a remote SQL instance (the RTC database for LCS 2005 EE) to MSDE / SQL EXPRESS running on the LCS node?
I have two LCS2005EE pools side by side in the same account domain. The company recently went through a split where a part of our operation was spun off to another company. We're trying to figure out a way to leave them a working LCS but cut our hosted server count. What I came up with was basically 3 or 4 possible scenarios:
Leave only two servers (LCS and SQL.)
Try to move and run two instances on new production equipment
Try to downgrade the remote RTC instance into MSDE / SQL EXPRESS and leave one server.
Any ideas would be greatly appreciated.
Joe H. Wise
View 7 Replies
View Related
Oct 1, 2007
Hi everyone!
We are trying to restore a database backup created o MSDE 2000 server to SQL Express 2005.
The collation settings of both SQL Express and MSDE are the same: Turkish_CI_AS
The problem is that the log of restore operation has some warnings like:
Processed 344 pages for database 'MYDB', file 'MYDB_Data' on file 1.
Processed 1 pages for database 'MYDB', file 'MYDB_Log' on file 1.
Converting database 'MYDB' from version 539 to the current version 611.
Database 'MYDB' running the upgrade step from version 539 to version 551.
Warning: The index "myt_idx01" on "dbo"."myt" may be impacted by the
collation upgrade. Run DBCC CHECKTABLE.
Warning: The index "PK_BankLDef" on
"dbo"."BankLDef" may be impacted by the collation
upgrade. Run DBCC CHECKTABLE.
Warning: The index "AgreeHist_idx1" on
"dbo"."AgreeHist" may be impacted by the collation upgrade.
Run DBCC CHECKTABLE.
....
....
Database 'MYDB' running the upgrade step from version 551 to version 552.
Database 'MYDB' running the upgrade step from version 552 to version 553.
Database 'MYDB' running the upgrade step from version 553 to version 554.
Database 'MYDB' running the upgrade step from version 554 to version 589.
Database 'MYDB' running the upgrade step from version 589 to version 590.
Database 'MYDB' running the upgrade step from version 590 to version 593.
Database 'MYDB' running the upgrade step from version 593 to version 597.
Database 'MYDB' running the upgrade step from version 597 to version 604.
Database 'MYDB' running the upgrade step from version 604 to version 605.
Database 'MYDB' running the upgrade step from version 605 to version 606.
Database 'MYDB' running the upgrade step from version 606 to version 607.
Database 'MYDB' running the upgrade step from version 607 to version 608.
Database 'MYDB' running the upgrade step from version 608 to version 609.
Database 'MYDB' running the upgrade step from version 609 to version 610.
Database 'MYDB' running the upgrade step from version 610 to version 611.
RESTORE DATABASE successfully processed 345 pages in 0.420 seconds
(6.712 MB/sec).
What is the severity of this warnings? Is there a way to make them disappear?
Thanks in Advance.
Adnan
View 1 Replies
View Related
Nov 9, 2005
I posted previously about concerns of using SQL Server 2005 Express in a shared hosting environment that is running SQL Server 2000. While I've received no responses I'm beginning to assume that an .mdf file created by mssql 2005 cannot be attached to mssql 2000. So I have a few questions...How do I set Visual Studio to create the aspnetdb file using an instance of msde instead of sql server express 2005? I've already tried changing the Instance name under Tools...Options...Database Tools...Data Connections to my msde instance but when I create a new site and use the the website configuration tool a sql 2005 db is still created in the App_Data folder.Do I need to uninstall 2005 express? Manually create and place a new .mdf file in the App_Data folder?
View 1 Replies
View Related
Dec 15, 2005
I have heard that machines running applications with embedded MSDEfrequently experience conflicts between the MSDE apps and otherapplications such as BackupExec. What can anyone tell me about this?And if this is true, does/will the new SQL Server Express Editionbehave better?
View 1 Replies
View Related
Feb 2, 2007
I posted this in one of the VB forums but I'm starting to think itmight be more appropriate to have it here, since it really seems to bea SQL server (MSDE/Express 2005) problem:Hey, all, I have a problem with queries against a SQL server instancethat I just found and is causing me fits. I hope someone can pointmein the right direction, please. TIA.Basically, I got a Vista OS machine to test my VB6 app on it as someof my clients will be switching over in the coming months. I went toa local Circuit City during early business hours in the middle of theweek and I installed my application on each of 5 PC's on the newVistaOS (Tuesday, when it was released). I had read that MSDE 2000, whichI normally use as my DB is not supported on Vista so I had downloadedand was using SQL Express 2005. Each system had at least a 1.9 GHzdual core processor and 1 GB of RAM. One process in my program findsrecords in one table that do not match records in another table andthen reports those un-matched entries. On my development machine(laptop with 1 GB of RAM, XP Pro SP2, MSDE 2000 (current SP), 2 GHzCentrino (IIRC)) the process takes less than 30 seconds consistently.On each of those 5 systems at Circuit City the process took 5 minutes(on each of 3 HP machines, a1700n, a1720n, a1730n, and 11 minutes oneach of two Gateway systems (the model numbers of which I forget atthe moment). Each of these computers should be much faster than mylaptop, and some had twice the RAM, and all had SATA or SATA IIdrivesinstead of my piddly 5400 laptop drive, I would have thought they'dall be faster but were abysmally slow.So, seeing a huge difference in the time, and to try to keep thisshort and sweet, I fired up another computer I have, running XP SP2,on 512 MB RAM, AMD Athlon 2300+. First I loaded MSDE 2000 and myapplication and ran the process. < 30 seconds on each of multipleruns. Second, I unloaded MSDE 2000 and installed SQL Express 2005andmoved the DB to it (sp_attach_db) which caused some upgrading(messages reported in OSQL about update/upgrade). When it was done Irebooted, to be sure, and the ran the program and the process again.On the same data, on the same computer, the process took 7-9 minutesconsistently on each of several runs. This makes this part of theapplication unusable, and even the simple stuff like grabbing asinglerecord from the DB (maybe 5 columns of no more than 500 bytes total)is noticeably slower on the SQL Express 2005 than on MSDE 2000.So, the problem seems to be with my interaction with the DB. I amusing ADO 2.8 in VB 6 (SP 6). I use DSN-less connections with aconnection string like: Driver={SQL Server};server=(local)caredata;database=caredata;Uid=sa; Pwd=<password>I use the RecordsSet Object to open the data similar to this:oRS.OpenstrSQL$, oCN, adOpenKeysetafter the oCN object has had the connection string set and the objectis opened.Considering that the same computer, against the same data, with thesame program, takes about 14 times (or more) longer to run, then ithas to be either that SQL Express 2005 is slow OR that my program isinteracting with it in an incorrect manner.Can someone point me in the right direction, please?Thank you.--HCSo, the problem isn't Vista
View 11 Replies
View Related