In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL DBA Bundle logo ‘Beating Backup Corruption’
The DBA Team are back, revealing more of their ‘Top 5 Hard-earned Lessons’. Lesson two is out now! Learn to beat backup corruption.
 
SQL Toolbelt Want to work faster with SQL Server?
If you want to work faster try out the SQL Toolbelt. "The SQL Toolbelt provides tools that database developers as well as DBAs should not live without." William Van Orden. Download the SQL Toolbelt here.
 
SQL Backup Pro logo Can you can restore your backups under pressure?
Use SQL Backup Pro's fully integrated DBCC CHECKDB to verify your backups, so you can restore them when it matters most. Download a free trial now.

In This Issue

T-SQL insert using SSIS Data Pump

This article describes a T-SQL procedure to insert data using the SSIS Data Pump More »


SQL Intersection in Las Vegas This Spring

Come join Steve Jones, Grant Fritchey and an amazing lineup of speakers at the spring SQL Intersection conference in Las Vegas, April 8-11, 2013. Register now and you get a Surface RT tablet. More »


Partitioning in SQL Server - Part 3

Arshad Ali provides a step-by-step guide to create a partitioned table/index. More »


From the SQLServerCentral Blogs - Transactional Replication Toolbox Scripts: Show Articles And Columns For All Publications

During the last few years I've worked extensively with transactional replication and have written a handful of scripts that have... More »


Editorial - The Cloud in Large IT Shops

I've seen many presentations and talks from companies that are using cloud services to replace traditional IT infrastructures and lower their costs. Often these presentations are from smaller companies that don't want to hire an IT administrator, or buy server or learn how to host and manage that hardware. Plenty of small companies would prefer that each employee manage their own laptop and nothing more.

However many of those strategies don't match the situation for large companies. Once you've hired an IT staff and made an investment in hardware and facilities, can the cloud really help you? I had my doubts, but this article about Toyota makes me rethink those doubts, or at least many of them.

The lesson from the article, for me, is that Toyota's IT group is learning to be a lean part of the business; they are building applications and tools that internal employees and customers can use to work better. They're not acting as a separate business that needs to build software and also manage and administer the platforms that run those applications. By building software for Toyota and its customers, and outsourcing other functions, the Toyota IT group is more focused.

Will this work in the long term? Will there be security issues from hosting email and other applications? Possibly, but I'm not sure if the problems and issues they encounter will be any worse than those problems that might come from managing all the hardware themselves.

I still think the idea of container data centers, running cloud platform software, makes sense for large companies, but I suspect that even if they host an application like Salesforce.com, the administration of the software will come from Salesforce and not internal IT server administrators.

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

Advertisement: If you are looking to speed up your development process and reduce mistakes with Continuous Integration, you might be interested in these white papers on automated deployment and CI for databases from Red Gate Software. It talks about how you can set up a process using various tools.

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:

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

You run the following code in an SSMS query window in SQL 2008, 2008 R2, or 2012. The SQL server and SSMS are default installations (language English(UNITED STATES), collation Latin1_General_CI_AS) with default configuration.  The database PLAYPEN exists and its default collation is the server default.

declare @k int;
select
  case when @k=0 OR NOT (@k = 0)
    then cast(0 as datetime)
    else cast('2012-04-01T10:00:00' as date)
  end as [when] ;

What value does the select statement deliver for column [when]?

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.

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.


Yesterday's Question of the Day

Will both of these queries will return the same result or not?

SELECT DATEADD(YY,114,0)

SELECT DATEADD(YY,0,114)

Answer: No

Explanation: They will not. In the DateAdd function the second argument (number) is added to third argument (which should be a date). In first query 0 will be converted to default date and then 114 is added to year part, but in the second query 114 is converted to a date and 0 is added.

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

» Discuss this question and answer on the forums

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.


Featured Script

Powershell - Export Stored Proc results to CSV file

This Powershell script calls a stored procedure and exports the results to a CSV file 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

Lot Of Blockings in production server - Hi, one of our production server we are getting lot of Blockings i found the blocked process i fired below queries i...

SQL Agent Job Sudden Failure, Login failed for user NT AUTHORITY\SYSTEM. - I have a scheduled job (runs every Friday at 5am) that has been running since 9/2011. Suddenly, the job failed...

Can't start SQL Server service as Network Service - I have a development server that is running SQL Server 2005. I was asked to install a new instance for...

collation conflict between "Latin1_General_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. - Hi, We have sql server 2005 reporting services on server A & its databases on Server B. I'm seeing the below error...

SQL Server 2005 : Business Intelligence

How to pass parameters and LogOn Information from ASP.NET (C#) to Report - Hello, I was using Crystal Reports earlier and had the code for opening the Crystal Report from my ASP.nET 2.0 (C#)...

SQL Server 2005 : SQL Server 2005 General Discussion

Copy a Databank to another SQL 2005 Server - Hello I try to copy a databank form one SQL Server 2005 (on Windows 2003 x86) to another SQl Server...

help needed for sql query - I am working on sql 2005. I have a table wherein it maintains the history of status Customer Status CreatedDate LastUpdateDate CustA...

SQL Server 2005 : SQL Server 2005 Strategies

Database mirrioring Error (Microsoft SQL Server, Error: 1418) - Hello All , Can you please tell me about database mirroring configuration setup I have created principal and mirror servers and before...

SQL Server 2005 : SS2K5 Replication

Editing replication stored procedures - Just wanted a high level answer really on whether or not it is a good idea to alter the sql...

Subscription expire duration - Hi, I would like to know if I change the no of days in publication properties in General tab "Subscription...

SQL Server 2005 : SQL Server 2005 Integration Services

DTExec succesfull in CMD window, fails in Agent Job - I am tasked with processing an excel file and the file is password protected. To do this, I have a...

SQL Server 2005 : T-SQL (SS2K5)

XML Column Read - Hi, I have a column in table with xml type. I want to extract it row wise but it is...

some one please help me to read this xml-data from a XML variable - HI, I have a @xml variable, I need to read this variable and to insert the values into my table in...

Database Design question regarding Normalized Tables - Hey all, Got a theoretical question for you guys. Let's say I have a table that contains information about a store....

Reporting on SQL Agent history in 2005 - Hopefully this is the right forum. It's fundamentally a T-SQL question. I'm trying to make a report that is useful for...

SQL Server 2005 : SQL Server Newbies

How to add two column and the to put the result on the next row? - [center]column1--- column2--- result 50 ------ 1500----- 1550 100----- - 1550----- 1650 40----- - 1650----- 1690 30----- - 1690----- 1720 Now I do have the value...

SQL Server 7,2000 : Backups

SQL Express Backup - I'm new to SQL and we have an SQLExpress database that we need to backup on a nightly basis. Is...

SQL Server 7,2000 : General

Scroll bars missing in some data tables - We are using the program MP2 with SQL Server 2000. We've been using this program for many years. Several months...

SQL Server 7,2000 : SQL Server Newbies

How to Join the two table - Table 1 A B C D F A1 B1 NULL NULL NULL A2 B2 NULL NULL NULL A3 B3 NULL NULL NULL A4 B4 NULL NULL NULL A5 B5 NULL NULL NULL Table 2 Loc Name A1 White A2 Black A3 Red A4 Orange A5 Blue B1 Green B2 Yellow B3 Gold B4 Rose B5 Silver

SQL Server 2008 : SQL Server 2008 - General

Relying only on VM snapshots as a backup - Hello, Right now we have a 2 SQL data warehouses running on a VM server and we are currently relying only...

Using the OUTPUT statement for data change logging - In my stored procedures that change data, I include data change logging. It logs to a table the Table name,...

How to use Select within select in the same Select statement..?? - [font="Courier New"][size="3"]Hi All, I Have table where it includes fields model_size, model_type, actual_qty, process_qty, order_type. Now I want to display all...

Upgrade sql 2005 to sql 2008 r2 - successfully done upgrade sql 2005 to sql 2008 r2, now the same server both database is running i want to...

sqlio tool in sql server 2008 - Hi, Please help me how to handle sqlio tool. Regards Sivakumar.T

concurrent DBA connected to same database - how many DBA currently accessing same database ? how to check

drive not accessible in sql, available via windows - sql 2008 in a cluster. added drive via iscsi and it is available properly in the cluster. it is also...

SBSMONITORING MEMORY AND SPACE HOG - I am writing program in VB.NET 2010 Pro to SQLEXPRESS Database for a small service . They are using sbs 2008...

help on group by with Join - I have two tables. One has records for transactions, another has records for purchased products. Transaction table has TransactionID, TotalAmount,...

Convert varbinary to integers and insert - Hi, I have a 28 integers stored in a varbinary that is passed to a stored procedure. The SP needs to...

my queries are too slow when SET FORCEPLAN OFF - Hi All, I am using an reporting tool for my dwh system. My tool are generating queries and those queries are too...

Query to sum only few values in a same column - Hello all, I am kind of new to sql server and I am stuck on this. It may sound stupid and...

Full & Simple Recovery Model - what is the standard way to put the database Full recovery model OR Simple recovery model?

Remove Cursor - Hi, I have a script from which I have to remove the cursor: DECLARE @AsOnDate SMALLDATETIME DECLARE @FlagProduction AS BIT DECLARE @tblAcAging AS...

Design Question - I seem to be struggling with something that I thought was fairly straight-forward originally when I started designing the database...

using replication in sql server 2008 r2 SE (OS: windows server 2008 R2 SE) - We are implementing a new architecture for one our modules. There are 100 tables in the database and out of...

Advices for best practices for configuring MS SQL Server system databases? - Hi, I need some advices and best practices regarding the size of MS SQL Server system databases. We use MS SQL...

Create a Column That holds the SUM of values in the same field where The Invoice Number is the same - First, sorry for the confusing title. I am looking for a way to sum the values based on the customerid and...

Anyone recomend a good SSAS 2008 Book? - Hi All, Can anyone recomend a good SSAS 2008 Book? Preferably something that include the basics of designing from scratch, and either...

MDS DATA - Hi all, I think this may be a basic question.But still i'm not clear about this.. If i'm using Master Data...

SQL Login error from client computer - [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. I can not connect to the SQL from the Other PC which...

SQL Server and MS Access, and unwanted Users and Schemas oh my.... - I've noticed something recently, and was wondering a) what caused it, and b) what can i do about it. We have...

I need to read the xml which is passed as an variable in stored procedure ?how to achieve that ? - I need to read the xml which is passed as an variable ,and insert its values to temporary table @msgchunk...

logical consistency-based I/O error - Hello, when i want to see properties of one data base ,or run maitenancetools or backup sqlserver give me this...

What Gives Better Performance? - Hello Everyone I hope that everyone is having a Fabulous day. I was in a discussion with a DB2 developer that has...

Having Problem with Calculate TimeStamp within same column. - [code="sql"] CREATE TABLE [t_Appointment_TimeStamp]( [ApptTimestampID] [int] IDENTITY(1,1) NOT NULL, [ApptID] [int] NULL, [TimeStampId] [int] NULL, [TimeStamp] [time](7) NULL, CONSTRAINT [PK_ApptTimestampID] PRIMARY KEY CLUSTERED ( [ApptTimestampID] ASC )WITH...

SSRS Smart TV - Good Morning Everyone, I have a fairly odd question today as you may have guessed from the title :-) I've been asked...

backup job using sql server agent in sql server 2008 - Hi, i have two systems(system A,system B) connected in network and with sql server 2008 installed on both. i want to take...

SQL Server 2008 : T-SQL (SS2K8)

TSQL Convert 3.100000000000e+003 to human readable format (Double) - I have a stored procedure that returns a XML string, I have a field 'Quantities' which contains an odd value...

TimeZone conversion in sql server 2008 - How i can convert my timezone in sql server, My requirement is to save date in client time zone not...

Error while creating stored procedure from SQLCMD - Must declare the scalar variable - Hello, I'm Getting the following error when I run the below SQLCMD command and not sure how to resolve this issue: [quote]C:\Test>sqlcmd...

Link server locking tables - hello SQL world- i am trying to copy tables form server A to server B using a link server. is there...

need extra column with totals in sql pivot result set - I am losing the battle on this. Here is my code i get the pivot table fine so this is...

date conversion - Is there a way to convert UniData datex to a sql date field in a query? :ermm:

NOT makes query never complete? - I understand it may be difficult to assist me without knowing my underlying schema and all the details. Let me...

Next business day, partial holiday calendar - Hi All, I am used to working with a calendar table that has a record for each day. Now I need...

Grouping timespans by the avg time per weekday - Hi There Long time - no see. I would appreciate a bit of help on a query I have to write. I work...

Error Creating ASSEMBLY - Hello I am trying to use a dll created in Clarion for C++ but i get an error like below Msg...

SQL Server 2008 : SQL Server Newbies

Help to diagnose LCK_M_IX Query - Hi all, I have a query that is stuck (running for few hours but normally takes 2-5 mins).. Id like...

What forum best for question about replication on SQL Server 2012? - Hello, I've looked through the forums to see if I could figure out the forum that would be the best for...

How have Trigger's example after insert send to mail on outlook?. - I create trigger after insert into table i want send mail on outlook and example trigger please. thank you.

Connecting to SQL Express via SQLCMD errors - Hi all, I cant connect to a sql express instance via sqlcmd and was hoping for some ideas! Command line:...

SQL Server 2008 : SQL Server 2008 High Availability

Failover Cluster Upgrade 64Bit - Hi everyone, we had to Upgrade RAM to our Servers running on Windows Server 2008 32 Bit. In Order to use...

Cluster Validation fails to check Storage - Hi All, I am getting an error while validating the cluster. Writing to sector 11 on disk with identifier 589e0435 from...

Need suggestions on replication - We are implementing a new architecture for one our modules. There are 100 tables in the database and out of...

SQL Server 2008 : SQL Server 2008 Administration

Changing Collation - Hi, I am trying to change the collation of the databases ReportServer and ReportServerTempDB from Latin1_General_CI_AS_KS_WS to Latin1_General_CI_AS (which is the...

one issue about transactional replication in SQL Server 2008. - Hi all, There is one problem about transactional replication in SQLServer 2008. After configuring the replication, there will be three jobs 'Distribution...

Creating a new Management Data Warehouse and removing the old one - When I took on the Role of DBA, i inherited some leftovers from the previous DBA. One such leftover was...

SQL monitoring - CURSORUPDATE - i have a procedure that runs it is cursorupdate, but thats about all i can see, anyway to determine what...

what is pending io count - Is someone able to explain what pending_io_count is in sys.dm_os_tasks please. The description on bol suggests it is io done...

Database Mail Stopped Working - Database Mail on our production server was working fine until about mid-morning today. Nothing has changed on the server that...

Grant View Privileges on Database Diagrams (is it possible) - I'm an intern and my boss asked me to look what permission he will need to set to give me...

Existing Table division - I have a table "Claim" which contains 85+ columns. records in thousand(Not High-transactional table). The data get populated with the...

get SQL server memory usage - Is there a simpler way to get what exactly SQL server usage of memory on the server except using perfmon? I...

Disable|Enable Index - Hi Experts, I read in a post that during BCP,BULK INSERT kind of operation we can disable the index using ALTER INDEX...

SQL08R2 Database Mail - I am so ready to scream, I set up DB mail and restarted the SQL Agent services but it fails...

SQL Server Agent jobs not running as scheduled - Hi all, has anyone encountered sql server agent not actually running the jobs it is schdeduled to run?! It was recently...

Career : Certification

Microsoft Certified Master - There's been a lot of debate recently about whether certifications are worth doing and whether they provide you or your...

Career : Employers and Employees

Preparing for a job interview... - So I've gotten to the point where I absolutely hate my job. My company has been bought out (via a...

Programming : Connecting

Port 56731? - We were having an issue where SSMS was unable to connect to a named instance of SQL Server 2008 R2....

Programming : Powershell

SSRS report powershell script - I have SSRS 2008R2 reporting having 3 data sources. Say reportname = \Marketing\Test.rdl DataSource name = \Shared DataSources\Data1 , 2 ,3 Now when deploy the report...

SQLServerCentral.com : Anything that is NOT about SQL!

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

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

SQLServerCentral.com : Tag Issues with Content

can not access master stored proc - Hi, I have the below code declare @ret int DECLARE @XMLFile varchar(255) SET @XMLFile = 'C:\myfile.xml' exec @ret = master..xp_FileExists @XMLFile When I execute this its throwing...

Reporting Services : Reporting Services

SSRS 2008R2 Data set Properties Error - I have a stored procedure which i have to design the report for this i have connected to the datasource...

Combing RS databases with other production databases - Curious what the industry norm is. Can / should i put my report databases on the same server / instance as other...

SSRS expressions to get part of a string - Hi, I need to write expression in my report to get the characters between "_" and "-". I have two columns in my...

how to get the source code from .rdl file - Hi , i have a rdl file that is displaying report, i want the source code of that rdl file.(since i...

Data Warehousing : Integration Services

Dynamically load CSV files To Sql Server Tables - Hi, I am writing a SSIS package of copying CSV files to SQL tables. Csv files are five in numbers...

Data Warehousing : Analysis Services

MDX and VB FUNCTIONS --- - Hi to all. I write this topic because i need an help about MDX and how to use VB Functions to...

How to Query Analysis Service Properties - Hi, Anyone knows a way to query the properties of an Analysis Service instance? I am trying to return the configured...

Simple MDX query - Hi, I'm trying to something that I think should be straight forward in MDX but i'm struggling. I'm new to MDX...

Article Discussions by Author : Discuss Content Posted by Michael Coles

Java & SSRS - I am trying to implement bar codes in Java application. Also want to integrate with SSRS reports, how to achieve...