disable all triggers: databases

Showing posts with label databases. Show all posts

Monday, March 19, 2012

Differing results on identical systems.

I have a staging environment and a developement environment set up on the same machine.
Both environments consist of two databases, a staging area and a data warehouse.

On the staging system when I run a query of the form:
select
count(*)
from [StagingArea].[dbo].[saTable]
join [DataWarehouse].[dbo].[dwView] on saTable.col = dwView.col
join [DataWarehouse].[dbo].[dwTable1] on dwTable1.col = dwView.col
join [DataWarehouse].[dbo].[dwTable2] on (dwTable1.col = dwTable2.col AND dwTable2.col = saTable.col)
I get an answer returned in 2 seconds.

When I run the same query on the developement system it never comes back with an answer (after 3+ hours).

The data in both systems is identical. But it would appear that the query optimizer is behaving differently on the two identical systems.

I am looking for a way to enforce the same behavior in both systems. Any tips would be appreciated.

Thanks.
-PMP

have u checked statistics on development , there may be lot of insert/update/delete action going on Develpment server for testing purpose. So reindex the tables invoved and run sp_updatestats.

Madhu

Differential SQL backups ? is this possible?

Hello, I am looking to backup one of our MS Sql databases offsite..
and because our databases are so large, and we do not want to consume
lots of bandwidth everynight during backups.. I was wondering:
Is it possible to backup only changes for a db?
thanks!
Lee
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Replication-...ict229803.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=795309
Have a look at transaction log and differential backups in BOL.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Differential maintenance in plan sql 2000: setup

There are maint plans in 2000, which you reach from the Maintenance Plans folder in EM. A 2000 maint
plan do not include diff backups, though.
Right-licking a db and select Backup and from there selecting Schedule do *not* create a maint plan.
So if you want to include stuff like naming the backup file with time stamp and deletion of old
backups, you have to do it yourself. There are plenty of code out there to get your started, for
instance below (2005, but adaptable for 2000):
http://www.karaszi.com/SQLServer/util_backup_script_like_MP.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"scottg" <scottgranado@.gmail.com> wrote in message
news:f9f653f3-f0c6-4cb8-ae3e-4694a380293f@.n58g2000hsf.googlegroups.com...
> Hello!
> I'm trying to setup a differential maintenance plan to backup all
> users databases on sql 2000 using t-sql (or not)
> I can't figure out or find how to include "all user databases" like i
> would using a maintenance plan. This is a cake to do in sql 2005 but
> sql 2000.... well lets just say

Differential maintenance in plan sql 2000: setup

Hello!
I'm trying to setup a differential maintenance plan to backup all
users databases on sql 2000 using t-sql (or not)
I can't figure out or find how to include "all user databases" like i
would using a maintenance plan. This is a cake to do in sql 2005 but
sql 2000.... well lets just say :(There are maint plans in 2000, which you reach from the Maintenance Plans folder in EM. A 2000 maint
plan do not include diff backups, though.
Right-licking a db and select Backup and from there selecting Schedule do *not* create a maint plan.
So if you want to include stuff like naming the backup file with time stamp and deletion of old
backups, you have to do it yourself. There are plenty of code out there to get your started, for
instance below (2005, but adaptable for 2000):
http://www.karaszi.com/SQLServer/util_backup_script_like_MP.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"scottg" <scottgranado@.gmail.com> wrote in message
news:f9f653f3-f0c6-4cb8-ae3e-4694a380293f@.n58g2000hsf.googlegroups.com...
> Hello!
> I'm trying to setup a differential maintenance plan to backup all
> users databases on sql 2000 using t-sql (or not)
> I can't figure out or find how to include "all user databases" like i
> would using a maintenance plan. This is a cake to do in sql 2005 but
> sql 2000.... well lets just say :(

Sunday, March 11, 2012

Differential Backups

I have set up differential backups for six of my critical databases to run every two hours, between certain times of the day. I have set them up bt creating the job in TSQL using append, but I want to set an option to delete/remove entries after 2 days like you can in Enterprise Manager. (In Enterprise Manager I can ony set up full backups) How can I accomplish this
Thanks
TinaI think you need to add the following in the parameter:
-DelBkUps 2DAYS
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures

Differential Backups

I have set up differential backups for six of my critical databases to run e
very two hours, between certain times of the day. I have set them up bt crea
ting the job in TSQL using append, but I want to set an option to delete/rem
ove entries after 2 days li
ke you can in Enterprise Manager. (In Enterprise Manager I can ony set up f
ull backups) How can I accomplish this?
Thanks,
TinaI think you need to add the following in the parameter:
-DelBkUps 2DAYS
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures

Differential backup with database maintenance plan

Hello,
I need to take daily differential backups of eight
databases. I was thinking of using a DB Maintenance Plan,
but the problem is it allows only full backups. Is there a
simple way of taking differential backups of multiple
db's? For example is it possible to modify the full backup
query of DB Maintenance Plan and use it as differential?
The query of DB Maintenance Plan full backup is like below:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 8CD36FED-A3BC-
43D1-91E5-33A8C02A3FEA -WriteHistory -VrfyBackup -
BkUpMedia DISK -BkUpDB "C:\DB\Backup\2004\full"
-CrBkSubDir -BkExt "BAK"'
ThanksMaint plans doesn't support diff backups. Db Maint does (www.dbmaint.com),
or write your own TSQL command and schedule them using Agent, quite simply
:-).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"keremcan" <kbuyuktaskin@.yahoo.com> wrote in message
news:2da501c3e183$8843dca0$a401280a@.phx.gbl...
> Hello,
> I need to take daily differential backups of eight
> databases. I was thinking of using a DB Maintenance Plan,
> but the problem is it allows only full backups. Is there a
> simple way of taking differential backups of multiple
> db's? For example is it possible to modify the full backup
> query of DB Maintenance Plan and use it as differential?
> The query of DB Maintenance Plan full backup is like below:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID 8CD36FED-A3BC-
> 43D1-91E5-33A8C02A3FEA -WriteHistory -VrfyBackup -
> BkUpMedia DISK -BkUpDB "C:\DB\Backup\2004\full"
> -CrBkSubDir -BkExt "BAK"'
> Thanks|||Hi,
Here is a link to T-SQL code wrote by Uma Chandar - MVP.
Script to do Differential backups on weekdays, Full on
sundays & filenames with timestamp info
http://www.umachandar.com/technical/SQL70Scripts/Main30.htm
as Tibor said you can schedule this script by SQL Agent.
HTH
Regards
THIRUMAL REDDY MARAM
Sys Admin/ SQL Server DBA
>--Original Message--
>Maint plans doesn't support diff backups. Db Maint does
(www.dbmaint.com),
>or write your own TSQL command and schedule them using
Agent, quite simply
>:-).
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"keremcan" <kbuyuktaskin@.yahoo.com> wrote in message
>news:2da501c3e183$8843dca0$a401280a@.phx.gbl...
>> Hello,
>> I need to take daily differential backups of eight
>> databases. I was thinking of using a DB Maintenance
Plan,
>> but the problem is it allows only full backups. Is
there a
>> simple way of taking differential backups of multiple
>> db's? For example is it possible to modify the full
backup
>> query of DB Maintenance Plan and use it as differential?
>> The query of DB Maintenance Plan full backup is like
below:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID 8CD36FED-A3BC-
>> 43D1-91E5-33A8C02A3FEA -WriteHistory -VrfyBackup -
>> BkUpMedia DISK -BkUpDB "C:\DB\Backup\2004\full"
>> -CrBkSubDir -BkExt "BAK"'
>> Thanks
>
>.
>|||Or you can simply create a job with this script:
BACKUP DATABASE [yourdb] TO DISK
= 'backupdrive\yourdb.bak' WITH INIT, NAME = N'yourdb
backup'
GO
BACKUP DATABASE [yourdb2] TO DISK
= 'backupdrive\yourdb.bak' WITH INIT, NAME = N'yourdb2
backup'
GO
--and so on and so forth--
>--Original Message--
>Hello,
>I need to take daily differential backups of eight
>databases. I was thinking of using a DB Maintenance Plan,
>but the problem is it allows only full backups. Is there
a
>simple way of taking differential backups of multiple
>db's? For example is it possible to modify the full
backup
>query of DB Maintenance Plan and use it as differential?
>The query of DB Maintenance Plan full backup is like
below:
>EXECUTE master.dbo.xp_sqlmaint N'-PlanID 8CD36FED-A3BC-
>43D1-91E5-33A8C02A3FEA -WriteHistory -VrfyBackup -
>BkUpMedia DISK -BkUpDB "C:\DB\Backup\2004\full"
>-CrBkSubDir -BkExt "BAK"'
>Thanks
>.
>

Differential Backup disproportionately large

Hello,
Just recently I noticed that on one of our databases, the diff backups
are HUGE compared to the full backups.
For example, the regular full backup is only 80 MB, however the diff
backups are 800 MB to 1.2 GB in size. The activity on this database is
low. Our other databases are not exhibiting this anomaly, whereas they
have much higher activiity.
Is there any logical explanation for this? How I can see what's going
on with the diff backups?
Thanks muchAre you sure you don't have several backups on the same file? Use RESTORE HEADERONLY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1161877716.372685.197900@.h48g2000cwc.googlegroups.com...
> Hello,
> Just recently I noticed that on one of our databases, the diff backups
> are HUGE compared to the full backups.
> For example, the regular full backup is only 80 MB, however the diff
> backups are 800 MB to 1.2 GB in size. The activity on this database is
> low. Our other databases are not exhibiting this anomaly, whereas they
> have much higher activiity.
> Is there any logical explanation for this? How I can see what's going
> on with the diff backups?
> Thanks much
>|||No, it's not possible. For example, I just now deleted all the DIFF
backups and started from zero. I ran the differential backup again. It
ran for about 5 minutes and created a diff backup that is 1.6 GB in
size! The db itself is only 90 MB in size, and the log is 137 MB in
size.
? Not sure how to go about troubleshooting this one... never seen
this problem before.
Tibor Karaszi wrote:
> Are you sure you don't have several backups on the same file? Use RESTORE HEADERONLY.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <tootsuite@.gmail.com> wrote in message news:1161877716.372685.197900@.h48g2000cwc.googlegroups.com...
> > Hello,
> >
> > Just recently I noticed that on one of our databases, the diff backups
> > are HUGE compared to the full backups.
> >
> > For example, the regular full backup is only 80 MB, however the diff
> > backups are 800 MB to 1.2 GB in size. The activity on this database is
> > low. Our other databases are not exhibiting this anomaly, whereas they
> > have much higher activiity.
> >
> > Is there any logical explanation for this? How I can see what's going
> > on with the diff backups?
> >
> > Thanks much
> >|||Strange... What does RESTORE HEADERONLY and RESTORE FILELISTONLY say? Anything out of the ordinary?
Does the size go down after doing a database backup?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1161878879.100901.263140@.k70g2000cwa.googlegroups.com...
> No, it's not possible. For example, I just now deleted all the DIFF
> backups and started from zero. I ran the differential backup again. It
> ran for about 5 minutes and created a diff backup that is 1.6 GB in
> size! The db itself is only 90 MB in size, and the log is 137 MB in
> size.
> ? Not sure how to go about troubleshooting this one... never seen
> this problem before.
>
>
> Tibor Karaszi wrote:
>> Are you sure you don't have several backups on the same file? Use RESTORE HEADERONLY.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> <tootsuite@.gmail.com> wrote in message
>> news:1161877716.372685.197900@.h48g2000cwc.googlegroups.com...
>> > Hello,
>> >
>> > Just recently I noticed that on one of our databases, the diff backups
>> > are HUGE compared to the full backups.
>> >
>> > For example, the regular full backup is only 80 MB, however the diff
>> > backups are 800 MB to 1.2 GB in size. The activity on this database is
>> > low. Our other databases are not exhibiting this anomaly, whereas they
>> > have much higher activiity.
>> >
>> > Is there any logical explanation for this? How I can see what's going
>> > on with the diff backups?
>> >
>> > Thanks much
>> >
>|||Hi Tibor, thanks for the suggestion, RESTORE FILELISTONLY revealed the
problem.
Tibor Karaszi wrote:
> Strange... What does RESTORE HEADERONLY and RESTORE FILELISTONLY say? Anything out of the ordinary?
> Does the size go down after doing a database backup?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <tootsuite@.gmail.com> wrote in message news:1161878879.100901.263140@.k70g2000cwa.googlegroups.com...
> > No, it's not possible. For example, I just now deleted all the DIFF
> > backups and started from zero. I ran the differential backup again. It
> > ran for about 5 minutes and created a diff backup that is 1.6 GB in
> > size! The db itself is only 90 MB in size, and the log is 137 MB in
> > size.
> >
> > ? Not sure how to go about troubleshooting this one... never seen
> > this problem before.
> >
> >
> >
> >
> > Tibor Karaszi wrote:
> >> Are you sure you don't have several backups on the same file? Use RESTORE HEADERONLY.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> <tootsuite@.gmail.com> wrote in message
> >> news:1161877716.372685.197900@.h48g2000cwc.googlegroups.com...
> >> > Hello,
> >> >
> >> > Just recently I noticed that on one of our databases, the diff backups
> >> > are HUGE compared to the full backups.
> >> >
> >> > For example, the regular full backup is only 80 MB, however the diff
> >> > backups are 800 MB to 1.2 GB in size. The activity on this database is
> >> > low. Our other databases are not exhibiting this anomaly, whereas they
> >> > have much higher activiity.
> >> >
> >> > Is there any logical explanation for this? How I can see what's going
> >> > on with the diff backups?
> >> >
> >> > Thanks much
> >> >
> >|||tootsuite@.gmail.com wrote:
> Hi Tibor, thanks for the suggestion, RESTORE FILELISTONLY revealed the
> problem.
>
Don't leave us hanging! What was the problem?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||It's embarassingly stupid, I'm afraid - was backing up the wrong
database :-)
Tracy McKibben wrote:
> tootsuite@.gmail.com wrote:
> > Hi Tibor, thanks for the suggestion, RESTORE FILELISTONLY revealed the
> > problem.
> >
> Don't leave us hanging! What was the problem?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||<tootsuite@.gmail.com> wrote in message
news:1161888872.392346.209340@.e3g2000cwe.googlegroups.com...
> It's embarassingly stupid, I'm afraid - was backing up the wrong
> database :-)
>
LOL!
If it's any consolation, you're not the only person who has done stuff like
that. :-)
> Tracy McKibben wrote:
>> tootsuite@.gmail.com wrote:
>> > Hi Tibor, thanks for the suggestion, RESTORE FILELISTONLY revealed the
>> > problem.
>> >
>> Don't leave us hanging! What was the problem?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>

Differential Backup disproportionately large

Friday, March 9, 2012

Different UPDATE behaviors across servers/databases

Hi!
I have a stored procedure that takes 22 minutes to run in one
environment, that only takes 1 sec or so to run in another
environment. Here is the exact situation:
Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
exactly the same, and the tables and index structures are exactly the
same. Implicit transactions are turned off on both databases.
Stored procedure:
BEGIN TRANSACTION
--step 1
TRUNCATE myTable
--step 2
INSERT INTO myTable VALUES ('myValues')
--step 3
UPDATE a
SET rating=AVG(someValues)
FROM myTable a
JOIN otherTable b
ON a.column1=b.column1
GROUP BY someColumns
COMMIT TRANSACTION
The update statement on the problem server is the only step that takes
forever. While it is running, I don't see anything that could be
blocking the statement. I used the following queries to determine if
there was another process blocking it:
select spid AS Blocked, blocked AS Blocking, waittime, cmd, substring
(nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where blocked <> 0
order by waittime desc
select dbid, name from sysdatabases where dbid in (select dbid from
master.dbo.sysprocesses where blocked <> 0)
select spid AS BlockingFromAbove, blocked AS TrueBlockingQuery,
waittime, cmd, substring (nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where spid in (select blocked from
master.dbo.sysprocesses where blocked <> 0)
order by waittime desc
When I change the UPDATE statement to a SELECT, it still takes longer
than it does on the test server (1 min 35 sec vs. several
milliseconds).
What could be causing the UPDATE to take forever on one server/
database, and run without a problem on another?
I am at a loss! Any help would be greatly appreciated.
Same execution plans on both server?
Also, when you say "data is exactly the same", does that mean whole database is identical, to the
point that one is a backup or attach of the other? If not, things like statistics can cause
different execution plans.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Baby Dragon" <nienna.gaia@.gmail.com> wrote in message
news:1177442708.864293.299400@.o40g2000prh.googlegr oups.com...
> Hi!
> I have a stored procedure that takes 22 minutes to run in one
> environment, that only takes 1 sec or so to run in another
> environment. Here is the exact situation:
> Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
> exactly the same, and the tables and index structures are exactly the
> same. Implicit transactions are turned off on both databases.
> Stored procedure:
> BEGIN TRANSACTION
> --step 1
> TRUNCATE myTable
> --step 2
> INSERT INTO myTable VALUES ('myValues')
> --step 3
> UPDATE a
> SET rating=AVG(someValues)
> FROM myTable a
> JOIN otherTable b
> ON a.column1=b.column1
> GROUP BY someColumns
> COMMIT TRANSACTION
> The update statement on the problem server is the only step that takes
> forever. While it is running, I don't see anything that could be
> blocking the statement. I used the following queries to determine if
> there was another process blocking it:
> select spid AS Blocked, blocked AS Blocking, waittime, cmd, substring
> (nt_username, 1, 15), dbid, physical_io,
> substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
> memusage
> from master.dbo.sysprocesses where blocked <> 0
> order by waittime desc
> select dbid, name from sysdatabases where dbid in (select dbid from
> master.dbo.sysprocesses where blocked <> 0)
> select spid AS BlockingFromAbove, blocked AS TrueBlockingQuery,
> waittime, cmd, substring (nt_username, 1, 15), dbid, physical_io,
> substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
> memusage
> from master.dbo.sysprocesses where spid in (select blocked from
> master.dbo.sysprocesses where blocked <> 0)
> order by waittime desc
> When I change the UPDATE statement to a SELECT, it still takes longer
> than it does on the test server (1 min 35 sec vs. several
> milliseconds).
> What could be causing the UPDATE to take forever on one server/
> database, and run without a problem on another?
> I am at a loss! Any help would be greatly appreciated.
>
|||I will take a look at the execution plans and any statistics that are
captured.
Thanks!

Different UPDATE behaviors across servers/databases

Hi!
I have a stored procedure that takes 22 minutes to run in one
environment, that only takes 1 sec or so to run in another
environment. Here is the exact situation:
Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
exactly the same, and the tables and index structures are exactly the
same. Implicit transactions are turned off on both databases.
Stored procedure:
BEGIN TRANSACTION
--step 1
TRUNCATE myTable
--step 2
INSERT INTO myTable VALUES ('myValues')
--step 3
UPDATE a
SET rating=AVG(someValues)
FROM myTable a
JOIN otherTable b
ON a.column1=b.column1
GROUP BY someColumns
COMMIT TRANSACTION
The update statement on the problem server is the only step that takes
forever. While it is running, I don't see anything that could be
blocking the statement. I used the following queries to determine if
there was another process blocking it:
select spid AS Blocked, blocked AS Blocking, waittime, cmd, substring
(nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where blocked <> 0
order by waittime desc
select dbid, name from sysdatabases where dbid in (select dbid from
master.dbo.sysprocesses where blocked <> 0)
select spid AS BlockingFromAbove, blocked AS TrueBlockingQuery,
waittime, cmd, substring (nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where spid in (select blocked from
master.dbo.sysprocesses where blocked <> 0)
order by waittime desc
When I change the UPDATE statement to a SELECT, it still takes longer
than it does on the test server (1 min 35 sec vs. several
milliseconds).
What could be causing the UPDATE to take forever on one server/
database, and run without a problem on another?
I am at a loss! Any help would be greatly appreciated.Same execution plans on both server?
Also, when you say "data is exactly the same", does that mean whole database
is identical, to the
point that one is a backup or attach of the other? If not, things like stati
stics can cause
different execution plans.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Baby Dragon" <nienna.gaia@.gmail.com> wrote in message
news:1177442708.864293.299400@.o40g2000prh.googlegroups.com...
> Hi!
> I have a stored procedure that takes 22 minutes to run in one
> environment, that only takes 1 sec or so to run in another
> environment. Here is the exact situation:
> Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
> exactly the same, and the tables and index structures are exactly the
> same. Implicit transactions are turned off on both databases.
> Stored procedure:
> BEGIN TRANSACTION
> --step 1
> TRUNCATE myTable
> --step 2
> INSERT INTO myTable VALUES ('myValues')
> --step 3
> UPDATE a
> SET rating=AVG(someValues)
> FROM myTable a
> JOIN otherTable b
> ON a.column1=b.column1
> GROUP BY someColumns
> COMMIT TRANSACTION
> The update statement on the problem server is the only step that takes
> forever. While it is running, I don't see anything that could be
> blocking the statement. I used the following queries to determine if
> there was another process blocking it:
> select spid AS Blocked, blocked AS Blocking, waittime, cmd, substring
> (nt_username, 1, 15), dbid, physical_io,
> substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
> memusage
> from master.dbo.sysprocesses where blocked <> 0
> order by waittime desc
> select dbid, name from sysdatabases where dbid in (select dbid from
> master.dbo.sysprocesses where blocked <> 0)
> select spid AS BlockingFromAbove, blocked AS TrueBlockingQuery,
> waittime, cmd, substring (nt_username, 1, 15), dbid, physical_io,
> substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
> memusage
> from master.dbo.sysprocesses where spid in (select blocked from
> master.dbo.sysprocesses where blocked <> 0)
> order by waittime desc
> When I change the UPDATE statement to a SELECT, it still takes longer
> than it does on the test server (1 min 35 sec vs. several
> milliseconds).
> What could be causing the UPDATE to take forever on one server/
> database, and run without a problem on another?
> I am at a loss! Any help would be greatly appreciated.
>|||I will take a look at the execution plans and any statistics that are
captured.
Thanks!

Different UPDATE behaviors across servers/databases

Hi!
I have a stored procedure that takes 22 minutes to run in one
environment, that only takes 1 sec or so to run in another
environment. Here is the exact situation:
Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
exactly the same, and the tables and index structures are exactly the
same. Implicit transactions are turned off on both databases.
Stored procedure:
BEGIN TRANSACTION
--step 1
TRUNCATE myTable
--step 2
INSERT INTO myTable VALUES ('myValues')
--step 3
UPDATE a
SET rating=AVG(someValues)
FROM myTable a
JOIN otherTable b
ON a.column1=b.column1
GROUP BY someColumns
COMMIT TRANSACTION
The update statement on the problem server is the only step that takes
forever. While it is running, I don't see anything that could be
blocking the statement. I used the following queries to determine if
there was another process blocking it:
select spid AS Blocked, blocked AS Blocking, waittime, cmd, substring
(nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where blocked <> 0
order by waittime desc
select dbid, name from sysdatabases where dbid in (select dbid from
master.dbo.sysprocesses where blocked <> 0)
select spid AS BlockingFromAbove, blocked AS TrueBlockingQuery,
waittime, cmd, substring (nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where spid in (select blocked from
master.dbo.sysprocesses where blocked <> 0)
order by waittime desc
When I change the UPDATE statement to a SELECT, it still takes longer
than it does on the test server (1 min 35 sec vs. several
milliseconds).
What could be causing the UPDATE to take forever on one server/
database, and run without a problem on another?
I am at a loss! Any help would be greatly appreciated.Same execution plans on both server?
Also, when you say "data is exactly the same", does that mean whole database is identical, to the
point that one is a backup or attach of the other? If not, things like statistics can cause
different execution plans.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Baby Dragon" <nienna.gaia@.gmail.com> wrote in message
news:1177442708.864293.299400@.o40g2000prh.googlegroups.com...
> Hi!
> I have a stored procedure that takes 22 minutes to run in one
> environment, that only takes 1 sec or so to run in another
> environment. Here is the exact situation:
> Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
> exactly the same, and the tables and index structures are exactly the
> same. Implicit transactions are turned off on both databases.
> Stored procedure:
> BEGIN TRANSACTION
> --step 1
> TRUNCATE myTable
> --step 2
> INSERT INTO myTable VALUES ('myValues')
> --step 3
> UPDATE a
> SET rating=AVG(someValues)
> FROM myTable a
> JOIN otherTable b
> ON a.column1=b.column1
> GROUP BY someColumns
> COMMIT TRANSACTION
> The update statement on the problem server is the only step that takes
> forever. While it is running, I don't see anything that could be
> blocking the statement. I used the following queries to determine if
> there was another process blocking it:
> select spid AS Blocked, blocked AS Blocking, waittime, cmd, substring
> (nt_username, 1, 15), dbid, physical_io,
> substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
> memusage
> from master.dbo.sysprocesses where blocked <> 0
> order by waittime desc
> select dbid, name from sysdatabases where dbid in (select dbid from
> master.dbo.sysprocesses where blocked <> 0)
> select spid AS BlockingFromAbove, blocked AS TrueBlockingQuery,
> waittime, cmd, substring (nt_username, 1, 15), dbid, physical_io,
> substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
> memusage
> from master.dbo.sysprocesses where spid in (select blocked from
> master.dbo.sysprocesses where blocked <> 0)
> order by waittime desc
> When I change the UPDATE statement to a SELECT, it still takes longer
> than it does on the test server (1 min 35 sec vs. several
> milliseconds).
> What could be causing the UPDATE to take forever on one server/
> database, and run without a problem on another?
> I am at a loss! Any help would be greatly appreciated.
>|||I will take a look at the execution plans and any statistics that are
captured.
Thanks!

Wednesday, March 7, 2012

Different Schema

Hi Is it possible to use SQL 2000 replication with two databases that
have different schema.
At the momenet I am using scheduled scripts to create export files ,
ftp them and then import them, its complex and I need a better way.
Thanks.
Yes - you could use transactional replication with
(a) Transformable subscriptions (DTS integration)
(b) Indexed Views on the publisher
(c) a custom sync object on the publisher
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks - in terms of failover and recovery if either server goes down
or if theres poor connectivity , is a) or c) the better solution ?
Paul Ibison wrote:
> Yes - you could use transactional replication with
> (a) Transformable subscriptions (DTS integration)
> (b) Indexed Views on the publisher
> (c) a custom sync object on the publisher
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||(c) would be less strain on the publisher's transactions and holds more
support going forward to new versions of SQl Server.
However I'm a little confused - in terms of failover it is very strange to
change the schema - normally the apps running on the publisher require the
same tables/fields on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Saturday, February 25, 2012

Different Query Results in SQL Server 7.0 and 2000

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

Different query plans

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

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

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

of them is a cluster.

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

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

I've also reindexed to no avail . . .

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

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

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

So they have the same number of CPUs?

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

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

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

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Monday, March 19, 2012

Differing results on identical systems.

Differential SQL backups ? is this possible?

Differential maintenance in plan sql 2000: setup

Differential maintenance in plan sql 2000: setup

Sunday, March 11, 2012

Differential Backups

Differential Backups

Differential backup with database maintenance plan

Differential Backup disproportionately large

Differential Backup disproportionately large

Friday, March 9, 2012

Different UPDATE behaviors across servers/databases

Different UPDATE behaviors across servers/databases

Different UPDATE behaviors across servers/databases

Wednesday, March 7, 2012

Different Schema

Saturday, February 25, 2012

Different Query Results in SQL Server 7.0 and 2000

Different Query Results in SQL Server 7.0 and 2000

Different query plans

disable all triggers

Blog Archive

About Me