Database connectivity to MS SQL Server

[dsdoubt]

Hi,

May I know the configuration that we need to do for connecting a MS SQl server from a Unix Datastage Server.


We have the odbc.ini updated as follows.

Code: Select all

WHRPOSDEV=MS SQL

[DBNAME]
Driver=/opt/nz/lib/lib/libtdsodbc.so
Description=MS SQL ODBC
Servername=SERVERNAME
Port=1433
Database=DBNAME
ReadOnly=false
ShowSystemTables=false
Socket=16384
DateFormat=1
TranslationDLL=
TranslationName=
TranslationOption=

Added an entry in uvodbc.config.

But, when I DS_CONNECT DBNAME from Command it gives the following error.

Code: Select all

SQLConnect error: sTATUS = -1 SQLState = IM003 Natcode = 0 [ODBC] [DataDirect] [ODBC lib] Specified driver could not be loaded Invalid parameter (s) found in configuration file.

Iam wondering, why is it still giving DataDirect error. As we have installed separate Driver to windows connectivity.

[ray.wurlod]

ODBC messages always have a string of entries in square brackets at the beginning. You can read about them in the ODBC API manual (Microsoft Press). For example [ODBC] indicates the ODBC driver manager. [Data Direct][ODBC lib] specifies that the driver library supplied by vendor Data Direct was invoked. Ordinarily you also get information about the client software being used, but the DS_CONNECT command operates at a lower level and does not put its vendor or actual identifiers into the message.

[daignault]

Appears that the driver listed below is not for SQL Server but for Teradata.
Replace the libtdsodbc.so with libsooodbc.so. Also ensure that the path is correct as the path is /opt/nz/lib/lib.

This line begins with "Driver=..." in the code fragment

Regards,

Ray Daignault

Hi,

May I know the configuration that we need to do for connecting a MS SQl server from a Unix Datastage Server.


We have the odbc.ini updated as follows.

Code: Select all

WHRPOSDEV=MS SQL

[DBNAME]
Driver=/opt/nz/lib/lib/libtdsodbc.so
Description=MS SQL ODBC
Servername=SERVERNAME
Port=1433
Database=DBNAME
ReadOnly=false
ShowSystemTables=false
Socket=16384
DateFormat=1
TranslationDLL=
TranslationName=
TranslationOption=

<snip>

[mlsravanth]

Hi ,

please try these below connectivity string , it has worked for me


Provider=sqloledb;Data Source="servername";Initial Catalog="Database";Integrated Security=SSPI;

WE can import the meta data using couple of RDBMS,

1. SQL server Enterprise DB , use plugin meta data iport functinality , select the option MSOLEDB, and heer it askd for the connection string , place the above connection string , it shud work

2. using Dynamic RDBMS, create a DSN/ODBC connectivity , we can extartc the data from a MS SQL.SQL server