Solved

Create a MS SQL Server database and a table into database at runtime

Posted on 2004-08-05
30
345 Views
Last Modified: 2010-04-05
I know how to create an Access database and a table into database at runtime.
Is there a way to create a MS SQL Server database and a table into database at runtime using Delphi code ?
0
Comment
Question by:esoftbg
  • 15
  • 13
  • 2
30 Comments
 
LVL 12

Author Comment

by:esoftbg
Comment Utility
I will check for answers after 12 - 16 hours ....
0
 
LVL 6

Expert Comment

by:bpana
Comment Utility
1. you can use SQL-DMO (SQL Distributed Management Objects)

2. you can use TADOConnection
Example follows creating a database (similar you can create a table in the database):

procedure TForm1.Button1Click(Sender: TObject);
var
  ADOConn: TADOConnection;
  slExec: TStringList;
begin
  ADOConn := TADOConnection.Create(Self);
  ADOConn.ConnectionString :=
    'Provider=SQLOLEDB.1;' +
    'Password=' + 'Password' + ';' +
    'Persist Security Info=True;' +
    'User ID=sa;' +
    'Initial Catalog=master;' +
    'Data Source=(local)';

  slExec := TStringList.Create;
  slExec.Add('CREATE DATABASE Sales');
  slExec.Add('ON');
  slExec.Add('( NAME = Sales_dat,');
  slExec.Add('   FILENAME = ''d:\dbtest\saledat.mdf'',');
  slExec.Add('   SIZE = 10,');
  slExec.Add('   MAXSIZE = 50,');
  slExec.Add('   FILEGROWTH = 5 )');
  slExec.Add('LOG ON');
  slExec.Add('( NAME = ''Sales_log'',');
  slExec.Add('   FILENAME = ''d:\dbtest\salelog.ldf'',');
  slExec.Add('   SIZE = 5MB,');
  slExec.Add('   MAXSIZE = 25MB,');
  slExec.Add('   FILEGROWTH = 5MB )');

  ADOConn.Execute(slExec.Text, cmdText, [eoExecuteNoRecords]);

  slExec.Free;
  ADOConn.Free;
end;
0
 
LVL 12

Author Comment

by:esoftbg
Comment Utility
I tested above, but without success:
"Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection."
0
 
LVL 2

Expert Comment

by:DarkCore_
Comment Utility
The reason for this is that you selected to validate thru Domain controller instead SQL Server owns user properties. user SA is the "administrator" of the Database and must have rights to do anything.

You can select this option when installing, or once installed, open the Console manager, select your server and choose "Properties", in Tab "Security" you have this option.

Just another thing ... when create the database, you must open a new connection ( or close and modify this connection ) to the new database you created, and then create the table.

If you don't want to close the current connection you can use in a query
  USE new_database
  CREATE TABLE .....
  USE my_database

Is important you return to your database ( or the database pointed by datasource )

Edu
0
 
LVL 6

Expert Comment

by:bpana
Comment Utility
hi,

1. did you changed the password? check the ConnectionString

or

2. Maybe you use Windows authentication connecting to the database. Change it to mixed mode.
Rightmouse click on your server name and select properties from Enterprise Manager.
Then select "Security Tab". Make sure "Sql Server and windows" option is selected.
0
 
LVL 12

Author Comment

by:esoftbg
Comment Utility
I have not SQL-DMO (SQL Distributed Management Objects)
I have installed MSDE2000 from MS Office XP
I have installed MDAC 2.8

May be I am trying to do something impossible with the software installed ?

I don't know  the Console manager....
When I Rightmouse click on my server name there is not Enterprise Manager....
0
 
LVL 2

Expert Comment

by:DarkCore_
Comment Utility
in MSDE there's no Console Manager. The only way to get it is with a FULL MS SQL Server.

With MSDE, once installed, without any tools, you only can change it thru registry

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q285097

Good Luck!

Edu
0
 
LVL 6

Expert Comment

by:bpana
Comment Utility
did it worked ?
0
 
LVL 12

Author Comment

by:esoftbg
Comment Utility
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\LoginMode
LoginMode    REG_DWORD    0x00000002 (2)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\LoginMode
LoginMode    REG_DWORD    0x00000002 (2)

but does not work yet....
0
 
LVL 6

Expert Comment

by:bpana
Comment Utility
did you managed it ?
if not maybe i can help ...
0
 
LVL 12

Author Comment

by:esoftbg
Comment Utility
I did try to install Microsoft SQL Server 2005 Beta version ....
I had a warning during the installation about my hardware does not meet the requirements for this software ....
Anyway the installation complete, but I have in the start menu only:
menu item -      "Microsoft SQL Server 2005"
submenu item -   "SQL Computer Manager"
When I start the  "SQL Computer Manager", it shows me the information as:
----------------------------------------------------------------------------------------------------------------
Property                    Value
----------------------------------------------------------------------------------------------------------------
Name                        SQL Server (SQLEXPRESS)
Host Name                 EMIL
SQL Service Type       1
State                         Running
Start mode                Automatic
Start name                LocalSystem
Old Password            ****************
Password                  ****************
Confirm Password      ****************
Error control              1
Binary Path                C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe
Process ID                 1512
Error Code                 0
----------------------------------------------------------------------------------------------------------------

When I try the code for Database creation it raises an exception:
[DBNETLIB][Connection open (Connect()).]SQL Server does not exist or access denied.

So I am at the start point without any success ....
0
 
LVL 6

Expert Comment

by:bpana
Comment Utility
Ok, i didn't test the new beta version of MS SQL. I'm just using SQL Server 2000.
Also, I have worked only with SQL authentication, not Windows authentication and I cannot test the TADOConnection for the 2nd case.

All you need in order to connect to an instance of SQL server is to set up a connection string.
Drop a TADOConnection on the form.
Clicking on the ConnectionString it will bring up a wizard to set up the connection.
There click on the Build button.
Select "Microsoft OLE DB Provider for SQL Server.
Click Next.
- for the SQL server name type: (local) or the name of your computer without brackets.
- for the authentication, in your case, use "Use Windows NT Integrated security"
- select a database on the server. (here, when you open the combobox it will show all the database on the server). If you didn't already installed a database it will show only the system databases. In this case select the "master database".
- click on the Test Connection button (you should get a confirmation message)

If you followed this steps and everything is ok, we will to continue the Database creation steps.
0
 
LVL 12

Author Comment

by:esoftbg
Comment Utility
I did follow above steps two times unsuccessful:
- for the SQL server name: (local) (EMIL) without brackets.
- for the authentication:      "Use Windows NT Integrated security"
but when I did try to select a database on the server it was raised the known exception:
  [DBNETLIB][Connection open (Connect()).]SQL Server does not exist or access denied.
followed by Microsoft Data Link warning message:
  Login failed. Catalog information cannot be retrieved.

So, it seems I have some problems with MS SQL Server that I think is installed successfully ....
0
 
LVL 6

Expert Comment

by:bpana
Comment Utility
no, for the SQL Serve name for the local computer you can use one of the next 2:
1. (local)
2. EMIL

just use (local)
0
 
LVL 6

Expert Comment

by:bpana
Comment Utility
to connect to an SQL Server which is on another computer you'll use the computer name.
suppose the computername is Comp1, for the SQL Server name you'll use Comp1
now, to connect to an instance of SQL Server on the local computer you can use:
1. Computername (in your case EMIL)
2. (local) - using like this it will connect to the local computer, it's not needed to provide the name of the computer. (it's like for the network address: 127.0.0.1 is the local ip address)
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 12

Author Comment

by:esoftbg
Comment Utility
I did try again:
first time I typed local
second time I selected EMIL (it is in the combo box)
The same exception is raised, no matter local or EMIL is the server name ....

I have not network (except cable modem for Internet)
0
 
LVL 6

Expert Comment

by:bpana
Comment Utility
ok, you can select EMIL from the combobox.

it seems that you are using SQL authentication, not Windows authentication (this option was set during the installation of SQL Server)

in this case select "Use a specific user name and password".
Username: sa
Password: YoursaPassword

sa is the administrator user in SQL Server. when you have installed SQL Server if you have provided a password for the sa account, put it in the Passord edit, if you didn't provided a passord, check the "Blank password" checkbox.
Check the "Allow saving password" checkbox.
0
 
LVL 6

Accepted Solution

by:
bpana earned 250 total points
Comment Utility
When installing SQL Server you can choose the authentication method:
1. Windows Authentication Mode
2. Mixed Mode (Windows Authentication and SQL Server Authentication)

So, whatever option you choosed during the installation, if you select "Use Windows NT Integrated security" you should be able to connect to SQL Server.s

If you cannot connect, it seems that SQL Server has not been successfully installed.

Or it might be a chance ... Maybe the SQL service is not started.
Go in Control Panel -> Administrative tools -> Services
Search MSSQLSERVICE service. If it is not started, start it.
0
 
LVL 12

Author Comment

by:esoftbg
Comment Utility
I did install MS SQL Server 2005 Beta two weeks ago and I don't remember which mode I selected during the installation ....
May be I need to uninstall the SQL Server and then install it again ?
0
 
LVL 6

Expert Comment

by:bpana
Comment Utility
well, you can try, because you said that you had an error during the installation.
before installing, check all the instalation requirements.
0
 
LVL 6

Expert Comment

by:bpana
Comment Utility
and it should be good to select Mixed Mode authentication (Windows Authentication and SQL Server Authentication)
0
 
LVL 12

Author Comment

by:esoftbg
Comment Utility
Ok, thanks I am starting to:
1. uninstall MSSQL Server;
2. delete the directory;
3. Install it again with Mixed Mode authentication (Windows Authentication and SQL Server Authentication).
0
 
LVL 12

Author Comment

by:esoftbg
Comment Utility
I have just uninstalled Microsoft SQL Server and deleted C:\Program Files\Microsoft SQL Server\MSSQL.1
Now I am going to install Microsoft SQL Server 2005 Beta again.
0
 
LVL 6

Expert Comment

by:bpana
Comment Utility
ok, if you are not sure what settings to choose during the installation, let me know
0
 
LVL 12

Author Comment

by:esoftbg
Comment Utility
I have Microsoft SQL Server 2005 Beta 2 installed again.
During the installation i have displayed two warnings:
1. About my computer hardware;
2. About IIS software.
But installation complete successfully with Mixed Mode authentication (Windows Authentication and SQL Server Authentication).
0
 
LVL 12

Author Comment

by:esoftbg
Comment Utility
Unfortunately I have the same exceptions when I try to build a Connection string ....
0
 
LVL 6

Expert Comment

by:bpana
Comment Utility
try with (local) for the servername, as in the following line
(local)
0
 
LVL 6

Expert Comment

by:bpana
Comment Utility
and use the sa user with the password you have set up
sa
0
 
LVL 12

Author Comment

by:esoftbg
Comment Utility
I am discouraged because it does not work. It is something wrong on my Windows XP with SP2. I will award you with the points for your efforts to help me. I am very tired to continue today. Tommorow I will try again ....
Thank you very much !
0
 
LVL 6

Expert Comment

by:bpana
Comment Utility
First of all thanks for the points.

Ok, now that you said that you have Windows XP SP2 i remebered that there are some issues with this service pack.

Take a look on this link to enable SQL Server connectivity on Windows XP Service Pack 2:
http://support.microsoft.com/default.aspx?scid=kb;en-us;841251&product=sql
0

Featured Post

What Security Threats Are You Missing?

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

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 my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

16 Experts available now in Live!

Get 1:1 Help Now