In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Backup "Impressed is the word!
Cut our backup times in half and reduced the space used by 80%!" Tobie Dunn, SQL Backup Pro 7 user. See what savings you can achieve - download a free trial today.
 
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. Get started with the 28-day free trial.
 
SQL Monitor The easiest installation of a third party tool, ever!
- Aaron Kolysko, Monsoon Commerce. Get started with SQL Monitor today to gain effortless insights into the health of your own servers - download a free trial.

In This Issue

11 Tips to Backup databases with SMO, VB, C# Powershell, Command lines

Sometimes we need to create backups using code. Sometimes we need to do it manually or automatically, programmatically using C#, VB, Powershell.  More »


SQL Saturday #170 - Munich, Germany

Free SQL Server training comes to Germany. Join fellow SQL Server pros in Munich on Sept 15, 2012. More »


Utilize SQL Server Storage Effectively

With the increased use of databases and the need to have more and more data online, database storage is an area DBAs need to manage effectively. If you plan your database and data management correctly you can build and manage a cost effective, high performing SQL Server solution. If not, you risk potential storage issues as well as performance degradation. This session will cover best practices and options focused on helping you manage your SQL Server storage. We will dive into the tools you can use to manage database storage, some of the latest trends, database design implications, data archiving and alternatives such as compression, BLOB data storage, filtered indexes and more. We’ll also introduce a tool that allows you to reduce the storage footprint of your live SQL Server databases. Join us for this free event and learn how to utilize your SQL Server storage effectively. More »


Getting Started with Master Data Services (MDS) In SQL Server 2012

MDS allows you to create a centralized hub for creating and managing enterprise master data. Arshad Ali discusses how to install and configure Master Data Services in SQL Server 2012, and a method to deploy sample models. More »


From the SQLServerCentral Blogs - Choosing a Mobile Workstation

I get a lot of e-mail and Twitter requests for advice about what laptop someone should buy. I don’t mind... More »


Editorial - Milk

I love this quote: "[we] ran it through a fairly sophisticated computing gobbledygook. It spit out at the other end predicted transmitting ability, predicted genetic values of whatever sort."  How would you like to explain your complex analysis to a business user using those terms? In most cases, those might be the best way to describe things to most users.

However that quote comes from a scientist that has spent a lot of time and knowledge refining models and using lots of data to try and predict the bulls whose progeny will result in the best dairy cow performance. The article is very interesting and talks a bit about the complexity of using genome data on cattle and how far it is from the experiments Mendel performed on pea plants. I still remember the dominant/recessive gene explanations from biology, though that explanation seems simplistic now. Comparing it to the work done by scientists predicting cow performance is like comparing horse drawn carriages to Formula 1 race cars.

Big data is in the news, with so many companies building products they want to sell to help you tame, analyze, and use the data your organization has available. However as this article hints, it's much more than managing all that data. There is a tremendous amount of knowledge and intelligence needed to use those tools and actually generate useful information.

As data professionals, we don't necessarily need all that knowledge, but we do need to communicate with those individuals that may have the knowledge and work closely with them to ensure their ideas are accurately applied against data in the algorithms we write. In addition to writing code and understanding statistics, strong communication skills are important to moving your career forward.

» 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:

Given the following table:

DECLARE @table TABLE (
  RowID INTEGER IDENTITY,
  BitValue BIT NOT NULL);

Which of the following values will be inserted as a true / 1 value into the BitValue column? (select all that apply)

 INSERT INTO @Table (BitValue)
  SELECT -1 UNION ALL
  SELECT 0 UNION ALL
  SELECT 1 UNION ALL
  SELECT 25;

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

This question is worth 1 point in this category: T-SQL. 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.

Delivering Business Intelligence with Microsoft SQL Server 2012

Equip your organization for informed, timely decision making using the expert tips and best practices in this practical guide. Delivering Business Intelligence with Microsoft SQL Server 2012, Third Edition explains how to effectively develop, customize, and distribute meaningful information to users enterprise-wide. Learn how to build data marts and create BI Semantic Models, work with the MDX and DAX languages, and share insights using Microsoft client tools. Data mining and forecasting are also covered in this comprehensive resource.

Get your copy from Amazon today.


Yesterday's Question of the Day

What is the output of the following statement:

SELECT DATEADD(s,0,0)

Answer: 1900-01-01 00:00:00.000

Explanation: DATEADD function adds zero seconds to default datetime 1900-01-01 00:00:00.000

Ref: Dateadd - http://msdn.microsoft.com/en-us/library/ms186819.aspx

» Discuss this question and answer on the forums

Delivering Business Intelligence with Microsoft SQL Server 2012

Equip your organization for informed, timely decision making using the expert tips and best practices in this practical guide. Delivering Business Intelligence with Microsoft SQL Server 2012, Third Edition explains how to effectively develop, customize, and distribute meaningful information to users enterprise-wide. Learn how to build data marts and create BI Semantic Models, work with the MDX and DAX languages, and share insights using Microsoft client tools. Data mining and forecasting are also covered in this comprehensive resource.

Get your copy from Amazon today.


Featured Script

Script to get an ordered list of all stored procedures with parameters

Generate a per-schema, per-procedure ordered list of all stored procedures for the current database, together with their parameters, datatypes and nullability. 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

Best institute for SQL DBA in Hyderabad - Hello friends, Can you please suggest me a good institute in Hyderabad for SQL DBA ? Also can you please suggest me...

How should i free up the space from the BACKUP Drive whcih is increasing Rapidly - How should i free up the space from the BACKUP Drive whcih is increasing Rapidly Thanks Rohit

How to shrink data file in sql server 2005 - I want shirnk mdf file , it is around 450 GB . What command exactly use? Please any one suggest to me It is...

data recover - Hi someone has deleted some records from a table and has also deleted the corresponding record from the audit log. It...

how to remove/delete standy by database on secondary instance? - I was doing Log shipping and something went wrote now I want to start all over. How to delete restored...

restore multiple differential backups ( each no recovery), is it possible ? - Good day all. Please let me know if this is possible. i have a server that has some faulty hardware, it...

Lock pages in Memory - Hi, We have a 2 node a/p cluster setup and I have enabled lock pages in memory on both nodes and...

SQL Server 2005 : Backups

One restore or many, many restores - So, let me first preface this by saying, I've been a SQL Server DBA for 13 years. I've performed some...

SQL Server 2005 : Business Intelligence

SSIS Execute Package task fails to Acquire Connection - down vote favorite I've a Master package where in I'm calling several packages using Execute package task. Both child and master packages...

SSIS package change source from Oracle 9i to Oracle 11g - ORA-12154 error - I am trying to change our source database Oracle 9i to new database 11g. I pass the credentials through configuration...

Can I get a value from a parm's dataset that isn't the label or value field - Imagine I have a parameter on a report whose available values are based on a dataset. The dataset has three...

SQL Server 2005 : Data Corruption

corrupted mdf file - hello: i was given a corrupted mdf file, the server the database (sql 2000) crashed and the only file recovered...

SQL Server 2005 : Development

procedure - Could not find server 'SGEXP01' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

Store proc or trigger - I have a situation where, we have SQL Server 2005 Standard edition SP4 - Over 100 little table per customer - each table...

SQL Server 2005 : SQL Server 2005 General Discussion

sql traces capturing all the sp_reset_connections all the time - I am wondering what is going on with the trace? It is a production sql 2005 box Enterprise Edition 2005. The schedule...

Reg: Automatic Delete the three days older backup file - Hi All My requirement three days old backup file should be delete automatically.Crrently my backup plan not working properly.baechuse of...

Intresting Query - Hi All I am facing Following interview Question.Its very intresting. I have one table that table only two columns in...

SQL Server 2005 : SQL Server 2005 Strategies

Splitting DBs into multiple files for IO spread - We have a shared SQL server with a few hundred small DBs on it. Currently all the DBs consist of...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Query running slow with hard coded value and parameter value .....!!!!! - Hi All, we are facing issue while running query. when t-sql query run with hard coded value the sql result set...

SQL Server 2005 : SQL Server 2005 Integration Services

Best Strategy to Create a Million Files using SSIS - Hi All, We have a requirement to create a file for each unique record from the Database and we have nearly...

HOW TO READ A ZIP FILE WITH THE SAME NAME FROM MULTIPLE FOLDERS AND UNZIP THEM - Hi, Iam relatively new to bids.Following is the scenario where i need help. c:\archive has 3 sub folders c:\archive\1 c:\archive\2 c:\archive\3 1 ,2,...

SQL Server 2005 : T-SQL (SS2K5)

SELECT - CASE - GROUP BY - Hi I am new to this forum, so hopefully im in the right place and thank you in advance. Overview: An error...

Inserting data in one table as selecting from other tables - Hi all! I have a simple question. I've made a simple database in Access and upsized it in my SQL Server...

sp_send_dbmail as CSV - hi guys im trying to send a resultset via mail in a csv file attachment, everything works great but the...

SQL Server 2008 : SQL Server 2008 - General

Working with Management Studio Solutions - I like using SSMS Solutions / Projects, but there is one aspect of it that drives me crazy, and I'm hoping...

A function that will return user input - I need to use a function to return records for all classes with an enrollment of more than a particular...

Survey data structure - Boss want me to create a asp.net application for survey in which there are about 50 questions. Most question is...

Loopback address - Hi, I wanted to know what is the use of the Loop back address. What importance and when it is used...

XML PATH Concatenate Syntax - I use a nested query to concatenate row data for an aggregate. The syntax I use within the nested query...

The instance of SQL server cannot be enrolled with any other UCP - Validation is failing while installing Utility Control Point. I did install before and then removed for some reason but wanted...

Get record Counts & JOIN on sys.extended_properties - I need to get the record counts by modifying the following query to include the record counts. Would I just perform...

SSIS package load data from SQl to Mysql - Try to create a SSIS package to insert data from SQL to Mysql. The setup is as follows OLE DB connection to...

Listing all SQL Logins and excluding builtin logins? - I'd like to be able to list all SQL Logins. However there are some logins that are builtin such as...

Insert sp_spaceused output into temp table - Hi All, I need to insert sp_spaceused output of DATABASE into temp table. How can I do this? Pls suggest. Thanks

SQL SERVER 2008 R2 SP2 - SQL SERVER 2008 R2 SP2 is inculude CU1 TO CU5 fixes or all fixes of SQL SERVER

SQL SERVER 2008 R2 SP1 CU6 - Sql Server 2008 R2 CU6 is inculude CU1 TO CU5 fixes or only inculude CU6 fixes

Calculating average for 3 consecutive years - Hi, I have this data: 12/30/2012 0:00 Car ford focus 0 12/30/2011 0:00 Car ford focus 11 12/30/2010 0:00 Car ford focus 7 12/30/2009 0:00 Car ford focus 15 12/30/2008 0:00 Car ford focus 6 12/29/2012 0:00 Car ford focus 12 12/29/2011 0:00 Car ford focus 44 12/29/2010 0:00 Car ford focus 21 12/29/2009 0:00 Car ford focus 6 12/29/2008 0:00 Car ford focus 3 12/30/2012 0:00 Car Audi TT

Comma Field Parsing - Two questions. #1 How can I select without using the LIKE? #2 How can I get this type of output? Thanks 101 1 101 2 101...

SQL Tool that integrates into Notepad++ - I found a tool a year or so ago that integrated with Notepad++ and allowed the user to run queries...

compressing 3 distinct records to 1 - I'm not even sure how to ask this question so don't know how to search for this in this forum,...

Stored Procedure with a loop or something? - I need to make a stored procedure that looks at table 1 and if there is a row set that...

how to write a select query by passing @cattyp as parameter for this tables and here i am trying for rand() in this select query ? - here i am having two table @alubm and @images fom this two table in want to write a select query two...

Pivoting the table - I need some help in pivoting the table. The table is like this: [b]File Main Sub Text[/b] 1 1 A. hello 1...

Different query plans for same statement after changing 'Maximum server memory' - Different query plans for same statement after changing 'Maximum server memory' A select statement as part of a SP with join...

Trigger to rollback if no PK assigned - I've been tasked to create a trigger that will deny the creation of a table if no PK (Primary Key)...

Creation of dynamic sql query to create a select statement to pick a unique record - Hi All I am working on a search stored procedure for a website, I have following input parameters: [b]@Region[/b]: A region name [b]@Country[/b]:...

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,...

LSASS.EXE CPU Bloating - I've seen a lot on LSASS.EXE CPU and Memory Bloating. Since I'm having the problem in the context of SQL...

BCP fails to import value in ISO 8601 YYYY-MM-DDThh:mm:ss format - because of "T" - Can someone figure out how to make this work? bcp -version 9 and 10, both SQL Server 2005 and 2008...

SQL Server 2008 : T-SQL (SS2K8)

Index refresh stored proc - Hey all, I have a stored proc that is supposed to either disable (for ETL) indexes on a table or rebuild...

Latest Stored Procedure Used - How to find the latest used stored procedure.

UPDATE when the values are the same - Does an UPDATE query overwrite existing values if the already existing value is the same as the one your updating...

Using data set to pivot table - Ii have an urgent problem and I am thankful for any help. I do a query based on user-selected parameters and...

"UPDATE" Is Not Updating Correctly - I am executing the SQL Statements on SQL Server 2008 (SSMS). What am I missing? (I'm sure it's me, not the...

Applying query across Server. - Hi all, Suppose i want to perform operations on 2 different server. How would i do it. I am just curious...

What is meant by set based programming? - Hi all, I have come across various section on this website where it is mention that SQL Server is a set...

comma in SQL - I just found out we have a sql script to create a table. CREATE TABLE [dbo].[DayList]( [DateID] [int] NOT NULL, [EnrDate] [int] NOT...

Challenging TSQL Situation - I have a very challenging TSQL situation. I am dealing with a few million rows and have to modify a...

unique constraint - I have a new table. The unique value is PersonID+lateDate, it can be used as primary key. But we would like...

SQL Server 2008 : Working with Oracle

DB-Link SQL Server to Oracle OpenQuery Retuns a single row - Hi, I'm having an issue with getting openquery to work correctly. Im Using: SQL server 2008 connecting to Oracle 11g. Microsoft OLE...

SQL Server 2008 : SQL Server Newbies

Major problems copying database from one instance - Hi, Please can someone help here. I'm trying some things on some hyper v machines, and have come accross a...

Split one row into two rows using CTE?? - Dear All, thanks for help me from my previous problem. Now i have the second problem. I've search in this forum,...

Just starting first job - SSIS & SSAS - I graduated recently from college and will by starting my first job in Business Intelligence in October. I'm currently going through...

Help designing a patent database - I need some help with designing my database. I'm creating a database that holds patents. A patent has this data Patent number Title Classifications Inventors Filed date Issue...

SQL Server - GROUP BY clause - SQL Server - GROUP BY clause Hi there, I need your help. Here is my problem. I tried this query in dbms SQL Server...

Can't connect to sql server from an XP machine, but I can from win7 - Hi there, I've been given access to a SQL Server and I wrote a VBA script (in excel) to query the...

SQL Server 2008 : SQL Server 2008 High Availability

unable to reconfigure mirroring - We are seeing the following error message when we are trying to reconfigure mirroring. We backed up the principal database,...

Will suspended Asynchronous mirroring affect transactional replication - We have server in production which is SQL SERVER 2008 R2 and acts as Publisher in Transactional Replication(It has two...

SQL Server 2008 : SQL Server 2008 Administration

Registered Server Shows Stopped But is Available - In SSMS, I have one server that shows stopped but I can connect to it. It is a SQL 2008...

need some guidance on intermittent poor performance - I am having an issue with our ERP software. Our db platform is MS SQL Sever 2008 64 bit unicode. One...

Max Degre of Parallellism - Good Day, according to Microsoft Best Practices Analyzer we should set the Max Degree of Parallellism to an optimal value. I...

Control_M for SSIS - Anyone here in this forum use Control-m software to excute SQl agent jobs? We currently use SQL agent to run SSIS...

Restoring error: the tail of the log for the database has not been backup up - The restoring process gives me an error: "the tail of the log for the database XYZ has not been backup...

what is the Best way to run huge script on production server ? - what is the Best way to run huge script on production server ? (code drops) The script has several alter procedures to...

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....

Career : Certification

70-457 70-458 70-460 - Hi guys, I am planning to pass these three exams in the next couples of months. (Self-funded .... :( :( ) Any suggestion would be...

SQLServerCentral.com : Anything that is NOT about SQL!

Crazy Interviews - Reading the topic 'Stupid Interviewer Tricks' reminded me of an interview where I couldn't tell if I was crazy or...

Fantasy football 2012 - Only 2.5 short months to football, can you believe it? I'm not ready, hardly feels like it's summer time yet. You...

SQL PASS 2012 - Code for After-Hours Party? - Getting ready to register and wanted to see if the code I need to append to the registration is available? I...

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 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

filter IDs - Hi all, Can someone help me with this. I have some Ids which I want to filter from my report. I...

Database Design : Design Ideas and Questions

Confused about how to design a new process (third week into new role) - Good afternoon, I'm not sure if I'm posting this in the right section but I could really do with some advice...

Data Warehousing : Integration Services

need to display All the column from 2 different table - Hello I need to display All the column from 2 different table for example table 1 Column1 Column2 column 3 table 2 Column1 Column2...

DTS to SSIS Migration issue 32 bit 64 bit - Hi, We have to migrate DTS packages in a 32 bit system to SSIS 2008 packages in a 64 bit system. The...

How to do you determine the cache size in the case of partial cache in look up trasformation(64 bit) - How to do you determine the cache size in the case of partial cache in look up trasformation(64 bit)

Query regarding Look Up Transformation-Look up transformation fetching multiple matched records from ther reference table - Look up transformation fetching multiple matched records.More information as below 1. Input data comes from a table called Contact(ContactID is unique...

Data Warehousing : Analysis Services

SSAS for the DBA - replication questions - Hey all, Sadly, I know close to nothing about SSAS. I recently joined a new company and we're working on some...

Missing data from dimensions - I have a DSV with a filter. when I process that particular dimension, not all the rows are read from...

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...