Export data to CSV or Text file

[vimalvisves]

Hi Friends,

I am getting some error when i use below code

insert into OPENrowset('msdasql','Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=d:\','select name from test.csv')
select name from sysobjects

I am getting error


Server: Msg 7390, Level 16, State 1, Line 1

The requested operation could not be performed because the OLE DB provider 'msdasql' does not support the required transaction interface.

OLE DB error trace [OLE/DB Provider 'msdasql' IUnknown::QueryInterface returned 0x80004002].


But if i select records from the same file, i am getting output,

select * from OPENrowset('msdasql','Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=d:\','select name from test.csv'

Means, I am able to select records but unable to insert, I want to insert, so kindly help me

[vimalvisves]

I got solution for this issue

declare @sql varchar(8000)
select @sql = 'bcp "Exec TestServer..prc_Test" queryout c:\abc\abc.csv -c -t, -T -S'+ @@servername
exec master..xp_cmdshell @sql

This will generate csv, that contains data whaich are displayed by procedure prc_Test

[pbmods]

Heya, vimalvisves.

Thanks for posting your solution.

Glad to hear you got it working! Good luck with your project, and if you ever need anything, post back anytime :)

I'll go ahead and move this to the MS SQL forum for other developers' reference.

[sat1212]

Thank you,

I found it very useful for my project,

Thank you so much,

Regards
Satish