For the last few months I’ve had the pleasure of working with NDepend version 3. Most of my development at home is on linux these days, so I haven’t used it as much as I’d like, but I have been using it to poke around in various codebases and see what the new Visual Studio integration is all about. The last version integrated with Visual Studio, technically speaking, but it didn’t seem nearly as thorough as what I’ve seen in version 3.

I wanted to run a query from SQL Express against Excel, and I found this: SELECT * FROM OPENROWSET('Microsoft.Ace.OLEDB.14.0', 'Excel 8.0;DATABASE=c:docstestdata.xls', 'Select * from [Sheet1$]') Which was just what I wanted, a way to select data from Excel. But it did not work. Msg 7403, Level 16, State 1, Line 1 The OLE DB provider “Microsoft.Ace.OLEDB.14.0” has not been registered. It appears that Microsoft.Ace.OLEDB.14.0 is registered as Microsoft.

Generally when we are defining tables, the more specific the column definition the better. Yesterday, however, I ran into a case where better definition actually has increased storage use for no appreciable benefit. Integers – Using Numeric vs Int As I was working on the database I came across a curious sight, multiple columns defined as numeric(7,0), numeric(9,0), and so on. It seemed like someone was trying to provide the database with the most specific definition possible for a number of different pieces of data.

One of the best ways to improve your skills is by helping other people in forums and newsgroups. I was doing just that tonight and I stumbled on this piece of code here: http://stackoverflow.com/questions/3622685/transfer-column-data-from-one-database-to-another update [DB1].[dbo].[Table1] set [DB1].[dbo].[Table1].[Column1] = [DB2].[dbo].[Table1].[Column1] from [DB1].[dbo].[Table1] db1Alias, [DB2].[dbo].[Table1] db2Alias where db1Alias.TeamId = db2Alias.TeamId and db1Alias.IndividualId = db2Alias.IndividualId Can you tell what is wrong with the code? If you try to run that you will get the following error

For the duration of the Edinburgh Festival Fringe I’m responsible for a couple of Samsung MagicNet plasma screens which run 24⁄7 in the windows outside a venue. They’re essentially 32″ screens with a built in PC running Windows XP and a VNC server. They’re pretty flexible as you can set them to open a webpage fullscreen then display just about anything. Unfortunately, they’re prone to faults and I have no real way of knowing when one occurs until I attempt to VNC into (or walk passed) one of them which brings me to the purpose of this post: How to ensure your remote VNC sessions are doing what they’re supposed to.

With this blog post I am hoping to start a new series of blogs devoted to the interesting T-SQL problems I encounter in forums during the week. The idea of this blog series came to me on Wednesday night when I thought I solved a complex problem… First Problem – Divide data into 15 min. time intervals The first problem, I’d like to discuss, is found in this MSDN thread:

Using SQL server to generate automatic emails, I’ve noticed from time to time that the emails will miscellaneously contain exclamations throughout the mail. Here’s an example to replicate the situation. We’ll generate a table with multiple rows of the alphabet and email the contents: declare @t table (letters varchar(100)) declare @counter int declare @string varchar(8000) set @counter = 0 set @string = '' insert into @t select 'abcdefghijklmnopqrstuvwxyz' while (@counter < 128) begin insert into @t select letters from @t set @counter = @@rowcount end select @string = @string + letters + '<br>' from @t select @string declare @mailObj int declare @hr int exec @hr = sp_OACreate 'CDO.

Today on the lazy DBA we’re going to talk about why Gandalf couldn’t open the door to the mines of Moria. OK, we’re not going to talk about Gandalf or even Trolls. Although some of us might work with a few Trolls. We are going to talk about Wizards without pointy hats. Scenario A great way to discuss any topic is to put it directly in front of us with a real life situation.

Someone posted a question, they wanted to return the quarters and years within a range that were passed in. The problem they had is that they stored this data in a year and a quarter column. The table looked like this CREATE TABLE Periods(PeriodQuarter INT,PeriodYear INT) INSERT Periods VALUES (1,2009) INSERT Periods VALUES (2,2009) INSERT Periods VALUES (3,2009) INSERT Periods VALUES (4,2009) INSERT Periods VALUES (1,2010) INSERT Periods VALUES (2,2010) INSERT Periods VALUES (3,2010) GO CREATE CLUSTERED INDEX ix_Periods on Periods(PeriodYear,PeriodQuarter) GO When we do this simple select query

Over the years I’ve had the chance to help a lot of people when it came to SQL Server. One of the things I have enjoyed the most in that time was helping with HA and DR topics. In my opinion, a truly recognized accomplishment for any DBA and Systems Team is to successfully secure the companies data and systems. The security I’m referring to isn’t to deny or grant a user access to data, but the security of data from disasters.