I'm confused. When I create an ODBC connection to a remote SQL server 2000
that uses a nonstandard TCP port, I have to give the port number to ODBC.
Which makes sense.
I just set up an Enterprise Manager "SQL Server Registration" for the same
remote server, and I gave it the IP address of the server computer, and
nowhere did it ask me for the port number. I thought "I won't be able to
connect unless I can tell EM what port to talk on".
But EM is able to connect to the server. Does it try all 65535 ports until
it finds a SQL server?
I'm sure the remote server is listening on the nonstandard port number I
gave; the server log says so.
Query Analyzer connects also. How does this work? What information am I
missing here so I can understand what's going on?
Thanks. I appreciate any help.
David WalkerHi
Wonders of Port 1434.
From BOL:
"When the SQL Server 2000 client Net-Libraries connect to an instance of SQL
Server 2000, only the network name of the computer running the instance and
the instance name are required. When an application requests a connection to
a remote computer, Dbnetlib.dll opens a connection to UDP port 1434 on the
computer network name specified in the connection. All computers running an
instance of SQL Server 2000 listen on this port. When a client Dbnetlib.dll
connects to this port, the server returns a packet listing all the instances
running on the server. For each instance, the packet reports the server
Net-Libraries and network addresses the instance is listening on. After the
Dbnetlib.dll on the application computer receives this packet, it chooses a
Net-Library that is enabled on both the application computer and on the
instance of SQL Server, and makes a connection to the address listed for
that Net-Library in the packet."
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <none@.none.com> wrote in message
news:uelxNLeFFHA.628@.TK2MSFTNGP15.phx.gbl...
> I'm confused. When I create an ODBC connection to a remote SQL server
2000
> that uses a nonstandard TCP port, I have to give the port number to ODBC.
> Which makes sense.
> I just set up an Enterprise Manager "SQL Server Registration" for the same
> remote server, and I gave it the IP address of the server computer, and
> nowhere did it ask me for the port number. I thought "I won't be able to
> connect unless I can tell EM what port to talk on".
> But EM is able to connect to the server. Does it try all 65535 ports
until
> it finds a SQL server?
> I'm sure the remote server is listening on the nonstandard port number I
> gave; the server log says so.
> Query Analyzer connects also. How does this work? What information am I
> missing here so I can understand what's going on?
> Thanks. I appreciate any help.
>
> David Walker|||Ah. Thanks. I see that in Client Network Utility, there's an alias for
the IP address with a comma and the port number.
How is the option "hide SQL server" related? I read that it changes the
UDP port to 2433 or something, but it actually seemed to change the TCP
port that I see in the Server Network Utility from my hand-picked port
to 2433 or something.
This part is kind of confusing. If you can explain the Hide part, it
would be great. I would like the SQL server to not listen on that port
and respond to just anyone who asks!
Thanks for any further enlightenment you can give.
David Walker
"Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
news:#nkcTBgFFHA.2928@.TK2MSFTNGP14.phx.gbl:
> Hi
> Wonders of Port 1434.
> From BOL:
> "When the SQL Server 2000 client Net-Libraries connect to an instance
> of SQL Server 2000, only the network name of the computer running the
> instance and the instance name are required. When an application
> requests a connection to a remote computer, Dbnetlib.dll opens a
> connection to UDP port 1434 on the computer network name specified in
> the connection. All computers running an instance of SQL Server 2000
> listen on this port. When a client Dbnetlib.dll connects to this port,
> the server returns a packet listing all the instances running on the
> server. For each instance, the packet reports the server Net-Libraries
> and network addresses the instance is listening on. After the
> Dbnetlib.dll on the application computer receives this packet, it
> chooses a Net-Library that is enabled on both the application computer
> and on the instance of SQL Server, and makes a connection to the
> address listed for that Net-Library in the packet."
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "DWalker" <none@.none.com> wrote in message
> news:uelxNLeFFHA.628@.TK2MSFTNGP15.phx.gbl...
> 2000
> until
>
>|||Hi
Broadcasts from clients attempting to enumerate those instances of SQL
Server present on the network are disabled when you tick the 'Hide" option.
In effect, it listens, but does not respond.
Security through obfustication does not work. A simple port scan fro ma
machine on a network fill quickly find out what is running on a machine.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <none@.none.com> wrote in message
news:uPgvdWhFFHA.628@.TK2MSFTNGP15.phx.gbl...
> Ah. Thanks. I see that in Client Network Utility, there's an alias for
> the IP address with a comma and the port number.
> How is the option "hide SQL server" related? I read that it changes the
> UDP port to 2433 or something, but it actually seemed to change the TCP
> port that I see in the Server Network Utility from my hand-picked port
> to 2433 or something.
> This part is kind of confusing. If you can explain the Hide part, it
> would be great. I would like the SQL server to not listen on that port
> and respond to just anyone who asks!
> Thanks for any further enlightenment you can give.
> David Walker
>
> "Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
> news:#nkcTBgFFHA.2928@.TK2MSFTNGP14.phx.gbl:
>
>|||Thanks. You said:
"Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
news:ujNrUhhFFHA.3032@.TK2MSFTNGP12.phx.gbl:
> Hi
> Broadcasts from clients attempting to enumerate those instances of SQL
> Server present on the network are disabled when you tick the 'Hide"
> option.
> In effect, it listens, but does not respond.
> Security through obfustication does not work. A simple port scan fro
> ma machine on a network fill quickly find out what is running on a
> machine.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
>
I know that security through obfuscation doesn't work. I wanted the
server to use TCP port 12345 (well, a different port really), and also
not broadcast its presence to clients that are looking for it -- in
effect, I was trying to force the clients to specify the exact port.
Apparently SQL server can't really do this. I found KB article 308091
which helps. I now see that there's a data port that defaults to 1433
but can be changed, and a pre-session handshake port of UDP 1434 which
apparently can't be changed, or can be changed to 2433, or something
equally vague.
And I was confused that I didn't have to (and couldn't see HOW to)
specify the exact port from Enterprise Manager. The dialog box asks for
a server; it doesn't give any clues how to specify an IP address plus a
port. Is there a way to do this?
By the way, you said that BOL says "Dbnetlib.dll opens a connection to
UDP port 1434 on the computer network name specified in the connection.
All computers running an instance of SQL Server 2000 listen on this
port.". What topic is this? I couldn't find it.
Sorry for so many questions... Thanks.
David Walker|||AND, the TCP port number given in the Client Network Utility seems a little
odd now that I think about it. You can specify *A* default port. Suppose
I connect to SQL servers on several machines, each with its SQL server
listening on a specific, different port. There's no way to pre-specify the
TCP data port for a given IP address?
I suppose that's why SQL servers listen on UDP port 1434, so they can reply
with their data port number. Seems like an odd design. The client should
be made to specify the port number if I have changed it on the server.
David|||Hi
You can, use the format:
servername, portnumber
or
ip address, portnumber
All the SQL 2000 Tools support it, as well as MDAC v2.5 and later.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <None> wrote in message
news:Xns9602EAD299831DWalker@.207.46.248.16...
> AND, the TCP port number given in the Client Network Utility seems a
little
> odd now that I think about it. You can specify *A* default port. Suppose
> I connect to SQL servers on several machines, each with its SQL server
> listening on a specific, different port. There's no way to pre-specify
the
> TCP data port for a given IP address?
> I suppose that's why SQL servers listen on UDP port 1434, so they can
reply
> with their data port number. Seems like an odd design. The client should
> be made to specify the port number if I have changed it on the server.
>
> David
>
Wednesday, March 7, 2012
EM connects to server without knowing port
I'm confused. When I create an ODBC connection to a remote SQL server 2000
that uses a nonstandard TCP port, I have to give the port number to ODBC.
Which makes sense.
I just set up an Enterprise Manager "SQL Server Registration" for the same
remote server, and I gave it the IP address of the server computer, and
nowhere did it ask me for the port number. I thought "I won't be able to
connect unless I can tell EM what port to talk on".
But EM is able to connect to the server. Does it try all 65535 ports until
it finds a SQL server?
I'm sure the remote server is listening on the nonstandard port number I
gave; the server log says so.
Query Analyzer connects also. How does this work? What information am I
missing here so I can understand what's going on?
Thanks. I appreciate any help.
David Walker
Hi
Wonders of Port 1434.
From BOL:
"When the SQL Server 2000 client Net-Libraries connect to an instance of SQL
Server 2000, only the network name of the computer running the instance and
the instance name are required. When an application requests a connection to
a remote computer, Dbnetlib.dll opens a connection to UDP port 1434 on the
computer network name specified in the connection. All computers running an
instance of SQL Server 2000 listen on this port. When a client Dbnetlib.dll
connects to this port, the server returns a packet listing all the instances
running on the server. For each instance, the packet reports the server
Net-Libraries and network addresses the instance is listening on. After the
Dbnetlib.dll on the application computer receives this packet, it chooses a
Net-Library that is enabled on both the application computer and on the
instance of SQL Server, and makes a connection to the address listed for
that Net-Library in the packet."
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <none@.none.com> wrote in message
news:uelxNLeFFHA.628@.TK2MSFTNGP15.phx.gbl...
> I'm confused. When I create an ODBC connection to a remote SQL server
2000
> that uses a nonstandard TCP port, I have to give the port number to ODBC.
> Which makes sense.
> I just set up an Enterprise Manager "SQL Server Registration" for the same
> remote server, and I gave it the IP address of the server computer, and
> nowhere did it ask me for the port number. I thought "I won't be able to
> connect unless I can tell EM what port to talk on".
> But EM is able to connect to the server. Does it try all 65535 ports
until
> it finds a SQL server?
> I'm sure the remote server is listening on the nonstandard port number I
> gave; the server log says so.
> Query Analyzer connects also. How does this work? What information am I
> missing here so I can understand what's going on?
> Thanks. I appreciate any help.
>
> David Walker
|||Ah. Thanks. I see that in Client Network Utility, there's an alias for
the IP address with a comma and the port number.
How is the option "hide SQL server" related? I read that it changes the
UDP port to 2433 or something, but it actually seemed to change the TCP
port that I see in the Server Network Utility from my hand-picked port
to 2433 or something.
This part is kind of confusing. If you can explain the Hide part, it
would be great. I would like the SQL server to not listen on that port
and respond to just anyone who asks!
Thanks for any further enlightenment you can give.
David Walker
"Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
news:#nkcTBgFFHA.2928@.TK2MSFTNGP14.phx.gbl:
> Hi
> Wonders of Port 1434.
> From BOL:
> "When the SQL Server 2000 client Net-Libraries connect to an instance
> of SQL Server 2000, only the network name of the computer running the
> instance and the instance name are required. When an application
> requests a connection to a remote computer, Dbnetlib.dll opens a
> connection to UDP port 1434 on the computer network name specified in
> the connection. All computers running an instance of SQL Server 2000
> listen on this port. When a client Dbnetlib.dll connects to this port,
> the server returns a packet listing all the instances running on the
> server. For each instance, the packet reports the server Net-Libraries
> and network addresses the instance is listening on. After the
> Dbnetlib.dll on the application computer receives this packet, it
> chooses a Net-Library that is enabled on both the application computer
> and on the instance of SQL Server, and makes a connection to the
> address listed for that Net-Library in the packet."
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "DWalker" <none@.none.com> wrote in message
> news:uelxNLeFFHA.628@.TK2MSFTNGP15.phx.gbl...
> 2000
> until
>
>
|||Hi
Broadcasts from clients attempting to enumerate those instances of SQL
Server present on the network are disabled when you tick the 'Hide" option.
In effect, it listens, but does not respond.
Security through obfustication does not work. A simple port scan fro ma
machine on a network fill quickly find out what is running on a machine.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <none@.none.com> wrote in message
news:uPgvdWhFFHA.628@.TK2MSFTNGP15.phx.gbl...
> Ah. Thanks. I see that in Client Network Utility, there's an alias for
> the IP address with a comma and the port number.
> How is the option "hide SQL server" related? I read that it changes the
> UDP port to 2433 or something, but it actually seemed to change the TCP
> port that I see in the Server Network Utility from my hand-picked port
> to 2433 or something.
> This part is kind of confusing. If you can explain the Hide part, it
> would be great. I would like the SQL server to not listen on that port
> and respond to just anyone who asks!
> Thanks for any further enlightenment you can give.
> David Walker
>
> "Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
> news:#nkcTBgFFHA.2928@.TK2MSFTNGP14.phx.gbl:
>
|||Thanks. You said:
"Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
news:ujNrUhhFFHA.3032@.TK2MSFTNGP12.phx.gbl:
> Hi
> Broadcasts from clients attempting to enumerate those instances of SQL
> Server present on the network are disabled when you tick the 'Hide"
> option.
> In effect, it listens, but does not respond.
> Security through obfustication does not work. A simple port scan fro
> ma machine on a network fill quickly find out what is running on a
> machine.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
>
I know that security through obfuscation doesn't work. I wanted the
server to use TCP port 12345 (well, a different port really), and also
not broadcast its presence to clients that are looking for it -- in
effect, I was trying to force the clients to specify the exact port.
Apparently SQL server can't really do this. I found KB article 308091
which helps. I now see that there's a data port that defaults to 1433
but can be changed, and a pre-session handshake port of UDP 1434 which
apparently can't be changed, or can be changed to 2433, or something
equally vague.
And I was confused that I didn't have to (and couldn't see HOW to)
specify the exact port from Enterprise Manager. The dialog box asks for
a server; it doesn't give any clues how to specify an IP address plus a
port. Is there a way to do this?
By the way, you said that BOL says "Dbnetlib.dll opens a connection to
UDP port 1434 on the computer network name specified in the connection.
All computers running an instance of SQL Server 2000 listen on this
port.". What topic is this? I couldn't find it.
Sorry for so many questions... Thanks.
David Walker
|||AND, the TCP port number given in the Client Network Utility seems a little
odd now that I think about it. You can specify *A* default port. Suppose
I connect to SQL servers on several machines, each with its SQL server
listening on a specific, different port. There's no way to pre-specify the
TCP data port for a given IP address?
I suppose that's why SQL servers listen on UDP port 1434, so they can reply
with their data port number. Seems like an odd design. The client should
be made to specify the port number if I have changed it on the server.
David
|||Hi
You can, use the format:
servername, portnumber
or
ip address, portnumber
All the SQL 2000 Tools support it, as well as MDAC v2.5 and later.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <None> wrote in message
news:Xns9602EAD299831DWalker@.207.46.248.16...
> AND, the TCP port number given in the Client Network Utility seems a
little
> odd now that I think about it. You can specify *A* default port. Suppose
> I connect to SQL servers on several machines, each with its SQL server
> listening on a specific, different port. There's no way to pre-specify
the
> TCP data port for a given IP address?
> I suppose that's why SQL servers listen on UDP port 1434, so they can
reply
> with their data port number. Seems like an odd design. The client should
> be made to specify the port number if I have changed it on the server.
>
> David
>
that uses a nonstandard TCP port, I have to give the port number to ODBC.
Which makes sense.
I just set up an Enterprise Manager "SQL Server Registration" for the same
remote server, and I gave it the IP address of the server computer, and
nowhere did it ask me for the port number. I thought "I won't be able to
connect unless I can tell EM what port to talk on".
But EM is able to connect to the server. Does it try all 65535 ports until
it finds a SQL server?
I'm sure the remote server is listening on the nonstandard port number I
gave; the server log says so.
Query Analyzer connects also. How does this work? What information am I
missing here so I can understand what's going on?
Thanks. I appreciate any help.
David Walker
Hi
Wonders of Port 1434.
From BOL:
"When the SQL Server 2000 client Net-Libraries connect to an instance of SQL
Server 2000, only the network name of the computer running the instance and
the instance name are required. When an application requests a connection to
a remote computer, Dbnetlib.dll opens a connection to UDP port 1434 on the
computer network name specified in the connection. All computers running an
instance of SQL Server 2000 listen on this port. When a client Dbnetlib.dll
connects to this port, the server returns a packet listing all the instances
running on the server. For each instance, the packet reports the server
Net-Libraries and network addresses the instance is listening on. After the
Dbnetlib.dll on the application computer receives this packet, it chooses a
Net-Library that is enabled on both the application computer and on the
instance of SQL Server, and makes a connection to the address listed for
that Net-Library in the packet."
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <none@.none.com> wrote in message
news:uelxNLeFFHA.628@.TK2MSFTNGP15.phx.gbl...
> I'm confused. When I create an ODBC connection to a remote SQL server
2000
> that uses a nonstandard TCP port, I have to give the port number to ODBC.
> Which makes sense.
> I just set up an Enterprise Manager "SQL Server Registration" for the same
> remote server, and I gave it the IP address of the server computer, and
> nowhere did it ask me for the port number. I thought "I won't be able to
> connect unless I can tell EM what port to talk on".
> But EM is able to connect to the server. Does it try all 65535 ports
until
> it finds a SQL server?
> I'm sure the remote server is listening on the nonstandard port number I
> gave; the server log says so.
> Query Analyzer connects also. How does this work? What information am I
> missing here so I can understand what's going on?
> Thanks. I appreciate any help.
>
> David Walker
|||Ah. Thanks. I see that in Client Network Utility, there's an alias for
the IP address with a comma and the port number.
How is the option "hide SQL server" related? I read that it changes the
UDP port to 2433 or something, but it actually seemed to change the TCP
port that I see in the Server Network Utility from my hand-picked port
to 2433 or something.
This part is kind of confusing. If you can explain the Hide part, it
would be great. I would like the SQL server to not listen on that port
and respond to just anyone who asks!
Thanks for any further enlightenment you can give.
David Walker
"Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
news:#nkcTBgFFHA.2928@.TK2MSFTNGP14.phx.gbl:
> Hi
> Wonders of Port 1434.
> From BOL:
> "When the SQL Server 2000 client Net-Libraries connect to an instance
> of SQL Server 2000, only the network name of the computer running the
> instance and the instance name are required. When an application
> requests a connection to a remote computer, Dbnetlib.dll opens a
> connection to UDP port 1434 on the computer network name specified in
> the connection. All computers running an instance of SQL Server 2000
> listen on this port. When a client Dbnetlib.dll connects to this port,
> the server returns a packet listing all the instances running on the
> server. For each instance, the packet reports the server Net-Libraries
> and network addresses the instance is listening on. After the
> Dbnetlib.dll on the application computer receives this packet, it
> chooses a Net-Library that is enabled on both the application computer
> and on the instance of SQL Server, and makes a connection to the
> address listed for that Net-Library in the packet."
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "DWalker" <none@.none.com> wrote in message
> news:uelxNLeFFHA.628@.TK2MSFTNGP15.phx.gbl...
> 2000
> until
>
>
|||Hi
Broadcasts from clients attempting to enumerate those instances of SQL
Server present on the network are disabled when you tick the 'Hide" option.
In effect, it listens, but does not respond.
Security through obfustication does not work. A simple port scan fro ma
machine on a network fill quickly find out what is running on a machine.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <none@.none.com> wrote in message
news:uPgvdWhFFHA.628@.TK2MSFTNGP15.phx.gbl...
> Ah. Thanks. I see that in Client Network Utility, there's an alias for
> the IP address with a comma and the port number.
> How is the option "hide SQL server" related? I read that it changes the
> UDP port to 2433 or something, but it actually seemed to change the TCP
> port that I see in the Server Network Utility from my hand-picked port
> to 2433 or something.
> This part is kind of confusing. If you can explain the Hide part, it
> would be great. I would like the SQL server to not listen on that port
> and respond to just anyone who asks!
> Thanks for any further enlightenment you can give.
> David Walker
>
> "Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
> news:#nkcTBgFFHA.2928@.TK2MSFTNGP14.phx.gbl:
>
|||Thanks. You said:
"Mike Epprecht \(SQL MVP\)" <mike@.epprecht.net> wrote in
news:ujNrUhhFFHA.3032@.TK2MSFTNGP12.phx.gbl:
> Hi
> Broadcasts from clients attempting to enumerate those instances of SQL
> Server present on the network are disabled when you tick the 'Hide"
> option.
> In effect, it listens, but does not respond.
> Security through obfustication does not work. A simple port scan fro
> ma machine on a network fill quickly find out what is running on a
> machine.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
>
I know that security through obfuscation doesn't work. I wanted the
server to use TCP port 12345 (well, a different port really), and also
not broadcast its presence to clients that are looking for it -- in
effect, I was trying to force the clients to specify the exact port.
Apparently SQL server can't really do this. I found KB article 308091
which helps. I now see that there's a data port that defaults to 1433
but can be changed, and a pre-session handshake port of UDP 1434 which
apparently can't be changed, or can be changed to 2433, or something
equally vague.
And I was confused that I didn't have to (and couldn't see HOW to)
specify the exact port from Enterprise Manager. The dialog box asks for
a server; it doesn't give any clues how to specify an IP address plus a
port. Is there a way to do this?
By the way, you said that BOL says "Dbnetlib.dll opens a connection to
UDP port 1434 on the computer network name specified in the connection.
All computers running an instance of SQL Server 2000 listen on this
port.". What topic is this? I couldn't find it.
Sorry for so many questions... Thanks.
David Walker
|||AND, the TCP port number given in the Client Network Utility seems a little
odd now that I think about it. You can specify *A* default port. Suppose
I connect to SQL servers on several machines, each with its SQL server
listening on a specific, different port. There's no way to pre-specify the
TCP data port for a given IP address?
I suppose that's why SQL servers listen on UDP port 1434, so they can reply
with their data port number. Seems like an odd design. The client should
be made to specify the port number if I have changed it on the server.
David
|||Hi
You can, use the format:
servername, portnumber
or
ip address, portnumber
All the SQL 2000 Tools support it, as well as MDAC v2.5 and later.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"DWalker" <None> wrote in message
news:Xns9602EAD299831DWalker@.207.46.248.16...
> AND, the TCP port number given in the Client Network Utility seems a
little
> odd now that I think about it. You can specify *A* default port. Suppose
> I connect to SQL servers on several machines, each with its SQL server
> listening on a specific, different port. There's no way to pre-specify
the
> TCP data port for a given IP address?
> I suppose that's why SQL servers listen on UDP port 1434, so they can
reply
> with their data port number. Seems like an odd design. The client should
> be made to specify the port number if I have changed it on the server.
>
> David
>
EM can't connect to server
I suddenly can't work with my Enterprise Manager.
It tells me that log in failed - Can't access user's default database.
What can I do about it?
I really need to fix it fast.
Thanks a lot
Shimon.Shimon Sim wrote:
> I suddenly can't work with my Enterprise Manager.
> It tells me that log in failed - Can't access user's default
> database.
> What can I do about it?
> I really need to fix it fast.
> Thanks a lot
> Shimon.
Connect from Query Analyzer and change the default database for the user
you are using to login from SQL EM.
sp_defaultdb 'user', 'db'
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I can't connect using QA either.
Thanks,
Shimon.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u6P6UNmYFHA.2264@.TK2MSFTNGP10.phx.gbl...
> Shimon Sim wrote:
> Connect from Query Analyzer and change the default database for the user
> you are using to login from SQL EM.
> sp_defaultdb 'user', 'db'
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Shimon Sim wrote:
> I can't connect using QA either.
> Thanks,
> Shimon.
Log in as a different user in QA... one with admin rights. Then change
the default database.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks
I connected using Visial Studio
Shimon.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u6P6UNmYFHA.2264@.TK2MSFTNGP10.phx.gbl...
> Shimon Sim wrote:
> Connect from Query Analyzer and change the default database for the user
> you are using to login from SQL EM.
> sp_defaultdb 'user', 'db'
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Hi Shimon,
Thanks for your posting!
From your descriptions, I understood you are not able to connect SQL Server
via SQL Server Enterprise Manager. Have I understood you? If I have
misunderstood your concern, please feel free to point it out.
First of all, would you please help me clarify the words "I connected using
Visial Studio"? You have said you are working wiht your Enterprise Manager?
When do you meet the error message "Can't access user's default database"?
More detailed informaiton, I believe, will make us closer to the
resolution. Please help me collecting the information by answering the
questions below
What account you are using to connect this SQL Server?
The SQL Server machine is in LAN or Internet?
You may refer the Knowledge Base article below
How to troubleshoot connectivity issues in SQL Server 2000
http://support.microsoft.com/kb/827422
BTW, if this issue is urgent, I would like to recommand you opening an
incident with PSS team direcly and there will be a dedicated Support
Professional working for you. Please be advised that contacting phone
support will be a charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w
to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others: https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Michael
Thanks for your response.
I resolved the issue.
What happen I deleted my default database. And all the users for the SQL
Server had that database as default. So every program (EM,QA) that uses
default database in order to operate was throwing errors that they can't
login to the server.
So I decided to create database project in VS that uses specifies database
for login and not default and changed default database for all the users.
Thanks again.
Shimon.
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:NIh58$oYFHA.2476@.TK2MSFTNGXA01.phx.gbl...
> Hi Shimon,
> Thanks for your posting!
> From your descriptions, I understood you are not able to connect SQL
> Server
> via SQL Server Enterprise Manager. Have I understood you? If I have
> misunderstood your concern, please feel free to point it out.
> First of all, would you please help me clarify the words "I connected
> using
> Visial Studio"? You have said you are working wiht your Enterprise
> Manager?
> When do you meet the error message "Can't access user's default database"?
> More detailed informaiton, I believe, will make us closer to the
> resolution. Please help me collecting the information by answering the
> questions below
> What account you are using to connect this SQL Server?
> The SQL Server machine is in LAN or Internet?
> You may refer the Knowledge Base article below
> How to troubleshoot connectivity issues in SQL Server 2000
> http://support.microsoft.com/kb/827422
> BTW, if this issue is urgent, I would like to recommand you opening an
> incident with PSS team direcly and there will be a dedicated Support
> Professional working for you. Please be advised that contacting phone
> support will be a charged call.
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
> http://support.microsoft.com/defaul...US;PHONENUMBERS
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a w to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/te...erview/40010469
> Others: https://partner.microsoft.com/US/te...upportoverview/
> If you are outside the United States, please visit our International
> Support page: http://support.microsoft.com/common/international.aspx
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Shimon,
You are welcome and it's great to hear you have resolved this!
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others: https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
It tells me that log in failed - Can't access user's default database.
What can I do about it?
I really need to fix it fast.
Thanks a lot
Shimon.Shimon Sim wrote:
> I suddenly can't work with my Enterprise Manager.
> It tells me that log in failed - Can't access user's default
> database.
> What can I do about it?
> I really need to fix it fast.
> Thanks a lot
> Shimon.
Connect from Query Analyzer and change the default database for the user
you are using to login from SQL EM.
sp_defaultdb 'user', 'db'
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I can't connect using QA either.
Thanks,
Shimon.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u6P6UNmYFHA.2264@.TK2MSFTNGP10.phx.gbl...
> Shimon Sim wrote:
> Connect from Query Analyzer and change the default database for the user
> you are using to login from SQL EM.
> sp_defaultdb 'user', 'db'
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Shimon Sim wrote:
> I can't connect using QA either.
> Thanks,
> Shimon.
Log in as a different user in QA... one with admin rights. Then change
the default database.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks
I connected using Visial Studio
Shimon.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u6P6UNmYFHA.2264@.TK2MSFTNGP10.phx.gbl...
> Shimon Sim wrote:
> Connect from Query Analyzer and change the default database for the user
> you are using to login from SQL EM.
> sp_defaultdb 'user', 'db'
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Hi Shimon,
Thanks for your posting!
From your descriptions, I understood you are not able to connect SQL Server
via SQL Server Enterprise Manager. Have I understood you? If I have
misunderstood your concern, please feel free to point it out.
First of all, would you please help me clarify the words "I connected using
Visial Studio"? You have said you are working wiht your Enterprise Manager?
When do you meet the error message "Can't access user's default database"?
More detailed informaiton, I believe, will make us closer to the
resolution. Please help me collecting the information by answering the
questions below
What account you are using to connect this SQL Server?
The SQL Server machine is in LAN or Internet?
You may refer the Knowledge Base article below
How to troubleshoot connectivity issues in SQL Server 2000
http://support.microsoft.com/kb/827422
BTW, if this issue is urgent, I would like to recommand you opening an
incident with PSS team direcly and there will be a dedicated Support
Professional working for you. Please be advised that contacting phone
support will be a charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w
to allMicrosoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others: https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Michael
Thanks for your response.
I resolved the issue.
What happen I deleted my default database. And all the users for the SQL
Server had that database as default. So every program (EM,QA) that uses
default database in order to operate was throwing errors that they can't
login to the server.
So I decided to create database project in VS that uses specifies database
for login and not default and changed default database for all the users.
Thanks again.
Shimon.
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:NIh58$oYFHA.2476@.TK2MSFTNGXA01.phx.gbl...
> Hi Shimon,
> Thanks for your posting!
> From your descriptions, I understood you are not able to connect SQL
> Server
> via SQL Server Enterprise Manager. Have I understood you? If I have
> misunderstood your concern, please feel free to point it out.
> First of all, would you please help me clarify the words "I connected
> using
> Visial Studio"? You have said you are working wiht your Enterprise
> Manager?
> When do you meet the error message "Can't access user's default database"?
> More detailed informaiton, I believe, will make us closer to the
> resolution. Please help me collecting the information by answering the
> questions below
> What account you are using to connect this SQL Server?
> The SQL Server machine is in LAN or Internet?
> You may refer the Knowledge Base article below
> How to troubleshoot connectivity issues in SQL Server 2000
> http://support.microsoft.com/kb/827422
> BTW, if this issue is urgent, I would like to recommand you opening an
> incident with PSS team direcly and there will be a dedicated Support
> Professional working for you. Please be advised that contacting phone
> support will be a charged call.
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
> http://support.microsoft.com/defaul...US;PHONENUMBERS
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a w
to all> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/te...erview/40010469
> Others: https://partner.microsoft.com/US/te...upportoverview/
> If you are outside the United States, please visit our International
> Support page: http://support.microsoft.com/common/international.aspx
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Shimon,
You are welcome and it's great to hear you have resolved this!
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w
to allMicrosoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others: https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
EM and the Resource consumption.....
If the Enterprise Manger is running and the registered servers will be
resource consuming issue for the server. We have couple of developers
connected to the server all the time using EM.
Lately I read an article on Management Studio for SQL Server 2005 which
handles this issue.
How much it is contributing to the resources usage of SQL Server? I am
thinking all possible way to improve the performance of our SQL Server.
Thank you,
AlwinHi
EM does not consume more resources than a normal client application
connection to SQL Server.
As long as you don't use the "open table" to look at data, you are fine.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Alwin Mark" <AMark_SFO@.hotmail.com> wrote in message
news:eygY6GatFHA.2472@.TK2MSFTNGP14.phx.gbl...
> If the Enterprise Manger is running and the registered servers will be
> resource consuming issue for the server. We have couple of developers
> connected to the server all the time using EM.
> Lately I read an article on Management Studio for SQL Server 2005 which
> handles this issue.
> How much it is contributing to the resources usage of SQL Server? I am
> thinking all possible way to improve the performance of our SQL Server.
> Thank you,
> Alwin
>
resource consuming issue for the server. We have couple of developers
connected to the server all the time using EM.
Lately I read an article on Management Studio for SQL Server 2005 which
handles this issue.
How much it is contributing to the resources usage of SQL Server? I am
thinking all possible way to improve the performance of our SQL Server.
Thank you,
AlwinHi
EM does not consume more resources than a normal client application
connection to SQL Server.
As long as you don't use the "open table" to look at data, you are fine.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Alwin Mark" <AMark_SFO@.hotmail.com> wrote in message
news:eygY6GatFHA.2472@.TK2MSFTNGP14.phx.gbl...
> If the Enterprise Manger is running and the registered servers will be
> resource consuming issue for the server. We have couple of developers
> connected to the server all the time using EM.
> Lately I read an article on Management Studio for SQL Server 2005 which
> handles this issue.
> How much it is contributing to the resources usage of SQL Server? I am
> thinking all possible way to improve the performance of our SQL Server.
> Thank you,
> Alwin
>
EM and the Resource consumption.....
If the Enterprise Manger is running and the registered servers will be
resource consuming issue for the server. We have couple of developers
connected to the server all the time using EM.
Lately I read an article on Management Studio for SQL Server 2005 which
handles this issue.
How much it is contributing to the resources usage of SQL Server? I am
thinking all possible way to improve the performance of our SQL Server.
Thank you,
Alwin
Hi
EM does not consume more resources than a normal client application
connection to SQL Server.
As long as you don't use the "open table" to look at data, you are fine.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Alwin Mark" <AMark_SFO@.hotmail.com> wrote in message
news:eygY6GatFHA.2472@.TK2MSFTNGP14.phx.gbl...
> If the Enterprise Manger is running and the registered servers will be
> resource consuming issue for the server. We have couple of developers
> connected to the server all the time using EM.
> Lately I read an article on Management Studio for SQL Server 2005 which
> handles this issue.
> How much it is contributing to the resources usage of SQL Server? I am
> thinking all possible way to improve the performance of our SQL Server.
> Thank you,
> Alwin
>
resource consuming issue for the server. We have couple of developers
connected to the server all the time using EM.
Lately I read an article on Management Studio for SQL Server 2005 which
handles this issue.
How much it is contributing to the resources usage of SQL Server? I am
thinking all possible way to improve the performance of our SQL Server.
Thank you,
Alwin
Hi
EM does not consume more resources than a normal client application
connection to SQL Server.
As long as you don't use the "open table" to look at data, you are fine.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Alwin Mark" <AMark_SFO@.hotmail.com> wrote in message
news:eygY6GatFHA.2472@.TK2MSFTNGP14.phx.gbl...
> If the Enterprise Manger is running and the registered servers will be
> resource consuming issue for the server. We have couple of developers
> connected to the server all the time using EM.
> Lately I read an article on Management Studio for SQL Server 2005 which
> handles this issue.
> How much it is contributing to the resources usage of SQL Server? I am
> thinking all possible way to improve the performance of our SQL Server.
> Thank you,
> Alwin
>
Sunday, February 26, 2012
EM and the Resource consumption.....
If the Enterprise Manger is running and the registered servers will be
resource consuming issue for the server. We have couple of developers
connected to the server all the time using EM.
Lately I read an article on Management Studio for SQL Server 2005 which
handles this issue.
How much it is contributing to the resources usage of SQL Server? I am
thinking all possible way to improve the performance of our SQL Server.
Thank you,
AlwinHi
EM does not consume more resources than a normal client application
connection to SQL Server.
As long as you don't use the "open table" to look at data, you are fine.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Alwin Mark" <AMark_SFO@.hotmail.com> wrote in message
news:eygY6GatFHA.2472@.TK2MSFTNGP14.phx.gbl...
> If the Enterprise Manger is running and the registered servers will be
> resource consuming issue for the server. We have couple of developers
> connected to the server all the time using EM.
> Lately I read an article on Management Studio for SQL Server 2005 which
> handles this issue.
> How much it is contributing to the resources usage of SQL Server? I am
> thinking all possible way to improve the performance of our SQL Server.
> Thank you,
> Alwin
>
resource consuming issue for the server. We have couple of developers
connected to the server all the time using EM.
Lately I read an article on Management Studio for SQL Server 2005 which
handles this issue.
How much it is contributing to the resources usage of SQL Server? I am
thinking all possible way to improve the performance of our SQL Server.
Thank you,
AlwinHi
EM does not consume more resources than a normal client application
connection to SQL Server.
As long as you don't use the "open table" to look at data, you are fine.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Alwin Mark" <AMark_SFO@.hotmail.com> wrote in message
news:eygY6GatFHA.2472@.TK2MSFTNGP14.phx.gbl...
> If the Enterprise Manger is running and the registered servers will be
> resource consuming issue for the server. We have couple of developers
> connected to the server all the time using EM.
> Lately I read an article on Management Studio for SQL Server 2005 which
> handles this issue.
> How much it is contributing to the resources usage of SQL Server? I am
> thinking all possible way to improve the performance of our SQL Server.
> Thank you,
> Alwin
>
EM and QA displays different format for a date field
Hi,
I have a datetime field with a value '1/1/02'. EM display it as 1/1/02 But
Query Analyzer displays it as 2002-01-01 00:00:00.000 (I used SELECT * FROM
Table1)
So in Query Analyzer, I can't tell the fomat of it. I just wonder if your
PC does the same.
Thanks.You have a problem under standing of what a data value is and how it is
stored. It is actually just stored as numeric value, denoting offsets from
some time in the reasonably distant past. Format is based on the user. QA
uses a standard value, mostly because when you are using QA the goal is
usually to see ALL of the data.
Enterprise manager is probably using your system settings. When you
actually use the data, you can format it as you wish.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
news:OqYzX0yUFHA.2136@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a datetime field with a value '1/1/02'. EM display it as 1/1/02
> But Query Analyzer displays it as 2002-01-01 00:00:00.000 (I used SELECT *
> FROM Table1)
> So in Query Analyzer, I can't tell the fomat of it. I just wonder if your
> PC does the same.
> Thanks.
>|||Chrissi,
Different clients display things differently, with the possible
excepting of strings made up of printable ASCII with codes
from 32 to 127.
If you want the datetime displayed in a particular format, I'd
suggest you select CONVERT(varchar(40),yourDatetimeColumn,X
XXX),
where XXXX is the appropriate SQL Server format code (see the
Books Online article CAST and CONVERT).
If you do this, you won't be able to "edit" the datetime values
in Enterprise Manager, but that's not a good idea anyway. Better
to make changes programmatically with UPDATE or INSERT
statements.
Steve Kass
Drew University
Chrissi wrote:
>Hi,
>I have a datetime field with a value '1/1/02'. EM display it as 1/1/02 Bu
t
>Query Analyzer displays it as 2002-01-01 00:00:00.000 (I used SELECT * FROM
>Table1)
>So in Query Analyzer, I can't tell the fomat of it. I just wonder if your
>PC does the same.
>Thanks.
>
>
I have a datetime field with a value '1/1/02'. EM display it as 1/1/02 But
Query Analyzer displays it as 2002-01-01 00:00:00.000 (I used SELECT * FROM
Table1)
So in Query Analyzer, I can't tell the fomat of it. I just wonder if your
PC does the same.
Thanks.You have a problem under standing of what a data value is and how it is
stored. It is actually just stored as numeric value, denoting offsets from
some time in the reasonably distant past. Format is based on the user. QA
uses a standard value, mostly because when you are using QA the goal is
usually to see ALL of the data.
Enterprise manager is probably using your system settings. When you
actually use the data, you can format it as you wish.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
news:OqYzX0yUFHA.2136@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a datetime field with a value '1/1/02'. EM display it as 1/1/02
> But Query Analyzer displays it as 2002-01-01 00:00:00.000 (I used SELECT *
> FROM Table1)
> So in Query Analyzer, I can't tell the fomat of it. I just wonder if your
> PC does the same.
> Thanks.
>|||Chrissi,
Different clients display things differently, with the possible
excepting of strings made up of printable ASCII with codes
from 32 to 127.
If you want the datetime displayed in a particular format, I'd
suggest you select CONVERT(varchar(40),yourDatetimeColumn,X
XXX),
where XXXX is the appropriate SQL Server format code (see the
Books Online article CAST and CONVERT).
If you do this, you won't be able to "edit" the datetime values
in Enterprise Manager, but that's not a good idea anyway. Better
to make changes programmatically with UPDATE or INSERT
statements.
Steve Kass
Drew University
Chrissi wrote:
>Hi,
>I have a datetime field with a value '1/1/02'. EM display it as 1/1/02 Bu
t
>Query Analyzer displays it as 2002-01-01 00:00:00.000 (I used SELECT * FROM
>Table1)
>So in Query Analyzer, I can't tell the fomat of it. I just wonder if your
>PC does the same.
>Thanks.
>
>
Subscribe to:
Posts (Atom)