April 1, 2009 at 8:33 am
Hi all,
I am trying to fetch, some SQL SERVER PROPERTIES on remote network machines (I have their names in a table).
I was trying do as below:-
(1)
SELECT 'SQL Server '
+ CAST(SERVERPROPERTY('MachineName') AS VARCHAR) + ' - '
+ CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '
+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('
+ CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'
And then do a join of the above on the PCs Name (from) my local table.
But, as you can see, I need a “FROM CLAUSE” to complete the join.
Does anyone know from what system table the server properties are coming from?
Else, could you please suggest an alternative for getting remote servers information (having these server names locally)?
Thanks in advance!
(2)
--:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
JOIN DwnLdbleSoftware_Tab ON MachineName = DwnLdbleSoftware_Tab.[PcName]
--:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
April 1, 2009 at 9:21 am
because SERVERPROPERTY() is a function, even if you had " FROM RemoteServer.master.dbo.sysobjects" as the from clause, the function will return the info from the machine you are connected to...not the remote server.
In this situation, I've always gone to the tables that might contain the data on the remote server, and not try to use server functions....
ie SELECT * FROM RemoteServer.sys.servers
Lowell
April 1, 2009 at 1:43 pm
Hi Lowell,
I probably didn't explain me good enough, however, could you please give more input on how this (read remote sql server property)can be accomplished? If you know. Many thanks.
April 1, 2009 at 4:35 pm
SELECT a.* FROM OPENROWSET('SQLOLEDB',
'DRIVER={SQL Server};SERVER=.\yukon1;Trusted_Connection=yes;',
'select @@servername') AS a
MJ
April 1, 2009 at 5:18 pm
This work just fine, as long as you have RPC turned on for your linked server:
exec ServerName.master.dbo.sp_executesql N'Select SERVERPROPERTY(''MachineName'')'
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 2, 2009 at 1:15 am
Thank you RBarry and MJ for your efforts, the problem is that I have these servers in a corperate network, therefore, in different locations round the globe(350 machines), therefore, configuring RPC or Linked server for everyone of them, apart from the security risk, could be tidious.
It might be useful letting you know why am doing this:-
FOR LICENSING REASONS, I NEED TO KNOW WHAT VERSIONS(developer,enterprise ecc.) OF SQL SERVERS ARE ON THESE MACHINES
Any futher help will be useful. Many thanks.
April 2, 2009 at 2:36 am
In my organization, we come across such situation very frequently, we have some 500+ servers running across globe. We mix DOS batch script with sql query.
1. Batch will open connection using osql or sqlcmd and run the query
osql -E -S servername -Q"set nocount on;your query" -n -w999 >> output.txt
2. out put will be forwarded to one single file ( in DOS >> is create/append , > is create and redirect)
3. using DOS findstr command we'll truncate all useless line like hyphens and blank lines
If you want this i can provide you some code.
April 2, 2009 at 3:07 am
Please! I really will appreciate if you can provide me the code. Thank you in advance.
April 2, 2009 at 4:12 am
Ok..
I have 3 files
1. abc.cmd - This is the batch file we will call from DOS prompt
[font="Courier New"]for /f %%a in (srvlist.txt) do osql -E -S %%a -i scr.txt -n -w999 >> out.txt
findstr /V "\-\-\-" out.txt > temp.txt
findstr "[a-z]" temp.txt > out.txt
del temp.txt
notepad out.txt[/font]
2. srvlist.txt - This file list of all servers to connect, for example, i am using following servers (running on my PC)
[font="Courier New"]SAC\SQL9_SRV1
SAC\SQL8_SRV1
SAC\SQL8_SRV2[/font]
3. scr.txt - this is sql code i have to run
set nocount on
SELECT 'SQL Server '
+ CAST(SERVERPROPERTY('MachineName') AS VARCHAR) + ' - '
+ CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '
+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('
+ CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'
Save these files, and from command prompt go to location where these files are
[font="Courier New"]cd /d d:\folder_name[/font]
then type
[font="Courier New"]abc.bat[/font]
It will run -
1. for all servers in list one by one (for /f %%a in (srvlist.txt) do osql -E -S %%a -i scr.txt -n -w999 >> out.txt)
2. Now it will truncate extra hyphens (findstr /V "\-\-\-" out.txt > temp.txt)
3. Then all blank lines (findstr "[a-z]" temp.txt > out.txt)
4. Then popup the result in notepad (notepad out.txt)
Please remember -
1. There might be some servers which you are unable to connect, for those you will see some error messages in output file, deal such servers manually.
2. If you are OK with some hyphens or spaces, comment those command lines (:: is used to comment in DOS)
Try this with few servers and let me know if it works.
April 2, 2009 at 4:20 am
I'll let you know as soon as possible. Many thanks.
April 2, 2009 at 10:37 am
greg
If you are on SQL 2005, look into PowerShell as well, it is great for this purpose. Google for some examples to grab server versions
If not, above batch script is quite typical
I suggest using SQLCMD instead of OSQL though
April 2, 2009 at 1:26 pm
greg eze (4/2/2009)
Thank you RBarry and MJ for your efforts, the problem is that I have these servers in a corperate network, therefore, in different locations round the globe(350 machines), therefore, configuring RPC or Linked server for everyone of them, apart from the security risk, could be tidious.
You can script this also. And I am not aware of any particular security risk, given that every single client SQL user in your corporate network can already use RPC to your servers.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 2, 2009 at 1:51 pm
To make use of RPC, I will definitely have to turn off the fire walls of those machines,we have them turned on by default. That's where the security issue comes in.
I shall try the script Boolean_z sent to me when am done with what am doing right now, and let you'all know as soon as possible. However any further input will be welcomed.
Many thanks to everyone that has contributed so far.
April 2, 2009 at 6:58 pm
Jerry Hung (4/2/2009)
gregIf you are on SQL 2005, look into PowerShell as well, it is great for this purpose. Google for some examples to grab server versions
I think, Powershell is introduced in SQL 2k8, its not available in 2k5.
April 3, 2009 at 12:53 am
Hi Boolean_z,
I tryed the script, it worked for my locally installed sql server, and, linked servers on the local machine. But I got the error below for the remote servers. I don't know if you can add more to this?
However, this is the best solution I have so far, come's to the worst, I'll just execute the script on different machines involved, scripting the results to a shared folder in the network.
I really appreciate the effort. Many thanks.
[SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
[SQL Native Client]Login timeout expired
[SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
[SQL Native Client]SQL Network Interfaces: Connection string is not valid [87].
[SQL Native Client]Login timeout expired
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply