November 21, 2016 at 9:56 am
How to create a store procedure to export a table to CSV file?
I try to use code below but company will not allow it.
Is there another way to do it?
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\0\testing.csv;',
'SELECT * FROM [SheetName$]') select * from mytable
November 21, 2016 at 10:03 am
What do you mean "The company will not allow it"?
If you're not allowed to export tables contents to a CSV, why are you trying if it's against company policy to do so?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 21, 2016 at 10:15 am
Thom A (11/21/2016)
What do you mean "The company will not allow it"?If you're not allowed to export tables contents to a CSV, why are you trying if it's against company policy to do so?
Guess that it's the OPENROWSET that isn't allowed
😎
November 21, 2016 at 10:16 am
Here is error message.
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.
November 21, 2016 at 10:25 am
You could try using bcp, which could be called with xp_cmdshell.
Another option is to change the process and use SSIS.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply