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.

Can’t stress enough how handy database snapshots (compatible only in Enterprise and Developer editions of SQL Server 2005+) come in when testing bulk imports, data scrubbing, or any sort of data / schema modification script. In nearly no time, I can create a snapshot (via shortcut snippet), run my script – don’t like the results? My script crapped out the data? I can run an instant undo by recovering from the snapshot, which works pretty fast. If I want to create another snapshot, I usually tend to overwrite the snapshot I created, and then create it again.

It’s so handy that I have a snippet for it:

Anywhoot, here’s how you create a snapshot:

1
2
3
4
5
6
7

-- CREATING A SNAPSHOT
CREATE DATABASE YourDatabase_snap ON         -- Name of new snapshot
(
  NAME = yourdb_data,                        -- Logical filename of source db you want to snapshot
  FILENAME = 'c:\YourDatabase_data_1800.ss'  -- Sparse file to create
)
AS SNAPSHOT OF YourDatabase;                 -- Source db name

1
2
3
4

-- RECOVERING FROM A SNAPSHOT
USE master;
RESTORE DATABASE YourDatabase                    -- Source db name where the data resided
FROM DATABASE_SNAPSHOT = 'YourDatabase_snap';    -- Snapshot db name

Deleting a snapshot is just like dropping a database.

1

DROP DATABASE YourDatabase_Snap

In my last project, there was a bit of data scrubbing on the database side (SQL Server 2008) that I decided to create a few UDF’s that function similar to ColdFusion’s Lists function. The one that varies a little bit is ListLen(), since I needed to take into account empty tokens. The ChopIf() was inspired by Perl’s chop() function. These UDFs should be SQL Server 2005-compatible.

I should say though, that some of these functions depend on each other. ListLen(), GetToken(), and ChopIf() are independent.

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

------------------------------------------------------------------
-- Functions similarly like ColdFusion ListSort() function,
-- except it currently only sorts strings. 
--
-- Example 1:
--    dbo.ListSort( 'dan is so mega awesome that he rules all the time', 'ASC', ' ' )
--
-- Returns: 
--    all awesome dan he is mega rules so that the time
--
-- Example 2:
--    dbo.ListSort( 'dan is so mega awesome that he rules all the time', 'DESC', ' ' )
--
-- Returns: 
--    time the that so rules mega is he dan awesome all
------------------------------------------------------------------
 
CREATE FUNCTION [dbo].[ListSort]
(
  @string    VARCHAR(2000),  
  @sort_type CHAR(3)       = 'ASC',
  @delimiter VARCHAR(2000) = ','
)
RETURNS VARCHAR(500)
AS
 
BEGIN
 
  DECLARE @position AS INT
  DECLARE @token AS VARCHAR (2000)
  DECLARE @counter   AS INT
  DECLARE @sortedList AS VARCHAR(500)
 
  DECLARE @sortTempTable TABLE ( token VARCHAR(500) )
  DECLARE @sortedTable   TABLE ( token VARCHAR(500) )  
 
  SELECT @string   = @string + @delimiter,
         @counter  = 1,
         @position = 0,
         @token    = ''
 
  WHILE ( PATINDEX( '%' + @delimiter + '%' , @string ) <> 0 ) 
  BEGIN
    SELECT @position = PATINDEX('%' + @delimiter + '%' , @string ),
           @token    = LEFT( @string, @position - 1 ),
           @string   = STUFF( @string, 1, @position, NULL ),
           @counter  = @counter + 1
 
    INSERT @sortTempTable( token ) VALUES( @token )     
  END
 
  SET @sortedList = ''
 
  -- Let's sort the table and put it into @sortedTable
  -- Because of nature of Rank(), we can't set @sortedList in this statement.
  -- Have to separate it into another select clause.
  INSERT INTO @sortedTable
    SELECT LTRIM( token )
    FROM   @sortTempTable
    ORDER  BY CASE WHEN @sort_type = 'ASC'  THEN ( RANK() OVER ( ORDER BY LTRIM(token) ASC ) )
                   WHEN @sort_type = 'DESC' THEN ( RANK() OVER ( ORDER BY LTRIM(token) DESC ) )
              END  
 
  SELECT @sortedList = @sortedList + token + @delimiter
  FROM   @sortedTable
 
  RETURN dbo.ChopIf( @sortedList, @delimiter )
 
END
GO

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

------------------------------------------------------------------
-- Functions sort of like ColdFusion's ListLen() method, but it
-- takes into account empty tokens. 
--
-- Example 1:
--    dbo.ListLen( 'Dan is cool', ' ' )
--
-- Returns: 
--    3
-- 
-- Example 2:
--    dbo.ListLen( 'dan,,very,,,,awesome,', ',' )
--
-- Returns: 
--    8
------------------------------------------------------------------
 
CREATE FUNCTION [dbo].[ListLen]
(
  @string VARCHAR(2000),
  @delimiter VARCHAR(2000) = ','
)
RETURNS INT
AS
BEGIN
 
  DECLARE @loopCount INT, 
          @tokenCount INT
 
  SELECT @loopCount = 0, 
         @tokenCount = 0
 
  -- If it's an empty string, the list length is 0
  IF DATALENGTH( @string ) = 0
    BEGIN
      SET @tokenCount = 0
    END
  ELSE
    BEGIN
      -- Count tokens, including empty ones like dan,,very,,,,awesome,
      SET @tokenCount = @tokenCount + 1
      WHILE ( @loopCount < DATALENGTH( @string ) )
      BEGIN
        IF SUBSTRING( @string, @loopCount, DATALENGTH( @delimiter ) ) = @delimiter
          BEGIN
            SET @tokenCount = @tokenCount + 1
          END
        SET @loopCount = @loopCount + 1
      END
    END
 
  -- Handle extra count from space being delimiter
  IF @delimiter = ' '
    SET @tokenCount = @tokenCount - 1
 
  -- If there's no token to the right of the last delimiter, then count that
  -- as an empty token.
  IF ( RIGHT( @string, 1 ) = @delimiter ) 
  BEGIN
    SET @tokenCount = @tokenCount + 1
  END
 
  RETURN @tokenCount
 
END
GO

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

------------------------------------------------------------------
-- Functions like ColdFusion's ListLast()
-- Gets token value that's been separated by a delimiter.
--
-- Example:
--    dbo.ListLast( 'Dan is cool', ' ' )
--
-- Returns: 
--    cool
------------------------------------------------------------------
 
CREATE FUNCTION [dbo].[ListLast]
(
  @string VARCHAR(2000),
  @delimiter VARCHAR(2000) = ','
)
RETURNS VARCHAR(2000)
AS
BEGIN
 
  RETURN dbo.ListGetAt( @string, dbo.ListLen( @string, @delimiter ) , @delimiter  )
 
END
GO

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

------------------------------------------------------------------
-- Wrapper for GetToken() Function
-- Gets token value that's been separated by a delimiter.
--
-- Example:
--    dbo.ListGetAt( 'Dan is cool', 2, ' ' )
--
-- Returns: 
--    is
------------------------------------------------------------------
 
CREATE FUNCTION [dbo].[ListGetAt]
(
  @string VARCHAR(2000),
  @token INT,
  @delimiter VARCHAR(2000)
)
RETURNS VARCHAR(2000)
AS
BEGIN
  RETURN dbo.GetToken( @string, @token, @delimiter )
END
GO

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

------------------------------------------------------------------
-- Returns the first item in a tokenized list.
--
-- Example:
--    dbo.ListFirst( 'Dan is cool', ' ' )
--
-- Returns: 
--    Dan
------------------------------------------------------------------
 
CREATE FUNCTION [dbo].[ListFirst]
(
  @string VARCHAR(2000),
  @delimiter VARCHAR(2000) = ','
)
RETURNS VARCHAR(2000)
AS
BEGIN
 
  RETURN dbo.ListGetAt( @string, 1, @delimiter )
 
END
GO

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
29
30
31
32
33
34
35
36
37
38
39
40

------------------------------------------------------------------
-- Functions similarly like ColdFusion GetToken() Function.
-- Gets token value that's been separated by a delimiter.
--
-- Example:
--    dbo.GetToken( 'Dan is cool', 2, ' ' )
--
-- Returns: 
--    is
------------------------------------------------------------------
 
CREATE FUNCTION [dbo].[GetToken]
(
  @string VARCHAR(2000),
  @tokenPosition INT,
  @delimiter VARCHAR(2000)
)
RETURNS VARCHAR(2000)
AS
BEGIN
 
  DECLARE @position AS INT
  DECLARE @token AS VARCHAR (2000)
  DECLARE @counter AS INT
 
  SELECT @string = @string + @delimiter,
         @counter = 1,
         @position = 0,
         @token = ''
 
  WHILE ( PATINDEX('%' + @delimiter + '%' , @string ) <> 0) AND ( @tokenPosition + 1 <> @counter )
  BEGIN
    SELECT @position = PATINDEX('%' + @delimiter + '%' , @string),
           @token    = LEFT(@string, @position-1),
           @string   = STUFF(@string, 1, @position, null),
           @counter  = @counter + 1
  END
 
  RETURN @token
END

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

------------------------------------------------------------------
-- Chops the last character if it's @chopped
--
-- Example:
--    dbo.ChopIf( 'Dan is cool!', '!' )
--
-- Returns: 
--    Dan is cool
------------------------------------------------------------------
 
CREATE FUNCTION [dbo].[ChopIf]
(
  @string VARCHAR(2000),
  @chopped VARCHAR(2000)
)
RETURNS VARCHAR(2000)
AS
BEGIN
 
  IF ( RIGHT( @string, DATALENGTH(@chopped) ) = @chopped )
  BEGIN
    SET @string = LEFT( @string, DATALENGTH( @string ) - DATALENGTH( @chopped ) ) 
  END 
 
  RETURN @string
 
END
GO

I love this tool. It’s a free add-on if you own SQL Prompt, RedGate’s version of intellisense, which is better, in my eyes, than Microsoft’s. It uses SQL Prompt’s FTS collection to search for any piece of string in any DB object. It’s crazy fast. What I like the most though, is the ability to right click on a result, and move the focus to appropriate DB object in the explorer panel. Hit CTRL+ALT+D to trigger the search screen and start typing away.

I can’t say how many various problems I’ve had connecting to SQL Server. I’ve lost count at this point. This was the last one I had, which I’ve encountered a number of times and always successful to fix it.

Cannot connect to x.x.x.x.

Additional Information:
A network-related or instance-specific error occurred while establishing a connection to SQL Server….

Now usually when I encounter this error, I do something suggested here at Pinal Dave’s site. This time nothing mentioned there worked. What did work, was me explicitly selecting the Network protocol, and not leaving it as the :

Still wondering why I have to do this, as I’ve never had to explicitly select the protocol before, and nothing on the server has changed in years. Will continue to investigate.

Currently I use RedGate’s SQL Data Compare to see differences in data across databases. It’s a great product. One thing I love about RedGate is the usability of their products. All of them are intuitive and easy to use. What if you don’t have the cash to do this though?

You could use the EXCEPT (introduced in SQL Server 2005) clause to do your comparison. For example:

  SELECT  *  
  FROM    #table2 -- returns 30 records
 
  EXCEPT
 
  SELECT  *  
  FROM    #table1 -- returns 10 records.

You get a result set of 20 records if those 10 records in the table1 statement are in table2. You can think of it as subtracting records from table2 that are in table1. You will get the end result of 20 records.

The following statement builds on that taking the end result and putting it back into table1. This is useful if you want to sync an outdated table (in this case table1) with another table that has additional records (table2).

INSERT INTO #table1
  SELECT  *  
  FROM    #table2 -- latest data table (we'll be copying from here)
 
  EXCEPT
 
  SELECT  *
  FROM    #table1 -- outdated table (has old data)

Other tools to compare data across tables: CompareData may let you do what you want, and its table compare is free unlimited, I believe, for the evaluation version. The great thing about this tool is also that it will check results of two SQL statements / stored procs. Useful when unit testing.

Also you can use TableDiff.exe in your “C:\Program Files\Microsoft SQL Server\90\COM” directory. Learn more about it at the SQL team website.

One handy trick that I used to do was use the function OBJECT_DEFINITION() to view the code of a sproc. It’s a handy little function that doesn’t get much love as OBJECT_ID or OBJECT_NAME(). Let’s say we want to get the code of the sproc dbo.uspGetBillOfMaterials from DB AdventureWorks. We do like so:

SELECT OBJECT_DEFINITION( OBJECT_ID('dbo.uspGetBillOfMaterials') )

You get a result that may not be too pretty. In this case, use PRINT rather than SELECT, since SSMS strips out line breaks to squeeze it in the cell.

Now let’s search all available user sprocs in the database to search for the string “%GetBillOf% :

SELECT  OBJECT_DEFINITION( p.[OBJECT_ID] ) AS code 
FROM    sys.procedures p
WHERE   OBJECT_DEFINITION( p.[OBJECT_ID] ) LIKE '%GetBillOf%'

Now, I do longer search this way since every day I have a job that writes out all DB objects (tables, sprocs, etc.) to a individual files and they get indexed via Copernic Desktop Search. I script them out using SQL Compare.

Unfortunately, I don’t know why, OBJECT_DEFINITION() doesn’t work on tables. It’s been a wanted feature by the community. If you want to learn more about this function, check out:

• How to script all stored procedures in a database
• Script Table definitions using TSQL

They can be handy sometimes if you want to modularize your code for the life-time of a routine. To create a temp stored procedure, all you do is use the # sign before the name of the sproc. Same concept as a temp table. Only the owner can execute it and its life is that of the session. Here’s an example that uses the Northwind db.

CREATE PROCEDURE #GetCustomers 
(
  @companyName VARCHAR(50)
)
AS
BEGIN
  SELECT * FROM Customers
  WHERE  CompanyName LIKE '%' + @companyName + '%'
END

To use it:

EXEC #GetCustomers 'The'

You can find the temp sproc in the tempdb:

If you want more information on the temp sproc, you can query the system view under the tempdb:

SELECT * FROM tempdb.sys.objects
WHERE [OBJECT_ID] = OBJECT_ID('tempdb.dbo.#GetCustomers')

Your next question maybe if you can create temp user defined functions? The answer is a big juicy delicious, “NO!” (at least as of SQL Server 2008 Enterprise).

Recently I’ve had to install SQL Server 2008 on a few machines. Some of these errors made absolutely no sense, but I found solutions that worked for me. Maybe they’ll help you.

Windows 2000 Server (32-bit)

“The procedure entry point GetConsoleProcessList could not be located in the dynamic link library KERNEL32.dll.”

That sounds pretty self-explanatory, right? Actually, this was the easiest error to make sense of. This is the error you get because SQL Server 2008 is not compatible with Windows 2000 Server. It comes from the fact that SQL Server 2008 needs .NET 3.5+, and that version of .NET is not Windows 2000-compatible.

Windows XP and Windows 2003 Server (32-bit)

Surprisingly, I received no errors while trying to install it on Windows XP. Everything went fine with Windows 2003 Server.

Windows 7 (64-bit)

OK, this was hell on earth. First I ran the setup, and mid-way through the installation, I received this gem:

“SQL Server Setup has encountered the following error:

Invoke or BeginInvoke cannot be called on a control until the window handle has been created..”

I noticed that this arises if you have the focus on another Window while the installer is running. I was browsing the web via Firefox and I received this. To make sure you don’t get this, don’t have any other Window running and only have the focus on the installer.

That wasn’t the only error I received. When I initially ran the setup, I saw a console window flash and then nothing would happen. (I waited for a few minutes and nothing. Setup.exe wasn’t even in the task manager.) When you run setup.exe, it fires landingpage.exe, which is the actual wizard installer. Unfortunately, it wasn’t running it. I even made sure I had the requirements, and nothing. I rebooted several times, and finally decided to run landingpage.exe directly. When I ran it, it finally saw the wizard. I was joyful, until it got to this part:

What the heck?! Where are the features?

Why would the wizard make it this far and then then not have the features to let me install? This was useless. If you’re curious, it should’ve looked like this:

Then for the heck of it, I decided to Install SQL Server 2008 SP1, without even installing SQL Server 2008 first. Don’t know why, but it let me install it anyways. Then when I ran the SQL Server 2008 installer again, but this time, I would see these features:

Ugh, still no luck. Finally, the only thing that I could conclude was that I may have a bad installation. So I restored my machine to an earlier restore point (to undo this installation mess) and re-downloaded the installation files.

Voila! No more problems. So that’s what it was. I was finally able to see all the features to install. So maybe the files got corrupted upon download or it was an incomplete download.

In the same way there’s a tight bond between MySQL and PHP, SQL Server and ASP.NET, SQL Server and ColdFusion – what goes well with Java? Oracle? Being curious, I started searching in employment web sites. I searched for “Java” and one of these databases: Oracle, MySQL, SQL Server and PostgreSQL. (I put in “SQL Server” using quotes.) The sites used were: craigslist, Monster.com, Dice.com, and Yahoo! Hotjobs.

The numbers signify how many job entries were returned.

So it does seem Oracle goes with Java. Also I noticed how many people call “SQL Server” just “SQL.” Sort of confusing and hard to tell if they’re referring to the platform or language.