Knight.Liao's Tale

Concatenate strings from c column into a single row is very common SQL operations in your project. However, this function is not provided directly by SQLSERVER. We should use several tricks to fulfill this feature.

There's a good article which provide how to write SQL statements for this feature[1].

Originally, there are several methods:

• 1. use UDF(user defined function) for Group by statement[1,2]. For the item which will be grouped, we can concatenate all return rows for a column to a single row using this UDF.
  This method is easy to understand, but the performance is the slowest in the three methods.
• 2. use  " FOR XML PATH ('') " clause[1].
• 3. use " cross apply" and " For XML PATH('') " clauses[1].
  This is the best one I think. It's not only easy to understand but also the performance is good. 

继续阅读

If we select some items from table which these items do not exist in the Group by clause, we'll get  an error:

Column 'xxx' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

There are three solutions:

• Aggregate the offending field
• Remove the offending field from the SELECT line
  • It's simple. However, this solution is the one you don't like at most
• Add the field to the GROUP BY clause
  • codes can be:

    select a, b, c, count(d)
    from tableAA
    group by a,b,c
    

My solution:

if your code is

select a, count(b)
from tableAA
group by a

继续阅读

Error Description:

I have a table of Database, one item string "a" of this table can be set as null. Now I want to insert a row to this database, and use 'null'  to assign variable "a". the codes can be :

string a=null;
.......
new SqlParameter("@a", a),
.......

Unluckly, I got an error: "expects parameter '@a' , which was not supplied

Solution:

the reason of it is because the difference of C# datatype and SQLserver datatype

we must use the null formate of SQLserver and not C#, so the code should be:

new SqlParameter("@a", ObjString(a))

which ObjString function is:

private object ObjString(string param)
 {
       return param == null ? (object)DBNull.Value : (object)param;
}

Reference:

[1]. http://forums.asp.net/t/1047321.aspx

This evening I meet an SQL programming error. When I want to insert one row to a table which contains an identity column, the error occur:

Error 8101 An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON

The solution is obviously: because of identity column.

The solution codes[1] can be written as follows:

-- Before your SQL Statement:
SET IDENTITY_INSERT <tablename> ON
{YOUR SQL INSERT STATEMENT}

-- After your SQL Statement:
SET IDENTITY_INSERT <tablename> OFF

Reference:

[1]. pinaldave http://blog.sqlauthority.com/2007/03/28/sql-server-fix-error-8101-an-explicit-value-for-the-identity-column-in-table-can-only-be-specified-when-a-column-list-is-used-and-identity_insert-is-on/

If you write a windows service, and you choose "Local Service" as account type, and if you want to connect to sql server db, you must use

User ID=*****;Password=***

instead of

integrated security=SSPI

Or you'll get a pemission exception.