Import Data question using DTS

I have a database A on 1 server and database A on the 2nd machine.
I would like to export data from tableA database A from the 1 server to the
2nd machine, but I only want to export data whose date = '02/28/05'.
When I export the data using DTS, I specify that I would like to "use query
to specify the data to transfer", and I wrote the query as the following:
select * from tableA where tableA.[DateRecorded]='02/28/05' and run it. It
said that it successfully export the data.
But when I went to the 2nd machine database A table A, I do not see the data
where [DateRecorded]='02/28/05'.
How can I export data only where [DateRecorded]='02/28/05' ?
Thank you very much.
Hi,
If you run your query directly against database a on server 1, are the rows
returned?
It might be a issue with string to date convertion. Try using the format
'20050228' or explicitly
convert the string to a datetime - Convert(datetime, '02/28/05', 1).
If there is a time component (ie not 00:00:00) you would need to discard
that before doing the comparison.
Or try: DateRecorded BETWEEN '20050228 00:00:00' AND '20050228 23:59:59'
Cheers
J.
"fniles" <fniles@.pfmail.com> wrote in message
news:e4p9JZoHFHA.3196@.TK2MSFTNGP15.phx.gbl...
> I have a database A on 1 server and database A on the 2nd machine.
> I would like to export data from tableA database A from the 1 server to
the
> 2nd machine, but I only want to export data whose date = '02/28/05'.
> When I export the data using DTS, I specify that I would like to "use
query
> to specify the data to transfer", and I wrote the query as the following:
> select * from tableA where tableA.[DateRecorded]='02/28/05' and run it. It
> said that it successfully export the data.
> But when I went to the 2nd machine database A table A, I do not see the
data
> where [DateRecorded]='02/28/05'.
> How can I export data only where [DateRecorded]='02/28/05' ?
> Thank you very much.
>
|||If the data type for [DateRecorded] is DATETIME, try:
select * from tableA where CONVERT(VARCHAR, tableA.[DateRecorded], 101)
='02/28/2005'
"fniles" wrote:

> I have a database A on 1 server and database A on the 2nd machine.
> I would like to export data from tableA database A from the 1 server to the
> 2nd machine, but I only want to export data whose date = '02/28/05'.
> When I export the data using DTS, I specify that I would like to "use query
> to specify the data to transfer", and I wrote the query as the following:
> select * from tableA where tableA.[DateRecorded]='02/28/05' and run it. It
> said that it successfully export the data.
> But when I went to the 2nd machine database A table A, I do not see the data
> where [DateRecorded]='02/28/05'.
> How can I export data only where [DateRecorded]='02/28/05' ?
> Thank you very much.
>
>
|||Thanks for the reply.

>If you run your query directly against database a on server 1, are the rows
>returned?
Yes, rows are returned when I run the query "select * from tableA where
tableA.[DateRecorded]='02/28/05'" against database A on server 1.
"J Hunter" <ms-nntp-nospam@.jshunter.co.uk> wrote in message
news:%23B2ZnfoHFHA.3628@.TK2MSFTNGP15.phx.gbl...
> Hi,
> If you run your query directly against database a on server 1, are the
> rows
> returned?
> It might be a issue with string to date convertion. Try using the format
> '20050228' or explicitly
> convert the string to a datetime - Convert(datetime, '02/28/05', 1).
> If there is a time component (ie not 00:00:00) you would need to discard
> that before doing the comparison.
> Or try: DateRecorded BETWEEN '20050228 00:00:00' AND '20050228 23:59:59'
> Cheers
> J.
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:e4p9JZoHFHA.3196@.TK2MSFTNGP15.phx.gbl...
> the
> query
> data
>
|||Ok, could you confirm the following:
1. Your using hte DTS Import/Export Wizard
2. You set up a source
3. You set up a destination
4. You provide a query
When you define a destination - does the table exist? If yes, have to tried
letting the wizard create a new table? Do you define any transforms? Does
clicking on the preview button display the desired results?
Cheers
J.
"fniles" <fniles@.pfmail.com> wrote in message
news:O36yUsoHFHA.1996@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Thanks for the reply.
rows[vbcol=seagreen]
> Yes, rows are returned when I run the query "select * from tableA where
> tableA.[DateRecorded]='02/28/05'" against database A on server 1.
>
> "J Hunter" <ms-nntp-nospam@.jshunter.co.uk> wrote in message
> news:%23B2ZnfoHFHA.3628@.TK2MSFTNGP15.phx.gbl...
format[vbcol=seagreen]
following:
>
|||Thanks for the reply.

> 1. Your using hte DTS Import/Export Wizard
Yes

> 2. You set up a source
Yes, database A from 1st server

> 3. You set up a destination
Yes, database A from 2nd machine

> 4. You provide a query
Yes.
select * from tableA where tableA.[DateRecorded]='02/28/05'

> When you define a destination - does the table exist?
Yes, tableA exists in both 1st server and 2nd machine

> If yes, have to tried letting the wizard create a new table?
No. Did you mean click on "Transform" and select "Create Destination Table"

>Do you define any transforms?
No

>Does clicking on the preview button display the desired results?
Yes
"J Hunter" <ms-nntp-nospam@.jshunter.co.uk> wrote in message
news:OtRcA3oHFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Ok, could you confirm the following:
> 1. Your using hte DTS Import/Export Wizard
> 2. You set up a source
> 3. You set up a destination
> 4. You provide a query
> When you define a destination - does the table exist? If yes, have to
> tried
> letting the wizard create a new table? Do you define any transforms?
> Does
> clicking on the preview button display the desired results?
> Cheers
> J.
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O36yUsoHFHA.1996@.TK2MSFTNGP12.phx.gbl...
> rows
> format
> following:
>
|||It sounds like everything is configured correctly. When I refered to a
destination table I did not mean to click on the Transforms button, but just
to enter a destination table that doesn't exist DTS will do the rest.
When you execute the Export does it confirm a number of lines copied? ie
Complete (91)?
Just incase there is a problem with your select on the destination table,
could you choose a new destination that doesn't exist. Once complete do a
select * from <new table> to verify new lines have been copied.
Cheers
J.
"fniles" <fniles@.pfmail.com> wrote in message
news:u2VyfvpHFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Thanks for the reply.
> Yes
> Yes, database A from 1st server
> Yes, database A from 2nd machine
> Yes.
> select * from tableA where tableA.[DateRecorded]='02/28/05'
> Yes, tableA exists in both 1st server and 2nd machine
> No. Did you mean click on "Transform" and select "Create Destination
Table"[vbcol=seagreen]
> No
> Yes
>
> "J Hunter" <ms-nntp-nospam@.jshunter.co.uk> wrote in message
> news:OtRcA3oHFHA.3936@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
"use
>