August 22, 2007 at 10:18 am
We have DTS packages that take data from an oracle database and update sql server (2000) databases.
These packages run perfectly, when manually executed directly on the server.
However, when we attempt to execute these packages manually through SQL Server Enterprise Manager from our PCs, some of the DTS packages run, and some fail. We get the message:
The number of failing rows exceeds the maximum specified. Insert error, column ......, status 12: Invalid status for bound data.
I do not think there is something wrong with the data, because the same package runs perfectly if executed directly on the server.
I have checked our tnsnames.ora file (since the source is Oracle), and our DSNs. I cannot figure out why we cannot run all of the packages from our PCs. We cannot keep logging on to the server to run these.
Any ideas?
August 22, 2007 at 11:33 am
Is it the same packages failing all the time or does it vary? Are there any that never fail?
August 22, 2007 at 11:36 am
It is always the same packages that keep failing.
August 22, 2007 at 12:19 pm
What do those packages do different from other packages that don't fail? Check job owner, is it different?
August 22, 2007 at 12:19 pm
Now - I can't address why it works in some places and not others - but that error usually is data-conversion or collation-related. like taking oracle date fields and shoving them into SQL datetime fields sometimes cause that, because it tried to use implicit conversions that don't always do the trick.
using the error you get (specifically - the column name it gives you and the value it mentions). If the data looks like it should "fit" into the field as it, then try putting in two CAST statements, one on the ORACLE side, and one on the SQL side. I tend to force values to a text-based value on its way out of oracle, and code a CAST statement on the way back in on the SQL side.
If the value you see mentioned in the error message doesn't look like it should belong in the SQL field type (like something alphanumeric trying to go into a numeric field), then use something like a CASE statement to suppress/replace it with something appropriate, or filter it out...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply