SQL server 2014 replication error

hi,

right now configuration some test on SQL server 2014 transaction replication and we receive the following error:

Error messages:

Error messages:
•	The process could not bulk copy into table '"dbo"."<tableName>"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)
Get help: http://help/MSSQL_REPL20037
•	Batch send failed
•	OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].Remark'. (Source: MSSQLServer, Error number: 7339)
Get help: http://help/7339
•	To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below.  Consult the BOL for more information on the bcp utility and its supported options. (Source: MSSQLServer, Error number: 20253)
Get help: http://help/20253
bcp "FLHAECO"."dbo"."<tableName>" in "\\HXDECOUATDBS01\WorkingDir\unc\HXDECOUATDBS01_HXDB_HMS_WMS\20170314093312\<tableName>_11#27.bcp" -e "errorfile" -t"\n<x$3>\n" -r"\n<,@g>\n" -m10000 -SHXTWMSUATDB -T -w (Source: MSSQLServer, Error number: 20253)
Get help: http://help/20253

Open in new window

'

it seems that the mentioned error was due to the collation difference in source and destination DB and continuously happen in production environment as well.

I knew the diff in collation can't be solve and it involve reinstall of the DB server, right?

Are there any methods ton bypassing the those columns with different collation in replication ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Máté FarkasDatabase Developer and AdministratorCommented:
Did you try to execute that command?

bcp.exe "FLHAECO"."dbo"."<tablename>" in "\\HXDECOUATDBS01\WorkingDir\unc\HXDECOUATDBS01_HXDB_HMS_WMS\20170314093312\<tableName>_11#27.bcp -e "errorfile" -t"\n<x$3>\n" -r"\n<,@g>\n" -m10000 -SHXTWMSUATDB -T -w

Open in new window


What is the error message from bcp ?
0
ZberteocCommented:
You can't have replication between databases with different collations. Actually all databases involved has to have the same collation, publication, distribution and subscriber. You will have to at least change the destination database to the same collation.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"You will have to at least change the destination database to the same collation."

but this one involve reinstall of the DB server, right?

"You can't have replication between databases with different collations"

someone discussed about the by pass option by using with ‘copy collation = True', right?

so if the target DB ran for a long time really has diff collation, what should we do other than reinstall it?

and if target DB is in diff country, language can be diff and it is normal. anyway to handle this kind of mis match ?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Vitor MontalvãoMSSQL Senior EngineerCommented:
I knew the diff in collation can't be solve and it involve reinstall of the DB server, right?
Depends. If this is only intended to be for a single column or a table you can easily change the collation for the object so not necessary to reinstall the SQL Server instance.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"you can easily change the collation for the object "

now we are going to change the schema of that target table and see if it works.

"you can easily change the collation for the object "

that one is a table, how to change the collation ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You just need to change the collation for the column. Something like:
ALTER TABLE tablename
    ALTER COLUMN columnname NVARCHAR(100) COLLATE newcollationname

Open in new window

0
marrowyungSenior Technical architecture (Data)Author Commented:
ok, tks. the subscriber's table with replication turned on, it has to be disabled the replication first ?

your script just changed the collation of one columns only and it don't impact the rest  ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
the subscriber's table with replication turned on, it has to be disabled the replication first ?
You may need to stop the replication so you can perform the collation change.

your script just changed the collation of one columns only and it don't impact the rest  ?
Correct. It only changes that single column collation.
0
marrowyungSenior Technical architecture (Data)Author Commented:
hi,

"Correct. It only changes that single column collation."

one thing, how can I detect such a change if no documentation is made.... any script for me to detect all column's collation for any exception?  is it possible ?

I am thinking about this can make the next DBA in trouble !
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
one thing, how can I detect such a change if no documentation is made.... any script for me to detect all column's collation for any exception?  is it possible ?
What do you mean? Do you want to track all collation changes that has been made? Only way is to create a DDL trigger and store the information somewhere so you can analyze it later.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Do you want to track all collation changes that has been made?"
e.g. which column has diff collation and then we will find out why .
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
which column has diff collation and then we will find out why .
Diff collation from what? From the other columns? table? database? instance? Or just when the collation of the column changes? If this latest then use a trigger as I've mentioned earlier.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Diff collation from what? From the other columns?"

diff from other columns . e.g. if we just change collation of one column, by someone, we should be able to detect it out and see who did that and why .
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Like I said before, only if you have a DDL trigger to capture all changes performed in objects.
0
marrowyungSenior Technical architecture (Data)Author Commented:
yes, totally understand.
0
marrowyungSenior Technical architecture (Data)Author Commented:
tks all for that, I might come back next monday as we tried to change the schema of that target table but it seems don't work,
0
marrowyungSenior Technical architecture (Data)Author Commented:
hi all,

we tried to recreate the table schema with the same definition, but diff errors comes:

Error messages:
The process could not bulk copy into table '"dbo"."HMS_ClassNomination"'. (Source: MSSQLServer, Error number: 20037)
Get help: http://help/20037
Batch send failed
OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].Remark'. (Source: MSSQLServer, Error number: 7339)
Get help: http://help/7339
To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below.  Consult the BOL for more information on the bcp utility and its supported options. (Source: MSSQLServer, Error number: 20253)
Get help: http://help/20253
bcp "FLHAECO"."dbo"."HMS_ClassNomination" in "\\<DB server>\WorkingDir\unc\<DB server>_HXDB_TESTING\20170323155940\HMS_ClassNomination_2#27.bcp" -e "errorfile" -t"\n<x$3>\n" -r"\n<,@g>\n" -m10000 -SHXTWMSUATDB -T -w (Source: MSSQLServer, Error number: 20253)
Get help: http://help/20253

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Better to you to open a new question since you'll attract more Experts.
0
marrowyungSenior Technical architecture (Data)Author Commented:
but already answering here, right ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you run the suggested command?
bcp "FLHAECO"."dbo"."HMS_ClassNomination" in "\\<DB server>\WorkingDir\unc\<DB server>_HXDB_TESTING\20170323155940\HMS_ClassNomination_2#27.bcp" -e "errorfile" -t"\n<x$3>\n" -r"\n<,@g>\n" -m10000 -SHXTWMSUATDB -T -w

Open in new window

Then check the errorfile.
0
marrowyungSenior Technical architecture (Data)Author Commented:
hi,

right now I found out other problems.

1) if publisher and distributor is SQL2014 and subscriber still in SQL2012, will it works ?
2) when i test using one table, when I setup a simple transaction replication, it give me this alert and replication can't continue:

error
3) I can't see why if, I found some existing replication has snapshot folder not accessible:

distributor trouble
then can't setup transaction replication at all as no folder to store the snapshot?

it seems from create replication wizard I can't see where to set to store snapshot, how can i set it?

4) is this means the distributor can be create/enable/disable in anytime, it is just a feature can be on/off ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
hi Victor,


"Did you run the suggested command?"

it has error as it said:

" The system cannot find the file specified"

so that path :\\<DB server>\WorkingDir\unc\<DB server>_HXDB_TESTING\20170323155940\ is supposed to be snapshot files and now by some reason some process remove it, so this command doesn't works anyway.

where can I set the snapshot path of transaction replication.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
where can I set the snapshot path of transaction replication.
Check in the Replication properties.
0
marrowyungSenior Technical architecture (Data)Author Commented:
hi,

how about this:

4) is this means the distributor can be create/enable/disable in anytime, it is just a feature can be on/off ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
I am checking this one:

"https://blogs.msdn.microsoft.com/repltalk/2015/03/04/repltip-only-one-log-reader-can-connect-message-when-dropping-a-publication/"

Sometimes when you try to drop a Transactional Publication using the GUI, you get the following error:
Only one Log Reader Agent or log–related procedure (sp_repldone, sp_replcmds, and sp_replshowcmds) can connect to a database at a time. If you executed a log–related procedure, drop the connection over which the procedure was executed or execute sp_replflush over that connection before starting the Log Reader Agent or executing another log–related procedure.

but I can't see I can see this option:

"r by right-clicking the Publication, select "View Log Reader Agent Status", then "Stop","

how come ?

I have removed all test publication from Replication-> local publications already, nothing there.
0
marrowyungSenior Technical architecture (Data)Author Commented:
Victor:

"ALTER TABLE tablename
    ALTER COLUMN columnname NVARCHAR(100) COLLATE newcollationname"

from server level I see both publisher, distributor and subscriber has the same collation. but it seems doesn't work.

I am not sure if replication replicating data from higher version (SQL2014) to lower version (SQL2012) is supported.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I've missed your big comment with images.
1) if publisher and distributor is SQL2014 and subscriber still in SQL2012, will it works ?
Yes, it should work.

2) when i test using one table, when I setup a simple transaction replication, it give me this alert and replication can't continue:
Looks like you have a big mess there. I'm not sure if I can help without having access to your system.

then can't setup transaction replication at all as no folder to store the snapshot?
Any Replication needs a snapshot to begin replication so yes, is a mandatory configuration.

4) is this means the distributor can be create/enable/disable in anytime, it is just a feature can be on/off ?
If you stop the distributor agent you'll stop the Replication.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I have removed all test publication from Replication-> local publications already, nothing there.
How did you remove it? I guess something stayed and it's originating all this mess. Check the SQL Agent jobs and verify if there's any from Replication running.
It's also a good practice to script your Replication so you can rebuild it later, easily.
0
marrowyungSenior Technical architecture (Data)Author Commented:
":How did you remove it? I guess something stayed and it's originating all this mess. Check the SQL Agent jobs and verify if there's any from Replication running."

other stuff removing by right click on it and delete. it is a UAT platform.

"If you stop the distributor agent you'll stop the Replication."

I can find it from windows service ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
other stuff removing by right click on it and delete.
It might be not enough. Once again, you can always generate a delete script for later use.

I can find it from windows service ?
No. Isn't a service but a SQL Agent job but you should interact with it by using the Replication Monitor.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"No. Isn't a service but a SQL Agent job but you should interact with it by using the Replication Monitor.

sorry, this is the turn distributor on and off?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
sorry, this is the turn distributor on and off?
Yes but I wouldn't do that unless you need to perform some maintenance task.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Yes but I wouldn't do that unless you need to perform some maintenance task.

sorry, what ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
turn distributor on and off
0
marrowyungSenior Technical architecture (Data)Author Commented:
ok. I will double check next week.

tomorrow will update AOG ticket. :):)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.