OPENQUERY problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

OPENQUERY problem

Author

Topic

mrajani
Starting Member

13 Posts

Posted - 2007-10-18 : 07:12:46

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

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 07:18:24

You can't have an expression in OPENQUERY. You will have to make the whole OPENQUERY syntax in an Nvarchar and then EXEC that (dynamic SQL)

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 07:19:45

See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90564

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-18 : 07:21:04

I think somewhere in this topic I have used dynamic sql with Openrowset. Try to use the sampe kind of approach
http://sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

mrajani
Starting Member

13 Posts

Posted - 2007-10-18 : 08:00:21

Hi,

Thanks for the reply.

I used

DECLARE @ApptLastUPdateDateTIme varchar(30)

SELECT @ApptLastUPdateDateTime = convert(datetime,ApptUpdateDtm,121) FROM [LastUpdateDateTime]

SET @sql_str ='SELECT * from ora_owner.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

Its giving error as

Msg 203, Level 16, State 2, Procedure xxxxxx, Line 25
The name 'select * from OPENQUERY([ORA DB], 'SELECT * from ora_owner.appointment
WHERE to_char(update_dtm, ''YYYY-MM-DD HH24:MI:SS'') > ''Jan 1 2005 1:01AM''')' is not a valid identifier.

Whats this error.

Why I am unable to change format of @ApptLastUPDateDateTime to yyyy-mm-dd hh:mm:ss

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-18 : 08:06:03

Waht does PRINT @sql_str give?

Madhivanan

Failing to plan is Planning to fail

mrajani
Starting Member

13 Posts

Posted - 2007-10-18 : 08:09:58

hi

print @sql_str displayed

select * from OPENQUERY([TIE DB], 'SELECT * from tie_owner.rtt_appointment
WHERE to_char(update_dtm, ''YYYY-MM-DD HH24:MI:SS'') > ''Jan 1 2005 1:01AM''')

mrajani
Starting Member

13 Posts

Posted - 2007-10-18 : 10:00:40

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.

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 10:13:36

What happens if you just try running the output from the PRINT statement - i.e.

select * from OPENQUERY([TIE DB], 'SELECT * from tie_owner.rtt_appointment
WHERE to_char(update_dtm, ''YYYY-MM-DD HH24:MI:SS'') > ''Jan 1 2005 1:01AM''')

Kristen

mrajani
Starting Member

13 Posts

Posted - 2007-10-18 : 10:23:11

Hi,

When I run the query from PRINT output, its executing well

What is the problem. I didnt understand.

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 10:38:22

Oops, missed this earlier:

EXEC (@sql_str)

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-19 : 01:27:04

If you want speed, why don'y you just transfer the COUNT thingy to ORACLE?
If you only want count, there is no need to transfer all records to SQL and count them there.

E 12°55'05.25"
N 56°04'39.16"

mrajani
Starting Member

13 Posts

Posted - 2007-10-19 : 11:58:51

Hi Kristen,

Thanks for the response. It got executed. Oops! a small mistake.
But I've a little more problem.
I am retrieving data from oracle table based on the date field(update_dtm). I need to insert into local table of the same structure.
Whenever a record is modified in oracle, the update_dtm field also modified to current date where as insert_dtm is updated only the first time record inserted.
When I am getting data, some are new records and some are modified records.
So, I need to do a query to insert into local table if it is a new record. If its existing record I need to modify.

How can I do without looping?

I have big bunch of records.
This import will ruun for every 10 minutes.

Pls suggest a solution

Kristen
Test

22859 Posts

Posted - 2007-10-19 : 12:38:46

Would it be acceptable to DELETE any records in the received record set, and then INSERT them all?

(i.e. no Foreign Key issues)

That's we do for our staging tables.

We:

1) get @StartDate = MAX(UpdateDate) from our [local] staging table
2) get all rows with Oracle UpdateDate >= @StartDate into a ##TEMP table (using OPENQUERY just as you are doing)
3) Join ##TEMP to Staging table, delete all matching rows
4) Inert from ##TEMP into Staging table
5) FINALLY Update the local production tables, from the staging table (again using @StartDate as a cutoff)

Kristen

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources