CategorySQL Server

Linked Server via MS Jet 4.0 Provider

Here’s another way to created a linked server using another provider, Microsoft Jet 4.0 OLE DB Provider. This is for SQL Server 2008. Check out my previous Linked Server tutorial if needed for SQL Server 2005.

For this setup, let’s use the login’s current security context. Make sure that you whatever SSMS you use to connect to the server uses the same user credentials as user that created the linked server.

So if I create that on a server, then I’ll have query it using the same credentials just used. If you want to know how to query it, check out my Previous Tutorial.

Common Table Expressions

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.

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:

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:

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

Linked Servers

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$']

TSQL Function to Split JSON Data

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

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

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

Bulk Import Ignoring Identity Column

Ever have to bulk import a text file (e.g. from Excel, or tabular delimited rows) into a table that had the first column be an identity auto-incrementing primary key? Yes, you could create a format file that skips the identity column, so that the first column of your text file doesn’t go into the identity column of your table. This MSDN page shows more about it.

The quick way though, is to create a view of that table and omit the identity column when you create the view. In this manner, your first column in the text file won’t map to the identity column and throw one of those delicious BCP errors.