SQL Server - DDL operation on Linked Server

How to perform DDL Operations on Linked Server in SQL Server?

Linked server in SQL Server is a server object. Linked server are configured to enable database engine to execute T-SQL statements that includes tables in another instance of SQL server or cross platform databases such as Oracle.

How to create Linked Server in SQL server?

Following is the code to create linked server from SQL Server to Oracle.

BEGIN

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = 'LINKEDSERVER')

EXEC master.dbo.sp_dropserver @server='LINKEDSERVER', @droplogins='droplogins'

EXEC sp_addlinkedserver  @server='LINKEDSERVER',@srvproduct='Oracle',@provider='OraOLEDB.Oracle', @datasrc='<SourceServerName>'

EXEC sp_addlinkedsrvlogin 'LINKEDSERVER', 'false', NULL, '<Source db userId>', '<Source db Password>'

EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1

EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'DynamicParameters', 1

EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'NestedQueries', 1

EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'SqlServerLIKE', 1

EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVER', @optname=N'use remote collation', @optvalue=N'true'

END

user has to provide Oracle Server Name, UserId and Password of oracle instance in above code mentioned in <>.

How to get records counts for a given table in source database?

Following sql server script creates Tab_RowCount function in Oracle database using lnked server.

BEGIN

EXEC master.dbo.sp_serveroption 

                                                        @server=N'LINKEDSERVER', 

                                                        @optname=N'rpc out', 

                                                        @optvalue=N'true'

exec ('

create or replace

function Tab_RowCount( tablename in varchar2(128) ) return number

as

ColumnValue number default NULL;

begin

execute immediate

''select count(*)

from '' || tablename INTO ColumnValue;

return ColumnValue;

end;

') AT LINKEDSERVER

EXEC master.dbo.sp_serveroption 

                                                         @server=N'LINKEDSERVER', 

                                                         @optname=N'rpc out', 

                                                         @optvalue=N'false'

END

Execute function to get all tables count from oracle using LINKEDSERVER:

Following SQL Server script get all tables record count from oracle.

Select * from OPENQUERY (LINKEDSERVER,

                                              ' Select Table_Name, Tab_RowCount(User||''.''||table_name) Cnt

                                                from user_tables ')

Drop user created function using LINKEDSERVER

BEGIN

EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVER', @optname=N'rpc out', @optvalue=N'true'

exec ('

drop 

function Tab_RowCount

') AT LINKEDSERVER

EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVER', @optname=N'rpc out', @optvalue=N'false'

END