Dear All,
We're trying to resolve a rather odd performance issue on one of our
servers.
We have a COM+ component calling a stored sprocedure in a SQL Server
database. The COM+ component is on a different machine, so the call is
going across a network. The number of logical reads on the database
from this stored procedure is in the gazillions, but when we call the
stored procedure "manually", so to speak, through Query Analyser on our
desktops, the logical reads drop to almost nothing in comparison.
Does anyone have any idea why this would be?
AaronUpon further investigation, we've found that the stored procedure when
called by the COM+ component uses a different execution plan from that
when it's called manually. It uses a different index which uses a
bookmark lookup, with all the attending perfomance hits. We've now
changed the stored procedure to force it to use a specific index which
doesn't use a bookmark lookup and we're testing it to see if that
improves the performance. I'd still like to know why it does this.|||Hi Aaron,
Please post your stored procedure here and importantly how you are calling
it from the application and through query analyser.
It could be something to do with parameterisation / parameter sniffing.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1148031447.871209.218660@.38g2000cwa.googlegroups.com...
> Upon further investigation, we've found that the stored procedure when
> called by the COM+ component uses a different execution plan from that
> when it's called manually. It uses a different index which uses a
> bookmark lookup, with all the attending perfomance hits. We've now
> changed the stored procedure to force it to use a specific index which
> doesn't use a bookmark lookup and we're testing it to see if that
> improves the performance. I'd still like to know why it does this.
>|||In addition to Tony's thoughts:
IT could be because of different SET options, for instance isolation level.
COM+ defaults to
serializable, where others defaults to READ COMMITTED.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1148031447.871209.218660@.38g2000cwa.googlegroups.com...
> Upon further investigation, we've found that the stored procedure when
> called by the COM+ component uses a different execution plan from that
> when it's called manually. It uses a different index which uses a
> bookmark lookup, with all the attending perfomance hits. We've now
> changed the stored procedure to force it to use a specific index which
> doesn't use a bookmark lookup and we're testing it to see if that
> improves the performance. I'd still like to know why it does this.
>
Showing posts with label dear. Show all posts
Showing posts with label dear. Show all posts
Tuesday, March 27, 2012
Calling Stored Procedure from COM+
Dear All,
We're trying to resolve a rather odd performance issue on one of our
servers.
We have a COM+ component calling a stored sprocedure in a SQL Server
database. The COM+ component is on a different machine, so the call is
going across a network. The number of logical reads on the database
from this stored procedure is in the gazillions, but when we call the
stored procedure "manually", so to speak, through Query Analyser on our
desktops, the logical reads drop to almost nothing in comparison.
Does anyone have any idea why this would be?
AaronUpon further investigation, we've found that the stored procedure when
called by the COM+ component uses a different execution plan from that
when it's called manually. It uses a different index which uses a
bookmark lookup, with all the attending perfomance hits. We've now
changed the stored procedure to force it to use a specific index which
doesn't use a bookmark lookup and we're testing it to see if that
improves the performance. I'd still like to know why it does this.|||Hi Aaron,
Please post your stored procedure here and importantly how you are calling
it from the application and through query analyser.
It could be something to do with parameterisation / parameter sniffing.
Tony.
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1148031447.871209.218660@.38g2000cwa.googlegroups.com...
> Upon further investigation, we've found that the stored procedure when
> called by the COM+ component uses a different execution plan from that
> when it's called manually. It uses a different index which uses a
> bookmark lookup, with all the attending perfomance hits. We've now
> changed the stored procedure to force it to use a specific index which
> doesn't use a bookmark lookup and we're testing it to see if that
> improves the performance. I'd still like to know why it does this.
>|||In addition to Tony's thoughts:
IT could be because of different SET options, for instance isolation level. COM+ defaults to
serializable, where others defaults to READ COMMITTED.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1148031447.871209.218660@.38g2000cwa.googlegroups.com...
> Upon further investigation, we've found that the stored procedure when
> called by the COM+ component uses a different execution plan from that
> when it's called manually. It uses a different index which uses a
> bookmark lookup, with all the attending perfomance hits. We've now
> changed the stored procedure to force it to use a specific index which
> doesn't use a bookmark lookup and we're testing it to see if that
> improves the performance. I'd still like to know why it does this.
>
We're trying to resolve a rather odd performance issue on one of our
servers.
We have a COM+ component calling a stored sprocedure in a SQL Server
database. The COM+ component is on a different machine, so the call is
going across a network. The number of logical reads on the database
from this stored procedure is in the gazillions, but when we call the
stored procedure "manually", so to speak, through Query Analyser on our
desktops, the logical reads drop to almost nothing in comparison.
Does anyone have any idea why this would be?
AaronUpon further investigation, we've found that the stored procedure when
called by the COM+ component uses a different execution plan from that
when it's called manually. It uses a different index which uses a
bookmark lookup, with all the attending perfomance hits. We've now
changed the stored procedure to force it to use a specific index which
doesn't use a bookmark lookup and we're testing it to see if that
improves the performance. I'd still like to know why it does this.|||Hi Aaron,
Please post your stored procedure here and importantly how you are calling
it from the application and through query analyser.
It could be something to do with parameterisation / parameter sniffing.
Tony.
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1148031447.871209.218660@.38g2000cwa.googlegroups.com...
> Upon further investigation, we've found that the stored procedure when
> called by the COM+ component uses a different execution plan from that
> when it's called manually. It uses a different index which uses a
> bookmark lookup, with all the attending perfomance hits. We've now
> changed the stored procedure to force it to use a specific index which
> doesn't use a bookmark lookup and we're testing it to see if that
> improves the performance. I'd still like to know why it does this.
>|||In addition to Tony's thoughts:
IT could be because of different SET options, for instance isolation level. COM+ defaults to
serializable, where others defaults to READ COMMITTED.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1148031447.871209.218660@.38g2000cwa.googlegroups.com...
> Upon further investigation, we've found that the stored procedure when
> called by the COM+ component uses a different execution plan from that
> when it's called manually. It uses a different index which uses a
> bookmark lookup, with all the attending perfomance hits. We've now
> changed the stored procedure to force it to use a specific index which
> doesn't use a bookmark lookup and we're testing it to see if that
> improves the performance. I'd still like to know why it does this.
>
Calling Stored Procedure from COM+
Dear All,
We're trying to resolve a rather odd performance issue on one of our
servers.
We have a COM+ component calling a stored sprocedure in a SQL Server
database. The COM+ component is on a different machine, so the call is
going across a network. The number of logical reads on the database
from this stored procedure is in the gazillions, but when we call the
stored procedure "manually", so to speak, through Query Analyser on our
desktops, the logical reads drop to almost nothing in comparison.
Does anyone have any idea why this would be?
AaronUpon further investigation, we've found that the stored procedure when
called by the COM+ component uses a different execution plan from that
when it's called manually. It uses a different index which uses a
bookmark lookup, with all the attending perfomance hits. We've now
changed the stored procedure to force it to use a specific index which
doesn't use a bookmark lookup and we're testing it to see if that
improves the performance. I'd still like to know why it does this.|||Hi Aaron,
Please post your stored procedure here and importantly how you are calling
it from the application and through query analyser.
It could be something to do with parameterisation / parameter sniffing.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1148031447.871209.218660@.38g2000cwa.googlegroups.com...
> Upon further investigation, we've found that the stored procedure when
> called by the COM+ component uses a different execution plan from that
> when it's called manually. It uses a different index which uses a
> bookmark lookup, with all the attending perfomance hits. We've now
> changed the stored procedure to force it to use a specific index which
> doesn't use a bookmark lookup and we're testing it to see if that
> improves the performance. I'd still like to know why it does this.
>|||In addition to Tony's thoughts:
IT could be because of different SET options, for instance isolation level.
COM+ defaults to
serializable, where others defaults to READ COMMITTED.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1148031447.871209.218660@.38g2000cwa.googlegroups.com...
> Upon further investigation, we've found that the stored procedure when
> called by the COM+ component uses a different execution plan from that
> when it's called manually. It uses a different index which uses a
> bookmark lookup, with all the attending perfomance hits. We've now
> changed the stored procedure to force it to use a specific index which
> doesn't use a bookmark lookup and we're testing it to see if that
> improves the performance. I'd still like to know why it does this.
>sql
We're trying to resolve a rather odd performance issue on one of our
servers.
We have a COM+ component calling a stored sprocedure in a SQL Server
database. The COM+ component is on a different machine, so the call is
going across a network. The number of logical reads on the database
from this stored procedure is in the gazillions, but when we call the
stored procedure "manually", so to speak, through Query Analyser on our
desktops, the logical reads drop to almost nothing in comparison.
Does anyone have any idea why this would be?
AaronUpon further investigation, we've found that the stored procedure when
called by the COM+ component uses a different execution plan from that
when it's called manually. It uses a different index which uses a
bookmark lookup, with all the attending perfomance hits. We've now
changed the stored procedure to force it to use a specific index which
doesn't use a bookmark lookup and we're testing it to see if that
improves the performance. I'd still like to know why it does this.|||Hi Aaron,
Please post your stored procedure here and importantly how you are calling
it from the application and through query analyser.
It could be something to do with parameterisation / parameter sniffing.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1148031447.871209.218660@.38g2000cwa.googlegroups.com...
> Upon further investigation, we've found that the stored procedure when
> called by the COM+ component uses a different execution plan from that
> when it's called manually. It uses a different index which uses a
> bookmark lookup, with all the attending perfomance hits. We've now
> changed the stored procedure to force it to use a specific index which
> doesn't use a bookmark lookup and we're testing it to see if that
> improves the performance. I'd still like to know why it does this.
>|||In addition to Tony's thoughts:
IT could be because of different SET options, for instance isolation level.
COM+ defaults to
serializable, where others defaults to READ COMMITTED.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1148031447.871209.218660@.38g2000cwa.googlegroups.com...
> Upon further investigation, we've found that the stored procedure when
> called by the COM+ component uses a different execution plan from that
> when it's called manually. It uses a different index which uses a
> bookmark lookup, with all the attending perfomance hits. We've now
> changed the stored procedure to force it to use a specific index which
> doesn't use a bookmark lookup and we're testing it to see if that
> improves the performance. I'd still like to know why it does this.
>sql
Thursday, March 22, 2012
Calling Functions
Dear Everybody,
Hi again!
Supposing I have an SQL Server Function named Fxn1 which returns a VARCHAR(6) value. I want to use this returned value in another function named Fxn2 but Fxn2 accepts a parameter of type INT. Fxn2 itself returns an INT value. This has to be achieved in a single line.
The following line of code will not work in SQL Server. How should I achieve this?
declare @.Res INT
EXEC @.Res = Fxn2 (CONVERT( INT, Fxn1 ))
Thanx in advance!Just curioius, but have you tried talking to the TA or your instructor? Its really important that you talk to them when you are stumped on homework, they need the feedback as much as you need the help finding the solution.
-PatP|||No thx for the suggestion. :rolleyes:
I always thought that this forum had people who cud come up with suggestions which were technical in nature!|||It does, but we still won't do your homework!
-PatP|||First of all stop calling this homework. You cannot pass judgements like that. To me it was a genuine problem that's why I referred it to this forum. Might be not for u.
If u r not interested, gr8. But keep ur thoughts to urself.|||"This has to be achieved in a single line."?
Are you running out of lines on your computer?
You may just need to create a single function that combines the logic of the two you have.|||I don't have any problem giving legitimate help (read a few thousand of my posts if you'd like to confirm that), but based on the attitude that you've displayed and the grammer and spelling that you've used (or more accurately failed to use), you appear to me to be a rather rude kid that is sqawking because they got caught trying to get someone to do their homework. If I've misinterpreted something, I'm sorry, but that is how you appear to me at the moment.
Based on the way you presented the question in your original posting, it reads like homework. Are you saying that it isn't homework? If not, can you give us a little background in what you are trying to do, and more importantly why it needs to be accomplished in a single line?
-PatP|||Are you running out of lines on your computer?I just hate it when that happens, don't you?
-PatP|||What error are you getting when you execute the statement? Obviously
EXEC @.Res = Fxn2 (CONVERT( INT, Fxn1 ))
...will error out if Fxn1 returns a string that cannot be converted to an INT.
Also, post the actual statement you are sending.|||Dear blindman (although I hate to use that term to address u),
First of all thx for ur concern & help. Can I show the audacity to ask your real name?
Second of all, lets move to my actual problem..
I have several procedures and functions in Oracle which I have to convert to SQL Server syntax. The Fxn1 and Fxn2 names that I had used was just to present the problem to the forum in a simple manner.
In one of the functions named CREATE_TRIPS_SCHED, a SELECT statement has been included (the result of which has been used in another query) which is as follows :
SELECT 'P'||T.T_ID, T.S_TIME, T.END_TIME,
NVL(T.DESIRED_TIME, MIN_TO_AMPM( TO_MINUTES(TO_NUMBER(AMPM2FULLHRS(T.EST_TIME))))),
T.DISP, DECODE(T.WHEEL_CHAIR,'Y',5,2)
FROM DISPATCH_TRIPS T
WHERE T.R_NUM LIKE 'X%'
In the above statement, MIN_TO_AMPM, TO_MINUTES and AMPM2FULLHRS are all UDFs, out of which MIN_TO_AMPM and TO_MINUTES fxns return INTEGER values and AMPM2FULLHRS returns a VARCHAR value.
The conversion for SQL Server that I had done is as under :
SELECT 'P' + T.T_ID, T.S_TIME, T.END_TIME,
(CASE T.DESIRED_TIME WHEN ISNULL THEN MIN_TO_AMPM( TO_MINUTES(CONVERT (INT, AMPM2FULLHRS (T.EST_TIME)))) ELSE T.DESIRED_TIME END),
T.DISP, (CASE T.WHEEL_CHAIR WHEN 'Y' THEN 5 ELSE 2 END)
FROM DISPATCH_TRIPS T
WHERE T.R_NUM LIKE 'X%'
And the error msg that I am getting is this :
Server: Msg 195, Level 15, State 10, Procedure CREATE_TRIPS_SCHED, Line 25
'AMPM2FULLHRS' is not a recognized function name.
Server: Msg 156, Level 15, State 1, Procedure CREATE_TRIPS_SCHED, Line 56
Incorrect syntax near the keyword 'END'.
Server: Msg 156, Level 15, State 1, Procedure CREATE_TRIPS_SCHED, Line 68
Incorrect syntax near the keyword 'END'.
I have to do this conversion without introducing other fxns of mine. So, this is the actual problem. I hope I have been able to get across my problem!
Thx again! :)|||Pat,
At least you had the decency to be 'sorry' even if my turning 'rude' prompted you to do so.
Originally Posted by Pat Phelan
It does, but we still won't do your homework!
Having a few thousand posts does not give you the right to be judgemental. It also does not entitle you to speak on behalf of the whole forum.
P.S. Are u always like that?|||Perhaps adding the owner of the function when calling the function helps (db.owner.function)?|||It appears that my first impression may have been wrong. Based on your last post to blindman, you do seem to have a good grasp of SQL and a practical reason for making your original request.
I think you should have success if you use:SELECT @.Res = dbo.Fxn2 (CONVERT( INT, dbo.Fxn1() ))This ought to safely degenerate into:SELECT @.Res = dbo.Fxn2 (dbo.Fxn1() )In answer to your other question, yes, especially when this forum is barraged with kids looking for someone to do their homework for them, I tend to react with considerable skepticism to posts that are presented that way. My intent is to "red flag" those posts for other forum members, so that they will consider whether they want to spend their time working on a problem that is likely going to be turned in by a kid with no appreciation and no clue. The other forum members can use their own judgement, I'm just trying to make sure that they think before they "burn daylight" answering a post that I perceive as looking for an opportunity to plagerize.
I've repeatedly had my work turned in by students, whose teachers have recognized my style and contacted me. That probably makes me overly sensitive to the issue. I also think that there is a real ethical problem there too, and being in a position where ethical behavior is probably more important than technical expertise, that is very important to me.
Based on the begining of this thread, I had no reason to suspect that you were anything other than an apprentice juvenile delinquent, trolling to find someone that would do their assignment for them. Based on your post to Blindman, it appears that my first impression was wrong, so I responded differently in this post. As I've said before, I have no problem giving legitmate technical assistance, but I outright refuse to allow someone to take advantage of me (or anyone else without their approval).
-PatP|||Knighthassan,
Pat, myself, and others have given literally thousands of bits of advice and help to people who have posted on these boards, all of it free. If Pat made a mistake about your situation then so be it, but he had reasons to be suspicious. People asking for free advice should be less disrespectful, especially of people like Pat who is arguably the most knowledgable SQL Server expert on this forum. The fact that he took considerable time to review your complex code and propose a solution after last few posts you made shows how gracious he is. I hope his solution was helpful.|||blindman and Pat,
Thx to u both my problem is resolved. I also realize that my language has been a bit too harsh at times but something there got to me. Anyways, no hard feelings and thx again.|||blindman and Pat,
Thx to u both my problem is resolved. I also realize that my language has been a bit too harsh at times but something there got to me. Anyways, no hard feelings and thx again.No problem, and I'm glad that your problem has been fixed.
I rarely miss on issues like this, but of course when I do then it's a doozie. Out of several hundred "homework" calls that I've made, this is only the second one that I know where my initial call was wrong.
Right now I'm probably overly sensitive to it, since American schools are just coming back from the Thanksgiving holiday which meant that quite literally tens of thousands of assignments were due last week.
I noticed that you'd dived into some other message threads and contributed to them too. I hope that you decide to stick around and join in the fun! Lord knows that there is no shortage of questions, and it always helps to have a different perspective (although mine is usually quite "different" all by itself!).
-PatP|||I'm glad we (mosty Pat) were able to assist you, and yeah working with databases can be frustrating in itself sometimes, but we try to keep things light on this forum so no hard feelings and post again with both questions and advice.|||Pat, myself, and others have given literally thousands of bits of advice and help to people who have posted on these boards, all of it free.
Thank you
If Pat made a mistake about your situation then so be it, but he had reasons to be suspicious.
Umm, maybe not
People asking for free advice should be less disrespectful
Absolutely...why do people get so worked up? Who cares what soemone says? This always cracks me up.
especially of people like Pat who is arguably the most knowledgable SQL Server expert on this forum.
Well, could be, but I know Nigel posts here from time to time.
The fact that he took considerable time to review your complex code and propose a solution after last few posts you made shows how gracious he is. I hope his solution was helpful.
I think he'd still be having trouble. Post the actuall CREATE FUNCTION statements for both functions...personally they look more like a sproc to me...
And as for B. Lindman, you could probaly say "Yo, Blind dude!"
I always wondered where he got a braille keyboard..|||And as for B. Lindman, you could probaly say "Yo, Blind dude!"
I always wondered where he got a braille keyboard..Ah, Braille keyboards are no biggie to get. The difference is only in a few keys, but the tactile feedback for the indicators is the challenging part.
-PatP|||Wut's tuff iss finding a ceeing I dogg thata caan tipe woof a dam.
Bad dog! Bad!|||Originally Posted by Brett Kaiser
I always wondered where he got a braille keyboard..
A braille display and a braille mouse too! The world gets curiouser and unfathomable each passing day..
:D|||A braille display and a braille mouse too! The world gets curiouser and unfathomable each passing day..You say that as though it were a bad thing! ;)
As a side note, couldn't you get into some kind of trouble for feeling the bumps on a Braille mouse in the office ?
-PatP|||Depends on what you mean by mouse...
Hi again!
Supposing I have an SQL Server Function named Fxn1 which returns a VARCHAR(6) value. I want to use this returned value in another function named Fxn2 but Fxn2 accepts a parameter of type INT. Fxn2 itself returns an INT value. This has to be achieved in a single line.
The following line of code will not work in SQL Server. How should I achieve this?
declare @.Res INT
EXEC @.Res = Fxn2 (CONVERT( INT, Fxn1 ))
Thanx in advance!Just curioius, but have you tried talking to the TA or your instructor? Its really important that you talk to them when you are stumped on homework, they need the feedback as much as you need the help finding the solution.
-PatP|||No thx for the suggestion. :rolleyes:
I always thought that this forum had people who cud come up with suggestions which were technical in nature!|||It does, but we still won't do your homework!
-PatP|||First of all stop calling this homework. You cannot pass judgements like that. To me it was a genuine problem that's why I referred it to this forum. Might be not for u.
If u r not interested, gr8. But keep ur thoughts to urself.|||"This has to be achieved in a single line."?
Are you running out of lines on your computer?
You may just need to create a single function that combines the logic of the two you have.|||I don't have any problem giving legitimate help (read a few thousand of my posts if you'd like to confirm that), but based on the attitude that you've displayed and the grammer and spelling that you've used (or more accurately failed to use), you appear to me to be a rather rude kid that is sqawking because they got caught trying to get someone to do their homework. If I've misinterpreted something, I'm sorry, but that is how you appear to me at the moment.
Based on the way you presented the question in your original posting, it reads like homework. Are you saying that it isn't homework? If not, can you give us a little background in what you are trying to do, and more importantly why it needs to be accomplished in a single line?
-PatP|||Are you running out of lines on your computer?I just hate it when that happens, don't you?
-PatP|||What error are you getting when you execute the statement? Obviously
EXEC @.Res = Fxn2 (CONVERT( INT, Fxn1 ))
...will error out if Fxn1 returns a string that cannot be converted to an INT.
Also, post the actual statement you are sending.|||Dear blindman (although I hate to use that term to address u),
First of all thx for ur concern & help. Can I show the audacity to ask your real name?
Second of all, lets move to my actual problem..
I have several procedures and functions in Oracle which I have to convert to SQL Server syntax. The Fxn1 and Fxn2 names that I had used was just to present the problem to the forum in a simple manner.
In one of the functions named CREATE_TRIPS_SCHED, a SELECT statement has been included (the result of which has been used in another query) which is as follows :
SELECT 'P'||T.T_ID, T.S_TIME, T.END_TIME,
NVL(T.DESIRED_TIME, MIN_TO_AMPM( TO_MINUTES(TO_NUMBER(AMPM2FULLHRS(T.EST_TIME))))),
T.DISP, DECODE(T.WHEEL_CHAIR,'Y',5,2)
FROM DISPATCH_TRIPS T
WHERE T.R_NUM LIKE 'X%'
In the above statement, MIN_TO_AMPM, TO_MINUTES and AMPM2FULLHRS are all UDFs, out of which MIN_TO_AMPM and TO_MINUTES fxns return INTEGER values and AMPM2FULLHRS returns a VARCHAR value.
The conversion for SQL Server that I had done is as under :
SELECT 'P' + T.T_ID, T.S_TIME, T.END_TIME,
(CASE T.DESIRED_TIME WHEN ISNULL THEN MIN_TO_AMPM( TO_MINUTES(CONVERT (INT, AMPM2FULLHRS (T.EST_TIME)))) ELSE T.DESIRED_TIME END),
T.DISP, (CASE T.WHEEL_CHAIR WHEN 'Y' THEN 5 ELSE 2 END)
FROM DISPATCH_TRIPS T
WHERE T.R_NUM LIKE 'X%'
And the error msg that I am getting is this :
Server: Msg 195, Level 15, State 10, Procedure CREATE_TRIPS_SCHED, Line 25
'AMPM2FULLHRS' is not a recognized function name.
Server: Msg 156, Level 15, State 1, Procedure CREATE_TRIPS_SCHED, Line 56
Incorrect syntax near the keyword 'END'.
Server: Msg 156, Level 15, State 1, Procedure CREATE_TRIPS_SCHED, Line 68
Incorrect syntax near the keyword 'END'.
I have to do this conversion without introducing other fxns of mine. So, this is the actual problem. I hope I have been able to get across my problem!
Thx again! :)|||Pat,
At least you had the decency to be 'sorry' even if my turning 'rude' prompted you to do so.
Originally Posted by Pat Phelan
It does, but we still won't do your homework!
Having a few thousand posts does not give you the right to be judgemental. It also does not entitle you to speak on behalf of the whole forum.
P.S. Are u always like that?|||Perhaps adding the owner of the function when calling the function helps (db.owner.function)?|||It appears that my first impression may have been wrong. Based on your last post to blindman, you do seem to have a good grasp of SQL and a practical reason for making your original request.
I think you should have success if you use:SELECT @.Res = dbo.Fxn2 (CONVERT( INT, dbo.Fxn1() ))This ought to safely degenerate into:SELECT @.Res = dbo.Fxn2 (dbo.Fxn1() )In answer to your other question, yes, especially when this forum is barraged with kids looking for someone to do their homework for them, I tend to react with considerable skepticism to posts that are presented that way. My intent is to "red flag" those posts for other forum members, so that they will consider whether they want to spend their time working on a problem that is likely going to be turned in by a kid with no appreciation and no clue. The other forum members can use their own judgement, I'm just trying to make sure that they think before they "burn daylight" answering a post that I perceive as looking for an opportunity to plagerize.
I've repeatedly had my work turned in by students, whose teachers have recognized my style and contacted me. That probably makes me overly sensitive to the issue. I also think that there is a real ethical problem there too, and being in a position where ethical behavior is probably more important than technical expertise, that is very important to me.
Based on the begining of this thread, I had no reason to suspect that you were anything other than an apprentice juvenile delinquent, trolling to find someone that would do their assignment for them. Based on your post to Blindman, it appears that my first impression was wrong, so I responded differently in this post. As I've said before, I have no problem giving legitmate technical assistance, but I outright refuse to allow someone to take advantage of me (or anyone else without their approval).
-PatP|||Knighthassan,
Pat, myself, and others have given literally thousands of bits of advice and help to people who have posted on these boards, all of it free. If Pat made a mistake about your situation then so be it, but he had reasons to be suspicious. People asking for free advice should be less disrespectful, especially of people like Pat who is arguably the most knowledgable SQL Server expert on this forum. The fact that he took considerable time to review your complex code and propose a solution after last few posts you made shows how gracious he is. I hope his solution was helpful.|||blindman and Pat,
Thx to u both my problem is resolved. I also realize that my language has been a bit too harsh at times but something there got to me. Anyways, no hard feelings and thx again.|||blindman and Pat,
Thx to u both my problem is resolved. I also realize that my language has been a bit too harsh at times but something there got to me. Anyways, no hard feelings and thx again.No problem, and I'm glad that your problem has been fixed.
I rarely miss on issues like this, but of course when I do then it's a doozie. Out of several hundred "homework" calls that I've made, this is only the second one that I know where my initial call was wrong.
Right now I'm probably overly sensitive to it, since American schools are just coming back from the Thanksgiving holiday which meant that quite literally tens of thousands of assignments were due last week.
I noticed that you'd dived into some other message threads and contributed to them too. I hope that you decide to stick around and join in the fun! Lord knows that there is no shortage of questions, and it always helps to have a different perspective (although mine is usually quite "different" all by itself!).
-PatP|||I'm glad we (mosty Pat) were able to assist you, and yeah working with databases can be frustrating in itself sometimes, but we try to keep things light on this forum so no hard feelings and post again with both questions and advice.|||Pat, myself, and others have given literally thousands of bits of advice and help to people who have posted on these boards, all of it free.
Thank you
If Pat made a mistake about your situation then so be it, but he had reasons to be suspicious.
Umm, maybe not
People asking for free advice should be less disrespectful
Absolutely...why do people get so worked up? Who cares what soemone says? This always cracks me up.
especially of people like Pat who is arguably the most knowledgable SQL Server expert on this forum.
Well, could be, but I know Nigel posts here from time to time.
The fact that he took considerable time to review your complex code and propose a solution after last few posts you made shows how gracious he is. I hope his solution was helpful.
I think he'd still be having trouble. Post the actuall CREATE FUNCTION statements for both functions...personally they look more like a sproc to me...
And as for B. Lindman, you could probaly say "Yo, Blind dude!"
I always wondered where he got a braille keyboard..|||And as for B. Lindman, you could probaly say "Yo, Blind dude!"
I always wondered where he got a braille keyboard..Ah, Braille keyboards are no biggie to get. The difference is only in a few keys, but the tactile feedback for the indicators is the challenging part.
-PatP|||Wut's tuff iss finding a ceeing I dogg thata caan tipe woof a dam.
Bad dog! Bad!|||Originally Posted by Brett Kaiser
I always wondered where he got a braille keyboard..
A braille display and a braille mouse too! The world gets curiouser and unfathomable each passing day..
:D|||A braille display and a braille mouse too! The world gets curiouser and unfathomable each passing day..You say that as though it were a bad thing! ;)
As a side note, couldn't you get into some kind of trouble for feeling the bumps on a Braille mouse in the office ?
-PatP|||Depends on what you mean by mouse...
Tuesday, March 20, 2012
Calling applications in unix platform
Dear all,
How do i call an application, which is reside in another unix platform in my flow of execution.
I'll need to call this application after my data extraction is successfully run.
Thanks for the helps in advance.
Thanks & Best Regards,
PassionOrienter
Hi PassionOrienter,
The answer sort of depends on how you'd normally invoke this program on your UNIX machine. One idea: assuming you can invoke it from ssh, obtain a command-line ssh program for your Windows machine, make sure you can use it from a Windows command prompt to execute your UNIX process, and then integrate it into your SSIS package with the Execute Process Task in the Control flow.
Hope this helps!
-David
|||Hi David,
Thanks for the solution provided.
I just wonder whether SSIS can call the application in Unix directly without having to write extra application in doing so.
Anyway, thanks for the help.
Thanks & Best Regards,
PassionOrienter
Calling applications in unix platform
Dear all,
How do i call an application, which is reside in another unix platform in my flow of execution.
I'll need to call this application after my data extraction is successfully run.
Thanks for the helps in advance.
Thanks & Best Regards,
PassionOrienter
Hi PassionOrienter,
The answer sort of depends on how you'd normally invoke this program on your UNIX machine. One idea: assuming you can invoke it from ssh, obtain a command-line ssh program for your Windows machine, make sure you can use it from a Windows command prompt to execute your UNIX process, and then integrate it into your SSIS package with the Execute Process Task in the Control flow.
Hope this helps!
-David
|||Hi David,
Thanks for the solution provided.
I just wonder whether SSIS can call the application in Unix directly without having to write extra application in doing so.
Anyway, thanks for the help.
Thanks & Best Regards,
PassionOrienter
Calling applications in unix platform
Dear all,
How do i call an application, which is reside in another unix platform in my flow of execution.
I'll need to call this application after my data extraction is successfully run.
Thanks for the helps in advance.
Thanks & Best Regards,
PassionOrienter
Hi PassionOrienter,
The answer sort of depends on how you'd normally invoke this program on your UNIX machine. One idea: assuming you can invoke it from ssh, obtain a command-line ssh program for your Windows machine, make sure you can use it from a Windows command prompt to execute your UNIX process, and then integrate it into your SSIS package with the Execute Process Task in the Control flow.
Hope this helps!
-David
|||Hi David,
Thanks for the solution provided.
I just wonder whether SSIS can call the application in Unix directly without having to write extra application in doing so.
Anyway, thanks for the help.
Thanks & Best Regards,
PassionOrienter
Monday, March 19, 2012
Calling all MSSQL 2005 Experts
Dear Group.
It's very unclear what you have done, after messing with the registry your
machine may be beyond repair. Or may at least require a complete uninstall
of VS and SQL Server and back again.
Wondered if any of you has any suggestion for the following?
Trying to install SQL Server 2005 Eval on a 'clean' machine. Well, my
mistake was probably that I had installed Visual Studio 2005
Professional Eval before which installed an MSSQL Express instance.
Since then I didn't get 'Enterprise Manager' (excuse my ignorance, I
know it's called differently in2005 but it's late and I'm tired) for
MSSQL 2005 installed.Don't remember the error exactly but after some
trying I received some error during the MSSQL Management Studio (is
that right?) installation that it needs to be upgraded in some ini file
which I looked for to no avail. ANYWAY, after some desperate registry
deletes and following MS Kbase articles I arrived at: 'The installer
has encountered an unexpected error installing this package. This may
indicate a problem with the package. The error code is 2718.' during
the installation of SQL Server Backward-Compatibility files.
Which is of course complete rubbish. We all now that Microsoft has done
an awful job in getting SQL Express and SQL Server installed
side-by-side but it never has been as bad as this on any machine
before. Needless to say the package is fine as I've used it many times
before. Any suggestions?
Gratefully yours,
Martin
'Just wanna get on with work. Tired of starring at error messages.'(theintrepidfox@.hotmail.com) writes:
Quote:
Originally Posted by
Wondered if any of you has any suggestion for the following?
Trying to install SQL Server 2005 Eval on a 'clean' machine. Well, my
mistake was probably that I had installed Visual Studio 2005
Professional Eval before which installed an MSSQL Express instance.
Since then I didn't get 'Enterprise Manager' (excuse my ignorance, I
know it's called differently in2005 but it's late and I'm tired) for
MSSQL 2005 installed.Don't remember the error exactly but after some
trying I received some error during the MSSQL Management Studio (is
that right?) installation that it needs to be upgraded in some ini file
which I looked for to no avail. ANYWAY, after some desperate registry
deletes and following MS Kbase articles I arrived at: 'The installer
has encountered an unexpected error installing this package. This may
indicate a problem with the package. The error code is 2718.' during
the installation of SQL Server Backward-Compatibility files.
Which is of course complete rubbish. We all now that Microsoft has done
an awful job in getting SQL Express and SQL Server installed
side-by-side but it never has been as bad as this on any machine
before. Needless to say the package is fine as I've used it many times
before. Any suggestions?
It's very unclear what you have done, after messing with the registry your
machine may be beyond repair. Or may at least require a complete uninstall
of VS and SQL Server and back again.
But if you already had SQL Server Management Studio Express installed,
that may be the root of your problem, as you cannot have both SSMS Express
and the big SSMS installed side by side.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Sunday, March 11, 2012
Calling a sp from a UDF
Dear folks,
Is it possible? We are getting a error message from QA.
Let me know any thought/idea/link about this.
Regards,
Enric"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:372F7226-3FF0-42D2-A446-9B87691685BD@.microsoft.com...
> Dear folks,
> Is it possible? We are getting a error message from QA.
> Let me know any thought/idea/link about this.
> Regards,
> Enric|||"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:372F7226-3FF0-42D2-A446-9B87691685BD@.microsoft.com...
> Dear folks,
> Is it possible? We are getting a error message from QA.
> Let me know any thought/idea/link about this.
> Regards,
> Enric
From the BOL
The types of statements that are valid in a function include:
a.. DECLARE statements can be used to define data variables and cursors
that are local to the function.
b.. Assignments of values to objects local to the function, such as using
SET to assign values to scalar and table local variables.
c.. Cursor operations that reference local cursors that are declared,
opened, closed, and deallocated in the function. FETCH statements that
return data to the client are not allowed. Only FETCH statements that assign
values to local variables using the INTO clause are allowed.
d.. Control-of-flow statements.
e.. SELECT statements containing select lists with expressions that assign
values to variables that are local to the function.
f.. UPDATE, INSERT, and DELETE statements modifying table variables that
are local to the function.
g.. EXECUTE statements calling an extended stored procedure.
So it looks like you can call extended sprocs, but not local sprocs.
Rick Sawtell
MCT, MCSD, MCDBA|||Only extended procedures can be executed from user-defined functions. Can't
you include the logic from your procedure in the UDF?
ML
http://milambda.blogspot.com/|||"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:372F7226-3FF0-42D2-A446-9B87691685BD@.microsoft.com...
> Dear folks,
> Is it possible? We are getting a error message from QA.
> Let me know any thought/idea/link about this.
> Regards,
> Enric
From BOL:
The following statements are allowed in the body of a multi-statement
function. Statements not in this list are not allowed in the body of a
function: ...
Assignment statements.
Control-of-Flow statements.
DECLARE statements defining data variables and cursors that are local to the
function.
SELECT statements containing select lists with expressions that assign
values to variables that are local to the function.
Cursor operations referencing local cursors that are declared, opened,
closed, and deallocated in the function. Only FETCH statements that assign
values to local variables using the INTO clause are allowed; FETCH
statements that return data to the client are not allowed.
UPDATE, INSERT, and DELETE statements modifying table variables that are
local to the function.
EXECUTE statements calling an extended stored procedure.
Also from BOL:
User-defined functions cannot be used to perform a set of actions that
modify the global database state.|||No this is not possible.
HTH, jens Suessmeyer.|||Have not tried it, but couldn't you call a clr sproc from a clr UDF?
William Stacey [MVP]
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:372F7226-3FF0-42D2-A446-9B87691685BD@.microsoft.com...
| Dear folks,
|
| Is it possible? We are getting a error message from QA.
| Let me know any thought/idea/link about this.
|
| Regards,
|
| Enric
Is it possible? We are getting a error message from QA.
Let me know any thought/idea/link about this.
Regards,
Enric"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:372F7226-3FF0-42D2-A446-9B87691685BD@.microsoft.com...
> Dear folks,
> Is it possible? We are getting a error message from QA.
> Let me know any thought/idea/link about this.
> Regards,
> Enric|||"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:372F7226-3FF0-42D2-A446-9B87691685BD@.microsoft.com...
> Dear folks,
> Is it possible? We are getting a error message from QA.
> Let me know any thought/idea/link about this.
> Regards,
> Enric
From the BOL
The types of statements that are valid in a function include:
a.. DECLARE statements can be used to define data variables and cursors
that are local to the function.
b.. Assignments of values to objects local to the function, such as using
SET to assign values to scalar and table local variables.
c.. Cursor operations that reference local cursors that are declared,
opened, closed, and deallocated in the function. FETCH statements that
return data to the client are not allowed. Only FETCH statements that assign
values to local variables using the INTO clause are allowed.
d.. Control-of-flow statements.
e.. SELECT statements containing select lists with expressions that assign
values to variables that are local to the function.
f.. UPDATE, INSERT, and DELETE statements modifying table variables that
are local to the function.
g.. EXECUTE statements calling an extended stored procedure.
So it looks like you can call extended sprocs, but not local sprocs.
Rick Sawtell
MCT, MCSD, MCDBA|||Only extended procedures can be executed from user-defined functions. Can't
you include the logic from your procedure in the UDF?
ML
http://milambda.blogspot.com/|||"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:372F7226-3FF0-42D2-A446-9B87691685BD@.microsoft.com...
> Dear folks,
> Is it possible? We are getting a error message from QA.
> Let me know any thought/idea/link about this.
> Regards,
> Enric
From BOL:
The following statements are allowed in the body of a multi-statement
function. Statements not in this list are not allowed in the body of a
function: ...
Assignment statements.
Control-of-Flow statements.
DECLARE statements defining data variables and cursors that are local to the
function.
SELECT statements containing select lists with expressions that assign
values to variables that are local to the function.
Cursor operations referencing local cursors that are declared, opened,
closed, and deallocated in the function. Only FETCH statements that assign
values to local variables using the INTO clause are allowed; FETCH
statements that return data to the client are not allowed.
UPDATE, INSERT, and DELETE statements modifying table variables that are
local to the function.
EXECUTE statements calling an extended stored procedure.
Also from BOL:
User-defined functions cannot be used to perform a set of actions that
modify the global database state.|||No this is not possible.
HTH, jens Suessmeyer.|||Have not tried it, but couldn't you call a clr sproc from a clr UDF?
William Stacey [MVP]
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:372F7226-3FF0-42D2-A446-9B87691685BD@.microsoft.com...
| Dear folks,
|
| Is it possible? We are getting a error message from QA.
| Let me know any thought/idea/link about this.
|
| Regards,
|
| Enric
Thursday, February 16, 2012
calculating with variables statistical values. How?
Dear all,
I am trying to solve this but I can't work out.
Set @.DESV_INSERTED = (SELECT stdev(desv) FROM #DESVIATIONTYPICAL WHERE
desviation_id = @.desviation_id AND desv > 0)
SET @.AVERAGE = (SELECT AVG(DESV) FROM #DESVIATIONTYPICAL WHERE
desviation_id = @.desviation_id AND desv > 0)
SET @.AUXMIN = SELECT (1.96 * @.DESV_INSERTED)
SET @.MINIMUM = SELECT (@.AVERAGE - @.AUXMIN)
SET @.MAXIMUM = SELECT (@.AVERAGE + @.AUXMIN)
And I obtain the following errors:
Server: Msg 156, Level 15, State 1, Procedure
up_WHS_PeriodWarningQControl_PROC, Line 850
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 156, Level 15, State 1, Procedure
up_WHS_PeriodWarningQControl_PROC, Line 852
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 156, Level 15, State 1, Procedure
up_WHS_PeriodWarningQControl_PROC, Line 853
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 156, Level 15, State 1, Procedure
up_WHS_PeriodWarningQControl_PROC, Line 855
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 170, Level 15, State 1, Procedure
up_WHS_PeriodWarningQControl_PROC, Line 856
Line 856: Incorrect syntax near '='.
Thanks in advance and best regards,Change:
SET @.AUXMIN = SELECT (1.96 * @.DESV_INSERTED)
SET @.MINIMUM = SELECT (@.AVERAGE - @.AUXMIN)
SET @.MAXIMUM = SELECT (@.AVERAGE + @.AUXMIN)
To:
SET @.AUXMIN = 1.96 * @.DESV_INSERTED
SET @.MINIMUM = AVERAGE - @.AUXMIN
SET @.MAXIMUM = AVERAGE + @.AUXMIN
and you probably have a few more similar lines in that procedure _after_ the
code you have shown. The line numbers shown in the errors are the lines in
the body of the stored procedure, after the keyword 'AS', so they have an
offset comapred to the line number in the stored procedure declaration
(which includes CREATE PROC and the parameter declarations).
Jacco Schalkwijk
SQL Server MVP
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:30173799-B153-49B2-9223-4C4B282A2B27@.microsoft.com...
> Dear all,
> I am trying to solve this but I can't work out.
>
> Set @.DESV_INSERTED = (SELECT stdev(desv) FROM #DESVIATIONTYPICAL WHERE
> desviation_id = @.desviation_id AND desv > 0)
> SET @.AVERAGE = (SELECT AVG(DESV) FROM #DESVIATIONTYPICAL WHERE
> desviation_id = @.desviation_id AND desv > 0)
>
> SET @.AUXMIN = SELECT (1.96 * @.DESV_INSERTED)
> SET @.MINIMUM = SELECT (@.AVERAGE - @.AUXMIN)
> SET @.MAXIMUM = SELECT (@.AVERAGE + @.AUXMIN)
>
> And I obtain the following errors:
>
> Server: Msg 156, Level 15, State 1, Procedure
> up_WHS_PeriodWarningQControl_PROC, Line 850
> Incorrect syntax near the keyword 'SELECT'.
> Server: Msg 156, Level 15, State 1, Procedure
> up_WHS_PeriodWarningQControl_PROC, Line 852
> Incorrect syntax near the keyword 'SELECT'.
> Server: Msg 156, Level 15, State 1, Procedure
> up_WHS_PeriodWarningQControl_PROC, Line 853
> Incorrect syntax near the keyword 'SELECT'.
> Server: Msg 156, Level 15, State 1, Procedure
> up_WHS_PeriodWarningQControl_PROC, Line 855
> Incorrect syntax near the keyword 'SELECT'.
> Server: Msg 170, Level 15, State 1, Procedure
> up_WHS_PeriodWarningQControl_PROC, Line 856
> Line 856: Incorrect syntax near '='.
>
> Thanks in advance and best regards,|||Enric,
SET @.AUXMIN = SELECT (1.96 * @.DESV_INSERTED)
is invalid syntax. To use a SELECT expression as
a value, you need to surround the entire SELECT
expression in parenthesis:
SET @.AUXMIN = (SELECT (1.96 * @.DESV_INSERTED))
You did this correctly in the first to SET statement,
but not in the final three.
Steve Kass
Drew University
Enric wrote:
>Dear all,
>I am trying to solve this but I can't work out.
>
>Set @.DESV_INSERTED = (SELECT stdev(desv) FROM #DESVIATIONTYPICAL WHERE
> desviation_id = @.desviation_id AND desv > 0)
>SET @.AVERAGE = (SELECT AVG(DESV) FROM #DESVIATIONTYPICAL WHERE
> desviation_id = @.desviation_id AND desv > 0)
>
>SET @.AUXMIN = SELECT (1.96 * @.DESV_INSERTED)
>SET @.MINIMUM = SELECT (@.AVERAGE - @.AUXMIN)
>SET @.MAXIMUM = SELECT (@.AVERAGE + @.AUXMIN)
>
>And I obtain the following errors:
>
>Server: Msg 156, Level 15, State 1, Procedure
>up_WHS_PeriodWarningQControl_PROC, Line 850
>Incorrect syntax near the keyword 'SELECT'.
>Server: Msg 156, Level 15, State 1, Procedure
>up_WHS_PeriodWarningQControl_PROC, Line 852
>Incorrect syntax near the keyword 'SELECT'.
>Server: Msg 156, Level 15, State 1, Procedure
>up_WHS_PeriodWarningQControl_PROC, Line 853
>Incorrect syntax near the keyword 'SELECT'.
>Server: Msg 156, Level 15, State 1, Procedure
>up_WHS_PeriodWarningQControl_PROC, Line 855
>Incorrect syntax near the keyword 'SELECT'.
>Server: Msg 170, Level 15, State 1, Procedure
>up_WHS_PeriodWarningQControl_PROC, Line 856
>Line 856: Incorrect syntax near '='.
>
>Thanks in advance and best regards,
>|||yeah, works.
I am feel very proud of your performance.
Enric
"Jacco Schalkwijk" wrote:
> Change:
> SET @.AUXMIN = SELECT (1.96 * @.DESV_INSERTED)
> SET @.MINIMUM = SELECT (@.AVERAGE - @.AUXMIN)
> SET @.MAXIMUM = SELECT (@.AVERAGE + @.AUXMIN)
> To:
> SET @.AUXMIN = 1.96 * @.DESV_INSERTED
> SET @.MINIMUM = AVERAGE - @.AUXMIN
> SET @.MAXIMUM = AVERAGE + @.AUXMIN
> and you probably have a few more similar lines in that procedure _after_ t
he
> code you have shown. The line numbers shown in the errors are the lines in
> the body of the stored procedure, after the keyword 'AS', so they have an
> offset comapred to the line number in the stored procedure declaration
> (which includes CREATE PROC and the parameter declarations).
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:30173799-B153-49B2-9223-4C4B282A2B27@.microsoft.com...
>
>|||Thanks Steve,
Regards to all
"Steve Kass" wrote:
> Enric,
> SET @.AUXMIN = SELECT (1.96 * @.DESV_INSERTED)
> is invalid syntax. To use a SELECT expression as
> a value, you need to surround the entire SELECT
> expression in parenthesis:
> SET @.AUXMIN = (SELECT (1.96 * @.DESV_INSERTED))
> You did this correctly in the first to SET statement,
> but not in the final three.
> Steve Kass
> Drew University
>
>
> Enric wrote:
>
>
I am trying to solve this but I can't work out.
Set @.DESV_INSERTED = (SELECT stdev(desv) FROM #DESVIATIONTYPICAL WHERE
desviation_id = @.desviation_id AND desv > 0)
SET @.AVERAGE = (SELECT AVG(DESV) FROM #DESVIATIONTYPICAL WHERE
desviation_id = @.desviation_id AND desv > 0)
SET @.AUXMIN = SELECT (1.96 * @.DESV_INSERTED)
SET @.MINIMUM = SELECT (@.AVERAGE - @.AUXMIN)
SET @.MAXIMUM = SELECT (@.AVERAGE + @.AUXMIN)
And I obtain the following errors:
Server: Msg 156, Level 15, State 1, Procedure
up_WHS_PeriodWarningQControl_PROC, Line 850
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 156, Level 15, State 1, Procedure
up_WHS_PeriodWarningQControl_PROC, Line 852
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 156, Level 15, State 1, Procedure
up_WHS_PeriodWarningQControl_PROC, Line 853
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 156, Level 15, State 1, Procedure
up_WHS_PeriodWarningQControl_PROC, Line 855
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 170, Level 15, State 1, Procedure
up_WHS_PeriodWarningQControl_PROC, Line 856
Line 856: Incorrect syntax near '='.
Thanks in advance and best regards,Change:
SET @.AUXMIN = SELECT (1.96 * @.DESV_INSERTED)
SET @.MINIMUM = SELECT (@.AVERAGE - @.AUXMIN)
SET @.MAXIMUM = SELECT (@.AVERAGE + @.AUXMIN)
To:
SET @.AUXMIN = 1.96 * @.DESV_INSERTED
SET @.MINIMUM = AVERAGE - @.AUXMIN
SET @.MAXIMUM = AVERAGE + @.AUXMIN
and you probably have a few more similar lines in that procedure _after_ the
code you have shown. The line numbers shown in the errors are the lines in
the body of the stored procedure, after the keyword 'AS', so they have an
offset comapred to the line number in the stored procedure declaration
(which includes CREATE PROC and the parameter declarations).
Jacco Schalkwijk
SQL Server MVP
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:30173799-B153-49B2-9223-4C4B282A2B27@.microsoft.com...
> Dear all,
> I am trying to solve this but I can't work out.
>
> Set @.DESV_INSERTED = (SELECT stdev(desv) FROM #DESVIATIONTYPICAL WHERE
> desviation_id = @.desviation_id AND desv > 0)
> SET @.AVERAGE = (SELECT AVG(DESV) FROM #DESVIATIONTYPICAL WHERE
> desviation_id = @.desviation_id AND desv > 0)
>
> SET @.AUXMIN = SELECT (1.96 * @.DESV_INSERTED)
> SET @.MINIMUM = SELECT (@.AVERAGE - @.AUXMIN)
> SET @.MAXIMUM = SELECT (@.AVERAGE + @.AUXMIN)
>
> And I obtain the following errors:
>
> Server: Msg 156, Level 15, State 1, Procedure
> up_WHS_PeriodWarningQControl_PROC, Line 850
> Incorrect syntax near the keyword 'SELECT'.
> Server: Msg 156, Level 15, State 1, Procedure
> up_WHS_PeriodWarningQControl_PROC, Line 852
> Incorrect syntax near the keyword 'SELECT'.
> Server: Msg 156, Level 15, State 1, Procedure
> up_WHS_PeriodWarningQControl_PROC, Line 853
> Incorrect syntax near the keyword 'SELECT'.
> Server: Msg 156, Level 15, State 1, Procedure
> up_WHS_PeriodWarningQControl_PROC, Line 855
> Incorrect syntax near the keyword 'SELECT'.
> Server: Msg 170, Level 15, State 1, Procedure
> up_WHS_PeriodWarningQControl_PROC, Line 856
> Line 856: Incorrect syntax near '='.
>
> Thanks in advance and best regards,|||Enric,
SET @.AUXMIN = SELECT (1.96 * @.DESV_INSERTED)
is invalid syntax. To use a SELECT expression as
a value, you need to surround the entire SELECT
expression in parenthesis:
SET @.AUXMIN = (SELECT (1.96 * @.DESV_INSERTED))
You did this correctly in the first to SET statement,
but not in the final three.
Steve Kass
Drew University
Enric wrote:
>Dear all,
>I am trying to solve this but I can't work out.
>
>Set @.DESV_INSERTED = (SELECT stdev(desv) FROM #DESVIATIONTYPICAL WHERE
> desviation_id = @.desviation_id AND desv > 0)
>SET @.AVERAGE = (SELECT AVG(DESV) FROM #DESVIATIONTYPICAL WHERE
> desviation_id = @.desviation_id AND desv > 0)
>
>SET @.AUXMIN = SELECT (1.96 * @.DESV_INSERTED)
>SET @.MINIMUM = SELECT (@.AVERAGE - @.AUXMIN)
>SET @.MAXIMUM = SELECT (@.AVERAGE + @.AUXMIN)
>
>And I obtain the following errors:
>
>Server: Msg 156, Level 15, State 1, Procedure
>up_WHS_PeriodWarningQControl_PROC, Line 850
>Incorrect syntax near the keyword 'SELECT'.
>Server: Msg 156, Level 15, State 1, Procedure
>up_WHS_PeriodWarningQControl_PROC, Line 852
>Incorrect syntax near the keyword 'SELECT'.
>Server: Msg 156, Level 15, State 1, Procedure
>up_WHS_PeriodWarningQControl_PROC, Line 853
>Incorrect syntax near the keyword 'SELECT'.
>Server: Msg 156, Level 15, State 1, Procedure
>up_WHS_PeriodWarningQControl_PROC, Line 855
>Incorrect syntax near the keyword 'SELECT'.
>Server: Msg 170, Level 15, State 1, Procedure
>up_WHS_PeriodWarningQControl_PROC, Line 856
>Line 856: Incorrect syntax near '='.
>
>Thanks in advance and best regards,
>|||yeah, works.
I am feel very proud of your performance.
Enric
"Jacco Schalkwijk" wrote:
> Change:
> SET @.AUXMIN = SELECT (1.96 * @.DESV_INSERTED)
> SET @.MINIMUM = SELECT (@.AVERAGE - @.AUXMIN)
> SET @.MAXIMUM = SELECT (@.AVERAGE + @.AUXMIN)
> To:
> SET @.AUXMIN = 1.96 * @.DESV_INSERTED
> SET @.MINIMUM = AVERAGE - @.AUXMIN
> SET @.MAXIMUM = AVERAGE + @.AUXMIN
> and you probably have a few more similar lines in that procedure _after_ t
he
> code you have shown. The line numbers shown in the errors are the lines in
> the body of the stored procedure, after the keyword 'AS', so they have an
> offset comapred to the line number in the stored procedure declaration
> (which includes CREATE PROC and the parameter declarations).
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:30173799-B153-49B2-9223-4C4B282A2B27@.microsoft.com...
>
>|||Thanks Steve,
Regards to all
"Steve Kass" wrote:
> Enric,
> SET @.AUXMIN = SELECT (1.96 * @.DESV_INSERTED)
> is invalid syntax. To use a SELECT expression as
> a value, you need to surround the entire SELECT
> expression in parenthesis:
> SET @.AUXMIN = (SELECT (1.96 * @.DESV_INSERTED))
> You did this correctly in the first to SET statement,
> but not in the final three.
> Steve Kass
> Drew University
>
>
> Enric wrote:
>
>
Friday, February 10, 2012
Calculating a dataset depending on a parameter in a report
Dear all
I have a SSRS report getting data from a cube. It has 3 tables and 3
different datasets linked to these tables. One table shows all members of one
dimension. Therefore this dataset requires most of the processing time.
I want to give the user a possibility inside this report to choose if he
wants to see all the members or not. Well one easy way would be to make 2
Reports. Another way more elegant way would be to define a report parameter
which will hold a part of a mdx querry that will be used in this dataset:
- [Suppliers].[Key].[All]
- [Suppliers].[Key].members
Inside the dataset I use the STRTOSET function with this parameter.
My problem is now that the first mdx querry let the column Key disappear
inside the dataset and therefore I get a error message while displaying the
report one table is referring an inexistent field.
Do you have an idea to solve this problem or do you see another approach?
Regards,
MarcHello Marc,
My understanding is that your customer wants to have an option to show the
Key field in the Report or not. And you wants to use a parameter in the
dataset MDX query to control the dataset. If I misunderstood your concern,
please feel free to let me know.
Well, since your dataset definition have been modified by the parameter,
you will lost the Key field in the dataset. That of course will cause an
error.
One workaround is using the subreport in the report and include 2 subreport
and then use the parameter to control which one to show up.
Also, I think the drilldown feature is another workaround for your
situation.
Hiding Report Items
http://msdn2.microsoft.com/en-us/library/ms157454.aspx
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei
Thanks for your answer. Can you explain me your workarounds more in detail?
My problem with one dataset showing all members is that it takes too long to
execute. So I have to give the user control if he wants either more
information but wait or less information and have quick report execution.
Your first workaround using a subreport sounds fine. But I don't know how I
can give a parameter value to the subreport (no function button there).
Hiding report items will not help because the dataset will be filled even
though the report item is hidden.
How does the drilldown feature work?
Thanks for your help,
Regards,
Marc|||Hello Marc,
For the Subreport, you could use the parameter to control its visability
property.
The drilldown feature works like a toggle item.
You could accomplish this by the article I provided in the previous post.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Wei
Thanks for your answer. Just hiding a report items does not solve my problem
because a filling of a dataset causes a long execution time. This happens
when a report item is visible or not.
To solve this issue I think I need a way to control which mdx is used to
fill a dataset or which dataset gets executed, both depending on a parameter.
Is there a way to achieve this?
Regards, Marc|||Hello Marc,
Unfortunatly, you could not use the parameter to control which dataset the
report item use.
This is because the layout will refer the fields in the dataset. If the
fields are not match, the layout could not render correctly.
My suggestion is to use the cache to have a better performance of the
report.
Report Caching in Reporting Services
http://msdn2.microsoft.com/en-us/library/ms155927.aspx
Also, if you are developing a ASP.NET web application, you could use the
Report Viewer Control to refer different report in your Web Page.
Hope this information will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei
Thanks for your answer. I solved this problem like you said with a
parameter. This parameter determines if a report item is visible or not.
In the dataset bound to this report item I defined members which get
calculated when the table is shown or just show 0 when the report item is
hidden (case â?¦ when â?¦ thenâ?¦ end).
But I still think this is complicated to achieve and it would be nice to
have a feature that does determine if a dataset gets calculated or not.
And it would be also nice if reporting services would show the all member as
a separate column - right now it gets hidden.
Kind regards,
Marc|||Hello Marc,
Thank you for your reply.
You could send your feedback directly to the product team via the following
web site:
http://connect.microsoft.com/SQLServer
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
I have a SSRS report getting data from a cube. It has 3 tables and 3
different datasets linked to these tables. One table shows all members of one
dimension. Therefore this dataset requires most of the processing time.
I want to give the user a possibility inside this report to choose if he
wants to see all the members or not. Well one easy way would be to make 2
Reports. Another way more elegant way would be to define a report parameter
which will hold a part of a mdx querry that will be used in this dataset:
- [Suppliers].[Key].[All]
- [Suppliers].[Key].members
Inside the dataset I use the STRTOSET function with this parameter.
My problem is now that the first mdx querry let the column Key disappear
inside the dataset and therefore I get a error message while displaying the
report one table is referring an inexistent field.
Do you have an idea to solve this problem or do you see another approach?
Regards,
MarcHello Marc,
My understanding is that your customer wants to have an option to show the
Key field in the Report or not. And you wants to use a parameter in the
dataset MDX query to control the dataset. If I misunderstood your concern,
please feel free to let me know.
Well, since your dataset definition have been modified by the parameter,
you will lost the Key field in the dataset. That of course will cause an
error.
One workaround is using the subreport in the report and include 2 subreport
and then use the parameter to control which one to show up.
Also, I think the drilldown feature is another workaround for your
situation.
Hiding Report Items
http://msdn2.microsoft.com/en-us/library/ms157454.aspx
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei
Thanks for your answer. Can you explain me your workarounds more in detail?
My problem with one dataset showing all members is that it takes too long to
execute. So I have to give the user control if he wants either more
information but wait or less information and have quick report execution.
Your first workaround using a subreport sounds fine. But I don't know how I
can give a parameter value to the subreport (no function button there).
Hiding report items will not help because the dataset will be filled even
though the report item is hidden.
How does the drilldown feature work?
Thanks for your help,
Regards,
Marc|||Hello Marc,
For the Subreport, you could use the parameter to control its visability
property.
The drilldown feature works like a toggle item.
You could accomplish this by the article I provided in the previous post.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Wei
Thanks for your answer. Just hiding a report items does not solve my problem
because a filling of a dataset causes a long execution time. This happens
when a report item is visible or not.
To solve this issue I think I need a way to control which mdx is used to
fill a dataset or which dataset gets executed, both depending on a parameter.
Is there a way to achieve this?
Regards, Marc|||Hello Marc,
Unfortunatly, you could not use the parameter to control which dataset the
report item use.
This is because the layout will refer the fields in the dataset. If the
fields are not match, the layout could not render correctly.
My suggestion is to use the cache to have a better performance of the
report.
Report Caching in Reporting Services
http://msdn2.microsoft.com/en-us/library/ms155927.aspx
Also, if you are developing a ASP.NET web application, you could use the
Report Viewer Control to refer different report in your Web Page.
Hope this information will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei
Thanks for your answer. I solved this problem like you said with a
parameter. This parameter determines if a report item is visible or not.
In the dataset bound to this report item I defined members which get
calculated when the table is shown or just show 0 when the report item is
hidden (case â?¦ when â?¦ thenâ?¦ end).
But I still think this is complicated to achieve and it would be nice to
have a feature that does determine if a dataset gets calculated or not.
And it would be also nice if reporting services would show the all member as
a separate column - right now it gets hidden.
Kind regards,
Marc|||Hello Marc,
Thank you for your reply.
You could send your feedback directly to the product team via the following
web site:
http://connect.microsoft.com/SQLServer
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
Calculated Member... Using case statment?!
Dear Friends,
I have a doubt that probably is to simple for you, but I'm a beginner in this subject and I need you.
how can I do a calculated member with case statment? I need to calculate the market value for factData based on each dimension...
For example the same calculated member, in the case of dimension is Bonds(1) the formula is one but when the dimension is Equities(2) the formula is diferent .
How can i do it? In the finally I only want the MarketValue (calculated member) for all the dimensions..
understood?
Thanks and regards!!
Hi,
Sure you can use case statement. Probably the best place to do it is a calculated memeber in dsv difinition of your fact table. This field should be calculated on a process step of your cube.
-Andrew
|||Andy,
In SSAS you have Named Calculations is DSV and Calculater Members in the cube.
I want a calculated member and not named calculation because the formula is based on other calculated members that I dont have already in DSV, only in the cube...
So, what you told does not make sense, but thanks!!
|||Pedro,Try this link - should help you plenty: http://technet.microsoft.com/en-us/library/ms144841.aspx
Rob|||Using SCOPE statements http://technet.microsoft.com/en-us/library/ms145989.aspx should provide better performance than using CASE. Custom Member formulas on the dimension members would be another option, it depends on the particular calculation as to which is the better option.|||Yeah - do what Darren suggests. He's an expert in MDX. But I'll restrain myself from advertising the user-group meeting that he's presenting at next week.
Rob|||
Thanks All!!
Where is the Darren meeting group?
Regards!
|||I'm speaking at the Adelaide SQL Server User Group next week (the one that Rob is President of 
http://www.sqlserver.org.au/events/ViewEvent.aspx?EventId=263
Calculated Member... Using case statment?!
Dear Friends,
I have a doubt that probably is to simple for you, but I'm a beginner in this subject and I need you.
how can I do a calculated member with case statment? I need to calculate the market value for factData based on each dimension...
For example the same calculated member, in the case of dimension is Bonds(1) the formula is one but when the dimension is Equities(2) the formula is diferent .
How can i do it? In the finally I only want the MarketValue (calculated member) for all the dimensions..
understood?
Thanks and regards!!
Hi,
Sure you can use case statement. Probably the best place to do it is a calculated memeber in dsv difinition of your fact table. This field should be calculated on a process step of your cube.
-Andrew
|||Andy,
In SSAS you have Named Calculations is DSV and Calculater Members in the cube.
I want a calculated member and not named calculation because the formula is based on other calculated members that I dont have already in DSV, only in the cube...
So, what you told does not make sense, but thanks!!
|||Pedro,Try this link - should help you plenty: http://technet.microsoft.com/en-us/library/ms144841.aspx
Rob|||Using SCOPE statements http://technet.microsoft.com/en-us/library/ms145989.aspx should provide better performance than using CASE. Custom Member formulas on the dimension members would be another option, it depends on the particular calculation as to which is the better option.|||Yeah - do what Darren suggests. He's an expert in MDX. But I'll restrain myself from advertising the user-group meeting that he's presenting at next week.
Rob|||
Thanks All!!
Where is the Darren meeting group?
Regards!
|||I'm speaking at the Adelaide SQL Server User Group next week (the one that Rob is President of
http://www.sqlserver.org.au/events/ViewEvent.aspx?EventId=263
Calculated Member Issue
Dear all,
My project running on AS2005 contains a dimension with 5 values in its table.
The fact table contains values for 4 of the values of the dimensions (the last one has not value yet).
When I browse the cube, if I display a calculated member (a sum) as measure and the dimension, I can see the measures for the 4 values of the dimension (the last value is not displayed).
But if I display a calculed member (done with "CALCULATIONS and which correspond to Measure.A/Measure.B), I see the measures for the 5 values of the dimension (the last value is displayed even if it has no value in the fact table). The fifth one is blank.
How can I avoid this ?
Thanks in advance for your support.
Juan
Hi Juan,
I'm assuming that you meant to say: "..if I display a sum measure and the dimension, I can see the measures for the 4 values of the dimension..".
So, if your issue occurs only with the calculated measure, not with measure group measures, you could try specifying Non Empty Behavior, like:
Create Member CurrentCube.[Measures].[RatioAB]
As [Measures].A /[Measures].B,
Non_Empty_Behavior = [Measures].B;
Subscribe to:
Posts (Atom)