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 all available sql servers

Is there a possibility to enumerate all available sql servers in pure java.
At this time i execute osl -L and interprete the result.
Process p = runTime.exec("osql -L");
Computers without a msde or sql server installed do not have the osql
program. Is there a way to install osql? Or is there an other way to find all
available sql servers?
Thanks in advance Davy
You could simply try to connect to port 1433 of all machines and/or use
a JDBC driver that supports named pipes and try to connect via named
pipe to each machine in the network. This might take quite a while,
though.
Alin.
|||Thanks for the respond.
I already tried to connect to all machines, but i took a while (some minutes
which is too long).
I also tried to open a socket on port 1433, but sql server does not respond.
Is there a possibility to multicast a udp message to all ms-sql-monitors
(port 1434)?
Pipes is maybe an alternitive, but i'm not sure if pipes are enabled.
Davy
|||UDP port 1434 might just work. Give it a try and let me know how it
goes.
Alin.
|||Davy:
Rolling your own sqlbrowse with UDP on port 1434 should work, but if
you're considering going through OSQL then you'd be better off using
ODBC (SQLBrowseConnect) which is on every Windows box.
Be careful that this broadcast method is pretty imprecise -- Windows
machines after XPSP2 (esp. W2K3+) turn off port 1434 by default, and the
SQL Server 2005 will also ship with this port off by default for most
scenarios.
-shelby
Shelby Goerlitz
Microsoft SQL Server
"Davy" <Davy@.discussions.microsoft.com> wrote in message
news:Davy@.discussions.microsoft.com:
> Thanks for the respond.
> I already tried to connect to all machines, but i took a while (some
> minutes
> which is too long).
> I also tried to open a socket on port 1433, but sql server does not
> respond.
> Is there a possibility to multicast a udp message to all ms-sql-monitors
> (port 1434)?
> Pipes is maybe an alternitive, but i'm not sure if pipes are enabled.
> Davy
|||It’s quite difficult to find out the structure of the udp messages send by
the sql monitor. If anyone knows the udp structure all information is
welcome. I can try to write the udp messages with some help.
Davy
"Alin Sinpalean" schreef:

> UDP port 1434 might just work. Give it a try and let me know how it
> goes.
> Alin.
>
|||So it doesn’t seem a good idea?
I'll give it try, if someone has any idea which information to store in the
udp messages.
I tried the SQLBrowseConnect method as well. I couldn't call it directly
from java, but I found a C++ program.
http://www.sqldev.net/misc/ListSQLSvr.htm
It's easy to use from java and it uses the SQLBrowseConnect API, which is
osql independent.
Thank you very much
Davy
|||> I'll give it try, if someone has any idea which information to store
in the
> udp messages.
Just send a single 0x02 byte. You can find more info about the response
here [jTDS source code]:
http://cvs.sourceforge.net/viewcvs.p...=1.7&view=auto
Alin.
|||So, it's not even that difficult. I'm working on it, i hope to finish it
tomorrow. I'll post the result. Maybe one more question: any idea how to find
out the multicast address?
Right now i'm testing with a fixed 10.0.0.255 multicast address. Probably
this address want be te same for every company, but how can i calculate this
address?
Davy
"Alin Sinpalean" schreef:

> in the
> Just send a single 0x02 byte. You can find more info about the response
> here [jTDS source code]:
> http://cvs.sourceforge.net/viewcvs.p...=1.7&view=auto
> Alin.
>
|||Ok, i created some classes to look up all sql servers reachable by a
computer. The program performs a lookup of all subnets of a computer and send
the 02 multicast message on each subnet.
Where should i put the classes? Here as plain text on the forum?
To lookup the subnet of an ip, I use IPCONFIG. Ipconfig is translated, so I
included english, french and dutch. If you need more languages you should add
the texts. I'll add some comment to the source code.
Davy
"Alin Sinpalean" wrote:

> in the
> Just send a single 0x02 byte. You can find more info about the response
> here [jTDS source code]:
> http://cvs.sourceforge.net/viewcvs.p...=1.7&view=auto
> Alin.
>
sql

EnumAvailableSqlServers does not return all instances

Hello All,

When I execute

SmoApplication.EnumAvailableSqlServers("AServerNameHere"), passing in the hostname of a system I know to be running multiple instances (2x 2000 and 1 x 2005), it only returns/finds the default unnamed instance. I thought this could have been some form of security issue as per http://blogs.msdn.com/sql_protocols/archive/2005/09/24/473502.aspx, however this is not the case because:

1. When I execute SmoAppliction.EnumAvailableSqlServers() (ie - without passing in a servername) ALL instances are discovered on the above hostname.

2. If i maunally execute SmoApplication.EnumAvailableSqlServers("AServernamehere\Instance") it successfully returns/discovers the instance I explicitly pass it.

Isn't this method supposed to return all instances running on the hostname that is specified? If so, in what manner are you to pass in the hostname ("Hostname\%" or "Hostname\*")?

Cheers

Rob

Have you tried passing in the hostname only without the instance name?|||

Hi Michiel,

Absolutely; this is the issue as when I pass in the hostname, EnumAvailableSqlServers only returns the default unnamed instance and not the other n named instances.

Cheers

Rob

|||Sorry to BTT, but does anyone have any info on this?sql

EnumAvailableSqlServers does not return all instances

Hello All,

When I execute

SmoApplication.EnumAvailableSqlServers("AServerNameHere"), passing in the hostname of a system I know to be running multiple instances (2x 2000 and 1 x 2005), it only returns/finds the default unnamed instance. I thought this could have been some form of security issue as per http://blogs.msdn.com/sql_protocols/archive/2005/09/24/473502.aspx, however this is not the case because:

1. When I execute SmoAppliction.EnumAvailableSqlServers() (ie - without passing in a servername) ALL instances are discovered on the above hostname.

2. If i maunally execute SmoApplication.EnumAvailableSqlServers("AServernamehere\Instance") it successfully returns/discovers the instance I explicitly pass it.

Isn't this method supposed to return all instances running on the hostname that is specified? If so, in what manner are you to pass in the hostname ("Hostname\%" or "Hostname\*")?

Cheers

Rob

Have you tried passing in the hostname only without the instance name?|||

Hi Michiel,

Absolutely; this is the issue as when I pass in the hostname, EnumAvailableSqlServers only returns the default unnamed instance and not the other n named instances.

Cheers

Rob

|||Sorry to BTT, but does anyone have any info on this?