"Execution Timeout expired" when using "Migrate to SQLite.." feature to export 10GB database.

[chukwuemeka]

The "Migrate to SQLite..." feature crashes with the exception:

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Steps to reproduce

Right-click on a fairly-large SQL Server database and select "Migrate to SQLite...". Walk through the dialog and select the file to export to. Wait. Status changes to: "Starting import" Status changes to: "Importing data" Crash window pops up. https://github.com/ErikEJ/SqlCeToolbox/issues/750

Further technical details

Version 4.7.632.0 - 1,240,185 downloads

SQL Server Compact 4.0 in GAC - Yes - 4.0.8482.1 SQL Server Compact 4.0 DbProvider - Yes

SQL Server Compact 4.0 DDEX provider - No SQL Server Compact 4.0 Simple DDEX provider - No

SQL Server Compact 3.5 in GAC - No SQL Server Compact 3.5 DbProvider - No

SQL Server Compact 3.5 DDEX provider - No

Sync Framework 2.1 SqlCe 3.5 provider - No

SQLite ADO.NET Provider included: 1.0.109.0 SQLite EF6 DbProvider in GAC - No

Database engine: SQL Server 2017 Developer Edition

Visual Studio or SSMS version: SSMS 17.3

[chukwuemeka]

Increasing SqlCommand's CommandTimeOut property got SqlClient to wait out the delay and the migration eventually completed successfully. I'm wondering if including CommandTimeout as part of the options in the Settings dialog is a good direction.

Please, advise.

[ErikEJ]

How did you increase the timeout?

This is actually a query to get the column metadata information from the server, and it should not time out. Which SQL Sever version are you using?

Could you try running this query against your database:
https://github.com/ErikEJ/SqlCeToolbox/blob/master/src/API/Repositories/ServerDBRepository.cs#L373

[ErikEJ]

I have just attempted a fix, included in the latest daily build. Please let me know if that solves your issue.

[chukwuemeka]

Thanks for the quick feedback.

The query is still running as I'm typing this response and it's already past the default 30seconds mark.

I increased the timeout by changing here: https://dotnetfiddle.net/mkQxj0

I had to add it to the dialog for the rest of the team to be able to get through the task at hand as per this commit: https://github.com/chukwuemeka/SqlCeToolbox/commit/00929a4d283daa594fa697cd551ad1213e657575 but I don't know if that approach is a good way to go.

Edit... The query eventually completed in 1m 20 seconds.

[ErikEJ]

Thanks, but I think this is a better approach: https://github.com/ErikEJ/SqlCeToolbox/commit/daff25d1787ed8aa1c909362048f2d7391c383a8 - please get latest daily build, and let me know.

[chukwuemeka]

Oh. I'll do that now. Thanks! I didn't see your previous comment.

[ErikEJ]

Did you try the latest daily build?

[chukwuemeka]

Sorry for the delayed feedback.

I haven't been able to complete test on the daily build yet due to an issue I have been scratching my head over. The extension keeps generating empty .db and 1KB .db-journal files for every test run with no errors. I suspect it's a problem with my workstation because I get the same result when I uninstall and reinstall the "Release" file that was generating good results a few hours ago.

However, I can verify that the optimization you made in this commit: https://github.com/ErikEJ/SqlCeToolbox/commit/daff25d1787ed8aa1c909362048f2d7391c383a8 has improved the query significantly. The query now completes in 2 seconds for the same database.

[chukwuemeka]

This is fine now.

I misinterpreted the status bar changing from "Importing data.... " to "Ready" to mean that the operation has completed and wasn't waiting long enough for the operation to complete.

I didn't account for the fact that my disk has been busier than normal with some other long-running tasks.

The call to dbRepository.ExecuteSqlFile(testFile); returns eventually with the status bar going from "Ready" to "Import Complete" with the .db file properly populated as expected.

Thanks for the quick resolution!

[chukwuemeka]

This is totally unrelated to the issue but I'm wondering whether using 400 instead of the count in this line: https://github.com/ErikEJ/SqlCeToolbox/blob/549a5d1d76df5f08ece8e1ac9bb84e4b5cf3fb5b/src/GUI/SqlCe35Toolbox/Commands/SqlServerDatabaseMenuCommandsHandler.cs#L249 is a deliberate hard limit or not.

Please, help clarify.

[ErikEJ]

To be honest, I have no idea where that number comes from.

[chukwuemeka]

😄

It says that you put the count a month+ after you wrote the loop and that was all 2 years ago. Also, it looks like your primary purpose for the count was to improve the progress notification.

Do you think it'd make sense to replace it with the count ?

[ErikEJ]

Fixed in this commit: https://github.com/ErikEJ/SqlCeToolbox/commit/da35d72654f5cbe586f3d6c57c1459088a6ea9f5 - let me know what you think?

[chukwuemeka]

I think it's good now. Thanks.

The status usually changes back to "Ready" whenever there's a long-running operation in between status updates, like between this: https://github.com/ErikEJ/SqlCeToolbox/blob/da35d72654f5cbe586f3d6c57c1459088a6ea9f5/src/GUI/SqlCe35Toolbox/Commands/SqlServerDatabaseMenuCommandsHandler.cs#L233 and this: https://github.com/ErikEJ/SqlCeToolbox/blob/da35d72654f5cbe586f3d6c57c1459088a6ea9f5/src/GUI/SqlCe35Toolbox/Commands/SqlServerDatabaseMenuCommandsHandler.cs#L236 and in between successive iterations of this: https://github.com/ErikEJ/SqlCeToolbox/blob/da35d72654f5cbe586f3d6c57c1459088a6ea9f5/src/GUI/SqlCe35Toolbox/Commands/SqlServerDatabaseMenuCommandsHandler.cs#L255 Is that normal?

Secondly, I noticed that you changed the temp file extension from .sqlce to .sqltb and I've been scratching my head trying to understand why. Please, help with some explanation.

[ErikEJ]

There are two sets of files, one set for going from embedded dB to SQL Server, and one for the other way.

[chukwuemeka]

Oh. That makes sense now. I've learnt a lot from you in the past few hours. Thanks again. :)