Date parsed: 18/10/2007 11:14:17
Date: Thu, 18 Oct 2007 11:14:17 -0000

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

Date parsed: 18/10/2007 18:37:48
Date: Thu, 18 Oct 2007 16:37:48 +0200

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