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.

Get Zahir Mohideen’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

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.

Originally published at https://zahirmohideen.blogspot.com/2012/04/execute-in-sql-server-2008.html