SQLJunkies Blogs

SQL Server 2008 Transact-SQL Recipes

joe.sack — Wed, 02 Jul 2008 04:50:00 GMT

** Cross-posted on www.joesack.com **

My new book, "SQL Server 2008 Transact-SQL Recipes" is coming out in a few weeks (estimated publication at the end of July). As of July 1^st, Amazon.com is selling it for $37.70. They don’t have a picture of the cover up yet, but it will have the standard bumble-bee color scheme Apress cover, so expect no surprises there. J

Although this was an update of my last book, it still took 9 months to write (plenty of weeknights and weekends holed up in my office). I updated existing content, and added content for both SQL Server 2008 and SQL Server 2005 SP2 additions.

Like my last book, I focus specifically on the Transact-SQL language. Even with the focus on T-SQL and not the GUI, the book wound up being greater than 800 pages. Also like the last book, I use a problem/solution format. The overall theme and mission statement for this book was:

"Look up what you need to do. Learn how to do it. Do it."

I'll have a downloadable detailed index link available after the publication date. That link will include the specific recipes for each chapter. In the meantime, here is a high level list of the chapters:

CHAPTER 1 SELECT

CHAPTER 2 Perform, Capture, and Track Data Modifications

CHAPTER 3 Transactions, Locking, Blocking, and Deadlocking

CHAPTER 4 Tables

CHAPTER 5 Indexes

CHAPTER 6 Full-Text Search

CHAPTER 7 Views

CHAPTER 8 SQL Server Functions

CHAPTER 9 Conditional Processing, Control-of-Flow, and Cursors

CHAPTER 10 Stored Procedures

CHAPTER 11 User-Defined Functions and Types

CHAPTER 12 Triggers

CHAPTER 13 CLR Integration

CHAPTER 14 XML, Hierarchies, and Spatial Data

CHAPTER 15 Hints

CHAPTER 16 Error Handling

CHAPTER 17 Principals

CHAPTER 18 Securables, Permissions, and Auditing

CHAPTER 19 Encryption

CHAPTER 20 Service Broker

CHAPTER 21 Configuring and Viewing SQL Server Options

CHAPTER 22 Creating and Configuring Databases

CHAPTER 23 Database Integrity and Optimization

CHAPTER 24 Maintaining Database Objects and Object Dependencies

CHAPTER 25 Database Mirroring

CHAPTER 26 Database Snapshots

CHAPTER 27 Linked Servers and Distributed Queries

CHAPTER 28 Query Performance Tuning

CHAPTER 29 Backup and Recovery

I enjoyed writing this book; but most importantly I hope you find it to be a useful, practical reference.

Last post on SqlJunkies, now I am only on SqlBlog.com

marcorusso — Tue, 10 Jun 2008 08:34:00 GMT

In the last months I cross-posted from my blog on SqlBlog.com to SqlJunkies.com, but starting from today I will be only on http://sqlblog.com/blogs/marco_russo.

This is my last post on this blog on SqlJunkies.com. See you on SqlBlog!

What means writing a book

marcorusso — Thu, 05 Jun 2008 15:58:35 GMT

I and Paolo Pialorsi have been interviewed at TechEd by Ken Rosen. We talked about our experience as book authors of Programming LINQ.

If you are interested in writing a book, or if you simply want to see our faces and hear our italian accent, you can watch the video available in both low resolution and high resolution. Enjoy!

Cross-posted from SQLBlog! - http://www.sqlblog.com

TechEd 2008 book signing and other meetings

marcorusso — Mon, 02 Jun 2008 13:40:01 GMT

I'm already in Orlando for TechEd 2008 Developers. Tomorrow I and Paolo Pialorsi will be at the TechEd bookshop for a book signing of our just released Programming Microsoft LINQ, scheduled at 4:00PM-4:30PM. I wrote a post a few days a go with the list of chapters included in the book. LINQ to SQL and LINQ to Entities are two technologies that are significative to access data, even if you don't have to use them in every possible scenario.

If you are attending to TechEd, meet us tomorrow at the bookshop to talk about LINQ!

Cross-posted from SQLBlog! - http://www.sqlblog.com

Programming Microsoft LINQ finally shipping

marcorusso — Sun, 25 May 2008 21:30:40 GMT

Finally, the Programming Microsoft LINQ book is available. We (I and Paolo Pialorsi, the other author) updated the website that supports our books (http://programminglinq.com), where you can download the sample code of the book.

What’s in this book? Well, we tried to cover everything that was in RTM, but we also introduced technologies that are still in beta or in early CTP stages, like LINQ to Entities and Parallel LINQ. To give you an idea of the content, at the end of this post there is a list of the chapters included in the book.

Now, the next news is that I will be at TechEd Developers next week in Orlando. Feel free to contact me if you want to arrange a meeting talking about LINQ or SSAS. Moreover, I and Paolo will be at the bookshop for book signing on June 3rd from 4:00pm to 4:30pm. I hope to see you there!

In these days I'm already working on a new project, this time on Data Warehousing and Analysis Services. More news on this blog in a few weeks...

Programming Microsoft LINQ

· Part I LINQ FOUNDATIONS

o 1 LINQ Introduction

o 2 LINQ Syntax Fundamentals

o 3 LINQ to Objects

· Part II LINQ to Relational Data

o 4 LINQ to SQL: Querying Data

o 5 LINQ to SQL: Managing Data

o 6 Tools for LINQ to SQL

o 7 LINQ to DataSet

o 8 LINQ to Entities

· Part III LINQ and XML

o 9 LINQ to XML: Managing the XML Infoset

o 10 LINQ to XML: Querying Nodes

· Part IV Advanced LINQ

o 11 Inside Expression Trees

o 12 Extending LINQ

o 13 Parallel LINQ

o 14 Other LINQ Implementations

· Part V Applied LINQ

o 15 LINQ in a Multitier Solution

o 16 LINQ and ASP.NET

o 17 LINQ and WPF/Silverlight

o 18 LINQ and the Windows Communication Foundation

· Appendixes

o A ADO.NET Entity Framework

o B C# 3.0: New Language Features

o C Visual Basic 2008: New Language Features

Cross-posted from SQLBlog! - http://www.sqlblog.com

Synonyms and SQL Server Express

Roman — Sat, 03 May 2008 04:15:43 GMT

I've been a big fan of using SQL Server aliases for a long time because it allows you to make physical location of SQL Servers transparent to the client machines. With SQL Server 2005 Microsoft introduced synonyms, allowing you to define logical names for objects in another database or even on another server. This could be among other things beneficial if you need to move some tables to another database. Instead of recoding your application, you can define synonyms and point them to the new location (I wrote an article for SearchSQLServer.com about synonyms recently, you can get more details there if interested) This week I realized that synonyms can have another great benefit. As you know, SQL Server Express has a limit of 4GB per database. If your database begins to grow close to 4GB, you can move one or more large tables to another database on the same server, create synonyms in the original database and point them to the new location. I tested it on my SQL Server Express instance and it does work as expected. So with this knowledge, this limitation might become less of an obstacle for you to consider SQL Server Express.

Cross-posted from SQLBlog! - http://www.sqlblog.com

Multiple Hierarchies from SQLCAT

marcorusso — Mon, 21 Apr 2008 16:52:18 GMT

I'm still late with blog reading and sometimes I discover interesting things one or two months later than the original posting. I just read the SQLCAT analysis of the several solutions available to handle multiple parent-child hierarchies within a single dimension. The multiple hierarchies pattern described in my many-to-many paper has been used (with some variations) and now I have the "SQLCAT certification" that this model is faster than others!

I think there is space for improvement in this area and the many-to-many space is still a relatively unexplored space. If you have experience adopting those models, please share your knowledge - and if you are shy, write me directly and I'll post the interesting data.

Cross-posted from SQLBlog! - http://www.sqlblog.com

Deleted All Logins to SQL Server 2005 and Lost sa Password: You Did WHAT? And BizTalk 2006 Is Using It?

drodabaugh — Fri, 11 Apr 2008 10:46:00 GMT

It's been a while since I blogged but I had a circumstance yesterday that I thought was noteworthy. Recently my employer cut our staff to about 1/4 of what it was nine months ago, so all of us in "management" are now hands-on. I've inherited DBA work (since we cut the DBA), something of which I've not done much in the past eight years during a BI career. I have always appreciated what competent DBA's do for those of us in BI development. I'm sure I'll appreciate it more as time progresses.

As the title suggests, I committed an egregious error. I was working on a dev server and needed to reload a database which hasn't yet gone to production. I inherited the database and the application from a consultant (yup, cut him too) and it hasn't been fully loaded. As part of the work, I thought I'd build an "initial load" procedure and add it to the documentation. One of the methods for initialization is to create the database with a script, so I had SSMS write a script. I told it to script the database logins. Did I read the script before punching the go-giddy button? No. In a few seconds, the script errored out saying that I didn't have permission to create logins. Huh? Of course I do! I'm a sysadmin. A few more minutes of sleuthing revealed the disaster: I had deleted every single login in that server. Every last one, including myself. I had even whacked Builtin/administrators.

Shortly thereafter I realized we didn't have the sa password. I had completely locked myself out of my own server. What to do? I considered a full rebuild of the server, but there were 17 user-defined database on the server. Additionally, the majority of those databases were for a dev instance of BizTalk 2006, and while I'm not an experienced DBA, I worked with our DBA during initial installation of BizTalk 2006 and knew that it wasn't backed up or restored conventionally. Plus, there was a dev SharePoint installation using the SQL Server as well and I knew even less about the restore procedures for that. I was felling a little sick at that point.

A quick Web search revealed that Microsoft has left a trap door in SQL Server 2005 for this problem. You may find a simple and correct procedure for resetting a lost sa password when you cannot otherwise get access to the server, in the following link: http://blogs.msdn.com/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx. I did exactly what Raul Garcia told me to do, and it worked! Restart the server in maintenance mode and then add a user to the the sysadmin role with sp_addsrvrolemember.

Next, I ran only the portions of the offending script which created logins. But was I done? Hardly. Inspection of the script (in which I asked for descriptive headers) told me that all SQL Server logins would be recreated with a random password and automatically disabled. I only had four of those, but I had to find the application passwords and make them match, and then enable the accounts. I also noticed that the associations between those logins and their database users seemed to be incorrect. I reset those with SSMS and they seemed to be working fine.

Next, I noticed that none of my backup jobs were running. SQL Agent could not access SQL Server. I use the same domain account to start both the SQL Server service and SQL Agent, but I had wiped out the permissions of that domain account. I fixed the problem by adding a login for the service account to SQL Server and gave it sa rights. I'm sure this is not the best possible way I could have solved that problem, but I can go back and fix it later. The highest priority of any DBA is to safeguard the company's data and this was only a dev server.

Finally, the SQL Server log showed that the BizTalk accounts could not log on to SQL Server. The message shown in the log was something like this:

Login failed for user '<domain>\<domain user>'. [CLIENT: 10.1.2.17]

Error: 18456, Severity: 14, State: 16

State 16 seems to indicate that a login has no permissions in a database which it is querying. Running sp_change_users_login with the "Report" option showed nothing. All of the BizTalk users had proper associations in their databases, just as SSMS said they did. Furthermore, we got the Windows password for one of the BizTalk Windows accounts and logged in to the server. We were able to connect to all the proper databases and query tables, just the permissions said we would. In other words, BizTalk was having no real problem connecting to SQL Server and querying its databases.

So, what kept causing the problem? We noted that the errors only occurred after restarting the SQL Server service, and then only one time. A ha! Is it possible that BizTalk was trying to connect to SQL Server and access its databases before the restart recovery is complete? Yes! Had I been more observant, I would have seen this in the log file. We put this to the test by stopping the BizTalk services on the BizTalk dev server and restarting the SQL Server service. Sure enough, the errors disappeared. It was nothing more than an order of operations issue. BizTalk tries to connect very quickly when it loses its SQL Server connection. It was trying to access a database before recovery was complete.

So that's the saga. Here's what we learned:

Don't let Dave be your DBA on production servers if he can't stop destroying dev servers.
Always have current backups. We did, so I knew I could get back if the method described herein didn't work.
BizTalk is a very complicated system. You'd better practice restoring it because if you really have to do it, it could take hours just to know the process for recovery.
Always record your sa passwords and securely store them.
Read database creation scripts BEFORE you execute them. Five minutes of reading may save four hours' work.
Microsoft has provided a way to create a new admin account on SQL Server even if you are boneheaded enough to completely sever access to SQL Server.

Many-to-Many Session at European PASS Conference 2008

marcorusso — Fri, 11 Apr 2008 08:50:30 GMT

I'll be a speaker at the European PASS Conference 2008 next week in Dusseldorf. I will talk about advanced dimensional modeling using many-to-many relationship. The content is based on "The many-to-many revolution" paper I wrote two years ago. If you will attend that conference and you already used some of the models I described in the paper, I will be happy to get direct feedback from you.

My session is scheduled on Wednesday at 14:30. Unfortunately, I will not have much time after the session because I will have to go to the airport - for this reason, contact me in advance if you want, I'll be at the conference starting from Monday.

Cross-posted from SQLBlog! - http://www.sqlblog.com

Take advantage of FE caching to optimize MDX performance

mosha — Fri, 28 Mar 2008 23:48:00 GMT

I have decided to move my blog from the sqljunkies site to sqlblog site. The transition started about a year ago. During this year I was posting every blog on both sites. This transition period has ended now, and from now on all the posts will be on sqlblog only. I apologize for all the inconvenience that it causes, but sqljunkies had too many problems to deal with, and the current owners didn't seem interested in fixing them. The sqlblog owners, on the other side, were were responsive and supportive, making sqlblog much more attractive solution for the blog publishers. The latest post is Take advantage of FE caching to optimize MDX performance.

LINQ to extract object permission from SSAS

marcorusso — Fri, 28 Mar 2008 10:01:33 GMT

Yesterday one customer of mine encountered the issue described in KB933836 and posted also by Chris Sells.

The issue is that the deployment of a SSAS database always overwrite object permissions defined on database objects like cubes. The "keep existing roles" setting of deployment wizard does not preserve those information. I needed to solve the issue and I simply made a simple program that creates an XMLA file with object permissions that I need to restore after database deployment. I used LINQ and XML Literals in Visual Basic - I wrote a book about LINQ and I have to find a reason for this, but I really think that LINQ saved my time.

I will write a more complete article about this issue one day, may be... by now, if you have the same issue, you can contact me to get the code I wrote. If you don't need it, look at the readability of LINQ in a case like this.

If I will receive some request, I will consider to publish this simple tool.

Cross-posted from SQLBlog! - http://www.sqlblog.com

Thoughts about the “Current Day” problem in SSAS

tpagel — Tue, 18 Mar 2008 04:37:00 GMT

Well, I think it’s time for a new post here in my blog... Yes, I’m still alive and still working in the MS BI space at Avanade... Many things changed in the last months so this blog was not on my top priority list, but maybe this will change again... So I just want to publish some thoughts about the “Current Day” problem in SSAS... There are quite some articles about that and what I want to tell you is nothing more than bringing two posts together... Mosha wrote quite something about this task... http://sqlblog.com/blogs/mosha/archive/2007/05/23/how-to-get-the-today-s-date-in-mdx.aspx. How to use VBA to find out the current date and how to map this with your dimension... At the end he mentions performance issues with this... And that might be right... Since this is a calculation which will be run in each MDX you send to the server there is nearly nothing it can cache... Mosha’s recommendation is to use some fixed member you change daily... So what can you do? I came across this article... http://www.sqlbi.eu/Home/tabid/36/ctl/Details/mid/374/ItemID/8/Default.aspx. Here you find some code how to build up a simple application which changes your actual MDX script on your server... I didn’t have time to test it but it should be pretty straight-forward to build up a tiny tool which updates a set with the actual date in your MDX script. So you have that fixed set and your performance should get better... If someone likes to implement it and give some feedback, I’ll be more than happy to publish it...

So long and thanks for all the fish

mz1313 — Sat, 01 Mar 2008 05:16:00 GMT

This is my last post on SQLJunkies. I'm moving to sqlblog.com - welcome to my new blog. It's empty meanwhile but not for long. Since nice part of my RSS feeds subscription belongs to sqlblog.com bloggers (Kalen Delaney, Adam Machanic, Denis Gobo, Linchi Shea etc), I was more than happy to receive an offer to move there. So... see you in my new blog.

Happy Launch Day!

RickH — Wed, 27 Feb 2008 17:04:34 GMT

It's Official, today!

We have a party for SQL Server 2008 in LA! The product may not be shipping, but that won't stop us from having our party.

Seriously, Today marks the beginning of a more useful Launch than the 2005 launch. What am I saying? The Launch Events will probably be more educational for you this time around because there hasn't been as much focus on having a lot of events on 2008 as of yet. Signup today for a launch event near you!

Cross-posted from SQLBlog! - http://www.sqlblog.com

Adios SQLJunkies

marathonsqlguy — Sat, 23 Feb 2008 23:09:41 GMT

Thanks for getting me started but it's time to close the door.

Allen

Cross-posted from SQLBlog! - http://www.sqlblog.com