Update Rows To Resolve Issues About Duplicate Keys On Create Unique Index

Unique Index Returns Duplicate Rows
We are running the following query, which has a unique index on Table_2 (col1 and sys1), and Column col1 from Table_1 is unique.

select top 100 s.*, x.col1
from Table_1 s
left outer join Table_2 x
on x.col1 = s.col1 and x.sys1 = 'SYSTEM0'

Unfortunately this query returns duplicate rows. And every time the result is different

But once we dbcc dbreindex the unique index on Table_2, the result will not have any dups.

Any ideas?

Thanks

Steve

View Replies !   View Related

---

Elimenating Duplicate Keys With Unique Row.
I have a large table that consists of the columns zip, state, city, county. The primary
key "zip" has duplicates but the rows are unique.
How do I filter out only the duplicate zips. So in effect I only have one row per unique key.
Randy Garland

if you just want a list of all rows with duplicate zipcodes then ...

SELECT * FROM TableName WHERE zip IN ( SELECT zip FROM TableName
GROUP BY zip HAVING COUNT(*)>1 )

Duncan

Duncan, I tried this but it does not return one row per key.
Randy Garland

View Replies !   View Related

---

Duplicate Key But Unique Rows.
I have a large table that consists of the columns zip, state, city, county. The primary key "zip" has duplicates but the rows are unique.
How do I filter out only the duplicate zips.
Randy Garland

View Replies !   View Related

---

Cannot Insert Duplicate Key Row In Object 'MSmerge_genhistory' With Unique Index 'unc1MSmerge_genhistory'
I have 1 client who keeps running into the following error on the subscriber and merge agents >
 
€œCannot insert duplicate key row in object 'MSmerge_genhistory' with unique index 'unc1MSmerge_genhistory'.€?
 
Last time we got this error I ran a reindex on table MSmerge_genhistory on the publisher database, I then successfully generated a new snapshot and the subscribers started to synchronize again.  This time around I keep getting the error even after I follow these steps (I also ran all the jobs to clean up replication).  The last time I ran into this error I created a job to reindex msmerge_genhistory on a nightly bases in an effort to avoid this problem.  Can somebody please provide me with a workaround and also the reason why this error occurs in the first place.
 
Thank you in advanced,
Pauly C
 

View Replies !   View Related

---

Cannot Insert Duplicate Key Row In Object 'dbo.lastlogin' With Unique Index 'IX_lastlogin'.
Hi.

I have been recently redesigning my tables - creating FK
relationships from child tables to the PK userid in the Users table.
The specifics of what I did and why can be seen here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1968856&SiteID=1

But, now I am getting the below error:

Cannot insert duplicate key row in object 'dbo.lastlogin' with unique index 'IX_lastlogin'.
The statement has been terminated.

Or, for that matter, SavedSearches or any other table where I need to
insert the same userid twice. I can see why I would want to avoid duplicates in the Users table. But, for lastlogin, savedsearches, and
a few of my other tables, the same user may account for multiple rows.

Any suggestions as to where I messed up and how to deal with this?

Thanks.


DBO.USERS






Code Snippet

CREATE TABLE [dbo].[users](
    [userid] [int] IDENTITY(1,1) NOT NULL,
    [lastname] [varchar](50) NULL,
    [firstname] [varchar](50) NULL,
    [email] [varchar](50) NOT NULL,
    [alternateemail] [varchar](50) NULL,
    [password] [varchar](50) NOT NULL,
    [role] [varchar](10) NOT NULL,
    [securityquestion] [varchar](50) NOT NULL,
    [securityanswer] [varchar](50) NOT NULL,
    [zipcode] [int] NOT NULL,
    [birthmonth] [tinyint] NOT NULL,
    [birthday] [tinyint] NOT NULL,
    [birthyear] [int] NOT NULL,
    [gender] [varchar](10) NULL,
    [city] [varchar](50) NULL,
    [state] [varchar](50) NULL,
    [country] [varchar](50) NULL,
    [registerdate] [datetime] NOT NULL,
    [editdate] [datetime] NULL,
    [confirmed] [bit] NULL CONSTRAINT [DF__Users__confirmed__4CC05EF3]  DEFAULT ((0)),
 CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
    [userid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_email] UNIQUE NONCLUSTERED
(
    [email] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


/****** Object:  Table [dbo].[lastlogin]    Script Date: 08/22/2007 14:16:16 ******/
SET ANSI_NULLS ON


DBO.SAVEDSEARCHES


CREATE TABLE [dbo].[savedsearches](
    [savedsearchesid] [int] IDENTITY(1,1) NOT NULL,
    [searchname] [varchar](50) NOT NULL,
    [userid] [int] NOT NULL,
    [date] [datetime] NULL,
    [isdefault] [bit] NULL,
    [gender] [char](10) NULL,
    [startyear] [varchar](50) NULL,
    [endyear] [varchar](50) NULL,
    [country] [varchar](50) NULL,
    [miles] [int] NULL,
    [pictures] [varchar](50) NULL,
    [postal] [int] NULL,
    [sort] [tinyint] NULL,
    [photostring] [varchar](50) NULL,
    [orderby] [tinyint] NULL,
 CONSTRAINT [PK_SavedSearches] PRIMARY KEY CLUSTERED
(
    [userid] ASC,
    [searchname] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[savedsearches]  WITH NOCHECK ADD  CONSTRAINT [FK_savedsearches_users] FOREIGN KEY([userid])
REFERENCES [dbo].[users] ([userid])
GO
ALTER TABLE [dbo].[savedsearches] CHECK CONSTRAINT [FK_savedsearches_users]
GO
SET QUOTED_IDENTIFIER ON
GO




The following insert statement returned the error in the subject because userid = 32 already exists in the Users table.

INSERT INTO lastlogin
VALUES (32, CONVERT(VARCHAR(26), GETDATE(), 109), 1, CONVERT(VARCHAR(26), GETDATE(), 109))

DBO.LASTLOGIN





Code Snippet


CREATE TABLE [dbo].[lastlogin](
    [lastloginid] [int] IDENTITY(1,1) NOT NULL,
    [userid] [int] NOT NULL,
    [date] [datetime] NOT NULL,
    [status] [bit] NOT NULL CONSTRAINT [DF_lastlogin_status]  DEFAULT ((0)),
    [activity] [datetime] NOT NULL CONSTRAINT [DF_lastlogin_activity]  DEFAULT (getutcdate()),
    [online]  AS (case when [status]=(1) AND datediff(minute,[activity],getutcdate())<(30) then (1) else (0) end),
 CONSTRAINT [PK_lastlogin] PRIMARY KEY CLUSTERED
(
    [date] ASC,
    [userid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[lastlogin]  WITH NOCHECK ADD  CONSTRAINT [FK_lastlogin_users] FOREIGN KEY([userid])
REFERENCES [dbo].[users] ([userid])
GO
ALTER TABLE [dbo].[lastlogin] CHECK CONSTRAINT [FK_lastlogin_users]

View Replies !   View Related

---

Delete Duplicate Rows With No Unique Columns
I have 4 rows which are exactly the same. I want to delete one row but i do not have any unique identifing columns. How should i delete that row ?

View Replies !   View Related

---

Cannot Insert Duplicate Key Row In Object 'dbo.blabla..' With Unique Index 'Idx_blablabl'. The Statement Has Been Terminated. At
We are developing a project that is expected to hold TB of data and the back end used is SQL Server 2005.

I have the following problem

I have applied Nonclustered index over a column on a table.

Designed a SP for insertion which caters for updation incase the criteria based on the input is met.

The logic goes like this

Incase there exists a row containing the value of the column that is indexed for uniqueness, there should be updation. If not there should be a new row created.

 

However often there is an error message that is placed above.  This happens only on some of the SPs and only on rare occasions.

Can any body tell me if there is any problem with the SQL Server 2005

 

 

 

Thanks in advance

R Suresh, SQLDBA

 

 

 

 

View Replies !   View Related

---

How To Create Unique Secondary Index ?? HELP
I have created the companyid as Primary Key.How to create a unique secondary index on Company Name. To avoid inserting duplicate records in database with the same companyname. I m creatin database in sql server 2005 with asp.net C# 2005. I know one way is write the query if not exists at the time of insert.But,i want to know is there anyother way to make a unique secondary index for the companyname on the company tablethanxs

View Replies !   View Related

---

Create Unique Index Error
Does anybody know the significance of 3 in the following error message?

"CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 3. "

Thank you

View Replies !   View Related

---

Create Unique Index On Multiple Columns
Hello, I will explain myself further. I want to make my table in such a way that no two colums have the same value for example:
Row 1 - Column 1 = "cool"
Row 1 - Column 3 = 91
Row 3 - Column 1 = "cool"
Row 3 - Column 3 = 91
 
I dont care about one column having duplicate values, I want to protect against  Column 1 and 3 having the same values on other rows. Is this possible to do in sql server?

View Replies !   View Related

---

How To CREATE Duplicate Rows
this may sound like a weird one, but i need to create duplicates of all rows that satisfy a condition.

using asp, i am able to select rows from a databate using a recordset, only to insert it straight back into the database, thus assigning it a new unique id.

but is there any one to perform this action just using sql?

thanks,

goran.

View Replies !   View Related

---

Delete Rows With Duplicate Column Data But Unique Row Data
Hello,

This probably has been addressed before but I was unable to get the search to work properly on this site.
I am needing a script/way of deleting all rows from a DB with the exception of one record left for each row that has duplicate column data. Example :
Row 1
Field1 = 12345 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Row 2
Field1 = 12345 Field2 =zzzzzz Field 3=xxxxxx Field4=yyyyyy etc.
Row3
Field1 = 12345 Field2 =20202 Field 3=11111 Field4=zzzzz etc.
Row 4
Field1 = 54321 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Etc. Etc.

I want to be able to find the duplicates for Field1 and then delete all but 1 of those rows.( I don't care which one I keep just so only one is left.) The data in the other fields may or may not be unique.

I know how to find the duplicates it's just the deleting part I am having problems with. Any help would be much appreciated. Thanks,

Kerry

View Replies !   View Related

---

How To Resolve Duplicate Data Problem
Hi All

i want the output like this
date poid sales ref unit cost ordered received sold shrinkage sale type postage delivery payment type
23/3/2007 12345 test - - 1 - tel 20 shipping credit card

for that i have written two sql queries
qry1 =
///

"SELECT im_products_stock_logs.orderid,im_products_stock_logs.log_type,
im_products_stock_logs.log_date, im_products_stock_logs.poid,
products.lead_time,products.cost_price,orders.sales_type,
isnull(orders.totalamt,0) as totalamt, isnull(orders.shippingamt,0)
as shippingamt, orders.delivery_method, orders.payment_method
FROM im_products_stock_logs LEFT OUTER JOIN orders ON
im_products_stock_logs.orderid = orders.orderid LEFT OUTER JOIN Products on
im_products_stock_logs.productid= products.productid WHERE
(im_products_stock_logs.productid = 790) and poid=14 order by log_date desc "
///
qry2=
///
SELECT im_products_stock_logs.log_type, SUM(im_products_stock_logs.qty)
AS qty, im_products_stock_logs.poid, DAY(im_products_stock_logs.log_date)
AS Expr2, YEAR(im_products_stock_logs.log_date) AS Expr3,
MONTH(im_products_stock_logs.log_date) AS Expr4,
{ fn MINUTE(im_products_stock_logs.log_date) } AS Expr5,
{ fn HOUR(im_products_stock_logs.log_date) }
AS Expr6 FROM im_products_stock_logs LEFT OUTER JOIN orders ON
im_products_stock_logs.orderid = orders.orderid LEFT OUTER JOIN
products ON im_products_stock_logs.productid = products.productid WHERE
( im_products_stock_logs.productid = 790 and im_products_stock_logs.colorid = 2 )
GROUP BY im_products_stock_logs.log_type, im_products_stock_logs.poid,
DAY(im_products_stock_logs.log_date), YEAR(im_products_stock_logs.log_date),
MONTH(im_products_stock_logs.log_date), { fn HOUR(im_products_stock_logs.log_date)
}, { fn MINUTE(im_products_stock_logs.log_date) }
ORDER BY YEAR(im_products_stock_logs.log_date) DESC,
MONTH(im_products_stock_logs.log_date) DESC,
DAY(im_products_stock_logs.log_date) DESC,
{ fn HOUR(im_products_stock_logs.log_date) }
DESC, { fn MINUTE(im_products_stock_logs.log_date) } DESC
///

the table use in are
im_products_stock_logs-orderid,log_type,log_date,poid,
products-lead_time,cost_price
orders-sales_type,delivery_method,payment_method

the sample data is

orders
orderid sales_type delivery_method payment_method
1025 tel v shipping 1

products
productid lead_time cost_price
13 4 45.00

im_products_stock_logs
logid productid orderid log_type log_date poid
40 13 1025 sold 23/3/2007 8


I have written the query for the same is

in my query i m getting the duplicate values .
How can I solve it please help me.
thanks

View Replies !   View Related

---

Unique Constraint Vs Unique Index In MS SQL 2000
HelloWhat should I use for better perfomance sinceunique constraint always use index ?ThanksKamil

View Replies !   View Related

---

What Is The Difference Between A UNIQUE INDEX And A UNIQUE CONSTRAINT?
A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.

View Replies !   View Related

---

Unique Constraint And Unique Index, What's The Difference?
What's the difference in the effect of the followings:
CREATE UNIQUE NONCLUSTERED INDEX
and
ALTER TABLE dbo.titles ADD CONSTRAINT
 titleind UNIQUE NONCLUSTERED

I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?

View Replies !   View Related

---

Unique Keys
Hello,I plan to create a table with 3 unique keys.Combination of three fields has to be unique for each row in a table thatare vendor ID (char 8), vendor name (char 40), and vendor office (5).Will it be okay to have a unique key which has a long character such asvendor name?How should I index those three fields? Those fields will be searched manytimes.RCW

View Replies !   View Related

---

Unique Constraint Vs Unique Index
BOL says a unique constraint is preferred over a unique index. It also states that a unique constraint creates a unique index. What then is the difference between the two, and why is a constraint preferred over the index?

View Replies !   View Related

---

Unique Index Vs Unique Constraint
Hi everyone,
I need urgent help to resolve this issue...
As far as the performance goes which one is better..
Unique Index(col1, col2) OR Unique constraint(col1, col2) ?
Unique constraint automatically adds a unique index
and unique index takes care of uniqueness then whats the use of unique constraint ?

Which one do one use ?

thanks
sonali

View Replies !   View Related

---

Unique Index Vs Unique Constraints
 

hi team,
.Can i create umique constraint with out unique index.when i am creating a unique constraint sql creates a unique index (default) can i have only unique constraint ?

View Replies !   View Related

---

What 's Difference Between Unique Key And Unique Index
What 's difference between Unique key and unique index in SQL server 2005?

View Replies !   View Related

---

CREATE TABLE DUPLICATE OBJECT/DUPLICATE FIELD NAME ERROR Msg 2714
Hello Everyone:
 
I am using the Import/Export wizard to import data from an ODBC data source. This can only be done from a query to specify the data to transfer.
 
When I try to create the tables, for the query, I am getting the following error:
 



Msg 2714, Level 16, State 4, Line 12

There is already an object named 'UserID' in the database.

Msg 1750, Level 16, State 0, Line 12

Could not create constraint. See previous errors.

 
I have duplicated this error with the following script:
 

USE [testing]

IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL

DROP TABLE [testing].[dbo].[users1]

CREATE TABLE [testing].[dbo].[users1] (

[UserID] bigint NOT NULL,

[Name] nvarchar(25) NULL,

CONSTRAINT [UserID] PRIMARY KEY (UserID)

)

IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL

DROP TABLE [testing].[dbo].[users2]

CREATE TABLE [testing].[dbo].[users2] (

[UserID] bigint NOT NULL,

[Name] nvarchar(25) NULL,

CONSTRAINT [UserID] PRIMARY KEY (UserID)

)

IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL

DROP TABLE [testing].[dbo].[users3]

CREATE TABLE [testing].[dbo].[users3] (

[UserID] bigint NOT NULL,

[Name] nvarchar(25) NULL,

CONSTRAINT [UserID] PRIMARY KEY (UserID)

)

 

I have searched the "2714 duplicate error msg," but have found references to duplicate table names, rather than multiple field names or column name duplicate errors, within a database.
 
I think that the schema is only allowing a single UserID primary key.
 
How do I fix this?
 
TIA

 

View Replies !   View Related

---

Adding Unique Keys
Would anyone please instruct how to prevent the duplicate record bysetting the unique keys on the ms sql server? i've been checking theduplicate record as front-end and i found out if there is an internetdelay or some other reasons, it has a chance to store the duplicateddata into the database. so i realized it has to be done on the back-endside.for example, if i have three columns (office code, office id, officesection) as a unique key, how can i setup this? thanks in advance.

View Replies !   View Related

---

Define Unique Keys
I have a primary key (column name is emp_id) in employee table. Also,I would like to make a combination of other two columns is unique.(combination of officecode field and claimno field must be unique).how can I implement this uniquess in ms sql 2000? thank you.

View Replies !   View Related

---

Constraint Expression For Unique Keys
if i have a table which defines a rule as "combination of two fieldmust be unique", how can I write this in a constraint expressionsection?i started learning more about ms sql side to handle all the necessaryrules in back-end instead of front-end.also any good learning links, references, or book recommandations?thanks

View Replies !   View Related

---

Maintaining Unique Keys When Offline
If you have a "Orders" table that is being sync'd to subscribers that are ocassionaly offline, and the subscribers add rows to their local Orders table.  When they go online to sync with the published "Orders" table, how do you handle keeping the "OrderId" field unique?
 
Example:
Both salespeople sync the following data down:
OrderId   Desc
1            Order 1
2            Test Order
 
 
 
Both salespeople go offline and add orders
Salesperson 1 adds:
OrderId   Desc
3            Joes Order
 
Salesperson 2 adds:
OrderId   Desc
3           Kathys Order
 
 
Now, when they go back online, they both will sync their orders up to the main database and they both have the OrderId of 3. 

View Replies !   View Related

---

WHY DO FORIEGN KEYS HAVE TO BE UNIQUE OR HAVE A CONSTRAINT?
How do I go about protecting rows from deletion in this scenerio?
Rule 1 The Administrator Users Account may not be deleted
Rule 2 All Groups have Administrator as a member, and the Administrator cannot be removed.
Rule 3 All Groups have the Administrators Group as a member, and the Administrators Group cannot be removed.
 
 

Four tables:
 
Users Table (
UID bigint Identity seeded with 1234 Primary key
UserID varchar(30) NOT NULL UNIQUE

)
INSERT FIRST RECORD (this record needs to be protected from deletion)
UID = 1234
UserID='Admininstrator'
 
INSERT FIRST RECORD (this record and others can be deleted)
UID = 1235
UserID='Test User 1'
 
Groups Table (
GID  bigint Identity seeded with 1234  Primary key
GroupName varchar(30) NOT NULL UNIQUE
)
 
INSERT FIRST RECORD (this record needs to be protected from deletion)
GID = 1234
UserID='Admininstrators'
 
INSERT SECOND RECORD (this record and others can be deleted)
GID = 1235
UserID='Test Group 1'
 
Group_Members Table (
GID bigint NOT NULL              //points to the group's ID and can't be unique
UID bigint NOT NULL              //points to the members UserID  and can't be unique
)
INSERT FIRST RECORD (this record needs to be protected from deletion because UID points to the Administrator)
GID = 1234
UID = 1234
 
INSERT SECOND RECORD (this record and others can be deleted because UID does not point to the Administrator.)
GID = 1234
UID = 1235

 
Group_Group_Members Table (
GID bigint NOT NULL              //points to the group's ID and can't be unique
GGID bigint NOT NULL //points to the group members GID and can't be unique
)
INSERT FIRST RECORD (this record needs to be protected from deletion because GGID points to the Administrators Group.)
GID = 1234
GGID = 1234
 
INSERT SECOND RECORD (this record and others can be deleted because GGID does not point to the Administrators Group.)
GID = 1234
GGID = 1235

 
I have tried using foriegn keys, constraints an every thing else, but I hit a brick wall because FK requires the ke to be primary (btw is UNIQUE).
Any help would be appreciated.

View Replies !   View Related

---

Problem With Duplicate Keys
Hello,There is a program which performs some scripted actions via ODBC on tablesin some database on mssql 2000. Sometimes that program tries to insertrecord with key that is already present in the database. The error comes upand the program stops.Is there any way to globally configure the database or the whole mssqlserver to ignore such attempts and let the script continue without any errorwhen the script tries to insert duplicate-key records?Thank you for any suggestions.Pawel Banys

View Replies !   View Related

---

BCP And Duplicate Primary Keys
Hi All,
I`m using BCP to import ASCII data text into a table that already has many records. BCP failed because of `Duplicate primary key`.
Now, is there any way using BCP to know precisely which record whose primary key caused that `violation of inserting duplicate key`.
I already used the option -O to output error to a `error.log`, but it doesn`t help much, because that error log contains the same error message mentioned above without telling me exactly which record so that I can pull that `duplicate record` out of my import data file.
TIA and you have a great day.
David Nguyen.

View Replies !   View Related

---

Duplicate Foreign Keys.
Hi all,

SQL server allows to create as many as foreign key constraints on a same table for a same column.

Will this affect the design or performance in anyway ?

Naming the constraint would be a good way to avoid this.But in case if someone has already created, How do I remove the existing duplicate keys ?

======================
For Example , I have 2 tables Author and Book. I could execute the below query n times and create as many as foreign keys I want.

ALTER TABLE Books
ADD 
FOREIGN KEY (AuthorID)
REFERENCES Authors (AuthorID)

======================

Thanks is advance,

DBAnalyst

View Replies !   View Related

---

Duplicate Keys And Conversion
Hi!
I am new .. very grateful for some advice. How do I eliminate duplicate ref keys Or should I have cache mode PARTIAL?

[Data Conversion [4910]] Error: Data conversion failed while converting column "salesperson_id" (88) to column "Copy of salesperson_id" (4929). The conversion returned status value 6 and status text "Conversion failed because the data value overflowed the specified type.".
[Lookup [3882]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

View Replies !   View Related

---

Sql Cannot Resolve Collation Conflict Equals - Comparing Rows And Fileds Between Table1 And View1
Hello,I currently have Table1 and View1.View1 is a query from 2 or 3 tables that works fine on its own.However in my current query if I try to use it...something like...SELECT a.col1, a.col2, a.col3, b.col1, b.col2, b.col3FROM View1 a JOIN Table1 b on a.col1 = b.col1WHERE a.col2 <b.col2 OR a.col3 <b.col3It throws an error "Server: Msg 446, Level 16, State 9, Line 1 Cannotresolve collation conflict for not equal to operation."Clearly I need to use collation between Table1 and View1, But I dontknow where I need to use "COLLATE SQL_Latin1_General_CP850_CI_AI" andhow? this is the collation set on Table1.Thank you!Yas

View Replies !   View Related

---

Runnaway UPDATE Statement In SQL2005 - How To Resolve?
I am in the process of moving a SQL2000 database to a SQL2005 database.

Porting from: SQL200, Windows Server 2000(SP4) (32 bit dual processor 4GB RAM)
to:SQL2005, Windows Server 2003(SP1) (x64 bit dual processor 4GB RAM)

 

After porting the database from SQL2000 to SQL2005 (no changes)
I then compare an update statement running from Management Studio on the 2003 Server and and Query analiser on the 2000

Server.

SQL2000 completes the command in 2 minutes SQL2005 is still running after 60 minutes.

SQL2000 is the live/production system with users connected, the SQL2005 is in a test environment with no other processors

running.

The SQL2005 activity monitor shows:
-----------------------------------
3 suspended processes in CXPACKET wait state and
2 runnable process high CPU counts (SQLServer running at 100% cpu).
All processes with the same Process ID.
Wait time is 0
High CPU counts for the runnable processes.
Low physical IO
No lock conflicts


When I add the "option (maxdop 1)" to the update statment then:

The activity monitor shows:
---------------------------
1 runnable process with a high CPU count (SQLServer running at 50% cpu).
Wait time is 0
High CPU count for the runnable processe.
Low physical IO
No lock conflicts

How do I debug this situation?
Why is the SQL2005 unable to complete the task?

The update statement is as follows...
-------------------------------------

update BI_LENDING_TRANSACTIONS
set [Balance Movement Month] = M.[Balance Movement Month]
from BI_LENDING_TRANSACTIONS as T,
     BI_BALANCE_MOVEMENT_DATES as M,
     BI_COMPANIES as C
where  (T.[Transaction Date] >=
  (SELECT DATEADD(d, - 70, minDate) from (select min([Transaction Date]) minDate
   from p_BI_LENDING_TRANSACTIONS) t1)
  OR
 T.[Transaction Date] >= C.[MostRecentSnapShotDate] or
 T.[Value Date] = T.[Balance Movement Month] ) and
 T.[Value Date] <= C.[MostRecentSnapShotDate] and
 T.[Value Date] >= T.[Transaction Date] and
 T.[Company_Code] = M.[Company_Code] and
 T.[Value Date] > M.[SnapShotFromDate] and
 T.[Value Date] <= M.[SnapShotToDate] and
 C.[Company_Code] = M.[Company_Code]

 

View Replies !   View Related

---

DTS Lookups - Generating And Testing Unique Keys
Hi,

I'm using SQL Server 2000 DTS to transform data from one table to another.  As part of this I need to combine several fields into a unique key in the new table.

As DTS is processing how do I test if the key hasn't already been used and generate a new unique key e.g. by adding a number to the end of the key ?

Regards

Michael

View Replies !   View Related

---

Unique Colm Indexes And Primary Keys
I have a deal table, each of these investments must be unique. I created a int pk : idDeal. Does that make sense or should i just use the deal colm being it has a unique constraint,
Reguarding indexes, should i make the auto # colm my pk and make that the clustered index? and put another index on the Deal Colmn? Any suggestions welcomed

Thank you

View Replies !   View Related

---

Importing Data With Duplicate Keys
I'm trying to merge two Access databases into one SQL server database. I have 3 tables that are all related with primary and foreign keys.

When I try to import my second set of 3 tables I get errors about the keys already existing in the database. Is there any way to force SQL server to assign new keys while preserving my existing relationships? Thanks!

View Replies !   View Related

---

Finding Duplicate Foreign Keys
Hi

i tried the following query and able to get the list of foreign keys with column names as well as referred tables and referenced column

select parent_column_id as 'child Column',object_name(constraint_object_id)as 'FK Name',object_name(parent_object_id) as 'parent table',name,object_name(referenced_object_id)as 'referenced table',referenced_column_id
from sys.foreign_key_columns inner join sys.columns on (parent_column_id = column_id and parent_object_id=object_id)
Order by object_name(parent_object_id) asc

but i am not able to get the fks created more than once on same column refering to same pk

Thanks in Advance

View Replies !   View Related

---

Finding Duplicate Entries (with Different Keys)
Yet another simple query that is eluding me. I need to find records in a table that have the same first name and last name. Because the table has a primaty key, these people were entered twice or they share the same first and last name.

How could you query this:

ID fname lname
10001 Bill Jones
10002 Joe Smith
10003 Sue Jenkins
10004 John Sanders
10005 Joe Smith
10006 Harrold Simpson
10007 Sue Jenkins
10008 Sam Worden

and get a result set of this:

ID fname lname
10002 Joe Smith
10005 Joe Smith
10003 Sue Jenkins
10007 Sue Jenkins

View Replies !   View Related

---

Ignore Duplicate Primary Keys
I have one table that stores log messages generated by a web service. I have a second table where I want to store just the distinct messages from the first table. This second table has two columns one for the message and the second for the checksum of the message. The checksum column is the primary key for the table.

My query for populating the second table looks like:
INSERT INTO TransactionMessages ( message, messageHash )
SELECT DISTINCT message, CHECKSUM( message )
FROM Log
WHERE logDate BETWEEN '2008-03-26 00:00:00' AND '2008-03-26 23:59:59'
AND NOT EXISTS (
SELECT * FROM TransactionMessages
WHERE messageHash = CHECKSUM( Log.message ) )

I run this query once per day to insert the new messages from the day before. It fails when a day has two messages that have the same checksum. In this case I would like to ignore the second message and let the query proceed. I tried creating an instead of insert trigger that only inserted unique primary keys. The trigger looks like:

IF( NOT EXISTS(
SELECT TM.messageHash
FROM TransactionMessages TM, inserted I
WHERE TM.messageHash = I.messageHash
) )
BEGIN
INSERT INTO TransactionMessages ( messageHash, message )
SELECT messageHash, message FROM inserted
END

That didn't work. I think the issue is that all the rows get committed to the table at the end of the whole query. That means the trigger cannot match the duplicate primary key because the initial row has not been inserted yet.

Does anyone know the best way to do this?

Thanks for your help,
Drew

View Replies !   View Related

---

Table With Duplicate Entry With Primary Keys
We have a SQL Server 6.5 table, with composite Primary Key, having the Duplicate Entry for the Key. I wonder how it got entered there? Now when we are trying to import this table to SQL2K, it's failing with Duplicate row error. Any Help?

View Replies !   View Related

---

Duplicate Primary Keys In Input File
 'm trying to import a text file but the primary key column contains duplicatres (tunrs out to be the nature of the legacy data). How can I kick out all duplicates except, say, for a single primary key value?

 

TIA,

Barkingdog

 

 

 

View Replies !   View Related

---

Renaming Duplicate Row Data To Be Unique?
I just converted an old non-relational database into something that MS SQL likes. The old primary keys were broken up into two columns, one being useful. The column I need to use has some rows with the same values in them.

I am looking for some way in a SQL script to look for the duplicate rows and add "_X" to the data where X is a value incremented by 1 for each duplicate row found.

For example, 3 duplicate rows with "5443aa" would return "5443aa", "5443aa_1","5443aa_2".

Any ideas?

--MartinZ

View Replies !   View Related

---

Need Help Regarding Index Keys For MS7 And MS-2K
Hi,My application needs to calculate the sort order of an index key(whether the index key is descending or ascending). The user mayconnect to MS7 or MS2K servers.As far as I know, the descending indices are supported in version 8i.e SQL 2000. In MS2K, I can get the index key information by SELECTINDEXKEY_PROPERTY(table_ID , index_ID , key_ID , IsDescending) whichreturns 1 if key is descending. But as this is only for MS2K, it willfail and report error for above query in case connected to SQL server7.Now, in the application we avoided using server version dependentcode. So, can you please suggest me other way to do the same so thatthe query should not produce an error if connected to MS7 server butshould work for MS-2000 server.Regards,Uday

View Replies !   View Related

---

Select Distinct Rows From Duplicate Rows....
Dear Gurus,I have table with following entriesTable name = CustomerName Weight------------ -----------Sanjeev 85Sanjeev 75Rajeev 80Rajeev 45Sandy 35Sandy 30Harry 15Harry 45I need a output as followName Weight------------ -----------Sanjeev 85Rajeev 80Sandy 30Harry 45ORName Weight------------ -----------Sanjeev 75Rajeev 45Sandy 35Harry 15i.e. only distinct Name should display with only one value of Weight.I tried with 'group by' on Name column but it shows me all rows.Could anyone help me for above.Thanking in Advance.RegardsSanjeevJoin Bytes!

View Replies !   View Related

---

How To Resolve An Indefinite Wait State On Update Command In SQL2005 (64bit 2003 Server)?
I am in the process of moving a SQL2000 database to a SQL2005 database.

Porting from: SQL200, Windows Server 2000(SP4) (32 bit dual processor)
to:SQL2005, Windows Server 2003(SP1) (x64 bit dual processor)


After porting the database from SQL2000 to SQL2005 (no changes) running the same update statement from Management Studio on the 2003 Server and and Query analiser on the 2000 Server.

SQL2000 completes the command in 2 minutes SQL2005 is still running after 60 minutes.

SQL2000 is the live/production system with users connected, the SQL2005 is in a test environment with no other processors running.

When the problem first showed up the SQL2005 activity monitor displayed CXPACKET wait type on 2 processes with the same pid number.  I now no longer have any wait type being displayed but my wait time is increasing rapidly. No block is reported.

I assume that I have an CXPACKET lock problem.

Am I correct that I have a CXPACKET problem and if so what is the resolution?

The update statement is as follows...

update BI_LENDING_TRANSACTIONS
set [Balance Movement Month] = M.[Balance Movement Month]
from BI_LENDING_TRANSACTIONS as T,
     BI_BALANCE_MOVEMENT_DATES as M,
     BI_COMPANIES as C
where  (T.[Transaction Date] >=
  (SELECT DATEADD(d, - 70, minDate) from (select min([Transaction Date]) minDate
   from p_BI_LENDING_TRANSACTIONS) t1)
  OR
 T.[Transaction Date] >= C.[MostRecentSnapShotDate] or
 T.[Value Date] = T.[Balance Movement Month] ) and
 T.[Value Date] <= C.[MostRecentSnapShotDate] and
 T.[Value Date] >= T.[Transaction Date] and
 T.[Company_Code] = M.[Company_Code] and
 T.[Value Date] > M.[SnapShotFromDate] and
 T.[Value Date] <= M.[SnapShotToDate] and
 C.[Company_Code] = M.[Company_Code]

 

 

View Replies !   View Related

---

SP Causes The Error Violation Of UNIQUE KEY Constraint Cannot Insert Duplicate Key
The following SP causes the error "Violation of UNIQUE KEY constraint 'AlumniID'. Cannot insert duplicate key in object [table name].
The statement has been terminated." AlumniID is the table's PK and is set to autoincrement. I'd appreciate any help or suggestions.

1 ALTER PROCEDURE dbo.sp_CreateUser
2
3 @UserID uniqueidentifier,
4 @UserName nvarchar(128),
5 @Email nvarchar(50),
6 @FirstName nvarchar(25),
7 @LastName nvarchar(50),
8 @Teacher nvarchar(25),
9 @GradYr int
10
11 AS
12 SET NOCOUNT ON;
13 --DECLARE @UserID uniqueidentifier
14 --SELECT @UserID = NULL
15 --SELECT @UserID = UserID FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName-- AND @ApplicationId = ApplicationId
16 INSERT INTO [table]
17 (UserID,UserName,Email,FirstName,LastName,Teacher,GradYr)
18 VALUES (@UserID,@UserName,@Email,@FirstName,@LastName,@Teacher,@GradYr 

View Replies !   View Related

---

Unique Constraint Does Not Permit Duplicate NULL Values
After adding a Unique constraint to a database I cannot add more than one record with a null value for the constrained field. I've tried both adding the constraint to an empty table as well as a table with multiple null values already in the subject field; both efforts have failed.

According to BOL SQL-7 allows Unique Constraints on fields with Null values. Am I missing a step? I do need to allow nulls in the field yet ensure that when there is a non-null value it is unique.

The SQL statement I've used is: ALTER TABLE tbl_MasterUIC ADD CONSTRAINT uniquesamplenbr UNIQUE NONCLUSTERED (samplenbr)

Thanks for any and all suggestions

View Replies !   View Related

---

SP Causes The Error Violation Of UNIQUE KEY Constraint Cannot Insert Duplicate Key
 

I have a table with 0 records.  When I try to insert records using a SP, it gives the following error.
Violation of UNIQUE KEY Constraint 'constraint name'.  Cannot insert duplicate key in object 'Objectname'.
How do I resolve this.
Thanks.

View Replies !   View Related

---

Should I Index Foreign Keys?
Hi all

What is the general consensus regarding indexing of foreign keys in tables?

I have a lot of queries that perform inner joins from the 'primary key' table to a 'foreign key' table. I was wondering if there are any special considerations to take into account.

For example, I think in some RDBMS, foreign keys are sort of automatically assigned indexes? Is there anything that SQL Server automagically does in the background when a foreign key constraint is created that may affect whether something should be indexed?

Thanks
Matt

View Replies !   View Related

---

Index For Foreign Keys Necessary ?
Is it necessary to put index on foreign key ? If yes, what are the pros and cons. If not, why not ?

Thanks for your help.
Jimmy
jenc@mwhse.com

View Replies !   View Related

---

Clustered Index On Client_ID+ORderNO+OrdersubNo, If I Create 3 Noncluster Index On Said Column Will It Imporve Performance
 

Dear All.
 
We had Teradata 4700 SMP. We have moved data from TD to MS_SQL SERVER 2003. records are 19.65 Millions.
 
table is >> Order_Dtl

Columns are:-

Client_ID  varchar 10
Order_ID  varchar 50
Order_Sub_ID decimal
.....
...
..
.
Pk is (ClientID+OrderId+OrderSubID)
 
Web Base application or PDA devices use to initiate the order from all over the country. The issue is this table is not Partioned but good HP with 30 GB RAM is installed. this is main table that receive 18,0000  hits or more. All brokers and users are using this table to see the status of their order.
 
The always  search by OrderID, or ClientID or order_SubNo, or enter any two like (Client_ID+Order_Sub_ID) or any combination.
 
Query takes to much time when ever server receive more querys. some orther indexes are also created on the same table like (OrderDate, OrdCreate Date and Status)
 
My Question are:-
 

Q1. IF Person "A" query to DB on Client_ID, then what Index will use ? (If any one do Query on any two combination like Client_ID+Order_ID, So what index will be uesd.? How does MS-SQL SERVER deal with these kind of issues.?
 
Q2. If i create 3 more  indexes on ClientID, ORderID and OrdersubID. will this improve the performance of query.if person "A" search record on orderNo so what index will be used. (Mind it their would be 3  seprate indexes for Each PK columns) and composite-Clustered index is also available.?
 
Q3. I want to check what indexes has been used? on what search?
 
Q4. How can i check what table was populated when, or last date of update (DML)?
 
My Limitation is i Dont Create a Partioned table.  I dont have permission to do it.
 
 
 
In Teradata we had more than 4 tb record of CRM data with no issue. i am not new baby in db line but not expert in sql server 2003.
 

I am thank u to all who read or reply.
 
Arshad
 
Manager Database
Esoulconsultancy.com
 
(Teradata Master)
10g OCP
 
 
 
 
 
 
 
 
 
 
 

View Replies !   View Related

---