Re: Replace SSMS 2005 with SSMS 2008

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Indeed the logical error you found can be a hell of a problem for most of us. As we all know that a default SQL Server 2005 Express Edition database will be in AUTO_CLOSE property set True it's going to be a bad really bad trouble. Hopefully they'll release a fix for these bugs.

I tried to reproduce the error you get and I got the following one:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The server principal "test" is not able to access the database "AW_TranRepl_Test" under the current security context. (Microsoft SQL Server, Error: 916)

------------------------------

When setting AUTO_CLOSE property to false, no problem.


You know, in my case, it seems SSMS 2008 is tring to reach to every database to list and get some info about them when clicking Databases even if the ones that my Login is not mapped. So I get this invalid user error when I expand the Databases node. I'm not sure what SSMS 2008 is trying to do behind the scenes. I must run the SQL Profiler against a SQL Server 2000 instance in a test environment and see what happens.

Also, yesterday I was testing Resource Governor and I somehow cause SSMS to crash but I could not reproduce it. It seems we'll have some problems with this feature too. My test was generally about changing a workgroup's resource pool while a connexion was open and moving the workgroup of this connection to another resource pool while Resource Governor disabled. I'll work on it later again to push and cause it to crash.

--
Ekrem Önsoy



"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message news:Xns9B039A63691A0Yazorman@xxxxxxxxxxxx
Ekrem Önsoy (ekrem@xxxxxxxxxxxx) writes:
I first wanted to share with you guys and confirm it.

The SQL Server Instance on the hosting company is a 8.0.760. I tried to
connect to another database on another web hosting company's SQL Server
and it connects and expands the Databases node successfully and in this
case, the SQL Server Instance's version is 9.0.3042.

I believe there is some kinda problem with 8.0.x version. I'll report this
issue now.

But not that my findings where the opposite: I got the error on SQL 2005,
not on SQL 2000. Anyway, I tracked it down to the dreaded AUTO_CLOSE
option. If the database is in AUTO_CLOSE, SSMS runs a query in that
database to get collation and some more information.

This is really bad: what if the server has many databases in auto-close,
a couple of thousand? Guess how long time it will take expand the Databases
node in Object Explorer.

And the fact that the exception is not handled properly, adds insult to
injury.

Turns out that this issue was reported just a few days ago,
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=362646
so I did not have to submit a bug myself.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


.

Relevant Pages

  • Re: SQL Server Management Studio Cannot Open SQL Express Files
    ... Currently I can work with the DB in SSMS but not in VS. ... I suggest you to open SQL Server Configuration Manager and see how many SQL ... If I create the database in VS2005 using the ... If I create the DB in Management Studio I cannot work with it in VS2005. ...
    (microsoft.public.sqlserver.tools)
  • Re: MS Access DAO -> ADO.NET Migration
    ... William Vaughn ... Microsoft MVP ... Hitchhiker's Guide to Visual Studio and SQL Server ... My migration app works building a SSCE database file with imported data ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Cluster will not fail over.
    ... > As far as the TCP/IP issue goes, you had to rebuild the cluster and were ... > able to restore the master database. ... > a cluster installation you'll have to revisit. ... >> This worked bringing up the sql server in minimal mode. ...
    (microsoft.public.sqlserver.clustering)
  • Re: MS Access DAO -> ADO.NET Migration
    ... full SQL Server and I see the logic you explained in a multi user ... allow two users to access the same database file Read/Write at any given ... The book was a pleasure to read after the gibberish that Microsoft 'puts ... Hitchhiker's Guide to Visual Studio and SQL Server ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: MS Access DAO -> ADO.NET Migration
    ... For that it is much harder to handle the incremental identifier, ... database but although they have the data, they are not connected at the same ... The book was a pleasure to read after the gibberish that Microsoft 'puts ... SQL Server Management Studio is nowhere to be found on my ...
    (microsoft.public.dotnet.framework.adonet)