Problems inserting a row in a table with identity column
Moderators: chulett, rschirm, roy
Hi,
I would try defining a view on that table as work-around to try trick DS to think there is no additional column and perform the insert on the view.
Another option if could be concidered is to handle the identity column yourself by using a regular bigint column and supplying the serial value in the DS job rather then letting SQLServer do it.
Regarding the wierd characters, are they by any chance NLS related? (EE on windows has no NLS available yet)
IHTH,
I would try defining a view on that table as work-around to try trick DS to think there is no additional column and perform the insert on the view.
Another option if could be concidered is to handle the identity column yourself by using a regular bigint column and supplying the serial value in the DS job rather then letting SQLServer do it.
Regarding the wierd characters, are they by any chance NLS related? (EE on windows has no NLS available yet)
IHTH,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Re: Problems inserting a row in a table with identity column
You should not have to include the identity column in the insert thorugh datastage, Try the insert directly using SQL statement and not specifying the identty column if it works first. If not what is the error.
Data when tortured long enough will confess to anything
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I vaguely remember that providing zero for the identity column also triggers it to use an auto-incremented value. Or is that Red Brick?
Anyway, not inserting into the identity column at all will work, since "next available" is its default value.
Anyway, not inserting into the identity column at all will work, since "next available" is its default value.
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.
Informix works the same way, from what I vaguely recall, when using its SERIAL field. We would load it with a zero to say "please give me the next available value". You could then query the SQLCA area (this gets even more vaguer) to find out what value it actually assigned if you needed that information downstream.ray.wurlod wrote:I vaguely remember that providing zero for the identity column also triggers it to use an auto-incremented value. Or is that Red Brick?
And before Ray busts me, yes... I know 'vaguer' is not a real word. Kind of hoping the more I use it, the more real it will get.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
all: Thank you for your suggestions. Now I am waiting for DS licence renewal, then I will be able to try to apply them.
Roy: Using a view is an interesting work-around: I will try it. Handling myself the identity column has not worked in any way: Insert row failed is the nicest error I get. NLS is not enabled either in DS (of course) and SQL Server.
rshewale:
ray: It seems that providing zero for the identity column is interpreted by SQL Server like forcing the value. I get Insert row failed.
Craig: see above. In addition:
$> cd /pub
$> join Craig beer
Stefano
Roy: Using a view is an interesting work-around: I will try it. Handling myself the identity column has not worked in any way: Insert row failed is the nicest error I get. NLS is not enabled either in DS (of course) and SQL Server.
rshewale:
I wish I would be able to, but everytime I try, I get the error APT_CombinedOperatorController(0),0: Fatal Error: Fatal: SQL data type '%1' defined on column %2 is not supported. I guess user-defined SQL statements in DRS stages only work with Oracle by now. If my memory does not fail, something similar is also stated in DS documentation.Try the insert directly using SQL statement
ray: It seems that providing zero for the identity column is interpreted by SQL Server like forcing the value. I get Insert row failed.
Craig: see above. In addition:
$> cd /pub
$> join Craig beer
Stefano
As my licence has not been renewed yet, I cannot access at the code at the moment. So we both must trust my memory. However code has the following form:
Notes:
- Code is wrapped for readability purpose, but it is not wrapped in the stage;
- Link dependencies are in the right order and correctly computed;
- SheetID is the identity column, it is the first column in the table and it is not included in the column list of the statement, neither a corresponding value is.
Ready to know about your suspicion
Code: Select all
INSERT INTO ds.dbo.Sheet
(CustCode, JobCode, SrcFileName, SrcPath, TrgFileName,
TrgPath, PostDate, SysTs, AcqDate, CustID,
PrnLnName, DivName, JobID, UsrID, SiteID,
RecNumber, RealRecNum, CustDesc, OpID, Xfer)
values
(?, ?, ?, ?, ?,
?, ?, ?, ?, ?,
?, ?, ?, ?, ?,
?, ?, ?, ?, ?)
- Code is wrapped for readability purpose, but it is not wrapped in the stage;
- Link dependencies are in the right order and correctly computed;
- SheetID is the identity column, it is the first column in the table and it is not included in the column list of the statement, neither a corresponding value is.
Ready to know about your suspicion
This is exactly what I thought happened. The most proper way to insert values within SQL from a Datastage stream is to use the following format:
Basically, you can not use ? for input. You need to specifically state "orchestrate." then the specific input field. It does provide a nice flexibility of using a different name for input field to use to insert on any database field.
Code: Select all
INSERT INTO [table] (field1, field2, field3...)
VALUES (ORCHESTRATE.INPUT_FIELD1, ORCHESTRATE.INPUT_FIELD2, ORCHESTRATE.INPUT_FIELD3...)
Many thanks for your suggestion, I will try as soon as possible.
The form you suggest sounds new to me in user-defined SQL statements, I only knew it as Ascential self-generated code. However, I did not expect it could work when used as user-defined statement, and I did not find it anywhere in the documents provided by Ascential.
The examples I have found for Oracle use the parameter placeholder form (:1, :2 etc.), so I tried to use the corresponding SQL Server form.
Stefano
The form you suggest sounds new to me in user-defined SQL statements, I only knew it as Ascential self-generated code. However, I did not expect it could work when used as user-defined statement, and I did not find it anywhere in the documents provided by Ascential.
The examples I have found for Oracle use the parameter placeholder form (:1, :2 etc.), so I tried to use the corresponding SQL Server form.
Stefano