Reporting Services (SSRS/MSRS) 2008 Error: Set used with the complement operator must have all members from the same level
When you use the Not In operator in a SSRS 2008 MDX query filter to exclude a named set, it uses a the complement operator in the constructed MDX. This is fine as long as "all members [are] from the same level." Since you got this error, they are not 
You can get around this by using the Except() MDX function instead of letting SSRS use the Complement operator
In the ReportServerService log, you'll see something like this:
Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: Query (..., ...) Set used with the complement operator must have all members from the same level.
Original filter
Dimension: Time
Hierarchy: Calendar Date
Operator: Not In
Filter Expression: [Today]
New filter
Dimension: Time
Hierarchy: Calendar Date
Operator: MDX
Filter Expression: Except([Time].[Calendar Date].[Calendar Date].MEMBERS, [Today])
Determining when RESTORE DATABASE command will complete (SQL Server 2008)
Ah, I see you just started restoring that 1TB monster and now everyone wants to know when it's going to be finished, where you're at in the process, etc. Fear not, Microsoft is very good at making up fictional numbers for us to use as rough estimates! I usually add 10-20% on top of these estimates just incase the database gremlins wander by to ruin your day again. Or incase you encounter "storage issues."
SELECT percent_complete AS [PercentComplete] ,estimated_completion_time/1000.0/60.0 AS [RemainingMinutes] ,total_elapsed_time/1000.0/60.0 AS [ElapsedMinutes] ,(estimated_completion_time+total_elapsed_time)/1000.0/60.0 AS [TotalMinutes] ,DATEADD(MILLISECOND, estimated_completion_time, GETDATE()) AS [EstimatedTimeOfCompletion] ,st.TEXT AS [CommandSQL] FROM sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(r.sql_handle) st WHERE command LIKE '%RESTORE DATABASE%'
Determining how long a database will be IN RECOVERY (SQL Server 2008)
So, your MSSQL service crashed in the middle of a big transaction? Or you bumped the service while it was rolling back some gigantic schema change (like say a column add on a 800 million row table)? Well, as you prepare your resume in preparation for the fallout from this debockle, you can use the following query to see how much time you have left. Or, I should say, how much time it thinks you have left... which seems to swing wildly up and down... microsoft math ftw.
DECLARE @DBName VARCHAR(64) = 'databasename' DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX)) INSERT INTO @ErrorLog EXEC sys.xp_readerrorlog 0, 1, 'Recovery of database', @DBName SELECT TOP 5 [LogDate] ,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete ,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining ,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining ,[TEXT] FROM @ErrorLog ORDER BY [LogDate] DESC
Maintaining a Type 1 Slowly Changing Dimension (SCD) using T-SQL
A few days ago, one of our SSIS packages that maintained a Type 1 Slowly Changing Dimension (SCD) of about 1 million rows crept up to 15 minutes of runtime. Now this doesn't sound too bad, but this is part of our hourly batches, so 15 minutes is 25% of our entire processing window. The package was using the Slowly Changing Dimension Wizard transformation - we were doing the standard OLEDB Source (which basically represented how the SCD "should" look) and then sending it to the SCD transform and letting it figure out what needed to be inserted and updated. One option was to switch to lookups instead of the SCD wizard to speed things up, maybe even some fancy checksum voodoo for the updates (see http://blog.stevienova.com/2008/11/22/ssis-slowly-changing-dimensions-with-checksum/ for an example). Then after thinking about it a little more - why are we sending a million rows down the pipeline every hour? We know only a small percentage of these are new - and another small percentage needs to be updated. Well, we can just write a quick SQL query to get us just those sets and the package would be much more efficient!
Wait a tick - why would we give the rows to SSIS if all it is going to do insert one set and update the other? Let's just do it all in T-SQL:
Parsing QueryString (GET) Variables From a URL using T-SQL
First of all - I know, don't do this. The application that put the URL in the column in the first place is MUCH better at handling URLs and ideally, you would just add columns for the GET variables you're after and have the application put those in. Parsing them out after the fact from a VARCHAR field is insane.
So now we're here and we need to do that thing that we said we shouldn't do. My approach is to use a Table Valued Function that only returns one column - the value of the variable or NULL if it can't find the variable in the querystring.
A Cleaner Way to Detect the State of a SQL Server Agent Job In SQL Server 2005
I have always felt that the traditional approach to getting Agent Job status by creating a table and populating it with output from xp_sqlagent_enum_jobs was incredibly crude. So I poked around a bit and came up with a solution using a Table Valued Function that queries sysjobactivity to determine the status of a given job. The reason I use a Table Valued Function is because we have a few tables that contain a list of jobs that need to be started at certain points in a given process (and they may or may not already be running) - with the TVF, you can simply CROSS APPLY the function and bob's your uncle.
Quick Analysis of Cached Query Plans in SQL Server 2005
I run this one pretty frequently when we need to figure out what procs are killing a complex ETL process and what exactly about them is making the server cry. So basically, if it's on the development server, I'll do a DBCC FREEPROCCACHE and a DBCC DROPCLEANBUFFERS, run the entire set of ETLs, then run this query and then dig deeper in to the query plans that look suspect (high *Scan counts usually, sometimes lots of Hash Matches or Merge Joins). On a production server, the clearing of the proc cache and dropcleanbuffers can be problematic so I'll often just run the query after a scheduled ETL run. If you want to see the query plans mapped out visually, click on the query_plan value and SSMS will open up the XML. Then save that XML file as a .sqlplan file. Once you have that, close the XML and then open the .sqlplan file.
High Handicappers: Why count Three off the Tee during practice?
First of all, I'm a high handicapper. Currently I float around 20-25. I do NOT count penalty strokes when my tee shot goes out of bounds when practicing (I count all other penalties, just not that one). I don't count those because they inflate my handicap. When practicing, I've got my driver out every hole except for par 3 holes simply because I can - not because it's the smart play. If you slice 50% of the time and the right side of the teebox is out of bounds (and the subsequent 250 yards are also out of bounds to the right) - the intelligent thing to do is take out your 4 or 5 iron and put it on the fairway. That's exactly what you would do during competition play. But when practicing, every time, without fail, I'll sacrafice 2-3 balls to the right lateral hazard. Technically, that's 4 strokes - tee out of bounds, drop, tee out of bounds, drop, etc. But that shouldn't be factored in to my handicap. Basically, if you take a risk during practice that you wouldn't take during competitive play - I see no reason to rack up stroke and distance penalties. You're better off (in my opinion) just saying to yourself "lesson learned - don't do that," dropping another ball and practicing the safe and logical play.
Adding Windows Event Log Logging To Existing SSIS Packages via C#
While working with some SSIS logging, it occured to me that there's really no good way to globally apply the same logging to all packages on your server. For our production SSIS server, it would make sense to log all OnError events to the Application Event log. Then, whatever event log monitoring app you use can notify you of the package error (with details!). Thus, this app was born - an app to remove pre-existing LogProviders and add in the intended LogProvider and options.
This is really just an example of the implementation that worked for my purposes - I'm sure some of you would prefer to log elsewhere. This should be enough to get you going. Please drop a line in the Comments if you post alternative approaches.
Usage
SSISForcedLogging.Console.exe "Z:\SSIS Packages" or SSISForcedLogging.Console.Exe "Z:\SSIS Packages\Package1.dtsx"
Customer Service at the Golf Course (the customer is NOT always right)
In most businesses, the name of the game is giving the customer what they want when they want it. Putting the right opportunities in front of them at the right time. At the golf course (the elitist ones), the game changes quite a bit. Take the Pro Shop for example - everything is overpriced, at least 20% above what you could get it for at TGW or GolfGalaxy. Why? Because if you're in need of something before a round and you remember just as you're standing in the pro shop, you're certainly not going to hop in your car and head over to GolfGalaxy - you just buy it there, you're trapped more or less. Don't even bother trying to work with the pro shop guys on the price either, they could care less. Which brings me to my second point - the staff.
Social Networks
What I'm Doing...
- @joekieffer good luck! in reply to joekieffer 2 weeks ago
- @ben_p bah, i'm proud of him - that's a ridiculous name to have on a resume. give him at least 2 years before you start the peer pressure in reply to ben_p 2010-06-11
- @michaelfransen ha, I was pretty sure u were ignoring me ;-) ty. in reply to michaelfransen 2010-06-11
- Last day at W3i today - moving to Trek Bicycle Corp! 2010-06-11
- Anyone know how to mimic gears.geolocation.getPermission() with the html5 navigator.geolocation object? want to add a message to the dialog 2010-06-07
- More updates...
Recent Posts
- Excel 2007 Hangs When Trying to Edit an OLAP Cube Filter
- Locating Rampant Database Growth
- Script sp_configure Output To Migrate Settings Between SQL Servers
- Script RESTORE DATABASE … WITH MOVE Stub
- VersionOne Nag: Reminding Us To Burn Our Points