EXECUTE in SQL Server 2008

Prior to SQL Server 2008 , the EXECUTE command could be used only in the local server .
Now , with SQL Server 2008 onwards , the EXECUTE command could be local as well as linked servers.

The prerequisite is that you would create a linked server ( of course ) and enable RPC OUT.

Here is an example .


C:>sqlcmd -S sql08 -W
1> use dbtest
2> go
Changed database context to 'dbtest'.

..........

.... ......

execute sp_serveroption 'ORACLE_LINKDB', 'RPC OUT' , True

I have created a linked server pointing to Oracle Database , in this case ORACLE_LINKDB.
Now , I can execute any oracle sql statements / PL SQL block ,
provided the user   have access to the objects.


1> Execute ( ' Select sysdate from dual')  AT ORACLE_LINKDB
2> go
SYSDATE
-------
2012-04-18 21:10:02.0000000
(1 rows affected)



1> Execute ( ' Select * from Control_Table where status =''running''')  AT ORACLE_LINKDB
2> go
JOBNAME     STATUS     STARTTIME   
-------     ------     ---------   
WLY_LOAD    running 2012-03-20  


In the following example , the user do not have any privileges on SCOTT schema .

1> Execute ( ' Select  *  from scott.emp')  AT ORACLE_LINKDB
2> go
OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINKDB" returned message "ORA-00942: table or view does not exist".
Msg 7215, Level 17, State 1, Server SQL08, Line 1
Could not execute statement on remote server 'ORACLE_LINKDB'.


Comments welcome.