Error: duplicate key row

What might cause the following error when inserting rows in tables that have primary keys defined as IDENTITY (1,1)?

duplicate key row in object 'aa' with unique index 'aa'

I have seen this happen frequently when data has been bulk loaded sometime in the past into a table that has an IDENTITY key. This is especially common when you have a test environment in which some production data was bulk loaded to give some "good test data." To me it means that an identity number has "already been used" as a record key.

Dave

|||The rows are inserted one at a time using the parent IDENTITY key as a FK constraint in the child table which also has an IDENTITY KEY. However, this scenario does not sound like it would cause an error.|||

Can you post the table structures? That would help us to see what your problem might be.

If you can post a few statements that cause the duplicates, that would even be better.

|||

If you have the property NOT FOR REPLICATION enabled for the identity column.

Merging changes would allow the exact id values to be inserted rather than a new value that would give the error if same id value exists in the participating server.

similarly, if you are trying to insert manually using SET IDENTITY_INSERT table ON...

Error: 1204 Severity: 19 State: 1

I am relatively new to SQL and I need help please!
On a 4GB table, with the largest table having 9 million
rows and recently started getting Error 1204 Severity 19
State 1,
I know it indicates a nonconfigurable resource limit has
been exceeded, but exactly what does nonconfigurable
resource mean?
What is happening on the server is blocking, not
deadlocking, but I'm concerned how do I fix this before it
gets worse. I run DBCC Checkdb every night and have
defragged the indexes on the larger tables. Once a week
I've been running DBCC reindex as well.
Increase the amount of locks? The min set now is 5000 and
the max is 214748367.
Thanks in advance for help,
Josephine.Go thru this KBA
http://support.microsoft.com/default.aspx?scid=%2Fservicedesks%2Fbin%2Fkbsearch.asp%3FArticle%3D323630
--
Thanks,
Lyudmila Fokina
Please do not send e-mail directly to this alias. This alias is for
newsgroup purposes only
Disclamer: This posting is provided "AS IS" with no warranties, and confers
no rights.
"Josephine" <anonymous@.discussions.microsoft.com> wrote in message
news:1ab5b01c44f20$9fbd0810$a301280a@.phx.gbl...
> I am relatively new to SQL and I need help please!
> On a 4GB table, with the largest table having 9 million
> rows and recently started getting Error 1204 Severity 19
> State 1,
> I know it indicates a nonconfigurable resource limit has
> been exceeded, but exactly what does nonconfigurable
> resource mean?
> What is happening on the server is blocking, not
> deadlocking, but I'm concerned how do I fix this before it
> gets worse. I run DBCC Checkdb every night and have
> defragged the indexes on the larger tables. Once a week
> I've been running DBCC reindex as well.
> Increase the amount of locks? The min set now is 5000 and
> the max is 214748367.
> Thanks in advance for help,
> Josephine.|||Look at here:
http://support.microsoft.com/default.aspx?scid=%2Fservicedesks%2Fbin%2Fkbsearch.asp%3FArticle%3D323630
--
Thanks,
Lyudmila Fokina
Please do not send e-mail directly to this alias. This alias is for
newsgroup purposes only
Disclamer: This posting is provided "AS IS" with no warranties, and confers
no rights.
"Josephine" <anonymous@.discussions.microsoft.com> wrote in message
news:1ab5b01c44f20$9fbd0810$a301280a@.phx.gbl...
> I am relatively new to SQL and I need help please!
> On a 4GB table, with the largest table having 9 million
> rows and recently started getting Error 1204 Severity 19
> State 1,
> I know it indicates a nonconfigurable resource limit has
> been exceeded, but exactly what does nonconfigurable
> resource mean?
> What is happening on the server is blocking, not
> deadlocking, but I'm concerned how do I fix this before it
> gets worse. I run DBCC Checkdb every night and have
> defragged the indexes on the larger tables. Once a week
> I've been running DBCC reindex as well.
> Increase the amount of locks? The min set now is 5000 and
> the max is 214748367.
> Thanks in advance for help,
> Josephine.|||Don't look at min and max for sp_configure. Look at configured and running. For locks, it should be 0 (auto).
The updated books online has some rather details info on 1204:
Error 1204
Severity Level 19
Message Text
The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active
users or ask the system administrator to check the SQL Server lock and memory configuration.
Explanation
This error occurs when there are not enough system locks to complete the current command. SQL Server then
attempts to obtain a LOCK block to represent and control the desired lock. When dynamically configured, the
lock limit is determined by the available memory. When statically configured, the lock limit is determined by
the sp_configure setting.
If you continue to encounter this problem, make sure your statistics are up to date, you have sufficient
indexes to run your query efficiently, and that the transaction isolation level for your application is not
more restrictive than necessary.
Action
Either execute the command again when activity on the server is low, or have the system administrator increase
the number of locks by executing sp_configure from the master database.
To view the current configuration:
sp_configure locks
GO
This reports the minimum, maximum, current run, and configuration values. To increase the number of locks, run
sp_configure again, specifying the number of locks to be configured. For example, to configure 10,000 locks:
sp_configure locks, 10000
GO
RECONFIGURE WITH OVERRIDE
GO
Stop and restart Microsoft® SQL ServerT so the changes can take effect. Locks are allocated at system startup.
If the number of locks cannot be increased at the current time, and the single action requires more locks than
the server is currently configured for, you may be able to reduce the number of locks required for the
operation. For example, try the following:
a.. For large UPDATE statements, break the updates into smaller units that will affect only a subset of
records at a time. For example, you could use the primary key, changing the single UPDATE statement from:
UPDATE employees
SET salary = salary * 1.05
WHERE employee_id BETWEEN 1000 AND 9999
GO
to several UPDATE statements:
UPDATE employees
SET salary = salary * 1.05
WHERE employee_id BETWEEN 1000 AND 4999
GO
UPDATE employees
SET salary = salary * 1.05
WHERE employee_id BETWEEN 5000 AND 9999
GO
a.. For a maintenance type of task or for a global update, consider putting the database into single-user mode
(if it is feasible to keep other users out of the database). Single-user mode does not set locks, so you will
not run out of locks, and the operation will run somewhat faster (because you save the locking overhead).
b.. For a large bulk copy operation, the entire operation is treated as a single transaction. When you use
the batch parameter (-b), the bcp utility will treat the operation in small transactions with the number of
rows specified. At the end of each small transaction, the system resources held by that transaction are freed,
so fewer locks are needed.
See Also
Understanding and Avoiding Blocking
bcp Utility
BULK INSERT
Errors 1000 - 1999
Setting Configuration Options
sp_configure
Starting, Pausing, and Stopping SQL Server
UPDATE
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Josephine" <anonymous@.discussions.microsoft.com> wrote in message
news:1ab5b01c44f20$9fbd0810$a301280a@.phx.gbl...
> I am relatively new to SQL and I need help please!
> On a 4GB table, with the largest table having 9 million
> rows and recently started getting Error 1204 Severity 19
> State 1,
> I know it indicates a nonconfigurable resource limit has
> been exceeded, but exactly what does nonconfigurable
> resource mean?
> What is happening on the server is blocking, not
> deadlocking, but I'm concerned how do I fix this before it
> gets worse. I run DBCC Checkdb every night and have
> defragged the indexes on the larger tables. Once a week
> I've been running DBCC reindex as well.
> Increase the amount of locks? The min set now is 5000 and
> the max is 214748367.
> Thanks in advance for help,
> Josephine.|||Thank you Tibor, that was helpful. I did the sp_configure and the locks are at 0, so they are configured dynamically. The RAM available right now with no blocking is 22MB. So that may be something to look into. Transaction Isolation Level? I'm not familiar with it. Unfortunately, all this is going on through an application that our company didn't write. I have to figure out how the query is generated through the application to isolate how much data is being processed. One thing that makes me think the app is really a mess is I have in the app error logs "Insert Statement conflicted with Foreign Key Constraint". By the way, the same people who wrote the app, created the database.
Turning on Profiler has killed the server, resource wise for me to determine the root. I don't think the application accesses the database well, it is only update 100 rows an hour..but I'm not getting much help from their support department as they think this is a database issue. I also think there may be some user error going on. Like trying to process multiple times or something. I guess what I'm looking for, is assurance that I'm not missing doing something from my end. Any thoughts? I'm checking into the RAM issue for sure. Thanks, Josephine.
>--Original Message--
>Don't look at min and max for sp_configure. Look at configured and running. For locks, it should be 0 (auto).
>The updated books online has some rather details info on 1204:
>Error 1204
>Severity Level 19
>Message Text
>The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active
>users or ask the system administrator to check the SQL Server lock and memory configuration.
>Explanation
>This error occurs when there are not enough system locks to complete the current command. SQL Server then
>attempts to obtain a LOCK block to represent and control the desired lock. When dynamically configured, the
>lock limit is determined by the available memory. When statically configured, the lock limit is determined by
>the sp_configure setting.
>If you continue to encounter this problem, make sure your statistics are up to date, you have sufficient
>indexes to run your query efficiently, and that the transaction isolation level for your application is not
>more restrictive than necessary.
>Action
>Either execute the command again when activity on the server is low, or have the system administrator increase
>the number of locks by executing sp_configure from the master database.
>To view the current configuration:
>sp_configure locks
>GO
>This reports the minimum, maximum, current run, and configuration values. To increase the number of locks, run
>sp_configure again, specifying the number of locks to be configured. For example, to configure 10,000 locks:
>sp_configure locks, 10000
>GO
>RECONFIGURE WITH OVERRIDE
>GO
>Stop and restart Microsoft=AE SQL ServerT so the changes can take effect. Locks are allocated at system startup.
>If the number of locks cannot be increased at the current time, and the single action requires more locks than
>the server is currently configured for, you may be able to reduce the number of locks required for the
>operation. For example, try the following:
> a.. For large UPDATE statements, break the updates into smaller units that will affect only a subset of
>records at a time. For example, you could use the primary key, changing the single UPDATE statement from:
>UPDATE employees
>SET salary =3D salary * 1.05
>WHERE employee_id BETWEEN 1000 AND 9999
>GO
>to several UPDATE statements:
>UPDATE employees
>SET salary =3D salary * 1.05
>WHERE employee_id BETWEEN 1000 AND 4999
>GO
>UPDATE employees
>SET salary =3D salary * 1.05
>WHERE employee_id BETWEEN 5000 AND 9999
>GO
>a.. For a maintenance type of task or for a global update, consider putting the database into single-user mode
>(if it is feasible to keep other users out of the database). Single-user mode does not set locks, so you will
>not run out of locks, and the operation will run somewhat faster (because you save the locking overhead).
>
> b.. For a large bulk copy operation, the entire operation is treated as a single transaction. When you use
>the batch parameter (-b), the bcp utility will treat the operation in small transactions with the number of
>rows specified. At the end of each small transaction, the system resources held by that transaction are freed,
>so fewer locks are needed.
>See Also
>Understanding and Avoiding Blocking
>bcp Utility
>BULK INSERT
>Errors 1000 - 1999
>Setting Configuration Options
>sp_configure
>Starting, Pausing, and Stopping SQL Server
>UPDATE
>
>-- >Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Josephine" <anonymous@.discussions.microsoft.com> wrote in message
>news:1ab5b01c44f20$9fbd0810$a301280a@.phx.gbl...
>> I am relatively new to SQL and I need help please!
>> On a 4GB table, with the largest table having 9 million
>> rows and recently started getting Error 1204 Severity 19
>> State 1,
>> I know it indicates a nonconfigurable resource limit has
>> been exceeded, but exactly what does nonconfigurable
>> resource mean?
>> What is happening on the server is blocking, not
>> deadlocking, but I'm concerned how do I fix this before it
>> gets worse. I run DBCC Checkdb every night and have
>> defragged the indexes on the larger tables. Once a week
>> I've been running DBCC reindex as well.
>> Increase the amount of locks? The min set now is 5000 and
>> the max is 214748367.
>> Thanks in advance for help,
>> Josephine.
>
>.
>|||Look into the RAM issue. Except for that, locking, blocking and deadlocking problems are application problems
that need to be addresses by the application developers.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message news:1c3dc01c4524a$d63e2130$a501280a@.phx.gbl...
Thank you Tibor, that was helpful.
I did the sp_configure and the locks are at 0, so they are
configured dynamically. The RAM available right now with
no blocking is 22MB. So that may be something to look
into. Transaction Isolation Level? I'm not familiar with
it. Unfortunately, all this is going on through an
application that our company didn't write. I have to
figure out how the query is generated through the
application to isolate how much data is being processed.
One thing that makes me think the app is really a mess is
I have in the app error logs "Insert Statement conflicted
with Foreign Key Constraint". By the way, the same people
who wrote the app, created the database.
Turning on Profiler has killed the server, resource wise
for me to determine the root.
I don't think the application accesses the database well,
it is only update 100 rows an hour..but I'm not getting
much help from their support department as they think this
is a database issue. I also think there may be some user
error going on. Like trying to process multiple times or
something.
I guess what I'm looking for, is assurance that I'm not
missing doing something from my end. Any thoughts?
I'm checking into the RAM issue for sure.
Thanks,
Josephine.
>--Original Message--
>Don't look at min and max for sp_configure. Look at
configured and running. For locks, it should be 0 (auto).
>The updated books online has some rather details info on
1204:
>Error 1204
>Severity Level 19
>Message Text
>The SQL Server cannot obtain a LOCK resource at this
time. Rerun your statement when there are fewer active
>users or ask the system administrator to check the SQL
Server lock and memory configuration.
>Explanation
>This error occurs when there are not enough system locks
to complete the current command. SQL Server then
>attempts to obtain a LOCK block to represent and control
the desired lock. When dynamically configured, the
>lock limit is determined by the available memory. When
statically configured, the lock limit is determined by
>the sp_configure setting.
>If you continue to encounter this problem, make sure your
statistics are up to date, you have sufficient
>indexes to run your query efficiently, and that the
transaction isolation level for your application is not
>more restrictive than necessary.
>Action
>Either execute the command again when activity on the
server is low, or have the system administrator increase
>the number of locks by executing sp_configure from the
master database.
>To view the current configuration:
>sp_configure locks
>GO
>This reports the minimum, maximum, current run, and
configuration values. To increase the number of locks, run
>sp_configure again, specifying the number of locks to be
configured. For example, to configure 10,000 locks:
>sp_configure locks, 10000
>GO
>RECONFIGURE WITH OVERRIDE
>GO
>Stop and restart Microsoft® SQL ServerT so the changes
can take effect. Locks are allocated at system startup.
>If the number of locks cannot be increased at the current
time, and the single action requires more locks than
>the server is currently configured for, you may be able
to reduce the number of locks required for the
>operation. For example, try the following:
> a.. For large UPDATE statements, break the updates into
smaller units that will affect only a subset of
>records at a time. For example, you could use the primary
key, changing the single UPDATE statement from:
>UPDATE employees
>SET salary = salary * 1.05
>WHERE employee_id BETWEEN 1000 AND 9999
>GO
>to several UPDATE statements:
>UPDATE employees
>SET salary = salary * 1.05
>WHERE employee_id BETWEEN 1000 AND 4999
>GO
>UPDATE employees
>SET salary = salary * 1.05
>WHERE employee_id BETWEEN 5000 AND 9999
>GO
>a.. For a maintenance type of task or for a global
update, consider putting the database into single-user mode
>(if it is feasible to keep other users out of the
database). Single-user mode does not set locks, so you will
>not run out of locks, and the operation will run somewhat
faster (because you save the locking overhead).
>
> b.. For a large bulk copy operation, the entire
operation is treated as a single transaction. When you use
>the batch parameter (-b), the bcp utility will treat the
operation in small transactions with the number of
>rows specified. At the end of each small transaction, the
system resources held by that transaction are freed,
>so fewer locks are needed.
>See Also
>Understanding and Avoiding Blocking
>bcp Utility
>BULK INSERT
>Errors 1000 - 1999
>Setting Configuration Options
>sp_configure
>Starting, Pausing, and Stopping SQL Server
>UPDATE
>
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Josephine" <anonymous@.discussions.microsoft.com> wrote
in message
>news:1ab5b01c44f20$9fbd0810$a301280a@.phx.gbl...
>> I am relatively new to SQL and I need help please!
>> On a 4GB table, with the largest table having 9 million
>> rows and recently started getting Error 1204 Severity 19
>> State 1,
>> I know it indicates a nonconfigurable resource limit has
>> been exceeded, but exactly what does nonconfigurable
>> resource mean?
>> What is happening on the server is blocking, not
>> deadlocking, but I'm concerned how do I fix this before
it
>> gets worse. I run DBCC Checkdb every night and have
>> defragged the indexes on the larger tables. Once a week
>> I've been running DBCC reindex as well.
>> Increase the amount of locks? The min set now is 5000
and
>> the max is 214748367.
>> Thanks in advance for help,
>> Josephine.
>
>.
>

Error... Invalid object name data1.

I have a db that has a table x in it called data1

I have a program that does to things, updates values in the data1
table and also inserts new rows into this table. The update existing
values works great. Then when the insert loop runs, I get this error
on the following line.

insert into data1 ('AdminManageLogIn','Password') Values ('123','222')

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'data1'.

I am connected to the table x in the earlier step, I do the update
and all is fine then I change the sql statement to

insert into data1 ('AdminManageLogIn','Password') Values ('123','222')

and it gives me this error.

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'data1'.

Thanks in advance for your assistance.
Dean-OIf you are sure you are in the right database then I would look at WHO owns
data1. For your statement to work it either needs to be you ir dbo.
Anybody else and it will throw your error.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know

"rockie12" <rockie12@.dtnspeed.net> wrote in message
news:d10dd1b6.0407190412.104bb5dd@.posting.google.c om...
> I have a db that has a table x in it called data1
> I have a program that does to things, updates values in the data1
> table and also inserts new rows into this table. The update existing
> values works great. Then when the insert loop runs, I get this error
> on the following line.
> insert into data1 ('AdminManageLogIn','Password') Values ('123','222')
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'data1'.
> I am connected to the table x in the earlier step, I do the update
> and all is fine then I change the sql statement to
> insert into data1 ('AdminManageLogIn','Password') Values ('123','222')
> and it gives me this error.
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'data1'.
> Thanks in advance for your assistance.
> Dean-O|||"rockie12" <rockie12@.dtnspeed.net> wrote in message
news:d10dd1b6.0407190412.104bb5dd@.posting.google.c om...
> I have a db that has a table x in it called data1
> I have a program that does to things, updates values in the data1
> table and also inserts new rows into this table. The update existing
> values works great. Then when the insert loop runs, I get this error
> on the following line.
> insert into data1 ('AdminManageLogIn','Password') Values ('123','222')
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'data1'.
> I am connected to the table x in the earlier step, I do the update
> and all is fine then I change the sql statement to
> insert into data1 ('AdminManageLogIn','Password') Values ('123','222')
> and it gives me this error.
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'data1'.
> Thanks in advance for your assistance.
> Dean-O

First., you should remove the quotes around your column names:

insert into data1 (AdminManageLogIn,Password) Values ('123','222')

You should also check the owner of the object - it's always good practice to
qualify the object name with its owner:

insert into dbo.data1 (AdminManageLogIn,Password) Values ('123','222')

Finally, another possibility is that your database is case-sensitive and the
correct object name is Data1 (or whatever) not data1.

Simon

Error while converting Oracle Timestamp to Sql Server Timestamp (datetime) - "Invalid date

I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:

[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description:
"Invalid date format".

I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:

To convert Oracle timestamp to Sql Server timestamp

If Row.CALCULATEDETADATECUST_IsNull = FalseThen

If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then

dt = Row.CALCULATEDETADATECUST

Row.CALCULATEDETADATECUSTD = dt

EndIf

EndIf

I don't know if my code is right . Please inform, how i can achieve this.

What is the value of the offending row? Redirect the error rows from the SQL dest and see what the offending value is.

The fact that it gets through 9500 rows and then dies means your logic is right but there is a dodgy row.|||

Thanks for your support, Crispin,

I redirected the output at destination (Fail Point) as you said and can see that some columns are displaying the Errors instead of the actual data that gives the clue about which is invalid)

Column 5

Error: Year, Month and day parameters describe an unrepresnatable date and time...

I doubt that the DateSerial function in the script component is unable to extract some formats..

So,

1) Does anybody know which representations and range of Oracle (timestamp) is not allowed in Sql Server (Datetime)
2) Do we have a stable code that can perform Oracle timestamp to Sql Server conversion here

Thanks

Subhash Subramanyam

|||

Hi Experts in SSIS forum,

Thanks to LoRez who has raised this question this month again.. I am still having the same problem in SSIS. I have an Oracle timestamp Source Columns that should be populated into Sql Server Datetime columns. Though I am aware of the ranges valid for both the cases, I was unable to frame it properly. . The expression (This only checks the Date Part , I wanted code that converts the time part of the Oracle timestamp into Sql Server Datetime) I have used to convert this is:

(!ISNULL(REQUESTED_ETA_DATE_CUST) && DATEPART("yyyy",REQUESTED_ETA_DATE_CUST) > 1752 && DATEPART("yyyy",REQUESTED_ETA_DATE_CUST) < 9999) ? REQUESTED_ETA_DATE_CUST : NULL(DT_DBTIMESTAMP)

Though the expression syntax is valid, it still gives error when I run the dataflow. The error is:

[Derived Column [136]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (136)" failed because error code 0xC0049067 occurred, and the error row disposition on "input column "REQUESTED_ETA_DATE_CUST" (372)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

Though http://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/7121008802 helps to create a trigger, but not the way we can achieve using SSIS.

Greatly Appreciate if anybody can pointer or paste the Expression or .NET code that can convert Oracle timestamp into Sql Server Datetime without issues

Thanks

Subhash Subramanyam

|||

Dear Umachandar, Thanks for briefing up the approach.

I'd appreciate if you go one step ahead to help my case work.Oracle Timestamp in my case is of the format 'MM/DD/YYYY hh:mm:ss.nnn' and I do not have any control over oracle source. Somehow I must be able to use this to populate the Sql Server datetime. We know that the year should be grater that 1752 which we can validate using above expression. But Validating TimePart seems to be difficult. I tried casting to string, but due to omission of leading zeros makes it difficult to substring that. So do you have a solution for this?

Umachandar's Reply

Oracle timestamp range subsumes that of SQL Server's datetime range. So you will not have any issues as long as you use the ISO 8601 format to specify the values (YYYY-MM-DDT hh:mm:ss.nnn). This will ensure that the value will be stored correctly irrespective of collation settings on either servers or Oracle session setttings. You can use timestamp with appropriate precision on Oracle side (timestamp(3) is closest) to match SQL Server datetime.

Migrating values from Oracle to SQL Server is a different ballgame. You will lose precision, values etc. Oracle has more richer support and wider ranges & ANSI SQL implementation.

Thanks

Subhash Subramanyam

|||

I've put up an article for this on my blog.

Cast this Oracle timestamp Column REQUESTED_ETA_DATE_CUST as below:

Decode(trunc((Extract(YEAR from REQUESTED_ETA_DATE_CUST))/1753), 0,'01/01/1753 12:00:00 AM', TO_CHAR(REQUESTED_ETA_DATE_CUST, 'MM/DD/YYYY hh:mms AM')) as REQUESTED_ETA_DATE_CUST

Or

Go for filtering valid ranges using SSIS transform after casting into a accepted datetime format.

Error while converting Oracle Timestamp to Sql Server Timestamp (datetime) - "Invalid date

I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:

[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description:
"Invalid date format".

I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:

To convert Oracle timestamp to Sql Server timestamp

If Row.CALCULATEDETADATECUST_IsNull = False Then

If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then

dt = Row.CALCULATEDETADATECUST

Row.CALCULATEDETADATECUSTD = dt

End If

End If

I don't know if my code is right . Please inform, how i can achieve this.

What is the value of the offending row? Redirect the error rows from the SQL dest and see what the offending value is.

The fact that it gets through 9500 rows and then dies means your logic is right but there is a dodgy row.|||

Thanks for your support, Crispin,

I redirected the output at destination (Fail Point) as you said and can see that some columns are displaying the Errors instead of the actual data that gives the clue about which is invalid)

Column 5

Error: Year, Month and day parameters describe an unrepresnatable date and time...

I doubt that the DateSerial function in the script component is unable to extract some formats..

So,

1) Does anybody know which representations and range of Oracle (timestamp) is not allowed in Sql Server (Datetime)
2) Do we have a stable code that can perform Oracle timestamp to Sql Server conversion here

Thanks

Subhash Subramanyam

|||

Hi Experts in SSIS forum,

Thanks to LoRez who has raised this question this month again.. I am still having the same problem in SSIS. I have an Oracle timestamp Source Columns that should be populated into Sql Server Datetime columns. Though I am aware of the ranges valid for both the cases, I was unable to frame it properly. . The expression (This only checks the Date Part , I wanted code that converts the time part of the Oracle timestamp into Sql Server Datetime) I have used to convert this is:

(!ISNULL(REQUESTED_ETA_DATE_CUST) && DATEPART("yyyy",REQUESTED_ETA_DATE_CUST) > 1752 && DATEPART("yyyy",REQUESTED_ETA_DATE_CUST) < 9999) ? REQUESTED_ETA_DATE_CUST : NULL(DT_DBTIMESTAMP)

Though the expression syntax is valid, it still gives error when I run the dataflow. The error is:

[Derived Column [136]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (136)" failed because error code 0xC0049067 occurred, and the error row disposition on "input column "REQUESTED_ETA_DATE_CUST" (372)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

Though http://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/7121008802 helps to create a trigger, but not the way we can achieve using SSIS.

Greatly Appreciate if anybody can pointer or paste the Expression or .NET code that can convert Oracle timestamp into Sql Server Datetime without issues

Thanks

Subhash Subramanyam

|||

Dear Umachandar, Thanks for briefing up the approach.

I'd appreciate if you go one step ahead to help my case work.Oracle Timestamp in my case is of the format 'MM/DD/YYYY hh:mm:ss.nnn' and I do not have any control over oracle source. Somehow I must be able to use this to populate the Sql Server datetime. We know that the year should be grater that 1752 which we can validate using above expression. But Validating TimePart seems to be difficult. I tried casting to string, but due to omission of leading zeros makes it difficult to substring that. So do you have a solution for this?

Umachandar's Reply

Oracle timestamp range subsumes that of SQL Server's datetime range. So you will not have any issues as long as you use the ISO 8601 format to specify the values (YYYY-MM-DDT hh:mm:ss.nnn). This will ensure that the value will be stored correctly irrespective of collation settings on either servers or Oracle session setttings. You can use timestamp with appropriate precision on Oracle side (timestamp(3) is closest) to match SQL Server datetime.

Migrating values from Oracle to SQL Server is a different ballgame. You will lose precision, values etc. Oracle has more richer support and wider ranges & ANSI SQL implementation.

Thanks

Subhash Subramanyam

|||

I've put up an article for this on my blog.

Cast this Oracle timestamp Column REQUESTED_ETA_DATE_CUST as below:

Decode(trunc((Extract(YEAR from REQUESTED_ETA_DATE_CUST))/1753), 0,'01/01/1753 12:00:00 AM', TO_CHAR(REQUESTED_ETA_DATE_CUST, 'MM/DD/YYYY hh:mms AM')) as REQUESTED_ETA_DATE_CUST

Or

Go for filtering valid ranges using SSIS transform after casting into a accepted datetime format.

Error while converting Oracle Timestamp to Sql Server Timestamp (datetime) - "Invalid date

I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:

[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description:
"Invalid date format".

I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:

To convert Oracle timestamp to Sql Server timestamp

If Row.CALCULATEDETADATECUST_IsNull = False Then

If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then

dt = Row.CALCULATEDETADATECUST

Row.CALCULATEDETADATECUSTD = dt

End If

End If

I don't know if my code is right . Please inform, how i can achieve this.

What is the value of the offending row? Redirect the error rows from the SQL dest and see what the offending value is.

The fact that it gets through 9500 rows and then dies means your logic is right but there is a dodgy row.|||

Thanks for your support, Crispin,

I redirected the output at destination (Fail Point) as you said and can see that some columns are displaying the Errors instead of the actual data that gives the clue about which is invalid)

Column 5

Error: Year, Month and day parameters describe an unrepresnatable date and time...

I doubt that the DateSerial function in the script component is unable to extract some formats..

So,

1) Does anybody know which representations and range of Oracle (timestamp) is not allowed in Sql Server (Datetime)
2) Do we have a stable code that can perform Oracle timestamp to Sql Server conversion here

Thanks

Subhash Subramanyam

|||

Hi Experts in SSIS forum,

Thanks to LoRez who has raised this question this month again.. I am still having the same problem in SSIS. I have an Oracle timestamp Source Columns that should be populated into Sql Server Datetime columns. Though I am aware of the ranges valid for both the cases, I was unable to frame it properly. . The expression (This only checks the Date Part , I wanted code that converts the time part of the Oracle timestamp into Sql Server Datetime) I have used to convert this is:

(!ISNULL(REQUESTED_ETA_DATE_CUST) && DATEPART("yyyy",REQUESTED_ETA_DATE_CUST) > 1752 && DATEPART("yyyy",REQUESTED_ETA_DATE_CUST) < 9999) ? REQUESTED_ETA_DATE_CUST : NULL(DT_DBTIMESTAMP)

Though the expression syntax is valid, it still gives error when I run the dataflow. The error is:

[Derived Column [136]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (136)" failed because error code 0xC0049067 occurred, and the error row disposition on "input column "REQUESTED_ETA_DATE_CUST" (372)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

Though http://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/7121008802 helps to create a trigger, but not the way we can achieve using SSIS.

Greatly Appreciate if anybody can pointer or paste the Expression or .NET code that can convert Oracle timestamp into Sql Server Datetime without issues

Thanks

Subhash Subramanyam

|||

Dear Umachandar, Thanks for briefing up the approach.

I'd appreciate if you go one step ahead to help my case work.Oracle Timestamp in my case is of the format 'MM/DD/YYYY hh:mm:ss.nnn' and I do not have any control over oracle source. Somehow I must be able to use this to populate the Sql Server datetime. We know that the year should be grater that 1752 which we can validate using above expression. But Validating TimePart seems to be difficult. I tried casting to string, but due to omission of leading zeros makes it difficult to substring that. So do you have a solution for this?

Umachandar's Reply

Oracle timestamp range subsumes that of SQL Server's datetime range. So you will not have any issues as long as you use the ISO 8601 format to specify the values (YYYY-MM-DDT hh:mm:ss.nnn). This will ensure that the value will be stored correctly irrespective of collation settings on either servers or Oracle session setttings. You can use timestamp with appropriate precision on Oracle side (timestamp(3) is closest) to match SQL Server datetime.

Migrating values from Oracle to SQL Server is a different ballgame. You will lose precision, values etc. Oracle has more richer support and wider ranges & ANSI SQL implementation.

Thanks

Subhash Subramanyam

|||

I've put up an article for this on my blog.

Cast this Oracle timestamp Column REQUESTED_ETA_DATE_CUST as below:

Decode(trunc((Extract(YEAR from REQUESTED_ETA_DATE_CUST))/1753), 0,'01/01/1753 12:00:00 AM', TO_CHAR(REQUESTED_ETA_DATE_CUST, 'MM/DD/YYYY hh:mms AM')) as REQUESTED_ETA_DATE_CUST

Or

Go for filtering valid ranges using SSIS transform after casting into a accepted datetime format.