Guides

Connection to Microsoft SQL Server

Examples in ASP e PHP code

The database services of Microsoft SQL Server offered by Hosting Solutions can be purchased as additional services to a hosting plan. However, this option is not available for all hosting plans.

Why use MS SQL Server?

MS SQL Server is one of the most advanced and secure relational databases on the market. If you want to develop dynamic websites with a high number of visitors, Microsoft SQL Server is certainly a suitable candidate as it ensures high performance and stability.

Access to the MS SQL Server plan

The data for accessing the database are delivered in the service activation email to the email address indicated at the time of purchase and can be viewed at any time from the control panel.

The data to be used to connect to the database are as follows:

  • Server Alias: SQL
  • database name: the name provided with the activation of the SQL Server service.
  • login: the name assigned upon activation of the SQL Server service.
  • password: the password assigned upon activation of the SQL Server service.

Example of connection using the ASP language

Dim Cn, oRs, nc, i, nrec, HTML_output
on error resume next
err=0
Set Cn = Server.CreateObject("ADODB.Connection")
Cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = "sql"
cn.Properties("Initial Catalog").Value = "database"
cn.Properties("User ID").Value = "nomeutente"
cn.Properties("Password").Value = "password"
cn.open

HTML_output = ""

if err=0 then response.write "Contenuto tabella prova: "
err=0
set oRS=Cn.Execute("select * from prova")
if err = 0 then

    nc=oRS.fields.count
    nrec=0
    HTML_output = "<table border=1 cellspacing=0>"
    HTML_output = HTML_output & "<tr bgcolor=blue>"
    for i=0 to nc-1

        HTML_output = HTML_output &  "<td>" & " " & ors(i).name & "</td>"

    next
    HTML_output = HTML_output &  "</tr>"
    do until oRs.eof

        HTML_output = HTML_output &  "<tr bgcolor=yellow >"
        for i = 0 to nc-1

            HTML_output = HTML_output &  "<td>" & " " & ors(i) & "</td>"

        next
        HTML_output = HTML_output &  "</tr>"
        ors.movenext
        nrec=nrec+1

    loop
    HTML_output = HTML_output &  "</table><br>" & 
                                "Numero Record selezionati: " & nrec
    oRs.close
    Cn.Close
    response.write(HTML_output)
else response.write "Errore: " & err.description
end if
Set oRs = Nothing
Set Cn = Nothing

Example of connection using the PHP language

In order to access MS SQL Server using the PHP language, it is necessary to use the appropriate library http://it.php.net/manual/it/ref.mssql.php 

<?php
$hostname = "SQL";
$dbName = "nomedatabase"; 
$username = "vostrousername"; 
$password = "vostrapassword"; 
$connessione = mssql_connect($hostname,$username,$password) 
    or die("ERRORE: il database non ha risposto."); 
mssql_select_db($dbName) 
    or die("ERRORE: il database richiesto non esiste"); 
$interrogazione = "select * from prova";
$risultato = mssql_query($interrogazione);

$html_risultato ='';
while ($riga = mssql_fetch_array($risultato)) {
    $html_risultato .= '<tr>';
    $html_risultato .= '<td> '.$riga["idcategoria"].'</td>';
    $html_risultato .= '<td> '.$riga["nomecategoria"].'</td>';
    $html_risultato .= '</tr>';
}
mssql_close($connessione);
// HTML ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<HTML>
<head>
<title> Test Connessione MS SQL Server </title>
<meta name="Generator" content="EditPlus">
<meta name="Author" content="">
<meta name="Keywords" content="">
<meta name="Description" content="">
</head>
<body>
<table border="1">
    <tr>
        <th colspan="3">
            <h3>Tabella Risultati Query: <?php echo $interrogazione; ?></h3>
        </th>
    </tr>
    <tr>
        <th>ID categoria</th>
        <th>Nome categoria</th>
    </tr>
    <?php echo $html_risultato; ?>
</table>
</body>
</html>