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