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
'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.
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.
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.
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.
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
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
Then check the errorfile.
where can I set the snapshot path of transaction replication.Check in the Replication properties.
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.
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.
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.
sorry, this is the turn distributor on and off?Yes but I wouldn't do that unless you need to perform some maintenance task.
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 ?