The type of column “xxx” conflicts with the type of other columns specified in the UNPIVOT list.

When pivoting data in SQL Server, all columns must be of same time, otherwise you will get this error. You might get this error if you have imperfect case statement or one column is of integer type while the other is of type varchar. Interesting I was getting this error even after casting my columns into varchar(10) but still the error. Turned out just casting wouldn’t fix the problem either. My problem code was

  The above code was giving The type of column “PercentValue” conflicts with the type of other columns specified in the UNPIVOT list becaust i…

Read More

cannot find stored procedure SQL Server

I was creating a stored procedure on my live server and was constantly getting the error message “Cannot find stored procedure” no matter how many times i created the same stored procedure on the server. Worse if I would create the same procedure again, I would get error, the object already exists. Found out that I was not using dbo.StoredProcedure with the stored procedure name, that is schema was missing. So remember on live server, using dbo (schema) can make a big difference esp if your site is being hosted on a shared host like mine. Lesson learned, dbo is…

Read More

How to create voucher numbers in SQL?

This is a short tutorial on creating voucher numbers in SQL Server. I took most of the help from this so article but made my own changes. This can be very handy script if you are working in a financial software where you need to generate voucher numbers or sequence numbers per day. Voucher numbers are typical numbers sequences based on department from start to end of financial year.Typical examples can be SAL/001, CST/001 etc. The following code creates a function (needs to be created first) which is then used by the table. Every time a new entry is made…

Read More

Be careful with CASE and NULL statement

Checking NULL values in database can be a little tricky. It is very easy to trickle those if you do not provide correct syntax. The recommend way to check null is ISNULL(myfieldname,”). When using NULL in case statement, it might run without any error but will give you in correct result. Consider this example Script to create #temp table. Note that # in front of tablename means it is temporary table. it will be automatically destroyed once you close you window.

Now lets say you want to pull column A value if it is not empty and column B…

Read More