I'm running a script (in SSMS) that inserts data from one database to another database into a table with the same structure. So I use 3 part table name [DB].[schema].[table] for tables of both databases. In Management Studio I set DB context to the database where data gets inserted TO -> [ProjectFileMulti].
There is an identity column so first statement is:
SET IDENTITY_INSERT [dbo].[FileStorage] ON
My attempts to insert data were failing with the following error:
The statement has been terminated.
Msg 544, Level 16, State 1, Line 4
Cannot insert explicit value for identity column in table 'FileStorage' when IDENTITY_INSERT is set to OFF.
After I got the error I noticed that DB context in SSMS switched to the database where data was selected FROM -> [ProjectFile].
(I'll attach the query as an attachment)
The trace file is also attached. The DB context gets switched several times and at the end it switched to the wrong database [ProjectFile].
There are some other errors in the trace file like: "FILESTREAM file named with GUID 'c76fef6b-9ab3-4f10-bb57-ff40788e4ec2' that belongs to FILESTREAM data file ID 0x10001 does not exist or cannot be opened." -- which might be a real reason why the statement fails but this message is not displayed in Management Studio(see image attached). It only displayed when I set identity_insert ON the table using 3-part name (SET IDENTITY_INSERT [ProjectFileMulti].[dbo].[FileStorage] ON)
Please let me know if you need more details.
I did run the same query from SSMS of SQL Server 2008 SP2. There are no DB context switches in the trace file (also attached). Also the error "file does not exists or cannot be opened" displayed in Management Studio.