Solved

Creating MS SQL Database

Posted on 2001-08-05
8
330 Views
Last Modified: 2011-10-03
I have an application that I distribute and it uses a MS SQL Server.  Currently I have the user execute a SQL script from within the Query Analyzer to create the database on their server.  I then have the user manually add a user to the database and manually create an ODBC data source.  This is definitly a lot of work for the user and leaves a lot of room for errors.  

My question is, how I can I have the program create the database, add a user with write permission to the database (is there one by default?), and setup the ODBC data source?  This needs to work with SQL Server 7 and 2000.
0
Comment
Question by:dokken
  • 4
  • 3
8 Comments
 
LVL 2

Expert Comment

by:Felixin
ID: 6354912
For the user you can just add the user at home, on the development server. Once you deliver the script to the user, you just have to generate the scripts including user creation and permissions.

For the ODBC I guess that if you something like the InstallShield, you should be able to create an ODBC connection at home, using the customer names for the server.
0
 

Author Comment

by:dokken
ID: 6355879
I ended up finding code for creating an ODBC data source from within a program so that part is solved.  I now just need to be able to creat the database from my application.  I haven't found any useful information on doing this.
0
 
LVL 5

Expert Comment

by:heathprovost
ID: 6357748
The easiest way to do something like this would be to use Active Data Objects (ADO).  Assuming you users client machines have ADO support (its built into ME and 2000, and is installed on 95 and 98 with the later MDAC updates or with Access 2000).  The nice thing about ADO is it doesnt require the creation of Datasources to access the DB server.  You could write a wizard that asked for the name of the MSSQL server on which to install the database and either the sa password or a user account with DB creation rights, and that is pretty much all the user would need to supply.  Once you know the name of the server and have a valid account, you could just connect to it directly and execute you scripts through ADO.  Ill try to dig up some code for you...

Heath
0
 
LVL 5

Expert Comment

by:heathprovost
ID: 6357799
The easiest way to do something like this would be to use Active Data Objects (ADO).  Assuming you users client machines have ADO support (its built into ME and 2000, and is installed on 95 and 98 with the later MDAC updates or with Access 2000).  The nice thing about ADO is it doesnt require the creation of Datasources to access the DB server.  You could write a wizard that asked for the name of the MSSQL server on which to install the database and either the sa password or a user account with DB creation rights, and that is pretty much all the user would need to supply.  Once you know the name of the server and have a valid account, you could just connect to it directly and execute you scripts through ADO.  Ill try to dig up some code for you...

Heath
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 5

Expert Comment

by:heathprovost
ID: 6357804
Here is some code I just threw together...

Place a TADOConnection, a TADOCOmmand, and a TButton on a blank form and use the following code for the button's onclick event:

procedure TForm1.Button1Click(Sender: TObject);
const
  //these constants would be variables in your code supplied by the user.
  //They would be the username, password, and server name for the MSSQL server
  username = 'sa';
  password = 'nimda4458';
  srvname = 'beta';

  //boilerplate connect string, will be filled in with above info
  connStr = 'Provider=SQLOLEDB.1;Password=%s;User ID=%s;Data Source=%s;';

  //These constants would be the commands to execute after connecting
  useMaster = 'USE Master';
  useNewDB = 'USE testDb';
  createDB = 'CREATE DATABASE testDb';
  createTable = 'CREATE TABLE testTable (testField varchar)';
  createUser = 'EXEC sp_addlogin TestUserName, TestPassword, testDb';
  GrantUser = 'EXEC sp_adduser TestUserName';
begin

  //Set up initial connection to DBMS
  ADOConnection1.ConnectionString := format(connStr, [password, username, srvname]);
  ADOConnection1.LoginPrompt := false;
  ADOConnection1.Connected := true;
  ADOCommand1.connection := ADOConnection1;

  //start Database creation

  //set default db to the Master DB
  ADOCommand1.CommandText := useMaster;
  ADOCommand1.Execute;
  //create the database
  ADOCommand1.CommandText := createDB;
  ADOCommand1.Execute;
  //create the user
  ADOCommand1.CommandText := createUser;
  ADOCommand1.Execute;
  //set default db to the new DB you just created
  ADOCommand1.CommandText := useNewDB;
  ADOCommand1.Execute;
  //add the user to this DB
  ADOCommand1.CommandText := GrantUser;
  ADOCommand1.Execute;
  //create a table
  ADOCommand1.CommandText := createTable;
  ADOCommand1.Execute;

  //all finished so disconnect
  ADOConnection1.Connected := false;
end;


Of course this could be written much better, I was hurrying cause I gotta leave in a minute.  Let me know if you have any questions, I commented it so you could see what was going on.

Heath
0
 

Author Comment

by:dokken
ID: 6358446
Do ADO components come with Delphi 5 Professional?  That's what I'm using.
0
 
LVL 5

Accepted Solution

by:
heathprovost earned 300 total points
ID: 6360680
not sure...  They are part of the Client Server version for sure.  Dont know about the Pro version...  Of course if you dont have them then this approach wont work unless you write the code to create the ADO objects manually (if you really want to do it this way I can give you code, but it is a bit more complicated).  You could however do nearly the same thing through ODBC, I only mentioned using ADO because it is simplier to code.  The approach for ODBC would be exactly the same, you would:

1. Create the datasource (you said you found out how to do this already, so I wont go into detail)

2. Connect to the datasource using whatever method you currently use.

3. Use a component (I assume you are using TQuery) to execute the commands as I gave in the above code.

4. Rinse and repeat until all commands have been executed

5. disconnect.

All the commands given in the original source are simply SQL commands, nothing special about them.  They can be executed through ODBC just as easily as ADO...

Heath
 
0
 

Author Comment

by:dokken
ID: 6364727
I decided to install Delphi 6 prior to receiving your last message, so I'm going to use ADO.  I think your method would work so I'm giving you the points.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now