Sql Server error

DSguru2B · Post by **DSguru2B** » Fri Nov 04, 2005 9:43 am

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.

Bala R · Post by **Bala R** » Fri Nov 04, 2005 10:42 am

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.

DSguru2B · Post by **DSguru2B** » Fri Nov 04, 2005 12:17 pm

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.

ray.wurlod · Post by **ray.wurlod** » Fri Nov 04, 2005 3:36 pm

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?

DSguru2B · Post by **DSguru2B** » Mon Nov 07, 2005 2:04 pm

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!!!

kcbland · Post by **kcbland** » Mon Nov 07, 2005 11:32 pm

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.

DSguru2B · Post by **DSguru2B** » Tue Nov 08, 2005 7:10 am

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???

kcbland · Post by **kcbland** » Tue Nov 08, 2005 7:19 am

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

DSguru2B · Post by **DSguru2B** » Tue Nov 08, 2005 7:30 am

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)

kcbland · Post by **kcbland** » Tue Nov 08, 2005 7:38 am

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

DSguru2B · Post by **DSguru2B** » Tue Nov 08, 2005 7:45 am

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!

kcbland · Post by **kcbland** » Tue Nov 08, 2005 7:47 am

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.

DSguru2B · Post by **DSguru2B** » Tue Nov 08, 2005 7:51 am

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?

kcbland · Post by **kcbland** » Tue Nov 08, 2005 8:00 am

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.

DSguru2B · Post by **DSguru2B** » Tue Nov 08, 2005 8:16 am

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