SQL Server Central has a great function that converts a JSON string into a table. Worth checking out! (Unfortunately you have to register to see it, but it’s free.)

Function looks like this

1
2
3

-- object example, braces surround the name:value objects 
SELECT id, name, VALUE FROM
dbo.fnSplitJson2('{Apples:20000,Oranges:400,Apricots:507}',NULL)

Which returns a table:

1
2
3
4

id	name	    value
1	Apples	    20000
2	Oranges	    400
3	Apricots    507

Log Parser Lizard is a great free tool if you use Log Parser to parse IIS logs using SQL. It’s a visual tool to query the logs. It also comes with pre-made queries. Let’s take a look at one, “Requests and Full Status by Number of Hits” in IIS logs:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- Let's query the IIS W3SVC80086301 Log file c:\temp\logs\ex080918.log
SELECT 	STRCAT(	cs-uri-stem, 
		REPLACE_IF_NOT_NULL(cs-uri-query, STRCAT('?',cs-uri-query))
		) AS Request, 
	STRCAT(	TO_STRING(sc-status), 		
		STRCAT(	'.',
			COALESCE(TO_STRING(sc-substatus), '?' )
			)
		) AS Status, 
	COUNT(*) AS Total 
FROM c:\temp\logs\ex080918.LOG 
WHERE (sc-status >= 400) 
GROUP BY Request, Status 
ORDER BY Total DESC

Which gives you the following result (depending, of course, what’s in your logs):

Also, I could’ve queried all the log files put together, such as:

1

SELECT * FROM c:\temp\logs\*LOG

Also, you can create global variables and use them in your queries so that you don’t always have to put the full path to a file. For example:

I’m setting the variable IISW3C equal to c:\temp\logs\ex*.log . The queries that come with this tool use these variables (keys) as a shortcut. For your IIS logs dir, you may want to set it up to point to C:\WINDOWS\system32\LogFiles\W3SVC80086301 . Once you’ve done this, you can do (HIT F5 to run query):

1
2

-- Get the top 10 from all IIS logs
SELECT TOP 10 * FROM #IISW3C#

You can also view LogParser graphs from this tool. Let’s try the query to show all extension with total hits:

1
2
3
4
5
6
7

SELECT  TO_UPPERCASE(EXTRACT_EXTENSION( cs-uri-stem )) AS Extension, 
	COUNT(*) AS [Total Hits]
FROM #IISW3C# 
GROUP BY Extension 
-- Ignore .CFM extension
HAVING TO_UPPERCASE(EXTRACT_EXTENSION( cs-uri-stem )) <> 'CFM'
ORDER BY [Total Hits] DESC

LogParser is a great way to query IIS logs (any text log, actually, that is delimited).

Once you have it installed (default install is to C:\Program Files\Log Parser 2.2), let’s try to query log file ex090915.log from directory C:\WINDOWS\system32\LogFiles\W3SVC1942853941 . The way you would do this, is this:

1

LogParser "select date, s-ip, cs-method from C:\WINDOWS\system32\LogFiles\W3SVC1942853941\ex090915.log" -rtp:-1

As you can probably imagine, “date”, “s-ip”, and “cs-method” are the column headers from the log file. The select statement goes in quotes. Also, rather naming a table, you give the path to the log file. What’s the argument -rtp:-1 ? If you don’t include this argument, every 10 results, it will prompt you to “press a key…,” then will show you the next batch of results. In any case, the select state we just ran will spit out the following in the console:

date       s-ip            cs-method 
---------- --------------- ---------
2009-09-15 192.168.157.128 GET
2009-09-15 192.168.157.128 GET
2009-09-15 192.168.157.128 GET
2009-09-15 192.168.157.128 GET
2009-09-15 192.168.157.128 GET
2009-09-15 192.168.157.128 GET
2009-09-15 192.168.157.128 POST
2009-09-15 192.168.157.128 POST
 
 
Statistics:
-----------
Elements processed: 27
Elements output:    27
Execution time:     0.02 seconds

LogParser will even generate graphs (.gif format) of your results.

If you want to use a GUI for your queries, I suggest you try Log Parser Lizard.

This is a great way to script out (via the command console) the TSQL for your db schema with data included. The tool is called SQL Server Database Publishing Wizard. Scripting out your data (via insert statements) is optional, as you can script out just the schema. Check out the documentation for help.

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 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.