Now that we have built a good foundation, we can leverage on our efforts and automate many more tasks.
Here is challenge #2: Obtain the following information on each SQL Server where SAP is running on:
- Windows (OS) version
- SQL Server version
- CPU Count
Run XP_MSVER (extended stored procedure) on each SAP database server.
Remember, we already have a text file with the list of server names (sapsystems.txt). Therefore, we can simply create the batch script below:
@echo off set infile=sapsystems.txt
for /f "tokens=1-3" %%a in (%infile%) do ( echo Getting SQL Server info from %%a ... osql -S%%a -dmaster –b -E –s"|" -Q"xp_msver" -w200 -o%%a_sqlinfo.txt )
Did you notice anything different in the OSQL parameters? –In case you didn't, here is what's different:
- First of all there are no blank spaces between the parameters and their values. OSQL doesn't care if you pass –S servername or –Sservername. It checks if the parameter exists and if it's in the correct case.
- We connect to the "master" database (-dmaster) because that's where the extended stored procedure "xp_msver" is stored.
- We did not have to use –n because we are only executing one SQL statement (xp_msver), which is why we use the –Q parameter.
- The column width is 200 (-w200).
Do you see how things become easier when scripts are used?
Overcoming Basis challenges by reaching inside the database
=Basis challenge #1: Reporting logon information
Basis challenge #2: Gathering SQL Server information
Basis challenge #3: Getting system profile parameters
Review: Conclusion and more resources