Call stored procedure from an application and save results in comma delimited file

>>Is there a way to do this?<<
Stored procedure execute on the Server side, so you won't be able to save a file on a cleint machine.
To write on the server side, you can use any of these methods (and maybe others ...)

- use xp_cmdshell to run echo commands redirected to a file with > and >>
- use the xp_cmdshell system stored procedure to run the BCP commandline tool with the QUERYOUT option
- use the sp_oacreate, sp_oamethod, ans sp_oa* system stored procedure to create filesystemobject object and create a textstream and add line in a loop
- use an ADO stream instead, with the savetofile method
- ...

what kind fo file do you need to generate ??

I want to generate a comma delimited text file. The application I am calling the stored procedure from is written in Microsoft Access - which is why I want SQL to handle the entire process if possible.

Could you go into a little further detail on these things? I have never used them before and so it's a little over my head. I have heard about xp_cmdshell before but haven't heard about any of the other methods you are describing.

>>-use an ADO stream instead, with the savetofile method

-use the xp_cmdshell system stored procedure to run the BCP commandline tool with the QUERYOUT option

-use the sp_oacreate, sp_oamethod, ans sp_oa* system stored procedure to create filesystemobject object and create a textstream and add line in a loop>>

Thanks!

>>use an ADO stream instead, with the savetofile method<<
Here I beg to differ with Hilaire as this would imply client side.

Another alternative is to create a DTS Package using your stored procedure as source.

>>The application I am calling the stored procedure from is written in Microsoft Access - which is why I want SQL to handle the entire process if possible. <<
The question, you need to ask yourself is where you want to write the file.  If it is on the local workstation than it is a no-brainer and should be done client side.

The file needs to be saved in a network directory that the local workstation running that Access application has access to. I will not be able to grant access to a directory on the server itself, I should have specified that.

This might be a dumb question but can the DTS package be executed from Microsoft Access?

In order to have the file created on the server you are making the assumption the server has access to that folder.  

>>This might be a dumb question but can the DTS package be executed from Microsoft Access?<<
Sure.  There are a couple of options:
1. You can generate the Visual Basic code to incorporate as a module in MS Access for greater flexibility or
2. For simplicity (less flexible) execute DTSRun passing any optional parameters in the command line.

>>In order to have the file created on the server you are making the assumption the server has access to that folder.  <<

This may also be a dumb question, but I mine-as-well as while I'm asking them...can I create a file from a stored procedure  or DTS package to a folder on the network that the SQL server doesn't have access to (I'm pretty sure the answer to this is going to be no to that)?

If the answer is indeed no, then I will probably have to pass the results back to Access and create the file Access side, which I was hoping to avoid.

Any suggestions?? All the comments so far sound good except for I can't create the file on the server and I can't request access to be granted to where the file needs to be placed (on the network).

Am I not coming across clearly? Sorry!

Unforunately I'm a BA consultant in a programming position trying to program at a client site (hence the denial for access to anything)! I don't have very strong SQL or Access skills, but I'm trying!!

Yes, I agree. I guess I will just have to execute the stored procedure, pull back the results, and then create the file. I was really hoping to not have to deal with Access like that (I really dislike Access), but that beats dealing with permission issues.

thanks for your help, I'm not sure how to give points to this question....

I'm not sure how to give points to this question....
See here:
What are my choices?
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi67

I guess I will just give you the points since you helped me even though the question was never really answered. I won't split the points because Hilaire never really followed up with me like you (acperkins) did.

Thanks for trying and thanks for showing me how to give points.

>>I won't split the points because Hilaire never really followed up with me like you (acperkins) did. <<
Be kind. We are all here volunteering our time, but we do have our work, as well.  We cannot always follow-up in the time frame you expect. I know for a fact that if Hilaire could have done it, than he would have done so.

In any case, thanks for the points.