RSS feed for InstantSpot site evBlog

Microsoft announces SQL Server 2008

Fri, 08 Jun 2007 13:38:05 GMT

Wait a minute... I'm still using SQL Server 2000!!!!

Looks like some cool stuff coming, including a new "merge" statement. Kinda like and update and insert statement all in one. Check out some details over at SQLTeam.com.

http://www.sqlteam.com/item.asp?ItemID=26945

Good Day!
Ryan

Removing Duplicates

Tue, 24 Apr 2007 17:19:14 GMT

Every once in a while I'll load a temporary table up with data before I load it into a production table so I can do some manipulation of the data if needed. Today I was in the process of loading data into the production table when I noticed there were some duplicate entries in the data I was loading. In fact there were many duplicates. I did some searching and I found this code, it removes the duplicate records, leaving just one entry.

[codeShare aprfbc90]

The code code was found at SQL Server Magazine's web site at the address below.

http://www.sqlmag.com/Article/ArticleID/21704/sql_server_21704.html

Hope this helps, I know I'll use it again.

Good Day,
Ryan

NULLS: SQL Server 2000 vs SQL Server 2005

Tue, 24 Apr 2007 11:22:15 GMT

The following information came from the SSWUG.ORG newsletter I get daily. I thought it was interesting and thought I'd share it.

An Interesting Difference: SQL Server 2000 to 2005
We've run into an oddity, or at least something to be aware of, when moving to SQL Server 2005 from 2000. If you have a column that is a varchar() that allows NULLs, and you're using ADO to access your SQL Server, there is at least one case where you can get different results back. The case is specifically where you're looking to get the len(gth) of the string returned from SQL Server. With SQL Server 2000, we were seeing a length of zero ("0") returned for a NULL value. With 2005, we're getting back NULL. It tripped up some routines on the site and I thought I'd pass it along. Makes sense, but odd that it gets different treatment through ADO (directly executed SELECT statements return NULL for len(gth) as expected). We now check both length and NULL return values.

For more on SSWUG visit their web site here.

Good Day!
Ryan

MS SQL RegExpression Matching

Mon, 09 Apr 2007 11:19:44 GMT

Good morning all, last week I was tasked with a new project at work. This project entailed taking string of characters and matching them up with an appropriate systems. The string of characters were account numbers and the systems were billing systems. See here at the company I work for we have hundreds if not thousands of different looking account numbers. Here are an example of just a few.

0652849190293
517M394586
313E880350350
178668
IIRSLUXW
NV300083
205SWTA280100A2800000
1788555400001
AL0000000000011999716

Now the reason we have so many different type of account numbers is beside the point. Things like this just happen when you work in an industry that is ripe for spin-offs and consolidations. However what this post is about is Regular Expressions!!! Not just regular expressions in CF, but regular Expressions on MS SQL server. A quick Google search for “MS Regular Expressions” led me to “The Code Project” site and to this article and download. In the article is a zip file containing four extended stored procedures to be loaded in the master DB of your MS SQL server. These four procedures; xp_regex_match, xp_regex_format, xp_regex_split and xp_regex_replace are very easy to load and use. In no time I had downloaded the file and created the procedures on my server for use.

So with these procedures in place I wrote a simple form in ColdFusion that allows a user to input a single value for an account number. ColdFusion then calls the procedure below which loops over a series of Regular Expression statements stored in a table. If the account number matched that result is saved in a list then the details are sent back to the user.

[codeShare apr1376e]

Pretty cool stuff if you ask me. And there really is no better way to learn more about Regular Expressions than to have to write 177 different expressions to match possible account numbers. And I've only begun to scratch the surface.

Good Day!
Ryan

MS SQL SP_HELP

Thu, 22 Feb 2007 19:31:08 GMT

Todd over at <cfsilence > blogged today about SP_HELP. I did not know this stored proc existed but apparently you can run it on any sort of object in your database and it will provide information on that object.

For example I ran:

EXEC sp_help TEAccount

After executing this SP on my table in query analyzer I got 7 result sets returned. The record sets tell you everything about the table, when it was created and by whom. It lists all of the columns in the table, any identity fields, as well as any index information. Give it a shot!

Thanks for the post Todd , keep on keepin on!

Good Day!
Ryan

SQL Joins

Mon, 04 Dec 2006 21:35:09 GMT

A post on <cfsilence> today got me think of joins and the right vs wrong way to do things.

When doing an SQL inner join is it best to have something like

SELECT    a.someThing1,a.someThing2
              b.someThing1,b.someThing2
FROM      someTable1 a INNER JOIN
              someTable2 b ON a.someKey1 = b.someKey1

or something like this

SELECT    a.someThing1,a.someThing2
              b.someThing1,b.someThing2
FROM      someTable1 a,
              someTable2 b
WHERE    a.someKey1 = b.someKey1

Notice the join is in the from statment in first example vs the where statement in the second example. Is there a difference betweent the two? Is one better than the other? How do you do it? How do you roll?

Good Day!
Ryan

Open Query vs "Linked" Query, what is the difference?

Wed, 08 Nov 2006 16:20:33 GMT

Hey everyone, I ran in to an interesting issue today trying to query an Oracle database I have set up as a linked server from my MS SQL box. The issue is the amount of time it takes a "linked" query to run vs. and Open Query. I call it a "linked" query becuase I don't know the actual name of the type of query. But I know the other is called an open query. The same query took 25 seconds when ran as linked vs 2 second when ran as an open query. That is a 92% difference, huge!!

So my question to you all is why? Where I can't share the table and column names with you I can share the general format of the queries below.

"Linked" Query: 25 seconds

SELECT DISTINCT
               T4.Column1,
               T4.Column2
FROM     LinkedServerName..Owner.Table1 T1,
                LinkedServerName..Owner.Table2 T2,
                LinkedServerName..Owner.Table3 T3,
                LinkedServerName..Owner.Table4 T4
WHERE         T1.Column = T2.Column AND
                    T2.Column = T3.Column AND
                    T3.Column = T4.Column AND
                    T1.Column = 100002800 AND
                    T4.Column = 'L' AND
                    (T4.Column is NULL OR T4.Column > getDate())

Open Query: 2 seconds

SELECT     A.Column1,
                    A.Column2
FROM         OPENQUERY (
                                            LinkedServerName,
                                            '
                                            SELECT DISTINCT
                                                            T4.Column1 AS Column1,
                                                            T4.Column2 AS Column2
                                            FROM     Owner.Table1 T1,
                                                            Owner.Table2 T2,
                                                            Owner.Table3 T3,
                                                            Owner.Table4 T4
                                            WHERE     T1.Column = T2.Column AND
                                                                T2.Column = T3.Column AND
                                                                T2.Column = T4.Column AND
                                                                T1.Column = 100002800 AND
                                                                T4.Column = ''L'' AND
                                                (T4.Column is NULL OR T4.Column > Current_Date)
                                            '
                                            )A

Ok, so they are basically the same query, just different ways of calling the same data. Any MSSQL DBAs out there that can shed some light on this issue?

Good Day!
Ryan

Sending E-Mail w/ MS SQL Server

Tue, 24 Oct 2006 19:25:10 GMT

In my quest for something to write about I came across and old post I had on an internal company blog site I wrote a few months ago. I can't get in trouble for copying of myself right?

At any rate, sending email using MS SQL server is pretty easy. Note, in order you send e-mail via MS SQL Server you must have Outlook loaded on the machine. View some not so easy to understand directions here.

If your MS SQL box was set up correctly chances are things are already good to go. The emails will be sent using the built in SQLDBA account. To test it out copy and paste the code below.

Simple example, just sending a test to myself
EXEC master.dbo.xp_sendmail
@recipients= 'someaddress@somedomain.com',
@subject= 'test',
@message= 'test'

More complex example, send email with attached results
EXEC master.dbo.xp_sendmail
@recipients = 'someaddress@somedomain.com',
@query = 'SELECT * FROM Sometable',
@subject = 'Some Report', @message = 'This is some report emailed from SQL',@attach_results = 'TRUE', @width = 250

Good Day!
Ryan

MS SQL Date Format

Tue, 10 Oct 2006 14:26:22 GMT

Here is a handy list of MS SQL date format functions! These are handy when trying to compare the DATE portion of a DATETIME field and the TIME portion is not important.

Copy and paste the code below into Query Analyzer and run it.

CODE:
PRINT '1) HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>' + CONVERT(CHAR(19),GETDATE())

PRINT '2) HERE IS MM-DD-YY FORMAT ==>' + CONVERT(CHAR( 8 ),GETDATE(),10)

PRINT '3) HERE IS MM-DD-YYYY FORMAT ==>' + CONVERT(CHAR(10),GETDATE(),110)

PRINT '4) HERE IS DD MON YYYY FORMAT ==>' +CONVERT(CHAR(11),GETDATE(),106)

PRINT '5) HERE IS DD MON YY FORMAT ==>' + CONVERT(CHAR(9),GETDATE(),6)

PRINT '6) HERE IS DD MON YYYY HH:MM:SS:MMM(24H) FORMAT ==>' + CONVERT(CHAR(24),GETDATE(),113)

Good Day!
Rya

EDIT 11-10-2006:
Code developed by Gregory A. Larsen and found here .

Challenge Accepted!

Thu, 28 Sep 2006 14:09:05 GMT

I’ve been called a lot of mean things in my life some to my face and surely more behind my back. However the other day I got called a name by a guy I don’t even know! This guy, Todd, if that is his real name, called me a lurker!!! A lurker??!! How can you call me a lurker if you don’t even know me Todd??

Well Todd didn’t just call me a lurker he called A LOT of ColdFusion developers lurkers or takers. We can define lurkers or takers in this case as developers that take code from blogs or other community sites but don’t give back to the community. For whatever reason, either they are too busy or too lazy they just don’t give back. Therefor Todd proposed a challenge. The challenge was for us all to get more involved in the ColdFusion Community, write a blog, join a CFUG or mailing list. Do something to give back.

Well Todd, challenge accepted! I’m going to try my best to make time to give back. What I say here on my InstantSpot may seem silly to those more experienced than I, so for that I apologize and welcome all feedback.

If you are new to the ColdFusion or Blog thing, here are some of the hot spots I visit often,.

Dave Shuck
Aaron J Lynch
Matt Woodward
Todd Sharp
Robert Froehling
Ray Camden
Mark Drew
ColdFusion Weely
ajaxCFC Yahoo! Group

Good Day!
Ryan