'Connecting SQL 2008 at runtime

I'm trying to connect at runtime with SQL Server 2008 with Delphi 2009 using DBExpress, it's not working. When I set all the properties at design time, it works great, but at RunTime, I'm getting "unknown driver: mssql". Below is the code: 


  scnConexao := TSQLConnection.Create(nil);
  scnConexao.DriverName := 'MSSQL';
  scnConexao.ConnectionName := 'MSSQLConnection';
  scnConexao.GetDriverFunc := 'getSQLDriverMSSQL';
  scnConexao.LibraryName := 'dbxmss.dll';
  scnConexao.VendorLib := 'oledb';
  scnConexao.LoginPrompt := False;
  scnConexao.Params.Add('SchemaOverride=sa.dbo');
  scnConexao.Params.Add('HostName=DESKTOP');
  scnConexao.Params.Add('DataBase=DBNAME');
  scnConexao.Params.Add('OS Authentication=False');
  scnConexao.Params.Add('User_Name=UserName');
  scnConexao.Params.Add('Password=Password');
  scnConexao.Params.Add('MSSQL TransIsolation=ReadCommited');
  scnConexao.Open;

I have included the dbxmss.dll in the same directory as my app, but to no avail. Any help would be greatly appreciated.
Tks

sql-serverdelphidelphi-2009dbexpress


Solution 1:[1]

Try this:

With MSSQLCONNECTION do begin
ConnectionName:= 'SQLCONN';
LoadParamsFromIniFile('file.ini');
try
Connected:=true;
Execute('select * from auto',nil);
except
  on E: Exception do begin
    ShowException(E, nil);
    Halt(1);
  end;
end;

end;

base.ini:

[SQLCQONN]
bad param
drivername=MSSQL
schemaoverride=%.dbo
vendorlibwin64=sqlncli10.dll
HostName=localhost\sqlexpress
database=sec
user_name=sa
password=Guess
blobsize=-1
localecode=0000
isolationlevel=ReadCommitted
os authentication=False

For details see http://docwiki.embarcadero.com/RADStudio/en/Setting_Up_TSQLConnection

Solution 2:[2]

I've had luck with Delphi 2007 and MSSQL with this. You might need to change the DLL name to match the version of the MSSQL driver that is in your version of Delphi/DBExpress. Also, I am using SQL Authentication (aka SSPI) instead of a SQL username and password:

FSQLConnection := TSQLConnection.Create(Self);
FSQLConnection.ConnectionName := 'MSSQLConnection';
FSQLConnection.DriverName := 'MSSQL';
FSQLConnection.GetDriverFunc := 'getSQLDriverMSSQL';
FSQLConnection.LibraryName := 'dbxmss30.dll';
FSQLConnection.VendorLib := 'oledb';
FSQLConnection.LoginPrompt:= False;

FSQLConnection.Params.Values['SchemaOverride'] := 'sa.dbo';
FSQLConnection.Params.Values['DriverName'] := 'MSSQL';
FSQLConnection.Params.Values['BlobSize'] := '-1';
FSQLConnection.Params.Values['ErrorResourceFile'] := '';
FSQLConnection.Params.Values['LocaleCode'] := '0000';
FSQLConnection.Params.Values['MSSQL TransIsolation'] := 'ReadCommited';
FSQLConnection.Params.Values['OS Authentication'] := 'True';
FSQLConnection.Params.Values['Prepare SQL'] := 'False';
FSQLConnection.Params.Values['HostName'] := 'MyServerName';
FSQLConnection.Params.Values['DataBase'] := 'MyDatabaseName';

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 SchmitzIT
Solution 2 SOlson

Related Questions