PHP, Microsoft SQL Server (MSSQL), and IIS: Connect and Query with ODBC

By  on  

I was recently thrown a real curveball of a project. Instead of using a fresh MySQL database to pull information from, the customer required that we pull information from their new Microsoft SQL Server 2005 server. This isn't the most desired method of PHP->Database interactivity but that's what the project called for and that's what I needed to do. Here's how to get things going.

Connecting to Microsoft SQL Server 2005 from PHP

// the "driver" first
$virtual_dsn = 'DRIVER={SQL Server};,45277;DATABASE=MyDatabase';
$connection = odbc_connect($virtual_dsn,'Username','Password') or die('ODBC Error:: '.odbc_error().' :: '.odbc_errormsg().' :: '.$virtual_dsn);

Choose Which Database

odbc_exec($connection,'USE MYDB');

Executing a Query

$result = odbc_exec($connection, 'SELECT FirstName FROM Names');

Using the Query Result

$first_name = odbc_result($result, 'FirstName')

Querying Tables and Columns with Spaces

// use brackets [] for tables and colums with spaces
$result = odbc_exec($connection, 'SELECT [First Name] FROM [Names Table]');

Like I said, while this isn't the most desirable setup in the world, it will do. Big ups to Hostway for their help in configuring the server; they provided everything we needed.

To learn more about the ODBC functions, visit

Recent Features

  • By
    LightFace:  Facebook Lightbox for MooTools

    One of the web components I've always loved has been Facebook's modal dialog.  This "lightbox" isn't like others:  no dark overlay, no obnoxious animating to size, and it doesn't try to do "too much."  With Facebook's dialog in mind, I've created LightFace:  a Facebook lightbox...

  • By
    Animated 3D Flipping Menu with CSS

    CSS animations aren't just for basic fades or sliding elements anymore -- CSS animations are capable of much more.  I've showed you how you can create an exploding logo (applied with JavaScript, but all animation is CSS), an animated Photo Stack, a sweet...

Incredible Demos

  • By
    AJAX For Evil:  Spyjax with jQuery

    Last year I wrote a popular post titled AJAX For Evil: Spyjax when I described a technique called "Spyjax": Spyjax, as I know it, is taking information from the user's computer for your own use — specifically their browsing habits. By using CSS and JavaScript, I...

  • By
    jQuery Link Nudging

    A few weeks back I wrote an article about MooTools Link Nudging, which is essentially a classy, subtle link animation achieved by adding left padding on mouseover and removing it on mouseout. Here's how to do it using jQuery: The jQuery JavaScript It's important to keep...


  1. Why didn’t you use the mssql_* functions?

  2. Connecting to databases other than mysql seems so awkward since I don’t do it much; however, just the other day I had to connect to an old dbase database (actually just flat files).

    If anyone is interested in you can read more about it here.

    It can be a real pain connecting to obscure databases.

  3. Is there still a character limit imposed by PHP’s SQL Server driver (it was based on Sql Server 4 which had a much smaller limit than current varchar’s can take)? I remember that being an issue.

  4. @Rob: I’m not sure about that. I didn’t run into any problems with what I used above.

  5. Is there still a character limit imposed by PHP’s SQL Server driver (it was based on Sql Server 4 which had a much smaller limit than current varchar’s can take)? I remember that being an issue.

    Also, have you looked at the mssql_* functions for using stored procedures?

    $sp = mssql_init('AllOpenCalls_S1', $dbLink);
    mssql_bind($sp, '@SortByColumn', $sortBy, SQLVARCHAR, false, false);
    $proc = mssql_execute($sp);
    while ($result = mssql_fetch_assoc($proc)){ ... }
  6. I just ran a google search and it appears MS finally updated the driver PHP uses so that shouldn’t be an issue. It appears to support up to SQL Server 2005.

    If ODBC gets you down, try the mssql_* functions. It makes it very easy to use parametrized stored procs:
    mssql_init, mssql_bind, mssql_execute, and mssql_free_result.

  7. It appears my data is outdated. MS released a new SQL driver for PHP in May which clears that and other Sql Driver related issues.

    If you want to use parameterized stored procedures, see here:

    PHP MSSQL Functions

  8. @Jesus: I found that most hosts only had ODBC functions, not MSSQL functions.

  9. sorry about the overposts I was having technical difficulties with post utility. Please disregard the extra garbage.

  10. Jay

    Listen up close yo. You really need to have a plan for timeouts bc those connections time out like a mug. I learned the hard way. The reason I say that is that there is prob a 100% chance that this is a remote connection unless your php is executing on a windows box and it is on the same server. Usually your php runs on apache and the sql server db is on another box, and so thus the timeouts.

    For apps that make a lot of database hits where constant timing out can be a pain you might consider running a job that dumps it to a mysql database, I’ve done that alot. If you just have some widget that makes a query, you might could get away with caching, but you always need to handle timeouts.

    I really prefer web services over direct db connections if its say a third party or a database and infrastructure that’s completely separate from your own but sometimes you don’t always have that option and web service calls time out too.

    Anyways hope that helps.

  11. Alex

    If somebody is familiar with ADOdb he can use php almost with any database. This is why we are encouraged at school doing this. It’s pretty simple and easier to handle than the original mysql :)

  12. You made my day!

    Actually you more than made my day – it is just the problem I needed to kickstart me with php on IIS!

  13. ADOdb, ODBC…… The Zend Framework has a nice database abstraction layer in pure php (requires PHP5 but if you’re starting PHP4 projects now then something is wrong with you) – it can talk to PDO and a bunch of other extensions, I assume that it can abstract away MSSQL.

    Maybe, maybe not… Probably not helpful but worth looking at (and E_STRICT compliant).

    OH! And, it’s loosely coupled with the rest of the “framework” – you can use it by itself and don’t need to touch the other stuff – a bit like a normal library such as PEAR.

  14. okmi

    we are trying to connect to a remote mssql database using php that runs on a completely separate linux box. looking at phpinfo(), it looks as though ODBC is listed as supported under dbx. the problem is that every time i try odbc_connect(), it says it is an undefined function. does anyone know how i would go about getting it to work in php?

  15. Donald Spears

    Hey, thanks for this…I’ve been trying to get a connection to a remote machine going for two days and every mssql function was crapping out on me and all attempts at fixing turned up nothing.

  16. satish

    cant i connect Microsoft SQL server in PHP application to access data from SQL server ?

  17. First, David, your the man. All I can say.

    I have been using odbc to mysql for years now. I work on projects in the public sector pulling data from various sources and manipulating it into various formats depending on the client needs. Other than the tablenames in [], which I did not know or use, my setup is similar.

    I’m trying now to figure out how to pull data at intervals to prevent clients/users from hitting their server directly (mission critical stuff). Your XML back file got my attention there, I just need to figure out how to create mssql->mysql mappings and learn something about sending the xml to mysql using some kind of field mapping. Great stuff though.

  18. Hello David
    Can you share anymore information on making the connection? I need to make the same type of connection but don’t have a helpful host. Did you need special extensions compiled into PHP?

    $virtual_dsn = ‘DRIVER={SQL Server};,45277;DATABASE=MyDatabase’;

  19. mwanly

    Please tell me how can i set character in this function (odbc)
    my database result appear like this : ????????
    please tell me if you know .. i really do want it ..

Wrap your code in <pre class="{language}"></pre> tags, link to a GitHub gist, JSFiddle fiddle, or CodePen pen to embed!