Showing posts with label inserts. Show all posts
Showing posts with label inserts. Show all posts

Wednesday, March 21, 2012

identity_insert?

I need to archive from one table to another but the new table, which is a duplicate of the old one, won't allow inserts into the ID column.
I am using:
set identity_insert soldVehicles on
INSERT INTO soldVehicles
SELECT *
FROM vehicles
Where sent2sold = 'yes'
but I get this error:
Error -2147217900


An explicit value for the identity column in table 'soldVehicles' can only be specified when a column list is used and IDENTITY_INSERT is ON.

set identity_insert soldVehicles on
INSERT INTO soldVehicles
SELECT *
FROM vehicles
Where sent2sold = 'yes'

As I have turned ID_insert ON it must be the column list?...
not sure what to do next.good assumption -- the error message actually says that|||Are you sure both table structures are identical?
An insert without an explicit column list is never a good idea, just as a SELECT * should not be used for this. SELECT * does not necessarily return the columns in the order expected by an unqualified INSERT INTO.|||Yes, that's correct. When the identity_insert option is set to True, you must specify the column list.|||Having just read your question again, I think you are taking the wrong approach. I don't see why you need to explicitly turn on identity_insert. If you need an identity column for the soldVehicles which will contain values that are distinctly different from the identity values in the original table, then there is no need to use the identity_insert option.

If on the other hand, you only want a single identification column in the soldVehicles table which will have values corresponding to those in the vehicles table, then there is no need to use neither an identity column nor hence the identity_insert option. You would just create a standard column to represent the vehicle ID numbers from the original table and insert the values as you would with any other insert operation.

In the following code sample, constraint and index definitions have been specified in shorthand notation for convenience. In a production system, I recommend proper naming of all objects.

create table vehicles
(
vehicleID identity(1,1) not null unique,
columnA int not null
)

create table soldVehicles
(
vehicleID int not null unique,
columnA int not null,
foreign key vehicleID references vehicles (columnA)
)

insert into soldVehicles (vehicleID, columnA)
select
v.VehicleID, v.ColumnA
from
vehicles v

Although this method is easy to implement and can indeed provide the functionality that you have described, are you not just after a column to mark whether or not a vehicle has been sold? Do you really need an extra table?|||thanks guys,
As Rudy well knows, I am a DB dunce and my old tables are shockingly simplistic and er...large.
To help out in the short-term I am trying to strip out and archive as much unused data as possible, rather than just marking it.
The new ID doesn't need to relate to its old value.
Looks like I am going to have to explicitly write out the list of column names.|||Are you from Cornwall?|||more or less, Devon now|||Oh yes. It is lovely down there.|||Looks like I am going to have to explicitly write out the list of column names.
Which is exactly what the error message told you to do.|||listen guys, when a person knows jack about databases, an error message like that is still pretty cryptic. I still didn't know what to do next, OK?
Robert chose to help, and now I know what to do.

these 'stating the obvious' replies don't help anyone.

In any case I have learnt not to trust error messages explicitly.|||these 'stating the obvious' replies don't help anyone.

I believe that comment was referring to you Ivon.|||thanks Robert.
I am trying to implement an insert similar to your example but I am getting a syntax error.
INSERT INTO soldVehicles (alt01, alt02, alt03, alt04, alt05) VALUES (SELECT alt01, alt02, alt03, alt04, alt05 FROM vehicles WHERE sent2sold = 'yes')
GO
Incidentally, what does the V. stand for in your example? do I need that to do this sort of batch insert?|||When using a SELECT to provide the values for an INSERT the VALUES keyword is not allowed. INSERT INTO soldVehicles (alt01, alt02, alt03, alt04, alt05)
SELECT alt01, alt02, alt03, alt04, alt05 FROM vehicles WHERE sent2sold = 'yes'
GO
See the manual (http://msdn2.microsoft.com/en-us/library/ms174335.aspx) for details.|||excellent! that's cracked it.
Thanks shammat|||Which is exactly what the error message told you to do.
Um...no. That has little to do with what the error message was saying. The main point is the IDENTITY INSERT requirement.|||The main point is the IDENTITY INSERT requirement.but the error message said two things (unless it was misquoted, and i can't be bothered to set up the test situation to reproduce the exact wording): identity_insert must be on, and you must use a column list

seeing as how identity_insert was on, it seems natural to conclude from the error message the need to use a column list, which was missing|||That's absolutely true Rudy, but some of us guys who prefer colouring-in pretty pictures, are still a bit in the dark even after an apparently obvious error message. it seems obvious now, even to me, but when I started this, I didn't know what a column list was! I had an incling but I was fed up guessing, so... I asked the experts. I speculated that it must be the column list and indicated I wasn't sure with a lovely little question mark.
Que your chance to show how clever you are.
or take a cheap swipe at the looser with no DB knowlege.

Anyway, all done now, I now know a tiny bit more and until I manage to find and employ a DB expert locally, I am fractionally better at doing it myself.|||another happy customer.

the plan to kill all traffic to this site is almost complete.|||Que your chance to show how clever you are.
or take a cheap swipe at the looser with no DB knowlege.

Why don't you have any DB knowledge? Read the help files, SQL Server's are excellent and I know they would have shown you the correct syntax for an INSERT statment because that's how I learned it. In fact, you should read it right now (http://msdn2.microsoft.com/en-us/library/ms189826.aspx). They break down what each part of any SQL statement means.

And you should dig around here to see recommendations for books to read. Don't pity yourself, (that's Mr. T's job) educate yourself. And it's lose, not loose.|||I believe that comment was referring to you Ivon.
Probably, yes. But if it was obvious, why post the question here?
It wasn't clear from the OP that darkmunk was a 'dunce' and 'looser with no DB knowledge' (his words, not mine). The OP made him look like someone who simply didn't read the error message. And yes, my post was totally unnecessary and unhelpful. Apologies to everyone who wants them.

(Also, I hate forums that don't quote quotes. That used to work here, didn't it?)|||ah, the world is a funny place isn't it?
Full of strange people who will never in a million years be able to visit a microsoft site and come away educated.

Thanks for the spelling lesson tho', don't know how I'd have got thru life without that.

Thanks to Robert and 'shammat' and anyone else with an ounce of empathy.
The job was sorted ages ago.|||Apologies to everyone who wants them.I'll take a dozen. I use 'em up pretty fast. Do you have any extra-large?|||I'll take a dozen. I use 'em up pretty fast. Do you have any extra-large?
Sure. D'you want fries with that order?

identity, indexes, datetime

Hi,
We have an largish audit table with many columns. There are
inserts/deletes only on this table, no updates. Its never been indexed
or had a pk. Reports are run against this table, usually on date ranges
(datetime col) with joins to other tables on userid etc.
Recently an identity column was added, to improve performance. For start
I'm wondering would this do anything at all for performance? Presumably
the table is sorted by this field in the absence of any other pk/index?
If so what benefit is this if the column is never used?
Surely the best strategy would be to put a clustered index on the
datetime field and not bother with the identity?
Is there much difference in time overhead adding a clustered,
non-clustered or identity field to existing tables with many rows (this
database is used by many client and the time taken for the schema update
is a concern).
Thanks
JB.jb
Well, choosing clustered index may not be so easy , you will have to
investigate many things. So, choosing "right" column/s will speed up the
query/is and improve perfomance ...
> Is there much difference in time overhead adding a clustered,
> non-clustered or identity field to existing tables with many rows (this
> database is used by many client and the time taken for the schema update
> is a concern).
Take a look at CREATE INDEX... WITH DROP EXISTSING option in the BOL
"jb" <b@.b.com> wrote in message
news:OoI2jPCfHHA.4636@.TK2MSFTNGP03.phx.gbl...
> Hi,
> We have an largish audit table with many columns. There are
> inserts/deletes only on this table, no updates. Its never been indexed or
> had a pk. Reports are run against this table, usually on date ranges
> (datetime col) with joins to other tables on userid etc.
> Recently an identity column was added, to improve performance. For start
> I'm wondering would this do anything at all for performance? Presumably
> the table is sorted by this field in the absence of any other pk/index? If
> so what benefit is this if the column is never used?
> Surely the best strategy would be to put a clustered index on the datetime
> field and not bother with the identity?
> Is there much difference in time overhead adding a clustered,
> non-clustered or identity field to existing tables with many rows (this
> database is used by many client and the time taken for the schema update
> is a concern).
> Thanks
> JB.|||Sory, should be DROP_EXISTING
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O62QTeCfHHA.3956@.TK2MSFTNGP03.phx.gbl...
> jb
> Well, choosing clustered index may not be so easy , you will have to
> investigate many things. So, choosing "right" column/s will speed up the
> query/is and improve perfomance ...
>> Is there much difference in time overhead adding a clustered,
>> non-clustered or identity field to existing tables with many rows (this
>> database is used by many client and the time taken for the schema update
>> is a concern).
>
> Take a look at CREATE INDEX... WITH DROP EXISTSING option in the BOL
>
>
>
> "jb" <b@.b.com> wrote in message
> news:OoI2jPCfHHA.4636@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> We have an largish audit table with many columns. There are
>> inserts/deletes only on this table, no updates. Its never been indexed or
>> had a pk. Reports are run against this table, usually on date ranges
>> (datetime col) with joins to other tables on userid etc.
>> Recently an identity column was added, to improve performance. For start
>> I'm wondering would this do anything at all for performance? Presumably
>> the table is sorted by this field in the absence of any other pk/index?
>> If so what benefit is this if the column is never used?
>> Surely the best strategy would be to put a clustered index on the
>> datetime field and not bother with the identity?
>> Is there much difference in time overhead adding a clustered,
>> non-clustered or identity field to existing tables with many rows (this
>> database is used by many client and the time taken for the schema update
>> is a concern).
>> Thanks
>> JB.
>|||> Recently an identity column was added, to improve performance. For start I'm wondering would this
> do anything at all for performance?
No. Well, it will increase row size a little so it will decrease performance slightly.
> Presumably the table is sorted by this field in the absence of any other pk/index?
Nope. Identity is a logical construct, and will have no bearing on physical structure.
> If so what benefit is this if the column is never used?
None, from a performance standpoint. From a logical standpoint, we get into the discussion of
identifying rows, natural vs. surrogate keys, but that is another topic.
> Surely the best strategy would be to put a clustered index on the datetime field and not bother
> with the identity?
That seems like the natural thing to do. However, another candidate is to cluster over the join
column(s). Do some testing what will give most gain. Whever you cluster on, you probably want to
have non-clustered index(es) on the other(s).
> Is there much difference in time overhead adding a clustered, non-clustered or identity field to
> existing tables with many rows (this database is used by many client and the time taken for the
> schema update is a concern).
Yes. Creating a cl ix will physicall re-sort the table, during you will have exclusive lock (unless
you are on 2005 and create using ONLINE option). Creating nc index will not re-sort data, only build
the index during which you have shared lock (unless using ONLINE). Adding an identity column will
proably mean SQL Server has to touch every row, end result can be similar to creating a cl ix
(sometimes, this can be deferred, but I very very much doubt adding an identity column can be
deferred, considering the identity value need to be populated).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jb" <b@.b.com> wrote in message news:OoI2jPCfHHA.4636@.TK2MSFTNGP03.phx.gbl...
> Hi,
> We have an largish audit table with many columns. There are inserts/deletes only on this table, no
> updates. Its never been indexed or had a pk. Reports are run against this table, usually on date
> ranges (datetime col) with joins to other tables on userid etc.
> Recently an identity column was added, to improve performance. For start I'm wondering would this
> do anything at all for performance? Presumably the table is sorted by this field in the absence of
> any other pk/index? If so what benefit is this if the column is never used?
> Surely the best strategy would be to put a clustered index on the datetime field and not bother
> with the identity?
> Is there much difference in time overhead adding a clustered, non-clustered or identity field to
> existing tables with many rows (this database is used by many client and the time taken for the
> schema update is a concern).
> Thanks
> JB.|||It is possible that adding an identity might make index rows on other
indexes smaller (if any other indexes exist). I know the OP stated there
were no other indexes on this particular table, but for argument's sake,
consider what happens when a heap table with NCIXs has an integer identity
column added. The ROWIDs in the NCIXs are reduced in size from 8bytes to
4bytes, reducing the storage size of those NCIXs. For NCIXs with only a
column or two, this can be a substantial reduction in overall size of the
index..
Is this important? Probably not terribly much, but I just thought it's worth
pointing out that although adding an identity column does widen the width of
table row storage, it can actually have the reverse effect on non-clustered
indexes. If performance critical queries rely on those NCIXs, it can
sometimes be a useful technique.. Where the identity is a BigInt however,
there's obviously no such gain..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%2340HOgCfHHA.3932@.TK2MSFTNGP02.phx.gbl...
>> Recently an identity column was added, to improve performance. For start
>> I'm wondering would this do anything at all for performance?
> No. Well, it will increase row size a little so it will decrease
> performance slightly.
>
>> Presumably the table is sorted by this field in the absence of any other
>> pk/index?
> Nope. Identity is a logical construct, and will have no bearing on
> physical structure.
>
>> If so what benefit is this if the column is never used?
> None, from a performance standpoint. From a logical standpoint, we get
> into the discussion of identifying rows, natural vs. surrogate keys, but
> that is another topic.
>
>> Surely the best strategy would be to put a clustered index on the
>> datetime field and not bother with the identity?
> That seems like the natural thing to do. However, another candidate is to
> cluster over the join column(s). Do some testing what will give most gain.
> Whever you cluster on, you probably want to have non-clustered index(es)
> on the other(s).
>
>> Is there much difference in time overhead adding a clustered,
>> non-clustered or identity field to existing tables with many rows (this
>> database is used by many client and the time taken for the schema update
>> is a concern).
> Yes. Creating a cl ix will physicall re-sort the table, during you will
> have exclusive lock (unless you are on 2005 and create using ONLINE
> option). Creating nc index will not re-sort data, only build the index
> during which you have shared lock (unless using ONLINE). Adding an
> identity column will proably mean SQL Server has to touch every row, end
> result can be similar to creating a cl ix (sometimes, this can be
> deferred, but I very very much doubt adding an identity column can be
> deferred, considering the identity value need to be populated).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "jb" <b@.b.com> wrote in message
> news:OoI2jPCfHHA.4636@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> We have an largish audit table with many columns. There are
>> inserts/deletes only on this table, no updates. Its never been indexed or
>> had a pk. Reports are run against this table, usually on date ranges
>> (datetime col) with joins to other tables on userid etc.
>> Recently an identity column was added, to improve performance. For start
>> I'm wondering would this do anything at all for performance? Presumably
>> the table is sorted by this field in the absence of any other pk/index?
>> If so what benefit is this if the column is never used?
>> Surely the best strategy would be to put a clustered index on the
>> datetime field and not bother with the identity?
>> Is there much difference in time overhead adding a clustered,
>> non-clustered or identity field to existing tables with many rows (this
>> database is used by many client and the time taken for the schema update
>> is a concern).
>> Thanks
>> JB.
>|||>> Yes. Creating a cl ix will physicall re-sort the table, during you will
>> have exclusive lock (unless you are on 2005 and create using ONLINE
>> option). Creating nc index will not re-sort data, only build the index
>> during which you have shared lock (unless using ONLINE). Adding an
>> identity column will proably mean SQL Server has to touch every row, end
>> result can be similar to creating a cl ix (sometimes, this can be
>> deferred, but I very very much doubt adding an identity column can be
>> deferred, considering the identity value need to be populated).
Tested this on 2.2 million records:
Clustered [on datetime field] - 5 mins
Non-clustered - 30 secs
Adding identity - ~1min

Monday, March 12, 2012

identity inserts

Hey All,

I was trying to use a typed dataset to create a very simple DAL. I found that the code generated for the INSERT statement includes an identity field the table has. That can obviously never work (unless identity_insert is set, which it is not). My question is whether it is possible to control this insert statement generation? Is there a property I am missing somewhere? My solution was to change the INSERT statement on the DataTableAdapter, but that seems awkward for me to have to do that..

Thanks,

Yuval

From SQL side you can turn on an option to enable identity inserts to a table:

SET IDENTITY_INSERT ONmyTable

Then you can insert values specifying columns and identity value:

INSERT INTOmyTable (id,name) VALUES(100, 'Iori')

Note: this is a session option, which means you have to turn on this option for every connection you want to insert identity values. For more information about this option, please refers to

http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_7zas.asp?frame=true

|||In my post, I actually said that this is not what I am trying to achieve. The problem is that the dataset generates an insert statement that includes the identity column and I have to edit it to not do that.|||Sorry for misunderstood you:)I've no idea of your issue, waiting for right answer...

Friday, March 9, 2012

identity in stored procedures

I have a stored procedure called sp_Insert_System_Header which inserts a record with an identity. I save this as id.
Then in the sp_Customer_Complaint_Entry stored procedure I want to use this field as a parameter for the case_id field.
When I run I get no errors But also no records get inserted into either table
Any Ideas.
CREATE PROCEDURE sp_Insert_System_Header
(
@.System_Type_IDint,
@.Priority_IDint,
@.PCAR_Manager_IDint,
@.Opportunity_IDint,
@.Status_IDint,
@.Initiated_By_IDint,
@.Location_IDint,
@.Other_Locationvarchar(100),
@.Initiated_OnDateTime,
@.Assigned_By_IDint,
@.Assigned_To_IDint,
@.Assigned_OnDateTime,
@.Resolved_OnDateTime,
@.Closed_BY_IDint,
@.Closed_OnDateTime,
@.Descriptionvarchar(2000),
@.Immediate_Actionsvarchar(2000),
@.Cause_Type_IDint,
@.Actual_Cause_Descriptionvarchar(2000),
@.Corrective_Descriptionvarchar(2000),
@.ID int OUTPUT
)
AS
INSERT INTO HEADER(
System_Type_ID,
Priority_ID,
PCAR_Manager_ID,
Opportunity_ID,
Status_ID,
Initiated_By_ID,
Location_ID,
OtherLocation,
Initiated_On,
Assigned_By_ID,
Assigned_To_ID,
Assigned_On,
Resolved_On,
Closed_BY_ID,
Closed_On,
Description,
Immediate_Actions,
Cause_Type_ID,
Actual_Cause_Description,
Corrective_Description
)
VALUES(
@.System_Type_ID,
@.Priority_ID,
@.PCAR_Manager_ID,
@.Opportunity_ID,
@.Status_ID,
@.Initiated_By_ID,
@.Location_ID,
@.Other_Location,
@.Initiated_On,
@.Assigned_By_ID,
@.Assigned_To_ID,
@.Assigned_On,
@.Resolved_On,
@.Closed_BY_ID,
@.Closed_On,
@.Description,
@.Immediate_Actions,
@.Cause_Type_ID,
@.Actual_Cause_Description,
@.Corrective_Description
)
Select @.ID = @.@.Identity
GO
CREATE PROCEDURE sp_Customer_Complaint_Entry
(
@.System_Type_IDint,
@.Priority_IDint,
@.Opportunity_IDint,
@.Status_IDint,
@.Initiated_By_IDint,
@.Location_IDint,
@.Other_Locationvarchar(100),
@.Initiated_OnDateTime,
@.Assigned_By_IDint,
@.Assigned_OnDateTime,
@.Resolved_OnDateTime,
@.Closed_BY_IDint,
@.Closed_OnDateTime,
@.Descriptionvarchar(2000),
@.Immediate_Actionsvarchar(2000),
@.Cause_Type_IDint,
@.Actual_Cause_Descriptionvarchar(2000),
@.Corrective_Descriptionvarchar(2000),
--THESE PARAMETERS ARE FOR CUSTOMER COMPLAINT
--SYSTEM ONLY
@.BusinessUnit_IDint,
@.RMANumbervarchar(50),
@.Product_Codevarchar(50),
@.Product_Namevarchar(100),
@.Customer_Numbervarchar(50),
@.Customer_Namevarchar(50),
@.Lot_Numbervarchar(50),
@.PO_Numbervarchar(50),
@.ID int OUTPUT
)
AS
Declare @.@.CASE_ID int,
@.@.PCAR_Manager_ID int
--FIND OUT WHO IS THE PCAR MANAGER FOR CUSTOMER COMPLAINT SYSTEM
EXEC
@.@.PCAR_Manager_ID = sp_getPCARID @.System_Type_ID, @.Initiated_By_ID
--ADD NEW RECORD TO SYSTEM HEADER
EXEC
@.@.CASE_ID = sp_Insert_System_Header
@.System_Type_ID,
@.Priority_ID,
@.@.PCAR_Manager_ID,
@.Opportunity_ID,
@.Status_ID,
@.Initiated_By_ID,
@.Location_ID,
@.Other_Location,
@.Initiated_On,
@.Assigned_By_ID,
@.@.PCAR_Manager_ID,
@.Assigned_On,
@.Resolved_On,
@.Closed_BY_ID,
@.Closed_On,
@.Description,
@.Immediate_Actions,
@.Cause_Type_ID,
@.Actual_Cause_Description,
@.Corrective_Description
--ADD NEW RECORD TO CUSTOMER COMPLAINT TABLE
INSERT INTO Customer_Complaint_System(
Case_ID,
BusinessUnit_ID,
RMANumber,
Product_Code,
Product_Name,
Customer_Number,
Customer_Name,
Lot_Number,
PO_Number)
VALUES(
@.@.CASE_ID,
@.BusinessUnit_ID,
@.RMANumber,
@.Product_Code,
@.Product_Name,
@.Customer_Number,
@.Customer_Name,
@.Lot_Number,
@.PO_Number
)
SELECT @.ID = @.@.CASE_ID
GO
I think the problem is incorrect usage of OUTPUT parameters.
The following is how you're doing it, which is actually appropriate syntax
for a RETURN value:
DECLARE @.myVariable INT
EXEC @.myVariable = my_Stored_Proc @.params, ...
For OUTPUT parameters, on the other hand, you do it this way:
DECLARE @.myVariable INT
EXEC my_Stored_Proc @.params, ..., @.myVariable OUTPUT
Does that make sense?
RETURN values, by the way, can only be of the INT datatype, and of course
you can only have one of them. OUTPUT params, on the other hand, can be of
any scaler datatype and you can have as many as you want. So they're quite
useful...
Also, two other comments: A) It's recommended that you not use sp_ to
prefix stored procedures as this is the prefix used for system stored
procedures and will cause a small performance penalty due to the server
looking for your stored procedure in the master database before looking
locally. B) You should probably not use @.@. to prefix variables, as that's
the prefix for system variables. Just a code readability issue.
"jat14" <anonymous@.discussions.microsoft.com> wrote in message
news:D140EA0D-E488-429E-9575-9942D9974D7E@.microsoft.com...
> I have a stored procedure called sp_Insert_System_Header which inserts a
record with an identity. I save this as id.
> Then in the sp_Customer_Complaint_Entry stored procedure I want to use
this field as a parameter for the case_id field.
> When I run I get no errors But also no records get inserted into either
table
> Any Ideas.
>
> CREATE PROCEDURE sp_Insert_System_Header
> (
> @.System_Type_ID int,
> @.Priority_ID int,
> @.PCAR_Manager_ID int,
> @.Opportunity_ID int,
> @.Status_ID int,
> @.Initiated_By_ID int,
> @.Location_ID int,
> @.Other_Location varchar(100),
> @.Initiated_On DateTime,
> @.Assigned_By_ID int,
> @.Assigned_To_ID int,
> @.Assigned_On DateTime,
> @.Resolved_On DateTime,
> @.Closed_BY_ID int,
> @.Closed_On DateTime,
> @.Description varchar(2000),
> @.Immediate_Actions varchar(2000),
> @.Cause_Type_ID int,
> @.Actual_Cause_Description varchar(2000),
> @.Corrective_Description varchar(2000),
> @.ID int OUTPUT
> )
> AS
> INSERT INTO HEADER(
> System_Type_ID,
> Priority_ID,
> PCAR_Manager_ID,
> Opportunity_ID,
> Status_ID,
> Initiated_By_ID,
> Location_ID,
> OtherLocation,
> Initiated_On,
> Assigned_By_ID,
> Assigned_To_ID,
> Assigned_On,
> Resolved_On,
> Closed_BY_ID,
> Closed_On,
> Description,
> Immediate_Actions,
> Cause_Type_ID,
> Actual_Cause_Description,
> Corrective_Description
> )
> VALUES(
> @.System_Type_ID,
> @.Priority_ID,
> @.PCAR_Manager_ID,
> @.Opportunity_ID,
> @.Status_ID,
> @.Initiated_By_ID,
> @.Location_ID,
> @.Other_Location,
> @.Initiated_On,
> @.Assigned_By_ID,
> @.Assigned_To_ID,
> @.Assigned_On,
> @.Resolved_On,
> @.Closed_BY_ID,
> @.Closed_On,
> @.Description,
> @.Immediate_Actions,
> @.Cause_Type_ID,
> @.Actual_Cause_Description,
> @.Corrective_Description
> )
> Select @.ID = @.@.Identity
> GO
>
> CREATE PROCEDURE sp_Customer_Complaint_Entry
> (
> @.System_Type_ID int,
> @.Priority_ID int,
> @.Opportunity_ID int,
> @.Status_ID int,
> @.Initiated_By_ID int,
> @.Location_ID int,
> @.Other_Location varchar(100),
> @.Initiated_On DateTime,
> @.Assigned_By_ID int,
> @.Assigned_On DateTime,
> @.Resolved_On DateTime,
> @.Closed_BY_ID int,
> @.Closed_On DateTime,
> @.Description varchar(2000),
> @.Immediate_Actions varchar(2000),
> @.Cause_Type_ID int,
> @.Actual_Cause_Description varchar(2000),
> @.Corrective_Description varchar(2000),
> --THESE PARAMETERS ARE FOR CUSTOMER COMPLAINT
> --SYSTEM ONLY
> @.BusinessUnit_ID int,
> @.RMANumber varchar(50),
> @.Product_Code varchar(50),
> @.Product_Name varchar(100),
> @.Customer_Number varchar(50),
> @.Customer_Name varchar(50),
> @.Lot_Number varchar(50),
> @.PO_Number varchar(50),
> @.ID int OUTPUT
> )
> AS
> Declare @.@.CASE_ID int,
> @.@.PCAR_Manager_ID int
> -- FIND OUT WHO IS THE PCAR MANAGER FOR CUSTOMER COMPLAINT SYSTEM
> EXEC
> @.@.PCAR_Manager_ID = sp_getPCARID @.System_Type_ID, @.Initiated_By_ID
> -- ADD NEW RECORD TO SYSTEM HEADER
> EXEC
> @.@.CASE_ID = sp_Insert_System_Header
> @.System_Type_ID,
> @.Priority_ID,
> @.@.PCAR_Manager_ID,
> @.Opportunity_ID,
> @.Status_ID,
> @.Initiated_By_ID,
> @.Location_ID,
> @.Other_Location ,
> @.Initiated_On,
> @.Assigned_By_ID,
> @.@.PCAR_Manager_ID,
> @.Assigned_On,
> @.Resolved_On,
> @.Closed_BY_ID,
> @.Closed_On,
> @.Description,
> @.Immediate_Actions,
> @.Cause_Type_ID,
> @.Actual_Cause_Description,
> @.Corrective_Description
> -- ADD NEW RECORD TO CUSTOMER COMPLAINT TABLE
> INSERT INTO Customer_Complaint_System(
> Case_ID,
> BusinessUnit_ID,
> RMANumber,
> Product_Code,
> Product_Name,
> Customer_Number,
> Customer_Name,
> Lot_Number,
> PO_Number)
> VALUES(
> @.@.CASE_ID,
> @.BusinessUnit_ID,
> @.RMANumber,
> @.Product_Code,
> @.Product_Name,
> @.Customer_Number,
> @.Customer_Name,
> @.Lot_Number,
> @.PO_Number
> )
> SELECT @.ID = @.@.CASE_ID
>
> GO
>

Identity corruption after 200-400 inserts???

Sql 2k, server has four dual-core processors. We're encountering an error
every 200-400 inserts that we are attempting to insert a duplicate row. The
table is simple:
BrokenIdentity
ID PK, identity
foo varchar
bar varchar
and so is the insert:
INSERT INTO BrokenIdentity (foo, bar) VALUES ('tar','fu')
but after about 200-400 inserts in a row we get the 'cannot insert duplicate
row' error. The rate this is happening is pretty shocking... the machine
isn't being shut down between the last good insert and the failing insert, so
that isn't the cause. Is this a common error with this type of modern
hardware? Are there any patches for this? Will setting the server to use
only one processor prevent this from happening? Does this also happen in
2k5? TIA.
Are shure that is the only type of insert statement you use?
Milan
"William Sullivan" <WilliamSullivan@.discussions.microsoft.com> wrote in
message news:DDB9BB67-48B4-4478-B103-B88BE937ADB4@.microsoft.com...
> Sql 2k, server has four dual-core processors. We're encountering an error
> every 200-400 inserts that we are attempting to insert a duplicate row.
> The
> table is simple:
> BrokenIdentity
> ID PK, identity
> foo varchar
> bar varchar
> and so is the insert:
> INSERT INTO BrokenIdentity (foo, bar) VALUES ('tar','fu')
> but after about 200-400 inserts in a row we get the 'cannot insert
> duplicate
> row' error. The rate this is happening is pretty shocking... the machine
> isn't being shut down between the last good insert and the failing insert,
> so
> that isn't the cause. Is this a common error with this type of modern
> hardware? Are there any patches for this? Will setting the server to use
> only one processor prevent this from happening? Does this also happen in
> 2k5? TIA.

Identity corruption after 200-400 inserts???

Sql 2k, server has four dual-core processors. We're encountering an error
every 200-400 inserts that we are attempting to insert a duplicate row. The
table is simple:
BrokenIdentity
ID PK, identity
foo varchar
bar varchar
and so is the insert:
INSERT INTO BrokenIdentity (foo, bar) VALUES ('tar','fu')
but after about 200-400 inserts in a row we get the 'cannot insert duplicate
row' error. The rate this is happening is pretty shocking... the machine
isn't being shut down between the last good insert and the failing insert, s
o
that isn't the cause. Is this a common error with this type of modern
hardware? Are there any patches for this? Will setting the server to use
only one processor prevent this from happening? Does this also happen in
2k5? TIA.Are shure that is the only type of insert statement you use?
Milan
"William Sullivan" <WilliamSullivan@.discussions.microsoft.com> wrote in
message news:DDB9BB67-48B4-4478-B103-B88BE937ADB4@.microsoft.com...
> Sql 2k, server has four dual-core processors. We're encountering an error
> every 200-400 inserts that we are attempting to insert a duplicate row.
> The
> table is simple:
> BrokenIdentity
> ID PK, identity
> foo varchar
> bar varchar
> and so is the insert:
> INSERT INTO BrokenIdentity (foo, bar) VALUES ('tar','fu')
> but after about 200-400 inserts in a row we get the 'cannot insert
> duplicate
> row' error. The rate this is happening is pretty shocking... the machine
> isn't being shut down between the last good insert and the failing insert,
> so
> that isn't the cause. Is this a common error with this type of modern
> hardware? Are there any patches for this? Will setting the server to use
> only one processor prevent this from happening? Does this also happen in
> 2k5? TIA.

Identity columns no longer identities

Hi,
I have an entire db that for no reason I can see has had every identity
column's identity property set to 'no'. I have lots of inserts into the
affected tables which obviously produces errors inserting null into a column
that can't be null.
I could let 1 or 2 tables go as a wayward click in ent manager but *every*
identity in the db has been turned off. Is there a db wide setting that can
do this? Has anyone ever seen anthying like this before?
The scarey thing is this box goes into production next week and now Im
living in fear of the same thing happening again.
Any help appreciated.
Al
"badlydressedboy" <badlydressedboy@.discussions.microsoft.com> wrote in
message news:D9C6489B-70E2-42A7-B643-DDA55BD74077@.microsoft.com...
> Hi,
> I have an entire db that for no reason I can see has had every identity
> column's identity property set to 'no'. I have lots of inserts into the
> affected tables which obviously produces errors inserting null into a
> column
> that can't be null.
> I could let 1 or 2 tables go as a wayward click in ent manager but *every*
> identity in the db has been turned off. Is there a db wide setting that
> can
> do this? Has anyone ever seen anthying like this before?
> The scarey thing is this box goes into production next week and now Im
> living in fear of the same thing happening again.
> Any help appreciated.
> Al
Umm, unless someone specifically scripted it, the only real way I can see
that happening is if this database was created as a replication subscriber.
In cases like that, default behavior is for the replicated tables to be
created w/o IDENTITY columns.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
|||On Mar 23, 12:06 pm, badlydressedboy
<badlydressed...@.discussions.microsoft.com> wrote:
> Hi,
> I have an entire db that for no reason I can see has had every identity
> column's identity property set to 'no'. I have lots of inserts into the
> affected tables which obviously produces errors inserting null into a column
> that can't be null.
> I could let 1 or 2 tables go as a wayward click in ent manager but *every*
> identity in the db has been turned off. Is there a db wide setting that can
> do this? Has anyone ever seen anthying like this before?
> The scarey thing is this box goes into production next week and now Im
> living in fear of the same thing happening again.
> Any help appreciated.
> Al
Was this DB "copied" from another DB by doing "SELECT * INTO" ?
|||"Tracy McKibben" wrote:

> On Mar 23, 12:06 pm, badlydressedboy
> <badlydressed...@.discussions.microsoft.com> wrote:
> Was this DB "copied" from another DB by doing "SELECT * INTO" ?
>
The crazy thing is that db creation was fine - its been up and running using
all the affected tables for a couple of weeks. We ran a bunch of data in (its
an ETL/OLAP db) in the morning and then went to do the same in the afternoon
but the identities had left the building.
There was an event that deleted them as they were definately there after db
creation otherwise the ETL wouldn't have worked at all. Its looking like that
event was somebody accidentally running a script (quite a bizarre script that
I am sure they would remember writing/running) but nobody is owning up.
Pretty weird stuff.
|||On Mar 23, 12:45 pm, badlydressedboy
<badlydressed...@.discussions.microsoft.com> wrote:
> "Tracy McKibben" wrote:
>
>
> The crazy thing is that db creation was fine - its been up and running using
> all the affected tables for a couple of weeks. We ran a bunch of data in (its
> an ETL/OLAP db) in the morning and then went to do the same in the afternoon
> but the identities had left the building.
> There was an event that deleted them as they were definately there after db
> creation otherwise the ETL wouldn't have worked at all. Its looking like that
> event was somebody accidentally running a script (quite a bizarre script that
> I am sure they would remember writing/running) but nobody is owning up.
> Pretty weird stuff.
SELECT INTO has nothing to do with DB creation, but it could have
everything to do with the creation of the TABLES. Does this ETL
process use SELECT INTO to build/populate the tables? Consider the
following example:
CREATE TABLE MyTable
(
IDColumn INT IDENTITY(1,1),
SomeVal1 CHAR(10),
SomeVal2 CHAR(10)
)
INSERT INTO MyTable (SomeVal1, SomeVal2) VALUES ('A', 'B')
SELECT * INTO MyNewTable FROM MyTable
SELECT * FROM MyNewTable
The structure of MyNewTable will appear to be the same as MyTable, but
it's NOT. The identity property of the IDColumn column doesn't get
copied.
If your ETL process involves the dropping of tables and then re-
creating them using SELECT INTO, that's your problem.

Wednesday, March 7, 2012

identity column problem skipped a range of values

Hi,
This is on SQL Server 2000 database. Inserts to a table with an identity
column on it have suddenly jumped in value. It was going along, 301,
302, ... then all of sudden identity column values jumped to 64221,
64222...
The app inserting data is an ASP.net app (1.1x). No changes were made to
the app. Does anyone have an idea what must be causing this. BTW, this
happened with only one person access the database.
jj"jj" <jj@.no_spam.net> wrote in message news:e7ua3f$5t9$1@.lists.fhcrc.org...
> Hi,
> This is on SQL Server 2000 database. Inserts to a table with an identity
> column on it have suddenly jumped in value. It was going along, 301,
> 302, ... then all of sudden identity column values jumped to 64221,
> 64222...
If you said it was skipping from say 302 to 306 or something reasonable, I'd
say there's some sort of rollback on transactions occurring.
(keep in mind that the IDENTITY column gets incremented regardless of the
success of a transaction.)
If it's happening with only one person, I'd set up profiler to see what
they're doing with the connection.
I mean I suppose it's possible they're starting and then rolling back 64000
transactions, but that sounds weird.
> The app inserting data is an ASP.net app (1.1x). No changes were made to
> the app. Does anyone have an idea what must be causing this. BTW, this
> happened with only one person access the database.
> jj

identity column problem skipped a range of values

Hi,
This is on SQL Server 2000 database. Inserts to a table with an identity
column on it have suddenly jumped in value. It was going along, 301,
302, ... then all of sudden identity column values jumped to 64221,
64222...
The app inserting data is an ASP.net app (1.1x). No changes were made to
the app. Does anyone have an idea what must be causing this. BTW, this
happened with only one person access the database.
jj"jj" <jj@.no_spam.net> wrote in message news:e7ua3f$5t9$1@.lists.fhcrc.org...
> Hi,
> This is on SQL Server 2000 database. Inserts to a table with an identity
> column on it have suddenly jumped in value. It was going along, 301,
> 302, ... then all of sudden identity column values jumped to 64221,
> 64222...
If you said it was skipping from say 302 to 306 or something reasonable, I'd
say there's some sort of rollback on transactions occurring.
(keep in mind that the IDENTITY column gets incremented regardless of the
success of a transaction.)
If it's happening with only one person, I'd set up profiler to see what
they're doing with the connection.
I mean I suppose it's possible they're starting and then rolling back 64000
transactions, but that sounds weird.

> The app inserting data is an ASP.net app (1.1x). No changes were made to
> the app. Does anyone have an idea what must be causing this. BTW, this
> happened with only one person access the database.
> jj|||"jj" <jj@.no_spam.net> wrote in message news:e7ua3f$5t9$1@.lists.fhcrc.org...
> Hi,
> This is on SQL Server 2000 database. Inserts to a table with an identity
> column on it have suddenly jumped in value. It was going along, 301,
> 302, ... then all of sudden identity column values jumped to 64221,
> 64222...
If you said it was skipping from say 302 to 306 or something reasonable, I'd
say there's some sort of rollback on transactions occurring.
(keep in mind that the IDENTITY column gets incremented regardless of the
success of a transaction.)
If it's happening with only one person, I'd set up profiler to see what
they're doing with the connection.
I mean I suppose it's possible they're starting and then rolling back 64000
transactions, but that sounds weird.

> The app inserting data is an ASP.net app (1.1x). No changes were made to
> the app. Does anyone have an idea what must be causing this. BTW, this
> happened with only one person access the database.
> jj