DSXchange

Hi guys,
I am encountering a small issue with inserting records into sql server using the DRS stage. Its working fine with the ODBC stage as my target stage but i hate that stage, totally kills the performance. When i use DRS stage as my target, it gives me the following error message and aborts.

warning msg:
odbcTst..Dynamic_RDBMS_20: [DataDirect][ODBC SQL Server Driver]Optional feature not implemented

fatal msg:
odbcTst..Dynamic_RDBMS_20: SQLBindParameter: Failed to bind a parameter.

any idea guyz???
thanks.

Seems like you are still connecting to the database using ODBC, from the log. IMHO whenever you see this "Optional feature not implemented" message from ODBC then you have not specified the correct column metadata. Reload it from the table definitions.

Hey Bala,
I dont think its the metadata issue. I tried to build another job, a straight load between two drs stages. Its still aborting with the fatal message pointing towards the target DRS stage.

In the DRS are you specifying SQL Server or ODBC as the connectivity mode? If the former, what happens when you try ODBC within DRS?

Are you 100% confident that the table definition on the link exactly matches what was imported from SQL Server? Could it have been imported from an earlier version of the table?

Hi Ray,
I tried to put odbc in drs, both source and target. still giving me the same error. I tried deleting the meta data and importing it again. still the same error.
actually my dba built the staging tables as mirror images of the target tables. i am running a test job as

target table ---->xfm-------->stg table

i even tried to import the exact same metadata for both source and target, i mean both source and target tables had metadata imported from target table. still the same error.
kinda stuck!!!

Are you using generated or user-defined SQL? A bind variable issue usually means a column count mismatch between the metadata and the SQL. A "Optional feature not implemented" message means that the SQL is not supported via the driver.

i am using generatel sql. if there is any kind of metadata mismatch. wont loading both the source and target drs with a particular table solve it. i even tried that. is this really ds or sql server problem???

Okay, but what's the SQL statement, can we take a look at that?

sure, the sql generated in the source is

SELECT POL_ID,POL_INSTNC_ID,CRETD_OPER_ID,TO_CHAR(CRETD_DT, 'YYYY-MM-DD HH24:MI:SS'),SOAR_OPPOR_DOC_ID,EST_INSRD_LIVES_NUM,TO_CHAR(POL_LAST_RENL_DT, 'YYYY-MM-DD HH24:MI:SS'),TO_CHAR(POL_NEXT_RENL_DT, 'YYYY-MM-DD HH24:MI:SS'),PH_LEGAL_NM,POL_DESC_TXT,TO_CHAR(POL_EFF_DT, 'YYYY-MM-DD HH24:MI:SS'),TO_CHAR(POL_STAT_DT, 'YYYY-MM-DD HH24:MI:SS'),SLS_OFFC_CD,EXCLD_IND,POL_STAT_CD,CIGNA_CO_CD,ISSUE_STATE_CD,ADMIN_CD,CLM_OFFC_CD,EARLY_WARNG_RTNG_CD,PERFMNC_GUAR_IND,SRC_SYS_CD,TO_CHAR(ANNVSY_MTHDAY_DT, 'YYYY-MM-DD HH24:MI:SS'),COMM_TBL_1_CD,COMM_TBL_2_CD,COMM_TBL_3_CD,MANL_COMM_IND,SIC_CD,ACCT_TY_CD,QRF_CASE_ID,TO_CHAR(LAST_UPD_DT, 'YYYY-MM-DD HH24:MI:SS'),LAST_OPER_ID,QRF_CASE_NM FROM CGI_IDB.Policy_BOR.POLICY
*************************************************************

and the sql generated in the target is:

INSERT INTO CGI_IDB.Prdcr_Cmp.STG_POLICY (POL_ID,POL_INSTNC_ID,CRETD_OPER_ID,CRETD_DT,SOAR_OPPOR_DOC_ID,EST_INSRD_LIVES_NUM,POL_LAST_RENL_DT,POL_NEXT_RENL_DT,PH_LEGAL_NM,POL_DESC_TXT,POL_EFF_DT,POL_STAT_DT,SLS_OFFC_CD,EXCLD_IND,POL_STAT_CD,CIGNA_CO_CD,ISSUE_STATE_CD,ADMIN_CD,CLM_OFFC_CD,EARLY_WARNG_RTNG_CD,PERFMNC_GUAR_IND,SRC_SYS_CD,ANNVSY_MTHDAY_DT,COMM_TBL_1_CD,COMM_TBL_2_CD,COMM_TBL_3_CD,MANL_COMM_IND,SIC_CD,ACCT_TY_CD,QRF_CASE_ID,LAST_UPD_DT,LAST_OPER_ID,QRF_CASE_NM) VALUES (:1,:2,:3,TO_DATE(:4, 'YYYY-MM-DD HH24:MI:SS'),:5,:6,TO_DATE(:7, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:8, 'YYYY-MM-DD HH24:MI:SS'),:9,:10,TO_DATE(:11, 'YYYY-MM-DD HH24:MI:SS'),TO_DATE(:12, 'YYYY-MM-DD HH24:MI:SS'),:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,TO_DATE(:23, 'YYYY-MM-DD HH24:MI:SS'),:24,:25,:26,:27,:28,:29,:30,TO_DATE(:31, 'YYYY-MM-DD HH24:MI:SS'),:32,:33)

Nothing funny there. Are you using INSERT ROWS WITHOUT CLEARING as your SQL action? Any chance you're using the TRUNCATE then INSERT?

i am using insert without clearing.....when i use TRUNCATE and then INSERT option , it gives me the following error

drstst..Dynamic_RDBMS_6: [DataDirect][ODBC SQL Server Driver][SQL Server]User does not have permission to perform this operation on table 'CGI_IDB.Prdcr_Cmp.STG_POLICY'.

pretty weird huh!

In Oracle, truncate requires ownership of the table because a truncate is really an ALTER statement.

That possible issue could be a data type on one of your columns. The messages relate to the SQL, so that's about all that's left. I suspect tech support will get a call from you.

i agree, i do not have the permission to truncate the table, thats why its giving me an error on that before it can even go towards the insert command.
If the problem lies in datatype mismatch, then why is it working fine with odbc stage??? thats whats puzzling me.
doesnt the word "BIND" ring a bell in the sense of security issues?

No, BIND means that the prepared SQL has to have aligned streaming data flowing into the BIND variables. If the amount of columns of streaming data doesn't match the number of BIND variables, you get BIND messages. That's why we've focused on the SQL.

okay!!!
well thanks for your time though. I guess i have to talk to my dba about it.