tag:blogger.com,1999:blog-63305445592754965712024-03-13T04:00:51.611-07:00Geospatial ProfessionalTechnology Trends, Software, Management, Interesting Problems and Projects, Raw Brain ExtractRoy Jacksonhttp://www.blogger.com/profile/14539201953708702607noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-6330544559275496571.post-6757395907652440202012-04-04T09:39:00.000-07:002012-04-04T09:39:56.212-07:00Census 2010 Blocks in SQL Server 2008 Geography Spatial Type<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div class="MsoNormal">
Loading census data into SQL Server’s Geography Spatial Type
should have been very straightforward. Simply
download the shapefiles from the census ftp site in batch mode, unzip, then
import to a SDE feature class with the geometry storage type set as “GEOGRAPHY”. </div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<o:p></o:p></div>
<div class="MsoNormal">
Unfortunately, I was not that lucky. During the import process with ArcCatalog,
the 8+ million blocks failed after loading around 2.5 million records with a variation
of the following error:<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: red; font-family: "Courier New"; font-size: 8.0pt; mso-no-proof: yes;">Msg 6522, Level 16,
State 1, Line 1<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: red; font-family: "Courier New"; font-size: 8.0pt; mso-no-proof: yes;">A .NET Framework
error occurred during execution of user-defined routine or aggregate
"geography": <o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: red; font-family: "Courier New"; font-size: 8.0pt; mso-no-proof: yes;">System.ArgumentException:
24200: The specified input does not represent a valid geography instance.<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: red; font-family: "Courier New"; font-size: 8.0pt; mso-no-proof: yes;">System.ArgumentException:
<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: red; font-family: "Courier New"; font-size: 8.0pt; mso-no-proof: yes;"> at Microsoft.SqlServer.Types.SqlGeography.ConstructGeographyFromUserInput(GeoData
g, Int32 srid)<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: red; font-family: "Courier New"; font-size: 8.0pt; mso-no-proof: yes;"> at
Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type,
SqlChars taggedText, Int32 srid)<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: red; font-family: "Courier New"; font-size: 8.0pt; mso-no-proof: yes;">.<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal">
Translation: There was some feature in the data that had a
valid shape according to esri, but not Microsoft. <o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
There are some differences between how esri and Microsoft
want the polygon shapes to be drawn, specifically with the order in which the
vertices are drawn (clockwise vs counterclockwise) for the exterior (outer
boundary) ring(s) and interior (donut hole) ring(s). The first attempt was to create a button
in ArcMap which read the geometry from the SDEBINARY geodatabase, created the Well
Known Text and then geography for each feature (accounting for the correct draw
orientation), and then pushed the results into a SQL Server table using ADO.net. <o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
The geometry creation code looks something like this:<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">Dim</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> geo <span style="color: blue;">As</span> <span style="color: blue;">New</span> SqlGeometry()<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">geo =
Microsoft.SqlServer.Types.SqlGeometry.STGeomFromText(<span style="color: blue;">New</span>
SqlChars(<span style="color: blue;">New</span> SqlString(myshapecalc)),
4326).MakeValid<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">mynewrow(<span style="color: #a31515;">"Shape"</span>) = geo.MakeValid<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-size: 10.0pt; line-height: 115%; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-no-proof: yes;">I was fairly happy
with my first results, until I remembered I wasn’t dealing with multipart
geometry, empty geometry, multiple interior rings, and other geo quirks. After some quick modifications, the code was
done and the results were populated, however I still hadnt solved the problem
of getting my data into GEOGRAPHY. When
I modified the code to pump GEOGRAPHY not GEOMETRY, I had the same
failure. Did a great job of solving the
wrong problem – at least now I have a esri to SQL Geometry table loader. <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-size: 10.0pt; line-height: 115%; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-no-proof: yes;"><br /></span></div>
<div class="MsoNormal">
<span style="font-size: 10.0pt; line-height: 115%; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-no-proof: yes;">I was definitely
surprised to realize that you can in fact push unprojected data into the
geometry data type, using the SRID from a geographic coordinate system. I am not really sure why this is not an
acceptable approach, other than geography is typically for Lat / Long
coordinates, and geometry is for feet / meters units. There are definitely stronger limitations on
what can be pushed into each – geography being much more stringent. One we realized this, I pushed all the
polygons (WGS 84 projection) into a geometry dataset using ArcCatalog, then
tried to convert the data to geography from there. <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-size: 10.0pt; line-height: 115%; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-no-proof: yes;"><br /></span></div>
<div class="MsoNormal">
<span style="font-size: 10.0pt; line-height: 115%; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-no-proof: yes;">One recommendation we
found was to use the geometry STUnion and MakeValid functions to try to fix the
shape’s validity from a geography perspective, which can be seen here:<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-size: 10.0pt; line-height: 115%; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-no-proof: yes;"><br /></span></div>
<div class="MsoNormal">
<span style="color: red; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">geography::STGeomFromText(shape.MakeValid().STUnion(shape.STStartPoint()).STAsText(),4326)</span><span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;"><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: red; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;"><br /></span></div>
<div class="MsoNormal">
<span style="font-size: 10.0pt; line-height: 115%; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-no-proof: yes;">This is taking the source
polygon, running the MakeValid() operation, then unioning the polygon with it’s
first vertex, and finally converting the result to geography. Doing so re-orders the vertices so that they
are drawn in the correct order. <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-size: 10.0pt; line-height: 115%; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-no-proof: yes;"><br /></span></div>
<div class="MsoNormal">
<span style="font-size: 10.0pt; line-height: 115%; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-no-proof: yes;">Using this query, I
was able to start pumping Geography records, unfortunately we still found that
the conversion failed. <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-size: 10.0pt; line-height: 115%; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-no-proof: yes;">At this point, I
needed to isolate the record or records that were causing the process to fail,
so I created this query:<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-size: 10.0pt; line-height: 115%; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-no-proof: yes;"><br /></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">declare</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> @objectid <span style="color: blue;">int<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">Declare</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> rowcursor <span style="color: blue;">CURSOR</span> <span style="color: blue;">FAST_FORWARD</span> <span style="color: blue;">FOR<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">select</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> objectid<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">from</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> dbo<span style="color: grey;">.</span>CENSUS_2010_TABBLOCK_GEOMETRY_IMPORT2<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">order</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: blue;">by</span> objectid<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">OPEN</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> rowcursor<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">FETCH</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: blue;">NEXT</span> <span style="color: blue;">FROM</span> rowcursor<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">INTO</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> @objectid<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">WHILE</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: magenta;">@@FETCH_STATUS</span> <span style="color: grey;">=</span> 0<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">BEGIN<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">declare</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> @sqlinsert
<span style="color: blue;">varchar</span><span style="color: grey;">(</span><span style="color: magenta;">max</span><span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">set</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> @sqlinsert
<span style="color: grey;">=</span> <span style="color: red;">'<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: red; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">insert into <o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: red; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">dbo.CENSUS_2010_TABBLOCK_GEOGRAPHY<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: red; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">select objectid,
blkidfp00,<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: red; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">geography::STGeomFromText(shape.MakeValid().STUnion(shape.STStartPoint()).STAsText(),4326)<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: red; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">from <o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: red; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">dbo.CENSUS_2010_TABBLOCK_GEOMETRY_IMPORT2<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: red; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">where objectid = '</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: grey;">+</span> <span style="color: magenta;">convert</span><span style="color: grey;">(</span><span style="color: blue;">varchar</span><span style="color: grey;">(</span>255<span style="color: grey;">),</span>@objectid<span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">begin</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: blue;">try<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">exec</span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">(</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">@sqlinsert<span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">end</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: blue;">try<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">begin</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: blue;">catch<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: blue;">insert</span> <span style="color: blue;">into</span>
geography_failures<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: blue;">select</span> @objectid<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">end</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: blue;">catch<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">FETCH</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: blue;">NEXT</span> <span style="color: blue;">FROM</span> rowcursor<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> <span style="color: blue;">INTO</span>
@objectid<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">END<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">CLOSE</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> rowcursor<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;">DEALLOCATE</span><span style="font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"> rowcursor<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-size: 10.0pt; line-height: 115%; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-no-proof: yes;">Here, I am taking all
input records, iteration thru each, attempting to push the input row into a
geography table, and where that doesn’t work, dumping the result into an error
table. This was very slow to run (1 day
21 hours), but it ended up showing me the feature that was causing the problem:<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-size: 10.0pt; line-height: 115%; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-no-proof: yes;"><br /></span></div>
<div class="MsoNormal">
<span style="color: red; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">POLYGON ((-73.539727170912329
41.134362625800748, -73.534610028565623 41.142645381690613, -73.538288255665464
41.136691869856634, -73.539808109894977 41.1357116088447, -73.540023947182021
41.134416585122494, -73.53999696752112 41.134416585122494, -73.539727170912329
41.134362625800748))<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: red; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">On a map, a pretty standard polygon with a pretty
ugly and unneeded set of vertices shooting off in one direction. <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXC87rYqiQmk8yrZAjmZpuBW-R8E20jjTQkyfLi_CYRVOvXR94MTpet9lCRKvKESo3ImP2gm7Xk_HBm0f2WoGJ23vDszHXdHAbUUfIDDHp6OvCBNUKYcKWOfaYba3k8E8bdQqbnoXJ9l8/s1600/Bad_Geometry.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="416" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXC87rYqiQmk8yrZAjmZpuBW-R8E20jjTQkyfLi_CYRVOvXR94MTpet9lCRKvKESo3ImP2gm7Xk_HBm0f2WoGJ23vDszHXdHAbUUfIDDHp6OvCBNUKYcKWOfaYba3k8E8bdQqbnoXJ9l8/s640/Bad_Geometry.PNG" width="640" /></a></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">Unfortunately, there is not really a clean way
of handling this shape, as far as I can determine, other than to manipulate the
shape coordinates, and manually load this single feature into the table. <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">In the end, we have learned there are some
issues with the census 2010 geometry, including gaps, overlaps, and funky
topological discrepancies. We also
learned that it can be a major hassle with significant roadblocks to try to
ditch esri formats when performing spatial operations inside the SQL database. Finally, we re-learned during QA that esri
handles cluster tolerance and spatial resolution, whereas SQL does not. In this instance, esri was giving bad results
along some polygon borders, whereas SQL was giving correct polygon association
every time. <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">But, this effort is definitely worth it. With proper spatial indexing and other logic,
I was able to assign census blocks to 128 million input records in about 6
hours, which is way faster than any other esri options. And we have some very handy reusable code for
the future. <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: 'Courier New';"><span style="font-size: 14px; line-height: 16px;"><br /></span></span></div>
</div>Roy Jacksonhttp://www.blogger.com/profile/14539201953708702607noreply@blogger.com1tag:blogger.com,1999:blog-6330544559275496571.post-85154879696478573612012-03-15T09:21:00.004-07:002012-03-15T09:21:47.462-07:00Rebuilding ArcGIS Server / Troubleshooting Proxy.ashx<div dir="ltr" style="text-align: left;" trbidi="on">
Recently my ArcGIS Server went down. For some unknown reason the SOM process would not start. <br />
<br />
<span style="background-color: #fafafa; color: #333333; font-family: Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif; font-size: 13px;">The error message in the system logs was:</span><br style="background-color: #fafafa; color: #333333; font-family: Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif; font-size: 13px;" /><br style="background-color: #fafafa; color: #333333; font-family: Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif; font-size: 13px;" /><span style="background-color: #fafafa; color: #333333; font-family: Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif; font-size: 13px;">The ArcGIS Server Object Manager service failed to start due to the following error: </span><br style="background-color: #fafafa; color: #333333; font-family: Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif; font-size: 13px;" /><b style="background-color: #fafafa; color: #333333; font-family: Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif; font-size: 13px;">ArcGIS Server Object Manager is not a valid Win32 application. </b><br />
<br style="background-color: #fafafa; color: #333333; font-family: Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif; font-size: 13px;" /><span style="background-color: #fafafa; color: #333333; font-family: Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif; font-size: 13px;">I also had this error in the Application log:</span><br style="background-color: #fafafa; color: #333333; font-family: Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif; font-size: 13px;" /><br style="background-color: #fafafa; color: #333333; font-family: Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif; font-size: 13px;" /><span style="background-color: #fafafa; color: #333333; font-family: Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif; font-size: 13px;">The application, C:\Program Files (x86)\ArcGIS\Server10.0\bin\ArcSOM.exe, generated an application error The error occurred on 03/12/2012 @ 13:58:12.647 The exception generated was c0000005 at address 0280C218 (ServerCore)</span><br />
<br />
I started by doing many iterations of re-installing and configuring everything, which did not help. I tried making some recommended registry changes which also did not help. Eventually I came to believe that the problem was with the operating system, and started searching for similar cases where a windows service would not start. <br />
<br />
I found this post which was very helpful:<br />
<br />
<a href="http://www.technipages.com/error-193-1-is-not-a-valid-win32-application.html">http://www.technipages.com/error-193-1-is-not-a-valid-win32-application.html</a><br />
<br />
It turned out that there was a file named "c:\program" that had been created by some unknown process the day before the application stopped running. Because of the space in the folder name "Program Files", the system was getting confused and tried to run the "c:\program" file instead of my service file. Deleting the file "c:\program" allowed my service to start. <br />
<br />
Unfortunately I had basically nuked all my configuration settings with permissions / SSL etc, which had to be rebuild. Of course that wasn't smooth and took several iterations of uninstall / install / configure to get right. On the last iteration I removed the ArcGIS server configuration folder "c:\inetpub\wwwroot\arcgis", which allowed a clean recreate of all those settings. Then I rebuilt everything using esri's documentation. <br />
<br />
Next, my application was not working. It uses dynamic token authentication via proxy as described here:<br />
<br />
<a href="http://forums.esri.com/Thread.asp?c=158&f=2396&t=297001#926770">http://forums.esri.com/Thread.asp?c=158&f=2396&t=297001#926770</a><br />
<br />
Each request to the server from the proxy page received a 401 Error- I thought that it was a problem with the configuration of the token service, but eventually after trying out every possible IIS configuration, and confirming that the token was being received, I figured there was an issue with the code. This was probably compounded by my authentication setting on the tokens virtual directory, which was set to integrated windows authentication (which was the only option that allowed the Rest directory and ArcCatalog to work from the local and remote machines). <br />
<br />
I added the code below to my proxy page code from <a href="http://forums.asp.net/t/1265699.aspx/1">this</a> blog post. <br />
<br />
<span style="background-color: #ebf3de; color: #222222; font-family: 'Segoe UI', Tahoma, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto;">webClient.Credentials=new NetworkCredential( "username","password","domain");</span><br />
<br />
Which made the application work again. Sounds easy, only took about 5 hours to figure out :)<br />
<br />
Hopefully this can help someone out there! The whole process starting with the server not working took about 24 working hours to resolve. Soooo tired....</div>Roy Jacksonhttp://www.blogger.com/profile/14539201953708702607noreply@blogger.com0tag:blogger.com,1999:blog-6330544559275496571.post-91037833251309129242012-02-23T11:57:00.000-08:002012-02-23T12:09:18.518-08:00Select Distinct for ArcGIS 10<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div class="MsoNormal">
Writing tools in VB.net is very easy to do with the ArcGIS
10 Snap-In framework. This is made even
better by the fact that you can code with Visual Studio 2008 Express Edition,
which is free, instead of paying for Visual Studio Professional as with ArcGIS
931.<br />
<br /></div>
<div class="MsoNormal">
<o:p></o:p></div>
<div class="MsoNormal">
The Select Distinct Tool that I will demonstrate is used to
select unique values in your attributes.
Very useful for identifying duplicated attributes (such as IDS), or
getting unique values based on attribute sorting (for example, give me the records
having the longest segment length for each unique street name). You can do this with SQL queries as well, but
this tool is usually easier to do when your data is in file geodatabase or
shapefile format. If the data is in SQL
server I would generally not recommend this tool. <o:p></o:p><br />
<br /></div>
<div class="MsoNormal">
Once you have installed VB express, and then the DotNet SDK
for ArcGIS (and the service packs), creating a command button toolbar is very
straightforward:<o:p></o:p></div>
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
1<br />
<b> Create a new project:</b><o:p></o:p></div>
<div class="MsoNormal">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjx2Tv84fvYMmHpzb8aHOiATndOLdqt5dBh5kPxyRh2VCzzUGOz16hdtYd81yvTujVtC1JLNXEq0vl-umgUwA4UapJij1C4krEqXG0cwhcvlzyn7-CxfcMLKmDbs74wnqnNVWyw8k91XMs/s1600/CreateProject.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="191" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjx2Tv84fvYMmHpzb8aHOiATndOLdqt5dBh5kPxyRh2VCzzUGOz16hdtYd81yvTujVtC1JLNXEq0vl-umgUwA4UapJij1C4krEqXG0cwhcvlzyn7-CxfcMLKmDbs74wnqnNVWyw8k91XMs/s320/CreateProject.PNG" width="320" /></a></div>
<div class="MsoNormal">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjx2Tv84fvYMmHpzb8aHOiATndOLdqt5dBh5kPxyRh2VCzzUGOz16hdtYd81yvTujVtC1JLNXEq0vl-umgUwA4UapJij1C4krEqXG0cwhcvlzyn7-CxfcMLKmDbs74wnqnNVWyw8k91XMs/s1600/CreateProject.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiA3LbUt50Zwjk73MWYpRcCfc91KZoj41NDeBv7k-M7hJXxAvaFV3e0wDKQZAfnNnMZRDlIiACBZSmJsXnqUfrSMJ7O_OLceHZf-0MP-9sV8asPHuiyRkAdxuowQylpLpuocGgE6X_0y7k/s1600/Addin1.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="227" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiA3LbUt50Zwjk73MWYpRcCfc91KZoj41NDeBv7k-M7hJXxAvaFV3e0wDKQZAfnNnMZRDlIiACBZSmJsXnqUfrSMJ7O_OLceHZf-0MP-9sV8asPHuiyRkAdxuowQylpLpuocGgE6X_0y7k/s320/Addin1.PNG" width="320" /></a><br />
<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<b></b><br />
<div class="MsoNormal">
<b><b><br /></b></b></div>
<b>
</b><br />
<div class="MsoNormal">
<b><b><br /></b></b></div>
<b>
</b><br />
<div class="MsoNormal">
<b><b><br /></b></b></div>
<b>
<div class="MsoNormal">
<b><br /></b></div>
<div class="MsoNormal">
<b><br /></b></div>
<div class="MsoNormal">
<b><br /></b></div>
<div class="MsoNormal">
<b><br /></b></div>
<div class="MsoNormal">
<b><br /></b></div>
<div class="MsoNormal">
<b><br /></b></div>
<div class="MsoNormal">
<b><br /></b></div>
<div class="MsoNormal">
<b><br /></b></div>
<div class="MsoNormal">
<b><br /></b></div>
Choose Button as your Add-in type:</b>
<o:p></o:p><br />
<b><br /></b><br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDOTBY76aJRpXikVwXb9hGb_jZlbyTXq5TiETLfeA6hiyEPY1RATgIp5pPqnCP5SwuyyQLcUGULnOzh7HvM2y8q1z7cI7hSq03x2CS3KjvDg1NU6V4x4J_TYuKlURMGNJc3SVMQ1REQzE/s1600/Addin2.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="232" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDOTBY76aJRpXikVwXb9hGb_jZlbyTXq5TiETLfeA6hiyEPY1RATgIp5pPqnCP5SwuyyQLcUGULnOzh7HvM2y8q1z7cI7hSq03x2CS3KjvDg1NU6V4x4J_TYuKlURMGNJc3SVMQ1REQzE/s320/Addin2.PNG" width="320" /></a><b><br /></b><br />
<b><br /></b><br />
<br /></div>
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="font-size: 7pt;"> </span><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<b>Add the ArcGIS ArcObjects SDK references you
will need for your logic:</b><o:p></o:p></div>
<div class="MsoNormal">
<br />
<o:p></o:p></div>
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYk8S-TyiRKaJBzCcRjpfvXG6ziP2s9m5n72JsrN6X_IStORCWhY60DHUapRXuaRGKME2y6MCAMQYa6Y3pJydEHzJd6e_oafLGaKYAz30BJn7Dv8_b3o3B8WXj9DzXmP8jHtaAy1QD9ss/s1600/AddRef.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="268" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYk8S-TyiRKaJBzCcRjpfvXG6ziP2s9m5n72JsrN6X_IStORCWhY60DHUapRXuaRGKME2y6MCAMQYa6Y3pJydEHzJd6e_oafLGaKYAz30BJn7Dv8_b3o3B8WXj9DzXmP8jHtaAy1QD9ss/s320/AddRef.PNG" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<b>Modify the Config.esriaddinx file to contain a
toolbar containing your button. </b><br />
<br />
Auto
Complete makes the toolbar configuration very simple! You will have to add the bolded Toolbars
section below manually, but the Commands section is created automatically using the
wizard. <o:p></o:p><br />
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"><</span><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">ESRI.Configuration</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> </span><span style="color: red; font-family: 'Courier New'; font-size: 10pt;">xmlns</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">=</span><span style="font-family: 'Courier New'; font-size: 10pt;">"<span style="color: blue;">http://schemas.esri.com/Desktop/AddIns</span>"<span style="color: blue;"> </span><span style="color: red;">xmlns:xsi</span><span style="color: blue;">=</span>"<span style="color: blue;">http://www.w3.org/2001/XMLSchema-instance</span>"<span style="color: blue;">><o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> <</span><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">Name</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">></span><span style="font-family: 'Courier New'; font-size: 10pt;">MyNewAddIn<span style="color: blue;"></</span><span style="color: #a31515;">Name</span><span style="color: blue;">><o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> <</span><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">AddInID</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">></span><span style="font-family: 'Courier New'; font-size: 10pt;">{2e9f459d-a591-4016-95b2-27ddc30ad802}<span style="color: blue;"></</span><span style="color: #a31515;">AddInID</span><span style="color: blue;">><o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> <</span><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">Description</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">></span><span style="font-family: 'Courier New'; font-size: 10pt;">Type in a
description for this Add-in.<span style="color: blue;"></</span><span style="color: #a31515;">Description</span><span style="color: blue;">><o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> <</span><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">Version</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">></span><span style="font-family: 'Courier New'; font-size: 10pt;">1.0<span style="color: blue;"></</span><span style="color: #a31515;">Version</span><span style="color: blue;">><o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> <</span><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">Image</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">></span><span style="font-family: 'Courier New'; font-size: 10pt;">Images\MyNewAddIn.png<span style="color: blue;"></</span><span style="color: #a31515;">Image</span><span style="color: blue;">><o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> <</span><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">Author</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">></span><span style="font-family: 'Courier New'; font-size: 10pt;">roy.jackson<span style="color: blue;"></</span><span style="color: #a31515;">Author</span><span style="color: blue;">><o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> <</span><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">Company</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">></span><span style="font-family: 'Courier New'; font-size: 10pt;">GISPROBLOG<span style="color: blue;"></</span><span style="color: #a31515;">Company</span><span style="color: blue;">><o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> <</span><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">Date</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">></span><span style="font-family: 'Courier New'; font-size: 10pt;">2/23/2012<span style="color: blue;"></</span><span style="color: #a31515;">Date</span><span style="color: blue;">><o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> <</span><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">Targets</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> <</span><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">Target</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">
</span><span style="color: red; font-family: 'Courier New'; font-size: 10pt;">name</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">=</span><span style="font-family: 'Courier New'; font-size: 10pt;">"<span style="color: blue;">Desktop</span>"<span style="color: blue;"> </span><span style="color: red;">version</span><span style="color: blue;">=</span>"<span style="color: blue;">10.0</span>"<span style="color: blue;"> /><o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> </</span><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">Targets</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> <</span><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">AddIn</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">
</span><span style="color: red; font-family: 'Courier New'; font-size: 10pt;">language</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">=</span><span style="font-family: 'Courier New'; font-size: 10pt;">"<span style="color: blue;">CLR</span>"<span style="color: blue;"> </span><span style="color: red;">library</span><span style="color: blue;">=</span>"<span style="color: blue;">MyNewAddIn.dll</span>"<span style="color: blue;"> </span><span style="color: red;">namespace</span><span style="color: blue;">=</span>"<span style="color: blue;">MyNewAddIn</span>"<span style="color: blue;">><o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> <</span><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">ArcMap</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> <</span><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">Commands</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; margin-left: 1in; margin-right: 0in; margin-top: 0in;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"><</span><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">Button</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">
</span><span style="color: red; font-family: 'Courier New'; font-size: 10pt;">id</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">=</span><span style="font-family: 'Courier New'; font-size: 10pt;">"<span style="color: blue;">GISPROBLOG_MyNewAddIn_SelectDistinct</span>"<span style="color: blue;"> </span><span style="color: red;">class</span><span style="color: blue;">=</span>"<span style="color: blue;">SelectDistinct</span>"<span style="color: blue;"> </span><span style="color: red;">message</span><span style="color: blue;">=</span>"<span style="color: blue;">Add-in command
generated by Visual Studio project wizard.</span>"<span style="color: blue;">
</span><span style="color: red;">caption</span><span style="color: blue;">=</span>"<span style="color: blue;">Select Distinct</span>"<span style="color: blue;"> </span><span style="color: red;">tip</span><span style="color: blue;">=</span>"<span style="color: blue;">Add-in command tooltip.</span>"<span style="color: blue;">
</span><span style="color: red;">category</span><span style="color: blue;">=</span>"<span style="color: blue;">Add-In Controls</span>"<span style="color: blue;"> </span><span style="color: red;">image</span><span style="color: blue;">=</span>"<span style="color: blue;">Images\SelectDistinct.png</span>"<span style="color: blue;"> /><o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> </</span><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">Commands</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> <b><</b></span><b><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">Toolbars</span></b><b><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">><o:p></o:p></span></b></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; margin-left: 1in; margin-right: 0in; margin-top: 0in;">
<b><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"><</span></b><b><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">Toolbar</span></b><b><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> </span></b><b><span style="color: red; font-family: 'Courier New'; font-size: 10pt;">id</span></b><b><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">=</span></b><b><span style="font-family: 'Courier New'; font-size: 10pt;">"<span style="color: blue;">GISPROBLOG_SELECTDISTINCT</span>"<span style="color: blue;"> </span><span style="color: red;">caption</span><span style="color: blue;">=</span>"<span style="color: blue;">GISPROBLOG_TOOLS</span>"<span style="color: blue;"> </span><span style="color: red;">showInitially</span><span style="color: blue;">=</span>"<span style="color: blue;">true</span>"<span style="color: blue;">><o:p></o:p></span></span></b></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<b><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> <</span></b><b><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">Items</span></b><b><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">><o:p></o:p></span></b></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<b><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> <</span></b><b><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">Button</span></b><b><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> </span></b><b><span style="color: red; font-family: 'Courier New'; font-size: 10pt;">refID</span></b><b><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">=</span></b><b><span style="font-family: 'Courier New'; font-size: 10pt;">"<span style="color: blue;">GISPROBLOG_MyNewAddIn_SelectDistinct</span>"<span style="color: blue;"> /><o:p></o:p></span></span></b></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<b><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> </</span></b><b><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">Items</span></b><b><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">><o:p></o:p></span></b></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<b><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> </</span></b><b><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">Toolbar</span></b><b><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">><o:p></o:p></span></b></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> <b></</b></span><b><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">Toolbars</span></b><b><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">><o:p></o:p></span></b></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> </</span><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">ArcMap</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> </</span><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt;">AddIn</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt; line-height: 115%;"></</span><span style="color: #a31515; font-family: 'Courier New'; font-size: 10pt; line-height: 115%;">ESRI.Configuration</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt; line-height: 115%;">></span><o:p></o:p><br />
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt; line-height: 115%;"><br /></span><br />
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt; line-height: 115%;"><br /></span></div>
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="font-size: 7pt;"> </span> <b>Configure
the button class to launch the main form code. </b><br />
<br />
Note the new <b>My.ArcMap</b> object!<o:p></o:p><br />
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">Imports</span><span style="font-family: 'Courier New'; font-size: 10pt;">
ESRI.ArcGIS.ArcMapUI<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">Imports</span><span style="font-family: 'Courier New'; font-size: 10pt;">
ESRI.ArcGIS.SystemUI<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">Imports</span><span style="font-family: 'Courier New'; font-size: 10pt;">
ESRI.ArcGIS.ADF<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">Imports</span><span style="font-family: 'Courier New'; font-size: 10pt;">
ESRI.ArcGIS.esriSystem<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">Imports</span><span style="font-family: 'Courier New'; font-size: 10pt;">
ESRI.ArcGIS.Carto<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">Imports</span><span style="font-family: 'Courier New'; font-size: 10pt;">
ESRI.ArcGIS.Geometry<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">Imports</span><span style="font-family: 'Courier New'; font-size: 10pt;">
ESRI.ArcGIS.Geodatabase<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">Imports</span><span style="font-family: 'Courier New'; font-size: 10pt;">
ESRI.ArcGIS.Framework<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">Imports</span><span style="font-family: 'Courier New'; font-size: 10pt;">
ESRI.ArcGIS<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">Public</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">Class</span> SelectDistinct<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">Inherits</span>
ESRI.ArcGIS.Desktop.AddIns.Button<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">Dim</span> m_pMxDoc
<span style="color: blue;">As</span> IMxDocument<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">Dim</span> m_pApp <span style="color: blue;">As</span> IMxApplication<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <b><span style="color: blue;">Public</span> myForm <span style="color: blue;">As</span> <span style="color: blue;">New</span> frmSelectDistinct<o:p></o:p></b></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">Public</span> <span style="color: blue;">Sub</span> <span style="color: blue;">New</span>()<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> m_pApp = <b><span style="color: blue;">My</span>.ArcMap.Application</b><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> m_pMxDoc = <b><span style="color: blue;">My</span>.ArcMap.Application.Document</b><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">End</span> <span style="color: blue;">Sub<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">Protected</span> <span style="color: blue;">Overrides</span> <span style="color: blue;">Sub</span>
OnClick()<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<b><span style="font-family: 'Courier New'; font-size: 10pt;"> myForm = <span style="color: blue;">New</span> frmSelectDistinct<o:p></o:p></span></b></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<b><span style="font-family: 'Courier New'; font-size: 10pt;"> myForm.m_app = <span style="color: blue;">My</span>.ArcMap.Application<o:p></o:p></span></b></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<b><span style="font-family: 'Courier New'; font-size: 10pt;"> myForm.Show()<o:p></o:p></span></b></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<b><span style="font-family: 'Courier New'; font-size: 10pt;"> myForm.TopMost = <span style="color: blue;">True<o:p></o:p></span></span></b></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">End</span> <span style="color: blue;">Sub<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">Protected</span> <span style="color: blue;">Overrides</span> <span style="color: blue;">Sub</span>
OnUpdate()<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">End</span> <span style="color: blue;">Sub<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">End</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">Class<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoListParagraphCxSpFirst" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<b><span style="font-size: 7pt;"> </span>Implement the frmSelectDistinct.vb code (see the download link at the end of the article)</b><o:p></o:p><br />
<b></b></div>
<div class="MsoListParagraphCxSpLast" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<br />
<span style="font-size: 7pt;"></span> <b>Run the Project! </b><br />
<br />
Your toolbar will be available from the customize menu:</div>
<div class="MsoNormal">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihYXeK7PHByrD-XafSoGh2b8qjO-jtbhaAibfg3iGSILNui6Q0lzdclpasEfYVLW8n8GFS5bpmfS-WWKyiRjHXVhaRWzduLR9pDRWXa6-d7JpRqhnFXvZaaLvCQwEJoW8Qzcwvlhrm3oA/s1600/ToolbarAdded.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="184" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihYXeK7PHByrD-XafSoGh2b8qjO-jtbhaAibfg3iGSILNui6Q0lzdclpasEfYVLW8n8GFS5bpmfS-WWKyiRjHXVhaRWzduLR9pDRWXa6-d7JpRqhnFXvZaaLvCQwEJoW8Qzcwvlhrm3oA/s320/ToolbarAdded.PNG" width="320" /></a></div>
</div>
<div class="MsoNormal">
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
Another great element of the snap-in framework is that it is
very easy to distribute the actual file that makes the code available to the
end user. <b>Simply provide the user the
with the .addin file, and once they double-click it the code is installed!</b> No more windows installer requirements. To remove the addin, you can look in
ArcCatalog’s addin folder in the home geodatabase and delete from there by
right clicking and choosing delete:<o:p></o:p></div>
<div align="center" class="MsoNormal" style="text-align: center;">
<br />
<o:p></o:p></div>
<div class="MsoNormal">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjWbuJTMdcuc6rJ8vJ_bqMTTjUtWNJcAeT7TEn4c2E8qBbgh-2LjlukhPHScWEr0NZh6U1mIeUnpRKSNy0VJXm0WgghJtfROP-0sW8WwNDmGaMcdwcc1TtBLnUzlKQCfZnmJNgyrAHvcC8/s1600/RemoveADdin.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="244" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjWbuJTMdcuc6rJ8vJ_bqMTTjUtWNJcAeT7TEn4c2E8qBbgh-2LjlukhPHScWEr0NZh6U1mIeUnpRKSNy0VJXm0WgghJtfROP-0sW8WwNDmGaMcdwcc1TtBLnUzlKQCfZnmJNgyrAHvcC8/s320/RemoveADdin.PNG" width="320" /></a><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
I have seen some issues using ESRI SDK objects in your code when the
end user doesn’t have the development environment installed, but these can be
handled with various configuration settings.
<o:p></o:p><br />
<br />
Also, custom icons were a pain to figure out, but just change the "Build Action" property of the icon to "AddInContent", and "Copy to Output Directory" to "Copy Option:. </div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
The source code, and
.addin file for this project (look in the bin/debug folder), can be downloaded
here:<o:p></o:p></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: #0000ee; font-family: 'Courier New';"><span style="font-size: 14px;"><u><a href="http://dl.dropbox.com/u/63807183/SelectDistinct/SelectDistinct_v10.zip">http://dl.dropbox.com/u/63807183/SelectDistinct/SelectDistinct_v10.zip</a></u></span></span><br />
<span style="color: #0000ee; font-family: 'Courier New';"><span style="font-size: 15px;"><u><br /></u></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
The installer can be downloaded here:<br />
<br />
<a href="http://dl.dropbox.com/u/63807183/SelectDistinct/SelectDistinctAddIn.esriAddIn">http://dl.dropbox.com/u/63807183/SelectDistinct/SelectDistinctAddIn.esriAddIn</a><br />
<br />
I hope this was helpful!</div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<o:p></o:p></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br />
<br />
<br /></div>
</div>Roy Jacksonhttp://www.blogger.com/profile/14539201953708702607noreply@blogger.com0tag:blogger.com,1999:blog-6330544559275496571.post-63852271980727630202012-01-30T16:18:00.000-08:002012-01-30T16:18:36.081-08:00SQL Server Database Notifications<div dir="ltr" style="text-align: left;" trbidi="on">
Oftentimes I am stuck wondering if a database query has completed,
or a certain amount of progress on a multi-step query has been made.<span> </span>I can always backtrack after the query has
completed by adding print statements to my lengthy batch files to see how each
section performs:<br />
<br />
<div class="MsoNormal">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">print</span><span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;"> <span style="color: red;">'Step 1 complete... '</span> <span style="color: grey;">+</span>
<span style="color: magenta;">convert</span><span style="color: grey;">(</span><span style="color: blue;">varchar</span><span style="color: grey;">(</span>255<span style="color: grey;">),</span></span><span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;"><span style="color: magenta;">getdate</span><span style="color: grey;">())<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;"><span style="color: grey;"><br /></span></span></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
… however, the downside is that the messages don’t appear
until the query has fully completed, which doesn’t help that much.<span> </span><o:p></o:p></div>
<div class="MsoNormal">
<span><br /></span></div>
<div class="MsoNormal">
I have found two workable options for getting notification
of a SQL Server Process Status, both during a process and upon completion.<span> </span><o:p></o:p></div>
<div class="MsoNormal">
<span><br /></span></div>
<div class="MsoNormal">
<b>Growl for Windows<o:p></o:p></b></div>
<div class="MsoNormal">
<b><br /></b></div>
<div class="MsoNormal">
The first option is using <a href="http://www.growlforwindows.com/gfw/default.aspx">Growl for Windows</a>+ <span style="color: red; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">xp_cmdshell</span> <o:p></o:p></div>
<div class="MsoNormal">
<span style="color: red; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;"><br /></span></div>
<div class="MsoNormal">
From the Growl site:<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Trebuchet MS","sans-serif";">Put
simply, Growl lets you know when things happen. Files finished downloading, friends
came online, new email has arrived - Growl can let you know when any event
occurs with a subtle notification.</span><o:p></o:p></div>
<div class="MsoNormal">
<span style="font-family: "Trebuchet MS","sans-serif";"><br /></span></div>
<div class="MsoNormal">
The version for Mac appears to be much more complete in terms of hooks to COTS software, but
with some simple configuration work I can get what I want.<span> </span><o:p></o:p></div>
<div class="MsoNormal">
<span><br /></span></div>
<div class="MsoNormal">
After installing the main software package, download the
command line program as well:<o:p></o:p></div>
<div class="MsoNormal">
<a href="http://www.growlforwindows.com/gfw/help/growlnotify.aspx">GrowlNotify
Command Line Script</a><o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Now, all we have to do is create a command line string and
execute it (just as easily with command prompt, shell in VB etc.).<span> </span>The query below contains the full syntax for
wrapping a Growl notify command for use in xp_cmdshell, which is a great way to
run command line statements from SQL Server Management Studio.<span> </span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Once xp_cmdshell is enabled, simply copy and
paste the code below into a query window:<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;">declare</span><span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;"> @myalert <span style="color: blue;">varchar</span><span style="color: grey;">(</span><span style="color: magenta;">max</span><span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;">set</span><span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;"> @myalert <span style="color: grey;">=</span> <span style="color: red;">'D:\workspace\code_checkouts\growl\growlnotify
/t:Hey_There /s:true /host:localhost /p:2 "Query Complete\n '</span> <span style="color: grey;">+</span> <span style="color: magenta;">convert</span><span style="color: grey;">(</span><span style="color: blue;">varchar</span><span style="color: grey;">(</span>255<span style="color: grey;">),</span><span style="color: magenta;">getdate</span><span style="color: grey;">())<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;">declare</span><span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;"> @mysql <span style="color: blue;">varchar</span><span style="color: grey;">(</span><span style="color: magenta;">max</span><span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;">set</span><span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;"> @mysql <span style="color: grey;">=</span> <span style="color: red;">'EXEC xp_cmdshell '''</span>
<span style="color: grey;">+</span>@myalert <span style="color: grey;">+</span> <span style="color: red;">''''<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;">exec </span><span style="color: grey; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;">(</span><span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;">@mysql<span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
And you will get a notification window popup:</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgjeDI5VGruStlrBCqnmxJ8g299WOhBiWFrCVfvFsctnxhs6Ii7tX9BcZT_AqSGtuYDcPJJztP8SLlSWniXg9Rh4Yjoyp4BlyNMxttngnDcQyw17DuGr4rEqQ7W0MtHW7hGl0yto5rsv7M/s1600/GrowlIcon.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgjeDI5VGruStlrBCqnmxJ8g299WOhBiWFrCVfvFsctnxhs6Ii7tX9BcZT_AqSGtuYDcPJJztP8SLlSWniXg9Rh4Yjoyp4BlyNMxttngnDcQyw17DuGr4rEqQ7W0MtHW7hGl0yto5rsv7M/s1600/GrowlIcon.png" /></a></div>
<div class="MsoNormal">
This is useful when I am at my workstation, but I also want
to receive an email if I am away to save me the energy of logging in every
couple hours to check progress.<span> </span><o:p></o:p></div>
<div class="MsoNormal">
<span><br /></span></div>
<div class="MsoNormal">
Configuring Growl to use email is fairly easy once you know
the settings.<span> </span>For Gmail account settings:<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<br /><div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiXRsjDxCbdEUbX8JrIBaAR3Vkjwurj0jdnS4Jp9h7nAL54TZgcW6HZErZdBBE-Jxo3qoRtDdtREC1-n9KA0YJP74th97aZzBcU4lh7XYCqhijLzxdzl5xZ0jB6X3KVxlbrUuO3gmx0pP4/s1600/GrowlEmail.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="199" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiXRsjDxCbdEUbX8JrIBaAR3Vkjwurj0jdnS4Jp9h7nAL54TZgcW6HZErZdBBE-Jxo3qoRtDdtREC1-n9KA0YJP74th97aZzBcU4lh7XYCqhijLzxdzl5xZ0jB6X3KVxlbrUuO3gmx0pP4/s320/GrowlEmail.png" width="320" /></a></div>
<div class="MsoNormal">
<span><br /></span></div>
<div class="MsoNormal">
Then enable the email notification for growlnotify:<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhYSMMMGrlLmZGYfnQtC3zlQy-5mI4elqqhnh84bk02c_3EGgticXWMBkjKAZdhLyapWZRgii0t_D7JXUXdtlfeyEXAVFGGDjhzytilNRx_Uhn7_aV0fT2oSGw_VJrFJpxi6wK3hsYiiJ0/s1600/GrowlForward.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="197" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhYSMMMGrlLmZGYfnQtC3zlQy-5mI4elqqhnh84bk02c_3EGgticXWMBkjKAZdhLyapWZRgii0t_D7JXUXdtlfeyEXAVFGGDjhzytilNRx_Uhn7_aV0fT2oSGw_VJrFJpxi6wK3hsYiiJ0/s320/GrowlForward.png" width="320" /></a></div>
<br /><div class="MsoNormal">
<span><br /></span></div>
<div class="MsoNormal">
Now, in addition to a popup, I get an email in my inbox:<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEha-2Ty_NVM7MZ9azOxqZvEHpOH4NduFN4cyu7avbEVZr6UZV4sgD65JsFqTBxw0cL30bNxhcfWytq3v5zasEVeEpS_z5cvAux4vO1Ry6bAx13AqxpUUzgKTKD_g5VgmN-1kDCl59wzvCc/s1600/GrowlSampleEmail.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="284" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEha-2Ty_NVM7MZ9azOxqZvEHpOH4NduFN4cyu7avbEVZr6UZV4sgD65JsFqTBxw0cL30bNxhcfWytq3v5zasEVeEpS_z5cvAux4vO1Ry6bAx13AqxpUUzgKTKD_g5VgmN-1kDCl59wzvCc/s320/GrowlSampleEmail.png" width="320" /></a></div>
<br /><div class="MsoNormal">
<span><br /></span></div>
<div class="MsoNormal">
<b>SQL Server Database
Mail<o:p></o:p></b></div>
<div class="MsoNormal">
<b><br /></b></div>
<div class="MsoNormal">
Another, probably better, option is using the built in SQL
Server 2008 functionality called Database Mail.<span>
</span><o:p></o:p></div>
<div class="MsoNormal">
This excellent walkthrough explains the whole concept:<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<a href="http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/">http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/</a><o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
I used this query to enable the functionality in SSMS:<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;">GO<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;">RECONFIGURE</span><span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;"> <span style="color: blue;">with</span> <span style="color: blue;">OVERRIDE<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;">GO<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: maroon; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;">sp_CONFIGURE</span><span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;"> <span style="color: red;">'Database Mail XPs'</span><span style="color: grey;">,</span> 1<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;">GO<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;">RECONFIGURE</span><span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;"> <span style="color: blue;">with</span> <span style="color: blue;">OVERRIDE<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;">GO</span><span><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
These are the Gmail settings for Database Mail:<o:p></o:p></div>
<div class="MsoNormal">
<br /><o:p></o:p></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjWJimfTA2qEATZJmhUyGiKFpgxKDTHTtxlEfYREmZ8zz9KHkKvYNG95WRmL_BY2d0dkuWhMT6AwGczh1j36p18JTKnxiW3Wwd_nX6yBO9ycZtW8DZTNFhQBgxO6YGLD_KHtNc8_88dWe0/s1600/DBMAil_Settings.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="277" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjWJimfTA2qEATZJmhUyGiKFpgxKDTHTtxlEfYREmZ8zz9KHkKvYNG95WRmL_BY2d0dkuWhMT6AwGczh1j36p18JTKnxiW3Wwd_nX6yBO9ycZtW8DZTNFhQBgxO6YGLD_KHtNc8_88dWe0/s320/DBMAil_Settings.png" width="320" /></a></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Once the settings are configured, you need to know how to
construct the command - this is detailed syntax page from MSDN:<o:p></o:p></div>
<div class="MsoNormal">
<a href="http://msdn.microsoft.com/en-us/library/ms190307.aspx">http://msdn.microsoft.com/en-us/library/ms190307.aspx</a><o:p></o:p></div>
<div class="MsoNormal">
Finally, I used this to send the email:<o:p></o:p></div>
<div class="MsoNormal">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;">EXEC</span><span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;"> msdb<span style="color: grey;">.</span>dbo<span style="color: grey;">.</span><span style="color: maroon;">sp_send_dbmail<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;"> </span><span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;">@profile_name
<span style="color: grey;">=</span> <span style="color: red;">'RoyJackson'</span><span style="color: grey;">,<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;"> @recipients <span style="color: grey;">=</span> <span style="color: red;">'roy.jackson@gdr.com'</span><span style="color: grey;">,<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;"> @body <span style="color: grey;">=</span> <span style="color: red;">'The stored procedure
finished successfully.'</span><span style="color: grey;">,<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;"> @subject <span style="color: grey;">=</span> <span style="color: red;">'Automated Success Message'</span>
<span style="color: grey;">;<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;"> <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;">And this to see if it worked – if the message failed you will
see an error row and some details as to the status:<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;">select</span><span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;"> <span style="color: grey;">*</span> <span style="color: blue;">from</span> msdb<span style="color: grey;">.</span>dbo</span><span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;"><span style="color: grey;">.</span><span style="color: green;">sysmail_allitems</span></span></div>
<div class="MsoNormal">
<span style="font-family: "Courier New"; font-size: 10.0pt; line-height: 115%;"><span style="color: green;"><br /></span></span></div>
<div class="MsoNormal">
<span><o:p></o:p></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfQ-osBKUlRy_dfncnZku3e0vaN0MSX2jAx7TqfKzralOO666El4bBrrJrX7bdfZwVj3KjyuCWgXyYiyao1Mx_rgBjq5h4MDNb6pjFg0wei3AqqRHI1FKAyJNlAW6dnIwZxijjb88Scqg/s1600/DBMail_Sample.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="175" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfQ-osBKUlRy_dfncnZku3e0vaN0MSX2jAx7TqfKzralOO666El4bBrrJrX7bdfZwVj3KjyuCWgXyYiyao1Mx_rgBjq5h4MDNb6pjFg0wei3AqqRHI1FKAyJNlAW6dnIwZxijjb88Scqg/s320/DBMail_Sample.png" width="320" /></a></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: red; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">Next stop is using Growl within some
other programs such as ArcGIS / python…<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: red; font-family: "Courier New"; font-size: 10.0pt; line-height: 115%; mso-no-proof: yes;">Hope this is useful!<o:p></o:p></span></div>
</div>Roy Jacksonhttp://www.blogger.com/profile/14539201953708702607noreply@blogger.com0tag:blogger.com,1999:blog-6330544559275496571.post-41359174446939769312012-01-23T11:43:00.000-08:002012-01-23T11:43:52.018-08:00A Humble Reset<div dir="ltr" style="text-align: left;" trbidi="on">Greetings out there to no-one in particular!<br />
<br />
One of my goals for 2012 is to re-engage the online community in some of the areas that I am interested in. This blog will be a place where I can talk about the never-ending challenges that I encounter on my projects, and what interests me as from a technical and business perspective. Hopefully I can interest people in learning a new skill or tool, can save some time in solving problems, or can meet and interact with others in the geospatial technology industry. <br />
<br />
I am always interested in solving problems by bringing people together, understanding business and user requirements, and applying my unique perspective and managerial / technical skills; it keeps me engaged and focused. <br />
<br />
The main technology tools I use today are Visual Studio 2008, SQL Server 2008, Javascript, ArcGIS for Server and Desktop, and a wide variety of third party data and mapping APIs. I strive to be an expert in data quality and sources, production and automated systems, tool and web development, making customers happy and listening. I want to continue to learn about business development, sales systems, and to be a better designer from a visual perspective. <br />
<br />
Thank you for visiting, sharing your thoughts, and beginning a conversation! <br />
<br />
~Roy</div>Roy Jacksonhttp://www.blogger.com/profile/14539201953708702607noreply@blogger.com0