SQL Server Home
SSMS of SQL Denali switches database context when it should not
12/21/2010 10:01:36 PM
User(s) can reproduce this bug
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.
SQL Server Denali CTP 1
Tools (SSMS, Agent, Profiler, Migration, etc.)
Windows Server 2008
Operating System Language
Steps to Reproduce
I assume the Denali trace file is self explaining.
The DD context switches to the wrong database.
It is expected that database context stays the same even if script failed with errors. I assume the multiple switches between database contexts are happening due to permissions validation (related to containment?:) ).
to post a comment.
Please enter a comment.
on 2/26/2012 at 11:19 PM
This was not fixed in RC0 and is preventing us from continuing our testing of 2012 as many of us are on a slow WAN where this is causing serious lag and loss of productivity.
Why is this closed if it is not in fact fixed?
on 4/8/2011 at 6:24 AM
Greetings from the SQL Server Manageability team.
Thanks for writing in to Microsoft. We greatly value your feedback.
We were able to reproduce this issue, and we have fixed this issue. This will reflect in an upcoming release of SQL Server.
Thanks again for providing feedback and making SQL Server the greatest Database server.
on 2/7/2011 at 11:07 PM
I attached 3 new files:
1. CreateDatabases.sql -- the script to creates test databases and populates one of them ([Files1]) with data. Make sure that after you run this script you manually delete one of the files from filestream container for [Files1] db. I decided to attach a script rather than DB backups as it is much smaller in size :)
2. Trace template (not sure if you need one but just in case if you do it is attached)
3. Actual REPRO.sql script that reproduced the issue
Please let me know if you neen more details.
on 2/7/2011 at 10:46 PM
To reproduce issue with missing error message... First of all the issue consists of the following items:
1. SSMS executes SQL batch(script) 2 times in context of 2 databases in sequence. On one database first, on another database second. Another database depend on something I don't yet get but it can be a user database or master database (may depend on in which database context the last successful query was executed... as a guess). It might happen that the query will be executed 2 times in context of the same database (the one we actually want it to be executed) and as a result showing the correct error. But it is executed TWICE.
2. SSMS is showing the error of only the second execution. So if the second execution happened to be for example [master] database then the only error shown by SSMS will be "Table does not exists or you do not have permissions".
The REPRO.sql script to be attached. I would recommend you to connect to a context of [Files1] database when you open the script then manually change DB context to [Files2] in SSMS UI. And run it.
Also time to time I notice that after I run this script SSMS does not show SPID correctly. It is not what SELECT @SPID returns. However I was able to find this bug is already in connect :)
on 2/7/2011 at 10:26 PM
I did investigate a little bit further and found out that SSMS does database context switches in many occasions. I’m happy to provide 2 database backups (see them attached).
1. You'll need Profiler open to follow what will be happening. Please find trace template attached (ContextSwitchTemplate.tdf).
2. Restore 2 databases (Files1 and Files2) from attached backups. The databases both are the same structure but only Files1 have some data in it.
3. Open SSMS and connect to a SQL Denali instance, in Object Explorer navigate to Databases and highlight database Files1. Click “New Query” (in top left corner)
4. Take a look to profiler. You’ll see a lot of “USE [Files1]” statements. If you look more precise you will notice that the same set of SQL statements is repeated twice starting with the following statement:
DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition = N'SQL Azure' then 2 else 1 end as 'DatabaseEngineType'
And ending with the following statement
if (db_id() = 1)
-- contained auth is 0 when connected to master
-- need dynamic sql so that we compile this query only when we know resource db is available
exec('select case when authenticating_database_id = 1 then 0 else 1 end from
sys.dm_exec_sessions where session_id = @@SPID')
end;use [Files1]; --resetting the context
If you monitor Profiler you’ll see that every time you activate your Query Window the same number of SQL Statements appears in Profiler. Not sure if it is as per design. The fact all the statements repeated twice makes me personally think – oops, something is wrong here. But it’s for you guys to confirm.
on 2/7/2011 at 7:29 PM
Sorry I'm not sure what exactly happened but I didn't mean to post previous message as it was not finished... I'll try again. Please ignore the previous one.
on 2/7/2011 at 7:28 PM
I did investigate a little bit further and found out that SSMS does database context switches in many occasions.
1. You'll need Profiler open with the following events:
on 2/7/2011 at 4:39 AM
We have found another repro steps using which similar problem of wrong database context switching could occur. Hence I have re-activated this bug. However, it will be great if you could share your scenario as well, for the purpose of verification.
on 2/2/2011 at 11:24 PM
As I have explained earlier, I am not able to repro this issue by running scripts producing similar error - Error: 5552. Given this, I am going to resolve the issue as "no repro" for now. If you come across this scenario again, please reactivate the bug with more details on repro steps. If you have any other comments or feedback about this, please feel free to either reactivate this bug, or send me an e-mail at firstname.lastname@example.org
on 2/1/2011 at 8:03 AM
We are investigating this issue. To repro this scenario, we executed some queries to repro the error: 5552 - "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."
However we could not repro the database context switching scenario with this error only. We were able to see this error in the messages window. If possible, can you share a T-SQL script (with the required database schema) which I can execute to repro this issue?
on 1/5/2011 at 9:25 AM
Thank you for reporting this issue. Our Dev team is looking into this issue and will report back on their findings and resolution.
on 12/22/2010 at 2:04 PM
I have noticed that when executed in "SSMS Denali" the script runs 2 times (first time in ProjectFileMulti db context, second time in ProjectFile db context).
When executed in "SSMS 2008 SP2" the script runs only 1 time in ProjectFileMulti db context.
Please find attached 2 trace files (in a single zip archive) with DatabaseName and DatabaseID fields added to the traces.
on 12/21/2010 at 10:06 PM
I attached 4 attachments (all reported attached successfully) but can see only 2. I'll monitor them if the rest of 2 do not appear I'll attach them again.
to post a workaround.
Please enter a workaround.
© 2013 Microsoft