Recently, I had to login to a SQL Server to get some data but the pain was I had to login to remote desktop using a different windows username and then opening Sql Server Management Studio and then opening connection to the Sql Server in a different server. I was thinking this is just crazy and I would simply just want to connect to the sql server using my local Sql Server Management Studio.
May 22, 2015
July 16, 2014
I run an instance of Sql Server in my local machine but noticed that my laptop is running very low in memory. So, I stopped the Sql Server by right clicking on the "Sql Server" name in Sql Server Management Studio and clicked Stop. A few prompts appeared that I had to click to stop the Sql Server.
May 30, 2014
I am trying to add a database diagram for the tables in Sql Server Management Studio and it's failing with the message "Database diagram support objects cannot be installed because this database does not have a valid owner". This is a bit weird as I thought the owner is already set.
May 24, 2014
This should have been an easy task to create a composite key using the design view in sql server management studio but it took me a while to figure it out. Basically, creating the composite key is easy and can be done in the following way.
May 2, 2014
We may need to take a database offline various reasons, for example, it was a test database and we don’t need it anymore or the application has been decommissioned, etc. Sometimes, I have noticed that Sql Server Management Studio becomes unresponsive and hangs for a long time while taking the database offline. In this post I will talk about how to fix this issue.
Usually, the database can be taken offline by right-clicking on the name of the database and clicking Tasks > Take Offline as shown below.
Once I click on “Take Offline”, I expect the database to be taken offline but instead it hangs. To avoid the issue, I can write sql script to take the database offline. Following is the script that will take the database offline and then bring it online.
April 15, 2014
I was trying to kill all spid that points my database but I kept getting the error message "Cannot Use Kill to kill your own Process". The error message was a bit strange as I have done this a number of times without any issues. So, basically this is what I tried.
April 7, 2014
I had this situation where I had a XML column in a table within a Sql Server database which allowed NULL and I had to change this to must have values. Changing this is trivial when it is a new database with some test data but in my situation, I had to modify an existing database and therefore - the quickest way to change was to add the default xml to all fields that had NULL and then to apply a default value for this column. However, when I add the default value via Sql Server Management Studio, it complained - "Error validating the default for column - column name".
February 25, 2014
I am trying to create a new database and ended up receiving an error like “Directory lookup for the file … failed with the operating system error 2”. The following screen shot tells the full story.
With this kind of error, it normally happens when a path is specified on where the database will be created and in my situation I have specified the path but have not created the appropriate folders. Sql Server Management Studio does not automatically create the folders where the database will be created and this needs to be created manually by the administrator.
In the previous post, I showed how to create a new Sql Server database from backup, however, at times the following error might occur -
System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'NewDB' database. (Microsoft.SqlServer.SmoExtended)
The above error really states that the new database is not the same the database in the backup. To avoid this error, I will, set couple more options in the Restore database dialog.
Click on Options on the left and select “Overwrite the existing database (WITH REPLACE)”.
And, in the Files option, I will set the data file folder and log file folder to the folder location that I used to create the database. By default, it will have the paths specified for the original backed up database.
Click on OK and continue to create the database as specified in the previous post on how to create a database from backup.
Usually, we take a backup of a database at regular time intervals and to be on the safe side that if something goes wrong, we have a point in time to go back to. However, we can also create a new database from the backup and this might be useful for debugging purposes. In this post, I will show how to create a new database from a backup of different database.
To create a database from a backup follow the following steps.
Create a new database with the new name.
Click OK and this will create the database. Once created, right click on Databases and choose Restore Database. In the Source section, select Device, click on the … icon
Click on Add and select the location and select the backup file.
Click OK twice and then select the destination. Select the NewDB that was created earlier.
Click on OK and the new database should be setup correctly.
February 23, 2014
The Sql Server has been setup and from the Services panel and Sql Server Configuration panel, I can see that it’s up and running but I cannot connect to it from asp.net solution. At this stage, I would verify if Sql Server is indeed running by logging into the server using Sql Server Management Studio from within the server. In this situation, it will connect from within the server but not when connected from outside – that is, from other servers.
The above issue can happen remote connections are not allowed in the Sql Server. To enable remote connections, open Sql Server Management Studio and connect to the Sql Server. Right click on the server name and click on “Properties”. This will open a up dialog like this.
Click on Connections from the left hand panel which will open a dialog like this.
Select the “Allow remote connections to the server” option and and click OK.
February 22, 2014
Has this ever happened to you that you have setup Sql Server (any version) and it seems to be up and running but I cannot access it from Sql Server Management Studio. To start with, how do you know it's up an running - I usually check if the Sql Server related services are up and running from Administrative Tools > Services section.
Once confirmed I connect from SSMS in the same server to the Sql Server but it does not work. At this stage, we need to check if firewall settings allow SSMS to access the sql server. Open firewall settings from Administrative Tools > Windows Firewall and Advanced Security and a new inbound rule to allow the SSMS program to access the sql server like below.
In the programs section, browse to the location where SSMS is installed. This setting will allow SSMS to access Sql Server.
February 21, 2014
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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
which I have received almost every time. As this does take up a lot of time to figure out the issue, this is how I have gone about it.
February 7, 2014
There are few ways we can get the size of a Sql Server database. There are built in stored procedures and we can also write our own and even use the sql server management studio to get the size. In this scenario, I am using the AdventureWorks database.
The stored procedure "sp_spaceused" returns the database size, index size, data size and unused space of the database. We can use it in the following way.
The stored procedure "sp_helpdb" returns the size of the database, name, dbid, filename, data size, log size and few other metadata. It can be used like below.
We can write a custom script to query from sysfiles to get the size of the database. The script and the result it produces is below.
Lastly, and most easily, we can get the size from the properties of the database. Right click on the database name in object explorer, click on properties and then choose Files from "Select a page".
January 13, 2014
I have noticed few times that when a new column is added to SQL server database table, the column gets added from design view and I can write a select query for it. But in the select query, the column name gets highlighted and says the column name is not valid even when the select query works. An example is below.
January 6, 2014
A unique key is a field in a table that appears only once in the table and therefore can uniquely identify the table. It's similar to a primary key in the database but it differs in some ways including that it can accept null value. The null value can appear only once for the unique column in the table.
To add a unique key using sql server management studio, and click on indexes / keys option. The following popup will display.
From the popup options, select the columns that need to be unique, choose Is Unique to Yes and set the type as Unique Key. Click close and save the changes to the database.
December 19, 2013
To correct the above error in Sql Server Management Studio, click on Options from top menu.
From the Options popup, select the "Designers" option from the left menu and then untick "Prevent saving changes that require table re-creation". Setting this option will allow saving the changes in the database.
December 17, 2013
Using the same database and tables setup in previous post, we will add a primary key for the table. The primary key is a column that uniquely identify a row of data within a table. So, for example, in the Customer table, you can have 2 customers with name - Steve Smith and you can easily differentiate between them. This might also mean duplicated fields - like the same customer might have been added multiple times and we cannot differentiate them. To allow for these scenarios, a unique key is required.
Once a database is created, scripts can be generated for it by right clicking the database name and selecting "Script Database as" > "Create To" as shown below.
This generates the scripts as per below.
If you notice, it has lot of options that we skipped while creating the database. For simplicity, we will remove these options. This options will automatically default to its default value. Here's the shortened script.
Once the script is run, the database will be created. Note that, if the database already exists, it won't work. To test the script in that case, just rename the database name and file names and then execute the script.