Best way to snif multiple servers without the use of linked servers
-
I am trying to build an administration script for SS2K to find all instances where a specific integrated or SQL authenticated user name exists. The problem I am having is that linkes servers doesn't give me the flexibiltiy I would like. Do any of you have a script that can be exec either via iSQL or Query Analyzer? This script is for use when employees seperate and have access to multiple systems (ex. QA and Production). I know this can be done using ASP, or other development language, but my goal is to make this process a part of the SQL environment to avoid adding complexity for other users.
-
So, what kind of 'flexibility' are you looking for..? There are several 'problems' or 'tasks' within your general description of what you want to do.. (ie find users or find servers etc..)
/Kenneth
-
Look at the SQL Farm Combine from SQL Farms. It lets you query multiple databases and servers at the same time, and does what you seek.
Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.
-
I like the sounds of the Combine app. Too bad I need this sooner rather than later.
Kenneth,
To answer your question. The flexibility I am looking for is that I would like to be able to run this script from ANY SQL Instance on my network and don't want to have to link all 35+ servers instances everytime I need to relocate the scripts execution server. Long story on why this is necessary. I ultimately need a script that will allow me to pass in USERNAME (Required and includes NT Authenticated usernames), SERVERNAME (Optional assumes %), and ShowObjects (True or False is true the output of the script would include a listing of all objects for each instance that the username is the owner). The output of the script would be either to a table or to a txt file for printing. I was envisioning a script that could be executed via iSQL or run as an Agent Job.
Does that help? The main portion I am in search of is T-SQL scripting to select criteria from a table and use that criteria to run a simple select statement on each instance returned in the criteria query. Make sense?
-
Mmm yes think it makes sense. Don't have any packaged solution, though.. But some thoughts.. (I'm sure you're aware of all already...)
Being able to run from any instance requires that any instance has network access to all other instances. This is a networking issue only. For the rest, I've seen this kind of stuff before, and the only way to get data out of the servers is to connect and ask some SQL.
For cycling through each server, one way of doing it is to have all server/instance names in a textfile, write a .cmd file with a FOR(serverfile.txt) DO(call isql/osql and do SQL stuff on each) loop cycling through each and output to file. In essence a DOS batch
This could then be scheduled as job. I would probably try this path myself if I had to come up with something quickly myself. Also, this assumes that you already know all server/instance names in advance.
/Kenneth
-
Using the SQL-DMO object library you can programmatically loop through server and object collections to achieve the result you want.
-
Kenneth,
that logic sounds perfect. Now I just have to figure it all out as this is my first project of this nature for SQL. Everything I have done prior has been via .NET or other dev languages.
Now I will show just how much of a newbie I am to SQL Dev
How would I use the SQL-DMO object library to programmatically loop through the servers and collections? Can it be developed within a "bat" file or does I have to develop the solution within a development language? -
Just to add to the above:
SQL Farm Combine will be available for Beta testers on Thursday.
Please refer to last month's SQL Server Magazine or SQL Server Standard that came out a couple of days ago to check out some of the features.
Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.
-
Here is a VB example using SQLDMO to list the server groups and their registered servers (there are many other collections that may be looped through such as Logins, Databases, etc.):
To use SQLDMO in your program you must set a reference to: Microsoft SQLDMO Object Library.
Sub test()
Dim svrgrp As SQLDMO.ServerGroup
Dim svr As SQLDMO.RegisteredServer
Dim Login As SQLDMO.Login
Dim ssvr As SQLDMO.SQLServer
For Each svrgrp In SQLDMO.ServerGroups
Debug.Print svrgrp.Name
For Each svr In svrgrp.RegisteredServers
Debug.Print " " & svr.Name
Next svr
Next svrgrp
End Sub
-
Just as I was a fraid of. To use SQL-DMO requires a programming language/compiler to build and execute. I am looking for a solution that can run as either a BAT file without the need to compile an executable or a T-SQL script that can be executed via iSQL.
-
Here's a snippet of something old I had lying around for automating a 'batch-delivery-thingy' to a given set of servers that implements the 'loop from a list' idea. It shouldn't be too hard to use as a template for what you want to do.
(the ': DOSEXIT' at the bottom should be w/o the space, but I couldn't figure out how to get rid of the smiley if there was no space..' )
@echo off
@if not "%OS%"=="Windows_NT" goto DOSEXIT
REM *** comments about what this script does
REM *** xcopies all files from sourcedir to remotedir
REM *** serverlist.txt contains UNC paths to all servers
REM *** after copy, sets read only on destination
rem copy out files to serverlist
for /f "tokens=1" %%A in (serverlist.txt) DO xcopy putFilesHere\*.* %%A\myDir\ /s /e /r /i
rem set readonly flag
for /f "tokens=1" %%A in (serverlist.txt) DO attrib +r /s %%A\myDir\myOtherSubdir\*.*
goto :EOF
: DOSEXIT
echo This script requires Windows NT
/Kenneth
-
You can save the following code as a VB-Script (.vbs), and execute it like a .BAT file. Or, execute the code using DTS, or as a Scheduled Task. You don't have to compile it into an executable.
Dim svrgrp
Dim svr
Dim Login
Dim ssvr
Dim strOutPut
dim sqldmoapp
set sqldmoapp = CreateObject("SQLDMO.Application")
Set svrgrp = CreateObject("SQLDMO.ServerGroup")
Set svr = CreateObject("SQLDMO.RegisteredServer")
Set Login = CreateObject("SQLDMO.Login")
Set ssvr = CreateObject("SQLDMO.SQLServer")
For Each svrgrp In sqldmoapp.ServerGroups
strOutPut = strOutPut & svrgrp.Name & vbCrLf
For Each svr In svrgrp.RegisteredServers
strOutPut = strOutPut & " " & svr.Name & vbCrLf
ssvr.Connect svr.Name, [USERID], [PASSWORD]
For Each Login In ssvr.logins
strOutPut = strOutPut & " " & Login.Name & vbCrLf
Next
ssvr.Disconnect
Next
Next
MsgBox (strOutPut)
-
It does not require a compiler!
Simply, create the file with the .vbs extension and you are done
Cheers,
* Noel -
[banging own head against wall] so quickly I forget about that magical dev language known as vbscript
[/banging own head against wall]Thanks for the reminder and sample syntax. That will get me to where I wanted to be. Thanks to all that have helped.
-
You can run that script under WSH without a development environment. You dim the variables, but without the type (You have to do createobject), and comment out with a single quote the As ...
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply