Problem with Stored proc using in the Report

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

General SQL Server Forums

New to SQL Server Programming

Problem with Stored proc using in the Report

Author

Topic

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-10-22 : 11:04:14

In this Stored Proc, all I did was add the UC field, Unit Price. I have to modify a report. But when I use the new Stored Proc, it only displays 2 fields for use. This is quite confusing to me. I have no idea what the problem is. What is most confusing, is in the Query design area of the Report, I specify it as a Stored Proc, I hit the ! button, it gives a request for parameters and i enter them, and it displays data. Yet when I hit ok it gives this message: "Could not update a list of fields for the query. Verify that you can connect to the data source and that the query syntax is correct".

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

--[_USP_QuotesSummary_ByWeek_Internal] '01/04/2009', '01/08/2009', 'ALL', 'ALL' , 'ALL', '1'

alter Proc [dbo].[_USP_QuotesSummary_ByWeek_Internal]
@StartDate varchar(25), @EndDate varchar(25), @CustName varchar(250), @Department varchar(4000), @Category varchar(4000),
@SalesTypes Varchar(50)
as

DECLARE @SQL VARCHAR(8000)

if @CustName <> 'ALL'
begin
set @CustName = replace(@CustName,',', ''',''')
set @CustName = '''' + @CustName + ''''
end

if @Department <> 'ALL'
begin
set @Department = replace(@Department,',', ''',''')
set @Department = '''' + @Department + ''''
end

if @Category <> 'ALL'
begin
set @Category = replace(@Category,',', ''',''')
set @Category = '''' + @Category + ''''
end

set @SalesTypes = replace(@SalesTypes,',', ''',''')
set @SalesTypes = '''' + @SalesTypes + ''''

SET @SQL = 'SELECT SOP10200.XTNDPRCE AS Price, SOP10200.QUANTITY AS quantity,
DATEADD(WK, DATEDIFF(WK, 6, SOP10100.DOCDATE), 6) AS ID, sop10200.unitcost AS UC,
IV40600.UserCatLongDescr as CatD,

SOP10200.ITEMNMBR + CHAR(13) + IV00101.ITEMDESC + CHAR(13) + ''Par Level:'' AS ITEMDESC, IV00101.ITMGEDSC
FROM dbo.SOP10200 AS SOP10200 INNER JOIN
dbo.IV00101 AS IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
dbo.SOP10100 AS SOP10100 ON SOP10200.SOPTYPE = SOP10100.SOPTYPE AND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE INNER JOIN
dbo.RM00101 ON SOP10100.CUSTNMBR = dbo.RM00101.CUSTNMBR INNER JOIN
dbo.SOP10106 ON SOP10200.SOPTYPE = dbo.SOP10106.SOPTYPE AND SOP10200.SOPNUMBE = dbo.SOP10106.SOPNUMBE
inner join dbo.IV40600 on IV40600.USCATVAL = IV00101.ITMGEDSC
Where SOP10100.DOCDATE between ''' + @StartDate + ''' and ''' + @EndDate + ''''

IF @Category <> 'ALL'
BEGIN
SET @SQL = @SQL + ' AND IV40600.Usercatlongdescr in (' + @Category+ ')'
END

IF @Department <> 'ALL'
BEGIN
SET @SQL = @SQL + ' AND sop10106.USRDEF05 in (' + @Department + ')'
END

IF @CustName <> 'ALL'
BEGIN
SET @SQL = @SQL + ' AND dbo.rm00101.CUSTNAME in (' + @CustName + ')'
END

IF @SalesTypes <> ''
BEGIN
SET @SQL = @SQL + ' AND SOP10200.SOPTYPE in (' + @SalesTypes + ')'
END

SET @SQL = @SQL + ' order by IV00101.ITMGEDSC, IV00101.ITEMDESC, DATEADD(WK,DATEDIFF(WK,6,SOP10100.DOCDATE),6)'
--print @SQL
EXEC (@SQL)

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-10-22 : 11:13:09

Where is the report being run, if its in Reporting services have you refeshed

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-10-22 : 11:16:25

yes in the REporting services. I never get that far to refresh bec. it bombs on this syntax stuff. yet all i have done to a working Report with stored proc, is to add a field to the SQL and try to use this new field in the Report.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-22 : 13:03:53

what's the purpose of dynamic sql here? why cant you use conditional filtering instead?

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-10-22 : 13:20:04

V, this is used by an ASP.net page to display the report. I filter the user by their login and also the fact that they can see some departments and not others, etc.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-22 : 13:21:36

so? that doesnt answer my question. why do you think you cant use conditional filtering?

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-10-22 : 13:34:23

V, very simply, I am very newbie here. I do what works, if it works, I carry on. If you want to show me a better way, I am very greatful and happy to see this. I am only using SQL server now less than 4 months. I was stuck with IBM for many years. You know, green screens, RPG the whole ball of wax.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-22 : 13:41:06

[code]SELECT SOP10200.XTNDPRCE AS Price, SOP10200.QUANTITY AS quantity,
DATEADD(WK, DATEDIFF(WK, 6, SOP10100.DOCDATE), 6) AS ID, sop10200.unitcost AS UC,
IV40600.UserCatLongDescr as CatD,

SOP10200.ITEMNMBR + CHAR(13) + IV00101.ITEMDESC + CHAR(13) + ''Par Level:'' AS ITEMDESC, IV00101.ITMGEDSC
FROM dbo.SOP10200 AS SOP10200 INNER JOIN
dbo.IV00101 AS IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
dbo.SOP10100 AS SOP10100 ON SOP10200.SOPTYPE = SOP10100.SOPTYPE AND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE INNER JOIN
dbo.RM00101 ON SOP10100.CUSTNMBR = dbo.RM00101.CUSTNMBR INNER JOIN
dbo.SOP10106 ON SOP10200.SOPTYPE = dbo.SOP10106.SOPTYPE AND SOP10200.SOPNUMBE = dbo.SOP10106.SOPNUMBE
inner join dbo.IV40600 on IV40600.USCATVAL = IV00101.ITMGEDSC
Where SOP10100.DOCDATE between @StartDate and @EndDate
and ( ',' + @category + ',' LIKE '%,' + IV40600.Usercatlongdescr + ',%' OR @Category='ALL')
and (',' + @Department + ',' LIKE '%,' + sop10106.USRDEF05 + ',%' OR @Department='ALL')
......
[/code]

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-10-22 : 13:48:04

I am still not understanding - why, the stored proc works when I execute it against the database in Management Studio but it will not work in the rport manager.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-22 : 13:50:29

?? what does that mean?

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-10-22 : 13:56:06

quote:
Originally posted by visakh16

?? what does that mean?

If the Stored proc works in the Studio Management, why does it cause such problems when used in the Report Manager?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-22 : 13:59:28

whats the problem you're getting?

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-10-22 : 14:10:30

I have taken a working report with working Stored proc and made a copy of both. I take the new one, and I test it as is, using the old SP and it works, then I change the SP to the new one, that which has a new field, is all the changes, and it only then shows (in Query designer), only 1 field ( not the new one) and it says that message
" could not update a list of fields for the query"
"Procedure or function '_USP_QuotesSummary_ByWeek_Internal' expects parameter '@StartDate', which was not supplied.

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-10-23 : 09:39:11

The bottom line is that I am able to run the Query in the Query designer and hit "OK", but then I get this message:

"Procedure or function '_USP_QuotesSummary_ByWeek_Internal' expects parameter '@StartDate', which was not supplied."

On the Query designer, I just list the Query name, and then designate that its' a Stored proc.

Am I perhaps leaving something out there? I am able to get around this by constantly going back to the dataset properties of the Query and resetting this to "stored Proc" but why this happens is what I want to know.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 12:31:23

Did you try alternative i gave or dynamic query?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources