Write SQL query output into text file/log file

Question

How to write the output of SQL query to .txt file. Here is what I am trying to do. The SQL query is written inside a perl script.

use strict;
use DataObject;

# create new object, number of default values are set.
my $obj = new DataObject();

# set database server to connect
$obj->SetDBIdent('someDB');

# get fully qualified name
my $bv = $obj->GetTbl("bv");

# query string
my $sql = qq[
    select TOP 100 bv.book_price from $bv bv
    where bv.book_yield is null
    OUTPUT INTO '\\logs.txt'        
];

# Execute the query
my $resultSet = $obj->DoSql($sql);

# print the results
foreach my $row (@$resultSet) {
    print "$row->{book_price}\t";

Here is the error I get--> Can't execute statement:

select TOP 100 bv.book_price from acctdb.dbo.bv bv
where bv.book_yield is null
OUTPUT INTO '\logs.txt'

 Transaction Rolled Back
DBD::Sybase::st execute failed: Server message number=156 severity=15 state=2 line=4 server=SYBDEVtext=Incorrect syntax near the keyword 'OUTPUT'.
Statement=
    select TOP 100 bv.book_price from  bv
    where bv.book_yield is null
    OUTPUT INTO '\logs.txt'

I am not a Perl programmer, but I can tell you that OUTPUT INTO '\\logs.txt' is not valid in a SQL statement. You would have to retrieve the data into your program and then output it from there. — Ann L.
– Ann L., Commented Mar 11, 2015 at 20:46
Is there anyother way to redirect the output result of query into a text file.?? — patz
– patz, Commented Mar 11, 2015 at 20:52
@AnnL. It may not be standard SQL, but it looks like OUTPUT INTO is valid T-SQL (although all of the examples I saw include a list of columns, i.e. OUTPUT foo INTO ...). — ThisSuitIsBlackNot
– ThisSuitIsBlackNot, Commented Mar 11, 2015 at 20:52
@ThisSuitIsBlackNot Ah, yes, I'd forgotten you can do that. But that clause is for use with data manipulation statements, not SELECTs. — Ann L.
– Ann L., Commented Mar 11, 2015 at 20:58
Simply remove the OUTPUT INTO '\\logs.txt' from your query, open a filehandle, and change your print statements to write to the handle instead of STDOUT, e.g.: my $file = 'logs.txt'; open my $fh, '>', $file or die "Failed to open '$file': $!"; and foreach my $row (@$resultSet) { print $fh "$row->{book_price}\t"; } — ThisSuitIsBlackNot
– ThisSuitIsBlackNot, Commented Mar 11, 2015 at 21:02

Giorgi Nakeuri · Accepted Answer · 2015-03-11 22:58:18Z

1

You can use bcp utility for this. If you can run bat files from Perl then create bat file:

bcp "select TOP 100 book_price from  DatabaseName.SchemaName.bv where book_yield is null" queryout logs.txt -c -T

edited Mar 11, 2015 at 22:58

answered Mar 11, 2015 at 22:41

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Write SQL query output into text file/log file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related