Skip to content

CategorySQL Server

msdb.dbo.sp_sqlagent_get_perf_counters high CPU

That stored proc generates server stats, and depending on the stat results, it will fire off the alerts in SQL Server. If that instance of that stored proc hangs or has abnormally high CPU, just kill it. The agent will fire it again when needed.

Also, by default, SQL Server comes with a set of alerts that the server can use (called, “Demo”). I would keep the alerts active if possible and see what alerts are being triggered and why – check logs.

There was also a bug on SQL Server 2008 that was fixed that stemmed from this proc causing high CPU, even if all Alerts are disabled. I’m not sure if this fix applied to SQL Server 2000 with latest SP.

More details: http://connect.microsoft.com/SQLServer/feedback/details/536354/execute-msdb-dbo-sp-sqlagent-get-perf-counters-hogs-cpu-although-no-alerts-enabled#details

Order of Indexing Matters

In SQL Server, the order that you declare Indexes matters! So take the following example:

And we run this query:

SELECT  cast(ca.AreaID AS varchar(4)),
        RemovalDate
FROM    ContentAreas ca WITH ( NOLOCK )
WHERE   ContentID = 232232
        AND removerID = 34343

If you are to run the following query, since you are using ContentID as part of the WHERE clause, you want to put the ContentID’s index at the top of the indexes/key in SSMS. It speeds up retrieval.  Also put RemoverID below that – test with viewing the execution plan first to see if it makes a difference. Remember, the more indexes you add, the more it takes to update the index.

Add a Covered Index to tables to get rid of Bookmark Lookups

Add a Covered Index to tables to get rid of “bookmark lookups.” The idea of bookmarks: SQL Server uses Bookmarks when you use columns in your query that are not non-clustered/clustered indexes. So if we have something like this:

SELECT  AreaID,
        ContentID,
        ApprovalCodeID,
        isSubmissionPoint

FROM    ContentAreas

WHERE   AreaID = 31
        AND ContentID > 30000
        AND ModifierID > 65000

And let’s say that AreaID is a clustered index and ContentID is a non-clustered index. If ModifierID is not an index of any kind, it would function as a “bookmark lookup” in this query. To not use a “bookmark lookup,” use a Covered Index, which clumping the Modifier column with ContentID and turning both into a Covered Index (a kind of composite index).

Script Out Table Data in SQL Server 2008

SQL Server 2008 has a new feature where you can script out data in tables via INSERT statements. Just do the following:



1. Right click on the database and select “Generate Scripts…”

2. Select the database.

3. This is the hidden part. Set “Script Data” to true.

4. Select Tables.

5. Select Tables.

6. You can script out a table script per file or as a single file. I prefer ANSI text.

7. Hit Finish.

Generate DateTime based on Time Offset

So let’s say you have a table with a column of type DateTime. Now you have to support timezones. The first thing you create is a table of time offsets (Google Time Offsets). The table would look like this:

Here’s schema for it. You can download the full script with the data as well.

CREATE TABLE [dbo].[TimeZones](
  [TimeZoneID] [int] IDENTITY(1,1) NOT NULL,
  [Offset] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [TimeLabel] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_TimeZones] PRIMARY KEY CLUSTERED 
(
  [TimeZoneID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Now for the purpose of this tutorial, let’s create an event table. This will have a set of dates/times which we’ll use to add the timeoffsets to. The table looks like this:

and here’s the script:

CREATE TABLE [dbo].[TimeEvent](
  [TimeEventID] [int] IDENTITY(1,1) NOT NULL,
  [EventName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [EventDateTime] [datetime] NULL,
  [TimeZoneID] [int] NULL,
 CONSTRAINT [PK_TimeEvent] PRIMARY KEY CLUSTERED 
(
  [TimeEventID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Now let’s INNER JOIN the tables properly adding the timeoffset to the event datetime:

SELECT  tz.TimeZoneID,
        tz.Offset,        
        te.TimeEventID,
        te.EventName,
        te.EventDateTime AS 'OriginalEventDateTime',

        -- Hour - Extract Hour from Offset
        SUBSTRING(Offset, 0, CHARINDEX(':', Offset, 0)) AS 'Hour',
        
        -- Minute - Extract Minute from Offset        
        SUBSTRING(Offset, CHARINDEX(':', Offset, 0) + 1, LEN(Offset)) AS 'Minute',
        
        -- New Date with UTC Addition
        DATEADD(HOUR,
                CAST(SUBSTRING(Offset, 0, CHARINDEX(':', Offset, 0)) AS INT),
                DATEADD(MINUTE,
                        CAST(SUBSTRING(Offset, CHARINDEX(':', Offset, 0) + 1,
                                       LEN(Offset)) AS INT), EventDateTime -- DateTime change via Offset
                         )) AS 'EventDateTimeWithTimeZone'
FROM    TimeZones tz
        INNER JOIN TimeEvent te ON tz.TimeZoneID = te.TimeZoneID

Here’s the result:

Logical vs Physical ER Diagrams

Logical diagrams are to convey requirements only. Physical diagrams represent the actual data structure to support the requirements and take into account technical scalability and speed.

Edit: I hate the way I had to format this document for this blog post. If you want this tutorial better formatted, check out the Word document.

One-to-Many Relationship


Logical

On ER/Studio, two tables are created. The Store table has a primary key StoreID. The Manager table has a primary key, ManagerID and a foreign key, StoredID (which is mapped to StoreID from the Store table).

Physical

On SQL Server, two tables are created. The Store table has a primary key StoreID. The Manager table has a primary key, ManagerID and a foreign key, StoredID (which is mapped to StoreID from the Store table).

Manager Table:

If you allow NULLs for StoreID in the Manager table, then you’ll be able to have a Manager without a store. If you don’t allow NULLs (leave it unchecked), then you’ll have to have at least one Store assigned to a Manager.

Querying

The above states that one store can have many managers. Here’s some sample data what’s in the tables:

<div align="center"><br />
  <img src="http://www.shinylight.com/wp-content/uploads/2010/01/image006.jpg" width="240" height="171" />
  </p>
</div>
<p>
<div align="center"><br />
  <img src="http://www.shinylight.com/wp-content/uploads/2010/01/image007.jpg" width="414" height="77" />
  </p>
</div>
<p>Get all manager information with for all managers that  belong to a store:</p>
<table width="100%" border="0" cellspacing="5" cellpadding="3">
  <tr>
    <td>SELECT  Manager.ManagerID,  Manager.FirstName,  Manager.LastName,  Manager.StoreID,
        Store.[Name],  Store.Address,  Store.State,  Store.City,  Store.Zip
FROM    Manager
        INNER JOIN Store ON Manager.StoreID = Store.StoreID


Get all manager information with for all managers (even if they don’t have a store):

SELECT  Manager.ManagerID,  Manager.FirstName,  Manager.LastName,  Manager.StoreID,
        Store.[Name],  Store.Address,  Store.State,  Store.City,  Store.Zip
FROM    Manager
        LEFT OUTER JOIN Store ON Manager.StoreID = Store.StoreID


Notice the NULL for Steamboat Willie. He doesn’t have a store, so all Store related fields show as NULL.


Many-to-Many Relationship



In order to implement this physically, you need a join table. In this case, we use StoreManager. Logically, you only need only two entities (Store and Manager).

Logical

Physical


On SQL Server, three tables are created. The Store table has a primary key StoreID. The Manager table has a primary key, ManagerID. The table StoreManager has two foreign keys:  StoredID (which is mapped to StoreID from the Store table) and ManagerID (which is mapped to the ManagerID from the Manager table).

StoreManager Table:

If you allow NULLs for StoreID and ManagerID in the StoreManager table, then you’ll be able to have a Manager without a store. If you don’t allow NULLs (leave it unchecked for both), then you’ll have to have at least one Store assigned to a Manager.

Here’s some sample data what’s in the tables:

<p align="center"><img src="http://www.shinylight.com/wp-content/uploads/2010/01/image016.jpg" width="157" height="140" /></p>
<!--start_raw--><br /><!--end_raw-->

<p align="center"><img src="http://www.shinylight.com/wp-content/uploads/2010/01/image017.jpg" width="331" height="65" /></p>
<!--start_raw--><br /><!--end_raw-->

<p align="center"><img src="http://www.shinylight.com/wp-content/uploads/2010/01/image018.jpg" width="73" height="204" /><br />
</p>
<!--start_raw--><br /><!--end_raw-->
<p>Get all manager information associated with his store:</p>
SELECT  Manager.ManagerID,  Manager.FirstName,  Manager.LastName,
        StoreManager.StoreID, StoreManager.ManagerID, Store.StoreID,
        Store.[Name],Store.Address, Store.State, Store.City, Store.Zip
FROM    Store
        INNER JOIN StoreManager ON Store.StoreID = StoreManager.StoreID
        INNER JOIN Manager ON StoreManager.ManagerID = Manager.ManagerID

SQLCompare via Command Console

If you haven’t heard of SQLCompare by RedGate, you’re missing out. It’s an amazing product. In summary, you can do the following (which I use it for 99% of the time):

  • Compare Schema / TSQL code from Different Databases
  • Sync Schema / TSQL code across two databases
  • Generate TSQL code from your comparison so you can use it for deployments
  • Generate reports on SQL Changes

Most of those things can be done via the GUI version of the tool. The product also comes with a command line version, which you can use it in your build script.

To script out your entire database via the command console, you can do the following:

SQLCompare /force /database1:YOURDATABASENAME /username1:sa /password1:password /server1:YOURDATABASESERVERNAME /makescripts:c:\x

To compare two databases and generate a report via the command console, you can do the following. The reason there’s so many switches is because you need to enter the database name and credentials for the two databases.

SQLCompare /force /database1:DB1NAME /username1:sa /password1:password /server1:SERVER1NAME /database2:DB2NAME /username2:sa /password2:password /server2:SERVER2NAME /report:c:\report.html /reporttype:Interactive

Java Pairs Well with Which Database?

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.

SQL Server 2008 Installation Errors

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.

Temporary Stored Procedure

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

Search for Code in Stored Procedures

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:

Compare Table Data

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.

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.

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.

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.

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

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

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

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

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

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

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:

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

DROP DATABASE YourDatabase_Snap