Wsrtjtyk

I'm having a bit of of trouble writing a php script which needs to use both a remote MS SQL database and a local MySQL database.
The MS SQL database belongs to a support ticket system from which I'm only reading data. The local MySQL database is used to store additional information about the support tickets.

My problem concerns the Swedish characters å, ä and ö. In both databases, they are all stored correctly which can be seen when browsing the tables with a DB GUI like HeidiSQL.

But when I'm fetching data from each db, the charters are displayed differently.

Here's an example

<?php



//header('Content-Type: text/html; charset=utf8mb4');

header('Content-Type: text/html; charset=utf8');



$dbaddress ="localhost";

$dbuser="user_name";

$dbpass='user_password';



$dbh = mysqli_connect($dbaddress,$dbuser,$dbpass,'ism') or die(mysqli_error());



mysqli_query($dbh,"SET NAMES utf8");

mysqli_set_charset($dbh,"utf8");



$query="SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8')";

$result = mysqli_query($dbh, $query);



$query = "SELECT * FROM tickets WHERE partner ='Dia Falun & Västerås AB, Dialect' limit 1";

$result = mysqli_query($dbh, $query);



while($partner = mysqli_fetch_array($result)){  

    $partnername=$partner['partner'];

}

print "<p>$partnername</p>";

print "<p>Detected encoding: ".mb_detect_encoding($partnername)."</p>";



$user = 'user_name';

$pass = 'user_password';

$server = 'server_address';

$database = 'db_name';



$CharacterSet = "utf8";

//Upprätta anslutningen

$connection_string = "DRIVER={SQL Server};SERVER=$server;DATABASE=$database;CharacterSet=$CharacterSet"; 



$ism = odbc_connect($connection_string,$user,$pass);



$query="SELECT sitename FROM Incident WHERE incidentnumber=415521";



    $result = odbc_exec($ism, $query) or die(odbc_errormsg());



    while(odbc_fetch_row($result)){        

        $partner=odbc_result($result,'sitename');



    }



print "<p>$partner</p>";

print "<p>Detected encoding: ".mb_detect_encoding($partner)."</p>"



?>

The thing I don't understand is regarding the headers. At the beginning of my script I specify the charset, if I set it to utf8 then the characters from the MySQL db are displayed correctly, but the ones from the MS SQL are just displayed as "�".
However, if I set the charset to utf8mb4, then it's the other way around. Characters from the MS SQL db are correct, while those from the MySQL db are displayed as "ä", "å" depending on the character.

The collation in the local MySQL db is set to "utf8_bin". I've tried to change it to "utf8mb4_bin" too, but it made no difference.

I think it may have something to do with the result I get when i test the encoding for each string with mb_detect_encoding()
For the local db I get utf8, but with the string returned from the MS SQL database, there is no result.

If necessary, I can only change the collation for the local database, I don't have admin access to the MS SQL database.

Can anyone please help?