I had to import information from an Excel file with datasheets that had 40+ columns. Using SSIS could be a bit tricky sometimes, so I decided to use a linked server. This feature works well. It's fast and less of a headache than SSIS. Originally designed for connecting to other databases, you can use it to import information by linking to a file. This is how I went about it (this is for SQL Server 2005) in importing an Excel (.xls) file.

1. Under Server Objects in your instance, create a new Linked Server:
   
   
   
   
2. Under the General section, pick an appropriate name for your linked server. Pick the OLE DB provider for Excel documents:
   
   
   
   
3. Since I'm using this on a local machine, I don't have to worry about security too much. Select "Be made without using a security context" under the Security section.
   
   
   
   
   
4. Select your Server Options. I suggest these settings for local access.
   
   
   
   
   
5. Hit OK to create it. You'll see the following objects:
   
   

SQL Server reads a spreadsheet in a workbook as a table. So now that we've created our linked server, let's see how to query them.

-- Querying three spreadsheets.
SELECT * FROM Hardware...['CORE PROBONO$']
SELECT * FROM Hardware...['ET013-PartialRackElevation$']
SELECT * FROM Hardware...[ILO_TEMPLATE$]

Since I don't always want to rely on the linked server, create tables into my general database where I slice and dice data.

-- Import data from a linked server into a database table
SELECT * 
INTO GENERAL.dbo.Elevation
FROM Hardware...['ET013-PartialRackElevation$']

This feature was introduced on SQL Server 2005. It’s a great way to query another query on the fly. I prefer using these over derived tables (DTs) because it provides more flexibility. Some people report better performance using Common Table Expressions (CTEs). I’ve seen and heard both though (that DTs are faster), but I suppose it depends. Just test it out and see for yourself.

Anywhoot, let’s play with CTEs. First let’s create two tables with dummy data.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

CREATE TABLE Records 
(
	RecordID INT IDENTITY(1, 1) PRIMARY KEY,
	RandomData VARCHAR(100)
)
 
DECLARE @t INT 
SET @t = 0
WHILE @t < 1000
BEGIN
	SET @t = @t + 1
	INSERT INTO Records VALUES( NEWID() )	
END
 
 
CREATE TABLE Information 
(
	RecordID INT IDENTITY(1, 1) PRIMARY KEY,
	RandomData VARCHAR(100)
)
DECLARE @t INT 
SET @t = 0
WHILE @t < 1000
BEGIN
	SET @t = @t + 1
	INSERT INTO Information VALUES( NEWID() )	
END

Now that we’ve create the dummy tables, here’s a barebones example of a CTE:

1
2
3
4
5
6
7
8
9
10
11

WITH Slice1 AS 
(
	-- The results for this query gets put into Slice1
	-- It persists for the life of this query.
	SELECT * FROM Records
	WHERE RecordID BETWEEN 5 AND 400
 
) -- Done creating a virtual table called Slice1, now let's 
  -- query it:
	SELECT * FROM Slice1
	WHERE RecordID > 300

CTE’s real power comes when you create multiple virtual tables then finally query them, joining any virtual table you created:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

-- This whole thing is 1 query:
WITH    Slice1
          AS ( SELECT   RecordID,
                        RandomData
               FROM     Records
             ) , -- done creating Table Slice1
 
        Slice2
          AS ( SELECT   RecordID
               FROM     Records
               WHERE    RecordID BETWEEN 10 AND 20
             ) , -- done creating Table Slice2
 
        Info
          AS ( SELECT   RecordID
               FROM     Information
               WHERE    RecordID IN ( 5, 6, 7, 9, 15, 18 )
             ) -- done creating Table Info
 
	-- Now that we've created all these virtual tables, let's use them together in
	-- one single query:             
    SELECT  RecordID,
            RandomData
    FROM    Slice1
    WHERE   Slice1.RecordID IN ( SELECT *
                                 FROM   Info
                                 WHERE  RecordID IN ( SELECT    RecordID
                                                      FROM      Slice2 ) )

First have a look at Microsoft Whitepaper that gives you an overview of the process. Make sure you get the SQL Server 2008 Upgrade Advisor. It will analyze databases and check for incompatibilities. It does not write any data to database you're analyzing. I highly recommend it, if you're making such a big jump as we did. Also, apply SP1 (currently the newest), and Windows updates.

The machine I'm working on is a 64bit machine with 16 GB RAM. Make sure you adjust the max memory taking up by SQL Server. By default, it will try to consume the max you have (the setting will be set at roughly 2 petabytes). To prevent OS starvation of memory, on a machine used for just SQL Server, allot 10-15% of memory to the OS. The rest will be for SQL Server. With 16GB, I set it to 13GB (13312 MB) of memory.

Also, I urge to change the compatibility level to SQL Server 2008 (100), that is of course, the Upgrade Adviser throws a lot of issues and you're on a tight deadline. Certain features have been deprecated and removed. For example, this will no longer work on 2008 (order by [table alias].[column alias]):

1
2
3

SELECT AreaID aid, ParentID, Name 
FROM Areas a
ORDER BY a.aid

or this way of truncating will no longer work:

1

BACKUP LOG AdventureWorks WITH TRUNCATE_ONLY

To avoid problems for future upgrades, it’s best to switch the compatibility to 2008.

Also, as an obvious reminder, keep a backup, especially if you’re going to do a detach/attach migration. Once you attach the 2000 db files to 2008, you can’t retach to 2000. I prefer a traditional .bak restore.

Lastly, I highly recommend this checklist when upgrading.

This is pretty impressive. It’s an emulator in JavaScript. I’ll have to see how many instructions get run through the profiler and how it reads the binary ROM file (perhaps it was converted beforehand).

This is a new job that is created by default on SQL Server 2008. By default, the job will most likely fail unless you fix it.

Where it breaks is on STEP 3, which is a Powershell command. It does not reference the correct SQL Server object. Change it to the following to fix it:

1

(Get-Item SQLSERVER:\SQLPolicy\COMPUTERNAME\DEFAULT).EraseSystemHealthPhantomRecords()

The purpose of this job is to purge unneeded information coming from SQL Server 2008’s new Policy Management features.

More about this particular issue.

More about Policy-Based Management by Pinal Dave.

Here’s a set of Firefox add-ons I use in my daily development. Note all of them analyze code. Some of them are for being productive.

Make sure you go to the Firefox web site to get the latest versions of these.

	CodeBurner for Firebug 1.2 CodeBurner is a Firefox add-on that integrates with Firebug, to extend it with reference material for HTML and CSS. https://addons.mozilla.org/en-US/firefox/addon/10273
	ColorZilla 2.0.2 Advanced Eyedropper, ColorPicker, Page Zoomer and other colorful goodies... https://addons.mozilla.org/en-US/firefox/addon/271
	Firebug 1.4.2 Firebug integrates with Firefox to put a wealth of development tools at your fingertips while you browse. You can edit, debug, and monitor CSS, HTML, and JavaScript live in any web page... Firebug 1.4 requires Firefox 3.0 or higher. https://addons.mozilla.org/en-US/firefox/addon/1843
	GooglePreview 3.22 Inserts preview images (thumbnails) and popularity ranks of web sites into the Google and Yahoo search results pages. https://addons.mozilla.org/en-US/firefox/addon/189
	Greasemonkey Allows you to customize the way a webpage displays using small bits of JavaScript. https://addons.mozilla.org/en-US/firefox/addon/748
	Html Validator 0.8.5.8 HTML Validator is a Mozilla extension that adds HTML validation inside Firefox and Mozilla. The number of errors of a HTML page is seen on the form of an icon in the status bar when browsing. https://addons.mozilla.org/en-US/firefox/addon/249
	IE Tab 1.5.20090525 IE Tab - an extension from Taiwan, features: Embedding Internet Explorer in tabs of Mozilla/Firefox... https://addons.mozilla.org/en-US/firefox/addon/1419
	Selenium IDE 1.0.2 Selenium IDE is an integrated development environment for Selenium tests. It is implemented as a Firefox extension, and allows you to record, edit, and debug tests. Selenium IDE includes the entire Selenium Core, allowing you to easily and quickly... https://addons.mozilla.org/en-US/firefox/addon/2079
	Web Developer 1.1.8 The Web Developer extension adds a menu and a toolbar with various web developer tools. https://addons.mozilla.org/en-US/firefox/addon/60
	Mozilla Labs - Ubiquity 0.1.9 An extension that allows for the use of dynamic commands in Firefox. https://addons.mozilla.org/en-US/firefox/addon/9527
	Text2Link 1.9.4 Text2Link is a simple and easy-to-use way to copy the text of a link, or to open URLs and send emails to addresses not marked-up as HTML links. https://addons.mozilla.org/en-US/firefox/addon/6003
	Tab URL Copier 1.1.9 Copies the URLs of all open tabs. Puts 'Copy Tab URLs' item in right click menu of tabs and Edit main menu. Useful for bibliographies, quickly sending someone a list of pages to view etc. Can now open tabs from previously copied... https://addons.mozilla.org/en-US/firefox/addon/2069
	Wired-Marker 3.1.09060400 Wired-Marker(http://www.wired-marker.org/en/) is a permanent (indelible) highlighter that you use on Web pages. The highlighter, which comes in various colors and styles, is a kind of electronic bookmark that serves as a guide when you revisit a Web page. The highlighted content is automatically recorded in a scrapbook and saved. https://addons.mozilla.org/en-US/firefox/addon/6219
	YSlow 2.0.0b6 YSlow analyzes web pages and why they're slow based on Yahoo!'s rules for high performance web sites. https://addons.mozilla.org/en-US/firefox/addon/5369
	FireShot 0.78 FireShot is a Firefox extension that creates screenshots of web pages (entirely or just visible part). Unlike other extensions, this plugin provides a set of editing and annotation tools, which let users quickly modify web captures and insert text annotations and graphical annotations. Such functionality will be especially useful for web designers, testers and content reviewers. The captures can be: - uploaded to FREE public screenshot hosting - saved to disk (PNG, GIF, JPEG, BMP) - printed - copied to clipboard - e-mailed - sent to configurable external editor for further processing. https://addons.mozilla.org/en-US/firefox/addon/5648
	Text Area Resizer & Mover 1.80 Allows to easily resize or even move textareas. https://addons.mozilla.org/en-US/firefox/addon/8287

This requires Subclipse. If you want to create a new project and fill it with SVN source code, you can do as follows. This creates a project where you can apply Subclipse operations.

1. Create a directory where you want to import your files from the SVN server
   
   
2. Right click on any existing project (doesn't matter which one)
   
   
3. Click on import
   
   
4. Select SVN -> Checkout Projects from SVN
   
   
5. "Use existing repository location" radio button
   
   
6. Select a folder (you must select a folder, not just the repo) for the folder to be checked out from SVN
   
   
7. "Check out as a project configured using the New Project Wizard"
   Leave all options as default and click finish.
   
   
8. Select the project type
   
   
9. put in a project name and the location of where the files are going to be
   put in (from step 1).

Ant (Another Neat Tool) is a tool to automate tasks needed to run successful builds, deployments, and tests. You can run Ant as a stand-alone tool, or run it from Eclipse.

Needed JAR Files

In Ant, a "task" is a single line of instruction that gets run in the build file, as opposed to a "target" which is a group of tasks. There are a core set of tasks in Ant: copy, delete, echo, and many more (http://ant.apache.org/manual/). Other important tasks (like FTP/SVN/Mail functionality), however, are not included. For that reason, we need to include these "add-ons" into Ant in the form of JAR files into Ant's classpath.

The following is a list of additional tasks we need, that are not part of Ant's core task list (download these before reading on):

• For SVN Tasks we need:
  http://subclipse.tigris.org/svnant/svn.html
  
  
• For Mail Tasks we need:
  http://ant.apache.org/manual/CoreTasks/mail.html
  
  
• For FTP Tasks we need:
  Apache Commons  
  http://commons.apache.org/net/download.html
  
  
• Apache ORO
  http://jakarta.apache.org/site/downloads/downloads_oro.cgi

1. Install the JRE, if you don't already have it.
   
   
2. Download latest version of ANT: http://ant.apache.org/bindownload.cgi and unzip to c:\Ant
   
   
3. Download the above latest JAR Files in Zip files. Zip files you download may have more than just JAR files, but you need to isolate these JAR files and put the JAR files into Ant's classpath, C:\Ant\lib
   
   
   
4. Put all the JAR files under C:\Ant\lib
   
   
5. Add the following variables to your SYSTEM Variables
   

   ANT_HOME=C:\Ant  

    
   Add this to your existing PATH variable:
   

   C:\Ant\bin;  

    
   Also, make sure there's a CLASSPATH and JAVA_HOME system variables setup for the JRE.
   e.g. 

   CLASSPATH=.;C:\Program Files\Java\jre1.6.0_07\lib\ext\QTJava.zip  
   JAVA_HOME=C:\Program Files\Java\jdk1.6.0_07  

1. That should be it. Try to run, go to the command console and type "ant". To load a build file, type:
   

   ant \-buildfile build.ant  

   
   

The typical Eclipse setup already comes with the core Ant tasks, so you just have to get the optional ones. Download the attachment and preferably put them here: C:\Ant\lib

1. Run Eclipse
   
   
2. Go to Run > External Tools > External Tools Configurations
   
   
3. Double click on the Ant Build Icon
   This will open up the "New_Configuration Wizard"
   
   
4. Name the build DeployStaging (there should already be a name in the field, but it's called "New_configuration"
   
   
5. Under the Main tab, put in the Buildfile (in this case, "Browse File System...") - browse to where your buildfile is. (No need to include the .properties file, as the build file will call it.)
   
   
6. Under the Classpath tab, click on User Entries then click on "Add External JARs..." and point it to the directory which has all the JARs you downloaded (C:\Ant\lib).
   
   
7. Select all the JARs.
   
   
8. Click Apply.
   
   
9. Click Close.
   
   
10. Save if prompted.
    
    
11. Select the build file and then Go to Run > Run
    
    
12. To Modify the script you've just setup, go to Run > External Tools > External Tools Configurations
    
    
13. To select another default target (for example, for to get the latest changes), go to the Targets tab, uncheck the default, and select the target you want (in this case, "update_latest").
    
    
14. Click Apply and close.
    
    
15. To see the build progress in detail, open up the console view by Window > Show View >Other > General > Console.