January 7, 2010

Twitter #sqlhelp WORKS!

Posted in SQL Server at 2:58 pm by David Taylor

I had a problem, in which our corporate data folks wanted a table of ours to have certain criteria. We sell marine electronics, and some of it is serialized, some not. There is a program in our packing department that records in our SQL Server the products that get packed, and what type of boxes they’re packed in. Corporate is working on a project to take the information in that table, and use it to do automatic Completions in our JDE One World ERP.

So, I am the SQL DBA/Developer at our location, and I was told Monday that we had duplicate Serial Numbers in our data, which were found during dts package testing. Now, our unserialized products get things written into the serial number column such as ‘4 Pack with NO SN’ while our serialized products get an 11 digit serial number. Corporate has decided that their DTS package will ignore the ‘4 Pack with NO SN’, bu the serial numbers cannot be duplicated.

I fought with this thing. I wrote a function, which I attached to a CHECK Constraint, and that didn’t work at all. I spent a couple hours today trying to get it to work, and finally at lunch time I put out a #sqlhelp call on Twitter. I described my problem as best I could, even posted my function, 140 characters at a time. I was stressed, I missed a conference call in the middle of it, but you know what, people absolutely rallied to help me!

The first response was from @sqlrunner, who, in the end solved the problem, but while we were talking it out, I got input, help and even calming words from @markvsql, (who had a great line that went, “with enough chimpanzees, anything is possible. :)” at which point I knew I was in good company ), @mrdenny, who introduced me to the concept of unique filtered indexes, which, while it really didn’t apply to the problem at hand, will be tucked away in my SQL toolbox for future use, @MladenPrajdic, who told me the database design was wrong, which I agree with (not my table, can’t help it , the C# people made it!), and also calmed me down when he saw I was falling apart with the stress of trying to keep up with several things at once. There were even people who actually wrote and/or posted blog posts *WHILE I WAS WORKING ON IT!* @onpnt pointed me to his http://www.sonetring.net/fileshare/trigg.txt, @bluedog67 pointed me to http://www.bluedog67.com/blog/post/Simple-Indexed-View-To-Constrain-Data.aspx, @markvsql gave me his http://markvsql.com/index.php/2009/05/enforcing-uniqueness-on-a-nullable-column/, and @billinkc wrote and posted http://billfellows.blogspot.com/2010/01/instead-of.html. Wild!

In the middle of it all, I emailed my stuff to @sqlrunner, who actually came back with a trigger that solved my problem. Can’t thank Josef enough. 13 emails back and forth to refine and define the problem then the solution, and just like that it was done. He told me, he has 16 yrs doing it, no problem. I think, take those years, and those of everyone else that helped, probably close to a century of experience in SQL all out there, willing to help with nothing in return but  heartfelt thanks. The Twitter SQL Community is the BEST!

The solution? Like so many things, once presented, it becomes self-evident. It’s the mark of greatness that one can pull it out of their head, though, and not have to ask for it.

The table looks like thus:

CREATE TABLE [dbo].[Productiontest](
      [WO] [nvarchar](50) NOT NULL,
      [Assy] [nvarchar](50) NOT NULL,
      [BoxNum] [nvarchar](50) NOT NULL,
      [SerialNum] [nvarchar](50) NOT NULL,
      [Date] [datetime] NOT NULL,
      [HQ] [bit] NULL,
      [ID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_Productiontest] PRIMARY KEY CLUSTERED
(
      [ID] 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

Some of the data like thus:

843726, 407420-1M, 45Pack100107133354, 91202030199, 2010-01-07 13:33:57.000, (null), 63635
843726, 406540-1, 45Pack90817134501, 4 Pack with NO SN, 2010-01-07 13: 45:16.000, (null), 63639

The solution, in all it’s glory, looks like thus:

ALTER TRIGGER [dbo].[DisallowDupeNum] on [dbo].[Productiontest]
AFTER INSERT
as
begin
SET NOCOUNT ON
   if exists (select 1 from inserted where serialnum like ‘%Pack with NO SN’ and isnumeric(serialnum)=0)
   return
      if not exists (select 1
                              from inserted
                              where exists (select 1
                                                      from production3
                                                 where inserted.serialnum=production3.serialnum))
      return
      else
        rollback
end 

Thanks, everybody! Especially @sqlrunner

who posts at SSIS – SQL Server Tidbits!

Advertisements

5 Comments »

  1. […] So where is the SQL Community? How do I find it and jump in? Truly, it is all around us. If you look around your area you will probably find SQL Server User Groups formed and meeting regularly. That is the first step. Get face to face with other SQL people. Wait, did we just make a community together? Other places I recommend and find myself monitoring through the day are listed below. Special mention should be noted for the still very young Twitter hash tag, #sqlhelp. If you throw a tweet up on Twitter with the tag #sqlhelp, you will find several replies coming back your way quickly for assistance with SQL Server. Check out a great example of this in action by David Taylor (blog | Twitter), “Twitter #sqlhelp WORKS!” […]

  2. […] because I remembered it being asked about as a #sqlhelp question on Twitter! (shows once again that #sqlhelp works!) In the end, I passed, and I am now a Microsoft Certified Technology Specialist in SQL Server 2008 […]

  3. Social comments and analytics for this post…

    This post was mentioned on Twitter by dyfhid: Twitter #sqlhelp WORKS!: http://wp.me/pL8sS-j

  4. markvsql said,

    That is a very cool solution. And the edition of SQL Server you are running is not a constraint. Sweet.

    • David Taylor said,

      Thanks to @sqlrunner, and everyone else who helped, yes, this is a cool solution. I seem to recall you put forth a solution as well, my friend!

      There’s even conversations carried on after the fact on Twitter with @PaulRandall about the Transaction Rollback part of it. This community is absolutely something else!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: