Different results for MDX queries when using Attribute Hierarchies

We receive different results for the follow 2 MDX expressions. The only difference is that the second parameter in the Where clause uses a separate dimension called Asset Class in the first query, whereas in the first it uses an Attribute Hierarchy dimension on the Asset dimension.

The first provides the expected results which is the top 10 Equity assets, whereas the second returns just 3 Equity assets which belong to the top 10 assets overall.

Can anyone explain this? Using a cross join in the Topcount function works, but unfortunately ProClarity which we are using does not deal with this properly.

Query 1

SELECT NON EMPTY { [Measures].[Value Base] } ON COLUMNS ,

NON EMPTY { TOPCOUNT( { [Asset].[Asset].[All].CHILDREN }, 10, ( [Measures].[Value Base] ) ) } ON ROWS

FROM [MIQB Daily]

WHERE ( [Period].[Month].&[2005-11-01T00:00:00], [Asset Class].[Asset Class Category].&[Equity])

Query 2

SELECT NON EMPTY { [Measures].[Value Base] } ON COLUMNS ,

NON EMPTY { TOPCOUNT( { [Asset].[Asset].[All].CHILDREN }, 10, ( [Measures].[Value Base] ) ) } ON ROWS

FROM [MIQB Daily]

WHERE ( [Period].[Year Month Hierarchy].[Month].&[2005-11-01T00:00:00], [Asset].[Asset Class Hierarchy].[Asset Class Category].&[Equity )

At first sight this might be an issue with your attribute relationships. Have you looked into that?|||

Yes we believe the relations have been set up correctly and the indicator on the hierarchy has turned green.

We think it is because the hierarchy in the Where clause is in the same dimension as the hierarchy in the Topcount function in the second case - possibly something to do with the auto exists?

Interestingly if you use the browser in the Dev Studio and filter on the [Asset].[Asset Class Hierarchy] in the sepate Filter pane, then doing a top 10 query works fine, but if you put the filter on the page section of the browser, it does not produce the correct results.

|||

Can you "translate" this to an Adventure Works cube? Do you get the same results if you run the two queries in management studio?

Regards

/Thomas

|||This is a known bug that Microsoft is fixing (see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=549706&SiteID=1). Should get it by the end of August. It is a major fix that will be backported to SP1 (as it required some fixes from the SP2 branch).|||

Many thanks for your post - we were worried that it might have been a 'feature' rather than a bug

Paul

|||We are testing the fix now and the results look promising.

Different results - Not Exists Vs Not In

The following example results to differnet results depending upon whether I'
m
using Not exists or Not in. I'm sdoing the same thing in 2 different ways.
Is there an explanation for this or is this a bug in SQL Server?
Current Version SQL 2000 SP3a
Example:
Declare @.temp1
table (
id int
)
Insert into @.temp1 (id) values (1)
Insert into @.temp1 (id) values (2)
Insert into @.temp1 (id) values (3)
Insert into @.temp1 (id) values (4)
-- Expected anawer 101,102,103,104
-- returns correct
Select
*
From
@.temp1 t1x
Where (100+t1x.id) NOT IN ( Select
t1y.id
from
@.temp1 t1y )
-- returns "NOTHING"
Select
*
From
@.temp1 t1x
Where
NOT EXISTS (Select
t1y.id
from
@.temp1 t1y
Where
t1y.id != (100+t1x.id) )Change the comparison expression from second query.

> Where
> t1y.id != (100+t1x.id) )
Where
t1y.id = (100+t1x.id) )
AMB
"Core" wrote:

> The following example results to differnet results depending upon whether
I'm
> using Not exists or Not in. I'm sdoing the same thing in 2 different ways.
> Is there an explanation for this or is this a bug in SQL Server?
> Current Version SQL 2000 SP3a
> Example:
> Declare @.temp1
> table (
> id int
> )
> Insert into @.temp1 (id) values (1)
> Insert into @.temp1 (id) values (2)
> Insert into @.temp1 (id) values (3)
> Insert into @.temp1 (id) values (4)
> -- Expected anawer 101,102,103,104
> -- returns correct
> Select
> *
> From
> @.temp1 t1x
> Where (100+t1x.id) NOT IN ( Select
> t1y.id
> from
> @.temp1 t1y )
> -- returns "NOTHING"
> Select
> *
> From
> @.temp1 t1x
> Where
> NOT EXISTS (Select
> t1y.id
> from
> @.temp1 t1y
> Where
> t1y.id != (100+t1x.id) )
>|||I think you intended your second example to be:
Select
*
From
@.temp1 t1x
Where
NOT EXISTS (Select
t1y.id
from
@.temp1 t1y
Where
t1y.id = (100+t1x.id) )
Result:
id
1
2
3
4
(4 row(s) affected)
However, the two queries are still not logically equivalent. Insert a
NULL in the table and you'll see what I mean.
David Portas
SQL Server MVP
--|||Thanks Alejandro,
I feel stupid. I had starred at this problem for 30 minutes.
"Alejandro Mesa" wrote:
> Change the comparison expression from second query.
>
> Where
> t1y.id = (100+t1x.id) )
>
> AMB
> "Core" wrote:
>|||I have been there too.
AMB
"Core" wrote:
> Thanks Alejandro,
> I feel stupid. I had starred at this problem for 30 minutes.
>
> "Alejandro Mesa" wrote:
>

Different results

I am trying to return a two character result for the day number and was
wondering why I get a one character day returned in Statement A, and a two
character result in Statement B?
declare @.day varchar(2)
--Statement A:
set @.day = case len(day(getdate()))
when 1 then cast('0' + cast (day(getdate()) as varchar(1)) as
varchar(2))
else
day(getdate())
end
print @.day
--the result is a one character day, if the date was February 7, 2005, the
result = 7
--Statement B:
if len(day(getdate())) = 1
set @.day = '0' + cast (day(getdate()) as varchar(1))
else
set @.day = day(getdate())
print @.day
--the result is a two character day, if the date was February 7, 2005, the
result = 07
Message posted via http://www.webservertalk.comThis is because of implicit conversion and datatype precedence for case/when
statement. Your 'else' clause has higher precedence (INT). Thus, your TRUE
(varchar(2)) clause has to be implicitly converted to INT (i.e. '07' -> 7).
This is the fix.
declare @.day varchar(2)
--Statement A:
set @.day = case len(day(getdate()))
when 1 then cast('0' + cast (day(getdate()) as varchar(1)) as
varchar(2))
else
cast(day(getdate()) as varchar(2)) --explicit conversion to
varchar
end
print @.day
And here is a trick without case/when or if/else:
e.g.
set @.day = right(day(getdate())+100,2)
print @.day
-oj
"Robert Richards via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:79f6ea5cca2a4a679dea642da149fc43@.SQ
webservertalk.com...
>I am trying to return a two character result for the day number and was
> wondering why I get a one character day returned in Statement A, and a two
> character result in Statement B?
> declare @.day varchar(2)
> --Statement A:
> set @.day = case len(day(getdate()))
> when 1 then cast('0' + cast (day(getdate()) as varchar(1)) as
> varchar(2))
> else
> day(getdate())
> end
> print @.day
> --the result is a one character day, if the date was February 7, 2005, the
> result = 7
> --Statement B:
> if len(day(getdate())) = 1
> set @.day = '0' + cast (day(getdate()) as varchar(1))
> else
> set @.day = day(getdate())
> print @.day
> --the result is a two character day, if the date was February 7, 2005, the
> result = 07
> --
> Message posted via http://www.webservertalk.com|||The answer - don't use implicit conversions. And read BOL about the CASE
expression and how it determines the datatype of the returned value. Below
is a quick script that demonstrates two much easier ways to accomplish the
task.
declare @.test datetime
set @.test = '20050115'
select '0' + cast(datepart(day, @.test) as varchar(2))
,RIGHT('0' + cast(datepart(day, @.test) as varchar(2)), 2)
,convert(char(2), @.test, 4)
set @.test = '20050102'
select '0' + cast(datepart(day, @.test) as varchar(2))
,RIGHT('0' + cast(datepart(day, @.test) as varchar(2)), 2)
,convert(char(2), @.test, 4)
"Robert Richards via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:79f6ea5cca2a4a679dea642da149fc43@.SQ
webservertalk.com...
> I am trying to return a two character result for the day number and was
> wondering why I get a one character day returned in Statement A, and a two
> character result in Statement B?
> declare @.day varchar(2)
> --Statement A:
> set @.day = case len(day(getdate()))
> when 1 then cast('0' + cast (day(getdate()) as varchar(1)) as
> varchar(2))
> else
> day(getdate())
> end
> print @.day
> --the result is a one character day, if the date was February 7, 2005, the
> result = 7
> --Statement B:
> if len(day(getdate())) = 1
> set @.day = '0' + cast (day(getdate()) as varchar(1))
> else
> set @.day = day(getdate())
> print @.day
> --the result is a two character day, if the date was February 7, 2005, the
> result = 07
> --
> Message posted via http://www.webservertalk.com|||Robert,
The diff are here:
(A)
> when 1 then cast('0' + cast (day(getdate()) as varchar(1)) as
(B)
> set @.day = '0' + cast (day(getdate()) as varchar(1))
In 'A' you are casting the whole expression to varchar(1), that is why you
get 1 character.
Another way of doing this is:
set @.day = right('0' + ltrim(day(getdate())), 2)
AMB
"Robert Richards via webservertalk.com" wrote:

> I am trying to return a two character result for the day number and was
> wondering why I get a one character day returned in Statement A, and a two
> character result in Statement B?
> declare @.day varchar(2)
> --Statement A:
> set @.day = case len(day(getdate()))
> when 1 then cast('0' + cast (day(getdate()) as varchar(1)) as
> varchar(2))
> else
> day(getdate())
> end
> print @.day
> --the result is a one character day, if the date was February 7, 2005, the
> result = 7
> --Statement B:
> if len(day(getdate())) = 1
> set @.day = '0' + cast (day(getdate()) as varchar(1))
> else
> set @.day = day(getdate())
> print @.day
> --the result is a two character day, if the date was February 7, 2005, the
> result = 07
> --
> Message posted via http://www.webservertalk.com
>|||I am completely wrong. I missed the "as varchar(2)" part. OJ and Scott post
s
explain the problem correctly.
AMB
"Alejandro Mesa" wrote:
> Robert,
> The diff are here:
> (A)
> (B)
> In 'A' you are casting the whole expression to varchar(1), that is why you
> get 1 character.
> Another way of doing this is:
> set @.day = right('0' + ltrim(day(getdate())), 2)
>
> AMB
>
> "Robert Richards via webservertalk.com" wrote:
>

Different result when using Indexing Service Query Form and SQL Server

When I use the "Indexing Service Query Form", searching documents for
the word "D=E9sir=E9", I get 50 matched records.
If I run the following query from SQL-Server 2005 (on the same machine)
:
SELECT * FROM
OPENQUERY
(CVCATALOG,
'SELECT FILENAME, RANK FROM SCOPE()
WHERE CONTAINS(''"d=E9sir=E9"'')
')
I only get 45 results.
This seems to be linked to the special characters (=E9) since it gives
the same result if I specify a word without accents.
Any idea?
By default IIRC SQL Server 2005 is accent insensitive whereas indexing
services is not. You have the option of making your catalog accent
sensitive.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<pierre.laduron@.thalesgroup.com> wrote in message
news:1160492048.051686.161080@.i3g2000cwc.googlegro ups.com...
When I use the "Indexing Service Query Form", searching documents for
the word "Dsir", I get 50 matched records.
If I run the following query from SQL-Server 2005 (on the same machine)
:
SELECT * FROM
OPENQUERY
(CVCATALOG,
'SELECT FILENAME, RANK FROM SCOPE()
WHERE CONTAINS(''"dsir"'')
')
I only get 45 results.
This seems to be linked to the special characters () since it gives
the same result if I specify a word without accents.
Any idea?
|||Hello Hilary,
Thanks for your quick reply. This seems to be an interesting track.
Can you tell me how I can make the catalog accent sensitive?
Thanks again,
Pierre
Hilary Cotter wrote:
> By default IIRC SQL Server 2005 is accent insensitive whereas indexing
> services is not. You have the option of making your catalog accent
> sensitive.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <pierre.laduron@.thalesgroup.com> wrote in message
> news:1160492048.051686.161080@.i3g2000cwc.googlegro ups.com...
> When I use the "Indexing Service Query Form", searching documents for
> the word "D=E9sir=E9", I get 50 matched records.
> If I run the following query from SQL-Server 2005 (on the same machine)
> :
> SELECT * FROM
> OPENQUERY
> (CVCATALOG,
> 'SELECT FILENAME, RANK FROM SCOPE()
> WHERE CONTAINS(''"d=E9sir=E9"'')
> ')
> I only get 45 results.
> This seems to be linked to the special characters (=E9) since it gives
> the same result if I specify a word without accents.
> Any idea?
|||use the alter fulltext catalog statement. Here is an example.
Warning - this will entail a catalog rebuild.
Create database fulltext
GO
create fulltext catalog testcatalog
GO
--this may return an erroneous result - consult
http://support.microsoft.com/?kbid=910067 for more info
select fulltextcatalogproperty('testcatalog','isaccentsen sitive')
GO
select is_accent_sensitivity_on from sys.fulltext_catalogs where
name='testcatalog'
GO
--setting it off
alter fulltext catalog testcatalog
rebuild with ACCENT_SENSITIVITY =OFF
GO
select fulltextcatalogproperty('testcatalog','isaccentsen sitive')
GO
select is_accent_sensitivity_on from sys.fulltext_catalogs where
name='testcatalog'
GO
--setting it on
alter fulltext catalog testcatalog
rebuild with ACCENT_SENSITIVITY =ON
GO
select fulltextcatalogproperty('testcatalog','isaccentsen sitive')
GO
select is_accent_sensitivity_on from sys.fulltext_catalogs where
name='testcatalog'
GO
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<pierre.laduron@.thalesgroup.com> wrote in message
news:1160557828.399727.117000@.c28g2000cwb.googlegr oups.com...
Hello Hilary,
Thanks for your quick reply. This seems to be an interesting track.
Can you tell me how I can make the catalog accent sensitive?
Thanks again,
Pierre
Hilary Cotter wrote:
> By default IIRC SQL Server 2005 is accent insensitive whereas indexing
> services is not. You have the option of making your catalog accent
> sensitive.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <pierre.laduron@.thalesgroup.com> wrote in message
> news:1160492048.051686.161080@.i3g2000cwc.googlegro ups.com...
> When I use the "Indexing Service Query Form", searching documents for
> the word "Dsir", I get 50 matched records.
> If I run the following query from SQL-Server 2005 (on the same machine)
> :
> SELECT * FROM
> OPENQUERY
> (CVCATALOG,
> 'SELECT FILENAME, RANK FROM SCOPE()
> WHERE CONTAINS(''"dsir"'')
> ')
> I only get 45 results.
> This seems to be linked to the special characters () since it gives
> the same result if I specify a word without accents.
> Any idea?

Different Result Same Sql Queries

I wrote 2 sql queries in two different ways.
The only difference in both queries are One using '=' and other one using 'in'.
All the other data is same.In my sense '=' and 'in' same
Both queries should give same result.
But one query giving different result and other query giving different result.
Is this 2 queries different?

SELECT sum(SALES_ITEM.NET_PRICE)
FROM PRODUCT_GROUP_LINK,
PRODUCT,
SALES_ITEM
WHERE (PRODUCT_GROUP_LINK.PRODUCT_CODE=PRODUCT.PRODUCT_CODE)
 AND (PRODUCT_GROUP_LINK.PRODUCT_CODE=SALES_ITEM.ORIG_PRODUCT)
 AND PRODUCT.STYLE_CODE='MC001'
 AND (SALES_ITEM.PRICE_LIST='1FAC'
 OR SALES_ITEM.PRICE_LIST='1FAW' )
 AND CLASS_CODE='TYPE'
group by STYLE_CODE
SELECT sum(sales_item.net_price)
from PRODUCT,
product_group_link,
SALES_ITEM
WHERE (PRODUCT_GROUP_LINK.PRODUCT_CODE=PRODUCT.PRODUCT_CODE)
AND (PRODUCT_GROUP_LINK.PRODUCT_CODE=SALES_ITEM.ORIG_PRODUCT)
AND PRODUCT.STYLE_CODE='MC001'
AND sales_item.PRICE_LIST IN ('1FAC','1FAW')
AND CLASS_CODE='TYPE'
group by style_code

Any idea?
Thanks

Answer is very simple

IN means OR not AND (i.e. either of the vlaues)

|||

sorry .every thing is fine in queries.

I spelt wrong in some price lists.

|||

You might spelt it wrong but in any case IN is equal to OR not AND... is it not like that?

regards

Anas

|||

yes.IN is not AND.

IN is equal to OR.

I placed correctly in my query.There is no problem in my query.

The problem only with spelling

Different result running SQL query directly or using SqlCommand

Hi, when running the following stored procedure:

ALTER PROCEDURE [dbo].[GetWerknemersBijLeidinggevende]
@.LeidinggevendeIDint,
@.Startint = 1,
@.Limitint = 25,
@.Sofinummerint =NULL,
@.Achternaamnvarchar(128) =NULL,
@.Functienvarchar(64) =NULLAS
WITH OrderedAS(SELECTROW_NUMBER()OVER (ORDER BY Achternaam)AS RowNumber,
Persoon.*
FROM Persoon
INNERJOIN Dienstverband
ON Persoon.ID = Dienstverband.PersoonID
INNERJOIN Bedrijfsonderdeel
ON Bedrijfsonderdeel.ID = Dienstverband.BedrijfsonderdeelID
INNERJOIN Leidinggevende
ON Bedrijfsonderdeel.ID = Leidinggevende.BedrijfsonderdeelID
WHERELeidinggevende.Begindatum <=getdate()
AND
(
Leidinggevende.Einddatum >getdate()
OR Leidinggevende.EinddatumISNULL)AND Leidinggevende.PersoonID = @.LeidinggevendeID
AND(Sofinummer = @.SofinummerOR @.SofinummerISNULL)AND(AchternaamLIKE @.Achternaam
OR AchternaamPartnerLIKE @.Achternaam
OR @.AchternaamISNULL))SELECT *
FROM Ordered
WHERE RowNumberbetween @.Startand (@.Start + @.Limit - 1)

When I run this in the database and fille de LeidinggevendeID parameter with a value I get a few rows returned, however when I run the following code:

[DataObject(true)]
public class PersoonFactory
 {
 [DataObjectMethod(DataObjectMethodType.Select,false)]
public static IList WerknemersBijLeidinggevende(int ldgID,int start,int max)
 {
 IList list =new List();
 SqlDataReader rdr =null;
 SqlConnection connection = DatabaseProvider.Connection;
 SqlCommand command =new SqlCommand("GetWerknemersBijLeidinggevende", connection);
 command.Parameters.AddWithValue("LeidinggevendeID", ldgID);
 command.CommandType = CommandType.StoredProcedure;
try { connection.Open(); rdr = command.ExecuteReader(CommandBehavior.CloseConnection);while (rdr.Read())
 {
 Persoon pers =new Persoon();
 pers.ID = rdr["ID"]as int?;
 pers.Achternaam = rdr["Achternaam"]as string;
 pers.AchternaamPartner = rdr["AchternaamPartner"]as string;
 pers.Achtertitels = rdr["Achtertitels"]as string;
 pers.DatumOverlijden = rdr["DatumOverlijden"]as DateTime?;
 pers.Geboortedatum = rdr["Geboortedatum"]as DateTime?;
 pers.Geslacht = rdr["Geslacht"]as string;
 pers.Middentitels = rdr["Middentitels"]as string;
 pers.Naamgebruik = (int)rdr["Naamgebruik"];
 pers.Sofinummer = rdr["Sofinummer"]as string;
 pers.Voorletters = rdr["Voorletters"]as string;
 pers.Voortitels = rdr["Voortitels"]as string;
 pers.Voorvoegsel = rdr["Voorvoegsel"]as string;
 pers.VoorvoegselPartner = rdr["VoorvoegselPartner"]as string;
 list.Add(pers);
 }
 }
catch {throw;
 }
finally {if (rdr !=null) rdr.Close();
else connection.Close();
 }
return list;
 }

I get 0 rows all of a sudden. Any idea why?

Your code works fine in my testing. You may need to capture a SQL Profiler trace to see what has been passed to SQL Server in the call of the SP, and what has been returned from SQL Server.

BTW, I notice in the SP you compare a column with a parameter in this way:

Sofinummer = @.Sofinummer
OR @.SofinummerISNULL

Since the @.Sofinummer can be null, so when both Sofinummer and @.Sofinummer are null, the expression "Sofinummer = @.Sofinummer" will return true if ANSI_NULLS option is off; and will return false when ANSI_NULLS if on. This won't cause confusion in this procedure since you add a "OR @.SofinummerISNULL" to deal with the null situation. However, we can use a ISNULL function to avoid such potential confusing result:

Sofinummer = ISNULL(@.Sofinummer,Sofinummer)

|||

I always thought that the original posters way was the better approach since one half of x=@.x OR @.x IS NULL can be quickly optimized out of the query (and not need to be evaluated on each row) since one half of it can be determined before the query is even executed and simplified, or isn't SQL Server that intelligent yet? That is unless SQL Server has been specifically told that x=ISNULL(@.x,x) is a special case, and also knows how to optimize that out of the query when @.x is/is not NULL.

For example

DECLARE @.x varchar(20)

SET @.x=NULL

SELECT *

FROM MyTable

WHERE (x=@.x OR @.x IS NULL)

without even executing the query, the where clause could be optimized to WHERE ((x=@.x) OR NULL IS NULL) -> WHERE ((x=@.x) OR TRUE) -> WHERE TRUE.

In the case @.x=1 it could be optimized to: WHERE ((x=@.x) OR 1 IS NULL) -> WHERE ((x=@.x) OR FALSE) -> WHEREx=@.x

|||

"Your code works fine in my testing. You may need to capture a SQL Profiler trace to see what has been passed to SQL Server in the call of the SP, and what has been returned from SQL Server."

Thanks for the reply. Well, since I am rather new to MS SQL (come from a MySQL background): How do I capture a SQL Profiler trace? Could you refer me to any documentation regarding this procedure?

Regards,

Dirk Louwers

|||

Motley:

I always thought that the original posters way was the better approach since one half of x=@.x OR @.x IS NULL can be quickly optimized out of the query (and not need to be evaluated on each row) since one half of it can be determined before the query is even executed and simplified, or isn't SQL Server that intelligent yet? That is unless SQL Server has been specifically told that x=ISNULL(@.x,x) is a special case, and also knows how to optimize that out of the query when @.x is/is not NULL.

Yes, I agree with you on this What I mean is that usingx=@.x where both x and @.x can be null may lead to nondeterministic result, based on the ANSI_NULLS setting.

Actually using x=ISNULL(@.x,x) will always add a nested loop in the execution plan so the performance may drop down when the table is huge. However when using "x=@.x OR @.x IS NULL", if @.x is null, SQL Server is not aware that actually the parameters are NULL and such clause won't filter any record, thus in the execution plan it may still uses index/table scan [+nested loop (if the query joins mutilple tables)] to retrieve the record.

To get max performance for such query, the best way it to execute different statements according to whether the parameters are null.If queries with such searching criterials are in a Stored Procedure, sometimes we can improve the performance by simply passing the values of the parameters to local variables defined in the SP, and then use the local variables in the queries. We have discussed such issue in a post In SQL2005, we have a new option new option for optimizing queries--Plan Guides. I have updated the post, please check it:

http://forums.asp.net/2/1377161/ShowThread.aspx

|||

Dirk_L:

Thanks for the reply. Well, since I am rather new to MS SQL (come from a MySQL background): How do I capture a SQL Profiler trace? Could you refer me to any documentation regarding this procedure?

Go to All Programms->Microsoft SQL Server2005->Performance Tools->SQL Server Profiler. You have to add Events and DataColumns according to what you want to trace. Since we just want to see what's happened when the stored procedure is executed, just start a trace using the TSQL_SPs template. To learn more about SQL Profiler, you can start from here:

http://msdn2.microsoft.com/en-us/library/ms187929.aspx

|||

Unfortunately I am using SQL Server 2005 Express on my development machine and the profiler doesn't seem to be included. Is there another options available other than moving the whole development project over to the production server and run it there?

|||If you have SQL2005 on your production server, you can use the Profiler on the production server to capture trace on your development server. If you don't have Profiler, you can print the SQL command just before you executing it to see whether it looks like you expected.

different result in Windows 2003 Server as in Windows 2000

I receive different results with SQL Server on a Windows 2003 Server then on
a Windows 2000 Desktop.
I detached the Database on the Windows 2000 Machine and attached exactly the
same database on the Windows 2003 Server, to make shure that the data are
100% the same. And I still get a different result with exact the same query.
As I run into the problem the first time I imported the data with DTS into
each database. To exclude the posibility that with the import was something
wrong I copied the database as described above.
The Query is executed in the SQL Query Analyser!
The query includes 5 tables and views linked with left joins.
There is no Group by in the statement!
The envoirnments are:
Windows 2003 Server (German)
SQL Server 2000 Enterprise Edition + SP3a
Reporting Services Enterprise Edition + SP2
Windows 2000 SP4
SQL Server 2000 SP3
Reporting Services Enterprise Edition + SP1
.NET 2003
I reinstalled both clients from scratch. There is nothing esle installed as
necesarry to run Reporting Services. On the 2003 Server .NET is NOT installe
d.
And just for the sake of completness: this is no joke and I have more then 5
years expirience in database programming with SQL Server and Oracle. I have
the courage to write this here only becouse I read about a similare problem
related with "group by". Apart from this I don't trust my selfe anymore sinc
e
this happen.
Has somebody any clue to this.If you post the actual query and give some more detail about what the
difference was we might be able to speak more intelligently about the issue.
Andrew J. Kelly SQL MVP
"Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
news:9D4C3B1C-E9F3-4B85-AF70-61550300C49E@.microsoft.com...
>I receive different results with SQL Server on a Windows 2003 Server then
>on
> a Windows 2000 Desktop.
> I detached the Database on the Windows 2000 Machine and attached exactly
> the
> same database on the Windows 2003 Server, to make shure that the data are
> 100% the same. And I still get a different result with exact the same
> query.
> As I run into the problem the first time I imported the data with DTS into
> each database. To exclude the posibility that with the import was
> something
> wrong I copied the database as described above.
> The Query is executed in the SQL Query Analyser!
> The query includes 5 tables and views linked with left joins.
> There is no Group by in the statement!
> The envoirnments are:
> Windows 2003 Server (German)
> SQL Server 2000 Enterprise Edition + SP3a
> Reporting Services Enterprise Edition + SP2
> Windows 2000 SP4
> SQL Server 2000 SP3
> Reporting Services Enterprise Edition + SP1
> .NET 2003
> I reinstalled both clients from scratch. There is nothing esle installed
> as
> necesarry to run Reporting Services. On the 2003 Server .NET is NOT
> installed.
> And just for the sake of completness: this is no joke and I have more then
> 5
> years expirience in database programming with SQL Server and Oracle. I
> have
> the courage to write this here only becouse I read about a similare
> problem
> related with "group by". Apart from this I don't trust my selfe anymore
> since
> this happen.
> Has somebody any clue to this.|||Sorry, I forgot the important part of the message.
Meanwhile I enclosed the problem to a dateformat problem. But not as simple
as it sounds like.
I reduced the query to one table, for which a date criteria is included in
the query.
Select * from Activities Where Activities.Date = '2006-03-06'
(Date is the name of the field for historic reasons and this can't be change
d)
The 2003 Srv interpreds the date YYYY-DD-MM and the 2000 Desktop interprets
it as YYYY-MM-DD. The language settings are the same, both are set to german
and both have the same dateformat in the control panel language settings.
If I change the language settings on the 2003 Srv to English(USA) the date
in the criteria get still interpreted as YYYY-DD-MM, even the language
settings in control panel display the format M/D/YYYY.
If I'm applay '2006-28-02' Query Analyser returns an error. If I use
2006-02-28 the query get execudet an the result comply with YYYY-DD-MM.
"Andrew J. Kelly" wrote:

> If you post the actual query and give some more detail about what the
> difference was we might be able to speak more intelligently about the issu
e.
> --
> Andrew J. Kelly SQL MVP
>
> "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> news:9D4C3B1C-E9F3-4B85-AF70-61550300C49E@.microsoft.com...
>
>|||A perfect example of why you should use the ISO or ANSI dateformats. These
should give you all the details:
http://www.karaszi.com/SQLServer/info_datetime.asp
Guide to Datetimes
http://www.sqlservercentral.com/col...sqldatetime.asp
Datetimes
http://www.murach.com/books/sqls/article.htm
Datetime Searching
Andrew J. Kelly SQL MVP
"Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
news:05B6397D-3B11-48D2-BEAE-8222E8C971FE@.microsoft.com...
> Sorry, I forgot the important part of the message.
> Meanwhile I enclosed the problem to a dateformat problem. But not as
> simple
> as it sounds like.
> I reduced the query to one table, for which a date criteria is included in
> the query.
> Select * from Activities Where Activities.Date = '2006-03-06'
> (Date is the name of the field for historic reasons and this can't be
> changed)
> The 2003 Srv interpreds the date YYYY-DD-MM and the 2000 Desktop
> interprets
> it as YYYY-MM-DD. The language settings are the same, both are set to
> german
> and both have the same dateformat in the control panel language settings.
> If I change the language settings on the 2003 Srv to English(USA) the date
> in the criteria get still interpreted as YYYY-DD-MM, even the language
> settings in control panel display the format M/D/YYYY.
> If I'm applay '2006-28-02' Query Analyser returns an error. If I use
> 2006-02-28 the query get execudet an the result comply with YYYY-DD-MM.
>
> "Andrew J. Kelly" wrote:
>|||Thank you Andrew!
I thought the format YYYY-MM-DD address this. The mistake was the "-".
(Should get some sleep, before posting into the newsgroups ;-)
"Andrew J. Kelly" wrote:

> A perfect example of why you should use the ISO or ANSI dateformats. Thes
e
> should give you all the details:
> http://www.karaszi.com/SQLServer/info_datetime.asp
> Guide to Datetimes
> http://www.sqlservercentral.com/col...sqldatetime.asp
> Datetimes
> http://www.murach.com/books/sqls/article.htm
> Datetime Searching
>
> --
> Andrew J. Kelly SQL MVP
>
> "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> news:05B6397D-3B11-48D2-BEAE-8222E8C971FE@.microsoft.com...
>
>

Different result in SP vs Query Analyzer

I'm getting some very odd behaviour which I can currently
duplicate. One of my SPs fails when it can't find a
record. I copy the SQL from the SP and execute it via
query analyzer and ta-da, the record appears.
Further investigation shows that the sql checks that a
record doesn't have a "completed" status. Comment this
out and the SP works fine.
Continuing the investigation, the developers run this SP
through debug and find the value being returned
= 'COMPLETED'. Run the *same* code in query analyzer and
the value being returned is 'PLANNED'... How is this
possible' The sql returns the correct value when run
through query analyzer, but the incorrect value when run
as a part of the SP!!
Below is the 'where' clause:
.
.
.
Where
(vtt.Trip_Id = @.FCTripId)
And (eqm.eqm_sequence_no = 2)
And (cst.cst_consign_status_desc <> 'COMPLETED')
TIA,
SJT> I'm getting some very odd behaviour which I can currently
> duplicate.
Can you give us enough information so we can try to duplicate? Table
schema, sample data, the code for the procedure maybe, desired results. See
http://www.aspfaq.com/5006
> Further investigation shows that the sql checks that a
> record doesn't have a "completed" status.
What datatype is this? How does sql "check"? What is the exact syntax you
are using? What data is actually stored in the column?
> Continuing the investigation, the developers run this SP
> through debug and find the value being returned
> = 'COMPLETED'. Run the *same* code in query analyzer and
> the value being returned is 'PLANNED'... How is this
> possible'
You're looking at a different row? The stored procedure is being executed
against the test database, and query analyzer is connected to production?
> And (cst.cst_consign_status_desc <> 'COMPLETED')
I'm going to guess that (shudder) "cst_consign_status_desc" is a CHAR
column. You should use VARCHAR so that trailing spaces are ignored, and
ensure that ANSI_PADDING is set the same in both environments to ensure that
you get consistent results.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Different result between using query and call from ASP

Dear all,
I have a problem, my developer create a store procedure to calculate an asset depreciation. He got different result when executed it between directly from query windows and called it from an ASP page. The right result is from query windows.
When I checked the script there's no strange syntax and the used parameters were simple, branch_id and period : fa_sp_calc_depre @.branch_id, @.period, simple isn't it ?
Assume the structure of the query like below :

insert into xxx
select ... from (
select ... from (
select ... from tblA
union all
select ... from tblB
) x
union all
select ... from tblC
union all
select ... from tblD
) x
left join tblB on ...

One thing that I suspect is the usage of alias x that repeated more than one. Can we used this structure with same alias in a subquery more than one ?

Best regards,

Hery

If the query executed fine directly then it should not behave differently from your UI.

Can you verify the following items,

1. Connection String - check the server, database name, credentials & etc

2. Parameter - Sometimes programmers set sample param (hard-coded value) & etc.

Finally, you can use the Profiler to profile the query (cross-verify).

|||Hi Manivannan thanks for the reply,
I've checked using Profiler and there's nothing with the query, so yesterday I've changed the alias of first subquery into something else. I checked on my computer and it worked fine with right calculation.

But today I found something weird, when our user do the calculation process she got wrong result again. And when I do the process on myu computer (with same program) I got the right result. It getting me more confused now. FYI our program is under web using ASP classic.

Where's the problem anyway ?

Best regards,

Hery|||Hi,
Is anybody help me on this ?

Best regards,

Hery|||

It really sounds like the two different interfaces are hitting two different servers -dev and prod perhaps.

Check the connection string used by the ASP application.

Is it the same server and database that you connect to using the Query windows?

Also, the aliases should not be causing any issue.

|||

Hi Arnie, thanks for the reply...

I've checked into the data and found that datetime field always changed when I executed the sp. For example, the period parameter is 200705 means month May year 2007, inside the sp it will generate the start date = 05/01/2007, but sometimes the value of start date would be 01/05/2007 and reproduce wrong result. I put set dateformat mdy inside the sp.

The question now is why the value of start date always changing since the syntax set it hard-coded (start_date = right(period,2)+'/01/'+left(period,4) ? I thought its value would be mdy, CMIIW.

Best regards,

Hery

Different Restore Error

I get the following error when I try to restore a database on a SQL 2005 server
TITLE: Microsoft SQL Server Management Studio Express
--
Restore failed for Server '172.27.101.60'. (Microsoft.SqlServer.Express.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
--
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The backup set holds a backup of a database
other than the existing 'Mirror_Test' database.
(Microsoft.SqlServer.Express.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476
--
BUTTONS:
OK
--
--
MG"MGeles" <michael.geles@.thomson.com> wrote in message
news:87ECF046-E857-4682-AF97-8F6425F13AD9@.microsoft.com...
> I get the following error when I try to restore a database on a SQL 2005
server
Is is possible the backup file contains multiple backups within it.
> TITLE: Microsoft SQL Server Management Studio Express
> --
> Restore failed for Server '172.27.101.60'.
(Microsoft.SqlServer.Express.Smo)
> For help, click:
>
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
> --
> ADDITIONAL INFORMATION:
> System.Data.SqlClient.SqlError: The backup set holds a backup of a
database
> other than the existing 'Mirror_Test' database.
> (Microsoft.SqlServer.Express.Smo)
> For help, click:
>
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476
> --
> BUTTONS:
> OK
> --
> --
> MG

Different Restore Error

I get the following error when I try to restore a database on a SQL 2005 ser
ver
TITLE: Microsoft SQL Server Management Studio Express
--
Restore failed for Server '172.27.101.60'. (Microsoft.SqlServer.Express.Smo
)
For help, click:
http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The backup set holds a backup of a database
other than the existing 'Mirror_Test' database.
(Microsoft.SqlServer.Express.Smo)
For help, click:
http://go.microsoft.com/fwlink?Prod...>
LinkId=20476
BUTTONS:
OK
--
MG"MGeles" <michael.geles@.thomson.com> wrote in message
news:87ECF046-E857-4682-AF97-8F6425F13AD9@.microsoft.com...
> I get the following error when I try to restore a database on a SQL 2005
server
Is is possible the backup file contains multiple backups within it.

> TITLE: Microsoft SQL Server Management Studio Express
> --
> Restore failed for Server '172.27.101.60'.
(Microsoft.SqlServer.Express.Smo)
> For help, click:
>
http://go.microsoft.com/fwlink?Prod...r />
kId=20476
> --
> ADDITIONAL INFORMATION:
> System.Data.SqlClient.SqlError: The backup set holds a backup of a
database
> other than the existing 'Mirror_Test' database.
> (Microsoft.SqlServer.Express.Smo)
> For help, click:
>
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476[/ur
l]
> --
> BUTTONS:
> OK
> --
> --
> MG

Different reportparameters for same dimension in different cube

Hi,

I have 1 report with 2 charts, both charts have their own dataset. The two datasets are mdx queries on 2 different cubes, but some dimensions have the same name.

Now I want to have 2 differenent selectable parameters for the [dim time] dimension. One for the first query in the first cube and the second for the other query in the other cube .

So I check in the mdx query builder, both dimensions as parameter, but because both dimensions have the same name, i have only one selectable [dim time] -parameter in my report.

How can i solve this?

Thanks,

Dennis

Dear Dennis

Please help me to pass a parameter thru reportbulder to get drill down from report1 to jump into report2 . I created report1 and report 2. I want to jump into report 2 using parameter . I am getting one error when I run the report1 after giving drill throu in property page of the report in reportBulder

"Query Parameter missing " . Please help me

regards

Polachan

|||

Hi Dennis,

You can solve this by mapping a second parameter to the dataset.

Under Report > Report Parameters, Add a new parameter.

Create a new name and copy over the rest of the information from the parameter created by the MDX designer.

Within the properties of the second dataset (click the ellipses next to the DataSet name), modify the Parameters Value to point at the new parameter you just created.

That should give you two parameters, each mapped to the appropriate dataset.

HTH,

Jessica

Different regional settings discovered in SQL cluster nodes!

We have advertantly failed to verify correct regional settings on both (Win2
003) Cluster nodes prior to installing SQL Server 2000 Ent Ed. SQL Server is
installed in an Active Passive config with System Databases installed to a
shared disk (Drive G Nod
e 1 has US English regional setings (date = mm/dd/yyyy) and US English input
Locale
Node 2 has Australian English regional (date=dd/mm/yyyy) and US English inpu
t locale
Our (mission critical production) database was detached from its old server
(AustralianEnglish regional and US English Locale) and moved to this clust
er 2 days ago. Testing and failovers work fine and the solution went live. W
e have only just discovered
the date discrapency. As the current date is (day & month) is still below 13
, nothing has blown up yet but we are 2 days away from the 13th!
The sysadmins are content to change the regional settings on node 1 to Engli
sh Australian and leave it like that. They failed-over the SQLService over t
o the second node and have scanned the systems databases and tables for date
s showing abnormal (future)
dates and found nothing. They believe there will not be a problem in simply
changing over Node 1's date settings to English Aust.
Sorry for the long note but I need to describe this as clearly as possible.
I am concerned that we have overlooked something. Can anyone tell me if it i
s safe to just change the system regional settings to English Australian? Wi
ll SQL server cope with the
date format change past 13Feb 13/2/2004 in AustEnglish format (dd/mm/yyyy)?
Should we rebuild the node with the correct regional settings and reinstall
SQL server on it? (In order to do this, SQL Server will need to be uninstall
ed from both nodes and the
master db deleted as the master db was created via node 1 US English to star
t with).
Thanks.
NRAs long as you have changed regional settings and you do not have code that
uses set dateformat otherwise this willbe ok. No datetime issues will be exp
erienced thereafter.|||Thanks for your reply, Olu. We did the change but found that SQLServer still
had problems on that node, and this was after we also fixed up the user pro
files to ensure the regional settings were correctly set.
We are attempting tonight to uninstall SQL reinstall, this time from the sec
ond node which was initially set with EngAust regional settings. We'll know
soon if that worked.
NR|||NR can U please keep me posted . . and what errors did you get on the node
after U changed regional settings'

Different regional settings discovered in SQL cluster nodes!

We have advertantly failed to verify correct regional settings on both (Win2003) Cluster nodes prior to installing SQL Server 2000 Ent Ed. SQL Server is installed in an Active Passive config with System Databases installed to a shared disk (Drive G:) Node 1 has US English regional setings (date = mm/dd/yyyy) and US English input Local
Node 2 has Australian English regional (date=dd/mm/yyyy) and US English input local
Our (mission critical production) database was detached from its old server (AustralianEnglish regional and US English Locale) and moved to this cluster 2 days ago. Testing and failovers work fine and the solution went live. We have only just discovered the date discrapency. As the current date is (day & month) is still below 13, nothing has blown up yet but we are 2 days away from the 13th!
The sysadmins are content to change the regional settings on node 1 to English Australian and leave it like that. They failed-over the SQLService over to the second node and have scanned the systems databases and tables for dates showing abnormal (future) dates and found nothing. They believe there will not be a problem in simply changing over Node 1's date settings to English Aust
Sorry for the long note but I need to describe this as clearly as possible. I am concerned that we have overlooked something. Can anyone tell me if it is safe to just change the system regional settings to English Australian? Will SQL server cope with the date format change past 13Feb 13/2/2004 in AustEnglish format (dd/mm/yyyy)? Should we rebuild the node with the correct regional settings and reinstall SQL server on it? (In order to do this, SQL Server will need to be uninstalled from both nodes and the master db deleted as the master db was created via node 1 US English to start with)
Thanks
NAs long as you have changed regional settings and you do not have code that uses set dateformat otherwise this willbe ok. No datetime issues will be experienced thereafter.|||Thanks for your reply, Olu. We did the change but found that SQLServer still had problems on that node, and this was after we also fixed up the user profiles to ensure the regional settings were correctly set
We are attempting tonight to uninstall SQL reinstall, this time from the second node which was initially set with EngAust regional settings. We'll know soon if that worked
NR|||NR can U please keep me posted . . and what errors did you get on the node after U changed regional settings'

different records result if a use N (unicode data)

A same query with a NOT LIKE statement and a wild character % returns
different records result if a use N (that means that the string follow is
unicode data) or not.
Par example:
select * from company
where company_name not like N'%'
select * from company
where company_name not like '%'
the result of the two queries is different.
How is it possible?
Company_Name is a varchar field (not a nvarchar)
RegardsI forget this happen if there are at least one record company with
company_name NULL
Thanks

Different query times

I have a procedure which pulls the data by batchID. What I
found is batch of 1000 records take 4 minutes while batch
of 6000 records take 1 minute. Same Stored Proc is run on
the same machine.
Anyone can shed light on what can be causing this?
Thanks
Likely, you are seeing data caching - which is a good thing. Try re-running
the first batch and see how long it now takes.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Atul" <anonymous@.discussions.microsoft.com> wrote in message
news:1cf6401c422f0$471db080$a401280a@.phx.gbl...
I have a procedure which pulls the data by batchID. What I
found is batch of 1000 records take 4 minutes while batch
of 6000 records take 1 minute. Same Stored Proc is run on
the same machine.
Anyone can shed light on what can be causing this?
Thanks
|||Atul
You mean that stored procedure accepts BatchID as a parameter. Am I right?
One of the options is to create stored procedure WITH RECOMPILE option. Each
time that stored procedure to be run a new execution plan will be created
by query optimizer.
There are lots of postes in this newsgroup about such kind of problems.
"Atul" <anonymous@.discussions.microsoft.com> wrote in message
news:1cf6401c422f0$471db080$a401280a@.phx.gbl...
> I have a procedure which pulls the data by batchID. What I
> found is batch of 1000 records take 4 minutes while batch
> of 6000 records take 1 minute. Same Stored Proc is run on
> the same machine.
> Anyone can shed light on what can be causing this?
> Thanks

Different query times

I have a procedure which pulls the data by batchID. What I
found is batch of 1000 records take 4 minutes while batch
of 6000 records take 1 minute. Same Stored Proc is run on
the same machine.
Anyone can shed light on what can be causing this?
ThanksLikely, you are seeing data caching - which is a good thing. Try re-running
the first batch and see how long it now takes.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Atul" <anonymous@.discussions.microsoft.com> wrote in message
news:1cf6401c422f0$471db080$a401280a@.phx.gbl...
I have a procedure which pulls the data by batchID. What I
found is batch of 1000 records take 4 minutes while batch
of 6000 records take 1 minute. Same Stored Proc is run on
the same machine.
Anyone can shed light on what can be causing this?
Thanks|||Atul
You mean that stored procedure accepts BatchID as a parameter. Am I right?
One of the options is to create stored procedure WITH RECOMPILE option. Each
time that stored procedure to be run a new execution plan will be created
by query optimizer.
There are lots of postes in this newsgroup about such kind of problems.
"Atul" <anonymous@.discussions.microsoft.com> wrote in message
news:1cf6401c422f0$471db080$a401280a@.phx.gbl...
> I have a procedure which pulls the data by batchID. What I
> found is batch of 1000 records take 4 minutes while batch
> of 6000 records take 1 minute. Same Stored Proc is run on
> the same machine.
> Anyone can shed light on what can be causing this?
> Thanks

Different query times

I have a procedure which pulls the data by batchID. What I
found is batch of 1000 records take 4 minutes while batch
of 6000 records take 1 minute. Same Stored Proc is run on
the same machine.
Anyone can shed light on what can be causing this?
ThanksLikely, you are seeing data caching - which is a good thing. Try re-running
the first batch and see how long it now takes.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Atul" <anonymous@.discussions.microsoft.com> wrote in message
news:1cf6401c422f0$471db080$a401280a@.phx
.gbl...
I have a procedure which pulls the data by batchID. What I
found is batch of 1000 records take 4 minutes while batch
of 6000 records take 1 minute. Same Stored Proc is run on
the same machine.
Anyone can shed light on what can be causing this?
Thanks|||Atul
You mean that stored procedure accepts BatchID as a parameter. Am I right?
One of the options is to create stored procedure WITH RECOMPILE option. Each
time that stored procedure to be run a new execution plan will be created
by query optimizer.
There are lots of postes in this newsgroup about such kind of problems.
"Atul" <anonymous@.discussions.microsoft.com> wrote in message
news:1cf6401c422f0$471db080$a401280a@.phx
.gbl...
> I have a procedure which pulls the data by batchID. What I
> found is batch of 1000 records take 4 minutes while batch
> of 6000 records take 1 minute. Same Stored Proc is run on
> the same machine.
> Anyone can shed light on what can be causing this?
> Thanks

Different Query Results in SQL Server 7.0 and 2000

I recently migrated my databases to a box with SQL Server
2000. This query (which is actually a view) returns the
right results in 7, but in 2000, the [Dockdate & Time]
and [Variance REC-DOCK hours] fields always return NULL.
I have narrowed the problem down to the WHERE clause, but
can't figure out how to resolve it. I have tried
replacing WHERE with AND, which *allows* the fields to
return non-NULL values, but the query retrieves over 1000
records instead of the 42 that it is supposed to.
Please Help!
SELECT DISTINCT
DDRD.REC_ID,
DDRD.TYPE,
DDRD.STATUS,
DDRD.BRANCH,
DDRD.CREATE_DATE,
DDRD.DOCK_DATE,
DD.[Dockdate & Time],
MIN(T.TRANSACTION_DATE) AS [Manifest Open],
DDRD.RECEIVE_DATE, --CONVERT(varchar,
ddrd.RECEIVE_DATE, 110),
CONVERT(VARCHAR(3),DATEPART(D,CONVERT(DA
TETIME,
(CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.[Dockdate &
Time]), 1))))-1) + ' Days, ' + CONVERT(CHAR(8),CONVERT
(DATETIME,(CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.
[Dockdate & Time]), 1))),8) AS [VAR_REC-DOCKhrs],
DDRD.SumOfRECEIVED_QTY,
DDRD.ER_NO
FROM DockDates.dbo.[Dock Dates Table] DD
RIGHT JOIN [rec-Dock Date by Receive Date] DDRD
ON (DD.REC_ID = DDRD.REC_ID)
LEFT JOIN [MOVEPROD].[dbo].[TRANSACTION] T ON
(DDRD.REC_ID = T.REC_ID)
WHERE(ddrd.RECEIVE_DATE BETWEEN '01-09-2004' AND '01-12-
2004')
GROUP BY DDRD.REC_ID,
DDRD.TYPE,
DDRD.STATUS,
DDRD.BRANCH,
DDRD.CREATE_DATE,
DDRD.DOCK_DATE,
DD.[Dockdate & Time],
DDRD.RECEIVE_DATE,
DDRD.[VAR_REC-DOCKhrs],
DDRD.SumOfRECEIVED_QTY,
DDRD.ER_NO
ORDER BY DDRD.REC_IDBecky,
1. what is wrong in your results? Is it the number of records returned, or
the values returned for [Dockdate & Time] and [Variance REC-DOCK hours], or
both?
2. can you provide DDL for the tables?
3. Do you have the same collation in both installation?
Quentin
"Becky Bowen" <anonymous@.discussions.microsoft.com> wrote in message
news:012101c3dad9$79dd71e0$a601280a@.phx.gbl...

quote:

---

> I recently migrated my databases to a box with SQL Server
> 2000. This query (which is actually a view) returns the
> right results in 7, but in 2000, the [Dockdate & Time]
> and [Variance REC-DOCK hours] fields always return NULL.
> I have narrowed the problem down to the WHERE clause, but
> can't figure out how to resolve it. I have tried
> replacing WHERE with AND, which *allows* the fields to
> return non-NULL values, but the query retrieves over 1000
> records instead of the 42 that it is supposed to.
> Please Help!
>
> SELECT DISTINCT
> DDRD.REC_ID,
> DDRD.TYPE,
> DDRD.STATUS,
> DDRD.BRANCH,
> DDRD.CREATE_DATE,
> DDRD.DOCK_DATE,
> DD.[Dockdate & Time],
> MIN(T.TRANSACTION_DATE) AS [Manifest Open],
> DDRD.RECEIVE_DATE, --CONVERT(varchar,
> ddrd.RECEIVE_DATE, 110),
> CONVERT(VARCHAR(3),DATEPART(D,CONVERT(DA
TETIME,
> (CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.[Dockdate &
> Time]), 1))))-1) + ' Days, ' + CONVERT(CHAR(8),CONVERT
> (DATETIME,(CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.
> [Dockdate & Time]), 1))),8) AS [VAR_REC-DOCKhrs],
> DDRD.SumOfRECEIVED_QTY,
> DDRD.ER_NO
> FROM DockDates.dbo.[Dock Dates Table] DD
> RIGHT JOIN [rec-Dock Date by Receive Date] DDRD
> ON (DD.REC_ID = DDRD.REC_ID)
> LEFT JOIN [MOVEPROD].[dbo].[TRANSACTION] T ON
> (DDRD.REC_ID = T.REC_ID)
> WHERE(ddrd.RECEIVE_DATE BETWEEN '01-09-2004' AND '01-12-
> 2004')
> GROUP BY DDRD.REC_ID,
> DDRD.TYPE,
> DDRD.STATUS,
> DDRD.BRANCH,
> DDRD.CREATE_DATE,
> DDRD.DOCK_DATE,
> DD.[Dockdate & Time],
> DDRD.RECEIVE_DATE,
> DDRD.[VAR_REC-DOCKhrs],
> DDRD.SumOfRECEIVED_QTY,
> DDRD.ER_NO
> ORDER BY DDRD.REC_ID

---

|||Thanks in advance for your help!!

quote:

---

>--Original Message--
>Becky,
>1. what is wrong in your results? Is it the number of

---

records returned, or
**Wrong number of records using AND
**Wrong results (Null fields) using WHERE

quote:

---

>the values returned for [Dockdate & Time] and [Variance

---

REC-DOCK hours], or

quote:

---

>both?

---

**Both

quote:

---

>2. can you provide DDL for the tables?

---

** Alright...you asked for it...
CREATE TABLE [dbo].[TRANSACTION] (
[TRANSACTION_ID] [decimal](9, 0) NOT NULL ,
[TRANSACTION_TYPE] [varchar] (3) NOT NULL ,
[TRANSACTION_DATE] [datetime] NULL ,
[SOURCE_LICENSE_PLATE_NO] [varchar] (20) NULL ,
[DEST_LICENSE_PLATE_NO] [varchar] (20) NULL ,
[PRODUCT_ID] [varchar] (40) NULL ,
[PERFORMED_BY] [varchar] (30) NULL ,
[ORDER_ID] [varchar] (30) NULL ,
[ORDER_TYPE] [varchar] (2) NULL ,
[ORDER_LINE_NO] [decimal](9, 0) NULL ,
[EXPECTED_RECEIPT_NO] [varchar] (12) NULL ,
[EXPECTED_RECEIPT_TYPE] [varchar] (3) NULL ,
[ERD_LINE_NO] [decimal](9, 0) NULL ,
[REC_ID] [decimal](9, 0) NULL ,
[RECEIVER_TYPE] [varchar] (3) NULL ,
[TASK_ID] [decimal](9, 0) NULL ,
[SOURCE_LOCATION_NO] [varchar] (20) NULL ,
[DESTINATION_LOCATION_NO] [varchar] (20) NULL ,
[DROP_LOCATION_NO] [varchar] (20) NULL ,
[EXPECTED_QUANTITY] [decimal](9, 0) NULL ,
[ACTUAL_QUANTITY] [decimal](9, 0) NULL ,
[EXPECTED_UOM] [decimal](2, 0) NULL ,
[ACTUAL_UOM] [decimal](2, 0) NULL ,
[UOM_FAMILY] [decimal](1, 0) NULL ,
[OLD_MSC] [varchar] (3) NULL ,
[NEW_MSC] [varchar] (3) NULL ,
[OLD_MKR] [varchar] (20) NULL ,
[NEW_MKR] [varchar] (20) NULL ,
[INVENTORY_ID] [decimal](9, 0) NULL ,
[PRODUCT_KEY] [varchar] (250) NULL ,
[OLD_INVENTORY_STATUS] [varchar] (3) NULL ,
[NEW_INVENTORY_STATUS] [varchar] (3) NULL ,
[HOLD_IND] [varchar] (1) NULL ,
[REASON_CODE] [varchar] (20) NULL ,
[ADJUSTMENT_MESSAGE] [varchar] (20) NULL ,
[RELEASE_GROUP_ID] [decimal](9, 0) NULL ,
[DD_INSTANCE_ID] [decimal](9, 0) NULL ,
[UPLOAD_FILE_NAME] [varchar] (30) NULL ,
[UPLOAD_IND] [varchar] (1) NULL ,
[LOGGING_SOURCE] [varchar] (80) NULL ,
[REQUEST_TRANS_NO] [decimal](10, 0) NULL ,
[REQUEST_TRANS_SEQ_NO] [decimal](5, 0) NULL ,
[SUCCESS_IND] [varchar] (1) NULL ,
[HOST_REFERENCE] [varchar] (40) NULL ,
[EXPIRY_DATE] [datetime] NULL ,
[LOT_ID] [varchar] (20) NULL ,
[BRANCH] [varchar] (20) NULL ,
[COUNTRY_OF_ORIGIN] [varchar] (20) NULL ,
[VENDOR_ID] [varchar] (20) NULL ,
[MANUFACTURING_DATE] [datetime] NULL ,
[ATTRIBUTE1] [varchar] (20) NULL ,
[ATTRIBUTE2] [varchar] (20) NULL ,
[ATTRIBUTE3] [varchar] (20) NULL ,
[ATTRIBUTE4] [varchar] (20) NULL ,
[ATTRIBUTE5] [varchar] (20) NULL ,
[ATTRIBUTE6] [varchar] (20) NULL ,
[ATTRIBUTE7] [varchar] (20) NULL ,
[ATTRIBUTE8] [varchar] (20) NULL ,
[ATTRIBUTE9] [varchar] (20) NULL ,
[ATTRIBUTE10] [varchar] (20) NULL ,
[ATTRIBUTE11] [varchar] (20) NULL ,
[ATTRIBUTE12] [varchar] (20) NULL ,
[ATTRIBUTE13] [varchar] (20) NULL ,
[ATTRIBUTE14] [varchar] (20) NULL ,
[ATTRIBUTE15] [varchar] (20) NULL ,
[ATTRIBUTE16] [varchar] (20) NULL ,
[ATTRIBUTE17] [varchar] (20) NULL ,
[ATTRIBUTE18] [varchar] (20) NULL ,
[ATTRIBUTE19] [varchar] (20) NULL ,
[ATTRIBUTE20] [varchar] (20) NULL ,
[REC_LINE_NO] [decimal](9, 0) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Dock Dates Table] (
[REC_ID] [int] NOT NULL ,
[Dockdate & Time] [smalldatetime] NULL ,
[Comments] [varchar] (255) NULL ,
[UsrID] [varchar] (10) NULL ,
[LastModUsrID] [varchar] (10) NULL ,
[LastModDate] [datetime] NULL ,
[InputDate] [datetime] NULL
) ON [PRIMARY]
GO
SELECT DISTINCT
R.REC_ID, R.TYPE, R.STATUS,
RD.BRANCH, R.CREATE_DATE, ER.ERHE_GD_01 AS DOCK_DATE,
R.RECEIVE_DATE,
R.RECEIVE_DATE - ER.ERHE_GD_01 AS
[VAR_REC-DOCKhrs], SUM(RD.RECEIVED_QTY) AS
SumOfRECEIVED_QTY, ER.ER_NO
FROM MOVEPROD.dbo.RECEIVER R LEFT OUTER JOIN
MOVEPROD.dbo.RECEIVER_DETAIL RD ON
R.REC_ID = RD.REC_ID LEFT OUTER JOIN
MOVEPROD.dbo.EXPECTED_RECEIPT ER ON
R.ER_ID = ER.ER_ID
WHERE (R.STATUS = 'CLO')
GROUP BY R.REC_ID, R.TYPE, R.STATUS, RD.BRANCH,
R.CREATE_DATE, ER.ERHE_GD_01, R.RECEIVE_DATE, ER.ER_NO,
R.RECEIVE_DATE - ER.ERHE_GD_01

quote:

---

>3. Do you have the same collation in both installation?

---

**2000--SQL_Latin1_General_CP1_CI_AS
**7.0--'

quote:

---

>Quentin
>"Becky Bowen" <anonymous@.discussions.microsoft.com>

---

wrote in message

quote:

---

>news:012101c3dad9$79dd71e0$a601280a@.phx.gbl...
Server[QUOTE]
the[QUOTE]
NULL.[QUOTE]
but[QUOTE]
1000[QUOTE]
12-[QUOTE]
>
>.
>

---

|||1. Depending on your null setting, changing from where to and can indeed
cause change of the query. I believe I saw other situations, but don't
remember which exactly. Anyway, AND can give different query than WHERE.
2. It is likely that you have the same collation between the two
servers/db/table column (remember that in SS2K you can set collation down to
column) since the one you give for 2K is the same as SS7 default. but a
verification would help, also verify in SS2K there is no change from
collation default.
3. [Variance REC-DOCK hours] is derived from ... which is derived from ...
Some where the chain broke in your DDL. I am not saying that that was the
cause since I can not explain why [Dockdate & Time] also became null. But
you may want to follow back with those ones that are correct in SS7 but
wrong in 2K step by step.
Quentin
"Becky" <anonymous@.discussions.microsoft.com> wrote in message
news:02ed01c3daea$de02be30$a501280a@.phx.gbl...[QUOTE]
> Thanks in advance for your help!!
> records returned, or
> **Wrong number of records using AND
> **Wrong results (Null fields) using WHERE
>
> REC-DOCK hours], or
> **Both
> ** Alright...you asked for it...
> CREATE TABLE [dbo].[TRANSACTION] (
> [TRANSACTION_ID] [decimal](9, 0) NOT NULL ,
> [TRANSACTION_TYPE] [varchar] (3) NOT NULL ,
> [TRANSACTION_DATE] [datetime] NULL ,
> [SOURCE_LICENSE_PLATE_NO] [varchar] (20) NULL ,
> [DEST_LICENSE_PLATE_NO] [varchar] (20) NULL ,
> [PRODUCT_ID] [varchar] (40) NULL ,
> [PERFORMED_BY] [varchar] (30) NULL ,
> [ORDER_ID] [varchar] (30) NULL ,
> [ORDER_TYPE] [varchar] (2) NULL ,
> [ORDER_LINE_NO] [decimal](9, 0) NULL ,
> [EXPECTED_RECEIPT_NO] [varchar] (12) NULL ,
> [EXPECTED_RECEIPT_TYPE] [varchar] (3) NULL ,
> [ERD_LINE_NO] [decimal](9, 0) NULL ,
> [REC_ID] [decimal](9, 0) NULL ,
> [RECEIVER_TYPE] [varchar] (3) NULL ,
> [TASK_ID] [decimal](9, 0) NULL ,
> [SOURCE_LOCATION_NO] [varchar] (20) NULL ,
> [DESTINATION_LOCATION_NO] [varchar] (20) NULL ,
> [DROP_LOCATION_NO] [varchar] (20) NULL ,
> [EXPECTED_QUANTITY] [decimal](9, 0) NULL ,
> [ACTUAL_QUANTITY] [decimal](9, 0) NULL ,
> [EXPECTED_UOM] [decimal](2, 0) NULL ,
> [ACTUAL_UOM] [decimal](2, 0) NULL ,
> [UOM_FAMILY] [decimal](1, 0) NULL ,
> [OLD_MSC] [varchar] (3) NULL ,
> [NEW_MSC] [varchar] (3) NULL ,
> [OLD_MKR] [varchar] (20) NULL ,
> [NEW_MKR] [varchar] (20) NULL ,
> [INVENTORY_ID] [decimal](9, 0) NULL ,
> [PRODUCT_KEY] [varchar] (250) NULL ,
> [OLD_INVENTORY_STATUS] [varchar] (3) NULL ,
> [NEW_INVENTORY_STATUS] [varchar] (3) NULL ,
> [HOLD_IND] [varchar] (1) NULL ,
> [REASON_CODE] [varchar] (20) NULL ,
> [ADJUSTMENT_MESSAGE] [varchar] (20) NULL ,
> [RELEASE_GROUP_ID] [decimal](9, 0) NULL ,
> [DD_INSTANCE_ID] [decimal](9, 0) NULL ,
> [UPLOAD_FILE_NAME] [varchar] (30) NULL ,
> [UPLOAD_IND] [varchar] (1) NULL ,
> [LOGGING_SOURCE] [varchar] (80) NULL ,
> [REQUEST_TRANS_NO] [decimal](10, 0) NULL ,
> [REQUEST_TRANS_SEQ_NO] [decimal](5, 0) NULL ,
> [SUCCESS_IND] [varchar] (1) NULL ,
> [HOST_REFERENCE] [varchar] (40) NULL ,
> [EXPIRY_DATE] [datetime] NULL ,
> [LOT_ID] [varchar] (20) NULL ,
> [BRANCH] [varchar] (20) NULL ,
> [COUNTRY_OF_ORIGIN] [varchar] (20) NULL ,
> [VENDOR_ID] [varchar] (20) NULL ,
> [MANUFACTURING_DATE] [datetime] NULL ,
> [ATTRIBUTE1] [varchar] (20) NULL ,
> [ATTRIBUTE2] [varchar] (20) NULL ,
> [ATTRIBUTE3] [varchar] (20) NULL ,
> [ATTRIBUTE4] [varchar] (20) NULL ,
> [ATTRIBUTE5] [varchar] (20) NULL ,
> [ATTRIBUTE6] [varchar] (20) NULL ,
> [ATTRIBUTE7] [varchar] (20) NULL ,
> [ATTRIBUTE8] [varchar] (20) NULL ,
> [ATTRIBUTE9] [varchar] (20) NULL ,
> [ATTRIBUTE10] [varchar] (20) NULL ,
> [ATTRIBUTE11] [varchar] (20) NULL ,
> [ATTRIBUTE12] [varchar] (20) NULL ,
> [ATTRIBUTE13] [varchar] (20) NULL ,
> [ATTRIBUTE14] [varchar] (20) NULL ,
> [ATTRIBUTE15] [varchar] (20) NULL ,
> [ATTRIBUTE16] [varchar] (20) NULL ,
> [ATTRIBUTE17] [varchar] (20) NULL ,
> [ATTRIBUTE18] [varchar] (20) NULL ,
> [ATTRIBUTE19] [varchar] (20) NULL ,
> [ATTRIBUTE20] [varchar] (20) NULL ,
> [REC_LINE_NO] [decimal](9, 0) NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Dock Dates Table] (
> [REC_ID] [int] NOT NULL ,
> [Dockdate & Time] [smalldatetime] NULL ,
> [Comments] [varchar] (255) NULL ,
> [UsrID] [varchar] (10) NULL ,
> [LastModUsrID] [varchar] (10) NULL ,
> [LastModDate] [datetime] NULL ,
> [InputDate] [datetime] NULL
> ) ON [PRIMARY]
> GO
> --
> SELECT DISTINCT
> R.REC_ID, R.TYPE, R.STATUS,
> RD.BRANCH, R.CREATE_DATE, ER.ERHE_GD_01 AS DOCK_DATE,
> R.RECEIVE_DATE,
> R.RECEIVE_DATE - ER.ERHE_GD_01 AS
> [VAR_REC-DOCKhrs], SUM(RD.RECEIVED_QTY) AS
> SumOfRECEIVED_QTY, ER.ER_NO
> FROM MOVEPROD.dbo.RECEIVER R LEFT OUTER JOIN
> MOVEPROD.dbo.RECEIVER_DETAIL RD ON
> R.REC_ID = RD.REC_ID LEFT OUTER JOIN
> MOVEPROD.dbo.EXPECTED_RECEIPT ER ON
> R.ER_ID = ER.ER_ID
> WHERE (R.STATUS = 'CLO')
> GROUP BY R.REC_ID, R.TYPE, R.STATUS, RD.BRANCH,
> R.CREATE_DATE, ER.ERHE_GD_01, R.RECEIVE_DATE, ER.ER_NO,
> R.RECEIVE_DATE - ER.ERHE_GD_01
>
> **2000--SQL_Latin1_General_CP1_CI_AS
> **7.0--'
> wrote in message
> Server
> the
> NULL.
> but
> 1000
> 12-|||The chain breaks at [Variance REC-DOCK hours]
The collation is the same throughout the 2000 Server as
well as 7.0
Thanks again.

quote:

---

>--Original Message--
>1. Depending on your null setting, changing from where

---

to and can indeed

quote:

---

>cause change of the query. I believe I saw other

---

situations, but don't

quote:

---

>remember which exactly. Anyway, AND can give different

---

query than WHERE.

quote:

---

>2. It is likely that you have the same collation between

---

the two

quote:

---

>servers/db/table column (remember that in SS2K you can

---

set collation down to

quote:

---

>column) since the one you give for 2K is the same as SS7

---

default. but a

quote:

---

>verification would help, also verify in SS2K there is no

---

change from

quote:

---

>collation default.
>3. [Variance REC-DOCK hours] is derived from ... which

---

is derived from ...

quote:

---

>Some where the chain broke in your DDL. I am not saying

---

that that was the

quote:

---

>cause since I can not explain why [Dockdate & Time] also

---

became null. But

quote:

---

>you may want to follow back with those ones that are

---

correct in SS7 but

quote:

---

>wrong in 2K step by step.
>Quentin
>"Becky" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:02ed01c3daea$de02be30$a501280a@.phx.gbl...
[Variance[QUOTE]
ON[QUOTE]
ON[QUOTE]
installation?[QUOTE]
Time][QUOTE]
clause,[QUOTE]
to[QUOTE]
&[QUOTE]
(8),CONVERT[QUOTE]
AND '01-[QUOTE]
>
>.
>

---

|||What datatype is ddrd.RECEIVE_DATE? If it is smalldatetime, then you
could try changing "BETWEEN '01-09-2004' AND '01-12-2004'" to "BETWEEN
CAST('01-09-2004' AS smalldatetime) AND CAST('01-12-2004' AS
smalldatetime)".
Hope this helps,
Gert-Jan
Becky Bowen wrote:

quote:

---

> I recently migrated my databases to a box with SQL Server
> 2000. This query (which is actually a view) returns the
> right results in 7, but in 2000, the [Dockdate & Time]
> and [Variance REC-DOCK hours] fields always return NULL.
> I have narrowed the problem down to the WHERE clause, but
> can't figure out how to resolve it. I have tried
> replacing WHERE with AND, which *allows* the fields to
> return non-NULL values, but the query retrieves over 1000
> records instead of the 42 that it is supposed to.
> Please Help!
> SELECT DISTINCT
> DDRD.REC_ID,
> DDRD.TYPE,
> DDRD.STATUS,
> DDRD.BRANCH,
> DDRD.CREATE_DATE,
> DDRD.DOCK_DATE,
> DD.[Dockdate & Time],
> MIN(T.TRANSACTION_DATE) AS [Manifest Open],
> DDRD.RECEIVE_DATE, --CONVERT(varchar,
> ddrd.RECEIVE_DATE, 110),
> CONVERT(VARCHAR(3),DATEPART(D,CONVERT(DA
TETIME,
> (CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.[Dockdate &
> Time]), 1))))-1) + ' Days, ' + CONVERT(CHAR(8),CONVERT
> (DATETIME,(CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.
> [Dockdate & Time]), 1))),8) AS [VAR_REC-DOCKhrs],
> DDRD.SumOfRECEIVED_QTY,
> DDRD.ER_NO
> FROM DockDates.dbo.[Dock Dates Table] DD
> RIGHT JOIN [rec-Dock Date by Receive Date] DDRD
> ON (DD.REC_ID = DDRD.REC_ID)
> LEFT JOIN [MOVEPROD].[dbo].[TRANSACTION] T ON
> (DDRD.REC_ID = T.REC_ID)
> WHERE(ddrd.RECEIVE_DATE BETWEEN '01-09-2004' AND '01-12-
> 2004')
> GROUP BY DDRD.REC_ID,
> DDRD.TYPE,
> DDRD.STATUS,
> DDRD.BRANCH,
> DDRD.CREATE_DATE,
> DDRD.DOCK_DATE,
> DD.[Dockdate & Time],
> DDRD.RECEIVE_DATE,
> DDRD.[VAR_REC-DOCKhrs],
> DDRD.SumOfRECEIVED_QTY,
> DDRD.ER_NO
> ORDER BY DDRD.REC_ID

---

|||Along these same lines, you might go one step further and replace
'01-09-2004' with
convert(smalldatetime, '01-09-2004', 110)
or
convert(smalldatetime, '01-09-2004', 105)
depending on whether this date is supposed to be January 9, 2004 or
September 1, 2004, respectively.
SK
Gert-Jan Strik wrote:
[QUOTE]
>What datatype is ddrd.RECEIVE_DATE? If it is smalldatetime, then you
>could try changing "BETWEEN '01-09-2004' AND '01-12-2004'" to "BETWEEN
>CAST('01-09-2004' AS smalldatetime) AND CAST('01-12-2004' AS
>smalldatetime)".
>Hope this helps,
>Gert-Jan
>
>Becky Bowen wrote:
>

Different Query Results in SQL Server 7.0 and 2000

I recently migrated my databases to a box with SQL Server
2000. This query (which is actually a view) returns the
right results in 7, but in 2000, the [Dockdate & Time]
and [Variance REC-DOCK hours] fields always return NULL.
I have narrowed the problem down to the WHERE clause, but
can't figure out how to resolve it. I have tried
replacing WHERE with AND, which *allows* the fields to
return non-NULL values, but the query retrieves over 1000
records instead of the 42 that it is supposed to.
Please Help!
SELECT DISTINCT
DDRD.REC_ID,
DDRD.TYPE,
DDRD.STATUS,
DDRD.BRANCH,
DDRD.CREATE_DATE,
DDRD.DOCK_DATE,
DD.[Dockdate & Time],
MIN(T.TRANSACTION_DATE) AS [Manifest Open],
DDRD.RECEIVE_DATE, --CONVERT(varchar,
ddrd.RECEIVE_DATE, 110),
CONVERT(VARCHAR(3),DATEPART(D,CONVERT(DATETIME,
(CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.[Dockdate &
Time]), 1))))-1) + ' Days, ' + CONVERT(CHAR(8),CONVERT
(DATETIME,(CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.
[Dockdate & Time]), 1))),8) AS [VAR_REC-DOCKhrs],
DDRD.SumOfRECEIVED_QTY,
DDRD.ER_NO
FROM DockDates.dbo.[Dock Dates Table] DD
RIGHT JOIN [rec-Dock Date by Receive Date] DDRD
ON (DD.REC_ID = DDRD.REC_ID)
LEFT JOIN [MOVEPROD].[dbo].[TRANSACTION] T ON
(DDRD.REC_ID = T.REC_ID)
WHERE(ddrd.RECEIVE_DATE BETWEEN '01-09-2004' AND '01-12-
2004')
GROUP BY DDRD.REC_ID,
DDRD.TYPE,
DDRD.STATUS,
DDRD.BRANCH,
DDRD.CREATE_DATE,
DDRD.DOCK_DATE,
DD.[Dockdate & Time],
DDRD.RECEIVE_DATE,
DDRD.[VAR_REC-DOCKhrs],
DDRD.SumOfRECEIVED_QTY,
DDRD.ER_NO
ORDER BY DDRD.REC_IDBecky,
1. what is wrong in your results? Is it the number of records returned, or
the values returned for [Dockdate & Time] and [Variance REC-DOCK hours], or
both?
2. can you provide DDL for the tables?
3. Do you have the same collation in both installation?
Quentin
"Becky Bowen" <anonymous@.discussions.microsoft.com> wrote in message
news:012101c3dad9$79dd71e0$a601280a@.phx.gbl...
> I recently migrated my databases to a box with SQL Server
> 2000. This query (which is actually a view) returns the
> right results in 7, but in 2000, the [Dockdate & Time]
> and [Variance REC-DOCK hours] fields always return NULL.
> I have narrowed the problem down to the WHERE clause, but
> can't figure out how to resolve it. I have tried
> replacing WHERE with AND, which *allows* the fields to
> return non-NULL values, but the query retrieves over 1000
> records instead of the 42 that it is supposed to.
> Please Help!
>
> SELECT DISTINCT
> DDRD.REC_ID,
> DDRD.TYPE,
> DDRD.STATUS,
> DDRD.BRANCH,
> DDRD.CREATE_DATE,
> DDRD.DOCK_DATE,
> DD.[Dockdate & Time],
> MIN(T.TRANSACTION_DATE) AS [Manifest Open],
> DDRD.RECEIVE_DATE, --CONVERT(varchar,
> ddrd.RECEIVE_DATE, 110),
> CONVERT(VARCHAR(3),DATEPART(D,CONVERT(DATETIME,
> (CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.[Dockdate &
> Time]), 1))))-1) + ' Days, ' + CONVERT(CHAR(8),CONVERT
> (DATETIME,(CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.
> [Dockdate & Time]), 1))),8) AS [VAR_REC-DOCKhrs],
> DDRD.SumOfRECEIVED_QTY,
> DDRD.ER_NO
> FROM DockDates.dbo.[Dock Dates Table] DD
> RIGHT JOIN [rec-Dock Date by Receive Date] DDRD
> ON (DD.REC_ID = DDRD.REC_ID)
> LEFT JOIN [MOVEPROD].[dbo].[TRANSACTION] T ON
> (DDRD.REC_ID = T.REC_ID)
> WHERE(ddrd.RECEIVE_DATE BETWEEN '01-09-2004' AND '01-12-
> 2004')
> GROUP BY DDRD.REC_ID,
> DDRD.TYPE,
> DDRD.STATUS,
> DDRD.BRANCH,
> DDRD.CREATE_DATE,
> DDRD.DOCK_DATE,
> DD.[Dockdate & Time],
> DDRD.RECEIVE_DATE,
> DDRD.[VAR_REC-DOCKhrs],
> DDRD.SumOfRECEIVED_QTY,
> DDRD.ER_NO
> ORDER BY DDRD.REC_ID|||Thanks in advance for your help!!
>--Original Message--
>Becky,
>1. what is wrong in your results? Is it the number of
records returned, or
**Wrong number of records using AND
**Wrong results (Null fields) using WHERE
>the values returned for [Dockdate & Time] and [Variance
REC-DOCK hours], or
>both?
**Both
>2. can you provide DDL for the tables?
** Alright...you asked for it...
CREATE TABLE [dbo].[TRANSACTION] (
[TRANSACTION_ID] [decimal](9, 0) NOT NULL ,
[TRANSACTION_TYPE] [varchar] (3) NOT NULL ,
[TRANSACTION_DATE] [datetime] NULL ,
[SOURCE_LICENSE_PLATE_NO] [varchar] (20) NULL ,
[DEST_LICENSE_PLATE_NO] [varchar] (20) NULL ,
[PRODUCT_ID] [varchar] (40) NULL ,
[PERFORMED_BY] [varchar] (30) NULL ,
[ORDER_ID] [varchar] (30) NULL ,
[ORDER_TYPE] [varchar] (2) NULL ,
[ORDER_LINE_NO] [decimal](9, 0) NULL ,
[EXPECTED_RECEIPT_NO] [varchar] (12) NULL ,
[EXPECTED_RECEIPT_TYPE] [varchar] (3) NULL ,
[ERD_LINE_NO] [decimal](9, 0) NULL ,
[REC_ID] [decimal](9, 0) NULL ,
[RECEIVER_TYPE] [varchar] (3) NULL ,
[TASK_ID] [decimal](9, 0) NULL ,
[SOURCE_LOCATION_NO] [varchar] (20) NULL ,
[DESTINATION_LOCATION_NO] [varchar] (20) NULL ,
[DROP_LOCATION_NO] [varchar] (20) NULL ,
[EXPECTED_QUANTITY] [decimal](9, 0) NULL ,
[ACTUAL_QUANTITY] [decimal](9, 0) NULL ,
[EXPECTED_UOM] [decimal](2, 0) NULL ,
[ACTUAL_UOM] [decimal](2, 0) NULL ,
[UOM_FAMILY] [decimal](1, 0) NULL ,
[OLD_MSC] [varchar] (3) NULL ,
[NEW_MSC] [varchar] (3) NULL ,
[OLD_MKR] [varchar] (20) NULL ,
[NEW_MKR] [varchar] (20) NULL ,
[INVENTORY_ID] [decimal](9, 0) NULL ,
[PRODUCT_KEY] [varchar] (250) NULL ,
[OLD_INVENTORY_STATUS] [varchar] (3) NULL ,
[NEW_INVENTORY_STATUS] [varchar] (3) NULL ,
[HOLD_IND] [varchar] (1) NULL ,
[REASON_CODE] [varchar] (20) NULL ,
[ADJUSTMENT_MESSAGE] [varchar] (20) NULL ,
[RELEASE_GROUP_ID] [decimal](9, 0) NULL ,
[DD_INSTANCE_ID] [decimal](9, 0) NULL ,
[UPLOAD_FILE_NAME] [varchar] (30) NULL ,
[UPLOAD_IND] [varchar] (1) NULL ,
[LOGGING_SOURCE] [varchar] (80) NULL ,
[REQUEST_TRANS_NO] [decimal](10, 0) NULL ,
[REQUEST_TRANS_SEQ_NO] [decimal](5, 0) NULL ,
[SUCCESS_IND] [varchar] (1) NULL ,
[HOST_REFERENCE] [varchar] (40) NULL ,
[EXPIRY_DATE] [datetime] NULL ,
[LOT_ID] [varchar] (20) NULL ,
[BRANCH] [varchar] (20) NULL ,
[COUNTRY_OF_ORIGIN] [varchar] (20) NULL ,
[VENDOR_ID] [varchar] (20) NULL ,
[MANUFACTURING_DATE] [datetime] NULL ,
[ATTRIBUTE1] [varchar] (20) NULL ,
[ATTRIBUTE2] [varchar] (20) NULL ,
[ATTRIBUTE3] [varchar] (20) NULL ,
[ATTRIBUTE4] [varchar] (20) NULL ,
[ATTRIBUTE5] [varchar] (20) NULL ,
[ATTRIBUTE6] [varchar] (20) NULL ,
[ATTRIBUTE7] [varchar] (20) NULL ,
[ATTRIBUTE8] [varchar] (20) NULL ,
[ATTRIBUTE9] [varchar] (20) NULL ,
[ATTRIBUTE10] [varchar] (20) NULL ,
[ATTRIBUTE11] [varchar] (20) NULL ,
[ATTRIBUTE12] [varchar] (20) NULL ,
[ATTRIBUTE13] [varchar] (20) NULL ,
[ATTRIBUTE14] [varchar] (20) NULL ,
[ATTRIBUTE15] [varchar] (20) NULL ,
[ATTRIBUTE16] [varchar] (20) NULL ,
[ATTRIBUTE17] [varchar] (20) NULL ,
[ATTRIBUTE18] [varchar] (20) NULL ,
[ATTRIBUTE19] [varchar] (20) NULL ,
[ATTRIBUTE20] [varchar] (20) NULL ,
[REC_LINE_NO] [decimal](9, 0) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Dock Dates Table] (
[REC_ID] [int] NOT NULL ,
[Dockdate & Time] [smalldatetime] NULL ,
[Comments] [varchar] (255) NULL ,
[UsrID] [varchar] (10) NULL ,
[LastModUsrID] [varchar] (10) NULL ,
[LastModDate] [datetime] NULL ,
[InputDate] [datetime] NULL
) ON [PRIMARY]
GO
--
SELECT DISTINCT
R.REC_ID, R.TYPE, R.STATUS,
RD.BRANCH, R.CREATE_DATE, ER.ERHE_GD_01 AS DOCK_DATE,
R.RECEIVE_DATE,
R.RECEIVE_DATE - ER.ERHE_GD_01 AS
[VAR_REC-DOCKhrs], SUM(RD.RECEIVED_QTY) AS
SumOfRECEIVED_QTY, ER.ER_NO
FROM MOVEPROD.dbo.RECEIVER R LEFT OUTER JOIN
MOVEPROD.dbo.RECEIVER_DETAIL RD ON
R.REC_ID = RD.REC_ID LEFT OUTER JOIN
MOVEPROD.dbo.EXPECTED_RECEIPT ER ON
R.ER_ID = ER.ER_ID
WHERE (R.STATUS = 'CLO')
GROUP BY R.REC_ID, R.TYPE, R.STATUS, RD.BRANCH,
R.CREATE_DATE, ER.ERHE_GD_01, R.RECEIVE_DATE, ER.ER_NO,
R.RECEIVE_DATE - ER.ERHE_GD_01
>3. Do you have the same collation in both installation?
**2000--SQL_Latin1_General_CP1_CI_AS
**7.0--'
>Quentin
>"Becky Bowen" <anonymous@.discussions.microsoft.com>
wrote in message
>news:012101c3dad9$79dd71e0$a601280a@.phx.gbl...
>> I recently migrated my databases to a box with SQL
Server
>> 2000. This query (which is actually a view) returns
the
>> right results in 7, but in 2000, the [Dockdate & Time]
>> and [Variance REC-DOCK hours] fields always return
NULL.
>> I have narrowed the problem down to the WHERE clause,
but
>> can't figure out how to resolve it. I have tried
>> replacing WHERE with AND, which *allows* the fields to
>> return non-NULL values, but the query retrieves over
1000
>> records instead of the 42 that it is supposed to.
>> Please Help!
>>
>> SELECT DISTINCT
>> DDRD.REC_ID,
>> DDRD.TYPE,
>> DDRD.STATUS,
>> DDRD.BRANCH,
>> DDRD.CREATE_DATE,
>> DDRD.DOCK_DATE,
>> DD.[Dockdate & Time],
>> MIN(T.TRANSACTION_DATE) AS [Manifest Open],
>> DDRD.RECEIVE_DATE, --CONVERT(varchar,
>> ddrd.RECEIVE_DATE, 110),
>> CONVERT(VARCHAR(3),DATEPART(D,CONVERT(DATETIME,
>> (CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.[Dockdate &
>> Time]), 1))))-1) + ' Days, ' + CONVERT(CHAR(8),CONVERT
>> (DATETIME,(CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.
>> [Dockdate & Time]), 1))),8) AS [VAR_REC-DOCKhrs],
>> DDRD.SumOfRECEIVED_QTY,
>> DDRD.ER_NO
>> FROM DockDates.dbo.[Dock Dates Table] DD
>> RIGHT JOIN [rec-Dock Date by Receive Date] DDRD
>> ON (DD.REC_ID = DDRD.REC_ID)
>> LEFT JOIN [MOVEPROD].[dbo].[TRANSACTION] T ON
>> (DDRD.REC_ID = T.REC_ID)
>> WHERE(ddrd.RECEIVE_DATE BETWEEN '01-09-2004' AND '01-
12-
>> 2004')
>> GROUP BY DDRD.REC_ID,
>> DDRD.TYPE,
>> DDRD.STATUS,
>> DDRD.BRANCH,
>> DDRD.CREATE_DATE,
>> DDRD.DOCK_DATE,
>> DD.[Dockdate & Time],
>> DDRD.RECEIVE_DATE,
>> DDRD.[VAR_REC-DOCKhrs],
>> DDRD.SumOfRECEIVED_QTY,
>> DDRD.ER_NO
>> ORDER BY DDRD.REC_ID
>
>.
>|||1. Depending on your null setting, changing from where to and can indeed
cause change of the query. I believe I saw other situations, but don't
remember which exactly. Anyway, AND can give different query than WHERE.
2. It is likely that you have the same collation between the two
servers/db/table column (remember that in SS2K you can set collation down to
column) since the one you give for 2K is the same as SS7 default. but a
verification would help, also verify in SS2K there is no change from
collation default.
3. [Variance REC-DOCK hours] is derived from ... which is derived from ...
Some where the chain broke in your DDL. I am not saying that that was the
cause since I can not explain why [Dockdate & Time] also became null. But
you may want to follow back with those ones that are correct in SS7 but
wrong in 2K step by step.
Quentin
"Becky" <anonymous@.discussions.microsoft.com> wrote in message
news:02ed01c3daea$de02be30$a501280a@.phx.gbl...
> Thanks in advance for your help!!
> >--Original Message--
> >Becky,
> >
> >1. what is wrong in your results? Is it the number of
> records returned, or
> **Wrong number of records using AND
> **Wrong results (Null fields) using WHERE
> >the values returned for [Dockdate & Time] and [Variance
> REC-DOCK hours], or
> >both?
> **Both
> >2. can you provide DDL for the tables?
> ** Alright...you asked for it...
> CREATE TABLE [dbo].[TRANSACTION] (
> [TRANSACTION_ID] [decimal](9, 0) NOT NULL ,
> [TRANSACTION_TYPE] [varchar] (3) NOT NULL ,
> [TRANSACTION_DATE] [datetime] NULL ,
> [SOURCE_LICENSE_PLATE_NO] [varchar] (20) NULL ,
> [DEST_LICENSE_PLATE_NO] [varchar] (20) NULL ,
> [PRODUCT_ID] [varchar] (40) NULL ,
> [PERFORMED_BY] [varchar] (30) NULL ,
> [ORDER_ID] [varchar] (30) NULL ,
> [ORDER_TYPE] [varchar] (2) NULL ,
> [ORDER_LINE_NO] [decimal](9, 0) NULL ,
> [EXPECTED_RECEIPT_NO] [varchar] (12) NULL ,
> [EXPECTED_RECEIPT_TYPE] [varchar] (3) NULL ,
> [ERD_LINE_NO] [decimal](9, 0) NULL ,
> [REC_ID] [decimal](9, 0) NULL ,
> [RECEIVER_TYPE] [varchar] (3) NULL ,
> [TASK_ID] [decimal](9, 0) NULL ,
> [SOURCE_LOCATION_NO] [varchar] (20) NULL ,
> [DESTINATION_LOCATION_NO] [varchar] (20) NULL ,
> [DROP_LOCATION_NO] [varchar] (20) NULL ,
> [EXPECTED_QUANTITY] [decimal](9, 0) NULL ,
> [ACTUAL_QUANTITY] [decimal](9, 0) NULL ,
> [EXPECTED_UOM] [decimal](2, 0) NULL ,
> [ACTUAL_UOM] [decimal](2, 0) NULL ,
> [UOM_FAMILY] [decimal](1, 0) NULL ,
> [OLD_MSC] [varchar] (3) NULL ,
> [NEW_MSC] [varchar] (3) NULL ,
> [OLD_MKR] [varchar] (20) NULL ,
> [NEW_MKR] [varchar] (20) NULL ,
> [INVENTORY_ID] [decimal](9, 0) NULL ,
> [PRODUCT_KEY] [varchar] (250) NULL ,
> [OLD_INVENTORY_STATUS] [varchar] (3) NULL ,
> [NEW_INVENTORY_STATUS] [varchar] (3) NULL ,
> [HOLD_IND] [varchar] (1) NULL ,
> [REASON_CODE] [varchar] (20) NULL ,
> [ADJUSTMENT_MESSAGE] [varchar] (20) NULL ,
> [RELEASE_GROUP_ID] [decimal](9, 0) NULL ,
> [DD_INSTANCE_ID] [decimal](9, 0) NULL ,
> [UPLOAD_FILE_NAME] [varchar] (30) NULL ,
> [UPLOAD_IND] [varchar] (1) NULL ,
> [LOGGING_SOURCE] [varchar] (80) NULL ,
> [REQUEST_TRANS_NO] [decimal](10, 0) NULL ,
> [REQUEST_TRANS_SEQ_NO] [decimal](5, 0) NULL ,
> [SUCCESS_IND] [varchar] (1) NULL ,
> [HOST_REFERENCE] [varchar] (40) NULL ,
> [EXPIRY_DATE] [datetime] NULL ,
> [LOT_ID] [varchar] (20) NULL ,
> [BRANCH] [varchar] (20) NULL ,
> [COUNTRY_OF_ORIGIN] [varchar] (20) NULL ,
> [VENDOR_ID] [varchar] (20) NULL ,
> [MANUFACTURING_DATE] [datetime] NULL ,
> [ATTRIBUTE1] [varchar] (20) NULL ,
> [ATTRIBUTE2] [varchar] (20) NULL ,
> [ATTRIBUTE3] [varchar] (20) NULL ,
> [ATTRIBUTE4] [varchar] (20) NULL ,
> [ATTRIBUTE5] [varchar] (20) NULL ,
> [ATTRIBUTE6] [varchar] (20) NULL ,
> [ATTRIBUTE7] [varchar] (20) NULL ,
> [ATTRIBUTE8] [varchar] (20) NULL ,
> [ATTRIBUTE9] [varchar] (20) NULL ,
> [ATTRIBUTE10] [varchar] (20) NULL ,
> [ATTRIBUTE11] [varchar] (20) NULL ,
> [ATTRIBUTE12] [varchar] (20) NULL ,
> [ATTRIBUTE13] [varchar] (20) NULL ,
> [ATTRIBUTE14] [varchar] (20) NULL ,
> [ATTRIBUTE15] [varchar] (20) NULL ,
> [ATTRIBUTE16] [varchar] (20) NULL ,
> [ATTRIBUTE17] [varchar] (20) NULL ,
> [ATTRIBUTE18] [varchar] (20) NULL ,
> [ATTRIBUTE19] [varchar] (20) NULL ,
> [ATTRIBUTE20] [varchar] (20) NULL ,
> [REC_LINE_NO] [decimal](9, 0) NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Dock Dates Table] (
> [REC_ID] [int] NOT NULL ,
> [Dockdate & Time] [smalldatetime] NULL ,
> [Comments] [varchar] (255) NULL ,
> [UsrID] [varchar] (10) NULL ,
> [LastModUsrID] [varchar] (10) NULL ,
> [LastModDate] [datetime] NULL ,
> [InputDate] [datetime] NULL
> ) ON [PRIMARY]
> GO
> --
> SELECT DISTINCT
> R.REC_ID, R.TYPE, R.STATUS,
> RD.BRANCH, R.CREATE_DATE, ER.ERHE_GD_01 AS DOCK_DATE,
> R.RECEIVE_DATE,
> R.RECEIVE_DATE - ER.ERHE_GD_01 AS
> [VAR_REC-DOCKhrs], SUM(RD.RECEIVED_QTY) AS
> SumOfRECEIVED_QTY, ER.ER_NO
> FROM MOVEPROD.dbo.RECEIVER R LEFT OUTER JOIN
> MOVEPROD.dbo.RECEIVER_DETAIL RD ON
> R.REC_ID = RD.REC_ID LEFT OUTER JOIN
> MOVEPROD.dbo.EXPECTED_RECEIPT ER ON
> R.ER_ID = ER.ER_ID
> WHERE (R.STATUS = 'CLO')
> GROUP BY R.REC_ID, R.TYPE, R.STATUS, RD.BRANCH,
> R.CREATE_DATE, ER.ERHE_GD_01, R.RECEIVE_DATE, ER.ER_NO,
> R.RECEIVE_DATE - ER.ERHE_GD_01
>
> >3. Do you have the same collation in both installation?
> **2000--SQL_Latin1_General_CP1_CI_AS
> **7.0--'
> >
> >Quentin
> >
> >"Becky Bowen" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:012101c3dad9$79dd71e0$a601280a@.phx.gbl...
> >> I recently migrated my databases to a box with SQL
> Server
> >> 2000. This query (which is actually a view) returns
> the
> >> right results in 7, but in 2000, the [Dockdate & Time]
> >> and [Variance REC-DOCK hours] fields always return
> NULL.
> >> I have narrowed the problem down to the WHERE clause,
> but
> >> can't figure out how to resolve it. I have tried
> >> replacing WHERE with AND, which *allows* the fields to
> >> return non-NULL values, but the query retrieves over
> 1000
> >> records instead of the 42 that it is supposed to.
> >>
> >> Please Help!
> >>
> >>
> >> SELECT DISTINCT
> >> DDRD.REC_ID,
> >> DDRD.TYPE,
> >> DDRD.STATUS,
> >> DDRD.BRANCH,
> >> DDRD.CREATE_DATE,
> >> DDRD.DOCK_DATE,
> >> DD.[Dockdate & Time],
> >> MIN(T.TRANSACTION_DATE) AS [Manifest Open],
> >> DDRD.RECEIVE_DATE, --CONVERT(varchar,
> >> ddrd.RECEIVE_DATE, 110),
> >> CONVERT(VARCHAR(3),DATEPART(D,CONVERT(DATETIME,
> >> (CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.[Dockdate &
> >> Time]), 1))))-1) + ' Days, ' + CONVERT(CHAR(8),CONVERT
> >> (DATETIME,(CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.
> >> [Dockdate & Time]), 1))),8) AS [VAR_REC-DOCKhrs],
> >> DDRD.SumOfRECEIVED_QTY,
> >> DDRD.ER_NO
> >> FROM DockDates.dbo.[Dock Dates Table] DD
> >> RIGHT JOIN [rec-Dock Date by Receive Date] DDRD
> >> ON (DD.REC_ID = DDRD.REC_ID)
> >> LEFT JOIN [MOVEPROD].[dbo].[TRANSACTION] T ON
> >> (DDRD.REC_ID = T.REC_ID)
> >> WHERE(ddrd.RECEIVE_DATE BETWEEN '01-09-2004' AND '01-
> 12-
> >> 2004')
> >> GROUP BY DDRD.REC_ID,
> >> DDRD.TYPE,
> >> DDRD.STATUS,
> >> DDRD.BRANCH,
> >> DDRD.CREATE_DATE,
> >> DDRD.DOCK_DATE,
> >> DD.[Dockdate & Time],
> >> DDRD.RECEIVE_DATE,
> >> DDRD.[VAR_REC-DOCKhrs],
> >> DDRD.SumOfRECEIVED_QTY,
> >> DDRD.ER_NO
> >> ORDER BY DDRD.REC_ID
> >
> >
> >.
> >|||The chain breaks at [Variance REC-DOCK hours]
The collation is the same throughout the 2000 Server as
well as 7.0
Thanks again.
>--Original Message--
>1. Depending on your null setting, changing from where
to and can indeed
>cause change of the query. I believe I saw other
situations, but don't
>remember which exactly. Anyway, AND can give different
query than WHERE.
>2. It is likely that you have the same collation between
the two
>servers/db/table column (remember that in SS2K you can
set collation down to
>column) since the one you give for 2K is the same as SS7
default. but a
>verification would help, also verify in SS2K there is no
change from
>collation default.
>3. [Variance REC-DOCK hours] is derived from ... which
is derived from ...
>Some where the chain broke in your DDL. I am not saying
that that was the
>cause since I can not explain why [Dockdate & Time] also
became null. But
>you may want to follow back with those ones that are
correct in SS7 but
>wrong in 2K step by step.
>Quentin
>"Becky" <anonymous@.discussions.microsoft.com> wrote in
message
>news:02ed01c3daea$de02be30$a501280a@.phx.gbl...
>> Thanks in advance for your help!!
>> >--Original Message--
>> >Becky,
>> >
>> >1. what is wrong in your results? Is it the number of
>> records returned, or
>> **Wrong number of records using AND
>> **Wrong results (Null fields) using WHERE
>> >the values returned for [Dockdate & Time] and
[Variance
>> REC-DOCK hours], or
>> >both?
>> **Both
>> >2. can you provide DDL for the tables?
>> ** Alright...you asked for it...
>> CREATE TABLE [dbo].[TRANSACTION] (
>> [TRANSACTION_ID] [decimal](9, 0) NOT NULL ,
>> [TRANSACTION_TYPE] [varchar] (3) NOT NULL ,
>> [TRANSACTION_DATE] [datetime] NULL ,
>> [SOURCE_LICENSE_PLATE_NO] [varchar] (20) NULL ,
>> [DEST_LICENSE_PLATE_NO] [varchar] (20) NULL ,
>> [PRODUCT_ID] [varchar] (40) NULL ,
>> [PERFORMED_BY] [varchar] (30) NULL ,
>> [ORDER_ID] [varchar] (30) NULL ,
>> [ORDER_TYPE] [varchar] (2) NULL ,
>> [ORDER_LINE_NO] [decimal](9, 0) NULL ,
>> [EXPECTED_RECEIPT_NO] [varchar] (12) NULL ,
>> [EXPECTED_RECEIPT_TYPE] [varchar] (3) NULL ,
>> [ERD_LINE_NO] [decimal](9, 0) NULL ,
>> [REC_ID] [decimal](9, 0) NULL ,
>> [RECEIVER_TYPE] [varchar] (3) NULL ,
>> [TASK_ID] [decimal](9, 0) NULL ,
>> [SOURCE_LOCATION_NO] [varchar] (20) NULL ,
>> [DESTINATION_LOCATION_NO] [varchar] (20) NULL ,
>> [DROP_LOCATION_NO] [varchar] (20) NULL ,
>> [EXPECTED_QUANTITY] [decimal](9, 0) NULL ,
>> [ACTUAL_QUANTITY] [decimal](9, 0) NULL ,
>> [EXPECTED_UOM] [decimal](2, 0) NULL ,
>> [ACTUAL_UOM] [decimal](2, 0) NULL ,
>> [UOM_FAMILY] [decimal](1, 0) NULL ,
>> [OLD_MSC] [varchar] (3) NULL ,
>> [NEW_MSC] [varchar] (3) NULL ,
>> [OLD_MKR] [varchar] (20) NULL ,
>> [NEW_MKR] [varchar] (20) NULL ,
>> [INVENTORY_ID] [decimal](9, 0) NULL ,
>> [PRODUCT_KEY] [varchar] (250) NULL ,
>> [OLD_INVENTORY_STATUS] [varchar] (3) NULL ,
>> [NEW_INVENTORY_STATUS] [varchar] (3) NULL ,
>> [HOLD_IND] [varchar] (1) NULL ,
>> [REASON_CODE] [varchar] (20) NULL ,
>> [ADJUSTMENT_MESSAGE] [varchar] (20) NULL ,
>> [RELEASE_GROUP_ID] [decimal](9, 0) NULL ,
>> [DD_INSTANCE_ID] [decimal](9, 0) NULL ,
>> [UPLOAD_FILE_NAME] [varchar] (30) NULL ,
>> [UPLOAD_IND] [varchar] (1) NULL ,
>> [LOGGING_SOURCE] [varchar] (80) NULL ,
>> [REQUEST_TRANS_NO] [decimal](10, 0) NULL ,
>> [REQUEST_TRANS_SEQ_NO] [decimal](5, 0) NULL ,
>> [SUCCESS_IND] [varchar] (1) NULL ,
>> [HOST_REFERENCE] [varchar] (40) NULL ,
>> [EXPIRY_DATE] [datetime] NULL ,
>> [LOT_ID] [varchar] (20) NULL ,
>> [BRANCH] [varchar] (20) NULL ,
>> [COUNTRY_OF_ORIGIN] [varchar] (20) NULL ,
>> [VENDOR_ID] [varchar] (20) NULL ,
>> [MANUFACTURING_DATE] [datetime] NULL ,
>> [ATTRIBUTE1] [varchar] (20) NULL ,
>> [ATTRIBUTE2] [varchar] (20) NULL ,
>> [ATTRIBUTE3] [varchar] (20) NULL ,
>> [ATTRIBUTE4] [varchar] (20) NULL ,
>> [ATTRIBUTE5] [varchar] (20) NULL ,
>> [ATTRIBUTE6] [varchar] (20) NULL ,
>> [ATTRIBUTE7] [varchar] (20) NULL ,
>> [ATTRIBUTE8] [varchar] (20) NULL ,
>> [ATTRIBUTE9] [varchar] (20) NULL ,
>> [ATTRIBUTE10] [varchar] (20) NULL ,
>> [ATTRIBUTE11] [varchar] (20) NULL ,
>> [ATTRIBUTE12] [varchar] (20) NULL ,
>> [ATTRIBUTE13] [varchar] (20) NULL ,
>> [ATTRIBUTE14] [varchar] (20) NULL ,
>> [ATTRIBUTE15] [varchar] (20) NULL ,
>> [ATTRIBUTE16] [varchar] (20) NULL ,
>> [ATTRIBUTE17] [varchar] (20) NULL ,
>> [ATTRIBUTE18] [varchar] (20) NULL ,
>> [ATTRIBUTE19] [varchar] (20) NULL ,
>> [ATTRIBUTE20] [varchar] (20) NULL ,
>> [REC_LINE_NO] [decimal](9, 0) NULL
>> ) ON [PRIMARY]
>> GO
>> CREATE TABLE [dbo].[Dock Dates Table] (
>> [REC_ID] [int] NOT NULL ,
>> [Dockdate & Time] [smalldatetime] NULL ,
>> [Comments] [varchar] (255) NULL ,
>> [UsrID] [varchar] (10) NULL ,
>> [LastModUsrID] [varchar] (10) NULL ,
>> [LastModDate] [datetime] NULL ,
>> [InputDate] [datetime] NULL
>> ) ON [PRIMARY]
>> GO
>> --
>> SELECT DISTINCT
>> R.REC_ID, R.TYPE, R.STATUS,
>> RD.BRANCH, R.CREATE_DATE, ER.ERHE_GD_01 AS DOCK_DATE,
>> R.RECEIVE_DATE,
>> R.RECEIVE_DATE - ER.ERHE_GD_01 AS
>> [VAR_REC-DOCKhrs], SUM(RD.RECEIVED_QTY) AS
>> SumOfRECEIVED_QTY, ER.ER_NO
>> FROM MOVEPROD.dbo.RECEIVER R LEFT OUTER JOIN
>> MOVEPROD.dbo.RECEIVER_DETAIL RD
ON
>> R.REC_ID = RD.REC_ID LEFT OUTER JOIN
>> MOVEPROD.dbo.EXPECTED_RECEIPT ER
ON
>> R.ER_ID = ER.ER_ID
>> WHERE (R.STATUS = 'CLO')
>> GROUP BY R.REC_ID, R.TYPE, R.STATUS, RD.BRANCH,
>> R.CREATE_DATE, ER.ERHE_GD_01, R.RECEIVE_DATE, ER.ER_NO,
>> R.RECEIVE_DATE - ER.ERHE_GD_01
>>
>> >3. Do you have the same collation in both
installation?
>> **2000--SQL_Latin1_General_CP1_CI_AS
>> **7.0--'
>> >
>> >Quentin
>> >
>> >"Becky Bowen" <anonymous@.discussions.microsoft.com>
>> wrote in message
>> >news:012101c3dad9$79dd71e0$a601280a@.phx.gbl...
>> >> I recently migrated my databases to a box with SQL
>> Server
>> >> 2000. This query (which is actually a view) returns
>> the
>> >> right results in 7, but in 2000, the [Dockdate &
Time]
>> >> and [Variance REC-DOCK hours] fields always return
>> NULL.
>> >> I have narrowed the problem down to the WHERE
clause,
>> but
>> >> can't figure out how to resolve it. I have tried
>> >> replacing WHERE with AND, which *allows* the fields
to
>> >> return non-NULL values, but the query retrieves over
>> 1000
>> >> records instead of the 42 that it is supposed to.
>> >>
>> >> Please Help!
>> >>
>> >>
>> >> SELECT DISTINCT
>> >> DDRD.REC_ID,
>> >> DDRD.TYPE,
>> >> DDRD.STATUS,
>> >> DDRD.BRANCH,
>> >> DDRD.CREATE_DATE,
>> >> DDRD.DOCK_DATE,
>> >> DD.[Dockdate & Time],
>> >> MIN(T.TRANSACTION_DATE) AS [Manifest Open],
>> >> DDRD.RECEIVE_DATE, --CONVERT(varchar,
>> >> ddrd.RECEIVE_DATE, 110),
>> >> CONVERT(VARCHAR(3),DATEPART(D,CONVERT(DATETIME,
>> >> (CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.[Dockdate
&
>> >> Time]), 1))))-1) + ' Days, ' + CONVERT(CHAR
(8),CONVERT
>> >> (DATETIME,(CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.
>> >> [Dockdate & Time]), 1))),8) AS [VAR_REC-DOCKhrs],
>> >> DDRD.SumOfRECEIVED_QTY,
>> >> DDRD.ER_NO
>> >> FROM DockDates.dbo.[Dock Dates Table] DD
>> >> RIGHT JOIN [rec-Dock Date by Receive Date] DDRD
>> >> ON (DD.REC_ID = DDRD.REC_ID)
>> >> LEFT JOIN [MOVEPROD].[dbo].[TRANSACTION] T ON
>> >> (DDRD.REC_ID = T.REC_ID)
>> >> WHERE(ddrd.RECEIVE_DATE BETWEEN '01-09-2004'
AND '01-
>> 12-
>> >> 2004')
>> >> GROUP BY DDRD.REC_ID,
>> >> DDRD.TYPE,
>> >> DDRD.STATUS,
>> >> DDRD.BRANCH,
>> >> DDRD.CREATE_DATE,
>> >> DDRD.DOCK_DATE,
>> >> DD.[Dockdate & Time],
>> >> DDRD.RECEIVE_DATE,
>> >> DDRD.[VAR_REC-DOCKhrs],
>> >> DDRD.SumOfRECEIVED_QTY,
>> >> DDRD.ER_NO
>> >> ORDER BY DDRD.REC_ID
>> >
>> >
>> >.
>> >
>
>.
>|||What datatype is ddrd.RECEIVE_DATE? If it is smalldatetime, then you
could try changing "BETWEEN '01-09-2004' AND '01-12-2004'" to "BETWEEN
CAST('01-09-2004' AS smalldatetime) AND CAST('01-12-2004' AS
smalldatetime)".
Hope this helps,
Gert-Jan
Becky Bowen wrote:
> I recently migrated my databases to a box with SQL Server
> 2000. This query (which is actually a view) returns the
> right results in 7, but in 2000, the [Dockdate & Time]
> and [Variance REC-DOCK hours] fields always return NULL.
> I have narrowed the problem down to the WHERE clause, but
> can't figure out how to resolve it. I have tried
> replacing WHERE with AND, which *allows* the fields to
> return non-NULL values, but the query retrieves over 1000
> records instead of the 42 that it is supposed to.
> Please Help!
> SELECT DISTINCT
> DDRD.REC_ID,
> DDRD.TYPE,
> DDRD.STATUS,
> DDRD.BRANCH,
> DDRD.CREATE_DATE,
> DDRD.DOCK_DATE,
> DD.[Dockdate & Time],
> MIN(T.TRANSACTION_DATE) AS [Manifest Open],
> DDRD.RECEIVE_DATE, --CONVERT(varchar,
> ddrd.RECEIVE_DATE, 110),
> CONVERT(VARCHAR(3),DATEPART(D,CONVERT(DATETIME,
> (CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.[Dockdate &
> Time]), 1))))-1) + ' Days, ' + CONVERT(CHAR(8),CONVERT
> (DATETIME,(CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.
> [Dockdate & Time]), 1))),8) AS [VAR_REC-DOCKhrs],
> DDRD.SumOfRECEIVED_QTY,
> DDRD.ER_NO
> FROM DockDates.dbo.[Dock Dates Table] DD
> RIGHT JOIN [rec-Dock Date by Receive Date] DDRD
> ON (DD.REC_ID = DDRD.REC_ID)
> LEFT JOIN [MOVEPROD].[dbo].[TRANSACTION] T ON
> (DDRD.REC_ID = T.REC_ID)
> WHERE(ddrd.RECEIVE_DATE BETWEEN '01-09-2004' AND '01-12-
> 2004')
> GROUP BY DDRD.REC_ID,
> DDRD.TYPE,
> DDRD.STATUS,
> DDRD.BRANCH,
> DDRD.CREATE_DATE,
> DDRD.DOCK_DATE,
> DD.[Dockdate & Time],
> DDRD.RECEIVE_DATE,
> DDRD.[VAR_REC-DOCKhrs],
> DDRD.SumOfRECEIVED_QTY,
> DDRD.ER_NO
> ORDER BY DDRD.REC_ID|||Along these same lines, you might go one step further and replace
'01-09-2004' with
convert(smalldatetime, '01-09-2004', 110)
or
convert(smalldatetime, '01-09-2004', 105)
depending on whether this date is supposed to be January 9, 2004 or
September 1, 2004, respectively.
SK
Gert-Jan Strik wrote:
>What datatype is ddrd.RECEIVE_DATE? If it is smalldatetime, then you
>could try changing "BETWEEN '01-09-2004' AND '01-12-2004'" to "BETWEEN
>CAST('01-09-2004' AS smalldatetime) AND CAST('01-12-2004' AS
>smalldatetime)".
>Hope this helps,
>Gert-Jan
>
>Becky Bowen wrote:
>
>>I recently migrated my databases to a box with SQL Server
>>2000. This query (which is actually a view) returns the
>>right results in 7, but in 2000, the [Dockdate & Time]
>>and [Variance REC-DOCK hours] fields always return NULL.
>>I have narrowed the problem down to the WHERE clause, but
>>can't figure out how to resolve it. I have tried
>>replacing WHERE with AND, which *allows* the fields to
>>return non-NULL values, but the query retrieves over 1000
>>records instead of the 42 that it is supposed to.
>>Please Help!
>>SELECT DISTINCT
>> DDRD.REC_ID,
>> DDRD.TYPE,
>> DDRD.STATUS,
>> DDRD.BRANCH,
>> DDRD.CREATE_DATE,
>> DDRD.DOCK_DATE,
>> DD.[Dockdate & Time],
>> MIN(T.TRANSACTION_DATE) AS [Manifest Open],
>> DDRD.RECEIVE_DATE, --CONVERT(varchar,
>>ddrd.RECEIVE_DATE, 110),
>> CONVERT(VARCHAR(3),DATEPART(D,CONVERT(DATETIME,
>>(CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.[Dockdate &
>>Time]), 1))))-1) + ' Days, ' + CONVERT(CHAR(8),CONVERT
>>(DATETIME,(CONVERT(MONEY, (DDRD.[RECEIVE_DATE] - DD.
>>[Dockdate & Time]), 1))),8) AS [VAR_REC-DOCKhrs],
>> DDRD.SumOfRECEIVED_QTY,
>> DDRD.ER_NO
>>FROM DockDates.dbo.[Dock Dates Table] DD
>> RIGHT JOIN [rec-Dock Date by Receive Date] DDRD
>>ON (DD.REC_ID = DDRD.REC_ID)
>> LEFT JOIN [MOVEPROD].[dbo].[TRANSACTION] T ON
>>(DDRD.REC_ID = T.REC_ID)
>>WHERE(ddrd.RECEIVE_DATE BETWEEN '01-09-2004' AND '01-12-
>>2004')
>>GROUP BY DDRD.REC_ID,
>> DDRD.TYPE,
>> DDRD.STATUS,
>> DDRD.BRANCH,
>> DDRD.CREATE_DATE,
>> DDRD.DOCK_DATE,
>> DD.[Dockdate & Time],
>> DDRD.RECEIVE_DATE,
>> DDRD.[VAR_REC-DOCKhrs],
>> DDRD.SumOfRECEIVED_QTY,
>> DDRD.ER_NO
>>ORDER BY DDRD.REC_ID
>>