Solved

Creating MS SQL Database

Posted on 2001-08-05
8
336 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
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…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Suggested Courses
Course of the Month4 days, 18 hours left to enroll

636 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