Remote-Agent Activation

Hi,
Two questions:
1. Not 100-percent sure about this, but it looks like for a
subscription configured with remote agent activation where the
destination owner for articles are not specified as dbo, during a
resync the articles at the subscriber can be owned by any login on the
subscriber. Is this correct? If so, how can this be fixed without
reinitialising the subscriptions!
2. Is it possible with a no-sync subscription to progate schema changes
without recreating the subscription?
Thanks in advance for any worthwhile comments.
1) no, articles define to have a different object owner on the subscriber
should always be owned by the different object owner after a resync.
2) yes, in sql 2005 the default is to replicate ddl changes, in sql 2000 you
can use sp_repladdcolumn or sp_repldropcolumn. It doesn't matter if you did
an automatic sync or a no sync.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<paul.mu@.gapbuster.com> wrote in message
news:1159942193.130907.275300@.h48g2000cwc.googlegr oups.com...
> Hi,
> Two questions:
> 1. Not 100-percent sure about this, but it looks like for a
> subscription configured with remote agent activation where the
> destination owner for articles are not specified as dbo, during a
> resync the articles at the subscriber can be owned by any login on the
> subscriber. Is this correct? If so, how can this be fixed without
> reinitialising the subscriptions!
> 2. Is it possible with a no-sync subscription to progate schema changes
> without recreating the subscription?
> Thanks in advance for any worthwhile comments.
>
|||Thanks for your response Hilary.
The answer to my first question was not what I was after.
Firstly, I am working with transactional replication on SQL Server
2000.
Secondly, I did not have this issue before he change to use remote
agent activation.
The current setup is that most of the articles in the publications do
not have the 'destination_owner' set (it is left as a blank) - the
source_owner is always 'dbo'.
The problem: with remote agent activation, when performing a resync,
the articles at the subscribers have been created with a different
'destination_owner' which is not 'dbo'. This owner can vary from one
resync to another.
A question: can this issue be resolved without having to reconfigure
the publication (ie. replace all blank destination_owner with 'dbo'),
which will require a resync?

Remote Restore via Linked Server

Hi, I'm initiating a restore via a linked server and an SQL Agent Job. The
job completes successfully in about 10 minutes, but the backup is left in a
loading state. This suggests that the connection timed out. Any thoughts
on how to make this more robust? (possibly start an SQL Agent job on the
remote server from the parent server')
Thanks
Bill"Bill Swartz" <swartz.bill@.gmail.com> wrote in message
news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl...
> Hi, I'm initiating a restore via a linked server and an SQL Agent Job.
> The job completes successfully in about 10 minutes, but the backup is left
> in a loading state. This suggests that the connection timed out. Any
> thoughts on how to make this more robust? (possibly start an SQL Agent
> job on the remote server from the parent server')
>
That's what I would do.
David|||Answering my own question to some degree, initiating the remote procedure
via an SQL Agent job on the remote server works fine. (or in other words it
does not time out). But, it also flags the step on the host server
immediately as successful.
What I'm looking for is a way for the job to wait, or at least report the
status of the restore back to the host server.
Bill
"Bill Swartz" <swartz.bill@.gmail.com> wrote in message
news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl...
> Hi, I'm initiating a restore via a linked server and an SQL Agent Job.
> The job completes successfully in about 10 minutes, but the backup is left
> in a loading state. This suggests that the connection timed out. Any
> thoughts on how to make this more robust? (possibly start an SQL Agent
> job on the remote server from the parent server')
> Thanks
> Bill
>|||This is a multi-part message in MIME format.
--050606090705070603040506
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Bill S wrote:
> Answering my own question to some degree, initiating the remote procedure
> via an SQL Agent job on the remote server works fine. (or in other words it
> does not time out). But, it also flags the step on the host server
> immediately as successful.
> What I'm looking for is a way for the job to wait, or at least report the
> status of the restore back to the host server.
> Bill
> "Bill Swartz" <swartz.bill@.gmail.com> wrote in message
> news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl...
>> Hi, I'm initiating a restore via a linked server and an SQL Agent Job.
>> The job completes successfully in about 10 minutes, but the backup is left
>> in a loading state. This suggests that the connection timed out. Any
>> thoughts on how to make this more robust? (possibly start an SQL Agent
>> job on the remote server from the parent server')
>> Thanks
>> Bill
>>
>
>
Hi
I've done a similar thing with a DTS package. In the package there's a
connection to the remote server and then a step that runs a restore on
that server. The package will not finish the execution until the restore
is done so that will give you what you want.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--050606090705070603040506
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">
Bill S wrote:
<blockquote cite="mid%237WfcfqrGHA.1140@.TK2MSFTNGP05.phx.gbl"
type="cite">
<pre wrap="">Answering my own question to some degree, initiating the remote procedure
via an SQL Agent job on the remote server works fine. (or in other words it
does not time out). But, it also flags the step on the host server
immediately as successful.
What I'm looking for is a way for the job to wait, or at least report the
status of the restore back to the host server.
Bill
"Bill Swartz" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:swartz.bill@.gmail.com"><swartz.bill@.gmail.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl">news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Hi, I'm initiating a restore via a linked server and an SQL Agent Job.
The job completes successfully in about 10 minutes, but the backup is left
in a loading state. This suggests that the connection timed out. Any
thoughts on how to make this more robust? (possibly start an SQL Agent
job on the remote server from the parent server')
Thanks
Bill
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
<font size="-1"><font face="Arial">Hi<br>
<br>
I've done a similar thing with a DTS package. In the package there's a
connection to the remote server and then a step that runs a restore on
that server. The package will not finish the execution until the
restore is done so that will give you what you want.<br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
</font></font>
</body>
</html>
--050606090705070603040506--

Remote Restore via Linked Server

Hi, I'm initiating a restore via a linked server and an SQL Agent Job. The
job completes successfully in about 10 minutes, but the backup is left in a
loading state. This suggests that the connection timed out. Any thoughts
on how to make this more robust? (possibly start an SQL Agent job on the
remote server from the parent server')
Thanks
Bill"Bill Swartz" <swartz.bill@.gmail.com> wrote in message
news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl...
> Hi, I'm initiating a restore via a linked server and an SQL Agent Job.
> The job completes successfully in about 10 minutes, but the backup is left
> in a loading state. This suggests that the connection timed out. Any
> thoughts on how to make this more robust? (possibly start an SQL Agent
> job on the remote server from the parent server')
>
That's what I would do.
David|||Answering my own question to some degree, initiating the remote procedure
via an SQL Agent job on the remote server works fine. (or in other words it
does not time out). But, it also flags the step on the host server
immediately as successful.
What I'm looking for is a way for the job to wait, or at least report the
status of the restore back to the host server.
Bill
"Bill Swartz" <swartz.bill@.gmail.com> wrote in message
news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl...
> Hi, I'm initiating a restore via a linked server and an SQL Agent Job.
> The job completes successfully in about 10 minutes, but the backup is left
> in a loading state. This suggests that the connection timed out. Any
> thoughts on how to make this more robust? (possibly start an SQL Agent
> job on the remote server from the parent server')
> Thanks
> Bill
>|||Bill S wrote:
> Answering my own question to some degree, initiating the remote procedure
> via an SQL Agent job on the remote server works fine. (or in other words
it
> does not time out). But, it also flags the step on the host server
> immediately as successful.
> What I'm looking for is a way for the job to wait, or at least report the
> status of the restore back to the host server.
> Bill
> "Bill Swartz" <swartz.bill@.gmail.com> wrote in message
> news:ueVmqUqrGHA.1732@.TK2MSFTNGP03.phx.gbl...
>
>
>
Hi
I've done a similar thing with a DTS package. In the package there's a
connection to the remote server and then a step that runs a restore on
that server. The package will not finish the execution until the restore
is done so that will give you what you want.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator

Remote Manage SQL Server Agent via SSMS

When I open up SSMS from the SQL Server 2005 box itself I can see all SQL
Server Agent jobs. When I open a specific job I can see the owner and steps
etc. When I use SSMS remotely from my Vista laptop to connect to the SQL
Server 2005 instance I can see the jobs, but when I go to look at properties
it is acting like it is a new job with none of the actual owner/config
information. The account I am using to connect in both cases is the same
account and is in the sysadmin role. Any advice is appreciated.
Thanks
--
Adam SYou need to install SP2 or later on your client machine. I believe you have
a mismatch between client and server (a later edition has probably been
upgraded on the server). You should always try to maintain consistency
between the version of tools installed on the server and all clients that
will access it...
A
"Adam S" <Adam S@.community.nospam> wrote in message
news:7B3CEA63-4090-48D4-AFEA-70487F537E5E@.microsoft.com...
> When I open up SSMS from the SQL Server 2005 box itself I can see all SQL
> Server Agent jobs. When I open a specific job I can see the owner and
> steps
> etc. When I use SSMS remotely from my Vista laptop to connect to the SQL
> Server 2005 instance I can see the jobs, but when I go to look at
> properties
> it is acting like it is a new job with none of the actual owner/config
> information. The account I am using to connect in both cases is the same
> account and is in the sysadmin role. Any advice is appreciated.
> Thanks
> --
> Adam S

Remote Manage SQL Server Agent via SSMS

When I open up SSMS from the SQL Server 2005 box itself I can see all SQL
Server Agent jobs. When I open a specific job I can see the owner and steps
etc. When I use SSMS remotely from my Vista laptop to connect to the SQL
Server 2005 instance I can see the jobs, but when I go to look at properties
it is acting like it is a new job with none of the actual owner/config
information. The account I am using to connect in both cases is the same
account and is in the sysadmin role. Any advice is appreciated.
Thanks
Adam SYou need to install SP2 or later on your client machine. I believe you have
a mismatch between client and server (a later edition has probably been
upgraded on the server). You should always try to maintain consistency
between the version of tools installed on the server and all clients that
will access it...
A
"Adam S" <Adam S@.community.nospam> wrote in message
news:7B3CEA63-4090-48D4-AFEA-70487F537E5E@.microsoft.com...
> When I open up SSMS from the SQL Server 2005 box itself I can see all SQL
> Server Agent jobs. When I open a specific job I can see the owner and
> steps
> etc. When I use SSMS remotely from my Vista laptop to connect to the SQL
> Server 2005 instance I can see the jobs, but when I go to look at
> properties
> it is acting like it is a new job with none of the actual owner/config
> information. The account I am using to connect in both cases is the same
> account and is in the sysadmin role. Any advice is appreciated.
> Thanks
> --
> Adam S

Remote Manage SQL Server Agent via SSMS

When I open up SSMS from the SQL Server 2005 box itself I can see all SQL
Server Agent jobs. When I open a specific job I can see the owner and steps
etc. When I use SSMS remotely from my Vista laptop to connect to the SQL
Server 2005 instance I can see the jobs, but when I go to look at properties
it is acting like it is a new job with none of the actual owner/config
information. The account I am using to connect in both cases is the same
account and is in the sysadmin role. Any advice is appreciated.
Thanks
Adam S
You need to install SP2 or later on your client machine. I believe you have
a mismatch between client and server (a later edition has probably been
upgraded on the server). You should always try to maintain consistency
between the version of tools installed on the server and all clients that
will access it...
A
"Adam S" <Adam S@.community.nospam> wrote in message
news:7B3CEA63-4090-48D4-AFEA-70487F537E5E@.microsoft.com...
> When I open up SSMS from the SQL Server 2005 box itself I can see all SQL
> Server Agent jobs. When I open a specific job I can see the owner and
> steps
> etc. When I use SSMS remotely from my Vista laptop to connect to the SQL
> Server 2005 instance I can see the jobs, but when I go to look at
> properties
> it is acting like it is a new job with none of the actual owner/config
> information. The account I am using to connect in both cases is the same
> account and is in the sysadmin role. Any advice is appreciated.
> Thanks
> --
> Adam S