Showing posts with label Sql Server Management Studio. Show all posts
Showing posts with label Sql Server Management Studio. Show all posts

May 22, 2015

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.

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.

image

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.

Take Database Offline / Online
  1. --take offline
  2. USE master
  3. GO
  4. ALTER DATABASE [database_name]
  5. SET OFFLINE WITH ROLLBACK IMMEDIATE
  6. GO
  7.  
  8. --take online
  9. USE master
  10. GO
  11. ALTER DATABASE [database_name]
  12. SET ONLINE
  13. GO

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.

image

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)”.

image

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.

image

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.

image

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

image1

Click on Add and select the location and select the backup file.

image

Click OK twice and then select the destination. Select the NewDB that was created earlier.

image

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.

image

Click on Connections from the left hand panel which will open a dialog like this.

image

Select the “Allow remote connections to the server” option and and click OK.

Now connect to the Sql Server from a remote Sql Server Management Studio or an asp.net solution and it should work.

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.

image

In the programs section, browse to the location where SSMS is installed. This setting will allow SSMS to access Sql Server.

February 21, 2014

I have setup Sql Server Express a number of times in Windows Azure within a VM and every time I have had some trouble setting it up so I could connect to it. Today, when I tried to set the sql server up, I received the error
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.

sp_spaceused

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.

  1. USE [AdventureWorks2012]
  2. exec sp_spaceused;

 

image

sp_helpdb

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.

  1. sp_helpdb 'AdventureWorks2012';

 

image

 

Custom Script

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.

  1. USE [AdventureWorks2012]
  2. GO
  3.  
  4. SELECT name, [size] * 8 / 1024 as [size MB]
  5. FROM sysfiles;


image

SSMS

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".

image

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.

1


2

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

While working with Sql Server, errors might occur when a table is altered. The table can be altered by adding a new column, deleting an existing column, or changing the data type of any of the columns. The error message that might come up is "Prevent saving changes that require the table re-creation".
To correct the above error in Sql Server Management Studio, click on Options from top menu.


1
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.

In one of the previous posts, I showed how to create a database from Sql Server Management Studio, in this one I will showed how to create the database from script. To do so, I will use the same database to show the create database script.
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.
1
This generates the scripts as per below.
Sql Script
  1. USE [master]
  2. GO
  3.  
  4. /****** Object:  Database [TestDB]    Script Date: 13/12/2013 2:13:54 PM ******/
  5. CREATE DATABASE [TestDB]
  6. CONTAINMENT = NONE
  7. ON  PRIMARY
  8. ( NAME = N'TestDB', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDB.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
  9. LOG ON
  10. ( NAME = N'TestDB_log', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
  11. GO
  12.  
  13. ALTER DATABASE [TestDB] SET COMPATIBILITY_LEVEL = 110
  14. GO
  15.  
  16. IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
  17. begin
  18. EXEC [TestDB].[dbo].[sp_fulltext_database] @action = 'enable'
  19. end
  20. GO
  21.  
  22. ALTER DATABASE [TestDB] SET ANSI_NULL_DEFAULT OFF
  23. GO
  24.  
  25. ALTER DATABASE [TestDB] SET ANSI_NULLS OFF
  26. GO
  27.  
  28. ALTER DATABASE [TestDB] SET ANSI_PADDING OFF
  29. GO
  30.  
  31. ALTER DATABASE [TestDB] SET ANSI_WARNINGS OFF
  32. GO
  33.  
  34. ALTER DATABASE [TestDB] SET ARITHABORT OFF
  35. GO
  36.  
  37. ALTER DATABASE [TestDB] SET AUTO_CLOSE OFF
  38. GO
  39.  
  40. ALTER DATABASE [TestDB] SET AUTO_CREATE_STATISTICS ON
  41. GO
  42.  
  43. ALTER DATABASE [TestDB] SET AUTO_SHRINK OFF
  44. GO
  45.  
  46. ALTER DATABASE [TestDB] SET AUTO_UPDATE_STATISTICS ON
  47. GO
  48.  
  49. ALTER DATABASE [TestDB] SET CURSOR_CLOSE_ON_COMMIT OFF
  50. GO
  51.  
  52.   ALTER DATABASE [TestDB] SET CURSOR_DEFAULTGLOBAL
  53. GO
  54.  
  55. ALTER DATABASE [TestDB] SET CONCAT_NULL_YIELDS_NULL OFF
  56. GO
  57.  
  58. ALTER DATABASE [TestDB] SET NUMERIC_ROUNDABORT OFF
  59. GO
  60.  
  61. ALTER DATABASE [TestDB] SET QUOTED_IDENTIFIER OFF
  62. GO
  63.  
  64. ALTER DATABASE [TestDB] SET RECURSIVE_TRIGGERS OFF
  65. GO
  66.  
  67.   ALTER DATABASE [TestDB] SETDISABLE_BROKER
  68. GO
  69.  
  70. ALTER DATABASE [TestDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
  71. GO
  72.  
  73. ALTER DATABASE [TestDB] SET DATE_CORRELATION_OPTIMIZATION OFF
  74. GO
  75.  
  76. ALTER DATABASE [TestDB] SET TRUSTWORTHY OFF
  77. GO
  78.  
  79. ALTER DATABASE [TestDB] SET ALLOW_SNAPSHOT_ISOLATION OFF
  80. GO
  81.  
  82. ALTER DATABASE [TestDB] SET PARAMETERIZATION SIMPLE
  83. GO
  84.  
  85. ALTER DATABASE [TestDB] SET READ_COMMITTED_SNAPSHOT OFF
  86. GO
  87.  
  88. ALTER DATABASE [TestDB] SET HONOR_BROKER_PRIORITY OFF
  89. GO
  90.  
  91. ALTER DATABASE [TestDB] SET RECOVERY SIMPLE
  92. GO
  93.  
  94.   ALTER DATABASE [TestDB] SETMULTI_USER
  95. GO
  96.  
  97.   ALTER DATABASE [TestDB] SET PAGE_VERIFY CHECKSUM
  98. GO
  99.  
  100. ALTER DATABASE [TestDB] SET DB_CHAINING OFF
  101. GO
  102.  
  103. ALTER DATABASE [TestDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
  104. GO
  105.  
  106. ALTER DATABASE [TestDB] SET TARGET_RECOVERY_TIME = 0 SECONDS
  107. GO
  108.  
  109.   ALTER DATABASE [TestDB] SETREAD_WRITE
  110. GO

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.
Sql Script - Shortened
  1. USE [master]
  2. GO
  3.  
  4. /****** Object:  Database [TestDB]    Script Date: 13/12/2013 2:13:54 PM ******/
  5. CREATE DATABASE [TestDB]
  6. CONTAINMENT = NONE
  7. ON  PRIMARY
  8. ( NAME = N'TestDB', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDB.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
  9. LOG ON
  10. ( NAME = N'TestDB_log', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
  11. GO

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.

December 14, 2013

Once the database has been created, tables need to be added to the database. Without the tables, the database has no data. In SQL Server Management Studio, to add a new table, simply right click on the "Tables" and click "New Table".

12

Reference: Shahed Kazi at AspNetify.com