MSDE simple question

[PlayKid]

I am trying to create a program for my school, and I am using MSDE as the server, since this is the first time I am using this, I am not really sure how it actually work.

I am making this program at my house, but will be transfer to my school after the program is complete, but what about the data, how can it be transfer as well?? As like MS Access, I just copy the mdb file to the other computer, but how MSDE do that?

 

Thanks

[Joe Mamma]

I am trying to create a program for my school, and I am using MSDE as the server, since this is the first time I am using this, I am not really sure how it actually work.

I am making this program at my house, but will be transfer to my school after the program is complete, but what about the data, how can it be transfer as well?? As like MS Access, I just copy the mdb file to the other computer, but how MSDE do that?

 

Thanks

the easiest way is to attach/detach the database file. . .

 

research the following

 

sp_attach_db

sp_attach_single_file_db

sp_detach_db

 

also might want to take alook at sp_helpfile before you detach, just so you know where on the file system the database actually is. Nothing worse than detaching an mdf file only to smack your head with an 'oh krikeys! that db wasnt in the default folder! and it had a totally different name than the file name!!!'

 

Also there is a web based admin interface for MSDE but it can be a little tricky to get running. Probably easyist to use the above mentioned stored procs.

[PlayKid]

Sorry for my stupidity,

 

since I am a beginner to MSDE, I am really not familiar with the server, therefore I am not really sure what you are talking about?

I have searched on the internet about sp_attach_db, but the result that I got from it is similar as what you were saying, but I am really clueless about where to start doing so. I have downloaded a software called EMS MS SQL Manager Lite, coz I saw some websites is saying may use that to do so, but still, I tried, nothing seems to be working to me.

Can you maybe walk me through about how to move the database to somewhere else?

Thank you very much,

 

PlayKid

[Joe Mamma]

here you go. . .

 

( code for a command line utility to attach/detach databases from a SQL server database. . . Must be run as a user with admin permissions on the machine/sql server)

 

first.

 

1. Create a new C# console application named "dbutil" .

2. Delete the class1.cs file that is automatically created.

3. add a COM reference to the library SQLDMO.DLL (you may have to browse for this. . . it is in the "Program Files\Microsoft SQL Server\80\Tools\Binn" folder.

4. add a new class, named Util.

5. replace the code in util with this:

 

using System;
using System.Collections;

namespace dbutil
{
 class Util
 {
   [sTAThread]
   static void Main(string[] args)
   {
     try
     {
       if (args.Length!=0)
       {
         Hashtable ht = new Hashtable();
         foreach (string s in args)
         {
           string temp = s.Trim();
           if ((s.IndexOf('-')!=0) && (s.IndexOf('/')!=0))
             throw new CommandException();
           string[] argitem = temp.Split('=');
           if (argitem.Length>2) 
             throw new CommandException();
           ht.Add(argitem[0].ToUpper().Remove(0,1), argitem.Length==1?null:argitem[1]);
         }
         Hashtable cmdArgs = ParseArgs(ht);
         RunArgs(cmdArgs);
       }
       else
         WriteHelp();
     }
     catch(Exception e)
     {
       Console.WriteLine(e.Message);
     }
     Console.WriteLine("Press any key to continue.");
     Console.Read();
   }

   static void WriteHelp()
   {
     Console.WriteLine("Attach/Detach a Database." +ArgHelpMsg);
   }

   static Hashtable ParseArgs(Hashtable ht)
   {
     if (ht.ContainsKey("?")) return null;
     return GetArguments(ht);
   }

   static Hashtable GetArguments(Hashtable ht)
   {
     if (!ht.ContainsKey("DB")) throw new CommandException();
     if ( ht.ContainsKey("D") && ht.ContainsKey("A") ) throw new CommandException();
     Hashtable args = new Hashtable();
     bool IsAttach = ht.ContainsKey("A");
     ht.Remove("D");
     ht.Remove("A");
     if (IsAttach)
     {
       object filename = ht["F"];
       if (filename == null) throw new CommandException();
       args.Add("F", filename.ToString().Replace("\"",""));
     }
     ht.Remove("F");
     object instance = ht["I"];
     if (instance != null) 
       args.Add("I", instance.ToString().Replace("\"",""));
     ht.Remove("I");
     args.Add("DB", ht["DB"].ToString().Replace("\"",""));
     ht.Remove("DB");
     if (ht.Count != 0) throw new CommandException();
     args.Add( IsAttach ? "A" : "D",null);
     return args;
   }

   static void RunArgs(Hashtable args)
   {
     if (args == null)
     {
       WriteHelp();
       return;
     }
     
     DBOperation(args);
   }

   static SQLDMO._SQLServer CreateServer(Hashtable args)
   {
     string instance = args["I"] == null ? "" : args["I"].ToString();
     SQLDMO._SQLServer result = new SQLDMO.SQLServerClass();
     result.LoginSecure = true;
     result.Connect( instance,null,null);
     return result;
    }

   static void DBOperation(Hashtable args)
   {
     SQLDMO._SQLServer srv = CreateServer(args);
     string db = args["DB"].ToString();
     if (args.ContainsKey("D"))
     {
       srv.DetachDB( args["DB"].ToString(), true);
       Console.WriteLine("Database Detached");
       return;
     }
     else
     {
       srv.AttachDBWithSingleFile(args["DB"].ToString(), args["F"].ToString());
       Console.WriteLine("Database Attached");
     }
     srv.Close();
   }

   static public string ArgHelpMsg
   {
     get
     {
       string nl = Environment.NewLine;
       string exename = AppDomain.CurrentDomain.FriendlyName.Replace(".exe","");
       string msg = nl +"Usage:";
       string temp = string.Format("{0}{1}{0}[{{-d}} | [-a]] {{-i=instancename}}{2}{0}{0}"+
         "[-db=\"databasename\"] {{[-f=\"filename\"]}} {{-?}}", "\t", exename, Environment.NewLine);
       msg += temp + nl;
       msg += nl;
       msg += "Options:"+nl;
       msg += string.Format("{0}-d{0}Detach database. (default mode) ", "\t") + nl;
       msg += string.Format("{0}-a{0}Attach database file.", "\t") +nl;
       msg += string.Format("{0}-i{0}Name of MSDE Instance.{1}{0}{0}(Optional - " +
         "use only if the MSDE is a named instance).", "\t", Environment.NewLine)+nl;
       msg += string.Format("{0}-db{0}Required name of database on which to operate.", "\t")+nl;
       msg += string.Format("{0}-f{0}File name to attach as database in -db option " +
         "{1}{0}{0}(Required with -a option. ignored with -d option).", "\t", Environment.NewLine)+nl;
       msg += string.Format("{0}-?{0}Display this message.", "\t") +nl;
       return msg;
     }
   }
 }

 public class CommandException: ApplicationException
 {
   public CommandException():base("Invalid Command Line Arguments. . . " + Util.ArgHelpMsg)
   {
   }
 }
}

Usage: dbutil [{-d} | [-a]] {-i=instancename}
			[-db="databasename"] {[-f="filename"]} {-?}
Options:
	-d	 Detach database. (default mode)
	-a	 Attach database file.
	-i	 Name of MSDE Instance.
			(Optional - use only if the MSDE is a named instance).
	-db	 Required name of database on which to operate.
	-f	 File name to attach as database in -db option
			(Required with -a option. ignored with -d option).
	-?	 Display this message.

Edited July 27, 2005 by Joe Mamma