1

I have SQL server which contains a link to another server(For e.g: test) I have a function in linked server(name:getvalue(@parameter)).so this function output i want to retrieve in SQL server 2012.I am using below methodology.

DECLARE @sql nvarchar(4000);
DECLARE @somevalue nvarchar(16)

SET @sql = 'SELECT * FROM OPENQUERY(test, 
                          ''SELECT @somevalue=getvalue(' +CAST('545454545' as nvarchar(10))+ ')'')';
EXEC (@sql)

I am getting error like

OLE DB provider "OraOLEDB.Oracle" for linked server "test" returned message "ORA-00936: missing expression". Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "SELECT @somevalue=getvalue(545454545)" for execution against OLE DB provider "OraOLEDB.Oracle" for linked server "test".

Please help me on this.Thanks in advance

  • Is your function called getvalue or getacid? Also, you're linking to an Oracle database so I'm presuming the syntax of the query you pass to openquery must conform to Oracle syntax. In this case you will need to add ` from dual` to the query. – Colin 't Hart Aug 4 '15 at 7:11
  • function is getvalue().even after using from dual,i am having same issue. – user3013146 Aug 4 '15 at 7:15
1

Your remote query is nested twice, first in OPENQUERY, then in EXEC. The @somevalue variable is declared at the outermost level, it simply does not exist in the remote query's scope, so the reference is invalid.

But you also cannot use a variable there for two reasons:

  1. First of all, as Colin 't Hart has noted, your linked server is Oracle. The SELECT @var = function(...) syntax is SQL Server and invalid in Oracle.

  2. the OPENQUERY rowset function lets you treat the remote side's result only as a rowset – it is not designed to populate variables, whether along the way or as an alternative to the rowset. So, even if @var-like references were allowed in the underlying query (for instance, if the remote server was SQL Server), with OPENQUERY you would still be unable to reference outer ("this" side's) variables at the remote query's scope, nor would the outer scope be able to access variables declared at the inner scope ("that" side) in case you wanted to use them in your remote query.

To conclude at this point, the best way to get a result from an Oracle scalar function in SQL Server is probably to have your remote query return the result as a rowset while invoking the remote query using a rowset function like OPENQUERY. After you get the rowset from OPENQUERY, you can do with it whatever SQL Server allows you to do, and that includes storing its column values into variables. So, your OPENQUERY query could basically go like this:

SELECT
  @somevalue = somecolumn
FROM
  OPENQUERY(
    'test',
    'your Oracle query'
  )
;

Note the position of @somevalue = – it is moved one level higher, where it is now valid syntax and makes sense. The somecolumn would be the name that you would assign to your Oracle query's single column returning the result. This is probably how your Oracle query should look like:

SELECT getvalue(argument) AS somecolumn FROM DUAL

Now, if you did not need to parametrise argument, the complete Transact-SQL query could look like this:

DECLARE @somevalue nvarchar(16);

SELECT
  @somevalue = myresult
FROM
  OPENQUERY(
    test, 
    'SELECT getvalue(545454545) AS myresult FROM DUAL'
  )
;

But as you want to be able to parametrise the argument, you need to make the SELECT FROM OPENQUERY query dynamic: build it as a string:

DECLARE @sql nvarchar(max);

…

SET @sql =
N'SELECT
  …
FROM
  OPENQUERY(
    test, 
    ''SELECT getvalue(' + CAST(@someargument AS nvarchar(16)) + N') AS myresult FROM DUAL''
  )
';

and then execute the string:

EXEC (@sql);

All that you are already doing. However, when you use the EXEC (...) syntax, you cannot directly receive the result into a variable1, you can only get it as a result set – yes, same limitation as with OPENQUERY. Unlike OPENQUERY, though, you cannot call EXEC in a FROM clause.

So, at this point you are still facing the problem of putting the result of getvalue into a variable.

Basically, there are two ways to go about it. First is to drop the @somevalue = bit from the dynamic query to let the OPENQUERY's result set pass through to the outermost scope. Although you cannot do ... FROM EXEC (...), you can do INSERT ... EXEC (...). So, insert the result set of EXEC into a temporary dataset (like a table variable):

DECLARE @tmpresult TABLE (result nvarchar(16));

INSERT INTO @tmpresult (result)
EXEC (@sql);

then read the column value from the temporary storage into a scalar variable:

SELECT @somevalue = result FROM @tmpresult;

The other, and, in my opinion, better, option is to use EXEC sp_executesql instead of EXEC () to execute the dynamic query. With the sp_executesql system stored procedure you can have parameters for the underlying query, including output parameters. An output parameter is precisely how you can pass the result into your @somevalue variable that you have declared in your main script.

So, keep the @somevalue = part of the dynamic query but declare it when invoking the query rather than in the query. That way the variable will work not just as a local variable but as a parameter. Including OUTPUT into the parameter declaration will make it an output parameter too. So, this is how you go:

SET @sql =
N'SELECT
  @tmpvalue = myresult
FROM
  …
';

EXEC sp_executesql @sql, '@tmpvalue nvarchar(16) OUTPUT', @somevalue OUTPUT;

You can see in the above code that I slightly changed the name of the variable in the dynamic query. I did that to emphasise the fact that the variable in your dynamic query and the one in your main script are different things. The @tmpvalue is the variable with which you work inside the dynamic query, while @somevalue is your main scope's variable that you will be passing as an actual receiver of the value stored in @tmpvalue. That is the way of exchanging data between variables of a dynamic query and those of the calling script. Note also that you must specify OUTPUT both in the parameter declaration and when specifying the receiver variable. This is the same convention as with output parameters of user-defined stored procedures.

So, to summarise, the final code would look something like this:

DECLARE
  @sql      nvarchar(max),
  @somvalue nvarchar(16)
;

SET @sql =
N'SELECT
  @tmpvalue = myresult
FROM
  OPENQUERY(
    test, 
    ''SELECT getvalue(' + CAST(@someargument AS nvarchar(16)) + N') AS myresult FROM DUAL''
  )
';

EXEC sp_executesql
  @sql,
  N'@tmpvalue nvarchar(16) OUTPUT',
  @somevalue OUTPUT
;

1Actually you can have parameters, including output parameters, with EXEC, but only if you have specified to execute the dynamic query at a remote server:

EXEC (@sql, @var1, @var2, ...) AT linked_server

Usually this is more useful when the remote server is a non-SQL Server one, as for SQL Server instances the EXEC sp_executesql method would likely be preferable.

0

@somevalue does not exist in the context of the linked queries. It has been declared above in the main session. Exec or OpenQuery does not see it. Then Your EXEC query will just output the data from the main select. If you want to get the value back in your variable you can update @sql and use sp_executesql:

SET @Param = N'@returnvalue nvarchar(16) OUTPUT';
EXECUTE sp_executesql @sql, @Param, @returnValue = @someVariable Output;
SELECT @max_title;
  • You are right about @somevalue not existing in the context of OPENQUERY's underlying query. However, there's more to this issue than just an undeclared variable. Apparently the OP is executing that query against an Oracle server, which means they first need to get the syntax right. – Andriy M Aug 4 '15 at 9:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.