-2

I'm trying to optimize an hourly txt file export and reduce the file size on SQL Server 2008 R2.

I created a job which is calling the below BCP:

EXECUTE master.dbo.xp_cmdshell 'BCP "SELECT Columns FROM table  queryout D:\OUTPUT\Filename.txt -S Servername  -t "|" -T'

Actually the txt file size is around 20MB, is it a way to reduce the size without removing columns or adding a condition in the select? Maybe in the bcp config? Thank you in advance!

Merka
  • 11
  • 1
  • 5
  • 1
    What is your question here then? If you want the file to be smaller, include less columns/rows. – Thom A Apr 05 '21 at 10:15
  • 2
    Why are you still using a version of SQL Server that's no longer supported in any way, shape or form by Microsoft? No more security updates, no more fixes ...... – marc_s Apr 05 '21 at 10:18
  • 1
    I don't get it: why make an *SQL* job that calls a batch command, just create a *Powershell* job that does the same thing much better. `xp_cmdshell` has many security issues – Charlieface Apr 05 '21 at 10:20

1 Answers1

0

You can use the -N option. BCP will export the data to your designated file using SQL Serves own native data format. This may reduce (most likely will) the size of your output file. However, to do this assumes that the destination is also a MS SQL Server.

As Charlieface stated, xp_cmdshell has serious security issues for today. It really should be disabled and not used at all on any class of server (prod, dev, QA, etc...)

jamie
  • 745
  • 4
  • 11