In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Backup Pro "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.
 
Red Gate Cloud Services Schedule Azure backups
Red Gate’s Cloud Services makes it simple to create and schedule backups of your SQL Azure databases to Azure blob storage or Amazon S3. Try it for free today.
 
SQL Server Connections SQL Server Connections Fall 2012
SQL Server Connections will feature SQLServerCentral.com speakers Steve Jones and Grant Fritchey on October 30, 2012 in Las Vegas, NV at the fabulous Bellagio. Register now.

In This Issue

Advanced SSIS Workflow Management – Step 8 of the Stairway to Integration Services

In Step 8 of the Stairway to SSIS we look at more advanced workflow management within a package. More »


SQL in the City - Chicago 2012

A free day of training in Chicago on Oct 5, 2012. Join Grant Fritchey, Steve Jones and more to discuss, debate, ask questions, and learn about how to better run your organizations SQL Servers. More »


SQL Server Resources - A list

A great list of SQL Server resources that you can use to help you improve your knowledge or ask questions. More »


SQLSaturday #160 - Kalamazoo

SQL Saturday comes back to Michigan. Come see Jeff Moden and others talk SQL Server on Sept 22, 2012. More »


From the SQLServerCentral Blogs - Following good practice saves wasted time down the line.

TweetG’day, Sometimes I come across what I call unusual coding practices. Developers usually have pressure on them to deliver and this... More »


Editorial - Creativity

At Red Gate we have "Down Tools Week" a few times a year. This is the same idea as the ShipIt days at Atlassian or the 20% time at Google; it's a chance for employees to have free reign to work on some project, approved as being semi-business related, with the chance to present their work to the company at the end. I think it's a really cool idea, though I've yet to come up with an idea that I'd want to take a week off to work on.

When I look at companies and think about the time spent on various projects and efforts, I think much of it is useful, but I'm not sure it's critical. All too often we get caught up in the idea that getting project X done or finishing a piece of software will make or break a company. The reality is while the item might be important, most of the time it will be later than scheduled, and the company will survive. I'm sure there are exceptions, but they're rare. Most of the time things run late, which makes me question whether or not managers should be pressing so hard to get every project finished on time.

What if you shelved all work from your developers (or markers, business analysts, etc.) for a month? What if you let them think about what they could do to make the company better, or improve operations, or maybe just find a way to make their job easier?  Would they innovate or just waste time? 37 Signals did just that, taking the month of June off to let people that weren't running day to day operations work on whatever they wanted. They got a lot of ideas, some of which they are going to implement.

It sounds crazy, but if a small company can do it, one where everyone wears multiple hats, couldn't you do with with your department? I'm sure you could. Now if you can only convince management that "thinking time" can be just as important as the rest of your daily job.

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

Peter Piper picked a peck... 

Running the following query in SQL 2008 R2:

CREATE TABLE #tbl_Avacado (
  veggie VARCHAR(15)COLLATE Latin1_General_CS_AS NOT NULL
  );

INSERT INTO #tbl_Avacado
VALUES ('Pepper'),('PEPPER'),('pepper'),('p3pp3r'),('peppers');

SELECT veggie FROM #tbl_Avacado
  ORDER BY veggie;

DROP TABLE #tbl_Avacado

What shall resulting order shall be?

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

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


Yesterday's Question of the Day

A database is in the bulk-logged recovery model. I am inserting data into a table using bulk import. At the time the bulk-import operation started, this table has one clustered index and the table already has data. In this bulk import operation, data and index pages are minimally logged in the transaction log.

Answer: False

Explanation: If the table has a clustered index and is empty, both data and index pages are minimally logged. In contrast, if a table has a clustered index and is non-empty, data pages and index pages are both fully logged regardless of the recovery model.

Reference: Table Requirements for Minimally Logging Bulk-Import Operations

» 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

Database_Object_Last_used_details

I have implemented following stored procedure which is providing the object wise last used date of all databases which is available in single instance. 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

Where is log for cube process errors ? - Hello, When I process one of the cubes I get errors. Can anyone tell me where these "cube process errors" will...

Fix orphaned users created WITHOUT LOGIN. - So I have restored a database to a new server and a check reveals a DB-user being orphaned. However, this...

Deattaching a replicated subscription database - hi, what is the best way of get rid of the log file from subscription database. it is growing big in...

Help!... Memory Use on Windows 2008 R2, SQL Server 2005 x64. The physical is 98% - Hello to everyone, first... sorry for my english... I'm from Colombia. I have a situation that i can't understand, a SQL...

SQL Server 2005 auditing using event notifications - Hello! I'm trying to implement SQL Server audit using service broker and event notifications. Along with usual audit info such as successful...

SQL Server 2005 : Business Intelligence

SSIS Package Fails - Hi All, The SSIS Package Fails saying "The AcquireConnection method call to the connection manager "Excel_Source" failed with error code 0xC0202009" Here...

SSIS Deployment - Hi All, I need a suggestion for ssis package deployment. In my solution 100 ssis packages are there, these are...

Need some live scenario in SSIS - Hi All, I am new in SSIS. I need some scenario based in SSIS controls. Can any body help me to...

SQL Server 2005 : SQL Server 2005 General Discussion

BCP Error !! Trying to copy Data in Excel format but errors !! - Hi Experts, I am trying to fetch the data to excel format and getting the below errors. I think my SQL...

NEED SSIS,SSAS Coding Standard - Hi Frnds I am looking for Some Coding Standards, that we need to keep in mind for implementing MSBI Project. Any Standards...

Data Migration Options - We have SQL Server databases located on different servers for our hosted web application. Each database can house multiple 'customers' along...

SQL Server 2005 : SS2K5 Replication

Need to replicate multiple databases (publications) to one central subscriber - I was wondering if there is anyway to replicate mutiple databases (database with the same schema on different servers) to...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Execution plan - Hi All, I have 2 sql sevr 2005 instances with same build no,same edition. I restored the database from one...

Self referring join condition (tblx.col1 = tblx.col1) alters perf. - WHY? - Can someone explain [b]WHY[/b] adding the self referring join condition on acct.BillingType decreases the Estimated number of rows by a...

INDEX REBUILD - Here is what I did 1. Ran the following command ALTER INDEX ALL ON APPOINTMENTS Rebuild; 2. Ran the following query select index_id, avg_page_space_used_in_percent,...

SQL Server 2005 : SQL Server 2005 Integration Services

Issue with slowly changing dimension component - Hi all, First time i am trying with SCD component. Just a hands on practice to know about the component....

SQL Server 2005 : T-SQL (SS2K5)

Minimum Value 3 Fields > 0 - This seems like it should be easy but I can't seem to come up with a simple formula. Need the...

Switch rows and columns - Hey all, getting a bit of friday afternoon brain freeze ... Say I have this data: [code] CREATE TABLE #Test ( ID INT IDENTITY PRIMARY...

Performance of Joins over Updates - I was wondering if anyone knows which is faster, to build data for my reporting table.. To write new data using...

Filling Buckets - A customer had reported an issue with one of our stored procedures so I took a look and found that...

SQL Server 2005 : SQL Server Newbies

convert xls templates into rdl files - Hi, I'm given my report requirements (Labels, layout, columns, tables) as an excel template which I convert into SSRS reports. At...

column to calculate the balance stock dynamically - I have a table as shown below and would like to have the balance quantity which should be dynamically calucalted...

SQL Server 7,2000 : Administration

Decyphering waitresource - I have a spid that shows a wait resource in the form n:n:nnnnnnn . The first number is dbid and the...

SQL Server 2008 : SQL Server 2008 - General

display table in hierarcies - i want to display a table in Hierarchie. the thing is that the table dosen't have parent id or somthing like...

Maintenance plan versus 3rd party software - Hi all, We are about to replace our infrastructure, inclusiding backup and tape library. For backup and recovery, we will use...

Tempdb : ONE Data File per cpu - Good Day, w had an auditing team running Microsoft Assessment on our database servers . This product recommends that the principle of...

2008 and 2012 client connectivity on a same machine - Hello, I'm setting up new ETL servers and I might need to install both 2012 and 2008 clients. Because I can't...

Table Partition - Hi All, I have two filegroup named with primary and secondary filegroup. The secondary filegroup contains two files called Bet_2009.ndf and...

Incrementing a column without seting Identity property - [code="sql"] CREATE TABLE [dbo].[LKP_PARAM_TYPE]( [PARAM_TYPE_ID] [int] NOT NULL, [PARAM_DESC] [varchar](50) NOT NULL, CONSTRAINT [XPKLKP_PARAM_TYPE] PRIMARY KEY CLUSTERED ( [PARAM_TYPE_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE...

Permission issue or query issue - Hi friends, I have small doubt in below query. This query is use for zip the file and when i...

deleting data from partitioned table - We have a table that is currently partitioned on a date column. The data has been copied to a warehouse...

weird update statement - I have a table name Test_name.which has two columns full_name and nick_name.I have values only in full_name where as Nick_name...

Replication between different databases - Hi all, I was wondering if anyone has had any experience using replication in the description text below: Detailed below is a...

UNION Help - I have a database that contains a number of individual tables for each sales agents. I need to find a...

Upgrade to 2008R Collation - Hi All, I am waiting for project in whcih I need to upgrade sql server 2000 to 2008R2. The main...

Parameter error - Hi All, I am week in write the query. Below query i am executing for upload the data to s3...

ASYNC_NETWORK_IO wait type on stored procedure that has finished giving the results to the web application and that is blocking another stored procedure - Greetings, I am a developer and am not a DBA or a Networking Engineer. I am just a lowly Developer. Please...

Check that a specific Group By condition does not exist - [code="sql"] CREATE TABLE #tblBlocks ( TemplateID int, BlockID int, FieldID int, BaseStage bit ) GO INSERT INTO #tblBlocks (TemplateID, BlockID, FieldID, BaseStage) SELECT 1,...

Default Language Setting of Database - Hi All, Our default language setting for a DB is set to US-english , but our client insists now that it was...

Passing comma separated values for IN list in DELETE statement from SQLCMD - Hello, Following are the contents of script Delete_employees.sql. [quote]USE CompanyDB BEGIN TRAN DELETE FROM dbo.employee WHERE empid IN ([b]<Comma separated values passed from SQLCMD>[/b]) COMMIT TRAN;[/quote] [b][u]Question:[/u][/b] Will...

Insert Into temp table and then copy into 'real' table - Hi Chaps I have a stored procedure which is scheduled to run hourly - this stored procedure deletes all rows from an...

Script Out Database Mail Settings? - Anyone bother to have created a script to Reverse Engineer / Script out your EXISTING database mail settings? I set up a...

SQL Server 2008 : T-SQL (SS2K8)

Conversion failed when converting character string to smalldatetime data type. - Good Day all I have been working on a union query for a while, this afternoon i had to add a...

[Help Needed] Looking for a solution for this case - Hi all, I have a task which required me to sum value of all children level from bottom to top...

ALTER COLUMN fails when changing datatype of PRIMARY KEY - I am currently working on a transaction table that has an IDENTITY column of type INT, which also has a...

Adding GROUP BY to PIVOT operator (how?) - I am trying to show how many employees were hired in each department by startyear (2001, 2002, 2003) and then...

Looking for a set based solution to this data - I hope a better description is in my 3rd post below.

T-SQL Help Needed - CREATE TABLE Issue ( id int not null identity(1,1) primary key, name varchar(10) ) CREATE TABLE Jrn ( id int not null identity(1,1) Primary Key, issue_id int, created_dt datetime ) ALTER...

T-SQL help - I have two identical tables. Table 1 has 12,000 rows and Table 2 has 11,000 rows. I need to insert...

How to insert empty row - I need to insert empty row in a temp table within a stored procedure. The data types are varchar, varchar,...

days into completed weeks only - Hi how do i easilty convert days into weeks (obviously Days/7 will give me weeks as decimal) Problem i have...

output ve space while printing in sql server 2000 - alter procedure Goals_history ( @stuid nvarchar(22), @startyear nvarchar(22), @endyear nvarchar(22), ) as begin select name,class,noofsubject,rank,test from @tab1 union select name,class,subject,rank,test from @tab2...

Looping through table - Hi, I have to write a query and i am not sure how to go about it. I have a table...

Fetching values under condition - Hi to all I have a Query which is fix like SELECT StudentId,EnrolmentId,SessionId,SchoolId, Name, ClassGroupId, ClassId, ClassName FROM ViewStudentTable WHERE IsBlock...

rCTE vs LIKE for Hierarchy - Hi people, I'm tunning a database and i've stumbled by some hierarchy queries, on the good old form of ID,...

Importing only rows with column data in specific columns. - Hi all - I have an interesting scenario that i would like some input on. I have a requirement to import...

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

Reverse Of Number without Using reverse() - Hi, How will i reverse the number in SQL Server with out using reverse()

SQL Server 2008 : SQL Server Newbies

Regarding row size - Hello Masters, As per "Maximum Capacity Specifications for SQL Server" (for details "http://msdn.microsoft.com/en-us/library/ms143432.aspx") Bytes per row8 is "8060". That means each row...

Data file Shrinking - Hello Masters, How the shrinking works? What happened when shrinking log file or DB file ? on my test db (whose actual...

log_reuse_wait_desc column says LOG_BACKUP - what should i do ? - I'm using Sql Server 2005. When i tried to compile a stored procedure i got the below message The transaction log for...

CONVERT varchar (50) to Decimal (4,4), 2012 Server Express - Greetings, I'm about as newbie as they come. I am using SQL Server Management Studio Express 2012 on a Win7 machine. BACKGROUND:...

How do I fix Damaged allocation pages - I get this result from dbcc checktable DBCC results for 'RELATED_PARTY'. Msg 8946, Level 16, State 3, Line 1 Table error: Allocation page...

SQL Cursor Help - I need to amend the below Cursor to output into a table off which I can Query/join the output of...

What would the SQL2008 equivilent be to outer union corr? - I'm still trying to get the hang of SQL2008.... I've been using SAS and had no problem joining these tabes...

SQL Server 2008 : SQL Server 2008 High Availability

An error occurred while reading the log for databas - Hi there, My name is Leo, i am a DBA in my company. Nice to meet you all in this forum....

Replicated table does not show words with accent - I have a database with transactional replication, more or less 15 days replicated table does not recognize stress, obs. the...

Using High Availability to move to a new server? - We have an existing windows 2003 server with sql 2008 r2 enterprise. Its part of a Hyper-V configuration. A new windows...

SQL Server 2008 : SQL Server 2008 Administration

Wait Stats Investigation - Hi All I'm using the following script to assess my highest wait_types ona fairly new SQL Server (+- 2 weeks) [code="sql"] SELECT TOP 15 wait_type...

2008 indexed view r2 standard edition - Not to be confused just to clairfy. i have a 3rd pary db which our warhouse team goes after. Their extract...

SQL Jobs monitoring tool - We have a large number of jobs running on SQL DB servers. Is there a tool to find out what...

Auto encryption of new databases - I am using SQL 2008 R2 Enterprise with database encryption. Performance is fine. I just wonder if there is a way...

Memory Leak SQL 2008 R2 64 bit? - OS - Windows 2008 R2 64 bit (no service pack installed on operating system level) SQL - SQL SERVER 2008 R2 64 bit...

collation issue when upgrade to 2008 R2 - Hi All, I am waiting for project in whcih I need to upgrade sql server 2000 to 2008R2. The main...

Single user mode during upgrade to SQL 2008? - IN place method for the database upgrade SQL 2000 to 2008, Can I go for below one of the steps? first database...

Cumulative Update #7 fails - I've downloaded CU7 for SQL 2008R2 SP1, KB 2703282, and attempted to install it on test instances. Only one was successful,...

SQL Server 2008 R2 SP1 Install failed - Hi for some reason my install of SP1 crashed and I cant re-run the installation again. it gives me error...

Job Failed. - While i was starting the job i got following error. JOB RUN: 'Full OnlineLogging' was run on 9/13/2010 at 3:03:52 AM DURATION: 0 hours,...

SQL Agent - SQLSTATE 21000 error - I have found weird error in my SQL Agent which is produced about every 30 seconds. The message reads as...

Career : Certification

70-461 - Hi, Id like to take this exam and looking for some good prep.. My T-SQL skills are basic at present...

SQLServerCentral.com : Anything that is NOT about SQL!

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

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

Counting Rows on a LookupSet Function in Reporting Services - I have two data sets. - Data1 contains a column (Tech) that has ID's for technicians. - Data2 contains a column (Tech)...

Expand Drilldowns automatically when print report - Hi people, do you know a way, when a user prints a report, to expand automatically all drilldowns? All this in...

IIf statement!? - Hi people. I need to customize a textbox on my report: if the field value = 'True' then write 'Allowed' How do I...

SSRS Chart issue..Bars dont show up but data does - I have created a column chart in which the data is showing up properly on the x and y axis...

export report to excel and excel should create formula fields for the sum fields of the report - When I export a report to excel and my report has sum fields, I would like excel to create the...

Data Warehousing : Integration Services

Need to implement the Business rule row by row - Hi, How can we agonize,Row by row like cursor in SSIS, what controls will be using, Here am reading data from...

Integration Services contain Analysis Services Processing Task. Fails but works in Analysis Services - The dimensions have processed Successfully prior to the updating of the SEN Cubes The attribute key cannot be found when processing:...

Data Warehousing : Analysis Services

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