In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Source Control Connect your existing source control system to SQL Server
SSMS plug-in connects SVN, TFS, Git, Hg and all others to SQL Server. No source control system needed to evaluate. Learn more.
 
SQL Storage Compress Compress live data by 73% 
Red Gate's SQL Storage Compress reduces the size of live SQL Server databases, saving you disk space and storage costs. Learn more.
 
SQL in the City SQL in the City - Free SQL Server training the Red Gate way - heading your way this Fall
SQL Server MVPs, ingeniously simple tools, and an awesome training experience. Register now for an event happening near you.

In This Issue

SQL Server 2012’s Information on Parallel Thread Usage

In the SQL Server 2012 execution plan we can see better the true thread reservations for a query and not just information on the maximum degree of parallelism and row distribution across parallel threads; thanks to the new parallel thread usage information. More »


SQL Saturday #167 - Columbus, GA

A free day of training in Columbus, GA. Come meet the local SQL professionals and spend a day talking SQL Server. More »


Removing the SQL Server Management Data Warehouse

SQL Server 2008 introduced a new feature, Management Data Warehouse (MDW), which allows users to collect metrics on their servers over time to aid in performance troubleshooting. A lot of people try this feature out, because it is easy to set up, and then find that it is not so easy to remove. In fact, removing MDW is not supported; in SQL Server 2012, though, a new system stored procedure was added to make this process easier. The problem is that this stored procedure (as well as several of the workarounds I've seen published) can leave several objects behind.  More »


From the SQLServerCentral Blogs - SQL Server : Implementation of Table Valued Parameter (TVP)

Some time there will be situation in application , where a grid is populated with data and user has option to edit... More »


Editorial - A Data Hub

One great promise of data warehousing over the years has been a "single view of the truth" for companies. By moving, cleaning, transforming, and standardizing data from other systems, we can put together a single location for the most accurate data a company can have. I suspect there are some organizations that have had success here, but many are struggling. The idea behind some of the newer SSIS tasks, Master Data Services (MDS), and Data Quality Services (DQS) in SQL Server is that we have some functions in the SQL Server platform to make this easier to achieve. Or perhaps to ensure we do so at a high level of success.

However just moving data to a central location isn't necessarily the only way to deal with the challenges of data. Perhaps there's a better way, a more distributed way that provides a framework for centralization, but distributes the ownership and knowledge of the data to others. Buck Woody recently wrote about data hubs as a project and idea that Microsoft is making available. It's a place to publish data for your organization, but groups inside your company, but available for others to use.

Many of us have experienced the issues of each developer or each department attempting to manage their own sources and lookup data. Even well known data such as postal codes can change and easily become stale quickly. How many developers are willing to write import routines to update this data for something such as postal codes, let alone internal data such as customer names. 

Ideally I think we should pull lots of our initial data from corporate sources, and establish central data hubs for all new types of information we gather and support. From there, a variety of import and update routines could be written and shared by all applications people use. It wouldn't provide perfection in terms of data quality and freshness, but it would be better than allowing each individual developer to make their own decisions.

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


The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

Everyday Jones

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

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.

You can also follow Steve Jones on Twitter:

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


Question of the Day

Today's Question:

Question: In what order do these events happen?

  1. Constraints, BEFORE triggers, INSTEAD OF triggers.
  2. INSTEAD OF triggers, constraints, BEFORE triggers, AFTER triggers.
  3. INSTEAD OF triggers, constraints, AFTER triggers.
  4. Constraints, INSTEAD OF triggers, AFTER triggers.

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

This question is worth 2 points in this category: trigger. 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.

The Microsoft Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset

As the most influential thought leaders in the data warehousing and business intelligence industry, the Kimball Group has developed pioneering techniques that serve as industry standards for DW/BI system design, development, and management. With this new edition of their bestseller, veteran experts from the Kimball Group get you up to speed with using the new Business Intelligence release of SQL Server: SQL Server 2008 R2. Covering the complete suite of data warehousing and BI tools that are part of SQL Server 2008 R2, the authors follow the full project lifecycle, including design, development, deployment, and maintenance. Get your copy today from Amazon.


Yesterday's Question of the Day

What is the output of following command:

SELECT ISNULL(1,2)

Answer: 1

Explanation: ISNULL checks the first argument, if it is NULL then it returns the second argument. Else it returns first argument.

Ref: ISNULL - http://msdn.microsoft.com/en-us/library/ms184325.aspx

» Discuss this question and answer on the forums

Microsoft SQL Server 2012 Reporting Services

Create, deploy, and manage business intelligence reports using the expert tips and best practices in this hands-on resource. Written by a member of the original Reporting Services development team, Microsoft SQL Server 2012 Reporting Services, Fourth Edition covers the complete process of building and distributing reports and explains how to maximize all of the powerful, integrated SSRS capabilities, including the new and enhanced features. A detailed case study and sample reports are included in this practical reference.

Get your copy from Amazon today.


Featured Script

2008 Index Rebuild using DMV

Index Maintenance procedure with HTML Reporting capabilities. Please refer to attached script for detailed information. More »


Database Pros Who Need Your Help!

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

SQL Server 2005 : Administering

SQL Server Configuration Manager Error - Hi Experts, I got an error while I was trying to connect to the SQL Server Configuration Manager, The server is...

Deleting records from a table that is actively written too.. - Hi there, We have a table where Application is logging. On average, roughly 800 rows are written to the table per minute...

Health Check script for sql server 2005 - Hi Experts, need a small help as i want to do health check on my sql server 2005, i was wondering...

Database in Recovery Pending State - One of my databases is in [b]Recovery pending[/b] state. I tried to run an Alter command on the database to...

SQL Server 2005 : Backups

sql 2005 and logshipping - hi, I have a basic question since years ago now, I did not find any solution. One database on one server name...

The database cannot be recovered because the log was not restored. - Hi, my db name is abc.. i hv restored the bckup on tht db.. But in middle i have cancleed the...

SQL Server 2005 : Business Intelligence

Anything out there better than SSRS - Hi All Beginning to look around for other BI tools. Don't have a vast amount of knowledge in this area, but...

Error -1056899072 : The following system error occurred: Key not valid for use in specified state. . While deploying Sample Project of SSAS Cubes. - Error -1056899072 : The following system error occurred: Key not valid for use in specified state. . I am using Visual Studio...

SQL Server 2005 : Working with Oracle

Refresh from Oracle - Hi friends, We need to refresh the data from Oracle database to SQL Server 2005 every hour or so.. We...

SQL Server 2005 : SQL Server 2005 General Discussion

Self Joins in SQL Server 2005 - Is there any better way of implementing self joins in SQL Server.? I mean, instead of looping the same table...

convert number into words - Hi I want to convert number into words in sql server 2005.For examople,we enter number like 15000 from front end...

SQL Server 2005 : SS2K5 Replication

Database Mirroring - Hi Gurus, Due to some network glitch mirroring connection is disable. Is it possible establish the connection automatically once network connected...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Use of statistics - Stored Proc v Ad hoc SQL - I had a curious situation recently where a stored procedure ran for 48 hours without completing. Conversely, running the procedure...

SQL Server 2005 : SQL Server 2005 Integration Services

switch to unicode - I have about ten SSIS packages that use varchar and char columns in tables. Most are built from the wizard. Is...

How to read data in a pdf file in SSIS - I was wondering if any body had a situation where data needs to be extracted from pdf files and exported...

SSIS 2008 set variable or connection string using dtexec - I have an SSIS package and I would like to set the flat file source connection string on the fly....

SQL Server 2005 : T-SQL (SS2K5)

set timeout in openrowset? - Hi all, Is there a way to set timeout in openrowset? My project requires to connect from a sql server 2005...

hierachy with miltiple parent - Childid Childname Parentid pid 100 Bingo 200 1 101 Pingo 201 1 102 Zingo 201 1 100 Bingo 201 2 101 Pingo 200 2 102 Zingo 201 2 100 Bingo 201 3 101 Pingo 201 3 102 Zingo 200 3 100 ...

Remove redundant data from address column - Hi, Consider the folllwing two records: [code="sql"]if object_id('test') > 0 drop table dbo.test create table dbo.test ( master_id int, Title varchar(20), Forename varchar(50), Surname varchar(50), Address1 varchar(200), Address2 varchar(200), Address3 varchar(200), Address4 varchar(200), Town varchar(100), County varchar(100), Postcode varchar

Select from stored proc for many records where single record ID being passed in - I'm stumped.. I have a fairly complex sproc that is expecting a single record identifier to be passed in, and...

SQL Server 7,2000 : Administration

Reading SQL dump file - I have a SQL server 2000 with SP3 on standalone environment. It is a production box. It is generating SQL dump...

SQL Server 7,2000 : Data Corruption

Suspect Mode - Good morning, It is a bit of a long story but i'm recently hired and have had a sql server that...

SQL Server 7,2000 : Performance Tuning

SQL Server Profiler - Hi All Does anybody know if and how I can get SQL Profiler to start automatically on a test server but...

SQL Server 2008 : SQL Server 2008 - General

configuration error - hi, while enabling xp_cmdshell from value 0 to 1 we are getting the following error when we try to reconfigure it...

Error: Agent XPs disabled. How do I fix this? - hi, I was trying to generate scripts for a DB i am working with but I keep having this error: [code] An...

Which SQL Front End??? - Morning all, I was wondering what front ends people are using? I'm aware that I can use MS Access but would prefer...

SQL Server 2008 Database Stuck - I am using SQL Server 2008 in on of our project, I am facing an issue, sometime daily or after...

SSIS to extract photos in correct jpg format - The job is to extract the photos from the SQL table into .jpg format. I have created an SSIS package and...

Beginner Has Questions re MS SQL 2008 Training Secifically BI Tools - I am a Junior Data Analyst , and use primarily Excel 2010 . I would like to begin using BI Tools that...

query help - hello friends I need one request, here is DLL [code="sql"] create table #temp (Student_ID char(9), Sex char(1), RaceCode varchar(5), Lunch int, Student_ESL int, GIEP...

SSRS Help - I'm still new at SSRS, so I'm looking for some help. I have built a report in Report Builder and...

Local System accounts - Can a local system account(not AD account) pass windows authentication for logging into SQL Server?

Service acounts for SQL Server - Hi Experts, I got a request for creating 5 service accounts for sql servre Question 1) these accounts shoulb be AD...

Adding a new Node on the existing SQL Server two nodes cluster. - I currently have a two nodes SQL Server cluster. Both nodes have identical hardware (dell R900 servers). I want to...

SQL Browser service needed for non-standard static port? - If I am using dynamic port settings on my sql server instance, I need the sql browser service to run...

RSClintPrint - So we are in the process of converting over from Crystal to SSRS. Things seem to be going fine; however,...

Script to test connection to SQL Server - Hi All, We have a table 'Servers' which lists all the Servers in our Company. My scenario is that we have a...

Looking for guidelines on using the Tuning Advisor - So I'm looking to assist the developers at my employer, who have been working on an SQL based application. The...

Visual Studio 2008 and SSRS Project - Request Failed with HTTP status 400 - This is doing my head in!!! We have two workstations, one report project and a Windows 2008 R2 (64bit) Server running...

blocking - Hello How to check blocking occurs in last days(means previous blocking) means i want to check transaction on Database for lat 3...

Port numbers 1433 and 1434 - Hi DBA's why there are two ports 1433 and 1434 for SQL Server. whts the purpose of two? how are they different? I came...

.NET 3.5.1 and 4.0 on same server - Is there any issue installing .NET 4.0 on the same server that's running .NET 3.5.1/SQL Server 2008 R2?

Is there any other way to resume mirroring while moving database to another server? - Hi All, I will try to move my db to another server while a live mirroring session is running? I will detach...

How to order by month which is varchar field according to date format? - I have month field in format as JAN-12 with data type varchar. Now i wan to order by this field...

BI certification? - Hi, I need help on deciding the track for certification in BI. I did search MS website , however it left...

sql server 2000: how to limit a users session connecting to the database. - if a user connects from the application end to the database, he gets only 30 minutes of session time after...

SQL SERVICE - I have 3 instances on SQL 2008 , when i tried to restart one of the sql service of an instance...

Backup SQL Server file to remote Hard disk - Sir, I Want to backup sql server file in remote hard disk directly.But sql server takes only internal drives as...

Memory Consumption -- CACHESTORE_SQLCP and MEMORYCLERK_SQLBUFFERPOOL - We have SQL Server 2008 box with 16GB physical memory. Maximum server memory in SQL Server has been set to...

Master-detail reporting question - Requirement: A product line can have 1 or many products. When a product line has all its products out of stock,...

List of all numbers between low and high - What is the best way to get a list of all numbers between low and high numbers? Thanks. Actually >= low,...

procedure started taking longer time to retrieve data in last two days - Hello, I was running a procedure to retrieve data which usually took 14-20 seconds but in last two days it started...

Full Text Search Indexing For apostrophe - [font="Arial"]Hi, I have to use exact match in my application. If I am searching "Application's" it should display only those...

Activity Monitor - Category Does Not Exist - Whenever I try to open Activity Monitor through SSMS on a particular instance, I'm greeted with the message - "Category does...

Performance Dashboard - Good morning, I have encountered an error when clicking on the "System CPU Utilization" graph. The error says: A data source instance...

SQL Server 2008 : T-SQL (SS2K8)

How do I pad a delimited number? - I am trying to write a SQL statement to pad numbers with 0's. The number string has a . delimiter and...

Stored Proc to split data in two - Hey all, I have a stored proc. It does what i want it to - but it seems over complex and a...

Urgent help please !! - Hi All, I've a question regarding a problem that I’m facing. I need to get the orders that were placed...

What is the performance bottleneck here? - [b]DISCLAIMER[/b] I did [i]not[/i] write this code Assume that #InstitutionChild is just a temp lookup table filed with about 95,000 int...

Date Format Problem - Hi, I have a little problem where my Database stores Dates in 'yy/mm/dd' format. I want to retrieve Date in 'dd/mm/yy' Format. I...

Pivot And Unpivot Table - Hi, I am a newbie in sql server.I have came across various article on internet which shows how to implement pivot...

Not able to insert a particular value - Hi, Please find the below query INSERT INTO tbl_employee (EmpNo,CompanyID,MngrID,DateOfFiled,DateOfClosed,DateOfCreation ,LastModified,Active,flag) VALUES( '2011-1508' ,89827,null,null,null,GETDATE(),GETDATE(),1,'C') In the table, column 'Active' is of type...

Query Help- Cursor change into FOR WHILE LOOP, TEMP TABLE - I have created a STORED PROCEDURE, which includes 5 different CURSORs. The company I am working for has just informed...

Multiple Recursive References inside CTE - Hi Guys, Hope you're all doing well! I have a problem and will provide an abstraction (extremely simplified) for it, in the...

Flattening a Parent Child Hierarchy - Hi, We are starting work on a BI project that is given data in a traditional Parent-Child Hierarchy format. The Problem is...

SQL Server 2008 : Working with Oracle

BIT datatype and Oracle - Hi, As per the business requirement, any SQL script that I make should follow ANSI standard, so that it should successfully...

SQL Server 2008 : SQL Server Newbies

Filtering data from a table - I have a table which contains data somewhat in following manner: ID Status Time 1 Started 7:00 1 Running 8:30 1 Completed 9:30 2 Started...

What solutions do you think will disappear? - Hi guys! We are currently working on a cloud product. We had a couple of internal meetings about cloud products replacing...

Backup and save a copy on a server on a different domain - Hi I would like to set up a daily job that backs up the database and transfers a copy of the...

SQL Server 2008 : Security (SS2K8)

TDE Encryption yet again - Hello everyone, Does any one know if TDE Encryption uses CBC (Cipher Block Chaining) or ECB (Electronic Code Block) Thanks

Security - Owned Schemas - Hi All I'm trying to understand something regarding SQL Server security If I have a SQL Login and that Login is also...

Open port? - Almost all our SQL servers are behind firewall. We have in house developed applications using these SQL servers. but I...

SQL Server 2008 : SQL Server 2008 High Availability

Migrate/Upgrade from Standard to Enterprise - Hi All, Please help/share your experience. Here are my requirements. [b]Current Environment[/b] SQL Server 2008 R2 Development environment 2 node Active/Passive cluster, Currently in standard...

Log Shipping errors - Hi, Trying to work with log shipping. Primary server is backup log files but the secondary isn't copying them to...

SQL Server 2008 : SQL Server 2008 Administration

Physically moving mdf/ldf files to different drive - Below is the code I am using to move files from C: drive to F: drive: ALTER DATABASE MyDB SET OFFLINE; GO --...

Index Fragmentation and Performance - Hi All I understand that External Fragmentation exists when the Index pages are not in a logical order for SQL Server...

Tempdb log file location and Filegroup optimizations - I've been reading articles about tempdb optimizations and I already understand a lot better the different ways to archive it....

Delete data in tables/performance issues - Hello, I need an advice, I need to get data daily from one server to another, pretty much I will truncate...

Career : Resumes and Job Hunters

How can I attract Senior Database Administrator candidates? - I have a few full-time DBA positions open on my team, one of which is for a Sr. DBA. We...

Programming : Connecting

MySQL as linked server - Hi everbody I need some suggestions I Have a sql server(2005) default instance in Windows Server 2003, I need to connect...

SQLServerCentral.com : Anything that is NOT about SQL!

Fiction aping reality? - I was watching the Mars landing yesterday morning (and boy was it tense) when I noticed one of the scientists...

My 10,000th Post! - [b]This is my 10,000th (ten thousandth) post! [/b] well err, 10K points technically, since I did spend some time on questions...

Good C# forum ? - Hi all, Can somebody please recommend good C# forum? I checked some of them from Google search result, but none of...

The SQL Saturday Thread - I figured I'd start a thread (I don't think anyone's done so) for all things [url=http://www.sqlsaturday.com/]SQL Saturday[/url]! I'm heading down to...

SQL Jokes & Riddles!!! - What did the one SQL table say to the other SQL table at the bar this past Friday night? :w00t:

Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,...

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 answers getting worse? - OK, we've had fun bashing frustrating posters & topics. But to be fair, we should hold ourselves much more accountable than them...

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 blocks up the whole SQL2005 server - Hi guys, I have an subscription that normally runs in the evening. 84 reports are built by this subscription. It's done...

Data Warehousing : Integration Services

Importing rows from SQL Server to Excel 2007 using SSIS - My System config if needed Database: SQL Server 2008 OS:Windows 2008 Server Microsoft Office 2007 64 bit M/c I have followed following steps . 1. Added...

Import data from excel and load into sql server - I need to import data from excel and excel has data like this for example Column Name Test01 test02 test03 test04 test05 Values State2_J...

Data Warehousing : Analysis Services

Can anyone let me know why not so many companies choose SSAS for data analysis and base layer for dashboard? - I kept tracking the job market and noticed not a lot of company developed their DW with the use of...

MDX - Cube Calculated member sliding 12 month comparison with previous month data - Hi, I want to compare Aggregate of following two different resultsets: [code="sql"] SELECT [Measures].[Internet Sales Amount] ON COLUMNS, NON Empty { LastPeriods( 12, [Date].[Calendar].[Month].[January 2008] ) } On Rows FROM [Adventure...

refresh Excel report based on Offline-SSAS-Cube (a *.cub-File) not possible - Hi, we can't refresh the Excel-reports based on a *.cub file even if there is a newer .cub-file. Background: We use SQL Server...

Data Warehousing : Data Transformation Services (DTS)

regression to classification binary - Can anyone assist me on how i go about arranging data that i used as continous input,to data that goes...

Notification Services : Administration

Unable to copy data to remote machine through BCP!! - hi folks, i am trying to copy data to remote server through BCP. I am getting the error while executing the query...