Post reply

Extract data into a csv file using OPENROWSET

  • 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.

  • July 29, 2008 at 3:09 pm

    #850159

    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'

  • July 29, 2008 at 4:06 pm

    #850181

    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

  • July 29, 2008 at 4:10 pm

    #850183

    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]

  • July 30, 2008 at 2:57 am

    #850305

    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 !

  • July 31, 2008 at 1:17 pm

    #851501

    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]

  • August 6, 2008 at 2:36 pm

    #854118

    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 !!

  • October 23, 2008 at 11:39 am

    #888776

    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!

  • June 11, 2009 at 2:32 am

    #1007836

    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

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

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