how to configure SQL server in ibm bluemix with asp .net

Can any one explain, how to configure SQL server in IBM blue mix for asp .net web application.

Transpose columns to rows SQL Server

How to dynamically transpose some columns to rows if the columns I want to convert to rows all start with a prefix of 'c' in the column name. I have a table as follows

DECLARE @t codes 
(
  Tax CHAR(5),
  ptype CHAR(2),
  c1 CHAR(1),
  c2 char(1),
  c3 char(1)
)

insert into @t (tax, ptype, c1, c2, c3) values ('AAAAA','10',Null, 1,2)
insert into @t (tax, ptype, c1, c2, c3) values ('BBBBB','21',3, 1,NULL)
insert into @t (tax, ptype, c1, c2, c3) values ('ZZZZZ','1',NULL, NULL, 2)
insert into @t (tax, ptype, c1, c2, c3) values ('CCCCC',NULL,1,3,4)
insert into @t (tax, ptype, c1, c2, c3) values ('YYYYY','4',NULL, NULL, NULL)
insert into @t (tax, ptype, c1, c2, c3) values ('DDDDD','8',2,5,6)

How do I output the below where ptype is not 'NULL' and when c1,c2,c3 are not 'NULL' with C1,C2,C3 values sorted ascending?

Tax   ptype  Columns value
----- -----  ------- -----
AAAAA 10     c2      1
AAAAA 10     c3      2 
BBBBB 21     c2      1
BBBBB 21     c1      3 
ZZZZZ 1      c3      2
DDDDD 8      c1      2 
DDDDD 8      c2      5
DDDDD 8      c3      6

How to list rows which has the same value in the first column but different in the second? (SQL)

I have a Data table like this:

Name        Grade
Jim          5
Jim          4
Charlie      3

I would like to get another Result table like this:

Name        Grades
Jim          5,4
Charlie      3

How can I list or sort out the grades of a child in one column?

Adding new parameter to stored procedure

I've done some Googling, but I can't seem to find an answer to what I'm looking for. Maybe my search terms are off. Here is my situation:

I have a stored procedure in my database that currently takes in and utilizes 11 parameters (all working great). I need to add a new parameter to this for a new column I added. We always explicitly define our columns in code, so there was no issue adding a column to the end of the table. However, if I add a new parameter in my stored procedure to populate this new column, will it throw an error back to my C# code if it isn't supplied, or will it default to null (or some other value) for the parameter?

Example C# code to call SP:

public static void InsertMailLog(string messageId, DateTime sentOrReceivedDate,
        string fromAddress, string toAddress, string subject, string receivedMessage, string tailNumber,
        string messageType, string direction, string sentOrReceived, string distributionList, ILogger AppEventLog, string filename = null)
    {
        List<string> lstParameterValues = new List<string>();

        try
        {
            lstParameterValues.Add(messageId ?? "");
            lstParameterValues.Add(sentOrReceivedDate.ToString("yyyy-MM-dd HH:mm:ss.fff"));
            lstParameterValues.Add(fromAddress ?? "");
            lstParameterValues.Add(toAddress);
            lstParameterValues.Add(subject ?? "");
            lstParameterValues.Add(receivedMessage ?? "");
            lstParameterValues.Add(tailNumber ?? "");
            lstParameterValues.Add(messageType ?? "");
            lstParameterValues.Add(direction ?? "");
            lstParameterValues.Add(sentOrReceived ?? "");
            lstParameterValues.Add(distributionList ?? "");
            lstParameterValues.Add(filename ?? "");  //THIS IS NEW, but it has not been published yet as the SP hasn't been updated.
            CommonDAL.ExecSpNonQuery("spMailLogInsert", lstParameterValues);
        }
        catch (Exception ex)
        {
            CommonBLL.LogError(ex, MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, "Error", messageId, tailNumber, messageType, "", Settings.Default.ContentProvider, AppEventLog);
        }
    }

Example SP:

ALTER PROCEDURE [dbo].[spMailLogInsert]
@SdMessageId         varchar(50),
@SentOrReceivedDate  datetime,
@FromAddress         varchar(100),
@ToAddress           varchar(100),
@Subject             varchar(255),
@Message             varchar(MAX),
@TailNumber          varchar(50),   
@MessageType         varchar(50),
@Direction           varchar(50),
@SentOrReceived      varchar(50),
@DistributionList    varchar(50),
@Filename            varchar(50)  --THIS IS NEW

AS
SET NOCOUNT ON

INSERT MailLog (
    SdMessageId,
    SentOrReceivedDate,
    FromAddress,
    ToAddress,
    [Subject],
    [Message],
    TailNumber,
    MessageType,
    Direction,
    SentOrReceived,
    DistributionList,
    Filename  --THIS IS NEW

) VALUES ( 
    @SdMessageId,
    @SentOrReceivedDate,
    @FromAddress,        
    @ToAddress,
    @Subject,
    @Message,
    @TailNumber,
    @MessageType,
    @Direction,
    @SentOrReceived,
    @DistributionList,
    @Filename  --THIS IS NEW
)

I completely understand that this is a terrible use of a stored procedure. I should be using Entity Framework, but it's already written, and I have a project to update the entire project to use EF in the DAL at a later date (This is very old code). My question is, if I add the new parameter "Filename" to the stored procedure BEFORE the new C# code above gets published, will I get an error, or will the SP simply default to NULL? Or, if someone has a better way to default this to NULL or empty string, if it isn't supplied, I'm all ears.

.Net datetime with milliseconds sql server using bulkcopy thows exception

I have this code, which throws exception.

SqlConnection con;
con = new SqlConnection(connectionStr);
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;     
cmd.CommandText = @"select [ID], [VERSION_ID], '08/26/2015 09:33:24:717 AM' as [Added_Dt],     [Loc_ID] as Column1
from dbo.Source where Added_Dt between CONVERT(DATETIME,'08/24/2015 09:25:43:283 AM') and CONVERT(DATETIME,'08/24/2015 09:25:43:283 AM')";

    cmd.CommandTimeout = con.ConnectionTimeout;
    con.Open();
    SqlDataReader rdr = cmd.ExecuteReader();
    System.Data.SqlClient.SqlBulkCopy bcp = new SqlBulkCopy(destconnectionStr, SqlBulkCopyOptions.UseInternalTransaction);
    bcp.BatchSize = (int)Global.BatchWriteThreshold;
    bcp.DestinationTableName = destinationTableName;
    bcp.NotifyAfter = (int)Global.BatchWriteThreshold;
    bcp.SqlRowsCopied += new SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
    bcp.WriteToServer(rdr);

destination table(SQL Server 2005): ID (int, not null), VERSION_ID (int, not null),Added_Dt(DATETIME,not null),Column1(varchar2(50) ,not null)

This throws exception if used with SqlBulkCopy but runs without any issues when using ado.net in single updates or directly from SQL Management Studio. I found that the removal of milliseconds part makes the bulkcopy run without exceptions but then I want the milliseconds to be there. How to resolve the issue?

What would be best way to show N number of column as per no of days in month

i want to show this kind of output

UserID  UserName 1  2  3 30

OR

UserID  UserName 1  2  3 31

user data saved in db select distinct UserID,Name from Userss Where IsActive=1 and order by UserID

and i want to just calculate no of days in month based on year and month name supplied by user.

one way i can do it. first i will create a temporary table and in loop add many columns to that table and later dump user data to specific column. i am not sure am i thinking in right direction. anyone can come up with suggestion or code sample to achieve it. thanks

returning multiple results set from union all query

I have got this SP to check whether the user is having any licenses that are stored in different tables..

I am getting results into dataset and from that dataset i am getting individual results with the count and if the count greater than zero then that user is having licenses.

This is the SP

ALTER PROCEDURE [dbo].[UserCheck]
(
@activatedBy varchar(30),
@brand varchar(20)
)
AS 
BEGIN 
   DECLARE @acctId as BIGINT
   SELECT @acctId = pk_acct_id from accounts with(nolock) where email = @activatedBy  and  brand = @brand

 IF LEN(@acctId) > 1
  BEGIN
     SELECT count(*) from dbo.links with(nolock) where one = @acctId
   union all 
     SELECT COUNT(*)FROM waveactivationinfo with(nolock) where Activated_by = @acctId    
   union all
      SELECT COUNT(*) FROM ABCActivationInfo with(nolock) WHERE Activated_by = @acctId
   union all
      SELECT COUNT(*) FROM CSE_ActivationInfo with(nolock) WHERE activated_by = @acctId
   union all
       SELECT COUNT(*) FROM Connect_ActivationInfo  with(nolock) WHERE activated_by = @acctId
   union all
       SELECT COUNT(*) FROM LicActivationInfo with(nolock) WHERE Activated_by = @acctId
   END 
END
GO

and then in DAL I am catching that results into dataset like this

    public DataSet UserCheck(string strEmailID, string strBrand)
    {
        DataSet ds = new DataSet();
        List<SqlParameter> ParaList = new List<SqlParameter>();
        ParaList.Add(new SqlParameter("@activatedBy", strEmailID));
        ParaList.Add(new SqlParameter("@brand", strBrand));
        ds = SqlHelper.ExecuteDataset(new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString), CommandType.StoredProcedure, "UserCheck", Convert.ToInt32(Utility.GetConfigValue("Connection_TimeOut")), ParaList.ToArray());
        return ds;
    }

I am retrieving that dataset in code behind like this ...

 DataSet ds = userDeactivate.UserCheck(txtEmailID.Text.Trim(), brandType);


if (ds != null)
{
    if (ds.Tables[0].Rows.Count > 0)
    {
        osCount = Int32.Parse(ds.Tables[0].Rows[0].ItemArray[0].ToString());
        waveCount=Int32.Parse(ds.Tables[0].Rows[1].ItemArray[0].ToString());
        aCount = Int32.Parse(ds.Tables[0].Rows[2].ItemArray[0].ToString());
        PassCount = Int32.Parse(ds.Tables[0].Rows[3].ItemArray[0].ToString());
        quickCount = Int32.Parse(ds.Tables[0].Rows[4].ItemArray[0].ToString());
        vmcCount = Int32.Parse(ds.Tables[0].Rows[5].ItemArray[0].ToString());
    }
 } 

I am thinking that this will not be a good way to check whether the user is having licenses .. Is there any alternatives for this

Is there any way to simply return the codes from SP for each result set .. if i want to get all counts from all queries do i need to modify any code in DAL ...