SQL SERVER – Running Batch File Using T-SQL – xp_cmdshell bat file

In last month I received few emails emails regarding SQL SERVER – Enable xp_cmdshell using sp_configure.
The questions are
1) What is the usage of xp_cmdshell and
2) How to execute BAT file using T-SQL?

I really like the follow up questions of my posts/articles. Answer is xp_cmdshell can execute shell/system command, which includes batch file.

1) Example of running system command using xp_cmdshell is SQL SERVER – Script to find SQL Server on Network

EXEC master..xp_CMDShell 'ISQL -L'

2) Example of running batch file using T-SQL
i) Running standalone batch file (without passed parameters)
EXEC master..xp_CMDShell 'c:findword.bat'

ii) Running parameterized batch file

DECLARE @PassedVariable VARCHAR(100)
DECLARE @CMDSQL VARCHAR(1000)
SET @PassedVariable = 'SqlAuthority.com'
SET @CMDSQL = 'c:findword.bat' + @PassedVariable
EXEC master..xp_CMDShell @CMDSQL

Book Online has additional examples of xp_cmdshell
A. Returning a list of executable files
B. Using Windows net commands
C. Returning no output
D. Using return status
E. Writing variable contents to a file
F. Capturing the result of a command to a file

Reference : Pinal Dave (http://blog.SQLAuthority.com), BOL

About these ads

SQL SERVER – De-fragmentation of Database at Operating System to Improve Performance

This issues was brought to me by our Sr. Network Engineer. While running operating system level de-fragmentation using either windows de-fragmentation or third party tool it always skip all the MDF file and never de-fragment them. He was wondering why this happens all the time.

The reason MDF file are skipped all the time in de-fragmentation because they are in use when SQL Server is running. Windows operating system de-fragmentation skips all the file in are currently in use.

After discovering this the real question was how to de-fragment when files are in use. Steps are Stop the Server, Re-start, keep the SQL Server services off and do de-fragmentation.

What if Server can not be taken off-line? In that case, nothing can be done. In my company SQL Server runs on redundant configuration – they are mirrored real time as well fail-over clustering is configured. We took one server down and system ran on safely on fail over server while we ran de-fragmentation on other server. We repeated the same order for all server. We see significant difference in our operating system performance as well as database response time.

One more thing to note, operating system needs more than 15% empty space to do optimal de-fragmentation.

Just for fun I will type my conversation with our Sr. Network Engineer. He is very smart person.

Sr. Network Engineer : Hey Pinal, MDF files does not de-fragment every night.
Pinal : May be because they are in use. Needs to alternate and de-fragment on cluster, what do you think?
Sr. Network Engineer : Yeah! Thanks. It will be good test for fail-over as well. Will do tonight.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Fix : Error : An error has occurred while establishing a connect to the server. Solution with Images.

While reviewing my my blog search engine terms I find Error 40 is the most common error searched. I have previously wrote blog about how to fix this error here : SQL SERVER – Fix : Error : 40 – could not open a connection to SQL server.

Today I have added few screen shot of that error and their solution to help readers who need additional help to understand my post.

Error Screen:

Solution Part 1: Enable SQL Server Service

Solution Part 2: Enable TCP/IP Protocol

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Fix : Error: 1418 – Microsoft SQL Server – The server network address can not be reached or does not exist. Check the network address name and reissue the command

Error: 1418 – Microsoft SQL Server – The server network address can not be reached or does not exist. Check the network address name and reissue the command

The server network endpoint did not respond because the specified server network address cannot be reached or does not exist.

Fix/Workaround/Solution:

Step 1) Your system Firewall should not block SQL Server port.

Step 2) Go to Computer Management >> Service and Application >> SQL Server 2005 Configuration >> Network Configuration
Enable TCP/IP protocol. Make sure that SQL SERVER port is by Default 1433.

Just to make sure follow one more step which may or may not be necessary.

Step 3) Go to Computer Management >> Service and Application >> SQL Server 2005 Configuration >> Client Configuration
Enable TCP/IP protocol.

Reference : Pinal Dave (http://blog.SQLAuthority.com)