Analysis Service Ports

SQL 2005 AS installed on a Cluster.
We are using a named instance and only one currently exists. MS White paper
reads: The default instance listens on port 2383, while named instances are
dynamically allocated. This behavior can be changed by explicitly assigning a
port to either the default or named instance of SSAS. If the value for Port
is 0, the default behavior described above is achieved. Otherwise, the value
is treated as an absolute (static) port number to use for the specified
instance.
I have modifed the Server PORT property, on the named instance (a default
instance does not exist) to listen on port 2382 and have added a rule to our
firewall to allow this traffic. However, AS continues to listen on port 2383.
Does anyone have any experience on this and how to change it.
Is there a range of ports I can tell our network team to open?
I am not sure on SQL 2005 but in SQL 2000 you need to start the service
again to take the effect of new port numbers...
Zaki.
"FredG" <FredG@.discussions.microsoft.com> wrote in message
news:6A9E45E1-E591-4086-B8A3-A5A1556BFFDC@.microsoft.com...
> SQL 2005 AS installed on a Cluster.
> We are using a named instance and only one currently exists. MS White
paper
> reads: The default instance listens on port 2383, while named instances
are
> dynamically allocated. This behavior can be changed by explicitly
assigning a
> port to either the default or named instance of SSAS. If the value for
Port
> is 0, the default behavior described above is achieved. Otherwise, the
value
> is treated as an absolute (static) port number to use for the specified
> instance.
> I have modifed the Server PORT property, on the named instance (a default
> instance does not exist) to listen on port 2382 and have added a rule to
our
> firewall to allow this traffic. However, AS continues to listen on port
2383.
> Does anyone have any experience on this and how to change it.
> Is there a range of ports I can tell our network team to open?
|||I have restarted the service multiple times and it continues to listen on
this port.
"Zakiuddin Mohammed" wrote:

> I am not sure on SQL 2005 but in SQL 2000 you need to start the service
> again to take the effect of new port numbers...
> Zaki.
> "FredG" <FredG@.discussions.microsoft.com> wrote in message
> news:6A9E45E1-E591-4086-B8A3-A5A1556BFFDC@.microsoft.com...
> paper
> are
> assigning a
> Port
> value
> our
> 2383.
>
>

An "EXISTS" Problem

Hey, guys,
Below are my DDL,
CREATE TABLE [dbo].[test1] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[A] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
)
CREATE TABLE [dbo].[test2] (
[A] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[B] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
)
test1 table
id A B
--
1 c 1
2 c 2
3 c 3
4 c 4
6 b
9 d
10 e
test2 table
A B
--
c 1
b 1
b 2
d 1
result table
id A B
--
2 c 2
3 c 3
4 c 4
6 b
9 d
Here is my sql to get the result table
SELECT P.id,P.A,P.B
FROM test1 P left outer join test2 R on P.A=R.A
WHERE (NOT EXISTS
(SELECT *
FROM test2 Q
WHERE P.A = Q.A AND P.A + P.B = Q.A + Q.B))
and R.A is not null
group by P.id,P.A,P.B
Can this SQL command be neater?
thanks a lot.
AllenHere are a couple of other methods, although 'cleaner' is a bit subjective'.
Personally, I prefer the NOT EXISTS technique over LEFT JOIN.
INSERT INTO test1 VALUES(1,'c',1)
INSERT INTO test1 VALUES(2,'c',2)
INSERT INTO test1 VALUES(3,'c',3)
INSERT INTO test1 VALUES(4,'c',4)
INSERT INTO test1 VALUES(6,'b', NULL)
INSERT INTO test1 VALUES(9,'d', NULL)
INSERT INTO test1 VALUES(10,'e', NULL)
GO
INSERT INTO test2 VALUES('c', 1)
INSERT INTO test2 VALUES('b', 1)
INSERT INTO test2 VALUES('b', 2)
INSERT INTO test2 VALUES('d', 1)
GO
SELECT P.id, P.A, P.B
FROM test1 P
JOIN test2 R ON P.A = R.A
WHERE NOT EXISTS
(
SELECT *
FROM test2 Q
WHERE
P.A = Q.A AND P.B = Q.B
)
GROUP BY P.id, P.A, P.B
GO
SELECT P.id, P.A, P.B
FROM test1 P
JOIN test2 R ON P.A = R.A
LEFT JOIN test2 Q ON P.A = Q.A AND P.B = Q.B
WHERE Q.A IS NULL
GROUP BY P.id, P.A, P.B
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Allen" <cpchen@.cht.com.tw> wrote in message
news:u19bQ77tDHA.2408@.tk2msftngp13.phx.gbl...
> Hey, guys,
> Below are my DDL,
> CREATE TABLE [dbo].[test1] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [A] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
> )
> CREATE TABLE [dbo].[test2] (
> [A] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
> [B] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
> )
> test1 table
> id A B
> --
> 1 c 1
> 2 c 2
> 3 c 3
> 4 c 4
> 6 b
> 9 d
> 10 e
> test2 table
> A B
> --
> c 1
> b 1
> b 2
> d 1
>
> result table
> id A B
> --
> 2 c 2
> 3 c 3
> 4 c 4
> 6 b
> 9 d
> Here is my sql to get the result table
> SELECT P.id,P.A,P.B
> FROM test1 P left outer join test2 R on P.A=R.A
> WHERE (NOT EXISTS
> (SELECT *
> FROM test2 Q
> WHERE P.A = Q.A AND P.A + P.B = Q.A + Q.B))
> and R.A is not null
> group by P.id,P.A,P.B
>
> Can this SQL command be neater?
>
> thanks a lot.
> Allen
>