Sql Server error
Moderators: chulett, rschirm, roy
Sql Server error
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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?
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?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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!!!
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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)
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?
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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!
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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?
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle