Query Results

Question

Post reply

Query Results

Moe M

Old Hand

Points: 364
More actions
March 3, 2006 at 3:21 pm

#76497

Hey guys,
I want to export a table to a csv file. The catch is I don't have permission to execute sp_cmdshell or sp_OA family of procedures.
I use SQL Server 2005 and I don't know any third method to do this.
Could you please help me??
Ryan
Moe M
Database Consultant
http://www.cubeangle.com

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

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

Ian Yates SSCoach Points: 19738 More actions · Answer 1

Couple of methods come to mind...

1. Use DTS from SQL 2000 if available

2. Use MS Excel to connect to the DB, populate a spreadsheet with the data and save as CSV from Excel

3. Use SSIS from 2005 - I'm not very familiar with it yet though so cannot offer any further opinion...

Let us know how you go!

Moe M Old Hand Points: 364 More actions · Answer 2

Thanks

But the problem with DTS or SSIS is if you want to execute them from a stored procedure first you have to be able to create a package using sp_OACreate and I don't have permission to execute the sp_OA family of procedures.

But Im gonna give the Execl idea a second idea.

Moe M
Database Consultant
http://www.cubeangle.com

Ian Yates SSCoach Points: 19738 More actions · Answer 3

Didn't realise you wanted to execute from a stored proc? You can run DTS without using a stored proc

Nishad Paul Grasshopper Points: 10 More actions · Answer 4

Maybe you could use 'bcp out' with a ',' as delimiter and output file as .csv.

Kindest Regards,

Nishad Paul

Moe M Old Hand Points: 364 More actions · Answer 5

Yeah I know I could but for some reason I need to do it from a stored proc.

Moe M
Database Consultant
http://www.cubeangle.com

Moe M Old Hand Points: 364 More actions · Answer 6

I guess for using bcp again I need to use xp_cmdshell in the stored proc.

Moe M
Database Consultant
http://www.cubeangle.com

Ian Yates SSCoach Points: 19738 More actions · Answer 7

Could you get a system admin to be the one who creates the procedure (you write the code though) and then the permissions of the code in the proc are those of the procedure owner (the admin) rather than you?