Linked SQL 2000 server and views

I only have basic SQL knowledge and am trying to modify some previously
created views, the problem I have is the views were originally only accessing
tables on the local SQL 2000 server which was fine, but now one of the
databases has been moved to another server and I'm trying to modify the views
to collect data from remote tables although still SQL 2000.
I have added the other server as linked server used a username with a
default database as the one I need to access, so I can view the tables on the
linked server.
From some stuff I read it looked as though I could create view specifying a
remote field by
servername.databasename.owner.table.field but this doesn't work it just says
I can only have 3 prefixes.
Can someone tell how I can do this,
Thanks.
You certainly can create a view referencing a linked server; BOL has an
example in the discussion about partitioned views (under the topic "create
view"). I suspect that there is an error in the syntax you are using to
create / alter the view. Post the actual statement you are using to create
the view and the complete error message; I'm sure someone will be able to
identify the problem.
"Marcus" <Marcus@.discussions.microsoft.com> wrote in message
news:7E3420EF-12AB-47F5-931B-02F045D61DF5@.microsoft.com...
> I only have basic SQL knowledge and am trying to modify some previously
> created views, the problem I have is the views were originally only
accessing
> tables on the local SQL 2000 server which was fine, but now one of the
> databases has been moved to another server and I'm trying to modify the
views
> to collect data from remote tables although still SQL 2000.
> I have added the other server as linked server used a username with a
> default database as the one I need to access, so I can view the tables on
the
> linked server.
> From some stuff I read it looked as though I could create view specifying
a
> remote field by
> servername.databasename.owner.table.field but this doesn't work it just
says
> I can only have 3 prefixes.
> Can someone tell how I can do this,
> Thanks.
|||This is the original view statement I have for getting the data from the
database Landesk_Dmart. I am trying to change it to get data from a remote
server hlflvlandesk8 from a database called landesk8. I have added
hlflvlandesk8 as a linked database with a username and password that defaults
to the landesk8 database. The owner of the table is ld8admin.
I tried referencing by using hlflvlandesk8.landesk8.ld8admin.Computer.<field
name>
which doesn't work.
What do I need to do?
CREATE VIEW dbo.CompSystem_Comp
AS
SELECT Landesk_Dmart.Landesk.Computer.[Computer_Idn],
Landesk_Dmart.Landesk.Computer.DeviceName,
Landesk_Dmart.Landesk.Computer.LoginName,
Landesk_Dmart.Landesk.Computer.Model,
Landesk_Dmart.Landesk.Computer.Manufacturer,
Landesk_Dmart.Landesk.CompSystem.AssetTag,
Landesk_Dmart.Landesk.Computer.Type,
'LastScan' = convert(varchar(12),dateadd(second,
convert(int,HWLastScanDate)-14400,'01/01/1970'), 101)
FROM
Landesk_Dmart.Landesk.Computer
INNER JOIN
Landesk_Dmart.Landesk.CompSystem ON
Landesk_Dmart.Landesk.Computer.Computer_Idn =
Landesk_dmart.Landesk.CompSystem.Computer_Idn
|||Just off the top of my head and without any testing. Try giving an alias
to your tables and using the alias to qualify the columns. Second, I assume
that you are using QA or some similar tool to create the view - in other
words, don't try to create the view using EM and its view editor.
"Marcus Bentley" <MarcusBentley@.discussions.microsoft.com> wrote in message
news:8ECBA77F-70EA-4E01-AF5B-72D7248A7F81@.microsoft.com...
> This is the original view statement I have for getting the data from the
> database Landesk_Dmart. I am trying to change it to get data from a remote
> server hlflvlandesk8 from a database called landesk8. I have added
> hlflvlandesk8 as a linked database with a username and password that
defaults
> to the landesk8 database. The owner of the table is ld8admin.
> I tried referencing by using
hlflvlandesk8.landesk8.ld8admin.Computer.<field
> name>
> which doesn't work.
> What do I need to do?
> CREATE VIEW dbo.CompSystem_Comp
> AS
> SELECT Landesk_Dmart.Landesk.Computer.[Computer_Idn],
> Landesk_Dmart.Landesk.Computer.DeviceName,
> Landesk_Dmart.Landesk.Computer.LoginName,
> Landesk_Dmart.Landesk.Computer.Model,
> Landesk_Dmart.Landesk.Computer.Manufacturer,
> Landesk_Dmart.Landesk.CompSystem.AssetTag,
> Landesk_Dmart.Landesk.Computer.Type,
> 'LastScan' = convert(varchar(12),dateadd(second,
> convert(int,HWLastScanDate)-14400,'01/01/1970'), 101)
> FROM
> Landesk_Dmart.Landesk.Computer
> INNER JOIN
> Landesk_Dmart.Landesk.CompSystem ON
> Landesk_Dmart.Landesk.Computer.Computer_Idn =
> Landesk_dmart.Landesk.CompSystem.Computer_Idn