Delphi 2010 Connection to MS SQL Server 2005 DB ?

Delphi 2010

I am trying to make a connection to a MS SQL Server 2005 DB on a MS Server 2008 OS utilizing the DBExpress component - TSQLConnection


I need to know what DLL's to use.

If I drop a TSqlConnection on a form in a Delphi 2010 application and set its Driver to MSSQL, it defaults to:

LibraryName:= dbxmss.dll
VendorLibrary:= sqlncli10.dll

I see the my OS has a DLL  "sqlncli.dll" in its System32 folder. and my delphi bin folder has a
"dbxmss.dll"

II have tried both of these files in the system32 folder and in the root folder of my executable.
I have also tried placing both the Connections and Drivers ini files in the executable folder and borland shared dbexpress folders

However, i keep getting "Unknown Driver: MSSQL"

here is my ini files:



connections ini file

[VPUCDS_VPN_SE01]
DriverName=MSSQL
HostName=SEAVALUPAY01
DataBase=vpucds02
User_Name=sysdba
Password=masterkey
BlobSize=-1
ErrorResourceFile=
LocaleCode=0000
MSSQL TransIsolation=ReadCommited
OS Authentication=False

drivers ini file

[MSSQL]
GetDriverFunc=getSQLDriverSQLServer
LibraryName=dbxmss.dll
VendorLib=sqlncli.dll
HostName=SEAVALUPAY01
Database=vpucds02
User_Name=sysdba	
Password=masterkey
BlobSize=-1
ErrorResourceFile=
LocaleCode=0000
MSSQL TransIsolation=ReadCommited
OS Authentication=False

Open in new window

DelphiRulezAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ebob42Commented:
There's a change in the SQL Server DBX4 driver for Delphi and C++Builder 2010, which not all developers may have noticed.

The readme.htm states in the Database Changes section for dbExpress, the following:
"MSSQL driver requires the installation of the SQL Native Client. SQL Native Client 2008 is required for dbxmss.dll. SQL Native Client 2005 is required for dbxmss9.dll"

This can be overlooked easily, especially since the Supported Servers section lists - among others - the following for dbExpress:
"Microsoft SQL Server 2008, 2005, 2000 (Ent) (Driver dbxMSS.dll, Client oledb.dll)"

Which makes it look like we can still use oledb.dll as client.

However, if you try to make a connection using DBX4 to a SQL Server database with Delphi or C++Builder 2010, you'll see the error message: "DBX Error: Driver could not be properly initialized. Client library may be missing, not installed properly, or of the wrong version."

For SQL Server 2008, you can download the SQL Native Client from http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en
"Microsoft SQL Server 2008 Native Client (SQL Native Client) is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server 2000, 2005, or 2008. SQL Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2008 features. This redistributable installer for SQL Native Client installs the client components needed during run time to take advantage of new SQL Server 2008 features, and optionally installs the header files needed to develop an application that uses the SQL Native Client API."

Note by the way, that if you use SQL Server 2008 on Windows 7, you need to apply Service Pack 1, because you may otherwise encounter "compatibility issues" (like permission problems and being unable to alter existing tables).
0
DelphiRulezAuthor Commented:
I will give this a try, thanks

0
DelphiRulezAuthor Commented:
so when i dowload and installt this SQL Native Client , will it effect anything regarding the actual Server on the machine....i mean can both sql server and sql client be there?  I don't get why the DLL's didn't come with server if they come with client.

Maybe you dont underst my predicament. I am running the executable on the same machine as the MS SQL Server 2005 server....and not on a client machine


also, if i install this native client, what two DLL's will i then use to make a connection using DBExpress to a SQL Server 2005 DB on a MS Server 2008 OS machine

thanx
0
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

ebob42Commented:
I understand your problem. Check out the readme, and check out the comments when you download the SQL Server 2008 client driver. It's good for SQL Server 2000 and higher. And Delphi 2010 now requires it for dbExpress...
0
DelphiRulezAuthor Commented:
ya, i got that part.....

what two DLL's will i then use to make a connection using DBExpress to a SQL Server 2005 DB on a MS Server 2008 OS machine
0
ebob42Commented:
The version of the SQL Server database is no longer important if you use Delphi 2010.

Delphi 2010 uses the SQL Server 2008 native client DLL, which will connect to ANY SQL Server database: 2000, 2005, 2008, express editions, etc.

So in order to make a connection using dbExpress to a SQL Server 200X DB on MS Server 2008 OS, you just need the native SQL Server 2008 client (to connect to 2000, 2005, 2008, express, etc.), which is sqlncli10.dll and then the dbExpress driver which is dbxMSS.dll

So the answer to your last question is: sqlncli10.dll and dmxmss.dll - that combination can handle all SQL Server 200X databases.
0
DelphiRulezAuthor Commented:
ok, i will give that a try

does it matter where the DLL's are located

how about the ini files

can i have a four files in the same folder with the EXE

thanx
0
ebob42Commented:
Your best bet to get rid of the .ini files is to clear the CONNECTIONAME property after you've filled in the SQLConnection component. Just the DriverName and then the sub-properties will be enough.

Then, place the two DLLs in the same directory as your executable. And don't forget the MIDAS.DLL (or add MidasLib to your uses clause).
0
DelphiRulezAuthor Commented:
>>>>And don't forget the MIDAS.DLL (or add MidasLib to your uses clause).

hmmm, where did that come from....first time i heard anything of that


ill give it a try

0
ebob42Commented:
MIDAS.DLL is the one that implements the ClientDataSet and DataSetProvider functionality. Have you never had the need to deploy it or add MidasLib to the uses clause? Did you use dbExpress before, or dbGo for ADO?

(note that it will work fine if Delphi is installed on the machine, but not if you put the app on a "clean machine", without Delphi installed, since that will probably not contain the MIDAS.DLL).
0
DelphiRulezAuthor Commented:
OK,

in the folder where my executable resides, I have the following files

Test.exe (executable)
midas.dll
sqlncli10.dll
dbxMSS.dll

I have remove the ini files

Still get "Unknown Driver: MSSQL"

I then added the  MidasLib to uses clause and got the same error


here is my code for the test executable


procedure TForm1.Button1Click(Sender: TObject);
var
 Conn: TSQLConnection;
begin
 Conn:= TSQLConnection.Create(nil);
 try
 Conn.ConnectionName:= 'VPUCDS_VPN_SE01';
 Conn.DriverName:= 'MSSQL';
 Conn.VendorLib:= 'SQLNCLI10.DLL';
 Conn.LibraryName:= 'DBXMSS.DLL';
 Conn.LoginPrompt:= true;
 Conn.Connected:= True;
 if Conn.Connected then
  ShowMessage('Connected!')
 else
 ShowMessage('Not Connected!')
 finally
   Conn.Free;
 end;
end;
0
ebob42Commented:
OK, if you assign the connectionname and drivername you should also deploy the two .ini files. dbxConnections.ini and dbxDrivers.ini. First try to place them in your current directory, although they may have to end up in the same directory as on your develoment machine (the C:\Documents and Settings\All Users\Documents\RAD Studio\dbExpress\7.0 directory - or something like it if you use Vista or Windows 7).
0
DelphiRulezAuthor Commented:
>>OK, if you assign the connectionname and drivername

OK, now you are really confusing me......

how else would you tell it what database to connect to?
0
DelphiRulezAuthor Commented:
lease describe in detail, what i need in the ini files, and what i need in my little code blurp above
0
DelphiRulezAuthor Commented:
I tried this:

procedure TForm1.Button1Click(Sender: TObject);
var
 Conn: TSQLConnection;
begin
 Conn:= TSQLConnection.Create(nil);
 Conn.ConnectionName:= 'VPUCDS_VPN_SE01';
 Conn.DriverName:= 'MSSQL';
 Conn.LibraryName:= 'DBXMSS.DLL';
 Conn.VendorLib:= 'SQLNCLI10.DLL';
 Conn.GetDriverFunc:= 'getSQLDriverMSSQL';
 Conn.Params.Add('User_Name=SYSDBA');
 Conn.Params.Add('Password=masterkey');
 Conn.Params.Add('Database=vpucds02');
 Conn.Params.Add('HostName=SEAVALUPAY01');
 Conn.LoginPrompt:= False;
 try
   Conn.Connected:= True;
   if Conn.Connected then
   ShowMessage('Connected!')
   else
   ShowMessage('NOT Connected!')
 finally
  Conn.Free;
 end;
end;


same thing:  "Unknown Driver: MSSQL"
0
ebob42Commented:
Then it cannot find your dbxdrivers.ini file with the information it wants to read.

I'll post an example that will not require that info shortly.
0
ebob42Commented:
BTw, seeing your database connection parameters: are you SURE you want to connect to SQL Server? The User_Name=SYSDBA and password masterkey seem to indicate a reference to InterBase instead ;-)
0
ebob42Commented:
The following code works fine for me, putting only the two DLLs in the same directory as the executable. No .ini files needed.

Note that I do not set the ConnectionName property, and I also set the LoadParamsOnConnect to False.
implementation
uses
  MidasLib; // no MIDAS.DLL needed
{$R *.dfm}

procedure TMainForm.Button1Click(Sender: TObject);
var
  Conn: TSQLConnection;
begin
  Conn := TSQLConnection.Create(nil);
  Conn.DriverName:= 'MSSQL';
  Conn.LoadParamsOnConnect := False;
  Conn.LibraryName:= 'DBXMSS.DLL';
  Conn.VendorLib:= 'SQLNCLI10.DLL';
  Conn.GetDriverFunc:= 'getSQLDriverMSSQL';
  Conn.Params.Add('User_Name=sa');
  Conn.Params.Add('Password=********');
  Conn.Params.Add('Database=weblog');
  Conn.Params.Add('HostName=192.168.1.7');
  Conn.LoginPrompt:= False;
  try
    Conn.Connected:= True;
    if Conn.Connected then
      ShowMessage('Connected!')
    else
      ShowMessage('NOT Connected!')
  finally
    Conn.Free;
  end;
end;

Open in new window

0
DelphiRulezAuthor Commented:
>>>The User_Name=SYSDBA and password masterkey seem to indicate a reference to InterBase instead ;-)

Its an example, i didn't want to put my real username and password on here
0
DelphiRulezAuthor Commented:
Nope, nothing you suggested above works - get the same thing - "Unknown Driver: MSQL"

I have attaached a screenshot


unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, WideStrings, DB, SqlExpr, StdCtrls, MidasLib;

type
  TForm1 = class(TForm)
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var
 Conn: TSQLConnection;
begin
 Conn:= TSQLConnection.Create(nil);
 Conn.DriverName:= 'MSSQL';
 Conn.LoadParamsOnConnect := False;
 Conn.LibraryName:= 'DBXMSS.DLL';
 Conn.VendorLib:= 'SQLNCLI10.DLL';
 Conn.GetDriverFunc:= 'getSQLDriverMSSQL';
 Conn.Params.Add('Database=vpucds02');
 Conn.Params.Add('HostName=SEAVALUPAY01');
 Conn.LoginPrompt:=True;
 try
   Conn.Connected:= True;
   if Conn.Connected then
   ShowMessage('Connected!')
   else
   ShowMessage('NOT Connected!')
 finally
  Conn.Free;
 end;
end;

end.

Open in new window

screenshot.jpg
0
senadCommented:
Try : DriverName=MSSQLSERVER
0
ebob42Commented:
MSSQLSERVER is the name of the driver from DevArt (CoreLab).

Which version of Delphi 2010 do you have? If you have Delphi 2010 Professional, then THERE IS NO SUPPORT for SQL Server, so it's unknown indeed. Is this perhaps the source of your problems?
Check out C:\Program Files\Embarcadero\RAD Studio\7.0\readme.htm which says:

Microsoft SQL Server 2008, 2005, 2000 (Ent) (Driver dbxMSS.dll, Client oledb.dll)

I will also attach a .zip file with a form that contains the same connection info you're using.
0
ebob42Commented:
I see a final difference...

Add DBXMsSQL to your uses clause. Save. Recompile. Run.

DBXMsSQL contains the information for the SQL Server driver. Without it, there is no way it could know about it. I assumed it was added to your uses clause (like in my version), sorry. Good thing you showed your complete file. Here's mine ;-)
unit MainFormDBX;
interface
uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, WideStrings, DBXMsSQL, DB, SqlExpr, StdCtrls;

type
  TMainForm = class(TForm)
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  MainForm: TMainForm;

implementation
uses
  MidasLib; // no MIDAS.DLL needed
{$R *.dfm}

procedure TMainForm.Button1Click(Sender: TObject);
var
  Conn: TSQLConnection;
begin
  Conn := TSQLConnection.Create(nil);
  Conn.DriverName:= 'MSSQL';
  Conn.LoadParamsOnConnect := False;
  Conn.LibraryName:= 'DBXMSS.DLL';
  Conn.VendorLib:= 'SQLNCLI10.DLL';
  Conn.GetDriverFunc:= 'getSQLDriverMSSQL';
  Conn.Params.Add('User_Name=sa');
  Conn.Params.Add('Password=********');
  Conn.Params.Add('Database=weblog');
  Conn.Params.Add('HostName=192.168.1.7');
  Conn.LoginPrompt:= False;
  try
    Conn.Connected:= True;
    if Conn.Connected then
      ShowMessage('Connected!')
    else
      ShowMessage('NOT Connected!')
  finally
    Conn.Free;
  end;
end;

end.

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DelphiRulezAuthor Commented:
I am using Delphi 2010 Enterprise and I am not using Devart drivers.  We used Devart Drivers back when we were developing in Delphi 7.

As my original question stated, I want to make a connection using Delphi 2010 (Enterprise) to a MS SQL Server 2005 on a MS Server 2008 OS.


ebob42:  I will try your suggestion
0
DelphiRulezAuthor Commented:
THAT WORKS!  THANK YOU SO MUCH FOR YOUR HELP AND FOR BEING PERSISTANT!
0
DelphiRulezAuthor Commented:
shoot, I mistakenly didn't check to of the  grading options and this ended with a 5,7 rating.  How do i go back and change this?
0
ebob42Commented:
I'm glad it worked - I'm not sure how to change the rating.
0
DelphiRulezAuthor Commented:
I have a question out with the Moderator, Im sure they will help me....thanks again!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.