Shulou(Shulou.com)06/03 Report--

This article mainly introduces "how to achieve bat/cmd batch connection SqlServer database query script", in the daily operation, I believe that many people have doubts on how to achieve bat/cmd batch connection SqlServer database query script, Xiaobian consulted all kinds of materials, sorted out simple and easy-to-use operation methods, hope to answer "how to achieve bat/cmd batch connection SqlServer database query script" to help! Next, please follow the editor to study!

The code is as follows:

@ echo oFFtitle Zabbix Monitoring Database query bat script:: name: Zabbix Monitoring script batch version:: description: query the database through the osql command line tool for some custom monitoring:: support: osql.exe and MSVCR71.DLL support are required Can be placed in the script sibling directory:: time: 2015-03-20Usg=%1: enter the script's current directory cd "% ~ dp0":: get the parameter set "Usg=%1":: determine the parameter if "% Usg%" = = "" goto Example:: initialize the connection parameter set host= "10.172.10.80" set db= "DBname" set user= "DBuser" set pd= "password":: execute different query codes according to different parameters (since Line adjustment) if "% Usg2%" = = "monitor1" (:: monitoring 1 set sql= "sql statement 1") else if "% Usg%" = = "monitor2" (:: monitoring 2 set sql= "sql statement 2") else if "% Usg%" = = "monitor3" (:: monitoring 3 set sql= "sql statement 3") else if "% Usg%" = = "monitor4" ( :: monitor 4 set sql= "sql statement 4"):: connect to the database and execute the query (the for here extracts the results of the query So it needs to be done on its own according to the actual situation. ) for / f "skip=2 delims="% I in ('osql.exe-S% host%-U% user%-P% pd%-d% db%-Q% sql%') do (set result=%%i goto: result):: direct execution without parameters will prompt usage: Exampleecho Example:echo=echo% ~ nx0 Conditionsecho=echo-- Echo Script will auto exit in 5s...ping-n 5 127.1 > nulexit:: automatically exit after outputting the result: resultecho% result%

How to use it:

①, verification execution: execute [script .bat + monitoring name] in CMD, for example: d:\ > monitor1.bat monitor1

I will not elaborate on how to set up ② and associated zabbix:zabbix. Note that the zabbix configuration file zabbix_command.conf is added as follows:

UserParameter=NewMonitor [*], cmd / k c:\ zabbix\ NewMonitor.bat monitor1

Ps: the main thing is to note that you need to add a cmd / k front, otherwise zabbix will not be able to execute or get the data.

Finally, several practical parameters of the osql.exe command and related download addresses are attached:

-U login_id user logs in to ID. Login ID is case sensitive. -P password is the password specified by the user. If the-P option is not used, osql prompts for a password. If you use the-P option at the end of the command prompt without a password, osql uses the default password (NULL). Passwords are case sensitive. -S server_ name [\ instance_name] specifies the Microsoft ®SQL Server ™2000 instance to connect to. Specify server_name on the server to connect to the default instance of SQL Server. Specify server_name\ instance_name on this server to connect to an instance of named SQL Server 2000. If no server is specified, osql connects to the default instance of SQL Server on the local computer. This option is required when performing osql from a remote computer on the network. -d db_name issues a USE db_name statement when it starts osql. -Q "query" executes the query and immediately exits osql. Enclose the query in double quotation marks and anything embedded in the query in single quotation marks. -Q "query" executes the query when it starts osql, but does not exit osql when the query is complete. (note that the query statement should not contain GO.) If you issue a query from a batch file, use variables or environment variables%. For example: SET table = sysobjects osql / Q "Select * from% table%" so far, the study on "how to implement bat/cmd batch connection SqlServer database query script" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.