Delphi 2010 Connection to MS SQL Server 2005 DB ?

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).

I will give this a try, thanks

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

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...

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

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.

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

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).

>>>>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

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).

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;

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).

>>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?

lease describe in detail, what i need in the ini files, and what i need in my little code blurp above

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"

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.

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 ;-)

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;

Select allOpen in new window

>>>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

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.

Select allOpen in new window

screenshot.jpg

Try : DriverName=MSSQLSERVER

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.

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

THAT WORKS!  THANK YOU SO MUCH FOR YOUR HELP AND FOR BEING PERSISTANT!

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?

I'm glad it worked - I'm not sure how to change the rating.

I have a question out with the Moderator, Im sure they will help me....thanks again!