Central Repository Merge Errors

General
1

Hi - first off thanks for the product, I’m very much enjoying getting to grips with it
I’m just running into a few issues I hope you can help with, apologies if this is addressed elsewhere that I have missed or if I’m being an idiot

I have SQLWATCH installed on a number of servers and am using a central repository, running SqlWatchImport.exe regularly to import data
However the import job often errors on merge statements failing in Table A due to FK constraints on Table B
When I look I can confirm that rows are missing in Table B in the Central Repository which would cause the FK constraint to fail, but when I check the SqlWatchImport logs I find that Table B has been checked and determined there are no rows to import as in below - dbo.sqlwatch_meta_procedure being Table B in this case:

2021-06-22 15:07:39.831 DEBUG Fetched “Last Seen” (“2021-06-22 08:27:29.600”) from “dbo.sqlwatch_meta_procedure” for “[]” in 7.8517ms. (Thread: 14)
2021-06-22 15:07:39.847 DEBUG Nothing to import from “[].dbo.sqlwatch_meta_procedure” (Thread: 26)

I’m not understanding why it is deciding “Nothing to import…” for this table even though there are multiple rows in the source instance of dbo.sqlwatch_meta_procedure which have a “Last Seen” later than 2021-06-22 08:27:29.600 and which are not in my target instance, ie my Central Repository

Hope thats clear…
Any advice would be very welcome, thanks…

2

You may have to try full load.
If you look into the app.config you will find a switch for the full load.
However, it will do full load for all of the remotes so you may have to put those in question in a separate environment, and set the same in the app.config to only process the selected remotes.

As for the meta tables, new records at source should be added straight away but for the existing records they are only updated once every 24 hours. It’s kind of a feature to make sure we don’t load meta tables too often but in reality it was a bad choice. I’ll be changing it in the next release.

Let me know how you get on.

3

Thanks Marcin, I’ll look at giving that a try, although it seems to be happening to different instances at different points. I feel I may end up with all instances in the full load environment in a short space of time. Will see how it goes though, thanks

4

Once you have run a full load, try to see if there is pattern if it happens again. I wonder if maybe you’re getting load errors prior to the merge errors so not all data is imported which then results in merge errors in subsequent imports?

5

Yeah there are no errors before the merge errors. As I say though the tables referenced in the FK constraints are logging as “Nothing to import…” even though there are new rows - its these rows not being identified for some reason and therefore not being loaded which is causing the FK constraint in the 2nd table and therefore the errors on the merge statement

I will attempt full loads and then switch back to deltas as you say though and see if it persists - thanks!

6

Which tables are they?

7

The tables I’m seeing the missing rows in the repository for currently are:

dbo.sqlwatch_meta_procedure
dbo.sqlwatch_meta_check
dbo.sqlwatch_meta_database
dbo.sqlwatch_meta_os_volume

the merge statements are failing in various tables but usually down to conflicts, ie missing rows, in the above tables
I know you said the meta tables only update existing rows every 24hrs but these all have new rows in source which aren’t making their way to target (Repository)

Thanks

8

So you get “Nothing to import…” message for meta tables (say dbo.sqlwatch_meta_os_volume) that have new rows in the source and those rows do not yet exist in the repo?

Can you run this on the repo and remote and show me results please? You can obfuscate the server names but in a way that I can still link them together:

	select 
		max_date_last_seen=max(date_last_seen)
		, cnt=count(*)
		, sql_instance
	from dbo.sqlwatch_meta_database
	group by sql_instance
9

Hi yeah thats the scenario I’m seeing.
I ran that query for 3 of the tables affected and added environment and table to the below output, I obviously filtered the query on the repo to just bring the relevant instance. The SqlWatchImport.exe ran just before I ran the query:

max_date_last_seen cnt sql_instance environment table
2021-06-22 08:27:22.740 17 SQLInstance1 Repo dbo.sqlwatch_meta_database
2021-06-23 08:00:26.527 19 SQLInstance1 Source dbo.sqlwatch_meta_database
2021-06-22 08:28:42.803 13 SQLInstance1 Repo dbo.sqlwatch_meta_os_volume
2021-06-23 14:04:45.400 17 SQLInstance1 Source dbo.sqlwatch_meta_os_volume
2021-06-22 08:27:29.600 38 SQLInstance1 Repo dbo.sqlwatch_meta_procedure
2021-06-23 13:00:27.077 338 SQLInstance1 Source dbo.sqlwatch_meta_procedure

Just to confirm this isn’t happening with all instances importing to the repo and instances where this issue is occurring other meta tables are importing data so its not across the board on all tables not identifying new rows

Thanks!

10

Bit of a conundrum.
Can you run profiler on the remote, limited to app name = SqlWatchImport
and text containing dbo.sqlwatch_meta_database to start with.
I am interested in what sql query the app is generating to get the data from the remote.

The piece in the C# responsible for it:

						sql = $"select * from { tableName } with (readpast)";

						// Some tables may have both, the last seen and last updated fields.
						// The last seen field takes precedence over the last udpated.

						if (lastSeenInRepo != "" && HasLastSeen == true)
						{
							sql += $" where date_last_seen > '{ lastSeenInRepo }'";
						}
						else if (lastUpdatedInRepo != "" && HasLastUpdated == true)
						{
							sql += $" where date_updated > '{ lastUpdatedInRepo }'";
						}

so nothing really complex. I am thinking it’s somehow getting the dates wrong - perhaps date format issue?
What timezone and locale are you in?

11

Hi I’m in the UK, UTC+1
Yeah you’re right it is a date format issue, profiler gives the query:

select * from dbo.sqlwatch_meta_database with (readpast) where date_last_seen > ‘2021-06-22 08:27:22.740’
which returns the standard, when I run directly on the source in SSMS:
Msg 242, Level 16, State 3, Line 10
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

A change to:
select * from dbo.sqlwatch_meta_database with (readpast) where date_last_seen > ‘2021-22-06 08:27:22.740’
or
select * from dbo.sqlwatch_meta_database with (readpast) where date_last_seen > ‘22-jun-2021 08:27:22.740’
returns all rows

12

Have you tried running the first query that give out of range error with explicit date format:

set dateformat YMD
select * from dbo.sqlwatch_meta_database with (readpast) where date_last_seen > '2021-06-22 08:27:22.740'

It looks like your machines all use different time formats?

13

Yeah that works fine with the date format specified
I’ll take a look at the differences in time formats on these servers
Many thanks!

14

Ok that’s good to know. The app should handle it regardless your different date formats so it’s something I will have to fix in the next release.