1

Topic: SQL Server 2019

We've just upgraded to SQL Server 2019 and have noticed some odd issues with the import wizard (eg sample method appearing in the species selection screen) and connection drops.

Nothing reproducible (across different imports), but thought I'd mention it in case anyone has any issues - if I manage to work out what's going I'll update!

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

2

Re: SQL Server 2019

Another weird error - I initially thought that the importer was dropping rows, but it seems that the Data screen isn't always sorting the table by the row number (this sounds vaguely familiar to a previous bug where large datasets weren't being sorted on output?). The attached shows an import of around 30,000 rows.

Post's attachments

Screenshot 2021-12-23 163602.jpg 23.14 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.
Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

3

Re: SQL Server 2019

Thank you for sharing your experiences with R6 and SQL Server 2019, Charlie.
At this time, R6 has not been thoroughly tested with SQL2019 - it is something that the Recorder 6 Steering Group has given consideration to, with Mike Weideli, but our main focus to date has been on progressing the feasibility study for the 'successor / parallel system'. I will double-check where we're at with this and update users via the R6 e-news.

Clare Blencowe
Sussex Biodiversity Record Centre | Manager
Recorder 6 Steering Group | Chair

4

Re: SQL Server 2019

See attached two screenshots for a recurring issue with the import wizard - on the species matching screen I am presented with bogus data (initial import.png), where a list of species is expected. After closing Recorder and restarting it, it seems to be fine and I get what I expect (after restart.png).

It seems to be only occurring after running a number of subsequent imports so it makes me wonder if there's some cache/temp data hanging around somewhere.

Post's attachments

Desktop.zip 404.42 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.
Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

5

Re: SQL Server 2019

The attached shows another example - data from the taxon occurrence comment column is being put into the species matching screen. Looking at the #Species table, this looks genuine - ie the field "Species_Name" has values of "SM4 recording in mid spring" and "SM4 recording in mid summer". However in the #Master table, these values are in the field "SYSTEM0100000006" which I believe is correct (it's the Taxon Occurrence Comment field not the Species field?).

Going backwards in the import wizard, setting the comment column to ignore and then proceeding works as expected. Going backwards a second time and setting the comment column back to taxon occurrence comment and proceeding also works fine.

I'm stabbing at the dark, looking behind the scenes with the import wizard so if anyone who knows better wants me to look at anything specific, let me know!

Post's attachments

Desktop.zip 321.28 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.
Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

6

Re: SQL Server 2019

clareblencowe wrote:

but our main focus to date has been on progressing the feasibility study for the 'successor / parallel system'

No problem, just putting this here in case its useful to anyone else :-)

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

7 (edited by charliebarnes 05-01-2022 16:31:30)

Re: SQL Server 2019

Another different one - grid ref appear in Location matching screen. Again, going backwards setting both the location and grid reference column to ignore, going forward in the wizard, then going back and setting the columns again and it then works as expected.

A thought - it may not be a problem with SQL server, it may be a problem with Recorder itself? Something going awry with the import wizard after multiple imports. We're on 6.30.0.291 and have been for a while, but I haven't noticed this problem before we upgraded the SQL server.

Post's attachments

Screenshot 2022-01-05 162711.jpg 233.37 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.
Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

8 (edited by charliebarnes 06-01-2022 10:48:07)

Re: SQL Server 2019

Interestingly today I've got both bogus data and expected data in the same matching screen (this time names).

(the obscured name is the single expected name)

Post's attachments

Screenshot 2022-01-06 104643.jpg 74.59 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.
Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

9 (edited by charliebarnes 10-01-2022 11:39:00)

Re: SQL Server 2019

I'm also regularly (once a day?) getting the error "Cannot continue the execution because the session is in the kill state." with the import wizard.

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

10

Re: SQL Server 2019

charliebarnes wrote:

I'm also regularly (once a day?) getting the error "Cannot continue the execution because the session is in the kill state." with the import wizard.

I'm also getting this error with the report wizard, but only when I ask it to sort on a column.

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

11 (edited by charliebarnes 11-01-2022 10:05:38)

Re: SQL Server 2019

Another new error for me with the import wizard (the screen prior to hitting "import"):

Warning: Fatal error 682 occurred at Jan 11 2022 9:51AM. Note the error and time, and contact your system administrator.

Closing and trying again with the same file works fine.

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

12

Re: SQL Server 2019

I seem to be having trouble importing Review information (columns Reviewer, Reviewer Role, Review Type, Review Preferred). Some records are having the review imported correctly, but others aren't. It appears random at the moment (all review data is the same).

In other news, I'm trying to get our IT to update the SQL server update - we're currently on the base install and there have been a lot of updates since, including some specifically to fix bugs in handling of temporary tables which the importer uses extensively. Fingers crossed this fixes all the problems!

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

13

Re: SQL Server 2019

The import wizard has just created me an import file with empty TAXON_DETERMINATION TAXON_LIST_ITEM_KEYs; this obviously then failed to import.

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

14

Re: SQL Server 2019

Hi

Testing of SQL Server 2019 has not moved as fast as I would have hoped due to my health issues. As far as I have managed to get suggests that there is a problem in sorting.  It looks like some feature which R6 uses have been deprecated in SQL 2019, although there is nothing in the documentation which suggests this.  For the moment I can only recommend that SQL Server 2019 is not used.  The work you have done is useful in that it have identified some areas which will be easier to investigate than those which I  originally found. I don't think it is an R6 issue, because I have a large test data file which will import in SQL 2017, but not in 2019. For a start the records do not display in the correct order.

Mike Weideli

15

Re: SQL Server 2019

We still haven't been upgraded to the latest CU version (stuck on GDR, I'm hoping we can get updated soon..). What version are you on Mike? We're running:

Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) - 15.0.2080.9 (X64)
    Nov  6 2020 16:50:01
    Copyright (C) 2019 Microsoft Corporation
    Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

Certainly not an R6 issue - never had any of these problems before we upgraded!

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

16

Re: SQL Server 2019

I'd bet a pint that all these errors are related to tempdb, caching, and on-the-fly alteration of tables. I've had a test server running the latest server running for a short while and haven't encountered any of the same errors. Still haven't had our production environment updated...

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

17

Re: SQL Server 2019

We've finally had our production server upgraded to the latest cumulative update (CU15) and so far, touch wood, we haven't had any errors yet. The issues with the report wizard seem to be solved, but I haven't done any large imports; that's next to test on my list.

Our version string is now:

Microsoft SQL Server 2019 (RTM-CU15) (KB5008996) - 15.0.4198.2 (X64)
    Jan 12 2022 22:30:08
    Copyright (C) 2019 Microsoft Corporation
    Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

18 (edited by charliebarnes 12-04-2022 14:55:54)

Re: SQL Server 2019

charliebarnes wrote:

Another weird error - I initially thought that the importer was dropping rows, but it seems that the Data screen isn't always sorting the table by the row number (this sounds vaguely familiar to a previous bug where large datasets weren't being sorted on output?). The attached shows an import of around 30,000 rows.

This one still seems to be occurring after updating SQL Server.

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

19

Re: SQL Server 2019

We moved to SQL Server Express 2019 (15.0.2080.9) this year and I've not imported data into it yet. Most of our data is going into Postgres.
I have only noticed one oddity but don't know if it is version related. I have a site with an LWS designation that on reporting from SSMS does not show up. I've tried several things to see why but to no avail.

Data Manger
Somerset Environmental Records Centre

20

Re: SQL Server 2019

Hi All,
I wonder if you can help with any updates to this please?  We're due to upgrade our server to Windows Server 2019, is it best to use SQL Server 2019 or would you recommend SQL Server 2017?  I wasn't sure whether there were still issues with 2019.
Any help greatly appreciated, thank you!

Jackie Ullyett
Data Manager
Beds & Luton BRMC

21

Re: SQL Server 2019

We've been on 2019 since December 2021 and the only ongoing issue is the out-of-order sorting in the import wizard. This just seems to be a display issue and doesn't seem to affect imported data in anyway - indeed, clicking on the column header and it sorts it fine, "like how it used to be":

Another weird error - I initially thought that the importer was dropping rows, but it seems that the Data screen isn't always sorting the table by the row number (this sounds vaguely familiar to a previous bug where large datasets weren't being sorted on output?). The attached shows an import of around 30,000 rows.

The important thing is to make sure it's updated - it took a while me to convince out IT support that there were 2 years of updates to apply...

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

22

Re: SQL Server 2019

Many thanks Charlie, sounds like SQL Server 2019 should be OK to go in that case!

Jackie Ullyett
Data Manager
Beds & Luton BRMC