March 6, 2007 at 7:21 am
i usually get a good amount of information about
the server that i'm on when i run this, but what
about running it against all servers in the environment?
is this possible?
then i could just export the output into an excel sheet
or some thing.
_________________________
March 6, 2007 at 8:54 am
Hi,
this thing works for me for Windows 2003 servers, but does not work for Windows 2000 servers. I can run this query towards remote computers if I connect Object Explorer to the server and open a new query window towards this server.
So I suggest to use OPENROWSET in a loop specifying each time a new server name. You can put your 2003 server names in a table, compose dynamic strings for your OPENROWSET with xp_cmdshell and then run the resulting queries.
Regards,Yelena Varsha
March 6, 2007 at 9:22 am
unfortunately i'm not that well versed in this and i don't
know how to use OPENROWSET in a loop, dynamic stringg with xp_cmdshell.
forgive me if i'm asking too much, but is there some sample
code out there some where?
thanks in advance.
_________________________
March 6, 2007 at 1:17 pm
For 2000 servers check the "msinfo32.exe" in C:\Program Files\Common Files\Microsoft Shared\MSInfo folder...
MohammedU
Microsoft SQL Server MVP
March 6, 2007 at 2:33 pm
thanks for all the good feed back people!
i'm looking over teh msinfo stuff now, and i'll
see how i can hit all the servers in my environment
at once.
_________________________
March 7, 2007 at 1:16 pm
If you're considering the use of 3rd party tools, you can look at SQL Farms. They have a tool that will execute your code on all databases and servers.
March 7, 2007 at 1:36 pm
seems like there would be a free little utility some where
which would allow you to do just that.
ahh well... what can you do?
_________________________
March 8, 2007 at 3:19 am
You can do this pretty quickly in C# if you are using windows authentication:
The following C# will take two parameters - the first is the name of a script file and the second is the name of a file containing a list of servers which each server on its own line - this code has no bells and whistles and no error handling but might do the job for you:
using
System;
using
System.Data;
using
System.Data.SqlClient;
using
System.IO;
namespace
SQLExecutor
{
class Program
{
static void Main(string[] args)
{
if(args.Length != 2 || File.Exists(args[0]) == false || File.Exists(args[1]) == false)
{
PrintUsage();
return;
}
string scriptToExecute = File.ReadAllText(args[0]);
string[] serverList = File.ReadAllLines(args[1]);
SqlConnectionStringBuilder connstr = new SqlConnectionStringBuilder();
connstr.IntegratedSecurity =
true;
Console.WriteLine("<DOCUMENT>");
foreach (string server in serverList)
{
Console.WriteLine(String.Format("<SERVER name=\"{0}\">", server));
connstr.DataSource = server;
using(SqlConnection conn = new SqlConnection(connstr.ToString()))
{
conn.Open();
using(SqlCommand cmd = new SqlCommand(scriptToExecute, conn))
{
SqlDataReader reader = cmd.ExecuteReader();
if(reader != null)
{
DataTable dt = new DataTable("ROW");
dt.Load(reader);
dt.WriteXml(
Console.Out);
}
}
}
Console.WriteLine("</SERVER>");
}
Console.WriteLine("</DOCUMENT>");
}
private static void PrintUsage()
{
Console.WriteLine("Usage: SQLExecutor <scriptfile> <serverfile>");
}
}
}
If you copy the above into a text file (SQLExecutor.cs) you should be able to compile it (assuming you have .net 2.0 installed) by running
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\csc.exe /target:exe /out:SQLExecutor.exe SQLExecutor.cs
Hope this helps
- James
--
James Moore
Red Gate Software Ltd
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply