2008-09-18

IIS hosting

TODO:
  1. From a PC use the Windows Professional's IIS to host an .aspx page that uses SQL Server Express 2005.
    1. boutell.com/newfaq/creating/hostmyown.html
    2. Include some notes & directions howto:
      1. Open up the IIS window on the PC by doing so: >My Computer >Manage >Services and Applications >IIS
      2. >Local Disk E: >Inetpub >ftproot (Go here to check the stuff that gets hosted (Thus FTP to here).)
      3. Then go to http://localhost/ in a browser's address bar to view the test .aspx page
      4. Etc...
  2. Take the dynamic IP of our IIS and put it in the browser's address bar to test. (Ex. http://192.168.254.2/Default.aspx)
  3. Get a static IP from DynDNS.com and add it to the IP input field in mydomainname.com's GoDaddy dashboard. Thus mydomainname.com with GoDaddy will point to the IP of our IIS server hosting PC.
  4. We are getting an error when our page includes a database stuff: aspspider.com/resources/Resource170.aspx
  5. Add mydomainname.com to here domains.live.com (What this service does, I do not know yet.)
Much later TODO:
  1. Set up a mail-server on the said IIS (or activate it if one is included with it)
  2. Take the DNS settings from Hotmail and add them so email messages to mydomainname.com's domain name get sent to his said domain name (ex. bob@mydomainname.com).
  3. MX (Mail Exchange) records are used in DNS records (or Zone files) to specify how email should be routed.
Extras
  1. With IIS7 Rewrite module a website can have a .htaccess file. See hanselman.com/blog/ASPNETMVCAndTheNewIIS7RewriteModule.aspx.

2008-07-11

VB: Regular Expressions, etc...

Regex.IsMatch("subject", "regex") ' Checks if the regular expression 
' matches the subject string.

VB: using Controls collection

' Clears textbox controls' content on the form using 
' Controls collection.
Dim intX As Integer
Do While intX < text = "" intx =" intX">

ViewState("UrlReferrer")

Sub Page_Load()
    If Page.IsPostBack = False Then
        ' Store URL Referrer to return to home page.
        ViewState("UrlReferrer") = Me.Request.UrlReferrer.ToString()
    End If
End Sub

Sub CancelBtn_Click()
    ' Redirect back to the home page.
    Me.Response.Redirect(CType(ViewState("UrlReferrer"), String))
End Sub

ASP confirm

 gt-a-s-p:button id="ConfirmOnClick" runat="server" onclientclick="return
confirm('You\'re sure you want to do this?');" text="Launch Airstrike"> lt/ a-s-p:button> 

ASP .NET set focus to a control (javascript)

body onload="javascript:document.forms[0].txtFirst.focus();"

2008-07-10

check for DbNull

Check for DbNull:
<_a s p :Label runat="server" ID="Label6" Text='< % # IIF(Typeof(
Eval("ShippedDate")) IS DbNull,"No Date",Eval("ShippedDate")) % >' />
or
<_%# ((Convert.IsDBNull ... I am telling ASP to give the picture a height value only if the value in the database is not NULL. I could've done this in the SQL instruction, but I decided to do it inside the Datalist.
<_%# ((Convert.IsDBNull(DataBinder.Eval(Container.DataI tem, "height"))) ? "" : "height = " + DataBinder.Eval(Container.DataItem, "height")) %> 
Or maybe this for NULLs?: <_%# Eval("First", "{0}, ") %_>
Or check for NULL in T-SQL:
ISNULL(check_expression, replacement_value) If 1st is NULL, return 2cnd.

2008-07-03

Search Stored Procedures

This searches all the Stored Procedures in 1 SQL Server database for the said string. I tested it in Query Analyzer and it found all the Stored Procedures that had occurrences of my "lq_Campaign" in 1 database:
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%lq_Campaign%'
AND ROUTINE_TYPE='PROCEDURE'

Here is another way

Below query you can searcg any table, stored procedure or views what ever has that expression as a column or object name.

SELECT * FROM SYSOBJECTS WHERE ID IN (SELECT ID FROM SYSCOMMENTS WHERE TEXT LIKE ‘%PRODUC%’)

The query produced all the object names--where ever it finds expression like ‘PRODUC‘.

Model View Controller

  • Primary goal is to separate the things that change from the things that do not.
  • Model is the data representation.
  • View is the presentation of the model.
  • Controller responds to events and modifies the model.
  • Model notifies the views to update the presentation.

2008-05-09

SQL Server 2005 Reports: tips

SSRS:
  • Page Header:
    • TextBox via Expression...: =Globals.ReportFolder & Globals.ReportName
  • Page Footer:
    • Page Footer: TextBox via Expression...: =Format(Globals!PageNumber) & " of " & Format(Globals!TotalPages) & " pages"
    • ="Printed by " & User!UserID & " on " & DateTime.Now.ToString()
    • ="Execution Time: " & IIF(System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds < 1, "0 seconds", ( IIF(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours & " hour(s), ", "") & IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes & " minute(s), ", "") & IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds & " second(s)", "")) )
  • Body:
    • TextBox via Expression...: =Format(Sum(Fields!Account.Value), "C0") (Works if column is numeric data type.)
    • Alternate rows by adding the following to the row via Properties > BackgroundColor expression of your detail row: =IIF(RowNumber(Nothing) Mod 2, "White", "Gainsboro")

2008-03-28

SSIS > VS Export wizard: table to Excel

After using the SSIS > VS Export wizard for table to Excel, you must add DROP TABLE Query to the Event Handlers tab or else it will error the second time it is reRun. SSIS

SSIS > VS Import wizard for Excel to table:

  • Be sure to only checkmark Results$.
  • Be sure to Select Delete table option.
  • Be sure to Change Results$ name to Destination table name.
  • If there is an error, it is often because a row in Excel has a NULL but the table column is NOT NULL (so add a blank to the Excel field).

2008-03-07

ReplaceCommasWithPipes.vbs

' Currently this VBScript is set to replace-commas-with-pipes for every file that is 
'in the folder where this VBScript sits:
' Before run it do the following:
' 1. Open VBScript in text editor
' 2. Set the parameters:
'        cFolder_Name = "." ' current directory
'        cOnlyOneFile = False ' set to True if I want to process only one file with name set below
'        cOnlyFileName = "thisfile.csv"
' 3. Place the VBScript into the target folder
' 4. Run it by double-click
' Notes: http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/79ddd204ebb88287/be5d195da9241df9%23be5d195da9241df9
' -------------------------------------------------------
On Error Resume Next

const cFolder_Name = "."  ' current directory
const cOnlyOneFile = False ' False, So all files in the folder.
const cOnlyFileName = "thisfile.csv" ' Uses this if the above was True (only wanted to process 1 file).

Set oFSO = CreateObject("Scripting.FileSystemObject")

' Get folder:
Set oFolder = oFSO.GetFolder(cFolder_Name)
If oFolder Is Nothing Or Err.Number <> 0 Then
  MsgBox "Can't get a folder for search files. " + vbCRLF + "Please check the folder name." + vbCRLF + Err.Description, vbCritical, "Fatal error!" ' I will probably comment this out when this VBScript runs automatically as a scheduled job.
End If

Set oFiles = oFolder.Files
If Err.Number <> 0 Or oFiles Is Nothing Then
  MsgBox "Can't get a list of files of folder." + vbCRLF + "" + vbCRLF + Err.Description, vbCritical, "Fatal error!"                               ' I will probably comment this out when this VBScript runs automatically as a scheduled job.
End If

Cnt = CInt(0)
For Each oFile In oFiles
  If oFSO.GetExtensionName(oFile.Name) = "csv" Then ' Checks for "csv" file extension.
    If (cOnlyOneFile And (oFile.Name = cOnlyFileName)) Or (Not cOnlyOneFile) Then
      Call ReplaceSymbols(oFile.Name) 
      Cnt = Cnt + 1
    End If
  End If
Next

Senmail()

MsgBox "Replacing is done. Total number of files had been processed: " + CStr(Cnt), vbExclamation, "Message..."                                    ' I will probably comment this out when this VBScript runs automatically as a scheduled job.
' cleanup
Set oFiles = Nothing 
Set oFSO = Nothing

' Find-and-replace:
Sub ReplaceSymbols(oFileName) ' AKA "File.Name".
  If oFSO.FileExists(oFileName) Then 
    Set oTextFile = oFSO.OpenTextFile(oFileName, 1, False) 
    sFileContents = oTextFile.ReadAll 
    oTextFile.Close 

    Set oRegEx = CreateObject("VBScript.RegExp") 
    With oRegEx 
      .Global = True 
      .IgnoreCase = False 
      .Pattern = "," ' Or vbTab or "|" etc...
      sFileContents = .Replace(sFileContents, "|") 
'      .Pattern = "\|"                               ' reverse replacing
'      sFileContents = .Replace(sFileContents, ",") 
    End With 

    Set oNewFile = oFSO.CreateTextFile(oFileName, True) ' Maybe oNewFile is not needed--it just could have been called oTextFile.
    oNewFile.Write sFileContents 
    oNewFile.Close 
  End If
End Sub


' NOTE: This first way would only work on my PC, not on the other user's PCs; so I must use the second snippet, Senmail(), at the bottom. NOTE: If I made the changes marked "future testing" this might work.
' Send email to Jon Doe. --If the PC this runs on is not set right, this email will not get sent. 
'Set oMessage = CreateObject("CDO.Message") 
'oMessage.From     = "Generated automatically for AWARE " ' NOTE: If future testing: Comment this out.  
'oMessage.To       = "jon.doe@jd.com,jon.doe2@jd.com"       ' NOTE: If future testing: Should be ";".                                      
'oMessage.Subject  = "AWARE: Extracts done."       
'oMessage.Sender   = "jon.doe@jd.com"                       ' NOTE: If future testing: Comment this out.                                               
'oMessage.TextBody = "AWARE: Commas replaced with pipes." 
'oMessage.Send

' NOTE : Is using MS Outlook API to send e-mails. So check If MS Outlook installed on a workstation before a using of this script.
Sub Senmail()
  Set objOutlook = CreateObject("Outlook.Application")
  Set objOutlookMsg = objOutlook.CreateItem(0)
  With objOutlookMsg
' This line caused it not to even popup warning, so leave it off:   .From     = "jon.doe@jd.com "   ' "Generated automatically for AWARE "
     .To = "jon.doe@jd.com; jon.doe2@jd.com; jon.doe3@jd.com; jon.doe4@jd.com"   
     .Subject = "AWARE: Extracts done."
' This line caused it not to even popup warning, so leave it off:   .Sender   = "jon.doe@jd.com"   ' NOTE: jon.doe2's email is automatically inserted here because it is sent from here Outlook.
     .Body = "AWARE: Commas replaced with pipes. [Note: This message was generated and sent automatically.]"
     .Send
  End With
  Set objOutlookMsg = Nothing
  Set objOutlook = Nothing
End Sub

2008-03-06

Create SSIS to copy tables

An SSIS to copy tables from a Source to a Destination. Directions:

  1. From Start, select SQL Server Business Intelligence Development Studio
  2. File
    1. New Project…
      1. Project types: Business Intelligence Projects
      2. Integration Services Project…
      3. Click “OK
  3. Toolbox
    1. Drag "Transfer SQL Server Objects Task" to "Control Flow" tab's pane.
      1. Right-click choose Edit…
        1. Objects
          1. SourceConnection ‹New connection…› = NWDSQL
          2. SourceDatabase = NIS_empl_wage
          3. DestinationConnection ‹New connection…› = TESTSQL
          4. DestinationDatabase = NIS_empl_wage
          5. CopyData = True
          6. ExistingData = Replace (so not append (dup errors))
          7. ObjectsToCopy (expand)
            1. CopyAllTables = True (so not sprocs)
          8. Note: Leave all other options False. Ex., Table Options—Can leave all these False because it will just move data so Destination’s Primary Keys will remain.
      2. Click “OK
  4. Save and Run

Note 1: Your Properties for the Transfer SQL Server Objects Task will now look like this:

  1. CopyAllTables = True (so not sprocs)
  2. CopyData = True
  3. DestinationConnection = TESTSQL
  4. DestinationDatabase = NIS_empl_wage
  5. ExistingData = Replace (so not append (dup errors))
  6. SourceConnection = NWDSQL
  7. SourceDatabase = NIS_empl_wage

Note 2: A DB "restore" by the DBA would nix Destination tables that do not exist in Source tables; but not this technique.

2008-02-21

CASE: This way (3) NULLs get put in tbl if data is blank.

command = New SqlCommand("INSERT INTO tblFoo (UIAcctNum, WorkZip, CheckRouteDesc, Agency, Division, RUN, Rpt_Unit_RUNDesc, StartDate,EndDate ,CheckRouteCode, Rpt_Unit_RUNDesc__old,NAICS) " & _ 
" VALUES (@UIAcctNum, @WorkZip, @CheckRouteDesc, @Agency, @Division, @RUN, " & _ 
"CASE WHEN ltrim(@Rpt_Unit_RUNDesc) = '' THEN NULL ELSE @Rpt_Unit_RUNDesc END, " & _ 
"@StartDate, " & _ 
"CASE WHEN ltrim(@EndDate) = '' THEN NULL ELSE @EndDate END, " & _ 
"@CheckRouteCode, " & _ 
"CASE WHEN ltrim(@Rpt_Unit_RUNDesc__old) = '' THEN NULL ELSE @Rpt_Unit_RUNDesc__old END, " & _ 
" CASE WHEN ltrim(@NAICS) = '' THEN NULL ELSE @NAICS END)", connection) ' This way (3) NULLs get put in tbl if data is blank.