mssql_execute : stored procedure execution failed

[AnotherPuterGuy]

Hey all,

 

This is my first post and I'm pretty much a PHP n00b, so please bear with me...

 

I'm running php 5.2.6 on Ubuntu 9.04 trying to execute an SP on a Windows 2003 server running SQL Server 2005 (full, not express).

 

My test script is as follows...

 

// Set up database parameters

$dbserver="sql_server_ip";

$dbuser="username";

$dbpw="password";

$db="databasename";

 

// Connect to server and select database.

$conn=mssql_connect($dbserver, $dbuser, $dbpw) or die("GDI: Unable to connect to db server $dbserver");

$database=mssql_select_db($db,$conn) or die("GDI: Unable to connect to database $db");

 

$sql = mssql_init('SchemaName.ProcedureName',$conn);

$retval = mssql_execute($sql);

mssql_close($conn);

echo "\n" . mssql_get_last_message() . "\n";

 

...and I get...

 

Warning: mssql_execute() [function.mssql-execute]: stored procedure execution failed

Changed database context to 'databasename'.

 

What is working:

  - mssql_query("select * from SchemaName.TableName");

  - mssql_query("exec SchemaName.ProcedureName");

 

The test SP is simply doing a select from SchemaName.TableName (no input parameters). I've verified that the user has execute permissions on the SP and when connecting to the database server as that user with SQL Mgmt Studio, the user can exec the SP. Connecting as SA has the same results as does giving this user db_owner permissions.

 

Reproducing the issue:

  - clean installation of Ubuntu 9.04 w/ all available updates

  - apt-get -y install apache2

  - apt-get -y install php5 libapache2-mod-php5

  - Test to make sure we are okay to this point (e.g. phpInfo()

  - aptitude install php5-mssql

  - restart apache2

  - Note: no mods to any conf or ini files

  - create the above php script

  - test it with mssql_query and watch it work

  - convert it to an mssql_execute statement and watch it fail.

 

Am I missing some configuration option somewhere that would allow mssql_query to exec the procedure, but not mssql_execute?

 

Thanks in advance...

 

 

 

[AnotherPuterGuy]

Figured it out...

 

In researching the problem before posting I saw references to configuration changes needed to FreeTDS, but apparently I was implementing the change wrong, but that is the solution.

 

  - Edit /etc/freetds/freetds.conf

  - Edit the section for MSSQL server

    - Replace the given section name with something meaningful (we'll say MyServer for this example)

    - Enter the IP and port (1433)

    - Change the version to 8.0

  - Restart Apache

  - When connecting to the server (the $conn statement in my example), use the section name that you used in the conf file (MyServer)

    - This is the part I was missing when trying to figure out the problem before I posted. I edited the conf file, but was not using the section name to connect.

  - Executing the SP will now work.

 

I've testing this on a clean Ubuntu 9.04 installation following the "reproducting the issue" instructions I provided above and it works like a charm.

 

Argh...I'd like those hours of my life back now...