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.netGerald,

quote:

---

>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...

quote:

---

> 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

quote:

---

> 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:

quote:

---

> 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

quote:

---

> it is searching the net for a server named S2 instead of using itself as

---

the

quote:

---

> 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...

quote:

---

> 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

quote:

---

> 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:

quote:

---

> 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

quote:

---

> it is searching the net for a server named S2 instead of using itself as

---

the

quote:

---

> datasource. If this is so, what can I do differently to resolve this
> problem?
> --
> Gerry Roston
> Pair of Docs Consulting
> gerry@.pairofdocs.net
>

---

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
>

Problem with leading digit in servername?

I have run into trouble since I moved my developmentcode into a production-server.

The production server has a name starting with a digit (i.e. '123server').

The problem occurs when I try to comunicate to another database on the same server, like this:

INSERT INTO 123server.databaseName.tableName VALUES(...)

This gives me the error message "Incorrect syntax near '123'".

Running the exact same code on a server named myServer (without leading digits) works great!

I know that in this case I could change the code into

INSERT INTO databaseName.tableName VALUES(...)

but in the real code, the database server is retrieved from a variable and could or could not be the same server.

Is this a known bug or am I doing something wrong?

regards Andreas Brosten

Try quoting the server name with brackets, like [servername].[databasename]....

When you are doing something like retrieving the name, you should always use [] around them, because you have no idea what could be returned.

|||Oh, of course. I feel like such an idiot!
Thanx pal!

Problem with job

Hi there,

Actually I'm trying to replicate a production database to a development server using merge publication. (I'm using Microsoft SQL Server 2005 - 9.00.2047.00).

When I created the publication job (automatically when creating the publication) I specified to get executed every 15 minutes, and later I created a subscription (push subscription) I specified it to execute continously. So I made some chenges on the data on the production server, and they get replicated every minute aprox. , and not in 15 min intervals as I defined before.... When I go to the server agent I can see the job for publication and It is supposed to be executing the publication every 15 min as I defined but this doesn't happen !!!

Is this a Bug ? Is there any fix ?

Thanks !

The subscription job, which you set to run continuously, is what's moving changes between publisher and subscriber. This runs/polls every 60 sec, which is why you're seeing changes replicated every minute. THis behavior you're seeing is by design and expected. If you want it to replicate every 15 minutes, then change the schedule for this subscription job.

I'm not sure what a "publication" job is, maybe you're referring to the snapshot agent job? For merge replication, its typical to schedule the snapshot agent to run maybe once a day, but not every 15 minutes. You may want to change it.

So no it's not a bug

|||

I've got my problem solved !!! Thank you so much !

Greg Y wrote:

The subscription job, which you set to run continuously, is what's moving changes between publisher and subscriber. This runs/polls every 60 sec, which is why you're seeing changes replicated every minute. THis behavior you're seeing is by design and expected. If you want it to replicate every 15 minutes, then change the schedule for this subscription job.

I'm not sure what a "publication" job is, maybe you're referring to the snapshot agent job? For merge replication, its typical to schedule the snapshot agent to run maybe once a day, but not every 15 minutes. You may want to change it.

So no it's not a bug

sql