Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

store procedure result to excel Expand / Collapse
Author
Message
Posted Tuesday, November 30, 2010 12:25 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 8:31 AM
Points: 236, Visits: 934
Please help in turning the store procedure result into excell and also to call this store procdure to batch file.

The store procedure suppose is;

USp_Sqlmonitor
Post #1028188
Posted Tuesday, November 30, 2010 12:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
SSIS can create Excel files from proc output.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1028195
Posted Tuesday, November 30, 2010 2:19 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 8:31 AM
Points: 236, Visits: 934
I don't want to depend on sql agent or ssis. I want to create the procedure to execute and result output should be in excell so that I can call this procedure into batch file.
Post #1028274
Posted Tuesday, November 30, 2010 2:54 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 1,429, Visits: 2,697
try using xp_cmdshell to BCP the results to an XLS file. Excel might complain that it is in the wrong format but should still open the file.

exec xp_cmdshell 'bcp "exec database.dbo.usp_Sqlmonitor" queryout c:\temp\test.xls -c -T'



Bob
-----------------------------------------------------------------------------
How to post to get the best help
Post #1028300
Posted Tuesday, November 30, 2010 3:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
If you output the format as if it were a CSV (commas and quotes), and put an xls file extension on it, Excel will open it without complaining. I've done that before.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1028307
Posted Tuesday, November 30, 2010 8:29 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 8:31 AM
Points: 236, Visits: 934
Thank you all for your response. I will try it tomorrow.
Post #1028366
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse