ssms interview questions
Top ssms frequently asked interview questions
I am using SQL Server Management Studio.
I wish to save the results of a query to an excel file.
I choose "save as" and then save to CSV file which I can open in excel. All good except I am missing column headers, any ideas how I get them exported?
Source: (StackOverflow)
When trying to connect to a server in Management Studio (specifically 2008), there is a field where you enter the Server name. That field also has a drop-down list where it shows a history of servers that you have attempted to connect to.
I would like to know:
- How to remove an individual item
from that history.
- How to remove an
item from the Login field history
for each Server name.
Thanks!
Source: (StackOverflow)
how do I make a composite key with SQL Server Management Studio?
I want two INT columns to form the identity (unique) for a table
Source: (StackOverflow)
I dropped a database from SQL Server, however it turns out that my login was set to use the dropped database as its default. I can connect to SQL Server Management Studio by using the 'options' button in the connection dialog and selecting 'master' as the database to connect to. However when ever I try to do anything in object explorer it tries to connect using my default database and fails.
Does anyone know how to set my default database without using object explorer?
Source: (StackOverflow)
I've recently used our company's spare laptop (that has a general user set up) while mine was being repaired. I've checked the "Remember password" option in SQL Server Management Studio when logging in to the database.
I need to clear the login and password information that I have used to prevent the next person that will use the laptop from using my login names and passwords. How can I do this?
Source: (StackOverflow)
If I want to save any changes in a table, previously saved in SQL Server Management Studio (no data in table present) I get an error message:
"Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created."
What can prevent the table to be easily edited? Or, is it the usual way for SQL Server Management Studio to require re-creating table for editing? What is it - this "option Prevent saving changes"?
Source: (StackOverflow)
I'm looking for a way to generate a "Create and insert all rows" script with SQL Management Studio 2008 R2.
I know that I can create a "create table" script.
I can also create an "insert in" script, but that will only generate a single row with placeholders.
Is there a way to generate an insert script that contains all currently stored rows?
Source: (StackOverflow)
When I create a table in SQL Server and save it, if I try to edit the table design, like change a column type from int to real, I get this error:
Saving changes is not permitted. The change you have made requires the following table to be dropped and re-created. You have either made changes to a table that can't be recreated or enabled the option prevent saving changes that require the table to be re-created.
Why do I have to re-create the table? I just want to change a data type from smallint to real.
The table is empty, and I didn't use it until now.
Source: (StackOverflow)
I am trying to connect to a Microsoft SQL 2005 server which is not on port 1433. How do I indicate a different port number when connecting to the server using SQL Management Studio?
Source: (StackOverflow)
I just installed SQL Server 2008 Express on my Vista SP1 machine. I previously had 2005 on here and used it just fine with the old SQL Server Management Studio Express. I was able to connect with no problems to my PC-NAME\SQLEXPRESS instance (no, PC-NAME is not my computer name ;-).
I uninstalled 2005 and SQL Server Management Studio Express. I then installed SQL Server 2008 Express on my machine and elected to have it install SQL Server Management Studio Basic.
Now, when I try to connect to PC-NAME\SQLEXPRESS (with Windows Authentication, like I always did), I get the following message:
Cannot connect to PC-NAME\SQLEXPRESS.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476
When I installed SQL Server 2008, I had it use SQLEXPRESS as the local instance name. As far as I can tell, through SQL Server Configuration Manager, SQL Server is configured to allow remote connections.
When I went to the help link it mentions, the help page suggests the following:
- Make sure that the SQL Server Browser service is started on the server.
- Use the SQL Server Surface Area Configuration tool to enable SQL Server to accept remote connections. For more information about the SQL Server Surface Area Configuration Tool, see Surface Area Configuration for Services and Connections.
Well, as far as I can tell, there is no SQL Server Browser service on my system (looked in MMC for one, couldn't find one).
And the SQL Server Surface Area Configuration tool doesn't exist in SQL Server 2008. So good job there with your help documentation, Microsoft ;-).
I'm at a loss for what to do right now. I had a lot of work I was hoping to get done today after upgrading to 2008 (the person I'm working with got it up and running with no problem and told me it would be easy - he's also far better with database stuff that I am). Does anyone have any idea what might be wrong? I'd really appreciate it. If I can't get this working in a few hours, I'm going back to SQL Server 2005 (if that will even work, gah...).
Edit: I have tried turning Windows Firewall off, and that did not help. Also, I noticed that I do not have a "Data" directory under my SQL Server 2008 install directory tree - could I have possibly set something up wrong when I installed it?
Source: (StackOverflow)
I am attempting to create a Stored Procedure for a newly created database. However the SSMS intellisense does not recognize more than half of the tables which have been created.
For example whilst in the left hand column under tables I have a table dbo.Room, when I type "dbo." in the new query window, that table is not listed, in fact only 17 out of 37 tables are listed. I can see no difference between the tables listed by intellisense and those not. If I manually type dbo.Room, it is underlined, with an error of "Invalid Object Name 'dbo.Room'.".
Have I missed something in setting up the tables?
UPDATE: I have tried refresh of the tables list (several times)
Source: (StackOverflow)
I'm using using Windows Vista and I'm having trouble logging in with a newly created user.
- I open SQL Server Management Studio.
- I create a new Login by right-clicking on Security->Logins.
Check: SQL Server Authentication
Login name: tester
Password: test
Click OK
- I added this user to User Mapping to my database of choice.
- Click File -> Connect Object Explorer, select SQL Server Authentication and enter tester/test and click Connect.
I get an error:
Login failed for user 'tester'. (Microsoft SQL Server, Error: 18456"
with Severity = 14 and State = 1.
What causes this error and how do I login with my user?
Source: (StackOverflow)
I need to restart a database because some processes are not working. My plan is to take it offline and back online again.
I am trying to do this in Sql Server Management Studio 2008:
use master;
go
alter database qcvalues
set single_user
with rollback immediate;
alter database qcvalues
set multi_user;
go
I am getting these errors:
Msg 5061, Level 16, State 1, Line 1
ALTER DATABASE failed because a lock could not be placed on database 'qcvalues'. Try again later.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5061, Level 16, State 1, Line 4
ALTER DATABASE failed because a lock could not be placed on database 'qcvalues'. Try again later.
Msg 5069, Level 16, State 1, Line 4
ALTER DATABASE statement failed.
What am I doing wrong?
Source: (StackOverflow)
When I submit a batch (e.g., perform a query) in SSMS, I see the time it took to execute in the status bar. Is it possible to configure SSMS to show the query time with millisecond resolution?
Here is the bar I am talking about with the section of interest circled in red:
Source: (StackOverflow)
Possible Duplicate:
Lost the IntelliSense
SQL Server 2008 Intellisense problem
I'm new to SQL Server, so this is probably an easy fix. In SQL Server 2008 R2, I've just imported a new data table and/or renamed fields in an existing table (it happens either way). When I start to write some SQL, the intellisense doesn't recognize the new field names and starts underlining everything.
The only fix I've found is to close and reopen SQL Server, but I'm sure there's a simpler solution out there. Suggestions please?
(This isn't a serious problem, just a pet peeve).
Source: (StackOverflow)