Error when read excel from mssql query using OPENDATASOURCE

Error when read excel from mssql query using OPENDATASOURCE - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Error when read excel from mssql query using OPENDATASOURCE (/showthread.php?tid=11732)

Error when read excel from mssql query using OPENDATASOURCE - El Forum - 09-21-2008

[eluser]bharata[/eluser]
hello members,

I'm newbie in using codeigniter as development framework, and now I just try to convert my application using CI, but I got a problem when using query with OPENDATASOURCE,

as you know, I use mssql server as my previous database, and I really love it.

here is my code for example :

Code:
$sql= "SELECT CAST(emp_id AS NUMERIC) AS emp_id, loan_code, loan_nper, mem_thp, "

    . "loan_pv, buy_rate, sell_rate, start_date "

    . "dbo.fn_pmt(sell_rate, loan_nper , -loan_pv, 0, 0) AS payment "

    . "FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', "

    . "'Data Source=\"".DOCUMENT_ROOT."/finance/uploads/".$file."\";User ID=;Password=;Extended properties=Excel 5.0')...Sheet1$"

            ;

$query = $this->db->query($sql);

when I ran this script, I got this confusing error message :

Quote:An Error Was Encountered
Error Number:

SELECT CAST(emp_id AS NUMERIC) AS emp_id , loan_code, loan_nper, mem_thp, loan_pv, buy_rate, sell_rate, start_date dbo.fn_pmt(sell_rate, loan_nper , -loan_pv, 0, 0) AS payment FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="C:/Program Files/xampp/htdocs/finance/uploads/test_2.xls";User ID=;Password=;Extended properties=Excel 5.0')...Sheet1$

I have no problem with my connection to the database, cause when I use insert/update/delete ot some queries which not using OPENDATASOURCE, it's okay with no error.

I just can't figuring out what happen with the error in my code, coz it's not explain very well what error occured, just the sql string on the screen.

And when I try those sql string in query analyzer, it's run well.

Has some one figuring out what the problems are /happened ?

best regards,

and thanks b4

--
bharata

Error when read excel from mssql query using OPENDATASOURCE - El Forum - 09-21-2008

[eluser]bharata[/eluser]
Just share what I've done,

I've found the problem, while it's caused by sql server need to set ansi_nulls and ansi_warnings ON before run the sql I meant.

soo, the thing just I did is query for set ansi_nulls ON and set ansi_warnings ON,
here is :

Code:
$sql= "SELECT CAST(emp_id AS NUMERIC) AS emp_id, loan_code, loan_nper, mem_thp, "

    . "loan_pv, buy_rate, sell_rate, start_date "

    . "dbo.fn_pmt(sell_rate, loan_nper , -loan_pv, 0, 0) AS payment "

    . "FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', "

    . "'Data Source=\"".DOCUMENT_ROOT."/finance/uploads/".$file."\";User ID=;Password=;Extended properties=Excel 5.0')...Sheet1$"

            ;

$query = $this->db->query('SET ANSI_NULLS ON');

$query = $this->db->query('SET ANSI_WARNINGS ON');

$query = $this->db->query($sql);

The thing I still confused is why CI still didn't notice the error message nor number? I've to used conventional mssql_query to check the error and found the solution through google.

thks folks,

--
bharata