jump to navigation

Cannot insert the value null into column ‘diagram_id’, table dbo.sysdiagrams December 28, 2011

Posted by Bilal in SQL Server 2005.
Tags: , , , , , ,
1 comment so far

When ever I copy the database from the production server to the development machine the database throws an error when updating a database diagram or creating a new database diagram. The error in question is


The solution to this problem is rather simple go to dbo.sysdiagram table and set change the diagram_id default value to 0. However when you right click to bring up the design window for the dbo.sysdiagram table the option is disabled.

Open up the columns and right click on the diagram_id column and select modify

This loads the sysdiagram design window from where you can set the default value of the diagram_id field


Save the changes and you will be able to update existing database diagrams and create new database diagrams.

Note this solution may not work for ever and has only been tested on sql server 2005.

Source: Link


AutoCompleteCustomSource – Specified Cast is Not Valid January 3, 2011

Posted by Bilal in Programming.
Tags: , , , , , , , , , , , , , , , ,

There was a requirement to implement a textbox with a databound AutoComple feature. As the Autocomplete source was a database, I have to go for a custom DataSource.

The main requirements for an auto-complete textbox (in this scenario) are:

  • AutoCompleteMode is set to Suggest(or SuggestAppend).
  • AutoCompleteSource is set to CustomSource
  • AutoCompleteCustomSource is set AutoCompleteStringCollection.

A sample code for this: (source)

namespace AutoCompleteTextBox
   public partial class frmAuto : Form
      public string strConnection = 
      AutoCompleteStringCollection namesCollection = 
             new AutoCompleteStringCollection();

      public frmAuto()

      private void frmAuto_Load(object sender, EventArgs e)
         SqlDataReader dReader;
         SqlConnection conn = new SqlConnection();
         conn.ConnectionString = strConnection;
         SqlCommand cmd = new SqlCommand();
         cmd.Connection = conn;
         cmd.CommandType = CommandType.Text;
         cmd.CommandText = "Select distinct [Name] from [Names] order by [Name] asc";
         dReader = cmd.ExecuteReader();

         if (dReader.HasRows == true)
            while (dReader.Read())
             MessageBox.Show("Data not found");

         txtName.AutoCompleteMode = AutoCompleteMode.Suggest;
         txtName.AutoCompleteSource = AutoCompleteSource.CustomSource;
         txtName.AutoCompleteCustomSource = namesCollection;

      private void btnCancel_Click(object sender, EventArgs e)
      private void btnOk_Click(object sender, EventArgs e)
         MessageBox.Show("Hope you like this example");

I implemented the above properties and other related coding. The build was okay but when I executed the application, I got a Specified Cast is Not Valid exception.

I thought that I made some mistake while implementing, I revised the code and could not find anything wrong. The error was still there! I googled and came to know that I am not alone with this exception. I got a different implementation from MSDN for loading to the custom source.

AutoCompleteStringCollection source = new AutoCompleteStringCollection ();
source.AddRange(new string[]

// Create and initialize the text box
var textBox = new TextBox
   AutoCompleteCustomSource = source,
   AutoCompleteMode = AutoCompleteMode.SuggestAppend,
   AutoCompleteSource = AutoCompleteSource.CustomSource,
   Location = new Point(20, 20),
   Width = ClientRectangle.Width - 40,
   Visible = true

I created a String[] of the size of the DataTable row count and then added it to the AutoCompleteStringCollection object (similar to the above code). On executing the application, the result was same.

As I placed the code in the middle of my application, to avoid any confusion, I added a new windows form and updated my Program.cs to start from that form. On executing the application, the same error was there to greet me. I even tried to replace my DataBound source with the above static code, but no way. A simple thing that was supposed to be done in less than 30 min took hours of my time, still unsolved.

Since my Program.cs was having a lot of startup configurations and checks, for a final test I created a new WindowsFormApplication Project and used the MSDN code in the FormLoad event. On executing the application, surprisingly it executed normally giving the desired results. This was totally a wired situation. Most of the times, we are worried when something does not work properly BUT some other times, we are worried when some thing works. It was one of those rare cases.

Upon further googling, I came to know that it has surprisingly something to do with the STAThread attribute. In my application, I am having different forms being executed in different threads so I have removed the STAThread. It was not possible to apply the attribute in my application. It revealed that this thing has been reported to Microsoft as a BUG in Visual Studio 2005 in 2004 and separately 2007. If you happen to get the same scenario, please rate this issues and post your comments to ask Microsoft from wake up from a 7+ year long sleep.

To get the thing done, I have to go for the alternative solution. I replaced the TextBox with a ComboBox and set its DropDownStyle to DropDown. It was not EXACTLY same as the desired output but still usable.

NTEXT vs NVARCHAR(MAX) in SQL 2005 May 28, 2009

Posted by Bilal in Microsoft, SQL Server 2005.
Tags: , , , , , , , ,

I recently profiled a sproc that makes heavy use of the TSQL SUBSTRING function (hundreds of thousands of times) to see how it performs on a SQL 2005 database compared to a SQL 2000 database. Much to my surprise the SQL 2005 database performed worse…dramatically worse than SQL 2000.

After much researching it turns out the problem is that the column the text was stored in was an NTEXT, but SQL 2005 has deprecated the NTEXT in favor of NVARCHAR(MAX). Now, you’d think that string functions on NTEXT would have the same performance on 2005 as it did on 2000, but thats not the case.

Ok, so NTEXT is old badness, and NVARCHAR(MAX) is new goodness. Then the next logical step would be to convert the column to be a NVARCHAR(MAX) data type, but here lies a little but very important gotcha.

By default NTEXT stores the text value in the LOB structure and the table structure just holds a pointer to the location in the LOB where the text lives.

Conversely, the default setting for NVARCHAR(MAX) is to store its text value in the table structure, unless the text is over 8,000 bytes at which point it behaves like an NTEXT and stores the text value in the LOB , and stores a pointer to the text in the table.

So, just to recap, the default settings for NTEXT and NVARCHAR(MAX) are completely opposite.

Now, what do you think will happen when you execute an ALTER COLUMN on a NTEXT column that changes the data type to a NVARCHAR(MAX)? Where do you think the data will be stored? In the LOB structure or the table structure?

Well, lets walk through an example. First create a table with one NTEXT column:

CREATE TABLE [dbo].[testTable](
[testText] [ntext] NULL

Next, put 20 rows in the table:

INSERT INTO testTable SELECT 'hmmm...i wonder if this will work'

Then run a select query with IO STATISTICS:

SELECT * FROM testTable

Now, looking at the IO stats, we see there was only 1 logical read, but 60 LOB logical reads. This is pretty much as expected as NTEXT stores its text value in the LOB not the table:

Table ‘testTable’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 60, lob physical reads 0, lob read-ahead reads 0.

Now, lets alter the table to be an NVARCHAR(MAX):


Now when we run the select query again with UI STATISTICS we still get a lot of LOB reads (though less than we did with NTEXT). So its obvious that when SQL Server did the alter table, it didn’t use the default NVARCHAR(MAX) setting of text in row, but kept the text in the LOB and still uses pointers lookups to get the text out of the LOB.

Table ‘testTable’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 40, lob physical reads 0, lob read-ahead reads 0.

This is not as expected and can be devastating for performance if you don’t catch it, since NVARCHAR(MAX) with text not in row actually performs WORSE than NTEXT when doing SUBSTRING calls.

So how do we fix this problem? Its actually fairly easy. After running your alter table, run an update statement setting the column value to itself, like so:

UPDATE testTable SET testText = testText

SQL server moves the text from the LOB structure to the table (if less than 8,000 bytes). So when we run the select again with IO STATISTICS we get 0 LOB reads.

Table ‘testTable’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

YEA! This is what we want.

Now, just for grins, what do you think happens if we change the NVARCHAR(MAX) back to NTEXT? Well it turns out that SQL Server moves the text back to the LOB structure. Completely backwards from what it did when converting NTEXT to NVARCHAR(MAX).

Source: Making Stuff Faster

Missing Items in Visual Studio 2005 Menu February 22, 2009

Posted by Bilal in .NET, C#, Microsoft, SQL Server 2005, Tips & Tricks, VB, Visual Studio 2005.
Tags: , , , , , ,
1 comment so far

I noticed some missing menu items in the VS2005 (like clean, rebuild, and many others) but i continued by using the commands from other locations; until I got stucked!  I wanted to export a form as a template but the Export was not available in the File menu.
The details of my environment are:

Since, I installed Resharper for the first time, the first option that came to my mind is that its due to Resharper. I tried to explore different options in the Resharper and VS specially the Key Mapping but no result. I was only left with the option to uninstall Resharper and I did it. But to my surprise, the problem was still there.
In such a situation, I call my rescue agent GooGle. The first suggestion provided by the agent worked well and my problem was solved.
I had installed SQL Server 2005 for the first time (initially, I was using SQL 2000 as per client requirements) but I don’t considered it bcoz it’s a Microsoft product and it was least expected to interfere in the scenario. I was WRONG. This problem occurred due to a bug in it.

Here is the solution provided by Microsoft.

Hope it will also solve your problem if u faced such an issue.