Joseph Michael Pesch
VP Programming

SQL Beginning of Day and End of Day (getdate() Date Only)

by 16. August 2009 21:05

declare
@ProcessDate datetime
, @ProcessDateMin datetime
, @ProcessDateMax datetime

set @ProcessDate =getdate()

set @ProcessDateMin =convert(datetime,floor(convert(float, @ProcessDate)))

set @ProcessDateMax =dateadd(hour, 23,dateadd(minute, 59,dateadd(second, 59, @ProcessDateMin)))


select @ProcessDate ProcessDate, @ProcessDateMin ProcessDateMin, @ProcessDateMax ProcessDateMax

ProcessDate             ProcessDateMin          ProcessDateMax
----------------------- ----------------------- -----------------------
2009-08-16 15:18:16.233 2009-08-16 00:00:00.000 2009-08-16 23:59:59.000

Tags: ,

SQL Server

Excel Automation Example

by 10. August 2009 12:20

EXCEL AUTOMATION SAMPLE:

using Microsoft.Office.Interop.Excel;

public partial class LoanMod_IncomeExpenseWorksheet : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
    ExcelOpenSpreadsheets(Server.MapPath("~/Excel/MAG-Financial-Statement.xls"));
  }
  /// <summary>
  /// Open the file path received in Excel. Then, open the workbook
  /// within the file. Send the workbook to the next function, the internal scan
  /// function. Will throw an exception if a file cannot be found or opened.
  /// </summary>
  private void ExcelOpenSpreadsheets(string FileName)
  {
    Application _excelApp = new Application();
    try
    {
      //
      // This mess of code opens an Excel workbook. I don't know what all
      // those arguments do, but they can be changed to influence behavior.
      //
      Workbook workBook = _excelApp.Workbooks.Open(FileName,
          Type.Missing, Type.Missing, Type.Missing, Type.Missing,
          Type.Missing, Type.Missing, Type.Missing, Type.Missing,
          Type.Missing, Type.Missing, Type.Missing, Type.Missing,
          Type.Missing, Type.Missing);

      //
      // Pass the workbook to a separate function. This new function
      // will iterate through the worksheets in the workbook.
      //
      ExcelScanIntenal(workBook);

      //
      // Clean up.
      //
      workBook.Close(false, FileName, null);
    }
    catch
    {
      //
      // Deal with exceptions.
      //
    }
  }
  /// <summary>
  /// Scan the selected Excel workbook and store the information in the cells
  /// for this workbook in an object[,] array. Then, call another method
  /// to process the data.
  /// </summary>
  private void ExcelScanIntenal(Workbook workBookIn)
  {
    //
    // Get sheet Count and store the number of sheets.
    //
    int numSheets = workBookIn.Sheets.Count;

    //
    // Iterate through the sheets. They are indexed starting at 1.
    //
    for (int sheetNum = 1; sheetNum < numSheets + 1; sheetNum++)
    {
      Worksheet sheet = (Worksheet)workBookIn.Sheets[sheetNum];
      Response.Write(sheet.Name + "<br />");
      //
      // Take the used range of the sheet. Finally, get an object array of all
      // of the cells in the sheet (their values). You can do things with those
      // values. See notes about compatibility.
      //
      Range excelRange = sheet.UsedRange;
      object[,] valueArray = (object[,])excelRange.get_Value(
          XlRangeValueDataType.xlRangeValueDefault);

      //
      // Do something with the data in the array with a custom method.
      //
      // ProcessObjects(valueArray);
    }
  }
}

Tags: ,

[None]

Workflow Foundation Error with Distributed Transaction Coordinator

by 7. August 2009 20:26

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Workflow.Runtime.Hosting.PersistenceException: Communication with the underlying transaction manager has failed. ---> System.Transactions.TransactionManagerCommunicationException: Communication with the underlying transaction manager has failed. ---> System.Runtime.InteropServices.COMException (0x80004005): Error HRESULT E_FAIL has been returned from a call to a COM component.
  at System.Transactions.Oletx.IDtcProxyShimFactory.ReceiveTransaction(UInt32 propgationTokenSize, Byte[] propgationToken, IntPtr managedIdentifier, Guid& transactionIdentifier, OletxTransactionIsolationLevel& isolationLevel, ITransactionShim& transactionShim)
  at System.Transactions.TransactionInterop.GetOletxTransactionFromTransmitterPropigationToken(Byte[] propagationToken)
  --- End of inner exception stack trace ---
  at System.Workflow.Runtime.Hosting.SqlWorkflowPersistenceService.System.Workflow.Runtime.IPendingWork.Commit(Transaction transaction, ICollection items)
  at System.Workflow.Runtime.WorkBatch.PendingWorkCollection.Commit(Transaction transaction)
  at System.Workflow.Runtime.WorkBatch.Commit(Transaction transaction)
  at System.Workflow.Runtime.VolatileResourceManager.Commit()
  at System.Workflow.Runtime.WorkflowExecutor.DoResourceManagerCommit()
  at System.Workflow.Runtime.Hosting.WorkflowCommitWorkBatchService.CommitWorkBatch(CommitWorkBatchCallback commitWorkBatchCallback)
  at System.Workflow.Runtime.Hosting.DefaultWorkflowCommitWorkBatchService.CommitWorkBatch(CommitWorkBatchCallback commitWorkBatchCallback)
  at System.Workflow.Runtime.WorkflowExecutor.CommitTransaction(Activity activityContext)
  at System.Workflow.Runtime.WorkflowExecutor.Persist(Activity dynamicActivity, Boolean unlock, Boolean needsCompensation)
  --- End of inner exception stack trace ---
  at System.Workflow.Runtime.WorkflowExecutor.Persist(Activity dynamicActivity, Boolean unlock, Boolean needsCompensation)
  at System.Workflow.Runtime.WorkflowExecutor.PerformUnloading(Boolean handleExceptions)
  at System.Workflow.Runtime.WorkflowExecutor.Unload()
  at System.Workflow.Runtime.WorkflowInstance.Unload()
  at BidMortgageWFWebService.StateMachineWF.NewSubmission(Guid LoanRequestID, Nullable`1 BiddingTimespan)
  at BidMortgageWFWebService.StateMachineWF.NewSubmissionWithBiddingTimeSpan(Guid LoanRequestID, Int32 Days, Int32 Hours, Int32 Minutes, Int32 Seconds)
  --- End of inner exception stack trace ---

To solve the error above, make sure the ports highlighted below are open (bi-directional) between the web and sql servers.

RPC server programs typically use dynamic port mappings to avoid conflicts with programs and protocols registered in the range of well-known TCP ports. RPC server programs associate their universally unique identifier (UUID) with a dynamic port and register the combination with the RPC EPM. The EPM provides a single point of contact for RPC clients. The RPC clients contact the EPM and use the server program’s UUID to determine the port being used by the server program. The following table indicates the network ports normally used by RPC.

Network Port Assignments for RPC 

Service Name UDP TCP
HTTP 80, 443, 593 80, 443, 593
Named Pipes 445 445
RPC Endpoint Mapper 135 135
RPC Server Programs <Dynamically assigned> 1024-5000 <Dynamically assigned> 1024-5000

Prior to the above error the errors below were occuring due to the lack of configuration settings shown below.

Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool.

NOTE: When enabling settings shown below on the web server only (i.e. before performing same setting on sql server) I got the following error:

The partner transaction manager has disabled its support for remote/network transactions.

Steps to change settings

On Windows Server 2003 SP1 and Windows XP SP2:

1.     Click Start, click Run, and type dcomcnfg to launch the Component Services Management console.

2.     Click to expand Component Services and click to expand Computers.

3.     Right-click My Computer, and click Properties.

4.     Click the MSDTC tab of the My Computer Properties dialog and click the Security Configuration button to display the Security Configuration dialog box.

On Windows Server 2008 and Windows Vista:

1.     Click Start, click Run, and type dcomcnfg to launch the Component Services Management console.

2.     Click to expand Component Services and click to expand Computers.

3.     Click to expand My Computer, click to expand Distributed Transaction Coordinator, right-click Local DTC, and click Properties.

4.    Click the Security tab of the Local DTC Properties dialog. 

WEB SERVER 2008 

  

 

SQL SERVER 2003 

The following is from this link: http://support.microsoft.com/kb/250367 

You can configure DTC to communicate through firewalls, including network address translation firewalls.

DTC uses Remote Procedure Call (RPC) dynamic port allocation. By default, RPC dynamic port allocation randomly selects port numbers above 1024. By modifying the registry, you can control which ports RPC dynamically allocates for incoming communication. You can then configure your firewall to confine incoming external communication to only those ports and port 135 (the RPC Endpoint Mapper port).

You must provide one incoming dynamic port for DTC. You may need to provide additional incoming dynamic ports for other subsystems that rely on RPC.

The registry keys and values described in this article do not appear in the registry by default; you must add them by using Registry Editor.

Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:

322756  (http://support.microsoft.com/kb/322756/ ) How to back up and restore the registry in Windows


Follow these steps to control RPC dynamic port allocation. You will have to do this on both computers. Note also that the firewall mustbe open in both directions for the specified ports:

  1. To start Registry Editor, click Start, click Run, type regedt32, and then click OK.

    You must use Regedt32.exe, rather than Regedit.exe, because Regedit.exe does not support the REG_MULTI_SZ data type that is required for the Ports value.
  2. In Registry Editor, click HKEY_LOCAL_MACHINE in the Local Machine window.
  3. Expand the tree by double-clicking the folders named in the following path:
    HKEY_LOCAL_MACHINE\Software\Microsoft\Rpc
  4. Click the RPC folder, and then click Add Key on the Edit menu.
  5. In the Add Key dialog box, in the Key Name box, type Internet, and then click OK.
  6. Click the Internet folder, and then click Add Value on the Edit menu.
  7. In the Add Value dialog box, in the Value Name box, type Ports.
  8. In the Data Type box, select REG_MULTI_SZ, and then click OK.
  9. In the Multi-String Editor dialog box, in the Data box, specify the port or ports you want RPC to use for dynamic port allocation, and then click OK.

    Each string value you type specifies either a single port or an inclusive range of ports. For example, to open port 5000, specify "5000" without the quotation marks. To open ports 5000 to 5020 inclusive, specify "5000-5020" without the quotation marks. You can specify multiple ports or ports ranges by specifying one port or port range per line. All ports must be in the range of 1024 to 65535. If any port is outside this range or if any string is invalid, RPC will treat the entire configuration as invalid.

    Microsoft recommends that you open up ports from 5000 and up, and that you open a minimum of 15 to 20 ports.
  10. Follow steps 6 through 9 to add another key for Internet, by using the following values:
    Value: PortsInternetAvailable
    Data Type: REG_SZ
    Data: Y
    This signifies that the ports listed under the Ports value are to be made Internet-available.
  11. Follow steps 6 through 9 to add another key for Internet, by using the following values:
    Value: UseInternetPorts
    Data Type: REG_SZ
    Data: Y
    This signifies that RPC should dynamically assign ports from the list of Internet ports.
  12. Configure your firewall to allow incoming access to the specified dynamic ports and to port 135 (the RPC Endpoint Mapper port).
  13. Restart the computer. When RPC restarts, it will assign incoming ports dynamically, based on the registry values that you have specified. For example, to open ports 5000 through 5020 inclusive, create the following named values:
    Ports : REG_MULTI-SZ : 5000-5020
    PortsInternetAvailable : REG_SZ : Y
    UseInternetPorts : REG_SZ : Y

DTC also requires that you are able to resolve computer names by way of NetBIOS or DNS. You can test whether or not NetBIOS can resolve the names by using ping and the server name. The client computer must be able to resolve the name of the server, and the server must be be able to resolve the name of the client. If NetBIOS cannot resolve the names, you can add entries to the LMHOSTS files on the computers.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

217351  (http://support.microsoft.com/kb/217351/ ) DCOM port range configuration problems

For more information about LMHOSTS files, click the following article number to view the article in the Microsoft Knowledge Base:

102725  (http://support.microsoft.com/kb/102725/ ) LMHOSTS file information and predefined keywords

Tags: ,

ASP.Net | Windows Communication Foundation

Javascript Countdown Timer Example

by 7. August 2009 15:47

<html>
<head>

<style type="text/css">
  body { font-size: .85em; }
  body,input,div { font-family: tahoma, verdana, arial; }
  div.Countdown { background-color: #fafad2; width: 450px; }
  #content { width: 475px; margin-left: auto; margin-right: auto; text-align: center; }
</style>

<script id="CountdownScript" type="text/javascript">
  function SetDefaultEnd() {
    var dt = new Date();
    var tm = dt.getTime();
    tm += 3600 * 1000 * 48;
    dt.setTime(tm);
    document.getElementById('txtEnd').value = dt;
  }
  function StartCountdown() {
    if (document.getElementById('txtTimer').innerHTML == 'Time is up')
      return;
    var expiry = new Date(document.getElementById('txtEnd').value);
    setTimeout('Countdown(' + expiry.getTime() + ')', 1000);
  }
  function Countdown(end) {
    var now = new Date();
    var span = (end - now.getTime()) / 1000;
    if (span <= 0) {
      // Countdown finished...
      document.getElementById('txtTimer').innerHTML = 'Time is up';
      return;
    }
    var days = Math.floor(span / 86400)
    var hours = Math.floor(span % 86400 / 3600);
    var mins = Math.floor(span % 86400 % 3600 / 60);
    var secs = Math.floor(span % 86400 % 3600 % 60);
    var d = (((parseInt(days) > 0) ? days + ' day' + ((parseInt(days) != 1) ? 's ' : ' ') : ''));
    var h = (((parseInt(hours) > 0) ? hours + ' hour' + ((parseInt(hours) != 1) ? 's ' : ' ') : ''));
    var m = mins + ' minutes ';
    var s = secs + ' seconds ';
    msg = 'Time is up in: ' + d + h + m + s;
    document.getElementById('txtTimer').innerHTML = msg;
    setTimeout('Countdown(' + end + ')', 1000);
  }
</script>


</head>

<body onload="SetDefaultEnd();">

<div id="content">
  Enter End Date:
  <input type="text" id="txtEnd" style="width: 200px; text-align: center;"" />
  <input type="button" id="btnStart" onclick="StartCountdown();" value="Start Countdown" />
  <div class="Countdown" id="txtTimer"></div>
</div>

</body>
</html>

Tags: ,

[None]

SQL Audit Table Methodology

by 6. August 2009 21:35

Tags: ,

SQL Server

VS2008 Icons

by 6. August 2009 06:39

C:\Program Files\Microsoft Visual Studio 9.0\Common7\VS2008ImageLibrary\1033\VS2008ImageLibrary.zip

 

Tags: ,

[None]