Invalid Object Name ##pivot - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Invalid Object Name ##pivot

Author

Topic

dsioson
Starting Member

6 Posts

Posted - 2006-06-29 : 04:16:36

Hi,

I hope someone can help me on my problem. I need to develop a crosstab query report in SRSS. Now, I got a code from this site and execute it in QUery Analyzer. Surprisingly, the crosstab stored proc works fine so I decided to use it in SRSS as my dataset. I created another stored procedure that will pass values to the crosstab procedure (which I just tried it using Query Analyzer) and use it as my actual Dataset in SRSS. When I loaded the dataset that executes the results, I got an error "Invalid object name ##pivot" which is clearly define on the crosstab stored proc. I wonder why it works in Query Anlyzer rather in the SRSS in VB.Net. The results produced in QAnalyzer is what I need and it blocked by the error. I cannot move on so please help me...

thanks

Denver

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-06-29 : 04:28:56

I think this thing is happening just b'coz dataset is disconnected resultset and when the connection to sql server breaks, all the temp tables created by that connection gets destroyed ( in your case ##pivot table). I'm not a .net programmer, but I think this may be the root cause of your problem. Try to use some other data access object which can maintain connection with the database.

Harsh Athalye
India.
"Nothing is Impossible"

dsioson
Starting Member

6 Posts

Posted - 2006-06-29 : 06:03:30

I'm still confused. Anyways, just to give you an idea here is the crosstab stored proc named "ZGC_PD_PTR_CROSSTAB_REPORT". And the stored proc that passed values is "ZGC_PD_PTR_REPORT". Here are their codes:

ZGC_PD_PTR_CROSSTAB_REPORT

CREATE PROCEDURE ZGC_PD_PTR_CROSSTAB_REPORT
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(20),
@table varchar(20)
AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')

SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)
SET ANSI_WARNINGS ON
GO

FOR ZGC_PD_PTR_REPORT

CREATE PROCEDURE ZGC_PD_PTR_REPORT AS

EXECUTE ZGC_PD_PTR_CROSSTAB_REPORT 'SELECT distinct
Customer_Group,Customer_Style, Prepack_Type, SUM(Quantity) as No_Of_PPK,Prepack_Size FROM Zprepack_type_ratio
GROUP BY Customer_Group,customer_style,
Prepack_Type, Prepack_Size',
'SUM(Quantity)',
'SIZE_CODE',
'ZPREPACK_TYPE_RATIO'
GO

I am using the second stored proc in the SQL Reporting services as my dataset. Hope this helps.

dsioson
Starting Member

6 Posts

Posted - 2006-07-14 : 04:13:09

Alright reporting services is limited with temporary tables so I resolve this issue using matrix control.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources