|
Home > Archive > MS SQL Server Replication > October 2006 > datatype conversion error in sql 2000 replication
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
datatype conversion error in sql 2000 replication
|
|
| archuleta37 2006-10-26, 12:14 am |
| I'm working on setting up replication on a database in SQL 2000 and get the
following error:
Implicit conversion from data type text to nvarchar is not allowed. Use the
CONVERT function to run this query.
(Source: MYBACKUPSERVER (Data source); Error number: 257)
Why would this occur? I don't understand why a field datatype would change
when doing replication. More importantly, I have no idea how to resolve this
issue and research hasn't helped much. Can anyone give me some insight on
where to start to fix this?
| |
| Hilary Cotter 2006-10-26, 12:14 am |
| It shouldn't be changing like this, can you post your schema or the problem
table?
--
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
"archuleta37" < archuleta37@discussi
ons.microsoft.com> wrote in message
news:F0B915F2-245D-4ECB-AC9D- CA5CA3D16AB6@microso
ft.com...
> I'm working on setting up replication on a database in SQL 2000 and get
> the
> following error:
>
> Implicit conversion from data type text to nvarchar is not allowed. Use
> the
> CONVERT function to run this query.
> (Source: MYBACKUPSERVER (Data source); Error number: 257)
>
> Why would this occur? I don't understand why a field datatype would change
> when doing replication. More importantly, I have no idea how to resolve
> this
> issue and research hasn't helped much. Can anyone give me some insight on
> where to start to fix this?
| |
| archuleta37 2006-10-26, 12:14 am |
| Hilary,
I'll have to check to see what I can give you from the schema, but here's
the last command that sql said it ran:
if (@@microsoftversion >= 0x080002C0) begin exec sp_addsynctriggers
N'PURGED_MGMT_TABLES
', N'null', N'DBSERVER', N'Accounting', N'Accounting',
N'sp_MSsync_ins_PURG
ED_MGMT_TABLES_1', N'sp_MSsync_upd_PURG
ED_MGMT_TABLES_1',
N'sp_MSsync_del_PURG
ED_MGMT_TABLES_1',
N'sp_MScft_Accountin
g_PURGED_MGMT_TABLES
', N'dbo', N'null', N'null', N'null',
0x000000100000000000
00000000000000000000
00000000000000000000
0000,
0,1,N'DBSERVER', 2 end
Transaction sequence number and command ID of last execution batch are
0x0001AA3A000001D500
D800000001 and 81.
"Hilary Cotter" wrote:
> It shouldn't be changing like this, can you post your schema or the problem
> table?
>
> --
> 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
>
>
>
> "archuleta37" < archuleta37@discussi
ons.microsoft.com> wrote in message
> news:F0B915F2-245D-4ECB-AC9D- CA5CA3D16AB6@microso
ft.com...
>
>
>
| |
| Raymond Mak [MSFT] 2006-10-26, 12:14 am |
| If your table has many columns, you are probably running into a limitation
in SQL2000 which is addressed in QFE build 8.00.2209. You can either contact
Microsoft support for the QFE fix (KB923563) or you can upgrade to SQL2005
which doesn't have this limitation out of the box.
-Raymond
"archuleta37" < archuleta37@discussi
ons.microsoft.com> wrote in message
news:21837EA5-B4B2-479F-8AF2- D7A42593F3DC@microso
ft.com...[color=darkred]
> Hilary,
> I'll have to check to see what I can give you from the schema, but here's
> the last command that sql said it ran:
>
> if (@@microsoftversion >= 0x080002C0) begin exec sp_addsynctriggers
> N'PURGED_MGMT_TABLES
', N'null', N'DBSERVER', N'Accounting', N'Accounting',
> N'sp_MSsync_ins_PURG
ED_MGMT_TABLES_1',
> N'sp_MSsync_upd_PURG
ED_MGMT_TABLES_1',
> N'sp_MSsync_del_PURG
ED_MGMT_TABLES_1',
> N'sp_MScft_Accountin
g_PURGED_MGMT_TABLES
', N'dbo', N'null', N'null',
> N'null',
> 0x000000100000000000
00000000000000000000
00000000000000000000
0000,
> 0,1,N'DBSERVER', 2 end
> Transaction sequence number and command ID of last execution batch are
> 0x0001AA3A000001D500
D800000001 and 81.
>
> "Hilary Cotter" wrote:
>
| |
| archuleta37 2006-10-28, 7:22 pm |
| That could be it, it might need more columns. I'll check as soon as I get a
chance. I tried to search for the kb article you reference but got no
results. Could you have a typo in the kb number? Could you post a url to read
the article?
"Raymond Mak [MSFT]" wrote:
> If your table has many columns, you are probably running into a limitation
> in SQL2000 which is addressed in QFE build 8.00.2209. You can either contact
> Microsoft support for the QFE fix (KB923563) or you can upgrade to SQL2005
> which doesn't have this limitation out of the box.
>
> -Raymond
>
> "archuleta37" < archuleta37@discussi
ons.microsoft.com> wrote in message
> news:21837EA5-B4B2-479F-8AF2- D7A42593F3DC@microso
ft.com...
>
>
>
| |
| archuleta37 2006-10-28, 7:22 pm |
| oops, I shouldn't post just before I'm going to bed. I meant to say "that
could be it, it might have too many columns" and in fact it turns out it does
have too many columns. It has 247 columns - way too many. I tried looking for
that article again but couldn't find it again - looking forward to reading it.
"archuleta37" wrote:
[color=darkred]
> That could be it, it might need more columns. I'll check as soon as I get a
> chance. I tried to search for the kb article you reference but got no
> results. Could you have a typo in the kb number? Could you post a url to read
> the article?
>
> "Raymond Mak [MSFT]" wrote:
>
| |
| Raymond Mak [MSFT] 2006-10-28, 7:22 pm |
| The bad part is I couldn't find the article on the public microsoft site
either (otherwise I would have posted the link to the QFE) so you would
probably need to contact Microsoft Support (which I know nobody likes to
do). I will see if I can dig up something.
-Raymond
"archuleta37" < archuleta37@discussi
ons.microsoft.com> wrote in message
news:4E800037-B152-445F-9898- 0FB6CB28E161@microso
ft.com...[color=darkred]
> oops, I shouldn't post just before I'm going to bed. I meant to say "that
> could be it, it might have too many columns" and in fact it turns out it
> does
> have too many columns. It has 247 columns - way too many. I tried looking
> for
> that article again but couldn't find it again - looking forward to reading
> it.
>
> "archuleta37" wrote:
>
| |
| Raymond Mak [MSFT] 2006-10-28, 7:22 pm |
| Looks like you have to contact Microsoft Support for the fix (and the KB),
it should be free though.
-Raymond
"archuleta37" < archuleta37@discussi
ons.microsoft.com> wrote in message
news:4E800037-B152-445F-9898- 0FB6CB28E161@microso
ft.com...[color=darkred]
> oops, I shouldn't post just before I'm going to bed. I meant to say "that
> could be it, it might have too many columns" and in fact it turns out it
> does
> have too many columns. It has 247 columns - way too many. I tried looking
> for
> that article again but couldn't find it again - looking forward to reading
> it.
>
> "archuleta37" wrote:
>
| |
| archuleta37 2006-10-28, 7:22 pm |
| OK - will do,
Thank you for your help Raymond
"Raymond Mak [MSFT]" wrote:
> Looks like you have to contact Microsoft Support for the fix (and the KB),
> it should be free though.
>
> -Raymond
>
> "archuleta37" < archuleta37@discussi
ons.microsoft.com> wrote in message
> news:4E800037-B152-445F-9898- 0FB6CB28E161@microso
ft.com...
>
>
>
| |
| archuleta37 2006-10-28, 7:22 pm |
| Raymond,
If we were able to create the initial snapshot, would this particular known
issue still occur when trying to replicate (but not when making the snapshot)
due to too many fields?
"Raymond Mak [MSFT]" wrote:
> Looks like you have to contact Microsoft Support for the fix (and the KB),
> it should be free though.
>
> -Raymond
>
> "archuleta37" < archuleta37@discussi
ons.microsoft.com> wrote in message
> news:4E800037-B152-445F-9898- 0FB6CB28E161@microso
ft.com...
>
>
>
| |
| Raymond Mak [MSFT] 2006-10-28, 7:22 pm |
| I am not sure if I understand your situation correctly but to be more
specific, this issue only affects the delivery of snapshot for transactional
publications that allow updateable subscribers. So to workaround without the
fix, you can:
1) Setup a "read-only" subscription
2) Use vertical partitioning to have fewer columns in your article if you
don't need them all at the subscriber
3) Change your publication to not allow updateable subscribers or simply put
the big table in a separate publication if you don't need update support for
that table at the subscriber
-Raymond
"archuleta37" < archuleta37@discussi
ons.microsoft.com> wrote in message
news:C4388F55-31A8-4B43-BE2D- 2279E1440345@microso
ft.com...[color=darkred]
> Raymond,
> If we were able to create the initial snapshot, would this particular
> known
> issue still occur when trying to replicate (but not when making the
> snapshot)
> due to too many fields?
>
>
> "Raymond Mak [MSFT]" wrote:
>
|
|
|
|
|