Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Unanswered: SPs in Access 2000 (SQL) / Crosstab problem / returning dataset

    I've recently "upsized" from Access97 (Jet) to Access 2000 (SQL) client/server using MS SQL Server 2000. As a result, I'm new to the concept of Stored Procedures. I am trying to work out a general solution to the fact SQL doesn't allow an easy way to create dynamic crosstab queries (from within Access client/server).

    I've included the SP code I found (sp_crosstab) to create the crosstab solution. To execute the sp_crosstab, I use another SP (execute_crosstabs) which defines the input parameters.

    If I run the SPs in Query Analyzer, the results are returned as a dataset. However, if I run them in MS Access 2000, the following message is returned:

    "The stored procedure executed successfully but did not return records." Likewise, if I attach an Access form to the SP, it returns the same message.

    I've seen ADO code which could return the records (to Access), but I would prefer an alteration to the SP (sp_crosstab) which would return the records automatically.

    For example, if I run the SP below (sp_MyTables which executes sp_tables), a dataset is returned automatically instead of the message "The stored procedure executed successfully but did not return records." If I attach sp_MyTables to an Access form, the records are returned in the form as well.

    My question is this: How can I get sp_crosstab to act like sp_tables (executed by sp_MyTables) to return a dataset instead of the infernal message?

    I've looked all over the Internet and have not seen this issue addressed directly. Your help would be EXTREMELY appreciated (and will probably make Internet history)!

    (I've included the SP's below.)

    Michael Dallas

    /*********************** sp_CrossTab ******************/
    CREATE procedure sp_CrossTab
    @tablename varchar(255),
    @crosscolumn varchar(255),
    @crossrow varchar(255),
    @crossvalue varchar(255)

    As

    -- Work variables
    declare
    @ReturnSet varchar(255),
    @sql varchar(8000), -- Hold the dynamically created sql statement
    @colname varchar(255), -- The current column when building sql statement
    @i smallint, -- know when we reached the last column (@i = @cols)
    @cols smallint, -- Number of columns
    @longest_col smallint, -- the len() of the widest column
    @CrLf char(2)
    -- Constants
    declare
    @max_cols_in_table smallint,
    @max_col_name_len smallint,
    @max_statement_len smallint,
    -- @sql7 bit, -- 1 when version 7, 0 otherwise.
    @err_severity int

    set nocount on

    set @max_cols_in_table = 255
    set @max_statement_len = 8000
    set @max_col_name_len = 128
    set @err_severity = 11
    set @CrLf = char(13) + char(10)


    -- Check inputs
    if @tablename is null or @crosscolumn is null or @crossrow is null or @crossvalue is null begin
    raiserror ('Missing parameter(s)!',@err_severity,1)
    return @@rowcount
    end

    -- Check for existence of the table.
    if (not exists(select * from sysobjects where name like @tablename))begin
    raiserror ('Table/View for crosstab not found!',@err_severity,1)
    return 0
    end

    -- Don't check for columns because we may actually get an expression as the column name

    -- prepare for future feature of checking database version to validate
    -- inputs. Default to version 7
    --set @sql7 = 1
    --if (patindex('%SQL Server 7.%',@@version) = 0) begin
    -- set @sql7 = 0
    --end

    -- Extract all values from the rows of the attribute
    -- we want to use to create the cross column. This table
    -- will contain one row for each column in the crosstab.
    create table #crosscol (crosscolumn varchar(255))
    set @sql = ' insert #crosscol Select Distinct ' + @crosscolumn +
    ' From ' + @tablename --+
    --' Group By ' + @crosscolumn
    --print @sql
    exec (@sql)
    set @cols = @@rowcount

    if @cols > @max_cols_in_table begin
    raiserror ('Exceeded maximum number of columns in Cross-tab',@err_severity,1)
    return 0
    end
    else begin
    if @cols = 0 begin
    raiserror ('Could not find values to use for columns in Cross-tab',@err_severity,1)
    return 0
    end
    else begin
    -- Check if any of the data is too long to make it a name of a column
    select @longest_col = max(len(convert(varchar(129),crosscolumn)))
    from #crosscol

    if @longest_col > @max_col_name_len begin
    raiserror ('Value for column name exceeds legal length of column names',@err_severity,1)
    return 0
    end
    else begin

    -- All Validations OK, start building the dynamic sql statement

    set @sql = ''
    -- Use tmp table rows to create the sql statement for the crosstab.
    -- each row in the table will be a column in the cross-tab
    set @sql = 'select isnull(convert(varchar(255), ' + @crossrow + '),''Undefined'') As '
    + @crossrow + ', ' + @CrLf + space(4)

    --set @sql = 'select ' + @crossrow + ', ' + char(13)

    declare cross_sql cursor for
    select crosscolumn
    from #crosscol
    order by crosscolumn

    --print 'Sql cross statment: ' + @sql

    open cross_sql
    fetch next from cross_sql into @colname
    -- Use "@i" to check for the last column. We need to input commas
    -- between columns, but not after the last column
    set @i = 0
    while @@FETCH_STATUS = 0 begin
    set @i = @i + 1
    set @colname = isnull(@colname,'Undefined')
    set @crossvalue = isnull(@crossvalue, 0)

    Set @sql = @sql + '''' +
    convert(varchar(128), @colname) +
    ''' = sum(case convert(varchar(128), ' + @crosscolumn + ')'
    + char(13) + char(10) + space(8) +
    ' when ''' + @colname + ''' then ' + @crossvalue + ' else 0 end) '

    if @i < @cols
    set @sql = @sql + ', ' + @CrLf + space(4)
    else
    set @sql = @sql + @CrLf

    fetch next from cross_sql into @colname
    end

    close cross_sql
    deallocate cross_sql

    set @sql = @sql + ' from ' + @tablename + ' Group By ' + @crossrow

    if len(@sql) >= @max_statement_len begin
    raiserror ('Crosstab sql statement cannot exceed 7999 characters',@err_severity,1)
    return 0
    end

    exec (@sql)
    Select 'Sql' = @sql
    set nocount off
    RETURN 1

    end
    end
    end
    /***************** End sp_crosstab *****************/

    /***************** execute_crosstabs ***************/
    CREATE PROCEDURE execute_crosstabs

    AS

    exec sp_crosstab
    @tablename = 'report_sales_summary_quotedate_calc',
    @crosscolumn = 'Track',
    @crossrow = 'QuoteDate',
    @crossvalue = 'EstCom'

    RETURN
    /************** End execute_crosstabs ***************/


    /***************** sp_MyTables ********************/
    CREATE PROCEDURE [sp_MyTables]
    AS
    Exec sp_tables
    RETURN
    /***************** End sp_MyTables *****************/

  2. #2
    Join Date
    Dec 2003
    Posts
    17
    So basically the stored procedure sp_CrossTab should return the crosstab query for you. If that is the case then try the following. Note that SET NOCOUNT will hide all results from being returned by the stored procedure when it is run by anything other than Query Analyser.

    /*********************** sp_CrossTab ******************/
    CREATE procedure sp_CrossTab
    @tablename varchar(255),
    @crosscolumn varchar(255),
    @crossrow varchar(255),
    @crossvalue varchar(255)

    As

    -- Work variables
    declare
    @ReturnSet varchar(255),
    @sql varchar(8000), -- Hold the dynamically created sql statement
    @colname varchar(255), -- The current column when building sql statement
    @i smallint, -- know when we reached the last column (@i = @cols)
    @cols smallint, -- Number of columns
    @longest_col smallint, -- the len() of the widest column
    @CrLf char(2)
    -- Constants
    declare
    @max_cols_in_table smallint,
    @max_col_name_len smallint,
    @max_statement_len smallint,
    -- @sql7 bit, -- 1 when version 7, 0 otherwise.
    @err_severity int

    set nocount on

    set @max_cols_in_table = 255
    set @max_statement_len = 8000
    set @max_col_name_len = 128
    set @err_severity = 11
    set @CrLf = char(13) + char(10)


    -- Check inputs
    if @tablename is null or @crosscolumn is null or @crossrow is null or @crossvalue is null begin
    raiserror ('Missing parameter(s)!',@err_severity,1)
    return @@rowcount
    end

    -- Check for existence of the table.
    if (not exists(select * from sysobjects where name like @tablename))begin
    raiserror ('Table/View for crosstab not found!',@err_severity,1)
    return 0
    end

    -- Don't check for columns because we may actually get an expression as the column name

    -- prepare for future feature of checking database version to validate
    -- inputs. Default to version 7
    --set @sql7 = 1
    --if (patindex('%SQL Server 7.%',@@version) = 0) begin
    -- set @sql7 = 0
    --end

    -- Extract all values from the rows of the attribute
    -- we want to use to create the cross column. This table
    -- will contain one row for each column in the crosstab.
    create table #crosscol (crosscolumn varchar(255))
    set @sql = ' insert #crosscol Select Distinct ' + @crosscolumn +
    ' From ' + @tablename --+
    --' Group By ' + @crosscolumn
    --print @sql
    exec (@sql)
    set @cols = @@rowcount

    if @cols > @max_cols_in_table begin
    raiserror ('Exceeded maximum number of columns in Cross-tab',@err_severity,1)
    return 0
    end
    else begin
    if @cols = 0 begin
    raiserror ('Could not find values to use for columns in Cross-tab',@err_severity,1)
    return 0
    end
    else begin
    -- Check if any of the data is too long to make it a name of a column
    select @longest_col = max(len(convert(varchar(129),crosscolumn)))
    from #crosscol

    if @longest_col > @max_col_name_len begin
    raiserror ('Value for column name exceeds legal length of column names',@err_severity,1)
    return 0
    end
    else begin

    -- All Validations OK, start building the dynamic sql statement

    set @sql = ''
    -- Use tmp table rows to create the sql statement for the crosstab.
    -- each row in the table will be a column in the cross-tab
    set @sql = 'select isnull(convert(varchar(255), ' + @crossrow + '),''Undefined'') As '
    + @crossrow + ', ' + @CrLf + space(4)

    --set @sql = 'select ' + @crossrow + ', ' + char(13)

    declare cross_sql cursor for
    select crosscolumn
    from #crosscol
    order by crosscolumn

    --print 'Sql cross statment: ' + @sql

    open cross_sql
    fetch next from cross_sql into @colname
    -- Use "@i" to check for the last column. We need to input commas
    -- between columns, but not after the last column
    set @i = 0
    while @@FETCH_STATUS = 0 begin
    set @i = @i + 1
    set @colname = isnull(@colname,'Undefined')
    set @crossvalue = isnull(@crossvalue, 0)

    Set @sql = @sql + '''' +
    convert(varchar(128), @colname) +
    ''' = sum(case convert(varchar(128), ' + @crosscolumn + ')'
    + char(13) + char(10) + space(8) +
    ' when ''' + @colname + ''' then ' + @crossvalue + ' else 0 end) '

    if @i < @cols
    set @sql = @sql + ', ' + @CrLf + space(4)
    else
    set @sql = @sql + @CrLf

    fetch next from cross_sql into @colname
    end

    close cross_sql
    deallocate cross_sql

    set @sql = @sql + ' from ' + @tablename + ' Group By ' + @crossrow

    if len(@sql) >= @max_statement_len begin
    raiserror ('Crosstab sql statement cannot exceed 7999 characters',@err_severity,1)
    return 0
    end

    set nocount off
    exec (@sql)

    end
    end
    end
    /***************** End sp_crosstab *****************/

  3. #3
    Join Date
    Dec 2003
    Posts
    2

    The Answer(s)

    I found that the problem was not with the routine itself but with Access (SQL client).

    Two things:

    1) To run a Text Stored Procedure (SP) properly in Access, you need to close the SP (in Access) and then reopen. Going from SQL view to Datasheet view will not execute the SP correctly.

    2) Make sure your underlying "Views" don't have any null values in the fields used. The recordset I was using on the sp_crosstab was a View which had subviews (nested views). Even though the view I was using had no null values, the sub-views did. The clue was an error message in the MS SQL Query Analyzer - "Warning: Null value is eliminated by an aggregate or other SET operation."

    Even though Query Analyzer would return the records, this error stopped Access cold (and would not return any records).

    Thank you for your time and help.

    Michael Dallas

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •