[SQLServer2005] Select from Excel Spreadsheet for SQL-Rejects

This is DBA bread and butter - not being a DBA and having no will whatsoever to become one (I am too weak) I always forget the syntax for this procedure, so here we go.

In order to be able to query an excel spreadsheet we need to enable ad hoc distributed queries, to do so follow the procedure in this other post.

Once that's sorted - assuming you have an excel spreadsheet with column names (in the example I have Column1, Column2, Column3, Column4) in the first row - this is how you go about selecting the spreadsheet content into a temporary table:

USE myDB

-- drop temp table
DROP TABLE #tempTable

-- select spreadsheet content into #tempTable
SELECT S.[Column1], S.[Column2], S.[Column3], S.[Column4]
INTO #tempTable
FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\mtSpreadsheet.xls;HDR=YES',
'select * from [Query1$]') AS S;

-- check if the stuff is there
select *
from #tempTable

That's it. Hope it helps some other fellow SQL-Reject.

[SQLServer2005] Enable Ad hoc distributed queries

The Ad Hoc Distrubuted Queries advanced feature is disabled by default - it needs to be enabled if you wanna run OpenRowSet or OpenDataSource operations (queries on excel spreadsheets and other stuff). 

To enable it run the following script and you should be OK:

-- allows you to see advaced options status
sp_configure 'show advanced options', 1
RECONFIGURE
GO
-- enables Ad Hoc Distributed Queries
sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO
-- shows you the full list of features
sp_configure

The same result can be achieved by opening SQL Server Area Configuration tool for features and after picking Ad Hoc Distributed Queries ticking the "Enable ..." checkbox.

That'd be all.

[CSS] Center DIV within DIV

This is one of those that'll give you nightmares.

You have a wrapper div and an inner DIV:

<div id="wrapper">
<div id="inner">
  <!-- your rubbish here -->
</div>
</div>

Css styling below will do the trick - text-align: center will work on IE but won't work on other browsers (css compliant) which require margin: 0 auto:

/* for IE */
div#wrapper {
text-align: center;
}

div#inner {
margin: 0 auto; /* this centers the DIV */
width: 30%; /* whatever */
}

alternatively you could set left and right margin separately.

[Google App Engine] How to Clear SDK Datastore

To clear the SDK datastore you'll just have to delete the following file: "./WEB-INF/appengine-generated/local_db.bin". The file will be generated for you again next time you run the development server and you'll have a clear db.

This is one of the little gotchas that come in handy when you start playing with the Google Application Engine. You'll find yourself persisting objects into the datastore then changing the JDO object model for your persistable entities ending up with a obsolete data that'll make your app crash all over the place.