Help get this topic noticed by sharing it on Twitter, Facebook, or email.

sf_validateSchema

I've noticed that the sf_refreshiad code was sub-optimal in that it downloaded the full cdc, then compared schemas, and, should a change be detected, drop the object and download a full. While this might not be noticeable on small objects, it really is crippling performance on large table which high velocity (sorry, I don't control our sfdc developpers). However, I was asked to perform hourly local replication, and the above issue was unacceptable for my use case.
I thus created sf_validateSchema, which is pretty much a copy/paste of the begining of sf_refreshIAD, except it will do a schema against a "top 0" of the object. While costing an Api call, it`s much lighter weight against objects with changing structure (there are times when the CDC download takes 2-3 hours, which I can avoid using this).
On my end, I simply test the object with this new stored proc which simply returns a 0 or 1 if the schema changed, and then I decide if I call sf_refreshIAD or sf_replicateIAD, avoiding the overhead.

Hopefully, this can help someone else:

CREATE PROCEDURE [dbo].[SF_ValidateSchema]
@table_server sysname = 'SALESFORCE_LIVE',
@table_name sysname = 'Account',
@FailOnMissingLocal bit = 0

AS
-- Parameters: @table_server - Salesforce Linked Server name (i.e. SALESFORCE)
-- @table_name - Salesforce object to copy (i.e. Account)
-- @schema_error_action - Controls the action for a schema change
-- - 'No' : FAIL on a schema change
declare @schema_error_action varchar(100) = 'no' --Always no

declare @sql nvarchar(max)
declare @parmlist nvarchar(4000)
declare @columnList nvarchar(max)
declare @deletecolumnList nvarchar(max)
declare @colname nvarchar(500)
declare @time_now char(8)
set NOCOUNT ON

print '--- Starting SF_ValidateSchema for ' + @table_name + ' ' + dbo.SF_Version()
declare @LogMessage nvarchar(max)
declare @SPName nvarchar(50)
set @SPName = 'SF_ValidateSchema:' + Convert(nvarchar(255), NEWID(), 20)
set @LogMessage = 'Parameters: ' + @table_server + ' ' + @table_name + ' ' + @schema_error_action + ' ' + ' Version: ' + dbo.SF_Version()
exec SF_Logger @SPName, N'Starting', @LogMessage

declare @delim_table_name sysname
declare @refresh_table sysname
declare @delim_refresh_table sysname
declare @delta_table sysname
declare @delim_delta_table sysname
declare @queryall_table sysname
declare @delim_queryall_table sysname

declare @server sysname
declare @database sysname
declare @timestamp_col_name nvarchar(2000)
declare @diff_schema_count int

set @schema_error_action = Lower(@schema_error_action)

Select @time_now = (select Convert(char(8),CURRENT_TIMESTAMP, 8))

-- Put delimeters around names so we can name tables User, etc...
set @delim_table_name = '[' + @table_name + ']'
set @refresh_table = 'TableRefreshTime'
set @delim_refresh_table = '[' + @refresh_table + ']'
set @delta_table = @table_name + '_Delta' + CONVERT(nvarchar(30), GETDATE(), 126)
set @delim_delta_table = '[' + @delta_table + ']'
set @queryall_table = @table_name + '_QueryAll'
set @delim_queryall_table = '[' + @queryall_table + ']'

-- Determine whether the local table and the previous copy exist
declare @table_exist int
declare @refresh_exist int
declare @delta_exist int
declare @deleted_exist int
declare @char_count varchar(10)

set @table_exist = 0
IF EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME=@table_name)
set @table_exist = 1
IF (@@ERROR <> 0) GOTO ERR_HANDLER

set @refresh_exist = 0
IF EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME=@refresh_table)
set @refresh_exist = 1
IF (@@ERROR <> 0) GOTO ERR_HANDLER

if (@refresh_exist = 0)
begin
exec ('Create Table ' + @refresh_table + ' (TblName nvarchar(255) null, LastRefreshTime datetime null default CURRENT_TIMESTAMP) ')
IF (@@ERROR <> 0) GOTO ERR_HANDLER
end

--Validate if object exists on Salesforce
declare @sf_obj_exists int
exec @sf_obj_exists = SF_IsValidSFObject @table_server,@table_name
if @sf_obj_exists = 0
Begin
Select @time_now = (select Convert(char(8),CURRENT_TIMESTAMP, 8))
print @time_now + ': Error: Salesforce table does not exist: ' + @table_name
set @LogMessage = 'Error: Salesforce table does not exist: ' + @table_name
exec SF_Logger @SPName, N'Message', @LogMessage
GOTO ERR_HANDLER
End

-- If table does not exist then replicate it
if (@table_exist = 0)
begin
Select @time_now = (select Convert(char(8),CURRENT_TIMESTAMP, 8))
print @time_now + ': Local table does not exist. Validation Returning local failure.'
set @LogMessage = 'Local table does not exist. Validation Returning local failure.'
exec SF_Logger @SPName, N'Message', @LogMessage
goto MISSINGLOCAL_EXIT
end

-- If the delta table exists, drop it
set @delta_exist = 0;
IF EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME=@delta_table)
set @delta_exist = 1
IF (@@ERROR <> 0) GOTO ERR_HANDLER

if (@delta_exist = 1)
exec ('Drop table ' + @delim_delta_table)
IF (@@ERROR <> 0) GOTO ERR_HANDLER

-- Create new delta table with updated rows
-- Retrieve current server name and database
select @server = @@servername, @database = DB_NAME()
SET @server = CAST(SERVERPROPERTY('ServerName') AS sysname)

-- Create an empty local table with the current structure of the Salesforce object
Select @time_now = (select Convert(char(8),CURRENT_TIMESTAMP, 8))
--print @time_now + ': Create ' + @delta_table + ' with new structure.'
exec ('Select Top 0 * into ' + @delim_delta_table + ' from ' + @table_server + '...' + @queryall_table )
IF (@@ERROR <> 0) GOTO ERR_HANDLER

-- Check to see if the column structure is the same
declare @cnt1 int
declare @cnt2 int
Select @cnt1 = Count(*) FROM INFORMATION_SCHEMA.COLUMNS v1 WHERE v1.TABLE_NAME=@delta_table
AND NOT EXISTS (Select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS v2
Join INFORMATION_SCHEMA.TABLES t1
On v2.TABLE_NAME = t1.TABLE_NAME and t1.TABLE_SCHEMA = v2.TABLE_SCHEMA
where (t1.TABLE_TYPE = 'BASE TABLE') and
v2.TABLE_NAME=@table_name and v1.COLUMN_NAME = v2.COLUMN_NAME and v1.DATA_TYPE = v2.DATA_TYPE
and v1.IS_NULLABLE = v2.IS_NULLABLE
and ISNULL(v1.CHARACTER_MAXIMUM_LENGTH,0) = ISNULL(v2.CHARACTER_MAXIMUM_LENGTH,0))
IF (@@ERROR <> 0) GOTO ERR_HANDLER

Select @cnt2 = Count(*) FROM INFORMATION_SCHEMA.COLUMNS v1
Join INFORMATION_SCHEMA.TABLES t1
On v1.TABLE_NAME = t1.TABLE_NAME and t1.TABLE_SCHEMA = v1.TABLE_SCHEMA
WHERE (t1.TABLE_TYPE = 'BASE TABLE') and v1.TABLE_NAME=@table_name
AND NOT EXISTS (Select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS v2 where
v2.TABLE_NAME=@delta_table and v1.COLUMN_NAME = v2.COLUMN_NAME and v1.DATA_TYPE = v2.DATA_TYPE
and v1.IS_NULLABLE = v2.IS_NULLABLE
and ISNULL(v1.CHARACTER_MAXIMUM_LENGTH,0) = ISNULL(v2.CHARACTER_MAXIMUM_LENGTH,0))
IF (@@ERROR <> 0) GOTO ERR_HANDLER

set @diff_schema_count = @cnt1 + @cnt2

if (@diff_schema_count > 0)
begin
Select @time_now = (select Convert(char(8),CURRENT_TIMESTAMP, 8))
if (@schema_error_action = 'no')
begin
print @time_now + ': Error: Table schema has changed and therefore validation returns error.'
set @LogMessage = 'Error: Table schema has changed and therefore validation returns error.'
exec SF_Logger @SPName, N'Message', @LogMessage
exec ('Drop table ' + @delim_delta_table)
GOTO ERR_HANDLER
end
end

SUCCESS:

print '--- Ending SF_ValidateSchema. Operation successful.'
set @LogMessage = 'Ending - Operation Successful.'
exec SF_Logger @SPName, N'Successful', @LogMessage
set NOCOUNT OFF
return 0

ERR_HANDLER:
-- We don't need the deleted and delta tables so drop them
IF EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME=@delta_table)
begin
exec ('Drop table ' + @delim_delta_table)
end
print('--- Ending SF_ValidateSchema. Operation FAILED.')
set @LogMessage = @LogMessage + char(10) + 'Ending - Operation Failed.'
exec SF_Logger @SPName, N'Failed', @LogMessage
RAISERROR (@LogMessage,16,1)
set NOCOUNT OFF
return 1

MISSINGLOCAL_EXIT:
IF EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME=@delta_table)
begin
exec ('Drop table ' + @delim_delta_table)
end

set @LogMessage = '--- Ending SF_ValidateSchema. Validation FAILED due to missing local table.'+';' + @table_server + ';' + @table_name
exec SF_Logger @SPName, N'Failed',@LogMessage
PRINT @LogMessage
If @FailOnMissingLocal = 1
Begin
RAISERROR (@LogMessage,16,1)
End
set NOCOUNT OFF
return 0

GO
1 person likes
this idea
+1
Reply