SQL Server Management Studio, a graphical front-end tool shipped with SQL Server for managing and querying databases. It also supports managing other bundled systems such as SSAS.

learn more… | top users | synonyms (2)

6
votes
4answers
9k views

How to find unused indexes?

I am working on a Data warehouse. I have tables with up to 200M records. Some of these tables have around 20+ indexes (I can't provide a reason why they have been created in the first place). This is ...
5
votes
3answers
121 views

quickly jump to an object in Object Explorer in SSMS

When expanding a tree with a large number of objects such as the Tables tree in SSMS Object Explorer, is there a method of quickly navigating to the table I am looking for, if I know the exact name or ...
2
votes
0answers
32 views

SQL Server - Debugging - How to show parametervalues as HEX

I am writing a script which sets Parameters in the Registry for SQL Server. The following works on some of my servers but not all. DECLARE @Parameters AS VARCHAR(MAX) SET @Parameters = '-T1118' ...
2
votes
2answers
153 views

Where can I find a compatible version of SSMS for SQL Server 2016 CTP?

Microsoft seem to have many versions of SQL Server 2016 CTP available at a time to download, at only 2.5GB a piece, but only one version of SSMS 2016 CTP, and if your db engine is the wrong version ...
1
vote
1answer
1k views

SQL Server Configuration Manager shows no services

I have installed Microsoft SQL Server Management Studio 2008 on my system. When I tried to open it for the first time, it asked me for the "Server Name" when I surfed I came to know that I have to ...
0
votes
0answers
75 views

SSMS as an End User Tool

Our business has decided to distribute SSMS as an end user tool (Don't laugh). How I can I stop the creation of # temp tables. Our record is a 896gb table!
10
votes
1answer
943 views

Why is “Lookup” colored as a function reserved word in SQL Server?

In SSMS 2208, the identifier "Lookup" is colored hot pink as if it were a function (same color as, say, "Power" or "Convert"). Why? I cannot find it in the official list of reserved words. Searches ...
4
votes
2answers
2k views

How to assign CTRL+F1 to sp_whoisactive in ssms 2012?

When I go to Tools > Options > Environment > Keyboard and Type in Query.Sp_whoisactive Press shortcut keys: Ctrl+F1 Click Assign. When I press Ctrl+F1, It still calls Help.
7
votes
3answers
5k views

How to connect once for all queries in SQL Server Management Studio?

SSMS asks me to connect for every query in my project. Is there a way to connect only once per connection for all of the queries using it in my project? The answers below (March 2014) don't answer ...
10
votes
2answers
16k views

Suppress SQL Server Management Studio auto complete

Is there a way to temporarily suppress SQL Server Management Studio's auto-complete while typing a query? I don't want to disable auto-complete completely just say hold down some key while typing in a ...
2
votes
1answer
114 views

Question about new behavior of current connection combobox in SQL Server 2012

I have a question about the new behavior of the database selection combobox in SQL Server 2012 (Press Ctrl + U to move your focus to it). It seems that, unlike in SQL Server 2008 R2, clicking in the ...
2
votes
2answers
352 views

SQL Server logins and users — assign multiple logins to a single user? [duplicate]

In SSMS, since I can't assign multiple login objects to a single user object, what is a best practice to achieve an equivalent security model? For example, I have 20 logins and want to give them all ...
1
vote
1answer
657 views

Ctrl-F1 shortcut stopped working in SSMS 2014

I've had the Ctrl-F1 query shortcut bound to sp_whoisactive for years (this technique is described in How to assign CTRL+F1 to sp_whoisactive in ssms 2012?). A few days ago, it stopped working. If I ...
1
vote
2answers
586 views

Format column length in output - SSMS

SQL Server 2012. Sample query at bottom. I'm trying to create a simple report for when a given database was last backed up. When executing the query at bottom with output to text in SSMS, the ...
2
votes
2answers
1k views

Specify Values for Template Parameters — command

When I create a stored procedure in SQL Server 2008 by using the template, the comments specify a hot-key combination that no longer works: -- Use the Specify Values for Template Parameters -- ...
0
votes
1answer
36 views

Finding keywords in SQL script

I have a list of 50 columns in a database that are likely to change or be removed within the next few months. I have a task to identify any SQL scripts that reference these tables. Up until now, I ...
3
votes
1answer
982 views

MSSQL Mixed-Mode Users cant connect unless they are in the server sysadmin role

SQL Server 10.0.5520 (2008 std sp3) My goal is to enable data connections in SSMS or Visual Studio for windows users w/out them being sysadmins. Ideally this would be done through Active Directory ...
0
votes
2answers
290 views

Disable SQL Server 2012 Management Studio remote access

I am trying to disable SQL Server 2012 Management Studio remote access from other servers. I have unchecked SQL Server Properties -> Connections -> Allow remote connections to this server. but still ...
0
votes
1answer
31 views

SQL Server - Check for opened Activity Monitor

I'm looking for a way to send an alert when a DBA tool is connected for "too long" on a SQL Server Production database. For most of the tools I listed out, I can find the program name with the ...
7
votes
1answer
5k views

How to move folder of SQL Server LocalDB instances from the default location?

I get multiple errors with LocalDB (startup error, can't create DB from SQL Server Management Studio and also multiple issues when trying to restore a backup) and none of the fixes I found are helping ...
10
votes
2answers
7k views

SQL Server Management studio slow connection or timeout when using Windows Authentication

I'm getting extremely long delays (10~30 seconds) in SQL Server Management Studio 2014 when attempting to connect to a SQL Server 2012 instance over TCP using Windows Authentication. This happens when ...
2
votes
1answer
102 views

Two Databases on same server handle date differently

I have two databases on the same server, one is a backup of the other from a few weeks ago. I have a third party application that has started misbehaving. It logs the LastLoginDate of a particular ...
-10
votes
1answer
80 views

Create table in SSMS [closed]

What's the error in this code? create table employee6(FullName varchar(50), DateOfBirth int,MobileNO int, HigherQualification varchar(50), ApplyingFor varchar(50),Regno int); insert into employee6 ...
7
votes
1answer
540 views

Prevent a stored procedure from being executed outside SSMS

I'm currently working on a stored procedure which modifies metadata for an application. This stored procedure can only be run when the application is offline. I didn't want developers to accidentally ...
0
votes
0answers
228 views

SSMS 2014 installation - Hidden errors in log

I have slipstream installed SSMS 2014 with SP1 + CU2 on my PC (Windows 7 Ent. SP1 with .NET fw 3.5.1 and .NET fw 4) It has a VisualStudio 2010 too. It has a SSMS 2008 R2 installed too. (I know its ...
2
votes
2answers
150 views

Create index with a commentary

I would like to add some comments to my indices. So I added some additional information to the index creation script, expecting to see this comment again as soon as I open the script again. But it was ...
0
votes
0answers
25 views

SSMS: set width for 'New Query' window (using MDI)

Is there a way to prevent SSMS from opening an enormously wide window when I'm using 2 monitors and have SSMS spanning both monitors? I only want the window as wide as a single monitor. Still using ...
0
votes
1answer
27 views

SSMS 2012 CSV exports

Is there a way to prevent SSMS 2012 from adding the word 'NULL' to CSV exports? Steps to reproduce behavior: Create a new query (Ctrl+N) Select Query | Results To | Results to Grid (Ctrl+D) Run ...
5
votes
1answer
373 views

Incorrect syntax near 'role' with generated script

I've recently had to recreate a user several times. To facilitate the creation of the user, I would right-click the user and choose script login as, create to, New Query Editor Window. However, after ...
2
votes
2answers
191 views

ssms - how to format T-SQL scripts?

as part of the process to add articles to my publication I use the stored procedure sp_addarticle: use [Returns] exec sp_addarticle @publication = N'USReturns', @article = N'...
1
vote
1answer
115 views

Task generated T-SQL shows unknown property IsMemoryOptimized

I have modified a Maintenance plan on SQL Server 2008 using SSMS 2014, particularly Reorganize index task - I have added another two databases for this task. When I wanted to check if the T-SQL is OK,...
1
vote
0answers
51 views

SQL Server 2014 Management Studio restore destination changes from selected database

In SQL Server 2014 Management Studio, when I right-click on a database and choose restore, then tell it to restore from a .bak from disk, it always changes the restore destination away from the ...
1
vote
2answers
235 views

Cant connect to Server using SSMS on my desktop

SQL SERVER 2008 R2 When attempting to connect to the default instance of sqlserver on our local server from SSMS on my desktop I'm receiving the following error. A network-related or instance-...
0
votes
1answer
249 views

SQL Server 2012: Users cannot see database properties

I migrated a database from SQL Server 2008 to 2012 and also the users. Now the weird thing is that if the user connects to SSMS on the same server as the SQL Server engine, he can display database ...
-4
votes
1answer
475 views

How do I create multiple users with one login to have access to single database [closed]

What is the benefit of creating a login for users? If a login grants the principal entry into the server, I want to grant this login to three users: db-writer db-reader ddl-admin each with ...
1
vote
1answer
36 views

How do I compare all tables with nonzero rowcounts (in an archive db) with tables of the same name in the line of business db?

(Full Disclosure: I am a software tester who is out of my depth. Because I am unable to get a definitive answer from the developer due to the necessary independence of the test team, I have to figure ...
3
votes
1answer
52 views

Microsoft SQL Server Management Studio - default table view, default table parameters

A friend has recently started teaching me how to design relational databases. Long story short, as I am not SQL-capable yet, we're using SSMS and lots of right-clicking. And I am frustrated with ...
1
vote
1answer
29 views

restoring SQL Server from backup into a database with different database name?

I have been provided SQL Server backups to restore but they have to be restored into new databases with different names on same server. To explain it, say I have database "MyDB" and its backup "...
0
votes
1answer
1k views

Missing Database Mail Configuration Wizard in SQL Server 2008 R2

I was able to verify that Database Mail is installed and running but when I expand the management folder there is no Database Mail Configuration Wizard.
7
votes
1answer
189 views

Tab color for registered servers in Management Studio

SSMS allows us to use custom color for each connection. Does anyone know where this information is saved? I'd like to keep the color setup but I keep losing it. edit When I connect from object ...
0
votes
2answers
85 views

Cannot connect in SSMS with Windows Authentication

I found and added the server name through <Browse for more...> -> Network Servers so it exists and is correct. What seems weird is that the Password field is empty and I cannot modify it...? ...
-1
votes
1answer
3k views

Unable to attach mdf file on SQL Server 2014

I have SQL Server 2014 on my Windows 8.1 Pro machine. However, I couldn't find any sample database for 2014 version and hence I am trying to attach AdventureWorks2012_Data.mdf file by right clicking ...
1
vote
0answers
35 views

SSMS 2012 intellisense is not working on one of the servers?

I dont know if this question is asked - I found similar question - How to enable intellisense, but my case is little different. We have 2 SQL Servers 2012 - one is in the other room, other is in our ...
0
votes
0answers
72 views

Error while opening Registered Servers in SQL Server

I just installed SQL Server Management Studio 2012 and I'm trying to open the Registered Server tab to import my xml file with all saved servers, but I'm getting the error described below: "SQL ...
2
votes
1answer
1k views

Connection to local SQL Server 2012 can be established from SSMS 2008 but not from SSMS 2012

I have two local SQL Server instances running on my local machine. The first is SQL Server 2008 R2 Enterprise Edition (named MSSQLSERVER) and the 2nd is SQL Server 2012 Business Intelligence Edition. ...
3
votes
0answers
334 views

SSMS Client Error: Internal connection fatal error

I am running SSMS 11.0.5646.0 on a Windows 7.0 Pro desktop connected by UTP directly to same network switch as my development server (Windows 2008R2 Standard, SQL 2008R2 SE with SP3). All within the ...
0
votes
1answer
28 views

Tools to visualize adhoc multi dimentional data

With SSMS I cannot run a MDX query such as Select [Measures].[Internet Average Sales Amount] on 0, [Product].[Category].children on 1, Product.[Model Name].children on 2 From [Adventure Works]; ...
4
votes
2answers
204 views

Why would SQL Server remain unresponsive after a slow query completes?

I've just finished debugging a problematic SQL Server 2012 table-valued function. It was declared to use a unique column in the table: CREATE FUNCTION [dbo].[myApp_getUserIdsFromXml] ( @...
1
vote
0answers
1k views

how can I track down the source of “Internal connection fatal error”?

I'm working on a 6-700 line SQL script today. I'm running it from SSMS. Pointing at a SQL 11.0 db. About half the time I run it, I get: An error occurred while executing batch. Error message is: ...
2
votes
1answer
236 views

Management Studio licensing issues

We have a licensed SQL Server 2012 Standard Edition instance, that for some reason keeps getting license expiration messages in Management Studio. Sometimes it gets this message: Your Microsoft ...