0

I have a stored procedure which returns a table output with 30 columns and around 200k records. Baseline of stored procedure: uses 4 temp tables within the procedure.

I need to take this output and put it into a CSV file. I tried using SSIS, but there seems to be a problem with SSIS having to execute stored procedure which uses TEMP tables. So now I am back to dealing with stored procedure output again.

In a nutshell:

  • Need output of stored procedure in a CSV, have to automate it for daily reports
  • Tried using SSIS, but SSIS not able to execute stored procedure using TEMP tables
  • How can I get stored procedure to give a CSV output with out using BCP/sqlcmd

EDIT:

I apologize it is definitely Possible through SSIS. Worked for me using the Set FMTONLY ON/Off feature. Please follow this link: Follow this Link to Get Three ways to do this using SSIS

I am specifically trying to use only a Stored Procedure to achieve this. So another question adds up to this:

Can I using only a stored procedure upload the output which is a CSV file to a SharePoint site.

EDIT 2:

Can I using only a stored procedure get the output in a CSV file. If anyone can suggest me this part, it would be helpful

Community
  • 1
  • 1
snp.it
  • 512
  • 2
  • 10
  • 30
  • 1
    It's not completely true that you can't use TEMP tables in your SSIS DataSource Stored procs. You just have to take some extra steps. I've done it before using the "WITH FMTONLY ON/OFF" method, but there are others as well: https://www.google.com/search?sourceid=navclient&aq=&oq=ssis+stored+procedure+with+temp+tables&ie=UTF-8&rlz=1T4GGNI_enUS551US551&q=ssis+stored+procedure+with+temp+tables&gs_l=hp...0i22i30l5.0.0.0.5598...........0.KLjifGZUZXw – Tab Alleman Dec 23 '14 at 16:47
  • 2
    I prefer the IF (0=1) hackaround in SSIS, but it's definitely possible and most likely the best way to do this. An Example http://stackoverflow.com/a/5076757/2587452 – Eric Hauenstein Dec 23 '14 at 20:52
  • I don't know squat about SharePoint, but I bet it's easily done using a CLR proc. – Tab Alleman Dec 24 '14 at 14:46
  • So really you've managed to answer your current question right? If you want get data from SQL to sharepoint you really need to use some kind of external agent such as power shell to query the data and push it up to a sharepoint web service – Nick.Mc Nov 16 '16 at 05:33
  • Here's powershell code to push a file up to sharepoint: http://www.sharepointdiary.com/2012/07/upload-file-to-sharepoint-library-using-powershell.html. You can optionally find code that extracts a CSV from SQL Server and slap them together – Nick.Mc Nov 16 '16 at 05:34

1 Answers1

-1

Right click server -->Facents-->Surface Area Configuration -->XPCmshellEnbled -->true

exec master..xp_cmdshell 'BCP "sp_who" QUERYOUT C:\av\sp_who.txt -S MC0XENTC -T -c '

Vishe
  • 3,383
  • 1
  • 24
  • 23