•   Posted in: 
  • SQL

If you add a new table and mark the ID column as primary key, but you forgot to set the int or bigint to auto increment, you can not fix this in the SQL Management Studio.


There are several ways to do this, I ended up removing the primary key, dropping the column and re-creating it.

Dropping the column also gave me the error about dropping the whole table and re-creating it. So I had to do this with some SQL, so no GUI today but just these two small SQL statements. I hope it helps, otherwise it is just an online backup for when I forget how I fixed this.

alter table YOURTABLENAME add YOURCOLUMNNAME bigint IDENTITY(1,1) primary key

Good luck!


ps. when you use select @@version you get to see what version of SQL server you are using. I had

Microsoft SQL Server 2008 (SP3) - 10.0.5520.0 (X64)
    Jul 11 2014 16:11:50
    Copyright (c) 1988-2008 Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)
This blogpost http://sqlserverbuilds.blogspot.nl/ has a nice list of different versions.

Pin on pinterest Plus on Googleplus Post on LinkedIn

Lazy productive I as a developer am, I did not want to write a stored procedure. So I ended up with a lot of SQL statements which needed to be combined and executed only if the previous one succeeds.

However there was a timeout during the execution. It was strange because when I copy pasted the SQL statement in SMSS it worked perfectly and fast too. My mistake was to use a new SqlCommand object which was not part of the transaction. I needed to re-use the same sqlcommand. Here is some working code:

using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConString"].ToString()))

    SqlCommand cmd = con.CreateCommand();
    SqlTransaction transaction = con.BeginTransaction();

    cmd.Connection = con;
    cmd.Transaction = transaction;

        cmd.CommandText = "SELECT productcode FROM products WHERE productid = " + productid;
        string code = cmd.ExecuteScalar().ToString();
        cmd.CommandText = "SELECT ProductID, ProductWeight, ProductEuroPrice, OrderID FROM ORDERPICKINGPRODUCTS WHERE orderid = " + orderid + " AND productid = " + productid;
        SqlDataReader sdr = cmd.ExecuteReader();
        DataTable dt = new DataTable();
        cmd.CommandText = "DELETE FROM ORDERPICKINGPRODUCTS WHERE orderid = " + orderid + " AND productid = " + productid;
    catch(Exception e)
I know that it is better to use parameters https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters%28v=vs.110%29.aspx

And the AddWithValue method… But this is just pseudo code and not in production. So don’t worry!

This is just a small sample to help people with a strange timeout which you can encounter if you use an sqlcommand outside the transaction.


Good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn

Microsoft SQL Server has the ability to format the query output to xml, which is great. I used version “Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)” to output XML to have a nested data structure, but there is unfortunately no first citizen support for JSON which is big on the web these days. Perhaps it is left out on purpose to make sure that people are unable to direct output their data as JSON and are forced to build a layer in between so that it can also authenticate and add security to a request. Web API is great for this. But back to the subject: I wanted to output my nested data as JSON really quickly with the minimum amount of code.

In a nutshell, I got the data as xml and converted that to JSON and send it to the output of a generic handler (*.ashx)

Here is my source code for my easy SQL2JSON solution:

private XDocument GetProducts(string q)
    SqlCommand comProd = new SqlCommand(@"SELECT ProductCode, ProductEan13, CategoryName FROM PRODUCTS 
        inner join CATEGORIES on products.CategoryID = CATEGORIES.CategoryID
        WHERE ((FactoryCode LIKE @name) OR (ProductCode LIKE @name)) AND (ProductDeleted = 0) for xml raw ('Product'), ROOT ('Products');");
    comProd.Parameters.AddWithValue("name", q + "%");
    string xml = SsDb.GetScalar(comProd).ToString();
    return XDocument.Parse(xml);

This gave me an `XDocument` object which I still have to convert to JSON

Since I was using an generic handler I retrieved the search parameter from the query string and used it as input parameter for the `GetProducts` method.

public void ProcessRequest(HttpContext context)
    context.Response.ContentType = "application/json";
    context.Response.Cache.SetMaxAge(new TimeSpan(24, 0, 0));

    string q = context.Request.QueryString.Get("q");

So the most important line is the last one:


Do not forget to use the one-nuget-package-that-rules-them-all (Install-Package Newtonsoft.Json )

using Newtonsoft.Json;


Now you can access your JSON data source with the C# generic handler from your JavaScript


I hope this has been of any help. good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn

I have a table filled with numbers. It is a table with the name numbers and there is only one field named ‘num’ which is an integer (so max value is 2.147.483.647 as seen on MSDN) and the integer is  an identity and unique.

2014-03-05 12_10_47-VM530.ssinternational - dbo.numbers - Microsoft SQL Server Management Studio

The table just contains numbers from 1 until 10.000. I use it to inner join, for instance:

You can have a table with a row: productA is 10 times in stock. You can then inner join the numbers table if you need 10 rows of productA.

select * from products 
inner join numbers on num <= stock

But I only had 10.000 rows in it and encountered a bug in my system because 10.000 was not enough. So I had to increase it. I have found several solutions to create a numbers table. But no snippet to update an existing number table.

So here is my snippet to add additional numbers to your number table:

begin transaction

SELECT @i = 10001;
WHILE @i <= 1000000
    INSERT INTO [numbers] (num) VALUES (@i);
    select @i= @i + 1;

Good luck!
Pin on pinterest Plus on Googleplus Post on LinkedIn
  •   Posted in: 
  • SQL


If you want to inner join two tables from different databases, you might encounter some collation errors.

When I executed this query:

SELECT ProductCode, ProductResalePrice
  FROM [database1].[dbo].[PRODUCTS] 
  inner join [database2].[dbo].[items] on
  ProductCode = [items].itemCode
where ProductDeleted = 0 

I got this message:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

So when I executed the following query, I found out that I had two different Collations:

SELECT DATABASEPROPERTYEX('database1', 'Collation') SQLCollation;
SELECT DATABASEPROPERTYEX('database2', 'Collation') SQLCollation;

I thought that it was because one database was migrated from SQL Server 2000 to 2005 to 2008 and the other one (database2) was from 2005 to 2008. But apparently the default collation depends on the region settings of your Windows installation and gets set when you install SQL Server.

Now you can change the collation of your database with various solutions which can be found around the internet, but I wanted to simply join two tables and do one update.

I had to Google a lot to find out, how to define collations when using an inner join. But here is the solution for my query:

SELECT ProductCode, ProductResalePrice
  FROM [database1].[dbo].[PRODUCTS] 
  inner join [database2].[dbo].[items] on
  ProductCode collate SQL_Latin1_General_CP1_CI_AS = [items].itemCode
where ProductDeleted = 0 

So you can define collation on the fields which you join. Open-mouthed smile 

By the way: this is a great resource for the different collations: http://msdn.microsoft.com/en-us/library/ms143515%28v=SQL.90%29.aspx

Hope that it helped you, it would have saved me a lot of frustrations and time.

kick it on DotNetKicks.com Shout it

Pin on pinterest Plus on Googleplus Post on LinkedIn