CodeIgniter Forums

Full Version: SQL Server Insert-Output issue
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Greetings community!

I have hit a wall here. I'm working with SQL Server, which is not quite familiar to me but, I'm managing it.

Here's the problem. I'm sending these sql instructions in on single query like tihis:

$recno = $this->db->query('insert into dbo.SC0010 (R_E_C_N_O_)
         output inserted.[R_E_C_N_O_]
         select max(r.R_E_C_N_O_) + 1 from dbo.SC0010 r', false, true);

Which is working just fine, for most tables. I get what I want which is the new inserted RECNO. However one particular table has a trigger, which makes this instruction to fail and return an error. I've looked into the error and found a solution which works fine if I run the query in a SQL Manager tool, but it doesn't work on CI. Here's the query with the solution applied to it:

$recno = $this->db->query('DECLARE @PedItens TABLE ( [RECNO] INT)
         insert into dbo.ZL2010 (R_E_C_N_O_)
         output inserted.[R_E_C_N_O_] into @PedItens
         select max(i.R_E_C_N_O_) + 1 from dbo.ZL2010 i
         select RECNO from @PedItens', false, true);

I applied a print_r in $recno and the result is this:

CI_DB_sqlsrv_result Object ( [scrollable] => buffered [conn_id] => Resource id #48 [result_id] => Resource id #61 [result_array] => Array ( ) [result_object] => Array ( ) [custom_result_object] => Array ( ) [current_row] => 0 [num_rows] => [row_data] => )

The query runs without errror, the new RECNO is inserted but there's no rows returned in it.

Anyone has gone through this kind of problem? Any help would be welcome.

Thank you.

I haven't gone through this issue before but I have a suggestion. Why not create a stored procedure and then do a simple query call to it. That might resolve your issue.