Framework: Laravel 5.2 and mssql TDS stream error

[omadoyeabraham2]

Hi all,

I am trying to connect to an mssql 2008 database from a laravel5.2 app and i keep getting this error

SQLSTATE[HY000]: [Microsoft][SQL Server Native Client 11.0]Protocol error in TDS stream

I am able to connect to the DB from a regular php script, the issue occurs when i try using it in my laravel app.

Please help, as i am still relatively new to laravel.

[michaelachrisco]

This may be many things. Can you give us a code sample (without the server info of course).
I use 2008-2016 and had to create this model trait in order to get SQL server 2008 working:

//app/ModelTraits/MSServerTrait.php
namespace App\ModelTraits;
trait MSServerTrait {
  protected static $carbonFields = [];
   public function getDates(){
    return array();
   }
}
//how to use

//app/FooModel.php
namespace App;
use Illuminate\Database\Eloquent\Model;
use App\ModelTraits\MSServerTrait;
class FooModel extends Model {
  use MSServerTrait;
  public $table = 'FooModel';
}

//config/database.php
...
        'production' => [
            'driver'   => 'sqlsrv',
            'host'     => env('DB_HOSTNAME'),
            'database' => env('DB_DATABASE'),
            'username' => env('DB_USERNAME'),
            'password' => env('DB_PASSWORD'),
            'port' => '1433',
            'charset'  => 'utf8',
            'prefix'   => '',
        ],
...

It may/may not help depending on your setup. Might also be in your freetds.conf file.

[michaelachrisco]

Is this still an issue @omadoyeabraham?

[omadoyeabraham2]

Hi @michaelachrisco very sorry that i forgot about this. Thankfully it's no longer an issue.

[webspeaks]

@omadoyeabraham I am facing similar issue. How did you solve it?

[emilas44]

Me too, same issue! How to fix this?

[ArielPremiumSupply]

It is still an issue for me.
SQLSTATE[HY000]: [unixODBC][Microsoft][ODBC Driver 13 for SQL Server]Protocol error in TDS stream

I get this error on Windows dev or on Linux environment.
Closed while no solution was posted? Favorite pet peeve on help discussions.

[michaelachrisco]

If I remember correctly, I was able to get the above working by installing the correct driver for the Ubuntu boxes and the model trait. I know that is not a whole lot to go on, but I have moved on from SQL server 08. MS SQL server 12 and 16 were working out of the box last I checked.

[ArielPremiumSupply]

Thank you michaelachrisco, I made sure that I had the latest drivers installed on both platforms:
https://github.com/Microsoft/msphpsql/releases/tag/v4.3.0
I tried the model trait as specified above, but still get the error. I am using Laravel 5.5 however.

I may have to build some custom service that exists in my public folder outside of the Laravel structure if I cannot get it working as my secondary DB connection within Laravel, because I know a regular PDO DB connection is working that route.

[ArielPremiumSupply]

My model:

namespace App;
use Illuminate\Database\Eloquent\Model;
use App\ModelTraits\MSServerTrait;

class OrderCustomerPO extends Model
{
use MSServerTrait;
protected $table = 'orders';
public $primaryKey = 'order_id';

}

The Controller

namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\OrderCustomerPO;
use DB;

class OrderStatusController extends Controller
{
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{

    $orders = DB::connection('sqlsrv')
    ->table('orders')
    ->select('order_id')
    ->where('customer','=', '102905')
    ->paginate(12);

Thing is, I am actually using DB instead of the Model as I am not sure how to tie that model to the secondary connection.

I confess I know not much about setting up secondary connections as such.

• Brian

[michaelachrisco]

@ArielPremiumSupply On your controller, what happens when you try to do: $orders = App\OrderCustomerPO::paginate(12); Do you get the same result?
IE SQLSTATE[HY000]: [unixODBC][Microsoft][ODBC Driver 13 for SQL Server]Protocol error in TDS stream

And no worries. If you still cannot get the above working, perhaps we should try to build a test repo and test via travis or some other service? With all that free time....lol

[michaelachrisco]

@ArielPremiumSupply I should also ask, what version of MSSQL you are using?

[ArielPremiumSupply]

@michaelachrisco

MSSQL: Using 2008 R2 Standard

From the below I get a zero count on records from both queries but no error message

namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\OrderCustomerPO;
use DB;

class OrderStatusController extends Controller
{
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{

// Query 1
$orders = OrderCustomerPO::paginate(12);
echo count($orders);

// Query 2
$db_ext = \DB::connection('sqlsrv');
$db_ext->OrderCustomerPO = new OrderCustomerPO;
$orders = $db_ext->OrderCustomerPO::all();
echo count($orders);

I appreciete the help so much. Yes, lol, free time... look at it all... its everywhere...

And have not met Travis yet... Have to go check it out. https://travis-ci.org/ I assume
^__^

• Brian

[michaelachrisco]

What do you get if you do:
OrderCustomerPO::all()->toSql();

I'm wondering if the sql is incorrect somewhere....like the migrations? I'm assuming you have records in your orders table?

Travis is nice in that its free and works off of one travis.yml file. Spins up a container that does a bunch of CI tools. There is other services but my goto is travis. 👍

[ArielPremiumSupply]

BadMethodCallException

Method toSql does not exist.

in Macroable.php (line 74)

Ahhhh, I lie, It is Laravel 5.4.36

Yes, there are like 600,000+ records
And it is a pre-existing table that I would not migrate with Laravel. I created a model, but no migration.

[michaelachrisco]

Huh I would have sworn there was a toSql method on Laravel? Example: https://stackoverflow.com/questions/22488171/using-laravels-tosql-on-queries-using-with-clause

How about: OrderCustomerPO::take(5)->toSql();?
OR

$db_ext->OrderCustomerPO = new OrderCustomerPO;
echo($db_ext->take(5)->toSql());

I am rusty on my eloquent queries!

Was the table something that you set up in Laravel or an existing table?

[ArielPremiumSupply]

It came down to a problem with the .env files and the config/database.php.
I ignored the env function and thought I was hard coding the values when the env values preceeded the hard coded values and were wrong.

'sqlsrv' => [
'driver' => 'sqlsrv',
'host' => env('DB_HOST_SECOND', '0.0.0.0'),
'port' => env('DB_PORT_SECOND', '1234'),
'database' => env('DB_DATABASE_SECOND', 'main_db'),
'username' => env('DB_USERNAME_SECOND', 'user'),
'password' => env('DB_PASSWORD_SECOND', '123abc'),
'charset' => 'utf8',
'prefix' => '',
],

I originally had these values which referred to the main MySQL server config values.

'sqlsrv' => [
'driver' => 'sqlsrv',
'host' => env('DB_HOST', '0.0.0.0'),
'port' => env('DB_PORT', '1234'),
'database' => env('DB_DATABASE', 'main_db'),
'username' => env('DB_USERNAME, 'user'),
'password' => env('DB_PASSWORD', '123abc'),
'charset' => 'utf8',
'prefix' => '',
],

slaps palm to forehead...
Strangely, I still do get the error saying toSql() is not a valid function.

[michaelachrisco]

Well its good that the issue is fixed! .env files are important 😄 Its quite possible the framework (and toSQL) has changed since I used it. GL!

[91Abdullah]

Possible port issue change port to 1433 in .env

[panzer-planet]

I thought I'd mention, for me it happened the first time and I searched for the meaning of the error before trying to simply load the page again. For some reason I got this error only on the first attempt. Remember guys, random failures do happen! Always try twice :)

[judgej]

I just got here due to this same error. Reason was the wrong port - I had copied some config for MySQL so was trying to talk to port 3306. Just commented that out and left the Laravel driver to use tis default.

[lxandrexl]

Possible port issue change port to 1433 in .env

Thanks it works for me !