Today I finally had the opportunity to do something that had been on my todo list for a while - move the collection of SQL Server Helper Scripts and the Kindle annotations to text file applescript I wrote into a proper repository. I hope that it will make it easier for other people to make changes and enhance the scripts rather than having to email me updates/leave me comments on requirements etc.
I chose github as it seemed to be the most frequently used repository amongst my colleagues and friends, and because I haven't really used git much before so thought it would be a good way to get some exposure to it (and so far a combination of the help text provided and a husband who knows his way about git seems to be working).
I'm going to spend a little time going back through the various blog posts to try and get the links all hooked up to the right place, but as with everything else around here, there are no guarantees I'll catch them all.
I've been making a lot of use of my Amazon kindle, but I was mildly annoyed that only annotations and highlights from Amazon purchased books end up on the kindle.amazon.com site. After doing a bit of reading, I discovered that the highlights and annotations are stored in 2 places on the kindle itself. Firstly embedded in the .mbp files that can be found in the Documents folder when the kindle is mounted, and secondly in the My Clippings.txt file, found in the same place.
The applications and scripts I found to query the mbp files seemed to only return the last few annotations/highlights, which wasn't what I wanted. I found a few applications that were windows based, but couldn't find anything for the mac, so, I decided to roll up my sleeves and do battle with applescript to write something that did what I wanted.
What I wanted was :
To use the My Clippings.txt file so I got all the highlights/annotations
To produce a text file per book listing highlights and annotations ordered by location
Optionally add the location to the end of the highlight/annotation
I used a couple of my Wednesday afternoon's at home to write the script, learnt quite a lot about applescript in the process, and polished my swearing as I got more and more frustrated with applescript and myself.
Originally I intended to spend some time refactoring and making it look prettier, but I've got it doing pretty much what I wanted, and having spoken to a few people, they thought it was probably useful as a starter for anyone else who wanted to do something similar.
So, I've added it to my downloads page and made it available here. The usual disclaimer applies - use at your own risk. If you refactor this to be prettier/more efficient I'd love to see the changes.
To use it:
Open the file in the applescript editor
Take a look at the code if you wish - I've tried to comment the methods etc
Run the code
A dialog will be displayed asking you to select your My Clippings.txt file - I've been copying this off the kindle and on to my desktop to ensure I can't destroy the original at all
A second dialog will be displayed asking you to select an output folder to put the text files in
A message box will be displayed asking you if you want to delete all files currently in the folder - the default is no
A message box will be displayed asking you if you want the location data appending to the end of a highlight/annotation - the default is yes
The script will then work through the My Clippings.txt file producing a text file per annotated/highlighted book (no bookmarks are output)
A final message box will be displayed telling you how many files it has output
@jamesbradshaw your name is even mentioned in the version history "suggestion by James Bradshaw"
and to which he responded with
@JaneFoth I have have just had the pointed out in the office. I have just to mod it to cope with schema names do you want my editions?
To which I of course said yes. James sent the updated procedure through, and spurred on by his activity, I also added a fix I'd been considering for a while - to allow the procedure to deal with tablenames which have a . in them, after having recently discovered that this was valid (basically by wrapping the table name in [ and ]).
So, no updates in over a year, and then 2 in one day. Must be the same as how it is with buses... Anyway, as usual the updated procedure can be found here and is listed on my downloads page.
I use toodledo to manage my todo list, and this works fine, except that it means I have my browser open all the time and don't really plan my days too well being a bit more reactive than proactive. A few weeks ago I stumbled across a tweet from Craig Murphy linking to the ebook version of a (very well written) Pomodoro manual (Pomodoro being a time mangement system). I've been using the Do It Tomorrow technique with toodledo, but haven't really embraced the daily system. Combining the two looks like it'll work well for me, allowing me to focus on what I need to do each day, whilst still recording other items (tomorrow and beyond) in toodledo.
I also recently stumbled across the PocketMod books from paper concept, and have been using a simple one as my work week planner for the last couple of weeks. Today I went one stage further and used Natalie's Pocket Project html/css work to create a Pocket Pomodoro template for myself to use.It has a week view on the front page - so I can put known appointments in there, allowing me to carry my calendar around the office with me. And then 5 pages of todos, one per week day with an Unplanned and Urgent section to note down extra items. I haven't put any checkboxes in as the number of pomodoros taken to fulfill a task will dictate this. There is an Activity Inventory page, to put any of the items that appear that are not do it today urgent - these will be put into toodledo at an opportune moment. Finally there is a notes page, to jot down anything that comes up during my week. Over time, as I use this more, this may become a second Activity Inventory page, so watch this space.As with Natalie's example, it only works on Webkit based browsers so I've also created it as a PDF for anyone who is interested in using this but isn't using a webkit based browser.And of course, once you've got your PocketMod printed, you'll need to fold it, and this YouTube video does a great job of explaining how to do that
The links again: Pocket Pomodoro as PDFAs with the other tools, utilities etc I've made available, leave me feedback via comments.
So now instead of the call spu_generateinsert @table = 'LanguageData' producing INSERT INTO [LanguageData] ([liID], [Value])VALUES (7,'D''Artagnan raconte qu''à sa première visite à') it produces INSERT INTO [LanguageData] ([liID], [Value])VALUES (7,N'D''Artagnan raconte qu''à sa première visite à')As usual, the script is available here And again as usual, leave me a comment if you think of some new functionality I should include, or any issues you come across.
The final helper procedure I wrote as part of my recent integration exercise was spu_scriptprocedures. It is a simple stored procedure which builds up a defensive SQL script for one or many stored procedures.
It has 4 parameters:
@db - the name of the database. Mandatory
@rolename - the name of the role for which a grant execute statement should be prepared. Optional - no grant statement is printed if this isn't specified
@proceduresToScriptCondition - the condition (based on the INFORMATION_SCHEMA.ROUTINES view) to use to select the procedures. Optional - if not specified all stored procedures will be scripted
@debug - print out the SQL being generated to help debug the code. Optional - defaults to off
Example usage: spu_scriptprocedures @db='master', @proceduresToScriptCondition='ROUTINE_NAME = ''spu_compareprocedures'''
This has been tested on SQL Server 2005.As with my other SQL helper procedures, please leave a comment if you have any suggestions on way to improve it, or bugs to report.Download the script here.
I have been a long time fan of RedGate's SQL Compare tool to compare all aspects of a database. The work I'm doing at the moment needs me to integrate some new code into an existing project - some of which involves database manipulation. However, I'm only interested in a subset of stored procedures as many of them are different for valid reasons. I couldn't work out how to do this in SQL Compare so, I wrote a script to do it for me. It is called spu_CompareProcedures and I thought I'd share it in the hope that is of value to others.
There are 5 parameters available, they are:
db1 - the name of the 1st database to use
db2 - the name of the 2nd database to use
proceduresToCompare - either set to '' to compare ALL stored procedures in both databases, or set to a comma separated list to restrict what gets checked
displayOnlyDifferent - defaults to 1, i.e. only display the stored procedures which are different. If set to 0 it'll show the ones that are the same as well, but in most cases this is just too much information
debug - defaults to 0. Used to output the SQL being generated behind the scenes to enable debugging of this stored procedure
If I wanted to compare the procedures spu_generateinsert and spu_compareprocedures on the database MyMasterDatabase and MyOtherDatabase I would specify spu_compareprocedures @db1 = 'MyMasterDatabase', @db2 = 'MyOtherDatabase', @proceduresToCompare='spu_generateinsert,spu_compareprocedures', @displayOnlyDifferent=1, @debug=0
As with spu_generateinsert, the output is displayed in the Messages window. It will report on what is being checked, any errors - i.e. procedures which exist in one database and not the other, or maybe either, and those which are different (or the same as well depending on the value of @displayOnlyDifferent). Sample output is: ****************************************************************** Comparing databases MyMasterDatabase and MyOtherDatabase Objects: spu_generateinsert spu_compareprocedures ****************************************************************** ***************** ** ERRORS ** ***************** spu_compareprocedures is missing for MyMasterDatabase spu_compareprocedures is missing for MyOtherDatabase ***************** ** COMPARISONS ** ***************** No differences foundIt is very simple, and just checks the contents of the stored procedure, it doesn't attempt to identify the differences. But, at least theoretically, this is enough to give a heads up as to the procedures to be concerned about.Download the script here.If you have suggestions, comments or bug reports, please leave me a comment and I'll do what I can to help out.
Further to my earlier update today I've reworked the printing out of the insert statements - mainly because I need it for the work I'm doing today...
It now produces a single statement per row of data, and then checks the DATALENGTH of that statement. If it exceeds the 8000 byte limit, then it looks for CHAR(10)/CHAR(13) characters and breaks the row into chunks based on these CHAR(10)/CHAR(13) limits. This relies on the fact that PRINT 'Hello ' + CHAR(13) + 'World' and PRINT 'Hello' PRINT 'World' both produce the same output of Hello WorldSo, instead of PRINTing CHAR(13) we're PRINTing multiple lines.All of which seems to work and I can finally get the As per usual, download the spu_generateInsert script here.
I'm about to try and make the procedure work within this limitation (a table I'm trying to script exceeds this), but in the meantime I've added an additional bit of error reporting which results in the final line in the messages window being displayed as: -- ** WARNING: The data length for at least one row exceeds 8000 bytes. The PRINT command is limited to 8000 bytes (for more information see http://msdn.microsoft.com/en-us/library/ms176047.aspx). Do not trust this data. ** -- so at least the user is informed of the issue. Download the updated script here.As always, comments and suggestions are welcomed.
Earlier today I had a user error using spu_generateinsert and couldn't work out what was wrong. It turns out I had specified the table name wrongly. So, I've updated the procedure to check for the existence of the table and to report if there isn't one. Hopefully, this will stop me making the same mistake again :-)
Download the updated script here.I also noticed today that the PRINT command only outputs 8000 bytes worth of data - so if the contents of a VARCHAR(MAX) is larger than this the output will be truncated. I'm mulling over various fixes for this but in the mean time have noted it as a known issue in the SQL.
