Jump to content

mssql_execute : stored procedure execution failed


Recommended Posts

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...

 

 

 

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.