OPENQUERY Problem

Hi,
I have created a linked server to oracle.
I executed the query as
SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
ora_owner.appointment where update_dtm > to_date(''2007-oct-11
18:06:05'',''yyyy-mon-dd HH24:Mi:SS'')')
Its executing fine.
But I want to get the date from another table from my sql server.
How can I form the OPENQUERY with a variable(contains date)?
SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
tie_owner.rtt_appointment where update_dtm > to_date(''+
@.ApptLastUPdateDateTimee + '',''yyyy-mon-dd HH24:Mi:SS'')')
This statement is giving error...
Incorrect sysntax at +
How do I get date in yyyy-mmm-dd hh:mm:ss format?
The same date I will form in the openquery.
This is struggling me a lot. Pls suggest an idea.
Thanks in advanceSome examples
DECLARE @.SQLx VARCHAR(500)
DECLARE @.var VARCHAR(20)
SET @.var = 'abcd'
SET @.SQLx = 'SELECT * FROM OPENQUERY(Server,
''EXEC pubs.dbo.sp2 '' + @.var + '')'
EXEC(@.SQLx)
<mrajanikrishna@.gmail.com> wrote in message
news:1192706057.368535.148870@.q5g2000prf.googlegroups.com...
> Hi,
> I have created a linked server to oracle.
> I executed the query as
> SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
> ora_owner.appointment where update_dtm > to_date(''2007-oct-11
> 18:06:05'',''yyyy-mon-dd HH24:Mi:SS'')')
> Its executing fine.
> But I want to get the date from another table from my sql server.
> How can I form the OPENQUERY with a variable(contains date)?
> SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
> tie_owner.rtt_appointment where update_dtm > to_date(''+
> @.ApptLastUPdateDateTimee + '',''yyyy-mon-dd HH24:Mi:SS'')')
> This statement is giving error...
> Incorrect sysntax at +
> How do I get date in yyyy-mmm-dd hh:mm:ss format?
> The same date I will form in the openquery.
> This is struggling me a lot. Pls suggest an idea.
> Thanks in advance
>|||On Oct 18, 1:11 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Some examples
> DECLARE @.SQLx VARCHAR(500)
> DECLARE @.var VARCHAR(20)
> SET @.var = 'abcd'
> SET @.SQLx = 'SELECT * FROM OPENQUERY(Server,
> ''EXEC pubs.dbo.sp2 '' + @.var + '')'
> EXEC(@.SQLx)
> <mrajanikris...@.gmail.com> wrote in message
> news:1192706057.368535.148870@.q5g2000prf.googlegroups.com...
>
> > Hi,
> > I have created a linked server to oracle.
> > I executed the query as
> > SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
> > ora_owner.appointment where update_dtm > to_date(''2007-oct-11
> > 18:06:05'',''yyyy-mon-dd HH24:Mi:SS'')')
> > Its executing fine.
> > But I want to get the date from another table from my sql server.
> > How can I form the OPENQUERY with a variable(contains date)?
> > SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
> > tie_owner.rtt_appointment where update_dtm > to_date(''+
> > @.ApptLastUPdateDateTimee + '',''yyyy-mon-dd HH24:Mi:SS'')')
> > This statement is giving error...
> > Incorrect sysntax at +
> > How do I get date in yyyy-mmm-dd hh:mm:ss format?
> > The same date I will form in the openquery.
> > This is struggling me a lot. Pls suggest an idea.
> > Thanks in advance- Hide quoted text -
> - Show quoted text -
Hi thank u for the reply,
What is the problem in my procedure...
DECLARE @.ApptLastUPdateDateTime varchar(30)
BEGIN
DECLARE @.sql_str VARCHAR(4000)
SELECT @.ApptLastUPdateDateTime = convert(varchar(23),ApptUpdateDtm,
120), FROM [LastUpdateDateTime]
SET @.sql_str ='SELECT * from tie_owner.rtt_appointment
WHERE to_char(update_dtm, ''YYYY-MM-DD HH24:MI:SS'') > ''' +
@.ApptLastUPDateDateTime + ''''
SET @.sql_str = N'select * from OPENQUERY([TIE DB], ''' +
REPLACE(@.sql_str, '''', ''') + ''')'
EXEC @.sql_str
END
I am getting error
The name 'select * from OPENQUERY([TIE DB], 'SELECT * from
tie_owner.rtt_appointment
WHERE to_char(update_dtm, ''YYYY-MM-DD HH24:MI:SS'') > ''2005-01-01
01:01:00''')' is not a valid identifier.
I am unable to fix this error.|||Replace EXEC (@.sql) with PRINT @.sql to see what script it creates in order
to debug
<mrajanikrishna@.gmail.com> wrote in message
news:1192715912.147689.145840@.i13g2000prf.googlegroups.com...
> On Oct 18, 1:11 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Some examples
>> DECLARE @.SQLx VARCHAR(500)
>> DECLARE @.var VARCHAR(20)
>> SET @.var = 'abcd'
>> SET @.SQLx = 'SELECT * FROM OPENQUERY(Server,
>> ''EXEC pubs.dbo.sp2 '' + @.var + '')'
>> EXEC(@.SQLx)
>> <mrajanikris...@.gmail.com> wrote in message
>> news:1192706057.368535.148870@.q5g2000prf.googlegroups.com...
>>
>> > Hi,
>> > I have created a linked server to oracle.
>> > I executed the query as
>> > SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
>> > ora_owner.appointment where update_dtm > to_date(''2007-oct-11
>> > 18:06:05'',''yyyy-mon-dd HH24:Mi:SS'')')
>> > Its executing fine.
>> > But I want to get the date from another table from my sql server.
>> > How can I form the OPENQUERY with a variable(contains date)?
>> > SELECT @.Counter = count(*) from OPENQUERY([TIE DB], 'select * from
>> > tie_owner.rtt_appointment where update_dtm > to_date(''+
>> > @.ApptLastUPdateDateTimee + '',''yyyy-mon-dd HH24:Mi:SS'')')
>> > This statement is giving error...
>> > Incorrect sysntax at +
>> > How do I get date in yyyy-mmm-dd hh:mm:ss format?
>> > The same date I will form in the openquery.
>> > This is struggling me a lot. Pls suggest an idea.
>> > Thanks in advance- Hide quoted text -
>> - Show quoted text -
>
> Hi thank u for the reply,
> What is the problem in my procedure...
> DECLARE @.ApptLastUPdateDateTime varchar(30)
> BEGIN
> DECLARE @.sql_str VARCHAR(4000)
> SELECT @.ApptLastUPdateDateTime = convert(varchar(23),ApptUpdateDtm,
> 120), FROM [LastUpdateDateTime]
>
> SET @.sql_str ='SELECT * from tie_owner.rtt_appointment
> WHERE to_char(update_dtm, ''YYYY-MM-DD HH24:MI:SS'') > ''' +
> @.ApptLastUPDateDateTime + ''''
> SET @.sql_str = N'select * from OPENQUERY([TIE DB], ''' +
> REPLACE(@.sql_str, '''', ''') + ''')'
> EXEC @.sql_str
> END
> I am getting error
> The name 'select * from OPENQUERY([TIE DB], 'SELECT * from
> tie_owner.rtt_appointment
> WHERE to_char(update_dtm, ''YYYY-MM-DD HH24:MI:SS'') > ''2005-01-01
> 01:01:00''')' is not a valid identifier.
> I am unable to fix this error.
>