Page 1 of 1
driver does not support quoted identifiers in SQL statements
Posted: Mon Aug 13, 2012 3:38 pm
by jweir
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.
Posted: Mon Aug 13, 2012 3:42 pm
by jweir
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)
Posted: Mon Aug 13, 2012 4:30 pm
by jweir
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)
Posted: Tue Aug 14, 2012 12:37 am
by ArndW
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?
Posted: Tue Aug 14, 2012 6:26 am
by jweir
Thanks for getting back to me.
The value of the parameter at run-time is: 1800-01-01 16:07:48
Posted: Tue Aug 14, 2012 6:47 am
by ArndW
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)"
Posted: Tue Aug 14, 2012 6:57 am
by jweir
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.
Posted: Tue Aug 14, 2012 7:03 am
by BI-RMA
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.
Posted: Tue Aug 14, 2012 7:05 am
by jweir
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.
Posted: Tue Aug 14, 2012 7:22 am
by jweir
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.
Posted: Tue Aug 14, 2012 7:56 am
by jweir
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...
Posted: Tue Aug 14, 2012 8:42 am
by jweir
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.
Posted: Tue Aug 14, 2012 1:22 pm
by ArunaDas_Maharana
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
Posted: Thu Aug 16, 2012 1:36 am
by ArndW
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.