In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
Deployment Manager NEW! Automate your .NET deployments
Deploy ASP.NET applications and SQL Server changes fast, frequently, and without fuss, using Deployment Manager, the new tool from Red Gate. Try it now.
 
SQL DBA Bundle Top 5 Hard-earned Lessons of a DBA
Lesson two has now been released! Read ‘Beating Backup Corruption’ and learn from the very best. Read now.
 
SQL Data Generator How do you generate test data for your database?
SQL Data Generator quickly populates databases with intelligent and meaningful test data. "SQL Data Generator is simple and effective." Michael Gaertner, Quintech. Download a free trial now.

In This Issue

Universal Product Codes: a Database Primer

An introduction to Universal Product Codes with code to help you use them in your database. More »


15 Quick Short Interview Questions Useful When Hiring SQL Developers

Here is a useful exam that you can administer in either 10 or 15 minutes, either oral or written, when you interview for hiring permanent or contract workers for jobs in SQL Server development or support. (This is not a test for SQL Data Base Administrators.)  More »


SSIS Basics: Using the Conditional Split

The Conditional Split can route data rows to different outputs depending on whatever criteria of the data that you wish. It works rather like the SWITCH block in a procedural language. Annette shows how to add a conditional split to your data flow. More »


From the SQLServerCentral Blogs - A Month of PowerShell – Day 21 (Backups)

Welcome to Day 21 of my “A Month of PowerShell” series. This series will use the series landing page on... More »


From the SQLServerCentral Blogs - MDX #26 – SSN can only be referenced as a member property in MDX

I’ve blogged about the Properties()  function before. MDX #11 – How to get number of cars each customer owned using Properties() function? MDX... More »


Editorial - More Evolution, More Complexity

Recently I had the chance to learn about some of the changes coming in the SQL Server platform in the next few years. At the MVP Summit we had the chance to talk with people that showed us features and changes coming in the next version of SQL Server as well as some ideas and thoughts about what might come after that. I greatly enjoy the latter sessions, since the ideas and goals of brilliant architects are always interesting to listen to.

As I heard about changes and additions, it occurred to me that while some of the features and functions become easier for people managing platforms, others become more difficult. The tuning decisions we might make with regards to resources become more complex. We have new knobs to turn, and more decisions to make on how to best balance the hardware available on our servers.

I've known a lot of people over the years that have feared for the evolution of software, which eliminates some of the "easier" tasks that administrators and even developers have had to manage in the past. There's no denying that the days of making a career of babysitting boxes, changing tapes, scanning logs manually, and more are likely over. Those menial, easy-to-automate tasks will become the domain of software at some point. We still might perform them, but it will be rarely.

As platforms advance, however, they do not become more autonomous. New decisions and management tasks are needed. That means new skills for those of us that want to remain in information technology. It's means a little more work for us, more studying and more time. However your career will span decades, so plan your learning along those lines. Don't try to learn everything this year. Pace yourself and plan on regular learning across the next five years and you might be amazed what you accomplish.

» 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 will be the results from after running following statements on SQL 2008:

CREATE TABLE T1 (C1 Int)

INSERT T1
VALUES ( 1 ),
( 2 ),
( 3 ),
( 4 )

UPDATE dbo.T1 
 SET C1=C1+(SELECT MAX(C1) 
              FROM dbo.T1)
 WHERE (SELECT MAX(C1) FROM dbo.T1) > C1

select * from T1

drop table T1

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.

Securing SQL Server

Written by Denny Cherry, a Microsoft MVP for the SQL Server product, a Microsoft Certified Master for SQL Server 2008, and one of the biggest names in SQL Server today, Securing SQL Server, Second Edition explores the potential attack vectors someone can use to break into your SQL Server database as well as how to protect your database from these attacks. In this book, you will learn how to properly secure your database from both internal and external threats using best practices and specific tricks the author uses in his role as an independent consultant while working on some of the largest and most complex SQL Server installations in the world. This edition includes new chapters on Analysis Services, Reporting Services, and Storage Area Network Security.

Get your copy from Amazon today.


Yesterday's Question of the Day

--Using SQL Server 2008R2 or 2012 
--I execute the following two (2) CREATE TABLE statements.

CREATE TABLE dbo.Output(ID INT,Col1 CHAR(15),Col2 INT, Col3 INT)
CREATE TABLE dbo.Input(ID INT IDENTITY(1,1),Col1 CHAR(15),Col2 INT, Col3 INT)

-- I then execute the following T-SQL statement
INSERT INTO Dbo.Input
VALUES('Surprise',20, 30),('Whoops',10,15),('Again',100,200)

--I then execute the following T-SQL 
--Delete statement
DELETE dbo.Input
OUTPUT DELETED.ID,DELETED.Col1,DELETED.Col2,DELETED.Col3 
INTO Dbo.Output
WHERE Col1 = 'Again' OR Col1 = 'whoops'

--I then execute the following T-SQL statement
SELECT * FROM dbo.Output

The Questions are: can OUTPUT be used with a deletion? If OUTPUT can be used with a delete, how many rows were deleted  by the Delete statement?

Select the three (3) correct answers.

Answer:

  • Output can be used when deleting rows
  • Two (2) rows have been inserted into Dbo.Output
  • Two (2) rows were deleted from Dbo.Input

Explanation: http://msdn.microsoft.com/en-us/library/ms177564.aspx shows that an UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client.

From: http://msdn.microsoft.com/en-us/library/ms189835.aspx

The OUTPUT_Clause section - Returns deleted rows, or expressions based on them, as part of the DELETE operation.

» Discuss this question and answer on the forums

Securing SQL Server

Written by Denny Cherry, a Microsoft MVP for the SQL Server product, a Microsoft Certified Master for SQL Server 2008, and one of the biggest names in SQL Server today, Securing SQL Server, Second Edition explores the potential attack vectors someone can use to break into your SQL Server database as well as how to protect your database from these attacks. In this book, you will learn how to properly secure your database from both internal and external threats using best practices and specific tricks the author uses in his role as an independent consultant while working on some of the largest and most complex SQL Server installations in the world. This edition includes new chapters on Analysis Services, Reporting Services, and Storage Area Network Security.

Get your copy from Amazon today.


Featured Script

Security Change Snapshot

This script gives a server level snapshot of recent security changes 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

The process could not connect to Distributor - Hi Guys I am in the process of creating replication between 2 remote servers, server 1 is the distributor and publisher...

LDF is IDF instead - The production database has 1 MDF and 1 log file. The log file extension is idf instead of LDF. Would this...

Avoid DEADLOCK for concurrent DELETE - Hi ALL, I have a table called Products which has many columns. It is a temporary table used for reporting purpose. The...

Problem with connecting 2 SQl 2005 Servers - Hi I am running SQL 2005 Standard edition under Windows Small Business Server 2003. Everything, that I need runs just fine from...

How to configure mirrored DB on ODBC for failover - Hello, I am having a question on configuring mirror DB on ODBC. When a Principal DB goes down the mirror DB...

Named Pipes Provider: Could not open a connection to SQL Server [53] - I am running SQL Server 2005 - 9.00.3042.00 On a Windows 2003 server service pack 2. I have a VB application...

What Windows PerfMon Counters Should be Monitored - We are using BMC Patrol. What Windows PerfMon Counters should be Monitor SQL2005 by it to ensure SQL is running...

SQL Server 2005 : Backups

Backup Failed but backup file is created - Hey guys, I have an issue with backing up a 80GB database over the network, Here is the error:A nonrecoverable I/O...

SQL Server 2005 : Business Intelligence

Help with SSRS report - Hello, I'm trying to create a report for listing the % compliance by Supervisor along with employee name, id, dept, and dates. SELECT...

Adding percentage on a tablix - Hello I've got a report which has this output below month Leads 2012 Leads 2013 Oppos 2012 Oppos 2013 Enquiries 2012...

SSRS Reports deploy in Reporting Server Manager - Hi Friends, I have a set of reports developed in BIDS env and now my SQL server has been reinstalled...

Custom DLL deployment - Hi, I have two SSIS packages which uses the Custom DLL in a script task. This Custom DLL has code which...

Creating Real time DASHBOARDS - Hi to all. I work for a company that uses from 2005 , SSAS of MICROSOFT. Now we are using SSAS 2008 R2...

SQL Server 2005 : Development

Partitioned Tables - i have one table it contains 30 million records, Now I have partitioned the Table, I could not find much...

SQL Server 2005 : SQL Server 2005 General Discussion

Why SQL Server enable Large Pages? - From SQL Server error log, i found following information: [highlight=#ffff11]2009-06-04 12:21:08.16 Server Large Page Extensions enabled. 2009-06-04 12:21:08.16 Server Large...

Execute Stored Procedure Multiple Times - We have a sproc that has been around a long time, it's ben tweaked & prodded, etc over the years to...

Trigger on insert help - Hello All, I hope someone can help because I'm lost as to what the problem is... I have created a trigger: [code="sql"]CREATE TRIGGER...

SQL Server 2005 : SQL Server 2005 Strategies

TempDB optimization - hello, in ma current configuration i have one data file and one log file of a tempdb database. as a process of...

SQL Server 2005 : SQL Server 2005 Performance Tuning

DeadLock - Hi I am getting below error: Transaction (Process ID xxx) was deadlocked on lock resources with another process and has been chosen...

SQL Server 2005 : SQL Server 2005 Integration Services

Exporting Multiple SSIS packages from MSDB - If we need to export a package from MSDB into the file system we can do it using the SQL...

SQL Server 2005 : T-SQL (SS2K5)

find whether a upper case letter is there in a given string - Hi all, i want to know whether a upper case letter is there in a given string or not? if i provide...

SQL Server 2005 : SQL Server Newbies

Moving Secondary Database in Transaction Log Configuration - I’ve been tasked with moving a production database server that is involved in log shipping. I am not a DBA...

SQL Server 7,2000 : Replication

The process could not bulk copy into table '"dbo"." - This errors Message was found in the Job History. 2013-02-27 12:28:18.758 Agent message code 20037. The process could not bulk copy...

SQL Server 7,2000 : T-SQL

using locks in sql server 2005 - i have one table, concurrently multiple users are hiting the table through insert action. i have placed trigger on the table...

SQL Server 2008 : SQL Server 2008 - General

Get Public role permissions - In SQL 2000, 2005, 2008, 2008r2 How to retrieve the permission for the default Public role in an db

Index Selection in SQL Server - Hi, Consider the following example : I have a table Employees: [code="sql"] Create Table Employees ( EmployeeId BigInt Identity(1,1) , EmployeeName Varchar(30) NOT NULL...

Getting minimum of top n rows without using subquery - Hi, We use SELECT min([date]) FROM table WHERE [date] IN (select top(100) [date] from table order by [date]) query for selecting...

Cannot create a row of size XXXX which is greater than the allowable maximum row size of 8060 - We are using SQl server 2008 R2 , we have such requirement so one of our table have 500 column . according...

How many Database can be created in a MSSQL 2008 - How many Database can be created in a MSSQL 2008 R2. Can any one give me the exact number with...

fetch tables most often queried - Is there a way to determine which tables of a database are being queried most often, sorted in descending order?

INSERT SELECT with large XML Dataset - XML DTD stripped... - Let me begin by saying I'm not very well versed in pulling XML files into SQL using a straight T-SQL...

Stored procedure runs faster than the code it contains... - I have a stored procedure which runs in about 1 second. If I take the code out of the stored...

SQL 2008 ODBC Connection - Hello, This is my first attempt at creating a ODBC connection from a workstation to a SQL 2008 server and I'm...

Question on how to dynamically change the 'FileConnection' property durning runtime in SSIS - Hello, I have an SSIS package with a number of File Connection Managers. These connection managers execute SQL scripts using Execute...

unexplained lock escalation - please help! - Hi, I have a stored procedure in my SQL 2008 database that contains only the following TSQL : WITH TableA_CTE AS ( SELECT TOP...

Database to Database Connection - Unsecure? - Hello, I work for a government agency and I'm trying to implement some automated data pull procedures and I was...

Trace flag 2371 - Hi All I want to enable trace flag 2371 for update statistics. The application generates a lot of queries. As enabling the...

Is SQL Server 2 tier architecture or 3 tier architecture - Hi Team, Can anybody please let me know Is SQL Server 2 tier architecture or 3 tier architecture ?

Need help with dynamic sql query - Hi, I need help with this dynamic query to execute the sql statements but it gives the following error when...

how to put my sql request within an application to disaply result? - Hi i have some request i would like to put them in one application so it's can display all the...

Does the query optimizer make use of transitive equality? - Does the query optimizer make use of transitive equality? By that I mean if A = B and B = C, then...

Need help with a select, insert - GOAL: insert into tblcompanyassignments from tbluserassignments. Only attempt the insert for userid that exist in both tbluserassignments and tblexistingusers. For...

Incorrect data type in Float column - Somehow, I have a table with non-numbers in my floating column. I can find them... select isnumeric(Column) as IS_Numeric , convert(nvarchar...

What, if any, is the best type of replication for implementing on hundreds of databases on one server? - Hi all, We have a 2 node (active/passive) SQL 2008 SP1 Enterprise Cluster that hosts about 500 databases. Now while the Cluster...

Declared Variable in WHERE clause weird behavior - I'm seeing a huge performance difference between two queries that are almost identical. The only SQL difference is in the...

Need help comparing two execution plans - Hi all, We're testing an upgrade from SQL 2005 to SQL 2008 R2 in our data warehouse environment. I've run into...

(Could not find stored procedure 'msdb.dbo.sp_dts_getfolder'. - sql 2005 - > 2008 R2 - I have a package on 2008r2 and job on 2005 pointing to the package on 2008r2. When i run the...

SQL Server 2008 : T-SQL (SS2K8)

Date Conversion - Hi all I have a date which is in a text file which looks like this '25/02/12 11:39:34' I have been playing...

using OUTPUT on remote server - I have the following: INSERT INTO TableB VALUES(COL1, COL2,....) OUTPUT INSERTED.COL1 INTO AUDITTable(COL1) SELECT COL1,COL2,.... FROM TABLEA This is giving me error: A remote table cannot...

Compare the data in two tables, If Different, Update - Hello everyone I am working on a problem where I need to compare the data in two tables. I can have...

Restrict overlapping records - HI All, Just wanted to know whether can we create a constraint to restrict the overlapping records in the following...

Query to list all jobs - Hello, We are consolidating servers and I was asked to write a query that returns a list of jobs on a...

Log File full / Database Down - I am a newbie and we ran a script that made our logfile drive go to 100%. We tried to...

Interesting Question in SQL - Hi Everyone.... I have a table (no Primary Key defined) with some value and I need to create a view on...

UDF Help - Hi Guys, I want to create UDF. Below is my logic. Please guide me where i am wrong. Alter Function [dbo].[udf_GPList](@EID int,@PID...

How to compare parent child data in SQL - Hi; I have two SQL tables like below; [CODE]T1: relationID, meterID, parentID, childID T2: dataID, meterID, date, amount[/CODE] Sample data of tables; [CODE] T1 T2 -----------------...

Spilt List Function - This must be much simplier than I'm making it... I have an unique idenifier for a person, and a space delimited...

SQL Server 2008 : SQL Server Newbies

MS Access front end to SQL server 2008R2 backend - Hello, We use MS Access 2010 to obtain data from our Sybase\SQL anywhere database. The name of this database is called "e2i". We...

SQL 2008 R2 Problem with TempDB when running SSIS - Hi I have a problem with some SSIS packages. I use SSIS to extract data from a server that is hosted...

change a result into one record - this is the code I used to retrieve a record set. SELECT p.HPROP, p.STYPE, p.SVALUE, NSFFee = CASE WHEN p.STYPE = 'nsffee' THEN p.hvalue...

Case Statement - Good Day To All I'm a Newbie so please be patient. I don't know if this is possible or am I...

Query Using Multiple table - I have two table ACINF and ACPMNTA ACINF COMCODE ACTCODE ACTDESC 3305 130000000000 CURRENT ASSETS 3305 130100000000 Loans 3305 130100010000 3305 130100020000 3305 130100090000 Loan to Others 3305 130100090001 Loan to Others 3305 130200000000 Loan to Employee (RHEL) 3305 130200010000 Loan to Emplyee...

Exclude duplicates, keep the value with the latest date - I'm trying to create a query with multiple columns. The CandidateID column includes values that appear more than once. I...

select to add alternate lines with text as 'go' - Dear All I have a query select Id from abc which returs 1 2 3 I want it to return 1 go 2 go 3 go Is there a way to...

SQL Server 2008 : SQL Server 2008 High Availability

Sqlserver 2008 R2 Mirroring failed to failover when the primary server got stacked. - Question: Sqlserver 2008 R2 Mirroring failed to failover when the primary server got stacked. Environment: 3 virtual servers: Primary, secondary and witness No...

SQL DTC Confusion - Hi All I am trying to figure out the ins and outs of DTC and how SQL uses it. Does SQL only...

Adding 2nd Node to SQL 2008 Enterprise Failover Clustering - Hello, I followed this link for installation: http://www.mssqltips.com/sqlservertip/1721/install-sql-server-2008-on-a-windows-server-2008-cluster-part-4/ Everything was alright for the 1st. Node. Then, I try to add 2nd node, in the...

Configuration sql 2008 cluster on san storage - Hi All, Iam unable to proceed with MSDTC storage configration where it fails with the folowing error: No disk exists in available...

SQL Server 2008 : SQL Server 2008 Administration

Backup times... - Hi, I have a 4.5GB database that takes about 67sec to restore and 30sec to backup. Since it has a lot of...

Memory Clerks Discrepancy - Hi all Environment: SQL 2008 Ent SP3 I am noticing a discrepancy in my Memory Clerks for SQL Plans (Adhoc plans) Running this...

Identifying Machine\IP - Hi Experts, We had a situation in which one user fetching data . We got machine\username but searching with that value in...

Can't Kill SPID “Transaction Rollback in Progress” - I'm running into frequent Blocking on a Development Server. I have been correctly the code or making recommendations to prevent this...

Dead lock issue - Hi, I am seeing lots dead lock. I am working on it to resolve the same. Need your help on this...

data loss for all published tables by adding article to replication? - Hi there, I'm not sure if this is the right forum but I try: yesterday I added an article to our transactional...

Application does not work with named instances?! - So I just have to ask this because I am a bit baffled and flabbergasted by this situation. In 9...

SQL installatin SLIPStrem - I am the first time trying to use the FullslipStream download file to install SQL server 2012. the download is...

find the status of SQL Services on multiple servers - I have a scenario where our DBAs and Server team planned for Maintenance, tonight. We have more than 800 servers that...

Low Performance - High CPU Problem and others... - Hello everyone, I got the Problem, that my SQL Server has always 97-99% CPU usage. We have snapshot isolation mode enabled,...

Career : Certification

Which one is best certification - Dear Friends, I have 2.5 years experience in SQL Development and BI tools (SSRS,SSIS). Which one Certification is best for me. and...

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

Programming : Connecting

Execute a sql job from AS400 command line - I am trying to use this command to execute a sql job from an AS400 command line. I am having...

SQLServerCentral.com : Anything that is NOT about SQL!

The Beer Cooler Thread - On popular request - at least one! - a dedicated topic about beer. I shall start with my favorite beer: Duvel. It is...

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

Query to Select data from a single table with mutliple conditions - Hi, I have a table with the below column headings. Business Unit Document ID Document Date Year Period Account Descr Dept Product Project Analysis Code Affiliate Currency Base Amount USD Amount Line Descr Reference Vendor Vendor Name Module Doc Line # AP Distrib...

Thumbnail Sharepoint problem with reports from SSRS - Hi, My question is related to SSRS but not about SSRS, anyway, i want to display my reports in the thumbnail...

Reporting Services : Reporting Services 2005 Administration

double hop issue for one db server and not for other - i have report server/iis installed in one machine and different db servers (locally on another machines and in branches ). to...

Data Warehousing : Integration Services

String to Date conversion with day name - Hi I have a csv file I use as a datasource and I need to pull the data into a table I'm...

Paramerters on DataFlow Task - I have a scenario , where I need to fetch data from Database 1 (on Server 1) and insert into Database...

need to upload file daily cumulative - I've been tasked in creating an ssis solution which extracts data once a day, loads specific fields and exports them...

Data Warehousing : Strategies and Ideas

Data flow mapping - ETL - Hi, I was wondering what do you guys use to map the data flow in ETL. I am looking at the...

Data Warehousing : Analysis Services

SSAS Security - Hey I’ve a cube where I’d like to implement security access on different measures and calculated members I've a calculation...