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:
>

Determin the order of triggers,

Hi all,
Can one determine the order of triggers, or set the order of triggers. I
have a two insert triggers, generally I would like one trigger to fire first
all the tme. Is this possible.
RobertYou can specify which trigger fires first (or last) using
sp_settriggerorder. See the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Bravery" <me@.u.com> wrote in message
news:%23nKBoCWMGHA.3408@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> Can one determine the order of triggers, or set the order of triggers. I
> have a two insert triggers, generally I would like one trigger to fire
> first
> all the tme. Is this possible.
> Robert
>|||You can set the first and the last trigger, i you have less than three
that shouldn=B4t be the problem:
sp_settriggerorder=20
HTH, Jens Suessmeyer.|||Hi
u can decide the order of triggers by the help of a Stored Procedure named
'sp_settriggerorder'.The syntax is as follows:
exec sp_settriggerorder <triggername>,<order>,'<operation>'
For Example:
exec sp_settriggerorder trig_Customer,first,'Insert'.
For more Info,log on to
http://www.sqlservercentral.com/col...erswhatsnew.asp
--
Regards
Ashish Narang
AIM: ashishnarang02
YIM: ashish_jsn@.yahoo.com
Mobile: +919820625179
"Jens" wrote:

> You can set the first and the last trigger, i you have less than three
> that shouldn′t be the problem:
> sp_settriggerorder
> HTH, Jens Suessmeyer.
>|||Thanks to all, for helping and poinmting in the right direction
Robert
"Robert Bravery" <me@.u.com> wrote in message
news:%23nKBoCWMGHA.3408@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> Can one determine the order of triggers, or set the order of triggers. I
> have a two insert triggers, generally I would like one trigger to fire
first
> all the tme. Is this possible.
> Robert
>

Determin start and end date of a month

Hi all,
does anyone know of a way of dynamically determining the start and end date
of a month.
i.e. if i pass 15th March as a parameter, I can determine that the start
date is 1st March (which is obvious) and the end date is 31st March -- this
is the key part!
Many thanks
ImmyImmy,
Hints:
You know what is the 1st day of the NEXT month, too.
So you date (add) minus 1 day...
HTH,
Robert.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:OBf1GoFRGHA.252@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> does anyone know of a way of dynamically determining the start and end
> date of a month.
> i.e. if i pass 15th March as a parameter, I can determine that the start
> date is 1st March (which is obvious) and the end date is 31st March --
> this is the key part!
> Many thanks
> Immy
>|||Rob... Yes, this I know... but it doesnt get me around the problem.
Basically, I am creating a procedure that will add information into a table
about 'A' given day... they day could be any day and will run daily.
Thus I need to be able to determine that the current day is say... 21st Feb
and that it's end date is 28th/289th etc.. so the trick is that come the 1st
of March, it will have different end dates to Feb... and then of course leap
years... etc...
"news.microsoft.com" <robe_2k5 *at*** hotmail.co.uk> wrote in message
news:evVLZuFRGHA.1416@.TK2MSFTNGP12.phx.gbl...
> Immy,
> Hints:
> You know what is the 1st day of the NEXT month, too.
> So you date (add) minus 1 day...
> HTH,
> Robert.
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:OBf1GoFRGHA.252@.TK2MSFTNGP10.phx.gbl...
>|||A calendar table would help here and with all sorts of Date problems.
http://www.aspfaq.com/show.asp?id=2519
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:e7OUSzFRGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Rob... Yes, this I know... but it doesnt get me around the problem.
> Basically, I am creating a procedure that will add information into a
> table about 'A' given day... they day could be any day and will run daily.
> Thus I need to be able to determine that the current day is say... 21st
> Feb and that it's end date is 28th/289th etc.. so the trick is that come
> the 1st of March, it will have different end dates to Feb... and then of
> course leap years... etc...
>
> "news.microsoft.com" <robe_2k5 *at*** hotmail.co.uk> wrote in message
> news:evVLZuFRGHA.1416@.TK2MSFTNGP12.phx.gbl...
>|||THATS the BAD BOY!!!
Actually - I am creating a calendar system.. hence the question. Many thanks
Immy
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:OXCVv%23FRGHA.1576@.tk2msftngp13.phx.gbl...
>A calendar table would help here and with all sorts of Date problems.
> http://www.aspfaq.com/show.asp?id=2519
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:e7OUSzFRGHA.1204@.TK2MSFTNGP12.phx.gbl...
>|||Immy:

> Rob... Yes, this I know... but it doesnt get me around the problem.
Perhaps I'm misunderstanding.

> and that it's end date is 28th/289th etc.. so the trick is that come the
> 1st of March, it will have different end dates to Feb... and then of
> course leap years... etc...
Using the technique I have already hinted at, the intricacies of the
calendar (including leap years) would all be handled by the server.

> Basically, I am creating a procedure that will add information into a
> table about 'A' given day... they day could be any day and will run daily.
OK, here is an example for you:
--DROP TABLE #Test
GO
CREATE TABLE #Test (SomeDay DATETIME NOT NULL)
GO
SET DATEFORMAT YMD
INSERT #Test (SomeDay) VALUES ('2005-02-01')
INSERT #Test (SomeDay) VALUES ('2004-02-01')
INSERT #Test (SomeDay) VALUES ('2003-02-01')
INSERT #Test (SomeDay) VALUES ('2002-02-01')
INSERT #Test (SomeDay) VALUES ('2001-02-01')
INSERT #Test (SomeDay) VALUES ('2000-02-01')
INSERT #Test (SomeDay) VALUES ('1999-02-01')
INSERT #Test (SomeDay) VALUES ('1999-03-02')
INSERT #Test (SomeDay) VALUES ('1999-04-03')
INSERT #Test (SomeDay) VALUES ('1999-05-04')
INSERT #Test (SomeDay) VALUES ('1999-06-05')
INSERT #Test (SomeDay) VALUES ('1999-08-31')
GO
SET DATEFORMAT YMD
SELECT SomeDay,
LastDayOfMonthOfSomeDay = DATEADD(d, -1,
(/* This expression is the FIRST day of the FOLLOWING month */
CONVERT(DATETIME, CAST(YEAR(DATEADD(m, 1, SomeDay)) AS CHAR(4)) + '/'
+ CAST(MONTH(DATEADD(m, 1, SomeDay)) AS VARCHAR(2)) + '/1')
/**/)
)
FROM
#Test
Robert
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:e7OUSzFRGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Basically, I am creating a procedure that will add information into a
> table about 'A' given day... they day could be any day and will run daily.
> Thus I need to be able to determine that the current day is say... 21st
> Feb and that it's end date is 28th/289th etc.. so the trick is that come
> the 1st of March, it will have different end dates to Feb... and then of
> course leap years... etc...
>
> "news.microsoft.com" <robe_2k5 *at*** hotmail.co.uk> wrote in message
> news:evVLZuFRGHA.1416@.TK2MSFTNGP12.phx.gbl...
>|||Glad I could help.
I'm not sure that I like being called a BAD BOY in this newsgroup though.
:-)
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:OPw9wDGRGHA.1576@.tk2msftngp13.phx.gbl...
> THATS the BAD BOY!!!
> Actually - I am creating a calendar system.. hence the question. Many
> thanks
> Immy
> "Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
> news:OXCVv%23FRGHA.1576@.tk2msftngp13.phx.gbl...|||Was referring to the solution.. not u! ;)
Cheers
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:%23t5IcIGRGHA.336@.TK2MSFTNGP12.phx.gbl...
> Glad I could help.
> I'm not sure that I like being called a BAD BOY in this newsgroup though.
> :-)
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:OPw9wDGRGHA.1576@.tk2msftngp13.phx.gbl...
>|||On Fri, 10 Mar 2006 16:09:19 -0000, Immy wrote:

>Rob... Yes, this I know... but it doesnt get me around the problem.
>Basically, I am creating a procedure that will add information into a table
>about 'A' given day... they day could be any day and will run daily.
>Thus I need to be able to determine that the current day is say... 21st Feb
>and that it's end date is 28th/289th etc.. so the trick is that come the 1s
t
>of March, it will have different end dates to Feb... and then of course lea
p
>years... etc...
Hi Immy,
Here's yet a third way to do this (using the test data kindly provided
by Robert):
SELECT SomeDay,
DATEADD(month,
DATEDIFF(month, '20050101', SomeDay),
'20050131') AS LastDayOfMonthOfSomeDay
FROM #Test
The trick here is to calculate the number of full months between your
date and the first month of a fixed starting point, then add that number
to the last day of the same month. You are free to choose any date
within the range of allowed datetimes as starting point, but make sure
to pick a month with 31 days!
Hugo Kornelis, SQL Server MVP

determin name of primary column

given the name of any table how can i programmatically get the name of the primary key column?

You can use the INFORMATION_SCHEMA.TABLE_CONSTRAINTS view like:

SELECT tc.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc

WHERE tc.TABLE_SCHEMA = 'dbo'

and tc.TABLE_NAME = '<your_table>'

and tc.CONSTRAINT_TYPE = 'PRIMARY KEY';

sql

Determin if this is Standard or Ent edition

How do I tell if an install of sql server is standard or enterprise edition?
moondaddy@.nospam.nospamselect serverproperty('edition')
Andrew J. Kelly SQL MVP
"moondaddy" <moondaddy@.nospam.nospam> wrote in message
news:%23zNH8yWOGHA.348@.TK2MSFTNGP11.phx.gbl...
> How do I tell if an install of sql server is standard or enterprise
> edition?
> --
> moondaddy@.nospam.nospam
>