-- RUN IN SQLCMD mode in SSMS
-- CHANGE INSTANCE NAMES BELOW
:setvar LOCALINSTANCE "(local)"
:setvar REMOTEINSTANCE "(local)\INSTANCE01"


-- SCRIPT START
:connect $(REMOTEINSTANCE)

use tempdb
go
if object_id('dbo.remoteview') is not null
drop view dbo.remoteview
go
if object_id('dbo.remotetable') is not null
drop table dbo.remotetable
go
create table dbo.remotetable(a int primary key clustered,b char(10))
go
insert dbo.remotetable values(1,'a')
insert dbo.remotetable values(2,'b')
insert dbo.remotetable values(3,'c')
go
create view dbo.remoteview 
as select * from dbo.remotetable
go

:connect $(LOCALINSTANCE)

use tempdb
go

if exists(select * from sys.servers where name = '$(REMOTEINSTANCE)' and is_linked = 1)
exec sp_dropserver '$(REMOTEINSTANCE)'
go

exec sp_addlinkedserver '$(REMOTEINSTANCE)',N'SQL Server'
go
exec sp_addlinkedsrvlogin '$(REMOTEINSTANCE)','true'
go

if exists(select * from sys.synonyms where name = 'synremotetable')
drop synonym synremotetable
go
create synonym synremotetable for [$(REMOTEINSTANCE)].tempdb.dbo.remotetable
go

if exists(select * from sys.synonyms where name = 'synremoteview')
drop synonym synremoteview
go
create synonym synremoteview for [$(REMOTEINSTANCE)].tempdb.dbo.remoteview
go

if object_id('dbo.localremoteview') is not null
drop view dbo.localremoteview
go
create view dbo.localremoteview 
as select * from [$(REMOTEINSTANCE)].tempdb.dbo.remoteview
go
if object_id('dbo.localremotetable') is not null
drop view dbo.localremotetable
go
create view dbo.localremotetable 
as select * from [$(REMOTEINSTANCE)].tempdb.dbo.remotetable
go
if object_id('dbo.localsynremoteview') is not null
drop view dbo.localsynremoteview
go
create view dbo.localsynremoteview
as select * from synremoteview
go
if object_id('dbo.localsynremotetable') is not null
drop view dbo.localsynremotetable
go
create view dbo.localsynremotetable
as select * from synremotetable
go


-- TEST 1
-- SELECT FROM REMOTE TABLE 4 PART NAME

declare @t table(a int,b char(10))

begin try
   print 'Test 1 : SELECT FROM REMOTE TABLE DIRECT WITH 4 PART NAME'
   insert @t
   select * from [$(REMOTEINSTANCE)].tempdb.dbo.remotetable

   exec('use tempdb ; alter index all on [remotetable] rebuild') AT [$(REMOTEINSTANCE)];

   insert @t
   select * from [$(REMOTEINSTANCE)].tempdb.dbo.remotetable

   print 'Test 1 : Success'
end try
begin catch
   print 'Test 1 failed : ' + error_message()
end catch
go

-- TEST 2
-- SELECT FROM REMOTE VIEW 4 PART NAME

declare @t table(a int,b char(10))

begin try
   print 'Test 2 : SELECT FROM REMOTE VIEW DIRECT WITH 4 PART NAME'
   insert @t
   select * from [$(REMOTEINSTANCE)].tempdb.dbo.remoteview

   exec('use tempdb ; alter index all on [remotetable] rebuild') AT [$(REMOTEINSTANCE)];

   insert @t
   select * from [$(REMOTEINSTANCE)].tempdb.dbo.remoteview

   print 'Test 2 : Success'
end try
begin catch
   print 'Test 2 failed : ' + error_message()
end catch
go

-- TEST 3
-- SELECT FROM LOCAL VIEW 4 PART NAME TO TABLE

declare @t table(a int,b char(10))

begin try
   print 'Test 3 : SELECT FROM LOCAL VIEW 4 PART NAME TO TABLE'
   insert @t
   select * from localremotetable

   exec('use tempdb ; alter index all on [remotetable] rebuild') AT [$(REMOTEINSTANCE)];

   insert @t
   select * from localremotetable

   print 'Test 3 : Success'
end try
begin catch
   print 'Test 3 failed : ' + error_message()
end catch
go

-- TEST 4
-- SELECT FROM LOCAL VIEW 4 PART NAME TO VIEW

declare @t table(a int,b char(10))

begin try
   print 'Test 4 : SELECT FROM LOCAL VIEW 4 PART NAME TO VIEW'
   insert @t
   select * from localremoteview

   exec('use tempdb ; alter index all on [remotetable] rebuild') AT [$(REMOTEINSTANCE)];

   insert @t
   select * from localremoteview

   print 'Test 4 : Success'
end try
begin catch
   print 'Test 4 failed : ' + error_message()
end catch
go

-- TEST 5
-- SELECT FROM LOCAL VIEW SYNONYM TO REMOTE TABLE

declare @t table(a int,b char(10))

begin try
   print 'Test 5 : SELECT FROM LOCAL VIEW SYNONYM TO REMOTE TABLE'
   insert @t
   select * from localsynremotetable

   exec('use tempdb ; alter index all on [remotetable] rebuild') AT [$(REMOTEINSTANCE)];

   insert @t
   select * from localsynremotetable

   print 'Test 5 : Success'
end try
begin catch
   print 'Test 5 failed : ' + error_message()
end catch
go

-- TEST 6
-- SELECT FROM LOCAL VIEW SYNONYM TO REMOTE VIEW

declare @t table(a int,b char(10))

begin try
   print 'Test 6 : SELECT FROM LOCAL VIEW SYNONYM TO REMOTE VIEW'
   insert @t
   select * from localsynremoteview

   exec('use tempdb ; alter index all on [remotetable] rebuild') AT [$(REMOTEINSTANCE)];

   insert @t
   select * from localsynremoteview

   print 'Test 6 : Success'
end try
begin catch
   print 'Test 6 failed : ' + error_message()
end catch
go

-- TEST 7
-- SELECT FROM LOCAL SYNONYM TO REMOTE VIEW

declare @t table(a int,b char(10))

begin try
   print 'Test 7 : SELECT FROM LOCAL SYNONYM TO REMOTE VIEW'
   insert @t
   select * from synremoteview

   exec('use tempdb ; alter index all on [remotetable] rebuild') AT [$(REMOTEINSTANCE)];

   insert @t
   select * from synremoteview

   print 'Test 7 : Success'
end try
begin catch
   print 'Test 7 failed : ' + error_message()
end catch
go

-- TEST 8
-- SELECT FROM LOCAL SYNONYM TO REMOTE TABLE

declare @t table(a int,b char(10))

begin try
   print 'Test 8 : SELECT FROM LOCAL SYNONYM TO REMOTE TABLE'
   insert @t
   select * from synremotetable

   exec('use tempdb ; alter index all on [remotetable] rebuild') AT [$(REMOTEINSTANCE)];

   insert @t
   select * from synremotetable

   print 'Test 8 : Success'
end try
begin catch
   print 'Test 8 failed : ' + error_message()
end catch
go



-- SCRIPT CLEANUP
:connect $(REMOTEINSTANCE)

use tempdb
go
if object_id('dbo.remoteview') is not null
drop view dbo.remoteview
go
if object_id('dbo.remotetable') is not null
drop table dbo.remotetable
go

:connect $(LOCALINSTANCE)

use tempdb
go

if exists(select * from sys.servers where name = '$(REMOTEINSTANCE)' and is_linked = 1)
exec sp_dropserver '$(REMOTEINSTANCE)'
go

if exists(select * from sys.synonyms where name = 'synremotetable')
drop synonym synremotetable
go
if exists(select * from sys.synonyms where name = 'synremoteview')
drop synonym synremoteview
go
if object_id('dbo.localremoteview') is not null
drop view dbo.localremoteview
go
if object_id('dbo.localremotetable') is not null
drop view dbo.localremotetable
go
if object_id('dbo.localsynremoteview') is not null
drop view dbo.localsynremoteview
go
if object_id('dbo.localsynremotetable') is not null
drop view dbo.localsynremotetable
go