I've got this SQL2K/WIN2K setup and a database having merge replication
setup. It's been running there for more than couple of years. Now due to
strategic changes we don't need this replication on the database.
When I tried to remove it, it started blocking normal user activity that is
mostly on the same tables which are published. After 15-20 minutes I had to
cancle it. The database is serving 24/7 and I want to remove replication with
minimal disruption to regular opration. Is there any easier way to remove
replication and metadata generated by it.?
I would appreciate any help/pointers.
Have you tried to call sp_removedbreplication? This proc should remove the
replication from the database.
Yi
"adi" <adi@.discussions.microsoft.com> wrote in message
news:7B04463C-FFA2-4BEA-8339-EB3340FEE5A2@.microsoft.com...
> I've got this SQL2K/WIN2K setup and a database having merge replication
> setup. It's been running there for more than couple of years. Now due to
> strategic changes we don't need this replication on the database.
> When I tried to remove it, it started blocking normal user activity that
> is
> mostly on the same tables which are published. After 15-20 minutes I had
> to
> cancle it. The database is serving 24/7 and I want to remove replication
> with
> minimal disruption to regular opration. Is there any easier way to remove
> replication and metadata generated by it.?
> I would appreciate any help/pointers.
Showing posts with label sql2k. Show all posts
Showing posts with label sql2k. Show all posts
Monday, March 26, 2012
Tuesday, March 20, 2012
Remote server setting -- Urgent
I have a sql2k clustered server. now this server need remote server connection to other server. I have set up remote server on both site, but only one way works: connection to clustered server is ok while from clustered server I can not get the remote sit
e. error msg:
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
I am sure network access to 1433 port on both virtual server IP and remote server IP is OK Do I still need to open 1433 port on each node of clustered server to remote server?
Pls help.
Thanks
George
If the remote server is in another forest/domain you may need a two-way trust between the domains. What forest + domain functionality level are they both running? (eg W2K mixed, W2K native, W2K3 native etc). Can you browse the Active Directory objects i
n the remote site (from both directions) using dsa.msc? If you can you don't have a trust problem. If the trusts are okay (or both servers are in the same domain) then you can add the SQL Server service account from one server to the local admins group
of the other (just as a test!). If this fails then you know you have bigger problems on your WAN than just SQL Server and permissions.
|||Thanks Stephen. But I can not see active directory; and the remote server is in a workgroup, not a domain.
pls advise, thanks a lot
George
e. error msg:
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
I am sure network access to 1433 port on both virtual server IP and remote server IP is OK Do I still need to open 1433 port on each node of clustered server to remote server?
Pls help.
Thanks
George
If the remote server is in another forest/domain you may need a two-way trust between the domains. What forest + domain functionality level are they both running? (eg W2K mixed, W2K native, W2K3 native etc). Can you browse the Active Directory objects i
n the remote site (from both directions) using dsa.msc? If you can you don't have a trust problem. If the trusts are okay (or both servers are in the same domain) then you can add the SQL Server service account from one server to the local admins group
of the other (just as a test!). If this fails then you know you have bigger problems on your WAN than just SQL Server and permissions.
|||Thanks Stephen. But I can not see active directory; and the remote server is in a workgroup, not a domain.
pls advise, thanks a lot
George
Remote Server question
sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisRThis is a multi-part message in MIME format.
--090909090705070004050507
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Is it possible that your remote server is set up to use Windows
Authentication only? (Even though you can set the 'sa' password, that
doesn't imply you can actually login using that standard SQL login.)
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
ChrisR wrote:
>sql2k sp3
>
>Ive always used Linked Servers and as such dont really know about Remote
>Servers. Anyways, here is the deal.
>Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
>is a Subscriber to Box1.
>So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
>as SA. But I get the message:
>Server: Msg 18483, Level 14, State 1, Line 1
>Could not connect to server 'BLA'because 'sa' is not defined as a remote
>login at the server.
>The SA password is the same on both these boxes, so while logged in as SA I:
>
>sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
> , @.useself = 'true'
>but that doesnt make my problem go away. The really funny thing here is that
>I googled it, saw that I posted the same question years ago, see that I got
>it, but cant read the answer.
>TIA, ChrisR
>
>
>
--090909090705070004050507
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Is it possible that your remote server is set up to use Windows
Authentication only? (Even though you can set the 'sa' password, that
doesn't imply you can actually login using that standard SQL login.)</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
ChrisR wrote:
<blockquote cite="mid%230BGYuarFHA.3424@.TK2MSFTNGP14.phx.gbl"
type="cite">
<pre wrap="">sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisR
</pre>
</blockquote>
</body>
</html>
--090909090705070004050507--|||This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C5AE09.DF950070
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thanks Mike, but no thats not the case.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:O$vQwAdrFHA.908@.tk2msftngp13.phx.gbl...
Is it possible that your remote server is set up to use Windows =Authentication only? (Even though you can set the 'sa' password, that =doesn't imply you can actually login using that standard SQL login.)
--
mike hodgson
blog: http://sqlnerd.blogspot.com=20
ChrisR wrote: sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote =
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, =which is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while =logged in as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote =
login at the server.
The SA password is the same on both these boxes, so while logged in as =SA I:
sp_addlinkedsrvlogin @.rmtsrvname =3D 'cpdbovs01'
, @.useself =3D 'true'
but that doesnt make my problem go away. The really funny thing here is =that I googled it, saw that I posted the same question years ago, see that I =got it, but cant read the answer.
TIA, ChrisR
--=_NextPart_000_0008_01C5AE09.DF950070
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Thanks Mike, but no thats not the case.
"Mike Hodgson" wrote in message news:O$vQwAdrFHA.908@.t=k2msftngp13.phx.gbl...Is it possible that your remote server is set up to use Windows =Authentication only? (Even though you can set the 'sa' password, that doesn't =imply you can actually login using that standard SQL login.)
--mike =hodgsonblog: http://sqlnerd.blogspot.com ChrisR wrote: sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote =Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, =which is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while =logged in as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote =login at the server.
The SA password is the same on both these boxes, so while logged in as =SA I:
sp_addlinkedsrvlogin @.rmtsrvname =3D 'cpdbovs01'
, @.useself =3D 'true'
but that doesnt make my problem go away. The really funny thing here is =that I googled it, saw that I posted the same question years ago, see that I =got it, but cant read the answer.
TIA, ChrisR
--=_NextPart_000_0008_01C5AE09.DF950070--
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisRThis is a multi-part message in MIME format.
--090909090705070004050507
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Is it possible that your remote server is set up to use Windows
Authentication only? (Even though you can set the 'sa' password, that
doesn't imply you can actually login using that standard SQL login.)
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
ChrisR wrote:
>sql2k sp3
>
>Ive always used Linked Servers and as such dont really know about Remote
>Servers. Anyways, here is the deal.
>Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
>is a Subscriber to Box1.
>So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
>as SA. But I get the message:
>Server: Msg 18483, Level 14, State 1, Line 1
>Could not connect to server 'BLA'because 'sa' is not defined as a remote
>login at the server.
>The SA password is the same on both these boxes, so while logged in as SA I:
>
>sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
> , @.useself = 'true'
>but that doesnt make my problem go away. The really funny thing here is that
>I googled it, saw that I posted the same question years ago, see that I got
>it, but cant read the answer.
>TIA, ChrisR
>
>
>
--090909090705070004050507
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Is it possible that your remote server is set up to use Windows
Authentication only? (Even though you can set the 'sa' password, that
doesn't imply you can actually login using that standard SQL login.)</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
ChrisR wrote:
<blockquote cite="mid%230BGYuarFHA.3424@.TK2MSFTNGP14.phx.gbl"
type="cite">
<pre wrap="">sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisR
</pre>
</blockquote>
</body>
</html>
--090909090705070004050507--|||This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C5AE09.DF950070
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thanks Mike, but no thats not the case.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:O$vQwAdrFHA.908@.tk2msftngp13.phx.gbl...
Is it possible that your remote server is set up to use Windows =Authentication only? (Even though you can set the 'sa' password, that =doesn't imply you can actually login using that standard SQL login.)
--
mike hodgson
blog: http://sqlnerd.blogspot.com=20
ChrisR wrote: sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote =
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, =which is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while =logged in as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote =
login at the server.
The SA password is the same on both these boxes, so while logged in as =SA I:
sp_addlinkedsrvlogin @.rmtsrvname =3D 'cpdbovs01'
, @.useself =3D 'true'
but that doesnt make my problem go away. The really funny thing here is =that I googled it, saw that I posted the same question years ago, see that I =got it, but cant read the answer.
TIA, ChrisR
--=_NextPart_000_0008_01C5AE09.DF950070
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Thanks Mike, but no thats not the case.
"Mike Hodgson"
--mike =hodgsonblog: http://sqlnerd.blogspot.com ChrisR wrote: sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote =Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, =which is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while =logged in as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote =login at the server.
The SA password is the same on both these boxes, so while logged in as =SA I:
sp_addlinkedsrvlogin @.rmtsrvname =3D 'cpdbovs01'
, @.useself =3D 'true'
but that doesnt make my problem go away. The really funny thing here is =that I googled it, saw that I posted the same question years ago, see that I =got it, but cant read the answer.
TIA, ChrisR
--=_NextPart_000_0008_01C5AE09.DF950070--
Remote Server question
sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisRIs it possible that your remote server is set up to use Windows
Authentication only? (Even though you can set the 'sa' password, that
doesn't imply you can actually login using that standard SQL login.)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
ChrisR wrote:
>sql2k sp3
>
>Ive always used Linked Servers and as such dont really know about Remote
>Servers. Anyways, here is the deal.
>Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
>is a Subscriber to Box1.
>So, Im trying to exec a proc on Box2 while logged on to Box1 while logged i
n
>as SA. But I get the message:
>Server: Msg 18483, Level 14, State 1, Line 1
>Could not connect to server 'BLA'because 'sa' is not defined as a remote
>login at the server.
>The SA password is the same on both these boxes, so while logged in as SA I
:
>
>sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
> , @.useself = 'true'
>but that doesnt make my problem go away. The really funny thing here is tha
t
>I googled it, saw that I posted the same question years ago, see that I got
>it, but cant read the answer.
>TIA, ChrisR
>
>
>|||Thanks Mike, but no thats not the case.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:O$v
QwAdrFHA.908@.tk2msftngp13.phx.gbl...
Is it possible that your remote server is set up to use Windows Authenticati
on only? (Even though you can set the 'sa' password, that doesn't imply you
can actually login using that standard SQL login.)
mike hodgson
blog: http://sqlnerd.blogspot.com
ChrisR wrote:
sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisR
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisRIs it possible that your remote server is set up to use Windows
Authentication only? (Even though you can set the 'sa' password, that
doesn't imply you can actually login using that standard SQL login.)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
ChrisR wrote:
>sql2k sp3
>
>Ive always used Linked Servers and as such dont really know about Remote
>Servers. Anyways, here is the deal.
>Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
>is a Subscriber to Box1.
>So, Im trying to exec a proc on Box2 while logged on to Box1 while logged i
n
>as SA. But I get the message:
>Server: Msg 18483, Level 14, State 1, Line 1
>Could not connect to server 'BLA'because 'sa' is not defined as a remote
>login at the server.
>The SA password is the same on both these boxes, so while logged in as SA I
:
>
>sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
> , @.useself = 'true'
>but that doesnt make my problem go away. The really funny thing here is tha
t
>I googled it, saw that I posted the same question years ago, see that I got
>it, but cant read the answer.
>TIA, ChrisR
>
>
>|||Thanks Mike, but no thats not the case.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:O$v
QwAdrFHA.908@.tk2msftngp13.phx.gbl...
Is it possible that your remote server is set up to use Windows Authenticati
on only? (Even though you can set the 'sa' password, that doesn't imply you
can actually login using that standard SQL login.)
mike hodgson
blog: http://sqlnerd.blogspot.com
ChrisR wrote:
sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisR
Remote Server question
sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisR
Is it possible that your remote server is set up to use Windows
Authentication only? (Even though you can set the 'sa' password, that
doesn't imply you can actually login using that standard SQL login.)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
ChrisR wrote:
>sql2k sp3
>
>Ive always used Linked Servers and as such dont really know about Remote
>Servers. Anyways, here is the deal.
>Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
>is a Subscriber to Box1.
>So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
>as SA. But I get the message:
>Server: Msg 18483, Level 14, State 1, Line 1
>Could not connect to server 'BLA'because 'sa' is not defined as a remote
>login at the server.
>The SA password is the same on both these boxes, so while logged in as SA I:
>
>sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
> , @.useself = 'true'
>but that doesnt make my problem go away. The really funny thing here is that
>I googled it, saw that I posted the same question years ago, see that I got
>it, but cant read the answer.
>TIA, ChrisR
>
>
>
|||Thanks Mike, but no thats not the case.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:O$vQwAdrFHA.908@.tk2msftngp13.phx.gbl...
Is it possible that your remote server is set up to use Windows Authentication only? (Even though you can set the 'sa' password, that doesn't imply you can actually login using that standard SQL login.)
mike hodgson
blog: http://sqlnerd.blogspot.com
ChrisR wrote:
sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisR
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisR
Is it possible that your remote server is set up to use Windows
Authentication only? (Even though you can set the 'sa' password, that
doesn't imply you can actually login using that standard SQL login.)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
ChrisR wrote:
>sql2k sp3
>
>Ive always used Linked Servers and as such dont really know about Remote
>Servers. Anyways, here is the deal.
>Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
>is a Subscriber to Box1.
>So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
>as SA. But I get the message:
>Server: Msg 18483, Level 14, State 1, Line 1
>Could not connect to server 'BLA'because 'sa' is not defined as a remote
>login at the server.
>The SA password is the same on both these boxes, so while logged in as SA I:
>
>sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
> , @.useself = 'true'
>but that doesnt make my problem go away. The really funny thing here is that
>I googled it, saw that I posted the same question years ago, see that I got
>it, but cant read the answer.
>TIA, ChrisR
>
>
>
|||Thanks Mike, but no thats not the case.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:O$vQwAdrFHA.908@.tk2msftngp13.phx.gbl...
Is it possible that your remote server is set up to use Windows Authentication only? (Even though you can set the 'sa' password, that doesn't imply you can actually login using that standard SQL login.)
mike hodgson
blog: http://sqlnerd.blogspot.com
ChrisR wrote:
sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisR
Wednesday, March 7, 2012
Remote Distributor question
sql2k sp3
BoxA Publisher.
BoxB Subscriber. To be used for reporting.
I want the processing drain of Replication to be done on
BoxB. So I was thinking of putting the Distributor on BoxB
and using a Push Subscription.
Do any of you gurus see any problems with my idea?
TIA, Chris?
Reporting services chews up a lot of cycles. You might find it better to
keep the distributor on the publisher.
The point you start to move the distributor off the publisher is when you
start to get significant locking on your distribution database.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:0a4701c46e74$e3ab90f0$a601280a@.phx.gbl...
> sql2k sp3
> BoxA Publisher.
> BoxB Subscriber. To be used for reporting.
> I want the processing drain of Replication to be done on
> BoxB. So I was thinking of putting the Distributor on BoxB
> and using a Push Subscription.
> Do any of you gurus see any problems with my idea?
> TIA, Chris?
|||>Reporting services chews up a lot of cycles.
My bad. I wont be using actual Reporting Services.
You might find it better to
>keep the distributor on the publisher.
>The point you start to move the distributor off the
publisher is when you
>start to get significant locking on your distribution
database.
>
Why is this? Why do people do it then?
>--Original Message--
>Reporting services chews up a lot of cycles. You might
find it better to
>keep the distributor on the publisher.
>The point you start to move the distributor off the
publisher is when you
>start to get significant locking on your distribution
database.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0a4701c46e74$e3ab90f0$a601280a@.phx.gbl...
BoxB
>
>.
>
|||Well, most people do it to reduce the load on the Publisher. The question is
when should you do it, and there is no real rule of thumb, at least as far
as I am aware.
The deciding factor for me is when you experience locking in your
distribution database.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:0c4201c46e8a$e7dd03a0$a601280a@.phx.gbl...[vbcol=seagreen]
> My bad. I wont be using actual Reporting Services.
> You might find it better to
> publisher is when you
> database.
> Why is this? Why do people do it then?
> find it better to
> publisher is when you
> database.
> message
> BoxB
BoxA Publisher.
BoxB Subscriber. To be used for reporting.
I want the processing drain of Replication to be done on
BoxB. So I was thinking of putting the Distributor on BoxB
and using a Push Subscription.
Do any of you gurus see any problems with my idea?
TIA, Chris?
Reporting services chews up a lot of cycles. You might find it better to
keep the distributor on the publisher.
The point you start to move the distributor off the publisher is when you
start to get significant locking on your distribution database.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:0a4701c46e74$e3ab90f0$a601280a@.phx.gbl...
> sql2k sp3
> BoxA Publisher.
> BoxB Subscriber. To be used for reporting.
> I want the processing drain of Replication to be done on
> BoxB. So I was thinking of putting the Distributor on BoxB
> and using a Push Subscription.
> Do any of you gurus see any problems with my idea?
> TIA, Chris?
|||>Reporting services chews up a lot of cycles.
My bad. I wont be using actual Reporting Services.
You might find it better to
>keep the distributor on the publisher.
>The point you start to move the distributor off the
publisher is when you
>start to get significant locking on your distribution
database.
>
Why is this? Why do people do it then?
>--Original Message--
>Reporting services chews up a lot of cycles. You might
find it better to
>keep the distributor on the publisher.
>The point you start to move the distributor off the
publisher is when you
>start to get significant locking on your distribution
database.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0a4701c46e74$e3ab90f0$a601280a@.phx.gbl...
BoxB
>
>.
>
|||Well, most people do it to reduce the load on the Publisher. The question is
when should you do it, and there is no real rule of thumb, at least as far
as I am aware.
The deciding factor for me is when you experience locking in your
distribution database.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:0c4201c46e8a$e7dd03a0$a601280a@.phx.gbl...[vbcol=seagreen]
> My bad. I wont be using actual Reporting Services.
> You might find it better to
> publisher is when you
> database.
> Why is this? Why do people do it then?
> find it better to
> publisher is when you
> database.
> message
> BoxB
Subscribe to:
Posts (Atom)