MS SQL Server :: Prevent Duplicate Insert

Cannot Insert Duplicate Key Row In Msrepl_transactions
We have an issue that our SQL Replication between our Application Server (holding 13 months of data) and our Reporting Server (holding 5-6 years of data).

Both systems are running on NT4, SQL Server 7 Ent SP3.

Our issue is that we used to run SQL Replication but then stopped using it, and used another method of replicating our data from the App Svr to Rpt Svr. However, we have now been told that we have to use SQL Replication.

We set it up as usual, but when the log reader starts up, it finds the 1st transaction and moves it into the distribution database, but it then fails when attempting to do the 2nd transaction with the error "Cannot insert duplicate key row in object MSrepl_transactions with unique index ucMSrepl_transactions.

We have checked the tables in the publisher db and they don't hold any duplicates, so I can only assume the key it is talking about is the key field in the distribution database, but this is a Binary field which I believe is populated by replication itself!! Is this correct?

If so, why is it trying to enter duplicates? And can we resync replication to fix it?

View Replies !

Don't Insert Duplicate Rows
Hi, I need to insert rows into table1 from table2 and table3 but I don't want to insert repeated combinations of col2, col3. So, table1 has the primary key col2, col3.

This the table1:

create table table1(
col1 int not null,
col2 int not null,
col3 int not null,
constraint PK_table1 primary key (col2, col3)
)

This is my "insert" code:

INSERT INTO table1
SELECT table2.col1,table2.col2, table3.col3
FROM table2, table3
WHERE table2.col1 = table3.col1

Wich conditions shoud i add to this code?

Thanks.

fmilano.

View Replies !

Cannot Insert Duplicate Key Row In Object
The following quote is the exact error message that I get when I attempt to create a pull subscription. The STAppointment is one of a 15 databases that need to replicate inorder for the application SalonTouch to run properly.

What is causing this error? Is it user rights? Is it a problem with sql2005? Am I missing a step? Is there a problem with the database I am working with? What else?

TITLE: New Subscription Wizard
------------------------------

SQL Server could not create a subscription for Subscriber 'D6LHWQ91PORTSUN9'.

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot insert duplicate key row in object 'dbo.MSmerge_replinfo' with unique index 'uc1MSmerge_replinfo'.
The subscription could not be created.
The subscription properties table 'MSsubscription_properties' does not exist in the current database.
Changed database context to 'STAppointment'.
The statement has been terminated. (Microsoft SQL Server, Error: 2601)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=2601&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

View Replies !

Trying To Append A Field If An Insert Happens To Contain A Duplicate
I deliberately intend to add some duplicates to one of my tables. For eg

Job User IsAdmin

JobID 235User ID 1
JobID 235User ID 5
JobID 235User ID 9
JobID 235User ID 5
JobID 235User ID 2
JobID 235User ID 9
JobID 235User ID 10
JobID 235User ID 1

I know its bad practice to do such a thing but there is a genuine reason. What I need to do is to be able to have a SQL statement that appends true to the IsAdmin field whenever it encounters the next UserID thats happens to be a duplicate. Hence the above would look like:

Job User IsAdmin

JobID 235User ID 1
JobID 235User ID 5
JobID 235User ID 9
JobID 235User ID 5 True
JobID 235User ID 2
JobID 235User ID 9 True
JobID 235User ID 10
JobID 235User ID 1 True

Thanks

View Replies !

Preventing Duplicate Rows On Insert
I have a table using an identity column as its Primary Key and twocolumns (table reduced for simplicity) EmployeeNumber and ArrivalTime.CREATE TABLE [tblRecords] ([ID] [bigint] IDENTITY (1, 1) NOT NULL ,[EmployeeNumber] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL ,[ArrivalTime] [datetime] NOT NULL ,CONSTRAINT [PK_tblRecords] PRIMARY KEY CLUSTERED([ID]) ON [PRIMARY]) ON [PRIMARY]GOI have an insert procedure that checks for duplicates before insertinga new record:IF (SELECT TOP 1 [ID] FROM tblRecords WHERE EmployeeNumber =@SocialSecurity) IS NULLBEGININSERT INTO tblRecords(EmployeeNumber,ArrivalTime)VALUES (@EmployeeNumber, @ArrivalTime)SELECT SCOPE_IDENTITY()ENDELSESELECT 0 AS DuplicateRecordIn 99.9% of the cases, this works well. However, in the event that theinsert attempts are literally "ticks" apart, the "SELECT TOP 1..."command completes on both attempts before the first attempt completes.So I end up with duplicate entries if the procedure is called multipletimes vey quickly. The system needs to prevent duplicateEmployeeNumbers within the past 45 days so setting the EmployeeNumberto UNIQUE would not work. I can check for older entries (45 days ornewer) very easily, but I do not know how to handle the times when theprocedure is called multiple times within milliseconds. Would aTRANSACTION with a duplicate check after the INSERT with a ROLLBACKwork in this case? Any help is greatly appreciated!-E

View Replies !

Conditional Insert - Duplicate Key Issue
I am in the process of migrating an old access database into a new SQL Server 2000 database with a modified structure.

Tables: [Chapters][Worksite][Employers]

I want to insert into the chapters table a ChapterID(PK), EmployerID(FK), and ChapterName.

All of the selected information is coming from the [Worksite] table. However, my conditional is based on a third table, the [Employers] table. Below is a copy of a query that returns the result set I would like to insert into my [Chapters] table.

Code:

Select distinct a.WorksiteCode, b.employerID, a.WorksiteName
From Worksite a, employers b
Where a.worksiteCode = b.employerWSCode

When I run this query it appears to return the proper result set. However, when I turn that query into an insert statement:

Code:

SET IDENTITY_INSERT Chapters ON
Insert into Chapters (chapterID, employerID, chapterName)
Select distinct a.WorksiteCode, b.employerID, a.WorksiteName
From Worksite a, employers b
Where a.worksiteCode = b.employerWSCode
SET IDENTITY_INSERT Chapters OFF

I recieve the following error message: Server: Msg 2627, Level 14, State 1, Procedure InsertChapterFromFixed, Line 10
Violation of PRIMARY KEY constraint 'PK_Chapters'. Cannot insert duplicate key in object 'Chapters'.
The statement has been terminated.

Anyone have any idea on how I can avoid this error message... either my distinct statement is not function as I expect it to, or I am doing something else wrong.

Thanks for any help.

View Replies !

Insert That Drops Duplicate Records
I ought to know how to do this, but it escapes me at the moment. I need to write an insert statement for a table that will be based on a complex select query. The select query may return rows that are already in the target table. In that case, I don't want duplicates created, but I don't want the query to error, either. I can't remember how to set that up.

View Replies !

Cannot Insert Duplicate Key In Object 'dbo.RunningJobs'
I'm using SSRS 2005 on a Server 2003 machine.

Some of my reports run fine but others take a long time to run and sometimes fail with an http error 503.

After a report errror happens I find entries in the event log that says: cannot open a connection to the report server

When I look at the log files for Reporting services I see an error caused by a primary key violation on the Running Jobs Table:

Violation of PRIMARY KEY constraint 'PK_RunningJobs'. Cannot insert duplicate key in object 'dbo.RunningJobs'

Does anyone know why do I get such errors?

Bellow is the error message in the logs:

w3wp!library!6!21/08/2006-09:30:22:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_RunningJobs'. Cannot insert duplicate key in object 'dbo.RunningJobs'.
The statement has been terminated.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.ReportingServices.Library.RunningJobsDb.AddRunningJobs(Hashtable runningJobs)

View Replies !

Cannot Insert Duplicate Key In Object 'dbo.RunningJobs'
We continue to recieve he following error on our RS 2005 SP1 Reporting Services installation:

Violation of PRIMARY KEY constraint 'PK_RunningJobs'. Cannot insert duplicate key in object 'dbo.RunningJobs'

This causes our server to become unresponsive and some reports to not render, Its becoming quite common. We reset IIS to correct it but is causes a service interuption. I know that it had been talked about before as something that may be fixed in SP2. But I do not see this referenced.

Does anybody know of any fix for this?

Thanks

Ron

View Replies !

Error Message On Attempted Duplicate Insert
Hi All, I have a web form that inserts information into two tables in sql 2005 database on a submit button click. I have a unique key on the tables preventing duplicate information which works fine. The problem I have is that at the minute if a users tries to insert a duplicate row they just get the built in sql error message. Is there a way I can validate the information before if goes into the database and display perhaps a label telling the user of their error or is there a way I can customize the build in sql error message? I've had a search on various forums and sites and can't find any info that would help me. Thanks in advance
Dave

View Replies !

Duplicate Records Are Being Inserted With One Insert Command.
This is like the bug from hell. It is kind of hard to explain, soplease bear with me.Background Info: SQL Server 7.0, on an NT box, Active Server pageswith Javascript, using ADO objects.I'm inserting simple records into a table. But one insert command isplacing 2 or 3 records into the table. The 'extra' records, have thesame data as the previous insert incident, (except for the timestamp).Here is an example. Follow the values of the 'Search String' field:I inserted one record at a time, in the following order (And only oneinsert per item):airplanejetdogcatmousetigerAfter this, I should have had 6 records in the table. But, I endedup with 11!Here is what was recorded in the database:Vid DateTime Type ProductName SearchString NumResultscgcgGeorgeWeb3 Fri Sep 26 09:48:26 PDT 2003 i null airplane 112cgcgGeorgeWeb3 Fri Sep 26 09:49:37 PDT 2003 i null jet 52cgcgGeorgeWeb3 Fri Sep 26 09:50:00 PDT 2003 i null dog 49cgcgGeorgeWeb3 Fri Sep 26 09:50:00 PDT 2003 i null jet 52cgcgGeorgeWeb3 Fri Sep 26 09:50:00 PDT 2003 i null jet 52cgcgGeorgeWeb3 Fri Sep 26 09:50:22 PDT 2003 i null dog 49cgcgGeorgeWeb3 Fri Sep 26 09:50:22 PDT 2003 i null cat 75cgcgGeorgeWeb3 Fri Sep 26 09:52:53 PDT 2003 i null mouse 64cgcgGeorgeWeb3 Fri Sep 26 09:53:06 PDT 2003 i null tiger 14cgcgGeorgeWeb3 Fri Sep 26 09:53:06 PDT 2003 i null mouse 64cgcgGeorgeWeb3 Fri Sep 26 09:53:06 PDT 2003 i null mouse 64Look at the timestamps, and notice which ones are the same.I did one insert for 'dog' , but notice how 2 'jet' records wereinsertedat the same time. Then, when I inserted the 'cat' record, another'dog' record was inserted. I waited awhile, and inserted mouse, andonly the mouse was inserted. But soon after, I inserted 'tiger', and 2more mouse records were inserted.If I wait awhile between inserts, then no extra records are inserted.( Notice 'airplane', and the first 'mouse' entries. ) But if I insertrecords right after one another, then the second record insertion alsoinserts a record with data from the 1st insertion.Here is the complete function, in Javascript (The main code ofinterestmay start at the Query = "INSERT ... statement):----------------------------------------------------------------------//Write SearchTrack Record ------------------------------------Search.prototype.writeSearchTrackRec = function(){Response.Write ("<br>Calling function writeSearchTrack "); // fordebugvar Query;var vid;var type = "i"; // Type is imagevar Q = "', '";var datetime = "GETDATE()";//Get the Vid// First - try to get from the outVid var of Cookieinctry{vid = outVid;}catch(e){vid = Request.Cookies("CGIVid"); // Gets cookie id valuevid = ""+vid;if (vid == 'undefined' || vid == ""){vid = "ImageSearchNoVid";}}try{Query = "INSERT SearchTrack (Vid, Type, SearchString, DateTime,NumResults) ";Query += "VALUES ('"+vid+Q+type+Q+this.searchString+"',"+datetime+","+this.numResults+ ")";this.cmd.CommandText = Query;this.cmd.Execute();}catch(e){writeGenericErrLog("Insert SearchTrack failed", "Vid: "+vid+"- SearchString:: "+this.searchString+" - NumResults: "+this.numResults, e.description);}}//end-----------------------------------------------------------------I also wrote a non-object oriented function, and created the commandobject inside the function. But I had the same results.I know that the function is not getting called multiple timesbecause I print out a message each time it is called.This really stumps me. I'll really appreciate any help you canoffer.Thanks,George

View Replies !

'Cannot Insert Duplicate Key' Error With Identity Column As PK

I guess there is first for everything...I had never seen error like this before

Violation of PRIMARY KEY constraint 'PK_table1'. Cannot insert duplicate key in object 'table1'. The statement has been terminated.

In this case Primary Key is Identity column. I do not include Idenity column in Insert statement.

Incidently SQL server machines had cluster failure couple of days before. I am not sure whether it has anything to do with it.

I see this error randomly.

How should I debug it

View Replies !

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 !

Problem Of Duplicate Records With A Simple Insert Statement !

I am using a simple stored proc shown below which inserts a record and updates the same record field called SortID with the Primary Key Column data of the same record.Everything was working fine until few days back. The site has been deployed 2 years back and was LIVE ever since.
We have got thousands of records in this Credits table. The table field called SortID is used for moving the credits up and down.
My problem is now after 2 years of deploying the table has got duplicate records, suprisingly a same credit is appearing under different MemberID and with the same SortID.
How are these duplicate records inserted with a simple insert statement & update ??
Is this a Locking problem or Transaction problem I have no idea.
Any ideas will be of great help
Thanks in Advance
Stored Procedure Used

CREATE PROC SP_SC_INSERT (@memberID int,@title varchar(30),@dir varchar(30),@desc varchar(20))ASINSERT INTO [dbo].[Credits]([MemberID],[Title],[Director], [Description], )VALUES(@memberID,@title,@dir, @desc, )UPDATE Credits Set SortID = @@IDENTITY WHERE CreditID = @@IDENTITYGO

View Replies !

Duplicate Last Record When Using SqlDataAdapter.Update For Insert Command
I'm getting duplicate records for the last record in the datatable. No matter how much or how little my datatable contains row records, it always duplicate the last one for some reason. Is there something wrong with my code below? EXAMID pulling from another stored procedure, which is outputed back to a variable.
---Data Access Layer---- If dt.Rows.Count > 0 Then
'INSERT EXAM ROSTERInsertComm = New SqlCommandsqladapter = New SqlDataAdapterInsertComm = New SqlClient.SqlCommand("ExamOfficers_AddOfficerSpecificExamRoster", conndb)InsertComm.CommandType = CommandType.StoredProcedure
sqladapter.InsertCommand = InsertCommInsertComm.Parameters.Add("@examid", SqlDbType.Int)InsertComm.Parameters("@examid").Value = examidInsertComm.Parameters.Add("@officerid", SqlDbType.Int, 12, "Officer_UID")InsertComm.Parameters.Add("@reimburse", SqlDbType.Bit, 12, "ReimburseToDb")InsertComm.Parameters.Add("@posttest", SqlDbType.Int, 12, "Post_Test")InsertComm.Parameters.Add("@pqcdate", SqlDbType.DateTime, 12, "pqc_date")InsertComm.Parameters.Add("@pqcscore", SqlDbType.Int, 12, "pqc_score")
conndb.Open()
sqladapter.UpdateBatchSize = 100InsertComm.UpdatedRowSource = UpdateRowSource.Nonesqladapter.Update(dt)
InsertComm.ExecuteNonQuery()InsertComm.Dispose()
End If
----Stored Procedure----
ALTER PROCEDURE [dbo].[ExamOfficers_AddOfficerSpecificExamRoster]
@ExamID as int,@OfficerID as int,@reimburse as bit=NULL,@posttest as int=NULL,@pqcdate as datetime=NULL,@pqcscore as int=NULL
ASBEGIN SET NOCOUNT ON;
Insert Into Exam_Officers(EXAM_UID,Officer_UID,reimburse,post_test,pqc_date,pqc_score)values(@ExamID,@OfficerID,@reimburse,@posttest,@pqcdate,@pqcscore)
END

View Replies !

Bulk Insert, Skip Rows With Duplicate Key Error?
Does sql server have a way to handle errors in a sproc which would allowone to insert rows, ignoring rows which would create a duplicate keyviolation? I know if one loops one can handle the error on a row by rowbasis. But is there a way to skip the loop and do it as a bulk insert?It's easy to do in Access, but I'm curious to know if SQL Server propercan handle like this. I am guessing that a looping operation would beslower to execute?

View Replies !

Duplicate Tables Insert/Update In Another Table? Triggers?
I want to be able to duplicate every single record that is inserted or updated in a particular table to another table, but not the delete. Is the best way to set-up a trigger? If so can anyone provide me with an example of how to do this? Also could you just duplicate certain columns in the row I would you have to do all columns?

Thanks for help.

View Replies !

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 !

Insert From Formview And Checking Database To Avoid A Duplicate Entry
I have a form view that I am using to insert new data into a sql express database and would like to find a way to avoid attempting to insert a record if the key already exists. is there a way to do this with the formview insert command. Everything works great until I try to add a record with an already existing value in the unique key field, then it breaks.

View Replies !

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 !

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 !

Violation Of PRIMARY KEY Constraint 'PK_tblType'. Cannot Insert Duplicate Key In Object 'dbo.tblType'.
the point here that i have a small table with two fileds,
ID (guid) as primerykey
RAF(char)
and the table is empty when i add a new row i recieve this exception,
Violation of PRIMARY KEY constraint 'PK_tblType'. Cannot insert duplicate key in object 'dbo.tblType'.
i found no way to solve the problem.
thanks in advans

View Replies !

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 !

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 !

Error Number For Duplicate Column Value During An Insert For A Unique Column ?
I have a table in which a non-primary key column has a unique index on it.
If I am inserting a record into this table with a duplicate column value for the indexed column, then what will be the error number of the error in above scenario? OR How could I find this out?

View Replies !

TOUGH INSERT: Copy Sale Record/Line Items For &"Duplicate&" Record
I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria

View Replies !

How To Insert Values Without Duplicate Values?
Hi,

I want to insert data into table without duplicate values ..

how to do?

View Replies !

SQL Injection - How To Prevent It?
I am building my first ASP.Net app from scratch and while working on the DAL I came across the problem of SQL Injection. I searched on the web and read different articles but I am still unsure about the answer. My question is should I add

db.AddInParameter(dbCommand, "AvatarImageID", DbType.Int32, avatarImageID);

Add in Parameters to my C# code to avoid SQL Injection. What is the best practice. I am unclear if the stored procedure already helps me avoid SQl Injection or if I need the add in parameters in the C# methods to make it work. I need some help. Thanks, Newbie

My C# update method in the DAL (still working on the code)

private static bool Update(AvatarImageInfo avatarImage)
        {
            //Invoke a SQL command and return true if the update was successful.
            db.ExecuteNonQuery("syl_AvatarImageUpdate",
                avatarImage.AvatarImageID,
                avatarImage.DateAdded,
                avatarImage.ImageName,
                avatarImage.ImagePath,
                avatarImage.IsApproved);

            return true;
        }

I am using stored procedures to access the data in the database.

My update stored proc

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[syl_AvatarImageUpdate]
    @AvatarImageID          int,
      @DateAdded              datetime,
      @ImageName              nvarchar(64),
      @ImagePath              nvarchar(64),
      @IsApproved             bit
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      BEGIN TRY
            UPDATE      [syl_AvatarImages]
            SET
                      [DateAdded]       = @DateAdded,
                        [ImageName]       = @ImageName,
                        [ImagePath]       = @ImagePath,
                        [IsApproved]      = @IsApproved
            WHERE [AvatarImageID] = @AvatarImageID
            RETURN
      END TRY
      BEGIN CATCH
            --Execute LogError SP
            EXECUTE [dbo].[syl_LogError];
            --Being in a Catch Block indicates failure.
            --Force RETURN to -1 for consistency (other return values are generated, such as -6).
            RETURN -1
      END CATCH
END

View Replies !

Prevent SP Compilation
Hi,I'm using SQL Server 2000 MSDE on a laptop running Windows XP.I have a couple of SP's that that quite some time to compile. So I waswondering: is there any way to have the database *not* recompile them everytime after a reboot?BOL says: "As a database is changed by such actions as adding indexes orchanging data in indexed columns, the original query plans used to accessits tables should be optimized again by recompiling them. This optimizationhappens automatically the first time a stored procedure is run afterMicrosoft® SQL ServerT 2000 is restarted."Now the SQL Server is restarted a lot, because laptops don't have endlessbatteries <g>Cheers,Bas

View Replies !

Prevent Sql Job Failure.
Have a job that calls a DTS package, DTS is an Export & Import wizard to copy tables. Someone deleted a table from source and my job failed last night. Inputs appreaciated.

View Replies !

How To Prevent Db Deletion
Hi

I want to try and protect myself from my own stupidity. I have a number of sql databases, but one is LIVE. It is easy to drop tables but I want to set something (e.g. a password) which will help prevent me from dropping tables on the live database.

Any help/direction here would be appreciated.

View Replies !

Best Way To Prevent Deadlocks
I'm going thru my application log, and just seeing what errors are popping up. I have a relatively intense search feature, thats causing alot of deadlocks.

Exception type: SqlException
Exception message: Transaction (Process ID 105) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

In general, what's the best way to resolve this ?

Should I see if I can apply "WITH (NOLOCK)" to my data ?

Any suggestions are greatly appreciated !

thanks again!
mike123

View Replies !

Prevent Duplication On UPDATE
Hello
I noticed a spelling mistake in the data in a column of several tables, I used the following syntax to alter the spelling:
UPDATE [dbo].[Prod_Cat] SET [ProdName]=N'merseyside' WHERE ProdName = 'mmserseyside'
The above code correctly updated the spelling error, but it also inserted a new row with the corrected data.
So I found myself with two Identical rows containing the corrected information. I had to manually delete the extra row. Because if I had put in a DELETE statement, I would have then lost both rows.
What do I need to do to prevent this happening next time. As I find that I need to update the names of some products, but I don't want to duplicate them.
Thanks
Lynn

View Replies !

How Do I: Prevent Race Condition?
I want to be able to read and update a value in the database without entering a race condition.
For example:
User #1 reads a row from the database, changes a value then writes the value back.
User #2 reads the same row AFTER user #1 has read it, but BEFORE user #1 writes it back. User #2 then changes the value and writes it back, overwriting the value that user #1 wrote.
I thought I could do this with transactions, but it just makes user #2 wait until user #1 is done writing before user #2 can write. It doesn't stop user #2 from reading while user #1 has it out.
Does that make sense?

View Replies !

How To Prevent SQL Injection Attacks
Hi,

On my site I have a simple textbox which is a keyword search, people type a keyword and then that looks in 3 colums of an SQL database and returns any matches

The code is basic i.e. SELECT * FROM Table WHERE Column1 LIKE %searcg%

There is no validation of what goes into the text box and I am worried about SQL injection, what can I do to minimize the risk

I have just tried the site and put in two single quotes as the search term, this crashed the script so I know I am vunerable.

Can anyone help, perhaps point me in the direction of furthur resources on the subject?

Thanks

Ben

View Replies !

What Are Sql Injection Attacks And How To Prevent?
this is a question I put in the sql community in microsoft, but havent be answered in full

------------

I am using dynamic sql to do a query with differents 'order' sentences and/or 'where' sentences depending on a variable I pass to the sp

ex:

create proc ex
@orden varchar(100)
@criterio varchar(100)

as
declare consulta varchar(4000)

set consulta=N'select pais from paises where '+@criterio' order by '+@orden

------------

I'd like to know it it uses 2 sp in the cache, as I read, the main sp and the query inside the variable of the dynamic sql. if so, as I imagine, then I suppose I have to do the main sp without any 'if' sentence to be the same sp, and so taking it from the cache and not recompile the sp

now, I have various 'if' sentences in the main sp (the caller of the dynamic sql) but I plan to remove them and do the 'if' by program -it is in asp.net-, so I suppose it is better because in this way the main sp is took from the cache, supposing this uses the cache different that the dynamic sql in the variable

what do u think? does the dynamic sql use 2 caches? if so, u think it is better to try to do the main sp same in all uses (no 'if' statements)?

-----

They told me this coding is not good (dynamic sql) because it can give control to the user?

I ask, how does it give control to use? what ar sql injection attack and how to prevent them?

I use dynamis sql because I have 150 queries to do, and thought dynamic sql is good

is it true that dynamic sql have to be recompiled in each execution? I suppose so only if the sql variable is different, right?

can u help me?

View Replies !

Prevent DELETE And/or UPDATE
Hi all!Are there any other way than using rights or Triggers to prevent aDELETE or an UPDATE on a specific column.The "problem" with rights is that they dont apply to all DB-usersThe "problem" with triggers is that they generate lots of extraSQL-codeI would like a solution something like below. If there are anyprimitives like this or other more neat solutions I would be glad toknowCREATE TABLE some_table NO DELETE/* ^^^^^^^^^*/(some_column SOME_TYPE NO UPDATE/* ^^^^^^^^^*/)For clarity, here is a trigger that currently solves the problemCREATE TRIGGER check_updateable_columns ON some_tableFOR UPDATEASIF UPDATE(some_column)RAISERROR(...)GOorCREATE TRIGGER delete_not_allowed ON some_tableINSTEAD OF DELETEASRAISERROR(...)GO

View Replies !

Prevent One Single Row From Being Updated
I am doing customization for microsoft POS. I manually added a recordto a table. The manage and maintenance of this table are done by POS,and user can update the contents of this table. Is there any way I canlock this single row at database level to prevent it from being deletedor changed by user? I am using SQL 2000 and vb.net.Thanks.Leanne

View Replies !

Prevent Trigger From Firing
Hi,

I have an update tigger on one of my tables.
I want to fire an Update SQL but somehow prevent trigger from firing.

Any Ideas..

View Replies !

How To Prevent Displaying Names Of DB?
I get an issue, that is: Once i expand databases from SQL Enterprise Manager, it takes a long time to wait displaying all names of databases. How should i do to prevent displaying all names of databases?

View Replies !

Trigger To Prevent Duplicates
Hi all,

I'm writing a trigger to prevent duplicates. I know that this can be done through primary key or unique constraints but in the real world my uniqueness is defined by 8 columns which is too a big an index to maintain on the primary / unique key.

If I create a table with 2 columns

CREATE TABLE Table1
(CentreCHAR(10),
Month CHAR(3)
)

Then create a trigger to prevent duplicates

CREATE TRIGGER trigger_Check_Duplicates
ON Table1
FOR INSERT, UPDATE
AS
-- This trigger has been created to check that duplicate rows are not inserted into AudioVisual table.
DECLARE @IsDuplicate INTEGER
-- Check if row exists
SELECT @IsDuplicate = 1
FROM Inserted i, Table1 t
WHERE t.Centre = i.Centre
AND t.Month = i.Month
IF (@IsDuplicate = 1)
-- Display Error and then Rollback transaction
BEGIN
RAISERROR ('This row already exists in the table', 16, 1)
ROLLBACK TRANSACTION
END

Then insert a row into the new table (no other data is in there)

INSERT Table1
VALUES('0691040176','AUG')

I get the Trigger error message that the row already exists. Why is this the case? I though that Table 1 (target table) would show no entries as it has no data - it should be a before image of the table and the inserted table should be an after image.

Please help!!!

Thanks

Neill

View Replies !

Prevent Duplicate Insert

How can I prevent duplicate inserts or entries to a table? Thank you. Note: I am using SQL Server and coding ASP.net pages in VB.

How can I prevent duplicate inserts or entries to a table?

Thank you.

Note: I am using SQL Server and coding ASP.net pages in VB.