A community of more than 1,500,000 database professionals and growing

Tablix Tutorial – Level 4 in the Stairway to Reporting Services

Jessica M. Moss from SQLServerCentral.com

This article shows how to group information, adjust for dynamic column and row groups, and generally use the tablix in Reporting Services. More »

SQL Server Analysis Services-Lead, Lag, OpeningPeriod, ClosingPeriod Time Related Functions

Additional Articles from SQLServerCentral.com

Working with SSAS time related accounting functions can be tricky. SQL Server 2012 now offers Lag and Lead functions. Are these functions available in SSAS? What other related functions are available? More »

From the SQLServerCentral Blogs - A Month of PowerShell – Day 13 (Databases: Adding Schemas, Tables and Indexes)

Wayne Sheffield from SQLServerCentral.com

Welcome to Day 13 of my “A Month of PowerShell” series. This series will use the series landing page on... More »

From the SQLServerCentral Blogs - SQL Server 2008 Statistics: What does a DBA need to know?

matt.bowler from SQLServerCentral.com

I’ve been looking into the behaviour of statistics in SQL Server 2008 – so I’d like to present an overview from... More »

Not in the way you think, but in the hookup process. When I shovel enough horse presents to fill up the device, I get to the point where I need to connect it to one of our farm vehicles and go a-spreadin'.

My wife complained at one point that I was unhooking the spreader and resting the tongue on the ground. When it got full she had trouble lifting it up and getting it on the hitch. I understood that and since I didn't want to be the permanent manure-speader-hooker-upper, we found a block to rest it on so that the hookup was easier. However you still want to jockey the ATV or tractor close enough that you can easily get one to meet the other and minimize the lifting.

As I was performing this process one morning, it got me thinking about work and some of the conflicts I've seen with other people. To be fair, I've had a few conflicts at one of my companies and was struggling to budge from my position. I try to work with people, but I'll admit I can be a bit stubborn and pig-headed at times, especially when I think I'm right.

But I think you must bring things together to be effective. Even the most adamant leaders, while they mostly get their way, they have to find a way to get the other side to bridge the gaps between them. Unless people can find a way to bridge those gaps, I really think that any enterprise is doomed to much less success than they might otherwise have. It doesn't matter if you're the CEO of a billion dollar company or a development team of 2, you need to find ways to work together.

That means compromise, and that means that you have to make an effort to find some common ground, to really examine your position and see if there is a way you can make changes that are acceptable to the other side.

I see my kids sometimes unable to do this, neither side willing to budge or consider any position other than their own. I'm amazed at how often this also occurs in business as well. There is always a way to work together and modify our position or argument. Often it comes down to just communicating with the other side. You might be amazed at how often you find out that that thing each of you cares about the most is not only different for each of you, but it's something the other side doesn't even worry about.

The next time you find yourself locked in a battle at work, think about me out there jockeying the manure spreader. Even if you can't find a way to work together, you'll probably end up smiling.

Steve Jones from SQLServerCentral.com

» Join the debate, and respond to today's editorial on the forums

If you want to see the process, check out the video podcast.

The Voice of the DBA Podcasts

The podcast feeds are now available at sqlservercentral.mevio.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

Overall RSS Feed: or now on iTunes!

• Windows Media Podcast - 28MB WMV
• iPod Video Podcast - 26.4MB MP4
• MP3 Audio Podcast - 5.3MB

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

» To submit an article, rant or editorial, log in to the Contribution Center

Today's Question:

This question uses SQL Server 2012.  The user defined error message 55555 does NOT exist in sys.messages.  I have the following table.

CREATE TABLE dbo.Test (ID INT PRIMARY KEY);
--I then execute the following TSQL statement

BEGIN TRY
   INSERT dbo.Test(ID) VALUES(1)
   INSERT dbo.Test(ID) VALUES(1);
END TRY
BEGIN CATCH
   SELECT ERROR_NUMBER() AS Msg#, 
   ERROR_SEVERITY() AS Level,
   ERROR_STATE() AS State,ERROR_MESSAGE();
THROW 55555,'why oh why do you do this',101;
END CATCH

/* The select statement returns:
 (actual message returned shortened due to lack of space to display full length in this QDD creation form )
Msg# Level State  Error Message
2627 14     1    Violation of PRIMARY KEY constraint 

The questions are: (select 3 answers)

a. What error level does THROW return ?

b. What error state does THROW return ?

c. What error message does THROW return ?  

By bitbucket

Think you know the answer? Click here, and find out if you are right.

This question is worth 1 point in this category: THROW. We keep track of your score to give you bragging rights against your peers.

We'd love to give you credit for your own question and answer. To submit a QOD, simply log in to Contribution Center.

SQL Server 2012 Query Performance Tuning

SQL Server 2012 Query Performance Tuning leads you through understanding the causes of poor performance, how to identify them, and how to fix them. You’ll learn to be proactive in establishing performance baselines using tools like Performance Monitor and Extended Events. You’ll learn to recognize bottlenecks and defuse them before the phone rings. You’ll learn some quick solutions too, but emphasis is on designing for performance and getting it right, and upon heading off trouble before it occurs. Delight your users. Silence that ringing phone. Put the principles and lessons from SQL Server 2012 Query Performance Tuning into practice today.

Get your copy from Amazon today.

Objective: Update all the M's to F and all F's to M.

-- #1 Table
CREATE TABLE GenderUpdate
(
ID SMALLINT IDENTITY,
Gender CHAR(1)
);

-- #2 Data
INSERT INTO GenderUpdate (Gender) 
VALUES ('M'), ('M'),('M'),('M'),('M'),('M'),('F'),('F'),('F'),('F'),('F'),('F'),('F'),('F'),('F'),('F')

-- #3 To check the data count gender wise
SELECT Gender, COUNT(gender) [COUNT] FROM GenderUpdate GROUP BY gender

-- #4 Final update with case statement
UPDATE GenderUpdate
 SET Gender = CASE WHEN GENDER = 'M' then 'F' ELSE 'M' END

By Raghavendra Mudugal

Answer: True

Explanation: The CASE statement can be used for updating a data in a table and the updates will be applied to that column successfully.

Ref: http://www.bennadel.com/blog/933-Using-CASE-Statements-In-A-SQL-UPDATE-Query.htm
http://msdn.microsoft.com/en-us/library/ms177523.aspx

» Discuss this question and answer on the forums

SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.

SQL Server Performance Base Line Script

Nirav Joshi from SQLServerCentral.com

Performance Base Line Report from SQL Server DMVs and Perfmon counters More »

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : Administering

OLE DB provider "OraOLEDB.Oracle" for linked server "OFAP" returned message "ORA-12592: TNS:bad packet - Please help us on the below issue We have a linked server which fetches data from Oracle 11.2.0 (which was migrated...

Management Studio - Copy Database - How to set Default Folder - Using the Management Studio Copy Database Wizard - is there a way to change the default destination folder for database and...

Any thoughts on this process? - This is more of generic programming question. We have a stored procedure which is used on many of our applications...

Copying Databases - Error - TempDB - Error: 823 Severity: 24 State: 7 - I'm trying to copy a few databases within the same server. Four of the database copies worked while two will...

Different between Application DBA and Production Support DBA - anyone knows the different between Application DBA and Production support DBA? In my understanding, production support is the person whom...

SQL Server 2005 : Business Intelligence

How to Compare Data between to servers based in Id's - Hi All, I have a requirement like, Identify the out of sync records from two servers. I have Id's to pass...

expression output - Hi, can any body tell the output for expression by taking any 2 dates as example. =cdec(DateDiff(DateInterval.Day,Fields!Date.Value,Fields!Date2.Value))*(-1) Thanks, Niha

SQL Server 2005 : CLR Integration and Programming.

Is it possible to make an SQLCLR data type 'comparable'? - I have a C# project to implement a complex number type and wanted to execute [code="sql"]SELECT DISTINCT c1 FROM complex[/code] This gives...

SQL Server 2005 : SQL Server 2005 General Discussion

problem to inshalling 2nd time SQL Server 2005 - TITLE: Microsoft SQL Server Setup ------------------------------ i try inshall SQL server 2005 bt error comeout like this "None of the selected features can...

CASE IN in where clause - I am trying to select different data from a table depending on what the passed parameter value is. If the...

Extracting xml data from text column - I'm very new sql server (oracle dba). I was assigned to work with sql server 2005. The user wants a...

SQL Server 2005 : SS2K5 Replication

Adding new subscriber to existing transactional replication setup - I have a Transaction replication setup between Server A – Publisher Server B - Distributor and Subscriber which is running fine without...

SQL Server 2005 : SQL Server 2005 Performance Tuning

How to Use SQL Profiler - Hi, I want to trace all sql,stored proc with sql server profiler. So that I may able to extract top resource...

SQL Server 2005 : SQL Server 2005 Integration Services

Get the fractional part of a decimal with SSIS Expressions - This shouldn't be too hard but I'm still having trouble getting the fractional part of a decimal number using SSIS...

Multiple Ole db sources to a single flat file destination - Hello friends, i have requirement where i have take data from different tables of sql server db, but here the important...

When does a variable get evaluated - Guys, I have a package scoped variable which gets its value from an expression: replace((DT_WSTR, 30)(DT_DBDATE)GETDATE() ,"-","") I use this variable to...

Unable to retrieve column information from the data source Error msg SSIS - Hello - I'm working on a SSIS Package I can Parse Query with no issue but as soon as I select...

SQL Server 2005 : T-SQL (SS2K5)

Slow Update Processing - Badly Written Code ? - I'm at a new place, trying to find my way around. There's an import/update process that runs very slowly and...

Weird Question About Querying with Dates - Okay so this is a very odd situation, one that I have not seen before. I'm trying to query a...

SQL Server 7,2000 : Administration

SQL Server 2000 Fulltext search error - Hi, I have Windows server 2003 SP2, with SQL server 2000 SP4. Server has 36 GB ram (min-max set to 32 GB),...

SQL Server 7,2000 : General

Update Triggers - Hi I am trying to create a trigger to store an original ordered qty field when the data is inserted...

QuickBooks to SQL Server - Does anyone have any ideas of ways to communicate between SQL Server and Quickbooks?  I have found a couple of...

SQL Server 2008 : SQL Server 2008 - General

Service pack 3 for SQL Server 2008 R2 - Is service pack 3 is available for SQL server 2008R2 version? If it does then can you send me the...

indices - what is index online and index offline ?

While installing 2005 server am getting below error?Please let me know any one solutionHow to solve? - While installing 2005 server am getting below error?Please let me know any one solutionHow to solve? Name: Microsoft SQL Server 2005...

datatype for such a long integer - Hi I got a dataset from somewhere which I have to store in sql server. It has one column which consists...

Function with 200+ IF statements! - I have a lot of respect for the optimisation gurus on this forum so when one of these guys (Jeff...

Getting sporadic "severe error" on 2008 R2 SP2 instance - Colleagues, could you please give me a bit of advice. We have one server with 2 SQL Server 2008 R2 instances running...

While, variable and table with nolock - Hello! When I looping a variable from a table with nolock hint in the while, then the execution time is bigger. Why? Here...

cluster differences - Can any one list the difference betweein SQL 2005 vs SQL 2008 cluster ?

While installing 2005 server am getting this? how to solve plz? - While installing 2005 server am getting this? how to solve plz? Minimum Hardware Requirement (Warning) Messages Minimum Hardware Requirement The current system does...

Date comparision logic....? - Hi Team, [b]select create_date, modify_date from sys.objects where create_date >='2010-04-02 16:59:23.267' and (modify_date >='2012-01-28 15:43:46.077' or modify_date <='2012-12-28 15:43:46.077')[/b] am using above query to display...

Select in scalar function - what am I doing wrong? - Hi I'm trying to create the function below CREATE FUNCTION checkLock (@van_id varchar , @nextdel DATETIME) RETURNS varchar AS BEGIN declare @locked as varchar = '0' declare @count...

SSIS Package Configuration - I have compiling like 8 different packages to one solution to use one single package configuration file. I have all...

Rebuild and organize indexes based on page_count - Hi all, I have a job weekly, it runs on Sunday. The purpose is REBUILD/REORGANIZE all indexes in my database. If...

Does log gets truncated on full backup? - I am using sql server 2008 r2 express. My doubt is related to log truncation. If i do full backup of...

error while installing sql server 2008 r2 express in a windows 8 pc - I was trying to install sql server 2008 R2 in windows 8 pc. First i installed sql server 2008 r2 by...

Really Strange - Hello Everyone I hope that your day has been better than mine. I have been working most of the day with a...

variable looping - Hi all, i have one ssis package which has to read file name from table and assign it to one...

SQL Agent won't start after installing Full-text Search - Last week I installed a new SQL 2008 R2 server. Got Agent configured and all was working. Today I ran...

SQL Server 2008 cluster upgrade - Hi Team, We have SQL Server 2008 active-passive cluster. We got requirement to upgrade it SQL Server 2008 R2. Can you...

Ran out of log space!!! - An archiving process that moves data from my prod db to archive an db consumed my entire log drive. Both...

Display on Month and Year from date formula ?? - Hi, I want to display only MM/YY from the formula of @enddate -21?? Thanks

Average memory per query - Hi All, I want to know the average amount of memory spent per query, or the memory spent for each query...

DWH : Fact table : index creation. - Below is the schema of my FACT table table : Fact.POLICY Size : 165 GB [quote] Policy_transactional_ID (surrogate key ) Policy_ID Audit_Trail_ID (FK) Journalization_ID (FK) Program_ID (FK) Product_ID (FK) Producer_ID...

How can we Group at particular set of records which come in a Sequence , If the sequence changes it would add the records to a new group. - Lets say ,There are 4 columns with Primary Key Constraints L1 L2 L3 Date Forecast 1 a x 01-jan 1.2 1...

send mail not working when used with attachment - hi, below is the script i have in SQL Server Agent. SET ROWCOUNT 0 EXEC msdb..sp_send_dbmail @profile_name = 'SQL Mail', @recipients = '<me@email.com>', @subject = 'subject', @body = 'Please...

DataType confusion - Greetz! I was speaking with a client a few days ago and they said something that contrasted with what I had...

sp_send_dbmail, encryption of email content - I have not found a way to encrypt an email sent from SQL Server. I'm not talking about the actual...

SSRS Application fails after about a month or so - Hi, I'm running SQLServer2008 on an XP SP3 box. All is well with the system except that SSRS seems to...

Backup compression - Hi Friends, Just wondering how do we uncompress the backup taken with "WITH COMPRESSION " option in SQL Server 2008 Also how...

Has anyone used Data Domain storage for SQL 2008 backups? - We are creating a Microsoft Failover Cluster with Windows Server 2008 and SQL Server 2008. Our storage administrator would like...

xp_cmdshell access denied to desktop file when logged as administrator - why ? - I am logged in as an administrator and each time i run the following query exec master.dbo.xp_cmdshell 'dir c:\users\administrator.mydomainname\desktop\resumes\*.*' I get...

Activity Monitor - % Processor Time Grayed Out - Does anyone know why the % Processor Time area within Activity Monitor would be grayed out?

SQL Server 2008 : T-SQL (SS2K8)

Need help with SQL query.. - [tbl_question] : questionId, question [tbl_answer] : answerId, questionId, answer, [tblTaskAnswer]: taskId, answerId table_task: taskId, name,...., So in tbl_question there are two question...

Get distinct values into single column - Hi - I am having trouble writing a query to get the distinct values of a column grouping into a single...

All Possible Combinations Loop - Hi, I am trying to build something that combines all values of all variables using SQL Server. For Example: A...

Issue with crypt_gen_random - This is my scalar function, which returns numbers between @min and @max (both included): [code] create function GetRandom(@min int, @max int) returns int as begin declare...

SQL Server 2008 : SQL Server Newbies

Randomising Dates - I have a Arrival date of 01/01/2012 the Departure date can be any date between 02/01/2012 and 29/01/2012 and needs...

Selecting very specific records ... - In another thread I had a query where I needed to select only patient new admissions. The query finds the...

Count by 30 min interval query - I need help in querying a table for volume by 30 minute interval by day month by month. Table dbo.HisT contained...

SELECT from OPENDATASOURCE error - I am trying to select from a table that is stored on a different server, I am using opendatasource. When...

Backing up & Restoring a DB - Hello, I have a db which I would like to backup and restore. Currently I go to db, right click tasks and...

SQL Server 2008 : Security (SS2K8)

Revoking public Role Permissions not working - Per my company's security policy, I need to revoke permissions to the public and guest roles to all objects. In...

SQL Server 2008 : SQL Server 2008 High Availability

Configuration sql 2008 cluster on san storage - Hi All, Iam unable to proceed with MSDTC storage configration where it fails with the folowing error: No disk exists in available...

Mirroring restore rate very low (< 5 MB/sec) - database mirror stuck in "synchronizing" state for several hrs - A production mirroring session started synchronizing this morning and at first the restore rate on the mirror server was at...

SQL Server 2008 : SQL Server 2008 Administration

VMWare Virtual SQL Server Administration - I need to find out what the concensus is on virtualizing SQL server. I know that VMWare reports the more processors...

SQL Server Agent not running - Hi, I have a SQL Server Standard 2008 R2 instance running which was upgraded from the Express version. This is on...

Shrink Data file with truncateonly - Hi, I wanted to shrink a data file with the truncate only clause.Will i require to rebuild my indexes?

Does DBCC Page show included data? - For my own interest I have been looking at DBCC IND and DBCC Page. Regarding a non clustered index page I...

SQL 2008 Express Syncronization - Hi, Can SQL 2008 Express be syncronized with another SQL 2008 Express instance on another server. I would like to setup...

Fragmention even after rebuilding the indexes - I Checked for fragmentation on my DEV Server and it was in the late 90's. So i decided i would...

When DB restored to different server will database mapping and permissions stay? - On Server A I have a database the is overwritten daily by a database on another server [Server B] After the...

What kind of license needed for implement mirroring on a virtual mechine? - Hi Friends, I have to implement mirroring or other DR on VM ware. here my question is What kind of licenses...

Agent Jobs Query - Hi, I would like to check each morning if my agent jobs have completed successfully. What would be the best method...

Programming : Powershell

collecting a count of errorlogs using posh - Hi, I am running a script to collect a count of all errors in the current error log using smo,...

SQLServerCentral.com : Anything that is NOT about SQL!

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

Reporting Services : Reporting Services

SSRS Parameters - Autocomplete - Hi geniuses! It is possible to have the autocomplete features in a SSRS report parameter? Thanks in advance! Best Regards!

LIKE statement with Parameter Problem - Hi, I am trying to add a LIKE statement to a query in report builder 3.0 with a parameter like...

Changing the SendFrom Email address for data-driven subs - I have recently setup my first data driven subscription without a hitch :w00t: However, I now need to be able to...

Data Warehousing : Integration Services

Export multiple files to Excel using SSIS - Hi all Can someone please help me with a link that has an example on how to export data to multiple...

Looking for a way to move files from a dynamic source location to another dynamic archive location - I got the following situation: A database with a settings table and archive tables. (In the setting table we store the location...

script task - Hi All, i have been using ssis for few years now i have used most of the components/task but never script task i...

check contents of a text file - Guys, I am using the Konesans file watcher to watch for a file (status.txt) to change. The file contains only one line...

Data Warehousing : Analysis Services

Related measures - I have two related tables in my DSV. The first table called Test has a primary key column called test_id...

Named sets in SSAS - Hi, I'm creating named sets in SSAS. I happen to be using 2012 but don't think that this has any effect...