Tech In4mr

Work Item Visualizer for TFS 2010 - Visual Studio Gallery

2012-02-08T19:17:00.000-08:00

Work Item Visualizer for TFS 2010 - Visual Studio Gallery

This is a quick and clean way to visualize your work item hierarchy and task dependencies from within Visual Studio 2010. Mike Parks & Cory Cissell leveraged examples from the source code of the Codeplex project Work Item Visualization to develop this handy VS2010 add-in.

Work Item Visualization add-in screenshot follows:

Export All Assemblies From Sql Server

2012-02-01T16:13:00.000-08:00

This script pulls all the assemblies from your Sql Server database and saves them locally.
Standing on the shoulders of giants I used the core script found at http://social.msdn.microsoft.com/Forums/en/sqlnetfx/thread/1e00e656-f322-45ec-b4e0-83db748fa97a as posted by Jian Kang

-- ===========================================================================
-- Author: D.R.Bowden
-- Create date: 20120201
-- Description: Export All Assemblies From Sql Server Database
-- ============================================================================
execute sp_configure 'show advanced options', 1;
go
reconfigure;
go
execute sp_configure 'Ole Automation Procedures', 1;
go
-- set the output path --
declare @LocalPath as varchar (max),
@SQL as varchar (max);

select @LocalPath = 'C:\temp\build',
@SQL = '';

-- ignore the microsoft.sqlserver.types.dll --
select @SQL = @SQL + '

SELECT @IMG_PATH = content FROM sys.assembly_files WHERE assembly_id = ' + cast (assembly_id as varchar (255)) + '

EXEC sp_OACreate ''ADODB.Stream'', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, ''Type'', 1
EXEC sp_OAMethod @ObjectToken, ''Open''
EXEC sp_OAMethod @ObjectToken, ''Write'', NULL, @IMG_PATH
EXEC sp_OAMethod @ObjectToken, ''SaveToFile'', NULL, ''' + @LocalPath + '\' + REPLACE([name], '.dll', '') + '.dll'', 2
EXEC sp_OAMethod @ObjectToken, ''Close''
EXEC sp_OADestroy @ObjectToken

PRINT ''EXPORTED ASSEMBLY: ' + [name] + ' to ' + @LocalPath + '\' + REPLACE([name], '.dll', '') + '.dll''
'
from sys.assembly_files
where assembly_id > 1;

set @SQL = 'DECLARE @ObjectToken INT, @IMG_PATH VARBINARY(MAX)
' + @SQL;

execute (@SQL);

Simple TF.exe example to add, checkin and delete a file in TFS from the commmand line

2011-05-04T13:07:00.001-07:00

I was asked a question earlier today and I thought it might be useful to share the answer.

“.. I am trying to find a way to check in artifacts into TFS via command line utilities. I have found a ton of documents on “tf”, but can not seem to locate that binary, any quick thoughts”

Below are the contents of a quick example bat file:

@Echo OFF

REM My root workspace is c:\-x-\_TFS_

set tfPath = "C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\tf.exe"

cd c:\-x-\_TFS_\Test\

dir ..\..\*.* >> test.txt

"C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\tf.exe" add test.txt /lock:none

"C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\tf.exe" checkin /comment:"just a demo of tf.exe" /noprompt .\test.txt

"C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\tf.exe" delete test.txt /lock:none

"C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\tf.exe" checkin /comment:"just cleaning up a demo of tf.exe" .\test.txt

Incremental SQL Deploy: When a schema change might cause data in a table to be lost

2011-03-23T06:39:00.000-07:00

Why does a SQL table change in a VS2010 DB Project raise the “The schema update is terminating because data loss might occur” deployment error?

This question has come up a lot lately…

Get acquainted with the “Block incremental deployment if data loss might occur”

SQL deployment property

1. “When you deploy changes to an existing database, some changes could cause data loss. If a change might cause data in a table to be lost, the deployment will be canceled if the Block incremental deployment if data loss might occur check box is selected. By default, this check box is selected, but you can find it in the Properties window for your project. For more information, see An Overview of Database Project Settings.” [1]

Changes that can raise the “The schema update is terminating because data loss might occur.” error

1. “The following kinds of changes will cause data loss: if a table is dropped and recreated, if you change the size of a column (char(100) to char(50) or nchar(100) to char(100)), or if you change the collation of a column that has a character type.“ [1]

2. “…, if you have renamed your objects or columns at the source you might incur data loss while updating the target.” [2]

Avoiding the “The schema update is terminating because data loss might occur.” error

1. Table Renaming: When you use refactoring to rename a database object or to move a database object to another schema, the refactoring log file records that action. At deployment time, the information in the log file helps preserve the intent of your changes. For example, you might lose data if you renamed a table, because the table would be dropped and a table would be created with the new name. With the refactoring log file, the deployment script can instead rename the table, preserving your intent and your data. [1]

Note: To perform an table renaming using database refactoring [In4mr]

1. select the table in the solution explorer

2. On the File Menu select Data à Refactor à Rename

3. This will update all non-dynamic SQL references in the project as well. Dynamic SQL will have to be updated manually or via find and replace

2. Script data before renaming columns: As long as your source has new objects there is nothing to worried about, but if you have renamed your objects or columns at the source you might incur data loss while updating the target. For example in the above schema comparison result image you can see the EmployeeID column has been renamed to BusinessEntityID, so while updating the target it will drop the EmployeeID column and add the BusinessEntityID column which will have no data even though the EmployeeID column has data in it. [2]

3. Column Renaming: Column renaming can be done by refactoring as well, but the process id slightly different. [in4mr]

Note: To perform an table renaming using database refactoring

1. At the top right corner of the Solution Explorer panel click the Schema View button.

Schema View (Far Right)

2. Browse to the table containing the column

3. Expand the columns node

4. Right click on the column to be renamed

5. Select Refactor à Rename

6. This will update all non-dynamic SQL references in the project as well. Dynamic SQL will have to be updated manually or via find and replace

4. Adding Columns: Adding a column to a table's existing column list will not cause data loss. [infmr]

References:

1. An Overview of Database Build and Deployment

http://msdn.microsoft.com/en-us/library/aa833165.aspx

MSDN , July 2010

2. Schema Compare in VS 2010

http://www.mssqltips.com/tip.asp?tip=2089

Arshad Ali , 8/12/2010

I hope this is helpful,

In4mr

my@in4mr.com

Marcus Aurelius played first edition D&D

2011-03-17T16:23:00.000-07:00

Imagine playing Dungeons and Dragons in the 2nd century or as Anais put it playing D&D in the time of D&D.

A ROMAN GLASS GAMING DIE | CIRCA 2ND CENTURY A.D. | Christie's

"Execute As" clause with a CLR based procedure

2011-03-07T10:10:00.000-08:00

If you have ever tried to write a file to a network share from sql server with a clr procedure using EXECUTE AS '' then you have learned true frustration.
Almost undocumented is that the only domain accounts that will actually be used for CLR pass trhough impersonation are CALLER, OWNER, and SELF.
To use these the dbo itself must be the account being used for access.

"Execute As" clause with a CLR based procedure

The other option is to use windows impersonation inside of your CLR class.

Delete those pesky orphan SQL Server Jobs

2010-12-01T23:07:00.000-08:00

CREATEPROCEDURE [dbo].[sputil_DeleteJobsLike_lite] (@jobnameVarchar(255),@jobsdeleted int =null output)

--============================================================================

--Author: drb

--Create date: 12/01/2009

--Description: deletes jobs that names match a string

--Note: @dbName includes the schema

/*USAGE

DECLARE@_deleted int

SET@_deleted =0

EXECUTE[sputil_DeleteJobsLike_lite] @jobname='QRY_7_',@jobsdeleted=@_deleted output

SELECT@_deleted [Jobs Deleted]

--============================================================================

BEGIN

DECLARE@SQL VARCHAR(MAX)

SET@SQL = ''

SELECT@jobsdeleted = ISNULL(COUNT(1),0) FROM msdb.dbo.sysjobs (nolock) where namelike '%'+@jobname+'%'

SELECT@SQL =

@SQL+ 'EXEC msdb..sp_delete_job @job_name = N'''+[name]+''';

'+CHAR(10)+CHAR(13)

FROM msdb.dbo.sysjobs

WHERE namelike '%'+@jobname+'%'

IF(RTRIM(@SQL)<>'')

EXEC(@SQL)

END

This is the "lite" version. The full version has contextual logging, verbosity level, parent transaction tracking and a WhatIf mode.
If that sounds interesting let me know and I will put up that framework.

Hubris, humility and data modelling

2010-10-14T20:59:00.001-07:00

Humility is similar to weight loss in that sometimes you have to convince yourself it exists before it will.

Sometimes you have to assume you are wrong even when you don't think you are in order to see what you missed. This can be tough as all of us live in firmly developed personal paradigms. Extricating ourselves from these to view the known as if it were unknown, or another known, is rarely some thing we consider without the assistance of a strong catalyst.

Today I had to step back and consider all the possible reasons for a group of intelligent people to design a data warehouse in a manner that went against all my personal experience. I had up until now believed that I was missing a peice of the puzzle which they possessed. Try as I might though I could not see beyond my own world definitions.

Biting my tongue and hubris I began my research. Beginning with the architecture reference sited for the design, Kimball. Here I found the source, not the solution, of the differences in approach that first caught my eye. You see I was raised and spoonfed Inmon models as a sophmore data waterhouse designer at intel. This strange unfamiliar design most definately rooted in kimball's methodology.

This does not begin to address the irregularities and what appeared to be contrary patterns in the dimensional model I had seen. It does however indicate that both approaches are valid and not only that, but equally so.
Exhibit 1. kimball and inmon dw models
To be cont...

TFS Deployer in TFS 2010

2010-10-06T00:58:00.000-07:00

This configuration took a while to put together and debug, but once in place it is an incredibly powerful and flexible system.
What follows is the core workflow I implemented for build quality driven deployment.

The process works like this:
1. A “solutions” directory is placed in the root of the team project and a <projectname>.<environment>.tfsbuild.sln (i.e. qmail.dev.tfsbuild.sln) is placed in it.
2. A BuildPrep (or PreBuild) and a PostBuild project are added to the .tfsbuild.sln. These handle preparation and cleanup tasks.
3. A continuous integration build is configured for this solution file.
4. A check in takes place
5. The solution’s BuildPrep project kicks off the following steps:
a. The latest Common.dll and Common.Providers.dll are copied from file://mytfs/common to the projects ReferenceAssemblies folder
b. Updates a SharedAssemblyVersion file with a Major.Minor.Build.Rev formatted and incremented build number. Then copies it to each project in the solution. This keeps grouped DLLs on the same version number.
6. The projects are compiled
7. If this is a release build then the DLLs are StrongNamed
8. The build output is copied to file://mytfs/TFS/Builds
9. A build notification is sent out to the TFSSubscribers@mydom.com distribution list.
10. At this time the Build Quality is “Unassigned”
11. A Developer or BuildMaster reviews the build and assigns a Build Quality. Typically the first assignment is “Ready for Initial Test”.
12. This change fires a BuildQualityChange Event that TFSDeployer is notified of.
13. TFS Deployer then Checks out the BuildProcessTemplates\Deployment scripts for the build that fired the event. These include the DeploymentMappings.xml and the various PowerShell task automation scripts.
14. The event is processed by using rules laid out in the DeploymentMappings.xml document attached. (snippet below)

<Mapping BuildDefinitionPattern="^Dev\.PEKContentService.*$"
Computer="myTFS"
OriginalQuality="*"
NewQuality="Ready for Initial Test"
Script="Dev.PEKContentService-Any2RFIT.ps1"
RunnerType="PowerShell"
NotificationAddress="TFSSubscribers@mydom.com">
<ScriptParameter name="DevelopmentServerName" value="myWEB.myDOM.COM" />
<ScriptParameter name="DevelopmentServerIP" value="10.101.244.106" />
<ScriptParameter name="QAServerName" value="MWEB.myDOM.COM" />
<ScriptParameter name="QAServerIP" value="10.101.244.174" />
<ScriptParameter name="ProdServerName" value="HOSA-P-WEB01-CD" />
<ScriptParameter name="ProdServerIP" value="10.180.28.50" />
</Mapping>

15. Based on the DeploymentMappings rules and the BuildQualityChange event properties the designated powershell script is run. In this case that is “Dev.PEKContentService-Any2RFIT.ps1” (snippet below)

if ($DevelopmentServerName -eq "myWeb") { Write-Output "Script parameter received! Publishing Common.dll to file://$developmentservername/Common/" ; }
#get build variables
$tproj = $TfsDeployerBuildDetail.TeamProject
$bquality = $TfsDeployerBuildDetail.Quality
$dloc = if($TfsDeployerBuildDetail){$TfsDeployerBuildDetail.DropLocation}else{""}
...
#initialize build log
$TFSDEployerLog = "file://mytfs/TFSBUILDS/Log.txt"
$date = ( get-date ).ToString('yyyyMMdd hh:mm:ss')
#write Release Notes
"This is a build notification that a $tproj build has been upgraded to (Ready For Initial Test) on..." >> $TFSDeployerLog
#Register Destination Variables
$Destination = "file://$developmentservername/wwwroot.dev/"
$DestinationPEKWebsiteBin = "file://$developmentservername/wwwroot.dev/PekContentService/bin/"
$DestinationPEKWebsiteBin = "file://$developmentservername/wwwroot.dev/PekContentTestHarness/"
...
#Register Source Variables
$SourceDlls = "$dropLocation\*.dll"
...
$Source="$dropLocation\_PublishedWebsites\"
#publish files and directory structure
Get-ChildItem -Path $Source
Copy-Item -Destination $Destination -Recurse -Force
Get-ChildItem -Path $dropLocation\actn.Lib.Security.dll
Copy-Item -Destination $DestinationPEKWebsiteBin -Force
...
#zip up test form
Get-ChildItem -Path $dropLocation\*.TestForm*
Copy-Item -Destination $DestinationTestHarness -Force
Get-ChildItem -Path $dropLocation\ComponentSpace*
Copy-Item -Destination $DestinationTestHarness -Force
dir DestinationTestHarness
add-Zip $dropLocation\PursuitListTestHarness.zip
#configuration and other follow-up tasks are scripted here…

16. When this script completes the code has been deployed to Dev and can be tested for integration issues by the developers.
17. Then they are comfortable the Developers, a buildmaster, a QA person or a Business Analyst can update the build quality to "Initial Test Passed"
18. This will trigger another event, and the TFSDeployer rules engine will invoke the corresponding script which then deploys and configures the application in the associated environment.

My favorite time tracker just hit #1

2010-07-11T07:28:00.000-07:00

Toggl Blog » Toggl is number 1! :)
Freelanceswitch just published a blogpost on top50 applications for PC users. Toggl was positioned as #1. Yay!

Don't forget... It has IPhone and Android Apps

WCF 4.0 Runtime Endpoint Discovery

2010-02-17T10:08:00.000-08:00

WCF 4.0 Runtime Endpoint Discovery

Based on WS-Discovery it supports both Managed (centralized service registration) and add-hoc (via udp milti-casting) models. The add-hoc model interested me because of its use of UDP multi-casting.
Essentially a Service broadcasts Hello and Bye messages announce availability , while Clients can broadcast Probe and Resolve messages which are used to query available services. In WCF 4.0 the broadcasting is done via a Service Behavior using a UdpDiscoveryEndPoint() and a UdpAnnouncementEndPoint(), an abstraction from the UDP multi-cast messaging implementation.
As always with multicasting there is the concern of increased network chatter, fortunately the message definitions are terse by design.
See this simple and direct article courtesy of Jesus Rodriguez MVP for more detail : Using WS-Discovery in WCF 4.0 - Jesus Rodriguez's WebLog

well known TCP Ports

2010-02-17T09:56:00.000-08:00

below is a list of well known TCP Ports

If you are ever curious , or find yourself needing an open port to map, the Internet Assigned Numbers Authority definitive port list is an excellent reference.

TCP port numbers between 1 and 1023 are considered Well Known, ports numbered between 1,024 and 49,151 are publicly Registered and finally ports 49152-65535 are dynamic.
For more information on Registered TCP Ports see the Internet Assigned Numbers Authority definitive port list .

Port Description Status
1/TCP,UDP TCP Port Service Multiplexer Official
2/TCP,UDP Management Utility Official
3/TCP,UDP Compression Process Official
4/TCP,UDP Unassigned Official
5/TCP,UDP Remote Job Entry Official
6/TCP,UDP Unassigned Official
7/TCP,UDP Echo Official
8/TCP,UDP Unassigned Official
9/TCP,UDP Discard Official
11/TCP,UDP Active Users Official
13/TCP,UDP DAYTIME – (RFC 867) Official
17/TCP,UDP Quote of the Day Official
18/TCP,UDP Message Send Protocol Official
19/TCP,UDP Character Generator Official
20/TCP FTP – data Official
21/TCP FTP—control (command) Official
22/TCP,UDP Secure Shell (SSH)—used for secure logins, file transfers (scp, sftp) and port forwarding Official
23/TCP Telnet protocol—unencrypted text communications Official
25/TCP,UDP Simple Mail Transfer Protocol (SMTP)—used for e-mail routing between mail servers Official
34/TCP,UDP Remote File (RF)—used to transfer files between machines Unofficial
35/TCP,UDP Any private printer server protocol Official
35/TCP,UDP QMS Magicolor 2 printer server protocol Unofficial
37/TCP,UDP TIME protocol Official
39/TCP,UDP Resource Location Protocol^[2] (RLP)—used for determining the location of higher level services from hosts on a network Official
41/TCP,UDP Graphics Official
42/TCP,UDP nameserver, ARPA Host Name Server Protocol Official
42/TCP,UDP WINS Unofficial
43/TCP WHOIS protocol Official
47/TCP GRE protocol Official
49/TCP,UDP TACACS Login Host protocol Official
52/TCP,UDP XNS (Xerox Network Systems) Time Protocol Official
53/TCP,UDP Domain Name System (DNS) Official
54/TCP,UDP XNS (Xerox Network Systems) Clearinghouse Official
55/TCP,UDP ISI-GL (ISI Graphics Language) Unofficial
56/TCP,UDP XNS (Xerox Network Systems) Authentication Official
56/TCP,UDP RAP (Route Access Protocol)^[3] Unofficial
57/TCP MTP, Mail Transfer Protocol Unofficial
58/TCP,UDP XNS (Xerox Network Systems) Mail Official
67/UDP Bootstrap Protocol (BOOTP) Server; also used by Dynamic Host Configuration Protocol (DHCP) Official
68/UDP Bootstrap Protocol (BOOTP) Client; also used by Dynamic Host Configuration Protocol (DHCP) Official
69/UDP Trivial File Transfer Protocol (TFTP) Official
70/TCP Gopher protocol Official
79/TCP Finger protocol Official
80/TCP,UDP Hypertext Transfer Protocol (HTTP) Official
81/TCP Torpark—Onion routing Unofficial
82/UDP Torpark—Control Unofficial
83/TCP MIT ML Device Official
88/TCP,UDP Kerberos—authentication system Official
90/TCP,UDP dnsix (DoD Network Security for Information Exchange) Securit Attribute Token Map Official
90/TCP,UDP Pointcast Unofficial
99/TCP WIP Message Protocol Unofficial
101/TCP NIC host name Official
102/TCP ISO-TSAP (Transport Service Access Point) Class 0 protocol^[4] Official
104/TCP,UDP ACR/NEMA Digital Imaging and Communications in Medicine Official
105/TCP,UDP CCSO name server protocol (CSO) Official
107/TCP Remote TELNET Service^[5] protocol Official
109/TCP Post Office Protocol 2 (POP2) Official
110/TCP Post Office Protocol 3 (POP3) Official
111/TCP,UDP Sun Remote Procedure Call Official
113/UDP ident—user identification system, used by IRC servers to identify users Official
113/TCP,UDP Authentication Service (auth) Official
115/TCP Simple File Transfer Protocol (SFTP) Official
117/TCP UUCP Path Service Official
118/TCP,UDP SQL (Structured Query Language) Services Official
119/TCP Network News Transfer Protocol (NNTP)—used for retrieving newsgroup messages Official
123/UDP Network Time Protocol (NTP)—used for time synchronization Official
135/TCP,UDP DCE endpoint resolution Official
135/TCP,UDP Microsoft EPMAP (End Point Mapper), also known as DCE/RPC Locator service^[6], used to remotely manage services including DHCP server, DNS server and WINS. Also used by DCOM Unofficial
137/TCP,UDP NetBIOS NetBIOS Name Service Official
138/TCP,UDP NetBIOS NetBIOS Datagram Service Official
139/TCP,UDP NetBIOS NetBIOS Session Service Official
143/TCP,UDP Internet Message Access Protocol (IMAP)—used for retrieving, organizing, and synchronizing e-mail messages Official
152/TCP,UDP Background File Transfer Program (BFTP)^[7] Official
153/TCP,UDP SGMP, Simple Gateway Monitoring Protocol Official
156/TCP,UDP SQL Service Official
158/TCP,UDP DMSP, Distributed Mail Service Protocol Unofficial
161/TCP,UDP Simple Network Management Protocol (SNMP) Official
162/TCP,UDP Simple Network Management Protocol Trap (SNMPTRAP)^[8] Official
170/TCP Print-srv, Network PostScript Official
177/TCP,UDP X Display Manager Control Protocol (XDMCP) Official
179/TCP BGP (Border Gateway Protocol) Official
194/TCP,UDP IRC (Internet Relay Chat) Official
199/TCP,UDP SMUX, SNMP Unix Multiplexer Official
201/TCP,UDP AppleTalk Routing Maintenance Official
209/TCP,UDP The Quick Mail Transfer Protocol Official
210/TCP,UDP ANSI Z39.50 Official
213/TCP,UDP IPX Official
218/TCP,UDP MPP, Message Posting Protocol Official
220/TCP,UDP IMAP, Interactive Mail Access Protocol, version 3 Official
256/TCP,UDP 2DEV "2SP" Port Unofficial
259/TCP,UDP ESRO, Efficient Short Remote Operations Official
264/TCP,UDP BGMP, Border Gateway Multicast Protocol Official
311/TCP Mac OS X Server Admin (officially AppleShare IP Web administration) Official
308/TCP Novastor Online Backup Official
318/TCP,UDP PKIX TSP, Time Stamp Protocol Official
323/TCP,UDP IMMP, Internet Message Mapping Protocol Unofficial
350/TCP,UDP MATIP-Type A, Mapping of Airline Traffic over Internet Protocol Official
351/TCP,UDP MATIP-Type B, Mapping of Airline Traffic over Internet Protocol Official
366/TCP,UDP ODMR, On-Demand Mail Relay Official
369/TCP,UDP Rpc2portmap Official
370/TCP,UDP codaauth2 – Coda authentication server Unofficial
370/TCP,UDP securecast1 – Outgoing packets to NAI's servers, http://www.nai.com/asp_set/anti_virus/alerts/faq.as Unofficial
371/TCP,UDP ClearCase albd Official
383/TCP,UDP HP data alarm manager Official
384/TCP,UDP A Remote Network Server System Official
387/TCP,UDP AURP, AppleTalk Update-based Routing Protocol Official
389/TCP,UDP Lightweight Directory Access Protocol (LDAP) Official
401/TCP,UDP UPS Uninterruptible Power Supply Official
402/TCP Altiris, Altiris Deployment Client Unofficial
411/TCP Direct Connect Hub Unofficial
412/TCP Direct Connect Client-to-Client Unofficial
427/TCP,UDP Service Location Protocol (SLP) Official
443/TCP,UDP Hypertext Transfer Protocol over TLS/SSL (HTTPS) Official
444/TCP,UDP SNPP, Simple Network Paging Protocol (RFC 1568) Official
445/TCP Microsoft-DS Active Directory, Windows shares Official
445/UDP Microsoft-DS SMB file sharing Official
464/TCP,UDP Kerberos Change/Set password Official
465/TCP Cisco protocol Unofficial
465/TCP SMTP over SSL Unofficial
475/TCP tcpnethaspsrv (Hasp services, TCP/IP version) Official
497/TCP Dantz Retrospect Official
500/UDP Internet Security Association and Key Management Protocol (ISAKMP) Official
501/TCP STMF, Simple Transportation Management Framework – DOT NTCIP 1101 Unofficial
502/TCP,UDP Modbus, Protocol Unofficial
504/TCP,UDP Citadel – multiservice protocol for dedicated clients for the Citadel groupware system Official
510/TCP First Class Protocol Unofficial
512/TCP Rexec, Remote Process Execution Official
512/UDP comsat, together with biff Official
513/TCP Login Official
513/UDP Who Official
514/TCP Shell—used to execute non-interactive commands on a remote system Official
514/UDP Syslog—used for system logging Official
515/TCP Line Printer Daemon—print service Official
517/UDP Talk Official
518/UDP NTalk Official
520/TCP efs, extended file name server Official
520/UDP Routing—RIP Official
524/TCP,UDP NCP (NetWare Core Protocol) is used for a variety things such as access to primary NetWare server resources, Time Synchronization, etc. Official
525/UDP Timed, Timeserver Official
530/TCP,UDP RPC Official
531/TCP,UDP AOL Instant Messenger, IRC Unofficial
532/TCP netnews Official
533/UDP netwall, For Emergency Broadcasts Official
540/TCP UUCP (Unix-to-Unix Copy Protocol) Official
542/TCP,UDP commerce (Commerce Applications) Official
543/TCP klogin, Kerberos login Official
544/TCP kshell, Kerberos Remote shell Official
545/TCP OSIsoft PI (VMS), OSISoft PI Server Client Access Unofficial
546/TCP,UDP DHCPv6 client Official
547/TCP,UDP DHCPv6 server Official
548/TCP Apple Filing Protocol (AFP) over TCP Official
550/UDP new-rwho, new-who Official
554/TCP,UDP Real Time Streaming Protocol (RTSP) Official
556/TCP Remotefs, RFS, rfs_server Official
560/UDP rmonitor, Remote Monitor Official
561/UDP monitor Official
563/TCP,UDP NNTP protocol over TLS/SSL (NNTPS) Official
587/TCP e-mail message submission^[9] (SMTP) Official
591/TCP FileMaker 6.0 (and later) Web Sharing (HTTP Alternate, also see port 80) Official
593/TCP,UDP HTTP RPC Ep Map, Remote procedure call over Hypertext Transfer Protocol, often used by Distributed Component Object Model services and Microsoft Exchange Server Official
604/TCP TUNNEL profile^[10], a protocol for BEEP peers to form an application layer tunnel Official
623/UDP ASF Remote Management and Control Protocol (ASF-RMCP) Official
631/TCP,UDP Internet Printing Protocol (IPP) Official
636/TCP,UDP Lightweight Directory Access Protocol over TLS/SSL (LDAPS) Official
639/TCP,UDP MSDP, Multicast Source Discovery Protocol Official
641/TCP,UDP SupportSoft Nexus Remote Command (control/listening): A proxy gateway connecting remote control traffic Official
646/TCP,UDP LDP, Label Distribution Protocol, a routing protocol used in MPLS networks Official
647/TCP DHCP Failover protocol^[11] Official
648/TCP RRP (Registry Registrar Protocol)^[12] Official
652/TCP DTCP, Dynamic Tunnel Configuration Protocol Unofficial
653/TCP,UDP SupportSoft Nexus Remote Command (data): A proxy gateway connecting remote control traffic Official
654/TCP AODV (Ad-hoc On-demand Distance Vector) Official
655/TCP IEEE MMS (IEEE Media Management System)^[13]^[14] Official
657/TCP,UDP IBM RMC (Remote monitoring and Control) protocol, used by System p5 AIX Integrated Virtualization Manager (IVM)^[15] and Hardware Management Console to connect managed logical partitions (LPAR) to enable dynamic partition reconfiguration Official
660/TCP Mac OS X Server administration Official
665/TCP sun-dr, Remote Dynamic Reconfiguration Unofficial
666/UDP Doom, first online first-person shooter Official
674/TCP ACAP (Application Configuration Access Protocol) Official
691/TCP MS Exchange Routing Official
692/TCP Hyperwave-ISP Official
694/TCP,UDP Linux-HA High availability Heartbeat Official
695/TCP IEEE-MMS-SSL (IEEE Media Management System over SSL)^[16] Official
698/UDP OLSR (Optimized Link State Routing) Official
699/TCP Access Network Official
700/TCP EPP (Extensible Provisioning Protocol), a protocol for communication between domain name registries and registrars (RFC 4934) Official
701/TCP LMP (Link Management Protocol (Internet))^[17], a protocol that runs between a pair of nodes and is used to manage traffic engineering (TE) links Official
702/TCP IRIS^[18]^[19] (Internet Registry Information Service) over BEEP (Blocks Extensible Exchange Protocol)^[20] (RFC 3983) Official
706/TCP SILC, Secure Internet Live Conferencing Official
711/TCP Cisco TDP, Tag Distribution Protocol^[21]^[22]^[23]—being replaced by the MPLS Label Distribution Protocol^[24] Official
712/TCP TBRPF, Topology Broadcast based on Reverse-Path Forwarding routing protocol (RFC 3684) Official
712/UDP Promise RAID Controller Unofficial
720/TCP SMQP, Simple Message Queue Protocol Unofficial
749/TCP,UDP Kerberos administration Official
750/TCP rfile Official
750/UDP loadav Official
750/UDP kerberos-iv, Kerberos version IV Official
751/TCP,UDP pump Official
751/TCP,UDP kerberos_master, Kerberos authentication Unofficial
752/TCP qrh Official
752/UDP qrh Official
752/UDP userreg_server, Kerberos Password (kpasswd) server Unofficial
753/TCP Reverse Routing Header (rrh)^[25] Official
753/UDP Reverse Routing Header (rrh) Official
753/UDP passwd_server, Kerberos userreg server Unofficial
754/TCP tell send Official
754/TCP krb5_prop, Kerberos v5 slave propagation Unofficial
754/UDP tell send Official
760/TCP,UDP ns Official
760/TCP,UDP krbupdate [kreg], Kerberos registration Unofficial
782/TCP Conserver serial-console management server Unofficial
783/TCP SpamAssassin spamd daemon Unofficial
829/TCP CMP (Certificate Management Protocol) Unofficial
843/TCP Adobe Flash socket policy server Unofficial
860/TCP iSCSI (RFC 3720) Official
873/TCP rsync file synchronisation protocol Official
888/TCP cddbp, CD DataBase (CDDB) protocol (CDDBP)—unassigned but widespread use Unofficial
901/TCP Samba Web Administration Tool (SWAT) Unofficial
901/TCP, UDP VMware Virtual Infrastructure Client (UDP from server being managed to management console) Unofficial
902/TCP VMware Server Console (TCP from management console to server being Managed) Unofficial
902/UDP VMware Server Console (UDP from server being managed to management console) Unofficial
903/TCP VMware Remote Console ^[26] Unofficial
904/TCP VMware Server Alternate (if 902 is in use, i.e. SUSE linux) Unofficial
911/TCP Network Console on Acid (NCA)—local tty redirection over OpenSSH Unofficial
953/TCP,UDP Domain Name System (DNS) RDNC Service Unofficial
981/TCP SofaWare Technologies Remote HTTPS management for firewall devices running embedded Check Point FireWall-1 software Unofficial
989/TCP,UDP FTPS Protocol (data): FTP over TLS/SSL Official
990/TCP,UDP FTPS Protocol (control): FTP over TLS/SSL Official
991/TCP,UDP NAS (Netnews Administration System) Official
992/TCP,UDP TELNET protocol over TLS/SSL Official
993/TCP Internet Message Access Protocol over SSL (IMAPS) Official
995/TCP Post Office Protocol 3 over TLS/SSL (POP3S) Official
999/TCP ScimoreDB Database System Unofficial
1001/TCP JtoMB Unofficial
1023/TCP,UDP Reserved^[1] Official

Port	Description	Status
1/TCP,UDP	TCP Port Service Multiplexer	Official
2/TCP,UDP	Management Utility	Official
3/TCP,UDP	Compression Process	Official
4/TCP,UDP	Unassigned	Official
5/TCP,UDP	Remote Job Entry	Official
6/TCP,UDP	Unassigned	Official
7/TCP,UDP	Echo	Official
8/TCP,UDP	Unassigned	Official
9/TCP,UDP	Discard	Official
11/TCP,UDP	Active Users	Official
13/TCP,UDP	DAYTIME – (RFC 867)	Official
17/TCP,UDP	Quote of the Day	Official
18/TCP,UDP	Message Send Protocol	Official
19/TCP,UDP	Character Generator	Official
20/TCP	FTP – data	Official
21/TCP	FTP—control (command)	Official
22/TCP,UDP	Secure Shell (SSH)—used for secure logins, file transfers (scp, sftp) and port forwarding	Official
23/TCP	Telnet protocol—unencrypted text communications	Official
25/TCP,UDP	Simple Mail Transfer Protocol (SMTP)—used for e-mail routing between mail servers	Official
34/TCP,UDP	Remote File (RF)—used to transfer files between machines	Unofficial
35/TCP,UDP	Any private printer server protocol	Official
35/TCP,UDP	QMS Magicolor 2 printer server protocol	Unofficial
37/TCP,UDP	TIME protocol	Official
39/TCP,UDP	Resource Location Protocol^[2] (RLP)—used for determining the location of higher level services from hosts on a network	Official
41/TCP,UDP	Graphics	Official
42/TCP,UDP	nameserver, ARPA Host Name Server Protocol	Official
42/TCP,UDP	WINS	Unofficial
43/TCP	WHOIS protocol	Official
47/TCP	GRE protocol	Official
49/TCP,UDP	TACACS Login Host protocol	Official
52/TCP,UDP	XNS (Xerox Network Systems) Time Protocol	Official
53/TCP,UDP	Domain Name System (DNS)	Official
54/TCP,UDP	XNS (Xerox Network Systems) Clearinghouse	Official
55/TCP,UDP	ISI-GL (ISI Graphics Language)	Unofficial
56/TCP,UDP	XNS (Xerox Network Systems) Authentication	Official
56/TCP,UDP	RAP (Route Access Protocol)^[3]	Unofficial
57/TCP	MTP, Mail Transfer Protocol	Unofficial
58/TCP,UDP	XNS (Xerox Network Systems) Mail	Official
67/UDP	Bootstrap Protocol (BOOTP) Server; also used by Dynamic Host Configuration Protocol (DHCP)	Official
68/UDP	Bootstrap Protocol (BOOTP) Client; also used by Dynamic Host Configuration Protocol (DHCP)	Official
69/UDP	Trivial File Transfer Protocol (TFTP)	Official
70/TCP	Gopher protocol	Official
79/TCP	Finger protocol	Official
80/TCP,UDP	Hypertext Transfer Protocol (HTTP)	Official
81/TCP	Torpark—Onion routing	Unofficial
82/UDP	Torpark—Control	Unofficial
83/TCP	MIT ML Device	Official
88/TCP,UDP	Kerberos—authentication system	Official
90/TCP,UDP	dnsix (DoD Network Security for Information Exchange) Securit Attribute Token Map	Official
90/TCP,UDP	Pointcast	Unofficial
99/TCP	WIP Message Protocol	Unofficial
101/TCP	NIC host name	Official
102/TCP	ISO-TSAP (Transport Service Access Point) Class 0 protocol^[4]	Official
104/TCP,UDP	ACR/NEMA Digital Imaging and Communications in Medicine	Official
105/TCP,UDP	CCSO name server protocol (CSO)	Official
107/TCP	Remote TELNET Service^[5] protocol	Official
109/TCP	Post Office Protocol 2 (POP2)	Official
110/TCP	Post Office Protocol 3 (POP3)	Official
111/TCP,UDP	Sun Remote Procedure Call	Official
113/UDP	ident—user identification system, used by IRC servers to identify users	Official
113/TCP,UDP	Authentication Service (auth)	Official
115/TCP	Simple File Transfer Protocol (SFTP)	Official
117/TCP	UUCP Path Service	Official
118/TCP,UDP	SQL (Structured Query Language) Services	Official
119/TCP	Network News Transfer Protocol (NNTP)—used for retrieving newsgroup messages	Official
123/UDP	Network Time Protocol (NTP)—used for time synchronization	Official
135/TCP,UDP	DCE endpoint resolution	Official
135/TCP,UDP	Microsoft EPMAP (End Point Mapper), also known as DCE/RPC Locator service^[6], used to remotely manage services including DHCP server, DNS server and WINS. Also used by DCOM	Unofficial
137/TCP,UDP	NetBIOS NetBIOS Name Service	Official
138/TCP,UDP	NetBIOS NetBIOS Datagram Service	Official
139/TCP,UDP	NetBIOS NetBIOS Session Service	Official
143/TCP,UDP	Internet Message Access Protocol (IMAP)—used for retrieving, organizing, and synchronizing e-mail messages	Official
152/TCP,UDP	Background File Transfer Program (BFTP)^[7]	Official
153/TCP,UDP	SGMP, Simple Gateway Monitoring Protocol	Official
156/TCP,UDP	SQL Service	Official
158/TCP,UDP	DMSP, Distributed Mail Service Protocol	Unofficial
161/TCP,UDP	Simple Network Management Protocol (SNMP)	Official
162/TCP,UDP	Simple Network Management Protocol Trap (SNMPTRAP)^[8]	Official
170/TCP	Print-srv, Network PostScript	Official
177/TCP,UDP	X Display Manager Control Protocol (XDMCP)	Official
179/TCP	BGP (Border Gateway Protocol)	Official
194/TCP,UDP	IRC (Internet Relay Chat)	Official
199/TCP,UDP	SMUX, SNMP Unix Multiplexer	Official
201/TCP,UDP	AppleTalk Routing Maintenance	Official
209/TCP,UDP	The Quick Mail Transfer Protocol	Official
210/TCP,UDP	ANSI Z39.50	Official
213/TCP,UDP	IPX	Official
218/TCP,UDP	MPP, Message Posting Protocol	Official
220/TCP,UDP	IMAP, Interactive Mail Access Protocol, version 3	Official
256/TCP,UDP	2DEV "2SP" Port	Unofficial
259/TCP,UDP	ESRO, Efficient Short Remote Operations	Official
264/TCP,UDP	BGMP, Border Gateway Multicast Protocol	Official
311/TCP	Mac OS X Server Admin (officially AppleShare IP Web administration)	Official
308/TCP	Novastor Online Backup	Official
318/TCP,UDP	PKIX TSP, Time Stamp Protocol	Official
323/TCP,UDP	IMMP, Internet Message Mapping Protocol	Unofficial
350/TCP,UDP	MATIP-Type A, Mapping of Airline Traffic over Internet Protocol	Official
351/TCP,UDP	MATIP-Type B, Mapping of Airline Traffic over Internet Protocol	Official
366/TCP,UDP	ODMR, On-Demand Mail Relay	Official
369/TCP,UDP	Rpc2portmap	Official
370/TCP,UDP	codaauth2 – Coda authentication server	Unofficial
370/TCP,UDP	securecast1 – Outgoing packets to NAI's servers, http://www.nai.com/asp_set/anti_virus/alerts/faq.as	Unofficial
371/TCP,UDP	ClearCase albd	Official
383/TCP,UDP	HP data alarm manager	Official
384/TCP,UDP	A Remote Network Server System	Official
387/TCP,UDP	AURP, AppleTalk Update-based Routing Protocol	Official
389/TCP,UDP	Lightweight Directory Access Protocol (LDAP)	Official
401/TCP,UDP	UPS Uninterruptible Power Supply	Official
402/TCP	Altiris, Altiris Deployment Client	Unofficial
411/TCP	Direct Connect Hub	Unofficial
412/TCP	Direct Connect Client-to-Client	Unofficial
427/TCP,UDP	Service Location Protocol (SLP)	Official
443/TCP,UDP	Hypertext Transfer Protocol over TLS/SSL (HTTPS)	Official
444/TCP,UDP	SNPP, Simple Network Paging Protocol (RFC 1568)	Official
445/TCP	Microsoft-DS Active Directory, Windows shares	Official
445/UDP	Microsoft-DS SMB file sharing	Official
464/TCP,UDP	Kerberos Change/Set password	Official
465/TCP	Cisco protocol	Unofficial
465/TCP	SMTP over SSL	Unofficial
475/TCP	tcpnethaspsrv (Hasp services, TCP/IP version)	Official
497/TCP	Dantz Retrospect	Official
500/UDP	Internet Security Association and Key Management Protocol (ISAKMP)	Official
501/TCP	STMF, Simple Transportation Management Framework – DOT NTCIP 1101	Unofficial
502/TCP,UDP	Modbus, Protocol	Unofficial
504/TCP,UDP	Citadel – multiservice protocol for dedicated clients for the Citadel groupware system	Official
510/TCP	First Class Protocol	Unofficial
512/TCP	Rexec, Remote Process Execution	Official
512/UDP	comsat, together with biff	Official
513/TCP	Login	Official
513/UDP	Who	Official
514/TCP	Shell—used to execute non-interactive commands on a remote system	Official
514/UDP	Syslog—used for system logging	Official
515/TCP	Line Printer Daemon—print service	Official
517/UDP	Talk	Official
518/UDP	NTalk	Official
520/TCP	efs, extended file name server	Official
520/UDP	Routing—RIP	Official
524/TCP,UDP	NCP (NetWare Core Protocol) is used for a variety things such as access to primary NetWare server resources, Time Synchronization, etc.	Official
525/UDP	Timed, Timeserver	Official
530/TCP,UDP	RPC	Official
531/TCP,UDP	AOL Instant Messenger, IRC	Unofficial
532/TCP	netnews	Official
533/UDP	netwall, For Emergency Broadcasts	Official
540/TCP	UUCP (Unix-to-Unix Copy Protocol)	Official
542/TCP,UDP	commerce (Commerce Applications)	Official
543/TCP	klogin, Kerberos login	Official
544/TCP	kshell, Kerberos Remote shell	Official
545/TCP	OSIsoft PI (VMS), OSISoft PI Server Client Access	Unofficial
546/TCP,UDP	DHCPv6 client	Official
547/TCP,UDP	DHCPv6 server	Official
548/TCP	Apple Filing Protocol (AFP) over TCP	Official
550/UDP	new-rwho, new-who	Official
554/TCP,UDP	Real Time Streaming Protocol (RTSP)	Official
556/TCP	Remotefs, RFS, rfs_server	Official
560/UDP	rmonitor, Remote Monitor	Official
561/UDP	monitor	Official
563/TCP,UDP	NNTP protocol over TLS/SSL (NNTPS)	Official
587/TCP	e-mail message submission^[9] (SMTP)	Official
591/TCP	FileMaker 6.0 (and later) Web Sharing (HTTP Alternate, also see port 80)	Official
593/TCP,UDP	HTTP RPC Ep Map, Remote procedure call over Hypertext Transfer Protocol, often used by Distributed Component Object Model services and Microsoft Exchange Server	Official
604/TCP	TUNNEL profile^[10], a protocol for BEEP peers to form an application layer tunnel	Official
623/UDP	ASF Remote Management and Control Protocol (ASF-RMCP)	Official
631/TCP,UDP	Internet Printing Protocol (IPP)	Official
636/TCP,UDP	Lightweight Directory Access Protocol over TLS/SSL (LDAPS)	Official
639/TCP,UDP	MSDP, Multicast Source Discovery Protocol	Official
641/TCP,UDP	SupportSoft Nexus Remote Command (control/listening): A proxy gateway connecting remote control traffic	Official
646/TCP,UDP	LDP, Label Distribution Protocol, a routing protocol used in MPLS networks	Official
647/TCP	DHCP Failover protocol^[11]	Official
648/TCP	RRP (Registry Registrar Protocol)^[12]	Official
652/TCP	DTCP, Dynamic Tunnel Configuration Protocol	Unofficial
653/TCP,UDP	SupportSoft Nexus Remote Command (data): A proxy gateway connecting remote control traffic	Official
654/TCP	AODV (Ad-hoc On-demand Distance Vector)	Official
655/TCP	IEEE MMS (IEEE Media Management System)^[13]^[14]	Official
657/TCP,UDP	IBM RMC (Remote monitoring and Control) protocol, used by System p5 AIX Integrated Virtualization Manager (IVM)^[15] and Hardware Management Console to connect managed logical partitions (LPAR) to enable dynamic partition reconfiguration	Official
660/TCP	Mac OS X Server administration	Official
665/TCP	sun-dr, Remote Dynamic Reconfiguration	Unofficial
666/UDP	Doom, first online first-person shooter	Official
674/TCP	ACAP (Application Configuration Access Protocol)	Official
691/TCP	MS Exchange Routing	Official
692/TCP	Hyperwave-ISP	Official
694/TCP,UDP	Linux-HA High availability Heartbeat	Official
695/TCP	IEEE-MMS-SSL (IEEE Media Management System over SSL)^[16]	Official
698/UDP	OLSR (Optimized Link State Routing)	Official
699/TCP	Access Network	Official
700/TCP	EPP (Extensible Provisioning Protocol), a protocol for communication between domain name registries and registrars (RFC 4934)	Official
701/TCP	LMP (Link Management Protocol (Internet))^[17], a protocol that runs between a pair of nodes and is used to manage traffic engineering (TE) links	Official
702/TCP	IRIS^[18]^[19] (Internet Registry Information Service) over BEEP (Blocks Extensible Exchange Protocol)^[20] (RFC 3983)	Official
706/TCP	SILC, Secure Internet Live Conferencing	Official
711/TCP	Cisco TDP, Tag Distribution Protocol^[21]^[22]^[23]—being replaced by the MPLS Label Distribution Protocol^[24]	Official
712/TCP	TBRPF, Topology Broadcast based on Reverse-Path Forwarding routing protocol (RFC 3684)	Official
712/UDP	Promise RAID Controller	Unofficial
720/TCP	SMQP, Simple Message Queue Protocol	Unofficial
749/TCP,UDP	Kerberos administration	Official
750/TCP	rfile	Official
750/UDP	loadav	Official
750/UDP	kerberos-iv, Kerberos version IV	Official
751/TCP,UDP	pump	Official
751/TCP,UDP	kerberos_master, Kerberos authentication	Unofficial
752/TCP	qrh	Official
752/UDP	qrh	Official
752/UDP	userreg_server, Kerberos Password (kpasswd) server	Unofficial
753/TCP	Reverse Routing Header (rrh)^[25]	Official
753/UDP	Reverse Routing Header (rrh)	Official
753/UDP	passwd_server, Kerberos userreg server	Unofficial
754/TCP	tell send	Official
754/TCP	krb5_prop, Kerberos v5 slave propagation	Unofficial
754/UDP	tell send	Official
760/TCP,UDP	ns	Official
760/TCP,UDP	krbupdate [kreg], Kerberos registration	Unofficial
782/TCP	Conserver serial-console management server	Unofficial
783/TCP	SpamAssassin spamd daemon	Unofficial
829/TCP	CMP (Certificate Management Protocol)	Unofficial
843/TCP	Adobe Flash socket policy server	Unofficial
860/TCP	iSCSI (RFC 3720)	Official
873/TCP	rsync file synchronisation protocol	Official
888/TCP	cddbp, CD DataBase (CDDB) protocol (CDDBP)—unassigned but widespread use	Unofficial
901/TCP	Samba Web Administration Tool (SWAT)	Unofficial
901/TCP, UDP	VMware Virtual Infrastructure Client (UDP from server being managed to management console)	Unofficial
902/TCP	VMware Server Console (TCP from management console to server being Managed)	Unofficial
902/UDP	VMware Server Console (UDP from server being managed to management console)	Unofficial
903/TCP	VMware Remote Console ^[26]	Unofficial
904/TCP	VMware Server Alternate (if 902 is in use, i.e. SUSE linux)	Unofficial
911/TCP	Network Console on Acid (NCA)—local tty redirection over OpenSSH	Unofficial
953/TCP,UDP	Domain Name System (DNS) RDNC Service	Unofficial
981/TCP	SofaWare Technologies Remote HTTPS management for firewall devices running embedded Check Point FireWall-1 software	Unofficial
989/TCP,UDP	FTPS Protocol (data): FTP over TLS/SSL	Official
990/TCP,UDP	FTPS Protocol (control): FTP over TLS/SSL	Official
991/TCP,UDP	NAS (Netnews Administration System)	Official
992/TCP,UDP	TELNET protocol over TLS/SSL	Official
993/TCP	Internet Message Access Protocol over SSL (IMAPS)	Official
995/TCP	Post Office Protocol 3 over TLS/SSL (POP3S)	Official
999/TCP	ScimoreDB Database System	Unofficial
1001/TCP	JtoMB	Unofficial
1023/TCP,UDP	Reserved^[1]	Official

Using Optional Outlput Paramaters in a SQL Stored Procedure

2010-01-13T11:46:00.000-08:00

Optional Outlput Paramaters in a SQL Stored Procedure

In the interest of re-usability I have spent plenty of time learning tricks to make my C#, VB.Net or Java classes integration friendly. This usually consists of providing extensibility points, web service wrappers, APIs, methods overloads etc. On a typical project though about a third of my code is in SQL Stored Procedures. Typically these are usage specific. Meaning that they are going to be called from a web page or client, or will act as a helper procedure exclusively.

So if I needed to implement similar logic in more than one of these scenarios multiple procedures needed to be developed.

Below is another option.

Providing your result as both an output parameter and a result set.
** note the output parameter can also be a table variable for procedures with a non-scalar result

-- =============================================
-- Stored procedure with an OUTPUT parameter
-- =============================================
-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo' AND 
   SPECIFIC_NAME = N'optional_output_parameter'
     )
   DROP PROCEDURE dbo.optional_output_parameter

GO

CREATE PROCEDURE dbo.optional_output_parameter
	@test1 int = 0, 
	@test2 int OUTPUT
AS
	
	set @test2 = 10 + @test1
	
	select @test2 [A: Procedure Result]
	
GO

-- =============================================
-- Execute the SP with an Output parameter
-- =============================================
DECLARE @test2 int

SET @test2 = NULL

EXEC dbo.optional_output_parameter 1 ,@test2 OUTPUT

SELECT @test2 [A: test2 output]
GO

-- =============================================
-- Add a NULL Default to the Output parameter
-- Execute the SP without an Output parameter 
-- =============================================
ALTER PROCEDURE dbo.optional_output_parameter
	@test1 int = 0, 
	@test2 int = null OUTPUT
AS

	set @test2 = 10 + @test1

	select @test2  [B: Procedure Result]

GO

-- =============================================
-- Example to execute the stored procedure
-- =============================================
DECLARE @test2 int

SET @test2 = NULL

EXEC dbo.optional_output_parameter 1 

SELECT @test2 [B: test2 output]
GO

DROP PROCEDURE dbo.optional_output_parameter

GO

IEnumerable.Except removes duplicates

2009-12-12T19:37:00.000-08:00

Except: IEnumerable<T>.Except(IEnumerable<T>);

All duplicate Items in the IEnumerable<TSource> are removed as a result of the Except call.


List<string> MailingList= new List<string> () 
{"daniel_bowden@yahoo.com",
"my@in4mr.com",
"dr.catalyst@yahoo.com",
"daniel@danielbowden.com",
"daniel_bowden@yahoo.com",
"mure@hellokitty.com",
"vampireseyes96@yahoo.com",
"daniel_bowden@yahoo.com",
"heidilady@yahoo.com",
"rachbu@yahoo.com"}

List<string> BannedEmails= new List<string> () 
{"dr.cataclyst@yahoo.com",
"heidilady@yahoo.com",
"msg@4u2read.com"}
List<string> CleanMailingList = MailingList.Except(BannedEmails);
String.Join("\n", CleanMailingList.Select(o => String.Format("<li>{0}</li>", o)).ToArray());

daniel_bowden@yahoo.com
my@in4mr.com
dr.catalyst@yahoo.com
daniel@danielbowden.com
mure@hellokitty.com
vampireseyes96@yahoo.com
rachbu@yahoo.com

Results: The emails in MailingList that were in BannedEmail have been removed exactly as advertized...or is it?

Note the email Daniel_Bowden@yahoo.com in our MailingList.
It occurs three times in MailingList and does not occur at all in BannedEmails. Most importantly it only occurs once in the result of our Except TResultCleanMailingList..

All duplicate Items in the IEnumerable<TSource> are removed as a result of the Except call.

Pro: Automatically remove duplicates from an IEnumerable of <T>
Con: If you are not aware of this behavior then data can be lost and implementations may not produce the intended results.

Use any font you want on your web page. (well almost)

2009-11-01T03:40:00.000-08:00

All Major browsers currently support some form of web font embedding.

Webkit/Safari 3.1+, Opera 10+, Mozilla/Firefox 3.5+, Google Chrome 3.0+ all support TTF font linking through the use of the @font-face CSS attribute.

Internet Explorer supports the .EOT format which can be created from a .TTF file format through the use of the TTF2EOT GNU General Public Licensed application.

TTF2EOT can be found here

Example Usage: C:\ttf2eot.exe c:\windows\fonts\arial.ttf c:\output.eot

Applying a crossbrowser approach to @font-face.

Reference the ".eot" first and IE will ignore the next src attribute due to the local() Syntax.


/* ---- Bradley Hand ITC ------------------------------------------ */
@font-face
{
font-family: Bradley_Hand_ITC;
src: url("Bradley_Hand_ITC.eot"); /* EOT file for IE */
src: local("Bradley Hand ITC"),url("Bradley_Hand_ITC.ttf") format("truetype"); /* non-IE */
}
.Bradley_Hand_ITC
{
font-family: Bradley_Hand_ITC;
}

Lets see it in action at, 10 fonts go where I've never sent them before, on a Web Page.

The Dude is back - My Favorite Net Mapping Tool

2009-10-27T16:17:00.001-07:00

Network Mapping, Auto Discovery and Network Monitoring, for free

Every developer has a bag of tricks comprised of their favorite apps and tools that make the job easier or make them more productive.
The Dude is my favorite network mapping tool. It's simple small and feature rich. It's auto discovery and diagram generation alone saved me from spending an hour or two today auditing servers for IP Address and configuration information. Now I can just post the automatically generated network map and report for review.

Deserializing XML with extra nodes using schema validation and xs:any

2009-09-26T12:46:00.000-07:00

Design a flexible schema using xs:any

Note: this can be abused, I recently had to reverse engineer a system where almost every document had multiple XS:any nodes. It becomes extremely difficult to determine what the intended design was.

When you work on distributed systems the challenges are often domain specific and resulting solutions less than portable.
However on the occasion that you find one it is very rewarding, what follows documents just such an occasion.

Challenge
The format of an XML message between distributed or disconnected systems

A) needs to support dynamic content nodes
B) may change with little to no notice
C) has nodes that whose attributes are not managed or restricted

Solution
In this somewhat extreme example our shipping provider regularly send us messages with optional or custom order information direct from the manufacturers.
When our shipping service deserialize these messages all we care about is the existence of an order node the rest of the nodes will be handled by downstream processes.
the "xs:any namespace='##any' " and "processContents='skip' " allows us to gather but not process the unmapped xml and still enforce our validation (the required Order element).
XML/XSD fragment demonstrating xs:any :

<xs:element name="Shipment">
      <xs:complexType>
         <xs:sequence>
           <xs:element name="OrderNo" type="xs:string"/>
              <xs:sequence>
                <xs:any namespace="##any" minOccurs="0"
                        maxOccurs="unbounded"
                        processContents="skip" />
              </xs:sequence>
         </xs:sequence>
      </xs:complexType>
   </xs:element>

On the Obejct side:

The .NET Framework includes two attributes that enable you to capture unknown XML elements and attributes:

* XmlAnyElementAttribute is used to control how unknown elements are stored in an object.
* XmlAnyAttributeAttribute is used to control how unknown attributes are stored in an object.

XmlAnyElementAttribute
is attached to a field, property, parameter, or return value and is
essentially a "wild card" that is capable of converting to and from XML
elements during serialization. You'll probably want to use it with an
array of XmlNode objects, becaise each unknown element from the XML
document will be placed into its own XmlNode object. If you use the
attribute with a single XmlNode object, you'll lose data if multiple
unknown elements are received. The XmlAnyElement attribute is typically
used like this:

C# Fragment demonstrating the [XmlAnyElement] attribute:

XmlNode[] _openElements = null;
[XmlAnyElement()]
public XmlNode[] OpenElements{get;set;}

XmlAnyAttribute
can be used with an array of XmlAttribute or XmlNode (it can't be used
with single objects of either type). It's typically used much like the
XmlAnyElement, as shown in the code below:

XmlNode[] _openAttributes = null;
[XmlAnyAttribute()]
public XmlNode[] OpenAttributes{get;set;}

When
you use XmlAnyElement and XmlAnyAttribute attributes, the serializer
will not generate UnknownElement and UnknownAttribute events.

The inspiration for this post post came from the article:

Using Open XML Schema with .NET
by Mickey Williams

SQL Reporting Services Security

2009-09-15T22:48:00.001-07:00

SQL Reporting Services missing folders and tabs

So you just installed SQL Reporting Services 2008 on your spiffy new Server 2008 or Vista PC. An you eagerly go to the familiar url http://localhost/reports or http://<servername>/reports and get a home page missing the "Site Settings" link. Actually it's pretty much missing everything except "Home", "My Subscriptions" and "Help" (which is anything but helpful).
Whats wrong? Why can't I add a data source, you ask?
I am guessing the Vista reference above gave away the big secret.

http://msdn.microsoft.com/en-us/library/bb630430.aspx

yep, you need to run IE in administrator mode....

UAC strikes again.

Clone a Hyper-V VM without SCVMM

2009-09-05T18:35:00.000-07:00

Clone a Hyper-V virtual machine without SCVMM

The following assumes that you have created a machine that you would like to use as a template.

**Sysprepping this machine is not required, but if you will be making more than one copy I highly recommend it (sysprep instructions follow post).

1. Right click on the template machine in Hyper-V Manager and choose Export (Do Not Start a Template)

2. Choose the <HYPER-V_DIRECTORY>\<NEW_VM_NAME> directory for a destination

3. Wait for the export to complete

4. Move the files and folders under <HYPER-V_DIRECTORY>\<NEW_VM_NAME>\_<TEMPLATE_NAME>_ up one level to <HYPER-V_DIRECTORY>\<NEW_VM_NAME>

5. Delete the folder <HYPER-V_DIRECTORY>\<NEW_VM_NAME>\_<TEMPLATE_NAME>_

6. In Hyper-V Manager choose “Import a Virtual Machine” from the Action menu on the top right

7. Import from the folder <HYPER-V_DIRECTORY>\<NEW_VM_NAME>

8. You will now see two <_TEMPLATE_NAME_> Virtual Machines in Hyper-V Manager

9. Right click the bottom one (it will have the most recent date) and rename it to your <NEW_VM_NAME>

10. Right click the <NEW_VM_NAME> Virtual Machine and choose “Settings” from the context menu

11. On the left hand side click on the Hard Drive named <TEMPLATE_NAME> (it should be under IDE Controller 0) ** image A**

12. Click Remove

13. Repeat steps 11 and 12 for all hard disks

14. Close Settings

15. Open Windows Explorer and Navigate to <HYPER-V_DIRECTORY>\<NEW_VM_NAME>\Virtual Hard Disks

16. Rename <TEMPLATE_NAME>.vhd to <NEW_VM_NAME>.vhd

17. Repeat step 15 for all remaining hard disks

18. Close Windows Explorer

19. In Hyper-V Manager right click the <NEW_VM_NAME> Virtual Machine and choose “Settings” from the context menu

20. On the left hand side click on IDE Controller 0 and select “Hard Disk” and click “ADD”

21. Repeat for remaining Hard Disks

a. If possible attach these to a SCSI Controller (this may have to be added via “Add Hardware” at the top of the page)

22. Close Settings

23. Right click the <NEW_VM_NAME> Virtual Machine and choose “Start”

24. Right click the <NEW_VM_NAME> Virtual Machine and choose “Connect”

If your template machine had Sysprep run on it:

25. The Virtual Machine’s screen will be grey for a while and then show what appears to be a Windows Server Setup Screen

26. Choose next, Accept the Eula and Enter <NEW_VM_NAME> when you are prompted for a machine name

27. Click next and the system will load for a while and then present you with the logon screen

Sysprep: http://technet.microsoft.com/en-us/library/cc766514(WS.10).aspx

Gotchas?: Make sure to read the links below if your going to sysprep a machine with Sql Server or Hyper-V
Sysprep a Sql Server: http://support.microsoft.com/kb/303774
Sysprep a Hyper-V Server: Http://blogs.msdn.com/mikekol/archive/2008/03/25/hyper-v-installation-tricks-part-1-sysprep-and-hyper-v.aspx

Create VS2008 snippets by Right-Click, Export to Snippet

2009-09-02T17:38:00.000-07:00

Create VS2008 Code Snippets inline simply and effectively.

Download Snippet Designer from CodePlex.

Highlight your code fragment in VS2008

“Right Click … Export to Snippet”

It comes with a Snippet Explorer tool to window to search snippets on your computer.

Picasa Content

Gotcha?: You might find that your VS2008 IDE has no menu item for the built in Snippet Manager. This is a bug with VS2008 and not related to the snippet designer.

Workaround: (ctrl k + ctrl b) brings up the default Snippet Manager

Tortoise svn Global-Ignore for Visual Studio solutions files and folders

2009-09-02T01:38:00.001-07:00

I have wanted to do this for a while, and I guess my projects have hit the critical mass required to justify spending 30 minutes setting it up.

To globally ignore folders and directories with Tortoise svn client.

1. open %appdata%\subversion\config (this is a file with no extension)

2. find the global ignore section under “[miscellany]”

3. replace the following line with its successor:

# global-ignores = *.o *.lo *.la *.al .libs *.so *.so.[0-9]* *.a *.pyc *.pyo

-with-

global-ignores = bin obj release compile debug *.bak *.user *.suo

!(HttpCookieCollection Class Provides a type-safe way to manipulate HTTP cookies.)

2009-09-01T13:25:00.001-07:00

Be forewarned utilizing the HttpCookieCollection Class to store a collection of newly created HTTPCookies , and casting them to HTTPCookie generates the following error:

Unable to cast object of type ‘System.String’ to type ‘System.Web.HttpCookie’;

Stick with the List<HttpCookie> and you’re in the clear.

Example:

var HttpCookies = new List<HttpCookie>( );

HttpCookie newHttpCookie1 = new HttpCookie "System_name1", "MySystem");

HttpCookies.Add(newHttpCookie6);

foreach (var c in HttpCookies)

{

c.Expires = DateTime.Now.AddYears(10);

c.Domain = "MyDomain.com";

}

Really “Super” Icons

2009-08-29T05:09:00.001-07:00

Homemade Superhero Icon set

No I don’t know why anyone else would want them. Yes I did have a reason for making them. really…

We decided to name our Virtual Servers after Superheroes at the office. I had spent 12 hours configuring 4 servers and 2 templates, virtual networks , SQL Server etcetera and decided to take a break from code and configuration.

I fired up Google and did a few image searches. I Found some classic Marvel hero images and dropped them Fireworks for editing, then saved them all as 90 x 120 and uploaded them to http://www.convertico.com to convert them to .ICO files. http://www.convertico.com is a website with a simple upload to convert to .ICO and right-click to save the result function,

Later I Made them into 256 pixel x 256 pixel vista icons using a free Icon Editor Application from http://icofx.ro/ which is nice and clean, and carries no spy/ad ware.

So without further ado….

Superhero Icon Library

Individual Superhero Icons

Update: my 2008 R2 server Desktop

Microsoft Hyper-V Best Practices

2009-08-28T08:46:00.001-07:00

Microsoft whitepapers outlining their best practices for implementing Hyper-V in a production environment

Deploying VM Using HyperV Technology

SQL 2008 in HyperV 2008

A Massive NetApp Hyper-V Deployment

2009-08-27T18:22:00.000-07:00

I ran across this truly massive virtualization project by Avanade

A quick summary...

Production Environment

Microsoft Hyper-V for Windows Server 2008, Core Installation, Enterprise Edition
Configured as a four-node Hyper-V Cluster
27 child partitions (virtual machines)
Virtualized applications run under either the Windows Server 2003 or Windows Server 2008 guest operating system
Virtualized Applications
Team Foundation Server
Systems Center Operations Manager 2007
Microsoft Windows Server 2008 Terminal Services (both TS farm and TS Gateway)
Servers and Storage
Sun Fire X4150 servers, each with two quad core processors, 32GB RAM
NetApp FAS3040 storage system configured with the iSCSI protocol
Development and Test Environment

Microsoft Hyper-V for Windows Server 2008, full installation, Enterprise Edition
One 4-node cluster
One 2-node cluster
Three standalone servers
Over 150 child partitions (virtual machines) across the dev and test Hyper-V hosts
Virtualized Applications
Varied, but includes applications in production
Servers and Storage
Dell 2950 servers, each with two quad core processors, 32GB RAM
NetApp FAS3020 storage system configured with the iSCSI protocol

Tech In4mr

Work Item Visualizer for TFS 2010 - Visual Studio Gallery

Export All Assemblies From Sql Server

Simple TF.exe example to add, checkin and delete a file in TFS from the commmand line

Incremental SQL Deploy: When a schema change might cause data in a table to be lost

Why does a SQL table change in a VS2010 DB Project raise the “The schema update is terminating because data loss might occur” deployment error?

Get acquainted with the “Block incremental deployment if data loss might occur”

SQL deployment property

Changes that can raise the “The schema update is terminating because data loss might occur.” error

Avoiding the “The schema update is terminating because data loss might occur.” error

References:

Marcus Aurelius played first edition D&D

"Execute As" clause with a CLR based procedure

Delete those pesky orphan SQL Server Jobs

Hubris, humility and data modelling

TFS Deployer in TFS 2010

My favorite time tracker just hit #1

WCF 4.0 Runtime Endpoint Discovery

WCF 4.0 Runtime Endpoint Discovery

well known TCP Ports

below is a list of well known TCP Ports

Using Optional Outlput Paramaters in a SQL Stored Procedure

Optional Outlput Paramaters in a SQL Stored Procedure

IEnumerable.Except removes duplicates

Except: IEnumerable<T>.Except(IEnumerable<T>);

All duplicate Items in the IEnumerable<TSource> are removed as a result of the Except call.

Use any font you want on your web page. (well almost)

All Major browsers currently support some form of web font embedding.

The Dude is back - *My Favorite Net Mapping Tool*

Network Mapping, Auto Discovery and Network Monitoring, for free

Deserializing XML with extra nodes using schema validation and xs:any

Design a flexible schema using xs:any

SQL Reporting Services Security

SQL Reporting Services missing folders and tabs

Clone a Hyper-V VM without SCVMM

Clone a Hyper-V virtual machine without SCVMM

Create VS2008 snippets by Right-Click, Export to Snippet

Tortoise svn Global-Ignore for Visual Studio solutions files and folders

!(HttpCookieCollection Class Provides a type-safe way to manipulate HTTP cookies.)

Really “Super” Icons

Homemade Superhero Icon set

Microsoft Hyper-V Best Practices

Microsoft whitepapers outlining their best practices for implementing Hyper-V in a production environment

A Massive NetApp Hyper-V Deployment

The Dude is back - My Favorite Net Mapping Tool