查看服务器工作状态代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】查看服务器工作状态
作者:Dezai.CN / 发布于2012/1/11/ 694
<div><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">-查看服务器工作状态</span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">create</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">function</span><span style="color: rgb(0,0,0)"> fgetsstatus( 
</span><span style="color: rgb(0,128,0)">@servername</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">varchar</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">50</span><span style="color: rgb(0,0,0)">) </span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">服务器名 </span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(0,128,0)">@userid</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">varchar</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">50</span><span style="color: rgb(0,0,0)">)</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">sa</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">用户名,如果为nt验证方式,则为空 </span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(0,128,0)">@password</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">varchar</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">50</span><span style="color: rgb(0,0,0)">)</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">密码 </span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,0)">
) </span><span style="color: rgb(0,0,255)">returns</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">varchar</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">20</span><span style="color: rgb(0,0,0)">) 
</span><span style="color: rgb(0,0,255)">as</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">begin</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">declare</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@re</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">varchar</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">20</span><span style="color: rgb(0,0,0)">),</span><span style="color: rgb(0,128,0)">@ire</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">int</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">返回状态 </span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">declare</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@srvid</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">int</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">定义服务器、数据库集id </span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">declare</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@err</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">int</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(0,128,0)">@src</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">varchar</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">255</span><span style="color: rgb(0,0,0)">), </span><span style="color: rgb(0,128,0)">@desc</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">varchar</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">255</span><span style="color: rgb(0,0,0)">) </span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">错误处理变量 </span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">创建sqldmo对象 </span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">exec</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@err</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)">sp_oacreate </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">sqldmo.sqlserver</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(0,128,0)">@srvid</span><span style="color: rgb(0,0,0)"> output 
</span><span style="color: rgb(0,0,255)">if</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@err</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)"><></span><span style="color: rgb(128,0,0); font-weight: bold">0</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">goto</span><span style="color: rgb(0,0,0)"> lberr 

</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">连接服务器 </span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">if</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,255)">isnull</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(0,128,0)">@userid</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(0,0,0)">)</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">如果是 Nt验证方式 </span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">begin</span><span style="color: rgb(0,0,0)"> 
 </span><span style="color: rgb(0,0,255)">exec</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@err</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)">sp_oasetproperty </span><span style="color: rgb(0,128,0)">@srvid</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">loginsecure</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(128,0,0); font-weight: bold">1</span><span style="color: rgb(0,0,0)"> 
 </span><span style="color: rgb(0,0,255)">if</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@err</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)"><></span><span style="color: rgb(128,0,0); font-weight: bold">0</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">goto</span><span style="color: rgb(0,0,0)"> lberr 

 </span><span style="color: rgb(0,0,255)">exec</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@err</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)">sp_oamethod </span><span style="color: rgb(0,128,0)">@srvid</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">connect</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(0,0,255)">null</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(0,128,0)">@servername</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">end</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">else</span><span style="color: rgb(0,0,0)"> 
 </span><span style="color: rgb(0,0,255)">exec</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@err</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)">sp_oamethod </span><span style="color: rgb(0,128,0)">@srvid</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">connect</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(0,0,255)">null</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(0,128,0)">@servername</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(0,128,0)">@userid</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(0,128,0)">@password</span><span style="color: rgb(0,0,0)"> 

</span><span style="color: rgb(0,0,255)">if</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@err</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)"><></span><span style="color: rgb(128,0,0); font-weight: bold">0</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">goto</span><span style="color: rgb(0,0,0)"> lberr 

</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">获取服务器状态 </span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">exec</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@err</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)">sp_oagetproperty </span><span style="color: rgb(0,128,0)">@srvid</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">Status</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(0,128,0)">@ire</span><span style="color: rgb(0,0,0)"> output 
</span><span style="color: rgb(0,0,255)">if</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@err</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)"><></span><span style="color: rgb(128,0,0); font-weight: bold">0</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">goto</span><span style="color: rgb(0,0,0)"> lberr 

</span><span style="color: rgb(0,0,255)">set</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@re</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,255)">case</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@ire</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">when</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">0</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">then</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">未知</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> 
 </span><span style="color: rgb(0,0,255)">when</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">1</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">then</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">运行...</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> 
 </span><span style="color: rgb(0,0,255)">when</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">2</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">then</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">暂停</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> 
 </span><span style="color: rgb(0,0,255)">when</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">3</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">then</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">停止...</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> 
 </span><span style="color: rgb(0,0,255)">when</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">4</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">then</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">正在启动...</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> 
 </span><span style="color: rgb(0,0,255)">when</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">5</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">then</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">正在启动停止...</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> 
 </span><span style="color: rgb(0,0,255)">when</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">6</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">then</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">连接...</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> 
 </span><span style="color: rgb(0,0,255)">when</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,0,0); font-weight: bold">7</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">then</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">正在暂停...</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">end</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">return</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(0,128,0)">@re</span><span style="color: rgb(0,0,0)">) 
lberr: 
</span><span style="color: rgb(0,0,255)">exec</span><span style="color: rgb(0,0,0)"> sp_oageterrorinfo </span><span style="color: rgb(0,0,255)">NULL</span><span style="color: rgb(0,0,0)">, </span><span style="color: rgb(0,128,0)">@src</span><span style="color: rgb(0,0,0)"> out, </span><span style="color: rgb(0,128,0)">@desc</span><span style="color: rgb(0,0,0)"> out 
</span><span style="color: rgb(0,0,255)">declare</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@errb</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">varbinary</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">4</span><span style="color: rgb(0,0,0)">) 
</span><span style="color: rgb(0,0,255)">set</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@errb</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,255)">cast</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(0,128,0)">@err</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">as</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">varbinary</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">4</span><span style="color: rgb(0,0,0)">)) 
</span><span style="color: rgb(0,0,255)">exec</span><span style="color: rgb(0,0,0)"> master..xp_varbintohexstr </span><span style="color: rgb(0,128,0)">@errb</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(0,128,0)">@re</span><span style="color: rgb(0,0,0)"> out 
</span><span style="color: rgb(0,0,255)">set</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@re</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">错误号: </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,128,0)">@re</span><span style="color: rgb(0,0,0)"> 
 </span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,0,255)">char</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">13</span><span style="color: rgb(0,0,0)">)</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">错误源: </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,128,0)">@src</span><span style="color: rgb(0,0,0)"> 
 </span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,0,255)">char</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">13</span><span style="color: rgb(0,0,0)">)</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">错误描述: </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,128,0)">@desc</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">return</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(0,128,0)">@re</span><span style="color: rgb(0,0,0)">) 
</span><span style="color: rgb(0,0,255)">end</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">go</span><span style="color: rgb(0,0,0)"> 


</span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> dbo.fgetsstatus(</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">192.168.102.208</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">sa</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">sa</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">)

 
</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">------------------ </span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,0)">运行...
</span></div>
试试其它关键字
　查看服务器工作状态　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)