OPENROWSET

Question

OPENROWSET

VastSQL

SSC-Dedicated

Points: 36121
More actions
August 4, 2008 at 1:29 am

#122795

Hi EXperts,
Am getting the error
Msg 492, Level 16, State 1, Line 2
Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. The column name "NoName" is a duplicate.
when i execute a select into statement from a openrowset .
Please help
TIA

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

Ratheesh.K.Nair (8/4/2008)
Hi EXperts,
Am getting the error
Msg 492, Level 16, State 1, Line 2
Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. The column name "NoName" is a duplicate.
when i execute a select into statement from a openrowset .
Please help
TIA

You should modify the statement you are using in the openrowset, and add unique aliases to the columns if it is possible.

For example the following will fail with a similar error message:

SELECT *

FROM OPENROWSET('SQLNCLI', 'Server=dev_andras;Trusted_Connection=yes;',

'SELECT 1 as a,1 as a,1 as a')

but can be fixed by changing the aliases:

SELECT *

FROM OPENROWSET('SQLNCLI', 'Server=dev_andras;Trusted_Connection=yes;',

'SELECT 1 as a,1 as b,1 as c')

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 2

Hi Andras,

THANK YOU VERY MUCH FOR THE REPLY

Now my problem is that i want to convert a CSV file to XML or into a temp table (cant use any tool).I want to automate this process atleast i want to call the same in an SP

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 3

Ratheesh.K.Nair (8/4/2008)
Hi Andras,
THANK YOU VERY MUCH FOR THE REPLY
Now my problem is that i want to convert a CSV file to XML or into a temp table (cant use any tool).I want to automate this process atleast i want to call the same in an SP

You can use the Microsoft Text Driver to read CSV, and then can insert the result into a temp table. For example:

SELECT x.A into #foo

FROM OPENROWSET('MSDASQL',

'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\;',

'select A from foo.csv') as x

Then you can use select "for xml" to create a nice xml from this temp table

e.g.

select * from #foo for xml path

You can read more on the xml output on http://msdn.microsoft.com/en-us/library/ms178107.aspx

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 4

Hi ,

I did the same but the problem is that the csv i have is extracted from peoplesoft database and contains many null values in the begining and some other texts .when i opens the file the text in the begining are shown as column name 🙁