Upgrading to SQL Server 2008 (Part II)

Sometime ago, I published some tips on upgrading to 2008. Here are some additional notes to keep in mind.

For upgrading, my plan of attack in the past has been as follows:

1. Run the Upgrade Advisor on all the databases (this doesn’t require much effort, just download, install, point to the db and run)

2. On production, do a custom install and only install what you need. You can do a side-by-side install and have SQL Server 2008 installed while SQL Server 2000 exists on the same server.

3. Analyze the Advisor’s reports and refactor where SQL objects that need to be refactored

4. Refactor TSQL as needed

5. Now, TSQL isn’t just in the DBs, it may also exists in the app code and other web files (.aspx, etc.). This is where it gets more challenging. Check to make sure if the analyzer can open .trc files. If so, run SQL Server profiler while the site is being used and those queries are being run. Then open that .trc file in the analyzer so it can check the validity.

If this doesn’t work, log the profiler results to a table. Then put the TSQL from output of the profiler in a sproc. Point the analyzer to that sproc to check the syntax.

You can automate this task by using Selenium or write a script using WGET to fetch and look for 500 HTTP error codes.

6. Refactor TSQL as needed.

7. Test the websites with 2008. Ideally you want to start testing with the latest compatibility. Having a lesser level of compatibility causes more overhead (since it has to support older features) and can allow the use of deprecated features that MS will abandon in the next release.

9. Optimize the settings for db server. Strip out all dbs you don’t need to avoid security problems, like: Northwind, AdventureWorks.

10. Before pushing it live, make sure you backup all dbs, including all system dbs.

11. As soon as the switch is flipped, keep an eye out on perform/SSMS monitor. Have handy the appropriate DMVs to monitor.

12. Even if the application(s) did not throw errors while testing on dev, there’s a chance that once you go to production, it starts throwing errors due to compatibility (maybe the more obscure pages were not tested). Be ready to flip the compatibility mode to 2000 – this takes place instantly.

Renaming a SQL Server Table (Watch Out)

Sometimes while testing on your local db, one has the habit (I admit to have this in the past) of renaming a table before replacing it. One does this as a quick backup. The problem with this strategy is that it doesn’t rename all of the dependencies. For example, if you had a table formsClients and renamed it formsClients_old, and then restored the formsClients table again (let’s say from an SSIS import), you’ll get something like this:

If we inspect the dependencies, we’ll see that in this case, it’s a foreign key constraint:

Delete Duplicate Rows

If you’ve ever tried to delete two rows that are identical from SSMS, you may come across this little beauty:

To get around this, you have to delete 1 row at a time and use SET ROWCOUNT. So you would do something like

SET ROWCOUNT 1
GO
DELETE FROM users_to_optin where email = 'someone@yahoo.com'

That would delete one record at a time, even if there were multiple instances of ‘someone@yahoo.com’.

After you’re done, make sure you set the rowcount back to 0, which turns the limitation off.

SET ROWCOUNT 0
GO

Check out more about ROWCOUNT.

Scripting Out SQL Server 2000 Database Objects from SQL Server 2008 Management Studio

If you’re still stuck working with a SQL Server 2000 database, there’s no reason why you shouldn’t be using SSMS 2008 R2. There’s lots of great add-ons for it and provides a rich set of features, aside from merging Query Analyzer and Enteprise Manager.

One thing to watch out for, is that if you script out your database objects, while you’re connected to SQL Server 2000, by default, it will script out TSQL that is compatible only with SQL Server 2008. To switch this, there are two ways:

Tools -> Options -> SQL Server Object Explorer -> Scripting

Database -> Tasks -> Generate Scripts

SSIS Data Import from Excel

I’ve always found the SSIS import tool a bit clunky. I tried to import a CSV file and it get crapping out before completing.

– Copying to [dbo].[export] (Stopped)

Messages

  • ·Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column “Email Address” returned status value 4 and status text “Text was truncated or one or more characters had no match in the target code page.”.
    (SQL Server Import and Export Wizard)
  • ·Error 0xc020902a: Data Flow Task 1: The “output column “Email Address” (10)” failed because truncation occurred, and the truncation row disposition on “output column “Email Address” (10)” specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
    (SQL Server Import and Export Wizard)

I sifted through the data, only a few hundred rows, and I could not see anything wrong with it. I made the columns the right type and increased the column size more than the largest number of characters in the fields. I searched Google and Microsoft Forums trying a handful of solutions and nothing worked.

So what did I do? I had enough and just opened the file from CSV, into Excel, resaved it as an Excel 2007. Re-ran the SSIS import wizard to open an Excel file instead of a CSV, and voila. I’m still baffled though, as to what exactly the problem is.

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.

Testing Database Import Scripts with Snapshots

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

TSQL Functions Inspired By ColdFusion’s Lists Functions

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

RedGate SQL Search

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.

Connection Problem with SQL Server 2008

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.