Another Slow Execution Plan with sp_prepare

We are having a similar problem. We have a 3rd party application that uses
sp_prepare and then executes it. The problem is the query plan from the
sp_prepare statement is different than if you run the statement using
sp_execute or sp_executesql. I run the following statements:
declare @.P1 int
exec sp_prepare @.P1 output, N'@.P1 bigint,@.P2 bigint,@.P3 bigint,@.P4
bigint,@.P5 bigint,@.P6 bigint,@.P7 bigint,@.P8 bigint', 'SELECT SHAPE
,S_.eminx,S_.eminy,S_.emaxx,S_.emaxy ,SHAPE.fid F_fid,SHAPE.numofpts
F_numofpts,SHAPE.entity F_entity,SHAPE.points F_points FROM (SELECT DISTINC
T
sp_fid,eminx,eminy,emaxx,emaxy FROM SDE.SDE.s162 SP_ WHERE SP_.gx >= @.P1 AN
D
SP_.gx <= @.P2 AND SP_.gy >= @.P3 AND SP_.gy <= @.P4 AND SP_.eminx <= @.P5 AND
SP_.eminy <= @.P6 AND SP_.emaxx >= @.P7 AND SP_.emaxy >= @.P8 ) S_
,SDE.SDE.ENTORDERLINESEGMENT, SDE.SDE.f162 SHAPE WHERE S_.sp_fid = SHAPE.fi
d
AND SDE.SDE.ENTORDERLINESEGMENT.SHAPE = S_.sp_fid AND (( ORDERID in (16320,
16825) ))', 1 select @.P1
exec sp_execute 1, 166, 169, 219, 224, 90269119, 119480870, 88777840,
117193071
This takes almost a minute to return.
Then I run this statement:
declare @.P1 int
exec sp_prepare @.P1 output, N'@.P1 bigint,@.P2 bigint,@.P3 bigint,@.P4
bigint,@.P5 bigint,@.P6 bigint,@.P7 bigint,@.P8 bigint', 'SELECT SHAPE
,S_.eminx,S_.eminy,S_.emaxx,S_.emaxy ,SHAPE.fid F_fid,SHAPE.numofpts
F_numofpts,SHAPE.entity F_entity,SHAPE.points F_points FROM (SELECT DISTINC
T
sp_fid,eminx,eminy,emaxx,emaxy FROM SDE.SDE.s162 SP_ WHERE SP_.gx >= @.P1 AN
D
SP_.gx <= @.P2 AND SP_.gy >= @.P3 AND SP_.gy <= @.P4 AND SP_.eminx <= @.P5 AND
SP_.eminy <= @.P6 AND SP_.emaxx >= @.P7 AND SP_.emaxy >= @.P8 ) S_
,SDE.SDE.ENTORDERLINESEGMENT, SDE.SDE.f162 SHAPE WHERE S_.sp_fid = SHAPE.fi
d
AND SDE.SDE.ENTORDERLINESEGMENT.SHAPE = S_.sp_fid AND (( ORDERID in (16320,
16825) ))', 1 select @.P1
DBCC Freeproccache
exec sp_execute 1, 166, 169, 219, 224, 90269119, 119480870, 88777840,
117193071
This returns in under 1 second. The only difference is the freeproccache.
The first sp_execute is using the store query plan from cache. The second i
s
forced to compile a new execution plan which is much faster.
Does anyone know why the sp_prepare query execution plan would be slower?
Shouldn't it create the same plan?
Also, how did you change sp_prepare to use varchar instead of nvarchar? I
get an error stating the sp_prepare expects @.statement of type
ntext/nchar/nvarchar.
Thanks for any help.
Doug Matney
"lorinda" wrote:

> This makes sense. Looks like I will have to find a work around. Thanks
> again for your help!
>dmatney (dmatney@.discussions.microsoft.com) writes:
> We are having a similar problem. We have a 3rd party application that
> uses sp_prepare and then executes it. The problem is the query plan
> from the sp_prepare statement is different than if you run the statement
> using sp_execute or sp_executesql. I run the following statements:
>...
> This returns in under 1 second. The only difference is the
> freeproccache. The first sp_execute is using the store query plan from
> cache. The second is forced to compile a new execution plan which is
> much faster.
> Does anyone know why the sp_prepare query execution plan would be slower?
> Shouldn't it create the same plan?
Since I have never understood the point with sp_prepare, or what it really
achieves, this had me stumped at first, but I think I know the answer.
SQL Server is fond of parameter sniffing. This means that when you
run something that has parameters, be that a stored procedure or
sp_executesql, it looks at the parameter values and take these as
guidance for the plan.
But when you run sp_prepare there are no actual parameter values. Still
SQL Server builds a plan this point, using standard assumptions. Your
query includs a lot of >=. I believe the standard assumption here is
a 30% hit-rate. This is far above the limit where a non-clustered index
is deemed to be more expensive than a table scan.
When you flush the cache, there is no plan for sp_execute to use, so
a new plan has to be created, and since the parameter values are now
available, the optimizer can "sniff" them, and build a plan that better
fits the actual values. Presumably, the input values are close to the
edges.
Since this is from a third-party app, I guess your options to address
this are limited. You could investigate if you could make any of the
involved indexes clustered, but assuming that there is a clustered index
already, this could have ramifications elsewhere in the application.
If you are on SQL 2005, you could add a plan guide, which is quite an
advanced exercise.

> Also, how did you change sp_prepare to use varchar instead of nvarchar? I
> get an error stating the sp_prepare expects @.statement of type
> ntext/nchar/nvarchar.
I guess you don't. It's the same with sp_executesql. The parameter is
ntext (nvarchar(MAX) in SQL 2005), and since this is a built-in stored
procedure, there is no implicit conversion from varchar.
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|||Thanks. I think you are correct about the parameter sniffing. I have tried
creating a plan guide using a Recompile hint and it works for this particula
r
sp_prepare but since the application doesn't use parameters for all values (
the OrderID in clause uses real values) I can't get it to work every time.
I'm going to do a little more research and see if we can't convince the
application developer to use a different method. Thanks again for your help
.
"Erland Sommarskog" wrote:

> dmatney (dmatney@.discussions.microsoft.com) writes:
> Since I have never understood the point with sp_prepare, or what it really
> achieves, this had me stumped at first, but I think I know the answer.
> SQL Server is fond of parameter sniffing. This means that when you
> run something that has parameters, be that a stored procedure or
> sp_executesql, it looks at the parameter values and take these as
> guidance for the plan.
> But when you run sp_prepare there are no actual parameter values. Still
> SQL Server builds a plan this point, using standard assumptions. Your
> query includs a lot of >=. I believe the standard assumption here is
> a 30% hit-rate. This is far above the limit where a non-clustered index
> is deemed to be more expensive than a table scan.
> When you flush the cache, there is no plan for sp_execute to use, so
> a new plan has to be created, and since the parameter values are now
> available, the optimizer can "sniff" them, and build a plan that better
> fits the actual values. Presumably, the input values are close to the
> edges.
> Since this is from a third-party app, I guess your options to address
> this are limited. You could investigate if you could make any of the
> involved indexes clustered, but assuming that there is a clustered index
> already, this could have ramifications elsewhere in the application.
> If you are on SQL 2005, you could add a plan guide, which is quite an
> advanced exercise.
>
> I guess you don't. It's the same with sp_executesql. The parameter is
> ntext (nvarchar(MAX) in SQL 2005), and since this is a built-in stored
> procedure, there is no implicit conversion from varchar.
> --
> 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
>

Another licensing question...

Hi,
Here is a brief intro about our application.
1) The client is a Borland C++ application acting as the front
end.
2) The client sends requests to our web server (IIS) over HTTPS.
3) The web server invokes a windows service.
4) The windows service calls a COM+ component.
5) The COM+ component accesses a SQL Server 2000 EE (Cluster). We
have 4 processor licenses.
6) There are few other applications, which run on another server,
accessing the SQL server also.
7) The COM+ component forwards client requests (after some
processing) to these applications.
8) These applications send the requests to external parties,
usually over one/two (not more) TCP connections.
9) The external parties send the responses to our requests
10) The responses from the external parties are routed back to the
clients thru the COM+ component.
When the client is started on the end user's desktop, it throws up
a logon screen where the user enters his user ID and password. This is
the first request from the client to the web server. If the
authentication (the user IDs and paasswords are stored in the SQL
server) is successful, he can send more requests (order entry
application). We have about 20 users right now who access the system
this way.
The client software is available for free download, but the user
ID/password is handed over only to a very restricted set of people. We
charge certain $$ per user per month.
We are duplicating this environment in another data center.
Now my question is since we have only about 20 users accessing the
SQL server, is it enough to get 1 server license and 20 CALs? There
may be other users, who might connect but fail to authenticate, though
that is a very very rare case. Even though they are invalid users, do
they make a genuine case for going for processor licenses?
Thanks & regards
zrb
I suggest you consult a Microsoft representative about this. What you
have described *may* constitute hosting. The standard retail licence
has a "no commercial hosting" clause so you may need to purchase a
service provider licence.
I say "may" because I have no real idea what Microsoft classes as
commercial hosting. In any case an authorised rep should be able to
give you a definitive answer.
David Portas
SQL Server MVP
|||Thanks. I am thoroughly confused now. :-) I'll contact a MS rep on
this.
David Portas wrote:
> I suggest you consult a Microsoft representative about this. What you
> have described *may* constitute hosting. The standard retail licence
> has a "no commercial hosting" clause so you may need to purchase a
> service provider licence.
> I say "may" because I have no real idea what Microsoft classes as
> commercial hosting. In any case an authorised rep should be able to
> give you a definitive answer.
> --
> David Portas
> SQL Server MVP
> --
|||Also, if I have 4 processor licenses (we paid $80,000 for them), but
have 2 Dual Processor servers, does this entitle me to install SQL
server on the two servers? In the same vein, can I go for 4 single
processor servers? :-)
|||Yes. A processor licence is just that - per processor. You just need a
licence for each processor in each server.
David Portas
SQL Server MVP

Another licensing question...

Hi,
Here is a brief intro about our application.
1) The client is a Borland C++ application acting as the front
end.
2) The client sends requests to our web server (IIS) over HTTPS.
3) The web server invokes a windows service.
4) The windows service calls a COM+ component.
5) The COM+ component accesses a SQL Server 2000 EE (Cluster). We
have 4 processor licenses.
6) There are few other applications, which run on another server,
accessing the SQL server also.
7) The COM+ component forwards client requests (after some
processing) to these applications.
8) These applications send the requests to external parties,
usually over one/two (not more) TCP connections.
9) The external parties send the responses to our requests
10) The responses from the external parties are routed back to the
clients thru the COM+ component.
When the client is started on the end user's desktop, it throws up
a logon screen where the user enters his user ID and password. This is
the first request from the client to the web server. If the
authentication (the user IDs and paasswords are stored in the SQL
server) is successful, he can send more requests (order entry
application). We have about 20 users right now who access the system
this way.
The client software is available for free download, but the user
ID/password is handed over only to a very restricted set of people. We
charge certain $$ per user per month.
We are duplicating this environment in another data center.
Now my question is since we have only about 20 users accessing the
SQL server, is it enough to get 1 server license and 20 CALs? There
may be other users, who might connect but fail to authenticate, though
that is a very very rare case. Even though they are invalid users, do
they make a genuine case for going for processor licenses?
Thanks & regards
zrbI suggest you consult a Microsoft representative about this. What you
have described *may* constitute hosting. The standard retail licence
has a "no commercial hosting" clause so you may need to purchase a
service provider licence.
I say "may" because I have no real idea what Microsoft classes as
commercial hosting. In any case an authorised rep should be able to
give you a definitive answer.
--
David Portas
SQL Server MVP
--|||Thanks. I am thoroughly confused now. :-) I'll contact a MS rep on
this.
David Portas wrote:
> I suggest you consult a Microsoft representative about this. What you
> have described *may* constitute hosting. The standard retail licence
> has a "no commercial hosting" clause so you may need to purchase a
> service provider licence.
> I say "may" because I have no real idea what Microsoft classes as
> commercial hosting. In any case an authorised rep should be able to
> give you a definitive answer.
> --
> David Portas
> SQL Server MVP
> --|||Also, if I have 4 processor licenses (we paid $80,000 for them), but
have 2 Dual Processor servers, does this entitle me to install SQL
server on the two servers? In the same vein, can I go for 4 single
processor servers? :-)|||Yes. A processor licence is just that - per processor. You just need a
licence for each processor in each server.
--
David Portas
SQL Server MVP
--

Another licensing question...

Hi,
Here is a brief intro about our application.
1) The client is a Borland C++ application acting as the front
end.
2) The client sends requests to our web server (IIS) over HTTPS.
3) The web server invokes a windows service.
4) The windows service calls a COM+ component.
5) The COM+ component accesses a SQL Server 2000 EE (Cluster). We
have 4 processor licenses.
6) There are few other applications, which run on another server,
accessing the SQL server also.
7) The COM+ component forwards client requests (after some
processing) to these applications.
8) These applications send the requests to external parties,
usually over one/two (not more) TCP connections.
9) The external parties send the responses to our requests
10) The responses from the external parties are routed back to the
clients thru the COM+ component.
When the client is started on the end user's desktop, it throws up
a logon screen where the user enters his user ID and password. This is
the first request from the client to the web server. If the
authentication (the user IDs and paasswords are stored in the SQL
server) is successful, he can send more requests (order entry
application). We have about 20 users right now who access the system
this way.
The client software is available for free download, but the user
ID/password is handed over only to a very restricted set of people. We
charge certain $$ per user per month.
We are duplicating this environment in another data center.
Now my question is since we have only about 20 users accessing the
SQL server, is it enough to get 1 server license and 20 CALs? There
may be other users, who might connect but fail to authenticate, though
that is a very very rare case. Even though they are invalid users, do
they make a genuine case for going for processor licenses?
Thanks & regards
zrbI suggest you consult a Microsoft representative about this. What you
have described *may* constitute hosting. The standard retail licence
has a "no commercial hosting" clause so you may need to purchase a
service provider licence.
I say "may" because I have no real idea what Microsoft classes as
commercial hosting. In any case an authorised rep should be able to
give you a definitive answer.
David Portas
SQL Server MVP
--|||Thanks. I am thoroughly confused now. :-) I'll contact a MS rep on
this.
David Portas wrote:
> I suggest you consult a Microsoft representative about this. What you
> have described *may* constitute hosting. The standard retail licence
> has a "no commercial hosting" clause so you may need to purchase a
> service provider licence.
> I say "may" because I have no real idea what Microsoft classes as
> commercial hosting. In any case an authorised rep should be able to
> give you a definitive answer.
> --
> David Portas
> SQL Server MVP
> --|||Also, if I have 4 processor licenses (we paid $80,000 for them), but
have 2 Dual Processor servers, does this entitle me to install SQL
server on the two servers? In the same vein, can I go for 4 single
processor servers? :-)|||Yes. A processor licence is just that - per processor. You just need a
licence for each processor in each server.
David Portas
SQL Server MVP
--

Another licensing question

Hi,
I have a question about SQL Server plus user CALs. What exactly is meant by
"user"? Is it a person that logs into my application or is it a user id
specified in connecion string?
Can I have one user CAL for X people, that are accessing SQL server through
identical userid in connection string?
for example:
users: John [logged into MyApplication at PC1], Mark[logged into
MyApplication at PC2], Bob[logged into MyApplication at PC3]
MyApplication is installed on everybody's PC (that is there are three copies
of MyApplication (PC1 and PC2 and PC3)).
Each installation of MyApplication is using this connection string to access
SQL Server:
ConnStr = "User ID=DEP_1;Password=abc;Persist Security Info=True;Connection
Timeout=15;Data Source=XXX;Initial Catalog=YYY"
Can I have one user CAL for user DEP_1 or do I need three user CALs for all
three users?
Thanks!
Hi
You needs CALS for all 3 users.
DEP_1 is just a security user and does not count in for CALS.
Regards
Mike
"Marek" wrote:

> Hi,
> I have a question about SQL Server plus user CALs. What exactly is meant by
> "user"? Is it a person that logs into my application or is it a user id
> specified in connecion string?
> Can I have one user CAL for X people, that are accessing SQL server through
> identical userid in connection string?
> for example:
> users: John [logged into MyApplication at PC1], Mark[logged into
> MyApplication at PC2], Bob[logged into MyApplication at PC3]
> MyApplication is installed on everybody's PC (that is there are three copies
> of MyApplication (PC1 and PC2 and PC3)).
> Each installation of MyApplication is using this connection string to access
> SQL Server:
> ConnStr = "User ID=DEP_1;Password=abc;Persist Security Info=True;Connection
> Timeout=15;Data Source=XXX;Initial Catalog=YYY"
> Can I have one user CAL for user DEP_1 or do I need three user CALs for all
> three users?
> Thanks!
|||Thanks Mike for quick answer!
1. Does this mean, that user CAL is nothing more than a paper?
2. Is user CAL somehow entered into SQL Server licensing properties? (I
found only licensing per-seat (device CAL) or per processor in my SQL Server
licensing setup)
3. How will Server know, if there are more users accessing server than are
defined in server licensing properties? (I will have only one CAL for DEP_1
and server will never know, who is currently logged into MyApplication)
4. Is user CAL always "mapped" (=issued) to real person?
5. What if MyApplication from previous post will be a windows service with
no UI interraction [no user logs into it. It will run on background on three
PCs and process some results]. Could it use only one user CAL then?
Bweh, I know, silly questions, but I'm lost in all these licensing options.
Thanks!
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> You needs CALS for all 3 users.
> DEP_1 is just a security user and does not count in for CALS.
> Regards
> Mike
> "Marek" wrote:
|||1. Yes, but if you don't follow the paper rules, you get very expensive
paperwork from Microsoft layers when they catch you.
2. On The server, go to Administrative Tools > Licensing
3. It does not know, but you are responsible. Point 2 can help you report on
it.
4. A CAL can be for a physical user or device and by the licensing terms, it
can only be transferred once to another user/device.
5. As long as the service does not get used by another program or service to
connect through it to the SQL Server (multiplexing)
http://www.microsoft.com/sql/howtobuy/default.asp
http://www.microsoft.com/sql/howtobuy/faq.asp
The CAL actually says a lot:
http://download.microsoft.com/downlo...A9/caleula.pdf
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Marek" <Marek@.discussions.microsoft.com> wrote in message
news:F588172A-B890-4D60-962B-8B211B7CD9C8@.microsoft.com...
> Thanks Mike for quick answer!
> 1. Does this mean, that user CAL is nothing more than a paper?
> 2. Is user CAL somehow entered into SQL Server licensing properties? (I
> found only licensing per-seat (device CAL) or per processor in my SQL
Server
> licensing setup)
> 3. How will Server know, if there are more users accessing server than are
> defined in server licensing properties? (I will have only one CAL for
DEP_1
> and server will never know, who is currently logged into MyApplication)
> 4. Is user CAL always "mapped" (=issued) to real person?
> 5. What if MyApplication from previous post will be a windows service with
> no UI interraction [no user logs into it. It will run on background on
three
> PCs and process some results]. Could it use only one user CAL then?
> Bweh, I know, silly questions, but I'm lost in all these licensing
options.[vbcol=seagreen]
> Thanks!
> "Mike Epprecht (SQL MVP)" wrote:
meant by[vbcol=seagreen]
id[vbcol=seagreen]
through[vbcol=seagreen]
copies[vbcol=seagreen]
access[vbcol=seagreen]
Info=True;Connection[vbcol=seagreen]
for all[vbcol=seagreen]
|||Prior to one of the service packs, not 3 or 3a, the number of CALs
restricted the number of concurrent user connections. You'd get errors
similar to those you see in MSDE when you exceed 8. Since the service pack,
however, the concurrent connections is limited only by a sp_configure
setting. However, there is nothing but paper that says you can't run
Developer Edition in production, but, then, that's why the paper was
written. The U.S. Constitution is nothing but paper, but if they catch you,
there's all sorts of pleasures waiting for you and your wallet.
Sincerely,
Anthony Thomas

"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uYtjOLCyEHA.3976@.TK2MSFTNGP09.phx.gbl...
> 1. Yes, but if you don't follow the paper rules, you get very expensive
> paperwork from Microsoft layers when they catch you.
> 2. On The server, go to Administrative Tools > Licensing
> 3. It does not know, but you are responsible. Point 2 can help you report
on
> it.
> 4. A CAL can be for a physical user or device and by the licensing terms,
it
> can only be transferred once to another user/device.
> 5. As long as the service does not get used by another program or service
to
> connect through it to the SQL Server (multiplexing)
> http://www.microsoft.com/sql/howtobuy/default.asp
> http://www.microsoft.com/sql/howtobuy/faq.asp
> The CAL actually says a lot:
>
http://download.microsoft.com/downlo...A9/caleula.pdf[vbcol=seagreen]
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Marek" <Marek@.discussions.microsoft.com> wrote in message
> news:F588172A-B890-4D60-962B-8B211B7CD9C8@.microsoft.com...
> Server
are[vbcol=seagreen]
> DEP_1
with[vbcol=seagreen]
> three
> options.
> meant by
> id
> through
three[vbcol=seagreen]
> copies
to
> access
> Info=True;Connection
> for all
>

Another licensing question

Hi,
I have a question about SQL Server plus user CALs. What exactly is meant by
"user"? Is it a person that logs into my application or is it a user id
specified in connecion string?
Can I have one user CAL for X people, that are accessing SQL server through
identical userid in connection string?
for example:
users: John [logged into MyApplication at PC1], Mark[logged into
MyApplication at PC2], Bob[logged into MyApplication at PC3]
MyApplication is installed on everybody's PC (that is there are three copies
of MyApplication (PC1 and PC2 and PC3)).
Each installation of MyApplication is using this connection string to access
SQL Server:
ConnStr = "User ID=DEP_1;Password=abc;Persist Security Info=True;Connection
Timeout=15;Data Source=XXX;Initial Catalog=YYY"
Can I have one user CAL for user DEP_1 or do I need three user CALs for all
three users?
Thanks!Hi
You needs CALS for all 3 users.
DEP_1 is just a security user and does not count in for CALS.
Regards
Mike
"Marek" wrote:

> Hi,
> I have a question about SQL Server plus user CALs. What exactly is meant b
y
> "user"? Is it a person that logs into my application or is it a user id
> specified in connecion string?
> Can I have one user CAL for X people, that are accessing SQL server throug
h
> identical userid in connection string?
> for example:
> users: John [logged into MyApplication at PC1], Mark[logged into
> MyApplication at PC2], Bob[logged into MyApplication at PC3]
> MyApplication is installed on everybody's PC (that is there are three copi
es
> of MyApplication (PC1 and PC2 and PC3)).
> Each installation of MyApplication is using this connection string to acce
ss
> SQL Server:
> ConnStr = "User ID=DEP_1;Password=abc;Persist Security Info=True;Connectio
n
> Timeout=15;Data Source=XXX;Initial Catalog=YYY"
> Can I have one user CAL for user DEP_1 or do I need three user CALs for al
l
> three users?
> Thanks!|||Thanks Mike for quick answer!
1. Does this mean, that user CAL is nothing more than a paper?
2. Is user CAL somehow entered into SQL Server licensing properties? (I
found only licensing per-seat (device CAL) or per processor in my SQL Server
licensing setup)
3. How will Server know, if there are more users accessing server than are
defined in server licensing properties? (I will have only one CAL for DEP_1
and server will never know, who is currently logged into MyApplication)
4. Is user CAL always "mapped" (=issued) to real person?
5. What if MyApplication from previous post will be a windows service with
no UI interraction [no user logs into it. It will run on background on t
hree
PCs and process some results]. Could it use only one user CAL then?
Bweh, I know, silly questions, but I'm lost in all these licensing options.
Thanks!
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> You needs CALS for all 3 users.
> DEP_1 is just a security user and does not count in for CALS.
> Regards
> Mike
> "Marek" wrote:
>|||1. Yes, but if you don't follow the paper rules, you get very expensive
paperwork from Microsoft layers when they catch you.
2. On The server, go to Administrative Tools > Licensing
3. It does not know, but you are responsible. Point 2 can help you report on
it.
4. A CAL can be for a physical user or device and by the licensing terms, it
can only be transferred once to another user/device.
5. As long as the service does not get used by another program or service to
connect through it to the SQL Server (multiplexing)
http://www.microsoft.com/sql/howtobuy/default.asp
http://www.microsoft.com/sql/howtobuy/faq.asp
The CAL actually says a lot:
http://download.microsoft.com/downl.../>
caleula.pdf
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Marek" <Marek@.discussions.microsoft.com> wrote in message
news:F588172A-B890-4D60-962B-8B211B7CD9C8@.microsoft.com...
> Thanks Mike for quick answer!
> 1. Does this mean, that user CAL is nothing more than a paper?
> 2. Is user CAL somehow entered into SQL Server licensing properties? (I
> found only licensing per-seat (device CAL) or per processor in my SQL
Server
> licensing setup)
> 3. How will Server know, if there are more users accessing server than are
> defined in server licensing properties? (I will have only one CAL for
DEP_1
> and server will never know, who is currently logged into MyApplication)
> 4. Is user CAL always "mapped" (=issued) to real person?
> 5. What if MyApplication from previous post will be a windows service with
> no UI interraction [no user logs into it. It will run on background on
three
> PCs and process some results]. Could it use only one user CAL then?
> Bweh, I know, silly questions, but I'm lost in all these licensing
options.[vbcol=seagreen]
> Thanks!
> "Mike Epprecht (SQL MVP)" wrote:
>
meant by[vbcol=seagreen]
id[vbcol=seagreen]
through[vbcol=seagreen]
copies[vbcol=seagreen]
access[vbcol=seagreen]
Info=True;Connection[vbcol=seagreen]
for all[vbcol=seagreen]|||Prior to one of the service packs, not 3 or 3a, the number of CALs
restricted the number of concurrent user connections. You'd get errors
similar to those you see in MSDE when you exceed 8. Since the service pack,
however, the concurrent connections is limited only by a sp_configure
setting. However, there is nothing but paper that says you can't run
Developer Edition in production, but, then, that's why the paper was
written. The U.S. Constitution is nothing but paper, but if they catch you,
there's all sorts of pleasures waiting for you and your wallet.
Sincerely,
Anthony Thomas
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uYtjOLCyEHA.3976@.TK2MSFTNGP09.phx.gbl...
> 1. Yes, but if you don't follow the paper rules, you get very expensive
> paperwork from Microsoft layers when they catch you.
> 2. On The server, go to Administrative Tools > Licensing
> 3. It does not know, but you are responsible. Point 2 can help you report
on
> it.
> 4. A CAL can be for a physical user or device and by the licensing terms,
it
> can only be transferred once to another user/device.
> 5. As long as the service does not get used by another program or service
to
> connect through it to the SQL Server (multiplexing)
> http://www.microsoft.com/sql/howtobuy/default.asp
> http://www.microsoft.com/sql/howtobuy/faq.asp
> The CAL actually says a lot:
>

> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: [url]http://www.microsoft.com/mvp" target="_blank">http://download.microsoft.com/downl...crosoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Marek" <Marek@.discussions.microsoft.com> wrote in message
> news:F588172A-B890-4D60-962B-8B211B7CD9C8@.microsoft.com...
> Server
are[vbcol=seagreen]
> DEP_1
with[vbcol=seagreen]
> three
> options.
> meant by
> id
> through
three[vbcol=seagreen]
> copies
to[vbcol=seagreen]
> access
> Info=True;Connection
> for all
>

Another licensing question

Hi,
I have a question about SQL Server plus user CALs. What exactly is meant by
"user"? Is it a person that logs into my application or is it a user id
specified in connecion string?
Can I have one user CAL for X people, that are accessing SQL server through
identical userid in connection string?
for example:
users: John [logged into MyApplication at PC1], Mark[logged into
MyApplication at PC2], Bob[logged into MyApplication at PC3]
MyApplication is installed on everybody's PC (that is there are three copies
of MyApplication (PC1 and PC2 and PC3)).
Each installation of MyApplication is using this connection string to access
SQL Server:
ConnStr = "User ID=DEP_1;Password=abc;Persist Security Info=True;Connection
Timeout=15;Data Source=XXX;Initial Catalog=YYY"
Can I have one user CAL for user DEP_1 or do I need three user CALs for all
three users?
Thanks!Hi
You needs CALS for all 3 users.
DEP_1 is just a security user and does not count in for CALS.
Regards
Mike
"Marek" wrote:
> Hi,
> I have a question about SQL Server plus user CALs. What exactly is meant by
> "user"? Is it a person that logs into my application or is it a user id
> specified in connecion string?
> Can I have one user CAL for X people, that are accessing SQL server through
> identical userid in connection string?
> for example:
> users: John [logged into MyApplication at PC1], Mark[logged into
> MyApplication at PC2], Bob[logged into MyApplication at PC3]
> MyApplication is installed on everybody's PC (that is there are three copies
> of MyApplication (PC1 and PC2 and PC3)).
> Each installation of MyApplication is using this connection string to access
> SQL Server:
> ConnStr = "User ID=DEP_1;Password=abc;Persist Security Info=True;Connection
> Timeout=15;Data Source=XXX;Initial Catalog=YYY"
> Can I have one user CAL for user DEP_1 or do I need three user CALs for all
> three users?
> Thanks!|||Thanks Mike for quick answer!
1. Does this mean, that user CAL is nothing more than a paper?
2. Is user CAL somehow entered into SQL Server licensing properties? (I
found only licensing per-seat (device CAL) or per processor in my SQL Server
licensing setup)
3. How will Server know, if there are more users accessing server than are
defined in server licensing properties? (I will have only one CAL for DEP_1
and server will never know, who is currently logged into MyApplication)
4. Is user CAL always "mapped" (=issued) to real person?
5. What if MyApplication from previous post will be a windows service with
no UI interraction [no user logs into it. It will run on background on three
PCs and process some results]. Could it use only one user CAL then?
Bweh, I know, silly questions, but I'm lost in all these licensing options.
Thanks!
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> You needs CALS for all 3 users.
> DEP_1 is just a security user and does not count in for CALS.
> Regards
> Mike
> "Marek" wrote:
> > Hi,
> >
> > I have a question about SQL Server plus user CALs. What exactly is meant by
> > "user"? Is it a person that logs into my application or is it a user id
> > specified in connecion string?
> >
> > Can I have one user CAL for X people, that are accessing SQL server through
> > identical userid in connection string?
> >
> > for example:
> >
> > users: John [logged into MyApplication at PC1], Mark[logged into
> > MyApplication at PC2], Bob[logged into MyApplication at PC3]
> >
> > MyApplication is installed on everybody's PC (that is there are three copies
> > of MyApplication (PC1 and PC2 and PC3)).
> >
> > Each installation of MyApplication is using this connection string to access
> > SQL Server:
> >
> > ConnStr = "User ID=DEP_1;Password=abc;Persist Security Info=True;Connection
> > Timeout=15;Data Source=XXX;Initial Catalog=YYY"
> >
> > Can I have one user CAL for user DEP_1 or do I need three user CALs for all
> > three users?
> >
> > Thanks!|||1. Yes, but if you don't follow the paper rules, you get very expensive
paperwork from Microsoft layers when they catch you.
2. On The server, go to Administrative Tools > Licensing
3. It does not know, but you are responsible. Point 2 can help you report on
it.
4. A CAL can be for a physical user or device and by the licensing terms, it
can only be transferred once to another user/device.
5. As long as the service does not get used by another program or service to
connect through it to the SQL Server (multiplexing)
http://www.microsoft.com/sql/howtobuy/default.asp
http://www.microsoft.com/sql/howtobuy/faq.asp
The CAL actually says a lot:
http://download.microsoft.com/download/5/E/7/5E7B79D4-90B8-4A3D-B964-8A72F49A21A9/caleula.pdf
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Marek" <Marek@.discussions.microsoft.com> wrote in message
news:F588172A-B890-4D60-962B-8B211B7CD9C8@.microsoft.com...
> Thanks Mike for quick answer!
> 1. Does this mean, that user CAL is nothing more than a paper?
> 2. Is user CAL somehow entered into SQL Server licensing properties? (I
> found only licensing per-seat (device CAL) or per processor in my SQL
Server
> licensing setup)
> 3. How will Server know, if there are more users accessing server than are
> defined in server licensing properties? (I will have only one CAL for
DEP_1
> and server will never know, who is currently logged into MyApplication)
> 4. Is user CAL always "mapped" (=issued) to real person?
> 5. What if MyApplication from previous post will be a windows service with
> no UI interraction [no user logs into it. It will run on background on
three
> PCs and process some results]. Could it use only one user CAL then?
> Bweh, I know, silly questions, but I'm lost in all these licensing
options.
> Thanks!
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > You needs CALS for all 3 users.
> > DEP_1 is just a security user and does not count in for CALS.
> >
> > Regards
> > Mike
> >
> > "Marek" wrote:
> >
> > > Hi,
> > >
> > > I have a question about SQL Server plus user CALs. What exactly is
meant by
> > > "user"? Is it a person that logs into my application or is it a user
id
> > > specified in connecion string?
> > >
> > > Can I have one user CAL for X people, that are accessing SQL server
through
> > > identical userid in connection string?
> > >
> > > for example:
> > >
> > > users: John [logged into MyApplication at PC1], Mark[logged into
> > > MyApplication at PC2], Bob[logged into MyApplication at PC3]
> > >
> > > MyApplication is installed on everybody's PC (that is there are three
copies
> > > of MyApplication (PC1 and PC2 and PC3)).
> > >
> > > Each installation of MyApplication is using this connection string to
access
> > > SQL Server:
> > >
> > > ConnStr = "User ID=DEP_1;Password=abc;Persist Security
Info=True;Connection
> > > Timeout=15;Data Source=XXX;Initial Catalog=YYY"
> > >
> > > Can I have one user CAL for user DEP_1 or do I need three user CALs
for all
> > > three users?
> > >
> > > Thanks!|||Prior to one of the service packs, not 3 or 3a, the number of CALs
restricted the number of concurrent user connections. You'd get errors
similar to those you see in MSDE when you exceed 8. Since the service pack,
however, the concurrent connections is limited only by a sp_configure
setting. However, there is nothing but paper that says you can't run
Developer Edition in production, but, then, that's why the paper was
written. The U.S. Constitution is nothing but paper, but if they catch you,
there's all sorts of pleasures waiting for you and your wallet.
Sincerely,
Anthony Thomas
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uYtjOLCyEHA.3976@.TK2MSFTNGP09.phx.gbl...
> 1. Yes, but if you don't follow the paper rules, you get very expensive
> paperwork from Microsoft layers when they catch you.
> 2. On The server, go to Administrative Tools > Licensing
> 3. It does not know, but you are responsible. Point 2 can help you report
on
> it.
> 4. A CAL can be for a physical user or device and by the licensing terms,
it
> can only be transferred once to another user/device.
> 5. As long as the service does not get used by another program or service
to
> connect through it to the SQL Server (multiplexing)
> http://www.microsoft.com/sql/howtobuy/default.asp
> http://www.microsoft.com/sql/howtobuy/faq.asp
> The CAL actually says a lot:
>
http://download.microsoft.com/download/5/E/7/5E7B79D4-90B8-4A3D-B964-8A72F49A21A9/caleula.pdf
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Marek" <Marek@.discussions.microsoft.com> wrote in message
> news:F588172A-B890-4D60-962B-8B211B7CD9C8@.microsoft.com...
> > Thanks Mike for quick answer!
> >
> > 1. Does this mean, that user CAL is nothing more than a paper?
> >
> > 2. Is user CAL somehow entered into SQL Server licensing properties? (I
> > found only licensing per-seat (device CAL) or per processor in my SQL
> Server
> > licensing setup)
> >
> > 3. How will Server know, if there are more users accessing server than
are
> > defined in server licensing properties? (I will have only one CAL for
> DEP_1
> > and server will never know, who is currently logged into MyApplication)
> >
> > 4. Is user CAL always "mapped" (=issued) to real person?
> >
> > 5. What if MyApplication from previous post will be a windows service
with
> > no UI interraction [no user logs into it. It will run on background on
> three
> > PCs and process some results]. Could it use only one user CAL then?
> >
> > Bweh, I know, silly questions, but I'm lost in all these licensing
> options.
> >
> > Thanks!
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> > > Hi
> > >
> > > You needs CALS for all 3 users.
> > > DEP_1 is just a security user and does not count in for CALS.
> > >
> > > Regards
> > > Mike
> > >
> > > "Marek" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a question about SQL Server plus user CALs. What exactly is
> meant by
> > > > "user"? Is it a person that logs into my application or is it a user
> id
> > > > specified in connecion string?
> > > >
> > > > Can I have one user CAL for X people, that are accessing SQL server
> through
> > > > identical userid in connection string?
> > > >
> > > > for example:
> > > >
> > > > users: John [logged into MyApplication at PC1], Mark[logged into
> > > > MyApplication at PC2], Bob[logged into MyApplication at PC3]
> > > >
> > > > MyApplication is installed on everybody's PC (that is there are
three
> copies
> > > > of MyApplication (PC1 and PC2 and PC3)).
> > > >
> > > > Each installation of MyApplication is using this connection string
to
> access
> > > > SQL Server:
> > > >
> > > > ConnStr = "User ID=DEP_1;Password=abc;Persist Security
> Info=True;Connection
> > > > Timeout=15;Data Source=XXX;Initial Catalog=YYY"
> > > >
> > > > Can I have one user CAL for user DEP_1 or do I need three user CALs
> for all
> > > > three users?
> > > >
> > > > Thanks!
>

Another database design issue help!

Hello all,
I am developing a web application and im in the middle of database design and facing a design issue. Here it is: A table for products a table for SizeChart (Small, Medium, Large) ColorChart (White, Ash, Yellow, Pink).
Each product is available in combination of different colors and size. Then product has images which are stored based on the selection from ColorChart table. I have created a table where many to many relation between product to color and size are related with primary key identity field for junction table too. I also have created a table tblProdImages with ProductID, ColorID, ImagePath fields
Here is the structure
Product ColorChart SizeChart
--- ---- ----
ProductID ColorID SizeID
Name ColorIcon Sybol

ProductColorSizeChart (junstcion table) ProductImages
------------ ------
ProductColorSizeID (Indentity) ProductImageID (Identity)
ProductID - FK ProductID - FK
ColorID - FK ColorID - FK
SizeID - FK ImagePathThumbnail
InStock ImagePathLarge
Offline

I have write DAL for product and managing junction table relation as collection in my product b/e object.
Here is my question
1- How effective this whole db schema is and how manageable is the relations, Suggest if any better solutions is.
2- Im facing updating problem in ProductDAL for updating collection items in database.
3- How can i manage and implement lazy loading in this scenario in my own written Data Access Layer.

Please clarify 'DAL' and 'b/e'|||1) The schema looks pretty good. The relationships look fairly straight forward and easy to maintain.
2 ) It would help if I had a better idea of what your Object Model & DAL look like.
3) It doesnt look like you have a situation where objects have amassive amount of data associated with them. Im not sureLazyLoading would be worth the trouble. Caching the objects andreturning references might be more useful for you.
|||Thanks Jeffrey for a prompt reply. First of all as far as b/e (business entity objects) are concerned they are blue print parent tables. As for relation btw product, color, size i have created a collection where each item is a structure having two fields for b/e of ColorInfo and SizeInfo (mapper of Color and Size tables). There is a possibility that a product may need to be added where this relation may not be applied as that product has no dependency of color and size so the collection will be empty and in DAL (Data Access Layer) class of product i have this check to determin the count of collection. If empty then Save method for relational entity objects will not be called.
I have implemented DAL code for saving relational object (Product,Color,Size) in product. Is it a right place?|||

asp.dude wrote:

Thanks Jeffrey for a prompt reply. First of allas far as b/e (business entity objects) are concerned theyare blue print parent tables. As for relation btw product, color, sizei have created a collection where each item is a structure havingtwo fields for b/e of ColorInfo and SizeInfo (mapper of Color and Sizetables). There is a possibility that a product may need to be addedwhere this relation may not be applied as that product has nodependency of color and size so the collection will be empty and in DAL(Data Access Layer) class of product i have this check to determin thecount of collection. If empty then Save method for relational entityobjects will not be called.
I have implemented DAL code for saving relational object (Product,Color,Size) in product. Is it a right place?

Thatsounds correct to me. I really couldnt tell you much more without seeing code.

Anonymous Logon error since SP4

Since SP4 my application and SQL log are filling with "Login failed for user
"NT Authority\Anonymous Logon". Where can I find more information
concerning this?
I've seen one or two references to this error showing up with SQL
replication but this is a single server install.
-trevor
Trevor,
Check that SQL Server is running under a domain account - this error
commonly occurs when running as LocalSystem and you try to access network
resources.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Trevor Miller" wrote:

> Since SP4 my application and SQL log are filling with "Login failed for user
> "NT Authority\Anonymous Logon". Where can I find more information
> concerning this?
> I've seen one or two references to this error showing up with SQL
> replication but this is a single server install.
> -trevor
>
>
|||SQL server indeed runs as LocalSystem but it's always been configured this
way, is the error msg a result of SP4 or a change in behavior? What network
resources would SQL be trying to access? I've tried putting the computer
account into domain admins but the error persists.
Additional advise?
-trevor
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:30AAB157-EB94-4680-B088-44689F364AF9@.microsoft.com...[vbcol=seagreen]
> Trevor,
> Check that SQL Server is running under a domain account - this error
> commonly occurs when running as LocalSystem and you try to access network
> resources.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> "Trevor Miller" wrote:

Anonymous Logon error since SP4

Since SP4 my application and SQL log are filling with "Login failed for user
"NT Authority\Anonymous Logon". Where can I find more information
concerning this?
I've seen one or two references to this error showing up with SQL
replication but this is a single server install.
-trevorTrevor,
Check that SQL Server is running under a domain account - this error
commonly occurs when running as LocalSystem and you try to access network
resources.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Trevor Miller" wrote:

> Since SP4 my application and SQL log are filling with "Login failed for us
er
> "NT Authority\Anonymous Logon". Where can I find more information
> concerning this?
> I've seen one or two references to this error showing up with SQL
> replication but this is a single server install.
> -trevor
>
>|||SQL server indeed runs as LocalSystem but it's always been configured this
way, is the error msg a result of SP4 or a change in behavior? What network
resources would SQL be trying to access? I've tried putting the computer
account into domain admins but the error persists.
Additional advise?
-trevor
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:30AAB157-EB94-4680-B088-44689F364AF9@.microsoft.com...[vbcol=seagreen]
> Trevor,
> Check that SQL Server is running under a domain account - this error
> commonly occurs when running as LocalSystem and you try to access network
> resources.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> "Trevor Miller" wrote:
>

Anonymous Logon error since SP4

Since SP4 my application and SQL log are filling with "Login failed for user
"NT Authority\Anonymous Logon". Where can I find more information
concerning this?
I've seen one or two references to this error showing up with SQL
replication but this is a single server install.
-trevorTrevor,
Check that SQL Server is running under a domain account - this error
commonly occurs when running as LocalSystem and you try to access network
resources.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Trevor Miller" wrote:
> Since SP4 my application and SQL log are filling with "Login failed for user
> "NT Authority\Anonymous Logon". Where can I find more information
> concerning this?
> I've seen one or two references to this error showing up with SQL
> replication but this is a single server install.
> -trevor
>
>|||SQL server indeed runs as LocalSystem but it's always been configured this
way, is the error msg a result of SP4 or a change in behavior? What network
resources would SQL be trying to access? I've tried putting the computer
account into domain admins but the error persists.
Additional advise?
-trevor
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:30AAB157-EB94-4680-B088-44689F364AF9@.microsoft.com...
> Trevor,
> Check that SQL Server is running under a domain account - this error
> commonly occurs when running as LocalSystem and you try to access network
> resources.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> "Trevor Miller" wrote:
>> Since SP4 my application and SQL log are filling with "Login failed for
>> user
>> "NT Authority\Anonymous Logon". Where can I find more information
>> concerning this?
>> I've seen one or two references to this error showing up with SQL
>> replication but this is a single server install.
>> -trevor
>>

Anonymous Access from Pivot Table to OLAP 2005

My asp-net web application connect to Analysis Services 2005 through Pivot Table client.

To use anonymous access with AS2000 i used to add this property to the connection string of the pivot: "SSPI=anonymous".

Which property give me the same thing with AS2005?

Thanks!

Hi,

The SSPI connection property is still there, so it should work the same. Are you getting an error?

David Botzenhart

|||

Hey David - good to hear from you !

Yes, you could still use SSPI=anonymous, but I really recommend in AS2005 to use standard OLEDB property for this purpose, i.e.

Protection Level=none

It has slightly better (more standard) behavior.

Mosha (http://www.mosha.com/msolap )

|||

Thanks, it's working.

But, if i set "Impersonation Level" property to Anonymous? Is this the same of SSPI=anonymous?

ANN: SQL Monitor Version 2 has just been released

SQL Monitor version 2.0 has just been released.

http://dbmonitor.tripod.com
SQL Monitor is an application that displays performance statistics for
sessions running on SQL Server databases. It has a very small
footprint on the server yet displays all the information required for
a DBA or database developer to see the impact individual sessions have
on a database server.

Features include:

Email alerts for performance issues or unexpected disconnects on
database servers
Logging of performance figures and individual session connects/
disconnects
Multi-server graph display showing all connected servers on the one
screen
Process information broken down into database/ user or session lists
Locking panel to display locking information
Simple one click server login
Adjustable server refresh from 1 second to two minutes
No time limit on trial (Restricted to max of 2 servers concurrently
connected)
Ability to sort the session lists based on activity between sessions

NOTE: This is a total rewrite of the previous version of DBMonitor.
All the excellent features of the previous application have been
incorporated and expanded into the new application.
The link is down.
Other links?|||On May 29, 7:13 pm, Trevize <jbg...@.gmail.comwrote:

Quote:

Originally Posted by

The link is down.
Other links?

Ops. lol.

The download link is now working.

Analysis Services web application authentication problem

In the following sceanrio
Web application uses Analysis Services to present data.
Web App (IIS) is installed on a different server to the SQL Server instance.
Web app uses Windows Integrated authentication.
It appears that the authentication is not correctly passed to AS and that
the app then fails.
This does not seem to be the case where AS and IIS are installed on the same
machine.
Does this sound familiar and if so are you aware of any fixes/workaroundsBTW: If the web app uses basic authentication then everything works......
"Andrew Wiles" <ajw@.nospam.please> wrote in message
news:edNjY9rhEHA.1964@.tk2msftngp13.phx.gbl...
> In the following sceanrio
>
> Web application uses Analysis Services to present data.
> Web App (IIS) is installed on a different server to the SQL Server
instance.
> Web app uses Windows Integrated authentication.
>
> It appears that the authentication is not correctly passed to AS and that
> the app then fails.
>
> This does not seem to be the case where AS and IIS are installed on the
same
> machine.
>
> Does this sound familiar and if so are you aware of any fixes/workarounds
>

Analysis Services web application authentication problem

In the following sceanrio
Web application uses Analysis Services to present data.
Web App (IIS) is installed on a different server to the SQL Server instance.
Web app uses Windows Integrated authentication.
It appears that the authentication is not correctly passed to AS and that
the app then fails.
This does not seem to be the case where AS and IIS are installed on the same
machine.
Does this sound familiar and if so are you aware of any fixes/workarounds
BTW: If the web app uses basic authentication then everything works......
"Andrew Wiles" <ajw@.nospam.please> wrote in message
news:edNjY9rhEHA.1964@.tk2msftngp13.phx.gbl...
> In the following sceanrio
>
> Web application uses Analysis Services to present data.
> Web App (IIS) is installed on a different server to the SQL Server
instance.
> Web app uses Windows Integrated authentication.
>
> It appears that the authentication is not correctly passed to AS and that
> the app then fails.
>
> This does not seem to be the case where AS and IIS are installed on the
same
> machine.
>
> Does this sound familiar and if so are you aware of any fixes/workarounds
>
|||Hi Andrew,
I realise this post is getting quite old now, but We are experiencing the same problem here,
Did you manage to get it resolved?

Quote:

Originally posted by Andrew Wiles
In the following sceanrio
Web application uses Analysis Services to present data.
Web App (IIS) is installed on a different server to the SQL Server instance.
Web app uses Windows Integrated authentication.
It appears that the authentication is not correctly passed to AS and that
the app then fails.
This does not seem to be the case where AS and IIS are installed on the same
machine.
Does this sound familiar and if so are you aware of any fixes/workarounds

Analysis Services connexion problem using AMO via ASP.NET 2

Hi,

I've got a strange problem.
I'm developing an ASP.NET application connecting to an Analysis
Services 2005 database.

On my developpement PC I can run the web site virtually and my
connexion is ok. I can see all the databases running under my server.
BUT, when I deploy the site on the server, even if the connexion is a
success, I can't see any of my databases.

Concretly, the code looks like :

Dim oServeur As Microsoft.AnalysisServices.Server
oServeur = New Microsoft.AnalysisServices.Server
oServeur.Connect("ServerName") ' I do not type "(local)"

This part of the code is ok both on developpement and server side.

But If I add this :

My.Response.Write(oServeur.DataBases.Count)

The response is "2" on application developpement side and
"0" on the server side ...

Can someone help me please ?

This very much looks like the permissions problem. Check the security under which your ASP application is running. Check the security on the virtual directory for your application.

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

Analysis Services connexion problem using AMO via ASP.NET 2

Hi,

I've got a strange problem.
I'm developing an ASP.NET application connecting to an Analysis
Services 2005 database.

On my developpement PC I can run the web site virtually and my
connexion is ok. I can see all the databases running under my server.
BUT, when I deploy the site on the server, even if the connexion is a
success, I can't see any of my databases.

Concretly, the code looks like :

Dim oServeur As Microsoft.AnalysisServices.Server
oServeur = New Microsoft.AnalysisServices.Server
oServeur.Connect("ServerName") ' I do not type "(local)"

This part of the code is ok both on developpement and server side.

But If I add this :

My.Response.Write(oServeur.DataBases.Count)

The response is "2" on application developpement side and
"0" on the server side ...

Can someone help me please ?

This very much looks like the permissions problem. Check the security under which your ASP application is running. Check the security on the virtual directory for your application.

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

Analysis Services connection using AMO

Hi,

I have AS2005 running on a development machine, and i am trying to connect to it via my application running on another machine.

The code is using the Analysis Mangement Object (AMO) API.

Currently, my application code only tries to connect and disconnect from the server.

Server svr = new Server();

svr.Connect(connectionString); < Exception thrown here

svr.Disconnect();

The connectionString simply contains the remote server's name. I have also tried different connection strings but it has not helped.

I have also enabled remote connections on AS, and opened the default port number on the server's firewall.

Every time it tries to connect the exception thrown is:

"Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host."

Can anybody help me with this problem?

I'm not sure if this will help, but I was getting a similar error with

the BI Development Studio. In that case it was a security

issue. Basically, becasue the machine was on a different domain,

I had to create a user on that machine with the same username /

password as the one that I log into my machine with. Have you

tried connecting in any other way?|||

Is the "other machine" an AS2K5 instance?

_-_-_ Dave

Analysis Services 2000 with SQL Server 2005

We have an application that uses Analysis Services 2000 Cubes with SQL Server 2005. We have a new client who has SQL Server 2005 on their server. Since our application requires Analysis Services 2000, I would like to know if there is any way they can get Analysis Services 2000 without buying SQL server 2000.

Hello! AS2000 and SQL Server 2000 are bundled. You run AS2000 on your SQL Server 2000 licence.

Answere: No!

HTH

Thomas Ivarsson

|||

Thank you, that helps. I was thinking that maybe they have some components for Analysis Services 2000 like they have for DTS 2000 in SQL Server 2005. The client was complaining about having to buy both SQL Server 2005 and SQL Server 2000. I will tell them that they have to do that.

Thanks,

M.Kundeti