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.
| Author |
Topic |
|
Zindros
Starting Member
3 Posts |
 Posted - 2011-09-27 : 01:58:32
|
| Hi all,I need to get query results to a variable from Oracle linked server. I send the query from SQL2005, Via Microsoft OLE DB provider to Oracle 9i. When I send the follwing query:EXEC('SELECT MATNR FROM SAPSR3.ZNKMM_BATCHES') AT LKSAP I get the results correctly to my screen. When I send the following query:[b]DECLARE @Item Varchar(20)EXEC('SELECT ? = MATNR FROM SAPSR3.ZNKMM_BATCHES', @Item OUTPUT) AT LKSAP SELECT @ItemI get the following errors:OLE DB provider "MSDAORA" for linked server "LKSAP" returned message "One or more errors occurred during processing of command.".OLE DB provider "MSDAORA" for linked server "LKSAP" returned message "ORA-00923: FROM keyword not found where expected".(1 row(s) affected)Msg 7215, Level 17, State 1, Line 2Could not execute statement on remote server 'LKSAP'.CAN SOMEONE HELP ON THIS PLEASE ? I JUST NEED TO GET THE RESULTS TO A VRIABLE.Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-27 : 04:45:55
|
| you should be using sp_executesql instead of EXEC. see link belowhttp://msdn.microsoft.com/en-us/library/ms188001(v=sql.90).aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Zindros
Starting Member
3 Posts |
Posted - 2011-09-27 : 09:18:55
|
| The query runs to a linked server with Oracle. The most important issue is to set up query in a way that Oracle and OLD DB provider understand the variables the query is using. I tried sp_executesql, but I failed. |
|
|
|
|
|
|
Query results to a variable