Extract data into a csv file using OPENROWSET

Question

Post reply

Extract data into a csv file using OPENROWSET

bhargavabs

Mr or Mrs. 500

Points: 544
More actions
July 29, 2008 at 5:22 am

#94320

insert into openrowset ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; .csv)};DefaultDir=C:\SaaS\DataLoad;', 'SELECT * from prod.csv' )
select prod_name from [prod_master].prod_definition
-----
and the error is :
Msg 7390, Level 16, State 2, Line 1
The requested operation could not be performed because OLE DB provider "MSDASQL" for linked server "(null)" does not support the required transaction interface.

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

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

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

Did you check the configuration below?

EXEC sp_configure 'Ad Hoc Distributed Queries'

Also, I just feel the part below should be something else, such as the file name. Or there is something wroing in your query somewhere. Am I right?

'SELECT * from prod.csv'

bhargavabs Mr or Mrs. 500 Points: 544 More actions · Answer 2

Hii

Yes I have set 'Ad Hoc Distributed Queries' to 1. Actually I have used the OPENROWSET with similar syntax to load data into table.

And the file name is mentioned as a part of select statement with default dir defined. I will anyway try changing that part !!

Thanks for the reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

Looks like you are missing a semi-colon after MSDASQL

insert into openrowset ('MSDASQL',

Try this

insert into openrowset ('MSDASQL;', .........

and another point to check has the OLE DB provider "MSDASQL;" been registered?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

bhargavabs Mr or Mrs. 500 Points: 544 More actions · Answer 4

I tried with ';' after MSDASQL and then comes the error 'The OLE DB provider "MSDASQL;" has not been registered.' which is exactly what you ve doubted. Need to check how to solve this. Can you please suggest a solution !

But when I try to select from csv file with same provider, it doesnt throw any error !

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

I got very frustrated working with your code and attempting to follow Microsofts procedures to correct the registration error so I developed my own without any problems. It is:

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Text;Database=F:\Testdata\;HDR=Yes;', 'SELECT * FROM Test02.txt')

SELECT KeywordId, Keyword FROM Keywords

Items to specifically note:

Using the Jet.OLEDB.4.0 provider

The output file "Test02.txt" was created with a first line "Keywordid,Keyword"

I successfully exported 215 rows to the text file.

Can you change yours to use the Jet.OLEDB provider and test?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

bhargavabs Mr or Mrs. 500 Points: 544 More actions · Answer 6

thank you for the reply.

I again have tried couple of options. It seems OPENROWSET feature is designed more for imports than exports. Currently am trying to build SSIS packages to accomplish the same ! This seems a bit easy than the earlier option in terms of debugging !!

Dash Bored Valued Member Points: 71 More actions · Answer 7

HOORAY! Thank you BitBucket, I was having the same problem and I've been googling everywhere looking to get it fixed. Your fix did it!

Rahul-314088 SSC Rookie Points: 37 More actions · Answer 8

Hi

I tried your solution on SQL Server 2008

I got this error

OLE DB provider "MSDASQL.1" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "MSDASQL.1" for linked server "(null)".

Please help me in detail

Thanks in advance