Welcome Guest, Not a member yet? Register   Sign In
Problem connecting to MS SQL Server
#1

[eluser]Circus-Killer[/eluser]
I am having troubles connecting to a MS SQL server.
Here are the values set in database.php:

$active_group = "default";
$active_record = TRUE;

$db['default']['hostname'] = "***.***.***.***";
$db['default']['username'] = "****";
$db['default']['password'] = "****";
$db['default']['database'] = "lso_new";
$db['default']['dbdriver'] = "mssql";
$db['default']['dbprefix'] = "";
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = "";
$db['default']['char_set'] = "utf8";
$db['default']['dbcollat'] = "utf8_general_ci";

I have no idea what the problem is. I think it may have to do with the charset and dbcollat values. i'm pretty sure that dbcollat value must be "SQL_Latin1_General_CP1_CI_AS". but i have no idea how to figure out what the char_set should be.

if i am wrong about the dbcollat or someone knows what the charset should be or how i can find out what the charset should be, please let me know.

thanks in advance.
#2

[eluser]Circus-Killer[/eluser]
okay, i've now tried changing charset and dbcollat to:

$db['default']['char_set'] = "windows-1252";
$db['default']['dbcollat'] = "sql_latin1_general_cp1_ci_as";

yet it still does not work. any ideas how i can get the connection to ms sql working?
#3

[eluser]senojeel[/eluser]
Just curious, what version of SQL Server are you connecting to?
#4

[eluser]polaris1927[/eluser]
If you are running version 1.6.1 modify the mssql_driver.php file as follows:


Code:
Discovered the cause.

Version 1.6.0, Module: mssql_driver


function _protect_identifiers($item, $affect_spaces = TRUE, $first_word_only = FALSE)
{
    // MSSQL doesn't use backticks
    return $item;
}

Was replaced in Version 1.6.1 with:


function _protect_identifiers($item, $first_word_only = FALSE)
{
    
    if (is_array($item))
    {    
        $escaped_array = array();

        foreach($item as $k=>$v)
        {
            $escaped_array[$this->_protect_identifiers($k)] = $this->_protect_identifiers($v, $first_word_only);
        }

        return $escaped_array;
    }    

    // This function may get "item1 item2" as a string, and so
    // we may need ""item1" "item2"" and not ""item1 item2""
    if (ctype_alnum($item) === FALSE)
    {
        if (strpos($item, '.') !== FALSE)
        {
            $aliased_tables = implode(".",$this->ar_aliased_tables).'.';
            $table_name =  substr($item, 0, strpos($item, '.')+1);
            $item = (strpos($aliased_tables, $table_name) !== FALSE) ? $item = $item : $this->dbprefix.$item;
        }

        // This function may get "field >= 1", and need it to return ""field" >= 1"
        $lbound = ($first_word_only === TRUE) ? '' : '|\s|\(';

        $item;// = preg_replace('/(^'.$lbound.')([\w\d\-\_]+?)(\s|\)|$)/iS', '$1"$2"$3', $item);
    }
    else
    {
        
        return "\"{$item}\"";
    }

    $exceptions = array('AS', '/', '-', '%', '+', '*');
        
    foreach ($exceptions as $exception)
    {
        
        if (stristr($item, " \"{$exception}\" ") !== FALSE)
        {
            $item = preg_replace('/ "('.preg_quote($exception).')" /i', ' $1 ', $item);
        }
    }
    return $item;
}

For a quick fix i reverted to the 1.6.0 code.

JC
#5

[eluser]senojeel[/eluser]
I already had this fix in place and I get the error:
An Error Was Encountered
Unable to connect to your database server using the provided settings.
#6

[eluser]senojeel[/eluser]
Circus-Killer, what version of SQL Server are you using?
#7

[eluser]winter[/eluser]
Hi all,

I'm sorry for semi-hijacking this thread, but it's the exact same topic.

I am trying to connect to a MSSQL DB running on Windows Server 2003. SQL It's running Server version 8.0.760. I am running debian(ubuntu) server in VM(virtualbox) kernel: 2.6.22-14-generic.

Here is what I have in system/application/config/database.php

Code:
$db['default']['hostname'] = "---.---.---.---";
$db['default']['username'] = "---";
$db['default']['password'] = "---";
$db['default']['database'] = "---";
$db['default']['dbdriver'] = "mssql";
$db['default']['dbprefix'] = "";
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = "";
$db['default']['char_set'] = "utf8";
$db['default']['dbcollat'] = "utf8_general_ci";

Of course with the correct info instead of "---".

I have:
Code:
$autoload['libraries'] = array('database');
in system/application/config/autoload.php

I follow the tutorials and had everything working fine. Then I realised I really should be getting the data from an MSSQL server, as that is where the data is. I checked, and CodeIgniter seems to support it. So I put in the above, and my little test site comes up blank, indicating an error.

If anyone has any advice, I appreciate hearing from you.

Thanks in advance.




Theme © iAndrew 2016 - Forum software by © MyBB