USE [master]

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_Temp_AddLinkedServer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

    drop procedure dbo.SP_Temp_AddLinkedServer;

GO

create procedure dbo.SP_Temp_AddLinkedServer

@@cloudServerIP nvarchar(250),

@@cloudServerSA nvarchar(50),

@@cloudSAPWD nvarchar(50)

AS

begin

    IF EXISTS(SELECT * FROM sys.servers WHERE name = @@cloudServerIP)

        EXEC master.sys.sp_dropserver @@cloudServerIP,'droplogins'  

    declare @TargetServer nvarchar(50)

    declare @strcloudServerIP nvarchar(50)

    declare @strcloudServerSA nvarchar(50)

    declare @strcloudSAPWD nvarchar(50)

    set @strcloudServerIP = @@cloudServerIP

    set @strcloudServerSA = @@cloudServerSA

    set @strcloudSAPWD = @@cloudSAPWD

    EXEC master.dbo.sp_addlinkedserver @server = @strcloudServerIP, @srvproduct=N'SQL Server'

     /* For security reasons the linked server remote logins password is changed with ######## */

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@strcloudServerIP,@useself=N'False',@locallogin=NULL,@rmtuser=@strcloudServerSA ,@rmtpassword=@strcloudSAPWD

    EXEC master.dbo.sp_serveroption @server=@strcloudServerIP, @optname=N'collation compatible', @optvalue=N'false'

    EXEC master.dbo.sp_serveroption @server=@strcloudServerIP, @optname=N'data access', @optvalue=N'true'

    EXEC master.dbo.sp_serveroption @server=@strcloudServerIP, @optname=N'dist', @optvalue=N'false'

    EXEC master.dbo.sp_serveroption @server=@strcloudServerIP, @optname=N'pub', @optvalue=N'false'

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

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

    EXEC master.dbo.sp_serveroption @server=@strcloudServerIP, @optname=N'sub', @optvalue=N'false'

    EXEC master.dbo.sp_serveroption @server=@strcloudServerIP, @optname=N'connect timeout', @optvalue=N''

    EXEC master.dbo.sp_serveroption @server=@strcloudServerIP, @optname=N'collation name', @optvalue=null

    EXEC master.dbo.sp_serveroption @server=@strcloudServerIP, @optname=N'lazy schema validation', @optvalue=N'false'

    EXEC master.dbo.sp_serveroption @server=@strcloudServerIP, @optname=N'query timeout', @optvalue=N''

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

    EXEC master.dbo.sp_serveroption @server=@strcloudServerIP, @optname=N'remote proc transaction promotion', @optvalue=N'true'

end

--use master exec SP_Temp_AddLinkedServer '10.1.12.110','sa','Sequoia2012'

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_Temp_GetCompareNameList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure dbo.SP_Temp_GetCompareNameList;

GO

create procedure dbo.SP_Temp_GetCompareNameList

@@filePath varchar(250)

--@@totalAmount numeric(15,3)=0 output

AS

BEGIN

 declare @TableNameConfigureFile varchar(250)

 declare @SQLExeStr varchar(300)

 set @TableNameConfigureFile = @@filePath

    /* Remove temp table */

    if object_id('Temp_Table_CompareTableName') is not null

    Begin

         drop table Temp_Table_CompareTableName

    End

    exec ('CREATE TABLE Temp_Table_CompareTableName(strName varchar(100))')

      set @SQLExeStr = 'bulk INSERT  Temp_Table_CompareTableName

    from ' +  '''' + @TableNameConfigureFile + ''''+

    ' with(

     FIELDTERMINATOR = ''\t'',

     ROWTERMINATOR = ''\n''

    ) '

    exec (@SQLExeStr)

    --select *from #CompareTableName

    --select *from tempdb..#CompareTableName

    --print @SQLExeStr

    -- exec SP_Temp_GetCompareNameList 'C:\ComparedTableList.txt'

    --select *from CompareTableName

end

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_Temp_GetTableColumns]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure dbo.SP_Temp_GetTableColumns;

GO

create procedure dbo.SP_Temp_GetTableColumns

@@tableName nvarchar(250)

AS

BEGIN

 declare @strTableName varchar(250)

 declare @SQLExeStr varchar(4000)

 set @strTableName = @@tableName

    /* Remove temp table */

    if object_id('Temp_Table_TableColumns') is not null

    Begin

        drop table Temp_Table_TableColumns

    End

    set @SQLExeStr = 'Select name into Temp_Table_TableColumns from SysColumns Where id=Object_Id(''' + @strTableName + ''')

    and name <> ''AuditTimestamp''

    and name <> ''CreatedTime''

    and name <> ''InstallTime''

    and name <> ''StartTime''

    and name <> ''ModificationTime''

    and name <> ''LockTime''

    and name <> ''LastActivityDate''

    and name <> ''MasterPageImage''

    and name <> ''LastLoginDate''

    '

    --print @SQLExeStr

    exec (@SQLExeStr)

end

--exec SP_Temp_GetTableColumns 'dbo.RexStatementTransaction'

--select *from TableColumns

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_Temp_CompareTables]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

    drop procedure dbo.SP_Temp_CompareTables;

GO

create procedure dbo.SP_Temp_CompareTables

@@compareTableName nvarchar(250),

@@compareRes int output,

@@cloudServerIP nvarchar(50) ,

@@cloudDBName nvarchar(50)

AS

BEGIN

    declare @TableName nvarchar(250)

    declare @SQLExeStr nvarchar(300)

    declare @strSQLLoadTableColumns nvarchar(300)

    declare @strcloudServerIP nvarchar(300)

    declare @strcloudDBName nvarchar(300)

    declare @selColumns nvarchar(3000)

    declare @finalSelColumns   nvarchar(3000)

    DECLARE @count int -- column numbers

    DECLARE @testResult int /*Test Result flag*/

    set @TableName = @@compareTableName

    set @strcloudServerIP = @@cloudServerIP

    set @strcloudDBName = @@cloudDBName

    set @testResult = 1 /*Default Value as false*/

    /*Print out the info of comparing table*/

    select dbo.FN_Temp_LogInfo( 'Testing The Table:' +@TableName)

    set @strSQLLoadTableColumns = 'exec SP_Temp_GetTableColumns ' + '''' + @TableName + ''''

    exec(@strSQLLoadTableColumns) -- call SP for insert the table's column to our test using table

        --SELECT @count= COUNT(1) FROM Temp_Table_TableColumns

        select @selColumns = isnull(@selColumns,'') + ',' + name from Temp_Table_TableColumns where name=name

        --print @selColumns

        set @finalSelColumns = SubString(@selColumns,2,LEN(@selColumns))

        BEGIN

            declare @sqlCloudQueryStr nvarchar(3000)

            declare @sqlGroundQueryStr nvarchar(3000)

            declare @finalSqlExec nvarchar(4000)

            declare @withStart nvarchar(100)

            declare @withEnd nvarchar(100)

            set @withStart = 'select @a=count(1)'  +  ' from ('

            set @withEnd = ')T'

            /* Get the final exec SQL compare statement*/

            set @sqlGroundQueryStr = ' select ' + @finalSelColumns + ' from ' + @TableName + ' except '

            set @sqlCloudQueryStr = 'select *from OPENQUERY( [' + @strcloudServerIP  + '], ''select ' + @finalSelColumns + ' from ' +  @strcloudDBName +'.' +  @TableName + ''''+ ')'

            set @finalSqlExec = @withStart +  @sqlGroundQueryStr + @sqlCloudQueryStr + @withEnd 

            declare @num int

            BEGIN TRY

                 EXECUTE sp_executesql @finalSqlExec,N'@a int output',@num output

                 IF(@num > 0)

                     Begin

                        set @testResult = 1

                        /*Set the case info*/

                        select dbo.FN_Temp_LogInfo(' >>>>>> Comparing Exception <<<<<<' )

                        select dbo.FN_Temp_LogInfo('        Exception Table:' + @TableName )

                        select dbo.FN_Temp_LogInfo('        Exception Query:' + @finalSqlExec)

                        select dbo.FN_Temp_LogInfo(' ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^')

                     End

                else

                    set @testResult = 0

            End TRY

            BEGIN CATCH

                        set @testResult = 1

                        select dbo.FN_Temp_LogInfo(' >>>>>> SQL Execution Exception <<<<<<' )

                        select dbo.FN_Temp_LogInfo('        Exception Table:' + @TableName )

                        select dbo.FN_Temp_LogInfo('        Exception Query:' + @finalSqlExec)

                        select dbo.FN_Temp_LogInfo('        SQL Error:' + ERROR_MESSAGE())

                        select dbo.FN_Temp_LogInfo(' ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^')

            End Catch

        END

    set @@compareRes = @testResult

    /*Set the case result*/

    select dbo.[FN_Temp_LogRes](@TableName,@testResult)

    return @@compareRes

end

--declare @iTemp int

--exec SP_Temp_CompareTables 'ApxAdaptorDb101.APXAdaptor.Session', @iTemp output,'10.1.12.110', 'ApxAdaptorDb101'