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
>
Showing posts with label tablea. Show all posts
Showing posts with label tablea. Show all posts
Monday, March 19, 2012
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.
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
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.
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
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 th
e
> 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 quer
y
> 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 da
ta
> 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...
> 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:[vbcol=seagreen]
>|||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"
>
> No
>
> Yes
>
> "J Hunter" <ms-nntp-nospam@.jshunter.co.uk> wrote in message
> news:OtRcA3oHFHA.3936@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
"use[vbcol=seagreen]
>
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 th
e
> 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 quer
y
> 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 da
ta
> 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...
> 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:[vbcol=seagreen]
>|||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"
>
> No
>
> Yes
>
> "J Hunter" <ms-nntp-nospam@.jshunter.co.uk> wrote in message
> news:OtRcA3oHFHA.3936@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
"use[vbcol=seagreen]
>
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
>
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
>
Friday, March 9, 2012
Import data error while delete rows in the destination table
Both SQLServer2005, different server, import tableA from serverA to serverB.
When we import tableA for the first time, serverB will get the whole
structure and data of tableA. So far is fine. By the way, we need to change
the type of a column from varchar to nvarchar.
Second time, we want to save the original structure and delete the exist data.
So we choose the "delete rows in the destination table" property. But when
we execute this, there is an error below while validation.
/******************
Error: 0xC02020F4 at Data Flow Task: The column "dbname" cannot be processed
because more than one code page (936 and 1252) are specified for it.
******************/
All the columns in this table have the same errors.
I don't know why. Thank you.
If you made this operation in a SSIS package to work , you have to change the property AlwaysUseDefaultCodepage of a OLEDB Source in a Data Flow Task . The value have to be "false"
Subscribe to:
Posts (Atom)