Another Way To Write a Stored Procedure

In the stored procedure below, there are 9 different if conditions being
checked. I was wondering if there is a more efficient way (or not) to write
this procedure, without having to write the same select criteria each time.
The only thing different is the where clause. Any suggestions are greatly
appreciated.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROC ListParticipantsByRole(
@.testRoleEnum int,
@.errorID int = 0 OUTPUT)
AS
SET NOCOUNT ON
-- List Originators
IF (@.testRoleEnum = 1)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleOriginator = 1
ORDER BY P.DisplayName
-- List Validators
IF (@.testRoleEnum = 2)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleValidator = 1
ORDER BY P.DisplayName
-- List Screeners
IF (@.testRoleEnum = 3)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleScreener = 1
ORDER BY P.DisplayName
-- List SMEs
IF (@.testRoleEnum = 4)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleSME = 1
ORDER BY P.DisplayName
-- List Validation OPRs
IF (@.testRoleEnum = 5)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleValidationOPR = 1
ORDER BY P.DisplayName
-- List Validation Authorities
IF (@.testRoleEnum = 6)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleValidationAuthority = 1
ORDER BY P.DisplayName
-- List Officials
IF (@.testRoleEnum = 7)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleOfficial = 1
ORDER BY P.DisplayName
-- List Test Directors
IF (@.testRoleEnum = 8)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleTestDirector = 1
ORDER BY P.DisplayName
-- List Survey Participants
IF (@.testRoleEnum = 9)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleSurveyParticipant = 1
ORDER BY P.DisplayName
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOSELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE 1 = CASE @.testRoleEnum
when 1 then T.TestRoleOriginator
when 2 then T.TestRoleValidator
...
end
ORDER BY P.DisplayName
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:C4C1B5B8-7E49-467A-83C7-4DEEED88B57B@.microsoft.com...
> In the stored procedure below, there are 9 different if conditions being
> checked. I was wondering if there is a more efficient way (or not) to
> write
> this procedure, without having to write the same select criteria each
> time.
> The only thing different is the where clause. Any suggestions are greatly
> appreciated.
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> ALTER PROC ListParticipantsByRole(
> @.testRoleEnum int,
> @.errorID int = 0 OUTPUT)
> AS
> SET NOCOUNT ON
> -- List Originators
> IF (@.testRoleEnum = 1)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleOriginator = 1
> ORDER BY P.DisplayName
> -- List Validators
> IF (@.testRoleEnum = 2)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidator = 1
> ORDER BY P.DisplayName
> -- List Screeners
> IF (@.testRoleEnum = 3)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleScreener = 1
> ORDER BY P.DisplayName
> -- List SMEs
> IF (@.testRoleEnum = 4)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleSME = 1
> ORDER BY P.DisplayName
> -- List Validation OPRs
> IF (@.testRoleEnum = 5)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidationOPR = 1
> ORDER BY P.DisplayName
> -- List Validation Authorities
> IF (@.testRoleEnum = 6)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidationAuthority = 1
> ORDER BY P.DisplayName
> -- List Officials
> IF (@.testRoleEnum = 7)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleOfficial = 1
> ORDER BY P.DisplayName
> -- List Test Directors
> IF (@.testRoleEnum = 8)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleTestDirector = 1
> ORDER BY P.DisplayName
> -- List Survey Participants
> IF (@.testRoleEnum = 9)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleSurveyParticipant = 1
> ORDER BY P.DisplayName
> SET NOCOUNT OFF
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>|||Mike,
A redesign of TestParticipants is the best solution. Instead of
having a bunch of separate columns TestRoleThis, TestRoleThat,
TestRoleOther, ... you should have, depending on whether
a participant can have more than one role, either a column TestRole,
with possible values 'Validator', 'Screener', etc., or a separate table
TestParticipantRoles that links the participants with their roles.
Then your stored procedure becomes a single simple query like
this (case where there is a linking table):
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN TestParticipantRoles R ON T.PersonnelID = R.PersonnelId
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE R.RoleID = @.testRoleEnum
ORDER BY P.DisplayName
Short of that, you can still now do
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE CASE @.testRoleEnum
WHEN 1 THEN T.TestRoleOriginator
WHEN 2 THEN T.TestRoleValidator
..
END = 1
ORDER BY P.DisplayName
This latter solution may turn out to have poorer performance, however.
Steve Kass
Drew University
Mike Collins wrote:

>In the stored procedure below, there are 9 different if conditions being
>checked. I was wondering if there is a more efficient way (or not) to write
>this procedure, without having to write the same select criteria each time
.
>The only thing different is the where clause. Any suggestions are greatly
>appreciated.
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>ALTER PROC ListParticipantsByRole(
> @.testRoleEnum int,
> @.errorID int = 0 OUTPUT)
>AS
> SET NOCOUNT ON
> -- List Originators
> IF (@.testRoleEnum = 1)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleOriginator = 1
> ORDER BY P.DisplayName
> -- List Validators
> IF (@.testRoleEnum = 2)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidator = 1
> ORDER BY P.DisplayName
> -- List Screeners
> IF (@.testRoleEnum = 3)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleScreener = 1
> ORDER BY P.DisplayName
> -- List SMEs
> IF (@.testRoleEnum = 4)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleSME = 1
> ORDER BY P.DisplayName
> -- List Validation OPRs
> IF (@.testRoleEnum = 5)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidationOPR = 1
> ORDER BY P.DisplayName
> -- List Validation Authorities
> IF (@.testRoleEnum = 6)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidationAuthority = 1
> ORDER BY P.DisplayName
> -- List Officials
> IF (@.testRoleEnum = 7)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleOfficial = 1
> ORDER BY P.DisplayName
> -- List Test Directors
> IF (@.testRoleEnum = 8)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleTestDirector = 1
> ORDER BY P.DisplayName
> -- List Survey Participants
> IF (@.testRoleEnum = 9)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleSurveyParticipant = 1
> ORDER BY P.DisplayName
> SET NOCOUNT OFF
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>
>|||Well, a more efficient design would store the numeric coefficient in a
single column, instead of having 9 different columns. It's hard to tell
from your requirements exactly what solution you need (e.g. can someone be a
screener and a roleSME?). But this is certainly not the best approach.
When you add or remove a role type, you have to change table structure and
all the code that references it. Blecch.
If a personnel member can only be in one role, then this design makes more
sense, IMHO:
CREATE TABLE dbo.TestRoles
(
TestRoleEnum INT PRIMARY KEY,
Description VARCHAR(32) UNIQUE
)
SET NOCOUNT ON;
INSERT dbo.TestRoles(Description) SELECT 'Originator';
...
CREATE TABLE dbo.TestParticipants
(
Personnelid INT /* FOREIGN KEY... */,
TestRoleEnum INT
)
Now your stored procedure can say:
ALTER PROCEDURE dbo.ListParticipantsByRole
@.testRoleEnum int,
@.errorID int = 0 OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT
P.DisplayName,
T.Personnelid
FROM
dbo.TestParticipants t
INNER JOIN
Common.dbo.vwPersonnelInfo p
ON
t.Personnelid = P.personellid
WHERE
t.TestRoleEnum = @.testRoleEnum
ORDER BY
P.DisplayName;
END
GO
And now you can add and remove test roles as you please, without having to
change any of the database structure. And as a bonus, your code becomes a
lot easier to read.
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:C4C1B5B8-7E49-467A-83C7-4DEEED88B57B@.microsoft.com...
> In the stored procedure below, there are 9 different if conditions being
> checked. I was wondering if there is a more efficient way (or not) to
> write
> this procedure, without having to write the same select criteria each
> time.
> The only thing different is the where clause. Any suggestions are greatly
> appreciated.
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> ALTER PROC ListParticipantsByRole(
> @.testRoleEnum int,
> @.errorID int = 0 OUTPUT)
> AS
> SET NOCOUNT ON
> -- List Originators
> IF (@.testRoleEnum = 1)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleOriginator = 1
> ORDER BY P.DisplayName
> -- List Validators
> IF (@.testRoleEnum = 2)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidator = 1
> ORDER BY P.DisplayName
> -- List Screeners
> IF (@.testRoleEnum = 3)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleScreener = 1
> ORDER BY P.DisplayName
> -- List SMEs
> IF (@.testRoleEnum = 4)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleSME = 1
> ORDER BY P.DisplayName
> -- List Validation OPRs
> IF (@.testRoleEnum = 5)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidationOPR = 1
> ORDER BY P.DisplayName
> -- List Validation Authorities
> IF (@.testRoleEnum = 6)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidationAuthority = 1
> ORDER BY P.DisplayName
> -- List Officials
> IF (@.testRoleEnum = 7)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleOfficial = 1
> ORDER BY P.DisplayName
> -- List Test Directors
> IF (@.testRoleEnum = 8)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleTestDirector = 1
> ORDER BY P.DisplayName
> -- List Survey Participants
> IF (@.testRoleEnum = 9)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleSurveyParticipant = 1
> ORDER BY P.DisplayName
> SET NOCOUNT OFF
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>|||try this.. and see if it works faster...
writing for 2 ofthem.. u can add the rest..
Hope this helps.
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE @.testRoleEnum = 1 and T.TestRoleOriginator = 1
ORDER BY P.DisplayName
union all
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE @.testRoleEnum = 2 and T.TestRoleValidator = 1
ORDER BY P.DisplayName|||Thank you all for the quick and great responses. I see now better ways of
designing my tables and how to better write my stored procedure. In this
scenario, a person can have more than one role.
"Mike Collins" wrote:

> In the stored procedure below, there are 9 different if conditions being
> checked. I was wondering if there is a more efficient way (or not) to writ
e
> this procedure, without having to write the same select criteria each tim
e.
> The only thing different is the where clause. Any suggestions are greatly
> appreciated.
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> ALTER PROC ListParticipantsByRole(
> @.testRoleEnum int,
> @.errorID int = 0 OUTPUT)
> AS
> SET NOCOUNT ON
> -- List Originators
> IF (@.testRoleEnum = 1)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleOriginator = 1
> ORDER BY P.DisplayName
> -- List Validators
> IF (@.testRoleEnum = 2)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidator = 1
> ORDER BY P.DisplayName
> -- List Screeners
> IF (@.testRoleEnum = 3)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleScreener = 1
> ORDER BY P.DisplayName
> -- List SMEs
> IF (@.testRoleEnum = 4)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleSME = 1
> ORDER BY P.DisplayName
> -- List Validation OPRs
> IF (@.testRoleEnum = 5)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidationOPR = 1
> ORDER BY P.DisplayName
> -- List Validation Authorities
> IF (@.testRoleEnum = 6)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidationAuthority = 1
> ORDER BY P.DisplayName
> -- List Officials
> IF (@.testRoleEnum = 7)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleOfficial = 1
> ORDER BY P.DisplayName
> -- List Test Directors
> IF (@.testRoleEnum = 8)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleTestDirector = 1
> ORDER BY P.DisplayName
> -- List Survey Participants
> IF (@.testRoleEnum = 9)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleSurveyParticipant = 1
> ORDER BY P.DisplayName
> SET NOCOUNT OFF
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>|||If you are planning to use a case statement, I would suggest going for a
union all
like I mentioned since it helps in better use of indexes, if any. If you
have not indexed the columns in the where clause, then go for the case. But
,
if you are redesining the table, thats the best way.|||That's a fairly straightforward approach. And here I was going to suggest
dynamic SQL.
Of course, the various suggestions to change the table structure are much
better in the long run.
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:u9$RbXPeGHA.564@.TK2MSFTNGP02.phx.gbl...
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE 1 = CASE @.testRoleEnum
> when 1 then T.TestRoleOriginator
> when 2 then T.TestRoleValidator
> ...
> end
> ORDER BY P.DisplayName
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:C4C1B5B8-7E49-467A-83C7-4DEEED88B57B@.microsoft.com...
greatly
>|||"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:O9EDeiPeGHA.3556@.TK2MSFTNGP02.phx.gbl...
> That's a fairly straightforward approach. And here I was going to suggest
> dynamic SQL.
> Of course, the various suggestions to change the table structure are much
> better in the long run.
I'm getting lazy. :-)|||Thanks for the additional input. Unfortunately it is not my decision to
redesign the database and I might have to use the case statements. I am
definitely going to give my two cents about redesigning the database.
"Omnibuzz" wrote:

> If you are planning to use a case statement, I would suggest going for a
> union all
> like I mentioned since it helps in better use of indexes, if any. If you
> have not indexed the columns in the where clause, then go for the case. Bu
t ,
> if you are redesining the table, thats the best way.sql

Another SelectCommand Distinct problem

why i type the select command like below the disctinct doesnt work? the query stil show all the Category i hav so how do i fix it??

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT DISTINCT Category, ID FROM Notes WHERE (UserName LIKE '%' + @.UserName + '%') ORDER BY ID DESC">

<SelectParameters>

<asp:SessionParameterName="UserName"SessionField="UserName"Type="String"/>

</SelectParameters>

</asp:SqlDataSource>

Hi

You may want to try

"SELECT DISTINCT Category FROM Notes WHERE (UserName LIKE '%' + @.UserName + '%') ORDER BY ID DESC"

The SELECT shown, viz., "Category, ID" brings all the distinct combinations of Category AND ID

Fouwaaz

|||

it work using singel feild.lets say if i select category, Title and weblink but i oni wan DISTINCT Category how do i change the code??

SELECT DISTINCT Category, Title,Weblink FROM Bookmarks WHERE (UserName = @.username) AND (Category = 'Top1' OR Category = 'Top2' OR Category = 'Top3' OR Category = 'Top4' OR Category = 'Top5')

now the distinct is handle 3 feild how to i make the distinct handle 1 feild?? bcoz i was trying to the output like this:

Original Data

Category Title Weblink
Top1 Top1Title www.top1title.com

Top1 Top2Title www.top2title.com

Top2 Top3Title www.top3title.com

Top2 Top4Title www.top4title.com

Top3 Top5Title www.top5title.com

Top3 Top6Title www.top6title.com

Output is the title become weblink...

|||

Hi

Sorry, it looks like I did not understand your question. This is the output you have shown

Category Title Weblink
Top1 Top1Title www.top1title.com

Top1 Top2Title www.top2title.com

Top2 Top3Title www.top3title.com

Top2 Top4Title www.top4title.com

Top3 Top5Title www.top5title.com

Top3 Top6Title www.top6title.com

Now, could you show the output that you would like to get?

Thanks

Fouwaaz

Another Row After SubTotal in Matrix

I have a Matrix in Report, with SubTotals of rows and columns.

I want to add another row below the subtotal (as well as another column to the left) in order to display percentage. Could anyone please tell me how?

There is a work around. you need to add one more column and display percentage in that column, this would add a column after total column and would display the sum of percentage column.

Then you reduce the width of the column (the column that you have manually added) to 0.

|||

First, Thanks for your reply.

I Tried it, but it doesn't work.

When I set the width of the added column to 0, the total row becomes 0 too.

?

Another question regarding licensing

Hi all,
I read different statements below and now I'm totally confused. My case is
as following: We want install SQLServer 2000 Standard Edition on a single
processor server. Our application Saperion is the only application which uses
that SQL instance. For Saperion we have a concurrent licensing model. That
means we have defined about 40 user who could use these applicaton, but at
the same time only 20 user have access. So, in maximum 20 user can work with
Saperion with SQL as the database engine.
Now the question is what kind of license do I have to buy. I understand the
processor licensing model, but the device CAL's and User CAL's not!
Thank you for your kind help in this matter.
Regards,
KE
User and device CAL licensing is based on total numbers rather than
concurrent numbers. On the information given it seems you would need 40 user
CALs if you chose the User CAL model. Device CALs are licensed per end-user
device so if you have multiple users sharing a workstation for example then
device CALs may be more cost effective than user CALs.
That's my understanding, but don't take my word for it. Ask a Microsoft
representative if you want a definitive answer.
Lots of info on licensing here:
http://www.microsoft.com/sql/howtobuy/default.asp
You may find the FAQ helpful.
David Portas
SQL Server MVP
|||Hi David,
Thank you very much for your answer. 40 user CAL's are not logical for me,
because Saperion never allows us to have more than 20 users online. That
means maximum 20 users can use the SQL server at a certain time. Logical for
me would be 20 user CAL's. If I'm wrong, tell me why......
Best regards,
KE
"David Portas" wrote:

> User and device CAL licensing is based on total numbers rather than
> concurrent numbers. On the information given it seems you would need 40 user
> CALs if you chose the User CAL model. Device CALs are licensed per end-user
> device so if you have multiple users sharing a workstation for example then
> device CALs may be more cost effective than user CALs.
> That's my understanding, but don't take my word for it. Ask a Microsoft
> representative if you want a definitive answer.
> Lots of info on licensing here:
> http://www.microsoft.com/sql/howtobuy/default.asp
> You may find the FAQ helpful.
> --
> David Portas
> SQL Server MVP
> --
>
|||SQL Server isn't licensed per CONCURRENT user. The fact that Saperion is
licensed that way wouldn't affect SQL Server licensing.
A user licence grants A USER a licence to use the software so one licence is
required PER USER not per connection or connected user.
David Portas
SQL Server MVP
|||Hi David,
Thank you very much for your explanation. In my point of view the SQL
licensing NOT per CONCURRENT user is very strange. Logical for me would be
only the CONCURRENT approach. However MS can do what they want!?!?!?
I have following additional question. Do we need for each SQL server
installation CAL's for all possible user (40 in our case)?
Example 1:
2 SQL Server Standard with user CAL's, 40 user can work with applications,
which use these servers.
Do we need 2 server licenses and 40 CAL's or 80 CAL's?
Example 2:
1 SQL Server Standard with processor license, 1 SQL Server Standard with
user CAL's.
According to MS homepage for the SQL Server with processor license I do not
need any CAL's. Is that correct? If yes, I assume that we need 1 server
license and 40 CAL's for the second SQL installation?
Thank you very much in advance.
Regards,
KE
"David Portas" wrote:

> SQL Server isn't licensed per CONCURRENT user. The fact that Saperion is
> licensed that way wouldn't affect SQL Server licensing.
> A user licence grants A USER a licence to use the software so one licence is
> required PER USER not per connection or connected user.
> --
> David Portas
> SQL Server MVP
> --
>
|||Inline below:

> I have following additional question. Do we need for each SQL server
> installation CAL's for all possible user (40 in our case)?
> Example 1:
> 2 SQL Server Standard with user CAL's, 40 user can work with applications,
> which use these servers.
> Do we need 2 server licenses and 40 CAL's or 80 CAL's?
2 server licenses. 40 CAL's. You can look at it like each user has a piece of paper that gives
him/her the right to connect to SQL Servers.

> Example 2:
> 1 SQL Server Standard with processor license, 1 SQL Server Standard with
> user CAL's.
> According to MS homepage for the SQL Server with processor license I do not
> need any CAL's. Is that correct? If yes, I assume that we need 1 server
> license and 40 CAL's for the second SQL installation?
Correct.
FYI: To speak to someone regarding licensing:
You can call 1-800-426-9400 (select option 4), Monday through Friday, 6:00
A.M. to 6:00 P.M. (PST) to speak directly to a Microsoft licensing
specialist for licensing problem. Worldwide customers can use the Guide to
Worldwide Microsoft Licensing Sites
http://www.microsoft.com/licensing/index/worldwide.asp to find contact
information in their locations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KE" <KE@.discussions.microsoft.com> wrote in message
news:7D9A468D-FB00-4D72-AD37-2324CF8C84AF@.microsoft.com...[vbcol=seagreen]
> Hi David,
> Thank you very much for your explanation. In my point of view the SQL
> licensing NOT per CONCURRENT user is very strange. Logical for me would be
> only the CONCURRENT approach. However MS can do what they want!?!?!?
> I have following additional question. Do we need for each SQL server
> installation CAL's for all possible user (40 in our case)?
> Example 1:
> 2 SQL Server Standard with user CAL's, 40 user can work with applications,
> which use these servers.
> Do we need 2 server licenses and 40 CAL's or 80 CAL's?
> Example 2:
> 1 SQL Server Standard with processor license, 1 SQL Server Standard with
> user CAL's.
> According to MS homepage for the SQL Server with processor license I do not
> need any CAL's. Is that correct? If yes, I assume that we need 1 server
> license and 40 CAL's for the second SQL installation?
> Thank you very much in advance.
> Regards,
> KE
>
>
>
> "David Portas" wrote:

Another nested SQL question

I am trying to get a nested SQL statement to work in my main SQL report code below. I can successfully run the nested code by itself and the main code by itself, however, I am having some trouble getting them to work together. [Note: the chart_components table and the episodes tables can be linked via the episode_key field ]

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--Begin nested query

(SELECT et1.episode_key, MAX(et1.status_date)

FROM srm.chart_components et1, srm.chart_components et2

WHERE et1.episode_key = et2.episode_key

AND et1.chart_component_ke = et2.chart_component_ke

AND et1.deficiency_type = et2.deficiency_type

AND et1.deficiency_status = et2.deficiency_status

AND et1.status_date = et2.status_date AND et2.deficiency_status = 'C'

GROUP by et1.episode_key

HAVING COUNT(et1.episode_key) = COUNT(et2.episode_key)) AS Chart_Comp_Date

-- End nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

See the much simpler version of the query that gets the Chart_Comp_Date from my reply in your other thread. If you want just the date in the SELECT list then you need to select only that column. You are selecting the episode_key and the date. This will raise errors. So fix it like:

Code Snippet

-- Begin nested query:

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

Thanks. The code below reflects your code snippet. However, I am receiving the following error when I run it.

"The multi-part identifier "c.deficiency_status" could not be bound."

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

-- begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

--end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

I had a mistake in the alias inside the case expression. Change c.deficiency_status to c1.deficiency_status.

|||

I just noticed that I get the same error is I run your code snippet (see below) just by itself.

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

I made the change above and and now receiving this error.

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Warning: Null value is eliminated by an aggregate or other SET operation.

My code follows:

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

-- end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

You have no correlation between the query in the SELECT list and the tables in the FROM clause. You need to reference the EPISODE_KEY from one of the outer tables also like below. Otherwise, you will get errors depending on the data.

Code Snippet

(select max()

...

where c1.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

having count(*) .... ) as Chart_Comp_Date

|||

Sometimes you can look at a problem for too long and not see the answer right in front of you. I can't believe I missed this! Thank you Umachandar for all your help with this query. I appreciate it.

|||

My thanks to Umachandar, Arnie and Shawn for their help on this problem. Below is the code I ended up using in the event someone else finds themselves in a similar situation.

select max(c1.status_date)as Chart_Comp_Dt,
c1.chart_component_ke,
c2.episode_type as Visit_Type,
c1.deficiency_type,
c1.deficiency_status,
c1.episode_key,
c2.account_number as Account_No,
c2.medrec_no as MRN,
c2.episode_date as Disch_Date,
c4.patientname as Patient_Name,
MAX(c3.event_date) as ABSCOMPDT
from srm.chart_components c1, srm.episodes c2, srm.event_history c3, dbo.PtMstr c4
where c1.EPISODE_KEY = c2.EPISODE_KEY
and c2.EPISODE_KEY = c3.ITEM_KEY
and c2.ACCOUNT_NUMBER = c4.accountnumber
and c2.episode_date between @.StartDate and @.EndDate
and c2.episode_type IN(@.visittype)
group by c1.episode_key,c1.chart_component_ke,c1.deficiency_type,
c1.deficiency_status,c1.episode_key,c2.account_number,c2.episode_type,
c2.medrec_no,c2.episode_date,c4.patientname
having (c2.episode_date < max(c1.status_date)) and
count(*) = sum(case c1.deficiency_status when 'C' then 1 end)
order by c2.episode_date desc

Another nested SQL question

I am trying to get a nested SQL statement to work in my main SQL report code below. I can successfully run the nested code by itself and the main code by itself, however, I am having some trouble getting them to work together. [Note: the chart_components table and the episodes tables can be linked via the episode_key field ]

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--Begin nested query

(SELECT et1.episode_key, MAX(et1.status_date)

FROM srm.chart_components et1, srm.chart_components et2

WHERE et1.episode_key = et2.episode_key

AND et1.chart_component_ke = et2.chart_component_ke

AND et1.deficiency_type = et2.deficiency_type

AND et1.deficiency_status = et2.deficiency_status

AND et1.status_date = et2.status_date AND et2.deficiency_status = 'C'

GROUP by et1.episode_key

HAVING COUNT(et1.episode_key) = COUNT(et2.episode_key)) AS Chart_Comp_Date

-- End nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

See the much simpler version of the query that gets the Chart_Comp_Date from my reply in your other thread. If you want just the date in the SELECT list then you need to select only that column. You are selecting the episode_key and the date. This will raise errors. So fix it like:

Code Snippet

-- Begin nested query:

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

Thanks. The code below reflects your code snippet. However, I am receiving the following error when I run it.

"The multi-part identifier "c.deficiency_status" could not be bound."

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

-- begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

--end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

I had a mistake in the alias inside the case expression. Change c.deficiency_status to c1.deficiency_status.

|||

I just noticed that I get the same error is I run your code snippet (see below) just by itself.

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

I made the change above and and now receiving this error.

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Warning: Null value is eliminated by an aggregate or other SET operation.

My code follows:

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

-- end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

You have no correlation between the query in the SELECT list and the tables in the FROM clause. You need to reference the EPISODE_KEY from one of the outer tables also like below. Otherwise, you will get errors depending on the data.

Code Snippet

(select max()

...

where c1.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

having count(*) .... ) as Chart_Comp_Date

|||

Sometimes you can look at a problem for too long and not see the answer right in front of you. I can't believe I missed this! Thank you Umachandar for all your help with this query. I appreciate it.

|||

My thanks to Umachandar, Arnie and Shawn for their help on this problem. Below is the code I ended up using in the event someone else finds themselves in a similar situation.

select max(c1.status_date)as Chart_Comp_Dt,
c1.chart_component_ke,
c2.episode_type as Visit_Type,
c1.deficiency_type,
c1.deficiency_status,
c1.episode_key,
c2.account_number as Account_No,
c2.medrec_no as MRN,
c2.episode_date as Disch_Date,
c4.patientname as Patient_Name,
MAX(c3.event_date) as ABSCOMPDT
from srm.chart_components c1, srm.episodes c2, srm.event_history c3, dbo.PtMstr c4
where c1.EPISODE_KEY = c2.EPISODE_KEY
and c2.EPISODE_KEY = c3.ITEM_KEY
and c2.ACCOUNT_NUMBER = c4.accountnumber
and c2.episode_date between @.StartDate and @.EndDate
and c2.episode_type IN(@.visittype)
group by c1.episode_key,c1.chart_component_ke,c1.deficiency_type,
c1.deficiency_status,c1.episode_key,c2.account_number,c2.episode_type,
c2.medrec_no,c2.episode_date,c4.patientname
having (c2.episode_date < max(c1.status_date)) and
count(*) = sum(case c1.deficiency_status when 'C' then 1 end)
order by c2.episode_date desc

Another nested SQL question

I am trying to get a nested SQL statement to work in my main SQL report code below. I can successfully run the nested code by itself and the main code by itself, however, I am having some trouble getting them to work together. [Note: the chart_components table and the episodes tables can be linked via the episode_key field ]

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--Begin nested query

(SELECT et1.episode_key, MAX(et1.status_date)

FROM srm.chart_components et1, srm.chart_components et2

WHERE et1.episode_key = et2.episode_key

AND et1.chart_component_ke = et2.chart_component_ke

AND et1.deficiency_type = et2.deficiency_type

AND et1.deficiency_status = et2.deficiency_status

AND et1.status_date = et2.status_date AND et2.deficiency_status = 'C'

GROUP by et1.episode_key

HAVING COUNT(et1.episode_key) = COUNT(et2.episode_key)) AS Chart_Comp_Date

-- End nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

See the much simpler version of the query that gets the Chart_Comp_Date from my reply in your other thread. If you want just the date in the SELECT list then you need to select only that column. You are selecting the episode_key and the date. This will raise errors. So fix it like:

Code Snippet

-- Begin nested query:

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

Thanks. The code below reflects your code snippet. However, I am receiving the following error when I run it.

"The multi-part identifier "c.deficiency_status" could not be bound."

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

-- begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

--end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

I had a mistake in the alias inside the case expression. Change c.deficiency_status to c1.deficiency_status.

|||

I just noticed that I get the same error is I run your code snippet (see below) just by itself.

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

I made the change above and and now receiving this error.

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Warning: Null value is eliminated by an aggregate or other SET operation.

My code follows:

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

-- end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

You have no correlation between the query in the SELECT list and the tables in the FROM clause. You need to reference the EPISODE_KEY from one of the outer tables also like below. Otherwise, you will get errors depending on the data.

Code Snippet

(select max()

...

where c1.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

having count(*) .... ) as Chart_Comp_Date

|||

Sometimes you can look at a problem for too long and not see the answer right in front of you. I can't believe I missed this! Thank you Umachandar for all your help with this query. I appreciate it.

|||

My thanks to Umachandar, Arnie and Shawn for their help on this problem. Below is the code I ended up using in the event someone else finds themselves in a similar situation.

select max(c1.status_date)as Chart_Comp_Dt,
c1.chart_component_ke,
c2.episode_type as Visit_Type,
c1.deficiency_type,
c1.deficiency_status,
c1.episode_key,
c2.account_number as Account_No,
c2.medrec_no as MRN,
c2.episode_date as Disch_Date,
c4.patientname as Patient_Name,
MAX(c3.event_date) as ABSCOMPDT
from srm.chart_components c1, srm.episodes c2, srm.event_history c3, dbo.PtMstr c4
where c1.EPISODE_KEY = c2.EPISODE_KEY
and c2.EPISODE_KEY = c3.ITEM_KEY
and c2.ACCOUNT_NUMBER = c4.accountnumber
and c2.episode_date between @.StartDate and @.EndDate
and c2.episode_type IN(@.visittype)
group by c1.episode_key,c1.chart_component_ke,c1.deficiency_type,
c1.deficiency_status,c1.episode_key,c2.account_number,c2.episode_type,
c2.medrec_no,c2.episode_date,c4.patientname
having (c2.episode_date < max(c1.status_date)) and
count(*) = sum(case c1.deficiency_status when 'C' then 1 end)
order by c2.episode_date desc

AND - OR Sql syntax problem

Need a OR clause in the below statement but can't get it right.

Existing:

WHERE(L_PicTable.PicCity = @.PicCity)AND(L_PicTable.PicState = @.PicState)AND(L_PicTable.LinkType ='C')AND(L_PicTable.PicEnabled = 1)

NEED it to be:

WHERE(L_PicTable.PicCity = @.PicCity)AND(L_PicTable.PicState = @.PicState)AND(L_PicTable.LinkType ='C' ORL_PicTable.LinkType ='CS')AND(L_PicTable.PicEnabled = 1)

Can't get this clause to work - are my ('s wrong?

AND(L_PicTable.LinkType ='C' ORL_PicTable.LinkType ='CS')

Try with this:

L_PicTable.LinkType =N'C' OR L_PicTable.LinkType =N'CS'

|||

zoltac007:

AND(L_PicTable.LinkType ='C' ORL_PicTable.LinkType ='CS')

You can useANDL_PicTable.LinkType in ( 'C' ,'CS' ). One more thing, you don't need to place each and every AND clause in a separate pair of braces, they are used just when you want the output ( true or false ) to be returned back considering a set of the conditions as a whole.

|||

Hello zoltac,

There's no problem with the ()'s.

Your clause rewritten in boolean logic is:

(L_PicTable.PicCity = @.PicCity)AND(L_PicTable.PicState = @.PicState)AND (L_PicTable.LinkType ='C') AND(L_PicTable.PicEnabled = 1)

OR

(L_PicTable.PicCity = @.PicCity)AND(L_PicTable.PicState = @.PicState)AND (L_PicTable.LinkType ='CS') AND(L_PicTable.PicEnabled = 1)

What is the statement supposed to do?

Jeroen Molenaar.

|||

I am trying to select all records where Enabled is true, City, State match the page parameters AND the LinkType is either C or CS. There are only three values for the LinkType field, C, S or CS.

Analysis Services error

I was able to build SQL 2005 cubes from my project. However, for some reasons, it doesn't work any more, and I have the error below. Anyone can shed some light would be greatly appreciated.

Error 1 File system error: The logical file cannot be found inside the physical file. Physical file: \\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Beacon DW.0.db\Tbl Policy Coverage Data.6.dim\14.ERP Effective Month.Tbl Policy Coverage Data.dstore. Logical file: . Errors in the metadata manager. An error occurred when loading the Coverage dimension, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Beacon DW.0.db\Tbl Policy Coverage Data.16.dim.xml'. Errors in the metadata manager. An error occurred when loading the Production cube, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Beacon DW.0.db\Beacon DW.350.cub.xml'. 0 0

Hi,

It sounds like the file is corrupted. It looks like its only the Coverage dimension, so you may be lucky.

Can the project be built or is the error when you try to deploy the project to the server?

If the error is in the deployment try to deploy to a new database to see if the corruption is on the server itself.

If the error is on the build which tells me something is corrupted in the project files itself do the following.

1. Go to a back up of the project if you have one.

2. If not make a backup of the project now and delete the Coverage dimension from the project to see if you can build it without that dimension.

I hope that helps,

David Botzenhart

|||

It appears that it is one of the xml files for the Coverage dimension was corrupted on the SQL server. I have deleted the Coverage dimension on my project, and it failed with the same error when I rebuilt the cube. Do you know of how to fix the corrupted file on the SQL server?

Mitch Cheung

Analysis Services 2005 OLE DB Provider Error

I try to open a SSAS Cube but I get a OLE DB error message below.

The same cube I can browse with the SQL Server Business Intelligence Development Studio.

Error message:

Microsoft OLE DB Provider for Analysis Services 2005 Microsoft OLE DB Provider for Analysis Services 2005 reported:

Errors in the OLE DB provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the '<MY Server Name>' server. Errors in the OLE DB provider. An error occurred while named instance information was being retrieved from the SQLBrowser service on the '<MY Server Name>' server.

The SQL Browser is used when you are attempting to communicate with a named instance. What you are seeing is a known problem, fixed in SP1, where the SQL Browser needs communication with a domain controller to validate your access rights. This happens to me if I disconnect my laptop from the network and try an connect to a named instance on it. I've also seen it if the SQL Browser is running under a service account that isn't a machine administrator -- and third I've seen this if you are running under XP SP2 and the firewall is stopping your connectivity to the SQL Browser.

You have a few options: 1) make it the default instance -- that works without any problems, 2) always make sure a DC is available, 3) make sure the XP firewall is disabled on the SQL Browser port# (and the various instance port#s), 4) attempt to connect specifically to the port # for the instance (which bypasses the redirector) -- try using <server>:<port#> syntax for the server name.

Hope that helps.

_-_-_ Dave

|||

Thanks Dave,

I have check the options:

1. I have installed sql2005 with the default instance <MSSQLSERVER> that is not the problem

2. I'm connected to the DC

3. I have disabled the firewall, and that has changed the error message!!

The new message is:

Error reported by OLAP server.

Additional information:

Microsoft? OLE DB Provider for Analysis Services reported:

Cannot connect to the server '<MYSERVER>. The server is either not started or too busy.

But I can work with the SQL Server Business Intelligence Development Studio without any problem!!

|||

I have found the solution for the reported error message.

After reinstalling the "Workstation components" I can connect to the cubes in SSAS for a client Olap tool.

Thanks Dave for your support.

Regards,

Peter

|||I got the same error with the same symptoms but resolved the OLAP connection problem simply by reinstalling the OLAP provider for SQL Server 2005. The file name is SQLServer2005_ASOLEDB9.msi and it can be found on the microsoft downloads site.|||

Hi Dave,

Some questions for you about your options:

1. How do you make SQL Server 2005 the default instance if SQL Server 2000 is also installed on the same machine.
SQL Server 2000 can be removed if this is an option.
2. How can you confirm that the DC is available besides when you log into the server which is on the domain?
3. The sql server is on a windows 2003 server. The AD server is in another state and is accessed via a VPN. Could this be a problem?
4. How do you connect to the port # since when I use the Data Connection Wizard and use 10.1.1.1:1384 it comes back with the error:
"Transport Layer error."
If I use the instance name syntax -> 10.1.1.1\InstanceName - it works but then it uses the redirector which causes the main error.

So a couple of your solutions have some problems for me... is there any other way around them?

Thanks,
Andrew.

|||

>>1. How do you make SQL Server 2005 the default instance if SQL Server 2000 is also installed on the same machine.

There could be only a single default instance on the machine. And Analysis Services 2000 does not support named instances, so for AS 2005 be default instance you need to remove AS2000.

>>2. How can you confirm that the DC is available besides when you log into the server which is on the domain?

Not sure about this. You can try posting on windows networing newsgroup.

>>4. How do you connect to the port # since when I use the Data Connection Wizard and use 10.1.1.1:1384 it comes back with the error:
"Transport Layer error."
Make sure you've got port numeber correctly. By default named instance picks up a port number dynamically on every restart. You can force it to use fixed port by specifying Port server configuration property.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Analysis Services 2005 OLE DB Provider Error

I try to open a SSAS Cube but I get a OLE DB error message below.

The same cube I can browse with the SQL Server Business Intelligence Development Studio.

Error message:

Microsoft OLE DB Provider for Analysis Services 2005 Microsoft OLE DB Provider for Analysis Services 2005 reported:

Errors in the OLE DB provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the '<MY Server Name>' server. Errors in the OLE DB provider. An error occurred while named instance information was being retrieved from the SQLBrowser service on the '<MY Server Name>' server.

The SQL Browser is used when you are attempting to communicate with a named instance. What you are seeing is a known problem, fixed in SP1, where the SQL Browser needs communication with a domain controller to validate your access rights. This happens to me if I disconnect my laptop from the network and try an connect to a named instance on it. I've also seen it if the SQL Browser is running under a service account that isn't a machine administrator -- and third I've seen this if you are running under XP SP2 and the firewall is stopping your connectivity to the SQL Browser.

You have a few options: 1) make it the default instance -- that works without any problems, 2) always make sure a DC is available, 3) make sure the XP firewall is disabled on the SQL Browser port# (and the various instance port#s), 4) attempt to connect specifically to the port # for the instance (which bypasses the redirector) -- try using <server>:<port#> syntax for the server name.

Hope that helps.

_-_-_ Dave

|||

Thanks Dave,

I have check the options:

1. I have installed sql2005 with the default instance <MSSQLSERVER> that is not the problem

2. I'm connected to the DC

3. I have disabled the firewall, and that has changed the error message!!

The new message is:

Error reported by OLAP server.

Additional information:

Microsoft? OLE DB Provider for Analysis Services reported:

Cannot connect to the server '<MYSERVER>. The server is either not started or too busy.

But I can work with the SQL Server Business Intelligence Development Studio without any problem!!

|||

I have found the solution for the reported error message.

After reinstalling the "Workstation components" I can connect to the cubes in SSAS for a client Olap tool.

Thanks Dave for your support.

Regards,

Peter

|||I got the same error with the same symptoms but resolved the OLAP connection problem simply by reinstalling the OLAP provider for SQL Server 2005. The file name is SQLServer2005_ASOLEDB9.msi and it can be found on the microsoft downloads site.|||

Hi Dave,

Some questions for you about your options:

1. How do you make SQL Server 2005 the default instance if SQL Server 2000 is also installed on the same machine.
SQL Server 2000 can be removed if this is an option.
2. How can you confirm that the DC is available besides when you log into the server which is on the domain?
3. The sql server is on a windows 2003 server. The AD server is in another state and is accessed via a VPN. Could this be a problem?
4. How do you connect to the port # since when I use the Data Connection Wizard and use 10.1.1.1:1384 it comes back with the error:
"Transport Layer error."
If I use the instance name syntax -> 10.1.1.1\InstanceName - it works but then it uses the redirector which causes the main error.

So a couple of your solutions have some problems for me... is there any other way around them?

Thanks,
Andrew.

|||

>>1. How do you make SQL Server 2005 the default instance if SQL Server 2000 is also installed on the same machine.

There could be only a single default instance on the machine. And Analysis Services 2000 does not support named instances, so for AS 2005 be default instance you need to remove AS2000.

>>2. How can you confirm that the DC is available besides when you log into the server which is on the domain?

Not sure about this. You can try posting on windows networing newsgroup.

>>4. How do you connect to the port # since when I use the Data Connection Wizard and use 10.1.1.1:1384 it comes back with the error:
"Transport Layer error."
Make sure you've got port numeber correctly. By default named instance picks up a port number dynamically on every restart. You can force it to use fixed port by specifying Port server configuration property.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Analysis Services 2005 OLE DB Provider Error

I try to open a SSAS Cube but I get a OLE DB error message below.

The same cube I can browse with the SQL Server Business Intelligence Development Studio.

Error message:

Microsoft OLE DB Provider for Analysis Services 2005 Microsoft OLE DB Provider for Analysis Services 2005 reported:

Errors in the OLE DB provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the '<MY Server Name>' server. Errors in the OLE DB provider. An error occurred while named instance information was being retrieved from the SQLBrowser service on the '<MY Server Name>' server.

The SQL Browser is used when you are attempting to communicate with a named instance. What you are seeing is a known problem, fixed in SP1, where the SQL Browser needs communication with a domain controller to validate your access rights. This happens to me if I disconnect my laptop from the network and try an connect to a named instance on it. I've also seen it if the SQL Browser is running under a service account that isn't a machine administrator -- and third I've seen this if you are running under XP SP2 and the firewall is stopping your connectivity to the SQL Browser.

You have a few options: 1) make it the default instance -- that works without any problems, 2) always make sure a DC is available, 3) make sure the XP firewall is disabled on the SQL Browser port# (and the various instance port#s), 4) attempt to connect specifically to the port # for the instance (which bypasses the redirector) -- try using <server>:<port#> syntax for the server name.

Hope that helps.

_-_-_ Dave

|||

Thanks Dave,

I have check the options:

1. I have installed sql2005 with the default instance <MSSQLSERVER> that is not the problem

2. I'm connected to the DC

3. I have disabled the firewall, and that has changed the error message!!

The new message is:

Error reported by OLAP server.

Additional information:

Microsoft? OLE DB Provider for Analysis Services reported:

Cannot connect to the server '<MYSERVER>. The server is either not started or too busy.

But I can work with the SQL Server Business Intelligence Development Studio without any problem!!

|||

I have found the solution for the reported error message.

After reinstalling the "Workstation components" I can connect to the cubes in SSAS for a client Olap tool.

Thanks Dave for your support.

Regards,

Peter

|||I got the same error with the same symptoms but resolved the OLAP connection problem simply by reinstalling the OLAP provider for SQL Server 2005. The file name is SQLServer2005_ASOLEDB9.msi and it can be found on the microsoft downloads site.|||

Hi Dave,

Some questions for you about your options:

1. How do you make SQL Server 2005 the default instance if SQL Server 2000 is also installed on the same machine.
SQL Server 2000 can be removed if this is an option.
2. How can you confirm that the DC is available besides when you log into the server which is on the domain?
3. The sql server is on a windows 2003 server. The AD server is in another state and is accessed via a VPN. Could this be a problem?
4. How do you connect to the port # since when I use the Data Connection Wizard and use 10.1.1.1:1384 it comes back with the error:
"Transport Layer error."
If I use the instance name syntax -> 10.1.1.1\InstanceName - it works but then it uses the redirector which causes the main error.

So a couple of your solutions have some problems for me... is there any other way around them?

Thanks,
Andrew.

|||

>>1. How do you make SQL Server 2005 the default instance if SQL Server 2000 is also installed on the same machine.

There could be only a single default instance on the machine. And Analysis Services 2000 does not support named instances, so for AS 2005 be default instance you need to remove AS2000.

>>2. How can you confirm that the DC is available besides when you log into the server which is on the domain?

Not sure about this. You can try posting on windows networing newsgroup.

>>4. How do you connect to the port # since when I use the Data Connection Wizard and use 10.1.1.1:1384 it comes back with the error:
"Transport Layer error."
Make sure you've got port numeber correctly. By default named instance picks up a port number dynamically on every restart. You can force it to use fixed port by specifying Port server configuration property.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Analysis Services 2005

I have been geting an error every once in a while shown below:

OLE DB error: OLE DB or ODBC error: [DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.; 08S01.

I look at other messages here and found people that say it is a network communication problem, but I am runing Analysis Services and SQL 2005 on the same box. Can anyone help with this?

Thanks

Pat

Are you seeing any other messages in the event logs that might indicate resource issues?

B.