How to view a table from another instance?

Hi,
on our server we have a default SQL 2000 instance and also another MSDE
instance with the name server\instance1.
In one of my database of the default instance I would like to create a view
to the table from the another instance. I have tried with select statement
like
SELECT * FROM server\instance1.database1.dbo.table1
but with no success (parsing problem :-) ).
Next I have tried to make a linked server but I din't succees also. How to
establish a linked server to another instance of the same server? Can
anybody help me?
IvanYou will either have to use a linked server, then you could use the
four part notation like SELECT * from
LinkedServername.Database.Owner.Objectname or you use the
OPENDATASOURCE method which can take a connection string for
establishing a connection to the other server.
HTH, Jens Suessmeyer
http://www.sqlserver2005.de
--|||Hi,
As you mentioned to access the data in a different instance you need to
create a Linked server.
Take a look into my article on creating Linked servers:-
http://www.microsoft.com/India/msdn/articles/166.aspx
Thanks
Hari
SQL Server MVP
"Ivan" <ivan@.nekje.si> wrote in message
news:u2Ri%23BB0GHA.5072@.TK2MSFTNGP03.phx.gbl...
> Hi,
> on our server we have a default SQL 2000 instance and also another MSDE
> instance with the name server\instance1.
> In one of my database of the default instance I would like to create a
> view to the table from the another instance. I have tried with select
> statement like
> SELECT * FROM server\instance1.database1.dbo.table1
> but with no success (parsing problem :-) ).
> Next I have tried to make a linked server but I din't succees also. How to
> establish a linked server to another instance of the same server? Can
> anybody help me?
> Ivan
>
>|||As others have indicated, first you need to create a linked server. Then, si
nce the instance name has a 'prohibited' character, the 'backslash' [\],
you will need to put the instance name in square brackets when you use it i
n a query.
SELECT
Column1
, Column2
, etc.
FROM [server\instance1].database1.dbo.table1
Also, please consider NOT using [ SELECT * ] and instead, using a named
list of columns. Not only is it a 'Best Practice', it will enable your code
to be more robust.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Ivan" <ivan@.nekje.si> wrote in message news:u2Ri%23BB0GHA.5072@.TK2MSFTNGP03.phx.gbl...[vbco
l=seagreen]
> Hi,
>
> on our server we have a default SQL 2000 instance and also another MSDE
> instance with the name server\instance1.
>
> In one of my database of the default instance I would like to create a vie
w
> to the table from the another instance. I have tried with select statement
> like
> SELECT * FROM server\instance1.database1.dbo.table1
> but with no success (parsing problem :-) ).
>
> Next I have tried to make a linked server but I din't succees also. How to
> establish a linked server to another instance of the same server? Can
> anybody help me?
>
> Ivan
>
>
>[/vbcol]|||Hi. There's no need to establish the server as "linked"
Look here
First a query against the second instance named ui0204\no2:
SELECT * from [ui0204\no2].bookshopdb.dbo.employees
RESPONSE:
Server: Msg 7411, Level 16, State 1, Line 1
Server 'ui0204\no2' is not configured for DATA ACCESS
Then we need to enable ui0204\no2 for data access
sp_serveroption @.server=[ui0204\no2], @.optname='data access', @.optvalue=
'TRUE'
RESPONSE:
The command(s) completed successfully.
Try the query again
SELECT * from [ui0204\no2].bookshopdb.dbo.employees
RESPONSE:
(13 row(s) affected)
That's how it's done.
gl
tt
"Arnie Rowland" wrote:
[vbcol=seagreen]
> As others have indicated, first you need to create a linked server. Then,
since the instance name has a 'prohibited' character, the 'backslash' [\
], you will need to put the instance name in square brackets when you use it
in a query.
> SELECT
> Column1
> , Column2
> , etc.
> FROM [server\instance1].database1.dbo.table1
> Also, please consider NOT using [ SELECT * ] and instead, using a name
d list of columns. Not only is it a 'Best Practice', it will enable your cod
e to be more robust.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Ivan" <ivan@.nekje.si> wrote in message news:u2Ri%23BB0GHA.5072@.TK2MSFTNGP
03.phx.gbl...