SQL Express generate mismatched column aliases

[p***@gmail.com]

We are attempting to upgrade our application from MSDE 2000 to SQL
Server 2005 Express.

The installation occurs without error.

However, the database views in the database are messed up.

One view in the DB is define as:

CREATE view [dbo].[vwUser] AS SELECT * FROM OPENDATASOURCE
('Microsoft.Jet.OLEDB.4.0', 'Data Source="d:\folder\control.MDB";User
ID=USERID;Password=password;Jet OLEDB:System Database="c:\folder
\System.mda";Mode="Share Deny None"')...[User]

If we mount this DB in 2005 Express Management Studio and open the
same view in Design view, we get the following.

SELECT UserID AS ClassID, Password AS Custom, ClassID AS Disabled,
Custom AS MaxChannels, Disabled AS MinChannels, MinChannels AS
Password,
MaxChannels AS UserID
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\folder\control.MDB";User
ID=USERID;Password=password;Jet OLEDB:System Database="c:\folder
\System.mda";Mode="Share Deny None"'
)...[User]

The thing to notice is that in the SQL Express Design version, the
Column name alias' do not match.

UserID AS ClassID,
Password AS Custom,
ClassID AS Disabled,
Custom AS MaxChannels,
Disabled AS MinChannels,
MinChannels AS Password,
MaxChannels AS UserID

When you execute the view, the resultset shows the incorrect
column(alias) names. This breaks all sorts of things in our stored
procedures.

Can someone explain why this is happening?

[Hugo Kornelis]

On Mon, 17 Mar 2008 17:55:38 -0700 (PDT), ***@gmail.com wrote:

(snip)

Post by p***@gmail.com
CREATE view [dbo].[vwUser] AS SELECT * FROM OPENDATASOURCE
('Microsoft.Jet.OLEDB.4.0', 'Data Source="d:\folder\control.MDB";User
ID=USERID;Password=password;Jet OLEDB:System Database="c:\folder
\System.mda";Mode="Share Deny None"')...[User]

Hi potoole77,

This is your first mistake. You should never use SELECT * in serious
code (it is okay in some subqueries, but never in the outer query).

There are several reasons for this. One of them is that it may wreack
havoc to views defined with SELECT *. This is caused by the fact that
some metadata is stored with the view (like number of columns, and their
names). Now when the underlying table changes, the underlying table
doesn't change with it - this can cause all kinds of interesting issues
when you select data from the view!

Post by p***@gmail.com
If we mount this DB in 2005 Express Management Studio and open the
same view in Design view,

And that's your second mistake. Don't use the view designer. This is not
one of those "real developers write code" things; there are serious
reasons. One of them is that the view designer is severely limited in
what it can do. Another is that it contains several bugs.

Post by p***@gmail.com
Can someone explain why this is happening?

I *think* that the columns came back in a different order from when the
view was first created, so the column names stored in the metadata of
the view didn't match the actual column names returned now. The view
designer used aliases in an attempt to "correct" the differences.