ASE ADO.NET Data Provider connection strings

Connects to

Sybase Adaptive

  • 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
.NET Framework Class LibrarySybase.Data.AseClient.AseConnectionInfo and class library downloads

Articlesread all »

Providers

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.