enumerating Foreach Loop from rowset

Please let me know if I am on the right track here.

I have an Execute SQL Task that selects multiple rows from an OLE DB connection, each row containing 3 columns (data types = string, Int32, Int32). In this task ResultSet = "Full result set" and Result Set > Result Name = 0, Variable Name = [User::viewInfo] which is a user variable with Data Type = Object.

I want to use a Foreach Loop Container to enumerate over the result set rows that are contained in the [User::viewInfo] variable described above. For each resultset row I want to breakout the 3 column values and assign them to 3 corresponding variables that can be referenced in a Data Flow in the Foreach Loop.

Current settings for the Foreach Loop Container: Collection > Enumerator = "Foreach ADO Enumerator", Collection > Enumerator Configuration > ADO object source variable = [User::viewInfo], Enumeration mode = "Rows in the first table". On the Variable Mappings page I select the 3 corresponding user variables I want the rowset column values assigned to, with indexes starting at 1 (not 0).

Thanks - Dana Reed

It sounds spot on to me Dana!

-Jamie

Enumerate/list data sources and their connection strings

I was wondering ..
Is there an easy way to enumerate all data sources and their configuration
parameters (e.g. connection strings) in a reporting services database ?
Thanks a bunch.Isn't there really anybody here on the forum with a suggestion how to
achieve this or is it actually so trivial that I don't deserve a reply ;) ?

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: The connection to SQL Server <server> has been broken.

All~
I am currently running the most recent version of MSDE and using EM as
a front end interface. In EM, I go to look at the databases I had
previously created, but receive the message:
The connection to SQL Server <server> has been broken. The connection
to the SQL Server is broken. Do you want to try reconnection...
I am unable to start SQL Agent, but I am not sure whether this is the
cause. I am able to connect via the Query Analyzer facility fine. I
get the following SQLDump [truncated].
What is the EXCEPTION_ACCESS_VIOLATION and how can I fix it?
...
**
*
* BEGIN STACK DUMP:
* 07/31/04 09:59:24 spid 0
*
* Exception Address = 00000000
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
...
Your help is greatly appreciated.
Richard
Check your SQL Server error logs for more information. Also
check the SQL Agent log and see if you find any errors
related to that issue. You may also want to check the stack
dump and compare it with that mentioned in the following
article:
PRB: Removal of Guest Account May Cause Handled Exception
Access Violation in SQL Server
http://support.microsoft.com/?id=315523
-Sue
On 31 Jul 2004 07:22:35 -0700, rkinnie01@.excite.com (Richard
K) wrote:

>All~
>I am currently running the most recent version of MSDE and using EM as
>a front end interface. In EM, I go to look at the databases I had
>previously created, but receive the message:
>The connection to SQL Server <server> has been broken. The connection
>to the SQL Server is broken. Do you want to try reconnection...
>I am unable to start SQL Agent, but I am not sure whether this is the
>cause. I am able to connect via the Query Analyzer facility fine. I
>get the following SQLDump [truncated].
>What is the EXCEPTION_ACCESS_VIOLATION and how can I fix it?
>...
>**
>*
>* BEGIN STACK DUMP:
>* 07/31/04 09:59:24 spid 0
>*
>* Exception Address = 00000000
>* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
>* Access Violation occurred reading address 00000000
>...
>Your help is greatly appreciated.
>Richard

Enterprise Manager: The connection to SQL Server <server> has been broken.

All~
I am currently running the most recent version of MSDE and using EM as
a front end interface. In EM, I go to look at the databases I had
previously created, but receive the message:
The connection to SQL Server <server> has been broken. The connection
to the SQL Server is broken. Do you want to try reconnection...
I am unable to start SQL Agent, but I am not sure whether this is the
cause. I am able to connect via the Query Analyzer facility fine. I
get the following SQLDump [truncated].
What is the EXCEPTION_ACCESS_VIOLATION and how can I fix it?
...
**
*
* BEGIN STACK DUMP:
* 07/31/04 09:59:24 spid 0
*
* Exception Address = 00000000
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
...
Your help is greatly appreciated.
RichardCheck your SQL Server error logs for more information. Also
check the SQL Agent log and see if you find any errors
related to that issue. You may also want to check the stack
dump and compare it with that mentioned in the following
article:
PRB: Removal of Guest Account May Cause Handled Exception
Access Violation in SQL Server
http://support.microsoft.com/?id=315523
-Sue
On 31 Jul 2004 07:22:35 -0700, rkinnie01@.excite.com (Richard
K) wrote:

>All~
>I am currently running the most recent version of MSDE and using EM as
>a front end interface. In EM, I go to look at the databases I had
>previously created, but receive the message:
>The connection to SQL Server <server> has been broken. The connection
>to the SQL Server is broken. Do you want to try reconnection...
>I am unable to start SQL Agent, but I am not sure whether this is the
>cause. I am able to connect via the Query Analyzer facility fine. I
>get the following SQLDump [truncated].
>What is the EXCEPTION_ACCESS_VIOLATION and how can I fix it?
>...
>**
>*
>* BEGIN STACK DUMP:
>* 07/31/04 09:59:24 spid 0
>*
>* Exception Address = 00000000
>* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
>* Access Violation occurred reading address 00000000
>...
>Your help is greatly appreciated.
>Richard

Enterprise Manager connection to Local server very sloooow.

Hi,
I'm having a problem connecting to a local copy of SQL server 2000 using
enterprise manager (on Win2k). Doing anything, connecting, refreshing
list of tables etc. takes a very long time (>10secs) slower even than
remote connections.
Also, the enterprise manager itself becomes doggedly slow - eg. tringing
to move a window across the screen is difficult becuase it is so
stuttery. I have checked task manager, and it's not using much CPU time
though.
One other clue is that when you use something like the DTS wizard, the
local server is not explicitly displayed as one of the available servers.
This has only started happening recently. Any ideas on what is going on?
Thanks!Check to see if you have the Auto-Close option turned on any of your databases.
and check to see if OBDC tracing is turned on.|||Thanks. Auto-Close was turned on, ODBC tracing was not.
I've turned auto-close off now, and this seems to have helped quite a
lot. Still a bit slow though, so makes me think there is something else
going on.
Any idea why the local server would not be displayed in the 'select
server' dropdown?
> Check to see if you have the Auto-Close option turned on any of your databases.
> and check to see if OBDC tracing is turned on.
>|||Thanks. Auto-Close was turned on, ODBC tracing was not.
I've turned auto-close off now, and this seems to have helped quite a
lot. Still a bit slow though, so makes me think there is something else
going on.
Any idea why the local server would not be displayed in the 'select
server' dropdown?
> Check to see if you have the Auto-Close option turned on any of your databases.
> and check to see if OBDC tracing is turned on.
>|||Sosh123 wrote:
> Thanks. Auto-Close was turned on, ODBC tracing was not.
> I've turned auto-close off now, and this seems to have helped quite a
> lot. Still a bit slow though, so makes me think there is something
> else going on.
> Any idea why the local server would not be displayed in the 'select
> server' dropdown?
>> Check to see if you have the Auto-Close option turned on any of your
>> databases. and check to see if OBDC tracing is turned on.
Did you check all your databases for Auto-Close?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Also check to make sure that none of the network components between you and
the server have their network connections set to Auto-negotiate, especially
through a switch. The protocol is not standardize and oftentimes will set
everything to 10 Mb, Half-duplex.
Also check your SQLEM Options to NOT reconnect to last connection on
relauch.
Sincerely,
Anthony Thomas
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23BhAFC7ZFHA.3620@.TK2MSFTNGP09.phx.gbl...
Sosh123 wrote:
> Thanks. Auto-Close was turned on, ODBC tracing was not.
> I've turned auto-close off now, and this seems to have helped quite a
> lot. Still a bit slow though, so makes me think there is something
> else going on.
> Any idea why the local server would not be displayed in the 'select
> server' dropdown?
>> Check to see if you have the Auto-Close option turned on any of your
>> databases. and check to see if OBDC tracing is turned on.
Did you check all your databases for Auto-Close?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

Enterprise Manager connection to Local server very sloooow.

Hi,
I'm having a problem connecting to a local copy of SQL server 2000 using
enterprise manager (on Win2k). Doing anything, connecting, refreshing
list of tables etc. takes a very long time (>10secs) slower even than
remote connections.
Also, the enterprise manager itself becomes doggedly slow - eg. tringing
to move a window across the screen is difficult becuase it is so
stuttery. I have checked task manager, and it's not using much CPU time
though.
One other clue is that when you use something like the DTS wizard, the
local server is not explicitly displayed as one of the available servers.
This has only started happening recently. Any ideas on what is going on?
Thanks!
Check to see if you have the Auto-Close option turned on any of your databases.
and check to see if OBDC tracing is turned on.
|||Thanks. Auto-Close was turned on, ODBC tracing was not.
I've turned auto-close off now, and this seems to have helped quite a
lot. Still a bit slow though, so makes me think there is something else
going on.
Any idea why the local server would not be displayed in the 'select
server' dropdown?

> Check to see if you have the Auto-Close option turned on any of your databases.
> and check to see if OBDC tracing is turned on.
>
|||Thanks. Auto-Close was turned on, ODBC tracing was not.
I've turned auto-close off now, and this seems to have helped quite a
lot. Still a bit slow though, so makes me think there is something else
going on.
Any idea why the local server would not be displayed in the 'select
server' dropdown?

> Check to see if you have the Auto-Close option turned on any of your databases.
> and check to see if OBDC tracing is turned on.
>
|||Sosh123 wrote:[vbcol=seagreen]
> Thanks. Auto-Close was turned on, ODBC tracing was not.
> I've turned auto-close off now, and this seems to have helped quite a
> lot. Still a bit slow though, so makes me think there is something
> else going on.
> Any idea why the local server would not be displayed in the 'select
> server' dropdown?
Did you check all your databases for Auto-Close?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Also check to make sure that none of the network components between you and
the server have their network connections set to Auto-negotiate, especially
through a switch. The protocol is not standardize and oftentimes will set
everything to 10 Mb, Half-duplex.
Also check your SQLEM Options to NOT reconnect to last connection on
relauch.
Sincerely,
Anthony Thomas

"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23BhAFC7ZFHA.3620@.TK2MSFTNGP09.phx.gbl...
Sosh123 wrote:[vbcol=seagreen]
> Thanks. Auto-Close was turned on, ODBC tracing was not.
> I've turned auto-close off now, and this seems to have helped quite a
> lot. Still a bit slow though, so makes me think there is something
> else going on.
> Any idea why the local server would not be displayed in the 'select
> server' dropdown?
Did you check all your databases for Auto-Close?
David Gugick
Quest Software
www.imceda.com
www.quest.com

Enterprise Manager connection to Local server very sloooow.

Hi,
I'm having a problem connecting to a local copy of SQL server 2000 using
enterprise manager (on Win2k). Doing anything, connecting, refreshing
list of tables etc. takes a very long time (>10secs) slower even than
remote connections.
Also, the enterprise manager itself becomes doggedly slow - eg. tringing
to move a window across the screen is difficult becuase it is so
stuttery. I have checked task manager, and it's not using much CPU time
though.
One other clue is that when you use something like the DTS wizard, the
local server is not explicitly displayed as one of the available servers.
This has only started happening recently. Any ideas on what is going on?
Thanks!Check to see if you have the Auto-Close option turned on any of your databas
es.
and check to see if OBDC tracing is turned on.|||Thanks. Auto-Close was turned on, ODBC tracing was not.
I've turned auto-close off now, and this seems to have helped quite a
lot. Still a bit slow though, so makes me think there is something else
going on.
Any idea why the local server would not be displayed in the 'select
server' dropdown?

> Check to see if you have the Auto-Close option turned on any of your datab
ases.
> and check to see if OBDC tracing is turned on.
>|||Thanks. Auto-Close was turned on, ODBC tracing was not.
I've turned auto-close off now, and this seems to have helped quite a
lot. Still a bit slow though, so makes me think there is something else
going on.
Any idea why the local server would not be displayed in the 'select
server' dropdown?

> Check to see if you have the Auto-Close option turned on any of your datab
ases.
> and check to see if OBDC tracing is turned on.
>|||Sosh123 wrote:[vbcol=seagreen]
> Thanks. Auto-Close was turned on, ODBC tracing was not.
> I've turned auto-close off now, and this seems to have helped quite a
> lot. Still a bit slow though, so makes me think there is something
> else going on.
> Any idea why the local server would not be displayed in the 'select
> server' dropdown?
>
Did you check all your databases for Auto-Close?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Also check to make sure that none of the network components between you and
the server have their network connections set to Auto-negotiate, especially
through a switch. The protocol is not standardize and oftentimes will set
everything to 10 Mb, Half-duplex.
Also check your SQLEM Options to NOT reconnect to last connection on
relauch.
Sincerely,
Anthony Thomas
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23BhAFC7ZFHA.3620@.TK2MSFTNGP09.phx.gbl...
Sosh123 wrote:[vbcol=seagreen]
> Thanks. Auto-Close was turned on, ODBC tracing was not.
> I've turned auto-close off now, and this seems to have helped quite a
> lot. Still a bit slow though, so makes me think there is something
> else going on.
> Any idea why the local server would not be displayed in the 'select
> server' dropdown?
>
Did you check all your databases for Auto-Close?
David Gugick
Quest Software
www.imceda.com
www.quest.com

Enterprise Manager connection question ..

Hi ;)

I have a database on my server provider ..and i connect via Enterprise Manager ...the thing i want to know is if theres a way to connect direct to my own database ..instead of having all databases in the tree layout and then selecting mine

is there a way in Enterprise Manager to point the Register Sql Server direct to my database ??

Thanksyes, you can create groups at the top of the tree. So I have "Local" and "Others". That way I don't see, or try to comm to, the ones in the "other" node when I look at my "local" one

Enterprise Manager Connection Issues

Hello,
I am having trouble connecting from enterprise
manager to a sql 2000 box. I can connect to all of our
other production boxes. This is a new box that we built
and I cannot connect. When I go to register it in
Enterprise Manager the error message says "Driver Not
Capable". I'm not sure exactly what this means. All I'm
trying to do is connect to the box. Is there a driver
either on my local laptop or on the server side that
isn't set up right or maybe needs to be updated? I can
open up the database locally on the server and I can
register other servers on it and have that server
registered to other servers. I know other people on my
team are not able to connect to it as well, so I know
it's not just me. Any help would be greatly
appreciated. Thanks.
-Craig CoffaroHello -
The error message "Driver not capable" indicates the problem
to be with the SQL driver. Check the registry entry
HKEY_LOCAL_MACHNE\Software\ODBC\ODBCINST
.INI
Check the driver location in the above registry entry. Check for
SQLsrv32.dll version. If the registry location is pointing to incorrect
file, change it and make it point to the correct one.
Have you installed Crystal reports after installing SQL server?
Regards,
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
"Craig Coffaro" <craig.coffaro@.kroger.com> wrote in message
news:902d01c4052c$5d59e990$a501280a@.phx.gbl...
> Hello,
> I am having trouble connecting from enterprise
> manager to a sql 2000 box. I can connect to all of our
> other production boxes. This is a new box that we built
> and I cannot connect. When I go to register it in
> Enterprise Manager the error message says "Driver Not
> Capable". I'm not sure exactly what this means. All I'm
> trying to do is connect to the box. Is there a driver
> either on my local laptop or on the server side that
> isn't set up right or maybe needs to be updated? I can
> open up the database locally on the server and I can
> register other servers on it and have that server
> registered to other servers. I know other people on my
> team are not able to connect to it as well, so I know
> it's not just me. Any help would be greatly
> appreciated. Thanks.
> -Craig Coffaro|||Chandra,
Yes I did install Crystal Reports 6 last week and ever since then
I have had problems with my SQL connections. Thanks for the advice and
I will give this a try. Thanks again.
Craig
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Chandra,
One other question I have is when I go to the registry:
HKEY_LOCAL_MACHNE\Software\ODBC\ODBCINST
.INI and I go down to SQL Server
I see in the window on the right, Driver and Setup both have the same
path with the sqlsrv32.dll in it. I also see DrverODBCver being
03.50...what exactly am I looking for or what do I need to be doing
witht this info? Is there a version of the ODBC driver that I should be
updating? Where exactly can I find this? Or is it the path
C:\WINDOWS\SYSTEM\sqlsrv32.dll that is the problem, if this is the
problem what path should it be? How does the installation of Crystal
Reports affect SQL? Any help you have for me Chandra is greatly
apprecaited. Thanks.
-Craig Coffaro
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||I'm not clear on what your question is. Do you need to find the version of
MDAC? If so, you can download the MDAC Component Checker from
microsoft.com/downloads.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Enterprise Manager Connection

I need to open up my firewall so that a client can access my SQL server
using the Enterprise Manager. I opened up port 1433, but apparently
that's just the data connection port, not for management.
Where can I get a list of all SQL related ports?
Thanks!
--DaveINF: TCP Ports Needed for Communication to SQL Server Through a Firewall
http://support.microsoft.com/defaul...2&Product=sql2k
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dave Navarro" <dave@.dave.dave> wrote in message
news:MPG.1a959ad45c280f7a989782@.news-40.giganews.com...
> I need to open up my firewall so that a client can access my SQL server
> using the Enterprise Manager. I opened up port 1433, but apparently
> that's just the data connection port, not for management.
> Where can I get a list of all SQL related ports?
> Thanks!
> --Dave|||You can use 1433 for management if the client is connecting using TCP
Sockets. They can create an alias or change the NetLib choice in the Client
Network Utility to use 1433 for this. If they are using Named Pipes
(probably) then they are using the same ports as TCP over NetBios. I think
it is 135, 137, and 138. If they are using MultiProtocol (doubt it) they
are using the ports for RPC, 135 for the mapper and then 50,000 and up.
Christian Smith
"Dave Navarro" <dave@.dave.dave> wrote in message
news:MPG.1a959ad45c280f7a989782@.news-40.giganews.com...
> I need to open up my firewall so that a client can access my SQL server
> using the Enterprise Manager. I opened up port 1433, but apparently
> that's just the data connection port, not for management.
> Where can I get a list of all SQL related ports?
> Thanks!
> --Dave|||In article <OPZAL5Z8DHA.2480@.TK2MSFTNGP12.phx.gbl>,
SRDBA@.Careerbuilder.com says...
> INF: TCP Ports Needed for Communication to SQL Server Through a Firewall
> http://support.microsoft.com/defaul...www.sqlpass.org
> "Dave Navarro" <dave@.dave.dave> wrote in message
> news:MPG.1a959ad45c280f7a989782@.news-40.giganews.com...
Thanks for the URL.
Unfortunately, I can't connect to the server using Enterprise Manager.
I can connect to it from ASP, however. The FAQ isn't specific as to
what the connection is used for, but from experience it's used to send
queries/receive data (strictly for data access).
When I am "inside" the firewall and use Enterprise Manager to connect
from another computer on the same network, it works just fine. But I
can't connect to it using Enterprise Manager from outside of the
firewall.
--Dave|||In article <u39HU5Z8DHA.1632@.TK2MSFTNGP12.phx.gbl>, csmith@.digex.com
says...
> You can use 1433 for management if the client is connecting using TCP
> Sockets. They can create an alias or change the NetLib choice in the Clie
nt
> Network Utility to use 1433 for this. If they are using Named Pipes
> (probably) then they are using the same ports as TCP over NetBios. I thin
k
> it is 135, 137, and 138. If they are using MultiProtocol (doubt it) they
> are using the ports for RPC, 135 for the mapper and then 50,000 and up.
> Christian Smith
> "Dave Navarro" <dave@.dave.dave> wrote in message
> news:MPG.1a959ad45c280f7a989782@.news-40.giganews.com...
Thanks for the info. I can successfully connect to port 1433 on the
server using ASP code running in IIS from outside the firewall.
However, Enterprise Manager will not make a connection to the server at
all.
--Dave|||I download TDImon from www.sysinternals.com and watched the
connections...
Enterprise Manager uses port 1433 for its data connection and port 1434
for the management connection.
It's working now.
--Dave
In article <MPG.1a9600c12aad755f989784@.news-40.giganews.com>,
dave@.dave.dave says...
> In article <u39HU5Z8DHA.1632@.TK2MSFTNGP12.phx.gbl>, csmith@.digex.com
> says...
> Thanks for the info. I can successfully connect to port 1433 on the
> server using ASP code running in IIS from outside the firewall.
> However, Enterprise Manager will not make a connection to the server at
> all.

Enterprise Manager Connection

I need to open up my firewall so that a client can access my SQL server
using the Enterprise Manager. I opened up port 1433, but apparently
that's just the data connection port, not for management.
Where can I get a list of all SQL related ports?
Thanks!
--DaveYou can use 1433 for management if the client is connecting using TCP
Sockets. They can create an alias or change the NetLib choice in the Client
Network Utility to use 1433 for this. If they are using Named Pipes
(probably) then they are using the same ports as TCP over NetBios. I think
it is 135, 137, and 138. If they are using MultiProtocol (doubt it) they
are using the ports for RPC, 135 for the mapper and then 50,000 and up.
Christian Smith
"Dave Navarro" <dave@.dave.dave> wrote in message
news:MPG.1a959ad45c280f7a989782@.news-40.giganews.com...
> I need to open up my firewall so that a client can access my SQL server
> using the Enterprise Manager. I opened up port 1433, but apparently
> that's just the data connection port, not for management.
> Where can I get a list of all SQL related ports?
> Thanks!
> --Dave|||INF: TCP Ports Needed for Communication to SQL Server Through a Firewall
http://support.microsoft.com/default.aspx?scid=kb;en-us;287932&Product=sql2k
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dave Navarro" <dave@.dave.dave> wrote in message
news:MPG.1a959ad45c280f7a989782@.news-40.giganews.com...
> I need to open up my firewall so that a client can access my SQL server
> using the Enterprise Manager. I opened up port 1433, but apparently
> that's just the data connection port, not for management.
> Where can I get a list of all SQL related ports?
> Thanks!
> --Dave|||In article <OPZAL5Z8DHA.2480@.TK2MSFTNGP12.phx.gbl>,
SRDBA@.Careerbuilder.com says...
> INF: TCP Ports Needed for Communication to SQL Server Through a Firewall
> http://support.microsoft.com/default.aspx?scid=kb;en-us;287932&Product=sql2k
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Dave Navarro" <dave@.dave.dave> wrote in message
> news:MPG.1a959ad45c280f7a989782@.news-40.giganews.com...
> > I need to open up my firewall so that a client can access my SQL server
> > using the Enterprise Manager. I opened up port 1433, but apparently
> > that's just the data connection port, not for management.
> >
> > Where can I get a list of all SQL related ports?
Thanks for the URL.
Unfortunately, I can't connect to the server using Enterprise Manager.
I can connect to it from ASP, however. The FAQ isn't specific as to
what the connection is used for, but from experience it's used to send
queries/receive data (strictly for data access).
When I am "inside" the firewall and use Enterprise Manager to connect
from another computer on the same network, it works just fine. But I
can't connect to it using Enterprise Manager from outside of the
firewall.
--Dave|||In article <u39HU5Z8DHA.1632@.TK2MSFTNGP12.phx.gbl>, csmith@.digex.com
says...
> You can use 1433 for management if the client is connecting using TCP
> Sockets. They can create an alias or change the NetLib choice in the Client
> Network Utility to use 1433 for this. If they are using Named Pipes
> (probably) then they are using the same ports as TCP over NetBios. I think
> it is 135, 137, and 138. If they are using MultiProtocol (doubt it) they
> are using the ports for RPC, 135 for the mapper and then 50,000 and up.
> Christian Smith
> "Dave Navarro" <dave@.dave.dave> wrote in message
> news:MPG.1a959ad45c280f7a989782@.news-40.giganews.com...
> > I need to open up my firewall so that a client can access my SQL server
> > using the Enterprise Manager. I opened up port 1433, but apparently
> > that's just the data connection port, not for management.
> >
> > Where can I get a list of all SQL related ports?
Thanks for the info. I can successfully connect to port 1433 on the
server using ASP code running in IIS from outside the firewall.
However, Enterprise Manager will not make a connection to the server at
all.
--Dave|||I download TDImon from www.sysinternals.com and watched the
connections...
Enterprise Manager uses port 1433 for its data connection and port 1434
for the management connection.
It's working now.
--Dave
In article <MPG.1a9600c12aad755f989784@.news-40.giganews.com>,
dave@.dave.dave says...
> In article <u39HU5Z8DHA.1632@.TK2MSFTNGP12.phx.gbl>, csmith@.digex.com
> says...
> > You can use 1433 for management if the client is connecting using TCP
> > Sockets. They can create an alias or change the NetLib choice in the Client
> > Network Utility to use 1433 for this. If they are using Named Pipes
> > (probably) then they are using the same ports as TCP over NetBios. I think
> > it is 135, 137, and 138. If they are using MultiProtocol (doubt it) they
> > are using the ports for RPC, 135 for the mapper and then 50,000 and up.
> >
> > Christian Smith
> >
> > "Dave Navarro" <dave@.dave.dave> wrote in message
> > news:MPG.1a959ad45c280f7a989782@.news-40.giganews.com...
> > > I need to open up my firewall so that a client can access my SQL server
> > > using the Enterprise Manager. I opened up port 1433, but apparently
> > > that's just the data connection port, not for management.
> > >
> > > Where can I get a list of all SQL related ports?
> Thanks for the info. I can successfully connect to port 1433 on the
> server using ASP code running in IIS from outside the firewall.
> However, Enterprise Manager will not make a connection to the server at
> all.

Enterprise Manager appears to slow my dial up connection

I am using a dial up connection to the Internet and to an online SQL
Server database.

I have a problem in that when I use Enterprise Manager to access even
my local server , or even, it appears, when it is just open with no
connection to any SQL Server, I experience very slow data transfer
over my normal dial up connection.

I am reasonably sure it is Enterprise Manager causing the problem as
when I close it and redial I have an acceptable rate of data transfer
using my dial up connection.

Can anyone:

a. support my conclusion that Enterprise Manager is the probable
cause of the slow connection.

b. suggest a solution

Best wishes, John MorganJohn Morgan (jfm@.XXwoodlander.co.uk) writes:
> I am using a dial up connection to the Internet and to an online SQL
> Server database.
> I have a problem in that when I use Enterprise Manager to access even
> my local server , or even, it appears, when it is just open with no
> connection to any SQL Server, I experience very slow data transfer
> over my normal dial up connection.
> I am reasonably sure it is Enterprise Manager causing the problem as
> when I close it and redial I have an acceptable rate of data transfer
> using my dial up connection.
> Can anyone:
> a. support my conclusion that Enterprise Manager is the probable
> cause of the slow connection.
> b. suggest a solution

I don't have a configuration that I easily can test from, but I have a
guess that you could try: for all your remote servers, right-click and
pick Edit SQL Server Registration Properties and uncheck "Display server
state in console".

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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.

Enterprise Manager - SQL Server does not exist or access denied

I've searched around google & here and can't find a solution.
I can connect to my database via a connection string on the web serverbut cannot connect via Enterprise Manager or just by ODBC connection onmy local machine.
I can ping the server fine and switched off my XP firewall & my router firewall, port 1433 is also open.
Someone has tested this from another machine and it works fine (cheers).
What else can I try, this is driving me insane!
Thanks in advance
R
p.s I'm on Win XP SP2, SQL Server 2000 sp3a
Did the someone that tested have SP2 or SP1? I recently hadproblems with Enterprise Manager, SQL Server with SP2. I triedthe Microsoft fixes and was unsuccessful. I noticed the problemoccured pre-SP2, once I uninstalled SP2... it worked fine. Icould be wrong and you may have already tried this or experienced withsomeone elses machine w/ SP2 and may work fine, but I suppose if youhaven't tried it yet... it could be something to look at.
I'm using WinXP SP1, SQL Server 2000 sp3a
|||Port 1433 is a TCP port but you can connect to SQL Server through UDP port and Named pipe if you right click in Enterprise manager and go to the server properties may be your firewall have removed some of the connection points and Microsoft have other ports near 1433 reserved but not used. Hope this helps.|||I did the testing and I'm using WinXP SP2, SQL Server 2000 Personal Edition Service Pack 3
|||I stand corrected... then it must the firewall settings for port 1433
|||Ok, now i'm in trouble.
I completly reinstalled XP (Service Pack 1) and am now going straightinto my set top box for my net connection (no firewall/router involved)
I have not installed SQL Server but I cannot connect to my database via a "UDL" file.
I should be able to connect using the SQL Server driver shouldn't I, without installing SQL Server?
If someone could PM me or get me on MSN I would appreciate someone else trying to connect to my database.
R

|||

shincello wrote:

If someone could PM me or get me on MSN I would appreciate someone else trying to connect to my database.

I am still able to connect to the server using the previously-supplied credentials.
|||Ok, thanks tmorton, I didn't realise it was you who checked it before.
Could I just confirm that I should be able connect through the SQL driver in a UDL file WITHOUT installing SQL Server?
I can access the SQL Server within an ASP.NET page on 2 differentexternal servers (web based) but cannot access the server from my'localhost' ASP.NET pages or through the direct UDL link.
The only thought I have beyond this is that my ISP must be blocking some connection?
|||Resolved!
I hooked up to another ISP and it worked straight away, weird eh?
Now i've got to speak to them to try to resolve it..
So anyone on NTL broadband in the UK WATCH OUT!
R
|||I spoke to my ISP and they gave me a list of proxy servers to go through to try to resolve this.
1) Is this likely to help at all?
2) How do I change the proxy server for Enterprise Manager? Or can ijust change my Proxy server for my whole network connection?
Cheers
Rich
|||

shincello wrote:

I spoke to my ISP and they gave me a list of proxy servers to go through to try to resolve this.
1) Is this likely to help at all?
2) How do I change the proxy server for Enterprise Manager? Or can ijust change my Proxy server for my whole network connection?

I'm sorry, this is outside my area of knowledge. Hopefully someone else will chime in.

Enterprise manager / register with name

Hi,

I connect to a server on an other location with a VPN connection.
The SQL server on the other side is running an instance "MAINSERVER"
There are no problems to register the server locally as mainserver, but when i connect with the VPN it isnt't possible to register the server with the name "MAINSERVER"
It is only possible to register with its IP address.

Has anyone an idea why i cant't connect using the name?

Thanks!Hey Leo,

Sounds like you have a DNS issue. Just a guess but are you running Windows 2000. I have had many problems with VPN when it comes to DNS and WINS. On the other hand my XP machines have no problem. I'm not sure what it is about 2000 but I would like to see what kind of answers you get on this one. Have you yourself looked into any DNS issues?

Kal

Enterprise manager (romote connection problem)

hi everybody

i have a problem while trying to connect to my sql server database

1st i have created my database from my HELM control panel

database name :ramy

IP Address: 74.52.76.178

Local Port: 1433

Remote Port: 1501

thats all but now iam trying to open it via ms sql server enterprise manager but i cant

1st i click on register server

then he ask me for available servers i write :74.52.76.178

then i select sql server login info

then he asks about username and password

username : ramy

password : 5402248

(these are correct values ) use it freely its just for trials

i get error message that he cant connect or login faliure

my question now

where i put the ip address and where to put the port

thanks

well this question should be posted to a SQL Server forum because this forum is for accessing databases via ASP.NET through programming.

Also, for the sake of security and my faith in mankind : Dont post your ip address, username, and password. Your just Begging people to come hack you.

mcm

|||

at all thanks man for your reply

about hacking my database never mind about this as i said its trial database

i dont know where is this forum you talk about

i which somebody help me

|||

MSDN Forums SQL Threads are here :

http://forums.microsoft.com/msdn/default.aspx?forumgroupid=19&siteid=1

hth,mcm

Enterprise Manager - SQL Server does not exist or access denied

I've searched around google & here and can't find a solution.
I can connect to my database via a connection string on the web serverbut cannot connect via Enterprise Manager or just by ODBC connection onmy local machine.
I can ping the server fine and switched off my XP firewall & my router firewall, port 1433 is also open.
Someone has tested this from another machine and it works fine (cheers).
What else can I try, this is driving me insane!
Thanks in advance
R
p.s I'm on Win XP SP2, SQL Server 2000 sp3a
Did the someone that tested have SP2 or SP1? I recently hadproblems with Enterprise Manager, SQL Server with SP2. I triedthe Microsoft fixes and was unsuccessful. I noticed the problemoccured pre-SP2, once I uninstalled SP2... it worked fine. Icould be wrong and you may have already tried this or experienced withsomeone elses machine w/ SP2 and may work fine, but I suppose if youhaven't tried it yet... it could be something to look at.
I'm using WinXP SP1, SQL Server 2000 sp3a
|||Port 1433 is a TCP port but you can connect to SQL Server through UDP port and Named pipe if you right click in Enterprise manager and go to the server properties may be your firewall have removed some of the connection points and Microsoft have other ports near 1433 reserved but not used. Hope this helps.|||I did the testing and I'm using WinXP SP2, SQL Server 2000 Personal Edition Service Pack 3
|||I stand corrected... then it must the firewall settings for port 1433
|||Ok, now i'm in trouble.
I completly reinstalled XP (Service Pack 1) and am now going straightinto my set top box for my net connection (no firewall/router involved)
I have not installed SQL Server but I cannot connect to my database via a "UDL" file.
I should be able to connect using the SQL Server driver shouldn't I, without installing SQL Server?
If someone could PM me or get me on MSN I would appreciate someone else trying to connect to my database.
R

|||

shincello wrote:

If someone could PM me or get me on MSN I would appreciate someone else trying to connect to my database.

I am still able to connect to the server using the previously-supplied credentials.
|||Ok, thanks tmorton, I didn't realise it was you who checked it before.
Could I just confirm that I should be able connect through the SQL driver in a UDL file WITHOUT installing SQL Server?
I can access the SQL Server within an ASP.NET page on 2 differentexternal servers (web based) but cannot access the server from my'localhost' ASP.NET pages or through the direct UDL link.
The only thought I have beyond this is that my ISP must be blocking some connection?
|||Resolved!
I hooked up to another ISP and it worked straight away, weird eh?
Now i've got to speak to them to try to resolve it..
So anyone on NTL broadband in the UK WATCH OUT!
R
|||I spoke to my ISP and they gave me a list of proxy servers to go through to try to resolve this.
1) Is this likely to help at all?
2) How do I change the proxy server for Enterprise Manager? Or can ijust change my Proxy server for my whole network connection?
Cheers
Rich
|||

shincello wrote:

I spoke to my ISP and they gave me a list of proxy servers to go through to try to resolve this.
1) Is this likely to help at all?
2) How do I change the proxy server for Enterprise Manager? Or can ijust change my Proxy server for my whole network connection?

I'm sorry, this is outside my area of knowledge. Hopefully someone else will chime in.

Enterprice manager.

Hi there.
When i allow remote connection to my sql with Enterprice Manager .. users
see
the list of all databases but can only enter there own .
How can i make it so, so they only see there own database witch there user
has
access to ?
Regards.
Steinarr.Hi,
This is not possible in SQL 7.0 and SQL 2000.
Thanks
Hari
SQL Server MVP
"Steinarr G." <steinki@.dynamicsystems.dk> wrote in message
news:eH$JN3JrFHA.3264@.TK2MSFTNGP12.phx.gbl...
> Hi there.
> When i allow remote connection to my sql with Enterprice Manager .. users
> see
> the list of all databases but can only enter there own .
> How can i make it so, so they only see there own database witch there user
> has
> access to ?
> Regards.
> Steinarr.
>|||Thanks Hari.
Now at least i know it for sure.
Regards.
Steinarr.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ueE%2364JrFHA.716@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This is not possible in SQL 7.0 and SQL 2000.
> Thanks
> Hari
> SQL Server MVP
> "Steinarr G." <steinki@.dynamicsystems.dk> wrote in message
> news:eH$JN3JrFHA.3264@.TK2MSFTNGP12.phx.gbl...
>