Insert From OpenQuery

Last Post 30 Nov 2006 09:35 AM by lmf232s. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
lmf232s
New Member
New Member

--
28 Nov 2006 12:34 PM
Im trying to read data from an oracle linked table and insert it into a sql server temp table.
My Problem is that i need to pass in a variable to the openquery statement.

Normonaly when i have values to pass in, i wrap the whole query in EXEC(' ') and then to add the variables i just do ''''' + @ObjType + '''''.

But i seem to be having problems getting an INSERT to work correclty when passing in a varaible using both methods. I was going to see if there was something i was missing.


INSERT INTO MyTestBookings(Customer, So, SoLine, Total)
SELECT a.Customer, a.So, a.SoLine, a.Total
FROM OPENQUERY(GLOVIA, '
SELECT Customer, So, SoLine, Total
FROM GLOVIA_PROD.SO.................
WHERE So.Added_Date Between to_date('''+@MinDate+''', ''MM/DD/YYYY'')
AND to_date('''+@MaxDate+''', ''MM/DD/YYYY'')
') a

This gives me an error of "Incorrect syntax near '+' ".

Now if i wrap exec('') around just the select statement it works fine (assuming i add some more quotes)

EXEC('
SELECT a.Customer, a.So, a.SoLine, a.Total
FROM OPENQUERY(GLOVIA, ''
SELECT Customer, So, SoLine, Total
FROM GLOVIA_PROD.SO.................
WHERE So.Added_Date Between to_date('''''+@MinDate+''''', ''''MM/DD/YYYY'''')
AND to_date('''''+@MaxDate+''''', ''''MM/DD/YYYY'''')
'') a
')

Now i just need to get the insert statement working

Any Ideas?
lmf232s
New Member
New Member

--
29 Nov 2006 09:07 AM
russellb,

What do you say? Everything is going pretty good i guess. Same day different crap.

Any way, i guess i had a brain fart yesterday because there was no reason for my to post this. I have done this before but for some reason i could not grasp it yesterday.

I should of mentioned that i had already tried what you posted as that gave me an error
"The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction."

The way it needs to be done is to put the exec around the entire statement.

EXEC('
INSERT MyTestBookings(Customer, So, SoLine, Total)
SELECT a.Customer, a.So, a.SoLine, a.Total
FROM OPENQUERY(GLOVIA, ''
SELECT Customer, So, SoLine, Total
FROM GLOVIA_PROD.SO.................
WHERE So.Added_Date Between to_date('''''+@MinDate+''''', ''''MM/DD/YYYY'''')
AND to_date('''''+@MaxDate+''''', ''''MM/DD/YYYY'''')
'') a
')


And yes there are alot of quotes to manage. You should see some of the really nasty queries that we have, there are quotes all over the place and they are so damn hard to manage. Its like trying to find a needle in a haystack sometimes. Although, i know no other way to do this.

I did read and test out a couple of weeks ago that instead of using the Openquery you can use

Select * From LinkedServer...Tablename

I think i got that right. Any way its something to do with the 3 ...'s. Although i believe this causes more overhead as the query with the 3 ...'s gets all the records returened and then is processed on the local server where as the open query is processed on the linked table server and then just the results are passed back. I think i got that right.

Any way if you know of a better way to work with linked servers im all ears.
lmf232s
New Member
New Member

--
30 Nov 2006 09:35 AM
Ya, when doing sql server - sql server i do just as you mentioned

Select * From LinkedServer.database.owner.Tablename

but for some reason that damn oracle causes some problems and seems to be a beast of its own and will not execute a statement with that syntax.

Thanks russellb

Side note: I dont get an email when you reply to these messages. Any idea whats going on there. I have subscribed to the thread?
You are not authorized to post a reply.

Acceptable Use Policy