Loop Through SQL Server Instance ?

Hi,
Is it possible to loop through dynamically through all sql instances in a particular environment ?
Thanking you in advance.
Regards,
RanjitHi,

Is it possible to loop through dynamically through all sql instances in a particular environment ?

So you dynamically finding all SQL Servers and the connecting through (insert ADO or DMO here)? Or do you mean looping through a list of servers provided by another source.

The former can be a bit tricky; lots of ways to "hide" a SQL Instance (google SQL Recon). The latter should be relatively trivial.

Regards,

hmscott|||Hi hmscott .. Yup ..I wanted the former ..
Thanks so much for the sql recon .. It did rack up my grey cells to dig further .. It seems they will be coming out with a command line version in the near future ..

Just wondering about "lots of ways to "hide" a SQL Instance" .. the one I can think of being a newbie is change the default port of sql server ..any other ? ..

Thanks,
Ranjit.

Loooping through SELECT values in SQL

Hi,

Environment - VB.NET, ASP.NET, SQL Server 2000.

In a SQL User-Defined Function, I am selecting a row which returns multiple values. I need to construct one single string out of those returned values. To do that, I am using CUROSR.

Now, CURSOR is expensive operation. If there are 1000 users at a time, it will consume lot of resources.

Is there a way, I can construct this String without using CURSORs??

Please advice. Thanks

PankajYou should be able to do something like this to concatenate the columns together:

DECLARE @.myResult VARCHAR(8000)
SET @.myResult = ''
SELECT
 @.myResult = @.myResult + myColumn1 + myColumn2 + myColumn3 + myColumn4
FROM
 myTable

Terri|||my mistake, the multiple values selected are from the same column (multiple rows)|||That's OK. you can still use the same method:

DECLARE @.myResult VARCHAR(8000)
SET @.myResult = ''
SELECT
 @.myResult = @.myResult + myColumn1
FROM
 myTable

Terri|||Even though the described SQL works this not a supported T-SQL approach. As per the documentation deom BOL on Select statement :
:: If the SELECT statement returns more than one value, the variable is assigned the last value returned.::

And such an approach is dangerous and can be removed in future builds or service packs. Donot rely on them.

Check out a thread posted by Umachander (MVP, SQL Server) on the same at the public.sqlserver.programmming newsgroup. http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e7dcU%23jiAHA.2088%40tkmsftngp03

There are many other approaches to the same problem. Some of the solutions have been outlined at : http://www.extremeexperts.com/sql/articles/IterateTSQLResult.aspxsql