How to change identity through script

Hi All,
I have a Column with identity(1,1) .
Using script how to set that to identity(2,2)
Thanks in advance.
SupriyaAssume your original table with identity (1,1) is called test. You can go
through the script below to change the seed and increment.
CREATE TABLE dbo.Tmp_test
(
id int NOT NULL IDENTITY (2, 2),
string varchar(90) NULL
)
SET IDENTITY_INSERT dbo.Tmp_test ON
IF EXISTS(SELECT * FROM dbo.test)
INSERT INTO dbo.Tmp_test (id, string)
SELECT id, string FROM dbo.test WITH (HOLDLOCK TABLOCKX)
SET IDENTITY_INSERT dbo.Tmp_test OFF
DROP TABLE dbo.test
EXECUTE sp_rename N'dbo.Tmp_test', N'test', 'OBJECT'
"Supriya Pagadala" <supriya_pcl@.yahoo.com> wrote in message
news:O0XJGAS9HHA.5456@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I have a Column with identity(1,1) .
> Using script how to set that to identity(2,2)
> Thanks in advance.
> Supriya
>|||Well, I thought I remembered that you could do this, so I tried to whomp a
little something up. Couldn't get it to work. But then I focus on 2000 docs,
while using 2005. So finally, I looked at the 2005 docs: this is an exact
copy/paste from 2005 BOL: ALTER TABLE:
--
CREATE TABLE MyCustomers (CustID INTEGER IDENTITY (100,1) PRIMARY KEY,
CompanyName NvarChar (50))
INSERT INTO MyCustomers (CompanyName) VALUES ('A. Datum Corporation')
ALTER TABLE MyCustomers ALTER COLUMN CustId IDENTITY (200, 2)
--
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'IDENTITY'.
--
If the supported syntax doesn't match BOL, we got a problem somewhere. So,
if this is true, then you're stuck creating a new table and doing the
insert.
Sorry,
Jay
"Supriya Pagadala" <supriya_pcl@.yahoo.com> wrote in message
news:O0XJGAS9HHA.5456@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I have a Column with identity(1,1) .
> Using script how to set that to identity(2,2)
> Thanks in advance.
> Supriya
>|||On Thu, 13 Sep 2007 06:48:38 -0700, Jay wrote:
>Well, I thought I remembered that you could do this, so I tried to whomp a
>little something up. Couldn't get it to work. But then I focus on 2000 docs,
>while using 2005. So finally, I looked at the 2005 docs: this is an exact
>copy/paste from 2005 BOL: ALTER TABLE:
>--
>CREATE TABLE MyCustomers (CustID INTEGER IDENTITY (100,1) PRIMARY KEY,
>CompanyName NvarChar (50))
>INSERT INTO MyCustomers (CompanyName) VALUES ('A. Datum Corporation')
>ALTER TABLE MyCustomers ALTER COLUMN CustId IDENTITY (200, 2)
Hi Jay,
If you go to that page in Books Online again, you'll see at the top of
the screen that this applies to SQL Server 2005 Compact Edition. This is
a different product, that is for some mysterious reason documented in
the same version of BOL (and I've lost track of the number of times I
myself felll victim to this misunderstanding - especially since the CE
version of BOL tends to come up before the "regular SQL Server" docs).
Changing identity is only supported on CE, and not on "normal" editions
of SQL Server.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Sep 12, 9:28 pm, "Supriya Pagadala" <supriya_...@.yahoo.com> wrote:
> Hi All,
> I have a Column with identity(1,1) .
> Using script how to set that to identity(2,2)
> Thanks in advance.
> Supriya
google for DBCC CHECKIDENT RESEED
isnt this the easiest way?|||does work on 2005.
use tempdb
if object_id('t') is not null drop table t
CREATE TABLE t (col1 INTEGER IDENTITY (100,1) PRIMARY KEY, col2 varchar
(10))
insert into t(col2) values('one')
DBCC CHECKIDENT (t, RESEED, 200)
insert into t(col2) values('two')
select * from t
"densial" <densial@.gmail.com> wrote in message
news:1189891139.711717.142170@.g4g2000hsf.googlegroups.com...
> On Sep 12, 9:28 pm, "Supriya Pagadala" <supriya_...@.yahoo.com> wrote:
>> Hi All,
>> I have a Column with identity(1,1) .
>> Using script how to set that to identity(2,2)
>> Thanks in advance.
>> Supriya
> google for DBCC CHECKIDENT RESEED
> isnt this the easiest way?
>|||I get it now.
This isn't resetting the initial value, or the increment, it is setting the
highest value used so the next value comes in above it.
Not what the OP asked for, usefull, but it doesn't change the increment from
1, to 2.
"Jay" <spam@.nospam.org> wrote in message
news:eGmGiZG%23HHA.748@.TK2MSFTNGP04.phx.gbl...
> does work on 2005.
> use tempdb
> if object_id('t') is not null drop table t
> CREATE TABLE t (col1 INTEGER IDENTITY (100,1) PRIMARY KEY, col2 varchar
> (10))
> insert into t(col2) values('one')
> DBCC CHECKIDENT (t, RESEED, 200)
> insert into t(col2) values('two')
> select * from t
> "densial" <densial@.gmail.com> wrote in message
> news:1189891139.711717.142170@.g4g2000hsf.googlegroups.com...
>> On Sep 12, 9:28 pm, "Supriya Pagadala" <supriya_...@.yahoo.com> wrote:
>> Hi All,
>> I have a Column with identity(1,1) .
>> Using script how to set that to identity(2,2)
>> Thanks in advance.
>> Supriya
>> google for DBCC CHECKIDENT RESEED
>> isnt this the easiest way?
>sql

how to change color of a field if it is duplicate..........

hi guys
how can i change the fore color of a field value if there is a duplicate of it.
thanks in advance but only for those who help me (Lolz) ;)
Sillytry like
in format editor in the font tab write a formula on color [x+2]

IF (field1) = PREVIOUS(field1) THEN
RGB(255,0,0)
ELSE
RGB(0,0,0)

Gragi|||hi grag

thanks for ur reply but problem in my case is that
the duplicate value can reside any where in the list.

How to browse cube in Excel?

Hi, all here,

Could anyone tell me how can I browse cube data in Excel?

Thanks a lot in advance for any guidance and help.

There are two options I know of.

One is Microsofts Free giveaway Data Access Tool. Only works with Excel 2002 onwards I think, get it at http://www.microsoft.com/downloads/details.aspx?FamilyId=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en home page for product is at http://www.microsoft.com/office/solutions/accelerators/exceladdin/default.mspx

Another is XLCubed www.xlcubed.com which is similar but far more powerful and friendly. This works with Excel 2000 SP2 onwards

Most of the Big front end tools like Cognos and proclarity have Excel plugins but they tend to be fairly expensive. XL3 is about ￡300 a seat and they do 30 day trial versions. We had four or five trials in a row before we settled on it as a final solution.

The MS tool is useable but not for anything too complicated. Both are really easy to pick up if your Excel users know anything about pivot tables.

regards

Steve

|||Hi, Steve, thanks a lot.|||

Hi,

I have downloaded and installed the add-in. When I try to connect to the database I get an error with Initialization of the data source failed.

Can one one help me on this?

cheers

Josh

How to browse cube in Excel?

Hi, all here,

Could anyone tell me how can I browse cube data in Excel?

Thanks a lot in advance for any guidance and help.

There are two options I know of.

One is Microsofts Free giveaway Data Access Tool. Only works with Excel 2002 onwards I think, get it at http://www.microsoft.com/downloads/details.aspx?FamilyId=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en home page for product is at http://www.microsoft.com/office/solutions/accelerators/exceladdin/default.mspx

Another is XLCubed www.xlcubed.com which is similar but far more powerful and friendly. This works with Excel 2000 SP2 onwards

Most of the Big front end tools like Cognos and proclarity have Excel plugins but they tend to be fairly expensive. XL3 is about ￡300 a seat and they do 30 day trial versions. We had four or five trials in a row before we settled on it as a final solution.

The MS tool is useable but not for anything too complicated. Both are really easy to pick up if your Excel users know anything about pivot tables.

regards

Steve

|||Hi, Steve, thanks a lot.|||

Hi,

I have downloaded and installed the add-in. When I try to connect to the database I get an error with Initialization of the data source failed.

Can one one help me on this?

cheers

Josh