ODBC Driver reliablity and performance issues - can data be bulk downloaded without ODBC?

[simonlawrence]

Hi everyone - Really could do with some advice, so here's hoping someone out there has encountered a similar issue to ourselves.

I run reports that use the INCIDENT table, and there are about 170000 records within it.   We run reports that analyse this data in many different ways. Originally folks here used to use the GUI and download/export to MSEXCEL - but this only downloads a maximum of 20000 records (and can take a while getting the data too). If different people were doing different reports, they'd all be doing the same thing - separately downloading and exporting to MSEXCEL.

Trouble is - 20000 records isn't enough for our analysis - especially for year on year comparisons, or for finding out answers to "when did 'x' start happening?" and the like. We did look at extending this, but we'd much rather have the data in a "proper" DB, where the contents of the INCIDENT table can be downloaded and stored automatically ona daily basis, requiring no user interaction at all.

Obviously, its efficient to have the data in a proper database anyway - its faster, and the results can be kept centrally and referred to by everyone who needs the data.     We're using the ODBC SERVICENOW driver to achieve this, and I have to say, our experience of it isn't very good at all (how do you guys find it, out of interest?)

Ok - that's the background - heres the issue:

I want all the fields (well, nearly all) the INCIDENT table.   Id like to do:

SELECT * INTO localtable from OPENQUERY (SNODBC,'SELECT * FROM INCIDENT')

..but I cant because some of the data types don't seem to like being imported into MSSQLSERVER owing to "precision" issues, and such. (it just means I have to do a query that specifies the fields by name so I can CAST them to datatypes that work).  

Ok so far, manually specifying the fields and CASTing the datatypes works well..     here's where it gets sticky:

I cant specify all the fields I want in the same query - it just times out.   So, my solution was to have 3 queries each pulling about 20 fields, join the results together on my MSSQLSERVER and so creating the day's INCIDENT table snapshot.

That does at least get me data, but not reliably.   Usually it fails - either one or all three queries fail - and the error message seems to be different each time.   it complains about the disk cache being too small (no it isn't, I've set it to 1GB in the ODBC admin tool), or it will just time out, or today's special "Unable to retrieve error message from OpenAccess SDK IP Layer".

The crazy thing is, I might as well specify all the fields I want (across multiple queries) because if I just ask for 5 fields, it seems to take the same amount of time as asking for 20 fields.   Each query can run for about 20-30 min before either actually delivering the data or just generating errors (in which case its try it again and again and again until eventually it works).

So, based on my experience so far - I'm finding it very hard to see how the ODBC driver is reliable enough to just do its job without constant intervention from technical resources that shouldn't have to be looking in on this -   is there any other way we can get our data?   Does SERVICENOW provide a daily data-dump-and-download-over-ftp service or similar?   I don't really mind HOW the data arrives here - but we need this daily - and the ODBC method just doesn't seem up to the job, and ive got a lot of fed up folks here who are depending on being able to use our data.

I've tried disabling the timeout on SERVICENOW ODBC, but this just means the queries don't work at all - they immediately fail.

Bandwidth to internet isn't a problem - we are on a 250Mbps connection .. guys I'm really running out of ideas,     if anyone has any suggestions,   I'm open to them.

Thanks!
-Simon

[Chris M3]

One thing to keep in mind is this solution does not work for tables that allow deletions.

We don't currently use the ODBC, when we started external reporting it was even more unreliable then it is now.   Instead we have some custom processes that make SOAP calls into the instance, downloading deltas each day.   We're currently looking at both export sets and the ODBC to supplement our existing process.

For tables that allow deletions (For example, task_ci), we do the deltas, and then we do the SOAP call to get all of the sys_id's in the table.   Reconcile off of those to handle deletions.   You can probably do something similiar with the ODBC, just not sure how it will handle tables with millions of rows.

[simonw]

Great to hear you have a working solution now.  

I noticed with the release of Helsinki there is an updated ODBC driver which addresses the concurrent connection issue in 1.0.9

[simonlawrence]

Well, that approach works kinda well if you want to download all the records all at once.   This is my (hopefully final) approach that uses DynamicSQL and only pulls back changes (modifies incidents and new incident records) to a local "master" copy:

My example needs 2 tables, that are identical in structure.   I called them SNOWINCIDENTS and SNOWINCIDENTSDELTAS_TEMP.

The former is my "master" table that I do my reporting on, the "_TEMP" table are the new and modified records ("new" or "modified" as measured by any record that has a SYS_UPDATED_ON date newer than the most recent date in SNOWINCIDENTS.SYS_UPDATED_ON.

Once I have the new and modified records in the _TEMP staging table, I then run a MERGE statement to combine the records into SNOWINCIDENTS.       If the reference number from _TEMP is found in SNOWINCIDENTS, then it will rewrite all columns for that incident number's row, otherwise it will insert a new record, again, writing all the columns for that incident.

My query pulls back all the columns I'm interested in, but you can of course modify it to pull back less or more as needed.   As I write this, I am running this every 5 min, and I'm pulling down about 20-50 records each time (compared to the 190000 total records I was pulling down with my previous method).

the main logic is kept as a SQL Stored Procedure (this deals with fetching the records from SERIVICENOW ODBC and pulling them into a temp table before merging the records into the master local copy).

I then have a MS WINDOWS Scheduled Task that runs every 5 min that runs whether a user is logged in or not which runs:

SQLCMD -S myserver.local.lan -U mySqlLoginID -P mySqlLoginIDPassword -d mySqlDatabase -Q "exec spUpdateSnowIncidentTable"

so, here is the definition for SNOWINCIDENTS and SNOWINCIDENTSDELTA_TEMP (they are identical in structure)

CREATE TABLE [snowIncidents]   --also use this definition for snowIncidentsDeltas_temp

(
[number] [nvarchar](40) NULL,
[description] [nvarchar](4000) NULL,
[short_description] [nvarchar](150) NULL,
[priority] [int] NULL,
[dv_priority] [nvarchar](40) NULL,
[impact] [int] NULL,
[dv_impact] [nvarchar](40) NULL,
[opened_at] [datetime2](7) NULL,
[dv_opened_by] [nvarchar](151) NULL,
[dv_assignment_group] [nvarchar](80) NULL,
[dv_assigned_to] [nvarchar](151) NULL,
[reassignment_count] [int] NULL,
[closed_at] [datetime2](7) NULL,
[sys_updated_on] [datetime2](7) NULL,
[sys_updated_by] [nvarchar](40) NULL,
[dv_closed_by] [nvarchar](151) NULL,
[close_notes] [nvarchar](4000) NULL,
[active] [bit] NULL,
[dv_state] [nvarchar](40) NULL,
[sys_mod_count] [int] NULL,
[dv_u_vendor_group] [nvarchar](80) NULL,
[dv_incident_state] [nvarchar](40) NULL,
[sys_created_by] [nvarchar](40) NULL,
[dv_parent_incident] [nvarchar](40) NULL,
[dv_caused_by] [nvarchar](40) NULL,
[reOpen_Count] [int] NULL,
[dv_u_symptom] [nvarchar](40) NULL,
[u_cost_centre] [nvarchar](40) NULL,
[dv_parent] [nvarchar](40) NULL,
[u_machine_code_full] [nvarchar](40) NULL,
[u_machine_manufacturer] [nvarchar](40) NULL,
[u_vendor_ref] [nvarchar](40) NULL,
[u_sla_name] [nvarchar](40) NULL,
[calendar_durationSS] [int] NULL,
[business_durationSS] [int] NULL,
[approval] [nvarchar](40) NULL,
[approval_set] [datetime2](7) NULL,
[assigned_to] [nvarchar](151) NULL,
[assignment_group] [nvarchar](80) NULL,
[category] [nvarchar](40) NULL,
[caused_by] [nvarchar](40) NULL,
[child_incidents] [int] NULL,
[close_code] [nvarchar](40) NULL,
[closed_by] [nvarchar](151) NULL,
[cmdb_ci] [nvarchar](255) NULL,
[company] [nvarchar](80) NULL,
[contact_type] [nvarchar](40) NULL,
[due_date] [datetime2](7) NULL,
[dv_approval] [nvarchar](40) NULL,
[dv_caller_id] [nvarchar](151) NULL,
[dv_category] [nvarchar](40) NULL,
[dv_close_code] [nvarchar](40) NULL,
[dv_company] [nvarchar](80) NULL,
[dv_contact_type] [nvarchar](40) NULL,
[dv_delivery_plan] [nvarchar](100) NULL,
[dv_delivery_task] [nvarchar](100) NULL,
[dv_escalation] [nvarchar](40) NULL,
[dv_location] [nvarchar](100) NULL,
[dv_notify] [nvarchar](40) NULL,
[dv_problem_id] [nvarchar](40) NULL,
[dv_rejection_goto] [nvarchar](40) NULL,
[dv_resolved_by] [nvarchar](151) NULL,
[dv_rfc] [nvarchar](40) NULL,
[dv_severity] [nvarchar](40) NULL,
[dv_subcategory] [nvarchar](40) NULL,
[dv_sys_class_name] [nvarchar](80) NULL,
[dv_u_cause_code] [nvarchar](80) NULL,
[dv_u_cause_code_inspired] [nvarchar](100) NULL,
[dv_u_incident_owner] [nvarchar](40) NULL,
[dv_u_inspired_closure_code] [nvarchar](100) NULL,
[dv_u_inspired_priority] [nvarchar](40) NULL,
[dv_u_machine_playable] [nvarchar](40) NULL,
[dv_u_machine_type] [nvarchar](40) NULL,
[dv_u_mmr] [nvarchar](40) NULL,
[dv_u_position_number] [nvarchar](40) NULL,
[dv_u_postion_mmr_ci] [nvarchar](40) NULL,
[dv_u_problem_code] [nvarchar](80) NULL,
[dv_u_repair_code] [nvarchar](40) NULL,
[dv_upon_approval] [nvarchar](40) NULL,
[dv_cmdb_ci] [nvarchar](255) NULL,
[dv_upon_reject] [nvarchar](40) NULL,
[dv_urgency] [nvarchar](40) NULL,
[dv_wf_activity] [nvarchar](100) NULL,
[escalation] [int] NULL,
[incident_state] [int] NULL,
[knowledge] [bit] NULL,
[made_sla] [bit] NULL,
[notify] [int] NULL,
[opened_by] [nvarchar](151) NULL,
[parent] [nvarchar](40) NULL,
[parent_incident] [nvarchar](40) NULL,
[problem_id] [nvarchar](40) NULL,
[resolved_at] [datetime2](7) NULL,
[resolved_by] [nvarchar](151) NULL,
[rfc] [nvarchar](40) NULL,
[severity] [int] NULL,
[sla_due] [datetime2](7) NULL,
[state] [int] NULL,
[subcategory] [nvarchar](40) NULL,
[sys_class_name] [nvarchar](80) NULL,
[sys_created_on] [datetime2](7) NULL,
[time_worked] [nvarchar](40) NULL,
[u_cause_code] [nvarchar](80) NULL,
[u_cause_code_inspired] [nvarchar](100) NULL,
[u_delivery_missed] [bit] NULL,
[u_direct_contact_number] [nvarchar](40) NULL,
[u_hostname] [nvarchar](40) NULL,
[u_incident_owner] [nvarchar](40) NULL,
[u_inspired_call_reference] [nvarchar](40) NULL,
[u_inspired_closure_code] [nvarchar](100) NULL,
[u_inspired_priority] [nvarchar](40) NULL,
[u_last_reopened] [datetime2](7) NULL,
[u_machine_category] [nvarchar](40) NULL,
[u_machine_name] [nvarchar](40) NULL,
[u_machine_playable] [nvarchar](40) NULL,
[u_machine_type] [nvarchar](40) NULL,
[u_mmr] [nvarchar](40) NULL,
[u_planned_end_date] [nvarchar](40) NULL,
[u_planned_end_time] [datetime2](7) NULL,
[u_position_number] [nvarchar](40) NULL,
[u_postion_mmr_ci] [nvarchar](40) NULL,
[u_problem_code] [nvarchar](80) NULL,
[u_repair_code] [nvarchar](40) NULL,
[u_subsystem] [nvarchar](40) NULL,
[u_symptom] [nvarchar](40) NULL,
[u_user_subcategory] [nvarchar](40) NULL,
[u_variable_details] [nvarchar](4000) NULL,
[u_vendor_group] [nvarchar](80) NULL,
[urgency] [int] NULL,
[user_input] [nvarchar](4000) NULL,
[watch_list] [nvarchar](4000) NULL,
[wf_activity] [nvarchar](100) NULL,
[work_end] [datetime2](7) NULL,
[work_notes] [nvarchar](4000) NULL,
[work_notes_list] [nvarchar](4000) NULL,
[work_start] [datetime2](7) NULL,
[sqlWriteDate] [datetime] NULL
) ON [PRIMARY]

GO

.. and here is the stored proc that does the work:

alter procedure spUpdateSnowIncidentTable
as

--Adds new Incident records and updates existing records from SERVICENOW based on MAX(SNOWINCIDENTS.SYS_UPDATED_ON) date. Records that exist in SERVICENOW after this date are
--downloaded and either updated or added.
--This script is intented to be run very reguarly (on the hour, or more frequently if desired) - and it will only pull records that we dont already have (because theyre new or
--they were updated).

--Author: Simon Lawrence <problem.management@galaleisure.com>
--V1000

if exists (select name from sys.all_objects where name = 'snowincidentsDeltas_temp' and type = 'u' ) delete from snowIncidentsDeltas_temp --erase contents of temp SERVICENOW extract

declare @sqlSelectHeader varchar(200) =   --first part of SQL string
N'
insert into snowincidentsDeltas_temp
select number,'

declare @sqlStore1 varchar(1000) =   --collection and Local type-casting
N'
description,
short_description,
priority,
dv_priority,
impact,
dv_impact,
opened_at,
dv_opened_by,
dv_assignment_group,
dv_assigned_to,
reassignment_count,
closed_at,
sys_updated_on, --when last modified
sys_updated_by, --who last modified
dv_closed_by,
close_notes,
active,
dv_state,
sys_mod_count,
dv_u_vendor_group,
dv_incident_state,
sys_created_by,
dv_parent_incident,
dv_caused_by,
reOpen_Count,
dv_u_symptom,
u_cost_centre,
dv_parent,
u_machine_code_full,
u_machine_manufacturer,
u_vendor_ref,
u_sla_name,
datediff(second,''1970-01-01'',calendar_duration) as calendar_durationSS,
datediff(second,''1970-01-01'',business_duration) as business_durationSS'

declare @sqlStore2 varchar(1000) =
N'
approval,
approval_set,
assigned_to,
assignment_group,
category,
caused_by,
child_incidents,
close_code,
closed_by,
cmdb_ci,
company,
contact_type,
due_date,
dv_approval,
dv_caller_id,
dv_category,
dv_close_code,
dv_company,
dv_contact_type,
dv_delivery_plan,
dv_delivery_task,
dv_escalation,
dv_location,
dv_notify'

declare @sqlStore3 varchar (1000) =
N'
dv_problem_id,
dv_rejection_goto,
dv_resolved_by,
dv_rfc,
dv_severity,
dv_subcategory,
dv_sys_class_name,
dv_u_cause_code,
dv_u_cause_code_inspired,
dv_u_incident_owner,
dv_u_inspired_closure_code,
dv_u_inspired_priority,
dv_u_machine_playable,
dv_u_machine_type,
dv_u_mmr,
dv_u_position_number,
dv_u_postion_mmr_ci,
dv_u_problem_code,
dv_u_repair_code,
dv_upon_approval,
dv_cmdb_ci,
dv_upon_reject,
dv_urgency,
dv_wf_activity,
escalation,
incident_state,
knowledge,
made_sla,
notify,
opened_by,
parent'

declare @sqlStore4 varchar (1000) =
N'
parent_incident,
problem_id,
resolved_at,
resolved_by,
rfc,
severity,
sla_due,
state,
subcategory,
sys_class_name,
sys_created_on,
time_worked,
u_cause_code,
u_cause_code_inspired,
u_delivery_missed,
u_direct_contact_number,
u_hostname,
u_incident_owner,
u_inspired_call_reference,
u_inspired_closure_code,
u_inspired_priority,
u_last_reopened,
u_machine_category,
u_machine_name,
u_machine_playable,
u_machine_type,
u_mmr,
u_planned_end_date,
u_planned_end_time,
u_position_number,
u_postion_mmr_ci,
u_problem_code,
u_repair_code,
u_subsystem,
u_symptom,
u_user_subcategory,
u_variable_details,
u_vendor_group,
urgency,
user_input,
watch_list,
wf_activity,
work_end,
work_notes,
work_notes_list,
work_start'

declare @sqlFrom varchar (300) = --from clause string. Z1 is the MSSQL Linked Server name for the SERVICENOW ODBC link.
N'
from
openquery --numerics need to be used with CAST other MSSQLSERVER has "precision exceeds max" error which is why not using [SELECT * FROM OPENQUERY(....   ]
(z1,''select number,'
 
declare @sqlRemoteSelect1 varchar(1000) = --remote
N'
description,
dv_state,
opened_at,
calendar_duration,
business_duration,
dv_assignment_group,
dv_assigned_to,
dv_opened_by,
cast(reassignment_count as integer) as reassignment_count,
cast(impact as integer) as impact,
dv_impact,
sys_updated_on,
closed_at,
close_notes,
active,
cast(priority as int) as priority,
dv_priority,
cast(sys_mod_count as integer) as sys_mod_count,
dv_closed_by,
sys_updated_by,
short_description,
dv_u_vendor_group,
dv_incident_state,
sys_created_by,
dv_parent_incident,
dv_caused_by,
cast(reopen_count as integer) as reOpen_Count,
dv_u_symptom,
u_cost_centre,
dv_parent,
u_machine_code_full,
u_machine_manufacturer,
u_vendor_ref,
u_sla_name'

declare @sqlRemoteSelect2 varchar(1000) =
N'
approval,
approval_set,
assigned_to,
assignment_group,
category,
caused_by,
cast (child_incidents as integer) as child_incidents,
close_code,
closed_by,
cmdb_ci,
company,
contact_type,
due_date,
dv_approval,
dv_caller_id,
dv_category,
dv_close_code,
dv_company,
dv_contact_type,
dv_delivery_plan,
dv_delivery_task,
dv_escalation,
dv_location,
dv_notify'

declare @sqlRemoteSelect3 varchar (1000) =
N'
dv_problem_id,
dv_rejection_goto,
dv_resolved_by,
dv_rfc,
dv_severity,
dv_subcategory,
dv_sys_class_name,
dv_u_cause_code,
dv_u_cause_code_inspired,
dv_u_incident_owner,
dv_u_inspired_closure_code,
dv_u_inspired_priority,
dv_u_machine_playable,
dv_u_machine_type,
dv_u_mmr,
dv_u_position_number,
dv_u_postion_mmr_ci,
dv_u_problem_code,
dv_u_repair_code,
dv_upon_approval,
dv_cmdb_ci,
dv_upon_reject,
dv_urgency,
dv_wf_activity,
cast (escalation as integer) as escalation,
cast (incident_state as integer) as incident_state,
knowledge,
made_sla,
cast (notify as integer) as notify,
opened_by,
parent'

declare @sqlRemoteSelect4 varchar (1000) =
N'
parent_incident,
problem_id,
resolved_at,
resolved_by,
rfc,
cast (severity as integer) as severity,
sla_due,
cast (state as integer) as state,
subcategory,
sys_class_name,
sys_created_on,
time_worked,
u_cause_code,
u_cause_code_inspired,
u_delivery_missed,
u_direct_contact_number,
u_hostname,
u_incident_owner,
u_inspired_call_reference,
u_inspired_closure_code,
u_inspired_priority,
u_last_reopened,
u_machine_category,
u_machine_name,
u_machine_playable,
u_machine_type,
u_mmr,
u_planned_end_date,
u_planned_end_time,
u_position_number,
u_postion_mmr_ci,
u_problem_code,
u_repair_code,
u_subsystem,
u_symptom,
u_user_subcategory,
u_variable_details,
u_vendor_group,
cast (urgency as integer) as urgency,
user_input,
watch_list,
wf_activity,
work_end,
work_notes,
work_notes_list,
work_start'

declare @sqlRemoteSelectFrom varchar (300) = --this section makes sure we only download DELTA records, not the entire table.   SERVICENOW ODBC system is too unreliable for large transfers, so regular deltas are the way to go.
N'
from incident where sys_updated_on > ''''' + cast((select max(sys_updated_on) from snowIncidents)   as varchar) + ''''''')' --only get records more recent than the most recent record we currently have. This will include existing (but updated) records and new records

declare @sqlCommand varchar (8000) = rtrim(   --assembles the dynamic SQL string.   Could be done in a single "hit" - but this aided debugging during development.
    ltrim(rtrim(@sqlSelectHeader))
  + ltrim(rtrim(@sqlstore1)) + ','+
  + ltrim(rtrim(@sqlstore2)) + ','+
  + ltrim(rtrim(@sqlstore3)) + ','+
  + ltrim(rtrim(@sqlstore4))
  + ltrim(rtrim(@sqlFrom))
  + ltrim(rtrim(@sqlRemoteSelect1)) + ','+
  + ltrim(rtrim(@sqlRemoteSelect2)) + ','+
  + ltrim(rtrim(@sqlRemoteSelect3)) + ','+
  + ltrim(rtrim(@sqlRemoteSelect4))
  + ltrim(rtrim(@sqlRemoteSelectFrom))
  )

begin

exec   (@sqlCommand) --execute the SQL statement in @sqlCommand string - fetches the delta records into local temp table SNOWINCIDENTSDELTAS_TEMP)

merge
snowIncidents as target   --set main table (destination) as the target
using snowIncidentsDeltas_temp as source on (target.number = source.number) --set the delta storage as the source
when matched then     --if there is a match (the same reference number exists in source and target, update the entire row in the target table)
update
  set
    target.number = source.number,
    target.description = source.description,
    target.short_description = source.short_description,
    target.priority = source.priority,
    target.dv_priority = source.dv_priority,
    target.impact = source.impact,
    target.dv_impact = source.dv_impact,
    target.opened_at = source.opened_at,
    target.dv_opened_by = source.dv_opened_by,
    target.dv_assignment_group = source.dv_assignment_group,
    target.dv_assigned_to = source.dv_assigned_to,
    target.reassignment_count = source.reassignment_count,
    target.closed_at = source.closed_at,
    target.sys_updated_on = source.sys_updated_on,
    target.sys_updated_by = source.sys_updated_by,
    target.dv_closed_by = source.dv_closed_by,
    target.close_notes = source.close_notes,
    target.active = source.active,
    target.dv_state = source.dv_state,
    target.sys_mod_count = source.sys_mod_count,
    target.dv_u_vendor_group = source.dv_u_vendor_group,
    target.dv_incident_state = source.dv_incident_state,
    target.sys_created_by = source.sys_created_by,
    target.dv_parent_incident = source.dv_parent_incident,
    target.dv_caused_by = source.dv_caused_by,
    target.reOpen_Count = source.reOpen_Count,
    target.dv_u_symptom = source.dv_u_symptom,
    target.u_cost_centre = source.u_cost_centre,
    target.dv_parent = source.dv_parent,
    target.u_machine_code_full = source.u_machine_code_full,
    target.u_machine_manufacturer = source.u_machine_manufacturer,
    target.u_vendor_ref = source.u_vendor_ref,
    target.u_sla_name = source.u_sla_name,
    target.calendar_durationSS = source.calendar_durationSS,
    target.business_durationSS = source.business_durationSS,
    target.approval = source.approval,
    target.approval_set = source.approval_set,
    target.assigned_to = source.assigned_to,
    target.assignment_group = source.assignment_group,
    target.category = source.category,
    target.caused_by = source.caused_by,
    target.child_incidents = source.child_incidents,
    target.close_code = source.close_code,
    target.closed_by = source.closed_by,
    target.cmdb_ci = source.cmdb_ci,
    target.company = source.company,
    target.contact_type = source.contact_type,
    target.due_date = source.due_date,
    target.dv_approval = source.dv_approval,
    target.dv_caller_id = source.dv_caller_id,
    target.dv_category = source.dv_category,
    target.dv_close_code = source.dv_close_code,
    target.dv_company = source.dv_company,
    target.dv_contact_type = source.dv_contact_type,
    target.dv_delivery_plan = source.dv_delivery_plan,
    target.dv_delivery_task = source.dv_delivery_task,
    target.dv_escalation = source.dv_escalation,
    target.dv_location = source.dv_location,
    target.dv_notify = source.dv_notify,
    target.dv_problem_id = source.dv_problem_id,
    target.dv_rejection_goto = source.dv_rejection_goto,
    target.dv_resolved_by = source.dv_resolved_by,
    target.dv_rfc = source.dv_rfc,
    target.dv_severity = source.dv_severity,
    target.dv_subcategory = source.dv_subcategory,
    target.dv_sys_class_name = source.dv_sys_class_name,
    target.dv_u_cause_code = source.dv_u_cause_code,
    target.dv_u_cause_code_inspired = source.dv_u_cause_code_inspired,
    target.dv_u_incident_owner = source.dv_u_incident_owner,
    target.dv_u_inspired_closure_code = source.dv_u_inspired_closure_code,
    target.dv_u_inspired_priority = source.dv_u_inspired_priority,
    target.dv_u_machine_playable = source.dv_u_machine_playable,
    target.dv_u_machine_type = source.dv_u_machine_type,
    target.dv_u_mmr = source.dv_u_mmr,
    target.dv_u_position_number = source.dv_u_position_number,
    target.dv_u_postion_mmr_ci = source.dv_u_postion_mmr_ci,
    target.dv_u_problem_code = source.dv_u_problem_code,
    target.dv_u_repair_code = source.dv_u_repair_code,
    target.dv_upon_approval = source.dv_upon_approval,
    target.dv_cmdb_ci = source.dv_cmdb_ci,
    target.dv_upon_reject = source.dv_upon_reject,
    target.dv_urgency = source.dv_urgency,
    target.dv_wf_activity = source.dv_wf_activity,
    target.escalation = source.escalation,
    target.incident_state = source.incident_state,
    target.knowledge = source.knowledge,
    target.made_sla = source.made_sla,
    target.notify = source.notify,
    target.opened_by = source.opened_by,
    target.parent = source.parent,
    target.parent_incident = source.parent_incident,
    target.problem_id = source.problem_id,
    target.resolved_at = source.resolved_at,
    target.resolved_by = source.resolved_by,
    target.rfc = source.rfc,
    target.severity = source.severity,
    target.sla_due = source.sla_due,
    target.state = source.state,
    target.subcategory = source.subcategory,
    target.sys_class_name = source.sys_class_name,
    target.sys_created_on = source.sys_created_on,
    target.time_worked = source.time_worked,
    target.u_cause_code = source.u_cause_code,
    target.u_cause_code_inspired = source.u_cause_code_inspired,
    target.u_delivery_missed = source.u_delivery_missed,
    target.u_direct_contact_number = source.u_direct_contact_number,
    target.u_hostname = source.u_hostname,
    target.u_incident_owner = source.u_incident_owner,
    target.u_inspired_call_reference = source.u_inspired_call_reference,
    target.u_inspired_closure_code = source.u_inspired_closure_code,
    target.u_inspired_priority = source.u_inspired_priority,
    target.u_last_reopened = source.u_last_reopened,
    target.u_machine_category = source.u_machine_category,
    target.u_machine_name = source.u_machine_name,
    target.u_machine_playable = source.u_machine_playable,
    target.u_machine_type = source.u_machine_type,
    target.u_mmr = source.u_mmr,
    target.u_planned_end_date = source.u_planned_end_date,
    target.u_planned_end_time = source.u_planned_end_time,
    target.u_position_number = source.u_position_number,
    target.u_postion_mmr_ci = source.u_postion_mmr_ci,
    target.u_problem_code = source.u_problem_code,
    target.u_repair_code = source.u_repair_code,
    target.u_subsystem = source.u_subsystem,
    target.u_symptom = source.u_symptom,
    target.u_user_subcategory = source.u_user_subcategory,
    target.u_variable_details = source.u_variable_details,
    target.u_vendor_group = source.u_vendor_group,
    target.urgency = source.urgency,
    target.user_input = source.user_input,
    target.watch_list = source.watch_list,
    target.wf_activity = source.wf_activity,
    target.work_end = source.work_end,
    target.work_notes = source.work_notes,
    target.work_notes_list = source.work_notes_list,
    target.work_start = source.work_start,

    target.sqlWriteDate = current_timestamp --.. and finally, a timestamp to show when the record was updated.

when not matched by target then --reference number not found in target table?   must be a new record. Write a new row:
insert
  (
    number,
    description,
    short_description,
    priority,
    dv_priority,
    impact,
    dv_impact,
    opened_at,
    dv_opened_by,
    dv_assignment_group,
    dv_assigned_to,
    reassignment_count,
    closed_at,
    sys_updated_on,
    sys_updated_by,
    dv_closed_by,
    close_notes,
    active,
    dv_state,
    sys_mod_count,
    dv_u_vendor_group,
    dv_incident_state,
    sys_created_by,
    dv_parent_incident,
    dv_caused_by,
    reOpen_Count,
    dv_u_symptom,
    u_cost_centre,
    dv_parent,
    u_machine_code_full,
    u_machine_manufacturer,
    u_vendor_ref,
    u_sla_name,
    calendar_durationSS,
    business_durationSS,
    approval,
    approval_set,
    assigned_to,
    assignment_group,
    category,
    caused_by,
    child_incidents,
    close_code,
    closed_by,
    cmdb_ci,
    company,
    contact_type,
    due_date,
    dv_approval,
    dv_caller_id,
    dv_category,
    dv_close_code,
    dv_company,
    dv_contact_type,
    dv_delivery_plan,
    dv_delivery_task,
    dv_escalation,
    dv_location,
    dv_notify,
    dv_problem_id,
    dv_rejection_goto,
    dv_resolved_by,
    dv_rfc,
    dv_severity,
    dv_subcategory,
    dv_sys_class_name,
    dv_u_cause_code,
    dv_u_cause_code_inspired,
    dv_u_incident_owner,
    dv_u_inspired_closure_code,
    dv_u_inspired_priority,
    dv_u_machine_playable,
    dv_u_machine_type,
    dv_u_mmr,
    dv_u_position_number,
    dv_u_postion_mmr_ci,
    dv_u_problem_code,
    dv_u_repair_code,
    dv_upon_approval,
    dv_cmdb_ci,
    dv_upon_reject,
    dv_urgency,
    dv_wf_activity,
    escalation,
    incident_state,
    knowledge,
    made_sla,
    notify,
    opened_by,
    parent,
    parent_incident,
    problem_id,
    resolved_at,
    resolved_by,
    rfc,
    severity,
    sla_due,
    state,
    subcategory,
    sys_class_name,
    sys_created_on,
    time_worked,
    u_cause_code,
    u_cause_code_inspired,
    u_delivery_missed,
    u_direct_contact_number,
    u_hostname,
    u_incident_owner,
    u_inspired_call_reference,
    u_inspired_closure_code,
    u_inspired_priority,
    u_last_reopened,
    u_machine_category,
    u_machine_name,
    u_machine_playable,
    u_machine_type,
    u_mmr,
    u_planned_end_date,
    u_planned_end_time,
    u_position_number,
    u_postion_mmr_ci,
    u_problem_code,
    u_repair_code,
    u_subsystem,
    u_symptom,
    u_user_subcategory,
    u_variable_details,
    u_vendor_group,
    urgency,
    user_input,
    watch_list,
    wf_activity,
    work_end,
    work_notes,
    work_notes_list,
    work_start,

    sqlWriteDate
    )
    values
      (
        source.number,
        source.description,
        source.short_description,
        source.priority,
        source.dv_priority,
        source.impact,
        source.dv_impact,
        source.opened_at,
        source.dv_opened_by,
        source.dv_assignment_group,
        source.dv_assigned_to,
        source.reassignment_count,
        source.closed_at,
        source.sys_updated_on,
        source.sys_updated_by,
        source.dv_closed_by,
        source.close_notes,
        source.active,
        source.dv_state,
        source.sys_mod_count,
        source.dv_u_vendor_group,
        source.dv_incident_state,
        source.sys_created_by,
        source.dv_parent_incident,
        source.dv_caused_by,
        source.reOpen_Count,
        source.dv_u_symptom,
        source.u_cost_centre,
        source.dv_parent,
        source.u_machine_code_full,
        source.u_machine_manufacturer,
        source.u_vendor_ref,
        source.u_sla_name,
        source.calendar_durationSS,
        source.business_durationSS,
        source.approval,
        source.approval_set,
        source.assigned_to,
        source.assignment_group,
        source.category,
        source.caused_by,
        source.child_incidents,
        source.close_code,
        source.closed_by,
        source.cmdb_ci,
        source.company,
        source.contact_type,
        source.due_date,
        source.dv_approval,
        source.dv_caller_id,
        source.dv_category,
        source.dv_close_code,
        source.dv_company,
        source.dv_contact_type,
        source.dv_delivery_plan,
        source.dv_delivery_task,
        source.dv_escalation,
        source.dv_location,
        source.dv_notify,
        source.dv_problem_id,
        source.dv_rejection_goto,
        source.dv_resolved_by,
        source.dv_rfc,
        source.dv_severity,
        source.dv_subcategory,
        source.dv_sys_class_name,
        source.dv_u_cause_code,
        source.dv_u_cause_code_inspired,
        source.dv_u_incident_owner,
        source.dv_u_inspired_closure_code,
        source.dv_u_inspired_priority,
        source.dv_u_machine_playable,
        source.dv_u_machine_type,
        source.dv_u_mmr,
        source.dv_u_position_number,
        source.dv_u_postion_mmr_ci,
        source.dv_u_problem_code,
        source.dv_u_repair_code,
        source.dv_upon_approval,
        source.dv_cmdb_ci,
        source.dv_upon_reject,
        source.dv_urgency,
        source.dv_wf_activity,
        source.escalation,
        source.incident_state,
        source.knowledge,
        source.made_sla,
        source.notify,
        source.opened_by,
        source.parent,
        source.parent_incident,
        source.problem_id,
        source.resolved_at,
        source.resolved_by,
        source.rfc,
        source.severity,
        source.sla_due,
        source.state,
        source.subcategory,
        source.sys_class_name,
        source.sys_created_on,
        source.time_worked,
        source.u_cause_code,
        source.u_cause_code_inspired,
        source.u_delivery_missed,
        source.u_direct_contact_number,
        source.u_hostname,
        source.u_incident_owner,
        source.u_inspired_call_reference,
        source.u_inspired_closure_code,
        source.u_inspired_priority,
        source.u_last_reopened,
        source.u_machine_category,
        source.u_machine_name,
        source.u_machine_playable,
        source.u_machine_type,
        source.u_mmr,
        source.u_planned_end_date,
        source.u_planned_end_time,
        source.u_position_number,
        source.u_postion_mmr_ci,
        source.u_problem_code,
        source.u_repair_code,
        source.u_subsystem,
        source.u_symptom,
        source.u_user_subcategory,
        source.u_variable_details,
        source.u_vendor_group,
        source.urgency,
        source.user_input,
        source.watch_list,
        source.wf_activity,
        source.work_end,
        source.work_notes,
        source.work_notes_list,
        source.work_start,
       
        current_timestamp   --and write a timestamp to show when the record was written
        )
;

return
end

.. Obviously , modify this as you need to - you may be collecting more or less fields (and there are some instance-specific fields in here) but you can see how it works, which should give you hints where you need to make changes.

The way that the dynamicSQL statement is assembled is a little awkward - it was done in segments so I could "switch off" some bits while I was writing it, but i'll rewrite this another day to make it a little neater.

Anyway, this is doing exactly what I needed - left it running for 3 hours now, and its rock solid.       The awesome thing is, even if it fails, I still have records in the local SNOWINCIDENTS table so reports will still work.   *if* if fails, it will probably only fail for that single attempt, so the next time the script runs, it'll just have twice as many updates to do as it would have done.   (seems to be just as happy updating 3000 records in one go as 20-50 records in one go, so I reckon this is "server-grade" as I call it)

Hope it helps you guys out - sure wish there was something like this when I was initially trying to work out how to get SNOW records local for reporting purposes, but learned loads from it - and maybe this will give someone out there a head start when trying to do something similar.

All the best,

Si

[tony_barratt]

Hi Simon,

Good point about the deltas.

Also, new in Geneva is the ability to export via the MID Server

Export sets

That could be useful depending on use case.

Best Regards

Tony

[Andy26]

Hi Simon,

I am having the same requirement where customer wants to have copy of DB backup on local data base. we have setup odbc connection with instance however now I am stuck how to query and insert the data in local database.

I am new to service, please excuse me if my query is silly 😞