txt file Pipe

Question

txt file Pipe

juliava

SSC-Addicted

Points: 444
More actions
December 4, 2023 at 8:09 pm

#4339707

Hello,
I need to put data from table to txt file as pipe on certain drive can someone assist me? Thank you

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

juliava SSC-Addicted Points: 444 More actions · Answer 1

i used this code:

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT * FROM dbo.CustomerInformation " queryout C:\Data\Objects.txt -t"|" -c -T ' and it is not working

ratbak Ten Centuries Points: 1358 More actions · Answer 2

Does SQL Server have permissions to write to that folder?

Are you getting any error messages?

Are you executing in SSMS? If so, does the login have permissions to write to that folder?

In a SQL Agent job? If SQL Agent job, does the SQL agent login have permissions to write to that folder?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

juliava wrote:

i used this code:
EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT * FROM dbo.CustomerInformation " queryout C:\Data\Objects.txt -t"|" -c -T ' and it is not working

What is your definition of "Not Working" here?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

juliava SSC-Addicted Points: 444 More actions · Answer 4

SQLState = S1000, NativeError = 0

Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to open BCP host data-file

NULL

juliava SSC-Addicted Points: 444 More actions · Answer 5

I change drive and code and it is working now

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT * FROM table1 " queryout G:\Backup\Test.txt -t"|" -c -T '

I need to know how to add headers please

This reply was modified 11 months, 3 weeks ago by juliava.

Ken McKelvey SSCoach Points: 18842 More actions · Answer 6

juliava wrote:

I need to know how to add headers please

If this is an Agent Job step then Powershell is a lot less hassle. Play with something like:

$SQLparams = @{
  'ServerInstance' = '.';
  'Database' = 'YourDB';
  'ErrorAction' = 'Stop';
  'Query' = 'SELECT * FROM dbo.table1' }

$MyPath = "G:\Backup\Test.txt"

Invoke-Sqlcmd @SQLparams |
  ConvertTo-Csv -Delimiter '|' -NoTypeInformation |
  % {$_ -replace '"',''} |
  Out-File -Encoding ascii $MyPath

Ken McKelvey SSCoach Points: 18842 More actions · Answer 7

My first replay seemed to time out so I posted again.

This reply was modified 11 months, 3 weeks ago by Ken McKelvey.

juliava SSC-Addicted Points: 444 More actions · Answer 8

juliava

SSC-Addicted

Points: 444

December 5, 2023 at 9:58 pm

#4340399

this is not working