Sql stored procedure output to text file

Collectives on Stack Overflow. Learn more. How to output stored procedure query results to a text file Ask Question. Asked 4 months ago. Active 4 months ago. Viewed times. CardName I have this created as a stored procedure that I want to run on a schedule. Edit - Thank you all for all of the advice and suggestions everyone has provided so far. Using PowerShell I think I could probably get this working, although my main hesitation has to do with the scheduling piece.

Final Edit - Was able to get this working using bcp. Improve this question. Brady Brady 1 1 gold badge 5 5 silver badges 18 18 bronze badges. Brady in your BCP command, it makes a separate connection to your SQL Server which will put you in the default database; your command should specify databasename.

Add a comment. Active Oldest Votes. For debugging you can run it directly in an SSMS query window. Improve this answer. Stu Stu I edited the OP with some additional information on where I'm getting stuck here.

Brady - You can't use max for the command for BCP - try with a suitable length eg varchar and retry — Stu.

Brady also note, from your comment in your edit, you should specify the full database. I was able to get it working with your help. I'll post my final bcp implementation in the OP. Sage Enterprise Intelligence Overview. Sage Intelligence Overview. AP Automation. Payment Solutions. Document Management. EDI Solution. Fixed Asset Management. Job Management Software. XE Currency Connector for Sage. Month-end Close. Time and Expense. AvaTax by Avalara. Warehouse Management Systems. ERP Migration.

Technology Consulting. Software Consulting. IS Assurance. Custom Development. Cisco Certified Partner. Enterprise Cloud Solution. Discrete Manufacturing. I also used bcp and found a couple other helpful posts that would benefit others if finding this thread. Just to answer a native way to do this that finally worked, everything had to be casted as a varchar. Stack Overflow for Teams — Collaborate and share knowledge with a private group.

Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Asked 8 years, 9 months ago. Active 11 months ago. Viewed k times. Improve this question. Al Phaba Al Phaba 6, 11 11 gold badges 46 46 silver badges 75 75 bronze badges.

In addition to the answers below, I would consider if you really want to generate the csv file from the procedure or not. The first problem is permissions: the SQL Server service account will need permission to write to an output folder, which may cause issues.

Let the procedure generate the results, and then use an external script to generate the csv file. If we execute this stored procedure, it returns the total number of records, i. In the previous example, we saw the primary use of the Return values in the SQL Server stored procedure. We can use these values to define the stored procedure logic and error handling.

Note: For this tip, you can create the function dbo. The dbo. Therefore, the following T-SQL uses these return codes to interpret the result accordingly. In this scenario, we execute the stored procure with a valid customer id. In this case, the stored procedure returns a value of 0.