How to Export data from a SP to an Excel sheet

Question

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

If you want to export data to a excel file you need to be looking at the bcp utility.

"Keep Trying"

Patrick.I Hall of Fame Points: 3105 More actions · Answer 2

Need more info.

Can BCP export to excel.

About xp_cmdshell

thanks in advance

ChiragNS One Orange Chip Points: 26137 More actions · Answer 3

You can use openrowset or opendatasource to export data to excel. If the data set is large its better to use bcp.

Example of bcp - xp_cmdshell 'bcp "select col1,xol2 from table1" queryout "C:\test.xls" -c -t"+" -r"" -S "YourServerInstance" -U"user" -P"pwd"'.

bcp is a command line utility so you need to xp_cmdshell to execute it from within sql server.

Pls lookup BOL for more details.

"Keep Trying"

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 4

Patrick

"** this is SQL 2000"

Will you stay in 2000 ? If so DTS and VB.script may help you.

I have left DTS behind me so I cannot help you there.

/Gosta

Sergiy SSC Guru Points: 110209 More actions · Answer 5

Patrick,

actually Excel can get data by running a query against SQL Server.

Did you consider this option?

_____________
Code for TallyGenerator

Patrick.I Hall of Fame Points: 3105 More actions · Answer 6

Gosta i know and i feel u.

I would def could have done this easier using SSIS.

Thanks

Patrick.I Hall of Fame Points: 3105 More actions · Answer 7

Yeah i know you can query sql data via excel etc..

But the people who need the data are not in our network.

The data needs to be send in interval across a network externally.

Thx guys...

I have used a package > job and xp_cmdshell to move the data

Cookies on SQLServerCentral