Stored procedure results to text file

Login to reply. Post reply. July 31, at am Please help! Thank you. It has scripting capabilities. Johan Learn to play, play to learn! How about this Who am I?

Sushila, Thanks a ton! I hadn't explored the option you mentioned. July 31, at pm See more: C. How to export the values from Stored procedure to text file , i got a stored procedure with input parameter. On the button click event i wanted to export the values to text file by passing input value. Stored Procedure: SQL. Copy Code.

Posted 9-May am KRI Updated 9-May am ZurdoDev. Add a Solution. And what sort of help do you need? What have you tried? Accept Solution Reject Solution. So call the stored procedure from your C code in the normal way, and use an SqlReader to read it. Then just use File. Write, File. If you want the contents of a stored procedure it gets complicated.

In summary, you must use the stored procedure to fill a table, then use BCP to export the table, and finally you need to clear the table. I wish there was a better way! There is and it's called OSQL. And the arguement headers -U, -P, etc.

Go figure. My main issues were with the syntax. Using BCP is quite straight forward, there's no need to try and store the result of a query. Your select statement returns a result set Each element, each intersection of some row and some column in the result set, is a single value with its own datatype.

Your query returns one column, but multiple rows. So you have multiple values. You can't assign multiple values to one variable, much like a single cell in excel can't also be a whole column in excel. As far as getting data out of sql to a text file, pure TSQL-based solutions aren't really a good fit for this. You want some kind of "client application" to run the SQL and then interact with the world outside the database. Since you said you wanted to run this on a schedule, you might want to use SQL Agent as your "client application", since it's also a scheduler.

You can use this job step type to run sqlcmd have a read through this and have sqlcmd output your query results to a text file in just the way you mentioned in your question.

You could also use a SQL Server integration services package. That's a particularly good idea if you already have an integration services catalog somewhere, or if you expect to be doing more exports. Otherwise, the CmdExec solution is fine. If you choose to use SQL Agent with a CmdExec step, you might want to set up a proxy account with limited permissions to execute the job.

This should get you started. In fact, the usual advice is to keep it disabled entirely. In regards to the error you are getting Sorry I am not much help with exporting it to a text file. 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.