Basis challenge #2: Gathering SQL Server information

Learn Basis best practices and how to overcome common administrator problems in this new checklist from SAP Basis guru Giovanni Davila.

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
  • RAM
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

 Home: Introduction
 =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

Dig Deeper on SAP Basis

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.