Determin whether port number assigned dynamically or hardcoded

I undstand that if you choose to dynamically assign a port number to an
instance this is a once only operation performed the first time that instance
is started and from then on it always attepts to use that port number.
Having just started with a new company I'm trying to work out if instance
port numbers were assigned dynamically or hardcoded during install as this
affect the client connection properties for connections (if hardcoded each
client needs to be configured with the port number rather than allowing an
instance to be resolved to a port).
Is there a regkey or anything that says how ports were assigned?
Steve
Steve Morgan
MCDBA
Snr Production DBA
If you are looking for a registry key to determine if SQL
Server is listening on the default port, it's located at:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\SuperSocketNetLib\Tcp
That will give you the listening port.
You can also get the information from the SQL Server log.
You can also get the information using various network
commands, tools.
-Sue
On Tue, 30 Nov 2004 08:55:09 -0800, Steve Morgan
<SteveMorgan@.discussions.microsoft.com> wrote:

>I undstand that if you choose to dynamically assign a port number to an
>instance this is a once only operation performed the first time that instance
>is started and from then on it always attepts to use that port number.
>Having just started with a new company I'm trying to work out if instance
>port numbers were assigned dynamically or hardcoded during install as this
>affect the client connection properties for connections (if hardcoded each
>client needs to be configured with the port number rather than allowing an
>instance to be resolved to a port).
>Is there a regkey or anything that says how ports were assigned?
>Steve
|||Hi Sue
Thnx for the reply but thats not my question - I know how to check the port
numbers currently being used.
What I need to work out is whether during the install the option to
dynamically assign instance port numbers was choosen or if they were
pre-choosen & hardcoded.
According to a technet article on connectivity problems this decision
affects whether on the client machine you can use the <server>\<instance
name> in your connection string or whether you have to use <server>\<port
number>
I'm having intermittent connection problems usine <server>\<instance name>
and want to know if the install port decision could be the root cause.
Steve
"Sue Hoegemeier" wrote:

> If you are looking for a registry key to determine if SQL
> Server is listening on the default port, it's located at:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\SuperSocketNetLib\Tcp
> That will give you the listening port.
> You can also get the information from the SQL Server log.
> You can also get the information using various network
> commands, tools.
> -Sue
> On Tue, 30 Nov 2004 08:55:09 -0800, Steve Morgan
> <SteveMorgan@.discussions.microsoft.com> wrote:
>
>
|||Check the same area in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\<IYournstanceName>\MSSQLServer\SuperSocketN etLib\Tcp
The combination of the values for TCPDynamicPorts and
TCPPort will help you determine the setting. You can find
them outlined in the following article:
http://support.microsoft.com/?id=823938
-Sue
On Wed, 1 Dec 2004 02:11:02 -0800, Steve Morgan
<SteveMorgan@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hi Sue
>Thnx for the reply but thats not my question - I know how to check the port
>numbers currently being used.
>What I need to work out is whether during the install the option to
>dynamically assign instance port numbers was choosen or if they were
>pre-choosen & hardcoded.
>According to a technet article on connectivity problems this decision
>affects whether on the client machine you can use the <server>\<instance
>name> in your connection string or whether you have to use <server>\<port
>number>
>I'm having intermittent connection problems usine <server>\<instance name>
>and want to know if the install port decision could be the root cause.
>Steve
>
>"Sue Hoegemeier" wrote:

Determin whether port number assigned dynamically or hardcoded

I undstand that if you choose to dynamically assign a port number to an
instance this is a once only operation performed the first time that instanc
e
is started and from then on it always attepts to use that port number.
Having just started with a new company I'm trying to work out if instance
port numbers were assigned dynamically or hardcoded during install as this
affect the client connection properties for connections (if hardcoded each
client needs to be configured with the port number rather than allowing an
instance to be resolved to a port).
Is there a regkey or anything that says how ports were assigned?
Steve
Steve Morgan
MCDBA
Snr Production DBAIf you are looking for a registry key to determine if SQL
Server is listening on the default port, it's located at:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\SuperSocketNet
Lib\Tcp
That will give you the listening port.
You can also get the information from the SQL Server log.
You can also get the information using various network
commands, tools.
-Sue
On Tue, 30 Nov 2004 08:55:09 -0800, Steve Morgan
<SteveMorgan@.discussions.microsoft.com> wrote:

>I undstand that if you choose to dynamically assign a port number to an
>instance this is a once only operation performed the first time that instan
ce
>is started and from then on it always attepts to use that port number.
>Having just started with a new company I'm trying to work out if instance
>port numbers were assigned dynamically or hardcoded during install as this
>affect the client connection properties for connections (if hardcoded each
>client needs to be configured with the port number rather than allowing an
>instance to be resolved to a port).
>Is there a regkey or anything that says how ports were assigned?
>Steve|||Hi Sue
Thnx for the reply but thats not my question - I know how to check the port
numbers currently being used.
What I need to work out is whether during the install the option to
dynamically assign instance port numbers was choosen or if they were
pre-choosen & hardcoded.
According to a technet article on connectivity problems this decision
affects whether on the client machine you can use the <server>\<instance
name> in your connection string or whether you have to use <server>\<port
number>
I'm having intermittent connection problems usine <server>\<instance name>
and want to know if the install port decision could be the root cause.
Steve
"Sue Hoegemeier" wrote:

> If you are looking for a registry key to determine if SQL
> Server is listening on the default port, it's located at:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\SuperSocketN
etLib\Tcp
> That will give you the listening port.
> You can also get the information from the SQL Server log.
> You can also get the information using various network
> commands, tools.
> -Sue
> On Tue, 30 Nov 2004 08:55:09 -0800, Steve Morgan
> <SteveMorgan@.discussions.microsoft.com> wrote:
>
>|||Check the same area in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
Server\<IYournstanceName>\MSSQLServer\SuperSocketNetLib\Tcp
The combination of the values for TCPDynamicPorts and
TCPPort will help you determine the setting. You can find
them outlined in the following article:
http://support.microsoft.com/?id=823938
-Sue
On Wed, 1 Dec 2004 02:11:02 -0800, Steve Morgan
<SteveMorgan@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hi Sue
>Thnx for the reply but thats not my question - I know how to check the port
>numbers currently being used.
>What I need to work out is whether during the install the option to
>dynamically assign instance port numbers was choosen or if they were
>pre-choosen & hardcoded.
>According to a technet article on connectivity problems this decision
>affects whether on the client machine you can use the <server>\<instance
>name> in your connection string or whether you have to use <server>\<port
>number>
>I'm having intermittent connection problems usine <server>\<instance name>
>and want to know if the install port decision could be the root cause.
>Steve
>
>"Sue Hoegemeier" wrote:
>

Detach and Attach functions in SQL Server 2005

Hi,

I'm trying to port my ASP.NET web application to the production system.

I'm connection to the SQL Server 2005 instance on my hosting server via CTP. I've uploaded the .mdf and .ldf files of my DB via FTP to the hosting server, and then tried to attach using this command:

use master gosp_attach_db'tgp','F:\webspace\disk20\db\TGP.mdf','F:\webspace\disk20\db\TGP_log.ldf' go

but then there is an error (obviously) stating that I don't have permissions to create a database in database master.

I must admit that I'm prettycluelessin this area. My hostingservicesalready created a "place holder" for my database (tgp), but I don't know how to proceed from here in order attach the database files in the production environment. Is this something I can do myself, or must I involve the hosting services?

Thanks,

Alon

sp_attach_db requires the same permissions as CREATE DATABASE - so you're likely not going to be able to do this (if so, let me know who your host is - I could use the free/extra space they'd let me set up *grin*).

You'll either need to contact them and have them hook up your DB, or look into using user-instance/attachable SQL Express functionality.|||

You have two options just backup your database and use management studio to restore your database on the host SQL Server after you have registered the host SQL Server in your management studio. In the backup and restore wizard choose the restore from device option. The other option is try the thread below for Attach database code modify it for your needs and use it. Hope this helps.

http://forums.asp.net/thread/981274.aspx

|||

Thak you for your reply.

I tried to backup/restore, but when I tried to point to the location of the backup file (on the remote server), got the error message that I'm not authorized... I've just sent a request from the hosting firm to handle this.

I have a general question: what are the guidelines when moving from the test/development system to the production system? I couldn't find any article summarizing the process.

Thank you,

Alon

|||You should only use a company that gives you access to management studio so you can use the backup and restore wizard because if you don't backup your database they will charge you to back it up. Here in the US most hosting company will give you the access. Hope this helps.|||

Yep, this helps

Alon