Dinamic subreports

Hi there,
I want to create a "big report" including a lot of subreports.. but I want
the user can select the reports to show in the "big report" from a list of
existing reports (generally graphs reports). Each user of the application
will have his "big report" configurated as he want, and the settings will be
stored on a table.
Any suggestions?
I have to assign to each subreport the reportname property, the parameters
(I previously have all Parameters in "big report" assigned from an aspx),
and the visibility property,right? but when and where do I write this code?
thank's a lot for any suggestion
Emanuele AndrettaAny suggestion' please...
"Emanuele" <emanuele75@.telefonica.net> ha scritto nel messaggio
news:OD5FI5MeFHA.1448@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I want to create a "big report" including a lot of subreports.. but I want
> the user can select the reports to show in the "big report" from a list of
> existing reports (generally graphs reports). Each user of the application
> will have his "big report" configurated as he want, and the settings will
be
> stored on a table.
> Any suggestions?
> I have to assign to each subreport the reportname property, the parameters
> (I previously have all Parameters in "big report" assigned from an aspx),
> and the visibility property,right? but when and where do I write this
code?
> thank's a lot for any suggestion
> Emanuele Andretta
>

Dimension Properties and Dimension Attributes not found

I am writing an MDX query and I want to include dimension attributes as part of the DIMENSION PROPERTIES section of the query.

SELECT

{ Measures.SalesAmount, Measures.ShipQuantity } on columns,

{ (Items.ItemNumber.Allmembers,

Time.Month.AllMembers) }

DIMENSION PROPERTIES

Items.Description, Items.Category,

MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

From Sales

My problem is that while I can include Items.Description in the dimension properties list, I cannot include Items.Category in the list. The query parser gives an error,

The [Items].[Category] dimension attribute was not found.

I look at the definition of the dimension, and I see no difference between the two attributes in the schema.

What should I be looking at to see if there are differences between these two attributes? Or am I doing something else wrong?

Mike

I'm guessing that Items.ItemNumber is an attribute hierarchy and so Items.ItemNumber.AllMembers includes the All member plus the members of the attribute. Since the server will only return dimension properties that apply to all the members returned for that dimension, having members from different levels can unintentionnally restrict the dimension properties. Try changing the mdx to remove the all member (probably something like Items.ItemNumber.ItemNumer.AllMembers).

Another possibility is that Items.Category is a valid member property name, but not one associated with the level you are querying. Check for a similarly named member property as its easy to confuse member properties for attribute hierarchies and user defined hierarchies. In this case the name you want might be something like "ItemNumber.Category" since it probably only applies to the ItemNumber level.

|||

I'm not sure I am understanding your answer.

Items.ItemNumber is an attribute heirarchy, and so (it appears) are Series, Family, Category, and a bunch of others.

There are two user-defined hierarchies.

I don't understand when to use Items.Description and Items.Description.Description - both work in the DIMENSION properties statement - the first is the hierarchy, the second is the level. Another items attribute hierarchy I have is [Product Life Cycle], and neither Items.[Product Life Cycle] nor Items.[Product Life Cycle].[Product Life Cycle] works in the dimension properties statement, tho they appear identical in the dimension editor.

Here is the Item Dimension in the BIDS dimension editor:

|||

Now I know why Items.Description works in my DIMENSION PROPERTIES statement.

It is referring to the built-in property of the dimension, the Description property that I see in the Dimension editor property window when I select the top node in the tree, the Items dimension node itself. It also works for Items.ID and Items.Name built-in properties.

Still, now the question remains, why can't I use Items.Category or Items.Whatever in my dimension properties statement?

|||

If Items.ItemNumber.Allmembers returns an all member, then that is likely your problem. Try the MDX satement with a single specific member.

Here's a re-wording of basically what I wrote in my first reply that might help.

When including member properties in MDX queries using the DIMENSION PROPERTIES syntax, here are two common causes for confusion.

First, the server will only return member properties that apply to all members requested in a hierarchy. This means that if you request members from multiple levels, you will only get member properties which you have requested and which exist for all of the levels from which you have requested members.So if you request “[MyDim].[MyHier].Members” you will only get member properties applying to all levels.Instead, you should specifically request just members of a single level using something like “[MyDim].[MyHier].[MyLevel].Members”.Note that most attributes hierarchies contain two levels – the all level and the attribute level.This means you can get different results from [MyDim].[MyAttribute].Members and [MyDim].[MyAttribute].[MyAttribute].Members.

A second source of confusion is the fact that you can have similarly named member property for attribute hierarchy levels and user defined hierarchy levels based on the same attributes.This is true even if the attribute hierarchy is not visible.As a result, you may be unintentionally using a perfectly valid member property name that doesn’t correspond to the level of the members you are requesting.This results in no error, but you don’t get back any member properties.

difficult SQL select statement

Hi all,

i have a table containing 24 columns, i would like to generate a new table by input the program gets from the user concurning of the columns he would like to see.

sound like an easy "select ? from table", but the thing is that how can the function know how to get a different number of variables, i mean, one time the user will want to see one column, and afterwards he will want to see 10 columns, is there a solution except generating 24 functions?.

i looked in all kinds of SQL tutorials and nothing came up so i came here, tnx for your help!.

Alon.

public string GenerateSqlSelect(string[] columnNames){

return "SELECT "+String.Join(", ",columnNames)+" FROM MyTable";

}

|||You could use params keyword to specify that your function has a variable number of arguments.
|||How user will know what are the columns from the table. Does he will write them, or select them using check boxes or some other method. I realy don't understand what are you for. Can you describe how user interface will look like, or some scenario of application work.
It is common practice that you always get all columns from table, but user can control which column want to see, for example in a grid or listview. So he will at first see all columns but it will be able to remove some of them. That configuration can be saved and used for next application start.

difficult SQL select statement

Hi all,

i have a table containing 24 columns, i would like to generate a new table by input the program gets from the user concurning of the columns he would like to see.

sound like an easy "select ? from table", but the thing is that how can the function know how to get a different number of variables, i mean, one time the user will want to see one column, and afterwards he will want to see 10 columns, is there a solution except generating 24 functions?.

i looked in all kinds of SQL tutorials and nothing came up so i came here, tnx for your help!.

Alon.

public string GenerateSqlSelect(string[] columnNames){

return "SELECT "+String.Join(", ",columnNames)+" FROM MyTable";

}

|||You could use params keyword to specify that your function has a variable number of arguments.
|||How user will know what are the columns from the table. Does he will write them, or select them using check boxes or some other method. I realy don't understand what are you for. Can you describe how user interface will look like, or some scenario of application work.
It is common practice that you always get all columns from table, but user can control which column want to see, for example in a grid or listview. So he will at first see all columns but it will be able to remove some of them. That configuration can be saved and used for next application start.sql

difficult SQL select statement

Hi all,

i have a table containing 24 columns, i would like to generate a new table by input the program gets from the user concurning of the columns he would like to see.

sound like an easy "select ? from table", but the thing is that how can the function know how to get a different number of variables, i mean, one time the user will want to see one column, and afterwards he will want to see 10 columns, is there a solution except generating 24 functions?.

i looked in all kinds of SQL tutorials and nothing came up so i came here, tnx for your help!.

Alon.

public string GenerateSqlSelect(string[] columnNames){

return "SELECT "+String.Join(", ",columnNames)+" FROM MyTable";

}

|||You could use params keyword to specify that your function has a variable number of arguments.
|||How user will know what are the columns from the table. Does he will write them, or select them using check boxes or some other method. I realy don't understand what are you for. Can you describe how user interface will look like, or some scenario of application work.
It is common practice that you always get all columns from table, but user can control which column want to see, for example in a grid or listview. So he will at first see all columns but it will be able to remove some of them. That configuration can be saved and used for next application start.

difficult select distinct query

Hi,

I have a table as following

aa Text1 aa, p@.xxx.be, 15267
aa Text1 aa, p@.xxx.be, 16598
aa Text1 aa, p@.xxx.be, 17568
aa Text2 aa, p@.xxx.be, 25698
aa Text3 aa, x@.zzz.be, 12258

I have to write a query as follows ...

SELECT DISTINCT TOP 500 fldText, fldContact, fldItemid
FROM table
WHERE fldCat = 10 AND CONTAINS (fldText, 'Text1')

In the example you can see the table has rows in which text and contact or
double but with different itemid's. Now my employer wants me to show only 1
row when text and contact or the same. He doesn't mind which itemid I show
... but I have to show one.

I've an idea of how to do this using a cursor and a temporary table but I
guess that will be fatal for the performance because then I have to loop
through all selected rows, check each row with all other rows and store the
primary key in the temporary table if dedected it isn't double. Afterwards
I can execute ... SELECT ... FROM TABLE where primary key in (select
temp_primarykey from #temptable).

I hoped I could do everything in 1 "easy" SELECT but I should not know how?
Any ideas are much appreciated.

Thanks a lot.
Perre Van Wilrijk.On Tue, 24 Aug 2004 15:18:36 +0200, Perre Van Wilrijk wrote:

>Hi,
>I have a table as following
>aa Text1 aa, p@.xxx.be, 15267
>aa Text1 aa, p@.xxx.be, 16598
>aa Text1 aa, p@.xxx.be, 17568
>aa Text2 aa, p@.xxx.be, 25698
>aa Text3 aa, x@.zzz.be, 12258
>I have to write a query as follows ...
>SELECT DISTINCT TOP 500 fldText, fldContact, fldItemid
>FROM table
>WHERE fldCat = 10 AND CONTAINS (fldText, 'Text1')
>In the example you can see the table has rows in which text and contact or
>double but with different itemid's. Now my employer wants me to show only 1
>row when text and contact or the same. He doesn't mind which itemid I show
>... but I have to show one.
>I've an idea of how to do this using a cursor and a temporary table but I
>guess that will be fatal for the performance because then I have to loop
>through all selected rows, check each row with all other rows and store the
>primary key in the temporary table if dedected it isn't double. Afterwards
>I can execute ... SELECT ... FROM TABLE where primary key in (select
>temp_primarykey from #temptable).
>I hoped I could do everything in 1 "easy" SELECT but I should not know how?
>Any ideas are much appreciated.
>Thanks a lot.
>Perre Van Wilrijk.

Hi Perre,

Try it with this instead:

SELECT fldText, fldContact, MIN(fldItemid)
FROM table
WHERE fldCat = 10
AND CONTAINS (fldText, 'Text1')
GROUP BY fldText, fldContact
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Different return formats

I have a table (call it table_a) that has a column with a datetime format.
1. When I run the following I get the following result:
select min(createdate) from table_a
RESULT: 2003-09-15 15:58:19.273
2. When I run the following I get the following result:
declare @.createdate datetime
select @.createdate = min(createdate) from table_a
print @.createdate
RESULT: Sep 15 2003 3:58PM
How can I get the variable @.createdate to hold the identical value returned in result #1?
--
Message posted via http://www.sqlmonster.comPRINT does not "return" a value it just prints to the output screen. Change
that to "SELECT @.Createdate" instead
"Robert Richards via SQLMonster.com" wrote:
> I have a table (call it table_a) that has a column with a datetime format.
> 1. When I run the following I get the following result:
> select min(createdate) from table_a
> RESULT: 2003-09-15 15:58:19.273
> 2. When I run the following I get the following result:
> declare @.createdate datetime
> select @.createdate = min(createdate) from table_a
> print @.createdate
> RESULT: Sep 15 2003 3:58PM
> How can I get the variable @.createdate to hold the identical value returned in result #1?
> --
> Message posted via http://www.sqlmonster.com
>|||PRINT performs an implict conversion to VARCHAR for its arguments. You can
use CONVERT to specify a format of something other than the default:
PRINT CONVERT(VARCHAR,@.createdate,121)
or you can just return the value as DATETIME, using SELECT, and format it
client-side.
--
David Portas
SQL Server MVP
--

Different Results With View Versus UDF

I have two views, for various reasons I decided to wrap a Select * From View with two UDFs. When run on their own, the UDFs return the exact same resultset as their respective view (did a compare and stuff to make sure). However, when I join them together, what was once 130,000 records (when joining the views) skyrockets into millions of records, and the resulting resultset is filled with duplicates. What is going on?Firstly I would use a stored procedure to return result sets, not UDF's. Secondly, it looks like you are getting a cartesian join, where every row in one table joins to many or every row in the other table. This is caused by incorrectly joining the two table (or views). This is illustrated by the duplicate rows you are getting. Check your joins, and make sure you join primary key to foreign key. To test the query, change the select list to a select count(*), and then run the query with just the first join condition. Then add each condition one by one. At some point the rowcount will explode. However, if the first join produces a massive row count, experiment with adding additional joins until you get the correct amount of records returned.

HTH

For more SQL tips, check out my blog:|||The exact same join with the exact same conditions using the views (which return an identical result set) returns the proper result.|||that's weird, you could have been executing a cross join|||You will have to post a repro script to demonstrate the problem. Otherwise it will be hard to guess what might be wrong. Your SELECT statement could be incorrect, the UDF could be wrong and so on.

different results with the same query

I have SELECT query which include 2 UNION operators.
SELECT ....
UNION
(select....
UNION
selec...)as t1...
If I execute this query in query analyzer, I get 7 records as a result.
If I create new SP and copy the same query into this new STORED PROCEDURE
and execute this procedure,
I get 13 records as a result.
How is that possible that absolutly the same sintax return diferent results?
Both queries are executed on the same database with sa account.
It seems to me that if I execute this query in query analyzer, it return
results as without the union.
Does anybody know why?
This is the first time for me experiencing something like this in 7 years.
Regards,
Simonno It should not. check somebody might have updated mean while.
Post complete script if you find problem
--
Regards
R.D
--Knowledge gets doubled when shared
"simon" wrote:

> I have SELECT query which include 2 UNION operators.
> SELECT ....
> UNION
> (select....
> UNION
> selec...)as t1...
> If I execute this query in query analyzer, I get 7 records as a result.
> If I create new SP and copy the same query into this new STORED PROCEDURE
> and execute this procedure,
> I get 13 records as a result.
> How is that possible that absolutly the same sintax return diferent result
s?
> Both queries are executed on the same database with sa account.
> It seems to me that if I execute this query in query analyzer, it return
> results as without the union.
> Does anybody know why?
> This is the first time for me experiencing something like this in 7 years.
> Regards,
> Simon
>
>|||nobody changed anything.
I execut the procedure in query analyzer: exec dbo.test
OR I execute script below in query analyzer(without "create procedure
dbo.test as" text)
and I get different results.
Amazing, isn't it? Both scripts are executed in the same query analyzer
window with the Sa account butt different result.
Is there some bug or what?
I also try to execute the script with SQL2005 SQL server managment studio
but the same result.
The script is:
CREATE PROCEDURE dbo.test
AS
declare @.idIzdelka varchar(20),@.idDrzave char(3)
set @.idIzdelka='I2314'
set @.idDrzave='CZK'
SELECT
Tk.st_nar_dob,nerazdeljena=sum(Tk.nerazdeljena),razdeljena=sum(Tk.razdeljena
),skupaj=sum(Tk.skupaj),
dobava=(SELECT rok_dobave FROM narDobIzdNed WHERE st_nar_dob=tk.st_nar_dob
AND izd_id=@.idIzdelka)FROM
(SELECT
st_nar_dob=T2.navisionId,sum(isnull(T2.navKolicina,0))-sum(T2.nar_kolicina)-
sum(isnull(T2.zakljucenaKol,0))
as nerazdeljena,
sum(T2.nar_kolicina)+sum(isnull(T2.zakljucenaKol,0)) as
razdeljena,sum(isnull(T2.navKolicina,0)) as skupaj FROM
(SELECT T1.*,zalogaNavision=(SELECT kolicina FROM skladisceIzdelek WHERE
navisionId=t1.navisionId
AND idIzdelka=@.idIzdelka),
navKolicina=(SELECT isnull(kolicina_dej,kolicina_nar) FROM narDobIzdNed
WHERE st_nar_dob=t1.navisionId AND izd_id=@.idIzdelka),
zakljucenaKol=(SELECT sum(nar_kolicina) FROM narociloIzdelek WHERE
navisionID=t1.navisionId AND izd_id=@.idIzdelka and izd_zakljucen=1)
FROM
(SELECT sum(n2.nar_kolicina) as nar_Kolicina,n2.navisionId FROM
(select n1.nar_id,n1.izd_id,max(n1.datum_spremembe) as datumSpremembe FROM
narociloIzdelek n1
GROUP BY n1.nar_id,n1.izd_id)AS T1
INNER JOIN narociloIzdelek n2 ON T1.nar_id=n2.nar_id AND T1.izd_id=n2.izd_id
AND T1.datumSpremembe=n2.datum_spremembe
INNER JOIN narocilo n ON n2.nar_id=n.nar_id INNER JOIN skladisce s ON
n.nar_skladisce_id=s.skladisce_id
INNER JOIN uporabnik u ON u.up_id=n.nar_up_id
WHERE n.nar_status=2 AND n2.izd_zakljucen=0 AND n2.izd_id=@.idIzdelka and
n2.nar_kolicina<>0
and not (n2.navisionId is null OR n2.navisionId='')AND
s.skladisce_drzava_id=@.idDrzave
GROUP BY n2.navisionId)as T1 )as T2 GROUP BY T2.navisionId
UNION
SELECT DISTINCT
T1. st_nar_dob,nerazdeljena=isnull(kolicina_
dej,kolicina_nar),0 as
razdeljena,
skupaj=isnull(kolicina_dej,kolicina_nar)
from--vse, ki so pod sprosti
(select st_nar_dob from narDobIzdNed WHERE izd_id=@.idIzdelka and (naZalogo
is null OR odobri=1)
UNION --vse, ki so obviseli z navision id-jem in so prav tako pod sprosti
select st_nar_dob=navisionID from skladisceIzdelek WHERE navisionID is not
null AND idSkladisca=4 AND idIzdelka=@.idIzdelka)
as T1 INNER JOIN narDobIzdNed n ON T1.st_nar_dob=n.st_nar_dob AND
n.izd_id=@.idIzdelka
WHERE T1.st_nar_dob not in(SELECT n2.navisionID FROM
(select n1.nar_id,n1.izd_id,max(n1.datum_spremembe) as datumSpremembe FROM
narociloIzdelek n1
WHERE n1.izd_id=@.idIzdelka GROUP BY n1.nar_id,n1.izd_id)AS T1
INNER JOIN narociloIzdelek n2 ON T1.nar_id=n2.nar_id AND T1.izd_id=n2.izd_id
AND T1.datumSpremembe=n2.datum_spremembe WHERE n2.izd_id=@.idIzdelka))as Tk
GROUP BY Tk.st_nar_dob
regards,S
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:726AB188-438B-4DBA-8CE6-DA4E1C35CEC1@.microsoft.com...
> no It should not. check somebody might have updated mean while.
> Post complete script if you find problem
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "simon" wrote:
>|||-- Check the settings for ansi nulls for the two stored procedures
-- which I believe are set when the stored procedure are created.
-- This could affect your results.
select name, OBJECTPROPERTY ( id , 'ExecIsAnsiNullsOn' ) as
ExecIsAnsiNullsOn
from sysobjects
where OBJECTPROPERTY ( id , 'IsProcedure' )=1
and name in ('test','?')|||thank you, that was the right answer.
In query analyzer is set to ON.
But I don't understand, I don't compare NULL=NULL anywhere in my query.
Does that UNION operator do internally?
I thought that SET ANSI NULLS ON or OFF affects only comparisations.
Regards,
Simon
<markc600@.hotmail.com> wrote in message
news:1128425201.376908.171720@.o13g2000cwo.googlegroups.com...
> -- Check the settings for ansi nulls for the two stored procedures
> -- which I believe are set when the stored procedure are created.
> -- This could affect your results.
> select name, OBJECTPROPERTY ( id , 'ExecIsAnsiNullsOn' ) as
> ExecIsAnsiNullsOn
> from sysobjects
> where OBJECTPROPERTY ( id , 'IsProcedure' )=1
> and name in ('test','?')
>|||On Tue, 4 Oct 2005 14:33:11 +0200, simon wrote:

>thank you, that was the right answer.
>In query analyzer is set to ON.
>But I don't understand, I don't compare NULL=NULL anywhere in my query.
>Does that UNION operator do internally?
>I thought that SET ANSI NULLS ON or OFF affects only comparisations.
Hi Simon,
Your query has many comparisons between columns, such as
WHERE st_nar_dob=t1.navisionId (random snippet)
If both st_nar_dob and t1.navisionId can hold NULL, then there will be
combinations that test as equal with SET ANSI_NULLS OFF, but unequal
with standard ANSI null handling.
As far as I know, both explicit DISTINCT and the implied DISTINCT in the
UNION operator are not affected by ANSI_NULLS setting - but I'm not 100%
sure.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

different results with select count(*)

Hi,
Strange behavior on SQL Server 2000 SP3a /Win2K Adv server
quad processor...
Table with (4) indexes: fields a,b,c,d
Select count(a) from table
Select count(b) from table
Select count(c) from table
Select count(d) from table
Select count(id) from table
generates different results (!)
We have dropped and rebuilt the indexes.
The table in question has approx. 2 million rows. The
count(id) query is treturning 11 million rows.
Thoughts?
Thanks in advance,
DanHi,
The count will change based on the number of null values inside the table.
Select count(colmn) will count only the not null values inside the table.
To have the full record count use
select count(*) from table_name
Thanks
Hari
MCDBA
"dan" <djlucarelli@.pa1call.org> wrote in message
news:43e001c4732c$1a133c40$a301280a@.phx.gbl...
> Hi,
> Strange behavior on SQL Server 2000 SP3a /Win2K Adv server
> quad processor...
> Table with (4) indexes: fields a,b,c,d
> Select count(a) from table
> Select count(b) from table
> Select count(c) from table
> Select count(d) from table
> Select count(id) from table
> generates different results (!)
> We have dropped and rebuilt the indexes.
> The table in question has approx. 2 million rows. The
> count(id) query is treturning 11 million rows.
> Thoughts?
> Thanks in advance,
> Dan|||> Thoughts?
Yes, don't allow NULLs, or use SELECT COUNT(*)
http://www.aspfaq.com/
(Reverse address to reply.)|||In addition to the other responses, you may have misconceptions about
how SQL-Server processes a query.
If NULLs are disallowed in the columns id, a, b, c and d, then the
queries
Select count(a) from table
Select count(b) from table
Select count(c) from table
Select count(d) from table
Select count(id) from table
will probably all be satisfied with the same query plan. The smallest
index will be used to count the total number of rows. It is highly
unlikely that the query "select count(a) from table" will use the index
on a, and the query "select count(b) from table" the index on b...
If the columns allow and contain NULLs: see the other responses.
Gert-Jan
dan wrote:
> Hi,
> Strange behavior on SQL Server 2000 SP3a /Win2K Adv server
> quad processor...
> Table with (4) indexes: fields a,b,c,d
> Select count(a) from table
> Select count(b) from table
> Select count(c) from table
> Select count(d) from table
> Select count(id) from table
> generates different results (!)
> We have dropped and rebuilt the indexes.
> The table in question has approx. 2 million rows. The
> count(id) query is treturning 11 million rows.
> Thoughts?
> Thanks in advance,
> Dan
(Please reply only to the newsgroup)

different results with select count(*)

Hi,
Strange behavior on SQL Server 2000 SP3a /Win2K Adv server
quad processor...
Table with (4) indexes: fields a,b,c,d
Select count(a) from table
Select count(b) from table
Select count(c) from table
Select count(d) from table
Select count(id) from table
generates different results (!)
We have dropped and rebuilt the indexes.
The table in question has approx. 2 million rows. The
count(id) query is treturning 11 million rows.
Thoughts?
Thanks in advance,
DanHi,
The count will change based on the number of null values inside the table.
Select count(colmn) will count only the not null values inside the table.
To have the full record count use
select count(*) from table_name
Thanks
Hari
MCDBA
"dan" <djlucarelli@.pa1call.org> wrote in message
news:43e001c4732c$1a133c40$a301280a@.phx.gbl...
> Hi,
> Strange behavior on SQL Server 2000 SP3a /Win2K Adv server
> quad processor...
> Table with (4) indexes: fields a,b,c,d
> Select count(a) from table
> Select count(b) from table
> Select count(c) from table
> Select count(d) from table
> Select count(id) from table
> generates different results (!)
> We have dropped and rebuilt the indexes.
> The table in question has approx. 2 million rows. The
> count(id) query is treturning 11 million rows.
> Thoughts?
> Thanks in advance,
> Dan|||> Thoughts?
Yes, don't allow NULLs, or use SELECT COUNT(*)
--
http://www.aspfaq.com/
(Reverse address to reply.)|||In addition to the other responses, you may have misconceptions about
how SQL-Server processes a query.
If NULLs are disallowed in the columns id, a, b, c and d, then the
queries
Select count(a) from table
Select count(b) from table
Select count(c) from table
Select count(d) from table
Select count(id) from table
will probably all be satisfied with the same query plan. The smallest
index will be used to count the total number of rows. It is highly
unlikely that the query "select count(a) from table" will use the index
on a, and the query "select count(b) from table" the index on b...
If the columns allow and contain NULLs: see the other responses.
Gert-Jan
dan wrote:
> Hi,
> Strange behavior on SQL Server 2000 SP3a /Win2K Adv server
> quad processor...
> Table with (4) indexes: fields a,b,c,d
> Select count(a) from table
> Select count(b) from table
> Select count(c) from table
> Select count(d) from table
> Select count(id) from table
> generates different results (!)
> We have dropped and rebuilt the indexes.
> The table in question has approx. 2 million rows. The
> count(id) query is treturning 11 million rows.
> Thoughts?
> Thanks in advance,
> Dan
--
(Please reply only to the newsgroup)

different results with select count(*)

Hi,
Strange behavior on SQL Server 2000 SP3a /Win2K Adv server
quad processor...
Table with (4) indexes: fields a,b,c,d
Select count(a) from table
Select count(b) from table
Select count(c) from table
Select count(d) from table
Select count(id) from table
generates different results (!)
We have dropped and rebuilt the indexes.
The table in question has approx. 2 million rows. The
count(id) query is treturning 11 million rows.
Thoughts?
Thanks in advance,
Dan
Hi,
The count will change based on the number of null values inside the table.
Select count(colmn) will count only the not null values inside the table.
To have the full record count use
select count(*) from table_name
Thanks
Hari
MCDBA
"dan" <djlucarelli@.pa1call.org> wrote in message
news:43e001c4732c$1a133c40$a301280a@.phx.gbl...
> Hi,
> Strange behavior on SQL Server 2000 SP3a /Win2K Adv server
> quad processor...
> Table with (4) indexes: fields a,b,c,d
> Select count(a) from table
> Select count(b) from table
> Select count(c) from table
> Select count(d) from table
> Select count(id) from table
> generates different results (!)
> We have dropped and rebuilt the indexes.
> The table in question has approx. 2 million rows. The
> count(id) query is treturning 11 million rows.
> Thoughts?
> Thanks in advance,
> Dan
|||> Thoughts?
Yes, don't allow NULLs, or use SELECT COUNT(*)
http://www.aspfaq.com/
(Reverse address to reply.)
|||In addition to the other responses, you may have misconceptions about
how SQL-Server processes a query.
If NULLs are disallowed in the columns id, a, b, c and d, then the
queries
Select count(a) from table
Select count(b) from table
Select count(c) from table
Select count(d) from table
Select count(id) from table
will probably all be satisfied with the same query plan. The smallest
index will be used to count the total number of rows. It is highly
unlikely that the query "select count(a) from table" will use the index
on a, and the query "select count(b) from table" the index on b...
If the columns allow and contain NULLs: see the other responses.
Gert-Jan
dan wrote:
> Hi,
> Strange behavior on SQL Server 2000 SP3a /Win2K Adv server
> quad processor...
> Table with (4) indexes: fields a,b,c,d
> Select count(a) from table
> Select count(b) from table
> Select count(c) from table
> Select count(d) from table
> Select count(id) from table
> generates different results (!)
> We have dropped and rebuilt the indexes.
> The table in question has approx. 2 million rows. The
> count(id) query is treturning 11 million rows.
> Thoughts?
> Thanks in advance,
> Dan
(Please reply only to the newsgroup)

Different Results with double quote

If i do the following SQL_queries:
select * from exampletable where CONTAINS(exampletable.exampletext, '
"Shure*" ')
select * from exampletable where CONTAINS(exampletable.exampletext, '
Shure* ')
The query with the double quotes returns much more result and most of them
do not contain the word shure. The query without the quotes returns the
correct rows. Whats the difference for the server?
I am using a Windows 2000 Server with SQL 2000 Server.
Thanks in andvance, Gerald.
Gerald wrote on Tue, 23 Jan 2007 14:50:01 -0800:

> If i do the following SQL_queries:
> select * from exampletable where CONTAINS(exampletable.exampletext, '
> "Shure*" ')
This is a prefix term search, which finds all words that start with the
letters Shure, for instance
Shure
Shurec
Shuretacal

> select * from exampletable where CONTAINS(exampletable.exampletext, '
> Shure* ')
The * is ignored as it only applies when double quotes are used, so only
rows with the word Shure are returned.
Dan
|||"Daniel Crichton" wrote:

> This is a prefix term search, which finds all words that start with the
> letters Shure, for instance
> Shure
> Shurec
> Shuretacal
Thx, Daniel. But in my case the query with the double quotes returns also
results, which do not contain any word starting with shure. It just returns
useless results.
thx, gerald
|||Gerald wrote on Wed, 24 Jan 2007 05:41:01 -0800:

> "Daniel Crichton" wrote:
>
> Thx, Daniel. But in my case the query with the double quotes returns also
> results, which do not contain any word starting with shure. It just
> returns useless results.
Strange. I just tested here using my own servers (both SQL 2000 and SQL
2005) with the following:
SELECT FullTitle FROM Product WHERE CONTAINS(Product.FullTitle,'"asp*"')
and
SELECT FullTitle FROM Product WHERE CONTAINS(Product.FullTitle,'asp*')
The results are as I expected - the first pulls back more titles (351
compared to 101), including ASP as a word, and other words that start wth
ASP such as ASP.NET. The second pulls back only those rows where the word
ASP occurs.
What language word breaker is your FTI using? Maybe there's some sort of
language subsitution going on. Or do you use the thesaurus?
Dan

Different query plans

I have 2 SQL databases which are the same and are giving me different
query plans.

select s.* from hlresults h
inner join specimens s on s.specimen_tk = h.specimen_tk
where s.site_tk = 9 and s.location in ('ABC','WIAD')
and s.date_collected between '2/1/2003' and '2/3/2006'
order by s.location, s.date_collected

Both boxes have the same configuration, the only difference is that one

of them is a cluster.

The Acluster box is taking twice as long to run the query.

I have run statistics on both, and the cluster is still creating a
bitmap and running some parallelism which the other box is not.
Also, the the first step, the A1 box estimates the rows returned to be
around 80K and the actual rows returned is about 40K - subtree cost =
248. The Acluster box estimates 400K - subtree cost=533!
After running statistics, how can it be so off?

I've also reindexed to no avail . . .

any insight would be very much appreciated. We just moved to this new
system and I hate that the db is now slower -

A1:
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 1 1
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 0 0
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 90 90
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 4 4
max server memory (MB) 4 2147483647 14336 14336
max text repl size (B) 0 2147483647 65536 65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 4096 4096
nested triggers 0 1 0 0
network packet size (B) 512 32767 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 0 0
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
scan for startup procs 0 1 1 1
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0

Acluster:
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 1 1
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 0 0
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 90 90
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 4 4
max server memory (MB) 4 2147483647 14336 14336
max text repl size (B) 0 2147483647 65536 65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 4095 4095
nested triggers 0 1 0 0
network packet size (B) 512 32767 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 0 0
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
scan for startup procs 0 1 1 1
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0traceable1 (tracykc@.gmail.com) writes:
> I have 2 SQL databases which are the same and are giving me different
> query plans.
>...
> Both boxes have the same configuration, the only difference is that one
> of them is a cluster.

So they have the same number of CPUs?

> I have run statistics on both, and the cluster is still creating a
> bitmap and running some parallelism which the other box is not.
> Also, the the first step, the A1 box estimates the rows returned to be
> around 80K and the actual rows returned is about 40K - subtree cost =
> 248. The Acluster box estimates 400K - subtree cost=533!
> After running statistics, how can it be so off?

You could try running UPDATE STATISTICS WITH FULLSCAN on the involved
tables, to be really sure that you have factored that part out.

Also, try adding OPTION (MAXDOP 1) on the cluster. Parallelism is
sometimes good, but sometimes it's bad...

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

Different no. of rows returned in SEM vs QA

Hi,
Any idea why when I run a SELECT stament in Query anaylser it returns 45 rows. But when I create the exact same SQL as a view in Enterprise manager it only returns 44 rows?
Thanks,
AlphCould you kindly post the query, so that we can help you better.|||Its a union query with 12 Selects. Here is the first select:

SELECT
dbo.tblWBS.PSWBS AS [PSWBS Code],
ConcatenatedWBS AS [CWBS Code],
case when CR is null then 'R' else 'C' end AS [Capital / Revenue],
NDACost AS [Cost Element],
SUM(dbo.udfBCWScost (NDACost,FiscalYear,Apr_Cost,dbo.tblWBS.PSWBS )) as Amount,
'01-04-' + ltrim(rtrim(str(FiscalYear))) AS [Start Date],
'30-04-' + ltrim(rtrim(str(FiscalYear))) AS [Finish Date],
left(dbo.tblWBS.wbs,6) + Right(dbo.tblWBS.wbs,5) AS [Charge Code]

FROM dbo.tblBCWSMonthly INNER JOIN
dbo.tblBCWSYearly ON dbo.tblBCWSMonthly.RecordId = dbo.tblBCWSYearly.RecordUid INNER JOIN
dbo.tblWBS ON dbo.tblBCWSYearly.WBSUId = dbo.tblWBS.WBSuid

WHERE (dbo.tblWBS.EPSLvl4 = N'1.1.5.17') and dbo.tblWBS.PSWBS = '1.1.5.17.10.01.17001.00000.30 '

GROUP BY dbo.tblWBS.PSWBS, ConcatenatedWBS, CR, NDACost,'01-04-' + ltrim(rtrim(str(FiscalYear))),'30-04-' + ltrim(rtrim(str(FiscalYear))),dbo.tblWBS.wbs

HAVING SUM(dbo.udfBCWScost (NDACost,FiscalYear,Apr_Cost,dbo.tblWBS.PSWBS )) <> 0

UNION ALL

>> Then another 11 select statements|||Check the "Set concat_null_yields_null" setting in your Query Analyzer Connection Properties dialog box. Try toggling it, as it may be set different than your Server default.

Different indexes - performance analysis question

Hello

I'm doing some performance analysis for my application. I'm doing 7600 SQL queries based on the following SQL query:

SELECT LocationId, ProductId, BatchId, SUM(Quantity) AS Quantity FROM Logistics WHERE UserId = [number] AND ProductId IN ([productidlist]) GROUP BY LocationId, ProductId, BatchId;

Data in table Logistics has LocationId = 1 and BatchId = 0 for absolute all rows in this test, UserId and ProductId may be different. For each SQL Query it's doing, it's also inserting new rows in the table. The table starts with 0 rows for the first SQL query above, ends with 35 000 rows. Execution for both Editions below is exactly the same (same data inserts)

The graph below is showing the time used in milliseconds (y axis) for each query (x axis) - both editions is doing the exactly the same query but with different indexes.

URL to graph: http://www.lostfields.com/sqlindexing.gif

Edition 2 has the following priority on the PK: UserId, LocationId, ProductId, BatchId
Edition 2 Optimized has the following priority on the PK: UserId, ProductId, BatchId, LocationId.

How come Edition 2 have to scan over a lot more indexes than Edition 2 Optimized? As I can see it this shouldn't have happened since LocationId = 1 all the time. Or am I missing something?

[edit] <img> tag didn't work so I have to just paste the url

What happens if you put locationId = 1 in your query. Its all about selectivity.

What other columns are on the table. I would also look at what happens if you include userid in the group by.

Can you capture the two query plans.

|||

Yes, thank you

It did help a lot to include LocationId = 1 in the query, when I did this in Edition 2 it became just similar to Edition 2 Optimized. I couldn't see any performance increase by including UserId in the GROUP BY.

The other fields/columns are just a TransactionDate (date for the insert) and TransactionId (identity to make it unqiue for stopping a duplicate insert).

The two Execution Plans can be found at http://www.lostfields.com/sqlindexing_plan.gif

I'm not sure why Edition 2 Optimized has a sort method there though, as Edition 2 doesn't, but have a filter (since LocationId isn't in where clause I guess).

Different @@version

I have 2 machines, both that I _thought_ were loaded with the same
version, but when I do select @.@.version on the two I get different
results:
1.
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Cop
yright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows
NT 5.2 (Build 3790: )
2.
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Cop
yright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows
NT 5.2 (Build 3790: Service Pack 1)
Notice how 1 doesn't say Service Pack 1. Both should have SP4 on them.
WHat is the difference?
Darin
*** Sent via Developersdex http://www.codecomments.com ***> Notice how 1 doesn't say Service Pack 1.
That is the service pack of the operating system, not the SQL Server instanc
e. The interesting part
is:
> Microsoft SQL Server 2000 - 8.00.2039
You can now map the build number to the service pack (Aaron has an article o
n this at
www.aspfaq.com). You can also use the SERVERPROPERTY() function to get the s
ervice pack from your
SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Darin" <darin_nospam@.nospamever> wrote in message news:OV95wYneGHA.4840@.TK2MSFTNGP03.phx.gb
l...
>I have 2 machines, both that I _thought_ were loaded with the same
> version, but when I do select @.@.version on the two I get different
> results:
> 1.
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
> May 3 2005 23:18:38
> Cop
> yright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows
> NT 5.2 (Build 3790: )
> 2.
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
> May 3 2005 23:18:38
> Cop
> yright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows
> NT 5.2 (Build 3790: Service Pack 1)
> Notice how 1 doesn't say Service Pack 1. Both should have SP4 on them.
> WHat is the difference?
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***

Different @@version

I have 2 machines, both that I _thought_ were loaded with the same
version, but when I do select @.@.version on the two I get different
results:
1.
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Cop
yright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows
NT 5.2 (Build 3790: )
2.
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Cop
yright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows
NT 5.2 (Build 3790: Service Pack 1)
Notice how 1 doesn't say Service Pack 1. Both should have SP4 on them.
WHat is the difference?
Darin
*** Sent via Developersdex http://www.developersdex.com ***> Notice how 1 doesn't say Service Pack 1.
That is the service pack of the operating system, not the SQL Server instance. The interesting part
is:
> Microsoft SQL Server 2000 - 8.00.2039
You can now map the build number to the service pack (Aaron has an article on this at
www.aspfaq.com). You can also use the SERVERPROPERTY() function to get the service pack from your
SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Darin" <darin_nospam@.nospamever> wrote in message news:OV95wYneGHA.4840@.TK2MSFTNGP03.phx.gbl...
>I have 2 machines, both that I _thought_ were loaded with the same
> version, but when I do select @.@.version on the two I get different
> results:
> 1.
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
> May 3 2005 23:18:38
> Cop
> yright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows
> NT 5.2 (Build 3790: )
> 2.
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
> May 3 2005 23:18:38
> Cop
> yright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows
> NT 5.2 (Build 3790: Service Pack 1)
> Notice how 1 doesn't say Service Pack 1. Both should have SP4 on them.
> WHat is the difference?
> Darin
> *** Sent via Developersdex http://www.developersdex.com ***

differenct between 2 select statments

I have 2 table (table1 & table2) where both contain documentpackageid column. In the first table I have a select:

SELECT documentpackageid FROM table1 WHERE xxx=94

The second table:

SELECT documentpackageid FROM table2 WHERE yyy<>10

Now I want the different between that 2 select statement. So that I have all documentpackageid from the first select - documentpakcageid from the second select

I have tried with:
SELECT documentpackageid FROM (SELECT documentpackageid FROM table1 WHERE xxx=94) AS foo WHERE foo.documentpackageoid !IN (SELECT documentpackageid FROM table2 WHERE yyy<>10);On Oracle the answer is:
SELECT documentpackageid FROM table1 WHERE xxx=94
MINUS
SELECT documentpackageid FROM table2 WHERE yyy<>10
Some DBMSs have "EXCEPT" instead of "MINUS".

If your DBMS has neither you can do this:

SELECT documentpackageid FROM table1 WHERE xxx=94
AND NOT EXISTS
( SELECT null FROM table2 WHERE yyy<>10
AND table2.documentpackageid = table2.documentpackageid)|||Hi, try this

select t1.documentpackageid -t2.documentpackageid from table1 t1,
table2 t2 where t1.xxx=94 and t2.yyy<>10

Madhivanan|||tony, mysql has neither EXCEPT nor MINUS

and versions prior to 4.1 don't even have subselects!!

here is another solution to this problem --select table1.documentpackageid
from table1
left outer
join table2
on table1.documentpackageid
= table2.documentpackageid
and table2.yyy <> 10
where table1.xxx = 94
and table2.documentpackageid is null

differences in SSIS file locations in SQL Server Agent step

When adding an SSIS step to a SQL Server Agent job, when selecting the location of a config file, the dialog lets you select from the database server you're working with. If selecting the location of the package itself (when the source is File System), the dialog lets you select from the machine where Management Studio is sitting instead of from the database server. Is that intentional? And if so, why? Should I just use a fully qualified file name for the package location rather than one using a drive letter?
Hmmm... Yeah, I don't like that. Though I always deploy by storing the packages in SQL Server versus the file system.

Let's let some of the other guys chime in.... I do know that some of the folks around here never use the SSIS step type in Agent and instead use the Command Line type. That allows them to build the DTEXEC statement the way they need it to be.|||

Phil Brammer wrote:

I do know that some of the folks around here never use the SSIS step type in Agent and instead use the Command Line type. That allows them to build the DTEXEC statement the way they need it to be.

That's might be the way I go. In production we'll be using Control-M anyway, so I might as well just have the command-line all prepped and ready to go for that.
|||

Phil Brammer wrote:

Let's let some of the other guys chime in.... I do know that some of the folks around here never use the SSIS step type in Agent and instead use the Command Line type. That allows them to build the DTEXEC statement the way they need it to be.

And it lets us get the full output of DTEXEC, which is handy for troubleshooting.