Importing Excel Data into SQL Server

Sometimes it can be a pain. Why? Because SSMS (or should I say SSIS) uses the Windows Excel driver, and it infers data types of the columns incorrectly, and will choke when you import. Sometimes it will work, sometimes not. So you have to import it as text. However, first you have to re-save the Excel file as a CSV (which will by default use commas as delimiters, and quotes for qualifiers).

So…

Step 1:
In Excel, save the file as a CSV, in this case, file.csv.

Step 2:
In SSMS, under the Import wizard, choose “Flat File Source:

Step 3:
Leave everything default, except use a one quote in the qualifier textbox:

STEP 4:
In the Advanced section, select all the columns and make sure the OutputColumnWidth is something big enough, larger than the size of the largest column. In this case, we’ll make it 150. Make sure the DataType is string [DT_STR]

Step 5:
Leave the rest of the settings except the target database/table where you’re importing, and Finish the import.

Step 6:
Done.

Better SSMS Objects Management with SQLTreeo

This is so cool and handy. You can hide and organize database objects (procs, tables, views, databases) in folders. This organization is saved locally to your machine, on a the server level, via extended properties. If you decide to save the info on the server, someone else who has the extension will view it organized accordingly.

Check it out.

Restoring an MDF/LDF from Another Machine

If you need to restore an MDF/LDF from one machine (source) into another machine (target), you copy the source’s MDF/LDF file to the target machine. Then start the target server in single user mode. Then run SSMS as Administrator on the target machine and restore the files. If you don’t run it as Admin, you’ll get the error listed here:

http://stackoverflow.com/questions/2330439/access-is-denied-when-attaching-a-database

http://stackoverflow.com/questions/4661013/sql-server-attaching-database-access-denied

Who’s Locking The Table

This is the script I use to view who/what is locking the table in SQL Server:

SELECT  l.request_session_id AS SessionID,
        o.Name AS 'Table Locked',
        DATEDIFF(second, at.Transaction_begin_time, GETDATE()) AS 'Duration (Seconds)',
        FORMAT(at.Transaction_begin_time, 'yyyy-mm-dd h:mm:ss tt') 'Started On',
        es.login_name 'User',
        es.[program_name] 'Program',
        COUNT(*) AS Locks
FROM    sys.dm_tran_locks l
        JOIN sys.partitions p ON p.hobt_id = l.resource_associated_entity_id
        JOIN sys.objects o ON o.object_id = p.object_id
        JOIN sys.dm_exec_sessions es ON es.session_id = l.request_session_id
        JOIN sys.dm_tran_session_transactions st ON es.session_id = st.session_id
        JOIN sys.dm_tran_active_transactions at ON st.transaction_id = at.transaction_id
WHERE   resource_database_id = DB_ID()
        AND o.Type = 'U'
GROUP BY at.Transaction_begin_time,
        l.request_session_id,
        o.Name,
        es.login_name,
        [program_name]

Search For Column Name in All Databases

Whipped up this little script today to search for a column in all my databases.

--------------------------------------------------------------------------------------------------
-- Search for the column name
--------------------------------------------------------------------------------------------------
DECLARE @searchFor AS VARCHAR(50)     = 'CustomerID' -- keyword to search for
DECLARE @databaseList AS VARCHAR(MAX) = 'Database1,Database2,Database3,Database4' -- comma delimited
 
DECLARE @orderBy AS VARCHAR(50)       = 'DateCreated ASC' -- You can also append a space and the ASC/DESC
-- Order By One of These
-- ObjectID, ColumnName, Table, Database, Date Created, Modified Date
--------------------------------------------------------------------------------------------------
 
DECLARE @sql NVARCHAR(MAX) = ''
-- loop
SET @databaseList = @databaseList + ','
DECLARE @pos AS INT
DECLARE @val AS VARCHAR(255)
 
WHILE PATINDEX('%,%', @databaseList) <> 0
BEGIN       
  SELECT  @pos = PATINDEX('%,%', @databaseList)
  SELECT  @val = LEFT(@databaseList, @pos - 1)       
  SELECT  @databaseList = STUFF(@databaseList, 1, @pos, NULL)
  SET @sql = @sql + 
             'SELECT  *
              FROM    ( SELECT  portalColumns.OBJECT_ID AS ObjectID ,
                                portalColumns.[NAME] ColumnName ,
                                ''[' + @val + '].dbo.['' + portalTables.[NAME] + '']'' AS [Table] ,
                                ''[' + @val + ']'' AS ''DatabaseName'',                  
                                create_date AS ''DateCreated'' ,
                                modify_date AS ''ModifiedDate''
                        FROM    [' + @val + '].sys.columns portalColumns
                                INNER JOIN [' + @val + '].sys.tables portalTables ON portalColumns.object_id = portalTables.object_id
                        WHERE   portalColumns.name LIKE ''%' + @searchFor + '%''
                      ) AS Results
              UNION '                       
END
 
SET @sql = LEFT(@sql, LEN(@sql) - 5) + ' ORDER BY ' + @orderBy
 
--PRINT @sql    
 
EXECUTE sp_executesql  @sql

Dapper – A C# Micro ORM Library Makes Data Access a Snap – Part 1

I’ve recently gotten a chance to try out Dapper – a micro ORM library by Sam Saffron, which originally spawned from StackOverflow’s codeset. If you’re looking for an extremely light-weight library to access your SQL Server, I highly recommend it. It focuses more on convention than configuration. It also does not have the overhead that EntityFramework or NHibernate have, and it emphasizes speed and ease-of-use.

Dapper is a collection of Extension Methods that enhance the Connection object (or rather extends the IDbConnection interface). It also embraces the use of inline SQL in your code, which is useful when retrieving sets that are less trivial. With its buddy, Dapper-Extensions by Thad Smith, it makes DB interaction even easier.

Both of these libraries can be downloaded from NuGet, and you’ll need them to run the following code.

There’s really just one thing that I found irritating about the Dapper project: the documentation. While there is documentation found at its Google Code home page, I find it very lacking.

Anywhoot, to get started first we need to create our table. Once we have that and add some data, we’ll use the Dapper to extract the data and map to objects.

----------------------------------------
-- Customer Schema
----------------------------------------
CREATE TABLE [dbo].[Customer](
[CustomerID] [uniqueidentifier] NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Age] [int] NULL,
[IsAllowed] [bit] NULL,
[DateRegistered] [datetime] NULL,
[AdditionalDetails] [nvarchar](max) NULL,
CONSTRAINT [PK_Customer_1] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[Customer] ADD  CONSTRAINT [DF_Customer_CustomerID]  DEFAULT (newid()) FOR [CustomerID]
GO
 
----------------------------------------
-- Data
----------------------------------------
INSERT INTO [dbo].[Customer]([CustomerID], [FirstName], [LastName], [Age], [IsAllowed], [DateRegistered], [AdditionalDetails])
  SELECT N'5458dff8-cea2-4bdb-9431-1dea56f109f8', N'Bruce', N'Wayne', 31, 1, '20080604 08:13:44.000', N'I am Batman' UNION ALL
  SELECT N'1767bc57-af03-4b17-891f-2aa9af244180', N'Peter', N'Parker', 25, 1, '20120103 12:22:28.000', N'I am Spider-Man' UNION ALL
  SELECT N'dc2bf42d-045b-4189-bd6b-8bf1bf120291', N'Eddie', N'Brock', 34, 0, '20040123 02:45:41.000', N'We are Venom'

OK, so this is what we have so far:

Because Dapper focuses more on convention over configuration, it’s important to note that when you create your class:

  • The name of your class must match the name of the table, otherwise an exception is thrown.
  • All column names must match all class property names, otherwise an exception is thrown.
  • If a column is not used (in NOT written out in the SELECT statement) and the class property exists, it will set to NULL or assign a default value – more on this later.

Now that we’re aware of these points, let’s see the code to get started:

using System;
using Dapper;
using System.Data.SqlClient;
 
class Customer
{
  public Guid CustomerID { get; set; }
  public string FirstName { get; set; }
  public string LastName { get; set; }
  public int Age { get; set; }
  public bool IsAllowed { get; set; }
  public DateTime DateRegistered { get; set; }
  public string AdditionalDetails { get; set; }
}
 
class Program
{
  private static void Main()
  {
 
    using (SqlConnection conn = new SqlConnection("Data Source=NARUTO;Initial Catalog=TESTDATABASE;Integrated Security=True"))
    {
      conn.Open();
 
      // Put dapper code here.
 
      conn.Close();
    }
 
    Console.ReadLine();
  }
}

OK, so there’s nothing out of the ordinary here. We’re creating the class we want to map our data to, creating a Connection object, opening, closing, and then disposing (via using). Now let’s suck up some data within our Main() method:

private static void Main()
{
  using (SqlConnection conn = new SqlConnection("Data Source=NARUTO;Initial Catalog=TESTDATABASE;Integrated Security=True"))
  {
    conn.Open();
 
    IEnumerable customers = conn.Query("SELECT * FROM Customer");
 
    foreach (Customer c in customers)
    {
      Console.WriteLine("CustomerID: " + c.CustomerID);
      Console.WriteLine("FirstName: " + c.FirstName);
      Console.WriteLine("LastName: " + c.LastName);
      Console.WriteLine("Age: " + c.Age);
      Console.WriteLine("IsAllowed: " + c.IsAllowed);
      Console.WriteLine("DateRegistered: " + c.DateRegistered);
      Console.WriteLine("AdditionalDetails: " + c.AdditionalDetails);
      Console.WriteLine("-----------------------------------------------");
    }
 
    conn.Close();
  }
 
  Console.ReadLine();
}

As we see, the Query extension method pulls in the data based on the select statements. The Query method returns a strongly-typed list of Customer. You should see the following:

Now what if we want to get the number of records in the table? Well, because Query returns an IEnumerable, we cannot use the Count property. To do so, we convert to a List:

List<Customers> customers = (List<Customers>)conn.Query("SELECT * FROM Customer");
Console.WriteLine(customers.Count);

That should give us the count. To see how the data types were mapped, we can take a look at the Dapper source code, SqlMapper.cs inside the static method SqlMapper(). For reference, you can take a peek at these two sources to understand SQL Server/CLR/.NET framework data types:

http://stackoverflow.com/questions/425389/c-sharp-equivalent-of-sql-server-2005-datatypes

http://msdn.microsoft.com/en-us/library/ms131092.aspx

For our example though, we saw that the data types were mapped:

Mismatches and Nulls

In the query that we ran, we were making the assumption that all columns in the db match all the properties in the class. What if we have a mismatch? What if we have this?

IEnumerable customers = conn.Query("SELECT LastName FROM Customer");

Notice that we’re only retrieving the LastName, while we have other properties in our Customer class. Well, this is what you would see:

So from the above, we notice that Dapper will handle Nulls by:

  • Setting strings as Nulls
  • Setting a Guid to 00000000-0000-0000-0000-000000000000
  • Setting an int to 0
  • Setting bool to false
  • Setting a DateTime to 1/1/0001 12:00:00 AM

Which will all happen when Dapper cannot find a class property to map to from the column value. Also, if you column name that DOES NOT match a property, it will throw a SqlException of “Invalid Column Name”.

Parameterization

What if we want to pass a parameter into our SQL? Let’s pass a parameter:

IEnumerable customers = conn.Query("SELECT * FROM Customer WHERE AGE &gt; @Age", new { Age = 30 } );

Notice that I’m passing in an anonymous object. I could also have done:

Customer someCustomer = new Customer {
Age = 33,
FirstName = "Clark Kent"
};
 
IEnumerable customers = conn.Query("SELECT * FROM Customer WHERE AGE &gt; @Age", someCustomer );

Which yields:

The property in someCustomer (FirstName = “Clark Kent”) is silently ignored because it’s not used in the SELECT statement.

When the above is run, SQL Server Profiler shows that the following is run:

exec sp_executesql N'SELECT * FROM Customer WHERE AGE &gt; @Age',N'@Age int',@Age=33

So we know it’s parameterized.

To call a stored procedure and map it to objects, we do, assuming we have the following sproc:

CREATE PROCEDURE GetCustomers
(
@IsAllowed BIT
)
AS
BEGIN
 
SELECT * FROM Customer
WHERE IsAllowed = @IsAllowed
 
END

We do:

// The second @ token does not have to be named "@IsAllowed" and can be named anything as
// long as the C# property in the anonymous object is named the same thing.
IEnumerable customers = conn.Query("GetCustomers @IsAllowed = @IsAllowed", new { IsAllowed = 33 } );

Also accepted:

IEnumerable customers = conn.Query("GetCustomers @IsAllowed = @Allow", new { Allow = 33 } );

Or you can be more explicit by specifying the command type (StoredProcedure):

IEnumerable customers = conn.Query("GetCustomers", new { IsAllowed = 33 }, commandType: CommandType.StoredProcedure );

Or writing it a more secure way:

DynamicParameters parameters = new DynamicParameters();
parameters.Add("@IsAllowed", 'f', dbType:DbType.Int32);
 
IEnumerable customers = conn.Query("GetCustomers", parameters, commandType: CommandType.StoredProcedure );

To insert data, we do as follows:

Customer customer = new Customer{
  FirstName = "Mazinger",
  LastName = "Z",
  Age = 40,
  IsAllowed = true,
  DateRegistered = DateTime.Now,
  AdditionalDetails = "Metallic defender"
};
 
conn.Execute(@"INSERT INTO Customer
                                  ( FirstName ,
                                  LastName ,
                                  Age ,
                                  IsAllowed ,
                                  DateRegistered ,
                                  AdditionalDetails
 
                   )
                   VALUES  ( @FirstName ,
                                 @LastName ,
                                 @Age ,
                                 @IsAllowed ,
                                 @DateRegistered ,
                                 @AdditionalDetails
                               )"
                 , customer);

This ends part 1 of the tutorial. I will post the 2nd part soon.

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

SSMS Template Explorer (Snippets)

I sometimes see people miss this. A very resourceful feature is the Template Explorer in SQL Server Management Studio. Here’s how to take advantage of it:

1. First you show the panel in SQL Server:

2. Double click on the snippet/template. We’ll double click on the Create Multi-statement table-value Function:

3. After you do so, it’ll open up a new query window with the source of the snippet.

4. Do CTRL+SHIFT+M to fill in the parameters:

Checklist to Consider Before Migrating from SQL Server to NoSQL

Here’s a checklist of items to consider before migrating to a NoSQL db from SQL Server. If you come from SQL Server land, you may be used to these features and facilities. So before shifting over to a different world, consider if the db you’re jumping into (MongoDB, CouchDB, etc.) meets your needs by analyzing these items. Keep in mind that some features may not exist in NoSQL, not because of immaturity, but because it may not apply to the nature of a NoSQL database.

Handle Load

Compare against SQL Server:

Reading

Writing

Resource Monitoring

CPU Usage

Memory Use

Disk use

Transactions

Are there software design patterns to rollback

Locking

Reading

Programming

Syntax

SELECT / UPDATE / DELETE / INSERT / JOINS

Reusable Code Modules

Compiled Stored procs

Functions

Custom Data Types

Dynamic Management Views

Indexing

Clustered

Non-Clustered

Toolset / Resources

IDE

Administration

Profiler

Excution Planner

Community

Books

Administration

Complexity

Backing up

Full

Differentials

Transaction Log

Recovery

Replication / Clustering

Snapshots

Security

C# Integration

Facilities

Full Text Search

Snapshots

Kooboo CMS First Impressions

Kooboo is a CMS based on ASP.NET MVC. Recently, I got a chance to take it for a spin, and here are some of my thoughts. Keep in mind that the drawbacks here may come just from my ignorance of the tool use. 🙂 I’ll update this as the more I learn about the inner workings.

Benefits

  • Admin Panel’s UI is intuitive for designers/programmers
    • Easy to add pages
    • Easy to add your own themes/styles
    • Easy to create your own type of content
    • Easy to add content
  • Lots of Features, more than Orchard
  • Mature, has been around for a while (2008)
  • Views are coded in Razor
  • Can connect to MongoDB and other datasource types
  • Versioning of any piece of content and view differences
  • Manage website resources easily – images/documents/etc.

Drawbacks

May not be a drawback once I figure out the “how” and get better understanding.

  • Once a site is created, when I migrated from XML to MongoDB, I lost all the website data from the XML files.
  • Admin Panel’s UI may not be intuitive to non-designers/programmers.
  • Site directory structure Kooboo generates is not the same as the traditional ASP.NET MVC.

Let’s take a look. For a site I created using Kooboo, named “batman”:

  • When a content type is created, it does not create a C# class file. (I didn’t see one at least, in the directory structure.) It does, however, create a MongoDB collection for the content, there’s just no C# class mapped to it.
  • There’s no clear way to bind a View to a model class as in traditional ASP.NET MVC since, Kooboo doesn’t create a C# class file. It doesn’t follow the traditional file/folder naming convention: for each View, you map that to a model.
  • Community not as large as other CMS communities (Orchard, Umbraco, DNN).

I’ll keep exploring, but this is what I’ve found so far.