In this issue

Featured Contents

Editorial

Featured Script

Brooks Law: "Adding manpower to a late software project makes it later!"

 
 advertisement
 
SQL Monitor New! SQL Monitor release
Check out the latest features in SQL Monitor 3.2 and take a look at our new custom metrics sharing site. Find out more here.
 
SQL Source Control Local evaluation repository makes trying SQL Source Control simple
The evaluation repository makes it easy to try SQL Source Control. Get started with the 28-day free trial.
 
SQL Backup Pro Free SQL Server Backup and Restore eBook - get yours
Grab your copy for the complete guide to setting up a reliable, tested backup and restore strategy. Then try SQL Backup Pro to work through the examples. Download free resources.

In This Issue

AlwaysOn Availability Groups in SQL Server 2012

This article discusses and demonstrates AlwaysOn Availability groups in SQL Server 2012, a new feature for high availability. More »


Export images from a SQL Server Table to a Folder with SSIS

Can I export images from SQL Server to a file in Windows? What SQL Server options are available to do so? Check out this tip to learn more. More »


SQL Saturday #167 - Columbus, GA

A free day of training in Columbus, GA. Come meet the local SQL professionals and spend a day talking SQL Server. More »


Editorial - Shadow IT

When I started working as a system administrator over 20 years ago, I worked for a central IT department at a remote facility for our company. Four of us managed a network of 1,000 nodes, with multiple servers under a boss that decreed we needed to respond quickly and effectively to requests made by clients. They came to us often, expecting results, and we delivered. I assumed all IT groups worked like this. Almost a year later I worked at another facility and was quite surprised to find a different situation.

In the other location there were also four people and 1,000 nodes, but the IT group only managed around three quarters of those nodes. Different groups had their own servers and applications they managed. There were machines under desks in one department, where the desk's owner was tasked with working half his time on department work, and half his time on technology issues. I only found out about this when one of them asked me how to solve a few problems; I then realized he was managing his own IT services for his group.

Shadow IT is still around, and in some cases it's growing. That can be a problem, not only for IT staffs who have less demand for services, and thus staff, but also for the security and data integrity of an organization. Someone managing IT operations part time may not understand the security or data issues they create, face, or just may not have time to deal with. They also won't necessarily have the experience to work efficiently and effectively on administrative needs, though I know a central IT staff will necessarily do a better job.

As a data professional I get concerned about Shadow IT groups, which might hoard their data, or even keep around data that's gotten stale. I worry they'll lose systems and then come to me for a solution to recover the data, often without having managed backups. Most importantly, I worry they'll make a mistake, cause a business problem, and not only will I need to clean up the mess, I'll share in the blame for not helping them build something they needed in the first place.

Shadow IT is usually the result of a non-responsive central group. I've always made it a point to try and help people get the tools they need to work. Sometimes blanket policies prevent this from happening, but by making friends I can usually get Shadow IT people to consult with me and listen to suggestions and ideas. It isn't perfect, but if I can get them to make regular backups, it's a win.

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

What is the output of the following queries (SQL Server 2008 and above):

--Query#1
SET DATEFORMAT mdy; 

DECLARE @dt1 DATE = '01-15-2012'

SELECT ISDATE(@dt1)

--Query#2
SET DATEFORMAT mdy;

DECLARE @dt2 VARCHAR(10) = '01-15-2012'

SELECT ISDATE(@dt2)

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.


Yesterday's Question of the Day

I have two stored procedures:

create proc sp_test1
 as 
begin
 select col1, col2 
  from table1
end

create proc sp_test2
as
begin
 select col1, col2 
  from table2
end

In the first procedure table1 does not exist. In the second procedure table2 and col1 exist, but col2 does not exist. If I execute both scripts separately, what would be the result?

Answer: Command(s) completed successfully and Invalid column name 'col2'

Explanation: Deferred name resolution allows you to refer to an object in a stored procedure that does not yet exist. However if the object exists, then it must have the same schema as referenced in the stored procedure.

Ref: Deferred Name Resolution and Compilation - http://msdn.microsoft.com/en-us/library/ms190686%28v=SQL.105%29.aspx

» Discuss this question and answer on the forums

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.


Featured Script

A simple way to find some arguments

A simple way to find any term in all banks or only in a specific database. 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

Need your advice on an automatica job - Hello, I have a project, it's an asp.net application using SQL 2005 as back end (will soon be upgraded to 2008...

Do I need to backup log if log shipping is implemented in that database ? - Hi, Do I need to backup log if log shipping is implemented in that database ? Will the log backup taken by...

DBA Interview Questions needed - Serious only please. - Hi dudes and dudettes... I've convinced the boss that he needs a dba since the 3rd party firm providing dba services...

Lock escalation concept - Hi All, Please clear my doubt about lock escalation topic in MSDN. Here is the topic: "Lock Escalation (Database Engine) For example, assume...

xp_delete_file not deleting files on share drive - Hello, xp_delete_file not deleting files on share drive. is there any permission to be given?

Configure Log Shipping Between SQL 2005 and SQL2008 - I am trying to find out if it is possible to configure Logshipping between SQL Server 2008 and SQL Server...

Linked Server to Oracle - For anyone who can help. I receive the following error when running a query. Msg 7399, Level 16, State 1, Line...

SQL Server 2005 : Business Intelligence

ssis flatfile data to database table prob - hi friend i have a small doubt in ssis plz tell me how to solve this prob all columns are separete...

Drag And Drop - Hi, We are working on one project where the user needs so many real time reports. The reports are combine of...

SSIS 2008 - Pivot transformation and DT_NText Datatype - Hi, Has anyone ever tried using a column with dt_ntext datatype in the pivot transformation for ssis 2008? I am trying to...

SSRS default parameter - Hi, I'm trying to develop a new rdl with 2 parameter, both are with drop down values. One parameter is...

High Level Designing - Hi everyone, we have a project and it is going to begin from the scratch, i haven't involved in High...

SQL Server 2005 : Development

Database Size - In My Production server i am facing following problem. Totally my database size showing 134 GB.But my data size is 10GB...

SQL Server 2005 : SQL Server 2005 General Discussion

Linked server query column needed - Hello all, Pardon my ignorance ... but i'm trying to add a column in my linked server query which is where the...

Database Size - In My Production server i am facing following problem. Totally my database size showing 134 GB.But my data size is 10GB...

idle configuration for 1000-1200 concurent users - Hi, i want to set up a database server in my company for 1000-1200 concurrent users. so i want to know that...

Is UPDATE FROM only available in SQL 2005? - I rarely have to write an UPDATE query that uses more than 1 table. Today I did, and I came...

SQL Server 2005 : SS2K5 Replication

add article to replication without snapshotting entire DB. - hi I simply want to add an article to an existing publication WITHOUT creating a new snapshot of the entire database,...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Metrics for stored procedure execution - Hi All, We have a SQL server 2005 database with 2000 stored procedure and would to perform the performance tuning for...

SQL Server 2005 : SQL Server 2005 Integration Services

Error when setting up dynamic connection string - Hi there, I am creating an SSIS package to pull information about job history and store it in a centralised database. I...

SQL Server 2005 : T-SQL (SS2K5)

String concatenation - Hi I have the following table [code="sql"] CREATE TABLE #Test (ID int NOT NULL IDENTITY(1,1) Primary key, TranID int NOT NULL, OriginCode varchar(5) NOT...

SQL Server 7,2000 : Administration

Sql Server 2000 Connection Problem in Window Service - I'm trying to connect window service with sql server 2000. on other four servers, it is working perfectly. But on...

SQL server 2005 administration (restoring mdf file without ldf) - Here is the situation: > C drive containing the SQL server 2005 is gone > D drive for data is available > E...

SQL Server 7,2000 : Security

SQL 2000 Connection problem in Windows Service asp.net c# - I'm trying to connect window service with sql server 2000. on other four servers, it is working perfectly. But on...

SQL Server 7,2000 : T-SQL

combine two tables - Hi, I have one problem to join two views in one. I have two views which have some fields same and some...

SQL Server 7,2000 : SQL Server Agent

Timing out scheduled jobs - I asked this question in another forum and unfortunately I still do not have an answer. This is the problem: I...

SQL Server 2008 : SQL Server 2008 - General

SSIS Mapping (Audit) - I have an inquiry based on how SSIS handles mappings for a certain number of columns that increase in time....

MDF File of MS SQL Server 2008 is Corrupted - Last Night I have a problem due to Power failure actually i have used Microsoft SQL Server 2008 everything is...

Import excel 2010 spreadsheet - Hi I tried to import excel 2010 spreadsheet, but I can not find any option for 2010, higest version seems to...

filegroups and tables. - How can I see which tables belong to a filegroup ? (A selection which gives me all tables belonging to a...

Issue with GETDATE() - Hi all, We have a server in Sydney and users across India and US. When ever a record is gets saved...

upgrade from 2005 to 2008 r2 - hi folks, I am looking to upgrade my sql from 2005 to 2008 r2. I understand sql 2008 r2 install needs...

Buffer catch Hit ration - Hi All, I am getting a alerts [b]Buffer catch Hit ration 100%[/b] and when i check buffer manager details through...

can any one tell me how to split the given @parameter data into three column of table? - this is way i tried to split data in parameter [code="other"] declare @parameter varchar (200) set @parameter ='1_2_3|4_5_6' ;WITH CTE1 AS( SELECT...

Cannot Add a Master Key to the Database - Hi, I'm very new to SQL Server, or databases, in general, so bare with me. I'm trying to create a...

Check room availability in hotel system - [font="Times New Roman"][/font] I'm a university student and I'm developing a database for a hotel system ,now I'm stack when I...

sql query prob - hi friends i have a small doubt in sql plz solve i have a table that table contains some nullt...

how to avoid sort transformations - Can anyone tell me :- "How to export Records from a table of a database to an excel file sorted based...

dead connection detection - In Oracle, one can set SQLNET.EXPIRE_TIME= <# of minutes> to have the server detect connections where the client has died (e.g....

Use a variable as a column name - without dynamic SQL? - [code="sql"] CREATE TABLE #tblItems ( ItemID int, ItemDescription varchar(50), ItemDate smalldatetime ); INSERT INTO #tblItems (ItemID, ItemDescription, ItemDate) SELECT 1, 'Item 1', '01 Jan 2012'...

SQL Beginner - I have a table in this format: Origin Destination Tonnage(Kton) Alabama Arizona 0.0012 Alabama Idaho 1.2 Idaho Alaska 644 Arizona Alaska 665 ......... Consisting of...

Backing Up/Restore to non-local drive - I've told my IT Admin many times that we don't/can't do SQL DB backups to network shares or anything but...

SQL 2008 R2 'wall of weird' submission - I am the developer of our in-house CRM application that runs against a database located on a single SQL 2008...

Hierarchyid Data Type -- Pros and Cons? - For a new application and database model, our team is trying to decide whether to use the hierarchyid data type....

Running .bat file in SSIS package - Hi, From a VB script task in SSIS, I am trying to run a bat file. Here is my piece of code....

Transparent partitioning. - My view in the world is that partitioning should be transparent to the user and to the 'programmer'. So a field...

SSIS Data Load - Is there a way to be able to use one flat file connection manager to load multiple files with different...

SQL Server won't start, no error messages - I have a SQL 2005 (default instance) and a SQL 2008 (named instance) on my local XP machine that I...

SQL Server 2008 : T-SQL (SS2K8)

Actual Exec plan not recommended missing index? - Query takes too much time for getting 90 rows also I have attached Actucal Execution plan, could you suggestion me...

Validate rows in 2 tables and write log - Hi, I'm having 2 tables with old and new project numbers, If project number in Table A matches with Project Number...

syntax i need - while copying row in oracle we can use %type (or) %rowtype is there..... like the same is any other synatax is...

find a grouping with at least one row within the group containing a certain value - Hi all, How do you find a group where number of rows in the group are gtr than 1 and at...

Making subdivision on Procedure - create table data_dd(slno numeric(22),goals varchar(22),kpi varchar(22),weightage numeric(22),record_status char(8),empcode varchar(12),accyear date) insert into(1,'webdevelopment',2451,40,u,002244,'2011-2012') insert into(6,'webdevelopment',2451,46,u,002244,'2011-2012') insert into(8,'webdevelopment',2451,46,u,001244,'2011-2012') insert into(9,'webdevelopment',2451,46,u,001244,'2011-2012'

##table question? - hello. here is the scenario: sp 1 inserts data in #table, #table is in sp2 and sp3 sp 2 and sp3 are...

How to do this? Part-2 - I have a table A: StudID RollNo Alpha D1234 beta A1122 charlie D1234 bravo C1342 tom B1964 harry A1122 and table B: RollNo SubjectCode D1234 1001 A1122 4001 D1234 2001 C1342 5001 B1964 6001 A1122 3001 I want to join the table A and B. For studID Alpha from table...

help with SQLCMD - Hi , I am trying to use SQLCMD to run a SQL Script. The script is of type. Begin tran Delete ..... Insert ..... Commit...

synatx - what is the syntax of range of year='2011-2012'

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

Best Practice : coalesce vs. isnull? - Just wondering, I like to use ISNULL(column_name, 0) while my friend like to use COALESCE(column_name, 0). We both try using ISNULL...

SQL Server 2008 : Working with Oracle

Oracle Database - Hello all... I am new to database learning. But really got interested in OCA studies. So I have decided to opt that...

Simple pass thru query - Hello Oracle Forum: I've got my first linked server established from SQL Server 2008 SSMS to Oracle. What is the proper syntax...

SQL Server 2008 : SQL Server Newbies

Create view with no outer joins - Need to create view with no outer joins so I can index the view. Here is the query I use...

use a variable to identify table in FROM statement - Dozens of our tables are set up where the table names are identical except for an ID number at the...

table sizes for all d/b on the server - Hi all, I am trying to retrieve table information in every d/b on the server, however in my code below, for...

Convert column to row - Hello everyone, I am a newbie to sql and I am faced with the problem below. I have 3 tables [b]Categories[/b] Cat_ID | Cat_Name 1 | Cosmetics 2...

SQL query performance question - I am working on a small research team that has been given access to a set of SQL Server 2008...

SSIS 2008r2 - In the advanced editor for the OLE DB Destination I am adding columns to "External Columns". Once they are added...

Running SSIS fails on local server, not remotely - I have a [u]very[/u] simple SQL Server 2008 R2 SSIS package that imports 4 .csv files into a table. If I...

SQL Server 2008 : SQL Server 2008 High Availability

Log Shipping & DataBase Mirroring - I am New to the Log Shipping & DataBase Mirroring, unfortunately i am asked to perform this by Client :( an any...

Would a smaller number of VLFs in the log speed up the redo phase of recovery of a mirror snapshot? - From time to time, when an intensive log operation takes place on the principal database, we observe very long snapshot...

sql server 2008 replication: Subscription not showing up in Local Subscriptions - Pub & Sub: 2008R2 Ent 64bit Distribution:20012 Ent 64bit. When I try to add new Subscription to the Local Publication. It say successfully created,...

MSDTC issue in Cluster environment - Hi All, I have configured Cluster in my location below are the details of structure. Q drive 40 GB (Quorum) ...

Replication problem - Hello, We are replicating few databases (which are identical in structures) in SQL Server 2008 R2 with transactional replication. The replication...

Log Shipping copy job fails intermittently - Hi, We are using log shipping in SQL Server 2008 R2 and working fine for years. But from last 2 weeks, We...

TRUNCATE TABLE on Mirrored DB - I truncated a table in my mirrored training environment on Principle side. From what I remembered, TRUNCATE TABLE [tablename] is not...

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding - Hi, We have log shipping configured in SQL Server 2008 R2 SP2. Log shipping copy job is failing and I'm getting the...

Database mirroring login failure - I have configured database mirroring between two servers at a distance 40 miles away from each other. Server specifications: SQL Server...

SQL Server 2008 : SQL Server 2008 Administration

Problem While accessing Network Drive - Trusted Connection - Hello, We have SQL Server 2008 installed on Windows 7 (32 bit). I am trying to execute a stored procedure which...

Views - Hi all Please help me understand the process on Views in SQL Server Table: [code="sql"]create table viewstest (Col1 int, Col2 int, Col3 int) go insert into...

Backup failed - We are taking backup on to data domain but its failing for 2instace and in another instance its working fine...

Join making conflict.... - SELECT @MSGOUT = @MSGOUT + ' <tr> <td>' + ss.ServerName +'</td> <td>' + ss.DBName +'</td> <td>' + Occurrences +'</td> </tr> ' FROM DBINVENTORY ss INNER JOIN (SELECT ServerName,DBname, cast(COUNT(dbname) as varchar(5))AS...

SQL-Agent Job succeeded - Despite of errors in step1 - I have a SQL-Agent Job which executes a SSIS-Package. There are Errors in the SSIS-Pakage, but the Job is succeeded. Here...

SQL Agent Job - "Run As" drop down list is empty - Why is the "Run As" drop down list is always empty when I try to set up a SQL Agent...

DNS Alias, Clustered instance, Default Port query - Hi all, I am moving databases from a default SQL 2000 instance to a clustered SQL 2008 R2 instance. My aim...

max memory setting SQL 2008 64 bit? - Hi, Operating system - Win server 2008 b4 bit enterprise edition Version - SQL server 2008 R2 b4 bit enterprie edition Physical Ram - 8GB Server...

Moving tempDB from RAID10 to RAID5 for OLTP? - My data files and tempDB file (single temp file) share the same RAID10 LUN and our SAN controller is saturated/overloaded....

Career : Resumes and Job Hunters

MS SQL developer - Sorry for only Russian, however the job opportunity is only for RU. Thanks for understanding. ??????????? MS SQL ????????: ?? 40 000 ?? 50...

SQLServerCentral.com : Anything that is NOT about SQL!

Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,...

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

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

Reporting Services : Reporting Services

diplaying multiple external images - i have a ssrs report by staff code and needs to diplay mutliple .jpg images stored in a folder and...

Image/Logo Updating - Hi. I would like to know if there is a way to enable to change a logo from a report after...

SSRS automatically run report & save pdf output to c: - Good day, I need help. I normally run a report via url and add the &rs:Format=PDF to open file directly...

Data Warehousing : Integration Services

FINDSTRING Function - Hi All, We are using FINDSTRING Function in one of Data flow Derived Column (Component), It work Fine for Symbols like...

Ways to address incremental updates in SSIS - Hi fellows, I'm trying to deploy a datamart, I have the star schema designed and I am working on the ETLs,...

Keep Package Running if Webclient has Timeout - Can someone here tell me how I can handle Webclient timeouts? I have a package downloading approximately 300 zipped files...

Data Warehousing : Data Transformation Services (DTS)

Crystal Report run through DTS - Hi, I just run crystal report daily without any parameter and after run, save the file in c:\temp directory. I have...