TL;DR: Go here for a code sample.

This is long overdue post – I’m too busy these days with contract work and other commitments.

By the nature of my work, I have to do data migrations on a regular basis. Some of the migrations from one database into another, but most of the migrations are from Excel spreadsheets into a database.
A database to database migration is somewhat nerve racking – usually the structure is broken, data is dirty, fields re-purposed and all other sort of terrible things you read about on The Daily Wtf. The best so far I’ve seen are along the lines of person middle names stored in field called CompanyName2; employee information stored in table called tbl_Jobs and info about actual jobs in table tbl_Job – see the difference here? That is all to avoid the confusion!

Database-to-database migration are unique in their own kind. But I found a lot of similar things in a process of export from Excel into SQL Server. So I’ll write down these things here – for my own future reference and you, my dear reader, might find this useful as well.

Continue reading

I’m an enemy to myself. I’m writing a huge stored procedures and send emails in a loop. And sometimes that loop is infinite.

Anyway, today I had to figure out how to deal with SQL Server mailing functionality and sp_sendmail_dbmail. Every time you send email via sp_sendmail_dbmail, your message is going through the a huge number of checks and verifications. (Check out the source of sp_sendmail_dbmail procedure, it is on msdb database, inside System Stored Procedures folder.)

After all the checks you eventually will find insert statement: INSERT sysmail_mailitems ... Now look into sysmail_mailitems table. You’ll see there a list of all the emails that have been sent, will be sent or failed. There are few views to aid your viewing experience that aggregate data from sysmail_mailitems into something useful.

Looking at emails is fun Not this bit is a bit of black magic for me, but that is how I understand it. I could be wrong, don’t take it as a reference.

  • Whenever sp_sendmail_dbmail is executed, info about this email is inserted into sysmail_mailitems table and into InternalMailQueue which is not a table, but a queue. (Note for myself, lookup queues in SQL Server).
  • That queue is used by a Broker, and whenever there are records in the queue, Broker runs DatabaseMail.exe program.
  • The queue holds some information about new mail message and a reference to the record in sysmail_mailitems. So DatabaseMail.exe pops a record from the queue, reads respective message from sysmail_mailitems and sends an email as requested.
  • Now black magic part is almost over. When I have created a 3 million message queue with emails (infinite loop, remember?), all 3 millions records were in sysmail_mailitems and in InternalMailQueue. There are helper procedures to remove emails from the pending list:

    exec msdb.dbo.sysmail_delete_mailitems_sp null, ‘unsent’

This literally removes all the records from

sysmail_mailitems with emails that have not been sent yet. (First parameter is date – delete all records older than that date. Second parameter can take ‘sent’, ‘unsent’, ‘failed’, ‘retrying’) But that procedure does not touch anything in InternalMailQueue. So after executing sysmail_delete_mailitems_sp I was left with no records in the table, but all of them were in the queue. Now, when DatabaseMail.exe runs, it will find a lot of messages in the Queue, but no records about the mail to be sent. So it will create a lot of “FAIL!” records in sysmail_log table. To clean up InternalMailQueue at the moment I’m running this query:

exec msdb.dbo.sysmail_stop_sp -- stop DatabaseMail.exe, so it does not try hard
ALTER QUEUE ExternalMailQueue WITH STATUS = ON
set nocount on
declare @Conversation_handle uniqueidentifier;
declare @message_type nvarchar(256);
declare @counter bigint;
declare @counter2 bigint;
set @counter = (select count(*) from ExternalMailQueue) -- count millions of queued messages
set @counter2=0
-- now do until nothing is left in the queue
while (@counter2<=@counter)
begin
    receive @Conversation_handle = conversation_handle, @message_type = message_type_name from ExternalMailQueue    -- pop messages from the queue
set @counter2 = @counter2 + 1
end

exec msdb.dbo.sysmail_start_sp -- start DatabaseMail.exe again

EXEC msdb.dbo.sysmail_help_queue_sp -- verify that there is nothing left in the queue

Hopefully I won’t need this article again, but can be useful for somebody facing the same problem. Most of the details were spread around the internet, but I could not find the big picture.

Reference:

  1. Using and Managing Databse Mail
  2. Forum discussion about message clearing
  3. MSDN: Troubleshooting Database Mail: General steps
  4. MSDN: Database Mail Architecture
  5. Database Mail Setup – SQL Server 2005
  6. Found this after I have wrote this: SQL Server 2008 : Sending and Receiving with Database Mail

Sometimes you need to do a report with totals column and with totals that depend on some condition, like
Product, Total Sales this year, total sales this month.
Use SUM(Case…) condition, like here:

SELECT  SUM(CASE WHEN order_date >= '01/01/09' THEN quantity ELSE 0 END) AS items_sold_since_date,
        SUM(quantity) AS items_sold_total,
        product_ID
FROM    Sales
GROUP BY product_ID

Credit goes to Joel Coehoorn

Another way to do Sum:

  COUNT(distinct case when Status.Complete = 1 then Status.ID else NULL end) as [Count Of Complete Status],

You can use temp table in the same manner as you use SQL Server cursor

create table #temp (
    value1 int,
    value2 int
)

Declare 
    @SomeValue1 int,
    @SomeValue2 int

insert into #temp (
    value1, 
    value2)
select 
    field1, 
    field2
from realTable
--where some condition

select top 1 
    @SomeValue1 = value1, 
    @SomeValue2 = value2
from #temp

-- while we have rows in the table, do work with them.
while @SomeValue1 is not null 
Begin

    -- Do something with value1 and value2


    -- delete the row we have just worked with
    delete from #temp where value1=@SomeValue1 and value2=@SomeValue2

    -- don't forget this bit!! otherwise you'd be stuck in the infinite loop, 
    -- as variable is not reset to null on empty table
    set @SomeValue1 = null  

    -- get the next row  in the table
    select top 1 @SomeValue1=value1, @SomeValue2 = value2
    from #temp

End

-- here we are done, can drop the temp table
drop table #temp

Probably you would benefit from adding @ID uniqueidentifier to temp table, in case you have repeating data, and delete rows from temp table based by the ID value

I run into a problem where I tried to connect to a fresh installation of SQL Server 2008 and could not do it for no apparent reason. But it turns out that SQL Server 2008 is shipped with TSP/IP disabled and nobody can connect to it via network, even from localhost.
Here is the solution:
http://support.microsoft.com/kb/823938

You can check in registry this key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer\SuperSocketNetLib\Tcp\Enabled
It must be set to 1 for SQL Server to be listening for network connection.

Run this on master database to see error log on the server:
exec Go Xp_readerrorlog
You should look for
SQL server listening on X.X.X.X: Y
This will be an indicator if network connections are allowed in the Server