Problem with linked server

Here's the situation: My production environment has two SQL Servers 2000,
call them S1 and S2. S1 also hosts IIS and my ASP.NET application.
On S1, I have created a linked server for S2. Thus, on S1, I can write:
SELECT * FROM S2.myDB.dbo.myTable
and everything is happy.
My development environment is slightly different. I have a single machine
running a single instance of SQL Server 2000 (acually, MSDE). I have made
local copies of the tables from both S1 and S2 in my local database. In
order to be able to copy, without modification, the stored procedures I
write on my development machine, I need to be able to access the tables in
my database as either:
myDB.dbo.myTable
OR
S2.myDB.dbo.myTable
To this end, I created a linked database on my development machine, but
instead of selecting type SQL Server (which forces the linked server name
and the hostname to be the same), I selected Microsoft OLE DB provider for
SQL Server (which allows specifying different hostname and data source
names). I specified my hostname as the data source and named the linked
server S2. SQL server dutifully made the linked server. Note that I have
confirmed the creation of the linked server by running sp_helpserver, which
does show both the local server (identified by the machine name) and the
linked server (identified by the assigned name) [but see more info below].
Despite this seeming success, I have been unable to do anything with this
linked server. Under the security tab, I selected 'Be made using this
security context', and entered 'sa' and its password. I then ran the query:
SELECT * FROM S2.someDB.dbo.myTable
and received: SQL Server does not exist or access denied.
Some additional information:
1- On the development machine, sqlservr runs under the SYSTEM context
2- Looking in the table master.dbo.sysservers, I saw the following
srvname datasource srvnetname
S1 S1 S1
S2 S2 S2
when running the query, it takes a while to respond, thus I am thinking that
it is searching the net for a server named S2 instead of using itself as the
datasource. If this is so, what can I do differently to resolve this
problem?
--
Gerry Roston
Pair of Docs Consulting
gerry@.pairofdocs.netYou could try adding a host entry for the linked server
with the same ip address as the development server using a
different name.
i.e.
192.168.1.1 development
192.168.1.1 Dev_Link
This will allow you to add the linked server as another
sql server.
Shane
>--Original Message--
>Here's the situation: My production environment has two
SQL Servers 2000,
>call them S1 and S2. S1 also hosts IIS and my ASP.NET
application.
>On S1, I have created a linked server for S2. Thus, on
S1, I can write:
> SELECT * FROM S2.myDB.dbo.myTable
>and everything is happy.
>My development environment is slightly different. I have
a single machine
>running a single instance of SQL Server 2000 (acually,
MSDE). I have made
>local copies of the tables from both S1 and S2 in my
local database. In
>order to be able to copy, without modification, the
stored procedures I
>write on my development machine, I need to be able to
access the tables in
>my database as either:
>myDB.dbo.myTable
>OR
>S2.myDB.dbo.myTable
>To this end, I created a linked database on my
development machine, but
>instead of selecting type SQL Server (which forces the
linked server name
>and the hostname to be the same), I selected Microsoft
OLE DB provider for
>SQL Server (which allows specifying different hostname
and data source
>names). I specified my hostname as the data source and
named the linked
>server S2. SQL server dutifully made the linked server.
Note that I have
>confirmed the creation of the linked server by running
sp_helpserver, which
>does show both the local server (identified by the
machine name) and the
>linked server (identified by the assigned name) [but see
more info below].
>Despite this seeming success, I have been unable to do
anything with this
>linked server. Under the security tab, I selected 'Be
made using this
>security context', and entered 'sa' and its password. I
then ran the query:
>SELECT * FROM S2.someDB.dbo.myTable
>and received: SQL Server does not exist or access denied.
>Some additional information:
>1- On the development machine, sqlservr runs under the
SYSTEM context
>2- Looking in the table master.dbo.sysservers, I saw the
following
>srvname datasource srvnetname
>S1 S1 S1
>S2 S2 S2
>when running the query, it takes a while to respond, thus
I am thinking that
>it is searching the net for a server named S2 instead of
using itself as the
>datasource. If this is so, what can I do differently to
resolve this
>problem?
>--
>Gerry Roston
>Pair of Docs Consulting
>gerry@.pairofdocs.net
>
>.
>|||Gerald,
>what can I do differently to resolve this problem?
Isn't a second instance for the dev box the most logical, desirable
solution?
"Gerald Roston" <groston@.annarbormachine.com> wrote in message
news:Ovk9A606DHA.3420@.TK2MSFTNGP11.phx.gbl...
> Here's the situation: My production environment has two SQL Servers 2000,
> call them S1 and S2. S1 also hosts IIS and my ASP.NET application.
> On S1, I have created a linked server for S2. Thus, on S1, I can write:
> SELECT * FROM S2.myDB.dbo.myTable
> and everything is happy.
> My development environment is slightly different. I have a single machine
> running a single instance of SQL Server 2000 (acually, MSDE). I have made
> local copies of the tables from both S1 and S2 in my local database. In
> order to be able to copy, without modification, the stored procedures I
> write on my development machine, I need to be able to access the tables in
> my database as either:
> myDB.dbo.myTable
> OR
> S2.myDB.dbo.myTable
> To this end, I created a linked database on my development machine, but
> instead of selecting type SQL Server (which forces the linked server name
> and the hostname to be the same), I selected Microsoft OLE DB provider for
> SQL Server (which allows specifying different hostname and data source
> names). I specified my hostname as the data source and named the linked
> server S2. SQL server dutifully made the linked server. Note that I have
> confirmed the creation of the linked server by running sp_helpserver,
which
> does show both the local server (identified by the machine name) and the
> linked server (identified by the assigned name) [but see more info below].
> Despite this seeming success, I have been unable to do anything with this
> linked server. Under the security tab, I selected 'Be made using this
> security context', and entered 'sa' and its password. I then ran the
query:
> SELECT * FROM S2.someDB.dbo.myTable
> and received: SQL Server does not exist or access denied.
> Some additional information:
> 1- On the development machine, sqlservr runs under the SYSTEM context
> 2- Looking in the table master.dbo.sysservers, I saw the following
> srvname datasource srvnetname
> S1 S1 S1
> S2 S2 S2
> when running the query, it takes a while to respond, thus I am thinking
that
> it is searching the net for a server named S2 instead of using itself as
the
> datasource. If this is so, what can I do differently to resolve this
> problem?
> --
> Gerry Roston
> Pair of Docs Consulting
> gerry@.pairofdocs.net
>|||Problem resolved by using sp_addlinkedserver instead of Enterprise Manager.
I suspect that the problem is due to a bug in the documentation and a bug in
Enterprise Manager. Reviewing the document shows that the string 'SQL
Server' can be used as the product_name along with SQLOLEDB as the
provider_name. Trying this with sp_addlinkedserver yields the error message:
You cannot specify a provider or any properties for product 'SQL Server'.
but doing the same from Enterprise Manager results in success, even though
the entry in master.dbo.sysservers is incorrect.
"Gerald Roston" <groston@.annarbormachine.com> wrote in message
news:Ovk9A606DHA.3420@.TK2MSFTNGP11.phx.gbl...
> Here's the situation: My production environment has two SQL Servers 2000,
> call them S1 and S2. S1 also hosts IIS and my ASP.NET application.
> On S1, I have created a linked server for S2. Thus, on S1, I can write:
> SELECT * FROM S2.myDB.dbo.myTable
> and everything is happy.
> My development environment is slightly different. I have a single machine
> running a single instance of SQL Server 2000 (acually, MSDE). I have made
> local copies of the tables from both S1 and S2 in my local database. In
> order to be able to copy, without modification, the stored procedures I
> write on my development machine, I need to be able to access the tables in
> my database as either:
> myDB.dbo.myTable
> OR
> S2.myDB.dbo.myTable
> To this end, I created a linked database on my development machine, but
> instead of selecting type SQL Server (which forces the linked server name
> and the hostname to be the same), I selected Microsoft OLE DB provider for
> SQL Server (which allows specifying different hostname and data source
> names). I specified my hostname as the data source and named the linked
> server S2. SQL server dutifully made the linked server. Note that I have
> confirmed the creation of the linked server by running sp_helpserver,
which
> does show both the local server (identified by the machine name) and the
> linked server (identified by the assigned name) [but see more info below].
> Despite this seeming success, I have been unable to do anything with this
> linked server. Under the security tab, I selected 'Be made using this
> security context', and entered 'sa' and its password. I then ran the
query:
> SELECT * FROM S2.someDB.dbo.myTable
> and received: SQL Server does not exist or access denied.
> Some additional information:
> 1- On the development machine, sqlservr runs under the SYSTEM context
> 2- Looking in the table master.dbo.sysservers, I saw the following
> srvname datasource srvnetname
> S1 S1 S1
> S2 S2 S2
> when running the query, it takes a while to respond, thus I am thinking
that
> it is searching the net for a server named S2 instead of using itself as
the
> datasource. If this is so, what can I do differently to resolve this
> problem?
> --
> Gerry Roston
> Pair of Docs Consulting
> gerry@.pairofdocs.net
>