Sybase Adaptive connection strings

ASE ADO.NET Data Provider

  • Standard

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;

    It's also possible to define the port value together with the data source value, separated either by colon or comma i.e. DataSource=servername,port; or DataSource=servername:port;

  • Specify charset

    Many times when connecting to Sybase ASE the charset needs to be provided in the connection string. This is done by adding charset=[charset value].

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;Charset=iso_1;
  • Use encrypted password

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;EncryptPassword=1;

    If encryption is not supported an error will occur.

  • Use password encryption or plain text

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;EncryptPassword=2;

    Password is sent over the wire after a login is negotiated. If encryption is possible it will be encrypted. Otherwise it will be sent as plain text.

  • Use SSL

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;Encryption=ssl;TrustedFile='c:\sybase\ini\trusted.txt';
  • Connection Pooling

    Connection pooling reuse connections from a pool. It saves the connection handle for reuse, instead of open new connections. Connection pooling is turned on by default.

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;Min Pool Size=5;Max Pool Size=50;

    The Max Pool Size value restricts the maximum number of open connections. The calls to AseConnection.Open() fail with an AseException if the limit is reached.

  • Disable Connection Pooling

    It's sometimes a good idea to disable connection pooling for trouble shooting.

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;Pooling=False;
  • Connection pooling with idle time out

    Specified in seconds, how long a connection can be idle in the pool before the driver closes the connection.

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;ConnectionIdleTimeout=600;

    Default value is 0, meaning connections can stay idle for an indefinite amount of time. The driver will never close the connection.

  • Connection pooling with max lifetime

    Specified in seconds, how long an underlying connection can exist before the driver closes the underlying connection instead of returning it to the connection pool upon connection object close. Idle pooled connections are closed and removed from the pool once they reach the defined Connection Lifetime.

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;Connection Lifetime=600;

    Default value is 0, meaning connections can remain open for an indefinite amount of time. The driver will never close the connection.

  • Cluster Edition connection failover

    If opening a connection to the primary server (specified by the Data Source value) is unsuccessful, the driver uses the servers listed in Alternate Servers.

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;HASession=1;
    AlternateServers
    =anotherASEserver:5001,aThirdASEserver:5005;

    After a connection is established, with any available instance, the client receives an updated list of failover targets from the server.

  • Connection failover alternative syntax

    This is just another way of specifying failover in the connection string.

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;HASession=1;Secondary Data Source=anotherASEserver;
    Secondary Server Port
    =5002;
  • Disable transaction auto-enlistment

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;Enlist=0;
  • Use MS DTC OLE Native protocol for distributed transactions

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;DistributedTransactionProtocol=OLE;

    (default protocol is XA)

  • Two connections in same transaction enlistment

    Use this if you open two database connections to the same Adaptive Server server and enlist these connections in the same distributed transaction. If not specified (TightlyCoupledTransaction=1;) the distributed transaction may fail.

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;TightlyCoupledTransaction=1;
  • Using the sql.ini file

    If the server isn't aliased, you have to use the ip address and port number in the connection string, not ideal as these may change occassionally. Installing sybase on a machine, you will have a "sql.ini" file that maps an alias name to an ip address and a port. That file can be rolled out to all users, and the sql.ini updated when necessary. Use the following connection string to force the AseConnection object to use the interface file.

    DSURL='file://c:\sybase\ini\sql.ini?SQL_MIDOFF_OPC1';Database=myDataBase;
    UID
    =myUsername;PWD=myPassword;APP=myAppName;
  • Provide client info to the ASE server

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;ClientHostName=computerName;ClientHostProc=localProcessName;
    ApplicationName
    =myAppName;

    ClientHostName and ClientHostProc are used for the login record on the server. The ApplicationName is used by Adaptive Server to identify the client application.

  • Set buffer cache size

    Default is 20.

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;BufferCacheSize=50;

    Increase for very large results to boost performance.

  • Use OEM code page type

    Default is ANSI.

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;CodePageType=OEM;
  • Count last update only

    The records affected count returned includes all records happening from all updates or inserts (including triggers) in a stored procedure. Use this to return only the last update/insert count.

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;CumulativeRecordCount=0;
  • Enable bulk load

    Using ASEBulkCopy is disabled by default. Use this to allow for different bulk load operations.

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;EnableBulkLoad=1;

    Options: 1 = bulk-load using array insert, 2 = bulk-load using the bulk copy interface, 3 = bulk-load using the fast logged bulk copy interface.

  • Set network transfer packet size

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;PacketSize=1024;

    The default packet size is 512 bytes.

  • Disable server packet size decission

    Adaptive Server server versions 15.0 and later wants to choose the optimal packetsize. Use this to disable that feature.

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;EnableServerPacketSize=0;
  • Max packet size

    If you are using EnableServerPacketSize but still needs to set an upper limit you can specify that limit in multiples of 512 all the way to 65,536.

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;RestrictMaximum PacketSize=4096;
  • Specify fetch results row count

    Use this to control how many rows are received during fetch from server.

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;FetchArraySize=50;

    Default is 25 rows.

  • Set connection timeout

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;LoginTimeOut=5;

    Specified in seconds. Default is 15 seconds. 0 means indefinite wait.

  • Connection pooling without ping

    Default behaviour when obtaining and activating an idle connection from the pool is to ping the server to verify that the underlying connection is valid. Use this connection string to disable ping.

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;Ping Server=false;
  • Increase size of text and binary receive

    Adaptive server sends no more than 32k of text and binary data as default.

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;TextSize=64000;
  • Activate use of ASE decimals

    The AseDecimal support a precision/scale of 78.

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;UseAseDecimal=1;
  • Use cursors

    Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;
    Pwd
    =myPassword;UseCursor=1;

    The driver does not use cursors by default. Use this option to activate cursors.

Problems connecting?
Get answer in the Sybase Adaptive Q & A forum

Sybase ASE OLE DB Provider

Adaptive Server Anywhere OLE DB Provider

  • Standard

    Provider=ASAProv;Data source=myASA;
  • TCP/IP

    Provider=ASAProv.90;Eng=server.database_name;Uid=myUsername;Pwd=myPassword;
    Links
    =tcpip(Host=servername);

    Use this when connecting to a server located on the other side of a router. Without the Links=tcpip(Host=servername) the error "Server could not be found" can arise when connecting.

.NET Framework Data Provider for OLE DB

  • Use an OLE DB provider from .NET

    Provider=any oledb provider's name;OledbKey1=someValue;OledbKey2=someValue;

    See the respective OLEDB provider's connection strings options. The .net OleDbConnection will just pass on the connection string to the specified OLEDB provider. Read more here.

Sybase SQL Anywhere (former Watcom SQL) ODBC driver

  • Default local database engine

    Driver={Sybase SQL Anywhere 5.0};DefaultDir=c:\dbfolder\;Dbf=c:\mydatabase.db;
    Uid
    =myUsername;Pwd=myPassword;Dsn="";

    The empty DSN parameter could be critical if experiencing error 7778.

    Remember to start the connection string with "odbc:" if you are using PHP PDO_ODBC

  • Database engine not running

    Driver={Sybase SQL Anywhere 5.0};Uid=myUsername;Pwd=myPassword;
    EngineName
    =myServerAddress;DatabaseName=myDataBase;
    Start
    =c:\sqlany50\win32\dbclient.exe;
    DatabaseSwitches
    =-commandSwitch1 -commandSwitch2;
    DatabaseFile
    =c:\pb\ex\psdemodb.db;AutoStop=NO;

    If AutoStop is set to YES then the database is unloaded automatically as soon as there are no more open connections to it.

    When the engine starts the database specified by DatabaseFile, the engine will use the supplied DatabaseSwitches as command line options to determine startup options for the database. The Sybase manual contains a List of DatabaseSwitch values.

Intersolv 3.60

  • Standard

    Driver={INTERSOLV 3.60 32-BIT Sybase};Srvr=myServerAddress;Database=myDataBase;
    Uid
    =myUsername;Pwd=myPassword;

Intersolv 3.10

Adaptive Server Enterprise ODBC driver

Adaptive Server Anywhere ODBC Driver

  • Standard

    Driver=Adaptive Server Anywhere 7.0;ENG=server.database_name;UID=myUsername;
    PWD
    =myPassword;DBN=myDataBase;LINKS=TCPIP(HOST=serverNameOrAddress);
  • Specific port

    Driver=Adaptive Server Anywhere 7.0;ENG=server.database_name;UID=myUsername;
    PWD
    =myPassword;DBN=myDataBase;LINKS=TCPIP(HOST=serverNameOrAddress:1234);
  • Multiple servers

    Driver=Adaptive Server Anywhere 7.0;ENG=server.database_name;UID=myUsername;
    PWD
    =myPassword;DBN=myDataBase;LINKS=TCPIP(HOST=Server1,Server2);
  • Multiple servers with different TCP/IP ports

    Driver=Adaptive Server Anywhere 7.0;ENG=server.database_name;UID=myUsername;
    PWD
    =myPassword;DBN=myDataBase;LINKS=TCPIP(HOST=Server1:3322,Server2:7799);

.NET Framework Data Provider for ODBC

  • Use an ODBC driver from .NET

    Driver={any odbc driver's name};OdbcKey1=someValue;OdbcKey2=someValue;

    See the respective ODBC driver's connection strings options. The .net OdbcConnection will just pass on the connection string to the specified ODBC driver. Read more here.

Q&Aask question »

Articlesread all »

Connect

Articlesread all »

Didn't find your connection string?

Start over from the connection string reference index - or try a search!

In the Q&A forums you can ask your own question and let somebody help you.

The knowledge articles contains solutions and guides.