Enumerate SQL server list?

Anyone know how to enumerate the SQL Servers on a network using .Net? I can
do this with VB6, but howso in .Net?
Hi Earl,
One way would be:
Q287737
http://support.microsoft.com/default...EN-US;Q287737&
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com
"Earl comcast net>" <brikshoe<at.> wrote in message
news:%23YXdcy5FEHA.2208@.TK2MSFTNGP09.phx.gbl...
> Anyone know how to enumerate the SQL Servers on a network using .Net? I
can
> do this with VB6, but howso in .Net?
>
|||I haven't seen how to do it using the .Net Framework, but if the method you use for VB6 provides a COM object interface, you can include a reference to the library in your project. .Net will create the classes required for the mashalling between the manag
ed and unmanaged code.
|||Earl,
This is worth looking at to give you some ideas. It's not .net though.
http://sqldev.net/misc/ListSQLSvr.htm
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
|||Hi Mika,
This is essentially the same thing I'm doing now in VB6, but I was looking
to see if there was another way to not use a COM reference in order to do
this with .Net. Maybe this is the wrong way of looking at this -- what am I
giving up using COM here? Incidentally, I've been on that RightHand site in
the past, good stuff you folks are building over there.
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:OyYPu47FEHA.3188@.TK2MSFTNGP10.phx.gbl...
> Hi Earl,
> One way would be:
> Q287737
> http://support.microsoft.com/default...EN-US;Q287737&
> --
> Miha Markic [MVP C#] - RightHand .NET consulting & software development
> miha at rthand com
> www.rthand.com
> "Earl comcast net>" <brikshoe<at.> wrote in message
> news:%23YXdcy5FEHA.2208@.TK2MSFTNGP09.phx.gbl...
> can
>
|||Miha,
Using this VB6 method, I get an invalid cast exception when I try to create
an object for the namelist (Dim oNames As SQLDMO.NameList). Even with a
Try-Catch, I still get the exception: "Query Interface for interface
SQLDMO.Namelist failed." Is this a COM wrapper issue?
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:OyYPu47FEHA.3188@.TK2MSFTNGP10.phx.gbl...
> Hi Earl,
> One way would be:
> Q287737
> http://support.microsoft.com/default...EN-US;Q287737&
> --
> Miha Markic [MVP C#] - RightHand .NET consulting & software development
> miha at rthand com
> www.rthand.com
> "Earl comcast net>" <brikshoe<at.> wrote in message
> news:%23YXdcy5FEHA.2208@.TK2MSFTNGP09.phx.gbl...
> can
>
|||Hehe, thanks Mark... I've read this and every article I could find on the
web about this topic and they all pertain to VB6. Gets me close though.
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:814797B0-7FBF-4D8A-8ABA-0CB3B8EBCE08@.microsoft.com...
> Earl,
> This is worth looking at to give you some ideas. It's not .net though.
> http://sqldev.net/misc/ListSQLSvr.htm
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
|||Thanks Al, the problem I'm having is that apparently one of the classes is
not being created because I get an error trying to assign the list object to
the name object.
Dim oSQLNames As SQLDMO.NameList
Dim oSQLApp As SQLDMO.Application
oSQLApp = New SQLDMO.Application
'getting invalid cast error on this assignment
oSQLNames = oSQLApp.ListAvailableSQLServers
"Al" <al_davie@.hotmail.com> wrote in message
news:A7B8DA42-0D39-4529-B2AE-9C8DAED59F1B@.microsoft.com...
> I haven't seen how to do it using the .Net Framework, but if the method
you use for VB6 provides a COM object interface, you can include a reference
to the library in your project. .Net will create the classes required for
the mashalling between the managed and unmanaged code.
|||Hi,
I've just tried your code fragment, and I don't get a problem.
Are you getting the problem in the debugger or deployed ?
Here are the facts for my set-up :-
VS 2003 / Framework 1.1
SQL Server 2000 SP3 (not Sp3a)
Windows 2000Pro SP4
Running via debugger, so not deployed.
Visual Basic Windows application
Option Explicit ON
Option Strict OFF
Option Compare Binary
The Imports and References are all the defaults for a Windows Application, excepted added the SQLDMO Object lib version 8.0
Let me know how you get on
|||If you're getting stuck, send me the source.
Email my Hotmail account, i'll send you my main email address.

Enumerate SQL server list?

Anyone know how to enumerate the SQL Servers on a network using .Net? I can
do this with VB6, but howso in .Net?Hi Earl,
One way would be:
Q287737
http://support.microsoft.com/defaul...;EN-US;Q287737&
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com
"Earl comcast net>" <brikshoe<at.> wrote in message
news:%23YXdcy5FEHA.2208@.TK2MSFTNGP09.phx.gbl...
> Anyone know how to enumerate the SQL Servers on a network using .Net? I
can
> do this with VB6, but howso in .Net?
>|||I haven't seen how to do it using the .Net Framework, but if the method you
use for VB6 provides a COM object interface, you can include a reference to
the library in your project. .Net will create the classes required for the m
ashalling between the manag
ed and unmanaged code.|||Earl,
This is worth looking at to give you some ideas. It's not .net though.
http://sqldev.net/misc/ListSQLSvr.htm
Mark Allison, SQL Server MVP
http://www.markallison.co.uk|||Hi Mika,
This is essentially the same thing I'm doing now in VB6, but I was looking
to see if there was another way to not use a COM reference in order to do
this with .Net. Maybe this is the wrong way of looking at this -- what am I
giving up using COM here? Incidentally, I've been on that RightHand site in
the past, good stuff you folks are building over there.
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:OyYPu47FEHA.3188@.TK2MSFTNGP10.phx.gbl...
> Hi Earl,
> One way would be:
> Q287737
> http://support.microsoft.com/defaul...;EN-US;Q287737&
> --
> Miha Markic [MVP C#] - RightHand .NET consulting & software developmen
t
> miha at rthand com
> www.rthand.com
> "Earl comcast net>" <brikshoe<at.> wrote in message
> news:%23YXdcy5FEHA.2208@.TK2MSFTNGP09.phx.gbl...
> can
>|||Miha,
Using this VB6 method, I get an invalid cast exception when I try to create
an object for the namelist (Dim oNames As SQLDMO.NameList). Even with a
Try-Catch, I still get the exception: "Query Interface for interface
SQLDMO.Namelist failed." Is this a COM wrapper issue?
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:OyYPu47FEHA.3188@.TK2MSFTNGP10.phx.gbl...
> Hi Earl,
> One way would be:
> Q287737
> http://support.microsoft.com/defaul...;EN-US;Q287737&
> --
> Miha Markic [MVP C#] - RightHand .NET consulting & software developmen
t
> miha at rthand com
> www.rthand.com
> "Earl comcast net>" <brikshoe<at.> wrote in message
> news:%23YXdcy5FEHA.2208@.TK2MSFTNGP09.phx.gbl...
> can
>|||Hehe, thanks Mark... I've read this and every article I could find on the
web about this topic and they all pertain to VB6. Gets me close though.
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:814797B0-7FBF-4D8A-8ABA-0CB3B8EBCE08@.microsoft.com...
> Earl,
> This is worth looking at to give you some ideas. It's not .net though.
> http://sqldev.net/misc/ListSQLSvr.htm
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk|||Thanks Al, the problem I'm having is that apparently one of the classes is
not being created because I get an error trying to assign the list object to
the name object.
Dim oSQLNames As SQLDMO.NameList
Dim oSQLApp As SQLDMO.Application
oSQLApp = New SQLDMO.Application
'getting invalid cast error on this assignment
oSQLNames = oSQLApp.ListAvailableSQLServers
"Al" <al_davie@.hotmail.com> wrote in message
news:A7B8DA42-0D39-4529-B2AE-9C8DAED59F1B@.microsoft.com...
> I haven't seen how to do it using the .Net Framework, but if the method
you use for VB6 provides a COM object interface, you can include a reference
to the library in your project. .Net will create the classes required for
the mashalling between the managed and unmanaged code.|||Hi,
I've just tried your code fragment, and I don't get a problem.
Are you getting the problem in the debugger or deployed ?
Here are the facts for my set-up :-
VS 2003 / Framework 1.1
SQL Server 2000 SP3 (not Sp3a)
Windows 2000Pro SP4
Running via debugger, so not deployed.
Visual Basic Windows application
Option Explicit ON
Option Strict OFF
Option Compare Binary
The Imports and References are all the defaults for a Windows Application, e
xcepted added the SQLDMO Object lib version 8.0
Let me know how you get on|||If you're getting stuck, send me the source.
Email my Hotmail account, i'll send you my main email address.

EnumAvailableSqlServers() returns nothing when there is no network connection

I am having a weird issue. I am using SMO's EnumAvailableSqlServers() method to fill a drop-down box. If I am connected to a network, it shows all networked Sql Servers, including the local instance. When I am not connected to any network, it shows nothing. Even if I connect to an ad-hoc wireless network, it works correctly, showing only the local instance, but if the wireless card is removed (and no other networks are enabled) it is blank. SQL Server Browser is running. I am running both SQL Server and SQL Browser services under a local administrator account (for testing).

The problem is that these are handheld devices and sometimes they will be on a network, and other times they will be inside secure facilities in which no network devices are ever allowed. So, it needs to work in either condition. And really, it's absurd that the method cannot work in an unnetworked environment - a major slip-up by MS that is about to force us to embedded linux.

So, two questions:

1. Is there a way to fix this such that it will fallback correctly and look for local instances when no network is available?

2. If not, is there a known way to "trick" a machine into thinking a network is there so this will run correctly, such as some sort of network driver that emulates a connected network?

Hello David,

The EnumAvailableSqlServers is based off of the ADO.NET SqlDataSourceEnumerator class. The implementation is based on UDP broadcast, with a timeout, so you need a network connection to run the enumeration. Here are some other good things to know about the method: it may not reliably see all servers respond before the timeout; it will not find SQL Server if the SQLBrowser switched off, or the server is marked as hidden; and the method will also fail if the local instance blocks TCP/IP Port 1433 and UDP 1434.
But all is not lost. Here is code snippet that uses SMO Wmi to retrieve a reliable list the local SQL Server 2005 instances on a machine without network access.

ManagedComputer mc = new ManagedComputer();

// Setup the collection of servers

foreach (ServerInstance i in mc.ServerInstances)

{

string servername = i.Name;

if (i.Name == "MSSQLSERVER") // This is the default instance

{

servername = ".";

} else {

servername = ".\\" + i.Name;

}

Console.WriteLine(servername);

}

Good luck and let us know if you have any more questions,

Jennifer

This posting is provided "as is" with no warranties, and confers no rights.

|||Thanks, I will attempt to implement that tomorrow. It will make everyone very happy if it works out.

The million dollar question, where is this type of thing documented so I don't run into such problems down the road? Reminds me of using API calls back in VB5 days.
|||

MSDN documents the SMO class library at

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

The EnumAvailableSqlServers() is at

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.smoapplication.enumavailablesqlservers.aspx

And ServerInstance is at

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.wmi.serverinstance.aspx

Best of luck,

Jennifer Beckmann

|||

Hi David,

I have been down this road many times and what surprises me is that the answer is the same no matter where I look.

As you have found the EnumAvailableSqlServers() is not very reliable and requires too many stipulations to work such as SQLBrowser, Firewall settings, Network connection etc. Even if you get all that right you will find that it is still hit or miss. And good luck getting any earlier versions of SQL Server to show up including SQL Express. Jennifer's solution is great if you are only dealing with SQL 2005.

However I have discovered a slightly different approach that has not failed me yet:

Code Snippet

Dim SQLServers As RegisteredServers.RegisteredServerCollection = SmoApplication.SqlServerRegistrations.RegisteredServers

This will pick up SQL 2005, SQL Express, SQL 2000 and MSDE instances without any stipulations. No firewall issues, no network issues, no SQL Browser issues. You get the same complete list every time. And correctly named too I might add.

Hope this helps you and many more frustrated folks,

Jamie

|||Hi David,
I think the easiest solution is that you need to make sure that your SQL Browser Service is up and running and set to auto start. good luck.
Chan

Enterprise Manager's Bug?

Hello, I'm in trouble to solve my network problem.
My network is full of NetBIOS packets, and bad traffic.
I checked packets and found that this problem appears when Enterprise
Manager is in use.
=== detail ===
* 192.168.***.*** is local machine (with Enterprise Manager)
* 192.168.+++.+++ is SQL Server 2000 service pack 3a
192.168.***.*** -> 192.168.+++.+++ [SMB] NT Create AndX Request, Path:
\svcctl
192.168.+++.+++ -> 192.168.***.*** [SMB] NT Create AndX Response, FID:
0x804b
192.168.***.*** -> 192.168.+++.+++ [DCERPC] Bind: call_id: 1 UUID: SVCC
TL
192.168.+++.+++ -> 192.168.***.*** [DCERPC] Bind_ack: call_id: 1 accept
max_xmit: 4280 max_recv: 4280
192.168.***.*** -> 192.168.+++.+++ [SVCCTL] OpenSCManager request,
\\192.172.+++.+++, ServiceActive
192.168.+++.+++ -> 192.168.***.*** [SVCCTL] OpenSCManager reply
192.168.***.*** -> 192.168.+++.+++ [SVCCTL] Open service A request
192.168.+++.+++ -> 192.168.***.*** [SVCCTL] Open service A reply
192.168.***.*** -> 192.168.+++.+++ [SVCCTL] unknown?! request
192.168.+++.+++ -> 192.168.***.*** [SVCCTL] unknown?! reply
... close connection packets ...
*** This occurs 1 - 2 times in 1 second! ***
=== detail end ===
OpenSCManager'
I think it is Win32 API to control windows service.
Why Enterprise Manager requests "start unknown service"?
Please tell me how to solve this problem, thank you.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Pyob from Japan.
Thank you for reading my long long sentence. :-)
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-Try turning off the polling of the service. In Enterprise
Manager, go to the menu to Tools, select Options. On the
General Tab, remove the check for Server State Polling.
-Sue
On Wed, 16 Jun 2004 18:57:49 +0900, "Pyob" <pyob@.mac.com>
wrote:

>Hello, I'm in trouble to solve my network problem.
>My network is full of NetBIOS packets, and bad traffic.
>I checked packets and found that this problem appears when Enterprise
>Manager is in use.
>=== detail ===
>* 192.168.***.*** is local machine (with Enterprise Manager)
>* 192.168.+++.+++ is SQL Server 2000 service pack 3a
>192.168.***.*** -> 192.168.+++.+++ [SMB] NT Create AndX Request, Path:
>\svcctl
>192.168.+++.+++ -> 192.168.***.*** [SMB] NT Create AndX Response, FID:
>0x804b
>192.168.***.*** -> 192.168.+++.+++ [DCERPC] Bind: call_id: 1 UUID: SVC
CTL
>192.168.+++.+++ -> 192.168.***.*** [DCERPC] Bind_ack: call_id: 1 accep
t
>max_xmit: 4280 max_recv: 4280
>192.168.***.*** -> 192.168.+++.+++ [SVCCTL] OpenSCManager request,
>\\192.172.+++.+++, ServiceActive
>192.168.+++.+++ -> 192.168.***.*** [SVCCTL] OpenSCManager reply
>192.168.***.*** -> 192.168.+++.+++ [SVCCTL] Open service A request
>192.168.+++.+++ -> 192.168.***.*** [SVCCTL] Open service A reply
>192.168.***.*** -> 192.168.+++.+++ [SVCCTL] unknown?! request
>192.168.+++.+++ -> 192.168.***.*** [SVCCTL] unknown?! reply
>... close connection packets ...
>*** This occurs 1 - 2 times in 1 second! ***
>=== detail end ===
>OpenSCManager'
>I think it is Win32 API to control windows service.
>Why Enterprise Manager requests "start unknown service"?
>Please tell me how to solve this problem, thank you.
>-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
>Pyob from Japan.
>Thank you for reading my long long sentence. :-)
>-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-|||Thanks for your help, my network is going well!
But shortage of network resource is the main problem, I think.
Thank you very much!
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Pyob from Japan.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:gif0d0h376sm7260q9ch74ahin18v2s7sp@.
4ax.com...
> Try turning off the polling of the service. In Enterprise
> Manager, go to the menu to Tools, select Options. On the
> General Tab, remove the check for Server State Polling.
> -Sue
> On Wed, 16 Jun 2004 18:57:49 +0900, "Pyob" <pyob@.mac.com>
> wrote:
>
SVCCTL[vbcol=seagreen]
>

Enterprise Manager Server Registration General Network Error

I'm having problems Registering servers in Enterrpise Manager.
This problem is workstation specific.
Workstation is XP Pro SP1, in an Active Directory domain. Only protocol in
machine is TCP/IP.
I have 4 machines with identical hardware ghosted from one source image and
all have the problem. hardware isn't the problem. I have at least one
other Win XP Pro computer built from a different build that has the problem.
User logs on using Active Driectory account. Opens Enterprise Manager,
register SQL server, types server name, uses NT Authentication. Error
returned is "General Network Error, check you network documentation"
Registration using SQL Authentication works fine (using SQL Authentication
is NOT an option for security reasons)
Same user can perform the same task using the same Active Directory
credentials using a different machine, so the account is not the problem.
This machine has different hardware, runs Windows 2000 Pro.
I can use this Windows 2000 machine on the same network port that the other
machine is on and don't have a problem. The network from the NIC to the
server isn't the problem.
I tried updating MDAC from 2.7 to 2.8 with no change.
SQL Server in question is newly build SQL 2000 SP3a with NO load on it
(application vendor hasn't installed software yet).
Query Analyzer has the same problem.
I tried using ODBCPING.EXE and received this response:
COULD NOT CONNECT TO SQL SERVER
SQLState: 08S01 Native Error: 11
Info. Message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]Gene
ral network
error. Check your network documentation.
SQLState: 01000 Native Error: 10054
Info. Message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]Conn
ectionRead
(recv()).
A similar error came up from Query Analyzer.
I did find 2 different SQL servers that will allow registrations, but these
users don't need access to those servers.
What do I need to look at on this client to correct this problem?
Scott Beginkb below has some good troubleshooting steps
http://support.microsoft.com/defaul...kb;en-us;827422
Andy.
"Scott Begin" <ScottBegin@.discussions.microsoft.com> wrote in message
news:3CF7A011-A316-493B-9EFB-D45434373BCF@.microsoft.com...
> I'm having problems Registering servers in Enterrpise Manager.
> This problem is workstation specific.
> Workstation is XP Pro SP1, in an Active Directory domain. Only protocol
> in
> machine is TCP/IP.
> I have 4 machines with identical hardware ghosted from one source image
> and
> all have the problem. hardware isn't the problem. I have at least one
> other Win XP Pro computer built from a different build that has the
> problem.
> User logs on using Active Driectory account. Opens Enterprise Manager,
> register SQL server, types server name, uses NT Authentication. Error
> returned is "General Network Error, check you network documentation"
> Registration using SQL Authentication works fine (using SQL Authentication
> is NOT an option for security reasons)
> Same user can perform the same task using the same Active Directory
> credentials using a different machine, so the account is not the problem.
> This machine has different hardware, runs Windows 2000 Pro.
> I can use this Windows 2000 machine on the same network port that the
> other
> machine is on and don't have a problem. The network from the NIC to the
> server isn't the problem.
> I tried updating MDAC from 2.7 to 2.8 with no change.
> SQL Server in question is newly build SQL 2000 SP3a with NO load on it
> (application vendor hasn't installed software yet).
> Query Analyzer has the same problem.
> I tried using ODBCPING.EXE and received this response:
>
> COULD NOT CONNECT TO SQL SERVER
> SQLState: 08S01 Native Error: 11
> Info. Message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]Ge
neral
> network
> error. Check your network documentation.
> SQLState: 01000 Native Error: 10054
> Info. Message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]Co
nnectionRead
> (recv()).
> A similar error came up from Query Analyzer.
> I did find 2 different SQL servers that will allow registrations, but
> these
> users don't need access to those servers.
> What do I need to look at on this client to correct this problem?
> Scott Begin
>
>
>|||I looked at the article
I've tried the suggestions listed, but with no luck
1) Check DNS.
I can ping the server by name from the affected client. (rules out name
resolution)
I can register the server by name using SQL Authentication (rules out name
resolution)
I can register the server by IP address (rules out name resolution)
2) Verify the enabled protocols and aliases
Working Windows 2000 machine has Named Pipes and TCP/IP configured. Not
working XP machine matches. No aliases defined on either machine.
Everything else in Client Network Utility is the same on both machines.
3) Verify that the instance of SQL Server is listening Correctly
Have working Windows 2000 machine that can connect to this instance of SQL
Server using NT Authentication. XP Machine can connect to this instance of
SQL Server using SQL Authentication but NOT NT Authentication. As Suggested
by the article, the SQL Server log shows it is listening on port 1433
correctly.
Instance is running correctly.
4) Troubleshoot MDAC Issues
Windows 2000 Pro machine that works is running MDAC 2.7 SP1 Refresh
Windows XP Machine that doesn't work is running MDAC 2.8 RTM
ODBC Ping on 2000 machine that works shows the connection is being opened
properly.
ODBC Ping on XP machine that doesn't shows the following errors:
COULD NOT CONNECT TO SQL SERVER
SQLState: 08S01 Native Error: 11
Info. Message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]Gene
ral network
error. Check your network documentation.
SQLState: 01000 Native Error: 10054
Info. Message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]Conn
ectionRead
(recv()).
Seems to be an ODBC Issue
5) Troubleshoot firewall Issues:
Not applicable. There isn't a firewall between the client and server.
6) Troubleshoot Authentication and Security Issues
I use Active Directory Account on Windows 2000 machine. NT Authentication
works. I use same Active Directory account on Windows XP machine, doesn't
work.
Problem isn't authentication / security
7) Troubleshoot Stress on TCP/IP sockets
One of the servers being connected is a new server, without an application
running on it I don't think the stress on the TCP/IP sockets is the issue
8) See if teh instance of SQL Server is started in single user mode
I can connect to the server from other workstations simultaneously, so this
isn't the problem
9) Verify Named Pipes connectivity to SQL Server
Not trying to use Named Pipes to connect.
10) Troubleshoot conenctions that time otu during the recovery process.
These servers have been running anywhere from 1 day to 4 months. Recovery
process isn't going on. If this were a problem, I couldn't connect from th
e
Windows 2000 machine.
11) Test different ways to connect to the instance of SQL Server
Use SQL Authentication: Works from affected machine
From other data sources: Enterprise manager Fails NT Authentication, works
SQL; ODBC fails NT authentication, works SQL; Query Analyzer fails NT
Authentication, works SQL;
Using other protocols: Not an option. Must use TCP/IP
Different Login Account: why? The login account works on another computer
Put entry in hosts file: Why, resolution not problem
Connect from SQL Server: Works
Connect using IP address: Fails NT Authenticatiion/ works SQL Authenticatio
n
Specify port: Why? This doesn't seem to be the problem
12) Capture Network Traces:
Haven't tried this
The recurring theme you should see in what I have done is NT Authentication
doesn't work from this client. SQL Authentication does work from this
client. There is some setting / Driver or something that prevents it from
working.
Any other Ideas?
Scott Begin
scott@.thebegins.com
"Andy Ball" wrote:

> kb below has some good troubleshooting steps
> http://support.microsoft.com/defaul...kb;en-us;827422
> Andy.
> "Scott Begin" <ScottBegin@.discussions.microsoft.com> wrote in message
> news:3CF7A011-A316-493B-9EFB-D45434373BCF@.microsoft.com...
>
>

Enterprise Manager Server Registration General Network Error

I'm having problems Registering servers in Enterrpise Manager.
This problem is workstation specific.
Workstation is XP Pro SP1, in an Active Directory domain. Only protocol in
machine is TCP/IP.
I have 4 machines with identical hardware ghosted from one source image and
all have the problem. Hardware isn't the problem. I have at least one
other Win XP Pro computer built from a different build that has the problem.
User logs on using Active Driectory account. Opens Enterprise Manager,
register SQL server, types server name, uses NT Authentication. Error
returned is "General Network Error, check you network documentation"
Registration using SQL Authentication works fine (using SQL Authentication
is NOT an option for security reasons)
Same user can perform the same task using the same Active Directory
credentials using a different machine, so the account is not the problem.
This machine has different hardware, runs Windows 2000 Pro.
I can use this Windows 2000 machine on the same network port that the other
machine is on and don't have a problem. The network from the NIC to the
server isn't the problem.
I tried updating MDAC from 2.7 to 2.8 with no change.
SQL Server in question is newly build SQL 2000 SP3a with NO load on it
(application vendor hasn't installed software yet).
Query Analyzer has the same problem.
I tried using ODBCPING.EXE and received this response:
COULD NOT CONNECT TO SQL SERVER
SQLState: 08S01 Native Error: 11
Info. Message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network
error. Check your network documentation.
SQLState: 01000 Native Error: 10054
Info. Message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead
(recv()).
A similar error came up from Query Analyzer.
I did find 2 different SQL servers that will allow registrations, but these
users don't need access to those servers.
What do I need to look at on this client to correct this problem?
Scott Begin
kb below has some good troubleshooting steps
http://support.microsoft.com/default...b;en-us;827422
Andy.
"Scott Begin" <ScottBegin@.discussions.microsoft.com> wrote in message
news:3CF7A011-A316-493B-9EFB-D45434373BCF@.microsoft.com...
> I'm having problems Registering servers in Enterrpise Manager.
> This problem is workstation specific.
> Workstation is XP Pro SP1, in an Active Directory domain. Only protocol
> in
> machine is TCP/IP.
> I have 4 machines with identical hardware ghosted from one source image
> and
> all have the problem. Hardware isn't the problem. I have at least one
> other Win XP Pro computer built from a different build that has the
> problem.
> User logs on using Active Driectory account. Opens Enterprise Manager,
> register SQL server, types server name, uses NT Authentication. Error
> returned is "General Network Error, check you network documentation"
> Registration using SQL Authentication works fine (using SQL Authentication
> is NOT an option for security reasons)
> Same user can perform the same task using the same Active Directory
> credentials using a different machine, so the account is not the problem.
> This machine has different hardware, runs Windows 2000 Pro.
> I can use this Windows 2000 machine on the same network port that the
> other
> machine is on and don't have a problem. The network from the NIC to the
> server isn't the problem.
> I tried updating MDAC from 2.7 to 2.8 with no change.
> SQL Server in question is newly build SQL 2000 SP3a with NO load on it
> (application vendor hasn't installed software yet).
> Query Analyzer has the same problem.
> I tried using ODBCPING.EXE and received this response:
>
> COULD NOT CONNECT TO SQL SERVER
> SQLState: 08S01 Native Error: 11
> Info. Message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]General
> network
> error. Check your network documentation.
> SQLState: 01000 Native Error: 10054
> Info. Message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead
> (recv()).
> A similar error came up from Query Analyzer.
> I did find 2 different SQL servers that will allow registrations, but
> these
> users don't need access to those servers.
> What do I need to look at on this client to correct this problem?
> Scott Begin
>
>
>
|||I looked at the article
I've tried the suggestions listed, but with no luck
1) Check DNS.
I can ping the server by name from the affected client. (rules out name
resolution)
I can register the server by name using SQL Authentication (rules out name
resolution)
I can register the server by IP address (rules out name resolution)
2) Verify the enabled protocols and aliases
Working Windows 2000 machine has Named Pipes and TCP/IP configured. Not
working XP machine matches. No aliases defined on either machine.
Everything else in Client Network Utility is the same on both machines.
3) Verify that the instance of SQL Server is listening Correctly
Have working Windows 2000 machine that can connect to this instance of SQL
Server using NT Authentication. XP Machine can connect to this instance of
SQL Server using SQL Authentication but NOT NT Authentication. As Suggested
by the article, the SQL Server log shows it is listening on port 1433
correctly.
Instance is running correctly.
4) Troubleshoot MDAC Issues
Windows 2000 Pro machine that works is running MDAC 2.7 SP1 Refresh
Windows XP Machine that doesn't work is running MDAC 2.8 RTM
ODBC Ping on 2000 machine that works shows the connection is being opened
properly.
ODBC Ping on XP machine that doesn't shows the following errors:
COULD NOT CONNECT TO SQL SERVER
SQLState: 08S01 Native Error: 11
Info. Message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network
error. Check your network documentation.
SQLState: 01000 Native Error: 10054
Info. Message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead
(recv()).
Seems to be an ODBC Issue
5) Troubleshoot firewall Issues:
Not applicable. There isn't a firewall between the client and server.
6) Troubleshoot Authentication and Security Issues
I use Active Directory Account on Windows 2000 machine. NT Authentication
works. I use same Active Directory account on Windows XP machine, doesn't
work.
Problem isn't authentication / security
7) Troubleshoot Stress on TCP/IP sockets
One of the servers being connected is a new server, without an application
running on it I don't think the stress on the TCP/IP sockets is the issue
8) See if teh instance of SQL Server is started in single user mode
I can connect to the server from other workstations simultaneously, so this
isn't the problem
9) Verify Named Pipes connectivity to SQL Server
Not trying to use Named Pipes to connect.
10) Troubleshoot conenctions that time otu during the recovery process.
These servers have been running anywhere from 1 day to 4 months. Recovery
process isn't going on. If this were a problem, I couldn't connect from the
Windows 2000 machine.
11) Test different ways to connect to the instance of SQL Server
Use SQL Authentication: Works from affected machine
From other data sources: Enterprise manager Fails NT Authentication, works
SQL; ODBC fails NT authentication, works SQL; Query Analyzer fails NT
Authentication, works SQL;
Using other protocols: Not an option. Must use TCP/IP
Different Login Account: why? The login account works on another computer
Put entry in hosts file: Why, resolution not problem
Connect from SQL Server: Works
Connect using IP address: Fails NT Authenticatiion/ works SQL Authentication
Specify port: Why? This doesn't seem to be the problem
12) Capture Network Traces:
Haven't tried this
The recurring theme you should see in what I have done is NT Authentication
doesn't work from this client. SQL Authentication does work from this
client. There is some setting / Driver or something that prevents it from
working.
Any other Ideas?
Scott Begin
scott@.thebegins.com
"Andy Ball" wrote:

> kb below has some good troubleshooting steps
> http://support.microsoft.com/default...b;en-us;827422
> Andy.
> "Scott Begin" <ScottBegin@.discussions.microsoft.com> wrote in message
> news:3CF7A011-A316-493B-9EFB-D45434373BCF@.microsoft.com...
>
>

Enterprise Manager runs slow on some computers

I have a network of WinXP SP1 machines who all connect to
SQL Server 2000 SP3 on a Win2k server. When running
enterprise manager, most of the computers take at least 4
minutes to expand the database tree. Working on databases
(retrieving properties etc) is also painfully slow. On
two computers, however, Enterprise Manager shows no
slowdown and work as I would expect. Can anyone assist me
in diagnosing the problem?I would guess that one of the snap ins on the slower machines might be
causing a problem, I had something similar happen to me only for me the
enterprise manager wouldn't open at all.
On one of the slower machines try opening the Microsoft Management Console
program (C:\windows\system32\mmc.exe) and click on file and add/remove snap
ins. CLick the Add button and then select the Microsoft SQL Enterprise
manager. Click Ok and then try to open the database tree to see if its any
quicker. If so you can then click save as and use this console file instead
of the enterprise manager. If not then I'm afraid i can;t help you.
Nick
"G. Sutherland" <g.sutherland@.REMOVEkildrummy.co.uk> wrote in message
news:5dbc01c474b6$da345870$a501280a@.phx.gbl...
> I have a network of WinXP SP1 machines who all connect to
> SQL Server 2000 SP3 on a Win2k server. When running
> enterprise manager, most of the computers take at least 4
> minutes to expand the database tree. Working on databases
> (retrieving properties etc) is also painfully slow. On
> two computers, however, Enterprise Manager shows no
> slowdown and work as I would expect. Can anyone assist me
> in diagnosing the problem?|||Check to ensure that the AutoClose properties of the databases are not set
in EM on the slow machines.
Andrew J. Kelly SQL MVP
"G. Sutherland" <g.sutherland@.REMOVEkildrummy.co.uk> wrote in message
news:5dbc01c474b6$da345870$a501280a@.phx.gbl...
> I have a network of WinXP SP1 machines who all connect to
> SQL Server 2000 SP3 on a Win2k server. When running
> enterprise manager, most of the computers take at least 4
> minutes to expand the database tree. Working on databases
> (retrieving properties etc) is also painfully slow. On
> two computers, however, Enterprise Manager shows no
> slowdown and work as I would expect. Can anyone assist me
> in diagnosing the problem?|||Check for a file called SQL.LOG on the slow machines. If it's there and is
growing, you've got ODBC Tracing turned on.
Otherwise, run a network trace and compare the protocols being used from
the slow vs. fast.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Also, in EM, you can click on File|Options... and choose to delete the
history of changes to the console files. This can speed up performance as
well.
Jacco Schalkwijk
SQL Server MVP
"G. Sutherland" <g.sutherland@.REMOVEkildrummy.co.uk> wrote in message
news:5dbc01c474b6$da345870$a501280a@.phx.gbl...
>I have a network of WinXP SP1 machines who all connect to
> SQL Server 2000 SP3 on a Win2k server. When running
> enterprise manager, most of the computers take at least 4
> minutes to expand the database tree. Working on databases
> (retrieving properties etc) is also painfully slow. On
> two computers, however, Enterprise Manager shows no
> slowdown and work as I would expect. Can anyone assist me
> in diagnosing the problem?|||Yes you fixed my prob-, SQL TRACING WAS TURNED ON.
I have a very fast computer and it still made E-P-M very slow alymost
unuseable... Found c:\sql.log file 2.5 GB's in size ha ha...
Turned Tracing off works very fast now. cool!
Hummm, Now to find out how it got turned on in the first place ? Who
did it did you ? why you little #&*&#?! get over here!
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Enterprise Manager runs slow on some computers

I have a network of WinXP SP1 machines who all connect to
SQL Server 2000 SP3 on a Win2k server. When running
enterprise manager, most of the computers take at least 4
minutes to expand the database tree. Working on databases
(retrieving properties etc) is also painfully slow. On
two computers, however, Enterprise Manager shows no
slowdown and work as I would expect. Can anyone assist me
in diagnosing the problem?
I would guess that one of the snap ins on the slower machines might be
causing a problem, I had something similar happen to me only for me the
enterprise manager wouldn't open at all.
On one of the slower machines try opening the Microsoft Management Console
program (C:\windows\system32\mmc.exe) and click on file and add/remove snap
ins. CLick the Add button and then select the Microsoft SQL Enterprise
manager. Click Ok and then try to open the database tree to see if its any
quicker. If so you can then click save as and use this console file instead
of the enterprise manager. If not then I'm afraid i can;t help you.
Nick
"G. Sutherland" <g.sutherland@.REMOVEkildrummy.co.uk> wrote in message
news:5dbc01c474b6$da345870$a501280a@.phx.gbl...
> I have a network of WinXP SP1 machines who all connect to
> SQL Server 2000 SP3 on a Win2k server. When running
> enterprise manager, most of the computers take at least 4
> minutes to expand the database tree. Working on databases
> (retrieving properties etc) is also painfully slow. On
> two computers, however, Enterprise Manager shows no
> slowdown and work as I would expect. Can anyone assist me
> in diagnosing the problem?
|||Check to ensure that the AutoClose properties of the databases are not set
in EM on the slow machines.
Andrew J. Kelly SQL MVP
"G. Sutherland" <g.sutherland@.REMOVEkildrummy.co.uk> wrote in message
news:5dbc01c474b6$da345870$a501280a@.phx.gbl...
> I have a network of WinXP SP1 machines who all connect to
> SQL Server 2000 SP3 on a Win2k server. When running
> enterprise manager, most of the computers take at least 4
> minutes to expand the database tree. Working on databases
> (retrieving properties etc) is also painfully slow. On
> two computers, however, Enterprise Manager shows no
> slowdown and work as I would expect. Can anyone assist me
> in diagnosing the problem?
|||Check for a file called SQL.LOG on the slow machines. If it's there and is
growing, you've got ODBC Tracing turned on.
Otherwise, run a network trace and compare the protocols being used from
the slow vs. fast.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Also, in EM, you can click on File|Options... and choose to delete the
history of changes to the console files. This can speed up performance as
well.
Jacco Schalkwijk
SQL Server MVP
"G. Sutherland" <g.sutherland@.REMOVEkildrummy.co.uk> wrote in message
news:5dbc01c474b6$da345870$a501280a@.phx.gbl...
>I have a network of WinXP SP1 machines who all connect to
> SQL Server 2000 SP3 on a Win2k server. When running
> enterprise manager, most of the computers take at least 4
> minutes to expand the database tree. Working on databases
> (retrieving properties etc) is also painfully slow. On
> two computers, however, Enterprise Manager shows no
> slowdown and work as I would expect. Can anyone assist me
> in diagnosing the problem?
|||Yes you fixed my prob-, SQL TRACING WAS TURNED ON.
I have a very fast computer and it still made E-P-M very slow alymost
unuseable... Found c:\sql.log file 2.5 GB's in size ha ha...
Turned Tracing off works very fast now. cool!
Hummm, Now to find out how it got turned on in the first place ? Who
did it did you ? why you little #&*&#?! get over here!
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Enterprise Manager Hangs

Our company recently moved to a corporate Windows 2000 Network and AD
from Novell and all our servers were moved from our little domain. We
are seeing a wierd problem with SQLSever since our move. The sever that
has SQL Server is working fine and we are able to register the server in
Enterprise Manager. However, it sits and hangs when trying to browse
the Databases or Management nodes (all other nodes are ok). We can also
get to Query Analyzer and queries against any of the the database tables
work just fine .. UNLESS Query Analyzer tries to browse for the
databases (to populate that database dropdown or display the Object
browser) .. in that case, it sits and hangs.
We have installed Service Pack 4 and this did not resolve the issue.
The hotfix for RAM over 2 gig on the server does not apply in our case.
Has anyone experienced this problem before? We rebooted SQL Server
yesterday and after a couple of reboots, it worked (the Query Analyzer
issue remained), but we are now back to the same issue.
Any clues, suggestions, and of course fixes would be much appreciated!
Thank you!
Artie
*** Sent via Developersdex http://www.codecomments.com ***
Can you try executing this sp from QA and tell us how much time it takes?
exec master.dbo.sp_MShasdbaccess
AMB
"Art" wrote:

> Our company recently moved to a corporate Windows 2000 Network and AD
> from Novell and all our servers were moved from our little domain. We
> are seeing a wierd problem with SQLSever since our move. The sever that
> has SQL Server is working fine and we are able to register the server in
> Enterprise Manager. However, it sits and hangs when trying to browse
> the Databases or Management nodes (all other nodes are ok). We can also
> get to Query Analyzer and queries against any of the the database tables
> work just fine .. UNLESS Query Analyzer tries to browse for the
> databases (to populate that database dropdown or display the Object
> browser) .. in that case, it sits and hangs.
> We have installed Service Pack 4 and this did not resolve the issue.
> The hotfix for RAM over 2 gig on the server does not apply in our case.
> Has anyone experienced this problem before? We rebooted SQL Server
> yesterday and after a couple of reboots, it worked (the Query Analyzer
> issue remained), but we are now back to the same issue.
> Any clues, suggestions, and of course fixes would be much appreciated!
> Thank you!
> Artie
>
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||Alejandro,
"exec master.dbo.sp_MShasdbaccess" has been churning for 2 minutes now
with no result. Hope this means that the plot thickens! Thanks for
your help!
Artie
*** Sent via Developersdex http://www.codecomments.com ***
|||" minutes seems to be a long time. Do you have many databases? Do you have autoclose turned on?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Art" <anonymousnospam@.devex.com> wrote in message news:uZrgpiHjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Alejandro,
> "exec master.dbo.sp_MShasdbaccess" has been churning for 2 minutes now
> with no result. Hope this means that the plot thickens! Thanks for
> your help!
> Artie
> *** Sent via Developersdex http://www.codecomments.com ***
|||We have about 20 databases. Thing is, we had absolutely no problems
until we moved to this new environment (Windows / AD) a few days ago -
Looks like this may be an access/permissions issue? I looked at the
autoclose option for a couple of the databases on the server and looks
like it is false (I used SELECT DATABASEPROPERTYEX('<DBNAME>',
'IsAutoClose')).
Thank you!
Artie
*** Sent via Developersdex http://www.codecomments.com ***
|||Hi,
If the owner of one of your databases is an AD account, it may cause the
problem. Set "sa" as the owner of all of your DBs
Erdal
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uGbVkrHjFHA.3316@.TK2MSFTNGP14.phx.gbl...
> " minutes seems to be a long time. Do you have many databases? Do you have
autoclose turned on?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Art" <anonymousnospam@.devex.com> wrote in message
news:uZrgpiHjFHA.1464@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
|||Normally, I would suspect ODBC tracing turned on at the client machine, but the fact that the query
also ran slow using QA got me suspicious. Perhaps you can run that query from QA again and use
Profiler to trace the statements inside that procedure to see which takes a long time?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Art" <anonymousnospam@.devex.com> wrote in message news:%23KeceaIjFHA.3316@.TK2MSFTNGP14.phx.gbl...
> We have about 20 databases. Thing is, we had absolutely no problems
> until we moved to this new environment (Windows / AD) a few days ago -
> Looks like this may be an access/permissions issue? I looked at the
> autoclose option for a couple of the databases on the server and looks
> like it is false (I used SELECT DATABASEPROPERTYEX('<DBNAME>',
> 'IsAutoClose')).
> Thank you!
> Artie
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||May be what Erdal is saying, is what is hurting the sp. Try executing the
only statement inside the sp [sp_mshasdbaccess] and you will get same
problem, but if you comment the line:
--owner = substring(suser_sname(sid), 1, 24),
then everything will be normal.
Try reporting this to Microsoft.
AMB
"Art" wrote:

> We have about 20 databases. Thing is, we had absolutely no problems
> until we moved to this new environment (Windows / AD) a few days ago -
> Looks like this may be an access/permissions issue? I looked at the
> autoclose option for a couple of the databases on the server and looks
> like it is false (I used SELECT DATABASEPROPERTYEX('<DBNAME>',
> 'IsAutoClose')).
> Thank you!
> Artie
>
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||Tibor
sp_MsHasDbAccess does not return any result at all .. it simply hangs.
So when I ran profiler while trying to execute it, it shows a bunch of
stuff under my login name and then sits on sp_MsHasDbAccess. (btw, some
of the statements before that was select IS_SRVROLEMEMBER ('sysadmin'),
which returns a 1; SELECT ISNULL(SUSER_SNAME(), SUSER_NAME()) correctly
returns my login name).
Thanks!
Aarti
*** Sent via Developersdex http://www.codecomments.com ***
|||I was referring to tracing the TSQL code *inside* MsHasDbAccess. You need to capture the right
events for this. See Alejandro's most recent post in this thread.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Art" <anonymousnospam@.devex.com> wrote in message news:%23gP7y1IjFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Tibor
> sp_MsHasDbAccess does not return any result at all .. it simply hangs.
> So when I ran profiler while trying to execute it, it shows a bunch of
> stuff under my login name and then sits on sp_MsHasDbAccess. (btw, some
> of the statements before that was select IS_SRVROLEMEMBER ('sysadmin'),
> which returns a 1; SELECT ISNULL(SUSER_SNAME(), SUSER_NAME()) correctly
> returns my login name).
> Thanks!
> Aarti
>
> *** Sent via Developersdex http://www.codecomments.com ***
>

Enterprise Manager Hangs

Our company recently moved to a corporate Windows 2000 Network and AD
from Novell and all our servers were moved from our little domain. We
are seeing a wierd problem with SQLSever since our move. The sever that
has SQL Server is working fine and we are able to register the server in
Enterprise Manager. However, it sits and hangs when trying to browse
the Databases or Management nodes (all other nodes are ok). We can also
get to Query Analyzer and queries against any of the the database tables
work just fine .. UNLESS Query Analyzer tries to browse for the
databases (to populate that database dropdown or display the Object
browser) .. in that case, it sits and hangs.
We have installed Service Pack 4 and this did not resolve the issue.
The hotfix for RAM over 2 gig on the server does not apply in our case.
Has anyone experienced this problem before? We rebooted SQL Server
yesterday and after a couple of reboots, it worked (the Query Analyzer
issue remained), but we are now back to the same issue.
Any clues, suggestions, and of course fixes would be much appreciated!
Thank you!
Artie
*** Sent via Developersdex http://www.codecomments.com ***Can you try executing this sp from QA and tell us how much time it takes?
exec master.dbo.sp_MShasdbaccess
AMB
"Art" wrote:

> Our company recently moved to a corporate Windows 2000 Network and AD
> from Novell and all our servers were moved from our little domain. We
> are seeing a wierd problem with SQLSever since our move. The sever that
> has SQL Server is working fine and we are able to register the server in
> Enterprise Manager. However, it sits and hangs when trying to browse
> the Databases or Management nodes (all other nodes are ok). We can also
> get to Query Analyzer and queries against any of the the database tables
> work just fine .. UNLESS Query Analyzer tries to browse for the
> databases (to populate that database dropdown or display the Object
> browser) .. in that case, it sits and hangs.
> We have installed Service Pack 4 and this did not resolve the issue.
> The hotfix for RAM over 2 gig on the server does not apply in our case.
> Has anyone experienced this problem before? We rebooted SQL Server
> yesterday and after a couple of reboots, it worked (the Query Analyzer
> issue remained), but we are now back to the same issue.
> Any clues, suggestions, and of course fixes would be much appreciated!
> Thank you!
> Artie
>
> *** Sent via Developersdex http://www.codecomments.com ***
>|||Alejandro,
"exec master.dbo.sp_MShasdbaccess" has been churning for 2 minutes now
with no result. Hope this means that the plot thickens! Thanks for
your help!
Artie
*** Sent via Developersdex http://www.codecomments.com ***|||" minutes seems to be a long time. Do you have many databases? Do you have a
utoclose turned on?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Art" <anonymousnospam@.devex.com> wrote in message news:uZrgpiHjFHA.1464@.TK2MSFTNGP14.phx.gb
l...
> Alejandro,
> "exec master.dbo.sp_MShasdbaccess" has been churning for 2 minutes now
> with no result. Hope this means that the plot thickens! Thanks for
> your help!
> Artie
> *** Sent via Developersdex http://www.codecomments.com ***|||We have about 20 databases. Thing is, we had absolutely no problems
until we moved to this new environment (Windows / AD) a few days ago -
Looks like this may be an access/permissions issue? I looked at the
autoclose option for a couple of the databases on the server and looks
like it is false (I used SELECT DATABASEPROPERTYEX('<DBNAME>',
'IsAutoClose')).
Thank you!
Artie
*** Sent via Developersdex http://www.codecomments.com ***|||Hi,
If the owner of one of your databases is an AD account, it may cause the
problem. Set "sa" as the owner of all of your DBs
Erdal
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uGbVkrHjFHA.3316@.TK2MSFTNGP14.phx.gbl...
> " minutes seems to be a long time. Do you have many databases? Do you have
autoclose turned on?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Art" <anonymousnospam@.devex.com> wrote in message
news:uZrgpiHjFHA.1464@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]|||Normally, I would suspect ODBC tracing turned on at the client machine, but
the fact that the query
also ran slow using QA got me suspicious. Perhaps you can run that query fro
m QA again and use
Profiler to trace the statements inside that procedure to see which takes a
long time?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Art" <anonymousnospam@.devex.com> wrote in message news:%23KeceaIjFHA.3316@.TK2MSFTNGP14.phx.
gbl...
> We have about 20 databases. Thing is, we had absolutely no problems
> until we moved to this new environment (Windows / AD) a few days ago -
> Looks like this may be an access/permissions issue? I looked at the
> autoclose option for a couple of the databases on the server and looks
> like it is false (I used SELECT DATABASEPROPERTYEX('<DBNAME>',
> 'IsAutoClose')).
> Thank you!
> Artie
>
> *** Sent via Developersdex http://www.codecomments.com ***|||May be what Erdal is saying, is what is hurting the sp. Try executing the
only statement inside the sp [sp_mshasdbaccess] and you will get same
problem, but if you comment the line:
-- owner = substring(suser_sname(sid), 1, 24),
then everything will be normal.
Try reporting this to Microsoft.
AMB
"Art" wrote:

> We have about 20 databases. Thing is, we had absolutely no problems
> until we moved to this new environment (Windows / AD) a few days ago -
> Looks like this may be an access/permissions issue? I looked at the
> autoclose option for a couple of the databases on the server and looks
> like it is false (I used SELECT DATABASEPROPERTYEX('<DBNAME>',
> 'IsAutoClose')).
> Thank you!
> Artie
>
> *** Sent via Developersdex http://www.codecomments.com ***
>|||Tibor
sp_MsHasDbAccess does not return any result at all .. it simply hangs.
So when I ran profiler while trying to execute it, it shows a bunch of
stuff under my login name and then sits on sp_MsHasDbAccess. (btw, some
of the statements before that was select IS_SRVROLEMEMBER ('sysadmin'),
which returns a 1; SELECT ISNULL(SUSER_SNAME(), SUSER_NAME()) correctly
returns my login name).
Thanks!
Aarti
*** Sent via Developersdex http://www.codecomments.com ***|||I was referring to tracing the TSQL code *inside* MsHasDbAccess. You need to
capture the right
events for this. See Alejandro's most recent post in this thread.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Art" <anonymousnospam@.devex.com> wrote in message news:%23gP7y1IjFHA.1412@.TK2MSFTNGP09.phx.
gbl...
> Tibor
> sp_MsHasDbAccess does not return any result at all .. it simply hangs.
> So when I ran profiler while trying to execute it, it shows a bunch of
> stuff under my login name and then sits on sp_MsHasDbAccess. (btw, some
> of the statements before that was select IS_SRVROLEMEMBER ('sysadmin'),
> which returns a 1; SELECT ISNULL(SUSER_SNAME(), SUSER_NAME()) correctly
> returns my login name).
> Thanks!
> Aarti
>
> *** Sent via Developersdex http://www.codecomments.com ***
>

Enterprise Manager Hangs

Our company recently moved to a corporate Windows 2000 Network and AD
from Novell and all our servers were moved from our little domain. We
are seeing a wierd problem with SQLSever since our move. The sever that
has SQL Server is working fine and we are able to register the server in
Enterprise Manager. However, it sits and hangs when trying to browse
the Databases or Management nodes (all other nodes are ok). We can also
get to Query Analyzer and queries against any of the the database tables
work just fine .. UNLESS Query Analyzer tries to browse for the
databases (to populate that database dropdown or display the Object
browser) .. in that case, it sits and hangs.
We have installed Service Pack 4 and this did not resolve the issue.
The hotfix for RAM over 2 gig on the server does not apply in our case.
Has anyone experienced this problem before? We rebooted SQL Server
yesterday and after a couple of reboots, it worked (the Query Analyzer
issue remained), but we are now back to the same issue.
Any clues, suggestions, and of course fixes would be much appreciated!
Thank you!
Artie
*** Sent via Developersdex http://www.developersdex.com ***Can you try executing this sp from QA and tell us how much time it takes?
exec master.dbo.sp_MShasdbaccess
AMB
"Art" wrote:
> Our company recently moved to a corporate Windows 2000 Network and AD
> from Novell and all our servers were moved from our little domain. We
> are seeing a wierd problem with SQLSever since our move. The sever that
> has SQL Server is working fine and we are able to register the server in
> Enterprise Manager. However, it sits and hangs when trying to browse
> the Databases or Management nodes (all other nodes are ok). We can also
> get to Query Analyzer and queries against any of the the database tables
> work just fine .. UNLESS Query Analyzer tries to browse for the
> databases (to populate that database dropdown or display the Object
> browser) .. in that case, it sits and hangs.
> We have installed Service Pack 4 and this did not resolve the issue.
> The hotfix for RAM over 2 gig on the server does not apply in our case.
> Has anyone experienced this problem before? We rebooted SQL Server
> yesterday and after a couple of reboots, it worked (the Query Analyzer
> issue remained), but we are now back to the same issue.
> Any clues, suggestions, and of course fixes would be much appreciated!
> Thank you!
> Artie
>
> *** Sent via Developersdex http://www.developersdex.com ***
>|||May be what Erdal is saying, is what is hurting the sp. Try executing the
only statement inside the sp [sp_mshasdbaccess] and you will get same
problem, but if you comment the line:
-- owner = substring(suser_sname(sid), 1, 24),
then everything will be normal.
Try reporting this to Microsoft.
AMB
"Art" wrote:
> We have about 20 databases. Thing is, we had absolutely no problems
> until we moved to this new environment (Windows / AD) a few days ago -
> Looks like this may be an access/permissions issue? I looked at the
> autoclose option for a couple of the databases on the server and looks
> like it is false (I used SELECT DATABASEPROPERTYEX('<DBNAME>',
> 'IsAutoClose')).
> Thank you!
> Artie
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

Enterprise Manager causing extensive network traffic

Does anybody have any idea what is happening here. On 3 out of five workstations when entering Enterprise Manager the network traffic jumps drastically to the point that the server needs to be rebooted. Any information would be greatly appreciated.
Thanks
NewbieTry Using the SQL Profiler that comes with SQL Server

This underused programme will list every command that hits the SQL Server in Real time.

Just run the default trace & see whats happening when a client logs on

GW|||I used profiler this morning, using the default trace and I also set it to log everything. It didn't log anything out of the ordinary, and there was nothing that coincided with the amount of traffic being generated.

Originally posted by GWilliy
Try Using the SQL Profiler that comes with SQL Server

This underused programme will list every command that hits the SQL Server in Real time.

Just run the default trace & see whats happening when a client logs on

GW|||OK

My next step would be to download a trial version of Commview 3 - install it onto the client.

This App is an excellent packet sniffer and will show you exactly any traffic that is happening & to which IP Address it is going to

You could try and decipher the packets with this tool if you are brave - at very least it will show how much traffic is generated on which port to where.

GW

Enterprise Manager blocks network on XP

I'm running XP SP2 (and all the latest hotfixes) and administer a
bunch of SQL2000 servers. Whenever I have enterprise manager running,
I find that my network access seems to block regularly. What I mean by
that is, If I open Explorer and try to access network drives, explorer
will hang for 10-15 second before responding. The same thing occurs
for other apps that access network drives (open/save dialogs, apps
with directory tree windows, etc).
Is anyone else seeing this same behavior? Any idea how to trace
something like this? It isn't network related according to a sniffer -
it seems more similar to they type of blocking we used to see with old
ODBC-based applications.
Thanks
What happens if you turn off the server state polling in
Enterprise Manager? On the menu go to tools then select
options. Clear the checkbox for Server State Polling on the
General tab.
You'd also want to check the event logs if turning off the
polling makes no difference.
-Sue
On 27 Feb 2007 12:28:53 -0800, steven.cardinal@.gmail.com
wrote:

>I'm running XP SP2 (and all the latest hotfixes) and administer a
>bunch of SQL2000 servers. Whenever I have enterprise manager running,
>I find that my network access seems to block regularly. What I mean by
>that is, If I open Explorer and try to access network drives, explorer
>will hang for 10-15 second before responding. The same thing occurs
>for other apps that access network drives (open/save dialogs, apps
>with directory tree windows, etc).
>Is anyone else seeing this same behavior? Any idea how to trace
>something like this? It isn't network related according to a sniffer -
>it seems more similar to they type of blocking we used to see with old
>ODBC-based applications.
>Thanks
|||I am hesitantly optimistic. Since turning that off, things seem
better. I was aware of the option in each server registration about
showing the server state (which, if turned off prompts me everytime I
want to connect) but wasn't aware of the global polling option.
Thank you very, very, very, very much
Steve
On Feb 27, 11:32 pm, Sue Hoegemeier <S...@.nomail.please> wrote:[vbcol=seagreen]
> What happens if you turn off the server state polling in
> Enterprise Manager? On the menu go to tools then select
> options. Clear the checkbox for Server State Polling on the
> General tab.
> You'd also want to check the event logs if turning off the
> polling makes no difference.
> -Sue
> On 27 Feb 2007 12:28:53 -0800, steven.cardi...@.gmail.com
> wrote:
>

Enterprise Manager blocks network on XP

I'm running XP SP2 (and all the latest hotfixes) and administer a
bunch of SQL2000 servers. Whenever I have enterprise manager running,
I find that my network access seems to block regularly. What I mean by
that is, If I open Explorer and try to access network drives, explorer
will hang for 10-15 second before responding. The same thing occurs
for other apps that access network drives (open/save dialogs, apps
with directory tree windows, etc).
Is anyone else seeing this same behavior? Any idea how to trace
something like this? It isn't network related according to a sniffer -
it seems more similar to they type of blocking we used to see with old
ODBC-based applications.
ThanksWhat happens if you turn off the server state polling in
Enterprise Manager? On the menu go to tools then select
options. Clear the checkbox for Server State Polling on the
General tab.
You'd also want to check the event logs if turning off the
polling makes no difference.
-Sue
On 27 Feb 2007 12:28:53 -0800, steven.cardinal@.gmail.com
wrote:

>I'm running XP SP2 (and all the latest hotfixes) and administer a
>bunch of SQL2000 servers. Whenever I have enterprise manager running,
>I find that my network access seems to block regularly. What I mean by
>that is, If I open Explorer and try to access network drives, explorer
>will hang for 10-15 second before responding. The same thing occurs
>for other apps that access network drives (open/save dialogs, apps
>with directory tree windows, etc).
>Is anyone else seeing this same behavior? Any idea how to trace
>something like this? It isn't network related according to a sniffer -
>it seems more similar to they type of blocking we used to see with old
>ODBC-based applications.
>Thanks|||I am hesitantly optimistic. Since turning that off, things seem
better. I was aware of the option in each server registration about
showing the server state (which, if turned off prompts me everytime I
want to connect) but wasn't aware of the global polling option.
Thank you very, very, very, very much
Steve
On Feb 27, 11:32 pm, Sue Hoegemeier <S...@.nomail.please> wrote:[vbcol=seagreen]
> What happens if you turn off the server state polling in
> Enterprise Manager? On the menu go to tools then select
> options. Clear the checkbox for Server State Polling on the
> General tab.
> You'd also want to check the event logs if turning off the
> polling makes no difference.
> -Sue
> On 27 Feb 2007 12:28:53 -0800, steven.cardi...@.gmail.com
> wrote:
>
>
>

Enterprise Manager blocks network on XP

I'm running XP SP2 (and all the latest hotfixes) and administer a
bunch of SQL2000 servers. Whenever I have enterprise manager running,
I find that my network access seems to block regularly. What I mean by
that is, If I open Explorer and try to access network drives, explorer
will hang for 10-15 second before responding. The same thing occurs
for other apps that access network drives (open/save dialogs, apps
with directory tree windows, etc).
Is anyone else seeing this same behavior? Any idea how to trace
something like this? It isn't network related according to a sniffer -
it seems more similar to they type of blocking we used to see with old
ODBC-based applications.
ThanksWhat happens if you turn off the server state polling in
Enterprise Manager? On the menu go to tools then select
options. Clear the checkbox for Server State Polling on the
General tab.
You'd also want to check the event logs if turning off the
polling makes no difference.
-Sue
On 27 Feb 2007 12:28:53 -0800, steven.cardinal@.gmail.com
wrote:
>I'm running XP SP2 (and all the latest hotfixes) and administer a
>bunch of SQL2000 servers. Whenever I have enterprise manager running,
>I find that my network access seems to block regularly. What I mean by
>that is, If I open Explorer and try to access network drives, explorer
>will hang for 10-15 second before responding. The same thing occurs
>for other apps that access network drives (open/save dialogs, apps
>with directory tree windows, etc).
>Is anyone else seeing this same behavior? Any idea how to trace
>something like this? It isn't network related according to a sniffer -
>it seems more similar to they type of blocking we used to see with old
>ODBC-based applications.
>Thanks|||I am hesitantly optimistic. Since turning that off, things seem
better. I was aware of the option in each server registration about
showing the server state (which, if turned off prompts me everytime I
want to connect) but wasn't aware of the global polling option.
Thank you very, very, very, very much
Steve
On Feb 27, 11:32 pm, Sue Hoegemeier <S...@.nomail.please> wrote:
> What happens if you turn off the server state polling in
> Enterprise Manager? On the menu go to tools then select
> options. Clear the checkbox for Server State Polling on the
> General tab.
> You'd also want to check the event logs if turning off the
> polling makes no difference.
> -Sue
> On 27 Feb 2007 12:28:53 -0800, steven.cardi...@.gmail.com
> wrote:
> >I'm running XP SP2 (and all the latest hotfixes) and administer a
> >bunch of SQL2000 servers. Whenever I have enterprise manager running,
> >I find that my network access seems to block regularly. What I mean by
> >that is, If I open Explorer and try to access network drives, explorer
> >will hang for 10-15 second before responding. The same thing occurs
> >for other apps that access network drives (open/save dialogs, apps
> >with directory tree windows, etc).
> >Is anyone else seeing this same behavior? Any idea how to trace
> >something like this? It isn't network related according to a sniffer -
> >it seems more similar to they type of blocking we used to see with old
> >ODBC-based applications.
> >Thanks

Enterprise manager and remote connection

Hi,
I am trying to connect to two different SQL Servers using my broadband inter
net connection.
Both servers are connected to a network which has one "visible" IP address.
Using the Enterprise Manager, I can connect successfully to one SQL Server,
but cannot access the SQL Server on another of the network servers. Obviousl
y in Enterprise Manager, I am supplying the IP address in order to connect t
o the SQL Servers, but how
do I differentiate between the two different SQL Servers at the other end an
d connect to both at the same IP address?
Many thanks,
Jonathan SIf the servers are on the same physical machine, then the default instance
listens on 1433 and the other "named instance" would listen on another
port. You'll need this info in order to connect. By default Named
Instances listen on dynamic ports. You'll want to change this to a fixed
port and only allow traffic to this specific port for the second instance.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Enterprise manager and remote connection

Hi,
I am trying to connect to two different SQL Servers using my broadband internet connection.
Both servers are connected to a network which has one "visible" IP address.
Using the Enterprise Manager, I can connect successfully to one SQL Server, but cannot access the SQL Server on another of the network servers. Obviously in Enterprise Manager, I am supplying the IP address in order to connect to the SQL Servers, but how
do I differentiate between the two different SQL Servers at the other end and connect to both at the same IP address?
Many thanks,
Jonathan S
If the servers are on the same physical machine, then the default instance
listens on 1433 and the other "named instance" would listen on another
port. You'll need this info in order to connect. By default Named
Instances listen on dynamic ports. You'll want to change this to a fixed
port and only allow traffic to this specific port for the second instance.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.