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

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 ?
LVL 12
esoftbgAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
bpanaConnect With a Mentor Commented:
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
 
esoftbgAuthor Commented:
I will check for answers after 12 - 16 hours ....
0
 
bpanaCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
esoftbgAuthor Commented:
I tested above, but without success:
"Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection."
0
 
DarkCore_Commented:
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
 
bpanaCommented:
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
 
esoftbgAuthor Commented:
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
 
DarkCore_Commented:
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
 
bpanaCommented:
did it worked ?
0
 
esoftbgAuthor Commented:
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
 
bpanaCommented:
did you managed it ?
if not maybe i can help ...
0
 
esoftbgAuthor Commented:
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
 
bpanaCommented:
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
 
esoftbgAuthor Commented:
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
 
bpanaCommented:
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
 
bpanaCommented:
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
 
esoftbgAuthor Commented:
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
 
bpanaCommented:
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
 
esoftbgAuthor Commented:
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
 
bpanaCommented:
well, you can try, because you said that you had an error during the installation.
before installing, check all the instalation requirements.
0
 
bpanaCommented:
and it should be good to select Mixed Mode authentication (Windows Authentication and SQL Server Authentication)
0
 
esoftbgAuthor Commented:
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
 
esoftbgAuthor Commented:
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
 
bpanaCommented:
ok, if you are not sure what settings to choose during the installation, let me know
0
 
esoftbgAuthor Commented:
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
 
esoftbgAuthor Commented:
Unfortunately I have the same exceptions when I try to build a Connection string ....
0
 
bpanaCommented:
try with (local) for the servername, as in the following line
(local)
0
 
bpanaCommented:
and use the sa user with the password you have set up
sa
0
 
esoftbgAuthor Commented:
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
 
bpanaCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.