Long running procedure returns 0 results

(4 posts) (2 voices)

Tags:

No tags yet.

  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

    Posted 1 month ago #
  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

    Posted 1 month ago #
  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.

    Posted 1 month ago #
  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

    Posted 1 month ago #

Reply

You must log in to post.