Accessing MSSQL server using DRS stage

[sumitgulati]

Hi,

I am facing a strange issue with DRS stage. I have a simple job that reads data from a table in ORACLE and inserts it into a table in MSSQL Server. I am using DRS stage to connect to both the databases. The reading part is fine but while writing the records into MSSQL Server the job is inserting either nothing OR some weird characters in the VARCHAR columns. I have tried using MSSQL Server and ODBC as database type but nothing worked.

If I replace the target DRS stage with ODBC stage the job works fine.

Job design

DRS-->TRF-->DRS

Below is the entry that we have for the target MSSQL Server in .odbc.ini file in DataStage host machine.
[DSNNAME]
Driver=/home/dsadm/Ascential/DataStage/xxxx/VMmsss19.so
Description=DataDirect SQL Server Wire Protocol
Database=GGGG
LogonID=uid
Password=pwd
Address=XXXXXX,1111
QuotedId=No
AnsiNPW=No

Does anyone have any clue why is this happening.

Regards,
-Sumit

[ray.wurlod]

Are they really VarChar in SQL Server, or NVarChar?

[roy]

Hi,
DRS stage has 2 ways of working:
1. native when you have specific DB type like Oracle
2. ODBC for other options.

did you choose the 1st option in your DRS (=MSSQL Server)?

(is there any native connection to MSSQL Server via unix?? )

[sumitgulati]

Ray, the fields are Varchar.

Roy, I tried with MSSQL Server first and got weird characters in one Varchar field. For the other Varchar filed nothing got populated.
When I tried with ODBC option all the Varchar field were blank in the target.