SQL Azure Connection Error with SSIS, SSAS

Today, I was playing around with making a small cube out of the data in the sys.bandwidth_usage table in SQL Azure, and I got an error message which reads:

"Test Connection failed because of an error in initializing provider. Server Name cannot be deteremined. It must appear as the first segment of the server's dns name (servername.database.windows.net.) Some libraries do not send the server name, in which case the server name must be included as part of the user name (username@servername). In addition, if both formats are used, the server names must match."



I solved this the way the error says to solve it, by adding what was in the server name field after the user name in the user name field. I also noticed that it can be solved by switching to the native .NET provider, and not using the server name in the user name. I played with the data source in SSIS, too, and it works the same way. Interestingly, SSRS chooses a native .NET SQL provider by default, and therefore doesn't have this problem.

SQL Azure: AOTW = As Of This Writing

I'm using a new acronym now. Because SQL Azure is changing so much, and I find myself saying and thinking "As Of This Writing, I'm just shortening it to AOTW.

SQL Azure Deprecating sysLogins

There is no sysLogins in SQL Azure. To find out a list of your SQL login accounts use:

select * from sys.sql_logins

The sys.sql_logins dmv doesn't work either. I assume both of these have been taken out because there are no active directory users AOTW.

Best Agile Tools - By Blog Favorite - Woody Zuill

Here we go again. I asked Woody what his favorite agile tools/blogs are. This was his very well thought out answer (the bolding is my own):

"I don't pay much attention to the Agile blogs - there is a LOT OF NOISE out there with people giving advice about stuff they have insufficient (or no) experience to give advice about, and probably very few people available for taking that advice anyway. Many of the "blogs" are merely lame marketing efforts. The most solid of the bloggers are worth reading - but I don't typically follow them, but I'm more likely to click a link to an article if it is from a blogger who is someone I respect.

Tools for managing an Agile effort are another matter.

My one general rule about Agile Tools is DON'T (if the tool is in the form of a computer application). There are better alternatives. (I am not talking about refactoring tools, testing tools, etc. here - but the management tools, the communication tools, things like that).

Agile is best done manually, and the computerized management tools will in most cases be counter-productive IMHO. (Or IMNSHO - which is In My Not So Humble Opinion).

In a nutshell: You learn Agile by study and practice and doing and talking about the doing and so on. Tools often block learning by forcing you to follow what someone else decided is useful. Tools needlessly complicate the simplicity of Agile. Simplify, simplify.

Like the Agile Manifesto says: We value Individuals and Interactions over Processes and Tools.

I take that to at least partly mean that we get a lot more value out of focusing on empowering Individuals and improving our ability to interact well as individuals than on following some Process or using some Tool.

I have only one rock solid, immutable rule, however (that I reserve the right to change anytime): Continuously Inspect and Adapt - continuous improvement of our thinking, our understanding, how we learn, how we get along, our principles, our practices, our processes, our tools, etc.

So, if I found a tool that looks like it would bring me value, I would certainly investigate it, try it, and use it if it proves to be userful. And I would continually evaluate its usefulness. But typically - I follow the rule of DON'T when it comes to computerized Agile management tools.

Right now, my favorite tools continue to be physical ones - white boards and erasable markers, sticky notes (small) and sticky notes (huge), 3 x 5 index cards and Sharpies, face-to-face interactions and information radiators, blank flip charts and a digital camera, eye of newt and toe of frog (to paraphrase Shakespeare)."

TechEd Berlin 2010 - Link to Videos on SQL Azure and SQL Server

I didn't get a chance to go to TechEd Berlin, but I've been watching the videos online. The user interface to the website is not super great. I shouldn't complain too much. I'm super grateful that they posted all the videos from that week online so quickly. By comparison, it will take two weeks to get the SQL Pass 2010 videos online.

Here's where the SQL Azure videos start (page 19 of the video list):
http://www.msteched.com/2010/Europe/Page19/

SQL Azure Reporting videos are on page 20:
http://www.msteched.com/2010/Europe/Page20/

Here is where the SQL videos start (page 18):
http://www.msteched.com/2010/Europe/Page18/

SQL Videos for BI/PowerPivot etc (page 21):
http://www.msteched.com/2010/Europe/Page21/

There were a few other presentations that I thought would be interesting to the SQL developer/DBA:
Introduction to PowerShell
http://www.msteched.com/2010/Europe/WSV301

SSRS 2008R2 Integration with SharePoint 2010
http://www.msteched.com/2010/Europe/OFS330

Creating Self-Service Analytic BI Applications with SharePoint 2010
http://www.msteched.com/2010/Europe/OFS206

Code First Development with Entity Framework
http://www.msteched.com/2010/Europe/DEV212

Building Business Applications with Visual Studio LightSwitch (which seems to be the MS Access front end for SQL Server....it's super cool.)
http://www.msteched.com/2010/Europe/DEV206

The Future of C#
http://www.msteched.com/2010/Europe/DEV301

Phew, that ought to keep us busy for a long time. Have fun and happy watching!

Finding The Difference Between Two Dates that are in Seperate Rows in T-SQL

This problem is often solved with a cursor, but is easily re-written using a CTE. In the below sample, we are using data from a university who's trying to figure out the time between when a course ended and when the next course began. So the data looks something like this:
learnerID StartDate CompletedDate
0000131801 2010-07-01 00:00:00.000 2010-09-23 00:00:00.000
0000131801 2010-10-01 00:00:00.000 2010-12-24 00:00:00.000
0000212970 2009-11-01 00:00:00.000 2010-01-24 00:00:00.000
0000212970 2010-02-01 00:00:00.000 2010-04-26 00:00:00.000
0000212970 2010-03-01 00:00:00.000 2010-05-24 00:00:00.000
0000212970 2010-06-01 00:00:00.000 2010-08-24 00:00:00.000
0000212970 2010-07-01 00:00:00.000 2010-09-23 00:00:00.000
0000212970 2010-10-01 00:00:00.000 2010-12-24 00:00:00.000

and the query that will give them the average days between a course ending and the next course starting looks like this:

with lc
as
(
select row_number() over (order by learner_id, start_date, learner_course_id) as rownum
, learner_course_id
, learner_id
, start_date
, coalesce(completed_date, end_date) as CompletedDate
from learner_courses
)
select datediff(d, lc1.completeddate, lc2.start_date) as daysbetweenstarts, *
from lc lc1
join lc lc2
on lc1.learner_id = lc2.learner_id
and lc2.rownum = (lc1.rownum + 1)
where datediff(d, lc1.completeddate, lc2.start_date) > -1

Best Books on TDD, Agile, and SCRUM by Woody Zuill

My friend, Woody Zuill, is a developer and manager that I respect an awful lot. Recently he was asked to recommend great books on agile software development. Since Woody is always reading, he seemed like a great guy to ask. His response was thoughtful and interesting, so I thought I'd post it below:

"The books I've found useful all have holes in them. You have to get a fair amount of bad to get a small amount of good.

- For me, the Lean books by the Poppendiecks were probably the most good with the least bad.

- Kent Beck's XP book - both editions. I think you need both to get it all. These books have been very useful to me. A lot more real stuff and real thinking than most of the "How To Agile" books.

- Kent Beck's TDD book

- The second edition of Alistair Cockburn's book - Agile Software Development: The Cooperative Game. I think the first edition missed the boat in a lot of things. I think he must have learned a lot by the time the second edition came out. I don't know how experienced he actually is, but I like the nature of his book. Note: His book on How to Survive an OOP project was one of the worst books I have seen.

- Of course, Michael Feather's Legacy Code book is great. I recommend that for all developers.

- And Bob Martin's Clean Code and Agile Software Development books are must reads. The Bob Martin Agile book is overall probably the most useful book I've read (on software development) - but it is a hard read in some ways, and very difficult to sort through.

- I found Diana Larsen and Esther Derby's Agile Retrospectives book very useful to adjust my thinking about making teams work well.

I used to like Mike Cohn's books on estimating and so on - but only barely. Not so much anymore. Same with Ken Schwaber's Scrum books, in some ways I wish they were never written.

I'ver read about 20 or 30 books on the topic, and flipped through a lot of others. Most are misleading at best. Knowing what I know about things in general, I suspect a lot of this garbage is written by people who don't really know what they are talking about and aren't able to admit it to themselves. I want to believe that - otherwise the authors are being purposely misleading or deceptive and I am not ready to accept that.

There is one book that I really loved, and felt the guy was actually right on - but I searched all over the Internet tonight looking for it and couldn't find it. When I get home I'll look around and see if I can find it. [ I found it: "Sustainable Software Development: An Agile Perspective" by Kevin Tate. I must have "loaned" it to someone because I couldn't find it anywhere at home. I remember it as being very good and humble - but I read it about 5 years ago and haven't seen it since so a re-read is in order, and I reserve the right to change my mind about its usefulness. ]

Well... there have been a lot of good in a lot of those (and other) books. And for me, the main use was to help me think through things. There is a lot of garbage to sift through, though."

Great stuff, huh? Below are links to all the books he references in the email:

Kent Beck's Books

Agile Software Development: The Cooperative Game

Working Effectively with Legacy Code

Bob Martin's Books

Clean Code

Esther Derby's Agile Retrospectives

Mike Cohn's Books

And the gem:

Sustainable Software Development

T-SQL - Split Long String into Several Small Strings

This is a fantastic little function:

CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(8000))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 8000 END) AS s
FROM Pieces
)

I found it here:

Stack Overflow

and here:

SQLTeam

TimeMatters Master Audit File Error

If you are running TimeMatters in a replicated environment and you get the error:

Unable to access Master Audit File
Record Audit will not be saved.

ERROR: The insert failed. It conflicted with an identity range check constraint in database 'TimeMatters10', replicated table 'lntmuser.Audit.Current', column 'Ident'. If the identity column is automatically managed by replication, update the range as follows: f

The run the following command on the publisher and it should fix it:

exec sp_adjustpublisheridentityrange @table_name = 'audit current', @table_owner = 'lntmuser'