Hot answers tagged

25 votes
Accepted

Can't see second azure sql database in SSMS object explorer

So for me, it turned out that when I was connecting using SSMS, I had set the database to connect to as my first database by accident - meaning that was all I could see. On connect, go to Options, ...
user avatar
  • 366
9 votes
Accepted

Cannot connect to SQL Server Integration Services from SQL Server Management Studio: "Class Not Registered"

Did you try downloading and using SSMS 2014 to see if you can connect? This problem used to come up when using different versions of SSIS and SSMS.
user avatar
  • 981
9 votes

Can't run SSMS as another user - requires elevated permissions

In case it helps anyone, I got it working by: Open the location to the shortcut, such as: C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft SQL Server Tools 17 Right-click > Properties > ...
user avatar
7 votes

Sql Server Management Studio 2008 has suddenly become unresponsive

The 'Connect to Server' window is off-screen. If you use Alt+F4, it will close the dialogue but keep Management Studio open. After this you can initiate a new dialogue through the menu and connect ...
user avatar
  • 71
6 votes
Accepted

Back up a database nightly

Back up a database nightly I have a development database that I would like to refresh with the data from the Production database. I would like for this to happen every evening if possible. ...
user avatar
5 votes

SSMS: Where does SQL Server store its server names?

When upgrading from one version of SQL Server Management Studio to another, e.g. 2014 to 2016, you can copy the SqlStudio.bin file from one directory to another and all of your connections will ...
user avatar
3 votes

Sql Management Studio 2014 wont connect to clustered instance

Windows Firewall is blocking sql server 2005 clustered instance connection. It needs UDP 1434 to be open. The down voted reply put me on the right path. Mariano C. MS Support
user avatar
3 votes
Accepted

Shut down operating system by killing task

smss.exe is an essential system process in Windows (it is the Windows session manager), and it is not related in any way to SQL studio. If you kill it (and same goes for other system processes, like ...
user avatar
  • 1,563
3 votes

SQL Server Debugging: "Unable to Step. Invalid operation"

I had the same problem. After some research I found this page which suggests that this is caused by a race condition. Although the workaround given there wasn't applicable for me, after stopping every ...
user avatar
  • 131
3 votes
Accepted

Some rows giving "String or binary data would be truncated" error when editing in Management Studio

I've experienced this on occasions when a table had a mixture of ntext and nvarchar(MAX) datatypes. Changing all the ntexts to nvarchar(MAX)s fixes the problem.
user avatar
3 votes

Timeout saving table in SQL Server

This answer came up for me in Google even though I was adding an index, not changing a column. There is a better way to do long running changes: Make the changes you want to the design of the table. ...
user avatar
  • 141
3 votes

Can't run SSMS as another user - requires elevated permissions

Seems to be a known (unresolved) bug: Archived - SSMS 2016 fails on Windows 10 Version 1607 while trying to execute it as a different domain user SSMS 2014 still works fine so that's a workaround, or ...
user avatar
  • 241
3 votes

SQL Server Management Studio - "Restore from Device" dialog is slow

@Randolph West suggested this and it works for me. Basically, when you want to restore database, don't click on the database itself. Instead, click on the folder 'Databases' and then restore.
user avatar
  • 130
3 votes

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' on Azure SQL with Active Directory Admin via SSMS

In my case, the reason seems to have been that the user is associated with multiple Azure ADs. I had to specify the Azure AD of the Azure SQL Server using the "Options >>" button:
user avatar
3 votes
Accepted

Why can't I restore encrypted backup through front end?

I'd suggest you try tearing down your setup and starting over. You should be able to restore from the SSMS GUI, so long as your certificates are all in place. Without having your setup scripts, it's ...
user avatar
  • 237
2 votes

Add conditions to maintenance plan sequence on SQL Server

I have a solution to this however it is not elegant. This works, but it is a cludge and it would never be considered a "best practice". The solution involves using artificially generated errors to ...
user avatar
  • 21
2 votes

How can I have SQL Server Management Studio 2008 connect to my instances automatically?

If you use the SSMSBoost plugin, then you can set any of your preferred connections to "Connect object explorer at startup". This option can be found on the SSMSBoost->Settings->Preferred Connections-...
user avatar
  • 121
2 votes

SSMS: Where does SQL Server store its server names?

To remove all entries for new SQL 2014, you need to delete file: C:\Users\(USER)\AppData\Roaming\Microsoft\SQL Server Management Studio\12.0\SqlStudio.bin
user avatar
  • 21
2 votes

Option do not show schema names in SQL Server Management Studio

Workaround: Tree/List View IntelliSense behavior is available in the "Object Explorer Details" window. To Start: Click on Object Explorer "Tables" folder. Click F7 and this will bring up the "...
user avatar
2 votes
Accepted

How do I permanently add additional connection parameters in SQL Server Management Studio

currently this appears to be a bug in SSMS (present v 2014). I develop SSMSBoost add-in for SSMS and starting from recent release we have fixed that problem. You can use our "Preferred connections" ...
user avatar
2 votes
Accepted

SQL SSMS Window Dissappear

Have you tried right clicking on the window in Task Manager on the application tab and choosing maximize? That usually works for me.
user avatar
  • 1,280
2 votes

SQL Server Management Studio - "Restore from Device" dialog is slow

Mine went from 2.5 mins to open, to 1-2 seconds. see https://blog.sqlauthority.com/2018/05/07/sql-server-restore-database-wizard-in-ssms-is-very-slow-to-open/ MSDB.dbo.backupset keeps a log of ...
user avatar
2 votes

Access Denied issue, SQL Server LocalDB on Windows Server 2008 R2 SP1

It's hard to say for certain, but I would check the following things: SQL Express is limited to databases 10 GB or smaller. Has your database grown to a size larger than that? But based on the ...
user avatar
2 votes
Accepted

Why do I get different memory numbers using TSQL versus the GUI?

Your query is returning memory used by SQL, not the max server memory it's allowed to consume. How do you get the minimum and maximum memory allocation of a SQL instance using T-SQL? This will show ...
user avatar
  • 403
2 votes

SQL SSMS on development - clear ldf files

You can truncate the log file by issuing the following code block from SSMS: -- Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE AdventureWorks2012 SET RECOVERY ...
user avatar
2 votes

Local Computer drives are not visible when creating backup through wizard

This behaviour is expected. SQL Server runs on the remote server, in a service account so it cannot not see your own volumes. Why would SQL Server have any special rights to see your local computer?. ...
user avatar
  • 50.7k
1 vote

SQL Server Managment Studio 2016 fails to open; multiple errors

Sounds like you may be running the release candidate or BETA version of the management tools. Try downloading the newest version from here: https://msdn.microsoft.com/en-us/library/mt238290.aspx or ...
user avatar
  • 1,846
1 vote

SQL Server 2012 constant CPU load

You would require at least either the view server state or view database state before you could think of analysing any performance issues inside SQL Server: What can user do with VIEW SERVER STATE ...
user avatar
  • 1,925
1 vote

Can't see second azure sql database in SSMS object explorer

SSMS does not automatically update the database list when new databases are added. You need to refresh the view manually. In Object Explorer, highlight the Databases node. Hit F5 to refresh. The list ...
user avatar

Only top scored, non community-wiki answers of a minimum length are eligible