Does replication setup make some entry in the Remote servers setti

[Wingman]

If I create a transactional replication - Push method in SQL 2K standard with
sp4, will replication create entries in the Remote Servers setting in EM?
Currently I found three entries in the Remote Servers listing, server name
for the publishing server name, server name for the subscriber server, and
repl_distrbutor.

If the replication does make these entries, I have no problem with the
repl_distributor being there. But I have problem with the subscriber server
name being there because I want to create a Linked Server with that same name
but I can't since the name is already in use by the remote servers.

Please let me know which one or all of the above names are legit if indeed
repliation put them in there to begin with.

Say if the above entries need to be there, how I should move them to the
Linked Servers setting because the remote servers feature is for backward
compatible reason and linked servers is more dynamic and is the one to
replace it.

Thanks in advance.

Wingman

[Geoff N. Hiten]

Remote Servers and Linked Servers are all stored in the sysservers table in
the master database. Different flag values determine what an entry actually
represents. There is even a special entry for the local server name. If
you want or need to use a replication participating server as a linked
server, there is a special work-around to enable that functionality.

PRB: Adding a Linked Server Causes Error 15028
http://support.microsoft.com/kb/274098/en-us
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

Post by Wingman
If I create a transactional replication - Push method in SQL 2K standard with
sp4, will replication create entries in the Remote Servers setting in EM?
Currently I found three entries in the Remote Servers listing, server name
for the publishing server name, server name for the subscriber server, and
repl_distrbutor.
If the replication does make these entries, I have no problem with the
repl_distributor being there. But I have problem with the subscriber server
name being there because I want to create a Linked Server with that same name
but I can't since the name is already in use by the remote servers.
Please let me know which one or all of the above names are legit if indeed
repliation put them in there to begin with.
Say if the above entries need to be there, how I should move them to the
Linked Servers setting because the remote servers feature is for backward
compatible reason and linked servers is more dynamic and is the one to
replace it.
Thanks in advance.
Wingman

[Wingman]

Ok, I tried that stored procedure and the server name now shows up in both
linked server and remote servers. And I still can't execute the remote
stored procedure. Here is my observation after reading the link. It said
the status for a remote server of a subscriber supposedly a 69. In order for
it to become a linked server of a subscriber it needs to be 229. Before I
executed the stored procedure, the server name that I want to change its
status is 1093 not 69. After the execution, it added 160 to it to become
1253. Is the 1253 status correct? The initial status number 1093 is not 69,
is it because there are other settings added values to it?

Here are my questions:
1) is it correct that the server name will show up both in the linked
servers and remote servers in EM?

2) Is the 1253 new status correct?

3) the link didn't say I need to do a service restart, what do you think? I
am getting this error when I execute a remote stored procedure:
"Could not execute procedure on remote server 'server1' because SQL Server
is not configured for remote access. Ask your system administrator to
reconfigure SQL Server to allow remote access."

Also, when I try to double click on the 'table' of the linked server, it
said the server doesn't exist or access denied. The SQL login name I
entered in the Linked server properties has the sys admin right and this
account exists in both servers.


Wingman

Post by Wingman
If I create a transactional replication - Push method in SQL 2K standard with
sp4, will replication create entries in the Remote Servers setting in EM?
Currently I found three entries in the Remote Servers listing, server name
for the publishing server name, server name for the subscriber server, and
repl_distrbutor.
If the replication does make these entries, I have no problem with the
repl_distributor being there. But I have problem with the subscriber server
name being there because I want to create a Linked Server with that same name
but I can't since the name is already in use by the remote servers.
Please let me know which one or all of the above names are legit if indeed
repliation put them in there to begin with.
Say if the above entries need to be there, how I should move them to the
Linked Servers setting because the remote servers feature is for backward
compatible reason and linked servers is more dynamic and is the one to
replace it.
Thanks in advance.
Wingman

[Geoff N. Hiten]

Yes, it is correct to see the servers listed in more than one place. The
values you refer to are bit flags held in an integer field. Addition is not
the correct operation, logical OR is the correct operation.

You need to enable each server for remote access, just like it says. Login
to each remote SQL server and execute the following:

exec sp_configure 'remote access', 1
go
reconfigure with override
go

This tells the remote server to accept remote queries.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

Post by Wingman
Ok, I tried that stored procedure and the server name now shows up in both
linked server and remote servers. And I still can't execute the remote
stored procedure. Here is my observation after reading the link. It said
the status for a remote server of a subscriber supposedly a 69. In order for
it to become a linked server of a subscriber it needs to be 229. Before I
executed the stored procedure, the server name that I want to change its
status is 1093 not 69. After the execution, it added 160 to it to become
1253. Is the 1253 status correct? The initial status number 1093 is not 69,
is it because there are other settings added values to it?
1) is it correct that the server name will show up both in the linked
servers and remote servers in EM?
2) Is the 1253 new status correct?
3) the link didn't say I need to do a service restart, what do you think?
I
"Could not execute procedure on remote server 'server1' because SQL Server
is not configured for remote access. Ask your system administrator to
reconfigure SQL Server to allow remote access."
Also, when I try to double click on the 'table' of the linked server, it
said the server doesn't exist or access denied. The SQL login name I
entered in the Linked server properties has the sys admin right and this
account exists in both servers.
Wingman

Post by Wingman
If I create a transactional replication - Push method in SQL 2K standard with
sp4, will replication create entries in the Remote Servers setting in EM?
Currently I found three entries in the Remote Servers listing, server name
for the publishing server name, server name for the subscriber server, and
repl_distrbutor.
If the replication does make these entries, I have no problem with the
repl_distributor being there. But I have problem with the subscriber server
name being there because I want to create a Linked Server with that same name
but I can't since the name is already in use by the remote servers.
Please let me know which one or all of the above names are legit if indeed
repliation put them in there to begin with.
Say if the above entries need to be there, how I should move them to the
Linked Servers setting because the remote servers feature is for backward
compatible reason and linked servers is more dynamic and is the one to
replace it.
Thanks in advance.
Wingman

[Wingman]

Ok, let me define some names first. We have two SQL servers. Server A is
the publishing server which I try to add a linked server into. Server B is
the subscriber server which is the linked server that ServerA tries to add. I
checked the sysservers table in the server A's master database and it has a
server B record in the table, which is what we want. This record also show
value 1 under both 'isremote' and 'dataaccess'. If I guess this right, they
mean the linked server B can be remoted in. Does this accomplish what you
ask me to do in your previous reply?

Secondly, I can issue a select statement from server A to retrieve data from
a table in server B but using the same SQL login I can't call the remote
stored procedure in server B from server A in query anayzer. What do you
think about this problem?

The Link didn't tell me to set the remote flag in both servers. Actually,
by running the script in the link, it set the remote flag to 1 already but
only to the sysservers table in server A. Do you still think I need to do
that in server B?

Wingman

Post by Geoff N. Hiten
Yes, it is correct to see the servers listed in more than one place. The
values you refer to are bit flags held in an integer field. Addition is not
the correct operation, logical OR is the correct operation.
You need to enable each server for remote access, just like it says. Login
exec sp_configure 'remote access', 1
go
reconfigure with override
go
This tells the remote server to accept remote queries.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

Post by Wingman
Ok, I tried that stored procedure and the server name now shows up in both
linked server and remote servers. And I still can't execute the remote
stored procedure. Here is my observation after reading the link. It said
the status for a remote server of a subscriber supposedly a 69. In order for
it to become a linked server of a subscriber it needs to be 229. Before I
executed the stored procedure, the server name that I want to change its
status is 1093 not 69. After the execution, it added 160 to it to become
1253. Is the 1253 status correct? The initial status number 1093 is not 69,
is it because there are other settings added values to it?
1) is it correct that the server name will show up both in the linked
servers and remote servers in EM?
2) Is the 1253 new status correct?
3) the link didn't say I need to do a service restart, what do you think?
I
"Could not execute procedure on remote server 'server1' because SQL Server
is not configured for remote access. Ask your system administrator to
reconfigure SQL Server to allow remote access."
Also, when I try to double click on the 'table' of the linked server, it
said the server doesn't exist or access denied. The SQL login name I
entered in the Linked server properties has the sys admin right and this
account exists in both servers.
Wingman

Post by Wingman
If I create a transactional replication - Push method in SQL 2K standard with
sp4, will replication create entries in the Remote Servers setting in EM?
Currently I found three entries in the Remote Servers listing, server name
for the publishing server name, server name for the subscriber server, and
repl_distrbutor.
If the replication does make these entries, I have no problem with the
repl_distributor being there. But I have problem with the subscriber server
name being there because I want to create a Linked Server with that same name
but I can't since the name is already in use by the remote servers.
Please let me know which one or all of the above names are legit if indeed
repliation put them in there to begin with.
Say if the above entries need to be there, how I should move them to the
Linked Servers setting because the remote servers feature is for backward
compatible reason and linked servers is more dynamic and is the one to
replace it.
Thanks in advance.
Wingman

[Geoff N. Hiten]

Comments Inline

Post by Wingman
Ok, let me define some names first. We have two SQL servers. Server A is
the publishing server which I try to add a linked server into. Server B is
the subscriber server which is the linked server that ServerA tries to add. I
checked the sysservers table in the server A's master database and it has a
server B record in the table, which is what we want. This record also show
value 1 under both 'isremote' and 'dataaccess'. If I guess this right, they
mean the linked server B can be remoted in. Does this accomplish what you
ask me to do in your previous reply?

Close. Connect to Server B and run the SQL command script I sent. What you
have here tells Server A how to find and connect to Server B. You still
have to tell Server B to accept remote procedure calls. That is what the
script does.

Post by Wingman
Secondly, I can issue a select statement from server A to retrieve data from
a table in server B but using the same SQL login I can't call the remote
stored procedure in server B from server A in query anayzer. What do you
think about this problem?

One, enable remote procedure calls as per above. Two, make sure the linked
server login you are using has execute permissions on the stored procedure
on B. Profiler can help you troubleshoot this.

Post by Wingman
The Link didn't tell me to set the remote flag in both servers. Actually,
by running the script in the link, it set the remote flag to 1 already but
only to the sysservers table in server A. Do you still think I need to do
that in server B?

Yes.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

Post by Wingman
Wingman

Post by Geoff N. Hiten
Yes, it is correct to see the servers listed in more than one place. The
values you refer to are bit flags held in an integer field. Addition is not
the correct operation, logical OR is the correct operation.
You need to enable each server for remote access, just like it says.
Login
exec sp_configure 'remote access', 1
go
reconfigure with override
go
This tells the remote server to accept remote queries.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

Post by Wingman
Ok, I tried that stored procedure and the server name now shows up in both
linked server and remote servers. And I still can't execute the remote
stored procedure. Here is my observation after reading the link. It said
the status for a remote server of a subscriber supposedly a 69. In
order
for
it to become a linked server of a subscriber it needs to be 229.
Before
I
executed the stored procedure, the server name that I want to change its
status is 1093 not 69. After the execution, it added 160 to it to become
1253. Is the 1253 status correct? The initial status number 1093 is
not
69,
is it because there are other settings added values to it?
1) is it correct that the server name will show up both in the linked
servers and remote servers in EM?
2) Is the 1253 new status correct?
3) the link didn't say I need to do a service restart, what do you think?
I
"Could not execute procedure on remote server 'server1' because SQL Server
is not configured for remote access. Ask your system administrator to
reconfigure SQL Server to allow remote access."
Also, when I try to double click on the 'table' of the linked server, it
said the server doesn't exist or access denied. The SQL login name I
entered in the Linked server properties has the sys admin right and this
account exists in both servers.
Wingman

Post by Wingman
If I create a transactional replication - Push method in SQL 2K
standard
with
sp4, will replication create entries in the Remote Servers setting in EM?
Currently I found three entries in the Remote Servers listing, server name
for the publishing server name, server name for the subscriber server, and
repl_distrbutor.
If the replication does make these entries, I have no problem with the
repl_distributor being there. But I have problem with the subscriber server
name being there because I want to create a Linked Server with that
same
name
but I can't since the name is already in use by the remote servers.
Please let me know which one or all of the above names are legit if indeed
repliation put them in there to begin with.
Say if the above entries need to be there, how I should move them to the
Linked Servers setting because the remote servers feature is for backward
compatible reason and linked servers is more dynamic and is the one to
replace it.
Thanks in advance.
Wingman

[Wingman]

The SQL login I have been using between the server A and B is the same name
and password plus has a dbo right on the database where the stored procedure
is stored in Server B. Since the login name has the dbo right, it should take
care of any permission issue, right?

Here is what I have:

Server A - has a linked server B entry in the sysservers table, which its
'IsRemote' and 'DataAccess' set to 1. Also Server A has its remote access
via rpc check box checked in EM.

Server B - has a linked server A entry in the sysservers table, which its
'IsRemote' and 'DataAccess' set to 1. Server B has its remote access via rpc
check box checked in EM.

When I execute the remote stored procedure in server A's query analyzer
using a login 'user1' who has the dbo right on the 'test' database indicated
below, and I include here for your reference:

exec ServerB.test.dbo.proc_test

Here is my error again:

"Could not execute procedure on remote server 'server1' because SQL
Server is not configured for remote access. Ask your system administrator to
reconfigure SQL Server to allow remote access."

My hair become more grey now!!

wingman

Post by Geoff N. Hiten
Comments Inline

Post by Wingman
Ok, let me define some names first. We have two SQL servers. Server A is
the publishing server which I try to add a linked server into. Server B is
the subscriber server which is the linked server that ServerA tries to add. I
checked the sysservers table in the server A's master database and it has a
server B record in the table, which is what we want. This record also show
value 1 under both 'isremote' and 'dataaccess'. If I guess this right, they
mean the linked server B can be remoted in. Does this accomplish what you
ask me to do in your previous reply?

Close. Connect to Server B and run the SQL command script I sent. What you
have here tells Server A how to find and connect to Server B. You still
have to tell Server B to accept remote procedure calls. That is what the
script does.

Post by Wingman
Secondly, I can issue a select statement from server A to retrieve data from
a table in server B but using the same SQL login I can't call the remote
stored procedure in server B from server A in query anayzer. What do you
think about this problem?

One, enable remote procedure calls as per above. Two, make sure the linked
server login you are using has execute permissions on the stored procedure
on B. Profiler can help you troubleshoot this.

Post by Wingman
The Link didn't tell me to set the remote flag in both servers. Actually,
by running the script in the link, it set the remote flag to 1 already but
only to the sysservers table in server A. Do you still think I need to do
that in server B?

Yes.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

Post by Wingman
Wingman

Post by Geoff N. Hiten
Yes, it is correct to see the servers listed in more than one place. The
values you refer to are bit flags held in an integer field. Addition is not
the correct operation, logical OR is the correct operation.
You need to enable each server for remote access, just like it says.
Login
exec sp_configure 'remote access', 1
go
reconfigure with override
go
This tells the remote server to accept remote queries.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

Post by Wingman
Ok, I tried that stored procedure and the server name now shows up in both
linked server and remote servers. And I still can't execute the remote
stored procedure. Here is my observation after reading the link. It said
the status for a remote server of a subscriber supposedly a 69. In
order
for
it to become a linked server of a subscriber it needs to be 229.
Before
I
executed the stored procedure, the server name that I want to change its
status is 1093 not 69. After the execution, it added 160 to it to become
1253. Is the 1253 status correct? The initial status number 1093 is
not
69,
is it because there are other settings added values to it?
1) is it correct that the server name will show up both in the linked
servers and remote servers in EM?
2) Is the 1253 new status correct?
3) the link didn't say I need to do a service restart, what do you think?
I
"Could not execute procedure on remote server 'server1' because SQL Server
is not configured for remote access. Ask your system administrator to
reconfigure SQL Server to allow remote access."
Also, when I try to double click on the 'table' of the linked server, it
said the server doesn't exist or access denied. The SQL login name I
entered in the Linked server properties has the sys admin right and this
account exists in both servers.
Wingman

Post by Wingman
If I create a transactional replication - Push method in SQL 2K
standard
with
sp4, will replication create entries in the Remote Servers setting in EM?
Currently I found three entries in the Remote Servers listing, server name
for the publishing server name, server name for the subscriber server, and
repl_distrbutor.
If the replication does make these entries, I have no problem with the
repl_distributor being there. But I have problem with the subscriber server
name being there because I want to create a Linked Server with that
same
name
but I can't since the name is already in use by the remote servers.
Please let me know which one or all of the above names are legit if indeed
repliation put them in there to begin with.
Say if the above entries need to be there, how I should move them to the
Linked Servers setting because the remote servers feature is for backward
compatible reason and linked servers is more dynamic and is the one to
replace it.
Thanks in advance.
Wingman

[Wingman]

Geoff,

I restarted the SQL service on server A and server B and the remote stored
procedure call is working now. Apparently sp_reconfigure command required a
service restart to make things work.

Post by Wingman
The SQL login I have been using between the server A and B is the same name
and password plus has a dbo right on the database where the stored procedure
is stored in Server B. Since the login name has the dbo right, it should take
care of any permission issue, right?
Server A - has a linked server B entry in the sysservers table, which its
'IsRemote' and 'DataAccess' set to 1. Also Server A has its remote access
via rpc check box checked in EM.
Server B - has a linked server A entry in the sysservers table, which its
'IsRemote' and 'DataAccess' set to 1. Server B has its remote access via rpc
check box checked in EM.
When I execute the remote stored procedure in server A's query analyzer
using a login 'user1' who has the dbo right on the 'test' database indicated
exec ServerB.test.dbo.proc_test
"Could not execute procedure on remote server 'server1' because SQL
Server is not configured for remote access. Ask your system administrator to
reconfigure SQL Server to allow remote access."
My hair become more grey now!!
wingman

Post by Geoff N. Hiten
Comments Inline

Post by Wingman
Ok, let me define some names first. We have two SQL servers. Server A is
the publishing server which I try to add a linked server into. Server B is
the subscriber server which is the linked server that ServerA tries to add. I
checked the sysservers table in the server A's master database and it has a
server B record in the table, which is what we want. This record also show
value 1 under both 'isremote' and 'dataaccess'. If I guess this right, they
mean the linked server B can be remoted in. Does this accomplish what you
ask me to do in your previous reply?

Close. Connect to Server B and run the SQL command script I sent. What you
have here tells Server A how to find and connect to Server B. You still
have to tell Server B to accept remote procedure calls. That is what the
script does.

Post by Wingman
Secondly, I can issue a select statement from server A to retrieve data from
a table in server B but using the same SQL login I can't call the remote
stored procedure in server B from server A in query anayzer. What do you
think about this problem?

One, enable remote procedure calls as per above. Two, make sure the linked
server login you are using has execute permissions on the stored procedure
on B. Profiler can help you troubleshoot this.

Post by Wingman
The Link didn't tell me to set the remote flag in both servers. Actually,
by running the script in the link, it set the remote flag to 1 already but
only to the sysservers table in server A. Do you still think I need to do
that in server B?

Yes.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

Post by Wingman
Wingman

Post by Geoff N. Hiten
Yes, it is correct to see the servers listed in more than one place. The
values you refer to are bit flags held in an integer field. Addition is not
the correct operation, logical OR is the correct operation.
You need to enable each server for remote access, just like it says.
Login
exec sp_configure 'remote access', 1
go
reconfigure with override
go
This tells the remote server to accept remote queries.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

Post by Wingman
Ok, I tried that stored procedure and the server name now shows up in both
linked server and remote servers. And I still can't execute the remote
stored procedure. Here is my observation after reading the link. It said
the status for a remote server of a subscriber supposedly a 69. In
order
for
it to become a linked server of a subscriber it needs to be 229.
Before
I
executed the stored procedure, the server name that I want to change its
status is 1093 not 69. After the execution, it added 160 to it to become
1253. Is the 1253 status correct? The initial status number 1093 is
not
69,
is it because there are other settings added values to it?
1) is it correct that the server name will show up both in the linked
servers and remote servers in EM?
2) Is the 1253 new status correct?
3) the link didn't say I need to do a service restart, what do you think?
I
"Could not execute procedure on remote server 'server1' because SQL Server
is not configured for remote access. Ask your system administrator to
reconfigure SQL Server to allow remote access."
Also, when I try to double click on the 'table' of the linked server, it
said the server doesn't exist or access denied. The SQL login name I
entered in the Linked server properties has the sys admin right and this
account exists in both servers.
Wingman

Post by Wingman
If I create a transactional replication - Push method in SQL 2K
standard
with
sp4, will replication create entries in the Remote Servers setting in EM?
Currently I found three entries in the Remote Servers listing, server name
for the publishing server name, server name for the subscriber server, and
repl_distrbutor.
If the replication does make these entries, I have no problem with the
repl_distributor being there. But I have problem with the subscriber
server
name being there because I want to create a Linked Server with that
same
name
but I can't since the name is already in use by the remote servers.
Please let me know which one or all of the above names are legit if indeed
repliation put them in there to begin with.
Say if the above entries need to be there, how I should move them to the
Linked Servers setting because the remote servers feature is for backward
compatible reason and linked servers is more dynamic and is the one to
replace it.
Thanks in advance.
Wingman

[Geoff N. Hiten]

Some of the settings in sp_configure do require a service restart. Others
are dynamic. I remember some of the key ones, but not all. Glad things are
working for you.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

Post by Wingman
Geoff,
I restarted the SQL service on server A and server B and the remote stored
procedure call is working now. Apparently sp_reconfigure command required a
service restart to make things work.

Post by Wingman
The SQL login I have been using between the server A and B is the same name
and password plus has a dbo right on the database where the stored procedure
is stored in Server B. Since the login name has the dbo right, it should take
care of any permission issue, right?
Server A - has a linked server B entry in the sysservers table, which its
'IsRemote' and 'DataAccess' set to 1. Also Server A has its remote access
via rpc check box checked in EM.
Server B - has a linked server A entry in the sysservers table, which its
'IsRemote' and 'DataAccess' set to 1. Server B has its remote access via rpc
check box checked in EM.
When I execute the remote stored procedure in server A's query analyzer
using a login 'user1' who has the dbo right on the 'test' database indicated
exec ServerB.test.dbo.proc_test
"Could not execute procedure on remote server 'server1' because SQL
Server is not configured for remote access. Ask your system administrator to
reconfigure SQL Server to allow remote access."
My hair become more grey now!!
wingman

Post by Geoff N. Hiten
Comments Inline

Post by Wingman
Ok, let me define some names first. We have two SQL servers. Server A is
the publishing server which I try to add a linked server into.
Server B
is
the subscriber server which is the linked server that ServerA tries
to
add. I
checked the sysservers table in the server A's master database and it
has
a
server B record in the table, which is what we want. This record
also
show
value 1 under both 'isremote' and 'dataaccess'. If I guess this
right,
they
mean the linked server B can be remoted in. Does this accomplish what you
ask me to do in your previous reply?

Close. Connect to Server B and run the SQL command script I sent.
What you
have here tells Server A how to find and connect to Server B. You still
have to tell Server B to accept remote procedure calls. That is what the
script does.

Post by Wingman
Secondly, I can issue a select statement from server A to retrieve
data
from
a table in server B but using the same SQL login I can't call the remote
stored procedure in server B from server A in query anayzer. What
do
you
think about this problem?

One, enable remote procedure calls as per above. Two, make sure the linked
server login you are using has execute permissions on the stored procedure
on B. Profiler can help you troubleshoot this.

Post by Wingman
The Link didn't tell me to set the remote flag in both servers.
Actually,
by running the script in the link, it set the remote flag to 1 already but
only to the sysservers table in server A. Do you still think I need to do
that in server B?

Yes.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

Post by Wingman
Wingman

Post by Geoff N. Hiten
Yes, it is correct to see the servers listed in more than one place.
The
values you refer to are bit flags held in an integer field.
Addition is
not
the correct operation, logical OR is the correct operation.
You need to enable each server for remote access, just like it says.
Login
exec sp_configure 'remote access', 1
go
reconfigure with override
go
This tells the remote server to accept remote queries.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

Post by Wingman
Ok, I tried that stored procedure and the server name now shows up
in
both
linked server and remote servers. And I still can't execute the remote
stored procedure. Here is my observation after reading the link.
It
said
the status for a remote server of a subscriber supposedly a 69.
In
order
for
it to become a linked server of a subscriber it needs to be 229.
Before
I
executed the stored procedure, the server name that I want to
change
its
status is 1093 not 69. After the execution, it added 160 to it
to
become
1253. Is the 1253 status correct? The initial status number 1093 is
not
69,
is it because there are other settings added values to it?
1) is it correct that the server name will show up both in the linked
servers and remote servers in EM?
2) Is the 1253 new status correct?
3) the link didn't say I need to do a service restart, what do you think?
I
"Could not execute procedure on remote server 'server1' because SQL
Server
is not configured for remote access. Ask your system administrator to
reconfigure SQL Server to allow remote access."
Also, when I try to double click on the 'table' of the linked
server,
it
said the server doesn't exist or access denied. The SQL login name I
entered in the Linked server properties has the sys admin right
and
this
account exists in both servers.
Wingman

Post by Wingman
If I create a transactional replication - Push method in SQL 2K
standard
with
sp4, will replication create entries in the Remote Servers
setting in
EM?
Currently I found three entries in the Remote Servers listing,
server
name
for the publishing server name, server name for the subscriber
server,
and
repl_distrbutor.
If the replication does make these entries, I have no problem with the
repl_distributor being there. But I have problem with the subscriber
server
name being there because I want to create a Linked Server with that
same
name
but I can't since the name is already in use by the remote servers.
Please let me know which one or all of the above names are legit
if
indeed
repliation put them in there to begin with.
Say if the above entries need to be there, how I should move them
to
the
Linked Servers setting because the remote servers feature is for backward
compatible reason and linked servers is more dynamic and is the one to
replace it.
Thanks in advance.
Wingman