• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

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

I want to call a stored procedure from an application and save the results as a comma delimited text file. Is there a way to do this?

I want the stored procedure to handle everything, I only want to return a 0 or 1 to the application upon completion or failure of the file.
0
brownkj1
Asked:
brownkj1
  • 5
  • 5
1 Solution
 
HilaireCommented:
>>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 ??
0
 
brownkj1Author Commented:
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!
0
 
Anthony PerkinsCommented:
>>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.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
brownkj1Author Commented:
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?

0
 
Anthony PerkinsCommented:
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.
0
 
brownkj1Author Commented:
>>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!!


0
 
Anthony PerkinsCommented:
It really depends on the permissions allowed or not by SQL Server.  For example, xp_cmdshell is usually something only an adminstrator can and should use.  What you can and cannot access from SQL Server is dependent on many factors.  Do not assume that the SQL Server has access to the whole domain.  If it does, than the network administrator should be quietly lead outside and shot.

>>I don't have very strong SQL or Access skills<<
This is exactly why you should only attempt this from the client and not the server.  It is a lot simpler.
0
 
brownkj1Author Commented:
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....
0
 
Anthony PerkinsCommented:
I'm not sure how to give points to this question....
See here:
What are my choices?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi67
0
 
brownkj1Author Commented:
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.

0
 
Anthony PerkinsCommented:
>>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.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now