Go Up to Database Connectivity (FireDAC)
This topic describes how to connect to Microsoft SQL Server.
The FireDAC native driver supports Microsoft SQL Server Standard and Express editions version 2000 and later, and Microsoft SQL Azure. For a description of how to connect to Microsoft SQL Server Compact Edition, see Connect to Microsoft SQL Server Compact Edition.
FireDAC requires one of the Microsoft SQL Server x86 or x64 ODBC drivers to be installed on the workstation:
Note: SQL Server Native Client 10.0 (SQL Server 2008) may fail to call a stored procedure, when it is connected to SQL Server 2000. The symptom of this issue is the error message "Incorrect Syntax near to {". In this case, use the ODBC driver from the SQL Server 2000 or 2005 distribution.
If the SQL Server ODBC driver has not been installed properly, an exception is raised when you try to connect:
[FireDAC][Phys][ODBC][Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
FireDAC requires:
FreeTDS can be transferred from CVS into a folder in your home directory (more 1) (more 2). To install FreeTDS on Mac OS X, use the commands:
cvs -z3 -d:pserver:anonymous@freetds.cvs.sourceforge.net:/cvsroot/freetds checkout -P freetdsccd freetds./autogen.sh./configure --with-tdsver=8.0 --with-unixodbc=/usr/localmakesudo make installecho [FreeTDS] > tds.driver.templateecho Description=v0.82 with protocol v8.0 >> tds.driver.templateecho Driver=/usr/local/lib/libtdsodbc.so >> tds.driver.templateodbcinst -i -d -f tds.driver.template
Note that you may also need to install the gawk utility. At this point, you can configure a ODBC DSN or test the FireDAC connection.
Note that the FreeTDS ODBC driver is not that efficient and stable as the original Microsoft ODBC driver.
To link the driver:
To connect to the Microsoft SQL Server DBMS, most applications require you to specify DriverID, Server, Database, OSAuthent, User_Name, and Password.
DriverID=MSSQL
Parameter | Description | Example value |
---|---|---|
Server | Name of a server running SQL Server on the network. The value must be either the name of a server on the network or the name of a SQL Server Client Network Utility advanced server entry. When you connect to the SQL Azure, you have to prepend the server name with the "tcp:" prefix. Note: The alternative TPC/IP port may be specified after a server name, separated by the comma. |
|
Port | Only for Mac OS X. Specifies the port where the SQL Server is listening. For the Windows platform the port may be specified after a comma in the Server parameter value. For example, Server=host, port. The default port is 1433. | |
Database | Name of the default database for the connection. If the Database is not specified, the default database defined for the login is used. | Northwind |
OSAuthent | Controls the authentication mode:
| No |
User_Name | The SQL Server login name, if OSAuthent=No. When you connect to the SQL Azure, you have to append the "@<server>" suffix to your user name. |
|
Password | The SQL Server login password, if OSAuthent=No. Note that the passwords with both '{' and '}' are not supported. | |
Network | Name of a network library dynamic-link library. The name does not need to include the path and must not include the .dll file name extension. | dbnmpntw |
Address | Network address of the server running an instance of SQL Server. Address is usually the network name of the server, but can be other names, such as a pipe, or a TCP/IP port and socket address. | |
MARS | Controls the MARS - multiple active result set support in a connection:
MARS is a feature supported by SQL 2005 and later. It is not supported by SQL Azure. The enabled MARS may lead to fetch performance degradation. For more details, read the following: | No |
Workstation | Workstation ID. Typically, this is the network name of the computer on which the application resides (optional). If specified, this value is stored in the master.dbo.sysprocesses column hostname and is returned by sp_who and the Transact-SQL HOST_NAME function. | Bookkeeper1 |
Language | SQL Server language name (optional). When connecting to a SQL Server with multiple languages, Language specifies which set of messages are used for the connection. | |
Encrypt | Controls the network traffic encryption:
| Yes |
LoginTimeout | Controls the amount of time, in seconds, before an application times out while attempting to establish a connection. 0 specifies an infinite wait (default value). | 30 |
VariantFormat | Controls the SQL_VARIANT data type representation:
| Binary |
ExtendedMetadata | Controls the extended description of the query result sets:
| True |
ApplicationName | Name of the application. If specified, this value is stored in the master.dbo.sysprocesses column program_name and is returned by sp_who and the Transact-SQL APP_NAME function. | AllBooks |
ODBCAdvanced | Allows you to specify any other additional ODBC connection parameter value. | MARS_Connection=no;Regional=yes |
MetaDefCatalog | Default database name. The Design time code excludes the catalog name from the object name if it is equal to MetaDefCatalog. | Northwind |
MetaDefSchema | Default schema name. The Design time code excludes the schema name from the object name if it is equal to MetaDefSchema. | dbo |
MetaCaseIns | Controls the metadata case-sensitivity:
| True |
DriverID=MSSQLServer=127.0.0.1Database=NorthwindUser_Name=saMetaDefSchema=dboMetaDefCatalog=Northwind
DriverID=MSSQLServer=DA\SQLEXPRESSDatabase=NorthwindOSAuthent=YesMARS=no
DriverID=MSSQLServer=tcp:nasdfert6.database.windows.netDatabase=NorthwindUser_Name=addemo@nasdfert6Password=asd123zxcEncrypt=YesMetaDefSchema=dboMetaDefCatalog=Northwind
DriverID=MSSQLServer=(localdb)\\v11.0Database=masterOSAuthent=Yes
Server=(localdb)\\v11.0DriverID=MSSQLODBCAdvanced=AttachDbFileName=C:\\Users\\Alex\\ADDemo.mdf
联系客服