Unable to view data from OLDB stage for a user defined query

[Satwika]

Hi ,

I am using ODBC stage as input stage .
I am using mention below query as user defined query.
DECLARE @AGGR_DT AS DATETIME
SELECT @AGGR_DT=CONVERT(DATETIME,LTrim(RTrim(JOB_PARM_VAL))) FROM TEMP_JOB_CONFIG WHERE JOB_PARM_NM='AGGR_DT'
SELECT top 10 EXTRNL_SCTY_ID,STCK_EXCH_ID,SCTY_ID_TYP,LTrim(RTrim(RBCDS_ID)) as RBCDS_ID into #SCTY_XREF_TEMP8 FROM SCTY_XREF WHERE SCTY_ID_TYP='T' AND STRT_DT<=@AGGR_DT AND END_DT>=@AGGR_DT;

SELECT EXTRNL_SCTY_ID,STCK_EXCH_ID,SCTY_ID_TYP,LTrim(RTrim(RBCDS_ID)) as RBCDS_ID FROM #SCTY_XREF_TEMP8

When a try to view data its giving error as mention below.

GSAP_LOOKUP_CreateLkpForFormatJobs_change_1..ODBC_110.ToBuildSctyXrefLkp1: DSD.BCIOpenR call to function SQLExecDirect failed.
Statement was:DECLARE @AGGR_DT AS DATETIME
SELECT @AGGR_DT=CONVERT(DATETIME,LTrim(RTrim(JOB_PARM_VAL))) FROM TEMP_JOB_CONFIG WHERE JOB_PARM_NM='AGGR_DT'
SELECT top 10 EXTRNL_SCTY_ID,STCK_EXCH_ID,SCTY_ID_TYP,LTrim(RTrim(RBCDS_ID)) as RBCDS_ID into #SCTY_XREF_TEMP8 FROM SCTY_XREF WHERE SCTY_ID_TYP='T' AND STRT_DT<=@AGGR_DT AND END_DT>=@AGGR_DT;

SELECT EXTRNL_SCTY_ID,STCK_EXCH_ID,SCTY_ID_TYP,LTrim(RTrim(RBCDS_ID)) as RBCDS_ID FROM #SCTY_XREF_TEMP8
SQLSTATE=S0002, DBMS.CODE=208
[DataStage][SQL Client][ODBC][IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Invalid object name '#SCTY_XREF_TEMP8'.
SQLSTATE=37000, DBMS.CODE=8180
[DataStage][SQL Client][ODBC][IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

Please help me.This query is working fine when i run it on sqlserver.

[chulett]

It seems you have more than one statement, which is problematic. And the fact that it 'runs fine' outside of DataStage doesn't mean a whole lot if it is not properly constructed for what it is being used for inside DataStage.

However, the error is Invalid object name '#SCTY_XREF_TEMP8' so I'd suggest you deal with that first. What it is supposed to be?

[ray.wurlod]

Column names that contain "#" or "$" need special handling. This is completely documented in the Information Center and in the manuals.

[rameshrr3]

Im wondering if the identifier '#SCTY_XREF_TEMP8' should have been #SCTY_XREF_TEMP8#' ?? just in case it was meant to be a job parameter