downloads | documentation | faq | getting help | mailing lists | reporting bugs | sites | links | my 
search for in the  
view the version of this page
Last updated: Thu, 21 Aug 2003

LVII. Microsoft SQL Server functions


These functions allow you to access MS SQL Server database.


Requirements for WIn32 platforms.

The extension requires the MS SQL Client Tools to be installed on the system where PHP is installed. The Client Tools can be installed from the MS SQL Server CD or by copying ntwdblib.dll from \winnt\system32 on the server to \winnt\system32 on the PHP box. Copying ntwdblib.dll will only provide access. Configuration of the client will require installation of all the tools.

Requirements for Unix/Linux platforms.

To use the MSSQL extension on Unix/Linux, you first need to build and install the FreeTDS library. Source code and installation instructions are available at the FreeTDS home page:

Note: In Windows, the DBLIB from Microsoft is used. Functions that return a column name are based on the dbcolname() function in DBLIB. DBLIB was developed for SQL Server 6.x where the max identifier length is 30. For this reason, the maximum column length is 30 characters. On platforms where FreeTDS is used (Linux), this is not a problem.


The MSSQL extension is enabled by adding extension=php_mssql.dll to php.ini.

To get these functions to work, you have to compile PHP with --with-mssql[=DIR], where DIR is the FreeTDS install prefix. And FreeTDS should be compiled using --enable-msdblib.

Runtime Configuration

The behaviour of these functions is affected by settings in php.ini.

Table 1. MS SQL Server configuration options

For further details and definition of the PHP_INI_* constants see ini_set().

Resource Types

Predefined Constants

The constants below are defined by this extension, and will only be available when the extension has either been compiled into PHP or dynamically loaded at runtime.

MSSQL_ASSOC (integer)

MSSQL_NUM (integer)

MSSQL_BOTH (integer)

SQLTEXT (integer)

SQLVARCHAR (integer)

SQLCHAR (integer)

SQLINT1 (integer)

SQLINT2 (integer)

SQLINT4 (integer)

SQLBIT (integer)

SQLFLT8 (integer)

Table of Contents
mssql_bind --  Adds a parameter to a stored procedure or a remote stored procedure
mssql_close -- Close MS SQL Server connection
mssql_connect -- Open MS SQL server connection
mssql_data_seek -- Moves internal row pointer
mssql_execute --  Executes a stored procedure on a MS SQL server database
mssql_fetch_array --  Fetch a result row as an associative array, a numeric array, or both
mssql_fetch_assoc --  Returns an associative array of the current row in the result set specified by result_id
mssql_fetch_batch --  Returns the next batch of records
mssql_fetch_field -- Get field information
mssql_fetch_object -- Fetch row as object
mssql_fetch_row -- Get row as enumerated array
mssql_field_length -- Get the length of a field
mssql_field_name -- Get the name of a field
mssql_field_seek -- Seeks to the specified field offset
mssql_field_type -- Gets the type of a field
mssql_free_result -- Free result memory
mssql_free_statement -- Free statement memory
mssql_get_last_message --  Returns the last message from the server
mssql_guid_string --  Converts a 16 byte binary GUID to a string
mssql_init --  Initializes a stored procedure or a remote stored procedure
mssql_min_error_severity -- Sets the lower error severity
mssql_min_message_severity -- Sets the lower message severity
mssql_next_result -- Move the internal result pointer to the next result
mssql_num_fields -- Gets the number of fields in result
mssql_num_rows -- Gets the number of rows in result
mssql_pconnect -- Open persistent MS SQL connection
mssql_query -- Send MS SQL query
mssql_result -- Get result data
mssql_rows_affected --  Returns the number of records affected by the query
mssql_select_db -- Select MS SQL database

add a note add a note User Contributed Notes
Microsoft SQL Server functions
Steve Milner
24-Oct-2003 11:30
If you are getting "Not associated with a trusted SQL Server connection" when trying to connect to SQL Server 2000 or MSDE on a foreign computer and you have allready set SQL Authentication to Windows and SQL Authentication, then try going to Control Panel->Administrative Tools->Local Security Policy->Local Policies->Security Options and enable both Network access: Let Everyone permissions apply to anonymous users AND Network access: allow anonymous SID/Name translation. This should stop the error and allow you to use the server.

This was tested on Windows XP Profesional running MSDE.
duarte at uma dot pt
07-Oct-2003 01:44
SQLServer 2000 (win2k) and PHP 4.3.3 + FreeTDS 0.61.1  (SuSE 7.3)

I coulnd't connect to SQLServer 2000 after compiling PHP 4.3.3 with FreeTDS 0.61.

But I had  in the same machine PHP 4.2.3 + FreeTDS 0.61 and it was working fine.

I found in a freetds mailing list that PHP now set mssql.textsize and mssql.textlimit, so, to get PHP 4.3.3 connecting with MSSQL 2000 with FreeTDS 0.61 we should comment out the following lines in PHP.INI:
;mssql.textlimit= xxxx
;mssql.textsize= xxxx

I have compiled FreeTDS --with-tdsver=7.0 --enable-msdblib
and PHP --with-mssql=/usr/local/freetds

More details in:

I hope this helps somebody because I spent 2 days to solve this "problem"...  ;-)
astercomp at at terra dot com form Guatemala
25-Sep-2003 07:42
I wasted 2 hours trying to figure out why I couldn't make the MSSQL functions work even if I had the correct dlls in the correct places and had edited my php.ini correctly:

1) My php.ini file was stored in c:\windows\system32 instead of c:\windows, so php used some defaults. I only realized this after viewing the results of the phpinfo() function. Under the "PHP Core" section, under "extension_dir" row, it had "C:\php4" as the parameter and I had just put "c:\php\extensions" and restarted the server.

2) Once I moved the file to the proper location, PHP started complaining about not finding php_msql.dll. Read carefully, I had uncommented php_msql.dll instead of php_mssql.dll in php.ini

I hope this helps somebody out there.
ashearer at shearersoftware dot com
05-Sep-2003 05:49
Beware enabling this extension without installing a new copy of ntwdblib.dll, even if it seems to work. Drag-installing SQL Server 2000's copy of ntwdblib.dll (with a date in the year 2000, replacing an existing 1998 version) solved a performance problem.

I had noticed a slight slowdown of simple PHP pages, and it turned out that just having the php_mssql extension enabled in php.ini was adding 0.5 seconds to each page load. Even "Hello world!" always stuttered for half a second. (Reproduced on two different Windows 2000 servers with IIS 5, under both CGI and FastCGI, PHP 4.3.2 and 4.3.3.) Updating ntwdblib.dll fixed both servers.
xxpig [AT] hotmail DOT com
02-Aug-2003 02:53
I cannot use Chinese in SQL when connect to MSSQL 2000 Server using FreeTDS for a long time.
And now, I have solved the problem.
Many documents said that using --with-tdsver=7.0 to compile FreeTDS, becuase the default version of tds use 5.0 protocol and the default port of 5.0 is 4000, the default port of 7.0 is 1433, equal to MSSQL's port.
I found that --with-tdsver=4.2 also use the port 1433, and the lower protocol can use Chinese!  May be 7.0 need to use UNICODE, and 4.2 don't use it.
If you had already compiled FreeTDS --with-tdsver=7.0, you can also set an enviroment as:
set TDSVER=4.2; export TDSVER
Then you can connect to MSSQL2000 and using Chinese in the query.
nogitsune101 at hotmail dot com
26-Jul-2003 01:32
Web Server Type: Windows 2000 Server - IIS 5
SQL Server Type: SQL 2000
Just thought I would put this up since I saw several people having this issue.

Few things that need to be done to get this to work:

1. Install the MS SQL 2000 Client Tools on your Server
2. Using the "Client Network Utility" set up an Alias
3. Edit your PHP.INI file to include the php_mssql.dll extension file.
4. Restart your IIS Service

If you get a "Access Denied" message you need to go to your "\WINNT\SYSTEM32" directory and find the "ntwdblib.dll"  Add the group "Everyone" to the security with "Read" and "Read - Execute" access.  Then restart your IIS Service again.

Here is a peice of sample code you can use to test your MS SQL access.

// ------------------------------------------------ //

="MsServerAlias"//put your server alias here
$usrname="user"//put your MS SQL username here
$passwrd="password"//put your MS SQL password here

if (
$body.= "<font color='blue'>Connection to $msserver was successful.</font><hr>"
$body.="<font color='red'>Error: ".mssql_get_last_message()."</font><hr>"

if (
$body.= "<font color='blue'>Connection to $msserver was successfully closed.</font><hr>"
$body.="<font color='red'>Error: ".mssql_get_last_message()."</font><hr>";

// ------------------------------------------------ //
yu at NOSPAM dot burgiss dot com
25-Jul-2003 02:54
Linux users connecting MS SQL Server thru FreeTDS:

If you are using FreeTDS version 0.61, which is the lastest at this point (July 25, 2003), you must comment out mssql.textlimit in your php.ini.

I found this info on freetds mailing list.
Because of this bug, I wasted nights and nights trying to connect to MS SQL.

Here's the combination that worked on my redhat.
apache_1.3.28, freetds-0.61, and php-4.3.2.
Download the tarballs, and expand them in /usr/src.

cd /usr/src/apache_1.3.28
./configure --prefix=/www --enable-module=so
make; make install; make clean

cd /usr/src/freetds-0.61
./configure --prefix=/usr/share/freetds --sysconfdif=/www/conf --with-tdsver=7.0 --enable-msdblib
make; make install; make clean

cd /usr/src/php-4.3.2
./configure --prefix=/www --with-mysql --with-mssql=/usr/share/freetds --with-apxs=/www/bin/apxs --enable-track-vars --with-config-file-path=/www/conf
make; make install; make clean

I found it convenient to put config files under /www/conf/.
Add the following to your freetds.conf and you won't need the "" stuff that was posted by others.

    host =
    port = 1433
    tds version = 7.0
    text size = 147483647

You can use "MsSqlServer" to connect.

I had problem mssql_bind, so I gave "EXEC ProcedureFoo @Foo = 'foo';" to mssql_query and it worked.

Like others have mentioned, you would want to change the text size in order to get anything bigger than 4kb.
I made my mssql.textsize = 147483647.

I hope this helps.
mark at d0gz dot net
19-Jul-2003 01:03
To use the entire API, be sure to compile php with the --with-mssql switch and *not* the --with-sybase switch.  Some MSSQL functions will work with the sybase switch, but not all (case in point, mssql_field_type()). 

One other minor note: be sure to include the port number when specifying the IP address of the MSSQL server.  It solved my connectivity problem with the phplib (7.4-pre2) database tools.

04-Apr-2003 07:56
If you have problems connecting to a MS SQL server, one reason may be that the MS SQL server is configured to use a protocol other then Named Pipes.

Install the MS SQL client software and run the client configuration tool. Add an Alias to the database server and specify the type of protocol the server is configured to (TCP/IP in my case).
lorenthal at hotmail dot com
01-Apr-2003 05:47
I had a problem connecting to any MS SQL Server. I configured everything OK but I couldn't connect to any server (I received the "MS SQL: Unable to connect to server:" message).

It worked fine in one computer but it didn't work in another computer. After some tests, I searched the web for a newer version of the "ntwdblib.dll" file, and I found one with 348KB size, while the one I had was only 273KB size.

After replacing the old file with the newer, everything went Ok.

You can find this file in the following address:

(note: I had to cut the address above into five lines to reduce the length of the line).

Hope it's useful to you,
Sergi Miranda
jerry dot wilborn at fast dot net
31-Mar-2003 01:16
be aware that compiling with --with-sybase=DIR may get your mssql functions working but variables like "MSSQL_ASSOC" when trying to fetch an array won't work. the mssql functions are mearly aliases to their respective sybase components (grep the sybase source for mssql).

what does this mean?  if you're 'while'ing thru a fetched row and wanting to get "list($column_name, $column_value) = each($row)" then you need to test $column_name such as:  if (is_int($column_name)) continue;

since the sybase fetch row gives both numeric and textual representations of the fetched data and has no ability to do one or the other.

 - jerry
user from .ee
27-Mar-2003 10:56
i could not connect to mssql database (redhat -> win2000) without port arter server ip (it was written in sybase conf but it still didn't work):


hope it is useful for someone.
dave at purevanity dot net
13-Mar-2003 04:46
Just a quick note for all you new FreeTDS users;

You need to compile your FreeTDS based on what MsSQL server version you are using. When compiling use the flags '--with-tdsver=' and then append the MsSQL version you are using. For MsSQL 2000, its 7.0.
larsrohr at eecs dot berkeley dot edu
29-Jan-2003 05:30
Apache/2.0.44 (Unix) mod_ssl/2.0.44 OpenSSL/0.9.7 PHP/4.3.0

We compiled PHP with "--with-mssql=/usr/local/freetds", and found that we could only mssql_connect using the IP:port syntax, not the name we defined in freetds.conf.

To fix this, we added to the /usr/local/apache2/bin/envvars file:
Ernesto Domato
28-Jan-2003 04:42
If you want to use the FreeTDS library under Linux to access the functions for Microsoft SQL Server, use the --with-sybase=/path/to/freetds instead of --with-mssql=/path/to/freetds as mentioned on the manual.

This will solve the problem that I had with mssql_query that does a segmentation fault.

You can also check bug #20935 ( for more information.

This note is for PHP 4.3.0

rwcatalano - the answer shop
15-Jan-2003 01:44
Web Server Type: Windows 2000 Server
SQL Server Type: Version 7

Unable to load dynamic library '.\php_mssql.dll' - The specified module could not be found. in Unknown on line 0

Unable to load dynamic library '.\php_mssql.dll' - The specified procedure could not be found. in Unknown on line 0

How To Fix:
First in most circumstances you are getting this error because you have a older version of PHP installed (Such as 4.2.3), but you are trying to use a newer php_mssql.dll file (Such as the one that comes with 4.3.0)

Step 1:
Create a file called info.php that contains the single line:
<?php phpinfo(); ?>

-executing this file in your web browser will tell you what version of PHP you are running and it will through a couple of visual alerts displaying the errors mentioned above.

Step 2:
If you installed PHP using the installer you will have to go back to the php site and download the binary .zip file for Windows, for the version of PHP you currently have installed.

Step 3:
Open the zip file (you don't have to unzip the whole file becuase you are just looking to copy one (1) dll file, "php_mssql.dll".

Step 4:
Pending on whether you are running a default install, copy the "php_mssql.php" file to the following directory "c:\winnt\"

Step 5:
Double check that the following file exists:

Step 6:
Edit your php.ini, search for the string "php_mssql.dll", and remove the semi-colon (;) from the front of the value.

Step 7:
Start and stop IIS Services by clicking the windows "start" button in the bottom left corner, select run, and type the command:
net stop iisadmin
(Type y at the promt)
When the command window dissapears,
click the windows "start" button in the bottom left corner again, select run, and type the command:
net start w3svc

Step 8:
Run your info.php file again.  If you did not get any errors, you are good to go; if you get the error again or your error looks like this: 'c:\program files\php\extensions\php_mssql.dll',
then you need to move your "c:\winnt\php_mssql.dll" to the directory mentioned in the error message, for this example: "c:\program files\php\extensions\".

Good Luck!!
peter at schumacher dot dk
09-Jan-2003 02:19
It's possible to change the default connection type from Named Pipes to TCP/IP
WITHOUT installing the Connectivity-Part from the MSSQL7 Server-cd,

The registry-key,


holds the current connection-type.

Initially it's set to "DSQUERY"="DBNMPNTW" which corresponds to Named Pipes.

Change it to "DSQUERY"="DBMSSOCN" which corresponds to TCP/IP.

(Only tested on Win2KPro)
27-Dec-2002 08:08
"--with-mssql" is available as of 4.3.0 (not earlier!)
james at adtekh dot com
24-Dec-2002 09:21
If you find that you are not getting the whole value of a text field back from the database, you have probably reached the default 4kb limit. This can be fixed by changing the mssql.textlimit and mssql.textsize values to something larger than 4096. I actually rewrote an entire section of code before realising it wasn't my code that was causing the problem, but this limit!
wolff at 21st dot de
17-Dec-2002 08:08
Please note that although it愀 stated everywhere that you can use --whith-sybase to make the mssql_* functions available on *nix platforms, this doesn愒 mean that all functions of the MSSQL-Extension are really available.

In fact, only those functions that are provided by the Sybase extension are mapped to the corresponding mssql_* function names, but no more. Therefore, a large part of the API is unavailable on Unix/Linux.

Functions that do not exist are for example mssql_fetch_assoc(), mssql_field_name(), mssql_field_length() and mssql_field_type().

As a result of this, if you use PEAR::DB愀 MSSQL driver class, you can愒 use the tableInfo() method. Automated insert/update query generation using autoExecute() won愒 work as well.

There愀 a new version of the MSSQL extension in CVS now that will also compile under Unix, making the full API available - but it愀 not likely that it will make it into the 4.3.0 release.
jcamam at yahoo dot es
15-Nov-2002 11:25
I had problems with ntext data types: only first 'n' characters were returned. I solved it using a cast:

SELECT cast ( column_name as text ) as column_name ...

It looks like it returns text value as varchar type, so it truncates the result.

Other limitation is the next: TEXTSIZE variable sets the max length of column content that should return

mssql_query ("set TEXTSIZE  100000;");

Hope this helps someone.

Josep Campillo
scott at datalink dot net dot au
28-Aug-2002 04:38
If you are running IIS/MSSQL/PHP with PHP in CGI mode and are getting a "CGI Error" message from IIS, read on...

A bug exists in the MSSQL driver (possibly on Microsoft's side) that will cause this problem on high performance servers running PHP in CGI mode, especially if your app uses frames.   It is related to lots of simultaneous connections to the SQL server from PHP. 

A bugnote on the PHP database had lots of input, which can be read here:

A solution was found, which involves tuning the Win2k or WinXP server for 'Forground Applications'.    This eliminates the problem completely.

It seems that CGI applications under IIS are launched into user space, owned by the IUSR_* user.   So tuning the server to process forground apps makes sense.

To do this, right-click 'My Computer', select 'Properties', select the 'Performance' tab, and then select 'Forground Applications' and click 'OK'.
php at zfi dot org dot uk
16-Aug-2002 07:58
We observed that datetime values returned through the mssql functions were in a different format to that observed in Enterprise Manager / Query Analyzer.

i.e. EM said: 2002-08-16 09:58:06
PHP-MSSQL said: 16 Aug 2002 9:58

The following php.ini setting stopped this from happening:

mssql.datetimeconvert = Off

System: PHP 4.2.2, SQL Server 2000, Win 2000 Server
a dot pfister at jenbacher dot com
06-Aug-2002 12:11

Just a Hint:
You have to install the Connectivity-Part from the SQl-Server CD or the MSDE to use the php_mssql.dll.
jd at keane dot com
25-Apr-2002 04:36
Connecting to MSSQL 7 on UNIX/Apache/php4.1.2.  I followed a bunch of instructions below from folks using Linux and other give this a try, it may work.

download freetds from
configure arguments --with-prefix=/usr/local/freetds --with-tdsver=7.0

edit freetds.conf in /usr/local/freetds/etc/freetds.conf
host =  XX.XX.XX.XXX(your MSSQL IP)
port = 1433
tds version = 7.0           
client charset = UTF-8   

reconfigure PHP using --with-sybase=/usr/local/freetds/ and all of your normal args. 

restart apache and it works...well it did for me.  Good luck

elee at kinwai dot net
15-Apr-2002 12:29
The belows procedures works on the following configuration: Mandrake linux 8.2,MS Sql server 2000, windows 2000 server, PHP ver 4.12, apache 1.3.24,Freetds 0.53.

Configure Freetds
1.    Download Freetds 0.53 at
2.    ./configure --prefix=/usr/local/freetds --with-tdsver=7.0
3.    make
4.    make install
5.    Edit /usr/local/freetds/etc/freetds.conf
6.    Add
[MyServer70]              『DSN for PHP
host =        『MS SQL Server IP
port = 1433                『MS SQL Default Port
tds version = 7.0            『MS SQL 2000 Version No.
client charset = UTF-8        『Charset Encoding, it can be omitted if you don't want to disply in unicode

Configure PHP 4.12
1.    Download PHP4 from
2.    ./configure --with-sybase=/usr/local/freetds --with-apache=../{Apache Path} --enable-track-vars
3.    copy all files in /usr/local/freetds/lib/ to /usr/lib
4.    make
5.    make install

Configure Apache 1.3.24
1.    ./configure --activate-module=src/modules/php4/libphp4.a
2.    make
3.    make install
4.    cp php.ini-dist /usr/local/lib/php.ini
5.    Edit Your httpd.conf or srm.conf file and add;
AddType application/x-httpd-php .php
6.    /usr/local/apache/bin/apachectl start

Feel free to email to us @ or
German Chu & Eagle Lee
nospam at nospam dot com
22-Feb-2002 04:00
If you have some trouble with FreeTDS and TEXT limit (default 4096) and
set TEXSIZE doesn't work and you can't even use mssql_init to use stored proc.

use a simple query with "exec stord_proced_name"..
and create the store proc with
set textsize ...
your data processing (like select...)
at the end enter
set textsize 0..

and it will work perfectly !!!!
21-Aug-2001 07:01
> Having trouble with pesky TEXT datatypes? Try the query 'set textsize 555555555;'
> to set the limit on how much a TEXT will return.

I was indeed having troubles with those pesky TEXT datatypes, this query alone didn't help either, i had to put

mssql.textlimit = 65536
mssql.textsize = 65536

in php.ini and then restart _the WHOLE_ of IIS (not just the website) to get it working (and don't forget to use that query too)

(That's on Win32 + IIS)
netservice at 21cn dot com
19-Apr-2001 11:01
on Linux,
FreeTDS are not support GB2312 charset, use Sybase-11.9.2 client tools is ok.
myers at fil dot org
23-Feb-2001 05:12
> Having trouble with pesky TEXT
> datatypes? Try the query 'set
> textsize 555555555;' to set the limit
> on how much a TEXT will return.

I was trying to figure out why 555555555 works and not anything higher.  Then it occured to me ... the machine in question has 512 MB of ram.  The moral of the story is that you have to adjust the size to how much memory you have and how much data you expect to come out of the database.
myers at fil dot org
22-Feb-2001 10:03
Having trouble with pesky TEXT datatypes? Try the query 'set textsize 555555555;' to set the limit on how much a TEXT will return.

Symptoms of the problem are hanging web browsers and "FATAL:  emalloc():  Unable to allocate -2147483648 bytes" errors in apache's error.log
steeven at kali dot com dot cn
24-Dec-2000 07:07
1. results sets are not supported, only the first result set can be retrieved. for example:
  SELECT 3,5
Only the first result set can be fetch. Use a Union instead.
2. Each fetch to cursor will get a result set. Fetch a large quantity row sets will raise the server's overhead on network. try other way instead.
3. The very usefull advantage of ADO is that muti-result set are supported. the core function is sp_cursorxxxxx. You can use it with a muti-record set support client. I'm very disapointed that PHP has not support it now.
caspian_ at hotmail dot com
07-Jul-2000 12:14
To compile PHP to support M$SQL on LINUX:<BR>
-Download FreeTDS:<BR>and compile as required.  Make sure to compile with the correct version of the protocol (read the readme)<BR>
-Compile PHP with '--with-sybase=/usr/local/freetds' or wherever you put freetds.<BR>

PHP will now support both mssql_*** and sybase_*** from Linux..  enjoy!
jhamann at sfresearch dot com
21-Apr-2000 11:49
I have just finished setting up php4 with freetds to connect to a MSSQL 7 server.  I used freetds 5.0 and configured with --with-tdsver=7.0
camber at ais dot org
11-Feb-2000 07:02
To access MS SQL from Unix you may use Sybase's OpenClient which happens to work (depending on platform, 7.0 needs SP1 and big endian platforms have trouble) or FreeTDS ( which is still alpha-ish but directly supports MS SQL.

 Last updated: Thu, 21 Aug 2003
show source | credits | sitemap | mirror sites 
Copyright © 2001-2003 The PHP Group
All rights reserved.
This mirror generously provided by:
Last updated: Sat 01 Nov 2003 04:13:36 EST EST