If I got this error, maybe some one else did too. Please suggest something.

Error: 0xC02020A1 at Data Flow Task, Source - mysourcefile [1]: Data conversion failed. The data conversion for column "myBadColumn" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Data Flow Task, Source - mysourcefile [1]: The "output column "myBadColumn" (157)" failed because truncation occurred, and the truncation row disposition on "output column "myBadColumn" (157)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Two questions:
1. How to tell SSIS not to stop for one bad row. Getting the good rows is more important than one bad row.

2. What code page do I need so this will load?1. You need to configure the error output of the source component to redirec t the errors upon truncation.sql

If exists..

Hi,
Is there any way to check whether a column is there in the table, if it is there i need to drop it through script.

i'm looking for the script, something like this..

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Tbl_Product_Tbl_Products]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Tbl_Product] DROP CONSTRAINT FK_Tbl_Product_Tbl_Products
GO

In the same way i need to check for a column and drop it through script.
Any help would be greatly appreciated.
Thanks in advance.Look at the INFORMATION_SCHEMA.Columns View...

But I would advise against any auto mucking|||Hi Brett..
Here is what i did..
if exists (select * from information_schema.columns where table_name = 'tablename' and column_name = 'columnname')
alter table tablename drop column columnname
go
alter table tablename add columnname varchar(255) null
go

It worked..
Thanks.sql

If Exists Column ?

Hello

How do you check if a column exist ?

for a table :

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[myTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[myTable]
GO

but I dont find it for a column

Thank youif exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tablename'
and COLUMN_NAME='columname' )
drop table [dbo].[myTable]|||I want to drop the column not the table ?

thank you for helping|||Here we go.......
--alter table tablename drop column columnname

if exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tablename'
and COLUMN_NAME='columname' )
alter table tablename drop column columnname
go|||This is one way....

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int, Col2 varchar(25))
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 1, 'a' UNION ALL SELECT 2, 'b' UNION ALL SELECT 3, 'c'
GO

SELECT * FROM myTable99

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'myTable99' AND COLUMN_NAME = 'Col2')
ALTER TABLE myTable99 DROP COLUMN Col2

SELECT * FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

If Else

All,

looking for help with outputting different text value into a cell.

e.g. a column of a table will only ever have two values, Y & N.

I am looking to populate a CELL within SSRS with Yes or No using a seperate Dataset.

SELECT 'Yes' AS [LookupValue], 'Y' AS LookupCode UNION
SELECT 'No', 'N'

This gives me my two column to lookup which is what I want. However, i need this to populate a cell with Yes instead of Y.

I have tried using it as a parameter but to no avail.

Anyone got any ideas?

Duncan,

Have you tried using the Switch statement instead?

=Switch(Fields!LookUpCode.Value = "Y", "Yes", Fields!LookUpCode.Value = "N", "No")

Hope this helps.

Jarret

IF construct in DTS

Hi All,

I am new to DTS. I have to pick data from a excel sheet and put it in SQL Server table. If the column value is N/A in the excel then I should load NULL in the target table. Else the value. This is being the case I wrote this construct and it seems to load only NULLs into the target and the values are not getting loaded.

If Trim(UCase(DTSSource("ABC transport"))) = "N/A" Then DTSDestination("ABC_transport") = NULL Else DTSDestination("ABC_transport") = DTSSource("ABC transport")
Main = DTSTransformStat_OK

Thanks in advance.

Regards,
Sathishtry the code below, use the line breaks I have...

If Trim(UCase(DTSSource("ABC transport"))) = "N/A" Then
DTSDestination("ABC_transport") = NULL
Else
DTSDestination("ABC_transport") = DTSSource("ABC transport")
End If
Main = DTSTransformStat_OK|||Hi,

Thanks for the reply.

I tried it, still it loads only the NULL and not the values.

Regards,
Sathish|||Well the syntax is correct.

I'd check the source data. Is excel displaying N/A when there is something else in the cell? I know it will sometimes show rounded figures when the cell hold the full data.

Can you post a sample of the excel spreadsheet?|||Originally posted by rokslide
Well the syntax is correct.

I'd check the source data. Is excel displaying N/A when there is something else in the cell? I know it will sometimes show rounded figures when the cell hold the full data.

Can you post a sample of the excel spreadsheet?|||Okie,... well there are 1000 non N/A records, 5159 N/A records and 1 null,... how many non N/A records are you getting loaded in your db table?? 6159 or 5159?|||Hi,

That was pretty fast. I am getting loaded with 6159 NULLs.

Regards,
Sathish|||using the dts wizard to import the spreadsheet to a temp ABC transport table and using the following code...

'************************************************* *********************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'************************************************* ***********************

Function Main()
DTSDestination("Renewal Month") = DTSSource("Renewal Month")
If Trim(UCase(DTSSource("ABC transport"))) = "N/A" Then
DTSDestination("ABC transport") = NULL
Else
DTSDestination("ABC transport") = DTSSource("ABC transport")
End If
Main = DTSTransformStat_OK
End Function

I get it with values where they are meant to be...

Wanna try this as a test to try and locate your problem?|||Not much luck. Still its not working for me. Let me try for some time to see whether I can get it.

Thank you very much for the help.

Regards,
Sathish|||No worries. Let me know if you need more help, sorry I couldn't solve your problem.|||The problem is with the excel driver - the excel driver attempts to guess at the datatype based on the first X number of entries. Save the excel file as a csv file and do the same dts script using the csv file and it will work.|||By if he is using VBScript to do the transformation then it would be using it's typing (eg. everything is a string) to do the comparison...

Also, wouldn't that also mean that my test transformation wouldn't work where it did work?

If conditional problem in T-Sql

I encounter a T-Sql problem related to if conditional processing:

The following script execute an insert statement depending on whether column 'ReportTitle' exists in table ReportPreferences. However it gets executed even when ReportTitle column is not present.

Could anyone offer some advice?

IF(Coalesce(Col_length('ReportPreferences','ReportTitle'),0) > 0)
Begin
INSERT INTO dbo.Defaults
SELECT FinancialPlannerID,ReportTitle
FROM dbo.ReportPreferences
end
GO

Were you trying to do this for entire column or for each row in the column? Col_length will always return the size as defined in the DDL. So your IF statement will always return true.

|||

Well the code that you have written is fine it should work perfectly.

Can you provide the code that you are using for droping the column of the table ?

|||

Alternatively if you want to check for existence of a column you could query the syscoumns table:

IF
EXISTS(Select*fromsyscolumnswhere [Name]='ReportPreferences'and Id=Object_Id('ReportTitle'))
Begin
 --Do your insert
End

|||

Hi,

Thanks for your alternative way of querying system table for column existence.

However the problem still persists: even though the EXISTS clause is evaluated to be false, the query engine is still trying to insert statement, resulting in an error:

Server: Msg 207, Level 16, State 3, Line 6
Invalid column name 'ReportTitle'.

This is a very strange phenomena.

- Yubo

|||Can you repost your new query and the error message pls?|||

From your earlier which I am copy pasting here:

**************************************************

IF(Coalesce(Col_length('ReportPreferences','ReportTitle'),0) > 0)
Begin
INSERT INTO dbo.Defaults
SELECT FinancialPlannerID,ReportTitle
FROM dbo.ReportPreferences
end
GO

***************************************************

It shows that ReportPreferences is the name of your table while the column name isReportTitle

While if you have just copy pasted the querry from ndinakar which is :

***************************************************

IF

EXISTS (Select * from syscolumns where [Name] = 'ReportPreferences' and Id = Object_Id('ReportTitle'))

Begin

-- Do your insert

End

***************************************************

The sequence of the name of the table is wrong.

Please try this instead and I am sure your problem would be solved :) .

If EXISTS (Select * from syscolumns where [Name] = 'ReportTitle' and id = Object_Id('ReportPreferences'))
Begin
Print ('yes')
End
Else
Begin
Print ('no')
End

And if this post does answer your question please dont hesitate to mark it as Answer.

Regards,

sql

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...I need to get the last (or highest number in Identity column)...

Ok,
I just need to know how to get the last record inserted by the highest
IDENTITY number. Even if the computer was rebooted and it was two
weeks ago. (Does not have to do with the session).
Any help is appreciated.
Thanks,
TrintSELECT IDENT_CURRENT('table_name') ;

--
David Portas
SQL Server MVP
--

"trint" <trinity.smith@.gmail.com> wrote in message
news:1127164340.720014.70160@.g14g2000cwa.googlegro ups.com...
> Ok,
> I just need to know how to get the last record inserted by the highest
> IDENTITY number. Even if the computer was rebooted and it was two
> weeks ago. (Does not have to do with the session).
> Any help is appreciated.
> Thanks,
> Trint|||Thank you for your quick response, David.
Trint

..Net programmer
trinity.smith@.gmail.com

*** Sent via Developersdex http://www.developersdex.com ***

identity, indexes, datetime

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 ...
>
> 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...
>

Identity whithout identity column

Hi!
Please help!
Is there a function in SQL Server that returns "ROWID" from any table
(without identity column) or how to get that number?
Regards, MarkoThere is no internal rowid in SQL Server (such violated the relational model). If you say what you
would need it for, we can suggest alternatives.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Marko Kopaè" <marko.kopac@.mais.si> wrote in message
news:%23Gp%23PYngDHA.2484@.TK2MSFTNGP09.phx.gbl...
> Hi!
> Please help!
> Is there a function in SQL Server that returns "ROWID" from any table
> (without identity column) or how to get that number?
> Regards, Marko
>

IDENTITY value copy in INSERT statement

Hello,
I would like to insert the value of an identity column into an other field
during the same insert statement and not by using a trigger.
Sample Table
CREATE TABLE Test (INT DocumentID IDENTITY(1,1), DocumentParentID)
Sample statements NOT working but to indicate what I would like to do
INSERT INTO Test(DocumentParentID) VALUES (Test.DocumentID)
INSERT INTO Test(DocumentParentID) VALUES (SCOPE_IDENTITY())
Is this possible and if yes, could you please inform me how?
Thanks in advance,
RemcoWithout using a trigger (error handling omitted):
CREATE TABLE Test (INT DocumentID IDENTITY(1,1), DocumentParentID)
DECLARE @.ID int
BEGIN TRAN -- these next two data operations should be atomic
INSERT INTO Test(DocumentParentID) VALUES (NULL)
SET @.ID = @.@.IDENTITY
UPDATE Test SET DocumentParentID = @.ID WHERE DocumentID=@.ID
COMMIT
INSERT INTO Test(DocumentParentID) VALUES (@.ID)
I dont know of a way to acheive this inline using identity.
Mr Tea
"Remco" <rembo_r@.hotmail.com> wrote in message
news:OEmKrq0FFHA.2156@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I would like to insert the value of an identity column into an other field
> during the same insert statement and not by using a trigger.
> Sample Table
> CREATE TABLE Test (INT DocumentID IDENTITY(1,1), DocumentParentID)
>
> Sample statements NOT working but to indicate what I would like to do
> INSERT INTO Test(DocumentParentID) VALUES (Test.DocumentID)
> INSERT INTO Test(DocumentParentID) VALUES (SCOPE_IDENTITY())
>
> Is this possible and if yes, could you please inform me how?
> Thanks in advance,
> Remco
>|||I prefer to use SCOPE_IDENTITY( ) unless you are using SQL Server 7 then use
@.@.identity
"Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
news:n10Sd.124$u56.22@.newsfe5-win.ntli.net...
> Without using a trigger (error handling omitted):
> CREATE TABLE Test (INT DocumentID IDENTITY(1,1), DocumentParentID)
> DECLARE @.ID int
> BEGIN TRAN -- these next two data operations should be atomic
> INSERT INTO Test(DocumentParentID) VALUES (NULL)
> SET @.ID = @.@.IDENTITY
> UPDATE Test SET DocumentParentID = @.ID WHERE DocumentID=@.ID
> COMMIT
> INSERT INTO Test(DocumentParentID) VALUES (@.ID)
> I dont know of a way to acheive this inline using identity.
> Mr Tea
> "Remco" <rembo_r@.hotmail.com> wrote in message
> news:OEmKrq0FFHA.2156@.TK2MSFTNGP09.phx.gbl...
field
>|||thanks for the tip :)
Mr Tea
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u6mVLN1FFHA.3972@.TK2MSFTNGP15.phx.gbl...
>I prefer to use SCOPE_IDENTITY( ) unless you are using SQL Server 7 then
>use
> @.@.identity
>
> "Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
> news:n10Sd.124$u56.22@.newsfe5-win.ntli.net...
> field
>|||Why does the document reference itself as its own parent? Typically an
adjacency list hierarchy in a table looks like this:
CREATE TABLE Documents (documentid INTEGER NOT NULL PRIMARY KEY,
parent_documentid INTEGER NULL REFERENCES Documents (documentid))
The root nodes of the tree then have NULL as the parent_documentid. If
you use IDENTITY as the key then will need either a trigger or an
INSERT followed by an UPDATE to populate a self-referencing parent id.
David Portas
SQL Server MVP
--|||>> I would like to insert the value of an identity column [sic] into
an other field [sic] during the same insert statement and not by using
a trigger. <<
IDENTITY is a table property that exists only in the machine, not in
the data model. Columns and fields are totally different concepts.
And it looks like you are trying to use an adjacency list model for a
hierarchy. Try a nested sets model and all of your problems go away and
you avoid proprietary code.
CREATE TABLE Documents
(document_id INTEGER NOT NULL,
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),
CONSTRAINT order_okay CHECK (lft < rgt) );
I have a whole book on trees and hierarchies in SQL.|||>> I would like to insert the value of an identity column [sic] into
an other field [sic] during the same insert statement and not by using
a trigger. <<
IDENTITY is a table property that exists only in the machine, not in
the data model. Columns and fields are totally different concepts.
And it looks like you are trying to use an adjacency list model for a
hierarchy. Try a nested sets model and all of your problems go away and
you avoid proprietary code.
CREATE TABLE Documents
(document_id INTEGER NOT NULL,
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),
CONSTRAINT order_okay CHECK (lft < rgt) );
I have a whole book on trees and hierarchies in SQL.|||Try using Ident)Seeed()
as in
INSERT INTO Test(DocumentParentID) VALUES (Ident_Seed('Test'))
"Remco" wrote:

> Hello,
> I would like to insert the value of an identity column into an other field
> during the same insert statement and not by using a trigger.
> Sample Table
> CREATE TABLE Test (INT DocumentID IDENTITY(1,1), DocumentParentID)
>
> Sample statements NOT working but to indicate what I would like to do
> INSERT INTO Test(DocumentParentID) VALUES (Test.DocumentID)
> INSERT INTO Test(DocumentParentID) VALUES (SCOPE_IDENTITY())
>
> Is this possible and if yes, could you please inform me how?
> Thanks in advance,
> Remco
>
>|||IDENT_SEED returns seed value, not the identity column value.
If you have a column defined as IDENTITY(1,1), The IDENT_SEED
Function will return 1
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"CBretana" <CBretana@.discussions.microsoft.com> wrote in message
news:F02EA293-84B0-4A8F-BA4A-A896EE548D33@.microsoft.com...
> Try using Ident)Seeed()
> as in
>
> INSERT INTO Test(DocumentParentID) VALUES (Ident_Seed('Test'))
>
> "Remco" wrote:
>|||I tested it with a newly created table, and it returned a '1', which was
both the seed and the value to be inserted... <gr>. It's Ident_Current()
That is needed here.
Try using Ident_Current()
as in
INSERT INTO Test(DocumentParentID) VALUES (Ident_Current('Test') + 1)
"Roji. P. Thomas" wrote:

> IDENT_SEED returns seed value, not the identity column value.
> If you have a column defined as IDENTITY(1,1), The IDENT_SEED
> Function will return 1
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "CBretana" <CBretana@.discussions.microsoft.com> wrote in message
> news:F02EA293-84B0-4A8F-BA4A-A896EE548D33@.microsoft.com...
>
>sql

Identity Specification limit

what happens when a column marked as Identity Specification reaches the limit? for example, I have some code tables using tinyints as keys, the actual number of entries will be 20 or so but there is some volatility, so eventually the 255 limit will be reached, what happens then?

the same thing applies to ints or bigints used as keys, eventually the database must run out of numbers

information will be appreciated

David Wilson.

Hi,

When the maximum has been reached, an error will be generated. Here is an example:

--CREATE TABLE IDENTITYTEST
--(
-- ID TINYINT IDENTITY(1, 1),
-- TEXTVALUE VARCHAR(50)
--)

DECLARE @.COUNTER INT
SET @.COUNTER = 0

WHILE @.COUNTER < 260
BEGIN
INSERT INTO IDENTITYTEST(TEXTVALUE) VALUES ('VALUE ' + CAST(@.COUNTER AS VARCHAR(3)))
SET @.COUNTER = @.COUNTER + 1
END

/*
RESULT:
Msg 8115, Level 16, State 1, Line 12
Arithmetic overflow error converting IDENTITY to data type tinyint.
Arithmetic overflow occurred.
*/

Reference: http://msdn2.microsoft.com/en-us/library/ms186775.aspx

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

right, I did about the same thing (not quite as elegant :-)) - with the same result

the question is - How do you fix it? and how do you code something into a daily checkup routine or the like to find it and fix it before it happens?

Identity seed/increment of MS SQLSERVER 2000 and MSDE

How can I change the increment value of an identity column?
I absolutely need to set a new value for the "increment"
DBCC CHECKIDENT only allow me to change the increment.
ALTER COLUMN does not allow altering identity
Is there a system column i can update or ?
I read few post suggesting using the enterprise manager to do so... but
we got lots of tables with many levels and fk. it's almost impossible in
our situation.
In fact we got many offline databases that where supposed to be
identity(-1,-1) but I just get a surprise!
Tons of data are alrealy inserted (positively) by a "kind of"
replication. but locally created records should be negative.
Thanks a lot
*** Sent via Developersdex http://www.developersdex.com ***Hi
You cannot update an IDENTITY property. Create another non_identity column
and move all data (of identity values) to the column.
You will be able to update a new created column and later on delete an
IDENTITY column
"Vincent" <anonymous@.devdex.com> wrote in message
news:Oq%23ev9A9HHA.600@.TK2MSFTNGP05.phx.gbl...
> How can I change the increment value of an identity column?
> I absolutely need to set a new value for the "increment"
> DBCC CHECKIDENT only allow me to change the increment.
> ALTER COLUMN does not allow altering identity
> Is there a system column i can update or ?
> I read few post suggesting using the enterprise manager to do so... but
> we got lots of tables with many levels and fk. it's almost impossible in
> our situation.
> In fact we got many offline databases that where supposed to be
> identity(-1,-1) but I just get a surprise!
> Tons of data are alrealy inserted (positively) by a "kind of"
> replication. but locally created records should be negative.
> Thanks a lot
>
> *** Sent via Developersdex http://www.developersdex.com ***

identity seed

Hello,
How can i set a identity column to start with 01 instead of 1?
Thnx
Identity values are integers, and the value of 01 an 1 are identical. So
there is no difference...
Is there something more in your question?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Samuel" <samuel@.hotrmail.com> wrote in message
news:%23E7VLd5rEHA.192@.tk2msftngp13.phx.gbl...
> Hello,
> How can i set a identity column to start with 01 instead of 1?
> Thnx
>
|||Hi Wayne,
I just wanted to start from 01.
How about related tables where i want the identity must start with 001 if
the main table starts with 1. For each new record in the main table, the
related table must rebuild the identiy to begin with 001.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eNGWeh5rEHA.4032@.TK2MSFTNGP12.phx.gbl...
> Identity values are integers, and the value of 01 an 1 are identical. So
> there is no difference...
> Is there something more in your question?
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Samuel" <samuel@.hotrmail.com> wrote in message
> news:%23E7VLd5rEHA.192@.tk2msftngp13.phx.gbl...
>
|||"Samuel" <samuel@.hotrmail.com> wrote in message
news:eFqh675rEHA.2732@.TK2MSFTNGP09.phx.gbl...
> I just wanted to start from 01.
> How about related tables where i want the identity must start with 001 if
> the main table starts with 1. For each new record in the main table, the
> related table must rebuild the identiy to begin with 001.
Samuel,
'001' is a formatted string version of the number 1. Identity values
are numeric and have no format. If you need to left-pad for display
purposes, you can use the following pattern:
SELECT RIGHT(REPLICATE('0', <n>) + YourCol, <n>) AS FormattedYourCol
FROM YourTable
Replace the <n>s in the above with the maximum length of the formatted
output string you'd like.

identity seed

Hello,
How can i set a identity column to start with 01 instead of 1?
ThnxIdentity values are integers, and the value of 01 an 1 are identical. So
there is no difference...
Is there something more in your question?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Samuel" <samuel@.hotrmail.com> wrote in message
news:%23E7VLd5rEHA.192@.tk2msftngp13.phx.gbl...
> Hello,
> How can i set a identity column to start with 01 instead of 1?
> Thnx
>|||Hi Wayne,
I just wanted to start from 01.
How about related tables where i want the identity must start with 001 if
the main table starts with 1. For each new record in the main table, the
related table must rebuild the identiy to begin with 001.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eNGWeh5rEHA.4032@.TK2MSFTNGP12.phx.gbl...
> Identity values are integers, and the value of 01 an 1 are identical. So
> there is no difference...
> Is there something more in your question?
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Samuel" <samuel@.hotrmail.com> wrote in message
> news:%23E7VLd5rEHA.192@.tk2msftngp13.phx.gbl...
>|||"Samuel" <samuel@.hotrmail.com> wrote in message
news:eFqh675rEHA.2732@.TK2MSFTNGP09.phx.gbl...
> I just wanted to start from 01.
> How about related tables where i want the identity must start with 001 if
> the main table starts with 1. For each new record in the main table, the
> related table must rebuild the identiy to begin with 001.
Samuel,
'001' is a formatted string version of the number 1. Identity values
are numeric and have no format. If you need to left-pad for display
purposes, you can use the following pattern:
SELECT RIGHT(REPLICATE('0', <n> ) + YourCol, <n> ) AS FormattedYourCol
FROM YourTable
Replace the <n>s in the above with the maximum length of the formatted
output string you'd like.

Identity Seed

have a table set one column as
data type = int
identity to yes
identity seed = 1 and identity increment = 1
after deleting some rows (e.g. 5), the number 5 will never be re used.
when the number has grown to the limit, can I still insert new rows?when you mention a limit, I am assuming you are talking about a check
constraint.
Regardless, identity column values are not necessary inserted in the order
of sequence, however the seed will always be no lower than the last
committed value.
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Music Lover" <music@.my-heart.org> wrote in message
news:OTtGn5$VDHA.1680@.tk2msftngp13.phx.gbl...
> have a table set one column as
> data type = int
> identity to yes
> identity seed = 1 and identity increment = 1
> after deleting some rows (e.g. 5), the number 5 will never be re used.
> when the number has grown to the limit, can I still insert new rows?
>
>|||No he means what happens when the INT field gets to 0x7FFFFFFF (2147483647)
"Mark Broadbent" <nospamplease_mark.broadbent@.virgin.net> wrote in message
news:VnpWa.582$k4.11506@.news2.nokia.com...
> when you mention a limit, I am assuming you are talking about a check
> constraint.
> Regardless, identity column values are not necessary inserted in the order
> of sequence, however the seed will always be no lower than the last
> committed value.
>
> --
> BR,
> Mark Broadbent mcdba,mcse+i
> _________________________
> "Music Lover" <music@.my-heart.org> wrote in message
> news:OTtGn5$VDHA.1680@.tk2msftngp13.phx.gbl...
> > have a table set one column as
> > data type = int
> > identity to yes
> > identity seed = 1 and identity increment = 1
> >
> > after deleting some rows (e.g. 5), the number 5 will never be re used.
> >
> > when the number has grown to the limit, can I still insert new rows?|||Little Test...
USE PUBS
CREATE TABLE dbo.tblIdentity ( ID INT IDENTITY(2147483640,1) CONSTRAINT
PK_tblIdentity PRIMARY KEY CLUSTERED, DATA CHAR(1) )
GO
INSERT INTO tblIdentity ( DATA )
SELECT 'A'
UNION ALL SELECT 'B'
UNION ALL SELECT 'C'
UNION ALL SELECT 'D'
UNION ALL SELECT 'E'
UNION ALL SELECT 'F'
UNION ALL SELECT 'G'
UNION ALL SELECT 'H'
UNION ALL SELECT 'I'
UNION ALL SELECT 'J'
GO
SELECT * FROM tblIdentity
GO
DROP TABLE dbo.tblIdentity
The Insert query generates the following Error:-
Server: Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.
So to answer your question.
No you can't insert records when the INT field reaches its limit
(2147483647)
When this happens you will have to reseed the field to -2147483648.
This will give you a few million more records...
When you run out of numbers after going through the negative values it'll be
time to a) archive some data b) change the number to a GUID or c) change the
number to a BIGINT. But 2147million records is enough for most people ;)
DBCC CHECKIDENT ( tblIdentity , RESEED , -2147483648 )
"Music Lover" <music@.my-heart.org> wrote:
> have a table set one column as
> data type = int
> identity to yes
> identity seed = 1 and identity increment = 1
> after deleting some rows (e.g. 5), the number 5 will never be re used.
> when the number has grown to the limit, can I still insert new rows?
>
>|||you can reset the identity seed to start incrementing from
your chosen number...
>--Original Message--
>have a table set one column as
>data type = int
> identity to yes
>identity seed = 1 and identity increment = 1
>after deleting some rows (e.g. 5), the number 5 will
never be re used.
>when the number has grown to the limit, can I still
insert new rows?
>
>.
>|||And bigint can store -9,223,372,036,854,775,808 through
9,223,372,036,854,775,807.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Tony Wilton" <tony@.scruffytiger.co.uk.NOSPAM> wrote in message
news:3f2a3824$0$12152$7b0f0fd3@.mistral.news.newnet.co.uk...
> If you use an INT field yes.
> Maximum values for data types are:-
> TINYINT 255
> SMALLINT 32767
> INT 2147483647
>
> "Music Lover" <music@.my-heart.org> wrote:
> > Thanks for your reply.
> > Want to double confirm
> > I can insert 2147million records withou any problem?
>|||thanks
how?
"jano" <janobermudes@.microsoft.com> wrote in message
news:09a801c35812$d7540810$a401280a@.phx.gbl...
> you can reset the identity seed to start incrementing from
> your chosen number...
>
> >--Original Message--
> >have a table set one column as
> >data type = int
> > identity to yes
> >identity seed = 1 and identity increment = 1
> >
> >after deleting some rows (e.g. 5), the number 5 will
> never be re used.
> >
> >when the number has grown to the limit, can I still
> insert new rows?
> >
> >
> >
> >.
> >

identity seed

Hello,
How can i set a identity column to start with 01 instead of 1?
ThnxIdentity values are integers, and the value of 01 an 1 are identical. So
there is no difference...
Is there something more in your question?
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Samuel" <samuel@.hotrmail.com> wrote in message
news:%23E7VLd5rEHA.192@.tk2msftngp13.phx.gbl...
> Hello,
> How can i set a identity column to start with 01 instead of 1?
> Thnx
>|||Hi Wayne,
I just wanted to start from 01.
How about related tables where i want the identity must start with 001 if
the main table starts with 1. For each new record in the main table, the
related table must rebuild the identiy to begin with 001.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eNGWeh5rEHA.4032@.TK2MSFTNGP12.phx.gbl...
> Identity values are integers, and the value of 01 an 1 are identical. So
> there is no difference...
> Is there something more in your question?
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Samuel" <samuel@.hotrmail.com> wrote in message
> news:%23E7VLd5rEHA.192@.tk2msftngp13.phx.gbl...
> > Hello,
> >
> > How can i set a identity column to start with 01 instead of 1?
> >
> > Thnx
> >
> >
>|||"Samuel" <samuel@.hotrmail.com> wrote in message
news:eFqh675rEHA.2732@.TK2MSFTNGP09.phx.gbl...
> I just wanted to start from 01.
> How about related tables where i want the identity must start with 001 if
> the main table starts with 1. For each new record in the main table, the
> related table must rebuild the identiy to begin with 001.
Samuel,
'001' is a formatted string version of the number 1. Identity values
are numeric and have no format. If you need to left-pad for display
purposes, you can use the following pattern:
SELECT RIGHT(REPLICATE('0', <n>) + YourCol, <n>) AS FormattedYourCol
FROM YourTable
Replace the <n>s in the above with the maximum length of the formatted
output string you'd like.

Identity reused problem

I have a table with id an identity column.
Can I forbid the identity column of taking the same values of deleted
ones?

How can I resolve this issue?

*** Sent via Developersdex http://www.developersdex.com ***"Marie-Christine Bechara" <marie-christine.bechara@.ifsal.comwrote in
message news:45f57272$0$502$815e3792@.news.qwest.net...

Quote:

Originally Posted by

>
>
I have a table with id an identity column.
Can I forbid the identity column of taking the same values of deleted
ones?
>
How can I resolve this issue?
>
*** Sent via Developersdex http://www.developersdex.com ***

An Identity is always an increasing value unless you do a DBCC CHECKIDENT
call.

So already it avoids doing this.

--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||"Marie-Christine Bechara" <marie-christine.bechara@.ifsal.comwrote in
message news:45f57272$0$502$815e3792@.news.qwest.net...

Quote:

Originally Posted by

>
>
I have a table with id an identity column.
Can I forbid the identity column of taking the same values of deleted
ones?

There is no foolproof way since SET IDENTITY INSERT ON will allow reuse.

You could leave the records in place, using a deleted flag column to
indicate a logical deletion.

You could use an insert trigger to intercept id's that are below the max(id)
value.

You could use stored procedures and permissions to block the usage of the
identity insert.

Quote:

Originally Posted by

>
How can I resolve this issue?
>
*** Sent via Developersdex http://www.developersdex.com ***

Identity property

Hello friends,

I am using sql server 2005. In some tables to create the column Autoincrement I had set the 'Idetity Specification' property to 'Yes'. I want to know that how can we do it through sql scripts i.e. by writing query.

Please let me know

Thanks & Regards
Girish Nehte

CREATE TABLE your_table( id_numint IDENTITY(1,1), fnamevarchar (20), minitchar(1), lnamevarchar(30))

PS: IDENTITY(seed, increment)|||

Thanks Addie.

Actually I have already build table and its identity field for id column is already set to "Yes". Now what I want to do is create a script which when executed will first set identity field to "No" and then again to "Yes", i.e. I want to alter that table.

How it can be done?

Thanks & Regards
Girish Nehte

|||T-SQL's ALTER TABLE statement doesn't support dropping the IDENTITY property in SQL Server 2000 or 7.0. Your only option for deleting an IDENTITY column is to create a new table structure without the IDENTITY column, then copy the data into this structure.

Just curious - why would you like to do that?

|||

I suspect what you want to do is to turn off the auto increment on the identity column so you can insert your own values. To do that, use

SETIDENTITY_INSERT tablenameOFF

then populate the table and issue

SETIDENTITY_INSERT tablenameON

|||

Actually in my project I want to create a script after running that script all the data from the data will be deleted and columns with identity "YES" will be reset to 0. Thats why and I think that it can be done by setting and resetting the identity field.

|||

Try:

1. using the truncate statement instead of delete (ex: TRUNCATE TABLE theNameOfYourTable)
2. EXEC ('DBCC CHECKIDENT(theNameOfYourTable,RESEED,0)')

identity problem

I have multiple tables with identity column replicated (merge
replication) from Server A to Server B. During the initial setup, I
specified that sql agent manages the identity columns automatically and
runs in continuous mode. But a while, I got identity error and I had to
stop the agent, run sp_adjustpublisherientityrange and restart the
agent. After more research, I found out that this is a SQL bug and what
Microsoft recommend is exactly what I did, they also said the agent
should be scheduled to run every minute or so. However, in my case, this
is unacceptable because client may still run out of ID before the agent
restart and it causes the application error out. So here's what I did
1) build a insert trigger on every articles to check the range and call
sp_adjustpublisheridentityhrange is necessary, but this solution doesn't
work because looks like sp_adjustpublisheridentity has no effect until
the identity column hits its limit
2) build a insert trigger on every articles to check and re-adjust the
check constraint. This somewhat working on the publisher side, however,
how to deal with in at the subscriber side? I know there's sp_help and I
can get the constraint from system tables, but they are more for human
eyes than for programs, just pain to parse and I just don't like putting
too much inside a trigger
any recommendations?
Thanks
Eric Li
SQL DBA
MCDBA
Eric
it is possible to create identity ranges which never overlap, in which case
you'll just need to give a large range to each subscriber and not need to
adjust it. Have a look at this article by Michael Hotek:
http://www.mssqlserver.com/replicati...h_identity.asp
Regards,
Paul Ibison
|||there are two approaches to this problem
1) follow the KB's advice
http://support.microsoft.com/default...&Product=sql2k
2) set your ranges to values where they never will be exceeded during the
lifetime of your replication solution.
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:ufUvuhoEEHA.580@.TK2MSFTNGP11.phx.gbl...
> I have multiple tables with identity column replicated (merge
> replication) from Server A to Server B. During the initial setup, I
> specified that sql agent manages the identity columns automatically and
> runs in continuous mode. But a while, I got identity error and I had to
> stop the agent, run sp_adjustpublisherientityrange and restart the
> agent. After more research, I found out that this is a SQL bug and what
> Microsoft recommend is exactly what I did, they also said the agent
> should be scheduled to run every minute or so. However, in my case, this
> is unacceptable because client may still run out of ID before the agent
> restart and it causes the application error out. So here's what I did
> 1) build a insert trigger on every articles to check the range and call
> sp_adjustpublisheridentityhrange is necessary, but this solution doesn't
> work because looks like sp_adjustpublisheridentity has no effect until
> the identity column hits its limit
> 2) build a insert trigger on every articles to check and re-adjust the
> check constraint. This somewhat working on the publisher side, however,
> how to deal with in at the subscriber side? I know there's sp_help and I
> can get the constraint from system tables, but they are more for human
> eyes than for programs, just pain to parse and I just don't like putting
> too much inside a trigger
> any recommendations?
> Thanks
> --
> Eric Li
> SQL DBA
> MCDBA