April 25, 2012 at 10:37 am
hi
i am adding my dataset like this
EXEC [pPortfolioDashboard1]
---------- Required parameter------------
@SessionGuid = @SessionGuid,
@FromDate = @FromDate,
@ToDate = @ToDate,
@ClassificationID = @ClassificationID,
@Date = @Date,
@Portfolios = @Portfolios,
------------optional parameter-----------------------
gives me error like
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
An error occurred while getting new row from user defined Table Valued Function :
System.ApplicationException: @Portfolios or @PortfolioObjectID
any idea?
April 25, 2012 at 1:04 pm
yes
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 25, 2012 at 1:59 pm
I guess my previous answer was not enough?
Try looking at the error message.
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
@Portfolios or @PortfolioObjectID cannot both be null, one must be populated
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 25, 2012 at 2:05 pm
Also, take a close look at the line highlighted, looks like there is a hanging comma (,) at the end
EXEC [pPortfolioDashboard1]
---------- Required parameter------------
@SessionGuid = @SessionGuid,
@FromDate = @FromDate,
@ToDate = @ToDate,
@ClassificationID = @ClassificationID,
@Date = @Date,
@Portfolios = @Portfolios, <<<<----- Look here
------------optional parameter-----------------------
April 25, 2012 at 2:06 pm
Check the stored procedure or user-defined function the report is calling. There is probably a "raiserror" statement in it that tests those two parameters.
What you will need to do is provide a value for one or both. The exact resolution (what value to provide and to which) will depend on the business-logic of the proc/UDF, and isn't something anyone here will know. That will be a question for the person who wrote the proc. Probably an employee at your company.
The proc might have documentation in it that says what the parameters are for and what to do with them. But only if the person who wrote it put it there.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 25, 2012 at 2:13 pm
i dont know ,if anybody knows,but my all previous sp has same thing that i am doing.
and its working fine
April 25, 2012 at 2:17 pm
riya_dave (4/25/2012)
i dont know ,if anybody knows,but my all previous sp has same thing that i am doing.and its working fine
We are all aware. have you read what we just said and done some research on your side? Remember, we can't see anything you don't show us.
April 25, 2012 at 2:18 pm
Lynn Pettis (4/25/2012)
Also, take a close look at the line highlighted, looks like there is a hanging comma (,) at the endEXEC [pPortfolioDashboard1]
---------- Required parameter------------
@SessionGuid = @SessionGuid,
@FromDate = @FromDate,
@ToDate = @ToDate,
@ClassificationID = @ClassificationID,
@Date = @Date,
@Portfolios = @Portfolios, <<<<----- Look here
------------optional parameter-----------------------
That is because as is typical he only provided whatever portion of the issue he thinks is relevant. I am actually quite surprised that neither you or Gus pointed out how completely ridiculous the whole code set it. It doesn't do anything. It sets these variable to the same value they had before. 😛
--Edit then again I guess it does. Hard to tell with partial snippets. :blush:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 25, 2012 at 2:21 pm
Sean Lange (4/25/2012)
Lynn Pettis (4/25/2012)
Also, take a close look at the line highlighted, looks like there is a hanging comma (,) at the endEXEC [pPortfolioDashboard1]
---------- Required parameter------------
@SessionGuid = @SessionGuid,
@FromDate = @FromDate,
@ToDate = @ToDate,
@ClassificationID = @ClassificationID,
@Date = @Date,
@Portfolios = @Portfolios, <<<<----- Look here
------------optional parameter-----------------------
That is because as is typical he only provided whatever portion of the issue he thinks is relevant. I am actually quite surprised that neither you or Gus pointed out how completely ridiculous the whole code set it. It doesn't do anything. It sets these variable to the same value they had before. 😛
Actually, the parameters of the procedure could have the same name as the variables being used. The parameters for the proc are on the left side of the equals sign and the variables with values being passed in on the right side of the equals sign.
Of course, this is just a guess since we don't have the DDL for the procedure to actually look at and verify.
April 25, 2012 at 2:22 pm
Lynn Pettis (4/25/2012)
Sean Lange (4/25/2012)
Lynn Pettis (4/25/2012)
Also, take a close look at the line highlighted, looks like there is a hanging comma (,) at the endEXEC [pPortfolioDashboard1]
---------- Required parameter------------
@SessionGuid = @SessionGuid,
@FromDate = @FromDate,
@ToDate = @ToDate,
@ClassificationID = @ClassificationID,
@Date = @Date,
@Portfolios = @Portfolios, <<<<----- Look here
------------optional parameter-----------------------
That is because as is typical he only provided whatever portion of the issue he thinks is relevant. I am actually quite surprised that neither you or Gus pointed out how completely ridiculous the whole code set it. It doesn't do anything. It sets these variable to the same value they had before. 😛
Actually, the parameters of the procedure could have the same name as the variables being used. The parameters for the proc are on the left side of the equals sign and the variables with values being passed in on the right side of the equals sign.
Of course, this is just a guess since we don't have the DDL for the procedure to actually look at and verify.
hehe I was editing my post at the exact same time you posted.
I stand my original answer. "YES"
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 25, 2012 at 2:24 pm
Sean Lange (4/25/2012)
Lynn Pettis (4/25/2012)
Sean Lange (4/25/2012)
Lynn Pettis (4/25/2012)
Also, take a close look at the line highlighted, looks like there is a hanging comma (,) at the endEXEC [pPortfolioDashboard1]
---------- Required parameter------------
@SessionGuid = @SessionGuid,
@FromDate = @FromDate,
@ToDate = @ToDate,
@ClassificationID = @ClassificationID,
@Date = @Date,
@Portfolios = @Portfolios, <<<<----- Look here
------------optional parameter-----------------------
That is because as is typical he only provided whatever portion of the issue he thinks is relevant. I am actually quite surprised that neither you or Gus pointed out how completely ridiculous the whole code set it. It doesn't do anything. It sets these variable to the same value they had before. 😛
Actually, the parameters of the procedure could have the same name as the variables being used. The parameters for the proc are on the left side of the equals sign and the variables with values being passed in on the right side of the equals sign.
Of course, this is just a guess since we don't have the DDL for the procedure to actually look at and verify.
hehe I was editing my post at the exact same time you posted.
I stand my original answer. "YES"
I agree with your original answer.
April 25, 2012 at 6:09 pm
need a suggestion
i am putting my select statement in temp table.
if i remove this temp and only keep select ,i am not getting this error.
Is there any limitation of temp table?
April 25, 2012 at 6:20 pm
I know I am going to regreat this, but, care to show us? Can't tell what you are doing based solely on your post.
April 25, 2012 at 6:30 pm
create table ##temp1(shortname nvarchar(255),
portfoliobaseid int,
account nvarchar(255),
InceptionDate date,
--Reportheading1 nvarchar(255),
DTDTWR float,
MTDTWR float,
QTDTWR float,
YTDTWR float,
InceptionToDateTWR float,
Latest1YearTWR float,
CurrentMV float)
-------------------------------------------
temp table
-----------------
insert into ##temp1
select vpo.ShortName,vp.PortfolioBaseID, Account = vp.PortfolioBaseCode,ph.InceptionToDatePeriodFromDate,
--(select ReportHeading1 From dbo.vPortfolioBase
-- Where PortfolioBaseCode = Replace(@Portfolios,'@','')) as reportheading1,
DayToDateTWR = case when (ph.DayToDateTWR IS NULL) then
phd.IRR
else
ph.DayToDateTWR
end
,
ph.MonthToDateTWR ,
ph.QuarterToDateTWR,ph.YearToDateTWR,
ph.InceptionToDateTWR,
ph.Latest1YearTWR,
sum(distinct(fa.MarketValue)) as marketvalue
from
dbo.fPerformanceHistoryPeriod(@ReportData) ph
join dbo.vPortfolioBaseSettingEx portfolioBase on
portfolioBase.PortfolioBaseID = ph.PortfolioBaseID
join dbo.vPerformance VP on VP.PortfolioBaseID = portfolioBase.PortfolioBaseID
join dbo.vPortfolio vpo on vpo.PortfolioID = vp.PortfolioBaseID
join dbo.fAppraisal(@ReportData2) fa on fa.PortfolioBaseID = vpo.PortfolioID
join dbo.fPerformance(@ReportData4) phd on phd.PortfolioBaseID = vp.PortfolioBaseID
where
ph.IsIndex = 0
group by
vpo.ShortName, vp.PortfolioBaseCode,ph.DayToDateTWR,
ph.MonthToDateTWR,
ph.QuarterToDateTWR,ph.YearToDateTWR,
ph.InceptionToDateTWR,
ph.InceptionToDatePeriodFromDate,
--ReportHeading1,
ph.Latest1YearTWR,
phd.IRR,
vp.PortfolioBaseID
if i remove temp from my sp,i am not getting that error
plz help
April 25, 2012 at 6:39 pm
Getting what error? You showed me the code like I asked, but I know nothing about the temporary table you are attempting to insert data into. For all I know you have fewer defined columns in the table than you do in your select statement.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply