Report Builder 3.0 using Stored Procedure No field

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

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Report Builder 3.0 using Stored Procedure No field

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2011-06-08 : 22:54:50

Report Builder 3.0 using Stored Procedure No fields.

I run the wizard, connect to my datasource (and test connection) and then on the next page select a stored procedure from the list. The function parameters pane populates with the input paramters if they exist but nothing appears in the Selected fields pane. How do I select fields? If I select the "Edit as Text button" and run the stored proceudr eit returns the correct results. Selecting the "Edit as Text button" again returns to the Design a query page. If I select the store procedure again and enter a value for the input paramter and then select "Next" it indicates Nullable object must have a value? If I select a stored procedure that does not have any input parameters when I select "Next" it indicates "You must have at least one field for your dataset". Yes, but how do I select a field? Here is an example SP.

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[ActionType_RP] Script Date: 06/09/2011 12:24:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Steve Harlington
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[ActionType_RP]

AS

SET NOCOUNT ON
Declare @sql nvarchar(max)
Select @sql=''
Select @sql=@sql+ 'SELECT DISTINCT OperatorAction.ActionType
FROM '+name+'.dbo.OperatorAction
' from sys.databases where name='VC' or name like 'VCA%'

Create table #t([ActionType] nvarchar(50))
Insert into #t

Exec(@sql)

Select DISTINCT dbo.ActionTypeConv ([ActionType]) As ActionType
from #t

UNION ALL

Select '(Exclude Action Type)'

ORDER BY [ActionType]

Drop table #t

SET NOCOUNT OFF

RETURN

But if do this it works?

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[Test_RP] Script Date: 06/09/2011 12:31:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Steve Harlington
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================

CREATE PROCEDURE [dbo].[Test_RP]

AS

SET NOCOUNT ON

Select * from SiteDetails

SET NOCOUNT OFF

RETURN

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2011-06-23 : 02:29:03

Seems report builder cannot see data from a stored procedure if the select statement obtains the data from a temp table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources