In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Backup Are you sure you can restore your backups?
Run full restore + DBCC CHECKDB quickly and easily with SQL Backup Pro's new automated verification. Check for corruption and prepare for when disaster strikes. Try it now.
 
Deployment Manager NEW! Deployment Manager Early Access Release
Deploy SQL Server changes and .NET applications fast, frequently, and without fuss, using Deployment Manager, the new tool from Red Gate. Try the Early Access Release to get a 20% discount on Version 1. Download the Early Access Release.
 
SQL Skills Deep technical training by world-renowned experts in 2013.
You can't get better ROI for your training budget. Read more.

In This Issue

Control Flow Task Errors – Step 9 of the Stairway to Integration Services

Step 9 of the Stairway to Integration Services looks at control flow tasks and error handling. More »


SQL in the City - Seattle 2012

Start the week in Seattle off with a free day of training on Nov 5, 2012 with SQL in the City. Grant Fritchey, Steve Jones and more will be talking SQL Server in the Pacific Northwest. Join us and debate and discuss SQL Server the Red Gate Way. More »


Improve Your Database Unit Testing Skills and Win Free Stuff

As the SQL Developer community grows to embrace the benefits of test-driven development for databases, so the importance of learning to do it properly increases. One way of learning effective TDD is by the use of code kata – short practice sessions that encourage test-first development in baby steps. I have a limited number of licences for SQL Test to give away free – just for practicing a bit of TDD and telling me about it. More »


Editorial - Dog Food

Chuck Wagon Dog FoodThis editorial was originally published on Sep 28, 2007. It is being re-published as Steve is out on the SQL in the City US 2012 tour.

I saw this article showing Sun bragging about their green data center, and being someone that is interested in alternative energy, paying attention to the environment, and conserving our use of natural resources, I thought this was pretty cool. I remember seeing a similar announcement from Sun years ago when they finally moved off a mainframe (or mini) for their internal financial and administrative systems and onto their own boxes (Solaris or SunOS).

This has been an interesting week here at SQLServerCentral.com. We released a new version of our site last Saturday and have been dealing with complaints, bugs, and even a few complements throughout the week. We are working through issues and trying to tailor our site to the needs and wants of the community. I'm not drifting off track here, so bear with me.

I've had a few people question whether we did much testing on the new site, or if we did, how could there be so many bugs. I feel a little like Microsoft employees must feel after spending a long time working on a product and then having people tell us that we didn't do a good job. We worked on the final product long and hard and there was lots of testing.

However testing doesn't really cover all the issues with the software. Things like search "work", but they just don't work the way many of you want them to work. That's because you have a different usage pattern than we do and than our testers. So with that in mind, I have an interesting poll:

Should software companies "dog food" their own products?

It's easy for Microsoft to do it. Everyone uses Windows, so they can just slip in new versions, people do their daily job and report on issues. Dell can run their own management software in data centers, and Google can require everyone to use Gmail and Google Reader.

But what if you're Adobe?

Most of your people probably don't edit images or build movies and it isn't really practical to pay the network guy to do that. And you might get him trying things, but not really "using" the product.

So I'm kind of wondering what makes sense to you or what flaws you see in "dog fooding" things. I know at Peoplesoft we ran their platform internally, but no one really seemed to care about our complaints in using the product. At End to End Training, where I'm a founder, we built our own platform to support user groups and ended up running it ourselves as the first "customer." As much as we appreciate feedback from other groups, we've learned a lot internally as wll.

If you build commercial software, do you run your own products? Does it make sense to do so? Let us know what you think.


I'm getting into the podcasting business (maybe) and there's a link below to the audio cast of today's editorial. Comments (good and bad) are welcome.

Dog Food (Podcast 3)

Music for today's Podcast from:

Incompetech.com

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

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


Question of the Day

Today's Question:

When I execute the following T-SQL

SET ANSI_PADDING ON

DECLARE @a VARCHAR ='xyz'    

SELECT 
  LEN(@a) AS 'Declared'
, LEN(CONVERT(VARCHAR,'xyz')) AS 'Converted'
, LEN(CAST('xyz' AS VARCHAR)) AS 'Cast'

What values are returned (returned values are in Declared, Converted and Cast sequence). For example: 4, 5, 6

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

This question is worth 1 point in this category: declare. 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 Backup and Restore

A DBA's tasks, from day-to-day, are rarely constant; with one exception: the need to ensure each and every day that any database in their charge can be restored and recovered, in the event of error of disaster. In this book, you'll discover how to perform each of these backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Red Gate's SQL Backup tool.

Get it from Amazon in print  or download the ebook for free from Red Gate


Yesterday's Question of the Day

What 4 values are returned by the following:



DECLARE @Date1 DATETIME = '2012-08-28 11:53:00'
      , @Date2 DATETIME = '2012-08-29 13:25:00'

SELECT 
  DATEDIFF(day, @Date1, @Date2)
, CAST(@Date2 - @Date1 AS INT)
, CAST(@Date2 AS INT) - CAST(@Date1 AS INT)
, CAST(CAST(@Date2 AS FLOAT) - CAST(@Date1 AS FLOAT) AS INT)

Answer: 1,1,2,1

Explanation: When CASTing a DATETIME to INT, times after 12:00 are rounded up to the next day number. All others return one as you'd expect, where CASTs truncate the result.

REF: http://msdn.microsoft.com/en-us/library/ms187928.aspx

» Discuss this question and answer on the forums

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks.

Get your copy from Amazon today.


Featured Script

Aggregating Intervals

create intervals from effective dates and aggregate like and adjacent intervals 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

Indexes question - Hello everyone, In our datawarehousing environment, every morning indexes are dropped and then data is loaded into the database and finally...

Differential backup failure - Hi Team, I am facing an issue with differential back up . The differential backup is failed only on sunday . we...

DBA Interview Questions needed - Serious only please. - Hi dudes and dudettes... The boss and I are conviced that we need a dba since the 3rd party firm providing...

DB shrink and backup error in sql2008 - Hi All, kindly help me. MS SQL server 2008 SP1, And While tried to shrink the same DB error occured as follows Msg 0,...

SQL Server 2005 : Backups

Odd Filegroup backup behavior - We have a particularly large database that is partitioned across multiple filegroups. We have nightly file group backups on the...

SQL Server 2005 : Business Intelligence

MDS ERROR - Executing SSIS package i am getting following error. [Execute SQL Task] Error: Executing the query "exec proc_BusinessRulesValidation @UserName = ?, @..." failed with the...

how do i import data from sap bw - Hi How do i import data from SAP BW into SQL I need this for a anytical tool [url=http://www.crgsa.yolasite.com]tableau[/url] Regards

SQL Server 2005 : Development

sqlmail timout connection pool - I am using an SQL Job to call a stored procedure which queries the database to get some email addresses...

SQL Server 2005 : SQL Server 2005 General Discussion

Why is there an implicit conversion between two varchars - I have some SQL (SQL 2008) that I inherited and am trying to find out why some of queries are...

SQL Server 2005 64 bit vs 32 bit - Hello to all. I have installed SQL Server 2005 Developer Edition and Visual Studio 2005 Standard Edition on my Windows...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Temp table for Performance Impact - Hi, We are using the table variables inside the sp. It is working fine at QA and staging server. When It...

SQL Server 2005 : T-SQL (SS2K5)

how to avoid duplicate business logic - Take the following example: [code="sql"]select * from tbl_Users U where U.active = 1 and U.deleted = 0[/code] It seems that the application owner keeps changing...

Looping through a data set and combining values - I have the following table [b]Table1[/b] EmailAdress Msg email1 blue email2 black email1 white email1 orange email4 red [b]Table2[/b] Email Body email1 blue,white,orange email2 black email4 red My goal is to go though the messages in...

create variable for IN statement - hi i have a query which i need to group some codes into one code. so... [code]case when ProdGrpCode in ('500','510','5201','580','630','460','470','480','490') then '82' ....[/code] is...

Bulk Insert - suddenly getting issues with EOL chracters? - Hi all Apologies if this subject has been done to death - I'm going round in circles reading threads on here and...

SQL Server 7,2000 : Administration

Reduce INITIAL SIZE of TEMPDB - I accidently made the INITIAL SIZE of my TEMPDB much too large. Now when I try to decrease the size,...

Cannot delete backup file; access is denied - Our dev database server is Windows 2000 SP3.  Every so often we hit this error message in a maint plan...

SQL Server 7,2000 : In The Enterprise

SQL 2008 x64 clustering active / active - Hello all, I've been trying for days now to get my new SQL Server x64 Enterprise cluster online in an...

SQL Server 7,2000 : Replication

How to run snapshot agent automatically - Hi Experts, I have done successfully snapshot replication. But I have to start snapshot agent manually. After manually starting, replication from...

SQL Server 7,2000 : T-SQL

SUBSTRING And CHARINDEX to find start/end of string - Hi all, I have a big long text string that can vary in lengh and contrain audit information about data that...

SQL Server 2008 : SQL Server 2008 - General

Data-Modeler-Tool - Hello, can anyone give a recommendation for a data modeling tool that ER data modeling. allowed with SQL Server, and change...

Specify schema on table name - Hi, I can across this link on the net [url]http://www.sommarskog.se/dyn-search-2005.html[/url]. Somewhere there's this: [quote] On lines 25-28 there is something very important: FROM dbo.Orders o JOIN...

Cross joining one table to find duplicates - I have been working on cleaning a database with addresses in an attempt to dedupe it. There's a lot of...

mtd, yesterday and previous month data in 1table - I am trying to get mtd, yesterday and previos month data in 1 table. Can someone please help me..

Questions about extended events - Hey all, I am trying to learn more about extended events. I have been using SQL Profiler to help me troubleshoot...

how to create a database with Windows credential - I was assgined a windows credentials (a user name and password), which only has the permission create a database. But how...

Simple LEFT JOIN question - I've got two tables Curriculum and UserCurriculum. I want to list all classes for each user and show nulls where...

Enforcing data integrity, preventing over-lapping date ranges - Hello All, I typically have join tables where I maintain the current and historical relationships between different entities here represented by...

Help needed with a query (Cursors+union+pivot?) - Hello, I've been trying to figure out how to solve this for a while, with no success. I have 2 tables: 1) QUESTIONS ID...

Protection from SQL Injection with dynamic SQL using SQL 2012 ? - Instead of this: --exec (@Query); I am using this: DECLARE @QueryCondition nVARCHAR(4000) DECLARE @ParmDefinition1 nVARCHAR(4000); SET @ParmDefinition1 = N'@fromtoRec nvarchar'; DECLARE @ParmDefinition2 nVARCHAR(4000); SET...

What SQl Agent Jobs Should I set up? - Hi, What are the most common/best SQL Agent Jobs to set up? The ones I generally set up are as follows:- (Daily...

AWE and PAE both should be enable on Windows 2008 R2 32 bit? - Hi Operating system - Windows 2008 Enterprise Edition 32 bit. SQL SERVER - MS SQLSERVER 2008 R2 32 bit. Physical Memory - 8GB whenever enabling AWE option...

Why does this take so long! - Hi Everybody This is my first posting in this forum, so please bear with me if I should get some thing...

SQL Server has encountered occurrence(s) of I/O requests taking longer than 15 seconds to complete - I have a few SQL Server 2008R2 SP1 and SP2 as well as a couple SQL Server 2008 SP3 getting...

Optimize query needed - Hello Expert. I need to convert data from an existing column from BigInt to VarBinary type. I wrote the function to...

Operating System error 665 on SQL 2008 R2 - I am getting this error : Operating system error 665(The requested operation could not be completed due to a file system...

IDENTIY COLUMN Property behaviour - Hi All, I have a table with IDENTITY(1,1), it will generate a sequence number. How SQL Server is generating these sequences? Thanks,

Problem patching to 2008 SP3 - I have a 2008 enterprise 32 bit instance running on Windows Server 2003 Enterprise server. I'm an administrator on the...

Changing db owner script - Hello to all, Thanks for your time and hopefully this wont be too difficult to answer. I need to change 30-40...

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)

TSQL Case Statement help - Hi All, First off im a TSQL Novice...so help is needed and much appreciated within several tables i have a job type...

Using MERGE for Audting - Hi I'm looking at using Merge which I've never used. I've achieved Auditing before with an After trigger that writes...

SQL Query to select one row and fill missing columns from another matching row. - I have a table with the following structure and need to retrieve only record from the table: Table: Person Records: Id, Name, MartialStatus,...

SQL Server + Mgngmt Studio Express on the top on VS2010 Pro - Hi, I just installed VS 2010 copy I got from college, I know that it comes with SQL Server Express and...

query window in ssms - I recently found when I use query window in SSMS, when I close the tab, it didn't ask me if...

Get multiple rows of data based on one condition and grouping - Here is my scenario along with the SQL commands, USE dbo_test --Create a sample table GO CREATE TABLE [dbo].[CustomerPurchase]( [PurchaseDate] [datetime] NOT NULL, [CustomerID] [int] NOT...

Multipe letters into one - All, I have read and asked lot of questionss about "Multiple space into one" here. I just thought the below scenario...

Dynamic Filter and Order By - Hi, I'm doing some testing with dynamic filtering and order by clause. I'm comparing dynamic SQL vs using the CASE statement... [code="sql"] DECLARE @FilterName...

find increase/decrease amount - i want to find the increase / decrease amount group by sector and month. --input table declare @t table ([id] int,[Amount] int,monthno int,Sector...

Best way to effienctly store hugh amounts of data - Would be interested to hear on some of the best ways to approach this situation. I've recently started at a company...

varchar to time or datetime or ARGGHH Please help me. - I have a datetime (your standard yyyy-mm-dd hh:mm:ss) column and a varchar column that holds an appt time that looks...

Multiple Space into one + 65,33,17,9,5,3,2 - All, I have recently read Jeff Moden's Multiple space into one space article and i saw M's reply on this topic. He...

Get the overall status of a particular product - Hi, need some help to write the query as i am not sure how to achieve this. I am having a...

DB Last Used Date - Hi Forum, As we are on the way to cleaning Production server, we need to find out the last usage date...

SQL Server 2008 : SQL Server Newbies

INVALID OBJECT NAME - Hello, I have wrote one store proc. So, in the stored procedure, it is using one table which is in the...

How to eval @Variable when assigned from SELECT field? - Hi, In code below, how can I evaluate a declared variable if no valid data is returned from attempt to...

Query Help - Hi, I need some help in writing a query for this below condition. Source Column: A.BLEI_CK Target Column:B.BILL_ENTY_SK IF A.BLEI_CK is NULL...

How can I count the number of occurances of a string in an ntext column for each record? - Dear SQL Gurus: I have a table [tblHTML] with an ntext column [strPage] that has HTML in it. Each record's [strPage] field...

Need to process different file types in SSIS - I have what I think will be a simple question. I need to have SSIS loop through a folder for...

SQL server 2008 Error handling, error Logging and Restartibility - Hi, I need to apply error handling in SSIS packages which used DFT (Only Source and destination) and Execute SQl task...

Using Access to put data into SQL Server - Hello everyone. So I have a database in SQL Server 2008 that contains 5 tables. I have connected Microsoft Access...

F5 to refresh - Hi, need some help. On one server when I open Management Studio and click on something in the object explorer and...

SQL Server 2008 : Security (SS2K8)

View Records That Are Associated With a User Table - I would like to find a way to setup a user table with logins (UserID) in one column and a...

SQL Server 2008 : SQL Server 2008 High Availability

sql server agent jobs history - Hi Team, SQL SERVER Agent jobs subplan_1,Plan_2, Plan_3 .. Etc i m not able see jobs history could you please suggest...

SQL Cluster question - Hi all, I need to know the following if it is possible or not I have a SQL 2K8 R2...

Script to Backup SQL Server Log Files - Hi All, Need assistance in creating a script for the below requirement. Any assistance to satisfy this requirement will be greatly...

OS Error when configuring Log Shipping - I am attempting to set up Log Shipping, and after going through the configuration wizard, I continually get the following...

SQL Server 2008 : SQL Server 2008 Administration

invisibel mssql 2008 r2 express server - Hi all! I have installed the total package with both sql server and management. When i try to use the management, it...

Alias for SQL server Instance - Hi I have installed SQL Server 2008 (F1SQL) with two named instances (DEV, QA) and this server is configured for...

Moving existing indexes to new filegroup,sql server 2008 - Hi, Can anyone of you please guide me how to move indexes (Primary filegroup) to new Index filegroup. I have done for...

DBCC CHECK DB ERROR - When we run DBCC CHECKDB on one of our production Server we get the below error.. Error:--The database could not be...

Customizing Rendering Extension Parameters in RSReportServer.Config - Hi, I am trying to add an additional extension based on CSV but to have a file extension TTX instead. I have...

Mgt Studio can see & login to other servers but not (local) - Built a new Windows 2008 R2 x64 server, and installed SQL Server 2008 on it, as a test version of...

Programming : Service Broker

WMIEventProviderNotificationQueue - Hi all Being new to Service broker I am just trying to understand a couple of points. I am using WMI...

Programming : Powershell

Power shell - Hi guys, I am new to powershell, can anyone please help me in undertanding powershell. I googled about powershell. But...

SQLServerCentral.com : Anything that is NOT about SQL!

Ryder Cup 2012 - So, the Ryder Cup's just started over at Medinah... Any golfing DBA's on here & will you be following the competition over...

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

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

SQLServerCentral.com : Editorials

Remote DBAs - There's still hope for all you DBAs out there looking for a telecommuting job that allows you to work in...

Reporting Services : Reporting Services

Changing filters using custom code - Hi I have a report which contains around 20 separate matrix tables. The tables all contain fllters one of which specifies...

Reporting from multiple tables at once - I have a weird situation... I have multiple tables which are basically the same in structure, and I need to...

Compare dates & Using Indicators - Hi geniuses! I need to compare dates (proj.last.publication (field) and current date) of various projects and depending of the result, expose...

Cascading Parameter of type DateTime does not work? - I have a normal integer parameter to allow the user to select a preset date range like this value, label 1,...

SQL Merge 2 Databases - Hi geniuses! I need to merge 2 databases to colect information for my reports. Have really no clue about how to...

Data Warehousing : Integration Services

accessing connection managers in script tasks - Hello, I'm trying to use an OLEDB connection in a script task. There's a fair amount of documentation on this topic,...

What is the best way to create a “flat file connection” to a password protected folder? - We have a password protected Samba drive we need to retrieve our ETL flat files from. Currently we are running a...

Windows batch file executions within SSIS fail when run from SQL Job Agent - Windows batch file executions do not fail within SSIS when SSIS run using DTExec The account we are using to execute...

Error on package not sure how to find error - Hi, One of my packages is failing and not sure why? What is the best way to find out what is causing...

Access to the path is denied. - HI All, I have designed an SSIS package for the below mentioned requirement. I receive flat files like csv files on daily...

Data Warehousing : Analysis Services

Help in combining 4 different MDX queries - Hi guys, I am new to complex MDX queries I have managed to write these queries but now i need to combine...