VS2012, Remote debugging SQL Server procedures called from a PHP app

Question

I am trying to debug stored procedure calls from within VS2012 (SSDT) when the calls are made from a PHP web app.

SQL Server and IIS are running on a separate machine in my workgroup (i.e. not domain)
I can manually debug remote stored procedures from my client machine using Visual Studio and the SQL Server Object Explorer
No firewall rules in place (both machines' firewall are disabled at the moment)
I am running VS, the SQL Server service, and the remote debugger all as the same Windows user name (in this case, "Administrator")

If I open a stored proc in VS, either from within my project or from the SQL Server Object Explorer, and set a breakpoint in that procedure, I want the debugger to halt on my breakpoint if that proc is called from my PHP web app.

I did see this post, but it doesn't get into specifics with VS2012 and also only references .NET app.

Is this kind of debugging even possible with a PHP app?

Dima Kurilo · Answer 1 · 2013-09-15T13:38:45.800

You can try to do something like:
1) Setup the remote dubugging tool to MSSQL Server machine:
http://msdn.microsoft.com/en-us//library/bt727f1t.aspx
2) Run the remote debugging monitor at MSSQL Server machine:
http://msdn.microsoft.com/en-us//library/xf8k2h6a.aspx
3) Open your database project and select: Debug - Attach to Process...
4) Browse, select your server.
5) Select sqlservr.exe and Attach
That's all, as I suppose.

Sample of code, that I used for testing:

//StoredProcedure sample
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void StoredProcedure1(string source)
    {
        SqlDataRecord tempRecord = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData("test", SqlDbType.VarChar, 50) });
        tempRecord.SetString(0, "teeeest");
        SqlContext.Pipe.Send(tempRecord); // here breakpoint with source=="php" condition
    }
};

//PHP code sample
<?php
$connectionInfo = array( "Database"=>"db", "UID"=>"sa", "PWD"=>"**************");
$link = sqlsrv_connect('server', $connectionInfo);

if( $link === false )
{
     echo "Could not connect.\n";
     die( print_r( sqlsrv_errors(), true));
}

$sql = " { call StoredProcedure1 ( ? ) } ";
  $param1 = 'php';
  $params = Array(
    Array(&$param1, SQLSRV_PARAM_IN)
  );
  $stmt = sqlsrv_query($link,$sql,$params);
  if ($stmt===false) {
    // handle error
    echo('1');
    print_r(sqlsrv_errors(),true);
  } else {
    print_r($stmt);
    sqlsrv_fetch( $stmt );
    print_r(sqlsrv_get_field( $stmt, 0));
  }

Thanks for the suggestion. I have done all this. See my original post - I am still unable to trigger breakpoints when running the app. — Shoeless, Sep 13 '13 at 19:51
@Shoeless, ugm, sorry. I don't understand you. You can add parameter to stored pocedure (like 'string source' or even 'string source="none"') and set it to "php", "myserver" or other in the exec lines. And set condition on the breakpoint (source=="php"). So your debuger halt only if condition is true. — Dima Kurilo, Sep 13 '13 at 22:07
Thanks Dimitry, but the issue is not related to conditional breakpoints. I want to set a breakpoint in a stored proceudre in VS, then run the PHP app in a browser, and pause at the breakpoint when the proc is called from the app. — Shoeless, Sep 15 '13 at 11:25
@Shoeless, if you attach to a process and PHP queries exactly this process, breakpoint always fire. I supposed that you want the breakpoint to fire only when a query is from php. Then you can add debug parameter to the query and set condition in VS with this parameter. — Dima Kurilo, Sep 18 '13 at 21:19

score -1 · Answer 2 · answered Sep 15 '13 at 06:39

I will go from different direction, try to debug the stored procedure from SQL Server Profiler, you can see the procedure call stack there and see what is the result of each stem of the procedure in addition you can add prints to the procedure and comment few parts (like lion in the desert), but it will give you what you aimed for also please refer to this question, it can help: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/de789aac-8077-44f1-8a93-311a25368095/trace-stored-procedure-through-sql-profiler

I am very familiar with the SQL Server Profiler. This does not address the issue of debugging stored procedures in VS. — Shoeless, Sep 15 '13 at 11:29

VS2012, Remote debugging SQL Server procedures called from a PHP app

2 Answers2