Creating New Views on SQL 2000

Recently we upgraded our server from SQL 7.0 to SQL 2000. I created a
new view in Enterprise Manager which pulls data from a linked server's
database. When I write the view...

CREATE VIEW dbo.VIEW1
AS
SELECT *
FROM LinkedServerName.DatabaseName.dbo.TableName

and I click on the run button it changes to:

CREATE VIEW dbo.VIEW1
AS
SELECT *
From LinkedServerName.DatabaseName.dbo.TableName TableName_1

I can still see the correct results, but the users can't- Once I
remove the TableName_1 they can see the results.

When I tried creating a view and did not use a linked server,
everything worked fine. Anyone have any answers?RJMAL (rjmal@.connecticare.com) writes:
> Recently we upgraded our server from SQL 7.0 to SQL 2000. I created a
> new view in Enterprise Manager which pulls data from a linked server's
> database. When I write the view...
> CREATE VIEW dbo.VIEW1
> AS
> SELECT *
> FROM LinkedServerName.DatabaseName.dbo.TableName
> and I click on the run button it changes to:
> CREATE VIEW dbo.VIEW1
> AS
> SELECT *
> From LinkedServerName.DatabaseName.dbo.TableName TableName_1
> I can still see the correct results, but the users can't- Once I
> remove the TableName_1 they can see the results.
> When I tried creating a view and did not use a linked server,
> everything worked fine. Anyone have any answers?

I was not able to reproduces this, because I was not even able to create
a view that accesses a linked server through Enterprise Manager. I came
as far that I saw the added alias, though.

Really why Enterprise Manager adds the alias, I don't know, but neither
do I understand why the alias would affect the common users. You say
they don't see the correct results - but what do they see? An error
message? No rows at all? Too few rows? Garbled data? From what sort of
application, to the users access the view?

Anyway, try using Query Analyzer to create the view instead. In my
opinion, Enterprise Manager plays too much behind your back to be
trustworthy.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp