Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Export to txt/csv

Author  Topic 

vimalvisves
Starting Member

3 Posts

Posted - 2007-08-07 : 07:16:14
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, any one kindly help me

Vishwa

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-07 : 07:46:21
moved from script library

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-08 : 02:27:04
I think you should make that as EXCEL file with .xls extension and then do query
See if this syntax helps you
http://sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

vimalvisves
Starting Member

3 Posts

Posted - 2007-08-08 : 04:28:58
Hi madhivanan,

Thank you for your reply,

I want to explain some thing to you, I am working with web application, in this application we decide to give some XL download to the users, I have already created them (The code is same as the code specified in your link).

The problem is Size of XL file is too big to download, even after i zip them. Now we decided to make it as CSV instead of XL.

In my SP, i am able to read CSV file, but i am getting error while exporting CSV file, is there any other way to export CSV from SP.

Note: Here i used SP instead of StoredProcedure


Vishwa
Go to Top of Page

vimalvisves
Starting Member

3 Posts

Posted - 2007-08-20 : 08:13:37
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


Vishwa
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-20 : 08:25:26

Thanks for sharing
I think that was covered in my thread


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -