Trying to execute SQL through OPENQUERY statement to linked MySQL server as a test
Interestingly the following openquery statement works:
SELECT * FROM OPENQUERY([MYSQL], 'SELECT REPLACE(description,''/'','''') as description2, length(description) as DescriptionLength, length(example) as ExampleLength FROM `mysql`.`help_topic` ORDER BY `name`')
However, when added as part of the BCP output statement:
declare @sql varchar(8000)
select @sql = 'bcp "SELECT * FROM OPENQUERY([MYSQL], ''SELECT REPLACE(description,''/'','''') as description2, length(description) as DescriptionLength, length(example) as ExampleLength FROM `mysql`.`help_topic` ORDER BY `name`'')" queryout "C:\TEST_MySQL\NEW_TOO.txt" -c -t"\"~\"" -r"\"\n\"" -T -S'+ @@servername
exec master..xp_cmdshell @sql
It no longer works!
Error = [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near '/'. SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC Driver 11 for SQL Server]Unable to resolve column level collations BCP copy out failed
That same BCP statement above works fine when the REPLACE(description,''/'','''') as description2 section is replaced with * on the inner select…!
Anybody have any ideas why this is occurring?