Appending SQL Tables from MS Access SET IDENTITY_INSERT takes a long time why?

Just for kicks and giggles, explain in detail why you are attempting a SET IDENTITY_INSERT ON statement, as this is not always best practice.

Basically I am refreshing a SQL relational database from MS Access.

The Constraints have be set to NO CHECK while I load so I can load the PK fields in the tables.

I run the SET IDENTITY_INSERT ON to allow this.

Once I do load the tables, I then set the SET IDENTITY_INSERT OFF; on the table.

I repeat this process for each table.

I have about 30 tables.

Just noting that  SET IDENTITY_INSERT ON / OFF is 'per session' and can only be in effect for on Table per session.

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-2017

The simplest answer is that it was intentionally left to work that way. Microsoft wants us to license SQL Server... so they didn't put much effort into optimizing getting data out of Access and into SQL Server from the MS Access perspective/direction.

Your best bet will be to go the other direction; use SSIS to load the data with the Access DB as your source.

The alternative would be to write a simple script to run a bulk insert on each table. Please note that this option will give you the most flexibility.

Good Luck!

I have more information to now share.

I have found that under Options -->  Client Settings --> Advanced Section

Default
     Checked Open Database by Using record-level locking  (I unchecked)

    ODBC Refresh Interval (SEC) was default to 1499   (I changed to 10)

    Refresh Interval (sec) 60 Default   (Changed to 10).

Now this didn't exactly resolve the issue.  

But now I can run SSMS SET IDENTITY_INSERT TableName ON;
Open access run query
close access
run SSMS SET IDENTITY_INSERT TableName OFF;

Repeat and it works every time.

HOWERVER:  I tried to wait and it doesn't seem to work without closing access and opening it back up.

I did the open close before changing the Advanced Items, but no luck.  So the advanced changes did have some affect.

"close access" ?? Aren't you running this process from a code module in Access?

I posted code that works.  It is not air code.

Please post your entire procedure.

Sorry,

I didn't write any code as this was supposed to be a run one or two times and be done and in SQL.

I will utilize your code and give it a try.  I do however think the session thing vs MS Access having it's own session may have something to do with this since they don't share the same session.  Maybe after sometime X is when Access realizes the Idnetiy is on and allows the insert.  Who knows.

You included Access in the topics list.  Since the data is SQL Server, the only reason to include Access is because you want to run the code there.

We can't help if you don't post your code.  All we are doing is guessing.

"I will utilize your code and give it a try.  I do however think the session thing vs MS Access having it's own session may have something to do with this since they don't share the same session"
It does ... I've already been down this road.  I will post how I did this later tonight.

Thanks to all that participated as this information surely can be used by future exchange users.

Only you get to assign points.  You don't delegate the task.