Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

Creating MS SQL Database

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
dokken
Asked:
dokken
  • 4
  • 3
1 Solution
 
FelixinCommented:
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
 
dokkenAuthor Commented:
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
 
heathprovostCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
heathprovostCommented:
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
 
heathprovostCommented:
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
 
dokkenAuthor Commented:
Do ADO components come with Delphi 5 Professional?  That's what I'm using.
0
 
heathprovostCommented:
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
 
dokkenAuthor Commented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now