MSDE Attach/Detach

[Roo2000]

I am attempting to detach/attach a dB from one disk to another. I understand that the System Stored Procedure must be run from the Master Database, but because I am using Access 2000/MSDE, I don't have the Enterprise Manager. As a result, I cannot find my way into the Master Database! This is hopefully a foolish little problem, but I am stumped. Any help will be appreciated. Thanks

 

[tlbroadbent]

You can execute the stored procedure using the fully qualified name of the procedure.

exec master.dbo.sp_detach_db 'family'

exec master.dbo.sp_attach_db 'family',
'C:\MSSQL\Data\Family.mdf',
'C:\MSSQL\Data\Family.ldf'

Or you can also change the database context of teh connection with the USE statement.

Use master
exec sp_detach_db 'family' Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Roo2000]

Thank you Terry. I knew that it must be a simple answer, but finding an answer is like the "needle in the hay stack" thing. Bob

 

[Roo2000]

I was hoping to have this issue resolved, but I just can't seem to get out of the gate. I must be doing it wrong. I open my project and enter the above code into stored procedures. In attempting to save it, I receive either an "incorrect parameter" or "you can't change the object type" message. An annoying question that I have is this: A procedure call to detach a dB from the Master makes sense to me, but how do I detach the dB that I'm calling the procedure from? To clairify this, if I was removing Pubs, it doesn't make sense to me to first load Pubs and then enter the procedure to detach it. Maybe that is the way it is done, but it seems odd that a dB should detach itself. I tried it from an alternative dB just to see, but that doesn't work either.

One version of code obtained from SQL Server magazine does at least save for me, but I always receive the "Database not here" error.

Alter Procedure msp_DetachLocalPubs
AS
IF (SELECT Name FROM master..sysdatabases WHERE Name='pubs')
IS NULL
RAISERROR 50000 'Database not here.'
Else
Exec sp_detach_db 'pubs'

I do of course replace Pubs with my dB name, which only leads me to another question: In the sql7/data file my dB file are all concantenated SQL, Do I include this addition? I'm also uncertain about this code because I havn't found "DetachLocal" in BOL or any of my other references.

Another bit of code I tried with the same result was this:

EXEC sp_detach_db @dbname = 'DatabaseName' , @skipchecks = 'true'

I've invested a lot of hours trying to solve this. If you can help me, please assume that I know next to nothing, because that is close to the truth.

At least the attach code seems that it may work, I tried and at least got the intelligent response that the dB was already attached, but then, I was calling it from the dB that I was suppose to be attaching. This approach has really got me confused. Any help?

 

[tlbroadbent]

I think I get a clearer picture now.

1- sp_detach_db and sp_attach_db are system stored procedures and don't require qualification. you can call them from any database context. I apologize for not thinking clearly on that matter in my first reply.

2- You can't detach a database if you or anyone else is using it. You still need to change the database context so you do not have the database you want to detach open. The USE statement might help but USE will not work if executed in a stored procedure. The best thing you can do is make sure you open the connection to SQL Server/MSDE in the context of an alternate database and then execute sp_detach_db. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Roo2000]

Terry, please be patient with me. You have no idea about my own frustration. I have to get this done soon. I am trying to write the following procedure from a alternate db:

Exec sp_detach_db'PPL','true'

When trying to save the procedure I get the following error:

"You cannot change the object type in a script"

I can't find anything that helps, can you?

 

[tlbroadbent]

I'm not familiar with Access Projects using MSDE. I just created my first project today to see if I can duplicate the error you are getting. The problem is I don't know how you are creating the code. Is it in a Stored Procedure in the project?

In the meantime, I tested another method. I created a VB function in my project that runs sp_detach_db. It seems to work fine. I'm sure there are other methods and perhpas those who have worked with Access Projects more than I can suggest a better method.

Create the following function in a VB module.

Function DetachDatabase(strDB As String) As Boolean
Dim strSQL As String
strSQL = "Use Master; Exec sp_detach_db '" & strDB & "'"
DoCmd.RunSQL (strSQL)
DetachDatabase = True
End Function

You would want to add error handling and perhaps code to confirm that the detach actually occurred. Let me know if this helps. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.