load data from micros sp into table

[raphael75]

I would like to load data from the micros.sp_R_sys_menuitem_fam_grp stored procedure into a temporary table so I can run some queries on it. I tried:

select
*
into
#t_test_sp
from
micros.sp_R_sys_menuitem_fam_grp();

but I get this error:

COMMIT/ROLLBACK not allowed within atomic operation

Is there a different way to do this? Thanks!

 

[raphael75]

BTW, it's Sybase 11.0.1.2596

 

[mvelazquez]

From what I understand , the select into command does not work with SP , because the select does not work properly to execute a procedure.

Perhaps these two links will be useful:

[URL unfurl="true"]http://stackoverflow.com/questions/1443203/how-do-i-select-from-a-stored-procedure-in-sybase[/url]
[URL unfurl="true"]http://www.sypron.nl/proctab.html[/url]

 

[raphael75]

I was able to create a php script that uses dbisql to grab the contents of micros.sp_R_sys_menuitem_fam_grp and save it in a csv. I then was able to load the data into our MySQL database and query it there. It took forever to grab the data (3.2GB!) but it worked.

After that, I copied the contents of the stored procedure to a new one and added this at the top:

alter PROCEDURE "custom"."custom_sp_R_sys_menuitem_fam_grp_mdf" ([highlight #CC0000]in business_date_start timestamp,in business_date_end timestamp[/highlight])

and modified these lines:

SET @start_business_date = COALESCE(@start_business_date,DATE([highlight #CC0000]business_date_start[/highlight])); -- YEARS(NOW(*),-10)
    SET @end_business_date = COALESCE(@end_business_date,DATE([highlight #CC0000]business_date_end[/highlight])); -- YEARS(NOW(*),10)

and if I call this new SP I can pass in a start and end date. I just tested it and it appears to work.