Setting up Transactional Replication.
Log Reader Agent successfull.
Snapshot Agent successfull.
Distribution Agent Error (Replication is failing because of this error. I don't know what is wrong with the stored procedure that causes it not to replicate):
Date 1/19/2007 10:28:13 AM
Log Job History (IS36-MMS_20061213-MMSRepTest-IS4-30)
Step ID 2
Server IS4
Job Name IS36-MMS_20061213-MMSRepTest-IS4-30
Step Name Run agent.
Duration 00:00:11
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
2007-01-19 16:28:24.700 Category:COMMAND
Source: Failed Command
Number:
Message: CREATE PROCEDURE "dbo"."spBTG_GetEventsSince"(@.EventMin datetime, @.BoatID int) AS
BEGIN
create table #TmpEvents
(
BoatHistoryID int null,
PositionID int null,
Event varchar(50) null,
EventDateTime datetime null,
Direction char(1) null,
River char(3) null,
Mile numeric(6,2) null
)
insert into #TmpEvents
select bh.BoatHistoryID, null, Event, EventDateTime, Direction, riverloc, mileloc
--jds 6/22/05 hardcoded index to fix it when you call it from vb
-- from
2007-01-19 16:28:24.700 Category:NULL
Source: Microsoft SQL Native Client
Number: 1018
Message: Incorrect syntax near 'index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.
Here is the entire source of the sp:
GO
CREATE PROCEDURE [dbo].[spBTG_GetEventsSince](@.EventMin datetime, @.BoatID int) AS
BEGIN
create table #TmpEvents
(
BoatHistoryID int null,
PositionID int null,
Event varchar(50) null,
EventDateTime datetime null,
Direction char(1) null,
River char(3) null,
Mile numeric(6,2) null
)
insert into #TmpEvents
select bh.BoatHistoryID, null, Event, EventDateTime, Direction, riverloc, mileloc
--jds 6/22/05 hardcoded index to fix it when you call it from vb
-- from mtsEventHistory eh (NOLOCK)
-- left join mtsBoatHistory bh (NOLOCK) on bh.EventID = eh.EventID
from mtsEventHistory eh (NOLOCK, index(PK_MtsEventHistory))
left join mtsBoatHistory bh (NOLOCK, index(IDX_MtsBoatHistory_BoatID)) on bh.EventID = eh.EventID
where EventDateTime > @.EventMin
and BoatID = @.BoatID
and voidflag = 0
And eh.Event IN ('Pickup','Drop','Log Exchange','Off-Layup','Off-Repair','On-Layup','On-Repair','Morning Log','Bt Trip Dir Chg','End Boat Charter Out', 'Begin Boat Charter Out', 'Begin Boat Charter In')
insert into #TmpEvents
select null,BoatPositionID, Description, PositionDateTime, Direction, river, mile
from mtsBoatPosition (NOLOCK)
where PositionDateTime > @.EventMin
and BoatID = @.BoatID
select * from #TmpEvents Order By EventDateTime
drop table #TmpEvents
END
Linda, you can either change the stored procedure definition in the script generated by the snapshot agent to the following:
CREATE PROCEDURE [dbo].[spBTG_GetEventsSince](@.EventMin datetime, @.BoatID int) AS
BEGIN
create table #TmpEvents
(
BoatHistoryID int null,
PositionID int null,
Event varchar(50) null,
EventDateTime datetime null,
Direction char(1) null,
River char(3) null,
Mile numeric(6,2) null
)
insert into #TmpEvents
select bh.BoatHistoryID, null, Event, EventDateTime, Direction, riverloc, mileloc
--jds 6/22/05 hardcoded index to fix it when you call it from vb
-- from mtsEventHistory eh (NOLOCK)
-- left join mtsBoatHistory bh (NOLOCK) on bh.EventID = eh.EventID
from mtsEventHistory eh with (NOLOCK, index(PK_MtsEventHistory))
left join mtsBoatHistory bh with (NOLOCK, index(IDX_MtsBoatHistory_BoatID)) on bh.EventID = eh.EventID
where EventDateTime > @.EventMin
and BoatID = @.BoatID
and voidflag = 0
And eh.Event IN ('Pickup','Drop','Log Exchange','Off-Layup','Off-Repair','On-Layup','On-Repair','Morning Log','Bt Trip Dir Chg','End Boat Charter Out', 'Begin Boat Charter Out', 'Begin Boat Charter In')
insert into #TmpEvents
select null,BoatPositionID, Description, PositionDateTime, Direction, river, mile
from mtsBoatPosition (NOLOCK)
where PositionDateTime > @.EventMin
and BoatID = @.BoatID
select * from #TmpEvents Order By EventDateTime
drop table #TmpEvents
END
Or, you can change the compatibility level of your subscriber database to 80 or lower.
-Raymond
|||
Raymond,
This sp currently runs on SQL2000. However, before we implement replication, it will be running on 2005. Is the change "adding with" something that will be required for SQL 2005 or does it have to do with replication?
Linda
|||
The use of the "with" keyword with query hints is a SQL2005 requirement that is not directly related to replication. As I mentioned before, you can always change the dbcmptlevel of your subscriber database to 80 or lower so the old syntax in your procedure can be accepted.
-Raymond
|||
Raymond,
On the subscriber database, do I run this command:
EXEC sp_dbcmptlevel MMS_20061213, 80;
to change the cmptlevel?
I did that and now I am getting this error:
Command attempted:
/* ============================================================ */
/* View: vMcsHeaders */
/* ============================================================ */
CREATE VIEW "dbo"."vMcsHeaders" AS
/* VIEW: vMcsHeaders
ABSTRACT: Used by the Contract System to work with contract headers
AUTHOR DATE
EJB 3/31/98 Created
SRM 4/8/98 - Added Customer_Code
EJB 5/1/98 - added filter for only contracts that are not deleteme = 1
EJB 6/10/98 - Ad
(Transaction sequence number: 0x00046CEA0000F21D005600000000, Command ID: 3385)
Error messages:
Invalid object name 'McsDemurrageSetUp'. (Source: MSSQLServer, Error number: 208)
Get help: http://help/208
Invalid object name 'McsDemurrageSetUp'. (Source: MSSQLServer, Error number: 208)
Get help: http://help/208
I notice the McsDemurrageSetUp is it referencing is spelled McsDemurrageSetup. How do I track down what kind of error this is? (Perhaps, I did not get the compatibility set correctly to 80?)
|||
It would appear that you are replicating from a case-insensitive database to a case-sensitive database, this is not a supported scenario.
|||
Raymond,
If I run the command: sp_server_info, both databases say "MIXED" under identifier case. However, I did figure out the problem. The storedprocedure is referencing a table that was not replicated because it did not have a primary key. I did not notice that initially when I noticed the case was different. The database I am working with needs a lot of work before it can successfully be replicated!
Thanks,
Linda
