SPs In Access 2000 (SQL) / Crosstab Problem / Returning Dataset

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 *****************/