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
My apologies...I wasn't for sure where to post an error like this...
Over the last 2 months I have gotten this SQL Server error (twice). All existing processes will continue to work, however no new processes can be created and users cannot connect to the server. This is the exact text of the message in the SQL Server error log.
Operating system error 10038: An operation was attempted on something that is not a socket...
Error: 17059, Severity: 18, State: 0
Error accepting connection request via Net-Library 'SSNETLIB'. Execution continuing.
Error: 17882, Severity: 18, State:
While we can typically just stop SQL Server Service and restart the services...I have found it is best to restart the machine during non-production times to take care of any 'residual' effects of this error.
The SQL Server 2000 SP4 box with Windows 2003 Standard SP1 is well maintained by our I.T. team and it typically will run 4 or 5 months without a reboot.
I would like to know what options I have with regards to trapping a duplicate record before it tries to post to a SQL database. I have set the column to unique in SQL. But when I try to use ASP and post a duplicate record I get a system error. I would like to just create a referential error to notify the user that they cannot post a duplicate record please try again. Any help would be greatly appreciated.
I receive the following error message when I try to use the Bulk Insert Task to load BCP data into a table:
Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 4. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (overflow) for row 1, column 1 (rowno).".
Task failed: Bulk Insert Task
In SSMS I am able to issue the following command and the data loads into a TableName table with no error messages: BULK INSERT TableName FROM 'C:DataDbTableName.bcp' WITH (DATAFILETYPE='widenative');
What configuration is required for the Bulk Insert Task in SSIS to make the data load? BTW - the TableName.bcp file is bulk copy file as bcp widenative data type. The properties of the Bulk Insert Task are the following: DataFileType: DTSBulkInsert_DataFileType_WideNative RowTerminator: {CR}{LF}
Any help getting the bcp file to load would be appreciated. Let me know if you require any other information, thanks for all your help. Paul
Hi All, I have a form that inserts information into two tables in sql 2005 database. 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
I apprecaite your hlep. I run this code and got an error message ----------------------------------------------------------- select customer_id ,company_name,CITY_AND_STATE, substring (city_and_state,1,CHARINDEX(',',city_and_state,0)-1)as city, substring (city_and_state,CHARINDEX(',',city_and_state,0)+2, 4) as state into validCustomers from customer
This gave me an error: ------------------------------------------------- Server: Msg 536, Level 16, State 3, Line 1 Invalid length parameter passed to the substring function. The statement has been terminated.
I am trying to insert a new record into a sql table and I get the following error message:
Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table 'tbl_MLS_ReportCriteria' when IDENTITY_INSERT is set to OFF.
The following function I am trying to design is to INSERT data into a SQL Server 2005 database. It is called from a different function, parse_file();, which is looping through a .CSV file and returning each record as an array to recordInsert();. Here is the function code and SQL syntax
I am receiving the following error when attempting to INSERT into the database.
Code:
[Microsoft][ODBC SQL Server Driver][SQL Server]The name "YFM700RVL" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
I have tried googling the error and since it is a long error, it has been hard trying to find an answer. One solution I found (which wasn't a solution) was to add the '@' infront of the variables. It did not work.
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
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?
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.
I have an issue where I am getting an error on an unique index.
I know why I am getting the error but not sure how to get around it.
The query does a check on whether a unique value exists in the Insert/Select. If I run it one record at a time (SELECT TOP 1...) it works fine and just won't update it if the record exists.
But if I do it in a batch, I get the error. I assume this is because it does the checking on the file before records are written out and then writes out the records one at a time from a temporary table.
It thinks all the records are unique because it compares the records one at a time to the original table (where there would be no duplicates). But it doesn't check the records against each other. Then when it actually writes out the record, the duplicate is there.
How do I do a batch where the Insert/Select would write out the records without the duplicates as it does when I do it one record at a time.
I have created few reports using SSRS 2005. I am using Oracle database in Data Source to fetch my data. It is working fine and showing me report correctly. But after running the report 8 to 10 times, it starts giving me Memory error. To get rid of that, I need to recycle (stop-start) ReportingService from IIS.
I am exactly getting following error...
Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
I am not getting the actual problem, why is it giving memory error only after running few times? Please let me know if anyone facing same problem or knowing the solution for the problem.
I'm trying to import data from a Sybase ASE 12.0 database called "OurTestDatabase" into MS SQL Server 2005. I started SSIS Wizard and indicated "Sybase ASE OLEDB Provider" as a source and SQL Native Client as the target. I'm gettign the following error message:
The same data source worked with DTS when we thought we'd convert to MS SQL Server 2000. Is this a bug in SSIS? What can be done? Using ".Net Framework Provider for ODBC" is not a good option because this doesn't allow me to choose any tables from the Sybase source.
Hi, I am getting error while opening the Data Base Engine in Sql server Management Studio. We applied SP2. and restarted the server but no luck. Error Message:Attempted to read or protected memory. This is often an indication that other memory is corrupt(mscorlib).
My problem is that the 'INSERT INTO' query that sends the records to thetable is dynamically compiled in VBA and and the target table has a twocolumn primary key. I have made a number of attempts at getting 'WHERENOT EXISTS' to cure the problem but so far without success and previouspostings have resulted in advice to create an 'ignore duplicates' index.This solved the problem in asmuch as it allowed the SQL to insert the records that did not alreadyexist but resulted in the message appearing every time the user ran thethe query. Whilst this is not a major problem it is vaguely irritatingand I would like to find a way to stop it happening. I suspect that thesolution may involve using the @@ERROR command but I am not sure of thesyntax.RegardsColin*** Sent via Developersdex http://www.developersdex.com ***
When I send message from Server1 to Server2,I found the error "This message could not be delivered because it is duplicate" in SQLProfiler on Target, why message can be duplicate ? who can help me?
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.
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?
I am duplicating a record from my asp.net page in to the database. When i click on save I am getting the following error message Violation of PRIMARY KEY constraint 'PK_clientinfo'. Cannot insert duplicate key in object 'clientinfo'. The statement has been terminated. The above message i am getting since i have tried to duplicate the clientname field of my table which is set as the primary key. What i want is instead of this message in the browser i need to display an alert saying "the clientname entered already exists" by checking the value from the database. Here is my code. pls modify it to achieve the said problem if(Page.IsValid==true) { conn.Open(); SqlCommand cmd = new SqlCommand("insert into clientinfo (client_name, address) values ('"+txtclientname.Text+"', '"+txtaddress.Text+"')", conn); cmd.ExecuteNonQuery(); conn.Close(); BindData(); txtclear(); System.Web.HttpContext.Current.Response.Write("<script>alert('New Record Added!');</script>"); Response.Redirect("Clientinfo.aspx"); }
I want to add the content of a table into anotherI tried to copy all fields, except the primary key:INSERT INTO table2(field2, field3, field4, ...)SELECT field2, field3, field4, ...FROM anotherDB.dbo.table1gives the following error:Violation of UNIQUE KEY constraint...Cannot insert duplicate key...Why?I didn't ask him to copy the key column; Isn't the SQL Server supposedto know how to increment the key ?
I have an File System Task that copies a file from one directory ot another. When I hard code the target directory (c:dirfile.txt) it works fine. When I change it to a virtual directory (\serverdirfile.txt) I get a security error:
[File System Task] Error: An error occurred with the following error message: "Access to the path '\gracehbtest oS2TMM_Live_Title_000002.xml' is denied.".
I am using SQL Server 2000. I have a table (tbDupes) with duplicate records. I want to populate another table (tbUnique) with unique records.
My tbDupes has about 50 columns and three of them make up my primary key in tbUnique. I thought I could get unique records by using the following SQL statement.
Code Snippet INSERT INTO tbUnique SELECT * FROM tbDupes
I received this error: Cannot insert duplicate key
Is there a database setting that will allow me to insert the first record and prevent any duplicates from being inserted? I thought this was the default behavior and think that maybe the DBA changed it.
I know I can achieve this by creating a cursor with tbDupes and then something like "IF NOT EXISTS(...) THEN INSERT". However, I would prefer to avoid using a cursor.
I'm trying to use an XML Task to do a simple XSLT operation, but it fails with this error message:
[XML Task] Error: An error occurred with the following error message: "There are multiple root elements. Line 5, position 2.".
The source XML file validates fine and I've successfully used it as the XML Source in a data flow task to load some SQL Server tables. It has very few line breaks, so the first 5 lines are pretty long: almost 4000 characters, including 34 start-tags, 19 end-tags, and 2 empty element tags. Here's the very beginning of it:
<?xml version="1.0" encoding="UTF-8"?> <ESDU releaselevel="2006-02" createdate="26 May 2006"><package id="1" title="_standard" shorttitle="_standard" filename="pk_stan" supplementdate="01/05/2005" supplementlevel="1"><abstract><![CDATA[This package contains the standard ESDU Series.]]></abstract>
There is only 1 ESDU root element and only 1 package element.
Of course, the XSLT stylesheet is also an XML document in its own right. I specify it directly in the XML Task:
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?
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
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'.
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
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
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.
