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 files from databases

Hi, I've created a new filegroup, then added to it a new filename, then create a table pointing to this filegroup. So far everything is ok, but if I want to revert the process in this way:

1- Drop the table - OK

2- Drop the filegroup - OK

3- Drop the filename - ERROR: Msg 5009, Level 16, State 9, Line 2
One or more files listed in the statement could not be found or could not be initialized.

When I query a catalog view with this query:

Select*FromSys.Database_Files

I get the file that I had recently deleted, it is offline but I can not delete it using the ALTER DATABASE instructions.

This is the code I use:

Use [TESTING ]

Go

-- Add a FileGroup to the Database

AlterDatabase TESTING AddFileGroup FG01

Go

-- Add a file to a FileGroup

AlterDatabase TESTING AddFile(

NAME= TESTING_DATA01,

FILENAME='D:\Sql Server\Data\Testing_Data01.ndf',

SIZE= 1 MB,

MAXSIZE= 10 MB,

FILEGROWTH= 1 MB

)ToFileGroup FG01

Go

-- Create the table using a specific FileGroup

CreateTable TABLE1 (

Id IntNotNull,

FirstName VarChar(30)NotNull,

LastName VarChar(30)NotNull,

BirthDate SmallDateTime

)

On FG01

So far everything works ok, but in the next code there is an error:

-- Delete the table

Drop Table TABLE1

Go

-- Remove a FileGroup from the Database

AlterDatabase TESTING Remove FileGroup FG01

Go

-- Try to remove the file

AlterDatabase TESTING Remove File TESTING_DATA01

When I try to remove the file there is an error:

"Msg 5009, Level 16, State 9, Line 2
One or more files listed in the statement could not be found or could not be initialized"

You simply need to change the order of your Remove statements to remove the file BEFORE you remove the filegroup.|||Thanks for the answer, but how can I delete the entries in the Sys.Database_Files that already exists ?

Remove "CONSTRAINED" flag on Paramaters using Graphical Interface

I'm using MS Report Designer 2005.

When I create a parameter using the checkbox in the graphical design mode's filter pane, Report Designer automatically sets the constrained flag, eg:

STRTOMEMBER(@.ToWorkItemSystemId, CONSTRAINED)

How do I remove this? I can't do it in the generated text query because the program only let you edit mdx queries graphically or textually EXCLUSIVELY?

Once you switch to MDX mode, you cannot go back to Designer mode. Or, rather, if you do, your manual changes will be nuked because the designer doesn't support reverse-engineering manual changes.

I am curious though, why would you want to remove the CONSTRAINED flag?

|||

Please disregard. I was getting an error that my Parameters were invalid due to the contrained flag. I've since figured out that they were in the wrong format.

In any case, if a flag set\removed textually, it would be nice if there a way to do the same graphically.

Remove "CONSTRAINED" flag on Paramaters using Graphical Interface

I'm using MS Report Designer 2005.

When I create a parameter using the checkbox in the graphical design mode's filter pane, Report Designer automatically sets the constrained flag, eg:

STRTOMEMBER(@.ToWorkItemSystemId, CONSTRAINED)

How do I remove this? I can't do it in the generated text query because the program only let you edit mdx queries graphically or textually EXCLUSIVELY?

Once you switch to MDX mode, you cannot go back to Designer mode. Or, rather, if you do, your manual changes will be nuked because the designer doesn't support reverse-engineering manual changes.

I am curious though, why would you want to remove the CONSTRAINED flag?

|||

Please disregard. I was getting an error that my Parameters were invalid due to the contrained flag. I've since figured out that they were in the wrong format.

In any case, if a flag set\removed textually, it would be nice if there a way to do the same graphically.

sql

Remote view

I was wondering if i should create a lil program that could run in the tray
showing status of jobs running on a SQL server. By that i mean if they are
enabled, runnable and so on. Are there anyone in here that can lead me to
some info related to that? I want to devlope it using C#.
I have been roaming the net for info about this but so far no luck. Is there
an API on the SQL server that enables me to do this?
Best regards
TrondThe SQL-DMO API should give you what you want for SQL7.0 and 2000.
However, DMO is a COM API so you may prefer instead to develop your
application with SMO, which is DMO's .NET replacement in SQL Server
2005.
http://msdn.microsoft.com/library/d.../>
01_2yi7.asp
David Portas
SQL Server MVP
--

Remote Server issue...

Did you ever figure this out ? I have the same problem.
I think you have to delete all subscribers and then delete the remote
server. Then create a linked server and then add back the subscribers.
Becuase, the sp_serveroption for data access will not work with a
remote server
Brett wrote:

quote:

---

> *I am attmepting to setup up a linked server from my
> production server (sql1) to my reporting server (sql2).
> I have replication from sql1 to sql2.
> I noticed that there is already a remote server setup on
> sql1 for sql2. It will not allow me to setup a linked
> server for sql2. Plus, I am unable to run a distributed
> query... I get the error message that the remote server
> is not configured from data access.
> How do I setup a linked server from sql1 to sql2 so that
> I have data access?
> Please advise..
> Thank you,
> Brett
> I am using SQL Server 2000 for my production and
> reporting servers. *

---

dannr09
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message110773.htmlIf you're using SQL Replication, we setup Remote Servers as opposed to
Linked Servers already. What functionality are you trying to acheive?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Remote Server

Hi all,

I am trying to locate some scripts for create/update and a nightly scripts that run to update some databases.

Now within the server itself there are no scripts that are running beside the normal system stored procedure.

I notice that the server is a remote server is it possible for the scripts that are running coming from another server.

Once I click on the server (NTBVM) and right click on the property RPC is check, and 'map remote logins to differents local logins' has the radio dial marked however when you look at the list below no information is recorded.

Please help

Thanking you in advanceGreetings, M'Lady! It's good to see you again.

While it is unlikely, it is possible that the scripts would run on another server.

Is this something you are investigating because it is happening now, or something you are planning so it will happen soon?

-PatP|||It something that I was asked to fix and give a time line.

One of the task is to modify registry settings for a OSI servlet(What is the world).

The network admin said that there are scripts that run and I've look everywhere for the scripts and can't find them.

The frontend of this tool call OSI and the backend is SQL. I wonder if I would be able to find the scripts in the tool itself.

I don't have a clue.

Thanks for responding so fast.

Lystra|||Ok, it is pretty easy to define OSI (http://searchnetworking.techtarget.com/sDefinition/0,,sid7_gci212725,00.html), and servlet (http://searchdatabase.techtarget.com/sDefinition/0,,sid13_gci212966,00.html), although it is somewhat peculiar to see them used together that way.

One thing that might help you is to run the SQL profiler overnight to see who/what connects to your server. That will at least give you a few "breadcrumbs" to start from!

Another "high probability" place to check is the SQL Agent running on the SQL Server. It is quite easy to schedule scripts (and many other things) to run as needed against your SQL server or other machines.

-PatP|||I have check the sql server agent nothing.

I will try profiler overnight.

Since servlet--is a small program that runs on a server, maybe the scripts are running from there?

Thanks

Lystra|||If there was nothing in the SQL Agent, the servlet would be my next guess. Do any of the network/admin/IT types have any kind of documentation for this system whatsoever? It sounds to me like you are the new kid on the block, being sent out to clean up a runaway freight train that has been careening unattended through the office for quite some time!

-PatP|||Yup, you can't be more right. This is my three week at this job. I could tell you the horror stories from the first week.

But I have to admit I love this job because it is pushing me to be a better dba. My last job was a contractor for the government and I was not able to use my troubleshooting skills to the fullest. Here I have to.

Lystra|||A friend of mine is in a similar situation in the DC area. She's been developing MS-Access for one of the government's largest construction contractors for an age. She's reached the point where she wants to go further than they are willing/able to manage, and she's trying to figure out what her next step should be...

Unfortunately, I've been out of the DC area for too long, so I've really lost touch with the IT/business community there. Oh well, can't win them all!

-PatP|||It's pretty much the same as you have left it.

Nothing have change.

Lystra

Remote Job Execution

Is there a way to execute SQL Server jobs (Not the jobs
created by DTS packages or Maintenance Plans. Just jobs
created with --> Create new Job...)remote (Trough a batch
file, third party software like tivoli schedular)?
Thanks.
Use OSQL the command line utility to run execute stored procedure
sp_start_job.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:98aa01c433ac$97e9f420$a301280a@.phx.gbl...
> Is there a way to execute SQL Server jobs (Not the jobs
> created by DTS packages or Maintenance Plans. Just jobs
> created with --> Create new Job...)remote (Trough a batch
> file, third party software like tivoli schedular)?
> Thanks.
>

Remote Job Execution

Is there a way to execute SQL Server jobs (Not the jobs
created by DTS packages or Maintenance Plans. Just jobs
created with --> Create new Job...)remote (Trough a batch
file, third party software like tivoli schedular)?
Thanks.Use OSQL the command line utility to run execute stored procedure
sp_start_job.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:98aa01c433ac$97e9f420$a301280a@.phx.gbl...
> Is there a way to execute SQL Server jobs (Not the jobs
> created by DTS packages or Maintenance Plans. Just jobs
> created with --> Create new Job...)remote (Trough a batch
> file, third party software like tivoli schedular)?
> Thanks.
>

Remote Job Execution

Is there a way to execute SQL Server jobs (Not the jobs
created by DTS packages or Maintenance Plans. Just jobs
created with --> Create new Job...)remote (Trough a batch
file, third party software like tivoli schedular)?
Thanks.Use OSQL the command line utility to run execute stored procedure
sp_start_job.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:98aa01c433ac$97e9f420$a301280a@.phx.gbl...
> Is there a way to execute SQL Server jobs (Not the jobs
> created by DTS packages or Maintenance Plans. Just jobs
> created with --> Create new Job...)remote (Trough a batch
> file, third party software like tivoli schedular)?
> Thanks.
>

Remote Installation Create Response File

Hi,

Actaully i want to install SQL SERVER 2005 Enterprise edition in the silent mode and I was tyring to create a response file (setup.iss), but from the document provided i saw that SQL SERVER 2005 Enterprise edition doesnt create response file, and doesnt support setup.iss. So please provide me some solution where i can generate response file or some thing which is similar to that.

SQL 2005 now supports a ton of switches which will allow you to select all the GUI options from the command line. Check the docs on the DVD for all the options. Stick the finished command line in a batch file and use your favorite remote install tool to launch the batch file.|||

Here's the link in BOL for silent installs with a ton of examples:

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

Thanks,
Sam Lester (MSFT)

Remote creation of a Maintenance plan

I'm trying to remotely create a maintenance plan using the management studio in order to backup databases. I get the following error.

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

TITLE: Maintenance Plan Wizard Progress

Create maintenance plan failed.

ADDITIONAL INFORMATION:

Create failed for JobStep 'Subplan'. (Microsoft.SqlServer.MaintenancePlanTasks)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The specified '@.subsystem' is invalid (valid values are returned by sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

The server doesn't have the Management studio client installed so I can't create the maintenance plan locally. If I install just the Management studio will the data currently in the server be effected? Any solution to the remote problem?

Thanks,

Neil

SSIS(SQL Server integration services) need to be installed along with the server, for executing maintenace plans successfully.

For SP1, the current plan is either SSIS or tools installed on the server box would address this issue.

Gops Dwarak

Remote creation of a Maintenance plan

I'm trying to remotely create a maintenance plan using the management studio in order to backup databases. I get the following error.

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

TITLE: Maintenance Plan Wizard Progress

Create maintenance plan failed.

ADDITIONAL INFORMATION:

Create failed for JobStep 'Subplan'. (Microsoft.SqlServer.MaintenancePlanTasks)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The specified '@.subsystem' is invalid (valid values are returned by sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

The server doesn't have the Management studio client installed so I can't create the maintenance plan locally. If I install just the Management studio will the data currently in the server be effected? Any solution to the remote problem?

Thanks,

Neil

SSIS(SQL Server integration services) need to be installed along with the server, for executing maintenace plans successfully.

For SP1, the current plan is either SSIS or tools installed on the server box would address this issue.

Gops Dwarak

Remote connection refused when creating ODBC connection to SQL Server 2005

When I create a new odbc connection to a SQL server 2005 Db I get a failure telling me dat de SQL server does not allow remote connections.

How can I allow the server to allow this.

Any help appreciated

regards

I would recommend looking at the following post, hopefully it already contains the answer to your issue:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=297514&SiteID=1

If the information here is not enough to fix your problem, let us know, we will be glad to help.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine