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
Dynamic RDBMS Stage SQL Server
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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).
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).
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Charter Member
- Posts: 166
- Joined: Wed Mar 16, 2005 6:52 am
- Location: Mumbai, India
als110 wrote: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.
Amey Vaidya<i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>