I have been exporting data as a csv file using xp_cmdshell within a stored procedure successfully for some time now in Sybase ASA 12.

I wish to export similar data, but pipe delimited instead of comma separated values.

From within ISQL the following works pefectly:

select '0300' AS HospitalCode, TRIM(URNumber), OrderType, SentenceType, OriginalDateStart,VariationNo, SentenceMonths,DateStart, DateExpiry, CLCAct, LifeIndicator, GPIndicator,TRIM(fnCurrentLocation(CMHO.URNumber)) as CurrentLocation, W.Ward from ClientMHAOrders as CMHO left outer JOIN Wards as W on W.WardName = CurrentLocation WHERE OrderType = 'FP' ORDER BY CMHO.URNumber, CMHO.DateStart; OUTPUT TO 'c:\\temp\\GLN_FORENSIC_20160220.txt' FORMAT TEXT DELIMITED BY '|' WITH COLUMN NAMES; 2> 'C:\\temp\\ForensicData.log','no_output'

Now when I put the code into a stored procedure as this:

call xp_cmdshell('""%SQLANY12%\\Bin32\\dbisql" -q -c "UID=DBA;PWD=sql;Server=MyServer;Database=TestData" SELECT ''0300'' AS HospitalCode, TRIM(URNumber), OrderType, SentenceType, OriginalDateStart, VariationNo, SentenceMonths, DateStart, DateExpiry, CLCAct, LifeIndicator, GPIndicator, TRIM(fnCurrentLocation(CMHO.URNumber)) as CurrentLocation, W.Ward from ClientMHAOrders as CMHO left outer JOIN Wards as W on W.WardName = CurrentLocation WHERE OrderType = ''FP'' ORDER BY CMHO.URNumber, CMHO.DateStart; OUTPUT TO ''c:\\temp\\GLN_FORENSIC_20160220.txt'' FORMAT TEXT DELIMITED BY ''|'' WITH COLUMN NAMES; 2> C:\\temp\\ForensicData.log"','no_output')

My stored procedure creates an error entry within the log file: ''' is not recognized as an internal or external command, operable program or batch file.

If I remove the "DELIMITED BY ''|'' it works, but the output is comma delimited. Obviously I have an error in syntax possibly, can anyone help with this please.

asked 19 Feb '16, 22:32

Datagaard's gravatar image

Datagaard
2658919
accept rate: 0%

Try representing the pipe character as an escaped hex character '\xZZ' where ZZ is the hex for pipe.

(20 Feb '16, 09:28) Breck Carter
Replies hidden

HI Breck.

No that didn't work, now I do get the file created, but \xZZ becomes the delimiter, not the hex equivalent. Oh isn't 7C the hex equivalant of the vertical bar "pipe" ?. I tried that too. Interestingly if I use the "^" character it works, just not "|".

(20 Feb '16, 18:27) Datagaard

I have tried this too which works for the HEx character broken pipe,

FORMAT TEXT DELIMITED BY ''\xA6'' ESCAPES ON WITH .....

Each time I try to represent the pipe character ''\x7C'' I get the same error in the log file.

(20 Feb '16, 18:48) Datagaard

Have you tried to use the UNLOAD statement instead of the "xp_cmdshell/OUTPUT TO" approach? - AFAIK, you would have to use an initial UNLOAD to add the column headers and then use UNLOAD ... APPEND or use an UNION to add column headers. (v17 has added a helpful WITH COLUMNS NAMES clause...)

Note, I have not tried to use a pipe character with UNLOAD but I guess the syntax is easier to use from within a stored procedure than your current approach.

permanent link

answered 20 Feb '16, 19:41

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 33%

converted 21 Feb '16, 08:37

Hi Volker,

The UNLOAD statement worked. Thanks. For information it would be interesting to know why the pipe character is the only character that doesn't work in the OUTPUT statement.

(20 Feb '16, 23:17) Datagaard
Replies hidden

See my other answer: It's no OUTPUT or dbisql problem, it's just the fact that you use the unmasked pipe in a command line where it has a particular meaning (i.e. to create a pipeline between two programs) and has to be escaped here if that meaning is not desired.

(22 Feb '16, 03:49) Volker Barth

While I highly recommend to use UNLOAD instead of a cmd shell and a DBISQL session to output data from a stored procedure, I guess the following should do the trick:

In your original statement the '|' seems to be interpreted as a command line operator, whether it is used within quotes or not. (Note: That's my humble impression, I'm not command line expert at all...). Here's an attempt for a similar query run against the v12 demo database, directly on the command line (and therefore without doubled quotes and the like...)

"%SQLANY12%\Bin64\dbisql" -q -c "UID=DBA;PWD=sql;DSN=SQL Anywhere
 12 Demo" select * from sysusertype order by type_name; OUTPUT TO 'c:\PipeDelim
itedExport.txt' FORMAT TEXT DELIMITED BY '|' WITH COLUMN NAMES; 2> C:\PipeDelim
itedExportError.log

This raises the same error you mentioned.

However, if you put the whole SQL statement within a double quote, the export works just fine in my tests with 12.0.1.4314 - and I would think it is because that way the command shell does not try to interpret the pipe character itself:

"%SQLANY12%\Bin64\dbisql" -q -c "UID=DBA;PWD=sql;DSN=SQL Anywhere
 12 Demo" "select * from sysusertype order by type_name; OUTPUT TO 'c:\PipeDelim
itedExport.txt' FORMAT TEXT DELIMITED BY '|' WITH COLUMN NAMES;" 2> C:\PipeDelim
itedExportError.log

Or, alternatively, you can escape the pipe character on the command shell with a leading caret:

"%SQLANY12%\Bin64\dbisql" -q -c "UID=DBA;PWD=sql;DSN=SQL Anywhere
 12 Demo" select * from sysusertype order by type_name; OUTPUT TO 'c:\PipeDelim
itedExport.txt' FORMAT TEXT DELIMITED BY '^|' WITH COLUMN NAMES; 2> C:\PipeDelim
itedExportError.log
permanent link

answered 22 Feb '16, 03:40

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 33%

edited 22 Feb '16, 03:44

Hi Volker,

That was actually my thinking too, that some how the command was being misinterpreted.

Thanks for your help.

(22 Feb '16, 16:58) Datagaard
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×106
×79
×7

question asked: 19 Feb '16, 22:32

question was seen: 610 times

last updated: 22 Feb '16, 16:58