SQLTeam.com | Weblogs | Forums

Silent truncation using OpenRowSet to Bulk Load data

I‌‌ have to load CSV data into SQL server table using OpenRowSet.
‌ I have installed the AccessDatabaseEngine_X64.exe, Access and SQL server both are 64-bit.
Also enabled below settings,
sp_configure 'show advanced options', 1
reconfigure with override
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure with override

,'Driver={Microsoft Access Text Driver (.txt,.csv)}','select * fromD:\MYDATA\go\test.CSV')

D‌ata was also getting loaded into the table, but some of my rows in CSV have data more than what is defined in the schema of the table (Test) and I don't want to change column size in the table(Test) So SQL started giving errors related to truncation. Without using OpenRowSet earlier I used to use "Set ANSI WARNINGS OFF" to do silent truncation. But with OpenRowSet if I use this command then it gives below error

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.‌‌

I want to use OpenRowSet because it is very fast. So can anyone please help me on how I can do silent truncation using OpenRowSet.

Try using OpenRowset(BULK
It's faster then the method you're using and you can create a view against your file with substring on each column with the length of the target table.

Thanks for the prompt reply. But creating views from csv will be time consuming. because 1st I will have to create views from csv which will load data into target table. 2nd adding substring for each column in the view will be again a pain because there will be more than 50 target tables each having minimum 300 columns.

So write a script to dynamically generate the views based on the column lengths.
You could map the tables to files in a table.