Just a quick SQL Server Question

Hi

I'm developing a program that uses SQL Server 7. I've created the database using the developers version of SQL Server 7 that came with Visual Studio.

When I eventually deploy the application, the database will be run from MSDE.

My question is, how do I transfer my database from SQL Server 7 to MSDE?

I've tried the DTS Inport/Export but I get a message telling me that my licence doesn't allow me to use DTS.


Also, how can I administer MSDE and add password. MSDE doesn't have an Enterprise Manager.



Thanks.

The easiest way is to detach the database from the original server, copy the data files to the new machine, and then re-attach the database to the new server.

Example:

On the development server, running the VS version of SQL, detach the database by executing the following statement on the server:
sp_detach_db 'MyDatabase'

Copy the datafiles from the SQL server machine to the MSDE machine (normally \mssql7\data\MyDatabase.mdf and \mssql7\data\MyDatabase.ldf)

On the MSDE machine, execute the following statement on the MSDE server:
sp_attach_db @dbname = N'MyDatabase', @filename1 = N'c:\mssql7\data\MyDatabase.mdf', @filename2 = N'c:\mssql7\data\MyDatabase.ldf'

Note: The "N"s at the front of the quoted strings are required.

You can execute the commands against the servers using either a QADP or through the OSQL/Query Analyser interfaces.


If you want to add new users to the MSDE database, you will have to build your own interface around the default stored procedures provided with SQL/MSDE. Alternatively, if the MSDE machine is connected to the SQL machine via any form of network, you can use the Enterprise Manager to administer the MSDE machine remotely.

Hi

Thanks for replying.

I've managed to detatch the database using the following VB code:

Code:

---

Private Sub cmdDetatch_Click()


Dim objConn As New ADODB.Connection
Dim objCommand As New ADODB.Command
Dim Params As String
With objConn
    .ConnectionString = "Driver=SQL Server;Server=BWYSVR01;" & _
        "Database=PUBS;User ID=Administrator;Password=nbdtf3a8"
    .Open
End With

With objCommand
    .CommandType = adCmdStoredProc
    .CommandText = "sp_detach_db"
    .ActiveConnection = objConn
End With

Params = "mv"

objCommand.Execute , Params

Set objCommand = Nothing
objConn.Close
Set objConn = Nothing


End Sub

---

I've tryed to re-attatch it using:

Code:

---

Private Sub cmdAttatch_Click()


Dim objConn As New ADODB.Connection
Dim objCommand As New ADODB.Command
Dim Param(1 To 3) As Variant
With objConn
    .ConnectionString = "Driver=SQL Server;Server=BWYSVR01;" & _
        "Database=PUBS;User ID=Administrator;Password=nbdtf3a8"
    .Open
End With

With objCommand
    .CommandType = adCmdStoredProc
    .CommandText = "sp_attach_db"
    .ActiveConnection = objConn
End With

Set Param(1) = "@dbname = N'mv'"
Set Param(2) = "@filename1 = N'c:\mv.mdf'"
Set Param(3) = "@filename2 = N'c:\mv.ldf'"

objCommand.Execute , Param

Set objCommand = Nothing
objConn.Close
Set objConn = Nothing


End Sub

---

but it doesn't work. What modifications should I make? I'm new to SQL server and stored procedures.

I used the SQL Server 7 tutorial from this site for the basic syntax.



Thanks.

Hi

I've just managed to attach the DB using the Query Analiser.

However, it didn't bring any of the tables with it.

Help?

Hi

It's OK.

I've found a simpler way.

I just restore a backup of the database ontop of a new database, and make sure that it forces the restore.

you probably do not need the "N"'s when using a QADP to attach, I nneded them because I was using the OSQL interface.

Perhaps the tables had already been deleted inadvertantly as they are always there when I use this.