SQL Format datetime

select convert(varchar, getdate(), 100) convertResult,100 style union
select convert(varchar, getdate(), 101),101 union
select convert(varchar, getdate(), 102),102 union
select convert(varchar, getdate(), 103),103 union
select convert(varchar, getdate(), 104),104 union
select convert(varchar, getdate(), 105),105 union
select convert(varchar, getdate(), 106),106 union
select convert(varchar, getdate(), 107),107 union
select convert(varchar, getdate(), 108),108 union
select convert(varchar, getdate(), 109),109 union
select convert(varchar, getdate(), 110),110 union
select convert(varchar, getdate(), 111),111 union
select convert(varchar, getdate(), 112),112 union
select convert(varchar, getdate(), 113),113 union
select convert(varchar, getdate(), 114),114  union
select convert(varchar, getdate(), 120),120  union
select convert(varchar, getdate(), 121),121  union
select convert(varchar, getdate(), 126),126  union
select convert(varchar, getdate(), 127),127  union
select convert(varchar, getdate(), 130),130  union
select convert(varchar, getdate(), 131),131

 

09:05:22 108
09:05:22:173 114
12-17-2013 110
12/17/2013 101
14 jQuery15205048664805831008_1387289176437? 1435  9:05:22:173AM 130
14/02/1435  9:05:22:173AM 131
17 Dec 2013 106
17 Dec 2013 09:05:22:173 113
17-12-2013 105
17.12.2013 104
17/12/2013 103
2013-12-17 09:05:22 120
2013-12-17 09:05:22.173 121
2013-12-17T09:05:22.173 126
2013-12-17T09:05:22.173 127
2013.12.17 102
2013/12/17 111
20131217 112
Dec 17 2013  9:05:22:173AM 109
Dec 17 2013  9:05AM 100
Dec 17, 2013 107

Config IIS Express with SSL

<site name="WebPortal" id="2">
    <application path="/" applicationPool="Clr4IntegratedAppPool">
            <virtualDirectory path="/" physicalPath="C:\WebPortal\Dev\POC\WebPortal\WebPortal.Main" />
    </application>
    <bindings>
            <binding protocol="http" bindingInformation="*:3303:localhost" />
            <binding protocol="https" bindingInformation="*:44302:localhost" />

    </bindings>
</site>

Error: exception has been thrown by the target of an invocation

This problem is cause by the System variable "Path" too long.

 

  1. Pressing 'Windows Key' + 'Pause Break'
  2. 'Advanced system settings' on the left
  3. 'Environment Variables' on the lower right corner
  4. Selecting 'PATH' under 'System variables'
  5. Clicking 'Edit'
  6. Shorten the path or setup another system variable to group some of the path

Insert into temporary table from Stored Procedure

DECLARE @mast_key_table table
(
    mast_key int,
    inc_mmk_calculation int,
    lastest_trx_date datetime null,
    ast_key int,
    pln_key int,
    sp_key int,
    seg_key int,
    incdet_key int,
    incdet_effdate datetime,
    uv_value decimal (18,8),
    incdet_cutoffdate datetime
)
-- EXEC sp_get_mastfordailyincdist <task_key>
INSERT INTO @mast_key_table
EXEC sp_get_mastfordailyincdist 4158
SELECT * from @mast_key_table order BY lastest_trx_date

SQL - Index node value in XML column

SET ARITHABORT { ON | OFF }

The below is from MSDN

Caution noteCaution

The default ARITHABORT setting for SQL Server Management Studio is ON. Client applications setting ARITHABORT to OFF can receive different query plans making it difficult to troubleshoot poorly performing queries. That is, the same query can execute fast in management studio but slow in the application. When troubleshooting queries with Management Studio always match the client ARITHABORT setting.

 

"SET ARITHABORT must be ON when you are creating or changing indexes on computed columns or indexed views. If SET ARITHABORT is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail."

SQL SERVER – Compatibility level

SQL SERVER – Fix : Error : Incorrect syntax near . You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel

 

 

Solution:

Change the database compatibility level using following command in management studio.

For SQL Server 2005:
EXEC sp_dbcmptlevel 'DatabaseName', 90

For SQL Server 2008:
EXEC sp_dbcmptlevel 'DatabaseName', 100

Validate DateTime

public static bool ValidateDateFormat(string sDate)
{
     bool isValid = true;
 
     DateTime Test;
     string[] formats = { "MM/dd/yyyy""M/d/yyyy""M/dd/yyyy""MM/d/yyyy" };
     if (!DateTime.TryParseExact(sDate, formats, new CultureInfo("en-US"), 
DateTimeStyles.None, out Test))      {         isValid = false;      }      //      return isValid; } public static DateTime ConvertDate(string sDate) {   string[] formats = { "M/d/yyyy""MM/dd/yyyy""M/dd/yyyy""MM/d/yyyy" };   DateTime tDate;   DateTime.TryParseExact(sDate, formats, new CultureInfo("en-US"),
DateTimeStyles.None, out tDate);   return tDate; }

SSIS - generate deployment manifest file

Creating a Deployment Manifest

  • With the Solution Explorer open Right-Click on the project name and click Properties
  • This will open the Project Properties Pages
  • Select the Deployment Utilityfrom the page
  • Change the CreateDeploymentUtility property to True

After you’ve gone through these steps the next time you build your project it will create the file (YourProjectName).SSISDeploymentManifest. This file is located in the same folder as your packages in the bin\Deployment folder.

If you run this file it will open the Package Installation Wizard that will allow you to deploy all your packages that were located in the project to a desired location.

jquery checkbox

To check on/off a checkbox:

$("#cbVoidInvoice").attr('checked'true); 
or 
$("#cbVoidInvoice").attr('checked'false); 
 
To check if checkbox check on:
$(this).is(':checked') 
 

e.Row.RowState == DataControlRowState.Edit not works on Alternating Rows

Problem:

The combine Alternatiing/Edit state fail the comparsion logic.

Solution:

if ((e.Row.RowState & DataControlRowState.Edit) > 0)