PDA

View Full Version : Attaching SQL database



ssharrock
07-11-2004, 09:05 PM
Hi all,

I am currently trying to write an installation package that installs MSDE and a copy of our SQL Server 7 database to a clients machine for evaluation .. I would like to know how to copy and configure the database side of things .. Is this done with scripting or some other process? ..

Thanks in advance,

Shane

BrianF
07-12-2004, 09:38 AM
I just went through this.

I found it to not be as smooth a process for getting a configured database onto a system.

What we ended up doing is using all .sql scripts and run them through the install using tools such as ado and osql.

When trying to use detach / attach (using stored procs) to get a database on to someone's system, I ran into a lot of unexplainable problems with database names and where the .MDF and .LDF files where supposed to be.

If you do go this route, be sure to test on a clean system and definately not the one you are developing with.

If anyone has been able to do this I would love to hear how you did it.

Brian

johnberndt
07-16-2004, 11:18 AM
Originally posted by BrianF
I just went through this.

I found it to not be as smooth a process for getting a configured database onto a system.

What we ended up doing is using all .sql scripts and run them through the install using tools such as ado and osql.

When trying to use detach / attach (using stored procs) to get a database on to someone's system, I ran into a lot of unexplainable problems with database names and where the .MDF and .LDF files where supposed to be.

If you do go this route, be sure to test on a clean system and definately not the one you are developing with.

If anyone has been able to do this I would love to hear how you did it.

Brian

Seems like a lot of people are having this problem. I wonder why Installshield doesn't support MSDE better as it is a common database system.

PilotBob
07-16-2004, 03:53 PM
I use the SQL Server view of InstallShield and have it script the database... during install it creates the db and runs the script. It should work on MSDE just as well as it works with SQL Server.

ssharrock
07-18-2004, 06:57 PM
Originally posted by PilotBob
I use the SQL Server view of InstallShield and have it script the database... during install it creates the db and runs the script. It should work on MSDE just as well as it works with SQL Server.

I had briefly tried this and had a error returned on build with the last stored procedure of the database .. I thought it was one particular stored proc and removed it and still got the error .. Will try again and see what I can find out ..

rei white
07-18-2004, 07:32 PM
I also encounter some problems when trying to create package with installshield x that install msde as well as attaching a database to the msde server. By default, installshield will run database attachment procedure before the msde installation. I would like to do it the other way around, which is installing MSDE then attaching dB. Any pointers anyone?

helmut
07-20-2004, 04:52 AM
I didn't use the built-in feature of InstallShield X for sql server maintenance. Instead I used the SQL-DMO interface, which is part of SQL Server installation.

rei white
07-20-2004, 06:51 PM
Hi there..
Thank you for the quick response. Would you be able to tell me how specifically you use sql-dmo to attach a database during msde installation. I kind a new on this matter. Once again thank you for any pointers given

rei white

helmut
07-21-2004, 02:15 AM
Here it is. This is a little piece of code from my current installation procedure. It will create a new database. But you may also attach an existing database file to the server installation.

You can use SQL-DMO to totally administrate your SQL Server installation. For further details on SQL-DMO have a look at the Microsoft knowledgebase (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_con01_8eun.asp)



function TmsCreateDatabase (strServerName)
object dmoServer;
object dmoDatabase;
object dmoDatafile;
object dmoLogfile;

string strDatabaseFilePath;

try
set dmoServer = CreateObject ("SQLDMO.SQLServer");
catch
MessageBox (Err.Description, SEVERE);
return;
endcatch;

if (! IsObject (dmoServer)) then
MessageBox ("Microsoft SQL-DMO is not installed on your computer!", SEVERE);
return;
endif;


try
dmoServer.LoginTimeout = 30;
dmoServer.LoginSecure = FALSE;
dmoServer.Connect (strServerName, "sa", "password");
catch
MessageBox (Err.Description, SEVERE);
endcatch;

if (! dmoServer.VerifyConnection (2)) then
MessageBox ("Unable to connect to database!", SEVERE);
set dboServer = NOTHING;
return;
endif;

if (dmoServer.Status () != 1) then
MessageBox ("Database not started! Service not running!", SEVERE);
set dboServer = NOTHING;
return;
endif;

set dmoDatabase = CreateObject ("SQLDMO.Database");

set dmoDatafile = CreateObject ("SQLDMO.DBFile");
set dmoLogfile = CreateObject ("SQLDMO.LogFile");

try
dmoDatabase.Name = "Eurotrans";

dmoDatafile.Name = "eurotrans.mdf.1";
dmoDatafile.PhysicalName = strDatabaseFilePath ^ "eurotrans.1.mdf";
dmoDatafile.PrimaryFile = TRUE;
dmoDatafile.Size = 30;
dmoDatafile.MaximumSize = 100;
dmoDatafile.FileGrowthType = 0;
dmoDatafile.FileGrowth = 10;

dmoDatabase.FileGroups ("PRIMARY").DBFiles.Add (dmoDatafile);

dmoLogfile.Name = "eurotrans.ldf.1";
dmoLogfile.PhysicalName = strDatabaseFilePath ^ "eurotrans.1.ldf";
dmoLogfile.Size = 1;
dmoLogfile.MaximumSize = 10;
dmoLogfile.FileGrowthType = 0;
dmoLogfile.FileGrowth = 1;

dmoDatabase.TransactionLog.LogFiles.Add (dmoLogfile);

dmoServer.Databases.Add (dmoDatabase);
catch
MessageBox (Err.Description, SEVERE);
endcatch;

set dmoLogfile = NOTHING;
set dmoDatafile = NOTHING;
set dmoDatabase = NOTHING;
set dmoServer = NOTHING;
end;

PilotBob
07-21-2004, 09:11 AM
Another thought... is it possible you didn't install MDAC on the machine? I am pretty sure that is what give you SQLDMO and osql. I'm not sure which InstallShield uses, but I assume one of them.

Try adding the MDAC 2.8 msm to your install and see if that makes a difference.

rei white
07-21-2004, 06:47 PM
Hi there..
Thank you for the quick response helmut. I m just wondering whether there are easier ways to configure this. The reason i use installshield x is because it has msde object predefined already, and i assumed that i can easily attached my sql script. I were able to attach the dB properly in user's end, however what i would like to do is to install msde to user's computer and attach the dB that i specified. It proves to be harder than i thought. Any suggestions anyone?

rei white

PilotBob
07-21-2004, 06:53 PM
Can you script out the database and data. IS X will run scripts automatically. This is what I am doing successfully.

Although, you could probably create a custom action that uses osql to do the attach fairly easily.

rei white
07-21-2004, 07:49 PM
Hi there..
Can you be more specific please. What do you mean by scripting the sql server?. Are you saying that i would be able to create a script for installing the msde using server configuration in IS X?. Can you suggest me on how to do this please. Thank you

rei white

PilotBob
07-22-2004, 01:29 PM
You should use the MSDE merge module to actually install MSDE.

Set up a SQL Server connection which allows you to script your db to a text file. When you install runs it will connection to the server and run the script.

However, you will probably need to do the MSDE install as a prerequisite since it will have to be there and running when your script runs.

BOb

rei white
07-22-2004, 06:12 PM
thank you bob.. Now, how do you run MSDE installation as a prerequisite before script attachment? because that is the main problem that i have right now. Once again thank you.

rei white

PilotBob
07-23-2004, 06:40 PM
I'm not 100% sure. In a Basic MSI project in the redistributables view there is an MSDE Prequisite item included (You have to download it, but it is there). You just select that and it happens automatically.

I assume there is a simmilar item in the InstallScript project type.

I'm sure someone else here will chime in.

BOb

Ingreyd
08-12-2004, 09:01 PM
I have to attach databases in my installation and I use ADO and the sp_attach_db command. See below

bResult = AttachSQLDatabase(TARGETDIR ^ "SQLDatabases\\PrintQueue.mdf", TARGETDIR ^ "SQLDatabases\\PrintQueue_log.ldf", SQLServer, "PrintQueue", SQLUser, SQLPassword);




export prototype BOOL AttachSQLDatabase(STRING, STRING, STRING, STRING, STRING, STRING);
//////////////////////////////////////////////////////////////////////////////
// Function: AttachSQLDatabase
// Description: Attaches a SQL database to the specified server
//
//////////////////////////////////////////////////////////////////////////////
function BOOL AttachSQLDatabase(sDataFile, sLogFile, sServer, sDatabase, sUser, sPassword)
OBJECT oCon, oCommand;
STRING sSQL, sConnection;
STRING sError, sErrorNumber;
NUMBER nError;

begin

try

SdShowMsg ("Attaching Database " + sDatabase, TRUE);

sConnection = "Provider=SQLOLEDB.1;Password=" + sPassword + ";Persist Security Info=True;User ID=" + sUser + ";Initial Catalog=" + "Master" + ";Data Source=" + sServer;

set oCon = CoCreateObject("ADODB.Connection");
oCon.Open(sConnection);

set oCommand = CoCreateObject("ADODB.Command");
oCommand.ActiveConnection = oCon;

sSQL = "sp_attach_db '" + sDatabase + "', '" + sDataFile +"','" + sLogFile + "'";

//Display Message

oCommand.CommandText = sSQL;
oCommand.Execute();

oCon.Close();

SdShowMsg ("Attaching Database " + sDatabase, FALSE);

return TRUE; //Return Value

catch

nError = Err.Number;
sError = Err.Description;
NumToStr (sErrorNumber, nError);

//WriteLog ("Attach Database Error: " + sErrorNumber, "Description: " + sError ,1);
SdShowMsg ("Attaching Database " + sDatabase, FALSE);

return FALSE;

endcatch;

end;

roberta.capucci
12-06-2004, 04:08 AM
Hi all,

I am currently trying to write an installation package that installs MSDE and a copy of our SQL Server 7 database to a clients machine for evaluation .. I would like to know how to copy and configure the database side of things .. Is this done with scripting or some other process? ..

Thanks in advance,

Shane

I have just read a lot of messages about attach databases.

I think the best solutions is to use sql script like this

EXEC sp_attach_db 'GPR001',
'*dir*GPR001.mdf',
'*dir*GPR001_log.ldf'

where GPR001 is my database.

The main question is: how i can start MSDE after installation?

thank you
Roberta

roberta.capucci
12-06-2004, 04:25 AM
Here it is. This is a little piece of code from my current installation procedure. It will create a new database. But you may also attach an existing database file to the server installation.

You can use SQL-DMO to totally administrate your SQL Server installation. For further details on SQL-DMO have a look at the Microsoft knowledgebase (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_con01_8eun.asp)



function TmsCreateDatabase (strServerName)
object dmoServer;
object dmoDatabase;
object dmoDatafile;
object dmoLogfile;

string strDatabaseFilePath;

try
set dmoServer = CreateObject ("SQLDMO.SQLServer");
catch
MessageBox (Err.Description, SEVERE);
return;
endcatch;

if (! IsObject (dmoServer)) then
MessageBox ("Microsoft SQL-DMO is not installed on your computer!", SEVERE);
return;
endif;


try
dmoServer.LoginTimeout = 30;
dmoServer.LoginSecure = FALSE;
dmoServer.Connect (strServerName, "sa", "password");
catch
MessageBox (Err.Description, SEVERE);
endcatch;

if (! dmoServer.VerifyConnection (2)) then
MessageBox ("Unable to connect to database!", SEVERE);
set dboServer = NOTHING;
return;
endif;

if (dmoServer.Status () != 1) then
MessageBox ("Database not started! Service not running!", SEVERE);
set dboServer = NOTHING;
return;
endif;

set dmoDatabase = CreateObject ("SQLDMO.Database");

set dmoDatafile = CreateObject ("SQLDMO.DBFile");
set dmoLogfile = CreateObject ("SQLDMO.LogFile");

try
dmoDatabase.Name = "Eurotrans";

dmoDatafile.Name = "eurotrans.mdf.1";
dmoDatafile.PhysicalName = strDatabaseFilePath ^ "eurotrans.1.mdf";
dmoDatafile.PrimaryFile = TRUE;
dmoDatafile.Size = 30;
dmoDatafile.MaximumSize = 100;
dmoDatafile.FileGrowthType = 0;
dmoDatafile.FileGrowth = 10;

dmoDatabase.FileGroups ("PRIMARY").DBFiles.Add (dmoDatafile);

dmoLogfile.Name = "eurotrans.ldf.1";
dmoLogfile.PhysicalName = strDatabaseFilePath ^ "eurotrans.1.ldf";
dmoLogfile.Size = 1;
dmoLogfile.MaximumSize = 10;
dmoLogfile.FileGrowthType = 0;
dmoLogfile.FileGrowth = 1;

dmoDatabase.TransactionLog.LogFiles.Add (dmoLogfile);

dmoServer.Databases.Add (dmoDatabase);
catch
MessageBox (Err.Description, SEVERE);
endcatch;

set dmoLogfile = NOTHING;
set dmoDatafile = NOTHING;
set dmoDatabase = NOTHING;
set dmoServer = NOTHING;
end;


I'm reading your post above, plesase can you say me how i can use your example?

Our main problem is to startup MSDE services after installation and continue with the application's installation.

than you in advance
roberta

Perucho
12-06-2004, 12:03 PM
The SQLServer object contains the objects and collections that implement SQL Server administrative tasks for SQL-DMO. The object allows SQL-DMO applications to connect to a server running SQL Server by name, establishing the context for administrative tasks.

SQLServer Object (SQL-DMO)
http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/9_dmor19_5.htm

MS SQL Server 7.0
http://www.schemamania.org/jkl/booksonline/SQLBOL70/

Start Method (SQLServer) (SQL-DMO)
http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/9_dmor81_22.htm
Remarks: the Start method can only be used on a SQLServer object not connected to a SQL Server installation.

Perucho
12-06-2004, 03:58 PM
Start SQL Service Manager
http://community.installshield.com/showthread.php?t=59978

You can use "net.exe" to start a local MSSQL Server:
net.exe start MSSQLServer


You can use SQL Server Service Control Manager (SCM) Utility:
scm.exe -Action 1
scm.exe -Action 1 -Server servername -Service MSSQLSERVER



How to Start SQL Agent in MSDE?
http://community.installshield.com/showthread.php?t=135587
http://community.installshield.com/showpost.php?p=282605&postcount=2


You can use "SQL-DMO":
http://community.installshield.com/showpost.php?p=95573&postcount=3

Here is something I put together without testing. Let me know if it works or not.


function BOOL startSQLServer (strServerName)
object dmoServer;
NUMBER nCounter;
BOOL bStartMode;
begin
try
set dmoServer = CreateObject ("SQLDMO.SQLServer");
catch
MessageBox (Err.Description, SEVERE);
return FALSE;
endcatch;

if (! IsObject (dmoServer)) then
MessageBox ("Microsoft SQL-DMO is not installed on your computer!", SEVERE);
return FALSE;
endif;

bStartMode = TRUE;

if(dmoServer.Status () != 1) then
dmoServer.Start(bStartMode, strServerName, "sa", "password");
else
return TRUE;
endif;

nCounter = 0;
while (dmoServer.Status () != 1)
nCounter++;
delay(2);
if(nCounter = 30) then
MessageBox("SQL Server cannot be started!", SEVERE);
return FALSE;
endif;
endwhile;

// free object
set dmoServer = NOTHING;

return TRUE;

end;

Perucho
03-31-2005, 10:46 AM
To start the MSSQLSERVER service:

You can use the InstallScript built-in functions:




if( ServiceExistsService(MSSQLSERVER) ) then

ServiceGetServiceState(MSSQLSERVER, nvServiceState);

if( nvServiceState != SERVICE_RUNNING ) then
ServiceStartService(MSSQLSERVER, "");
else
MessageBox("MSSQLSERVER service is running!", INFORMATION);
endif;

else

MessageBox("MSSQLSERVER service does not exist!", INFORMATION);

endif;


ServiceExistsService ( szServiceName );
ServiceGetServiceState ( szServiceName, nvServiceState );
ServiceStartService ( szServiceName, szStartServiceArgs );
ServiceStopService ( szServiceName );


Or You can use SQL Server Service Control Manager (SCM) Utility: (calling LAAW())
scm.exe -Action 1 -Server servername -Service MSSQLSERVER

mistyboy
10-12-2005, 01:33 PM
Anything For installing DB Scripts on ORACLE, DB2 and other Databases. Please help..