PHP & MSSQL Stored Procedures

[Lyleyboy]

Good morning all,

 

I am new to using PHP with MSSQL Server. I have a connection and I can run queries directly but now I'd like to run a stored procedure.

I have to admit to now knowing much about SP's at all.

 

To give a bit of background the SP just runs a query and returns * from an external database. The SP runs fine in SQL Server Managment Studio.

 

All I'd like to do is to return that result in PHP. I can refine the query in the SP later. At this time I have no parameters to pass.

 

My SP

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[usp_Vivantio_Assets]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;



SELECT * 
FROM         OPENDATASOURCE ('SQLOLEDB', 
                      'Data Source=SERVERURL;User ID=USERNAME;Password=PASSWORD')._DATABASE.dbo.qryHDAsset
END

 

My PHP (picked from various tutorial sites)

$myServer = "****";
$myUser = "sa";
$myPass = "****";
$myDB = "****";
//echo $row['Asset_UniqueKey'];
//$sp = "usp_Assets";


$s = @mssql_connect($myServer, $myUser, $myPass)or die("Couldn't connect to SQL Server on $myServer");

$d = @mssql_select_db($myDB, $s)or die("Couldn't open database $myDB");



$query = mssql_init($sp, $s)or die("Query failed");

$result = mssql_execute($query) or die ("Execute failed");

$numProds = mssql_num_rows($result);

echo "<h1>" . $numProds . " Rows" . ($numProds == 0 ? "" : "s") . " Found: </h1>";

while($row = mssql_fetch_row($result))

{

echo "<li>" . $row[Asset_UniqueKey] . "</li>";

}

 

The page returns "Execute failed" if I remove this 'or die' then I get the <h1>Rows Found</h1> but with no numbers.

 

 

I do have the following in my PHP Error Log

[21-May-2010 09:08:39] PHP Warning:  mssql_execute(): message: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query. (severity 16) in C:\publicwww\sp.php on line 54

[21-May-2010 09:08:39] PHP Warning:  mssql_execute(): stored procedure execution failed in C:\publicwww\sp.php on line 54

 

I am totally baffled now. I'd really appreciate some help for a newbie.

 

If I have missed any information please feel free to shout up. Oh! I'm running PHP/Apache on a windows server connecting to a different server running MSSQL on windows.

 

Thanks in advance.

[Brian Swan]

Since you are running on Windows, I'd suggest you use the driver that is produced by Microsoft (the sqlsrv driver).

 

You can download the sqlsrv driver here: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=ccdf728b-1ea0-48a8-a84a-5052214caad9.

This video will show you how to install it:

.

This topic in the documentation shows you how to call a stored procedure: http://msdn.microsoft.com/en-us/library/cc296167(SQL.90).aspx

 

If you are interested int the difference between the sqlsrv and mssql drivers, there are some topics here: http://blogs.msdn.com/brian_swan/archive/tags/mssql+driver/default.aspx

 

Hope that helps.

 

-Brian

[Sudantha]

i have problems with running MSSQL in PHP Stored procedures but i figured out how to do it

 

check my blogpost 

 

http://sudantha.wordpress.com/2010/05/25/running-microsoft-sql-mssql-stored-procedures-in-php/

[James25]

Thanks Sudantha, the link is really helpful