How to Capture Query results to Text file.


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

avatar image

482 44 46 51

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

avatar image

388 27 33 37

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Dec 05, 2011 at 06:36 AM

Seen: 1538 times

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

Copyright 2017 Redgate Software. Privacy Policy