MS SQL Server – Error – Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET.

Earlier today, a business user wanted to know why a 4 GB text file was not properly imported into a MS SQL Server Table.

I went by and reviewed the code she was using:

IF EXISTS (
SELECT *
FROM sysobjects
WHERE [name]= ‘table_v3′
AND OBJECTPROPERTY(id, N’IsUserTable’) = 1
)
BEGIN
DROP TABLE table_v3
END

select *
into table_v3
from txtsrvDBA…[countries#csv]

I reviewed the Linked Server (txtsrvDBA) and saw that it was actually accessing a text file.  The reverse engineeed syntax of the Linked Server is pasted below:

EXEC master.dbo.sp_addlinkedserver
@server = N’txtsrvDBA’
, @srvproduct=N’Jet 4.0′
, @provider=N’Microsoft.Jet.OLEDB.4.0′
, @datasrc=N’E:\SharedDBA’
, @provstr=N’Text’
On her desktop, it appeared to have worked; save for the fact that she thinks the number of records imported is less than it should have being.

But, on the server it constantly, repeatedly, and annonying kept saying

Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. The column name…blanked out” is a duplicate.

After wasting a hour or two, finally gave up and googled the error message.

The problem appears to be tried to cases where text files have missing header rows or where header rows have duplicate column names.

MS Solution is to create a so called schema file and in that file give more concise information as to whether header rows are present in the data file.  You may also
list the colum names.

The problem is described a bit more fully in http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/5cde68f1-01b1-4f48-af91-b064e4288f98

And, the format of the schema file is detailed in

http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx

Things to note about the schema file:

  1. Should be located in the same directory as data file to be imported
  2. Should be named schema.ini

Sample Schema file:

[COUNTRIES.txt]
ColNameHeader=False
Format=CSVDelimited
MaxScanRows=0
CharacterSet=ANSI

 

Sample Query [using Linked Server]

select *
from OpenRowset(‘MSDASQL’
,’Driver={Microsoft Text Driver (*.txt; *.csv)}
; DefaultDir=E:\SharedDBA
;Extended properties=”ColNameHeader=False;Format=pipeDelimited;”’
,’select count(*) from countries.csv’
)

And, somehow the problem depends on the client — Unforgivable it works when queried from Query Analyzer (MS SQL Server 2000), but fails in Management Studio (MS SQL Server 2005).

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s