Welcome Guest, Not a member yet? Register   Sign In
Error when read excel from mssql query using OPENDATASOURCE
#1

[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
#2

[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




Theme © iAndrew 2016 - Forum software by © MyBB