SQLblog.com - The SQL Server blog spot on the web

How to relate tables in DAX without using relationships

Marco Russo (SQLBI) — Tue, 09 Feb 2010 14:44:00 GMT

PowerPivot supports only one type of relationship between two tables, which is the one-to-many relationship. You can define that a column in a table (the “many” side) corresponds to a lookup table through a column which is an identity column there (the “one” side). DAX provides support to follow this relationship through functions such as RELATED and RELATEDTABLE. As I already wrote in this blog, many-to-many relationships are not directly supported by DAX and we can work-around that by writing more...(read more)

Help for SQL Server

BuckWoody — Tue, 09 Feb 2010 14:29:00 GMT

Sometimes issues pop up with your system. You need a way to find help, quickly. Here’s a few links that might be useful – feel free to post a reply to this post with other sources you might know, from web sources to your favorite consultant.

Note – this list is not exhaustive, and I’m happy to add or edit it if you post a reply. I know a lot of SQL Server professionals, and I don’t want to leave out anyone or commit them to something!

Web Help – Use these links to do a little research on your own

Main Troubleshooting Page:	http://technet.microsoft.com/en-us/sqlserver/bb895929.aspx
The main Microsoft page for learning resources is here:	http://www.microsoft.com/sqlserver/2008/en/us/learning.aspx
There are lots and lots of great community sites. Here are just a few:	http://www.sqlcommunity.com/ http://www.mvps.org/links.html#SqlServer http://www.sqlpass.org/ http://www.mssqltips.com/ http://sqlservercentral.com
serverfault.com (mind your manners in here and you’ll get some help!)	http://serverfault.com/
Post a feature request or a bug here:	https://connect.microsoft.com/site/sitehome.aspx/?siteid=68&wa=wsignin1.0

Phone Help – Use these Links to get someone to talk with

The main launching page for Microsoft Support is here – they can do most anything over the phone:

http://www.microsoft.com/microsoftservices/en/us/support.aspx

On-site Help – Use these links to get someone at your facility

Microsoft consulting services can either help you with a deep problem or help you	http://www.microsoft.com/microsoftservices/en/us/consulting.aspx
Many of the “Most Valuable Professionals” (MVP) for SQL Server either work at support and consulting firms or know people who do. They are a great resource:	http://www.mvps.org/links.html#SqlServer
Microsoft Partners (Use SQL Server as a search term and use the filter tool for services and your location):	http://www.microsoft.com/sqlserver/2008/en/us/find-partner.aspx
I use the web search of “SQL Server” and “Consulting” to locate other folks. You can add the name of your town (like “Seattle”) to narrow the range:	http://www.bing.com/search?q=SQL+Server+consulting&form=OSDSRC

SSIS Snack: Configuring an SSIS 2005 Lookup Transformation for a Left Outer Join

andyleonard — Tue, 09 Feb 2010 12:00:00 GMT

Introduction In SSIS 2005, the Lookup Transformation will fail if it does not find a matching record in the lookup table if configured with the default settings. Build the Data Flow Create a Data Flow Task and add an OLEDB Source. In the OLEDB Source Adapter, connect to AdventureWorks and use the following T-SQL statement to extract data from the AdventureWorks.Person.Contact: Select FirstName, MiddleName, LastName From Person.Contact Configuring the Lookup Drag a Lookup onto the Data Flow canvas...(read more)

Bad habits to kick : relying on undocumented behavior

AaronBertrand — Tue, 09 Feb 2010 03:01:00 GMT

In my last post in this series , I talked about the common habit of creating an IDENTITY column on every single table. Today I want to talk about a more broad concept: relying on undocumented (and therefore probably undefined, and certainly far from guaranteed) behavior and objects. ORDER BY in a view This is probably the most infamous of all SQL Server undocumented behaviors. In SQL Server 2000, users learned to create views with a built-in ordering, such as follows: CREATE VIEW dbo.MyView AS SELECT...(read more)

T-SQL Tuesday #002: The Roundup

Adam Machanic — Mon, 08 Feb 2010 19:14:00 GMT

According to T-SQL Tuesday rules as ratified by me in the first and second T-SQL Tuesday posts, the T-SQL Tuesday host is supposed to post a roundup within two days of the end of the event. So a reasonable person should expect a roundup to be posted by the second Thursday of the month. It gives me no pleasure to admit that I've been completely unreasonable and have totally dropped the ball. I'm twothree four weeks late. (I actually started the post two weeks ago. That's bad. And now I'm forced to...(read more)

Make it Easy for People to Help You

BuckWoody — Mon, 08 Feb 2010 14:17:59 GMT

OK, there are probably a dozen or more of these kinds of posts, but I’ll dive in anyway. From time to time, people send me e-mails or comments on this blog asking for help. Sometimes it’s on the topic at hand, and other times the topic just jogs their memory about something else.

Often I’m happy to help. If I know the answer without doing any research (or even if I have to do a little) I’ll interrupt what I’m doing and dash out a note with the answer. But of course I have a job (three, to be exact) and so any time I help with a question I’m lengthening my day, spending less time with my family, and so on. If you think about it, everyone that you ask (and everyone I ask) is in the same boat – when someone helps me, I am taking their most valuable asset: their time. So I learned a very important lesson very early on: Make it easy for people to help you.

So here’s the steps to follow to do that – it really isn’t that hard:

Request, don’t demand. I got an e-mail yesterday (on Sunday) where someone found a blog entry about one topic, and basically said this about another topic: “Tell me where I can find ‘x’ so that I can alter it.” Guess what I did? That’s right, I hit the delete key. If you are asking a question from a professional, you need to understand that they normally get paid – very well, sometimes – for their time. Make sure your question is a question, not a demand.
Be clear about the problem. Vague statements don’t help – and very few people have the time to dig the real question out of you. Be specific. Ask the single question you really need help with.
Keep the problem limited. “Rewrite my code for me” isn’t going to happen. “help me with this line” might. “Where do I go to find out more about the SELECT statement” is even better. If your problem takes more than a few minutes for someone to answer, then you should probably get someone on-site to help you.
Explain what you’ve already done. This, of course, means you’ve already actually done something. What have you looked up, what do you already understand, where have you looked, what have you tried?
Be polite. Please and Thank You are magic words, whether you get the answer you were looking for or not.

I’ll add one thing when you’re responding to a blog – not just mine, anyone’s. If you want to ask a question, ask it as a reply to a post, not an e-mail. The author wants to answer the question once, and it’s almost a guarantee that you’re not the only one with that question. Also, other readers might know the exact answer and help you even more. I know, you have to register, all that stuff. Just consider it the price of getting your answer.

Speaking about Indexing

AllenMWhite — Mon, 08 Feb 2010 14:05:00 GMT

I'll be presenting two sessions this week (February 9 and 11, 2010), both on SQL Server Indexing. The first is for the PASS AppDev Virtual Chapter, and will take place on Tuesday at noon EST. You can attend the meeting via this link: https://www323.livemeeting.com/cc/usergroups/join?id=8PQHN2&role=attend The second will be the same presentation at the Northeast Ohio SQL Server Users Group at the Microsoft office in Independence Ohio. You can reserve a seat here: http://www.bennettadelson.com/seat.aspx?sig=sql&ID=210...(read more)

Metaproblem: Drama

andyleonard — Mon, 08 Feb 2010 12:00:00 GMT

Introduction This post is the ninth part of a ramble-rant about the software business. The current posts in this series are: Goodwill, Negative and Positive Visions, Quests, Missions Right, Wrong, and Style Follow Me Balance, Part 1 Balance, Part 2 Definition of a Great Team The 15-Minute Meeting This post is an introduction to metaproblems and discusses a common metaproblem: drama. Problems I define a problem as an unsolved issue. It could be minor. Or it could threaten your project, llivelihood,...(read more)

Think before unchecking sysadmin rights of BUILTIN\Administrators.

Uri Dimant — Mon, 08 Feb 2010 07:58:00 GMT

Hello every body. This is my first blog on that great site so I am really exciting.

I have recently met our client who uchecked the sysadmin rights of BUILTIN\Administrators group before given any permissions to another account.

That was NOT such problem if the BUILTIN\Administrators group was removed from sysadmin role accidentally/by mistake, then you must login with another sysadmin login. If there is no other sysadmin login, you must login with SQL authentication as sa with the password that was set during setup to sa. Once logged in as a member of sysadmin, you are able to add BUILTIN\Admisnitrators back to sysadmin role.
However everything above does not work for the client. Uhhh,the client also disabled SA accoount as well as DAC connection.
Moreover, there is no domain controller where you can create a sysadmin domain acoount and grant the access to the machine running SQL Server,that was a stand alone computer with single instance installed on.

The solution we found was to start SQL Server with single user mode. As Raul said that using the single-user mode, SQL Server 2005 prevents a Windows Administrator to abuse this privilege to act on behalf of the sysadmin without being noticed. This allows Windows Administrator accounts to perform certain maintenance tasks, such as installing patches. To someone who is not familiar how to start the instance in single user mode and adding login to the server role being system administrator please read the below link describing step by step the procedure.

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

Bad habits to kick : putting an IDENTITY column on every table

AaronBertrand — Mon, 08 Feb 2010 05:33:00 GMT

Back in October, I started a series of blog posts called " Bad Habits to Kick ," and thought I would revive the theme. I've worked with developers that dabble in SQL, and they tend to have a few common traits. I'm not sure where they come from, but one that I find rather distracting is the tendency to place an IDENTITY column on every single table. Usually this is done because this is an "easy" way to add a column to the table that allows you to identify a single row. Now don't get me wrong, I am...(read more)

Injection is not always about SQL

AaronBertrand — Sun, 07 Feb 2010 18:38:00 GMT

I think anybody even remotely involved with databases these days has seen the xkcd comic about Bobby Tables : http://xkcd.com/327/ Basically, the comic warns against SQL injection, and reminds you to sanitize your database inputs rather than blindly append incoming data to queries. There are more elaborate discussions about this concept in these articles on MSDN, and of course your favorite search engine will have many results as well: Books Online : SQL Injection MSDN Mag : Stop SQL Injection Attacks...(read more)

NJ .NET User Group Presentation: Building a SQL Server Search Engine in .NET

Mike C — Sun, 07 Feb 2010 18:11:00 GMT

I'll be presenting at the Northern NJ .NET User Group meeting on Tuesday February 9. Topics we'll cover include: Intro to SQL Server 2008 Integrated Full-Text Search (iFTS) features and functionality, including: Full-text indexes Thesaurus Word breakers, filters and stemming Multilanguage support Intro to SQL Server 2008 FILESTREAM storage Troubleshooting with iFTS dynamic management views and functions Building a simple and powerful .NET-based search engine-style interface Click here for more information:...(read more)

DevWeek and PASS Europe

Davide Mauri — Sun, 07 Feb 2010 16:16:23 GMT

In the next month I’ll be speaking at two conferences, one in London, the DevWeek and another one at Neuss, the PASS European Summit.

At the DevWeek I’ll deliver a session created explicitly to give developers some insight on SQL Server development, so that when they have to deal with a database they can do the right thing just from the beginning. As a developer I learned at my expenses (looooong ago, in the around-2K golden years) that application performances are strictly related to database performance. And since a database can be (IMHO) seen as the fundaments of an application, the more it can be done correctly at the beginning, the more your application will be solid and fast.

SQL Server best practices for developers
http://www.devweek.com/sessions/conference1.asp

In Italy we created a specific course (from 2 to 4 days) upon this idea, and I can tell you that right from the start it was a big big big success, so we'll try to replicate that success in this session. I’m sure developers will love it!

After DevWeek, the month after, in April, I’ll also speak at PASS. One spotlight session will dedicated to SQL Server Development again, but in this case the focus will only be on how we approach the solution to a problem and how we write the query to solve it. I’ll show that “just” changing that approach – and thus also changing the query – we can make the query run at much higher speeds! It’s all about our mind, not the code.

Blazing Fast Queries: When Indexes Are Not Enough
http://www.sqlpass.org/summit/eu2010/Agenda/Spotlights/BlazingFastQueriesWhenIndexesAreNotEnough.aspx

The other session is specific for the BI guys, where I’ll not discuss architectures but I’ll focus engineering. Yes, I’ll try to make some order, defining some rules regardless the architecture and the methodology you want to follow, rules that will help to have a solid yet flexible (“adaptive”) solution:

Adaptive BI Best Practices
http://www.sqlpass.org/summit/eu2010/Agenda/ProgramSessions/AdaptiveBIBestPratices.aspx

So, see you there!

“sys2” scripts are growing

Davide Mauri — Sun, 07 Feb 2010 15:33:31 GMT

I’ve just released a new script, and now the sys2 scripts are reached the double-digit number: 10!

http://sys2dmvs.codeplex.com/

The latest addition is the sys2.tables_columns scripts that helps me to detect which tables uses LOB column so that I can also check if that LOB data resides on a dedicated filegroup or not. Usually when I have such situations and the LOB data is seldom accessed (say, 1 time each dozens of requests) I create a specific filegroup and use that for LOB data, so that the standard data pages will only hold a 16byte pointer to that data, helping to keep data pages dense and thus extremely efficient from a caching and (read) I/O access point of view.

Connect Digest : 2010-02-06

AaronBertrand — Sat, 06 Feb 2010 23:32:00 GMT

Upgrading a database with read-only filegroups Earlier today, I complained that I should be able to upgrade a database with read-only filegroups. In this case an upgrade from, say, 2000 to 2005 is blocked because the engine can't update the system schema / structure. Since I am trying to protect the data, not the system schema, I think it should be okay that the engine ignores the read-only flag during the upgrade process. The workaround is rather cumbersome : get exclusive access to the original...(read more)

It's Official - SQLSaturday is Coming to NYC!

Mike C — Sat, 06 Feb 2010 19:59:00 GMT

New Jersey SQL Server User Group (NJSQL) is bringing SQLSaturday #39 to NYC on April 24, 2010! The free all-day training event will be hosted by Microsoft at their Midtown Manhattan offices. The speaker line-up is growing fast—if you'd like to present, visit the event's open call for speakers . This is a free full-day training event, but registration is required to attend. Seating is limited. Registration, speaker, and sponsorship details are posted at http://www.sqlsaturday.com/39/eventhome.aspx...(read more)

MVP Book - Many Thanks!

Paul Nielsen — Fri, 05 Feb 2010 21:24:00 GMT

On behalf of the 53 MVPs who collaberated on the SQL Server MVP Deep Dives book , I’m pleased to say thank you to the many who have purchased the book. Together we have raised $12,400 for www.warchild.org . If you haven’t yet purchased the book, you should: no other tech book is such a win/win proposition. 100% of the author royalties goes helps children traumatized by war, you get 59 great chapters, and MVPs got to share their expertise in a collaborative effort. http://sqlblog.com/blogs/paul_nielsen/archive/2009/09/29/53-mvps-warchild-org-and-sqlservermvpdeepdives-com.aspx...(read more)

Configuring a MDS load-balanced web farm using IIS v7 ARR

mattande — Fri, 05 Feb 2010 17:24:00 GMT

(this post was contributed by Nick Nieslanik, Senior Software Engineer on the MDS team).

Many production environments will want to configure Master Data Services (MDS) in a load-balanced Web Farm for scalability and performance reasons. This blog entry will walk through configuring MDS to run with Internet Information Services v7.x Application Request Routing (ARR), a software load-balancer.

Preparation

To configure an MDS web farm, you'll need at least three IIS Web Servers available. Assign one of these servers the task of being the IIS ARR server. The remaining servers will serve as MDS web application servers. Please note that you’ll want a centrally located MDS database that all MDS application servers are configured to use. Once the servers are allocated, please follow the steps indicated below.

Configuration Steps

Step 1

Install ARR v2 on the allocated ARR server by following steps 1 through 3 located here. Reboot the server after finishing the installation steps in the link provided.

Step 2

Install and configure Master Data Services on the allocated application servers. Detailed setup and configuration settings are located here.

Step 3

Create an MDS server farm on the ARR server. Detailed steps for configuring an ARR server can be found here. Please ignore the Health Test section in step 2 of the ARR configuration page as we’ll cover that below.

Step 4

Once the server farm is created, the Server Affinity setting must be changed to allow Master Data Services to work correctly. To do this, perform the actions below.

Select the server farm created earlier in Step 3 and double click the Server Affinity feature.

Once in the Server Affinity panel, check the "Client affinity" checkbox if it is not already checked and choose a unique cookie name for the server farm.

Click Apply in the upper right hand corner.

Step 5

Perform a server farm health test from the ARR Server. To do this, click the MDS server farm created in Step 3 and select Health Tests from the Features view and follow steps 1 & 2 here. This link will also elaborate on the Client Affinity setting configured in Step 4. When creating the healthCheck.txt test file, please place it into the default web site root rather than the MDS web application folder.

Limitations

ASP.NET SQL Session State is not currently supported. Thus Client Affinity must be set as described in Step 4 for MDS to work correctly in a Farm scenario.

Downloads

ARR v2 x86: http://download.microsoft.com/download/4/D/F/4DFDA851-515F-474E-BA7A-5802B3C95101/ARRv2_setup_x86.EXE

ARR v2 x64: http://download.microsoft.com/download/3/4/1/3415F3F9-5698-44FE-A072-D4AF09728390/ARRv2_setup_x64.EXE

Links

ARRv2 RTW Announcement: http://forums.iis.net/t/1162464.aspx

ARRv2 Installation: http://learn.iis.net/page.aspx/482/install-application-request-routing/

ARRv2 Configuration: http://learn.iis.net/page.aspx/485/define-and-configure-an-application-request-routing-server-farm/

ARRv2 Load Balancing: http://learn.iis.net/page.aspx/486/http-load-balancing-using-application-request-routing/

MDS Installation and Configuration: http://sqlblog.com/blogs/mds_team/archive/2009/12/10/installing-and-configuring-master-data-services-2008-r2-november-ctp.aspx

SQL Saturday #33 in Charlotte!

RickHeiges — Fri, 05 Feb 2010 16:35:00 GMT

SQL Saturday is coming to Charlotte next month! If you are in the area, come on by. We have a great line-up of speakers. Find out more information at http://www.sqlsaturday.com/33/eventhome.aspx Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!...(read more)

On filtered indexes and defensive coding

Alexander Kuznetsov — Fri, 05 Feb 2010 15:16:00 GMT

When one and the same constant is copied and pasted in more than one place, there is always a chance that we can change it in one place and fail to change in another, resulting in a discrepancy. For example, recently I read a very interesting post by Michelle Ufford, Filtered Indexes Work-Around. I will not repeat the whole post here, I encourage you to read it in full, but here are the relevant parts. There is a filtered index: CREATE NONCLUSTERED INDEX MyLatestData ON dbo.myTable ( myDate ) Include...(read more)

Quote of the Day: On What Really Makes Something True

BuckWoody — Fri, 05 Feb 2010 14:02:02 GMT

“Whether you believe you can or you believe you can't, you're probably right.” - Henry Ford

Interview with Andy Warren about SQL Saturday, PASS, and More

andyleonard — Fri, 05 Feb 2010 12:00:00 GMT

Introduction Andy Warren ( Blog - @sqlAndy ) is a DBA's DBA. He is a friend and mentor. He calls 'em like he sees 'em, and I haven't seen him pull a punch yet. I requested an interview with Andy before I learned of the transfer of SQL Saturday to PASS. Sometimes life works out that way... The Interview How did you, Brian, and Steve come up with the SQL Saturday idea? What inspired you? What need were you trying to meet in the community? Brian and I had both been to a few Code Camps, and as you know...(read more)

Avoiding calculated column in DAX

Marco Russo (SQLBI) — Fri, 05 Feb 2010 11:27:00 GMT

A calculated column is a DAX expression which is evaluated when the PowerPivot workbook is updated. It is very useful, but there are cases where you want to delay calculation at query time. For example: You want to make part of the calculation depending on the selection in the PivotTable (i.e. selecting a scenario for a simulation) You want to avoid the storage of another column in the PowerPivot workbook (it has a cost in terms of space, after all) First of all, if you have to be worried about storage...(read more)

Killing a SQL Server thread? Don’t!

Linchi Shea — Thu, 04 Feb 2010 20:09:00 GMT

Sometimes, when you kill a session (i.e. a spid) in a SQL Server instance, the spid just refuses to go away not because it’s doing a rollback. Perhaps, it’s stuck on a certain dependency on something external to SQL Server or it’s just simply stuck for some decipherable reasons. And the spid may hang around for as long as the instance is online and will only go away when the instance is restarted. In more than a few occasions, I have heard people wondering if they can just kill the thread of the...(read more)

When bad error messages happen to good people

AaronBertrand — Thu, 04 Feb 2010 15:27:00 GMT

Over the years I have largely been amused by the variance in error messages that come out of SQL Server. Some are very verbose and some even border on provide too much information, but the ones that irk me are the ones that leave you scratching your head. I wanted to point out a few of these, and ask if you have any misleading or unhelpful error messages that you see a lot? Msg 8152, Level 16, State 14, Line 5 String or binary data would be truncated. What string or binary data? Could you be a bit...(read more)