Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Following code is used for write a number in the text file but instead it is writing "Echo is ON" into the text file.

SET @CMD = 'ECHO '+@Ver+'>'+@Vercheck      
  Print @CMD

EXEC xp_cmdshell @CMD  
  ,NO_OUTPUT 

Print @CMD gives "ECHO 1>C:\Pharmsuite\Vercheck.txt"

Here instead of 1, the query is writing "Echo is ON".

share|improve this question
up vote 9 down vote accepted

This command: ECHO 1>C:\Pharmsuite\Vercheck.txt

Does not do what you think it should do because 1> has a special meaning.

This problem can be fixed by adding a space before >:

ECHO 1 >C:\Pharmsuite\Vercheck.txt

Or in your case:

SET @CMD = 'ECHO '+@Ver+' >'+@Vercheck

It also works with this command (making sure there is a space before ECHO [thanks to Andriy M]):

SET @CMD = '>'+@Vercheck+' ECHO '+@Ver

You should look at:

Numeric handles:

STDIN = 0 Keyboard input
STDOUT = 1 Text output
STDERR = 2 Error text output
UNDEFINED = 3-9

command 2> filename Redirect any error message into a file
command 2>> filename Append any error message into a file
(command)2> filename Redirect any CMD.exe error into a file

Anyhow, you should avoid using xp_cmdshell. If you are trying to output something, Powershell or SQL Server command line commands are better options.

Using Powershell and Invoke-Sqlcmd:

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "MyComputer\MyInstance"
Invoke-Sqlcmd -InputFile "C:\MyFolder\TestSQLCmd.sql" | Out-File -filePath "C:\MyFolder\TestSQLCmd.rpt"

You obviously have to adapt it to your own requierements and you must also look at Daniel Hutmacher's answer using osql.

share|improve this answer

The problem is, 1> has a specific meaning. It means "redirect standard output", whereas 2> would be "redirect standard error". > by itself is short-hand for 1>.

You can try:

SET @CMD = 'ECHO>'+@Vercheck + ' ' + @Ver

Since you can perform redirection before the rest of the arguments to echo.

share|improve this answer

Alternate solution

Instead of using xp_cmdshell, which comes with a lot of security risks, you could run something like this from the command prompt:

osql >output_file.txt -S myServer\myInstance -E -Q "PRINT @@VERSION"

The -S switch denotes the name of the server and instance, -E means Windows authentication (you could instead use -U and -P for username and password), and -Q is the actual query. Note that I've used PRINT instead of SELECT in order to get rid of the column headers.

Obviously, you could replace "PRINT @@VERSION" with any valid T-SQL code.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.