Using the new iPad as a Mobile/Personal Hotspot

by Joe Havelick 16. March 2012 15:31

Since it took me a bit of shuffling to figure it out, I figured I'd write some instructions for hot to enable your new iPad as a mobile hotspot.

  1. You need an iPad with Verizon 4G. (AT&T plans do not support mobile hotspot at this time).
  2. You need to sign up for a data plan. You can do this through Settings > Cellular Data. Currently, all Verizon data plans support mobile hotspot.
  3. Finally, and this was the part I had to find, you can enable the Personal Hotspot feature through Settings > General > Network.

Update:

It seems like after enabling the Personal Hotspot for the first time, or after a reboot (I'm not sure), the Personal Hotspot menu item appeared off the root of the settings menu. I'm not sure why this is, but I hope this helps anyway.

Tags: , , , ,

Tech Tips

Migrate Libraries and Playlists from Rhapsody to Spotify

by Joe Havelick 24. August 2011 18:58
UPDATE:  I have created a tool to automate most of this process. Please see Rhapsody2Spotify

If you're a Rhapsody user looking to try out Spotify, one of the frustrating things will be the lack of your historical playlists. Rhapsody appears to have done everything it can to prevent you from easily sharing out a library or playlist in a portable format, but there is a fairly simple approach to migrate a playlist or library.

Step 1: Export your library

Option 1:

You're going to want to export the contents of the database table which stores your track information. It's a SQL Lite database, and basic instructions on accessing it can be gathered here. I would suggest using the following SQL command, which will exclude tracks you have deleted:

SELECT ARTIST_NAME, TRACK_NAME
FROM TRACK
WHERE In_Library = 1

Alternately, you could just generate a list for a single playlist using the following:

SELECT ARTIST_NAME, TRACK_NAME
FROM Track
INNER JOIN Playlist_Track ON  Playlist_Track .Track_ID = Track.Track_ID
INNER JOIN Playlist ON  Playlist_Track .Playlist_ID = Playlist.Playlist_ID
WHERE In_Library = 1
AND Playlist_Name = 'Blues'

Save the results to a CSV file.

Option 2:

Right click on a playlist, and select share. Email the playlist to yourself. Copy and paste just the artists and tracks to Excel

Step 2: Remove the quotes

It appears that there is no way to export to CSV from SQLit without double quote qualifiers. The tool that we will use doesn't like that. Simply open the CSV in Excel and save it as a CSV. Alternately, you could do a Replace in notepad.

Step 3: Migrate

The tool we will use is located at http://www.ivyishere.org/. Insomuch as it might be tempting to upload our file, it did not work for me. However, if you open the CSV as a text file, then use the copy/paste mode, I had very good success.

Step 4: Paste into Spotify

Copy the results as instructed. Load the Spotify desktop all, create a new playlist, select it, then click on Paste from the edit menu.

Note: Ivy only imports by Artist and Track name. Although we could export a lot more, it would only consume extra space :-/

Happy listening.

Tags:

Tech Tips

Restore All SQL Databases from a Backup File

by Joe Havelick 5. July 2011 09:22

As part of a  disaster recovery plan, I recently needed to have a script to restore all the databases, from all the backups (Full and Differential) from a single backup file. Here's how I did it:

 

SET NOCOUNT ON;

DECLARE @DBName nvarchar(256)
DECLARE @BackupFile nvarchar(256) = N'\\SERVER\PATH\FILE.bak' 
DECLARE @FileID int

DECLARE @BackupHeaders TABLE
      (
      BackupName nvarchar(128) NULL,
      BackupDescription  nvarchar(255) NULL,
      BackupType smallint NULL,
      ExpirationDate datetime NULL,
      Compressed tinyint NULL,
      Position smallint NULL,
      DeviceType tinyint NULL,
      UserName nvarchar(128) NULL,
      ServerName nvarchar(128) NULL,
      DatabaseName nvarchar(128) NULL,
      DatabaseVersion int NULL,
      DatabaseCreationDate  datetime NULL,
      BackupSize numeric(20,0) NULL,
      FirstLSN numeric(25,0) NULL,
      LastLSN numeric(25,0) NULL,
      CheckpointLSN  numeric(25,0) NULL,
      DatabaseBackupLSN  numeric(25,0) NULL,
      BackupStartDate  datetime NULL,
      BackupFinishDate  datetime NULL,
      SortOrder smallint NULL,
      CodePage smallint NULL,
      UnicodeLocaleId int NULL,
      UnicodeComparisonStyle int NULL,
      CompatibilityLevel  tinyint NULL,
      SoftwareVendorId int NULL,
      SoftwareVersionMajor int NULL,
      SoftwareVersionMinor int NULL,
      SoftwareVersionBuild int NULL,
      MachineName nvarchar(128) NULL,
      Flags int NULL,
      BindingID uniqueidentifier NULL,
      RecoveryForkID uniqueidentifier NULL,
      Collation nvarchar(128) NULL,
      FamilyGUID uniqueidentifier NULL,
      HasBulkLoggedData bit NULL,
      IsSnapshot bit NULL,
      IsReadOnly bit NULL,
      IsSingleUser bit NULL,
      HasBackupChecksums bit NULL,
      IsDamaged bit NULL,
      BeginsLogChain bit NULL,
      HasIncompleteMetaData bit NULL,
      IsForceOffline bit NULL,
      IsCopyOnly bit NULL,
      FirstRecoveryForkID uniqueidentifier NULL,
      ForkPointLSN decimal(25, 0) NULL,
      RecoveryModel nvarchar(60) NULL,
      DifferentialBaseLSN decimal(25, 0) NULL,
      DifferentialBaseGUID uniqueidentifier NULL,
      BackupTypeDescription  nvarchar(60) NULL,
      BackupSetGUID uniqueidentifier NULL,
      CompressedBackupSize binary(8) NULL
);

DECLARE @FileList TABLE
      (
      LogicalName nvarchar(128) NOT NULL,
      PhysicalName nvarchar(260) NOT NULL,
      [Type] char(1) NOT NULL,
      FileGroupName nvarchar(120) NULL,
      Size numeric(20, 0) NOT NULL,
      MaxSize numeric(20, 0) NOT NULL,
      FileID bigint NULL,
      CreateLSN numeric(25,0) NULL,
      DropLSN numeric(25,0) NULL,
      UniqueID uniqueidentifier NULL,
      ReadOnlyLSN numeric(25,0) NULL ,
      ReadWriteLSN numeric(25,0) NULL,
      BackupSizeInBytes bigint NULL,
      SourceBlockSize int NULL,
      FileGroupID int NULL,
      LogGroupGUID uniqueidentifier NULL,
      DifferentialBaseLSN numeric(25,0)NULL,
      DifferentialBaseGUID uniqueidentifier NULL,
      IsReadOnly bit NULL,
      IsPresent bit NULL,
      TDEThumbprint varbinary(32) NULL
 );

DECLARE @DataFilePath nvarchar(256) = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\';
DECLARE @LogFilePath nvarchar(256)  = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\';

--Load backup headers (Backup level Information)
DECLARE @RestoreStatement nvarchar(256);
SET @RestoreStatement = N'RESTORE HEADERONLY FROM DISK=N''' + @BackupFile + '''' ;
INSERT INTO @BackupHeaders
      EXEC(@RestoreStatement);

DECLARE iDB CURSOR FOR SELECT DISTINCT DatabaseName FROM @BackupHeaders 
--For each DB
OPEN iDB
FETCH NEXT FROM iDB INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN 
	IF @DBName IN ('master','msdb','model','tempdb') GOTO NextDB;	

	PRINT '';
	PRINT '';
	PRINT '-- Restoring :' + @DBName ;
	
	DECLARE @DBNewName nvarchar(256) = N'Restored_' + @dbName ;
	PRINT '--- Destination Database Name: ' + @DBNewName;
	
	--Get the file list for this DB
	SET @RestoreStatement = N'RESTORE FILELISTONLY
		 FROM DISK=N''' + @BackupFile + ''' WITH FILE=' + CAST((SELECT TOP 1 Position from @BackupHeaders WHERE DatabaseName = @DBName) as nvarchar(10));;
	DELETE FROM @FileList;
	INSERT INTO @FileList
		EXEC(@RestoreStatement);
	--SELECT * FROM @FileList;
	
	--Get the physical file names
	DECLARE @DataFileName nvarchar(256) = REPLACE((SELECT LTRIM(RTRIM(RIGHT(PhysicalName,CHARINDEX('\',REVERSE(PhysicalName)) - 1))) AS DataFileName FROM @FileList WHERE Type = 'D' ), '.mdf', '');
	DECLARE @LogFileName nvarchar(256)  = REPLACE((SELECT LTRIM(RTRIM(RIGHT(PhysicalName,CHARINDEX('\',REVERSE(PhysicalName)) - 1))) AS DataFileName FROM @FileList WHERE Type = 'L' ), '.ldf', '');
	PRINT '--- Destination DataFileName: ' + @DBNewName;
	PRINT '--- Destination LogFileName: ' + @DBNewName;

	DECLARE @LogicalDataFileName nvarchar(256) = (SELECT LogicalName FROM @FileList WHERE Type = 'D');
	DECLARE @LogicalLogFileName nvarchar(256)  = (SELECT LogicalName FROM @FileList WHERE Type = 'L');
	PRINT '--- LogicalDestination DataFileName: ' + @DBNewName;
	PRINT '--- LogicalDestination LogFileName: ' + @DBNewName;

	
	DECLARE iBackup CURSOR FOR SELECT Position FROM @BackupHeaders WHERE DatabaseName = @DBName ORDER BY Position ASC 
	--For each backup, in sequence
	OPEN iBackup
	FETCH NEXT FROM iBackup INTO @FileID
	WHILE @@FETCH_STATUS = 0
	BEGIN 
		
		--Restore the file
		PRINT '---- Restoring FileID: ' + CONVERT(nvarchar(5),@FileID)
			
		DECLARE @RestoreSQL nvarchar(max)= N'
		RESTORE DATABASE 
			[' + @dbNewName + ']
			FROM  DISK = N''' + @BackupFile + '''
			WITH  FILE = ' + CONVERT(nvarchar(5),@FileID) + ',
			MOVE N''' + @LogicalDataFileName + ''' TO N''' + @DataFilePath + @DataFileName + ''',
			MOVE N''' + @LogicalLogFileName + ''' TO N''' + @LogFilePath + @LogFileName + ''',
			NORECOVERY,  
			NOUNLOAD,  
			REPLACE'
		--PRINT @RestoreSQL

		EXEC(@RestoreSQL)
		
		PRINT '';
	NextFile:
	FETCH NEXT FROM iBackup INTO @FileID
	END 
	CLOSE iBackup
	DEALLOCATE iBackup
	
	--Recover the database
	PRINT '--- Recovering ' + @DBNewName
	RESTORE Database @DBNewName WITH RECOVERY
	

NextDB:
FETCH NEXT FROM iDB INTO @DBName
END 
CLOSE iDB
DEALLOCATE iDB

 

Note:

  • This was not engineered to handle incrementals, but I believe it should be easily accomidated with a couple small changes (if any)
  • This excludes the system databases via the 'IF @DBName IN...' statement.

Tags:

Tech Tips

Aerobatic Flight Data Recorder

by Joe Havelick 13. November 2010 14:48

Note: to view the .kml files in the article, you must download and install Google Earth.

The Concept

Among my various hobbies, I fly aerobatics and I dabble with electronics. When flying competative aerobatics, which I started last year, you a gauged on the accuracy of your performance of certain maneuvers.  ex. A loop should be a perfect circle, otherwise, you lose points. That being said, it's hard to do these things correctly, and you never get to "see" your own performance.

I needed a project to occupy my mind, so I decided to that I should build a flight data recorder that would allow me to track certain aspects of my flight such as 3d coordinates in space, G forces, roll rates, and possibly even cockpit voice. But certainly, the most useful would be the 3d position data. This could be done with a basic GPS unit, as they are able to not only determine your point on earth, but your altitude as well. With the help of an Arduino, I was able to build a device that would capture 1 data point per second and write it out to an SD card. I could then feed the data to a very cool utility, GPS Visualizer, which can take standard NMEA output:

$GPGGA,184313.50,4226.45458,N,07135.36688,W,2,10,0.80,1172.2,M,-33.2,M,,0000*5C
$GPGGA,184314.00,4226.44174,N,07135.38045,W,2,10,0.80,1167.2,M,-33.2,M,,0000*59
$GPGGA,184314.50,4226.42855,N,07135.39442,W,2,10,0.84,1164.7,M,-33.2,M,,0000*50
$GPGGA,184315.00,4226.41495,N,07135.40825,W,2,10,0.84,1163.2,M,-33.2,M,,0000*56
$GPGGA,184315.50,4226.40103,N,07135.42133,W,2,09,0.84,1163.2,M,-33.2,M,,0000*5C

...and convert it to a number of useful outputs, most particularly Google Earth, such that the flight could be viewed in a 3d world.

Flight Test Alpha

The first flight was a bust. The unit never got a GPS lock, so I collected no good data. My passenger ended up barfing. I was not a good day for technology or aviation.

Flight Test Beta

1286892046-21655-208.253.159.20.kmz (217.07 kb)

I decided that it would be quite expensive to go flying every time I needed to test the unit, so I asked a friend to take the unit up on one of his flights. The pilot was doign 3 ILS simulated approaches, which brought him from Hanscom Air Force Base to Manchester to Portsmith and back to Hanscom. It was a total success. You can not only see the entire flight track in detail, but pan around and see the elevation of the flight as well.

Note: Color coding is used to indicate groundspeed.

Flight Test Charlie

1286892076-21704-208.253.159.20.kmz (120.55 kb)

Having had a successful test completed, I decided to take it up for aerobatic testing. Everything appeared to work well, until I looked at the results:

Basically, the GPS unit I was using didn't like being inverted, or moved around a 4+ Gs. The altitude data was all wrong, and it "flattened" the whole flight. Also, my (different) passenger got sick. Back to the drawing board.

I decided I would replace the unit with a better unit that would sample at up to 4 times per second, had a omni-directional antenna, and was able to be configured to expect to be diving at 100 knots and pulling 5 Gs. Upon testing, I found that the unit would only reliably sample at 2 times per second. So with the unit rebuilt, and some coding modifications to handle the higher throughput more efficiently, I set out for...

Flight Test Delta

1289677643-30373-146.115.171.212.kmz (118.64 kb)

Instead of bringing someone for this flight, who would inevitably get sick and force me to truncate it prematurely, I decided to go it alone. I spent about 25 minutes doing loops, spins, half cubans, and the like. It was a good flight and I thought would yield some interesting data. When I got back on the ground, my heart sunk to see that the unit was not running. The power was still on, and the connections were solid. I concluded that the battery had been drained. It was a new 9v to begin with, so this think was sucking power. I would have to wait and see how much data I collected.

Once I got home, I eagerly uploaded the data, expecting to be disappointed. I was not.  It got all of my acro.  It died seconds after my last loop. I didn't get to see my landings, but this was cool.  As for accuracy, it was well beyond my minimum expectations. Take a look:

A loop, looking a little pinched, but not bad.

Two separate 45 ups to a 1 turn spin (foreground) and 1 and 1/4 turn spin (background).

The whole ride.

Clearly, I'm very happy with the results and look forward to continued progress on both this project and my aerobatics. I highly recommend downlaod ing Google Earth and viewing the above .kml file. It's way cooler when you can pan around.

The Unit

So here's what it looks like:

The black box (no pun intended) contains the 9v battery to power the unit.

On the right is the unit itself.  It contains:

That's it.

Future Potential

Here are a few things I'd like to do with it in the future:

  • Capture  up to 9 degrees of motion. This includes acceleration, rotation, and magnetic direction. This will allow me to better present the flight, since the current data indicates nothing about flight attitude. If I feel like getting real geeky, I could use this data in coordination with the GPS to get a more accurate display of the flight track
  • Audio. I would love to be able to "playback" the flight with narrations about what's going on, and my thoughts for later anaysis.
  • Migrate to a Netduino. Writing code for the Arduino is not exactly fun. I spent hours debugging my code, and going for test drives in my car. I'm a .NET guy, so the prospect for building this thing in Visual Studio with the Compact .NET framework sounds too good to be true.  I believe it has more memory and a faster processor as well. for $5 more, it's a no brainer. Sadly, I didn't find out about this until after doing it all on an Arduino.
  • Better power efficiency. Chewing through a 9v battery in just over an hour sucks.
  • Better battery. Perhaps a rechargeable lithium battery will also serve to give me more runtime.

Tags:

Aviation | Tech Tips

Send email notification if databases are not backed up in specific duration.

by Joe Havelick 10. May 2010 14:15

Backup utilities may notify you when a backup fails, but don't provide coverage for you forgetting to set the job, or not having permissions to a certain resource. As part of my protection plan, in addition to regular notifications, I like to know if a database HAS NOT been backed up for any reason within the past two days.  The following script accomplishes this.

Note that this utilizes Database Mail and a DBMail profile called "Default DB Mail Profile". These must be manually configured.  More information can be found here.

 --Declarations 
DECLARE @Threshold DATE = CONVERT(DATE, Getdate()-2) --The cutoff from when we consider backups to be current (2 days before the end of today)
DECLARE @SendEmail BIT = 0
DECLARE @emailbody VARCHAR(2000) = 'The following databases have not been backed since before ' + CONVERT(VARCHAR(32), @Threshold) + ':
<BR/><UL>'
DECLARE
 @DatabaseName VARCHAR(256)
DECLARE
 @LastBackupTaken VARCHAR(256)

DECLARE @UserName VARCHAR(256)
 
DECLARE emailcursor CURSOR FAST_FORWARD FOR
  SELECT t1.name                                                              AS databasename,
         Coalesce(CONVERT(DATE, MAX(t2.backup_finish_date)), 'Not Yet Taken') AS lastbackuptaken,
         Coalesce(CONVERT(VARCHAR(32), MAX(t2.user_name), 101), 'NA')         AS username
  FROM   sys.sysdatabases t1
         LEFT OUTER JOIN msdb.dbo.backupset t2
           ON t2.database_name = t1.name
  WHERE  database_name NOT IN ( 'TempDB' )
  GROUP  BY t1.name
  HAVING Coalesce(CONVERT(VARCHAR(32), MAX(t2.backup_finish_date), 101), 'Not Yet Taken') < @Threshold
  ORDER  BY t1.name

--Open Cursor
OPEN emailcursor
FETCH NEXT FROM emailcursor INTO @DatabaseName, @LastBackupTaken, @UserName
WHILE @@FETCH_STATUS = 0
  BEGIN
      ---
      SELECT @SendEmail = 1
      SELECT @emailbody = @emailbody + '<LI>' + @DatabaseName + ' (' + @LastBackupTaken + ')</LI>'
      FETCH NEXT FROM emailcursor INTO @DatabaseName, @LastBackupTaken, @UserName
      ---    
  END
CLOSE emailcursor
DEALLOCATE emailcursor

SELECT @emailbody = @emailbody + '</UL>'

IF @SendEmail = 1
  BEGIN
      EXEC msdb.dbo.Sp_send_dbmail
        @recipients= 'myemail@address.com',
        @copy_recipients = '',
        @subject = 'SERVER Database Backups',
        @body = @emailbody,
        @body_format = 'HTML',
        @profile_name = 'Default DB Mail Profile';
  END
GO
 

Tags:

Tech Tips

List SharePoint site owners

by Joe Havelick 6. April 2010 11:21

I recently had some issues with site owners on my SharePoint sites.  Specifically, one of the sites didn't have one altogether.  The frontend and command line tools all reported "User could not be found".  This also derived from the error "You cannot delete the owners of a Web site collection" when attempting to remove a user for a dead account. 

If you find yourself in that predicament, the following SQL query can be run on any of the content databases to determine who (if anyone) is currently the owner or secondary owner of each site:

SELECT webs.fullurl,
       webs.title,
       sites.id                 AS siteid,
       sites.ownerid,
       owner.tp_login           AS ownerlogin,
       sites.secondarycontactid AS owner2id,
       owner2.tp_login          AS owner2login
FROM   sites
       INNER JOIN webs
         ON sites.id = webs.siteid
       LEFT OUTER JOIN userinfo AS owner2
         ON sites.secondarycontactid = owner2.tp_id
            AND sites.id = owner2.tp_siteid
       LEFT OUTER JOIN userinfo AS owner
         ON sites.ownerid = owner.tp_id
            AND sites.id = owner.tp_siteid 

Tags: ,

Tech Tips

About Me

Joe Havelick is a reasonable facsimile of this photo.


profile for Joe on Stack Exchange, a network of free, community-driven Q&A sites

Recommendations