DallasDBAs.com

Explaining SQL Server in plain english

Header Right

TSQL

T-SQL Tuesday: Brick walls

by 1 Comment

T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t). This month’s edition is hosted by Wayne Sheffield (b|t) who has asked us to tell a story about a time we ran into a Brick Wall.

So many walls, so few Bulldozers…

The largest brick walls I’ve ever run up against were not technical issues as you might expect…but people and processes.

Processes:

I was working for a company that bought a company that bought the company I had hired on with.  The newest owner was growing their managed hosting business by buying others out.   Not a bad approach when you have deep pockets.  BUT, they never integrated any of them.  Everything was still in its original silo.  So, anytime I got a call or a ticket, I had to search through 4-5 knowledge base/Sharepoints, 2-3 password repositories and 4 ticketing systems.  I could easily spend 2 hours to research and close a ticket that took 3 minutes to resolve on the technical side.

This place broke my flipper.  As in, I no longer gave a flip after it took a full year to get all the access I needed to every environment.  The Bulldozer here is that I got laid off.  They gave me a big bag of money to leave and go do cool things with modern SQL versions for other places.  YES!!

People:

I was asked to bill a crazy high rate for a full-time contract, to watch over a very small environment of 25 servers.  I was asked to put in best practices, be the unofficial team lead, make things go faster, secure them, etc.

BUT…JimBob the Manager (clearly not his real name) gave me the brick wall at every turn. It took 6 weeks to get rid of NetBackup in favor of SQL Server Maintenance Plans. (I know…lets not go there.).  Compressed backups?  Made me prove it.   Index maintenance?  OK, but he wouldn’t let me schedule it.  New indexes that actually made sense?  No chance, as that was a code change to the application our Very Big Vendor had written for us.  Within 3 months, I was down to 15 minutes of real work a day, and the rest spent blogging, Tweeting and answering questions on DBA.StackExchange.

The Bulldozer on this one was me walking out the door into cooler things.  33% pay cut was worth it, to save my sanity and go independent again!

Technology:

I don’t run into technical Brick Walls, because I know how to Tweet using #sqlhelp, as well as read blogs from people I trust in the #sqlCommunity, and Vendor docs.

Thanks for reading!

Kevin3NF


TIL: Joining across databases with different collations

by 3 Comments

Quick post today…

Yesterday I was approached by a developer with something it has never occurred to me to try in 18 years of being a DBA.

“Can I write a query across two databases with different collations?”

My first thought took me back to SQL 7, when you had to reinstall SQL Server to change collation settings (IIRC).   My second and immediate thought was “Quit being so old and look it up, Kevin.”

So I bounced over to The Google, then to an answer on StackExchange that I can’t find right now (of course, but I did upvote it yesterday) and found what I needed.

I have two test databases on my local SQL 2014 Dev instance, both with an Orders and OrderDetails table in them with no data.  Both were the default collation so I changed one of them to CS instead of CI:

ALTER DATABASE [test2] COLLATE SQL_Latin1_General_CP1_CS_AS

I entered a test order in each DB manually, and in the “Note” column I changed the case of the word Five.   Why Five?  No idea…its just text, and that’s what my developer (Jared) was having to join on.

Select * from Test.dbo.orderdetails where note = 'five'
 
Select * from test2.dbo.OrderDetails where Note = 'FIve'

Results:


OrderDetailID OrderID ItemQty Note
------------- ----------- ----------- ---------
1 1 5 Five

(1 row(s) affected)

OrderDetailID OrderID ItemQty Note
------------- ----------- ----------- ---------
1 2 5 FIve

(1 row(s) affected)

Next up, write the Select statement the normal way to duplicate the error Jared was getting:

--fails
Select *
from Test.dbo.orderdetails A join test2.dbo.OrderDetails B
 on A.[note] = B.[Note]

Result:

Msg 468, Level 16, State 9, Line 11
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CS_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

 

So…now to bring it in the correct way (and there is probably more than one correct syntax for this…I didn’t look):


--works
Select *
From Test.dbo.orderdetails A
Join test2.dbo.OrderDetails B
on A.[note] = 
   Cast(B.[Note] as varchar(50)) COLLATE SQL_Latin1_General_CP1_CI_AS

Result:


OrderDetailID OrderID ItemQty Note OrderDetailID OrderID ItemQty Note
--------------------------------------------------------------------- 
1 1 5 Five 1 2 5 FIve

(1 row(s) affected)

 

All I did here was tell the query to treat the Test2 Note field as Case Insensitive for this one query.  Nothing changed in the source data itself, or the database settings/DDL.

It took me five times longer to write this up than to test it, since I already had some generic test databases ready to go.   Same ones I use for testing my answers on MSDN, SSC and DBA Stack questions.

If this helped you, please share, like, re-post, etc. as you see fit.   Or a thumbs up in a comment if you like.

Thanks for reading!

Kevin3NF

(stalk me on Twitter)