Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Creating MS SQL Database

Posted on 2001-08-05
8
Medium Priority
?
339 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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 1200 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
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…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

688 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