In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL DBA Bundle Top 5 hard-earned Lessons of a DBA
New! Part 4, ‘Disturbing Development’ by Grant Fritchey, features the return of Joe Deebeeay and a server-threatening encounter with ORMs - read it here.
 
SQL Compare Compare and sync databases with SQL Compare
“SQL Compare is fast, extremely easy to use, full-featured and affordable. I wouldn't bother messing around with anything else.” Adam Machanic, SQL Server MVP. Download a 14-day free trial.
 
SQL Monitor Get your priorities straight with SQL Monitor
“SQL Monitor gives me the ability to quickly see what my priorities should be, and delivers the information I need to make the right decisions,” Aaron Kolysko, DBA. Start monitoring with a free trial.

In This Issue

Super Fast Transactional Replication Repair

Your production SQL Server transactional replication just failed and the business impact is critical. How do you get replication restored in minutes? More »


Show off your Solutions

We're looking for scripts that you use in your daily job to find, fix, and solve issues in your environment. More »


Extending SQL Server DDL Triggers for More Functionality

In this tip Aaron Bertrand describes some of the ways in which you can extend the DDL trigger functionality. More »


From the SQLServerCentral Blogs - A Rickety Stairway to SQL Server Data Mining, Part 13: Using CLR with Analysis Services

by Steve Bolton                I was drawn into the realm of SQL Server in a roundabout manner thanks to Visual Basic. Around... More »


Editorial - The Speed of Azure

As database administrators we seem to be slow to embrace new technologies and paradigms. There was a lack of enthhusiasm from DBAs for SANs years ago, and virtual machines more recently, at a time when many other technology professioanls were embracing these ideas. Even today there is resistance from some people, and sometimes with good reason. SQL Servers are not like other servers and have much different hardware requirements. Too often the virtual machine and storage administrators do not appreciate that SQL Servers need different architectures.

Lately the cloud services push is seeing lots of resistance from DBAs. Various vendors and media hype the idea and potential savings, which then convince management that systems need to be moved or built in the cloud. There are some applications that fit better in the cloud, but not all of them. I certainly don't want sensitive information in the cloud, at least until we work out some of the legalities for who owns, controls, and responds to subpoena about data.

The applications that make sense seem to be those that are distributed with lots of paying clients. I ran across a blog that looked at some of the companies that have moved into the Azure cloud with pieces of their businesses and been quite pleased with the results. Quite a few of these are cost-scalable by clients, meaning that a new user or customer is paying some fee that makes it economical to add new databases and servers for that client. If they leave, you can shut down their server.

It's definitely easier to start an application in the cloud rather than move an existing one. The architecture is different from an on-premise application, and that means code changes. Not something many companies want to engage in, given the past success of such projects. However the cost savings can be significant for new projects, if those new projects involve investment in equipment, facilities, people, or some combination of all of those.

However one of the biggest items I see mentioned why companies like cloud platforms like Azure is the speed of deployment. To me that means that the IT infrastructure people, from storage to admin to DBAs, are falling down on the job. There's no reason we can't deploy new machines as quickly as Azure these days.

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

Populate a table variable as shown below and set the variable @x to an integer value between 1 and 19 (there will be 20 rows in the table variable). Then execute the two SELECT statements.  In which scenario will statement 1 return more rows than statement 2?  (No cheating!)

DECLARE @Table TABLE
( iAsInt int,
  iAsString varchar(2));
    
DECLARE @i int, @x int;
SET @i = 1;

WHILE @i <= 10
BEGIN
  INSERT INTO @Table
  VALUES (@i,     CAST(@i AS varchar(2))),
         (@i + 1, CAST(@i AS varchar(2)));
    
  SET @i = @i + 1;
END

SET @x = ??? --set an integer value between 1 and 19

--statement 1
SELECT TOP (@x) WITH TIES iAsInt, iAsString FROM @Table ORDER BY iAsInt;
--statement 2
SELECT TOP (@x) WITH TIES iAsInt, iAsString FROM @Table ORDER BY iAsString;

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

This question is worth 1 point in this category: Top. 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 Step by Step

Teach yourself the programming fundamentals of SQL Server 2012—one step at a time. Ideal for beginning SQL Server database administrators and developers, this tutorial provides clear guidance and practical, learn-by-doing exercises for building database solutions that solve real-world business problems.

Get your copy from Amazon today.


Yesterday's Question of the Day

I have a table, Sales, with the Notes field having the Filestream attribute. I run this query:

select
 salesmen, notes
 into #salestemp
 from Sales

What is the data type of the Notes field in the temporary table?

Answer: varbinary(max)

Explanation: Filestream data is stored in a varbinary column, with the filestream attribute. The INTO clause, however, does not copy the Filestream attribute. Data in the column must fit in the varbinary(max) 2GB limit as this is the type of the new column.

Ref: INTO - http://msdn.microsoft.com/en-us/library/ms188029.aspx

» Discuss this question and answer on the forums

SQL Server 2012 Integration Services Design Patterns

SQL Server 2012 Integration Services Design Patterns is a book of recipes for SQL Server Integration Services (SSIS). Design patterns in the book show how to solve common problems encountered when developing data integration solutions. Because you do not have to build the code from scratch each time, using design patterns improves your efficiency as an SSIS developer. In SSIS Design Patterns, we take you through several of these snippets in detail, providing the technical details of the resolution.

Get your copy from Amazon today



Featured Script

Search all stored procedures on all databases

Need to find every stored procedure in any database that references the table FooBar? Here is a quick and easy search to do that. 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

Error on SQlServer reporting services(Internet explorer cannot display the webpage) - Hi Team, we have recently installed SQLServer2008R2 standard edition with reporting sevices,everything went fine during the installation.Once the installation is completed...

What is the difference between 2005 and 2008 in installation method - Hi all Here i have some doubt about new features in sql server 2008 What is the differences in sql server 2005...

Scheduled job not running - One of my jobs stopped running. The job is enabled but the next run date in the Job Activity Monitor...

Logon Trigger fails on SQL Server Authenctication - I have a very simple logon trigger on SQL 2005. [code="sql"] CREATE TRIGGER Tr_ServerLogon ON ALL SERVER FOR LOGON AS BEGIN INSERT INTO AuditDatabase.dbo.LogonHist SELECT SYSTEM_USER,USER,@@SPID,GETDATE() END GO [/code] A...

"Sql server error 5120" while attaching database - HI, I am trying to attach few databases to sql server 2005.I have my .mdf files in "D:\MSSQL\Data" and my .ldf...

SQL Server 2005 : Backups

Microsoft SQL Server, Error : 2 - While connecting to SQL Server 2005, I am getting the following error. "An error has occurred while establishing a connection to...

SQL Server 2005 : Business Intelligence

SSRS DEPLOYEMENT PROBLEM - Hi every body, I created a report with SSRS without problem, but when I tried to deploy it , I received the...

deleting rows from a table after processing them in SSIS - Hi Guys, I am a newbie and I need to do the following task: I have to process the rows in one...

Crear Documentacion de Pacquete o proyecto ssis 2008 - Estimados. Muy Buenos Dias, mi Pregunta es la siguiente: existe alguna opcion en ssis 2008 que me permita exportar un paquete (dtsx)...

Import Excel binary files into SQL Db using SSIS - Hello, I have a several .xlsb (MS Excel binary) files with data that I need to import into a SQL...

SQL Server 2005 : CLR Integration and Programming.

Using an ASP - I am a total newbie and have a question to ask. I am the only DBA on a team managing...

SQL Server 2005 : SQL Server 2005 General Discussion

Slow Windows 7 ODBC Connection to SQL Server 2005 - I had a SQL Server 2005 Developer edition installed on a Windows 7 Pro computer. I set up an ODBC...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Alert for out of workers - Since there is not a Performance Tuning section ... for SQL 2008, posting this question here. We had a period, today, when...

SQL Server 2005 : SQL Server 2005 Integration Services

Derived Column to handled mulitple conditions in CASE statement? - HI, I've got a case statement that works, but because I have to do it in SSIS I am at...

SSIS by using SP with multiple result sets - Hello, We have an SP which is developed to give us two result sets based on selection. I want to...

SSIS package reacts slow in BIDS - Hello, One of my packages in my SSIS solution is reaction very slow in the BIDS UI. Selecting a task in the...

SSIS and MSDTC on a SQL Authenticated Connection - Hi I have a package that has a container with a required transaction, meaning that the package starts an MSDTC...

SQL Server 7,2000 : General

hyderabad dba institutes - Dear Friends, i'm working for a development company, as Junior DBA. for oracle concepts,Wilshire and SQL * are very famous in hyderabad. is there...

SQL Server 7,2000 : T-SQL

Show a Start and End Sequence for a Given anchor value - Hello, I'm having quite sticky problem at the moment and hoping someone can help to educate me! For the purposes of the...

SQL Server 7,2000 : SQL Server Agent

Database Mail - Im having problems sending mail from an on premise database server to an off premise exchange server with office 365....

SQL Server 2008 : SQL Server 2008 - General

The best method for reading books - [b]Please answer me because your method in reading and learning will help me greatly [/b]

Query question - clustered index internals - Hi all, I have a question I hope the community can help me with. I have a query that is running...

How to find all SQL Server Agent Jobs that are currently running by Query - The query below I searched from net, this returns jobs status. When I give job Id in filter of this...

GeoSpatial Data Types - Hi, I'm going crazy here. I need to store Google map locations in a SQL table. I create the table with two...

Greater/Less than on text column - Hey, I have a column (INVOICENO) which is nvarchar(12). It's this datatype as an invoice could be a credit note, so...

Alter data logical name, add _data as defaul during database creation - Greetings - Is there a way you can change the default database creation settings to add _data in the end of...

SSIS - What is the best practice for creating a fixed length flat file. - I am running into the problem of SSIS putting the CRLF in random places at the end of the row....

Indexed view - Is it a good idea to put index on view? Some of the views are taking longer to execute and...

Finding Differing Module Definitions - So, I've been given the task of improving the performance of a database that was acquired via the purchase of...

Ho to track the single user data in a multi user application ? - Hi Friends, I am trying to track a multi user application . I am one of the user. I want to...

TDE implementation - Hi All, we are planing to implement TDE database level security. Please confirm me if that there are no application level...

how to archive data based on on archive it self? - Hi I have an archive tabel in Oracle. We copy this to a ms sql 2008 r2 database for datawarehouse purpose. The...

kll - hghjm

DTA - Hi, I analysed the trace file using DTA and got recommendations. And i got estimated improvement number as 79% when i given...

Maximum number of Instances in SQL 2008 - Hi - I'm a bit confused on the number of instances allowed to run under a single SQL 2008 license. This article...

Importing text file(without delimiters) to SQL server. - Hi Experts, i have a text file with 1 -3 lakh records and 5 columns or so.... each column is seperated...

Checking References for a New DBA - Hi, I've recently taken on a role that involves overseeing some of our IT services, and one of them includes...

Servers Consolidation and Linked Servers - Hi, We are planning to consolidate 5 servers in a 2-node active/active cluster. Some of the servers have Linked server between them. After...

Risks of not upgrading from SQL 2000, SQL 2005 - Hello - I am inheriting 5 servers to administer from a jack-of-all-trades DBA/developer in another department. 2 of the instances are SQL...

Creating enough empty pages in the database. - I use the following script to create empty space in a database. (So that during an opperation the database does not...

Making a copy of a table. - What is a good method to make a copy of a table. Only the table not the data. Including all indexes. Excluding referential...

Long-running process - Hi, We have a third-party application with a SQL Server backend that imports the records we insert into a staging table....

Sum Time - Hey, I have a column named TOTALTIME (datetime data type). This is used to store the amount of time something takes...

Code review rant... - During an online code review: Me: Your trigger is not set based. It will only process a single row. SQL Dev (w/...

sqlcmd - Hi, could any one pls help me ? am scratching my head with sqlcmd. this the issue create table testcmd (a int identity, b...

Report not working on Report Server - I'm not sure if this is the right place to post this one, but maybe someone can help. I have a...

Settings of Windows Server for SQL - Hello, Do you have any tips for settings of WS? Roles, memory, etc.. ? it can be different betwen performance? any...

SQL Server 2008 R2 Backup Failed 'There is insufficient system memory in resource pool 'default' to run this query.' - Hi I have been having an issue for some time now with Backups failing on an instance of SQL Server...

SQL Server 2008 : T-SQL (SS2K8)

using row_number() over partition by to get datediff by row - I have a list of events that occur for a patient. I need to count the number of events that...

Parsing a variable for a where clause - I have a variable that gets set by a user on a web form, and the user can set that...

Delete failing in distributed transaction on linked server - I am looking to "transfer" records from a table on Server A to a table on Server B. And then...

calcualte Days between 2 dates but exclude weekend - Code not working need help please urgent - Dear friends, I have requirement to Calculate Days between 2 Dates but the code is not working- My current code is- -- CAST ((datediff(dd,convert(int,Job...

OPENXML query problem - Hi, I need help with querying an xml file with openxml. My xml file looks like that (it is longer, but the...

STDEVP help - If you have a record, where there are 12 columns, one for each of the last 12 months, and you...

Linked server queries - I have a view that is built on OPENQUERY function calls to linked server (Oracle db). When I run queries...

Dropping select tables across a database. - Hi I have a situation whereby I have hundreds of tables across my instances that need dropping. The tables are prefixed...

Text Search for age-group related words - Hi Everyone, I have an app that creates groups and designates members based on their age. Now I would like to...

Most Efficient Insert query - Good Morning Everyone I hope that everyone had a very nice weekend. I am inserting rows from one database to another. The...

Funnies on Views??? - Hi all Just a quick one. As we all know a view is a set of columns from a table we...

Need some query help. - I need to figure out how to do a group by on this query by number of days between start...

SQL Server 2008 : SQL Server Newbies

Application Slowness issue - Hi, My database is working fine, but application is getting slow. According to the client,If they login with one or two...

Displaying a week numbers as a rows for given date range - Hi, I would like to display a week number as a row for given date range. After all I want to...

Bulk Copy. - Hi Experts, i have a text file with 1 -3 lakh records and 5 columns or so.... each column is seperated...

Power Pivot - Hi Guys.Need some of your expertise. I just wanted to know is it possible to run more then 1 query on...

Split Database - I have a split database that has one Back end and 3 front ends. Is it still possible to migrate...

Converting a old .sql to new - Hey all, I have a question from a complete noob to sql. I have a old set up .sql that...

SQL Server 2008 Upgrade Advisor failure... - Hello, We're running SQL Server 2008 upgrade advisor against a SQL Server 2000 instance. When asked to identify which components to...

SQL Server 2008 : Security (SS2K8)

SSL encryption - Hi Guys, I am not too familiar with SSL encryption in SQL server. Can anyone please give me the pros and...

Best way to completely keep an entire Active Directory group of people out of a SQL Server - I'd like to ban an entire group from accessing specific SQL Servers, does anyone know of a solid way to...

SQL Server 2008 : SQL Server 2008 High Availability

How to shrink mirrored database log file - One of our production database is configured mirror , the log file growth around 150 GB, we need to shrink the...

SQL 2008 cluster does not failover to another node - Hi guys, I have this issue when I installed a SQL 2008 cluster instance on the Windows 2012 OS. I have...

SQL Server 2008 : SQL Server 2008 Administration

Failed DB back-up! - Failed database back-up I get the following error message in my maintenainceplan history. Unfortunately I do not have much knowledge of...

Client tools? - What are client tools in SQL Server and why do we really need them.

Add e-mai signature to SQL Database Mail - Hi All, Is it possible to configure SQL Server 2008 R2 database mail to add a signature to all e-mails sent...

Index maintenance, excluding index with page_level_lock disabled - Hi all, I'm running into an issue with not being able to rebuild / reorganize an index because allow_page_locks is not enabled...

failure:restore master database in sql server 2008r2... - Hi, I hope you are doing good. Iam facing one problem with Restoration master Database in Sql server 2008r2(named instance) enterprise...

Moving SQL Server 2008 R2 to new Server Hardware - Hi All, I'm tasked with moving an existing SQL Server 2008 R2 environment to a new server. The current server is...

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

Programming : Powershell

Help making my RestoreDB script better pls? - I'm a total noob with posh :-) I got it powershell running and then hacked together this script. I was following the...

Retrieving Data From Hyphenated Server - What better place to solve a nagging Powershell issue than good ol' SQL Server Central :) I'm trying to retrieve disk...

SQLServerCentral.com : Anything that is NOT about SQL!

What is Functional DBA? - Hi All, I hear “Functional DBA”, I search in Google to know exact meaning for it and found that “A functional...

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

Database Design : Hardware

Hidden SAN fragmentation - Hey folks, a quickie question about the drive space assignment mechanics. We'll simplify things. Working on a single drive in a...

Data Warehousing : Integration Services

Failure Constraint Route Completes Successfully, but terminates parent Foreach Loop Container? - Attached is a photo of the container in question of my package. Essentially, I am simply taking a flat file,...

Convert String to Datetime (USING SSIS) - HI Guys. Need some help. I want to insert a value "5/27/2013 16:42:37.490000" (Read from a flat file (DT_STR)) into a...

passing variable from execute sql task to ole db source - Hi All I would like to know how to pass a paramater from Execute SQL task to OLE DB source I have...

why doesn UNPIVOT transform yield more rows than T-SQL unpivot? - Excel source file contains 12,483 rows. For the SSIS Unpivot transform I utilize Excel Source transform to pull these in....

call sproc in oracle - We would like to do this in SSIS package: 1. pull a list of employeeID, with their address from a SQL...

Data Warehousing : Analysis Services

Any solution for better performance about a cube with 3 distinct counts on the same source DB table on SQL Server 2008 R2? - [b]Backgrounds:[/b] I have a big table about 180G for size and 4 billion rows on SQL server 2008R2 64bit so far,...