x

How to Capture Query results to Text file.

Team,

I have few stored proc that runs every day one after other, i want to capture every stored proc results to a text file and save it with different file names.

What is the work around to complete this.
more ▼

asked Dec 05, 2011 at 06:36 AM in Default

ETHMAN5 gravatar image

ETHMAN5
482 44 46 48

How are they being called/executed (web app, .sql script, batch file, PowerShell, SQL Agent job)?
Dec 05, 2011 at 07:06 AM Shawn_Melton
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

found this solution by someone else ---

Here is an 11 line solution. Granted this looks very long, but it is only because I included a lot of remarks about each step, and the fact that I chose a large table to work with. Should have picked something smaller to show you and example with, but this is a real part of one of my databases. This solution creates a temporary table from a query, exports it as a comma delimited text file, then deltes the temp table. Again, start to finish, only 11 lines of code (without remarks or error handling).

 Private Sub CommandExportAgentInfo_Click()

Dim spec, tbl, path, filename, s As String 's=SQL Dim db As Object

'Create file export spec by doing a manual export, clicking the Advanced button, and after 'getting all you options the way you like them, click the SaveAs button 'and give your spec an easy to remember and relevant name

Set db = CurrentDb 'Need to utilize the Execute SQL spec = "tblAgent" 'The spec I set up is to save a table as a comma delimited, with no quotes around strings tbl = "tblTempExport" 'Pretty self explanitory, set up a variable to hold the name of the table you wish to export path = "C:\Temp" 'Again just a variable to hold the destination directory filename = "tblAgent.txt" 'Or what ever you wish your filename to be 'My suggestion is to have these come from text boxes on a form. That way the person 'exporting the table can select their own destination and filename.

s = "SELECT tblAgentInfo.intID, tblAgentInfo.AgentID, tblAgentInfo.AgentNameL, " & _ "tblAgentInfo.AgentNameF, tblAgentInfo.SSN, tblAgentInfo.DOB, tblAgentInfo.StatusID, " & _ "tblAgentInfo.HireDate, tblAgentInfo.TermDate, tblAgentInfo.SalesID, tblAgentInfo.CableData, " & _ "tblAgentInfo.CableDataPassword, tblAgentInfo.KBLogin, tblAgentInfo.KBPassword, " & _ "tblAgentInfo.NTLogin, tblAgentInfo.NTPassword, tblAgentInfo.ISPLogin, tblAgentInfo.ISPPassword, " & _ "tblAgentInfo.intSupvID INTO " & tbl & " FROM tblAgentInfo;" 'Build your query then switch to SQL view, copy from there, and paste into VBA 'Also, using the & _ line continuation makes for an easier read

db.Execute s 'Found this little command here in DBForums. Using this you do not have to 'use the docmd.setwarnings False because Execute does not generate the messages 'like "you are about to create a table, you are about to insert 50 rows, etc...

DoCmd.TransferText acExportDelim, spec, tbl, path & filename 'The DoCmd.TransferText has several options for importing, exporting and linking 'various file and types and you can do delimited or fixed.

DoCmd.DeleteObject acTable, tbl 'Delete the temporary table

End Sub
I hope this helps you out,
more ▼

answered Dec 05, 2011 at 06:58 AM

IT1 gravatar image

IT1
348 25 28 31

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x241

asked: Dec 05, 2011 at 06:36 AM

Seen: 1197 times

Last Updated: Dec 05, 2011 at 06:36 AM