Active Record with mssql ?

[eluser]Matthieu Fauveau[/eluser]
You don't need to know if you are using it or not. It's Active Record, it will handle itself which driver to use.

Just modify the file I indicated and remove the brackets from your active record query and it should work in both MySQL or MSSQL environment.

Hoping I'm clearer now

Edit :
Zcoders > Damn, you edited your previous post!
Derek > I'll do that in a bit. No problem.

[eluser]ZCoders[/eluser]
My question was stupid, why edit the post. :-)

[eluser]Derek Jones[/eluser]
Thank you Matthieu, that will help us make sure it doesn't fall through the cracks, and will give users in the future something to search on.

[eluser]woodydude[/eluser]
Hi,

I'm having a similar issue with MSSQL however it doesn't matter if I'm using Active Record or not. (tbh I'd prefer working with MySQL but the current website's written using this DB and I'm slowly migrating the site over to PHP )

The erroneous response I'm getting is:

A Database Error Occurred
Error Number:
SELECT * FROM Dir_Location

The code I'm having an issue with is:

Code:

$query = $this->db->query('SELECT * FROM Dir_Location');
return $query->result_array();

My db connection settings are:

Code:

$db['default']['hostname'] = "localhost";
$db['default']['username'] = "user1";
$db['default']['password'] = "mypassword";
$db['default']['database'] = "intranet";
$db['default']['dbdriver'] = "mssql";
$db['default']['dbprefix'] = "";
$db['default']['pconnect'] = FALSE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = "";
$db['default']['char_set'] = "utf8";
$db['default']['dbcollat'] = "Latin1_General_CI_AS";

I've tried putting the table name in [] brackets which doesn't seem to help in any way, I've read through all the links on this thread however any pointers in the right direction would be greatly received.

[eluser]Randy Casburn[/eluser]
Hi Woody - Open your config.php file and max out your error logging setting. We need to caputure all the errors being generated. The DB debug setting just splats these non-descript things on the display. The error logs will tell us what the developers need us to know.

Do that, run through it, open the error log and tell us what your log says.

Randy

[eluser]woodydude[/eluser]
Hi Randy,
Thanks for the prompt reply (I got it when I got home, doh). Right I've got the error log:

Code:

DEBUG - 2008-09-02 11:29:57 --> Config Class Initialized
DEBUG - 2008-09-02 11:29:57 --> Hooks Class Initialized
DEBUG - 2008-09-02 11:29:57 --> URI Class Initialized
DEBUG - 2008-09-02 11:29:57 --> No URI present. Default controller set.
DEBUG - 2008-09-02 11:29:57 --> Router Class Initialized
DEBUG - 2008-09-02 11:29:57 --> Output Class Initialized
DEBUG - 2008-09-02 11:29:57 --> Input Class Initialized
DEBUG - 2008-09-02 11:29:57 --> XSS Filtering completed
DEBUG - 2008-09-02 11:29:57 --> XSS Filtering completed
DEBUG - 2008-09-02 11:29:57 --> XSS Filtering completed
DEBUG - 2008-09-02 11:29:57 --> XSS Filtering completed
DEBUG - 2008-09-02 11:29:57 --> XSS Filtering completed
DEBUG - 2008-09-02 11:29:57 --> XSS Filtering completed
DEBUG - 2008-09-02 11:29:57 --> XSS Filtering completed
DEBUG - 2008-09-02 11:29:57 --> XSS Filtering completed
DEBUG - 2008-09-02 11:29:57 --> XSS Filtering completed
DEBUG - 2008-09-02 11:29:57 --> XSS Filtering completed
DEBUG - 2008-09-02 11:29:57 --> Global POST and COOKIE data sanitized
DEBUG - 2008-09-02 11:29:57 --> Language Class Initialized
DEBUG - 2008-09-02 11:29:57 --> Loader Class Initialized
DEBUG - 2008-09-02 11:29:57 --> Database Driver Class Initialized
DEBUG - 2008-09-02 11:29:57 --> Controller Class Initialized
DEBUG - 2008-09-02 11:29:57 --> Model Class Initialized
ERROR - 2008-09-02 11:29:57 --&gt; Severity: Warning  --&gt; mssql_query() [<a href='function.mssql-query'>function.mssql-query</a>]: message: The SELECT permission was denied on the object 'Dir_Location', database 'Intranet', schema 'dbo'. (severity 14) D:\cdsintranet\ci\database\drivers\mssql\mssql_driver.php 106
ERROR - 2008-09-02 11:29:57 --&gt; Severity: Warning  --&gt; mssql_query() [<a href='function.mssql-query'>function.mssql-query</a>]: Query failed D:\cdsintranet\ci\database\drivers\mssql\mssql_driver.php 106
ERROR - 2008-09-02 11:29:57 --&gt; Severity: Warning  --&gt; mssql_query() [<a href='function.mssql-query'>function.mssql-query</a>]: message: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. (severity 16) D:\cdsintranet\ci\database\drivers\mssql\mssql_driver.php 106
ERROR - 2008-09-02 11:29:57 --&gt; Severity: Warning  --&gt; mssql_query() [<a href='function.mssql-query'>function.mssql-query</a>]: Query failed D:\cdsintranet\ci\database\drivers\mssql\mssql_driver.php 106
DEBUG - 2008-09-02 11:29:57 --&gt; DB Transaction Failure
ERROR - 2008-09-02 11:29:57 --&gt; Query error:
DEBUG - 2008-09-02 11:29:57 --&gt; Language file loaded: language/english/db_lang.php

It looks like there's a permission issue, is that correct? It's a bit strange because I'm using the same login details in the (horrid) .net installation which is running along side the php install on the same server.

Thanks in advance,

Thanks in advance

[eluser]Randy Casburn[/eluser]
Well the DSN seems to be working, connecting and logging you in to the server (at least). This is either a DB or table specific permissions problem set on the DB itself. Have you attempted to log in manually with this very same DSN to see if you can access this same DB/table? I'd try that next. My guess is you won't be able to and will end up setting permissions for this user internally in MSSQL for this table. But that's a WAG.

Randy

[eluser]woodydude[/eluser]
Ah ha! There was an issue with the permissions for that user. It's all hunky good now, Thanks

[eluser]Randy Casburn[/eluser]
You're very welcome.

Randy