Spooling query results to a text file

Spooling query results to a text file

Post by Michael McSkean » Mon, 08 Dec 1997 04:00:00



Sorry if this is a regular question but can somebody tell me how to
spool query results to a text file rather than to the screen in ISQL.
Ive seen it done in Oracle but for the life in me I can't find any
mention of how to do this in SQL-Server except for creating some sort
of mail client. I would of thought this would be a standard
task.

Im pretty new to SQL and databases so bear with me......

=========================================================================
Michael McSkeane

 
 
 

Spooling query results to a text file

Post by Gary Kueve » Mon, 08 Dec 1997 04:00:00


Quote:>Sorry if this is a regular question but can somebody tell me how to
>spool query results to a text file rather than to the screen in ISQL.
>Ive seen it done in Oracle but for the life in me I can't find any
>mention of how to do this in SQL-Server except for creating some sort
>of mail client. I would of thought this would be a standard
>task.

Its not that simple in either MS SQL or Sybase.

One way is to put your results into a 'permanent' table either in your
database or in tempdb, then use bcp to get the data into a text file.
Control the process with a batch file.

Gary

 
 
 

Spooling query results to a text file

Post by Darrel Knigh » Tue, 09 Dec 1997 04:00:00



> >Sorry if this is a regular question but can somebody tell me how to
> >spool query results to a text file rather than to the screen in ISQL.
> >Ive seen it done in Oracle but for the life in me I can't find any
> >mention of how to do this in SQL-Server except for creating some sort
> >of mail client. I would of thought this would be a standard
> >task.

> Its not that simple in either MS SQL or Sybase.

> One way is to put your results into a 'permanent' table either in your
> database or in tempdb, then use bcp to get the data into a text file.
> Control the process with a batch file.

> Gary

What a load of rubbish....' not that simple in either MS SQL or Sybase'.

Look at the -o option for the command line version of ISQL or you can
use the DOS > symbol. It's not rocket science is it!!!!

Darrel Knight
* Computer Consultant
Tetbury, UK

 
 
 

Spooling query results to a text file

Post by Neil Pik » Sun, 14 Dec 1997 04:00:00


Michael,

 All you can do is redirect the output of the whole ISQL session with
the -o parameter or the > sign.

 Neil Pike MVP/MCSE
 Protech Computing Ltd (MS Solution Provider)

 
 
 

Spooling query results to a text file

Post by Michael McSkean » Tue, 23 Dec 1997 04:00:00




>>Sorry if this is a regular question but can somebody tell me how to
>>spool query results to a text file rather than to the screen in ISQL.
>>Ive seen it done in Oracle but for the life in me I can't find any
>>mention of how to do this in SQL-Server except for creating some sort
>>of mail client. I would of thought this would be a standard
>>task.

Im sorry I didn't make myself clear in the original question. The
server is kept at a different site so I really need to do this in ISQLW
unless someone can tell me how to get ISQL onto my client
Am I being thick or have I just not been looking in the right places.
=========================================================================
Michael McSkeane

 
 
 

Spooling query results to a text file

Post by Neil Pik » Thu, 25 Dec 1997 04:00:00


Michael,

 If you've installed SQL or the SQL tools on your pc, then you'll have
ISQL already.

 Neil Pike MVP/MCSE
 Protech Computing Ltd (MS Solution Provider)

 
 
 

Spooling query results to a text file

Post by Darwin Hathew » Tue, 30 Dec 1997 04:00:00



> >>Sorry if this is a regular question but can somebody tell me how to
> >>spool query results to a text file rather than to the screen in ISQL.
> >>Ive seen it done in Oracle but for the life in me I can't find any
> >>mention of how to do this in SQL-Server except for creating some sort
> >>of mail client. I would of thought this would be a standard
> >>task.

> Im sorry I didn't make myself clear in the original question. The
> server is kept at a different site so I really need to do this in ISQLW
> unless someone can tell me how to get ISQL onto my client
> Am I being thick or have I just not been looking in the right places.
> =========================================================================
> Michael McSkeane


You should be able to use a UNC filename for the output from either ISQL
or BCP:

isql /U xx /P xx /S xx <scriptfile.sql >\\wkstation\share\dirpath\file
bcp db.owner.table out \\wkstn\share\dirpath\file /f fmtfile

or map the share to a drive letter.

You'll need pernmission to access the share (create one or use the
default c$ share) on the target PC.

Alternatively, you may install the SQL utilities (use the SQL Server
CD-ROM setup and pick "utilities only" option) and have the client PC do
a "pull" with bcp or ISQL when it's ready.  NT workstations can run the
scheduler service and schedule the job via the "at" command or W95
workstations can run a job via System Agent (from the W95 Plus disk, I
think) other scheduler utilities.

Opinions expressed herein are my own and may not represent those of my employer.

 
 
 

1. How to spool query result to file on HD

Can someone show me haw to spool query result to file on HD. I am using
MSSQL 2000

e.g Oracle:

SET SERVEROUTPUT ON size 1000000;
set feedback off
set termout off
set trimspool on
set linesize 80
set verify off
BEGIN
  DBMS_OUTPUT.PUT_LINE('####################################');
  DBMS_OUTPUT.PUT_LINE('# User and DB Connections with IPs #');
  DBMS_OUTPUT.PUT_LINE('####################################');
END;
/
SELECT
users.login,sys_user_type.name,user_session.device_address,user_session.port
,expiry_date FROM user_session,users,sys_user_type
  WHERE user_session.user_id = users.id
    AND user_session.user_type_id = sys_user_type.id

2. Help with SQL Query in VB

3. Spooling output of query to test file using query analyzer

4. Error in Column 1: Numeric Value out of range

5. How to spool results to a file

6. server dies immediately after startup

7. Index spool / eager spool costing 70% of query

8. NYC Area - INGRES Developers - Full Time Position for Several Developers

9. Return query results to a text file on Hard drive...Newbie Question

10. output query results to a text file

11. How do I Print query results to an e-mail - or text file

12. query result to text file