Exporting MS SQL Table Data using PHP

I regularly export table data from MySQL using PHP, however a question on StackOverflow.com came up where the user wanted to export data from MS SQL rather than MySQL.

Handily, PHP has support for many of the same functions for MS SQL as it does for MySQL.

So with a bit of reworking I was able to come up with the following script;

<?php

    $servername = '.\SQLSERVER';

    $username = 'databaseuser';

    $password = 'database';

    $database = 'CriticalPath';

    $table = 'Users';

    if (!function_exists('mssql_fetch_row'))

    {

        $output .= "MSSQL functions are not available.<br />\n";

        exit;

    }

    // Connect database

    if(!$dbconnect = mssql_connect($servername, $username, $password))

    {

        $output .= "Connection failed to the host 'localhost'.";

        exit;

    } // if

    if (!mssql_select_db($database))

    {

        $output .= "Cannot connect to database '$database'";

        exit;

    } // if

    $result=mssql_query("select * from $table");

    $out = '';

    $columns = mssql_num_fields($result);    

    for ($i = 0; $i < $columns; ++$i) {

        // Fetch the field information

        $field = mssql_fetch_field($result, $i);

        $out .= '"'.$field->name.'",';

    }

    $out .= "\r";

    // Add all values in the table to $out.

    while ($l = mssql_fetch_array($result)) 

    {

       for ($i = 0; $i < $columns; $i++) 

       {

          $out .='"'.$l["$i"].'",';

       }

       $out .= "\r";

    }

    // Open file export.csv.

    $f = fopen ('export.csv','w');

    // Put all values from $out to export.csv.

    fputs($f, $out);

    fclose($f);

    header('Content-type: application/csv');

    header('Content-Disposition: attachment; filename="export.csv"');

    readfile('export.csv');

    mssql_free_result($result);

?>

SQL Server - Formatting Numbers as Strings with Leading Zero’s

Sometimes it’s necessary to format numbers as Strings with leading zero’s.

One such example is when we sort a list in Excel, Excel will sort the numbers as 0,1,10,2,20,3,30,…..

So, to get round this, we simply append a leading 0 to the number.

This is achieved in T-SQL by using the RIGHT instruction with following syntax.

RIGHT('00'+ CONVERT(VARCHAR,YourNumber),2)

The first Parameter - 00, is the default format for your formatted number, and should contain the number of zero’s corresponding to the total length of your formatted string.

The second parameter -‘YourNumber’, is the name of the field you wish to convert.

The third parameter - 2, is the total length of your formatted field.

Retrieving data from a Stored Procedure (with parameters) in Microsoft Excel 2007 / 2010

I have a method here which allows you to add Parameters to the Stored Procedure from Excel.

There are quite a few steps, but I’ve broken it down quite alot.

The process is actually really easy once you know how... as with anything.

1. Open Excel
2. Select the Data Tab (If you’re using Excel 2007 onwards!)
3. Select the “From Other Sources” drop down box
4. Select “From SQL Server”
5. It will open up the following box
   
   
   
6. Enter “.\SQLExpress” (That’s Dot Backslash SQLExpress and it’s without the quotes) in the “Server name:” box
7. Depending on your Server Configuration, either leave the Log on credentials as “Use Windows Authentication”, or select the “Use the following User Name and Password” radio button and enter your SQL Server credentials
8. Hit “Next >”
9. You should then see the following screen:
   
   
   
10. In the “Select the database that contains the data you want:” Dropdown, select the database you wish to use.
11. In the list at the bottom, select a table, it doesn’t matter which you choose.
12. Press “Next >”
13. You should then be presented with the following screen:
    
    
    
14. In the “Filename” Textbox, set the name you would like to save the connection as, remembering to leave the “.odc” on the end.
15. If you want the Password to be stored with this connection, then check the “Save password in file” Checkbox, say “Yes” to the warning box.
16. In the “Description” enter something meaningful for this connection
17. In the “Friendly Name:” Textbox, set the name you would like to appear in the Recent Connections dialog. There’s no need for a “.odc” extension here.
18. Hit “Finish”
19. You should then be shown the following screen:
    
    
    
20. Click “Properties...”
21. The “Connection Properties” window will be shown:
    
    
    
22. Click the “Definition” tab, which will show:
    
    
    
23. If required click the “Save password” Checkbox, and press “Yes” to the warning box
24. Change the “Command Type” Dropdown to be “SQL”
25. Set the “Command text to “exec ” + the name of your Stored Procedure. If you stored procedure requires parameters then simply tack them on the end seperated by commas.
    
    e.g. exec GetUsersByAreaandName Eastern, Smith
    
    If you wish to pass in a Null, then simply type “NULL” as a parameter.
    
    e.g. exec GetUsersByAreaandName Eastern, NULL
    
26. Press “OK”, and Press “Yes” to the warning box
27. You should be shown the Import Data screen again, Press “OK”

If everything went according to plan, you should have all your data on screen!