Remove identity property of a primary key

Hi,
I have a table with a column named ID as primary key, this column has
the identity property. This ID is referenced by some other tables as foreign
key.
Is there a way, I can use "alter table alter ID int not null...." TSQL
to remove this identity property?
Thanks!
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy1. drop FK
2. sp_rename table with identity to some other name
3. create table new with the same name without identity
4. insert new select * from old
5. create FK
6 drop old
"Hardy Wang" <hardywang@.hotmail.com> wrote in message
news:O7AqRnXrFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a table with a column named ID as primary key, this column has
> the identity property. This ID is referenced by some other tables as
> foreign key.
> Is there a way, I can use "alter table alter ID int not null...." TSQL
> to remove this identity property?
> Thanks!
> --
> WWW: http://hardywang.1accesshost.com
> ICQ: 3359839
> yours Hardy
>|||The only way is dropping the column. Try using EM if you really want to do
this. In EM, before saving changes, press button "save change script", third
from left to right in the tool bar. You will see what really EM does in orde
r
to accomplish this tak.
Example: (from northwind.orders)
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Shippers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Employees
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Customers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT DF_Orders_Freight
GO
CREATE TABLE dbo.Tmp_Orders
(
OrderID int NOT NULL,
CustomerID nchar(5) NULL,
EmployeeID int NULL,
OrderDate datetime NULL,
RequiredDate datetime NULL,
ShippedDate datetime NULL,
ShipVia int NULL,
Freight money NULL,
ShipName nvarchar(40) NULL,
ShipAddress nvarchar(60) NULL,
ShipCity nvarchar(15) NULL,
ShipRegion nvarchar(15) NULL,
ShipPostalCode nvarchar(10) NULL,
ShipCountry nvarchar(15) NULL
) ON [PRIMARY]
GO
DECLARE @.v sql_variant
SET @.v = N''
EXECUTE sp_addextendedproperty N'MS_Description', @.v, N'user', N'dbo',
N'table', N'Tmp_Orders', N'column', N'OrderID'
GO
ALTER TABLE dbo.Tmp_Orders ADD CONSTRAINT
DF_Orders_Freight DEFAULT (0) FOR Freight
GO
IF EXISTS(SELECT * FROM dbo.Orders)
EXEC('INSERT INTO dbo.Tmp_Orders (OrderID, CustomerID, EmployeeID,
OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName,
ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate,
ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion,
ShipPostalCode, ShipCountry FROM dbo.Orders (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.[Order Details]
DROP CONSTRAINT FK_Order_Details_Orders
GO
DROP TABLE dbo.Orders
GO
EXECUTE sp_rename N'dbo.Tmp_Orders', N'Orders', 'OBJECT'
GO
ALTER TABLE dbo.Orders ADD CONSTRAINT
PK_Orders PRIMARY KEY CLUSTERED
(
OrderID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX CustomerID ON dbo.Orders
(
CustomerID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX CustomersOrders ON dbo.Orders
(
CustomerID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX EmployeeID ON dbo.Orders
(
EmployeeID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX EmployeesOrders ON dbo.Orders
(
EmployeeID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX OrderDate ON dbo.Orders
(
OrderDate
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShippedDate ON dbo.Orders
(
ShippedDate
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShippersOrders ON dbo.Orders
(
ShipVia
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShipPostalCode ON dbo.Orders
(
ShipPostalCode
) ON [PRIMARY]
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Customers FOREIGN KEY
(
CustomerID
) REFERENCES dbo.Customers
(
CustomerID
)
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Employees FOREIGN KEY
(
EmployeeID
) REFERENCES dbo.Employees
(
EmployeeID
)
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Shippers FOREIGN KEY
(
ShipVia
) REFERENCES dbo.Shippers
(
ShipperID
)
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.[Order Details] WITH NOCHECK ADD CONSTRAINT
FK_Order_Details_Orders FOREIGN KEY
(
OrderID
) REFERENCES dbo.Orders
(
OrderID
)
GO
COMMIT
AMB
"Hardy Wang" wrote:

> Hi,
> I have a table with a column named ID as primary key, this column has
> the identity property. This ID is referenced by some other tables as forei
gn
> key.
> Is there a way, I can use "alter table alter ID int not null...." TSQ
L
> to remove this identity property?
> Thanks!
> --
> WWW: http://hardywang.1accesshost.com
> ICQ: 3359839
> yours Hardy
>
>

remove additional sql server named instance

Hi
I need to remove an additional named instance of sql server. It has nothing running against it and no user databases have been created on this instance
I intend to use ADD/REMOVE Programs but I am nervous that I will be prompted with a whole series of "such and such file is being used by another program do you still wish to remove" and because we are running an actual default instance used as a live production server (with two mission critical apps) I need to know what the best way to remove the named instance and are there any risks/precautions I need to make before it's removed
Another thing I noticed was that the default instance is 39MB and the named instance is 107MB. Any reason why the big difference in size
Thanks!!
ShelleyAdd/remove programs is the correct way to remove the unnecessary named
instance...
interesting question about the size. I assume you're getting that number
from add/remove? I've never really looked at that info since I would
normally get my sizing info dirrectly from SQL. I would guess that the
difference might come from space used in system databases such as master and
msdb but I really am just guessing. You might look at the size differences
between the system db's to see if that accounts for it...
--
Brian
"shelley" <anonymous@.discussions.microsoft.com> wrote in message
news:649E12B5-B0E2-4D64-8332-491C1E8D9920@.microsoft.com...
> Hi!
> I need to remove an additional named instance of sql server. It has
nothing running against it and no user databases have been created on this
instance.
> I intend to use ADD/REMOVE Programs but I am nervous that I will be
prompted with a whole series of "such and such file is being used by another
program do you still wish to remove" and because we are running an actual
default instance used as a live production server (with two mission critical
apps) I need to know what the best way to remove the named instance and are
there any risks/precautions I need to make before it's removed?
> Another thing I noticed was that the default instance is 39MB and the
named instance is 107MB. Any reason why the big difference in size?
> Thanks!!!
> Shelley|||Brian,
I wasn't around for the installation of named instance and when I try to start up the service for the this instance I receive the message: Remove SQL Server Instance: Could not start the MSSQL$MCDERMOTT service on the local computer. Error 3: The system could not find the path specified.
So I'm not sure where the 107MB is located either. When I run the add/remove will I be prompted about the deletion of shared files. Should I say 'No' to deleting all of them? I do not want anything to impact our default production instance.
Thanks!
Shelley

Remote Setup on cluster node fail

Hi,

I've tried to install SQL Server 2005 September CTP as the first named instance on a Windows Server 2003 Enterprise Edition Cluster.

During the setup process a scheduled task is created on the second cluster node, but this task is not able to run.

I received this error message in the scheduled task log file:
0x80070005: Access is denied

The user account I'm using to install SQL Server is member of the administrator on all cluster nodes, it has also the permission to log on as a service and as a batch job. The user is also permitted to access the computer from the network.

Thanks in advance for the help!

Regards,
ClausThis might be caused by a known Windows bug. Task Scheduler service fails to launch setup on a remote node even if Task Scheduler is running. If someone is logged into remote node, they risk of hitting this Windows bug. Thefore log off any Terminal Server sessions from all remote nodes before you start setup. |||Hi Yim,

Yes! That's it - i logged of from the second cluster node and the installation completes successful!

Thanks a lot!

Remote Server into a named Instance of SQL Server 2005

We are setting up a database server with 2 instances of SQL Server 2005. One for the default and another for Reporting Service. I have them installed but when I try to a registration from another Server for the Reporting Service Instance I can not get in. I am using the sa userid and password, the same one that works when I am on the server but I get an error that says Remote Server connections may not be allowed. When I go to properties the Allow remote connections box is checked. Anyone know what else I need to do to get to the named instance?If you have another instance on the server it probably uses not the default port. If you have chosen another port than the default 1433 for the named instance, you should either start SQL Browser (which will handle request and will forward to the appropiate port on the server) or you directly name it within your connecting application by using the following syntax:

Servername\InstanceName,portnumber.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Remote instance not listed in Available Servers in Enterprise Manager

When using the register available servers in Enterprise Manager, the
named instances on a remote machine are not listed. The unnamed
instance is listed and will register successfully.
I have tried manually typing the names of the remote NAMED instances;
then tried to authenticate and get the message "SQL server does not
exist or access denied".
Local machine; SQL Server 2000 SP4 (Developer Edition) installed.
2 Instances
Unnamed
DEV\DB1
On the remote machine SQL Server 2000 (MSDE Edition) installed
3 Instances;
Unnamed
REM\DB1
REM\DB2
Both machines are on the same domain. All SQL server instances are
running.
I have also used the svrnetcn.exe tool to make sure the Named Pipes
and TCP/IP protocols are enabled. The port on the TCP/IP protocol has
been changed to 1433 on all remote instances. (both machines were
rebooted)
What else can I check ?
Thanks
D> I have also used the svrnetcn.exe tool to make sure the Named Pipes
> and TCP/IP protocols are enabled. The port on the TCP/IP protocol has
> been changed to 1433 on all remote instances. (both machines were
> rebooted)
Each instance on a machine must listen on a unique TCP port. Consequently,
all of the instances on your REM machine can't be listening on the same 1433
port. Check the errorlog file for those instances for related TCP port
binding errors and change the named instance ports, if needed.
The normal configuration is that the default (unnamed) instance listens on
1433 and named instance ports are determined dynamically. When a client
connects to a named instance, it firsts gets a list of running named
instances via a UDP 1434 request and then connects to the TCP port of the
specified instance. Make sure your firewall allows TCP 1433, UDP 1434 and
TCP traffic on the named instance ports. See
http://support.microsoft.com/kb/287932
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DM" <dmcb73@.gmail.com> wrote in message
news:1190197075.363594.146870@.22g2000hsm.googlegroups.com...
> When using the register available servers in Enterprise Manager, the
> named instances on a remote machine are not listed. The unnamed
> instance is listed and will register successfully.
> I have tried manually typing the names of the remote NAMED instances;
> then tried to authenticate and get the message "SQL server does not
> exist or access denied".
> Local machine; SQL Server 2000 SP4 (Developer Edition) installed.
> 2 Instances
> Unnamed
> DEV\DB1
> On the remote machine SQL Server 2000 (MSDE Edition) installed
> 3 Instances;
> Unnamed
> REM\DB1
> REM\DB2
> Both machines are on the same domain. All SQL server instances are
> running.
> I have also used the svrnetcn.exe tool to make sure the Named Pipes
> and TCP/IP protocols are enabled. The port on the TCP/IP protocol has
> been changed to 1433 on all remote instances. (both machines were
> rebooted)
> What else can I check ?
> Thanks
> D
>

Remote connections to named instances fails on SQL 2005 cluster

I have a new SQL Server 2005 cluster with multiple named instances of SQL
Server 2005 running. From any cluster machine, I can connect to any instanc
e
of SQL Server running in the cluster. From remote machines, I can connect t
o
the default instance of SQL Server, but I can not connect to any named
instances. Help?
+ SQL Browser is running on one physical machine in the cluster
+ Surface Area Configuration has "remote clients" enabled for all instances
+ Named pipes and TCP/IP are enabled
+ DTC is running on the cluster. It is configured to allow network access
on each physical machine.
+ Windows firewall is disabled. Remote machines on same subnet as cluster
machines.
+ Client machine can ping virtual servers?
After waiting a few hours, remote connections now work
?
"Bill Q" wrote:

> I have a new SQL Server 2005 cluster with multiple named instances of SQL
> Server 2005 running. From any cluster machine, I can connect to any insta
nce
> of SQL Server running in the cluster. From remote machines, I can connect
to
> the default instance of SQL Server, but I can not connect to any named
> instances. Help?
> + SQL Browser is running on one physical machine in the cluster
> + Surface Area Configuration has "remote clients" enabled for all instance
s
> + Named pipes and TCP/IP are enabled
> + DTC is running on the cluster. It is configured to allow network access
> on each physical machine.
> + Windows firewall is disabled. Remote machines on same subnet as cluster
> machines.
> + Client machine can ping virtual servers

Remote Connections Enabled, but I still get error that Remote is not configured - Sql 2005

Fellow Devs,

I have an instance of SQL Server Express 2005 running on another box and I have Remote Connections enabled over both TCP/IP and Named Pipes, but on my other box I keep getting the error that the server does not accept Remote Connections.

Any ideas why this might be happening? Is there some other configuration?

start server configuration manager

click on protocols > TCP/IP and properties

go to IP addresses and under IPALL remove everythink from TCP dynamic port , under TCP Port enter port you want to use like 1433

save all changes restart service and should work

|||and if you have windows firewall on you have to allow to accept connection on this port|||

I don't see that option in my server configuration manager. I just see "File Server", "Application Server" and "Remote Access/VPN Server". Where do I modify thse settings?

Thanks!

|||

1) open SQL Server configuration manager
2) on the left under "Sql server 2005 network configuration" click on protocols for SQL
3) on right side right click TCP/IP > properties and tab IP addresses

|||

I was having the same problem and I followed your instructions, but now VS doesn't even detect the sql server. Any solutions?

Thanks.

|||

Swackhammer1:

I was having the same problem and I followed your instructions, but now VS doesn't even detect the sql server. Any solutions?

Thanks.

If VS is not detecting the SQL Server it may mean your SQL Server service maybe off. You may want to download the advanced version from the link below if not first get the eval and then spend $33 to get the developer edition. Hope this helps.

http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx

|||

looool what kind of solution is that ? doesn't work pay $33.

here is an article explaining step by step what you can do to enable remote connections - pretty much same idea i gave you , but when you will follow it must work

|||You have given the original poster very low level usually not adviced connection to SQL Server and it is not working, Express is best used for small company intranet hosted application nothing more. I have used SQL Server since 1998 and I have not got the need to connect to SQL Server on those layers. So mine will cost after 180 days but it is pain free.|||

i saw many of your posts and i KNOW that you work with SQL for long time and you know about it much more than i do.
I agree that SQL express is perfect for small project, intranet & for development and i believe that is what this person needs - when someone ask how to enable remote connections in SQL express- my guess would be that he/she doesn't work for BIG corporation that has billions of transaction / day

My point is that there is no reason to buy anything cos EXPRESS edition can work perfectly all you need is to spend few minutes with it and make the setup + maybe change firewall settings.

|||Yes but connecting to SQL Server through TCP port is not good advice because there is also the known UDP port and two others Microsoft admit to have reserved but only give to customers as needed which opens you up to known security issues. Microsoft was like Oracle selling the developer edition for hundreds we asked for the lower price for access and got it. Fighting with Express eats into development time.|||Ok. Here's the thing. I'm using SQL EXPRESS and VS 2005 on my machine, but the company I'm developing for has the full SQL SERVER 2005. Before I changed the tcp/ip setting, I could see my server in VS. After changing the settings though, it no longer shows up. I do have the service running. I double checked.|||OK. I decided to get the trial version of SQL SERVER 2005 for now. Let's see how that works out.|||

The links below from Microsoft covers most of what you need and some of what I have been trying to explain in details. Hope this helps.

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

|||

Caddre - very good links :)

BTWSwackhammer1 where in VS you can not see your SQL server ? in databse explorer when you click "add connection" ? if that is the problem just enter ".\SQLEXPRESS" in server name field or IP address of you server or "YOUR_COMPUTER_NEME\SQLEXPRESS"

remote connection error 26

I receive an error 26 "Error locating server/instance specified" when trying
to connect to a named instance of sql2005 through sql server mgmt studio (i
can connect locally just not from a remote pc). I can also connect via the
old query analyzer to the remote sql2005 server.
I have enabled remote connections in SQL on the server and my firewall is
allowing traffic through the port specified in the asconfig\msmdredir.ini
since I was able to telnet to it.
Also, the sqlbrowser service is running.
Any suggestions?
Are you using dynamic ports?
Start SQL Server Configuration Manager->Protocols for
XX->TCP/IP->Properties->IP Addresses->IPAll dynamic ports
If you are try specifying the dynamic port number in the Server Name fileld
like this:
<server>\sqlexpress,<portnumber>
in mgmt studio
"matt" wrote:

> I receive an error 26 "Error locating server/instance specified" when trying
> to connect to a named instance of sql2005 through sql server mgmt studio (i
> can connect locally just not from a remote pc). I can also connect via the
> old query analyzer to the remote sql2005 server.
> I have enabled remote connections in SQL on the server and my firewall is
> allowing traffic through the port specified in the asconfig\msmdredir.ini
> since I was able to telnet to it.
> Also, the sqlbrowser service is running.
> Any suggestions?
|||Thanks for your suggestion. Yes I tried appending the port number to the
server\instance with no success.
What I ended up doing was to create another instance of SQL 2005 as a test
and I was able to connect to that without any problems.
I have a suspicion that one of the recent MS updates (security and/or
MSXML6) may have wrecked my orignal instance somehow since I have seen a
similar posting (although for older patches) elsewhere on the web and I did
not have any problems until after this past set of updates.
"Axel" wrote:
[vbcol=seagreen]
> Are you using dynamic ports?
> Start SQL Server Configuration Manager->Protocols for
> XX->TCP/IP->Properties->IP Addresses->IPAll dynamic ports
> If you are try specifying the dynamic port number in the Server Name fileld
> like this:
> <server>\sqlexpress,<portnumber>
> in mgmt studio
>
> "matt" wrote:

remote connection error 26

I receive an error 26 "Error locating server/instance specified" when trying
to connect to a named instance of sql2005 through sql server mgmt studio (i
can connect locally just not from a remote pc). I can also connect via the
old query analyzer to the remote sql2005 server.
I have enabled remote connections in SQL on the server and my firewall is
allowing traffic through the port specified in the asconfig\msmdredir.ini
since I was able to telnet to it.
Also, the sqlbrowser service is running.
Any suggestions?Are you using dynamic ports?
Start SQL Server Configuration Manager->Protocols for
XX->TCP/IP->Properties->IP Addresses->IPAll dynamic ports
If you are try specifying the dynamic port number in the Server Name fileld
like this:
<server>\sqlexpress,<portnumber>
in mgmt studio
"matt" wrote:

> I receive an error 26 "Error locating server/instance specified" when tryi
ng
> to connect to a named instance of sql2005 through sql server mgmt studio (
i
> can connect locally just not from a remote pc). I can also connect via th
e
> old query analyzer to the remote sql2005 server.
> I have enabled remote connections in SQL on the server and my firewall is
> allowing traffic through the port specified in the asconfig\msmdredir.ini
> since I was able to telnet to it.
> Also, the sqlbrowser service is running.
> Any suggestions?|||Thanks for your suggestion. Yes I tried appending the port number to the
server\instance with no success.
What I ended up doing was to create another instance of SQL 2005 as a test
and I was able to connect to that without any problems.
I have a suspicion that one of the recent MS updates (security and/or
MSXML6) may have wrecked my orignal instance somehow since I have seen a
similar posting (although for older patches) elsewhere on the web and I did
not have any problems until after this past set of updates.
"Axel" wrote:
[vbcol=seagreen]
> Are you using dynamic ports?
> Start SQL Server Configuration Manager->Protocols for
> XX->TCP/IP->Properties->IP Addresses->IPAll dynamic ports
> If you are try specifying the dynamic port number in the Server Name filel
d
> like this:
> <server>\sqlexpress,<portnumber>
> in mgmt studio
>
> "matt" wrote:
>