Dynamic RDBMS Stage SQL Server

[als110]

I am trying to load data to a sql server db. I have been successful loading data to a table without dates through datastage. I have also been successful with adding rows through query analyzer. My issue is that in my job my dates have the following format as a varchar(8) yyyymmdd.

I try to pass the varchar(8) into the RDBMS stage and then do a convert. below is the syntax, none of them work through datastage I just get a general query error and the job aborts

I have tried all three of the following and none of htem work:

INSERT INTO #IVRSCHEMA#.#TgtTablePolicy# (SourceSystemId,PolicyId,PolicyEffectiveDate,PolicyName,Company,LineOfBusiness,PolicyStatus,PolicyIssueState,PolicyAddressZip,CreateDate,LastUpdateDate) VALUES (:1,:2,convert(datetime, :3, 121),:4,:5,:6,:7,:8,:9,convert(datetime, :10, 121),convert(datetime, :11, 121))

INSERT INTO #IVRSCHEMA#.#TgtTablePolicy# (SourceSystemId,PolicyId,PolicyEffectiveDate,PolicyName,Company,LineOfBusiness,PolicyStatus,PolicyIssueState,PolicyAddressZip,CreateDate,LastUpdateDate) VALUES (:1,:2,convert(datetime, '20050110', 121),:4,:5,:6,:7,:8,:9,convert(datetime, '20050110', 121),convert(datetime, '20050110', 121))

INSERT INTO #IVRSCHEMA#.#TgtTablePolicy# (SourceSystemId,PolicyId,PolicyEffectiveDate,PolicyName,Company,LineOfBusiness,PolicyStatus,PolicyIssueState,PolicyAddressZip,CreateDate,LastUpdateDate) VALUES (:1,:2,convert(datetime, PolicyEffectiveDate, 121),:4,:5,:6,:7,:8,:9,convert(datetime, CreateDate, 121),convert(datetime, LastUpdateDate, 121))

Here is the statement I used in query analyzer and this inserted a record:
INSERT INTO IVRPolicyDataMart.dbo.Policy
(SourceSystemId,PolicyId,PolicyEffectiveDate,PolicyName,Company,LineOfBusiness,PolicyStatus,PolicyIssueState,PolicyAddressZip,CreateDate,LastUpdateDate)
VALUES ('GMS','0000123456',convert(datetime, '20060110', 121),'Aaron','JALC','SG','TRM'
,'PA','15636',convert(datetime, '20060110', 121),convert(datetime, '20060110', 121));

Anyone have any suggestions?

Thanks

aaron

[ray.wurlod]

Please post the warning message that is generated. Clearly if the SQL works from query analyzer and not from DataStage, either it's not exactly the same SQL from DataStage (you can verify using stage tracing or SQL tracing), or something unexpected is occurring with the data. Only by inspecting the warning can we decide which is which.

[als110]

INSERT INTO IVRPolicyDataMart.dbo.Policy (SourceSystemId,PolicyId,PolicyEffectiveDate,PolicyName,Company,LineOfBusiness,PolicyStatus,PolicyIssueState,PolicyAddressZip,CreateDate,LastUpdateDate) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11)

[als110]

IVR,0: Warning: IVR: [DataDirect][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ':'.

IVR,0: Warning: IVR: [DataDirect][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

APT_CombinedOperatorController,0: Failure during execution of operator logic

APT_CombinedOperatorController,0: Fatal Error: Fatal: SQLPrepare: Failed to prepare SQL statement: INSERT INTO IVRPolicyDataMart.dbo.Policy (SourceSystemId,PolicyId,PolicyEffectiveDate,PolicyName,Company,LineOfBusiness,PolicyStatus,PolicyIssueState,PolicyAddressZip,CreateDate,LastUpdateDate) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11).

[ray.wurlod]

I don't seen any convert functions in that SQL - do you?

[als110]

I dont do it in the sql i do it in a transformer. I have tried it within the sql but im not sure if im using incorrect syntax but that doesnt load either

[ameyvaidya]

IVR,0: Warning: IVR: [DataDirect][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ':'.

INSERT INTO IVRPolicyDataMart.dbo.Policy (SourceSystemId,PolicyId,PolicyEffectiveDate,PolicyName,Company,LineOfBusiness,PolicyStatus,PolicyIssueState,PolicyAddressZip,CreateDate,LastUpdateDate) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11).

I COULD be wrong.... but
For parallel Jobs, I distinctly remember passing values as ORCHESTRATE.<InCol> (Atleast as far as the Oracle enterprise stage is concerned.).

Do not have access to PX... Someone with PX access could validate this???

Recommend starting off with the auto-generated Insert SQL as a base and then making modifications.