May 11, 2010 at 5:23 am
hi,
we are using transactional replication on an sql 2008 server. we try now to add a table to the replication that only consists of 1 culomn of type date and we insert the dates to the table. when we then create the replication we are getting an error on the creation of the snapshot.
here is the create table with the insert dates:
USE [Product]
GO
/****** Object: Table [TimeSlices].[BaseDate] Script Date: 05/03/2010 15:52:24 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TimeSlices].[BaseDate]') AND type in (N'U'))
DROP TABLE [TimeSlices].[BaseDate]
GO
USE [Product]
GO
/****** Object: Table [TimeSlices].[BaseDate] Script Date: 05/03/2010 15:52:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [TimeSlices].[BaseDate](
[TargetDate] [date] NOT NULL,
CONSTRAINT [PK_BaseDate] PRIMARY KEY CLUSTERED
(
[TargetDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [TimeSlices].[BaseDate] SELECT TargetDate FROM [TimeSlices].UDF_GetTravelDateRange(GETDATE()-2 , GETDATE()+1000)
attached is the UDF.
this is the add article:
use [Product]
exec sp_addarticle @publication = N'Product$(QuoteNumber)', @article = N'BaseDate', @source_owner = N'TimeSlices', @source_object = N'BaseDate', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'BaseDate', @destination_owner = N'TimeSlices', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_TimeSlicesBaseDate', @del_cmd = N'CALL sp_MSdel_TimeSlicesBaseDate', @upd_cmd = N'SCALL sp_MSupd_TimeSlicesBaseDate'
GO
this is the error we get:
Message: Query for data failed
Stack: at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowNativeBcpOutException(CConnection* pNativeConnectionWrapper)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strBaseBcpObjectName, Boolean fUnicodeConversion, String strDataFile, String strLoadOrderingHint, String strWhereClause, Boolean useTableLockHint, Int32 bcpFileFormatVersion)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.BcpOutThreadProvider.DoWork(WorkItem workItem)
at Microsoft.SqlServer.Replication.WorkerThread.NonExceptionBasedAgentThreadProc()
at Microsoft.SqlServer.Replication.AgentCore.BaseAgentThread.AgentThreadProcWrapper() (Source: MSSQLServer, Error number: 0)
Get help: http://help/0
Message: Conversion failed when converting date and/or time from character string.
Stack: (Source: MSSQLServer, Error number: 241)
Get help: http://help/241
does anybody know why we are getting the conversion error on the snapshot creation?
thanks,
Chris
May 11, 2010 at 8:45 am
are you replicating between databases on the same server or to another server?
The probability of survival is inversely proportional to the angle of arrival.
May 11, 2010 at 9:06 am
for development and unit testing we are replicating on the same server.
for QA and production we are replicating between 2 or more servers then.
regards,
Chris
May 11, 2010 at 1:20 pm
A stupid question, are you replicating between the same SQL Server versions? The reason why I ask is that DATE is a new data type introduced in 2008. If it is being replicated to 2005 or 2000, then you have to map the column correctly with the right data type.
-Roy
May 12, 2010 at 12:26 am
yes, i am replicating between sql2008 and sql2008.
regards,
May 12, 2010 at 10:07 am
this seems to be a bug in replication snapshot creation. we ended up changing the table to
CREATE TABLE [TimeSlices].[BaseDate](
[PK_Integer] [int] IDENTITY(1,1) NOT NULL,
[TargetDate] [date] NOT NULL,
CONSTRAINT [PK_BaseDate] PRIMARY KEY CLUSTERED
(
[PK_Integer] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_BaseDate] UNIQUE NONCLUSTERED
(
[TargetDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
and now we can replicate the dates.
December 13, 2010 at 10:15 am
I appreciate that the originators issue has been worked around by changing the table but what if this is not possible? Does anyone have an alternate solution to this.
I too have a table with a date in the primary key and the same issue with snapshot generation. I probably will go down the same route with adding a surrogate key but this is not ideal.
Will this be something that MS will fix anytime soon?
Thanks
Dave
David Bridge
David Bridge Technology Limited
www.davidbridgetechnology.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply