0

I am trying to understand what a particular piece of source code does. The original author has written the following excerpt:

/*******************************************/
-- Inserează în tabela temporară.
/*******************************************/
INSERT INTO #tmp
(
    [tip]
  , [ev]
  , [ap]
  , [cant]
  , [preț]
  , [data]
)
SELECT *
FROM
OPENDATASOURCE
(
    'Microsoft.Jet.OLEDB.4.0'
  , 'Data Source=C:\ETU; Extended Properties="TEXT; HDR=yes; FMT=DELIMITED"'
)...[EV#csv]

When I am executing this piece of code in SSMS, I get the following error:

Msg 7314, Level 16, State 1, Line 4

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not contain the table "EV#csv". The table either does not exist or the current user does not have permissions on that table.

MSDN shows certain syntaxes for linked servers, but I do not think that the original author meant that.

There is a certain file on the hard disk which full path is C:\ETU\EV20150126000001.csv.

I have attempted to rename the file to the EV#csv new name, yet to no avail.

SELECT @@VERSION says:

Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (Intel X86) Sep 16 2010 20:09:22 Copyright (c) 1988-2008 Microsoft Corporation Express Edition with Advanced Services on Windows NT 5.1 (Build 2600: Service Pack 3)

Improve this question
4
  • The Jet.OLEDB.4.0 is data access library for Excel/Access so the code is trying to read data from CSV file into a temporary table. The DATA Source parameter should point to the filename though. If you change the source to '('Microsoft.Jet.OLEDB.4.0' , 'Data Source=C:\ETU\EV20150126000001.csv; Extended Properties="TEXT; HDR=yes; FMT=DELIMITED"' )' you will read the data from the file
    – Spörri
    Commented Apr 16, 2015 at 9:03
  • @Spörri: Strange. It still complains about some linked server. The file path is correct - with double quotes or not - yet the following message appears: "OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "'C:\ETU\EV20150126000001.csv' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"."
    – user62547
    Commented Apr 16, 2015 at 9:37
  • ok, haven't done this for a long time: blog.waynesheffield.com/wayne/code-library/ad-hoc-querying/… SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\Temp\;Extended Properties="Text;HDR=YES;"')...[test#csv] Will open test.csv from c:\temp so change [EV#csv] to [EV20150126000001#csv] but since this is a CSV file I would use openrowset bulk: bradsruminations.blogspot.com/2011/01/…
    – Spörri
    Commented Apr 16, 2015 at 10:02
  • Yep. That did the trick. So, maybe that '#' sign is a place-holder for a period. The final source code went into the following version: "SELECT * FROM OPENDATASOURCE ( 'Microsoft.Jet.OLEDB.4.0' , 'Data Source=C:\ETU; Extended Properties="TEXT; HDR=yes; FMT=DELIMITED"' )...[EV20150126000001#csv]". Thank you for commenting. Maybe you can create an answer with that, if you feel like.
    – user62547
    Commented Apr 16, 2015 at 10:13

1 Answer 1

Reset to default
1

The Jet.OLEDB.4.0 is data access library for Excel/Access so the code is trying to read data from CSV file into a temporary table.

If you change the source to OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\ETU;Extended Properties="Text;HDR=YES;"')...[etu#csv] you will open c:\etu\etu.csv so changing the last parameter to ...EV20150126000001#csv will open your file. See:http://blog.waynesheffield.com/wayne/code-library/ad-hoc-querying/ As this is a CSV file I would prefer to use openrowset bulk as the Jet.OLEDB drivers are "interesting" on 64bit platforms: see: http://bradsruminations.blogspot.com/2011/01/ for further ideas.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.