1

I'm currently using

EXEC xp_cmdshell 'ECHO Hello > c:\file.txt'
EXEC xp_cmdshell 'ECHO World >> c:\file.txt'

To create and append text files from sql server. I get

Hello
World

as the result.

How can I using separate commands add both 'Hello' and 'World' on the same line in the output file so that I would end up with the file that looks like

Hello World

I need to do this because xp_cmdshell has a limit of 8000 characters.

Tomas
  • 181
  • 2
  • 10
  • 2
    What are you trying to achieve in the first place ? There are various other way to export data to file like [SSIS](https://learn.microsoft.com/en-us/sql/integration-services/sql-server-integration-services?view=sql-server-ver15), [bcp](https://learn.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver15), [SQL Server Import and Export Wizard](https://learn.microsoft.com/en-us/sql/integration-services/import-export-data/get-started-with-this-simple-example-of-the-import-and-export-wizard?view=sql-server-ver15). – Squirrel Jun 17 '21 at 07:20
  • I'm trying to update an old stored procedure that exports data to a .csv file, that started failing because some rows became longer than 8000 characters. SSIS seems like a bit too much work (With the integration server and all that), currently looking into bcp, export wizards are no good because it's an automated procedure. – Tomas Jun 17 '21 at 07:27
  • 2
    The problem with all these types of procedures is that they start from the wrong direction. You are much better off using C# or Powershell to pull data and store it in a file, rather than pushing it from T-SQL – Charlieface Jun 17 '21 at 08:45

1 Answers1

1

This seems to work

EXEC xp_cmdshell 'echo | set /p=Hello > c:\file.txt'
EXEC xp_cmdshell 'echo | set /p=World >> c:\file.txt'
James Casey
  • 2,447
  • 1
  • 11
  • 19