Case Statement & View

I am executing a case statement list below,

USE Northwind

SELECT
MONTH(OrderDate) AS OrderMonth,
SUM(CASE YEAR(OrderDate)
WHEN 1996 THEN 1
ELSE 0
END) AS c1996,
SUM(CASE YEAR(OrderDate)
WHEN 1997 THEN 1
ELSE 0
END) AS c1997,
SUM(CASE YEAR(OrderDate)
WHEN 1998 THEN 1
ELSE 0
END) AS c1998
FROM Orders
GROUP BY MONTH(OrderDate)
ORDER BY MONTH(OrderDate)

According to BOL I should be able to save this query as a view.
However when I try to save the query as a view I get a error message
stating

"View definition includes no output columns or includes no items in
the FROM clause"

According to what I have read although the case statement is not
supported via the enterprise query pane, the query should still run
and be saved. In my case however I cannot seem to save it no matter
what I try.

Can anyone shed any light on the matter?

Thanks in advance

BryanWhat version of SQL Server? I get the following error when I try to save
the query as a view using SQL 2000 SP3a:

The ORDER BY clause is invalid in views, inline functions,
derived tables, and subqueries, unless TOP is also specified.

It creates fine when I remove the ORDER BY. I then retrieved the results
using the following query

SELECT *
FROM MyView
ORDER BY OrderMonth

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Bryan" <bryanmcguire@.btinternet.com> wrote in message
news:8134a2a4.0409110831.46c0af89@.posting.google.c om...
>I am executing a case statement list below,
> USE Northwind
> SELECT
> MONTH(OrderDate) AS OrderMonth,
> SUM(CASE YEAR(OrderDate)
> WHEN 1996 THEN 1
> ELSE 0
> END) AS c1996,
> SUM(CASE YEAR(OrderDate)
> WHEN 1997 THEN 1
> ELSE 0
> END) AS c1997,
> SUM(CASE YEAR(OrderDate)
> WHEN 1998 THEN 1
> ELSE 0
> END) AS c1998
> FROM Orders
> GROUP BY MONTH(OrderDate)
> ORDER BY MONTH(OrderDate)
>
> According to BOL I should be able to save this query as a view.
> However when I try to save the query as a view I get a error message
> stating
> "View definition includes no output columns or includes no items in
> the FROM clause"
> According to what I have read although the case statement is not
> supported via the enterprise query pane, the query should still run
> and be saved. In my case however I cannot seem to save it no matter
> what I try.
> Can anyone shed any light on the matter?
> Thanks in advance
> Bryan|||I'm using SQL Server 2000 sp3

thanks

Bryan

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message news:<G8G0d.18454$6S7.12715@.newssvr24.news.prodigy.com>...
> What version of SQL Server? I get the following error when I try to save
> the query as a view using SQL 2000 SP3a:
> The ORDER BY clause is invalid in views, inline functions,
> derived tables, and subqueries, unless TOP is also specified.
> It creates fine when I remove the ORDER BY. I then retrieved the results
> using the following query
> SELECT *
> FROM MyView
> ORDER BY OrderMonth
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bryan" <bryanmcguire@.btinternet.com> wrote in message
> news:8134a2a4.0409110831.46c0af89@.posting.google.c om...
> >I am executing a case statement list below,
> > USE Northwind
> > SELECT
> > MONTH(OrderDate) AS OrderMonth,
> > SUM(CASE YEAR(OrderDate)
> > WHEN 1996 THEN 1
> > ELSE 0
> > END) AS c1996,
> > SUM(CASE YEAR(OrderDate)
> > WHEN 1997 THEN 1
> > ELSE 0
> > END) AS c1997,
> > SUM(CASE YEAR(OrderDate)
> > WHEN 1998 THEN 1
> > ELSE 0
> > END) AS c1998
> > FROM Orders
> > GROUP BY MONTH(OrderDate)
> > ORDER BY MONTH(OrderDate)
> > According to BOL I should be able to save this query as a view.
> > However when I try to save the query as a view I get a error message
> > stating
> > "View definition includes no output columns or includes no items in
> > the FROM clause"
> > According to what I have read although the case statement is not
> > supported via the enterprise query pane, the query should still run
> > and be saved. In my case however I cannot seem to save it no matter
> > what I try.
> > Can anyone shed any light on the matter?
> > Thanks in advance
> > Bryan|||Dan,

Hi sorry, I just checked I was only running sp 1 not 3a as I posted
earlier. I have now changed to sp3a and ran the query again. This time
I manged to save the view by removing the "Order By"...

Thanks again

Bryan

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message news:<G8G0d.18454$6S7.12715@.newssvr24.news.prodigy.com>...
> What version of SQL Server? I get the following error when I try to save
> the query as a view using SQL 2000 SP3a:
> The ORDER BY clause is invalid in views, inline functions,
> derived tables, and subqueries, unless TOP is also specified.
> It creates fine when I remove the ORDER BY. I then retrieved the results
> using the following query
> SELECT *
> FROM MyView
> ORDER BY OrderMonth
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bryan" <bryanmcguire@.btinternet.com> wrote in message
> news:8134a2a4.0409110831.46c0af89@.posting.google.c om...
> >I am executing a case statement list below,
> > USE Northwind
> > SELECT
> > MONTH(OrderDate) AS OrderMonth,
> > SUM(CASE YEAR(OrderDate)
> > WHEN 1996 THEN 1
> > ELSE 0
> > END) AS c1996,
> > SUM(CASE YEAR(OrderDate)
> > WHEN 1997 THEN 1
> > ELSE 0
> > END) AS c1997,
> > SUM(CASE YEAR(OrderDate)
> > WHEN 1998 THEN 1
> > ELSE 0
> > END) AS c1998
> > FROM Orders
> > GROUP BY MONTH(OrderDate)
> > ORDER BY MONTH(OrderDate)
> > According to BOL I should be able to save this query as a view.
> > However when I try to save the query as a view I get a error message
> > stating
> > "View definition includes no output columns or includes no items in
> > the FROM clause"
> > According to what I have read although the case statement is not
> > supported via the enterprise query pane, the query should still run
> > and be saved. In my case however I cannot seem to save it no matter
> > what I try.
> > Can anyone shed any light on the matter?
> > Thanks in advance
> > Bryan

CASE statement

Hello,
I created a view in SQL Server Express 2005 that contained CASE END
statements.
I use this view in a .NET 2005 project, which works fine on my machine.
When I deploy my project to a machine that is running SQL Server 200, I get
the following error:
The Query Designer does not support the CASE SQL contruct.
Any way around this'
TIA,
Amber"amber" <amber@.discussions.microsoft.com> wrote in message
news:C5E9214E-628F-4CC3-8CC4-4DD7A5347999@.microsoft.com...
> Hello,
> I created a view in SQL Server Express 2005 that contained CASE END
> statements.
> I use this view in a .NET 2005 project, which works fine on my machine.
> When I deploy my project to a machine that is running SQL Server 200, I
> get
> the following error:
> The Query Designer does not support the CASE SQL contruct.
> Any way around this'
> TIA,
> Amber
>
If you could include the CASE statement that you used, and any other
relevant DDL we could be more helpful.
Rick Sawtell|||Which SQL 200x? :) 2000 or 2005?
The way around this: don't use the Query Designer. Script it and run the
code in Query Analyzer.
amber wrote:
> Hello,
> I created a view in SQL Server Express 2005 that contained CASE END
> statements.
> I use this view in a .NET 2005 project, which works fine on my machine.
> When I deploy my project to a machine that is running SQL Server 200, I ge
t
> the following error:
> The Query Designer does not support the CASE SQL contruct.
> Any way around this'
> TIA,
> Amber
>|||"amber" wrote:
> The Query Designer does not support the CASE SQL contruct.
> Any way around this'
> TIA,
> Amber
>
Yes. Learn to write SQL instead of using the Query Designer. The designer is
a crutch that will do you know favours in the long run. If you want to use
anything more than very basic stuff then you must avoid it.
David Portas
SQL Server MVP
--

CASE SQL Construct

I am using the following code to construct an SQL 7.0 View Column

"CASE WHEN [DailyHours] > SUM([TransAmt]) THEN 0 ELSE 1 END"

and it works great!!

However, when I try the same line in SQL 2000, I get the message "The query designer does not support the case SQL construct"

The help screen is no help as all it says is "the syntax you entered is valid but is not supported visually by Query Designer. Be sure the verify your syntax before saving."

It will not let me save, so I'm not sure what to do from here now??You need to write your query in Query Analyzer, not the Query Designer, which is limited in the types of query logic it can represent in its GUI interface.
No self-respecting DBA writes queries in Query Designer. Time to take off the training wheels...|||You are right, I shouldn't be using Query Designer.... here's my problem.

My client is in another city, and I am logging onto their system remotely to copy some code I've written using a VPN they set up for me on my lap top...

The only software I have on my lap top is designer ... which is lame I know.

I have Visual Studio on my primary system...... but no simple way to put this code on their system - ANY thoughts?|||Why don't you use osql?|||No self-respecting DBA writes queries in Query Designer. Time to take off the training wheels...

You taking midol today?|||You taking midol today?
Just my usual grace and elegance.|||Just rename the file to remove the .txt extension|||I opened the file and I get a black screen that looks like an old DOS prompt, which is asking for a password. No matter what I enter it doesn't accept, and a blank simply closes the window....... I'm confused..... what's the password - my windos password or something specific that I have yet to learn??|||at a command window type 'osql.exe /?' it will give you a quick help message on how to use osql. Once you are in to the server, you can execute t-sql commands from there. Look it up in BOL if you need more help.

Case sensitive query? Problems with Integration Services Query

Hi,
i am writing a IntegrationServices2005 job which copies the result of a
view to a table.
I found a very strange problem:
select * from [dbo].[warehouse_aufwandurlaub] -> runs in < 1 second.
select * from [dbo].[warehouse_AufwandUrlaub] -> runs in > 30 Seconds
(timeout).
the name of the view is "warehouse_AufwandUrlaub".
If i run the query in query analyzer, both querys exectute very fast.
What is causing this failure? how can i avoid this?
Thx
Best regards,
Patrick DingerSorry i forgot to mention, that i am using SQL Server 2005 with SP1.
Thx in advance,
Patrick Dinger|||Any activites on the server at this time?
Don't you really need WHERE condition?
"Patrick Dinger" <paxos2k@.gmail.com> wrote in message
news:1147690976.451955.271160@.j55g2000cwa.googlegroups.com...
> Sorry i forgot to mention, that i am using SQL Server 2005 with SP1.
> Thx in advance,
> Patrick Dinger
>|||Hi,
its not an ressource problem, there isn't really activity on the server
currently.
the error is reproducible. No, i do not need a where condition...
Best regards,
Patrick Dinger

CASE in a VIEW

Hello,
I've have a query that I want to convert into a view... but it contains
a CASE statement. Views will not accept CASE and I really need to use a
case statement here because of the way the tables are structured. Am I
doomed to creating a new (very messy) query that will suffice in a view,
do I have to use a stored procedure or is there an elegant way to
resolve this problem?
Thanks in advance,
Craig H.
select le.Name as Client, lec.Name as CompanyType, div.name as Division,
p.Name as PName,
case
when apc.paymentrouting_id IS NOT NULL THEN 'A'
when bpc.paymentrouting_id IS NOT NULL THEN 'B'
when cpc.paymentrouting_id IS NOT NULL THEN 'C'
when cpc.paymentrouting_id IS NOT NULL THEN 'D'
else 'UNKNOWN'
end as ServiceType
into #temptable
from PROD.dbo.LegalEntity le
inner join PROD.dbo.contract c
on ((c.contracting_legalentity_id = le.legalentity_id) and (c.enddate IS
NULL or c.enddate > getdate()))
inner join PROD.dbo.LegalEntity div
on div.legalEntity_ID = c.providing_legalentity_id
inner join PROD.dbo.LegalEntityCode lec
on lec.LegalEntityCode = le.LegalEntityCode
inner join PROD.dbo.payee p
on ((p.contract_id = c.contract_id) and (p.active = 'true'))
inner join PROD.dbo.paymentrouting pr
on pr.payee_id = p.payee_id
left join PROD.dbo.APaymentChannel apc
on ((apc.paymentrouting_id = pr.paymentrouting_id) and (apc.active =
'true'))
left join PROD.dbo.BPaymentChannel bpc
on ((bpc.paymentrouting_id = pr.paymentrouting_id) and (bpc.active =
'true'))
left join PROD.dbo.CPaymentChannel cpc
on ((cpc.paymentrouting_id = pr.paymentrouting_id) and (cpc.active =
'true'))
left join PROD.dbo.DPaymentChannel dpc
on ((dpc.paymentrouting_id = pr.paymentrouting_id) and (dpc.active =
'true'))
go
select tt.client, tt.CompanyType, tt.Division, tt.PName, tt.ServiceType
from #temptable tt
group by tt.client, tt.CompanyType, tt.Division, tt.PName, tt.ServiceType
order by tt.client
go
drop table #temptable
go
"The power of accurate observation is frequently called cynicism by
those who don't have it."CASE statements are supported in views. You can't create them using the
Query Designer in Enterprise Manager, as that has limitations on what is
allowed in a view, but if you create the view in Query Analyzer is should
all work fine.
Jacco Schalkwijk
SQL Server MVP
"Craig H." <spam@.thehurley.com> wrote in message
news:OPzJy5pJFHA.2356@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I've have a query that I want to convert into a view... but it contains a
> CASE statement. Views will not accept CASE and I really need to use a
> case statement here because of the way the tables are structured. Am I
> doomed to creating a new (very messy) query that will suffice in a view,
> do I have to use a stored procedure or is there an elegant way to resolve
> this problem?
> Thanks in advance,
> Craig H.
>
> select le.Name as Client, lec.Name as CompanyType, div.name as Division,
> p.Name as PName,
> case
> when apc.paymentrouting_id IS NOT NULL THEN 'A'
> when bpc.paymentrouting_id IS NOT NULL THEN 'B'
> when cpc.paymentrouting_id IS NOT NULL THEN 'C'
> when cpc.paymentrouting_id IS NOT NULL THEN 'D'
> else 'UNKNOWN'
> end as ServiceType
> into #temptable
> from PROD.dbo.LegalEntity le
> inner join PROD.dbo.contract c
> on ((c.contracting_legalentity_id = le.legalentity_id) and (c.enddate IS
> NULL or c.enddate > getdate()))
> inner join PROD.dbo.LegalEntity div
> on div.legalEntity_ID = c.providing_legalentity_id
> inner join PROD.dbo.LegalEntityCode lec
> on lec.LegalEntityCode = le.LegalEntityCode
> inner join PROD.dbo.payee p
> on ((p.contract_id = c.contract_id) and (p.active = 'true'))
> inner join PROD.dbo.paymentrouting pr
> on pr.payee_id = p.payee_id
> left join PROD.dbo.APaymentChannel apc
> on ((apc.paymentrouting_id = pr.paymentrouting_id) and (apc.active =
> 'true'))
> left join PROD.dbo.BPaymentChannel bpc
> on ((bpc.paymentrouting_id = pr.paymentrouting_id) and (bpc.active =
> 'true'))
> left join PROD.dbo.CPaymentChannel cpc
> on ((cpc.paymentrouting_id = pr.paymentrouting_id) and (cpc.active =
> 'true'))
> left join PROD.dbo.DPaymentChannel dpc
> on ((dpc.paymentrouting_id = pr.paymentrouting_id) and (dpc.active =
> 'true'))
> go
> select tt.client, tt.CompanyType, tt.Division, tt.PName, tt.ServiceType
> from #temptable tt
> group by tt.client, tt.CompanyType, tt.Division, tt.PName, tt.ServiceType
> order by tt.client
> go
> drop table #temptable
> go
>
> --
> "The power of accurate observation is frequently called cynicism by those
> who don't have it."|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23obv4FqJFHA.3084@.TK2MSFTNGP10.phx.gbl...
> CASE statements are supported in views. You can't create them using the
> Query Designer in Enterprise Manager, as that has limitations on what is
> allowed in a view, but if you create the view in Query Analyzer is should
> all work fine.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Craig H." <spam@.thehurley.com> wrote in message
> news:OPzJy5pJFHA.2356@.TK2MSFTNGP14.phx.gbl...
>
Jacco is correct, it's a limitation of EM, not SQL Server itself (assuming
you're running service pack 3). Here's a proof of concept that obviates the
need for a CASE statement by making use of NULL propagation, in case you're
interested. You'll want to check the setting of CONCAT_NULL_YIELDS_NULL and
make sure it's on. I believe that's the default.
DECLARE @.a INT, @.b INT, @.c INT, @.d INT
SELECT @.a = NULL, @.b = 2, @.c = NULL, @.d = 4
SELECT COALESCE(
LEFT('A' + CAST(@.a AS VARCHAR),1),
LEFT('B' + CAST(@.b AS VARCHAR),1),
LEFT('C' + CAST(@.c AS VARCHAR),1),
LEFT('D' + CAST(@.d AS VARCHAR),1),
'UNKNOWN'
)
-Chris Hohmann

Case expressions may only be nested to level 10

I'm running into this issue when I try to query a table (well a view) that was poorly designed and has many fields in the table as YesNo fields instead of a single field that just contains a value. (Fields A-N in the table for example)

So my query looks like:

CASE
WHEN A = 1 THEN 'A'
WHEN B = 1 THEN 'B'
WHEN C = 1 THEN 'C'
WHEN D = 1 THEN 'D'
WHEN E = 1 THEN 'E'
WHEN F = 1 THEN 'F'
WHEN G = 1 THEN 'G'
WHEN H = 1 THEN 'H'
WHEN I = 1 THEN 'I'
WHEN J = 1 THEN 'J'
WHEN K = 1 THEN 'K'
WHEN L = 1 THEN 'L'
WHEN M = 1 THEN 'M'
WHEN N = 1 THEN 'N'
END AS GeneratedField

Is there some other way I can approach this? I can't change the table as it isn't mine, but I need to run a report that looks at those fields and in GeneratedField places the result for the field that was checked.

Thanks.

Yes. Case Expression can be nested upto 10 Level.

But your expression is not looking like a nested expression.

Can you post the exact expression you are using.. Is any one of these A-N columns hold the value as 1 and reset 0.

|||

Ars_maxer,

There is another way of doing it, it's complicated than simpe case statement, though. Use Unpivot.

Here it is.

Code Snippet

declare @.tb table (
id int,
A int,
B int,
C int,
D int
);
insert into @.tb values(1, 1,0,0,0)
insert into @.tb values(2, 0,1,0,0)
insert into @.tb values(3,0,0,1,0)
insert into @.tb values(4,1,0,0,0)

select id, genvalue, vvalue from
( select id, A,B,C,D from @.tb)p
UNPIVOT
(vvalue for genvalue in (A,B,C,D) ) as unpvt
where vvalue =1

id genvalue vvalue
1 A 1
2 B 1
3 C 1
4 A 1

|||

This is a little hoakie, but here we go:

Code Snippet

declare @.a int, @.b int, @.c int, @.d int, @.e int

set @.a = 0

set @.b = 0

set @.c = 1

set @.d = 0

set @.e = 0

selectcoalesce(replace(convert(char(1),nullif(@.a,0)),'1','A'),

replace(convert(char(1),nullif(@.b,0)),'1','B'),

replace(convert(char(1),nullif(@.c,0)),'1','C'),

replace(convert(char(1),nullif(@.d,0)),'1','D'),

replace(convert(char(1),nullif(@.e,0)),'1','E')

)

|||

I'm not doing anything that isn't shown in my SQL statement there (Other than a SELECT and FROM and WHERE). However, nothing unusual.

Now if I run that SQL with say 8 statements then it works just fine.

I've read that this appears to be an issue and that others have run into it before. In some cases they wrapped the whole thing in a stored procedure on the linked server or their server and ran it that way.

In my case I'm executing this against a linked server and can't make a sproc there.

I'll play with the examples provided above and see if those will address the issue for me or not.

Thanks!

Cascading Multivalue Parameters fail to populate in subscription view

SSRS - SP2

We have many reports with cascading multivalue parameters. The reports and the parameters work as expected within the BI development and when deployed to the RS server. The issue we are having is that when we go to the Report Manager webpage for one of these reports and create a New Subscription the report parameters in the subscription field fail to populate. The first one or two parameters may properly populate but the 3rd, 4th, and 5th fail to populate. As a result we can not select the parameters to submit the subscription.

We have two example reports where one report uses only sql (text) and the other report uses only stored procedures. Both reports have 2 or more cascading parameters.

Any known issues with cascading multivalue parameters in the Report Manager subscription view?

I'm having the exact same problem. Did you get a fix or an answer?

Cascading Multivalue Parameters fail to populate in subscription view

SSRS - SP2

We have many reports with cascading multivalue parameters. The reports and the parameters work as expected within the BI development and when deployed to the RS server. The issue we are having is that when we go to the Report Manager webpage for one of these reports and create a New Subscription the report parameters in the subscription field fail to populate. The first one or two parameters may properly populate but the 3rd, 4th, and 5th fail to populate. As a result we can not select the parameters to submit the subscription.

We have two example reports where one report uses only sql (text) and the other report uses only stored procedures. Both reports have 2 or more cascading parameters.

Any known issues with cascading multivalue parameters in the Report Manager subscription view?

I'm having the exact same problem. Did you get a fix or an answer?

Carriage Return In View

Is it possible to return one field in a view that has something similar to
vbCrLf in Visual Basic? I want to return it as a formatted envelope
address. Thanks.
Daviduse char(13) as your column value
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"David C" wrote:

> Is it possible to return one field in a view that has something similar to
> vbCrLf in Visual Basic? I want to return it as a formatted envelope
> address. Thanks.
> David
>
>|||Thanks. Actually, I had to use ... + Char(13) + Char(10)
David
"mark baekdal" <markbaekdal@.discussions.microsoft.com> wrote in message
news:2CEEA448-B43B-457C-BE12-D0547A4F3AD4@.microsoft.com...
> use char(13) as your column value
>
> regards,
> Mark Baekdal
> http://www.dbghost.com
> http://www.innovartis.co.uk
> +44 (0)208 241 1762
> Database change management for SQL Server
>
> "David C" wrote:
>

capture showplan in profiler

What other event classes or columns do I need to view the showplan all event
class in profiler? The textdata seems to be empty. Using SQL Server 2000Binary data column. There is also a pretty cool way of extracting the
information if you save/load the trace into a table here
http://www.umachandar.com/technical/SQL2000Scripts/UtilitySPs/Main9.htm
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uW4ukVqcDHA.652@.tk2msftngp13.phx.gbl...
What other event classes or columns do I need to view the showplan all event
class in profiler? The textdata seems to be empty. Using SQL Server 2000

cant view recently added disk

I just added a new disk on a 2003 server box running sql 2000 and i want to
redirect my backups to the new disk (which resides on our EMC), the disk
shows up and i was able to add it as a new resource in cluster manager but it
doesnt show up in sql enterprise manager. Any thoughts? Thanks
You must add the disk as a dependency to the SQL Server.
Add the Resource to the SQL server group. Take the SQL Server offline.
Right click the SQL Server resource (not the group) and choose Properties.
Select the Dependencies tab. Add the new disk resource to the Resource
dependencies list. OK. Bring the entire SQL group online.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"ampsman78" <ampsman78@.discussions.microsoft.com> wrote in message
news:29D391B2-28D0-4071-9C01-80DD1C3CA561@.microsoft.com...
> I just added a new disk on a 2003 server box running sql 2000 and i want
to
> redirect my backups to the new disk (which resides on our EMC), the disk
> shows up and i was able to add it as a new resource in cluster manager but
it
> doesnt show up in sql enterprise manager. Any thoughts? Thanks

cant view recently added disk

I just added a new disk on a 2003 server box running sql 2000 and i want to
redirect my backups to the new disk (which resides on our EMC), the disk
shows up and i was able to add it as a new resource in cluster manager but it
doesnt show up in sql enterprise manager. Any thoughts? ThanksYou must add the disk as a dependency to the SQL Server.
Add the Resource to the SQL server group. Take the SQL Server offline.
Right click the SQL Server resource (not the group) and choose Properties.
Select the Dependencies tab. Add the new disk resource to the Resource
dependencies list. OK. Bring the entire SQL group online.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"ampsman78" <ampsman78@.discussions.microsoft.com> wrote in message
news:29D391B2-28D0-4071-9C01-80DD1C3CA561@.microsoft.com...
> I just added a new disk on a 2003 server box running sql 2000 and i want
to
> redirect my backups to the new disk (which resides on our EMC), the disk
> shows up and i was able to add it as a new resource in cluster manager but
it
> doesnt show up in sql enterprise manager. Any thoughts? Thanks

cant view recently added disk

I just added a new disk on a 2003 server box running sql 2000 and i want to
redirect my backups to the new disk (which resides on our EMC), the disk
shows up and i was able to add it as a new resource in cluster manager but i
t
doesnt show up in sql enterprise manager. Any thoughts? ThanksYou must add the disk as a dependency to the SQL Server.
Add the Resource to the SQL server group. Take the SQL Server offline.
Right click the SQL Server resource (not the group) and choose Properties.
Select the Dependencies tab. Add the new disk resource to the Resource
dependencies list. OK. Bring the entire SQL group online.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"ampsman78" <ampsman78@.discussions.microsoft.com> wrote in message
news:29D391B2-28D0-4071-9C01-80DD1C3CA561@.microsoft.com...
> I just added a new disk on a 2003 server box running sql 2000 and i want
to
> redirect my backups to the new disk (which resides on our EMC), the disk
> shows up and i was able to add it as a new resource in cluster manager but
it
> doesnt show up in sql enterprise manager. Any thoughts? Thanks

Can't view merge agent properties (trying again)

In Enterprise Manager (EM), if you go to: [Replication Monitor -> Agents ->
Merge Agents] you'll see a list of Merge Agents to the right.
I'd like to be able to select one of the items, right-click, and select
"Agent Properties".
When I do this, I get a dialog box that appears. The title of the dialog is
"Connect to SQL Server". It's looking for "Connection Information" and it
wants a Login name and Password.
Well, my SQL Server uses the sa user. So, I tried the sa user and it's
password. Nope!
The SQL Server Agent runs under a windows account. I tried that ID and it's
password. Nope!
The other strange thing about this is that my friend doesn't have this
problem on his laptop/SQL Server. He can view the agent properties no
problem. One difference we can detect is that his SQL Server uses a Windows
Account and mine uses sa. Not sure if that makes a difference.
I also went to [Security -> Logins] and added a new login and gave him every
available permission I could find (using the SQL Server Login Properties
Tabbed Dialg). I tried logging on with this guy. Nope!
So, sa doesn't work. The SQL Server Agent windows ID doesn't work. The new
Login guy didn't work. Ummm. What exactly is it looking for? But for some
reason, if I enter the ID and password I get the same MsgBox:
SQL Server Enterprise Manager
A connection could not be established to XXXX [3023].
Reason: SQL Server does not exist or access denied.
ConnectionOpen (Connect())..
Please verify SQL Server is running and check your SQL Server registration
properties (by right-clicking on the XXXX [3023] node) and try again.
Thanks in advance,
William Campbell
Hi.
Maybe I mis-read the msdn web page, but I am reading this (about managed
newsgroups):
a.. Unlimited on-line technical support - keep your PSS incidents
a.. A commitment to respond to your post within two business days
The thing is, this hasn't happened. Did I incorrectly enter my post
somehow? It's been more than 2 business days.
Thanks,
William Campbell
"MSDN-Managed" <NO_SPAM> wrote in message
news:9DB1BD5B-54AA-4CA9-BD40-48084F121F8A@.microsoft.com...
> In Enterprise Manager (EM), if you go to: [Replication Monitor ->
Agents ->
> Merge Agents] you'll see a list of Merge Agents to the right.
> I'd like to be able to select one of the items, right-click, and select
> "Agent Properties".
> When I do this, I get a dialog box that appears. The title of the dialog
is
> "Connect to SQL Server". It's looking for "Connection Information" and it
> wants a Login name and Password.
> Well, my SQL Server uses the sa user. So, I tried the sa user and it's
> password. Nope!
> The SQL Server Agent runs under a windows account. I tried that ID and
it's
> password. Nope!
> The other strange thing about this is that my friend doesn't have this
> problem on his laptop/SQL Server. He can view the agent properties no
> problem. One difference we can detect is that his SQL Server uses a
Windows
> Account and mine uses sa. Not sure if that makes a difference.
> I also went to [Security -> Logins] and added a new login and gave him
every
> available permission I could find (using the SQL Server Login Properties
> Tabbed Dialg). I tried logging on with this guy. Nope!
> So, sa doesn't work. The SQL Server Agent windows ID doesn't work. The
new
> Login guy didn't work. Ummm. What exactly is it looking for? But for
some
> reason, if I enter the ID and password I get the same MsgBox:
> --
> SQL Server Enterprise Manager
> --
> A connection could not be established to XXXX [3023].
> Reason: SQL Server does not exist or access denied.
> ConnectionOpen (Connect())..
> Please verify SQL Server is running and check your SQL Server registration
> properties (by right-clicking on the XXXX [3023] node) and try again.
> Thanks in advance,
> William Campbell
>
|||The problem appears to be that you're not using a posting alias that you've
registered with MSDN. You can start that process using the Register link on
this page: http://msdn.microsoft.com/newsgroups/managed/. The MSDN team that
monitors this newsgroup looking for those posts is using a tool that points
out the posts from Managed Customers and your posting address isn't
appearing as one.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"William Campbell" <NO_SPAM_AT_WILLIAM_CAMPBELL> wrote in message
news:OWjeHj%232EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Hi.
> Maybe I mis-read the msdn web page, but I am reading this (about managed
> newsgroups):
> a.. Unlimited on-line technical support - keep your PSS incidents
> a.. A commitment to respond to your post within two business days
> The thing is, this hasn't happened. Did I incorrectly enter my post
> somehow? It's been more than 2 business days.
> Thanks,
> William Campbell
> "MSDN-Managed" <NO_SPAM> wrote in message
> news:9DB1BD5B-54AA-4CA9-BD40-48084F121F8A@.microsoft.com...
> Agents ->
> is
> it's
> Windows
> every
> new
> some
>
|||Hmm. But I did. The "MSDN-Managed" newgroup post (the original, not the
one with my name) is using the ID that is registered with our Universal
subscription.
I went to the link you mentioned (a few days ago), made sure that I gave our
ID an alias so that our email address didn't appear (thus, the alias
MSDN-Managed and the "NO_SPAM" email address). And submitted the post
through the web interface from the same link you displayed (you sign-in
through that passport account to the web interface).
That's why I'm confused. I went through all the steps. The one from
"MSDN-Managed" (not "William Campbell") is using our subscription id.
William Campbell
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:uRWmYw%232EHA.2788@.TK2MSFTNGP15.phx.gbl...
> The problem appears to be that you're not using a posting alias that
you've
> registered with MSDN. You can start that process using the Register link
on
> this page: http://msdn.microsoft.com/newsgroups/managed/. The MSDN team
that
> monitors this newsgroup looking for those posts is using a tool that
points
> out the posts from Managed Customers and your posting address isn't
> appearing as one.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> Please reply to the newsgroups only, thanks.
> "William Campbell" <NO_SPAM_AT_WILLIAM_CAMPBELL> wrote in message
> news:OWjeHj%232EHA.2540@.TK2MSFTNGP09.phx.gbl...
dialog[vbcol=seagreen]
Properties[vbcol=seagreen]
The
>
|||Hmm, I'll have a chat with the manager of that team then, as our internal
tool clearly isn't picking it up, and get somebody to look at your post.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"William Campbell" <NO_SPAM_AT_WILLIAM_CAMPBELL> wrote in message
news:ud31bc$2EHA.3616@.TK2MSFTNGP11.phx.gbl...
> Hmm. But I did. The "MSDN-Managed" newgroup post (the original, not the
> one with my name) is using the ID that is registered with our Universal
> subscription.
> I went to the link you mentioned (a few days ago), made sure that I gave
> our
> ID an alias so that our email address didn't appear (thus, the alias
> MSDN-Managed and the "NO_SPAM" email address). And submitted the post
> through the web interface from the same link you displayed (you sign-in
> through that passport account to the web interface).
> That's why I'm confused. I went through all the steps. The one from
> "MSDN-Managed" (not "William Campbell") is using our subscription id.
> William Campbell
> "Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
> news:uRWmYw%232EHA.2788@.TK2MSFTNGP15.phx.gbl...
> you've
> on
> that
> points
> rights.
> dialog
> Properties
> The
>
|||Is there a timeframe for this? It's been a few days (and 8 days since my
initial post under this "Msdn-Managed" ID).
Are you saying that this ID that I'm posting under right now ... doesn't
show up as someone with a Universal account? Currently I'm at the web
interface. I'm signed in. Next to the Sign In/Sign Out button is another
smaller button that you can edit a profile. I looked there and didn't see
anything that I could "check" or fill in that I didn't already.
This is the ID that we have associated with our Univeral account. So, I'm
confused. If it's not showing up as valid - can we take care of the issue so
that it does show up as valid? Because I'm not sure what else I can do here
- and It's been weeks since my initial post.
Thanks,
William Campbell
"Stephen Dybing [MSFT]" wrote:

> Hmm, I'll have a chat with the manager of that team then, as our internal
> tool clearly isn't picking it up, and get somebody to look at your post.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Please reply to the newsgroups only, thanks.
> "William Campbell" <NO_SPAM_AT_WILLIAM_CAMPBELL> wrote in message
> news:ud31bc$2EHA.3616@.TK2MSFTNGP11.phx.gbl...
>
>
|||Have you sent email to ngmsdnfb@.microsoft.com about this yet? The managers
of that team are on that alias and will get back to you fairly quickly. I'm
going to forward this along to them, but you might as well send them mail
yourself as well.
It doesn't look to me like you've followed the directions to set up a valid
posting address that can be recognized by our system and until you do so, I
believe that you're going to have this problem. Here are instructions for
registering:
1. Use the passport associated with your MSDN subscription to login at
http://msdn.microsoft.com/subscriptions/.
2. On the What's Hot tab, click the <here> link at the end of the first
paragraph of the "Unlimited Support" section, which takes you to the
registration page.
3. Pick a nickname and domain. For example, you could use johndoe for the
Nickname and @.nospam.nospam as the domain. Click the [Submit] button and it
should register johndoe@.nospam.nospam as your no-spam alias. That alias may
or may not be available. We are experiencing an intermittent problem with
this page where a link goes down and rejects all submissions. If the alias
you select gets rejected, please try waiting 10-15 minutes before trying
again.
When you successfully register we attempt to popup a page with more
information. It explains how to configure your profile, among other things.
If you have popups blocked, the link is:
http://msdn.microsoft.com/subscripti...gednewsgroups/
Once you've successfully registered your account, you'll need to start
posting using is, not the <NO_SPAM> address you used for this post.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"MSDN-Managed" <NO_SPAM> wrote in message
news:33FC6C3D-AF7D-4DAA-8E04-1C2E7944EC39@.microsoft.com...
> Is there a timeframe for this? It's been a few days (and 8 days since my
> initial post under this "Msdn-Managed" ID).
> Are you saying that this ID that I'm posting under right now ... doesn't
> show up as someone with a Universal account? Currently I'm at the web
> interface. I'm signed in. Next to the Sign In/Sign Out button is another
> smaller button that you can edit a profile. I looked there and didn't see
> anything that I could "check" or fill in that I didn't already.
> This is the ID that we have associated with our Univeral account. So, I'm
> confused. If it's not showing up as valid - can we take care of the issue
so
> that it does show up as valid? Because I'm not sure what else I can do
here[vbcol=seagreen]
> - and It's been weeks since my initial post.
> Thanks,
> William Campbell
> "Stephen Dybing [MSFT]" wrote:
internal[vbcol=seagreen]
rights.[vbcol=seagreen]
the[vbcol=seagreen]
Universal[vbcol=seagreen]
gave[vbcol=seagreen]
sign-in[vbcol=seagreen]
link[vbcol=seagreen]
team[vbcol=seagreen]
post[vbcol=seagreen]
Information"[vbcol=seagreen]
ID[vbcol=seagreen]
this[vbcol=seagreen]
properties no[vbcol=seagreen]
a[vbcol=seagreen]
him[vbcol=seagreen]
work.[vbcol=seagreen]
But[vbcol=seagreen]
again.[vbcol=seagreen]
|||William, would you please send me your email address at
stephd@.microsoft.com? I've been chatting with one of the managers of the
Managed Newsgroup support team and he'd like to talk to you directly. If
you'll send me your email address, I'll have him contact you.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:uhBxoGT4EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Have you sent email to ngmsdnfb@.microsoft.com about this yet? The managers
> of that team are on that alias and will get back to you fairly quickly.
I'm
> going to forward this along to them, but you might as well send them mail
> yourself as well.
> It doesn't look to me like you've followed the directions to set up a
valid
> posting address that can be recognized by our system and until you do so,
I
> believe that you're going to have this problem. Here are instructions for
> registering:
> 1. Use the passport associated with your MSDN subscription to login at
> http://msdn.microsoft.com/subscriptions/.
> 2. On the What's Hot tab, click the <here> link at the end of the first
> paragraph of the "Unlimited Support" section, which takes you to the
> registration page.
> 3. Pick a nickname and domain. For example, you could use johndoe for the
> Nickname and @.nospam.nospam as the domain. Click the [Submit] button and
it
> should register johndoe@.nospam.nospam as your no-spam alias. That alias
may
> or may not be available. We are experiencing an intermittent problem with
> this page where a link goes down and rejects all submissions. If the alias
> you select gets rejected, please try waiting 10-15 minutes before trying
> again.
> When you successfully register we attempt to popup a page with more
> information. It explains how to configure your profile, among other
things.
> If you have popups blocked, the link is:
> http://msdn.microsoft.com/subscripti...gednewsgroups/
> Once you've successfully registered your account, you'll need to start
> posting using is, not the <NO_SPAM> address you used for this post.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> Please reply to the newsgroups only, thanks.
> "MSDN-Managed" <NO_SPAM> wrote in message
> news:33FC6C3D-AF7D-4DAA-8E04-1C2E7944EC39@.microsoft.com...
my[vbcol=seagreen]
another[vbcol=seagreen]
see[vbcol=seagreen]
I'm[vbcol=seagreen]
issue[vbcol=seagreen]
> so
> here
> internal
post.[vbcol=seagreen]
> rights.
not[vbcol=seagreen]
> the
> Universal
> gave
post[vbcol=seagreen]
> sign-in
from[vbcol=seagreen]
id.[vbcol=seagreen]
message[vbcol=seagreen]
that[vbcol=seagreen]
> link
> team
that[vbcol=seagreen]
> post
Monitor ->[vbcol=seagreen]
the[vbcol=seagreen]
> Information"
and[vbcol=seagreen]
> ID
have[vbcol=seagreen]
> this
> properties no
uses[vbcol=seagreen]
> a
gave
> him
> work.
> But
> again.
>
|||Test - did this work? Hopefully it picks up my post now.
|||I still don't think you're following the directions. Your posting address
appears to be set to "NO_SPAM" and that is not one of the valid choices from
the registration page that I have pointed out a couple of times, there's no
domain listed. I'm including the directions again below. Please choose a
nickname and enter it in the nickname box (and it would be a very good idea
to make it more unique than "NO_SPAM" and then pick one of the domains from
the choose a domain drop-down list box. Then after as it's registered,
you'll need to use that full email address as your posting address. This
isn't a free service so we need to make it unique enough to ensure that
you're the only one using that alias.
1. Use the passport associated with your MSDN subscription to login at
http://msdn.microsoft.com/subscriptions/.
2. On the What's Hot tab, click the <here> link at the end of the first
paragraph of the "Unlimited Support" section, which takes you to the
registration page.
3. Pick a nickname and domain. For example, you could use johndoe for the
Nickname and @.nospam.nospam as the domain. Click the [Submit] button and it
should register johndoe@.nospam.nospam as your no-spam alias. That alias may
or may not be available. We are experiencing an intermittent problem with
this page where a link goes down and rejects all submissions. If the alias
you select gets rejected, please try waiting 10-15 minutes before trying
again.
When you successfully register we attempt to popup a page with more
information. It explains how to configure your profile, among other things.
If you have popups blocked, the link is:
http://msdn.microsoft.com/subscripti...gednewsgroups/
Once you've successfully registered your account, you'll need to start
posting using it, not the <NO_SPAM> address you used again for this post.
Mitch should be following up with you today via email.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
"MSDN-Managed" <NO_SPAM> wrote in message
news:E42F00CD-59D8-493F-B94A-05737B1F8082@.microsoft.com...
> Test - did this work? Hopefully it picks up my post now.

Can't view Locks / Process ID

Hi,
We have SQL 7 running on Windows 2000 Server. For some reason we are
unable to view Locks / Process ID from workstations running Windows XP
SP2 with Enterprise Manager. Nothing shows up in the window on the
right. All it's says at the top of the window is "There are no items to
show in this view" If we use Enterprise Manager on the server, we can
view the Locks / Process ID. We can view both, Process Info and Locks /
Object from the workstations and server, just not the Locks / Process
ID. We used to, but I don't know what changed. I'm thinking it might
have to do with upgrading to XP SP2, or a Windows update, but I'm not
sure.
Has anyone out there experienced this? Or does anyone know the
solution?
Thanks,
MarkHave you tried reinstalling the Client tools on Affected boxes. Also if you
could start the profiler trace in the background and check if its running
any queries.
HTH
Vishal|||Vishal Gandhi wrote:
> Have you tried reinstalling the Client tools on Affected boxes. Also if yo
u
> could start the profiler trace in the background and check if its running
> any queries.
> HTH
> Vishal
I have tried reinstalling the client tools. I even installed them on a
new PC. Same results. I don't know much about SQL, I did start the
profiler and it was capturing data. I'm not sure what you mean by
running it in the background and see if it is running any queries.
Thanks,
Mark|||Vishal Gandhi wrote:
> Have you tried reinstalling the Client tools on Affected boxes. Also if yo
u
> could start the profiler trace in the background and check if its running
> any queries.
> HTH
> Vishal
I have tried reinstalling the client tools. I even installed them on a
new PC. Same results. I don't know much about SQL, I did start the
profiler and it was capturing data. I'm not sure what you mean by
running it in the background and see if it is running any queries.
Thanks,
Mark

Can't view Locks / Process ID

Hi,
We have SQL 7 running on Windows 2000 Server. For some reason we are
unable to view Locks / Process ID from workstations running Windows XP
SP2 with Enterprise Manager. Nothing shows up in the window on the
right. All it's says at the top of the window is "There are no items to
show in this view" If we use Enterprise Manager on the server, we can
view the Locks / Process ID. We can view both, Process Info and Locks /
Object from the workstations and server, just not the Locks / Process
ID. We used to, but I don't know what changed. I'm thinking it might
have to do with upgrading to XP SP2, or a Windows update, but I'm not
sure.
Has anyone out there experienced this? Or does anyone know the
solution?
Thanks,
MarkHave you tried reinstalling the Client tools on Affected boxes. Also if you
could start the profiler trace in the background and check if its running
any queries.
HTH
Vishal|||Vishal Gandhi wrote:
> Have you tried reinstalling the Client tools on Affected boxes. Also if you
> could start the profiler trace in the background and check if its running
> any queries.
> HTH
> Vishal
I have tried reinstalling the client tools. I even installed them on a
new PC. Same results. I don't know much about SQL, I did start the
profiler and it was capturing data. I'm not sure what you mean by
running it in the background and see if it is running any queries.
Thanks,
Mark|||Vishal Gandhi wrote:
> Have you tried reinstalling the Client tools on Affected boxes. Also if you
> could start the profiler trace in the background and check if its running
> any queries.
> HTH
> Vishal
I have tried reinstalling the client tools. I even installed them on a
new PC. Same results. I don't know much about SQL, I did start the
profiler and it was capturing data. I'm not sure what you mean by
running it in the background and see if it is running any queries.
Thanks,
Mark

Cant view Locks / Process ID

Hi,

We have SQL 7 running on Windows 2000 Server. For some reason we are
unable to view Locks / Process ID from workstations running Windows XP
SP2 with Enterprise Manager. Nothing shows up in the window on the
right. All it's says at the top of the window is "There are no items to
show in this view" If we use Enterprise Manager on the server, we can
view the Locks / Process ID. We can view both, Process Info and Locks /
Object from the workstations and server, just not the Locks / Process
ID. We used to, but I don't know what changed. I'm thinking it might
have to do with upgrading to XP SP2, or a Windows update, but I'm not
sure.

Has anyone out there experienced this? Or does anyone know the
solution?

Thanks,
Mark(mfanny@.gmail.com) writes:
> We have SQL 7 running on Windows 2000 Server. For some reason we are
> unable to view Locks / Process ID from workstations running Windows XP
> SP2 with Enterprise Manager. Nothing shows up in the window on the
> right. All it's says at the top of the window is "There are no items to
> show in this view" If we use Enterprise Manager on the server, we can
> view the Locks / Process ID. We can view both, Process Info and Locks /
> Object from the workstations and server, just not the Locks / Process
> ID. We used to, but I don't know what changed. I'm thinking it might
> have to do with upgrading to XP SP2, or a Windows update, but I'm not
> sure.

I find it a bit difficult to believe that Windows XP SP2 would matter,
at least when it comes to the communication with SQL Server. But maybe
some interaction with the MMC has broken.

If you use any Profiler, can you detect wether Locks / ProcessID generate
any calls to SQL Server?

--
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

Can't use variable against a partitioned view?

I'm using SQL Server 2000. I have two partitioned tables, Result1 and
Result2, and a partitioned view ResultView. This query is correctly
optimized to use Result1:
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = 1
This query scans both tables:
DECLARE @.myid int
SET @.myid = 1
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = @.myid
This means that I cannot take advantage of partitioning unless all queries
use constants for their predicates?!!! That means the entire application
would have to be build around dynamic SQL instead of simple stored procedure
parameters. Can this be true?
Here are the execution plans:
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = 1
StmtText
-----
|--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
|--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1010])))
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
|--Index
Scan(OBJECT[Toggle].[dbo].[Result1].[IX_Result1_TestID]))
DECLARE @.myid int
SET @.myid = 1
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = @.myid
StmtText
-------
|--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
|--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1010])))
|--Concatenation
|--Parallelism(Gather Streams)
| |--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
| |--Filter(WHERESTARTUP EXPR([@.myid]=1)))
| |--Index
Seek(OBJECT[Toggle].[dbo].[Result1].[PK_Result1]),
SEEK[Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
|--Filter(WHERESTARTUP EXPR([@.myid]=2)))
|--Index
Seek(OBJECT[Toggle].[dbo].[Result2].[PK_Result2]),
SEEK[Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
Thanks,
IB
The first execution plan removes the unneeded table reference entirely
because the partition value is known at compile time. However, the second
parameterized query is also efficient. Note the STARTUP EXPR; the
corresponding table is accessed at execution time only if the predicate
(@.myid]=1 or @.myid]=2) is true. Run the query with SET STATISTICS IO ON to
see the actual stats.
Hope this helps.
Dan Guzman
SQL Server MVP
"Itchy Brother" <Itchy Brother@.discussions.microsoft.com> wrote in message
news:8813AC97-916C-4573-9701-5EB74AE1BF71@.microsoft.com...
> I'm using SQL Server 2000. I have two partitioned tables, Result1 and
> Result2, and a partitioned view ResultView. This query is correctly
> optimized to use Result1:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> This query scans both tables:
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> This means that I cannot take advantage of partitioning unless all queries
> use constants for their predicates?!!! That means the entire application
> would have to be build around dynamic SQL instead of simple stored
> procedure
> parameters. Can this be true?
> Here are the execution plans:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> StmtText
> -----
> |--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1010])))
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
> |--Index
> Scan(OBJECT[Toggle].[dbo].[Result1].[IX_Result1_TestID]))
>
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> StmtText
>
> -------
> |--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1010])))
> |--Concatenation
> |--Parallelism(Gather Streams)
> | |--Stream
> Aggregate(DEFINE[partialagg1010]=Count(*)))
> | |--Filter(WHERESTARTUP EXPR([@.myid]=1)))
> | |--Index
> Seek(OBJECT[Toggle].[dbo].[Result1].[PK_Result1]),
> SEEK[Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> |--Parallelism(Gather Streams)
> |--Stream
> Aggregate(DEFINE[partialagg1010]=Count(*)))
> |--Filter(WHERESTARTUP EXPR([@.myid]=2)))
> |--Index
> Seek(OBJECT[Toggle].[dbo].[Result2].[PK_Result2]),
> SEEK[Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> Thanks,
> IB
|||Itchy,
The query plan has to work for all possible values of the
variable/parameter, because the query plan is cached and reused.
However, as noted by Dan, if run the query and monitor the table reads,
you will see that the irrelevant partition is not accessed.
Gert-Jan
Itchy Brother wrote:
> I'm using SQL Server 2000. I have two partitioned tables, Result1 and
> Result2, and a partitioned view ResultView. This query is correctly
> optimized to use Result1:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> This query scans both tables:
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> This means that I cannot take advantage of partitioning unless all queries
> use constants for their predicates?!!! That means the entire application
> would have to be build around dynamic SQL instead of simple stored procedure
> parameters. Can this be true?
> Here are the execution plans:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> StmtText
> -----
> |--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1010])))
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
> |--Index
> Scan(OBJECT[Toggle].[dbo].[Result1].[IX_Result1_TestID]))
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> StmtText
>
> -------
> |--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1010])))
> |--Concatenation
> |--Parallelism(Gather Streams)
> | |--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
> | |--Filter(WHERESTARTUP EXPR([@.myid]=1)))
> | |--Index
> Seek(OBJECT[Toggle].[dbo].[Result1].[PK_Result1]),
> SEEK[Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
> |--Filter(WHERESTARTUP EXPR([@.myid]=2)))
> |--Index
> Seek(OBJECT[Toggle].[dbo].[Result2].[PK_Result2]),
> SEEK[Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> Thanks,
> IB

Can't use variable against a partitioned view?

I'm using SQL Server 2000. I have two partitioned tables, Result1 and
Result2, and a partitioned view ResultView. This query is correctly
optimized to use Result1:
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = 1
This query scans both tables:
DECLARE @.myid int
SET @.myid = 1
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = @.myid
This means that I cannot take advantage of partitioning unless all queries
use constants for their predicates?!!! That means the entire application
would have to be build around dynamic SQL instead of simple stored procedure
parameters. Can this be true?
Here are the execution plans:
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = 1
StmtText
-----
|--Compute Scalar(DEFINE:([Expr1009]=Convert([globalagg1011])))
|--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010])))
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
|--Index
Scan(OBJECT:([Toggle].[dbo].[Result1].[IX_Result1_TestID]))
DECLARE @.myid int
SET @.myid = 1
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = @.myid
StmtText
-------
|--Compute Scalar(DEFINE:([Expr1009]=Convert([globalagg1011])))
|--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010])))
|--Concatenation
|--Parallelism(Gather Streams)
| |--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
| |--Filter(WHERE:(STARTUP EXPR([@.myid]=1)))
| |--Index
Seek(OBJECT:([Toggle].[dbo].[Result1].[PK_Result1]),
SEEK:([Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
|--Filter(WHERE:(STARTUP EXPR([@.myid]=2)))
|--Index
Seek(OBJECT:([Toggle].[dbo].[Result2].[PK_Result2]),
SEEK:([Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
Thanks,
IBThe first execution plan removes the unneeded table reference entirely
because the partition value is known at compile time. However, the second
parameterized query is also efficient. Note the STARTUP EXPR; the
corresponding table is accessed at execution time only if the predicate
(@.myid]=1 or @.myid]=2) is true. Run the query with SET STATISTICS IO ON to
see the actual stats.
Hope this helps.
Dan Guzman
SQL Server MVP
"Itchy Brother" <Itchy Brother@.discussions.microsoft.com> wrote in message
news:8813AC97-916C-4573-9701-5EB74AE1BF71@.microsoft.com...
> I'm using SQL Server 2000. I have two partitioned tables, Result1 and
> Result2, and a partitioned view ResultView. This query is correctly
> optimized to use Result1:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> This query scans both tables:
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> This means that I cannot take advantage of partitioning unless all queries
> use constants for their predicates?!!! That means the entire application
> would have to be build around dynamic SQL instead of simple stored
> procedure
> parameters. Can this be true?
> Here are the execution plans:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> StmtText
> -----
> |--Compute Scalar(DEFINE:([Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010])))
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
> |--Index
> Scan(OBJECT:([Toggle].[dbo].[Result1].[IX_Result1_TestID]))
>
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> StmtText
>
> -------
> |--Compute Scalar(DEFINE:([Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010])))
> |--Concatenation
> |--Parallelism(Gather Streams)
> | |--Stream
> Aggregate(DEFINE:([partialagg1010]=Count(*)))
> | |--Filter(WHERE:(STARTUP EXPR([@.myid]=1)))
> | |--Index
> Seek(OBJECT:([Toggle].[dbo].[Result1].[PK_Result1]),
> SEEK:([Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> |--Parallelism(Gather Streams)
> |--Stream
> Aggregate(DEFINE:([partialagg1010]=Count(*)))
> |--Filter(WHERE:(STARTUP EXPR([@.myid]=2)))
> |--Index
> Seek(OBJECT:([Toggle].[dbo].[Result2].[PK_Result2]),
> SEEK:([Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> Thanks,
> IB|||Itchy,
The query plan has to work for all possible values of the
variable/parameter, because the query plan is cached and reused.
However, as noted by Dan, if run the query and monitor the table reads,
you will see that the irrelevant partition is not accessed.
Gert-Jan
Itchy Brother wrote:
> I'm using SQL Server 2000. I have two partitioned tables, Result1 and
> Result2, and a partitioned view ResultView. This query is correctly
> optimized to use Result1:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> This query scans both tables:
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> This means that I cannot take advantage of partitioning unless all queries
> use constants for their predicates?!!! That means the entire application
> would have to be build around dynamic SQL instead of simple stored procedure
> parameters. Can this be true?
> Here are the execution plans:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> StmtText
> -----
> |--Compute Scalar(DEFINE:([Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010])))
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
> |--Index
> Scan(OBJECT:([Toggle].[dbo].[Result1].[IX_Result1_TestID]))
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> StmtText
>
> -------
> |--Compute Scalar(DEFINE:([Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010])))
> |--Concatenation
> |--Parallelism(Gather Streams)
> | |--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
> | |--Filter(WHERE:(STARTUP EXPR([@.myid]=1)))
> | |--Index
> Seek(OBJECT:([Toggle].[dbo].[Result1].[PK_Result1]),
> SEEK:([Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
> |--Filter(WHERE:(STARTUP EXPR([@.myid]=2)))
> |--Index
> Seek(OBJECT:([Toggle].[dbo].[Result2].[PK_Result2]),
> SEEK:([Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> Thanks,
> IB

Can't use variable against a partitioned view?

I'm using SQL Server 2000. I have two partitioned tables, Result1 and
Result2, and a partitioned view ResultView. This query is correctly
optimized to use Result1:
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = 1
This query scans both tables:
DECLARE @.myid int
SET @.myid = 1
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = @.myid
This means that I cannot take advantage of partitioning unless all queries
use constants for their predicates?!!! That means the entire application
would have to be build around dynamic SQL instead of simple stored procedure
parameters. Can this be true?
Here are the execution plans:
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = 1
StmtText
----
--
|--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
|--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1010])))
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
|--Index
Scan(OBJECT[Toggle].[dbo].[Result1].[IX_Result1_TestID]))
DECLARE @.myid int
SET @.myid = 1
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = @.myid
StmtText
----
----
--
|--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
|--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1010])))
|--Concatenation
|--Parallelism(Gather Streams)
| |--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
| |--Filter(WHERESTARTUP EXPR([@.myid]=1)))
| |--Index
Seek(OBJECT[Toggle].[dbo].[Result1].[PK_Result1]),
SEEK[Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE[partialagg1010]=Count(*)))
|--Filter(WHERESTARTUP EXPR([@.myid]=2)))
|--Index
Seek(OBJECT[Toggle].[dbo].[Result2].[PK_Result2]),
SEEK[Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
Thanks,
IBThe first execution plan removes the unneeded table reference entirely
because the partition value is known at compile time. However, the second
parameterized query is also efficient. Note the STARTUP EXPR; the
corresponding table is accessed at execution time only if the predicate
(@.myid]=1 or @.myid]=2) is true. Run the query with SET STATISTICS IO ON to
see the actual stats.
Hope this helps.
Dan Guzman
SQL Server MVP
"Itchy Brother" <Itchy Brother@.discussions.microsoft.com> wrote in message
news:8813AC97-916C-4573-9701-5EB74AE1BF71@.microsoft.com...
> I'm using SQL Server 2000. I have two partitioned tables, Result1 and
> Result2, and a partitioned view ResultView. This query is correctly
> optimized to use Result1:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> This query scans both tables:
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> This means that I cannot take advantage of partitioning unless all queries
> use constants for their predicates?!!! That means the entire application
> would have to be build around dynamic SQL instead of simple stored
> procedure
> parameters. Can this be true?
> Here are the execution plans:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> StmtText
> ----
--
> |--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1
010])))
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE[partialagg1010]=Count(*))
)
> |--Index
> Scan(OBJECT[Toggle].[dbo].[Result1].[IX_Result1_TestID])
)
>
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> StmtText
>
> ----
----
--
> |--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg1
010])))
> |--Concatenation
> |--Parallelism(Gather Streams)
> | |--Stream
> Aggregate(DEFINE[partialagg1010]=Count(*)))
> | |--Filter(WHERESTARTUP EXPR([@.myid]=1)))
> | |--Index
> Seek(OBJECT[Toggle].[dbo].[Result1].[PK_Result1]),
> SEEK[Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> |--Parallelism(Gather Streams)
> |--Stream
> Aggregate(DEFINE[partialagg1010]=Count(*)))
> |--Filter(WHERESTARTUP EXPR([@.myid]=2)))
> |--Index
> Seek(OBJECT[Toggle].[dbo].[Result2].[PK_Result2]),
> SEEK[Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> Thanks,
> IB|||Itchy,
The query plan has to work for all possible values of the
variable/parameter, because the query plan is cached and reused.
However, as noted by Dan, if run the query and monitor the table reads,
you will see that the irrelevant partition is not accessed.
Gert-Jan
Itchy Brother wrote:
> I'm using SQL Server 2000. I have two partitioned tables, Result1 and
> Result2, and a partitioned view ResultView. This query is correctly
> optimized to use Result1:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> This query scans both tables:
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> This means that I cannot take advantage of partitioning unless all queries
> use constants for their predicates?!!! That means the entire application
> would have to be build around dynamic SQL instead of simple stored procedu
re
> parameters. Can this be true?
> Here are the execution plans:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> StmtText
> ----
--
> |--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg
1010])))
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE[partialagg1010]=Count(*)
))
> |--Index
> Scan(OBJECT[Toggle].[dbo].[Result1].[IX_Result1_TestID])
)
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> StmtText
>
> ----
----
--
> |--Compute Scalar(DEFINE[Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE[globalagg1011]=SUM([partialagg
1010])))
> |--Concatenation
> |--Parallelism(Gather Streams)
> | |--Stream Aggregate(DEFINE[partialagg1010]=Cou
nt(*)))
> | |--Filter(WHERESTARTUP EXPR([@.myid]=1)))
> | |--Index
> Seek(OBJECT[Toggle].[dbo].[Result1].[PK_Result1]),
> SEEK[Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE[partialagg1010]=Cou
nt(*)))
> |--Filter(WHERESTARTUP EXPR([@.myid]=2)))
> |--Index
> Seek(OBJECT[Toggle].[dbo].[Result2].[PK_Result2]),
> SEEK[Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> Thanks,
> IB