Solved

Creating MS SQL Database

Posted on 2001-08-05
8
332 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Process Javascript errors with Delphi TWebBrowser 1 130
drawing animated level bar based on numbers 3 97
How to convert memory stream to PDF file 6 54
update joined tables 2 45
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

776 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