I have been trying to get replication to work and have been having
nothing but problems.
I have a bunch of jobs set up in my EM and they all have red circles
with an X through it ( I assume this is because they all failed).
I have been trying to set up the publisher and distributer on the same
server.
Here is one of the jobs I can't get to work. I get an error on the
first step if I copy it to Query and run it. But when I right click the
job and look at the history I get the following message:
The job failed. The Job was invoked by User DINO\tfs. The last step to
run was step 3 (Detect nonlogged agent shutdown.).
According to this, I assume that it got to step 3 and died, but if I run
the Query (as I mentioned), it dies at step 1. The error I got was:
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_MSdetect_nonlogged_shutdown'.
If it couldn't find the stored procedure in Query, how could it find it
in the Job I ran?
Here are the steps that are running.
*******************************************************************
Step 1
Step Name: Log agent startup message.
Type: Transact-SQL Script(TSQL)
Database: Distribution
sp_MSadd_distribution_history @.perfmon_increment = 0, @.agent_id = 2,
@.runstatus = 1,
@.comments = 'Starting agent.'
I get an error here when running here from Query:
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_MSdetect_nonlogged_shutdown'.
Step 2
Step Name: Run Agent
Type: Replication Snapshot
Database: Distribution
-Publisher [DINO] -PublisherDB [Northwind] -Distributor [DINO]
-Publication [Northwind] -DistributorSecurityMode 1
Step 3
Step Name: Detect nonlogged agent shutdown.
Type: Transact-SQL Script(TSQL)
Database: Distribution
sp_MSdetect_nonlogged_shutdown @.subsystem = 'Snapshot', @.agent_id = 2
***********************************************************************
The messages I get from history are not very helpful as it doesn't tell
me much. I had a job that got an error because a table didn't exist. I
know this because I tried it in Query. But the history of the job
always shows the same type of message. Just that the last step run was
X. But not why it died. Query knows why, why doesn't the Sql Agent?
Thanks,
Tom.Hi,
Check whether you can extract any information from the following url
http://www.cis.usouthal.edu/share/New%20Folder/Microsoft%20SQL%20Server/MSSQL/Install/instdist.sql
This contains the sp_MSdetect_nonlogged_shutdown stored procedure. At
present i am not able to provide any information coz i am unsure of the
setup you have installed there. I will try my best to sort this out.
Regards,
Jagan Mohan
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:3FC98D38.1020506@.deltanet.com...
> I have been trying to get replication to work and have been having
> nothing but problems.
> I have a bunch of jobs set up in my EM and they all have red circles
> with an X through it ( I assume this is because they all failed).
> I have been trying to set up the publisher and distributer on the same
> server.
> Here is one of the jobs I can't get to work. I get an error on the
> first step if I copy it to Query and run it. But when I right click the
> job and look at the history I get the following message:
> The job failed. The Job was invoked by User DINO\tfs. The last step to
> run was step 3 (Detect nonlogged agent shutdown.).
> According to this, I assume that it got to step 3 and died, but if I run
> the Query (as I mentioned), it dies at step 1. The error I got was:
> Server: Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'sp_MSdetect_nonlogged_shutdown'.
>
> If it couldn't find the stored procedure in Query, how could it find it
> in the Job I ran?
> Here are the steps that are running.
> *******************************************************************
> Step 1
> Step Name: Log agent startup message.
> Type: Transact-SQL Script(TSQL)
> Database: Distribution
> sp_MSadd_distribution_history @.perfmon_increment = 0, @.agent_id = 2,
> @.runstatus = 1,
> @.comments = 'Starting agent.'
> I get an error here when running here from Query:
> Server: Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'sp_MSdetect_nonlogged_shutdown'.
>
> Step 2
> Step Name: Run Agent
> Type: Replication Snapshot
> Database: Distribution
> -Publisher [DINO] -PublisherDB [Northwind] -Distributor [DINO]
> -Publication [Northwind] -DistributorSecurityMode 1
> Step 3
> Step Name: Detect nonlogged agent shutdown.
> Type: Transact-SQL Script(TSQL)
> Database: Distribution
> sp_MSdetect_nonlogged_shutdown @.subsystem = 'Snapshot', @.agent_id = 2
> ***********************************************************************
> The messages I get from history are not very helpful as it doesn't tell
> me much. I had a job that got an error because a table didn't exist. I
> know this because I tried it in Query. But the history of the job
> always shows the same type of message. Just that the last step run was
> X. But not why it died. Query knows why, why doesn't the Sql Agent?
> Thanks,
> Tom.
>|||Jagan Mohan wrote:
> Hi,
> Check whether you can extract any information from the following url
> http://www.cis.usouthal.edu/share/New%20Folder/Microsoft%20SQL%20Server/MSSQL/Install/instdist.sql
> This contains the sp_MSdetect_nonlogged_shutdown stored procedure. At
> present i am not able to provide any information coz i am unsure of the
> setup you have installed there. I will try my best to sort this out.
I looked at it, but it won't tell me anything as the error says it can't
find the procedure. But the Server Agent got to the 3rd step, but
failed, so I assume it did step 1 correctly. Not sure what the
difference is.
> Regards,
> Jagan Mohan
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:3FC98D38.1020506@.deltanet.com...
>>I have been trying to get replication to work and have been having
>>nothing but problems.
>>I have a bunch of jobs set up in my EM and they all have red circles
>>with an X through it ( I assume this is because they all failed).
>>I have been trying to set up the publisher and distributer on the same
>>server.
>>Here is one of the jobs I can't get to work. I get an error on the
>>first step if I copy it to Query and run it. But when I right click the
>>job and look at the history I get the following message:
>>The job failed. The Job was invoked by User DINO\tfs. The last step to
>>run was step 3 (Detect nonlogged agent shutdown.).
>>According to this, I assume that it got to step 3 and died, but if I run
>>the Query (as I mentioned), it dies at step 1. The error I got was:
>>Server: Msg 2812, Level 16, State 62, Line 1
>>Could not find stored procedure 'sp_MSdetect_nonlogged_shutdown'.
>>
>>If it couldn't find the stored procedure in Query, how could it find it
>>in the Job I ran?
>>Here are the steps that are running.
>>*******************************************************************
>>Step 1
>>Step Name: Log agent startup message.
>>Type: Transact-SQL Script(TSQL)
>>Database: Distribution
>>sp_MSadd_distribution_history @.perfmon_increment = 0, @.agent_id = 2,
>>@.runstatus = 1,
>>@.comments = 'Starting agent.'
>>I get an error here when running here from Query:
>>Server: Msg 2812, Level 16, State 62, Line 1
>>Could not find stored procedure 'sp_MSdetect_nonlogged_shutdown'.
>>
>>Step 2
>>Step Name: Run Agent
>>Type: Replication Snapshot
>>Database: Distribution
>>-Publisher [DINO] -PublisherDB [Northwind] -Distributor [DINO]
>>-Publication [Northwind] -DistributorSecurityMode 1
>>Step 3
>>Step Name: Detect nonlogged agent shutdown.
>>Type: Transact-SQL Script(TSQL)
>>Database: Distribution
>>sp_MSdetect_nonlogged_shutdown @.subsystem = 'Snapshot', @.agent_id = 2
>>***********************************************************************
>>The messages I get from history are not very helpful as it doesn't tell
>>me much. I had a job that got an error because a table didn't exist. I
>>know this because I tried it in Query. But the history of the job
>>always shows the same type of message. Just that the last step run was
>>X. But not why it died. Query knows why, why doesn't the Sql Agent?
>>Thanks,
>>Tom.
>>
>
to locate the snapshot