I tried something like below but it didn't work...

Create View  NameOfView

as

Select Data

from

ServerName.DatabaseName.dbo.Table

--
Brian Conner

Message posted via http://www.sqlmonster.com

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"Brian Conner via SQLMonster.com" <u47161@uwe> wrote in message
news:9eaa01a6d7fc1@uwe...

Please always say what "it didn't work" means.  Did you get an error
message?  What was it?  Etc.  (And what version of SQL Server are you
running?)

Anyway, your syntax for creating a cross-server view is fine.  I imagine
that you probably have not defined a linked server for the view to use.
Linked servers must be defined to support this cross-server view.  (Why?
Just think of it as the server running the query, not you personally running
the query.  The SQL Server needs to understand what is going on from
information defined within the SQL Server.)  Here is an example:

-- Defines the linked server (the "servername" in the 4 part name.)
EXEC master.dbo.sp_addlinkedserver @server = N'ServerName',
 @srvproduct=N'Linked Server', @provider=N'SQLNCLI',
 @datasrc=N'ServerName', @catalog=N'DatabaseName'

-- This sets up the linked server to use your personal permissions.
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'ServerName',@useself=N'True',
  @locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

After that, you can create your view:

Create View  NameOfView
as
Select Data
from
ServerName.DatabaseName.dbo.Table

Note that the linked server name does not have to be the same as the name of
the physical server.  The data source, of course, needs the name of the
server.

RLF

"Brian Conner via SQLMonster.com" <u47161@uwe> wrote in message
news:9eaa01a6d7fc1@uwe...