Just a quick SQL Server Question

[PaulB]

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.

[Orpheus]

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.

[PaulB]

Hi

Thanks for replying.

I've managed to detatch the database using the following VB 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:

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.

[PaulB]

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?

[PaulB]

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.

[Orpheus]

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.