General discussion

Locked

Redirect query output to text file

By arutjothi ·
In MS-SQL v 7.0, instead of displaying the result of a query in Result Pan, is there any change to redirect the result to a text file?

This conversation is currently closed to new comments.

10 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Redirect query output to text file

by gutridgesj In reply to Redirect query output to ...

i don't know about that, did not see a way.

but what i do is copy from that text window and paste to another app

or, if you have the SQLMail working, you could send an email to the sql server and the results would be emailed to you. we do this also.

Collapse -

Redirect query output to text file

by arutjothi In reply to Redirect query output to ...

I want to automate i.e., without manual intervention either a text file or Word Document or Excel has to be generated when even a ordinary level staff execute a query. If this is done, all the output files could be stored in a directory and if access rights given to the required users we can avoid many front end application development.

Collapse -

Redirect query output to text file

by arutjothi In reply to Redirect query output to ...

Please read 'change' as 'chance'

Collapse -

Redirect query output to text file

by chrandrob In reply to Redirect query output to ...

You can set up a DTS package (Data Transfer Services) to export to a text file. DTS gives you the option of exporting a table or the results of a query.

A DTS package also has the ability to execute an ActiveX script (using either VBScript or JScript). This allows you to write anything you want to a text file.

Plus you can schedule it to run automatically.

If you want to go this direction, and need more assistance, email me.

Collapse -

Redirect query output to text file

by arutjothi In reply to Redirect query output to ...

I want to automate i.e., without manual intervention either a text file or Word Document or Excel has to be generated when even a ordinary level staff execute a query. If this is done, all the output files could be stored in a directory and if access rights given to the required users we can avoid many front end application development.

Collapse -

Redirect query output to text file

by chrandrob In reply to Redirect query output to ...

Excel file is one of the many formats that DTS can export data to. And it is very easily automatable. Simply save the dts package and schedule a job to run it.

SQL Server has a built-in scheduler (jobs) that can be set to run as often as you want. This requires certain admin permissions that you may not have. Your DBA, SysAdmin, or whoever is in charge of permissions there can give you the needed permissions or schedule it for you.

A way around the permissions issue is to use an external program to execute dtsrun.exe which can run a dts package. I use Task Scheduler to execute it.

I export and export and ftp about 400 MB of text data and about 10 GB of images every day using Task Scheduler and dts packages. The dts packages email me to tell me if it suceeded or failed along with statistical data telling me how much was done. I spend about 5 minutes of time checking my email, and that's it.

Collapse -

Redirect query output to text file

by arutjothi In reply to Redirect query output to ...

Thank you very much for your help. But i want to store the output in a text file without manual intervention.

Collapse -

Redirect query output to text file

by Mark_T In reply to Redirect query output to ...

This is what we've done internally.

Create a .bat file, call it whatever you like

copy this following text into it:

isql /U username -n -E /d "DSN" -Q "stored procedure name" /S servername /w150 -oC:\output.txt

parameters you'll have to change are the username, password, dsn name and servername.

Instead of the "stored procedure name" above you could try a select statement instead. I've not tried it this way as our stored procedure was formatting the results into fixed width.

Play around with the above iSQL command to get it right, but as far as I know it the ONLY way to do this.

Anyhow, just run the .bat file and theres your outputted file.

Hope this helps

Collapse -

Redirect query output to text file

by arutjothi In reply to Redirect query output to ...

Thanks a lot. It helped me very much. But it is not working in windows 200o professinal.

Collapse -

Redirect query output to text file

by arutjothi In reply to Redirect query output to ...

This question was closed by the author

Back to Desktop Forum
10 total posts (Page 1 of 1)  

Related Forums