insert or replace: error

Showing posts with label error. Show all posts

Friday, March 30, 2012

Inserted Rows count from SSIS not like table Rows Count

Hi all

i using lookup error output to insert rows into table

Rows count rows has been inserted on the table 59,123,019 mill

table rows count 6,878,110 mill ............................

any ideas

So from the error output you are getting 59 123 019 rows? How are you counting this?
What are you using to insert the rows?|||Mapping from values i get and columns on distination table|||How are you counting the rows inserted?|||

I figuer out the Reasone my lookup error output configure to ignore the error and the error was "can't insert duplicated key" i change the error output to insert duplicated rows in flat file but i don't test it yet , i will hint with updates , hope that's resolve the problem

wish me luck

Phil

I Count rows from distination table "Select Count "

|||

Hosam Abdel Wahab wrote:

Phil
I Count rows from distination table "Select Count "

I mean, how are you counting the rows inserted from within SSIS?

But, yes, it sounds like you are on the correct path to figuring out your problem.

Monday, March 26, 2012

Insert Values Error

Hey Guys:

I am trying to create a form, and then insert the values entered by a user into a sql database, I have enclosed the page code below. Everything works except the data is not being inserted into the database, and i keep getting the default message in my error message section. I took this right from the quick start tutorial and started working with it, and keep getting an error.

I believe the error is located in the INSERT statement


<%@. Page Language="vb" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %
<html
<script language="VB" runat="server"
 Dim MyConnection As SqlConnection Sub Page_Load(Sender As Object, E As EventArgs)
 MyConnection = New SqlConnection("server=localhost;database=planetauction;uid=planetauction;pwd=bean13")
 If Not (IsPostBack)
 BindGrid()
 Page.DataBind()
 End If
 End Sub
 Sub AddAuthor_Click(Sender As Object, E As EventArgs)
 Page.Validate()
 If Not Page.IsValid
 Return
 End If
 Dim DS As DataSet
 Dim MyCommand As SqlCommand
 If txtLastName.Value = ""
 Message.InnerHtml = "ERROR: Null values not allowed for Author ID, " & _
 "Name or Phone"
 Message.Style("color") = "red"
 BindGrid()
 Return
 End If
 Dim InsertCmd As String = "insert into users (txtLastName) values (@.lastname)"
 MyCommand = New SqlCommand(InsertCmd, MyConnection)
 MyCommand.Parameters.Add(New SqlParameter("@.lastname", SqlDbType.NVarChar, 50))
 MyCommand.Parameters("@.lastname").Value = txtLastName.Value
 MyCommand.Connection.Open()
 Try
 MyCommand.ExecuteNonQuery()
 Message.InnerHtml = "Record Added<br>" & InsertCmd.ToString()
 Catch Exp As SQLException
 If Exp.Number = 2627
 Message.InnerHtml = "ERROR: A record already exists with the " & _
 "same primary key"
 Else
 Message.InnerHtml = "ERROR: Could not add record, please ensure " & _
 "the fields are correctly filled out"
 End If
 Message.Style("color") = "red"
 End Try
 MyCommand.Connection.Close()
 BindGrid()
 End Sub
 Sub BindGrid()
 Dim MyCommand As SqlDataAdapter = new SqlDataAdapter( _
 "select * from users", MyConnection)
 Dim DS As DataSet = new DataSet()
 MyCommand.Fill(DS, "Users")
 MyDataGrid.DataSource=DS.Tables("Users").DefaultView
 MyDataGrid.DataBind()
 End Sub
</script
<body style="font: 10pt verdana"
 <form runat="server" ID="Form1"
 <h3><font face="Verdana">Inserting a Row of Data</font></h3
 <table width="95%">
 <tr>
 <td valign="top"
 <ASP:DataGrid id="MyDataGrid" runat="server"
 Width="700"
 BackColor="#ccccff"
 BorderColor="black"
 ShowFooter="false"
 CellPadding=3
 CellSpacing="0"
 Font-Name="Verdana"
 Font-Size="8pt"
 HeaderStyle-BackColor="#aaaadd"
 EnableViewState="false"
 /
 </td>
 <td valign="top"
 <table style="font: 8pt verdana">
 <tr>
 <td colspan="2" bgcolor="#aaaadd" style="font:10pt verdana">Add a New Author:</td>
 </tr>
 <tr>
 <td nowrap>Last Name: </td>
 <td>
 <input type="text" id="txtLastName" runat="server" NAME="txtLastName"><br
 </td>
 </tr>
 <tr>
 <td></td>
 <td style="padding-top:15">
 <input type="submit" OnServerClick="AddAuthor_Click" value="Add Author" runat="server" ID="Submit1" NAME="Submit1">
 </td>
 </tr>
 <tr>
 <td colspan="2" style="padding-top:15" align="center">
 <span id="Message" EnableViewState="false" style="font: arial 11pt;" runat="server"/>
 </td>
 </tr>
 </table
 </td>
 </tr>
 </table
 </form
</body>
</html>

Put a more specific error message in while debugging:


Try
 MyCommand.ExecuteNonQuery()
 Message.InnerHtml = "Record Added<br>" & InsertCmd.ToString()
 Catch Exp As SQLException
 If Exp.Number = 2627
 Message.InnerHtml = "ERROR: A record already exists with the " & _
 "same primary key"
 Else
 Message.InnerHtml = "ERROR: Could not add record, please ensure " & _
 "the fields are correctly filled out. Exception: " & Exp.ToString()
 End If Message.Style("color") = "red"
 End Try

|||Mr. Reily:

Thanks for your advice. I added the detailed error message and here is what I got:

ERROR: Could not add record, please ensure the fields are correctly filled out. Exception: System.Data.SqlClient.SqlException: Invalid column name 'txtLastName'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.reference3_aspx.AddAuthor_Click(Object Sender, EventArgs E) in http://localhost/planetauction/reference3.aspx:line 47

I really think the error is in the insert statement. I need to put either double or single quotes around something in the insert statement, right now the database believes the value is referring to a column name, rather than inserting that data into the column specified.

Let me know what u think.

Thanks|||Thanks guys, got it all figured out.

It was the insert statment that was screwed up. For anyone else with the same problem, I simply mixed up the values, and the column names in the sql insert statements.

For Reference:

 Dim InsertCmd As String = "insert into users (COLUMNNAME1, COLUMNNAME2) values (@.FIRST SQL PARAMETER LISTED BELOW, SECOND SQL PARAMETER LISTED BELOW, etc)"
 MyCommand = New SqlCommand(InsertCmd, MyConnection)
 MyCommand.Parameters.Add(New SqlParameter("@.lastname", SqlDbType.NVarChar, 50))
 MyCommand.Parameters("@.lastname").Value = txtLastName.Value
 MyCommand.Connection.Open()

Wednesday, March 21, 2012

insert to two tables

I posted this question previously but am now getting another error and fixing it (months ago). I am trying insert The ID2 column (pk) from table 2 into ID2 column (fk) of table 1. What the T-SQL is trying to do is check to see if the record in table 2 already exists in then just pass the value to the insert query for table 1. If the record does not exist then insert data into table 2first then perform the table 1 insert.

This SP works fine if the user does not exist in table 2. However, if the user does exist in table2 then instead of passing the ID, a null value is passed.

Can anyone tell me why the first query does not work? If there is an easier way of doing this then I am all ears.

Thank you

DECLARE @.IdentityHolder int

BEGIN TRANSACTION
IF EXISTS (SELECT ID2 FROM [tbl2] WHERE ID2 = @.ID2)
BEGIN
(SELECT ID2 FROM [tbl2] WHERE ID2 = @.ID2)
END
ELSE
BEGIN
INSERT INTO [tbl2] ([ID2], [FN], [LN], emailAdd])
VALUES ( @.ID2, @.fName, @.lName, @.emailAdd)
END
COMMIT

SET @.IdentityHolder = (ID2)
INSERT INTO [tbl1]([ID2], [event])
VALUES (@.ID2, @.event)Your column list has 5 cols and your value list has 4?|||nope, tbl2 column list has four - ([ID2], [FN], [LN], emailAdd]) and the values has four - (@.ID2, @.fName, @.lName, @.emailAdd)|||OK,

What;'s this then?

SET @.IdentityHolder = (ID2)|||Because your ID2 in your set statement is not correlated to ant table/column value

Try this

BEGIN TRANSACTION
INSERT INTO [tbl2]
([ID2], [FN], [LN], emailAdd])
VALUES ( @.ID2, @.fName, @.lName, @.emailAdd)

INSERT INTO [tbl1]
([ID2], [event])
VALUES (@.IdentityHolder, @.event)

IF @.@.error = 0
COMMIT
ELSE
ROLLBACK

You begin the tran ... If the first insert fails, the row exists in tbl2, so you insert into tbl1. If the first insert succeeds, the row did not exist in tbl2, so you have performed both inserts without an extra call to the database. The final check of @.@.error looks for a failure to insert into tbl1. If that <> 0, the entire transaction is rolled back.

Advanced error handling is left as an excercise to the developer.|||how does @.IdentityHolder know to get the value of ID2? @.IdentityHolder is a variable and needs to be set right?|||Violation of PRIMARY KEY constraint 'PK_tbl2. Cannot insert duplicate key in object 'tbl2'.

Cannot insert the value NULL into column 'ID2', table 'tbl1; column does not allow nulls. INSERT fails.|||i know what the violation is, when using the query you gave me I am not searching to see if the user already exists in the db. The "cannot insert a null" is the prblem I am still having. I am still unable to pull the fk value for tbl1 from tble2. Any ideas on how to do this?|||ok ... post the schema for both tables and let's work this out. I assumed in my solution that you were providing an incoming value for @.IdentityHolder. My mistake (you know what happens when you assume).

If this is not the case and you are looking for an identity value upon a successful insert into tbl2, you will want to use the scope_identity() function.|||yeah, i did scope first but it is giving me some other number. For example, if the value is 102 then scope gives me 17.

tbl1
id1 (pk)
event

tbl2
ID2(pk)
ID1(fk)
FN
LN
emailAdd|||so id1 in tbl2 is not an identity column?

Then you must be passing in a value for id1 if the entry does not exist in tbl2|||With this schema
tbl1
id1 (pk)
event

tbl2
ID2(pk)
ID1(fk)
FN
LN
emailAdd

CREATE proc Two_Table_Insert @.ID2 = null, @.ID1, @.FN, @.LN, @.emailAdd, @.event = null
AS
IF @.ID2 is not null
BEGIN
IF EXISTS (select 1 from tbl2 where ID2 = @.ID2)
BEGIN
BEGIN TRANSACTION
SET @.ID1 = ID1
FROM tbl2
WHERE ID2 = @.ID2
COMMIT
END
ELSE
BEGIN
BEGIN TRANSACTION
INSERT INTO tbl2 (ID2, ID1, FN, LN, emailAdd)
VALUES (@.ID2, @.ID1, @.FN, @.LN, @.emailAdd)
-- optional check status of 1st insert ...
-- rollback if failed and exit or do insert into tbl1 if event is not null
-- check status of second insert ... rollback if failed and exit
COMMIT
END|||I don't under stand why this line is in the script. "WHERE ID2 = @.ID2" I will not know this ID, it will increment aotumatically with the insert.

Also I did mess up the scema, it should look like this

tbl1
ID1(pk)
ID2(fk)
event

tbl2
ID2(pk)
FN
LN
emailAdd

When the user enter a request it will look like this:
102, 'john', 'doe', 'jdoe@.email.com', 'request desription'

the data will enter the db like this;
tbl2
102, john, doe, jdoe@.email.com,
tbl1
+1, 102, request description.

my orginal code work fine except I wasn't pulling the pk for tbl2 to fill tbl1.|||I don't under stand why this line is in the script. "WHERE ID2 = @.ID2" I will not know this ID, it will increment aotumatically with the insert.

so ... is ID2 in tbk2 an identity column?

is ID1 in tbl1 an identity column?|||so ... is ID2 in tbk2 an identity column?

is ID1 in tbl1 an identity column?

I guess I don't know what the term "identity column" means. ID2 is the PK of tbl2.|||BOL is your friend:

IDENTITY (Property)

Creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.

Note The IDENTITY property is not the same as the SQL-DMO Identity property that exposes the row identity property of a column.

This is why I asked for the schema of the tables. a column that has the IDENTITY property will script out sort of like this

CREATE TABLE tbl2 (
ID2 int not null IDENTITY,
FN varchar(32) null,
LN varchar(32) null,
emailAdd varchar(255) null )

so ... does ID2 in tbl2 have the IDENTITY property? How about ID1 in tbl1?

If ID2 is an identity column, you can capture the value when you insert a row by using the SCOPE_IDENTITY() function. If not, then we have to use another way to get the value of ID2 upon insert into tbl2.|||Sorry, I know that as the primary key, but yes both ID1 and ID2 are Identity columns. I tried using scope_Identity but it only grabs the insert value of ID2 and not the value of ID2if it exist.|||You have stated that tbl2.id2 is an identity column, but you also stated in a prior post to this thread that :

When the user enter a request it will look like this:
102, 'john', 'doe', 'jdoe@.email.com', 'request desription'

The user does not provide the value of an identity column for an insert where the column is a primary key, unless you set identity_insert on. They can for the table where the value would be the FK.

Nevertheless, I'm going to take the data as stated in the quote above and give you what would work.

CREATE proc Two_Table_Insert @.ID2, @.FN, @.LN, @.emailAdd, @.event
AS
DECLARE @.tbl2ID2 int, @.err int
IF EXISTS (select 1 from tbl2 where ID2 = @.ID2)
BEGIN
INSERT INTO tbl1 (ID2, event)
values (@.id2, @.event)

SELECT @.err = @.@.ERROR
END
ELSE
BEGIN
BEGIN TRANSACTION
INSERT INTO tbl2 (FN, LN, emailAdd)
VALUES (@.FN, @.LN, @.emailAdd)

SELECT @.err = @.@.ERROR
IF @.err = 0
BEGIN
select @.tbl2ID2 = scope_identity()
INSERT INTO tbl1 (ID2, event)
values (@.tbl2ID2, @.event)

SELECT @.err = @.@.ERROR
IF @.err = 0
COMMIT
ELSE
ROLLBACK
END
ELSE
ROLLBACK
END
RETURN @.err|||still not working, the scope_Identity is getting the value from the tbl1 not tbl2. Is there a way to get the value of strored procedure parameters. Also I was wrong, id2 is not an Identity column (it is the PK) because it is supplied by the user.|||I got it to work i just used the @.ID2 in the value of both tables and it worked. When I first set up the db both values were automatically generated by the db, but then i changed the scema and forgot i could do it this way. thanks|||glad to help. should you post again, besure to include all elements of the schema ... as you can see, it was important to arrive at a workable solution.|||no doubt, i can see clearly now the rain is gone. ;)

Monday, March 19, 2012

Insert Syntax Error

In SQL 7.0 SP3 , I am receiving this message...

Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near ')'.

When I try to execute this code from SQL Query Analyzer...

DECLARE @.DPPNumberCursor INT
DECLARE DPPNumberCursor Cursor for Select PPAP_ID
from ppap
where ppap_cancel <> "1" and
ppap_close <> "1" and
projectonhold <> "1"

OPEN DPPNumberCursor

Fetch Next From DPPNumberCursor

INTO @.dppnumbercursor

While @.@.Fetch_Status = 0
Begin
INSERT INTO APQPSubformTable (apqpsub_id)
Values (@.dppnumbercursor)

Bsically, I want to insert the number held in @.dppnumbercursor in the APQPSub_id field.

Any help would be appreciated.How about END at the end?|||...and DEALLOCATE/CLOSE would be very appropriate ;)|||OK, I'm an idiot...can you tell I'm a newbie? Thanks for not tearing me up too bad :-)

Thanks to both for your help....|||You're welcome...from both of us ;)|||Nobody is going to mention the double quotes?

Or the fact that s/he doesn't need a cursor at all?

INSERT INTO APQPSubformTable(apqpsub_id)
SELECT PPAP_ID
FROM ppap
WHERE ppap_cancel <> '1'
AND ppap_close <> '1'
AND projectonhold <> '1'|||I was thinking about it, but then decided not to...when he/she thanked "both" of us ;)|||It's been one of those days:-)

Just for my own edification, what about the double quotes?

The reason I used the cursor was because I needed to add two records into the APQPSubformtable for each open project in the ppap table. Here is the completed working code...

DECLARE @.DPPNumberCursor INT
DECLARE DPPNumberCursor Cursor for Select PPAP_ID
from ppap
where ppap_cancel <> "1" and
ppap_close <> "1" and
projectonhold <> "1"

OPEN DPPNumberCursor

Fetch Next From DPPNumberCursor

INTO @.dppnumbercursor

While @.@.Fetch_Status = 0
Begin
INSERT INTO APQPSubformTable (apqpsub_id,[Key_element#],key_element, resp_area, [Fkey_element#], GYR_status)
Values (@.dppnumbercursor,"31","Start Of Production (SOP) [F4]","OP","0","G")

INSERT INTO APQPSubformTable (apqpsub_id,[Key_element#],key_element, resp_area, [Fkey_element#], GYR_status)
Values (@.dppnumbercursor,"32","Stable Production (Review) [F5]","OP","0","G")

FETCH NEXT from DPPNumberCursor

INTO @.DPPNumberCursor

End

Close DPPNumberCursor|||Double-quotes are harmless as long as you know what your connection settings are when you compile your procedure/function/trigger/view (should be SET QUOTED_IDENTIFIER OFF).|||Just for my own edification, what about the double quotes?

It is a better practice to use single quotes to delimit strings. Double quotes can serve two purposes: delimit a string and delimit an identifyer (ie table name or column name). There is a QUOTED_IDENTIFYER property the determines which. Set to ON, everything within double quotes is supposed to be considered an Identifyer.

I've read in BOL that SLQ Server is 'not very rigorous' in enforcing this rule, depending on the length of the string in question. Small strings such as the ones you are using seem to work OK, but you'd be better off delimiting the strings correctly than relying on SQL Analyzer to interpret what it is you really mean.|||Don't Play with Settings...

INSERT INTO APQPSubformTable (apqpsub_id,[Key_element#],key_element, resp_area, [Fkey_element#], GYR_status)
SELECT @.dppnumbercursor,'31','Start Of Production (SOP) [F4]','OP','0','G'
UNION ALL
SELECT @.dppnumbercursor,'32','Stable Production (Review) [F5]','OP','0','G'

Insert syntax error

I'm not quite sure if this is the right forum. I am trying to execute a insert statement. Using an ADO connection with Jet 4 (MS Access and VBA).

"INSERT INTO tblLocking (Table, Record, User) VALUES ('tblDiscrepancy','70','mnewheiser')"

I am getting a syntax error on the above statement. All fields that i am inserting to (Table, Record and User) are all text types, and the table does exist.

There is a fourth column in the table (LockID) which is a Access [Auto-Number] type(why i'm not sure on the forum) do i need to declare this in the statement.

Any help would be much appreciated as its is beginning to drive me mad.

CheersOk, so it appears the solutions wasn't as complicated as i thought it would be.

Although 'Table' and 'User' where valid columns in the table, they are also reserved words (or something like that). So in the statement they needs [ and ] around them (i.e. [Table])

Don't have to slit my wrists now. :)

insert stored procedure with error check and transaction function

Hi, guys
I try to add some error check and transaction and rollback function on my insert stored procedure but I have an error "Error converting data type varchar to smalldatatime" if i don't use /*error check*/ code, everything went well and insert a row into contract table.
could you correct my code, if you know what is the problem?

thanks

My contract table DDL:
************************************************** ***

create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);

My insert stored procedure is:
************************************************** *****

create proc sp_insert_new_contract
( @.contractDate [smalldatetime],
@.tuition [money],
@.studentId [char](4),
@.contactId [int])
as

if not exists (select studentid
from student
where studentid = @.studentId)
begin
print 'studentid is not a valid id'
return -1
end

if not exists (select contactId
from contact
where contactId = @.contactId)
begin
print 'contactid is not a valid id'
return -1
end
begin transaction

insert into contract
([contractDate],
[tuition],
[studentId],
[contactId])
values
(@.contractDate,
@.tuition,
@.studentId,
@.contactId)

/*Error Check */
if @.@.error !=0 or @.@.rowcount !=1
begin
rollback transaction
print Insert is failed
return -1
end
print New contract has been added

commit transaction
return 0
goI recreated your environment including tables, DRI, and stored procedure in question. This is how I call it which successfully executes:

exec sp_insert_new_contract
@.contractDate = '01/01/2004',
@.tuition = 3000,
@.studentId = 'ABCD',
@.contactId = 1

Monday, March 12, 2012

INSERT Statement Error

I have a web app w/a form that takes user data and inserts into a customers
table. Now if I try to insert a record w/identical 'customerName', this is
the error:
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'FK_transactions_customers'. The conflict occurred in database 'CHZ', table
'customers', column 'CID'.
Now the constraint 'FK_transactions_customers' uses CID.Customers as my
Primary key and Trans.CID as the foreign key. I can't seem to figure out wh
y
the error is thrown when using an existing 'customerName'.
Here's the ddl:
CREATE TABLE [customers] (
[CID] [int] IDENTITY (1, 1) NOT NULL ,
[customerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[customerID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[address] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CDB] [bit] NULL ,
CONSTRAINT [PK_customers] PRIMARY KEY NONCLUSTERED
(
[CID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
TIA for help.Post the DDL for Trans, as well as the offending INSERT statement.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:FB917852-FA0D-4EE8-B6DD-15A142D3B934@.microsoft.com...
I have a web app w/a form that takes user data and inserts into a customers
table. Now if I try to insert a record w/identical 'customerName', this is
the error:
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'FK_transactions_customers'. The conflict occurred in database 'CHZ', table
'customers', column 'CID'.
Now the constraint 'FK_transactions_customers' uses CID.Customers as my
Primary key and Trans.CID as the foreign key. I can't seem to figure out
why
the error is thrown when using an existing 'customerName'.
Here's the ddl:
CREATE TABLE [customers] (
[CID] [int] IDENTITY (1, 1) NOT NULL ,
[customerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[customerID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[address] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CDB] [bit] NULL ,
CONSTRAINT [PK_customers] PRIMARY KEY NONCLUSTERED
(
[CID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
TIA for help.|||You are trying to insert a customer which isn=B4t present in the
transaction table. Check this.
HTH, jens Suessmeyer.|||"Jens" wrote:

> You are trying to insert a customer which isn′t present in the
> transaction table. Check this.
> HTH, jens Suessmeyer.
>
Shouldn't be a problem, unless the foreign key constraint is backwards
(customers references transactions instead of transactions references
customers).

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint

For some reason, I'm getting this error, even without the DBCC Check:

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'Category_Category_FK1'. The conflict occurred in database 'mydb', table 'Category', column 'CategoryID'.
The statement has been terminated.

The very first insert fails...it was working fine before:

DELETE Category;

-- Now, insert the initial 'All' Root Record

INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'All'

INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 2, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'Store'

/* Finally, insert the rest and match on the Parent
Category Name based on the CategoryStaging table
*/

WHILE (@.@.ROWCOUNT <> 0)
BEGIN
INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID)
SELECT c.CategoryID, s.CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1
FROM Category c INNER JOIN CategoriesStaging s ON c.[Name] = s.ParentCategoryName
WHERE NOT EXISTS (SELECT 1 FROM Category c WHERE s.[CategoryName] = c.[Name])
AND s.CategoryName <> 'All'

Here's the schema:

CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[ParentCategoryID] [int] NULL,
[Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [Category_PK] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [mydatabase]
GO
ALTER TABLE [dbo].[Category] WITH NOCHECK ADD CONSTRAINT [Category_Category_FK1] FOREIGN KEY([ParentCategoryID])
REFERENCES [dbo].[Category] ([CategoryID])

The error message is pretty clear. You have specified key values that doesn't exist in the parent table. The message indicates the constraint and table so you can find out which tables are involved. I think your problem is the following:

You are using IDENTITY column as primary key. And you have another column that references the identity column in the same table. When you do a DELETE it doesn't reset the seed for identity column. So if you had performed data manipulations before you are going to get new identity value based on the previous generated values and it will not start at 1 or whatever your seed is. TRUNCATE TABLE on the other hand will reset the identity seed to the original value. Or you can also use DBCC CHECKIDENT. Typically when you store this sort of parent child relationship you should have the root's parent as NULL to simplify your operations rather than the root id itself.

|||

A few questions then to your response:

1) I cannot use truncate on that table, because it has a FK relationship

2) I've tried DBCC CHECKIDENT ('Category', RESEED, 0). That seems to work only half the time, then I get that error again here and there still

What I want is, every time we need to import new categories

a) Delete all data in the Category Table

b) Ensure that the new inserts start at 1 again for the CategoryID identity

how can this be accomplished if 1 and 2 have been tried..based on my SQL I've coded?

|||DBCC CHECKIDENT is the only way if you have references. Could you post some sample code that demonstrates the problem and also mention the version of SQL Server? I haven't had any issues with DBCC CHECKIDENT to reseed identity values. And I don't know of any bug in SQL Server related to the DBCC CHECKIDENT command.|||I need to drop the constraint, do my inserts, then recreate the constraint at the end. I am working on the syntax for that drop and re-create..|||I've had this error msg and simply deleted the relationship, and in Enterprise Manager redefined the relationship by clicking FIRST on the primary table key and then linking it to the other table's key.|||

I get this problem as well and have the following table and with constraints:

CREATE TABLE [BudgetDetail] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,

[BudgetHeaderID] [int] NULL ,
[SupersededDetailID] [int] NULL

CONSTRAINT [PK_BudgetDetail] PRIMARY KEY NONCLUSTERED
(
[RecordID]
) ON [PRIMARY] ,
CONSTRAINT [FK_BudgetDetail_BudgetDetail_SupersededDetailID] FOREIGN KEY
(
[SupersededDetailID]
) REFERENCES [BudgetDetail] (
[RecordID]
)
) ON [PRIMARY]
END

The delete fails with the conbstraint error even though the [SupersededDetailID] is set to NULL.

DELETE FROM BudgetDetail WHERE BudgetHeaderID = 1885 AND RecordID = 269673

DELETE statement conflicted with COLUMN SAME TABLE REFERENCE constraint 'FK_BudgetDetail_BudgetDetail_SupersededDetailID'. The conflict occurred in database 'SitestreamTest', table 'BudgetDetail', column 'SupersededDetailID'.
The statement has been terminated.

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint

For some reason, I'm getting this error, even without the DBCC Check:

The very first insert fails...it was working fine before:

DELETE Category;

-- Now, insert the initial 'All' Root Record

INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 2, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'Store'

/* Finally, insert the rest and match on the Parent
Category Name based on the CategoryStaging table
*/

Here's the schema:

CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[ParentCategoryID] [int] NULL,
[Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [Category_PK] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [mydatabase]
GO
ALTER TABLE [dbo].[Category] WITH NOCHECK ADD CONSTRAINT [Category_Category_FK1] FOREIGN KEY([ParentCategoryID])
REFERENCES [dbo].[Category] ([CategoryID])

|||

A few questions then to your response:

1) I cannot use truncate on that table, because it has a FK relationship

2) I've tried DBCC CHECKIDENT ('Category', RESEED, 0). That seems to work only half the time, then I get that error again here and there still

What I want is, every time we need to import new categories

a) Delete all data in the Category Table

b) Ensure that the new inserts start at 1 again for the CategoryID identity

how can this be accomplished if 1 and 2 have been tried..based on my SQL I've coded?

I've had this error msg and simply deleted the relationship, and in Enterprise Manager redefined the relationship by clicking FIRST on the primary table key and then linking it to the other table's key.|||

I get this problem as well and have the following table and with constraints:

CREATE TABLE [BudgetDetail] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,

[BudgetHeaderID] [int] NULL ,
[SupersededDetailID] [int] NULL

The delete fails with the conbstraint error even though the [SupersededDetailID] is set to NULL.

DELETE FROM BudgetDetail WHERE BudgetHeaderID = 1885 AND RecordID = 269673

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint

For some reason, I'm getting this error, even without the DBCC Check:

The very first insert fails...it was working fine before:

DELETE Category;

-- Now, insert the initial 'All' Root Record

INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 2, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'Store'

/* Finally, insert the rest and match on the Parent
Category Name based on the CategoryStaging table
*/

Here's the schema:

CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[ParentCategoryID] [int] NULL,
[Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [Category_PK] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [mydatabase]
GO
ALTER TABLE [dbo].[Category] WITH NOCHECK ADD CONSTRAINT [Category_Category_FK1] FOREIGN KEY([ParentCategoryID])
REFERENCES [dbo].[Category] ([CategoryID])

|||

A few questions then to your response:

1) I cannot use truncate on that table, because it has a FK relationship

2) I've tried DBCC CHECKIDENT ('Category', RESEED, 0). That seems to work only half the time, then I get that error again here and there still

What I want is, every time we need to import new categories

a) Delete all data in the Category Table

b) Ensure that the new inserts start at 1 again for the CategoryID identity

how can this be accomplished if 1 and 2 have been tried..based on my SQL I've coded?

insert statement

my insert statement got no error but when i check my table the values were not inserted actually.. what maybe the problem?what's the statement? post it here. also post the table DDL.|||my insert statement got no error but when i check my table the values were not inserted actually.. what maybe the problem?I suspect that something didn't work as you expected.

As jezemine suggested, we could help a lot more if you posted the DDL to create the table and the DML that has you puzzled.

-PatP|||my DDL is not hard coded. i have 5 columns in my Cust table(ID, PW, Name, Contact, Add) and the data types are all varchar just having different sizes. the password(PW) is auto generated in my code so i allow nulls for this just for the mean time.

the second problem i encounter is that it has error about the violation of primary key. how can i check that if i can't see the values i inserted in the table? and besides, I'm sure that I'm not entering duplicate primary keys but the error is still the same.

additionally, if you can also help... the speed of my application is very slow. that's only my code and it's responding slooowly... i can't imagine how it will run if i already have a lot of codes.

cmd.CommandText = "INSERT INTO Cust(ID, Name, Contact, Add) VALUES('@.ID','@.Name','@.Contact','@.Add')"; cmd.Parameters.Add("@.ID",SqlDbType.VarChar).Value=textBoxID.Text; cmd.Parameters.Add("@.Name",SqlDbType.VarChar).Value=textBoxName.Text; cmd.Parameters.Add("@.Contact",SqlDbType.VarChar).Value=textBoxContact.Text; cmd.Parameters.Add("@.Add",SqlDbType.VarChar).Value=textBoxAdd.Text;|||you want to remove the single quotes around the parameters:

INSERT INTO Cust(ID, Name, Contact, Add)
VALUES(@.ID,@.Name,@.Contact,@.Add)

very likely, the reason you are seeing pk violations is because you kept trying to insert the value '@.ID' into the pk column.

You might want to (re)read the examples here:
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx|||it has no errors now but still, i can't see in my table the values i inserted. no changes|||Why don't you use stored procedures?|||when you check to see if the values are there, are you sure you are looking at the same database/table that your app is inserting to?

what method are you using to verify the values are there? SSMS, QA, or what? what query are you issuing?|||Why don't you use stored procedures?

Because he/she is not taking the time to read the manual or even check help. When people refer him/her to sites with information, he/she ignores it and continues to ask the same question.

Looking for quick answers and other people to solve your SQL problems for you is not going to teach you anything. Reading the manual or actually bothering to read what people are sending you is also important.|||how sure are you that i didn't read and tried those links? you don't know how i take time reading related articles just to answer my question. if you have nothing to say good, then better fall silent. I'm not forcing everyone here to answer my question especially you! besides, there's a lot of forums that can help me... more patient! I can't blame myself for being slow if you think so, because I'm just new. Don't you realize that you just post here in my thread just to say that.. well, thank you for having time insulting me.|||Your welcome.

I am sure you'll have better luck with those patient forums.

Godspeed.|||my insert statement got no error but when i check my table the values were not inserted actually.. what maybe the problem?

First off, chill

Second, do you think this post helps us figure out what the problem is?

Third, why don't you use stored procedures?|||my insert statement got no error but when i check my table the values were not inserted actually.. what maybe the problem?
the second problem i encounter is that it has error about the violation of primary key.
Care to clarify?

Wednesday, March 7, 2012

Insert Query Problem

hi all i m trying to insert in my datbase this query but it is giving error at the line myCommand.ExecuteNonQuery();

myConnection.Open();
String insertCmd = "insert into CRtable(@.CR_NO, @.Asset_Type, @.PortIDNew, @.SpecialRequest, @.MovePortFrom, @.MovePortTo, @.MoveHardware, @.MoveSoftware, @.AllSelected, @.SpecialSoftwarePort, @.SpecialSoftwares, @.OtherSoftwares, @.FolderName, @.FolderPurpose, @.FolderUserIds, @.FolderRetentionPeriod, @.SpecialAccessOnExistFolder, @.AcessType, @.ExistFolderUserId, @.SpecialAcessOnIntranet, @.InternetUserId, @.MailId, @.HeadSet, @.Requester, @.RequestDateTime, @.Dept, @.PurposeOfCrForm, @.RequestedPort, @.ecode, @.qty, @.process, @.cost_evaluation, @.capital_exp, @.tat)";

SqlCommand myCommand = new SqlCommand(insertCmd,myConnection);myCommand.Parameters.Add("@.CR_NO",crm1);
myCommand.Parameters.Add("@.Asset_Type",StrAssetType2+StrAssetType);
myCommand.Parameters.Add("@.PortIdNew",StrPorts);
myCommand.Parameters.Add("@.SpecialRequest",StrSpecialRequest);
myCommand.Parameters.Add("@.MovePortFrom",txtAssetMovementFrom);
myCommand.Parameters.Add("@.MovePortTo",txtAssetMovementTo);
myCommand.Parameters.Add("@.MoveHardware",StrAssetHardware);
myCommand.Parameters.Add("@.MoveSoftware",StrAssetSoftware);
myCommand.Parameters.Add("@.AllSelected", StrAssetStatus);
myCommand.Parameters.Add("@.SpecialSoftwarePort",StrSpecialSoftwarePort);
myCommand.Parameters.Add("@.SpecialSoftwares",StrSpecialSoftware);
myCommand.Parameters.Add("@.OtherSoftwares",StrOtherSoftware);
myCommand.Parameters.Add("@.FolderName",StrFolderName);
myCommand.Parameters.Add("@.FolderPurpose","");//
myCommand.Parameters.Add("@.FolderUserIds",StrFolderUserId);
myCommand.Parameters.Add("@.FolderRetentionPeriod",StrFolderRetentionDays);
myCommand.Parameters.Add("@.SpecialAccessOnExistFolder","");//
myCommand.Parameters.Add("@.AcessType",StrAcessType);
myCommand.Parameters.Add("@.ExistFolderUserId",StrExistFolderUserId);
myCommand.Parameters.Add("@.SpecialAcessOnIntranet","");//
myCommand.Parameters.Add("@.InternetUserId","");//
myCommand.Parameters.Add("@.MailId","");//
myCommand.Parameters.Add("@.HeadSet","");//
myCommand.Parameters.Add("@.Requester",StrRequest);
myCommand.Parameters.Add("@.RequestDateTime",time);
myCommand.Parameters.Add("@.Dept",StrDept);
myCommand.Parameters.Add("@.PurposeOfCrForm",StrPurpose);
myCommand.Parameters.Add("@.RequestedPort"," ");//
myCommand.Parameters.Add("@.ecode",ecode);
myCommand.Parameters.Add("@.qty",quantity);
myCommand.Parameters.Add("@.process",proces);
myCommand.Parameters.Add("@.cost_evaluation",cost);
myCommand.Parameters.Add("@.capital_exp"," ");//
myCommand.Parameters.Add("@.tat",tat);
try
{
myCommand.ExecuteNonQuery();
}
catch (SqlException er)
{
Response.Write(er.StackTrace);
}

myConnection.Close();
}

the error mwssage is following

Line 1: Incorrect syntax near ')'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near ')'.

Source Error:

Line 182:// try
Line 183:// {
Line 184: myCommand.ExecuteNonQuery();
Line 185:// }
Line 186:// catch (SqlException er)

Source File: c:\inetpub\wwwroot\cr request form\crpreview1.aspx.cs Line: 184

why this is occuring?

The correct structure for an INSERT statement is like this:

INSERT INTO someTable(column1, columns2)VALUES (@.param1, @.param2)

|||hi thanks for ur response but there is another problem actully I am passing some values in a parameter as blank resulting in incorrect formation of my InsertString.
it is be getting build as
"insert ito table values('val1',val2',)
Resulting in Error.But I have to pass these null values depending upon the Requet.Querystring form another page.It can be any value or it can not have any value .what should i do for it?|||hi the command run properly but the values r not inserting in the table .
what will be there.my query is
string insertCmd="insert into CRtable('" + crm1 + "','" + StrAssetType2 + StrAssetType +"','" + StrPorts + "','" + StrSpecialRequest + "','" + txtAssetMovementFrom + "','" + txtAssetMovementTo + "','" + StrAssetHardware + "','" + StrAssetSoftware + "','" + StrAssetStatus + "','" + StrSpecialSoftwarePort + "','" + StrSpecialSoftware + "','" + StrOtherSoftware + "','" + StrFolderName + "','" + null + "','" + StrFolderUserId + "','" + StrFolderRetentionDays + "','" + null + "','" + StrAcessType + "','" + StrExistFolderUserId + "','" + null + "','" + null + "','" + null + "','" + null + "','" + StrRequest + "','" + time + "','" + StrDept + "','" + StrPurpose + "','" + null + "','" + ecode + "','" + quantity + "','" + proces + "','" + cost + "','" + null + "'," + tat + ")";

but no insertion in table.|||

I agree with tmorton , first please make sure you use correct T-SQL syntax in your query. For example if you want to insert a row with some null fields, you should use such query:

insert into mytable(column1,column2,column3) values('val1',null,'val3')

|||

for those string variable, use ' ' for the value

www.cyberxforum.com

|||

ruchipunetha:
hi the command run properly but the values r not inserting in the table .
what will be there.my query is
string insertCmd="insert into CRtable('" + crm1 + "','" + StrAssetType2 + StrAssetType +"','" + StrPorts + "','" + StrSpecialRequest + "','" + txtAssetMovementFrom + "','" + txtAssetMovementTo + "','" + StrAssetHardware + "','" + StrAssetSoftware + "','" + StrAssetStatus + "','" + StrSpecialSoftwarePort + "','" + StrSpecialSoftware + "','" + StrOtherSoftware + "','" + StrFolderName + "','" + null + "','" + StrFolderUserId + "','" + StrFolderRetentionDays + "','" + null + "','" + StrAcessType + "','" + StrExistFolderUserId + "','" + null + "','" + null + "','" + null + "','" + null + "','" + StrRequest + "','" + time + "','" + StrDept + "','" + StrPurpose + "','" + null + "','" + ecode + "','" + quantity + "','" + proces + "','" + cost + "','" + null + "'," + tat + ")";
but no insertion in table.

Again, the correct syntax is:

INSERT INTO someTable(column1, columns2)VALUES (@.param1, @.param2)

Note that I used parameters, and note that there are 2 parts: column names and values to assign to those columns. Do not build a SQL statement like that, from data supplied by a UI. It is insecure.|||

i reckon that used parameters can prevent SQL injection hack..

www.cyberxforum.com

|||

yuan:
i reckon that used parameters can prevent SQL injection hack..

Yes, that's correct.|||i agree with u but the crm1 are string variable not constant i cannot put them in single quoto.

INSERT query gets String data right truncation through ODBC but it works in SQL Server 7?

Hello Helpfull Helpers,
When I try to run an insert query from my Web site I get the following
error...
Error Diagnostic Information
ODBC Error Code = 22001 (String data right truncation)
[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data
would be truncated.
The error occurred while processing an element with a general
identifier of (CFQUERY), occupying document position (701:3) to
(701:73).
...but when I copy and paste the query into SQL Server Enterprise
Manager, the Insert succeeds.
Why does the query work in SQL Server Enterprise Manager but not from
my Web page? The Web page used to work fine.
My System Information:
======================
Windows 2000 Server (Operating System)
Cold Fusion 4.5 Server (Dynamic Web Server)
SQL Server 7.0 (Database Server)
The Query That Fails In The Web Page But Succeeds In Enterprise
Manager:
================================================== ======================
INSERT INTO dboJob(CUID, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12,
f13, f16, f17, f18, f19)
VALUES(
14036,
'09/17/2004 1:3:7 PM',
0,
'o',
'Testing new limit region functionality',
'Limit region should include cascading city, state or province,
country, and zip.',
'Fully functional.',
'',
'11',
'p',
'',
'',
'3',
'5',
0,
0 )
Table Information (field names have been changed to protect the
innocent):
================================================== ========================
CREATE TABLE [dbo].[dboJob] (
[JOID] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ,
[CUID] [decimal](18, 0) NOT NULL ,
[f1] [varchar] (2) NOT NULL ,
[f2] [datetime] NOT NULL ,
[f3] [datetime] NOT NULL ,
[f4] [money] NOT NULL ,
[f5] [varchar] (1) NOT NULL ,
[f6] [varchar] (100) NULL ,
[f7] [varchar] (3000) NOT NULL ,
[f8] [varchar] (1500) NOT NULL ,
[f9] [varchar] (3000) NULL ,
[f10] [numeric](18, 0) NOT NULL ,
[f11] [char] (1) NOT NULL ,
[f12] [varchar] (200) NOT NULL ,
[f13] [varchar] (64) NULL ,
[f14] [varchar] (1) NOT NULL ,
[f15] [varchar] (1) NOT NULL ,
[f16] [varchar] (1) NOT NULL ,
[f17] [varchar] (1) NOT NULL ,
[f18] [money] NOT NULL ,
[f19] [money] NOT NULL ,
[f20] [datetime] NOT NULL
) ON [PRIMARY]
GO
Thanks,
Nate
I suggest you try the INSERT statement through QA. After the INSERT, check whether the data has been
truncated. Also, read about SET ANSI_WARNING.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"nate" <nathandeneau@.cox.net> wrote in message
news:4630acfb.0409141227.64eb7823@.posting.google.c om...
> Hello Helpfull Helpers,
> When I try to run an insert query from my Web site I get the following
> error...
> Error Diagnostic Information
> ODBC Error Code = 22001 (String data right truncation)
> [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data
> would be truncated.
> The error occurred while processing an element with a general
> identifier of (CFQUERY), occupying document position (701:3) to
> (701:73).
> ...but when I copy and paste the query into SQL Server Enterprise
> Manager, the Insert succeeds.
> Why does the query work in SQL Server Enterprise Manager but not from
> my Web page? The Web page used to work fine.
> My System Information:
> ======================
> Windows 2000 Server (Operating System)
> Cold Fusion 4.5 Server (Dynamic Web Server)
> SQL Server 7.0 (Database Server)
> The Query That Fails In The Web Page But Succeeds In Enterprise
> Manager:
> ================================================== ======================
> INSERT INTO dboJob(CUID, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12,
> f13, f16, f17, f18, f19)
> VALUES(
> 14036,
> '09/17/2004 1:3:7 PM',
> 0,
> 'o',
> 'Testing new limit region functionality',
> 'Limit region should include cascading city, state or province,
> country, and zip.',
> 'Fully functional.',
> '',
> '11',
> 'p',
> '',
> '',
> '3',
> '5',
> 0,
> 0 )
> Table Information (field names have been changed to protect the
> innocent):
> ================================================== ========================
> CREATE TABLE [dbo].[dboJob] (
> [JOID] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ,
> [CUID] [decimal](18, 0) NOT NULL ,
> [f1] [varchar] (2) NOT NULL ,
> [f2] [datetime] NOT NULL ,
> [f3] [datetime] NOT NULL ,
> [f4] [money] NOT NULL ,
> [f5] [varchar] (1) NOT NULL ,
> [f6] [varchar] (100) NULL ,
> [f7] [varchar] (3000) NOT NULL ,
> [f8] [varchar] (1500) NOT NULL ,
> [f9] [varchar] (3000) NULL ,
> [f10] [numeric](18, 0) NOT NULL ,
> [f11] [char] (1) NOT NULL ,
> [f12] [varchar] (200) NOT NULL ,
> [f13] [varchar] (64) NULL ,
> [f14] [varchar] (1) NOT NULL ,
> [f15] [varchar] (1) NOT NULL ,
> [f16] [varchar] (1) NOT NULL ,
> [f17] [varchar] (1) NOT NULL ,
> [f18] [money] NOT NULL ,
> [f19] [money] NOT NULL ,
> [f20] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> Thanks,
> Nate

INSERT query gets String data right truncation through ODBC but it works in SQL Server 7?

Hello Helpfull Helpers,
When I try to run an insert query from my Web site I get the following
error...
Error Diagnostic Information
ODBC Error Code = 22001 (String data right truncation)
[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data
would be truncated.
The error occurred while processing an element with a general
identifier of (CFQUERY), occupying document position (701:3) to
(701:73).
...but when I copy and paste the query into SQL Server Enterprise
Manager, the Insert succeeds.
Why does the query work in SQL Server Enterprise Manager but not from
my Web page? The Web page used to work fine.
My System Information:
====================== Windows 2000 Server (Operating System)
Cold Fusion 4.5 Server (Dynamic Web Server)
SQL Server 7.0 (Database Server)
The Query That Fails In The Web Page But Succeeds In Enterprise
Manager:
======================================================================== INSERT INTO dboJob(CUID, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12,
f13, f16, f17, f18, f19)
VALUES(
14036,
'09/17/2004 1:3:7 PM',
0,
'o',
'Testing new limit region functionality',
'Limit region should include cascading city, state or province,
country, and zip.',
'Fully functional.',
'',
'11',
'p',
'',
'',
'3',
'5',
0,
0 )
Table Information (field names have been changed to protect the
innocent):
========================================================================== CREATE TABLE [dbo].[dboJob] (
[JOID] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ,
[CUID] [decimal](18, 0) NOT NULL ,
[f1] [varchar] (2) NOT NULL ,
[f2] [datetime] NOT NULL ,
[f3] [datetime] NOT NULL ,
[f4] [money] NOT NULL ,
[f5] [varchar] (1) NOT NULL ,
[f6] [varchar] (100) NULL ,
[f7] [varchar] (3000) NOT NULL ,
[f8] [varchar] (1500) NOT NULL ,
[f9] [varchar] (3000) NULL ,
[f10] [numeric](18, 0) NOT NULL ,
[f11] [char] (1) NOT NULL ,
[f12] [varchar] (200) NOT NULL ,
[f13] [varchar] (64) NULL ,
[f14] [varchar] (1) NOT NULL ,
[f15] [varchar] (1) NOT NULL ,
[f16] [varchar] (1) NOT NULL ,
[f17] [varchar] (1) NOT NULL ,
[f18] [money] NOT NULL ,
[f19] [money] NOT NULL ,
[f20] [datetime] NOT NULL
) ON [PRIMARY]
GO
Thanks,
NateI suggest you try the INSERT statement through QA. After the INSERT, check whether the data has been
truncated. Also, read about SET ANSI_WARNING.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"nate" <nathandeneau@.cox.net> wrote in message
news:4630acfb.0409141227.64eb7823@.posting.google.com...
> Hello Helpfull Helpers,
> When I try to run an insert query from my Web site I get the following
> error...
> Error Diagnostic Information
> ODBC Error Code = 22001 (String data right truncation)
> [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data
> would be truncated.
> The error occurred while processing an element with a general
> identifier of (CFQUERY), occupying document position (701:3) to
> (701:73).
> ...but when I copy and paste the query into SQL Server Enterprise
> Manager, the Insert succeeds.
> Why does the query work in SQL Server Enterprise Manager but not from
> my Web page? The Web page used to work fine.
> My System Information:
> ======================> Windows 2000 Server (Operating System)
> Cold Fusion 4.5 Server (Dynamic Web Server)
> SQL Server 7.0 (Database Server)
> The Query That Fails In The Web Page But Succeeds In Enterprise
> Manager:
> ========================================================================> INSERT INTO dboJob(CUID, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12,
> f13, f16, f17, f18, f19)
> VALUES(
> 14036,
> '09/17/2004 1:3:7 PM',
> 0,
> 'o',
> 'Testing new limit region functionality',
> 'Limit region should include cascading city, state or province,
> country, and zip.',
> 'Fully functional.',
> '',
> '11',
> 'p',
> '',
> '',
> '3',
> '5',
> 0,
> 0 )
> Table Information (field names have been changed to protect the
> innocent):
> ==========================================================================> CREATE TABLE [dbo].[dboJob] (
> [JOID] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ,
> [CUID] [decimal](18, 0) NOT NULL ,
> [f1] [varchar] (2) NOT NULL ,
> [f2] [datetime] NOT NULL ,
> [f3] [datetime] NOT NULL ,
> [f4] [money] NOT NULL ,
> [f5] [varchar] (1) NOT NULL ,
> [f6] [varchar] (100) NULL ,
> [f7] [varchar] (3000) NOT NULL ,
> [f8] [varchar] (1500) NOT NULL ,
> [f9] [varchar] (3000) NULL ,
> [f10] [numeric](18, 0) NOT NULL ,
> [f11] [char] (1) NOT NULL ,
> [f12] [varchar] (200) NOT NULL ,
> [f13] [varchar] (64) NULL ,
> [f14] [varchar] (1) NOT NULL ,
> [f15] [varchar] (1) NOT NULL ,
> [f16] [varchar] (1) NOT NULL ,
> [f17] [varchar] (1) NOT NULL ,
> [f18] [money] NOT NULL ,
> [f19] [money] NOT NULL ,
> [f20] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> Thanks,
> Nate

Friday, February 24, 2012

Insert produces error

Hi,

Using SQL Server 2000 with Windows 2000 Adv Server
&
Microsoft Access linked table (running stored procedure using ADO as
follows:

************************************************** ********
Private Sub cboAddrType_NotInList(NewData As String, Response As Integer)

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim msg As String

On Error GoTo Err_AddrType_NotInList
'Exit the procedure if the combo box was cleared
If Trim(NewData) = "" Then Exit Sub

'Confirm that the user wants to add AddrType
msg = "'" & Trim(NewData) & "' is not in the list." & vbCr & vbCr
msg = msg & "Do you want to add it?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
'If the user chose not to add AddrType, set the response
'argument to supress an error message and undo changes.
Response = acDataErrContinue
MsgBox "No record added.", vbOKOnly, "Action Cancelled"
Else
'If the user chose to add AddrType, open a recordset
'using the AddrType table

Set cmd = New ADODB.Command
Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB;Data Source=penland01;Initial
Catalog=groomery;Integrated Security=SSPI;"

cmd.ActiveConnection = cnn
cmd.CommandText = "spInsertAddrType"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("AddrType", adVarChar,
adParamInput, , Trim(NewData))
cmd.Execute Parameters:=prm
'Set Response argument to indicate that new data is being added
Response = acDataErrAdded

cnn.Close
Set cnn = Nothing
End If

Exit_AddrType_NotInList:
Exit Sub

Err_AddrType_NotInList:
MsgBox Err.Description
Response = acDataErrContinue
************************************************** ********

"NewData" is a text string - in this case "Test"

The stored procedure referenced in the code is:

************************************
CREATE PROCEDURE [spInsertAddrType]
(@.AddrType [nvarchar](50))

AS
INSERT INTO [groomery].[dbo].[tblAddrTypes]
([fldAddrType])

VALUES
(@.AddrType)
GO
*************************************

When I execute this code, I receive the following error

"Cannot update identity column 'fldAddrTypeID'."

fldAddrTypeID is configured as follows:

***************************
Data Type = int
Identity = Yes
Identity Seed = 1
Identity Increment = 1
***************************

The documentation I've found online concerning this error says that it is
produced when you try to supply a value for an identity field without SET
IDENTITY_INSERT on. Obviously I am NOT specifying a value, so I can't
figure why I'm getting this error.

Thanks for any help you can offer.

ToddHi,

Found the answer elsewhere but thought I'd share it here in case someone
else has this problem.

Access's upsizing wizard created a trigger on tblAddrTypes which (evidently)
was meant to emulate Access's autonumber functionality. Once I deleted that
trigger, everything worked fine.

Todd
"Todd" <infoNOSPAM@.MAPSONgroomery.biz> wrote in message
news:T1j5e.11405$FN4.303@.newssvr21.news.prodigy.co m...
> Hi,
> Using SQL Server 2000 with Windows 2000 Adv Server
> &
> Microsoft Access linked table (running stored procedure using ADO as
> follows:
> ************************************************** ********
> Private Sub cboAddrType_NotInList(NewData As String, Response As Integer)
> Dim cnn As ADODB.Connection
> Dim cmd As ADODB.Command
> Dim prm As ADODB.Parameter
> Dim msg As String
> On Error GoTo Err_AddrType_NotInList
> 'Exit the procedure if the combo box was cleared
> If Trim(NewData) = "" Then Exit Sub
> 'Confirm that the user wants to add AddrType
> msg = "'" & Trim(NewData) & "' is not in the list." & vbCr & vbCr
> msg = msg & "Do you want to add it?"
> If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
> 'If the user chose not to add AddrType, set the response
> 'argument to supress an error message and undo changes.
> Response = acDataErrContinue
> MsgBox "No record added.", vbOKOnly, "Action Cancelled"
> Else
> 'If the user chose to add AddrType, open a recordset
> 'using the AddrType table
>
> Set cmd = New ADODB.Command
> Set cnn = New ADODB.Connection
> cnn.Open "Provider=SQLOLEDB;Data Source=penland01;Initial
> Catalog=groomery;Integrated Security=SSPI;"
> cmd.ActiveConnection = cnn
> cmd.CommandText = "spInsertAddrType"
> cmd.CommandType = adCmdStoredProc
> Set prm = cmd.CreateParameter("AddrType", adVarChar,
> adParamInput, , Trim(NewData))
> cmd.Execute Parameters:=prm
> 'Set Response argument to indicate that new data is being added
> Response = acDataErrAdded
> cnn.Close
> Set cnn = Nothing
> End If
> Exit_AddrType_NotInList:
> Exit Sub
> Err_AddrType_NotInList:
> MsgBox Err.Description
> Response = acDataErrContinue
> ************************************************** ********
> "NewData" is a text string - in this case "Test"
> The stored procedure referenced in the code is:
> ************************************
> CREATE PROCEDURE [spInsertAddrType]
> (@.AddrType [nvarchar](50))
> AS
> INSERT INTO [groomery].[dbo].[tblAddrTypes]
> ([fldAddrType])
> VALUES
> (@.AddrType)
> GO
> *************************************
> When I execute this code, I receive the following error
> "Cannot update identity column 'fldAddrTypeID'."
> fldAddrTypeID is configured as follows:
> ***************************
> Data Type = int
> Identity = Yes
> Identity Seed = 1
> Identity Increment = 1
> ***************************
> The documentation I've found online concerning this error says that it is
> produced when you try to supply a value for an identity field without SET
> IDENTITY_INSERT on. Obviously I am NOT specifying a value, so I can't
> figure why I'm getting this error.
> Thanks for any help you can offer.
> Todd

Sunday, February 19, 2012

Insert Oracle Linked Server timestamp error...

I'm trying to insert into an Oracle table, setup via a linked server.
Everything works, but I receive an error during the insert, the
statement and error are below:
INSERT INTO ORALINKSRV..FIN.FILE_XFER_STATUS_DETAIL
(FILE_SEQ_ID,FILE_RETURN_CD,OFFICE_DEPT_
ID,FILE_NM,
FILE_PROC_DTTM FILE_SUFFIX_ID)
Select FILE_SEQ_ID,FILE_RETURN_CD,OFFICE_DEPT_I
D,FILE_NM,
FILE_PROC_DTTM,FILE_SUFFIX_ID
From Temp_FIN_Detail
ERROR:
Server: Msg 7354, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied invalid metadata for column
'FILE_PROC_DTTM'. The data type is not supported.
OLE DB error trace [Non-interface error: Column 'FILE_PROC_DTTM'
(ordinal 5) of object '"FIN"."FILE_XFER_STATUS_DETAIL"' reported an
unsupported value for DBTYPE of 13].
We've updated to the latest Oracle drivers on the server. We've
switched between the Oracle and MS OleDB Providers.
Any work arounds appreciated...
Thanks
bobAdditional information.
This is Oracle 9.203.
Oracle introduced a new data type timestamp, this data type does not
seem to be updateable or insert via SQL Server linked server.
I'd appreciate if anyone else could test and confirm this. We've
contacted MS Support and they weren't very helpful, indicating we were
the only ones who have reported a problem, and that is is most likely
an Oracle problem.
I'm just looking for some guidance so we can establish a standard to
not use the Oracle time stamp data type on tables that require data to
be transferred back and forth between SQL Server and Oracle.
Thanks
bob|||Try using Openquery and wrapping the value in to_timestamp.
You should also check your nls timestamp format on the
Oracle server.
-Sue
On 28 May 2004 12:34:05 -0700, bob@.lifeasbob.com (Bob
Horkay) wrote:

>Additional information.
>This is Oracle 9.203.
>Oracle introduced a new data type timestamp, this data type does not
>seem to be updateable or insert via SQL Server linked server.
>I'd appreciate if anyone else could test and confirm this. We've
>contacted MS Support and they weren't very helpful, indicating we were
>the only ones who have reported a problem, and that is is most likely
>an Oracle problem.
>I'm just looking for some guidance so we can establish a standard to
>not use the Oracle time stamp data type on tables that require data to
>be transferred back and forth between SQL Server and Oracle.
>Thanks
>bob|||Sue,
The query can be made to work for openquery with a to_char() statement,
unfortunately this does not work for Inserts.
I finally did receive a response from Microsoft support, the timestamp data
type is not supported, there is no plan to support it. They suggested using
the .net managed providers for Oracle, which of course can not be used for
linked servers.
They referenced several kb articles about data types supported and not
supported in Oracle, but basically the timestamp, clobs and blobs were out
( i expected the blobs but was surprised by the timestamp).
Anyway at least I know Microsofts position on it's ole db provider, they
support it, but have no plans to enhance it, watch thos data types
introduced from Oracle 8 on...
Bob
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:04snb01147cfcpn394k2ufrrv9jsr953u9@.
4ax.com...
> Try using Openquery and wrapping the value in to_timestamp.
> You should also check your nls timestamp format on the
> Oracle server.
> -Sue
> On 28 May 2004 12:34:05 -0700, bob@.lifeasbob.com (Bob
> Horkay) wrote:
>
>

Insert Oracle Linked Server timestamp error...

I'm trying to insert into an Oracle table, setup via a linked server.
Everything works, but I receive an error during the insert, the
statement and error are below:
INSERT INTO ORALINKSRV..FIN.FILE_XFER_STATUS_DETAIL
(FILE_SEQ_ID,FILE_RETURN_CD,OFFICE_DEPT_ID,FILE_NM ,
FILE_PROC_DTTM FILE_SUFFIX_ID)
Select FILE_SEQ_ID,FILE_RETURN_CD,OFFICE_DEPT_ID,FILE_NM,
FILE_PROC_DTTM,FILE_SUFFIX_ID
From Temp_FIN_Detail
ERROR:
Server: Msg 7354, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied invalid metadata for column
'FILE_PROC_DTTM'. The data type is not supported.
OLE DB error trace [Non-interface error: Column 'FILE_PROC_DTTM'
(ordinal 5) of object '"FIN"."FILE_XFER_STATUS_DETAIL"' reported an
unsupported value for DBTYPE of 13].
We've updated to the latest Oracle drivers on the server. We've
switched between the Oracle and MS OleDB Providers.
Any work arounds appreciated...
Thanks
bob
Additional information.
This is Oracle 9.203.
Oracle introduced a new data type timestamp, this data type does not
seem to be updateable or insert via SQL Server linked server.
I'd appreciate if anyone else could test and confirm this. We've
contacted MS Support and they weren't very helpful, indicating we were
the only ones who have reported a problem, and that is is most likely
an Oracle problem.
I'm just looking for some guidance so we can establish a standard to
not use the Oracle time stamp data type on tables that require data to
be transferred back and forth between SQL Server and Oracle.
Thanks
bob
|||Try using Openquery and wrapping the value in to_timestamp.
You should also check your nls timestamp format on the
Oracle server.
-Sue
On 28 May 2004 12:34:05 -0700, bob@.lifeasbob.com (Bob
Horkay) wrote:

>Additional information.
>This is Oracle 9.203.
>Oracle introduced a new data type timestamp, this data type does not
>seem to be updateable or insert via SQL Server linked server.
>I'd appreciate if anyone else could test and confirm this. We've
>contacted MS Support and they weren't very helpful, indicating we were
>the only ones who have reported a problem, and that is is most likely
>an Oracle problem.
>I'm just looking for some guidance so we can establish a standard to
>not use the Oracle time stamp data type on tables that require data to
>be transferred back and forth between SQL Server and Oracle.
>Thanks
>bob
|||Sue,
The query can be made to work for openquery with a to_char() statement,
unfortunately this does not work for Inserts.
I finally did receive a response from Microsoft support, the timestamp data
type is not supported, there is no plan to support it. They suggested using
the .net managed providers for Oracle, which of course can not be used for
linked servers.
They referenced several kb articles about data types supported and not
supported in Oracle, but basically the timestamp, clobs and blobs were out
( i expected the blobs but was surprised by the timestamp).
Anyway at least I know Microsofts position on it's ole db provider, they
support it, but have no plans to enhance it, watch thos data types
introduced from Oracle 8 on...
Bob
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:04snb01147cfcpn394k2ufrrv9jsr953u9@.4ax.com...
> Try using Openquery and wrapping the value in to_timestamp.
> You should also check your nls timestamp format on the
> Oracle server.
> -Sue
> On 28 May 2004 12:34:05 -0700, bob@.lifeasbob.com (Bob
> Horkay) wrote:
>

insert or update errors

Is there a way I can stop a form from inserting or updating a record when there is an error. I have an sql 2000 DB. I have noticed that if the db field can handle 50 characters and the form field has no limit on the number of characters no errors are displayed to the user if they try to use more than the 50 characters in the textbox. The record is not saved. None of the fields are saved. I do notice the autonumber generated is skipped by the db. That is, the next autonumber for a successful insert skips the logical next number. How do I capture this error, or any save error and return the user back to the form? Yes, I have limited the number of characters a user can type on the textbox now, but I would really like to catch save or insert errors. I use asp.net 2.0 and VB. I don't know C#.
thanks
Milton

You can add some validation within stored procedures but are just using raw SQL for insert and update? It is far better to use stored procedures for all insert, update and delete operations. (If possible all access should be via stored procedures)

If you include an output BIT parameter called say NAD (for No Abnormalities Detected) and only set to to 1 if the insert/update worked with no error. Use a command button to fire the insert/update and if there is an error, post a message to the user and stay on the same page.

|||Its always a good idea to do some basic validation at the front end (like data types, length etc) before passing all the data to the db and validate at the back end. Validation at the DB level should be for any constraint validation, PK-FK validation etc.|||

Is there an example here or elsewhere? I don't see how the form interacts with the sql server. Output BIT parameter? I have a linkbutton control on my form which has no code behind. I have the command name as "insert" for that link button in the properties of the linkbutton. I use VB not C#.
thanks
Milton

|||I will post some links for you tonight.|||Please look at my posting onhttp://forums.asp.net/thread/1687128.aspx. This will allow you to create a range of stored procedures plus wrapper code in either VB.NET or C#. The accompanying library is in C#. I will attempt to produce a VB.Net port this weekend.

insert operation time out

I got the following trobule shooing:
when I insert a huge table , I usually got ' time out ' error,
to this ,I find the question SQL through SQL profile as follows:
insert into hit(.....)
and this find insert operation is blocking
this table include identified column and it's primary key,
It has about1800 million records.
and now how to slove this time out question?
thanks for any ideas!you have 1800 milion records to insert? How are you doing it now? What is
the source? Please post DDL script for target.
MC
<lovexueer@.gmail.com> wrote in message
news:1144749849.058444.299630@.u72g2000cwu.googlegroups.com...
>I got the following trobule shooing:
> when I insert a huge table , I usually got ' time out ' error,
> to this ,I find the question SQL through SQL profile as follows:
> insert into hit(.....)
> and this find insert operation is blocking
> this table include identified column and it's primary key,
> It has about1800 million records.
>
> and now how to slove this time out question?
>
> thanks for any ideas!
>|||> and now how to slove this time out question?
When you have timeouts due to blocking, the first step is to identify and
correct the cause of the blocking.
One possible cause is long-running SELECT queries. Keeping in mind that
performance and concurrency go hand-in-hand, you might find that tuning
queries improves query performance and reduces blocking episodes.
Hope this helps.
Dan Guzman
SQL Server MVP
<lovexueer@.gmail.com> wrote in message
news:1144749849.058444.299630@.u72g2000cwu.googlegroups.com...
>I got the following trobule shooing:
> when I insert a huge table , I usually got ' time out ' error,
> to this ,I find the question SQL through SQL profile as follows:
> insert into hit(.....)
> and this find insert operation is blocking
> this table include identified column and it's primary key,
> It has about1800 million records.
>
> and now how to slove this time out question?
>
> thanks for any ideas!
>|||maybe I mistake it, I meant this table total records is 1800 milion.|||I am sure I have no long-running SELECT queries,
only a lot of running insert operation,
this table total records is 1800 milion, when I inset a record ,it need
much time unitl time out,
And I viewed all blocks and found only insert block as SQL statement
'insert into hit.......',
anyone have other solutions?
anyway ,best regards!|||<lovexueer@.gmail.com> wrote in message
news:1144765954.572739.26760@.z34g2000cwc.googlegroups.com...
> I am sure I have no long-running SELECT queries,
> only a lot of running insert operation,
> this table total records is 1800 milion, when I inset a record ,it need
> much time unitl time out,
> And I viewed all blocks and found only insert block as SQL statement
> 'insert into hit.......',
>
Can you post the full syntax.
It looks like it may be trying to insert 1800 million rows and commit it as
a single batch.
Look into using BULK INSERT or BCP with a smaller batch size.
> anyone have other solutions?
> anyway ,best regards!
>|||timeouts with inserts to large tables is very often index issues.
what are your indexes? do you have clustered indexes?
can you get rid of indexes?|||> And I viewed all blocks and found only insert block as SQL statement
> 'insert into hit.......',
How many rows are you inserting per transaction? I wouldn't expect an
atomic inserts to block each other for long periods of time. However, you
might have timeout problems if your I/O system is can't keep up with high
write demands. Make sure that data and log are on separate disks.
Hope this helps.
Dan Guzman
SQL Server MVP
<lovexueer@.gmail.com> wrote in message
news:1144765954.572739.26760@.z34g2000cwc.googlegroups.com...
>I am sure I have no long-running SELECT queries,
> only a lot of running insert operation,
> this table total records is 1800 milion, when I inset a record ,it need
> much time unitl time out,
> And I viewed all blocks and found only insert block as SQL statement
> 'insert into hit.......',
> anyone have other solutions?
> anyway ,best regards!
>|||thanks everyone!
this table is full of 1800 milion,
and it include Identified column as primay key ,it's clustered index
only when client program post a SQL statemt as follows:
insert into hit ........
running this SQL statemnt in client program is very frequently,
I pay attention to many blocks in this insert operation.
and sometimes it post error 'time out'
can anyone help me?|||per transaction is only one rows.
but per transaction is very frequently,
maybe I/O system really can't keep up with demands.|||per transaction insert only a row,but transactions are very frequently
,
maybe because of I/O system can't keep up with high wirte demands.|||To summarize the information you've supplied:
1) You have a large table (1.8 billion rows).
2) The identity column primary key is the clustered index
3) You insert one row per transaction
4) You have multiple clients inserting simultaneously
5) You observe frequent blocking and sometimes timeouts
Do you have any other indexes on the table other than the clustered index?
Are data and log files on the same disk/array?
Execute sp_lock via query analyzer to identify the blocking resources. Take
a look at the avg. disk queue length via Performance Monitor. This should
average no more that 2 times the number of physical disks in the array.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<lovexueer@.gmail.com> wrote in message
news:1144912220.346986.109010@.u72g2000cwu.googlegroups.com...
> per transaction insert only a row,but transactions are very frequently
> ,
> maybe because of I/O system can't keep up with high wirte demands.
>|||thanks you ,
your five point is very right,
this table only index is clustered index.
and data and log on the same disk array raid5.
I thinks IO system is so slowly than it comes out 'time out'.
by the way, I have two ways:
1) drop this clustered index ,maybe I avoid this question.
2) export part of rows and delete part of rows,reduce rows of this
table ,maybe I avoid this question.
do you think so ?
Dan Guzman =E5=86=99=E9=81=93=EF=BC=9A
> To summarize the information you've supplied:
> 1) You have a large table (1.8 billion rows).
> 2) The identity column primary key is the clustered index
> 3) You insert one row per transaction
> 4) You have multiple clients inserting simultaneously
> 5) You observe frequent blocking and sometimes timeouts
> Do you have any other indexes on the table other than the clustered index?
> Are data and log files on the same disk/array?
> Execute sp_lock via query analyzer to identify the blocking resources. Ta=ke
> a look at the avg. disk queue length via Performance Monitor. This should
> average no more that 2 times the number of physical disks in the array.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <lovexueer@.gmail.com> wrote in message
> news:1144912220.346986.109010@.u72g2000cwu.googlegroups.com...
> > per transaction insert only a row,but transactions are very frequently
> > ,
> > maybe because of I/O system can't keep up with high wirte demands.
> >|||> and data and log on the same disk array raid5.
This is very bad for performance when you do a lot of inserts/updates. Data
and log should be separated, especially when you do a lot of writes.
Furthermore, since log I/O are mostly writes, the write penalty incurred by
RAID-5 is bad for performance. I strongly suggest you move the log
somewhere else. Moving the log to a mirrored drive shared with the OS would
be much better.
Also, be sure you have pre-allocated the needed space. Do not rely on
autogrow to expand your files during application processing.
>1) drop this clustered index ,maybe I avoid this question.
This will eliminate I/O for the non-leaf index pages but that's a relatively
small percentage of the I/O needed by the insert. There are many more data
pages than index pages. I suggest you keep the primary key in place. The
clustered index key is sequentially assigned (identity) so the index inserts
are sequential and efficient.
> 2) export part of rows and delete part of rows,reduce rows of this
> table ,maybe I avoid this question.
I doubt this will help much either. The number of I/Os needed to write data
will be nearly constant with a sequential clustered index key regardless of
table size. Although you will have fewer non-leaf index pages with fewer
rows, this is fairly insignificant with your narrow index key.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<lovexueer@.gmail.com> wrote in message
news:1144979250.304108.153570@.j33g2000cwa.googlegroups.com...
thanks you ,
your five point is very right,
this table only index is clustered index.
and data and log on the same disk array raid5.
I thinks IO system is so slowly than it comes out 'time out'.
by the way, I have two ways:
1) drop this clustered index ,maybe I avoid this question.
2) export part of rows and delete part of rows,reduce rows of this
table ,maybe I avoid this question.
do you think so ?
Dan Guzman ':
> To summarize the information you've supplied:
> 1) You have a large table (1.8 billion rows).
> 2) The identity column primary key is the clustered index
> 3) You insert one row per transaction
> 4) You have multiple clients inserting simultaneously
> 5) You observe frequent blocking and sometimes timeouts
> Do you have any other indexes on the table other than the clustered index?
> Are data and log files on the same disk/array?
> Execute sp_lock via query analyzer to identify the blocking resources.
> Take
> a look at the avg. disk queue length via Performance Monitor. This should
> average no more that 2 times the number of physical disks in the array.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <lovexueer@.gmail.com> wrote in message
> news:1144912220.346986.109010@.u72g2000cwu.googlegroups.com...
> > per transaction insert only a row,but transactions are very frequently
> > ,
> > maybe because of I/O system can't keep up with high wirte demands.
> >|||thanks greate!
so Dan Guzman ,
what on hell shoulld I do?|||The first step is to move the log to a separate drive. Consider using a
bulk insert technique as Greg suggested earlier. Another option is to
increase the application command timeout.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<lovexueer@.gmail.com> wrote in message
news:1145266019.471977.168970@.t31g2000cwb.googlegroups.com...
> thanks greate!
> so Dan Guzman ,
> what on hell shoulld I do?
>|||thank you ,Dan Guzman
I will try it|||>I got the following trobule shooing:
>when I insert a huge table , I usually got ' time out ' error,
>to this ,I find the question SQL through SQL profile as follows:
>insert into hit(.....)
>and this find insert operation is blocking
>this table include identified column and it's primary key,
>It has about1800 million records.
>and now how to slove this time out question?
>thanks for any ideas!
Maybe too late, but..
I had yersterday a problem very similar, I solved running a
maintenance Plan, thta means...reorganize dta and index pages...
As was told before , your problems sound as a index pagination
problem....That will solve your problem
regards
ALberto

Friday, March 30, 2012

Inserted Rows count from SSIS not like table Rows Count

Monday, March 26, 2012

Insert Values Error

Wednesday, March 21, 2012

insert to two tables

Monday, March 19, 2012

Insert Syntax Error

Insert syntax error

insert stored procedure with error check and transaction function

Monday, March 12, 2012

INSERT Statement Error

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint

INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint

insert statement

Wednesday, March 7, 2012

Insert Query Problem

INSERT query gets String data right truncation through ODBC but it works in SQL Server 7?

INSERT query gets String data right truncation through ODBC but it works in SQL Server 7?

Friday, February 24, 2012

Insert produces error

Sunday, February 19, 2012

Insert Oracle Linked Server timestamp error...

Insert Oracle Linked Server timestamp error...

insert or update errors

insert operation time out

insert operation time out

insert or replace

Blog Archive

About Me