|
Hi all,
I am working for information warehouse application. Here database used is Oracle.Here we are loading data from flat file to oracle table using SQL Loader. Which is getting failed and showing error
ORA 00001 unique constraint violated
I have checked for the corresponding table by using DESC <table_name>;
It is showing NOT NULL constraint for all the columns and not showing any primary key. Is there any other way to check for primary key?
I know there can be a issue of indexes as i am new to database please guide me how can i check for index constraints in a table.
Please help me to resolve this unique constraint voilation.
Please guide me, what can be the expected reason for this failure.
|
|
|
|
|
The error message has nothing to do with indexes, but rather referential integrity. There is a column that has a constraint on it that constrains what data is allowed for the column. The data must exist in some column in another table. It is this other column in the other table that is indexed. The usual method to avoid this error is to disable the contraints while loading data with SQL*Load and to then enable the constraints after all data has been successfully loaded. Alternatively, if you can obtain the schema definition, you may try to rearrange the order of the data in your flat file and ensure that the referred-to tables are loaded prior to loading a table that contains referential integrity constraints.
Chris Meech
I am Canadian. [heard in a local bar]
The America I believe in has always understood that natural harmony is only one meal away from monkey burgers. [Stan Shannon]
GOOD DAY FOR: Bean counters, as the Australian Taxation Office said that prostitutes and strippers could claim tax deductions for adult toys and sexy lingerie. [Associated Press]
|
|
|
|
|
Hi
Thanks for your response.
I am new to database can u please explain me more regarding referential integrity constraint and how to check it.
I need it urgently please help me
|
|
|
|
|
Apologies, but the other thing to check is the data itself. There is probably some index on this table that has been defined as unique, or no duplicates allowed. This index could be on a single column or a group of columns. You should check the schema definition to verify this.
Chris Meech
I am Canadian. [heard in a local bar]
The America I believe in has always understood that natural harmony is only one meal away from monkey burgers. [Stan Shannon]
GOOD DAY FOR: Bean counters, as the Australian Taxation Office said that prostitutes and strippers could claim tax deductions for adult toys and sexy lingerie. [Associated Press]
People vote 1s for espier because there is no zero. [Ed Gadziemski]
|
|
|
|
|
What are the best practices for writing stored procedures in SQL which improves the performance of the server?
|
|
|
|
|
Try to restrict the queries result set by using the WHERE clause.
Try to restrict the queries result set by returning only the particular columns from the table, not all table's columns.
Use views and stored procedures instead of heavy-duty queries.
Try to avoid using SQL Server cursors, whenever possible.
If you need to return the total table's row count, you can use alternative way instead of SELECT COUNT(*) statement.
Try to use constraints instead of triggers, whenever possible.
Use table variables instead of temporary tables.
Try to avoid the HAVING clause, whenever possible.
Try to avoid using the DISTINCT clause, whenever possible.
Include SET NOCOUNT ON statement into your stored procedures to stop the message
Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows.
Use the FAST number_rows table hint if you need to quickly return 'number_rows' rows.
UNION ALL statement instead of UNION, whenever possible.
Ricardo Casquete
|
|
|
|
|
Hi Deeksha,
Guess the following links will help you.
http://vyaskn.tripod.com/coding_conventions.htm
http://www.extremeexperts.com/SQL/Articles/BestPractices.aspx
Thanks and Regards
Pani
|
|
|
|
|
Hi Guys and Gals,
Your help is needed, is there any way to see when (date/time) is the specific record inserted or updated in sql server?
Many Thanks.
Stephen
---------------------
www.islasolutions.net
|
|
|
|
|
No. To acheive this, You need to include modifieddate, createddate columns manually in all your tables and update them.
|
|
|
|
|
how about the transaction log, is sql server store info in this log?
---------------------
www.islasolutions.net
|
|
|
|
|
Not sure of that.
Let's see, if some one could answer this..
|
|
|
|
|
How about using a trigger? of course you will need to have a column to store the value but I bet that's easiest/simplest way..
Greetings,
:->
|
|
|
|
|
Thanks, actually planning to implement this in future, but for now i just want to check, if possible to can see the date of my previous records when it was inserted or updated, think theres no other way then.
many thanks to all the response .
Regards,
Stephen
---------------------
www.islasolutions.net
|
|
|
|
|
Hi,
I have the small doubt,I created a table Author in oracle and i put primary key constraint on authorid field.And i created another table Article and foreign key constaraint placed on this table's authorid.Now iam inserting data into these two tables through front end.
My doubt is why we have to create
Dim pk(1) As DataColumn
pk(0) = ds.Tables(0).Columns("custid")
ds.Tables("customers").PrimaryKey = pk
This is for primary key.
The below is for foreign key:
Dim fk As ForeignKeyConstraint
fk = New ForeignKeyConstraint("fk", ds.Tables(0).Columns("custid"),
ds.Tables(1).Columns("custid"))
fk.DeleteRule = Rule.Cascade
fk.UpdateRule = Rule.Cascade
ds.Tables(1).Constraints.Add(fk)
ds.EnforceConstraints = True
with out creating this constraints iam getting exception "sys... violated" when iam trying to insert duplicate values.
Then what is the use of above code and when the code will be useful.
Thanks in advance.
|
|
|
|
|
If I understand your situation correctly, it sounds like when you get the error without the contraints THAT error is coming from the actual database. The constraints you are setting up are on the dataset object. The difference being that with the constraints you will throw an error right away when you try to add a duplicate record into the dataset. Without the constraints the error will not be thrown until you try to update the database by using the dataset's update method. At least, this is what it sounds like. I could be wrong.
|
|
|
|
|
Hi Friends,
I have created a stored procedure which copy data from Table1 to Table2 table, its working i am happy.
Now i have to use Table2 in crystal report how can i run that procedure from the crystal report so it copy data from table1 to table2 to use in the crystal report.
if any explaination required pls do not hesitate to ask.
I am stuck help me pls.
Naveed Kamboh
|
|
|
|
|
Hi all.
One question that has foxed me for several years... is there a way to retrieve the output from Stored Procedures using .NET C#?
For example, if I have a stored procedure run through Query Analyzer, the Output tab reveals lots of row counts and the output from any PRINT statements used throughout that SP. It also reveals useful messages whenever errors occur. This is often useful for both debugging stored procedures, but also for outputting warning or status messages.
Obviously it is possible to use RAISERROR but sometimes this is not appropriate if you are checking the ADO.NET errors collection after your execution.
So is there any way to retrieve the non-error output using ADO.NET ??
Thanks.
|
|
|
|
|
Hi,
This is a part of my Stored proc where I am trying to assign NULL values to the date variables
ALTER PROCEDURE [dbo].[USP_SearchVehicleOnDateRange]
@DateFrom DateTime=null
,@DateTo DateTime=null
AS
BEGIN
SET NOCOUNT ON;
IF LEN(@DateFrom)<=0
begin
select @DateFrom = NULL
end
IF LEN(@DateTo)<=0
begin
select @DateTo = NULL
end
.....
...
But when I execute this its throwing the below error.
Msg 241, Level 16, State 1, Procedure USP_SearchVehicleOnDateRange, Line 16
Conversion failed when converting datetime from character string.
Any clue on how assign a NULL value to the data field?
Thanks
-L
|
|
|
|
|
If I'm not mistakem, LEN is used for strings only, so if you want to know if that parameter is NULL, simply ask
if @DateFrom = NULL
begin
...
end
Please check if that works for you
daniero
|
|
|
|
|
SQL Server Books Online
IS [NOT] NULL
Determines whether or not a given expression is NULL.
Syntax
expression IS [ NOT ] NULL
Remarks
To determine if an expression is NULL, use IS NULL or IS NOT NULL rather than comparison operators (such as = or !=). Comparison operators return UNKNOWN if either or both arguments are NULL.
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
First off - your error is on line 16, and you have only showed us 14 lines of the stored procedure.
Secondly, you should use IS NULL and not LEN() to check if a variable contains a null value.
IF @DateFrom IS NULL<br />
BEGIN<br />
'Do something (but why set @DateFrom to NULL, since it defaults to NULL above???)<br />
END
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I had an idea I could be wrong, but I didn't have some place to make sure (developing PC) at the time. Thanks for correcting me.
daniero
|
|
|
|
|
can u tell me what is the difference between open xml update and dbdataadapter.update(dataset)
what is happening inside dbdataadapter.update(dataset)
which one is better why
guide me please
thanks
-
|
|
|
|
|
Hi Friends,
I have a table with fileds ID,code,descripiton, date
where ID is primaryKey also date is a unique while code and description could hame same data.
my problem is i have to fetch last two records for each of the same code and description in the table.
How can i do that please?
Please not that
"select top 2 * from table1 order by id des"c is returning only last 2 recods of the table.
While i need last 2 records for each of the code and description in the table.
thanks
Naveed Kamboh
|
|
|
|
|
Hi,
okay I will try my best, so I hope this will help you. My idea is to use a Stored Procedure or something like this, where you build a loop over all value-pairs of code and description (can be retrieved by a distinct or a group-by). And inside that loop you select the last two records using a where-condition to identify the matching records. Afterwards you can put these selected rows into a temporary table. Perform a select at the end to retrieve the result.
Pseudo-code:
<br />
create temporary table X (must have same scheme like your original table)<br />
<br />
foreach value-pair E of code and description<br />
begin<br />
insert into X <br />
select top 2 * from original_table where code = E.code and description = E.description<br />
end<br />
<br />
select * from X<br />
Hmmm, i hope this helps
Regards Sebastian
-- modified at 9:02 Tuesday 6th June, 2006
|
|
|
|