remove IDENTITY in sql server

suppose my table is
create table TEST (
id NUMERIC(10,0) IDENTITY(1,1) NOT NULL)
I want to remove IDENTITY(1,1) from id without dropping tableHi ankur
I suspect you are planning on sorting out your own no-gaps identity field here. This is a very common request but rarely is a good choice. Before you carry on I would really recommend you just carry for a little longer in this thread:
http://www.dbforums.com/showthread.php?t=1620216
It could save you a heck of a lot of wasted time if it turns out this is not the best way to procede.|||I am toatally new to sql server

so

initailly our project manager has ordered me to put auto incrment

but

now

PM has said to remove auto increment

I am in bit trouble dont know to remove auto increment

so

I asked help from you

help me please|||Look up the ALTER TABLE command in BOL. You will want to pay attention to the DROP COLUMN option.|||beh.

just tell him you removed it, but don't. PM shouldn't be making such low level design decisions!|||why don't you want to drop the table?

create a new one, copy over the numbers (after all, the table has only one column), then drop the old one and rename the new one

something about this whole scenario does not smell right

the project manager is actually you, isn't she|||Think bigger picture

If you still need to retain the id, what is the PM's plan to manage new id's?

And do you have a natural key?|||table is too large but I have taken a small example

there large data in I want not to drop

if there is any query are way to remove IDENTITY(1,1) without effecting
data|||table is too large but I have taken a small example

there large data in I want not to drop

if there is any query are way to remove IDENTITY(1,1) without effecting
data

No, but what's big? ALTER does not allow you to drop an IDENTITY Property on a Column...

As a matter of fact, do you know how save a script in Enterprise Mangler?

go create a test table with an identity column

Then go into EM and remove the identity column and save it...it wil ask you if you want to save the script SQL Server used to make the change...you'll see what SQL Server has to do

Well I can tell you..it it will copy the data to another table where the column will be defined as int, drop the old table and rename the new table to the old table...but go look for yourself

Personally, I'd bdp the data out in native format, create a new table with the definition you want, bcp the data in, rename the old table to something else like table_old, the rename the new table to the old

Again, how many rows are we talking about

Oh...you can disable the identity column in your stored procedures...you are using sprocs as data access only right...and then do

SET IDENTITY_INSERT ON

But that's a hack|||And do you have a natural key?as if this would make a difference to the yes-identity-no-wait-a-sec-no-identity PM|||I want to remove IDENTITY(1,1) from id without dropping tableDropping the identity property can not be done directly, but you can acheive similar effects in more than one way. The problem is that there are many reasons for not wanting to drop the table, and without understanding exactly what you are trying to do or avoid I can't give you clear instructions.

The short answer is to simply recreate a working copy of the table without the identity property, drop the original table, then rename the working copy so it has the same name as the original. This process is easy, safe, and relatively foolproof.

There are a number of other solutions, but without understanding your requirements I have no idea which solution or solutions to suggest.

-PatP|||This may just be naivety on my part but why can't you add a new int column and copy the identity values into the new column, then drop the identity column?|||This may just be naivety on my part but why can't you add a new int column and copy the identity values into the new column, then drop the identity column?Not naive at all George - that's the way I would do it (though with decimal(10) so you can insert half a million records a day for 6 years). There is a downside to that though:
http://www.nigelrivett.net/SQLAdmin/AlterTableProblems.html|||Ahh I see!
Thanks Poots :)

I guess as long as you're aware of the potential problems you can use your best judgement per case.|||Yes I got my answer to remove IDENTITY from column

see attachment set IDENTITY to NO|||see attachment set IDENTITY to NOJust curioius, but do you have any clue what setting IDENTITY to "NO" on this screen will actually do? It will produce exactly the result you have requested, using exactly the method that you wanted to avoid.

As long as you are happy with what you've found, that's fine. It just isn't what you requested! ;)

-PatP|||Yes I got my answer to remove IDENTITY from column

see attachment set IDENTITY to NO

To quote a Guiness Stout Commercial...

"Brilliant!"|||go create a test table with an identity column

Then go into EM and remove the identity column and save it...it wil ask you if you want to save the script SQL Server used to make the change...you'll see what SQL Server has to do

That sounds oddly familiar

Remove <select a value> option

I have a parameter that get his values from a query.
his defualt value is Null.
In the report page, The first value is "<select a value>".
even if I give the parameter another defualt value Then it open with
this value selected but still the first value is "<select a value>".
Can I remove this?
Thanks.On Feb 25, 12:23 am, "nicknack" <roezo...@.gmail.com> wrote:
> I have a parameter that get his values from a query.
> his defualt value is Null.
> In the report page, The first value is "<select a value>".
> even if I give the parameter another defualt value Then it open with
> this value selected but still the first value is "<select a value>".
> Can I remove this?
> Thanks.
Please refer to the response I posted on the other Reporting Services
group.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Where is the other reporting services group..I can't find it.
Cheers
D
"EMartinez" wrote:
> On Feb 25, 12:23 am, "nicknack" <roezo...@.gmail.com> wrote:
> > I have a parameter that get his values from a query.
> > his defualt value is Null.
> >
> > In the report page, The first value is "<select a value>".
> > even if I give the parameter another defualt value Then it open with
> > this value selected but still the first value is "<select a value>".
> >
> > Can I remove this?
> >
> > Thanks.
> Please refer to the response I posted on the other Reporting Services
> group.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>

remote query

i am trying to do a remote query and when I run the query
it gives me this:
login failed for user '(null)'. Reason: Not associated
with a trusted SQL Server connection
My query statement
select * from openquery(server, 'select[table]
from database')
any help would be niceIt looks like a problem with how you have the security
mapping configured for your linked server. Make sure you
have this configured correctly with a valid login, password,
etc. You can find more information in books online under
linked servers.
-Sue
On Tue, 7 Oct 2003 13:36:32 -0700, "Jamie Elliott"
<jelliott@.alexlee.com> wrote:
>i am trying to do a remote query and when I run the query
>it gives me this:
>login failed for user '(null)'. Reason: Not associated
>with a trusted SQL Server connection
>My query statement
>select * from openquery(server, 'select[table]
>from database')
>any help would be nice|||Hi Jamie,
I agree with Sue. If you have Enterprise Manager, it is better to change
the security mapping in Security tab in linked server Properties. For
example, I would use a SQL login (allow that linked server to accept SQL
authentication) that can access linked SQL Server and select "Be made using
this security context", input the SQL login and password.
Bill Cheng
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--
| Content-Class: urn:content-classes:message
| From: "Jamie Elliott" <jelliott@.alexlee.com>
| Sender: "Jamie Elliott" <jelliott@.alexlee.com>
| Subject: remote query
| Date: Tue, 7 Oct 2003 13:36:32 -0700
| Lines: 11
| Message-ID: <055701c38d12$b1c82ec0$a301280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcONErHIaJzjVVOxSfOfBlXPy1QDEQ==| Newsgroups: microsoft.public.sqlserver.server
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:310079
| NNTP-Posting-Host: TK2MSFTNGXA11 10.40.1.163
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| i am trying to do a remote query and when I run the query
| it gives me this:
|
| login failed for user '(null)'. Reason: Not associated
| with a trusted SQL Server connection
|
| My query statement
| select * from openquery(server, 'select[table]
| from database')
|
| any help would be nice
|