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

June 24, 2015

I have this Sql table setup in Sql Server that contains a XML field and I need to count the number of times a xml node appears in the xml. In practice, it could be the table contains user data and the xml contains a list of cities the user has visited and we need to find out how many cities the user has visited.

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.

May 18, 2015

The main difference between nvarchar and varchar column is in the character set of data that it can store. Varchar is single byte whereas nvarchar is double-byte and therefore nvarchar can store Unicode characters while varchar cannot.

May 15, 2015

To escape a single quote in Sql Server, use two single quotes. Note, don't use a double quote.

For example, the following SQL Script will give error.

July 25, 2014

I generated two scripts using Database project in Visual studio - one for updating the database and another for rollback if deployment fails. When I ran the update script, the database updated fine but I ran the rollback script - it failed with the message "Rows were detected. The schema update is terminating because data loss might occur". The issue is somewhat obvious; I have added new fields in a table and if I rollback, meaning if drop those fields then any data in those fields will be lost. So, how can we create script so we can run it. Since the script is needed for rollback, it might be necessary to run it.

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 29, 2014

In the previous post, I showed how to save an image to a database. In this post, I will show how to retrieve an image and display it in an page. Note that the image is saved in bytes format rather than as a file and displaying it in an Image control requires more work. Therefore, in this post, I have used the html img element to display the image.

May 26, 2014

Save Image to Sql Server

Sql server allows for the "Image" data type that can be used to save images in databases. In this post, I will show how to save an image to the database. In a nutshell, I will use a File Upload control to upload the image and use entity framework to save the image.

May 24, 2014

I am importing an excel spread sheet to sql server database but for some reason I am getting the error “The value violated the integrity constraints for the column”. I checked the table data type and it should really just work but could not get it to work.

I am importing an excel spread sheet into a sql server database and is getting the error "DTS_E_INDUCEDTRANSFORMFAILUREONERROR" and it more messages like "The value could not be converted because of a potential loss of data" and "The source column is of nVarchar(272 -length) and the destination column is nVarchar(256 -length)"

So, really there are 2 failure messages here. After some investigation, I realised that with the first message "The value could not be converted because of a potential loss of data", it's because one of the rows had a double value whereas I initially set the sql table to have int data type. As a result, it failed while converting this particular row. Changing the data type to float fixed this issue.

The second error message is because the string value in the excel is lot bigger in length compared to the max length set in the sql table. Changing the max number of characters in sql table fixed this issue.

Overall, importing an excel spread sheet to sql table is quite easy provided correct data types are applied to the sql table.

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.

Take Database Offline / Online
  1. --take offline
  2. USE master
  3. GO
  4. ALTER DATABASE [database_name]
  6. GO
  8. --take online
  9. USE master
  10. GO
  11. ALTER DATABASE [database_name]
  13. GO

April 29, 2014

I was working on a Sql server database and since there were few issues, I copied the backup of the database to run some queries. Since I don't use it often and the database is huge, I saved the mdf file in an external hard drive but then I notice that every now then when I open Sql Server  Management Studio, the database fails to open with the message "Recovery Pending".

To solve this issue, I can simply reload the database from the backup but then I will need access to the backup file every time. Alternatively, I can write a few queries to solve this issue.

To start with, I will set the database to Emergency state and change it to a Single User mode.

  1. USE master
  2. GO
  4. --run 1
  6. GO
  8. GO


Next, check if the database has errors using CHECKDB command.

  1. --run 2

This is the step where you need to decide what to do with the errors that you have received. Depending on your scenario, you can repair the database with or without any data loss.

  1. --run 3
  2. --no data loss
  5. --potential data loss


Finally, change the database mode to multi user mode.

  1. --run 4
  3. GO


Your database should be running again now.

Few days back I ran into a issue where my website failed with the message that

  1. {System.Data.SqlClient.SqlException (0x80131904): Could not allocate space for object 'dbo.Table1'.'PK_TableID' in database 'TestDB' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
  2. Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.


Initially I thought the database was out space but then it sounded strange as I was not expecting the database to run out of space. After checking the database space I noticed that it only had a few MBs of data remaining for the primary while the log file had a huge data capacity. To fix the issue, update the max size of the database so it can save the data. Also, make sure the server has sufficient space to cater for the increased space.

However, sooner or later you are going to run out of space and therefore it's a good idea to increase the max size to unlimited but you need to make sure the server can cater for it.

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 14, 2014

Sql - Count by XML Attribute

I have a Sql table with a XML column and I want to do a count of rows of data grouped by an attribute value of the XML column. We might need these kind of queries when the XML attribute refer to a type of data. For example, I might have country code as AU and US that refers to two different countries where the data came from. Ok, so, how can I do that?

First, we have a sql table with an XML column to hold the data. I will use the same table as in my previous post on how to query csv from XML column. Here is a copy of the example data that I have.


To query this table, I will perform the usual sql select operation on the column and get the attribute values and then perform sql count operation from an outer sql query. Here's the script on how I did it.

Sql Script
  1. SELECT src.typeid as [TypeId],
  2.     count(src.typeid) as [Count]
  3. FROM
  4.   (
  5.     SELECT [Tag].value('(/a/@data)[1]', 'nvarchar(50)') as name
  6.         ,[Tag].value('(/a/@type)[1]', 'int') as typeid
  7.     FROM [Employee].[dbo].[Table_2]
  8.   ) src
  10.   group by src.typeid

April 11, 2014

Query Comma Separated Values in Sql

Here is a situation where I will need to select and compare values from a table in sql server which stores data in a comma separated list. Well selecting data is not a problem but we want to run a query and compare the values within the list. In my scenario, the list contains numbers only but the solution will work for both numbers and strings. One solution will be to compare using LIKE and then check if the number exist but this won't necessarily be correct as ,2, and ,22, and 12, might all return the same result and the list does not contain commas at the start or at the end.

April 7, 2014

I have this scenario where I need to insert xml data to a xml column in Sql Server that already has an empty string in it. The xml column I have allows null and I need to update both null and and empty xml to a default value. When I started writing the script finding the null value was really easy - I could write something like this.

  1. SELECT xmldata
  2. from Table_1
  3. where XmlData IS NULL

But finding the xml column with empty string was difficult. First of all, I could not write the following

  1. SELECT xmldata
  2. from Table_1
  3. where XmlData = ''


as it returned error

Msg 402, Level 16, State 1, Line 2
The data types xml and varchar are incompatible in the equal to operator.

So, to solve the issue, I used the inbuilt function DataLength() on the xml column and compared if the value is 5 or not. Data length for empty string is 5. Based on this comparison, I updated the xml column.

  1. SELECT xmldata
  2. from Table_1
  3. where DATALENGTH(XmlData) = 5

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

March 21, 2014

Index Size in Sql Server Database

In the previous post, I showed how to retrieve the size of individual tables of a sql server database. In this post, I will show how to get the size of indexes in a sql server database. Here is the script that does that.

  1. USE AdventureWorks2012;
  4.              AS IndexName,
  5.     SUM(page_count * 8) AS IndexSizeKB,
  6.     SUM(page_count * 8) / 1024 as IndexSizeMB
  7. FROM sys.dm_db_index_physical_stats(
  8.     db_id(), object_id('dbo.TableName'), NULL, NULL, 'DETAILED') AS t
  9. JOIN sys.indexes AS ind
  10. ON t.[object_id] = ind.[object_id] AND t.index_id = ind.index_id
  11. GROUP BY
  12. ORDER BY IndexSizeKB DESC


The script looks up the sys.dm_db_index_physical_stats table and finds the page count and converts it to index size. Here's a screen shot of the result.


Reference: Shahed Kazi at