Junk Drawer – A quick and finally simple way to import misc data into SQL

Dale Newman’s post on Junk Drawer caught my eye and I put it on my To Read list (basically one of my browser tabs, mocking me until I got to it). I finally took time to read it and boy am I glad I did.

This definitely goes into my Bat Utility Belt of dev/dba tools. šŸ™‚

Frequently I need to throw some data into SQL to query against, join against production data, etc, for instance when someone from another department throws a spreadsheet of staff at me and asks if I can add their email addresses. A quick join against our export of Active Directory and away I go.

However, getting this data into SQL is usually a mind numbing dance using the SQL Import Wizard, fretting that I’ll get a truncation error or assigning a column as an Int when clearly there was a single row on line 1765 that happened to have a decimal point. Ugh.

Junk Drawer is a tool that any of us should have thought of, and probably did, but Dale actually created. The article was a real pleasure to read. Dale is a fun and true programmer in that “good” techniques like dependency injection just don’t always make it into one-off toolbox tools. It’s the perfect mix of humor and real-world practicality.

Thanks Dale! If you ever quickly import misc data into SQL you own it to your sanity to check this out. šŸ™‚

A Quick Script to Help Update Your Current Student Photos in Destiny from Aeries

At my district we use Aeries, our Student Information System from Eagle Software.Ā  We also use Destiny, our district-wide library system from Follett Software.Ā  Both are great products and, better yet, both are housed on our Microsoft SQL Servers.Ā  This allows us another level of integration since we can get to the raw database for various needs.

One way we take advantage of this is how we load our student photos into Destiny.Ā  Destiny takes a zip file, which contains jpeg files of the students as well as a idlink.txt file, which is an index file that ties the jpeg to the studentā€™s record.

Iā€™m subscribed to the Follett Talk list serve, which is an email list that several Follett Destiny administrators are a part of and every day there are great discussions and help from various users around the country.

When I mentioned that we had a script to help out when updating student photos in Destiny, several were interested .Ā  Iā€™ve been trying to write up a formal blog post on this with great screenshots and directions but, alas, I havenā€™t had the time.

Anyway, one of our neighboring libraries politely called me about it this morning and I thought I should just put it out there as is for anyone who can use it.

The SQL script file and a quick documentation document are attached to this post.Ā  Get them below.

What it does is grab all the photos (from the school sites you designate) from the server you store your Aeries photos on (this used to be our ABI server but is now our Aeries.Net server), generates the idlink.txt file and puts them all in a zip file for you.Ā  Since it pulls the photos straight from the information in Aeries and also generates the idlink.txt automatically I always get the latest photos (such as from a picture makeup day or newly enrolled students) and also any corrections that may have been put into Aeries.

The script runs in less than a minute for our entire district and then I have a nice easy zip file to upload to Destiny.

There is a little documentation attached but whomever will do this procedure at your site will need some foundational knowledge of Aeries and SQL.Ā  This is not for a librarian at a site that does not have knowledge/access to the Aeries server.

This does not write to any data in the Aeries database so there is no harm in running it.Ā  If there is an error in the script (for instance, you forgot to add your proper school sites and server name) it will simply give you an error but will not cause any changes to data, so donā€™t worry. ļŠ

I hope this helps!

P.S. For those of you who also use BlueBear I had two extra lines of code on the script.Ā  We use BlueBear as our ASB accounting system for our high schools.Ā  It takes photos in the same way but needs them unzipped, all just stored in a single folder.Ā  The last line in my script would unzip the results from above into a folder per school site for our high schools.Ā  This way I would just run the Student Photo Update process in BlueBear and point it at the folder for the correct site.

If you have BlueBear here are the two extra lines I use:
print ‘ECHO Extracting photos for Blue Bear…’
select distinct ‘”c:Program Files7-Zip7z.exe” e c:pics’ + cast(stu.sc as varchar) + ‘Pics.zip -oc:pics’ + cast(stu.sc as varchar) + ‘Ā  >nul 2>&1’
from stu
join #pics on (stu.sc = #pics.sc and stu.bm = #pics.shortname)
where del = ” and tg = ” and bm <> ”
and stu.sc in (310, 320, 410, 430)

Destiny Student Photo Import Quick Documentation

Student Photo Queries.sql

A Great Resource for Different Strategies on Concatenating SQL Results

In every DBAā€™s career I think having to concatenate results happens at least a few times.  Probably more than we like to admit because we tend to live in table-land.  :)  However, there are those occasions, which are usually driven by some downstream requirement to format output.  Now, I know that formatting should be handled by whatever data viewing method you are using, but sometimes that just isnā€™t possible or practical.  Other times it may just be that we need to transform data from one system to another and that other system is not as normalized as the tables you are working with.

Like I said, I do it fairly infrequently, so I never remember the best way in my head.  I usually end up looking at how Iā€™ve done it in the past.  I started thinking that there may be better ways then some of the convoluted strategies Iā€™ve found in previous solutions.

Trusty Google sent me here:

http://www.projectdmx.com/tsql/rowconcatenate.aspx

Itā€™s an incredible (though certainly not exhaustive) list of ways to deal with this depending on your need.  I like XML and chose to go with simplicity so, for this particular task, I went with Eugene Koganā€™s ā€œblackbox XMLā€ method.  Itā€™s only a few lines and if you are familiar with XML and SQL then itā€™s not that hard to understand.

Iā€™ve definitely bookmarked this for later reference!

This is just scary. T-SQL with “shapes”

I was reading one quick article on SQL and code formatting.  Now that SQL (2005+ I believe) allows for international characters they gave a pretty scary example of what can be done:

CREATE TABLE "ā•šā•¦ā•©ā•—" ( "ā””ā”¬ā”“ā”" nvarchar(10))

DECLARE @ nvarchar(10) set @=‘ā•’

INSERT INTO "ā•šā•¦ā•©ā•—"

    ( "ā””ā”¬ā”“ā”" )

SELECT replicate(@,5)

SELECT *

FROM"ā•šā•¦ā•©ā•—"

DROP TABLE "ā•šā•¦ā•©ā•—"

This is no joke.  I just did it and it worked great.  :-0

Map IP Address to a Geographical Location

Here’s a great article on how to get a geographic location from an IP address within SQL Server:
http://www.sqlservercentral.com/articles/SQL+Server/67215/

The article is very easy to follow and gives great direction of setting up a user defined function in SQL to give you back a location based on the IP address.Ā  Since SQL servers are very good at processing data quickly this seems like a natural way to get this information.Ā  Once you have the function set up you can easily use it in asynchronous processes like analyzing logs, post processing of customer data, etc.Ā  You can also set this up as a trigger within SQL or a service callable by outside apps, such as a webapp.

I’ve seen this used in a lot of ways that I don’t care for (thanks for letting me know about all the fictional hot girls that live in my area, but I don’t think my wife would approve :)) but there are some legitimate ideas coming around.Ā  For instance, let me power up my iPhone and see what Nerd Dinners are available in my area (work in progress).

Another scenario is blocking spam.Ā  For instance, at my work we service Riverside County in southern California, USA.Ā  We have methods to stop unauthorized users from creating accounts and blocking spam to our Wiki’s and such.Ā  But why not use location based blocks as well?Ā  I know my users are all from Riverside County, so why not block everyone from, say, outside of southern California?Ā  While a user or two may be blocked while attempting to access their work from their vacation in Maui, I don’t think I’d get that much flack from blocking these edge cases.

SQL Server Integration Services Control Flows

Bret Stateham is giving this talk.  He is a great presenter.  Very animated and speaks clearly.  He also explains complicated details very easily in a friendly manner.  If you have a chance to hear this guy speak definitely take advantage of that!

Boy I wish I had a recording of this.  Iā€™d love my two DBA colleagues to see this.  All but two of our SQL servers are 2000, simply because thatā€™s the latest available when the systems were installed.  It works great but you donā€™t have access to the great tools, such as SSIS, in 2005 or 2008.  Iā€™ve pinged Lynn Langit and Daniel Egan about possibly being a resource for recording these sessions in the future.  You can read about the initial details in my blog post here.  <update>OK, hereā€™s an update.  Iā€™m 45 minutes into the session and Bret is hilarious!  He definitely makes this topic a lot of fun by his passion and animated style.  Lots oā€™ fun!</update>

This is really an intro to SSIS, which is exactly what I need.  His Data Flow Task talk is the next session. 

One data source weā€™re looking to need is an SFTP site.  Currently this isnā€™t in SSIS but we do have a vendorā€™s connector from n Software.  This will be fun to try out.  If for some reason it doesnā€™t work out Bret said we can create one through the Script Task or even a formal task developed in C#.  I may end up doing something like this to hit our GIS server using REST.  I did see a Web Task and itā€™s quite possible that it can consume REST already.  This means that weā€™d have to consume XML and possibly convert this to some other usable format if necessary.

Bret just gave a great tip that may have frustrated me for a while.  The variables you create a scoped.  If you have a task selected and you create a variable, that variable will be scoped to that task, meaning it will only be available to that task and will disappear after that task is finished.  Chances are you want your variable to be accessible by other tasks in your package.  In this case make sure you click an empty area of your design surface prior to creating the variable.  That will scope it to the package itself.

In SSIS 2005 you could only write Scripts in VB .Net, however, in 2008 you have the ability to use C#.

Man!  Debugging in SSIS is awesome!  I canā€™t way to dive into this stuff at work.

Loops, Sequence Containers and Groups:

Loops are exactly as they sound.  The allow you to take a set of tasks and loop through them, much like a subroutine.

Sequence Containers do not loop.  They do, however, offer scope and environment properties.  You can define variables that are only available the that sequence.  You can also set transaction properties on that individual container.  Maybe you have several different containers each with their own transaction level.  Pretty neat.

Groups are simply a design feature.  They are simply there for organizing the items on your design surface but have no properties and no impact on the package at all.

The For Each Loop allows you to specify the collection your picking items from.  The possibilities include XML objects (for each node) and even SMO (SQL Management Objects) for things like ā€œfor each server do thisā€.  Thatā€™s pretty cool.

Bret showed a great example where he needs to process files every day that may not have the same filename.  For instance this may be an AS400 dumping csv files that were time stamped.  He generates what the filename should be in a Script Task, saves that into a variable, and then his File Task uses that variable as itā€™s connection string.  Sweet.  We need to do this as well and this would really help.

This was a great talk.  SSIS is definitely something Iā€™ve been looking to get into ever since SQL 2005.  It looks like this is something I can dive into right away.  For the more complex things Bret recommended the book Professional SQL Server 2005 Integration Services from Wrox.

Unfortunately I canā€™t attend Bretā€™s next session since Iā€™m attending Mike Rothā€™s Asterisk session.  Mike and I are working on a side project together using Asterisk, so I should give audience support and also learn what the heck weā€™ll be using! šŸ™‚

 

Technorati Tags: ,

A Journey Through SQL Server Analysis Services

This is given by Ben Aminnia. 

Heā€™s really putting an emphasis on planning, which is great.  Lynn Langit said her new book has the first 5 chapters devoted to what needs to be done before you even open BIDS.

Ben has an interesting presentation style, at least for this talk.  Heā€™s giving it like his company has an existing Analysis Services project that we are interviewing to possibly take over.  Iā€™m not quite sure yet we are on the driving end (we are really interviewing Ben to see if we want to take the job) or on the other end (he is interviewing us to see if weā€™re qualified to take the job).  I hope more of the former since Iā€™m at the talk because I donā€™t know anything about SSAS.

While he is doing and interview style here at Code Camp he is assuming we donā€™t know about SSAS so thatā€™s a good strategy to take.  He just gave a really good 4 minute background on Analysis in general and what a Cube is (since I raised my hand because I didnā€™t know! :))

During this talk Ben is using the example of a system that monitors disk space availability (among other data).  He actually has a great real world system for doing just this. It is based on the cover story of the February 2008 issue of SQL Server Magazine. You can find the online version here.  It’s a very complex SSIS package but it allowed Ben to do this without having to spend months developing it.  For him it works great and is easy to maintain. Ben has even made his own additions and discussed these changes with the original author. If you contact Ben directly (ben at sql.la) he can give you his additions.

Great talk!

Technorati Tags: ,

Whatā€™s new in SQL Server 2008 Analysis Services

Lynn Langit is presenting this one.  Her main career focus is Business Intelligence and has a book coming out.  Iā€™ll have to pick this up.

This is an area that has always been of fascination to me yet I donā€™t know anything about it other than simple data reporting.  Working at the Val Verde Unified School District we have vast amounts of data and could really take advantage of BI.  Hopefully Iā€™ll be able to use some of this with our new SQL 2008 install and start introducing it to various departments.  Maybe we could even use it in IT to analyze our support trends.  That would be sweet!

Excel has a great add-in to act as a client to BI data.  Excel is one of the primary tools used at our organization, especially by our business office, so this would be a great sell.

Wow!  Lynn just showed how she took her Excel spreadsheet of local data and analyzed it using Microsoftā€™s own services.  She asked Excel to analyze it, it reported she wasnā€™t connected to SSAS, she said she wanted to use Microsoftā€™s data services in the Cloud, and then it sent the results back.  This took only a few seconds.  Yes you are sending your data to Microsoft and yes this service is only free during beta testing, but this is amazing.  Could really open doors for cheap introduction to SSAS and BI in general.

Check out SQL Server Data Mining in the Cloud for details on how to do this.

At VVUSD we have the mentality that weā€™d rather pay up front had host our own services, but using this might be a great way to sell research in this area in the beginning.

Lynn gave a great explanation of SQL Data Services.  It is SQL Server that has been optimized for performance and high-availability.  Thus many features have been removed and interaction has been restricted.  There are schemaless containers via Authorities, Containers and Entities (think property bags).  Also, the only query language currently is LINQ.  This all may change but thatā€™s the current state.

Wow!  Once you have a validated and fairly good model Lynn showed how this can be used real-time.  You can hit your model real-time, such as from a form your sales guy is using, and get instant prediction about your current state.  For instance, if youā€™re selling bikes, and in the first 3 questions your sales guy finds that this potential customer is in the bottom 10% of likelihood to buy a bike, they can thank the customer and hang up right there.  Instantly, in real time, they have found out the likely results of their work.  We could use this in our school and, based on grades, get instant feedback as to how successful a student might be.  Education organizations spend millions in this area so I think our work would be cut out for us! :)  In the IT dept, for example, we could possibly instantly predict the support costs weā€™ll incur from a department (or specific user) and hardware (laptops, phones, etc) based off of previous support calls.  So if a user who travels a lot and is particularly rough with their equipment asks for a new laptop we may find that itā€™s more cost effective to buy a better warranty or a tougher laptop (or deny the request all together).

This is definitely a large area (full careers in themselves) but something that would definitely be worth spending some time on if possible. 

OK, Iā€™m definitely going to have to pick up a book.  This is all so new to me that most of this is over my head, but the demos are absolutely amazing.

ESRI 2008 UC: SQL Server 2008 and ArcGIS

I attended the Intro and Advanced sessions of Administration for SQL Server.  The intro was a pretty straight forward topic of installation and configuration for setting up ArcSDE with SQL Server.

The interesting points were how ArcSDE integrates with SQL Server 2008.  As I mentioned in my talk on SQL Server 2008 at the IE .Net User Group storing spatial data in a SQL database is not a new thing, just new to SQL Server.  ArcGIS already has implementations to take advantage of server level spatial handling in Oracle, DB2 and possibly others.  Only in 9.3 are SQL Server 2008 and PosgresSQL now supported.d

Since SQL Server 2008 hasn’t been officially released yet (all info still points to Q3 2008, which leaves less than 2 more months) ESRI is working with Microsoft on the latest release candidate version.  While you can play with it ESRI of course highly discourages using ArcSDE against SQL 2008 until the official release.  At that time ESRI will certify ArcSDE against SQL 2008 and will most likely release SP1 to deal with any changes since the 9.3 release.

That being said, what exactly is ArcSDE offloading to SQL Server?

Remember that the ArcGIS geodatabase is a very proprietary data model, allowing you to store not only geographic items (features) but also tables, topology rules, data access rules, locking schemes, business rules, etc.  SQL Server 2008 is still at heart simply a data storage platform.  This means that the bulk of the management of your GIS data will still be handled by ArcSDE. 

SQL Server now includes spatial features including two new data types (GEOGRAPHY and GEOMETRY) as well as a whole host of functions to manipulate these data types with.  The data is indexed using new Spatial Indexes on the server.  These are the things that ArcSDE will take advantage of.

ArcSDE will store geographic data in the new GEOGRAPHY and GEOMETRY data types.  This allows SQL to manage it’s own spatial indexes on the data.  Doing this also allows ArcSDE to take advantage of the built-in spatial functions for simple requests.

ArcSDE will take advantage of the indexing schemes built in to SQL Server 2008, however, all the settings are left to Microsoft defaults.  ESRI claims this is because every user’s data is different.  While this is true, I got the impression that it’s more because the indexing scheme is so new that everyone is still coming to terms on it’s quality and how to actually manually maintain it.

Now that you can store your data in the underlying database, several questions come to mind:

  • Can I simply enter my own geographic data in SQL Server and use these in ArcGIS?
    • Yes you can, however, you must register it with ArcSDE before you can integrate it with other ArcSDE data.  If you never plan to process this data using ArcGIS tools then you never have to register it.  However, that doesn’t seem like a likely scenario since you imported the data into your geodatabase.
      To register the data use the sdelayer tool.  This sets up all the geodatabase data and relationships so that the features can be managed using standard ArcGIS tools.
  • Can I manipulate ArcGIS data directly in SQL?
    • Yes!  You can hit the data directly using the built-in spatial methods.  This is pretty awesome for automated tasks that might fire at scheduled moments or for triggers. 
      • For instance, at our school district our Student Information System (SIS) has a field for each student storing what their school of residence is.  Since students may request to attend another school in our district, often it is important to know what school they normally would have gone to.  Currently we autopopulate this data do this once a week or so.  This is a manual process by using ArcGIS Desktop to simply join our students against our school polygons, output the results to a table and import these values back into the SIS.  Once our GIS data is in ArcSDE on SQL 2008 I will be able to setup a trigger on the student record causing it to use the ST_INTERSECT method to find this data on the fly.  Sweet! šŸ™‚
    • However, (you knew there had to be a however right?) you MUST BE VERY CAREFUL.  As with manipulating most SQL data you must know exactly what you’re doing.  If you are inserting new objects you must find what the next ObjectID should be.  If you are working with versioned features it’s more complicated as you have to work with versioned views within a versioning session.  SQL 2008 has no concept of these so ArcSDE installed several utility stored procedures into SQL Server that you will use to manage this process.
  • What about the SRID?
    • If you are new to SQL Server 2008 you may have noticed this weird SRID value that is attached to every spatial value.  This is the equivalent of the Spatial Reference used in ArcGIS such as NAD State Plain 1984 or WGS.  However, the SQL Server SRID corresponds to the EPSD code (European Petroleum Survey Group).  This is a table of spatial references all attached to a unique ID. 
      This is required when registering your imported data with ArcGIS using the sdelayer command.
      What’s different is ArcGIS’s SRID’s do not correspond to the EPSD code.  So what do you do?  ArcGIS stores it’s SRID as auth_SRID.  Here is an example of how to join the ArcGIS srid_auth against the EPSD reference table to find out which SRID to map to:
      select sr.auth_srid from SDE_layers l join SDE_spatial_references sr on l.srid = sr.srid where l.table_name = ‘buildings’ and l.owner = ‘dbo’
      This command joins the SDE_layers table against the SDE_spatial_references table and looks for the layer named buildings owned by dbo.

All in all some really great stuff!  I can’t wait to play with this.

I guess my one complaint is that while you can request an ArcGIS Desktop eval cd from the ESRI website I don’t know that you can get some type of an eval version of ArcGIS Server.

Technorati Tags: ,

SQL 2008 RTM’d!!!

I’m in the SQL Server SIG lunch at the ESRI 2008 UC.

Ed Katibah (Spatial Program Manager at Microsoft) just announced that earlier this morning he received an email that SQL Server 2008 has officially gone RTM (Release to Manufacturing).

This is great.  The last few months it’s been stated that SQL Server 2008 would hit the market Q3 of 2008.  I guess this means they are officially on track!

Wahoo!