Warning: mssql_connect()

[abaddon5586]

I have a new install of PHP with Apache 2.2 on a Windows XP box and am getting the following error when using this line of PHP:

 

mssql_connect($server,$username,$password) or die ("Could not connect");

 

Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: "servername" in "directory" on line 226.

 

I have the latest version of ntwdblib.dll from http://www.helpspot.com/helpdesk/index.php?pg=kb.page&id=13

 

The following returns

 

<?php

if (function_exists('mssql_fetch_row')) {

echo "MSSQL functions are available.<br />\n";

} else {

echo "MSSQL functions are not available.<br />\n";

}

 

?>

 

<?php

phpinfo();

?>

 

MSSQL functions are available.

 

Multiple locations is odd but all relevant files (identical) are in both locations

Configuration File (php.ini) Path C:\WINDOWS

Loaded Configuration File C:\php\php.ini

extension_dir C:\php\ext C:\php\ext

 

Uncommented in php.ini (both C:\Windows and C:\php

extension=php_mssql.dll

 

Tested connection using MSSQL Management Studio, login/pwd works.

 

Have tested this with 2008 SQL Express and 2005 SQL Full, neither works. I do have this install working with MySQL.

 

Any ideas?

[Brian Swan]

Have you configured SQL Server to allow remote connections? If you have, can you get more detailed error info?

 

BTW, since you are running PHP on Windows, I'd suggest you use the Microsoft-built driver (available here: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=ccdf728b-1ea0-48a8-a84a-5052214caad9 , or via the Web Platform Installer: http://www.microsoft.com/web/downloads/platform.aspx ). It does not rely on the depricated DBLib technology. If you are interested, more info here: http://blogs.msdn.com/b/brian_swan/archive/2010/03/08/mssql-vs-sqlsrv-what-s-the-difference-part-1.aspx and

http://blogs.msdn.com/b/brian_swan/archive/2010/03/10/mssql-vs-sqlsrv-what-s-the-difference-part-2.aspx.

 

-Brian

[abaddon5586]

I was able to connect to the DB. Thanks for the info, fun read

 

For anyone interested, I had to add the extensions to my C:\php\ext directory and the php.ini file, from there just use the new functions as described in

 

http://blogs.msdn.com/b/brian_swan/archive/2010/03/10/mssql-vs-sqlsrv-what-s-the-difference-part-2.aspx

 

and if you're using mixed authentication here is the format, as this function uses Windows authentication by default.

 

$connectionInfo = array( "UID"=>"myPassword","PWD"=>"myPassword");

$conn = sqlsrv_connect( $serverName, $connectionInfo);

[Brian Swan]

Cool...glad you have things working. Am I right in reading that you connected with the php_sqlsrv.dll driver (not the php_mssql.dll driver)? Just curious.

 

Thanks.

 

-Brian

[abaddon5586]

Correct, I connected with php_sqlsrv.dll . I am running into an issue with sqlsrv_fetch though.  I'm passing it a valid query in the form of

 

$result=sqlsrv_query($conn,$query);

 

but am receiving the following error in the code segment

 

$i=0;

while ($i <= $result+1) {

if( sqlsrv_fetch( $result ) === false )

{

echo "Error in retrieving row.\n";

die( print_r( sqlsrv_errors(), true));

}

 

Connection established. Error in retrieving row. Array ( [0) => Array ( [0) => IMSSP [sqlSTATE) => IMSSP [1) => -28 [code) => -28 [2) => The active result for the query contains no fields. [message) => The active result for the query contains no fields. ) )

 

Ignore incorrect brackets/parentheses combination. [] was messing up the post so I changed it to [)

 

But yea, the sqlsrv driver works great so far. A bit of an adjustment since most examples are mssql related.

[abaddon5586]

Oh, and right before the error in the previous post I run this

 

if( $result === false )

{

    echo "Error in statement preparation/execution.\n";

    die( print_r( sqlsrv_errors(), true));

}

 

which makes it through with no error

 

[Brian Swan]

What is the query that you are executing? If it is an INSERT, UPDATE, or DELETE query, try calling sqlsrv_rows_affected (instead of sqlsrv_fetch).

 

-Brian

[abaddon5586]

USE database SELECT TOP 10 field1, field2 FROM table

 

-Ben

[abaddon5586]

SQLSTATE: IMSSP code: -28 message: The active result for the query contains no fields.

 

Looks like it matches

 

SQLSTATE

 

 

For errors that originate from the ODBC driver, the SQLSTATE returned by ODBC. For information about SQLSTATE values for ODBC, see ODBC Error Codes.

For errors that originate from the SQL Server Driver for PHP, a SQLSTATE of IMSSP.

 

For warnings that originate from the SQL Server Driver for PHP, a SQLSTATE of 01SSP.

 

from

 

http://msdn.microsoft.com/en-us/library/cc296200%28SQL.90%29.aspx

 

but I can't seem to find what the error code -28 means for this driver. (Edit:: other than the brief description of course)

 

-Ben

[Brian Swan]

I think your query is returning 2 result sets: one for the "USE database" statement, and one for the "SELECT ..." statement. The first result set has no fields (obviously). Try calling sqlsrv_next_result($stmt) after you execute the query and before you start iterating through rows. Let me know how that works.

 

-Brian

[abaddon5586]

You were correct, it was using a return set from "USE database".

 

Thanks for all the help with this.

 

 

-Ben

[glam_rock]

Just a question as I am trying to get php_sqlsrv.dll set up.

The installation now however supplies me with like 6 differently and complexly named dlls.

which one do i use?

 

thank you!

[Brian Swan]

This article has a table that lays out which .dll file to use depending on your set up: http://msdn.microsoft.com/en-us/library/cc296170.aspx

 

-Brian