oracle date conversion

is really bugging me...
Found out that some date fields wont' be allowed on sql server so my syntax
is
select * from openquery('oracleserver', 'select column1, column2,
to_char(date, 'yyyy/mm/dd') as date from table')
this works fine when selecting but when inserting this result into a table
on sql server I'm in trouble
insert into table xx
(columns...)
select * from openquery('oracleserver', 'select column1, column2,
to_char(date, 'yyyy/mm/dd') as date from table')
I get an error saying use ROBUST PLAN.
then i put on Robust plan and it says The query processer could not produce
a query plan
Then I found this article that says post you troubles here...? Anyone ?
My next attempt would be to avoid linked server and use transform data task
in a DTS package to see if that helps
http://www.aspfaq.com/show.asp?id=2400Perhaps a language neutral datetime format will work better. Try converting
to the format yyyymmdd.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"michael v" <test@.test.com> wrote in message news:%23SsmMfv0FHA.908@.tk2msftngp13.phx.gbl...

> is really bugging me...
> Found out that some date fields wont' be allowed on sql server so my synta
x
> is
> select * from openquery('oracleserver', 'select column1, column2,
> to_char(date, 'yyyy/mm/dd') as date from table')
> this works fine when selecting but when inserting this result into a table
> on sql server I'm in trouble
> insert into table xx
> (columns...)
> select * from openquery('oracleserver', 'select column1, column2,
> to_char(date, 'yyyy/mm/dd') as date from table')
> I get an error saying use ROBUST PLAN.
> then i put on Robust plan and it says The query processer could not produc
e
> a query plan
> Then I found this article that says post you troubles here...? Anyone ?
> My next attempt would be to avoid linked server and use transform data tas
k
> in a DTS package to see if that helps
> http://www.aspfaq.com/show.asp?id=2400
>|||Thanx for the reply but found out that it wasn't the date conversion at all.
It was a column with 4000 characters as then lenght.
When not trying to insert this column it works fine...
What to do ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uOH5aqv0FHA.3568@.TK2MSFTNGP15.phx.gbl...
> Perhaps a language neutral datetime format will work better. Try
converting to the format yyyymmdd.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "michael v" <test@.test.com> wrote in message
news:%23SsmMfv0FHA.908@.tk2msftngp13.phx.gbl...
syntax
table
produce
task|||Ahh, that explains the error message. You could try adding some substring to
short the number of
characters returned, I guess.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"michael v" <test@.test.com> wrote in message news:eqzHfvv0FHA.1028@.TK2MSFTNGP12.phx.gbl...[
color=darkred]
> Thanx for the reply but found out that it wasn't the date conversion at al
l.
> It was a column with 4000 characters as then lenght.
> When not trying to insert this column it works fine...
> What to do ?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uOH5aqv0FHA.3568@.TK2MSFTNGP15.phx.gbl...
> converting to the format yyyymmdd.
> news:%23SsmMfv0FHA.908@.tk2msftngp13.phx.gbl...
> syntax
> table
> produce
> task
>[/color]|||thanx
but I found an article mentioning trouble with row size limit / varchar
fields.
i changed the field from varchar to text and it worked.
Is there something I should now about the text field type ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#Wgp2vy0FHA.3892@.TK2MSFTNGP12.phx.gbl...
> Ahh, that explains the error message. You could try adding some substring
to short the number of
> characters returned, I guess.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "michael v" <test@.test.com> wrote in message
news:eqzHfvv0FHA.1028@.TK2MSFTNGP12.phx.gbl...
all.
in
?
data
>