driver does not support quoted identifiers in SQL statements

jweir · Post by **jweir** » Mon Aug 13, 2012 3:38 pm

Hi all,

I am trying to select a string from a parameter set value file which holds a timestamp. This is my delta date to pull delta records. My source is SQL Server. However, when I try to compare this parameter value to the source column (USTIMESTAMP), it does not pull any records and throws this information in the Director log:

Code: Select all

Connected to Microsoft SQL Server, version 10.00.2531 through driver VMmsss24.so.
The driver does not support quoted identifiers in SQL statements

My Where clause is where my parameter is being used:

Code: Select all

WHERE USTIMESTAMP > convert(timestamp, '#ps_AppraisalFormSection_DeltaDate.EXTRACT_TIMESTAMP#')

Note that I have to convert my parameter to a timestamp since it is stored as a string.

Do I have to use a different driver? Or is my Where clause incorrect? I know this same job was working months ago, however I ran it today and cannot get it to work.

Thanks in advance.

jweir · Post by **jweir** » Mon Aug 13, 2012 3:42 pm

Here is the fatal error I receive when I do not try and convert the parameter to timestamp:

Code: Select all

ODBC function "SQLExecute" reported:  SQLSTATE = 22007: Native Error Code = 241: Msg = [IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string. (CC_OdbcDBStatement::executeSelect, file CC_OdbcDBStatement.cpp, line 1,389)

jweir · Post by **jweir** » Mon Aug 13, 2012 4:30 pm

Update. I have gotten rid of the first problem. I altered the odbc.ini file to include quoted identifiers. The line in the Director log is no longer there.

However, my job still aborts due to the error:

Code: Select all

ODBC function "SQLExecute" reported:  SQLSTATE = 22007: Native Error Code = 241: Msg = [IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string. (CC_OdbcDBStatement::executeSelect, file CC_OdbcDBStatement.cpp, line 1,389)

ArndW · Post by **ArndW** » Tue Aug 14, 2012 12:37 am

The first message is just a warning and isn't applicable to your problem. What is the value of #ps_AppraisalFormSection_DeltaDate.EXTRACT_TIMESTAMP# at runtime?

jweir · Post by **jweir** » Tue Aug 14, 2012 6:26 am

Thanks for getting back to me.

The value of the parameter at run-time is: 1800-01-01 16:07:48

ArndW · Post by **ArndW** » Tue Aug 14, 2012 6:47 am

Is your SQL-Server datatype "datetime" or "datetime2"?

Oh, I just saw the error - your convert() is backwards... plus you don't want to convert to a timestamp in SQL-Server, that is a very different type of field - you want datetime or datetime2

try convert('#ps_AppraisalFormSection_DeltaDate.EXTRACT_TIMESTAMP#',datetime2,20)"

Note that the 20 is the ODBC type for "yyyy-mm-dd hh:mi:ss(24h)"

jweir · Post by **jweir** » Tue Aug 14, 2012 6:57 am

I have tried what you have suggested. However, I get the following fatal error:

Code: Select all

ODBC function "SQLExecute" reported:  SQLSTATE = 42000: Native Error Code = 102: Msg = [IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '#ps_AppraisalFormSection_DeltaDate.EXTRACT_TIMESTAMP#'.
ODBC function "SQLExecute" reported:  SQLSTATE = 42000: Native Error Code = 8,180: Msg = [IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (CC_OdbcDBStatement::executeSelect, file CC_OdbcDBStatement.cpp, line 1,389)

What should the datatype be in my table definition? I still have it as timestamp, as there is no selection for datetime data type.

BI-RMA · Post by **BI-RMA** » Tue Aug 14, 2012 7:03 am

Did you look up the correct Style-value for the conversion you want?

I do not use SQL-Server a lot, but there are dozens of conversion formats and it may be necessary to specify the correct one corresponding to your data.

jweir · Post by **jweir** » Tue Aug 14, 2012 7:05 am

Scratch my last post. Since it is in my WHERE clause, it has no place in my table definition. I got one of my test jobs mixed in with the final version.

jweir · Post by **jweir** » Tue Aug 14, 2012 7:22 am

Here is my select statement that is generated by DataStage in the log.

Code: Select all

sql_SRC_APPRAISALFORMSECTION,0: Execute failed on statement SELECT
HRORGANIZATION , 
APPRAISALFORM ,  
APPRAISALFORMSECTION ,  
CSTIMESTAMP ,  
CSACTOR ,  
USTIMESTAMP ,  
USACTOR ,  
CONVERT(int, DELETEFLAG) as DELETEFLAG ,  
APPRAISALSECTION , 
MANAGER ,  
SELF ,  
PEER ,  
WEIGHT ,  
DISPLAYSEQUENCE , 
APPRAISALSECTIONTYPE , 
MAXIMUMTHIRDPARTYSCORE ,  
SINGLEUSESECTIONTYPE,
(select max(USTIMESTAMP) from hcm.APPRAISALFORMSECTION) as EXTRACT_TIMESTAMP
 FROM hcm.APPRAISALFORMSECTION
WHERE USTIMESTAMP >  convert('#ps_AppraisalFormSection_DeltaDate.EXTRACT_TIMESTAMP#',datetime,20)

The USTIMESTAMP column that I am comparing the parameter to is a datetime data type.

jweir · Post by **jweir** » Tue Aug 14, 2012 7:56 am

It definitely has to do with the parameter. I hardcoded the time and I was able to pull rows:

Code: Select all

WHERE USTIMESTAMP >  convert(datetime,'1800-01-01 16:07:48')

Do I have to do something unique when I am dealing with parameter set value files? Since I am selecting the time from a value file of a parameter set...

jweir · Post by **jweir** » Tue Aug 14, 2012 8:42 am

My problem has been magically fixed. I re-ran my job and it now pulls the rows I need. The only thing I did was cleared out my SELECT statement and made sure I copied and pasted my entire parameter. Here is my code that works. Let me know if someone spots something that is different from what I had before, but I could not see anything:

Code: Select all

SELECT
HRORGANIZATION , 
APPRAISALFORM ,  
APPRAISALFORMSECTION ,  
CSTIMESTAMP ,  
CSACTOR ,  
USTIMESTAMP ,  
USACTOR ,  
CONVERT(int, DELETEFLAG) as DELETEFLAG ,  
APPRAISALSECTION , 
MANAGER ,  
SELF ,  
PEER ,  
WEIGHT ,  
DISPLAYSEQUENCE , 
APPRAISALSECTIONTYPE , 
MAXIMUMTHIRDPARTYSCORE ,  
SINGLEUSESECTIONTYPE,
(select max(USTIMESTAMP) from #ps_EDWTEST_STG_PROJDEF.$SQL_SERVER_LMHCM_SCHEMA#.APPRAISALFORMSECTION) as EXTRACT_TIMESTAMP
 FROM #ps_EDWTEST_STG_PROJDEF.$SQL_SERVER_LMHCM_SCHEMA#.APPRAISALFORMSECTION
WHERE USTIMESTAMP >  convert(datetime,'#ps_AppraisalFormSection_Delta_Date.EXTRACT_TIMESTAMP#')

Marking as resolved.

ArunaDas_Maharana · Post by **ArunaDas_Maharana** » Tue Aug 14, 2012 1:22 pm

yes , you have 20 extra earlier

Error code
WHERE USTIMESTAMP > convert('#ps_AppraisalFormSection_DeltaDate.EXTRACT_TIMESTAMP#',datetime,20)

WHERE USTIMESTAMP > convert(datetime,'#ps_AppraisalFormSection_Delta_Date.EXTRACT_TIMESTAMP#')

Also the sequence of parameter

ArndW · Post by **ArndW** » Thu Aug 16, 2012 1:36 am

ArunaDas_Maharana - that "20" is an optional parameter the SQL-Server "Convert()" which specifies the string format. It should be used to make sure that no incorrect parsing of the string date is made.