Jump to content

PHP & MSSQL Stored Procedures


Lyleyboy

Recommended Posts

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 3 weeks later...

Archived

This topic is now archived and is closed to further replies.

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