Hi Jeff,

The following should help.

select *

FROM OPENROWSET('MICROSOFT.ACE.OLEDB.12.0','Text;Database=c:\temp;HDR=Yes', 'SELECT * FROM [test.CSV]')

select *

FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=c:\temp;','select * from [test.CSV]')

Further to this, one...

Hi there,

thanks for your reply.

Yes You are correct it would be better for them to use openquery and this is something we will try. I am curious why the...

A futher update.

If instead of inserting into a table we simply select from the source table no blocking occurs, something like

Select * From [DATA\CA\Queries\CA Import]

Here is a screen shot from Activity Monitor showing the blocking

A little more information.

The following select statement is being blocked

select table_name from INFORMATION_SCHEMA.VIEWS where table_name like 'AAA_Utilities_AAA%'

by

Select * into [testable]

From [DATA\CA\Queries\CA Import]

in which [DATA\CA\Queries\CA Import] is a view shown...

Hi all,

Firstly, There are now 64 bit OLEDB drivers available, these were released with the release 64 bit Microsoft Office earlier this month and have been around in beta form...

A little more information on this one.

If I use the MICROSOFT.JET.OLEDB.4.0 driver I get the same problems.

If I use ODBC, Driver={Microsoft Excel Driver (*.xls) it works fine.

If I specify a SCHEMA.INI file with DateTimeFormat="yyyy-mm-dd" the datetime column is read in as varchar correctly, I can then cast it to datetime. It therefore appears that the...

Melbourne Australia, UTC + 10:00

A little more information for the pot.

If I add some extra rows to the csv file

DATETIME

2010-05-04 07:00

2010-05-04 07:00:00

2010-05-04 08:00:00

2010-05-04 07:10:10

These are read in as

2010-05-04 06:59:59.997

2010-05-04 06:59:59.997

2010-05-04 08:00:00.000

2010-05-04 07:10:10.000

Note the 2010-05-04...

Firstly, I must appologise for not including some sample data, while I have visited the site many times, I haven't asked many questions and really just didn't think about it...

Yes, one would think there should be a better solution however, Microsoft have not released 64-bit versions of ACE and their suggestion is to use SSIS when accessing external data....

My application has no problems connecting and using 64 SQL Server apart from the inability to use ACE drivers. My application created views which use OPENROWSET to access external...

Sorry, I must be having a bit of a thick moment.

Are you saying that the ACE drivers can be used in an openrowset query in a x64 installation of...

Hi there,

Thanks for you reply, however I'm pretty sure the ACE drivers are only 32 bit and won't load into a 64 bit installation of SQL. To...