Differential Backups in Maintenance Plans

Using SQL Server2000, I don't see any way of setting up a maint plan to
include nightly differential backups. I would like to use a maint plan as it
allows you to select "All user DBs". Since we add databases quite a lot, I
like the dynamic aspect of maint plans.
Does anyone know of workaround or even a script that would allow me to
dynamically backup each user database? I've looked at sp_msforeachdb, but
can't seem to get it to work as I'm not too good at scripting.
Thanks
RonRon,
In SQL Server 2000, as you have discovered, there is no way to do
differential backups. Technet when discussing SQL Server 2000
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx#ELYAG
describes how to set up differential backups one database at a time,
creating a schedule for each differential backup. By the way, please note
that _master_ cannot be backed up differentially.
You, of course, do not want to do that, but if you do it for one database it
will give you the working syntax. E.g.
BACKUP DATABASE MyDatabase TO
DISK = N'\\BackupServer\MyDatabase_diff_200710230021.BAK'
WITH NOINIT , NOUNLOAD , DIFFERENTIAL ,
NAME = N'MyDatabase backup', NOSKIP , STATS = 10, NOFORMAT
Now, from that perhaps you can create a script from that. Here is one that
only PRINTs the command, but you can change this to EXECUTE it instead.
sp_msforeachdb @.command1='
DECLARE @.BuildStr NVARCHAR(500)
SET @.BuildStr = CONVERT(NVARCHAR(20),GETDATE(),120)
SET @.BuildStr = REPLACE(REPLACE(REPLACE(@.BuildStr,''
'',''''),'':'',''''),''-'','''')
SET @.BuildStr = ''
Backup Database $ TO DISK = N''''\\BackupServer\$_diff_''+@.BuildStr
SET @.BuildStr = @.Buildstr + '''
WITH NOINIT , NOUNLOAD , DIFFERENTIAL ,''
SET @.BuildStr = @.Buildstr + ''
NAME = N''''$ backup'''', NOSKIP , STATS = 10, NOFORMAT ''
IF ''$''<>''master''
PRINT @.BuildStr'
,@.replacechar='$'
Of course, the backups need to be in the same location as other backups for
your maintenance plan deletion of old files to include these as well. Also,
remember that sp_msforeachdb is unsupported.
RLF
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:44788552-025A-4A26-8387-F31F069AE7D2@.microsoft.com...
> Using SQL Server2000, I don't see any way of setting up a maint plan to
> include nightly differential backups. I would like to use a maint plan as
> it
> allows you to select "All user DBs". Since we add databases quite a lot,
> I
> like the dynamic aspect of maint plans.
> Does anyone know of workaround or even a script that would allow me to
> dynamically backup each user database? I've looked at sp_msforeachdb, but
> can't seem to get it to work as I'm not too good at scripting.
> Thanks
> Ron|||Thank you Russell!
"Russell Fields" wrote:
> Ron,
> In SQL Server 2000, as you have discovered, there is no way to do
> differential backups. Technet when discussing SQL Server 2000
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx#ELYAG
> describes how to set up differential backups one database at a time,
> creating a schedule for each differential backup. By the way, please note
> that _master_ cannot be backed up differentially.
> You, of course, do not want to do that, but if you do it for one database it
> will give you the working syntax. E.g.
> BACKUP DATABASE MyDatabase TO
> DISK = N'\\BackupServer\MyDatabase_diff_200710230021.BAK'
> WITH NOINIT , NOUNLOAD , DIFFERENTIAL ,
> NAME = N'MyDatabase backup', NOSKIP , STATS = 10, NOFORMAT
> Now, from that perhaps you can create a script from that. Here is one that
> only PRINTs the command, but you can change this to EXECUTE it instead.
> sp_msforeachdb @.command1='
> DECLARE @.BuildStr NVARCHAR(500)
> SET @.BuildStr = CONVERT(NVARCHAR(20),GETDATE(),120)
> SET @.BuildStr = REPLACE(REPLACE(REPLACE(@.BuildStr,''
> '',''''),'':'',''''),''-'','''')
> SET @.BuildStr = ''
> Backup Database $ TO DISK = N''''\\BackupServer\$_diff_''+@.BuildStr
> SET @.BuildStr = @.Buildstr + '''
> WITH NOINIT , NOUNLOAD , DIFFERENTIAL ,''
> SET @.BuildStr = @.Buildstr + ''
> NAME = N''''$ backup'''', NOSKIP , STATS = 10, NOFORMAT ''
> IF ''$''<>''master''
> PRINT @.BuildStr'
> ,@.replacechar='$'
> Of course, the backups need to be in the same location as other backups for
> your maintenance plan deletion of old files to include these as well. Also,
> remember that sp_msforeachdb is unsupported.
> RLF
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:44788552-025A-4A26-8387-F31F069AE7D2@.microsoft.com...
> > Using SQL Server2000, I don't see any way of setting up a maint plan to
> > include nightly differential backups. I would like to use a maint plan as
> > it
> > allows you to select "All user DBs". Since we add databases quite a lot,
> > I
> > like the dynamic aspect of maint plans.
> >
> > Does anyone know of workaround or even a script that would allow me to
> > dynamically backup each user database? I've looked at sp_msforeachdb, but
> > can't seem to get it to work as I'm not too good at scripting.
> >
> > Thanks
> >
> > Ron
>
>

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

Differences between SQL2000 Dev. Edition & SQL2005 Dev. Edition

Hello all,

Can anyone tell me or suggest links about differences of SQL Server
2000 Developer Edition & SQL Server 2005 Developer Edition?
Specifically, I need to know about the licencing policy.

So far I know that SQL Server 2005 Developer Edition is licenced for
develpoment (what else), test and demo use. Each licence entitles only
one developer to use the software on as many systems as necessary and
additional developers can use the software by parchasing additional
licences (some sentences borrowed by microsoft's presentation of the
product).
Does 2000 Edition has more functions than the latest one? In other
words, what the advantages and disadvantages of these 2 Versions?

Thanks in advance?

PS: Shoul I post this here?x-rays wrote:
> Hello all,
> Can anyone tell me or suggest links about differences of SQL Server
> 2000 Developer Edition & SQL Server 2005 Developer Edition?
> Specifically, I need to know about the licencing policy.
> So far I know that SQL Server 2005 Developer Edition is licenced for
> develpoment (what else), test and demo use. Each licence entitles only
> one developer to use the software on as many systems as necessary and
> additional developers can use the software by parchasing additional
> licences (some sentences borrowed by microsoft's presentation of the
> product).

http://www.microsoft.com/sql/howtobuy/development.mspx

> Does 2000 Edition has more functions than the latest one?

No. SQL Server 2005 Dev Edition is feature equivalent to SQL Server
2005 Enterprise. Other than the licence the only difference I'm aware
of is that Dev works on the desktop Windows OS whereas Enterprise
doesn't

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Hello David,

thanks for replying, my last question was a bit thick I suppose "Does
2000 Edition has more functions than the latest one?", a newer version
is always better. What was the licence policy of SQL Server 2000 Dev.
Edition? Do you remember?

And a 2nd one, did microsoft stop selling SQL Server 2000 Editions?

PS: Please excuse my english.

Thanks in advance!

David Portas wrote:
> x-rays wrote:
> > Hello all,
> > Can anyone tell me or suggest links about differences of SQL Server
> > 2000 Developer Edition & SQL Server 2005 Developer Edition?
> > Specifically, I need to know about the licencing policy.
> > So far I know that SQL Server 2005 Developer Edition is licenced for
> > develpoment (what else), test and demo use. Each licence entitles only
> > one developer to use the software on as many systems as necessary and
> > additional developers can use the software by parchasing additional
> > licences (some sentences borrowed by microsoft's presentation of the
> > product).
> http://www.microsoft.com/sql/howtobuy/development.mspx
> > Does 2000 Edition has more functions than the latest one?
> No. SQL Server 2005 Dev Edition is feature equivalent to SQL Server
> 2005 Enterprise. Other than the licence the only difference I'm aware
> of is that Dev works on the desktop Windows OS whereas Enterprise
> doesn't
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/...US,SQL.90).aspx
> --