Title: Attach and Detach MSSQL database or MSDE
Question: how can i attach or detach an MSSQL database ?
Answer:
/************************************
//Attach and detach an MSSQL database
//or MSDE using ADO in Delphi 7.
//
//by Joseph Fuchaka Barasa
//jfuchakab@yahoo.com
//
//Helps one to work with database objects
//(tables, queries, stored procedures
//etc) using free version of MSDE.
//without using the costly Enterprise
//version of the database.
************************************/
procedure detach_database;
var
strcommand, detachedDB: string;
begin
detachedDB:=trim(EditDetachedBName.Text);//database name
strcommand:='EXEC sp_detach_db @dbname = '+detachedDB+'';//detaching
with AttachConn do begin//AttachConn is ADOConnection
close;//attach on the master system database.
//computer_name is the name of your computer
ConnectionString:='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog='+'master'+';Data Source='+computer_name+'';
open;
end;//with attachconn
with qryDetach do begin//qryDetach is AdoQuery
close;
sql.Clear;
sql.Text:=strcommand;
try
ExecSQL;
ShowMessage('Detach was Successfull');
Except on E:Exception do begin
Showmessage('Error Dettaching the Database');
Application.Terminate;
end;
end;//try - except
end;
end;//detach
procedure attach_database;
var
strcommand, dbase_Name, file1, file2: string;
begin
//database name
dbase_Name:=trim(EditAttachedDBase.Text);
file1:=trim(EditDatabaseFIle.Text);//database file
file2:=trim(EditLogFile.Text);//log file
with AttachConn do begin
close;//connect to master system database
ConnectionString:='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog='+'master'+';Data Source='+computer_name+'';
open;
end;//with attachconn
//sql commands for attaching
strcommand:='EXEC sp_attach_db @dbname = '+dbase_Name+', ';
strcommand:=strcommand + '@filename1 = "'+File1+'"'+', ';
strcommand:=strcommand + '@filename2 = "'+File2+'"'+'';
with qryAttach do begin
close;
sql.Clear;
sql.Text:=strcommand;
try
ExecSQL;
ShowMessage('Database Successfully attached');
Except on E:Exception do begin
Showmessage('Error Attaching the Database');
Application.Terminate;
end;
end;//try - except
end;//with query attach
end;
/******************************************************
//questions, improvements, criticism,
//and suggestions should be send to :
//jfuchakab@yahoo.com
//the zip of the project is attached
//have a nice day.
//*****************************************************