April 17, 2008 at 8:11 pm
I'm a bit confused and if someone can help me out that would be great!
I have a table called : repl_test with 1 column of varchar(20)
This database is replicated from serverA to serverB. I would insert 1 row into this repl_test table to test whether or not replication will successfully sent the data over. Instead I get the error:
Error converting data type varchar to bigint.
The table is exactly the same on both servers. Any idea what might be causing this?
This is a push transactional replication btw.
Thanks.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
April 18, 2008 at 1:07 am
Hi Oberion
I tried all possible way to generate error that you are getting but could not do so untill I changed my data type.
I even tried setting "convert data type" properties of particular article but could not generate it.... 🙁
I can only suggest you to re-verify your table structure at both end.
[font="Verdana"]--www.sqlvillage.com[/size][/font]
April 18, 2008 at 7:13 am
Replication uses stored procedures to do inserts, updates, and deletes on the subscriber. Check the procedures for this table to make sure they do not have data type problems.
April 18, 2008 at 10:35 am
Hi,
Thanks for looking into it for me. I have verified that both tables are the same. I used a script and ran it on both servers.
I just went and generate the create table code and it looks like:
CREATE TABLE [dbo].[TransRepl_Test](
[testcol] [varchar](20) NOT NULL,
CONSTRAINT [PK_TransRepl_Test] PRIMARY KEY CLUSTERED
(
[testcol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
and serverB looks like this:
CREATE TABLE [dbo].[TransRepl_Test](
[testcol] [varchar](20) NOT NULL,
CONSTRAINT [PK_TransRepl_Test] PRIMARY KEY CLUSTERED
(
[testcol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I'm also just doing a straight up insert statement:
insert into transrepl_test
select 'hello'
After today's testing, I'm going to refresh the stage environment with the database from Production. Hopefully that will fix it because it worked just a few days ago.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
April 23, 2008 at 4:10 pm
omg, I just figured this out!
a noobie mistake, I can't believe it!
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
April 25, 2008 at 2:04 pm
Details? for the rest of us noobies? a problem with your primary key?
April 25, 2008 at 7:43 pm
I had that table with varchar(20) on both servers. I also had other tables, one of them is named 'attribute'.
when I originally scripted it out, every table had it own little section of sp_addArticle. Within the sp_addArticle, I was using:
@ins_cmd = 'dbo.TableName'
@del_cmd = 'dbo.TableName'
@upd_cmd = 'dbo.TableName'
When I went and made it into a while loop, I had everything set correctly except for those 3 commands which were
@ins_cmd = 'dbo.attribute
@del_cmd = 'dbo.attribute'
@upd_cmd = 'dbo.attribute'
so when trying to ins, del, or upd, it was trying to do it against the attribute table.
that's why when I performed my regular INSERT statement, it wrote to the test table on the local server correctly but when it tried to replicate it, it was trying to write to the attribute table which has an ID column of bigint.
It was right in my face when looking at the profiler but took me a few days to really see it.
To fix it, I went into my while loop and removed those 3 statements since it is setup by default anyways and everything works like a charm.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply