1. 

   pfsit, Member

   I have a stored procedure that runs a query which includes calling another stored procedure and putting the results in an temp table.

   When I run it on the server it runs fine (takes about 1.5 minutes), returns about 100k results. I can call it from a php file on the same server as mydbr it loads fine.

   When I try to run it in myDBR it immediately returns all of the columns with no results. I have tried using the refresh feature and it never returns any results.

   Is there anything else I should try?

   Edit: sorry didn't realize I was under the Installation section
2. 

   myDBR Team, Key Master

   If the execution returns an empty set immediately, it would indicate that the query does not return any data. If there would be a timeout, it would not return the empty set immediately.

   Does the query return an empty set if you run the exactly same procedure call in the SQL Editor as you do in the server side.

   What is the database you are using (and which db driver)? If you are using MS SQL Server, there might be different connection initialization (ARITHABORT/ANSI_NULLS) which might cause the execution to abort without error messages. You can change the initialization parameters by defining the db_connection -> sql_server_init/sqlsrv_server_init in the user/defaults.php.

   Note that with 100K rows your browser will have hard time rendering the HTML (will require a lot of memory). Although, your first problem is to get the data into client.

   --
   myDBR Team
3. 

   pfsit, Member

   I am using a MS-SQL 2012 R2 Database, with the sql_server driver

   I tried setting this in the defaults.php file
   $mydbr_defaults['db_connection']['sqlsrv_server_init'] = 'SET ANSI_WARNINGS ON;SET QUOTED IDENTIFIER OFF;';

   But it still just goes to the result screen with empty results, but the correct column headings.

   I can call the stored procedure in SSMS and in another PHP file and it returns results. I have a couple of others that I have been putting in that have the same problem

   The things in common on my procedures that aren't running is that they all take a little more than a minute and a half, and they call stored procedures and put results into a temp table with-in the procedure for the report.

   I have other reports that run just fine.
4. 

   myDBR Team, Key Master

   Just to be sure check the connection initialization first. The report below will show the settings. Run the procedure from myDBR and from SSMS to see if the connection initialization (@@options) is different. That could explain the immediate empty result (command execution is aborted without errors).

   create procedure sp_DBR_sql_server_options
   
   as
   
   begin
   
   declare @options int
   
   select @options = @@options
   
   create table #options (
   
   opt varchar(50)
   
   )
   
   if ( (1 & @options) = 1 ) insert into #options values ('DISABLE_DEF_CNST_CHK')
   
   if ( (2 & @options) = 2 )  insert into #options values ('IMPLICIT_TRANSACTIONS' )
   
   if ( (4 & @options) = 4 )  insert into #options values ('CURSOR_CLOSE_ON_COMMIT')
   
   if ( (8 & @options) = 8 )  insert into #options values ('ANSI_WARNINGS')
   
   if ( (16 & @options) = 16 )  insert into #options values ('ANSI_PADDING')
   
   if ( (32 & @options) = 32 )  insert into #options values ('ANSI_NULLS')
   
   if ( (64 & @options) = 64 )  insert into #options values ('ARITHABORT')
   
   if ( (128 & @options) = 128 )  insert into #options values ('ARITHIGNORE' )
   
   if ( (256 & @options) = 256 )  insert into #options values ('QUOTED_IDENTIFIER')
   
   if ( (512 & @options) = 512 )  insert into #options values ('NOCOUNT')
   
   if ( (1024 & @options) = 1024 )  insert into #options values ('ANSI_NULL_DFLT_ON')
   
   if ( (2048 & @options) = 2048 )  insert into #options values ('ANSI_NULL_DFLT_OFF')
   
   if ( (4096 & @options) = 4096 )  insert into #options values ('CONCAT_NULL_YIELDS_NULL')
   
   if ( (8192 & @options) = 8192 )  insert into #options values ('NUMERIC_ROUNDABORT')
   
   if ( (16384 & @options) = 16384 )  insert into #options values ('XACT_ABORT')
   
   select * from #options
   
   end
   
   

   
   --
   myDBR Team
5. 

   pfsit, Member

   Sorry for the time in between posts

   The problems I run into seem to be only when my query runs a different stored procedure inside of the query and dumps the results into a temporary table.

   When I run the above command I get the following from SSMS

   opt
   ANSI_WARNINGS
   ANSI_PADDING
   ANSI_NULLS
   ARITHABORT
   QUOTED_IDENTIFIER
   ANSI_NULL_DFLT_ON
   CONCAT_NULL_YIELDS_NULL

   myDBR gives me the following result

   opt
   ANSI_PADDING
   ANSI_NULLS
   QUOTED_IDENTIFIER
   ANSI_NULL_DFLT_ON
   CONCAT_NULL_YIELDS_NULL
   # 5
6. 

   myDBR Team, Key Master

   Change the $mydbr_defaults['db_connection']['sqlsrv_server_init'] (assuming you are using the SQLSRV-driver) to match the SSMS settings. This way the query should work identically between the platforms.

   --
   myDBR Team
7. 

   pfsit, Member

   Sorry it would probably help to see the query. I have tried setting ansi_warnings and arithabort off and adding a refresh and running it for smaller date ranges.

   if object_id('sp_DBR_Beverage_Report','P') is not null
   drop procedure sp_DBR_Beverage_Report
   go
   create procedure sp_DBR_Beverage_Report
   @startdate datetime,
   @enddate datetime
   as
   begin

   SET ANSI_WARNINGS OFF;
   SET ARITHABORT OFF;

   select 'dbr.refresh', 90, 'counter';

   SET @startdate = CAST(@startdate AS DATE);
   SET @enddate = CAST(@enddate AS DATE);

   IF OBJECT_ID('tempdb.dbo.#TempResults','U') IS NOT NULL
   DROP TABLE #TempResults;

   IF OBJECT_ID('tempdb.dbo.#coolersbytype','U') IS NOT NULL
   DROP TABLE #coolersbytype;

   IF OBJECT_ID('tempdb.dbo.#coolersbymarket','U') IS NOT NULL
   DROP TABLE #coolersbymarket;

   IF OBJECT_ID('tempdb.dbo.#mmlocations','U') IS NOT NULL
   DROP TABLE #mmlocations;

   IF OBJECT_ID('tempdb.dbo.#BevManufacturers','U') IS NOT NULL
   DROP TABLE #BevManufacturers;

   CREATE TABLE #TempResults (
   pos_code varchar(32) NULL,
   pos_description varchar(32) NULL,
   loc_code varchar(32) NULL,
   loc_description varchar(32) NULL,
   cus_code varchar(32) NULL,
   cus_description varchar(32) NULL,
   nat_code varchar(32) NULL,
   nat_description varchar(32) NULL,
   sit_code varchar(32) NULL,
   sit_description varchar(32) NULL,
   revenue_collected varchar(32) NULL,
   product_costs varchar(32) NULL,
   total_comm varchar(32) NULL,
   total_taxes varchar(32) NULL,
   items_sold varchar(32) NULL,
   price varchar(32) NULL,
   net_revenue varchar(32) NULL,
   gross varchar(32) NULL,
   net_profit varchar(32) NULL,
   pro_code varchar(32) NULL,
   pro_description varchar(32) NULL,
   Product_SKU varchar(32) NULL,
   pdf_code varchar(32) NULL,
   pdf_description varchar(32) NULL,
   pro_source_id varchar(32) NULL,
   pro_id varchar(32) NULL,
   pkp_source_id varchar(32) NULL,
   pkp_id varchar(32) NULL,
   pdf_source_id varchar(32) NULL,
   pdf_id varchar(32) NULL,
   pos_source_id varchar(32) NULL,
   pos_id varchar(32) NULL,
   loc_source_id varchar(32) NULL,
   loc_id varchar(32) NULL,
   cus_source_id varchar(32) NULL,
   cus_id varchar(32) NULL,
   nat_source_id varchar(32) NULL,
   nat_id varchar(32) NULL,
   sit_source_id varchar(32) NULL,
   sit_id varchar(32) NULL,
   pro_revenue varchar(32) NULL,
   pro_net_revenue varchar(32) NULL,
   pro_gross varchar(32) NULL,
   pro_gross_pr varchar(32) NULL,
   pro_net_profit varchar(32) NULL,
   pro_net_profit_pr varchar(32) NULL,
   pdf_revenue varchar(32) NULL,
   pdf_net_revenue varchar(32) NULL,
   pdf_gross varchar(32) NULL,
   pdf_gross_pr varchar(32) NULL,
   pdf_net_profit varchar(32) NULL,
   pdf_net_profit_pr varchar(32) NULL,
   pos_revenue varchar(32) NULL,
   pos_net_revenue varchar(32) NULL,
   pos_gross varchar(32) NULL,
   pos_gross_pr varchar(32) NULL,
   pos_net_profit varchar(32) NULL,
   pos_net_profit_pr varchar(32) NULL,
   loc_revenue varchar(32) NULL,
   loc_net_revenue varchar(32) NULL,
   loc_gross varchar(32) NULL,
   loc_gross_pr varchar(32) NULL,
   loc_net_profit varchar(32) NULL,
   loc_net_profit_pr varchar(32) NULL,
   cus_revenue varchar(32) NULL,
   cus_net_revenue varchar(32) NULL,
   cus_gross varchar(32) NULL,
   cus_gross_pr varchar(32) NULL,
   cus_net_profit varchar(32) NULL,
   cus_net_profit_pr varchar(32) NULL,
   nat_revenue varchar(32) NULL,
   nat_net_revenue varchar(32) NULL,
   nat_gross varchar(32) NULL,
   nat_gross_pr varchar(32) NULL,
   nat_net_profit varchar(32) NULL,
   nat_net_profit_pr varchar(32) NULL,
   sit_revenue varchar(32) NULL,
   sit_net_revenue varchar(32) NULL,
   sit_gross varchar(32) NULL,
   sit_gross_pr varchar(32) NULL,
   sit_net_profit varchar(32) NULL,
   sit_net_profit_pr varchar(32) NULL,
   pos_grp_param_desc varchar(32) NULL,
   pos_param_code varchar(32) NULL,
   pos_param_desc varchar(32) NULL,
   loc_grp_param_desc varchar(32) NULL,
   loc_param_code varchar(32) NULL,
   loc_param_desc varchar(32) NULL,
   cus_grp_param_desc varchar(32) NULL,
   cus_param_code varchar(32) NULL,
   cus_param_desc varchar(32) NULL,
   pdf_grp_param_desc varchar(32) NULL,
   pdf_param_code varchar(32) NULL,
   pdf_param_desc varchar(32) NULL,
   pro_grp_param_desc varchar(32) NULL,
   pro_param_code varchar(32) NULL,
   pro_param_desc varchar(32) NULL,
   sit_grp_param_desc varchar(32) NULL,
   sit_param_code varchar(32) NULL,
   sit_param_desc varchar(32) NULL,
   nat_param_code varchar(32) NULL,
   nat_param_desc varchar(32) NULL,
   GroupNumber varchar(32) NULL
   )

   INSERT INTO #TempResults
   EXEC Vendmax.dbo.RPT_Financials_By_PDF
   @NPB_Date = @startdate,
   @NPE_Date = @enddate,
   @STR_Basis = 'Product Sales',
   @STR_Top_Level_Grouping = 'POS',
   @STR_Include_MicroMarket = 'MicroMarket Only',
   @STR_Show_MM_Sales_Based_On = 'Actual MM Sales',
   @STR_Level_Of_Detail = 'PRO',
   @STR_Machine_Type = 'All',
   @STR_Point_Of_Sale_Type = 'All',
   @STR_Show_Outsourced = 'No',
   @STR_Choose_Record = 'All',
   @STR_Packaged_Product_Detail = 'Yes',
   @STR_Vend_Visits = 'All Vend Visits';

   CREATE TABLE #coolersbymarket (
   locID varchar(32) NULL,
   locSource varchar(32) NULL,
   locationCode varchar(32) NULL,
   nveCode varchar(32) NULL,
   locationDesc varchar(32) NULL,
   posID varchar(32) NULL,
   posDesc varchar(32) NULL,
   nveID varchar(32) NULL,
   customer varchar(32) NULL,
   locationID varchar(32) NULL,
   nveDesc varchar(32) NULL
   )

   INSERT INTO #coolersbymarket
   SELECT
   loc.loc_id AS 'locID'
   ,loc.loc_source_id AS 'locSource'
   ,loc.code AS 'locationCode'
   ,nve.code AS 'nveCode'
   ,loc.description AS 'locationDesc'
   ,pos.pos_id AS 'posID'
   ,pos.description AS 'posDesc'
   ,nve.nve_id AS 'nveID'
   ,cus.description AS 'customer'
   ,loc.loc_id AS 'locationID'
   --,loc.code AS 'locationCode'
   --,nve.code AS 'nveCode'
   ,nve.description AS 'nveDesc'
   FROM Vendmax.dbo.non_vending_equipment nve
   JOIN Vendmax.dbo.equipment_placement_history eph
   ON nve.nve_id = eph.nve_id
   AND nve.nve_source_id = eph.nve_source_id
   JOIN Vendmax.dbo.points_of_sale pos
   ON eph.pos_id = pos.pos_id
   AND eph.pos_source_id = pos.pos_source_id
   JOIN Vendmax.dbo.locations loc
   ON pos.loc_id = loc.loc_id
   AND pos.loc_source_id = loc.loc_source_id
   JOIN Vendmax.dbo.customers cus
   ON loc.cus_id = cus.cus_id
   AND loc.cus_source_id = cus.cus_source_id
   WHERE eph.placement_status = 'A'
   AND LEFT(nve.code,3) IN ('60-','50-','40-')
   --AND pos.pos_active = 'N'
   AND nve.description LIKE '%cooler%'
   --AND LEFT(pos.description, 3) != 'zzz'
   AND loc.loc_id IN
   (SELECT loc_id FROM Vendmax.dbo.points_of_sale WHERE pos_type = 'M' AND pos_active = 'Y')
   ORDER BY cus.description,loc.code
   ;

   CREATE TABLE #mmlocations (
   locCode varchar(32) NULL,
   locID varchar(32) NULL,
   locSource varchar(32) NULL
   );

   CREATE TABLE #coolersbytype (
   locID varchar(32) NULL,
   locSource varchar(32) NULL,
   locCode varchar(32) NULL,
   hasCoke varchar(32) NULL,
   hasPepsi varchar(32) NULL
   )

   INSERT INTO #mmlocations
   SELECT
   MAX(locationCode) AS 'locCode',
   locID,
   locSource
   FROM #coolersbymarket
   GROUP BY locID, locSource;

   INSERT INTO #coolersbytype
   SELECT
   MAX(mml.locID) AS 'locID'
   ,MAX(mml.locSource) AS 'locSource'
   ,MAX(mml.locCode) AS 'locCode'
   ,(SELECT COUNT(*) FROM #coolersbymarket WHERE MAX(mml.locCode) = locationCode AND LEFT(nveCode,3) = '50-') AS 'hasCoke'
   ,(SELECT COUNT(*) FROM #coolersbymarket WHERE MAX(mml.locCode) = locationCode AND LEFT(nveCode,3) = '60-') AS 'hasPepsi'
   FROM #mmlocations mml
   JOIN #coolersbymarket cbm
   on mml.locCode = cbm.locationCode
   AND mml.locCode = cbm.locationCode
   GROUP BY mml.locCode

   CREATE TABLE #BevManufacturers (
   productID varchar(32) NULL
   ,proSource varchar(32) NULL
   ,manufacturerID varchar(32) NULL
   )

   INSERT INTO #BevManufacturers
   SELECT pro.pro_id
   ,MAX(pro.pro_source_id)
   ,MAX(grpm.grp_id)
   FROM Vendmax.dbo.products pro
   JOIN Vendmax.dbo.group_members grpm
   ON pro.pro_id = grpm.member_id
   AND pro.pro_source_id = grpm.member_source_id
   WHERE grpm.grp_id IN ('5639364','5645249','5698088','5717422','4679846', '5727297', '5800150')
   GROUP BY pro.pro_id;

   SELECT
   CONCAT(DATENAME(month, @startdate),' ',DATEPART(day,@startdate),', ',DATEPART(year,@startdate)) AS 'ORT FROM DATE'
   ,CONCAT(DATENAME(month, @enddate),' ',DATEPART(day,@enddate),', ',DATEPART(year,@enddate)) AS 'ORT TO DATE'
   ,CASE when bev.manufacturerID = '5639364' then 'Pepsi'
   when bev.manufacturerID = '5645249' then 'Coke'
   when bev.manufacturerID = '5698088' then 'Red Bull'
   when bev.manufacturerID = '5717422' then 'Monster'
   when bev.manufacturerID = '4679846' then '7-UP'
   when bev.manufacturerID = '5727297' then 'VSA'
   when bev.manufacturerID = '5800150' then 'All Brands Beverage'
   else 'Not in Pepsi/Coke Product Group'
   end AS 'Beverage Product Manufacturer'
   ,'206' AS 'Franchise OPS'
   ,'Premier Food Service' AS 'Franchise Name'
   ,temp.cus_description AS 'Customer'
   ,temp.loc_description AS 'Location'
   ,loc.addr1 AS 'Address 1'
   ,loc.addr2 AS 'Address 2'
   ,loc.addr3 AS 'Address 3'
   ,loc.city AS 'City'
   ,loc.state AS 'State'
   ,loc.zip AS 'Zip'
   ,pos_code AS 'POS Code'
   ,CASE
   WHEN cool.hasCoke > '0' AND cool.hasPepsi = '0' THEN 'COKE'
   WHEN cool.hasCoke = '0' AND cool.hasPepsi > '0' THEN 'CONSUMER'
   WHEN cool.hasCoke > '0' AND cool.hasPepsi > '0' THEN 'COKE/CONSUMER'
   WHEN cool.hasCoke = '0' AND cool.hasPepsi = '0' THEN 'CONSUMER'
   ELSE 'Not Found'
   END AS 'Cooler'
   ,(SELECT TOP 1 barcode
   FROM Vendmax.dbo.barcode_map WHERE pkp_id = pkp.pkp_id AND pkp_source_id = pkp.pkp_source_id ORDER BY LEN(barcode) DESC) AS 'UPC'
   ,temp.pro_description AS 'Product Name'
   ,temp.price AS 'TotalPrice'
   ,temp.items_sold AS 'Quantity'
   ,temp.pdf_description AS 'Product Family'
   FROM #TempResults temp
   LEFT JOIN Vendmax.dbo.locations loc
   ON temp.loc_id = loc.loc_id
   AND temp.loc_source_id = loc.loc_source_id
   JOIN Vendmax.dbo.products pro
   ON temp.pro_id = pro.pro_id
   AND temp.pro_source_id = pro.pro_source_id
   JOIN Vendmax.dbo.packaged_products pkp
   ON pro.pro_id = pkp.pro_id
   AND pro.pro_source_id = pkp.pro_source_id
   AND (pkp.uni_id = 1 AND pkp.uni_source_id = 1)
   LEFT JOIN #coolersbytype cool
   ON loc.loc_id = cool.locID
   AND loc.loc_source_id = cool.locSource
   LEFT JOIN #BevManufacturers bev
   ON temp.pro_id = bev.productID
   AND temp.pro_source_id = bev.proSource
   WHERE
   LEFT(temp.pdf_description,1) != 'z'
   AND LEFT(temp.pdf_description,3) != 'BAG'
   ORDER BY cus_description, loc_description;

   end
   go
8. 

   myDBR Team, Key Master

   A bit long but nothing major there.

   You have the "select 'dbr.refresh', 90, 'counter';" in the report and the report produces 100K rows into the temp table. You really want to run the query on every 90 seconds?

   And the GROUP BY in the following query looks bit weird (does not really make sense):

   SELECT
   
     MAX(mml.locID) AS 'locID'
   
     ,MAX(mml.locSource) AS 'locSource'
   
     ,MAX(mml.locCode) AS 'locCode'
   
     ,(SELECT COUNT(*) FROM #coolersbymarket WHERE MAX(mml.locCode) = locationCode AND LEFT(nveCode,3) = '50-') AS 'hasCoke'
   
     ,(SELECT COUNT(*) FROM #coolersbymarket WHERE MAX(mml.locCode) = locationCode AND LEFT(nveCode,3) = '60-') AS 'hasPepsi'
   
   FROM #mmlocations mml
   
   JOIN #coolersbymarket cbm on mml.locCode = cbm.locationCode AND mml.locCode = cbm.locationCode
   
   GROUP BY mml.locCode
   
   

   --
   myDBR Team
9. 

   pfsit, Member

   Sorry you are correct on the refresh, I had set that previously when I thought it would poll for new data ( for the query to finish running on the server side).

   I have turned the refresh off and then set the following on the user/defautlts.php file

   $mydbr_defaults['db_connection']['sqlsrv_server_init'] = 'SET ANSI_WARNINGS ON;SET ARITHABORT OFF;SET ANSI_NULLS ON;SET QUOTED_IDENTIFIER OFF;';

   The report still doesn't return any data. When I run it for a month it takes ~60 seconds to run on the server side, but the page is loading immediately.
10. 

    myDBR Team, Key Master

    Are you using same credentials to run the queries on both platforms? Does myDBR have execute rights tp Vendmax.dbo.RPT_Financials_By_PDF?

    The next step would be to isolate the problem. Put in debug selects so you see what queries are executed. If the report returns immediately, it would indicate problems with the Vendmax.dbo.RPT_Financials_By_PDF call.
    --
    myDBR Team

Reply

You must log in to post.