SQL SERVER: Execute SSIS Package Using Store Procedure

Originally, I used 'Microsoft.SqlServer.Dts.Runtime'to execute local SSIS package under asp.net Web application. Under client's environment, SQL Server and web application are hosted on different Windows servers. Now, I use store procedure to execute SSIS package placed in DB server.

 
set @params = '/conn \"a\";"\"' + @a+ '\"" /set \package.variables[b].Value;"\"' + @b+ '\""'
set @cmd = 'C:\dtexec.exe /f ' + @PackagePath + ' ' + @params
EXEC xp_cmdshell @cmd

Dont forget to enable 'xp_cmdshell' option first!

I had to used 32 bit version of dtexec.exe as my package need OLEDB provider Jet 4.0. The provider is not available under 32 bit environment. As a result, I use the x86 version to handle this issue.

Errors when executing SQL Server SSIS Package From Stored Procedure
How to Call SSIS Package from the Stored Procedure

Allow downloading mdb file from ASP.Net page

I would like to let users download a file after they click a button. I just use Response.Redirect("~/123.mdb") to handle this situation. 

But, an error message is shown: 
"There is no build provider registered for the extension '.mdb'. You can register one in the section in machine.config or web.config"

To Deal with this issue, go to web.config, add a fileExtension element in web.config

FileExtensions in web.config

Call SSIS package in C#

I need to call a SSIS package in a web application to import access data into SQL Server. However, I discover the performance is extremely low. And I don't know it's my personal problem or common issue. If possible, don't use it.

Package package = null;
DTSExecResult result;

//connection manager
package = new Application().LoadPackage(packagePath + "\\importData.dtsx", null);
package.Connections["rs"].ConnectionString = connectionStr;

//variable
Variables vars = package.Variables;
vars["TenderNumber"].Value = tenderNum;

result = package.Execute();

if (result == DTSExecResult.Failure)
{
    foreach (var e in package.Errors)
    {
        logHelper.Error("Package Execution Error", new Exception(e.Description));
    }

    throw new Exception(package.Errors[0].ToString());
}

CommandTimeout in Entity Framework

I use Entity Framework in my web application and then use it to execute a stored procedure command in SQL Server. Unluckily, I receive a connection timeout issue. First, I try to add 'Connection Timeout=120;' into connection string. But, the problem still exists. Then, solving the issue by CommandTimeout 

using (RSEntities rEntity = new RSEntities())
{
  //import data from temp tables
  rEntity.CommandTimeout = 120;
  rEntity.ExecuteStoreCommand("EXEC dbo.ImportMdbData");
}

Blank option in ASP.NET DropDownList

I would like to create an blank option in DropDownList by default and don't use append to add options into the list. So, I bind the datasource first and then insert the blank option afterwards.

.aspx.cs

DropDownTenderNumber.DataSource = list;
DropDownTenderNumber.DataBind();
DropDownTenderNumber.Items.Insert(0, new ListItem("----PLEASE SELECT----", "0"));

Further, I use 'InitialValue' in RequiredFieldValidator to exclude the blank option.

.aspx

Freeze/Lock Web Page While Processing in ASP.Net

I would like to create a layer to cover a page while it is processing. First, downloading a loading icon from ajaxload. Then, you just need to paste following code in .aspx file.

UpdateProgress

UpdatePanel

Script Manager

CSS

.overlay  
{
    position: fixed;
    z-index: 98;
    top: 0px;
    left: 0px;
    right: 0px;
    bottom: 0px;
    background-color: #aaa; 
    filter: alpha(opacity=80); 
    opacity: 0.8; 
}
.overlayContent
{
    z-index: 99;
    margin: 250px auto;
    width: 80px;
    height: 80px;
}
.overlayContent h2
{
    font-size: 18px;
    font-weight: bold;
    color: #000;
}
.overlayContent img
{
    width: 60px;
    height: 60px;
}

Use UpdateProgress To Cover Screen

I have tried to browse it use chrome, firefox and ie and it works fine.

globalization Element in web.config

I would like to provide a textbox to user and let them filter data by a given date in (dd/MM/yyyy) format.

query = query.Where(i => i.OperationDate <= end);

Unfortunately, query results seems not working properly. It seems that SQL Server uses date format (MM/dd/yyyy) to do the searching. Then, the question is how to specify the date format in my web application. By adding following line in web.config, the issue is solved.

CultureInfo Class:
Represents information about a specific culture including the names of the culture,the writing system, and the calendar used, as well as access to culture-specific objects that provide information for common operations, such as formatting dates and sorting strings.

SSIS: DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

I create an SSIS project using SQL Server Business Intelligence Development Studio under Windows Server 2008 R2. When I run the project, something is wrong and an error message is shown below:

"[OLE DB Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER."

I don't get any idea from above message. After I disable the Run64BitRunTime option, everything work fine.

Javascript: Freeze/Lock Web Page While Loading

I would like my web page could be freeze/locked when processing, and users are unable to click any button. Here is a excellent JavaScript library - Impromptu .

 
 var v;
 
 function trigger()
 {
  var txt = 'Processing';
  v = $.prompt(txt, {
   opacity: 0.8
  });
  execute();
 }
 
 function execute()
 {
  //execute your code
  $.prompt.close();
 }

It works perfect on Chrome and IE.

CRUD in SharePoint Client Object Model

I want to create/read/update/delete list item via SharePoint Client Object Model using Microsoft.SharePoint.Client and Microsoft.SharePoint.Client.Runtime.

Create

using (ClientContext context = new ClientContext(url))
{
    //set credentials
    ICredentials cache = new NetworkCredential(userName, password);
    context.Credentials = cache;

    List barcodeReaderList = context.Web.Lists.GetByTitle(listName);
    ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
                
    ListItem item = barcodeReaderList.AddItem(itemCreateInfo);
    item["Title"] = barcode;
    item.Update();

    context.ExecuteQuery();
}

Update

using (ClientContext context = new ClientContext(url))
{
    //set credentials
    ICredentials cache = new NetworkCredential(userName, password, domain);
    context.Credentials = cache;

    List list = context.web.Lists.GetByTitle(listName);
    ListItem item = list.GetItemById(id);
    item["Title"] = title;

    item.Update();
    context.ExecuteQuery();
}

Delete

using (ClientContext context = new ClientContext(url))
{
    List list = context.Web.Lists.GetByTitle(listName);

    ListItem item = list.GetItemById(id);
    item.DeleteObject();

    context.ExecuteQuery();
}

Read

private IEnumerable newLists;

var dt = new DateTime(2010, 3, 20);
var query = from list
            in clientContext.Web.Lists
            where list.Created > dt && list.Hidden == false
            select list;

newLists = clientContext.LoadQuery(query);
clientContext.ExecuteQueryAsync(onPartQuerySucceeded, onPartQueryFailed);

Verify Local Windows Account Password

I would like to verify local windows account and password through a web part in SharePoint 2010. The code snippet is show below:

protected bool verifyUserAccount(string computerName, string userName, string password)
{
    bool flag = false;
    using (PrincipalContext context = new PrincipalContext(ContextType.Machine, computerName, userName, password))
    {
        flag = context.ValidateCredentials(userName, password);
    }
    return flag;
}

Then the above snippet can really verify local windows account and password!

Update Windows Local Account Password

I would like to enable users to update their account passwords through a Web Part in SharePoint 2010.

using System.DirectoryServices;
using System.DirectoryServices.AccountManagement;

string userPath = "WinNT://computerName/userName";
DirectoryEntry userEntry = new DirectoryEntry(userPath);
object[] password = {newPassword};
object ret = userEntry.Invoke("SetPassword", password);
userEntry.CommitChanges();

userEntry.Close();

Above snippet code can really update their passwords!

Change local administrator password in C# 

Update Modified / Created in List Item

I would like to migrate a site to another SharePoint Server as a site collection. Unfortunately, I cannot export the site using "Save site as template" (many issues needed to be solved, such as MaxTemplateDocumentSize). 

Apart from "Save site as template", I can choose between site collection backup and export site. For site collection, it includes fields Modified By, Created By, Created and Modified which are absent in site (*.cmp) file. In fact, I cannot move a site up one level without importing site file(*.cmp). Here is a method to update those fields programatically:

            SPListItem item = workflowProperties.Item;
            SPUser user = workflowProperties.Web.AllUsers[@"Test\wawa"];
            SPFieldUserValue currentUser = new SPFieldUserValue(workflowProperties.Web, user.ID, user.LoginName);
            item["Author"] = currentUser;
            item["Editor"] = currentUser;
            item.UpdateOverwriteVersion();

Custom DLL in SharePoint Solution 2010

I reference a custom DLL to a SharePoint Event Receiver solution. But, the event is not triggered when adding a list item. In the log file, an error log can be found:

Could not load type 'SAFP.SharedMethods' from assembly 'SharedMethods, Version=1.0.0.0, Culture=neutral, PublicKeyToken=5e96847a5abc7fb4'

As I have deployed the DLL to GAC already and included the assembly in web.config SafeControl, I have no idea what am I missing.

After asking my friend "Google", it tells me that something should be done on package inside SharePoint Project.

1. Click Package and then click Advanced button

2. Add additonal assmeblies

 

Then I upgrade the solution and everything works fine.

Setting NFS Client in Windows 2008 R2

Here are steps needed to be configure:

(1) Server Manager-> Add Role -> File Services

Https Web Service Client

I run a Web Service Client in Java and then a exception is shown:

"PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target"

Based on the above error mesage, it seems related to SSL certificate. Then, I do the following step:

(1) Download the Web Service certificate

(2) Install the certificate

After I install the certificate to JRE, the issue is solved.

If I install the cert in Java\jre6, tomcat under eclipse runs fine.
If I install the cert in Java\jdk\jre, tomcat runs fine.

Reference:
keytool reference

Setting NFS Client in Windows 7

I need to map a network drive to a NFS Server in CentOS 5.5 and the steps are shown below:

 1. Go to Control Panel => Programs and Features => Turn Windows features on or off and enable Services for NFS

 2. If you want to connect to NFS as root, go to regedit and edit HKKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ClientForNFS\CurrentVersion\Default\RegNotify.

     AnonymousGid: root
     AnonymousUid: root

3. Restart service of "Client for NFS"

4. Map network drive

CTI Client in C#

I try set agent desktop state through a custom CTI Client in C#.

The code is shown below:

 
   public static void Main(string[] args)
        {
            Int32 port = 12028;
            String server = "127.0.0.1";

            TcpClient client = new TcpClient(server, port);
            NetworkStream stream = client.GetStream();

            byte[] message = new byte[ 8 + 17 + extension.Length + 3 + agentID.Length + 3];

            int len = 0;
            //header 
            UInt32[] length = { IntToNetworkOrder(34) };
            Buffer.BlockCopy(length, 0, message, len, sizeof(UInt32));
            len += sizeof(UInt32);

            UInt32[] msgTypeID = { IntToNetworkOrder(38) };
            Buffer.BlockCopy(msgTypeID, 0, message, len, sizeof(UInt32));
            len += sizeof(UInt32);

            //fix part (17)
            UInt32[] invokeID = { IntToNetworkOrder(100) };
            Buffer.BlockCopy(invokeID, 0, message, len, sizeof(UInt32));
            len += sizeof(UInt32);

            UInt32[] reserved1 = { IntToNetworkOrder(1) };
            Buffer.BlockCopy(reserved1, 0, message, len, sizeof(UInt32));
            len += sizeof(UInt32);

            ushort[] agentState = { ShortToNetworkOrder(3) };
            Buffer.BlockCopy(agentState, 0, message, len, sizeof(ushort));
            len += sizeof(ushort);

            ushort[] reserved2 = { ShortToNetworkOrder(0) };
            Buffer.BlockCopy(reserved2, 0, message, len, sizeof(ushort));
            len += sizeof(ushort);

            ushort[] reserved3 = { ShortToNetworkOrder(0) };
            Buffer.BlockCopy(reserved3, 0, message, len, sizeof(ushort));
            len += sizeof(ushort);

            ushort[] eventReasonCode = { ShortToNetworkOrder(32760) };
            Buffer.BlockCopy(eventReasonCode, 0, message, len, sizeof(ushort));
            len += sizeof(ushort);

            byte[] forcedFlag = new byte[] { 0 };
            Buffer.BlockCopy(forcedFlag, 0, message, len, sizeof(byte));
            len += sizeof(byte);

            //floating part
            //agent instrument
            byte[] agentInstrumentID = new byte[] { 6 };
            Buffer.BlockCopy(agentInstrumentID, 0, message, len, sizeof(byte));
            len += sizeof(byte);

            byte[] agentInstrumentLen = new byte[] { (byte)(extension.Length + 1) };
            Buffer.BlockCopy(agentInstrumentLen, 0, message, len, sizeof(byte));
            len += sizeof(byte);

            byte[] agentInstrumentData = System.Text.Encoding.ASCII.GetBytes(extension);
            Buffer.BlockCopy(agentInstrumentData, 0, message, len, agentInstrumentData.Length);
            len += agentInstrumentData.Length;

            Buffer.BlockCopy(forcedFlag, 0, message, len, sizeof(byte));
            len += sizeof(byte);

            //agent id
            byte[] agentIdID = new byte[] { 194 };
            Buffer.BlockCopy(agentIdID, 0, message, len, sizeof(byte));
            len += sizeof(byte);

            byte[] agentIdLen = new byte[] { (byte)(agentID.Length + 1) };
            Buffer.BlockCopy(agentIdLen, 0, message, len, sizeof(byte));
            len += sizeof(byte);

            byte[] agentIdData = System.Text.Encoding.ASCII.GetBytes(agentID);
            Buffer.BlockCopy(agentIdData, 0, message, len, agentIdData.Length);
            len += (agentIdData.Length);

            Buffer.BlockCopy(forcedFlag, 0, message, len, sizeof(byte));
            len += sizeof(byte);

            stream.Write(message, 0, message.Length);

            stream.Close();
            client.Close();
        }

        public static UInt32 IntToNetworkOrder(UInt32 source)
        {
            if (BitConverter.IsLittleEndian)
            {
                return (uint)(source >> 24) |
                             ((source << 8) & 0x00FF0000) |
                             ((source >> 8) & 0x0000FF00) |
                              (source << 24);
            }

            return source;
        }

        public static ushort ShortToNetworkOrder(ushort source)
        {
            if (BitConverter.IsLittleEndian)
            {
                return (ushort)(source >> 8 | source << 8);
            }
            return source;
        }

CTI Protocol: Set Agent Desktop State

I try to use CTI protocol to update state of agent desktop and download a sample client which written in C++ from  http://developer.cisco.com/c/portal/agreement?fileEntryId=147817.

Then, I run cticlient\msclient\Debug\msclient.exe with following commands:

1. Open socket

    connect -s 172.20.32.144 -n 12028

2. Set agent state ready

    setagentstatereq -s 3 -e 32760 -f 0 -p 112233 -c Testing_CSQ -d agent11  -a 2011

References:

Cisco Unified Contact Center Express CTI Protocol Developer Guide, Release 8.0(1)

Enable basic authentication in IIS

Only basic authentication is available for connecting SharePoint Rest Interface in vCenter Orchestrator. Then, I go to IIS Manager and enable basic authentication under SharePoint website. But, the authentication method does not work actually. 

However, it works fine when I disable windows authentication. For your reference, 

Reference Link: IIS Authentication Methods

PowerShell: Add-PSSnapin WebAdministration in Windows Server 2008

An error occurred when I execute Add-PSSnapin WebAdministration.

After Goggling, I discover that I forgot to install IIS 7.0 PowerShell Snap-in as the OS is Windows Server 2008. On the other hand, it is no need to install the snap-in if your OS is Windows Server 2008 R2.

Installing the IIS 7.0 PowerShell Snap-in