Try to generate the CSV file in UTF-8 format using SSIS (if code page 65001 is not supported by the SQL version).

1. Create a SSIS package
2. Add a data flow task
   • Add an OLE DB source > fetch data from the SQL table
   • Add a flat file destination (CSV)
3. Call the SSIS package via an SQL agent job
4. Execute the above job inside your script or Stored Procedure (SP)