There are the resulte of the test I performed :

(sql2005 dev edtn SP2 CU5)

set nocount on

-- is "Ad Hoc Distributed Queries" enabled ? if not, enable it

declare @SQLConfigOption varchar(256)

Create table #tmpConfigOption (configName varchar(128), MinValue varchar(15), MaxValue varchar(15), ConfigValue varchar(15), RunValue varchar(15))

Set @SQLConfigOption = 'sp_configure @configname = ''Ad Hoc Distributed Queries'''

insert into #tmpConfigOption

exec (@SQLConfigOption)

if exists (select * from #tmpConfigOption where configName = 'Ad Hoc Distributed Queries' and RunValue = '0' )

begin

exec sp_configure @configname = 'Ad Hoc Distributed Queries', @configvalue = '1' ;

RECONFIGURE WITH OVERRIDE;

End

go

/*

* Now the test : http://www.sqlservercentral.com/Forums/Topic449625-338-1.aspx#bm449892

*/

/*

-- content of Import_OpenRowset.csv

field1,field2

1,a

2,b

3,c

9223372036,10Pos

92233720368,11Pos

922337203685,12Pos

9223372036854,13Pos

92233720368547,14Pos

922337203685477,15Pos

9223372036854775,16Pos

922337203685477580,TheMaxBigInt

*/

SELECT cast(field1 as varchar(10)) as field1

, cast(field2 as varchar(15)) as field2

INTO dbo.file1table

FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=X:\;'

, 'SELECT * from X:\Test\Import_OpenRowset.csv')

/*

-- result

Msg 232, Level 16, State 2, Line 5

Arithmetic overflow error for type varchar, value = 9223372036.000000.

The statement has been terminated.

Msg 208, Level 16, State 1, Line 2

*/

go

SELECT cast(field1 as bigint) as field1

, cast(field2 as varchar(15)) as field2

INTO dbo.file1table2

FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=X:\;'

, 'SELECT * from X:\Test\Import_OpenRowset.csv')

go

Select * from dbo.file1table;

/*

Invalid object name 'dbo.file1table'.

*/

go

Select * from dbo.file1table2;

/*

field1 field2

-------------------- ---------------

1 a

2 b

3 c

9223372036 10Pos

92233720368 11Pos

922337203685 12Pos

9223372036854 13Pos

92233720368547 14Pos

922337203685477 15Pos

9223372036854774 16Pos

922337203685477632 TheMaxBigInt

*/

go

IF OBJECT_ID('dbo.file1table','U') IS NOT NULL

DROP TABLE dbo.file1table

GO

IF OBJECT_ID('dbo.file1table2','U') IS NOT NULL

DROP TABLE dbo.file1table2

GO

/*

* test as ended

*/

-- did we enable "Ad Hoc Distributed Queries" ? If yes, disable it

if exists (select * from #tmpConfigOption where configName = 'Ad Hoc Distributed Queries' and RunValue = '0' )

begin

exec sp_configure @configname = 'Ad Hoc Distributed Queries', @configvalue = '0' ;

RECONFIGURE WITH OVERRIDE;

End

go

drop table #tmpConfigOption

go