In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
sqldbabundle 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.
 
deploymentmanager NEW! Never waste another weekend deploying
Deploy SQL Server changes and ASP .NET applications fast, frequently, and without fuss, using Deployment Manager, the new tool from Red Gate. Try it now.
 
SQL Backup Pro SQL Backup Pro wins Gold Community Choice Award
Find out why the SQL Server Community voted SQL Backup Pro 'Best Backup and Recovery Product 2012'. Get faster, smaller, fully verified backups. Download a free trial now.

In This Issue

Fun with ACID

What is ACID and why should we care? A look at the principal that drives all database and DB management software design: A.C.I.D. More »


Handling Errors in SQL Server 2012

The error handling of SQL Server has always been somewhat mysterious. Now at last, the THROW statement has been included in SQL Server 2012 that, when combined with the TRY...CATCH block, makes error handling far easier. Robert Sheldon explains all. More »


Free eBook: SQL Server Execution Plans

Every Database Administrator, developer, report writer, and anyone else who writes T-SQL to access SQL Server data, must understand how to read and interpret execution plans. This book leads you right from the basics of capturing plans, through how to interrupt them in their various forms, graphical or XML, and then how to use the information you find there to diagnose the most common causes of poor query performance, and so optimize your SQL queries, and improve your indexing strategy. More »


From the SQLServerCentral Blogs - Using New Horizontal/Vertical Tab Groups

This isn’t really one of those features of SSMS that I’ve used a great deal. At least didn’t until recently.... More »


Editorial - Serious Security

There's an interesting piece at Wired on hackers and their impact on security. Despite the constant hacks and cracks of passwords, the regular lectures and pieces written on the subject, many people refuse to use separate passwords on every site, or choose long passphrases, or implement many of the best practices that are published on security. Some of that might be a lack of knowledge, but much of it is likely explained by behavioral economics.

Most people are never hacked and don't have issues. Even if they do experience some problem, they can often recover fairly easily. Lots of hacks are just annoying, like using your email account to send SPAM, akin to random vandalism. As a result, many people don't bother to change their habits. It might also be a tendency that's hard-wired in our personalities. I've educated friends on passwords and given them Password Safe. A few use it religiously, but others keep forgetting, preferring to keep regenerating and changing passwords or re-using them.

To solve some of these issues and create the behavior that we want, there are suggestions in the article for software designers. One is requiring stronger mechanisms up front, another suggests perhaps changing from alphanumeric pass-phrases to image based ones. One poses the idea of enforcing penalties on users. These might be ways in which we can convince software users to take security more seriously. If it's true that software is eating the world, then perhaps the designers and developers should do their part to help make sure security is a part of the new world.

» 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 result of the SELECT COUNT statement at the end of this code?


CREATE TABLE TblA1
  (
   col1 INT ,
   col2 VARCHAR(10)
   );
GO
CREATE TABLE TblA2
  (
   col3 INT ,
   col2 VARCHAR(10)
   );
GO

INSERT INTO TblA1(col1, col2)
VALUES (1, 'A'),(2, 'B'),(3, 'C'),(4, 'D'),(5, 'E'),(6, 'F');
GO
INSERT INTO TblA2(col3, col2)
VALUES (1, 'A'),(2, 'B'),(3, 'C'),(4, 'D'),(5, 'E'),(6, 'F');
GO
select COUNT(1)
 from TblA1
 where col1 in (select col1 from TblA2 where TblA1.col2 = TblA2.col2)

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.

SQL Server Hardware will provide the fundamental knowledge and resources you need to make intelligent decisions about choice, and optimal installation and configuration, of SQL Server hardware, operating system and the SQL Server RDBMS.

Pick up your copy of this great book from MVP Glenn Berry at Amazon today.


Yesterday's Question of the Day

The TRY…CATCH construct cannot be used in a user-defined function.

Answer: TRUE

Explanation: This is true. A constraint with the try..catch syntax is that it is not allowed in functions. Please refer to http://msdn.microsoft.com/en-us/library/ms175976(v=sql.90).aspx

» Discuss this question and answer on the forums

Pro SQL Server 2012 Practices

Expert SQL Server Practices 2012 Edition is an anthology of high-end wisdom from a group of accomplished database administrators who are quietly but relentlessly pushing the performance and feature envelope of Microsoft SQL Server 2012. With an emphasis upon performance—but also branching into release management, auditing, and other issues—the book helps you deliver the most value for your company’s investment in Microsoft’s flagship database system.

Get your copy from Amazon today.


Featured Script

GetDateInString

This function will return a value of date if found within a string, the date format in the string will vary. If no date is found a null value is returned 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

Sql server Agent stops working - Hi all, I have been seeing a strange issues.We have a Sql server 2005 Enteprise edition installed on a windows 2008...

Missing green arrow on the sql server icon in object explore - Hello everyone I have tried to Google this issue for quite awhile and can't find any definitive solution. Now in my...

SSRS Dynamic path for target folder - Hello, I have a monthly report that needs to be delivered to a file share. The catch is...the folder is different...

SQL Server 2005 : Backups

Transaction Log Backup Job Failure - does it break chain? - Never had a "set-in-stone" answer on this one and I've, fortunately, never had to find out the hard way, but...

SQL Server 2005 : Business Intelligence

WMI Data Reader Task - Access Denied - remote - Hi folks SSIS - 2008 R2 on a Windows 7 machine accessing a remote server - which I think is 2003. I'm trying to...

SSAS - I currently use Integration Services and Reporting Services quite extensively to query and populate data from SQL. I've always been...

SSRS 2005 Multi-Value Filter. - Hi all, I met with a very tricky problem. I have a muti-value filter in my name whereby user are able...

same measure in 2 cubes, but not displaying data in one cube related reports only - Hi , I have a measure called "Stock Sales COGS Local Currency" in sales cube and monthly cube. product levels are 6...

SQL Server 2005 : CLR Integration and Programming.

Regular Expressions - I am looking into writing a regular expression for validating emails. I am an absolute novice at this, could some...

SQL Server 2005 : Working with Oracle

Linked Server to Oracle - I have set up a linked server to Oracle 9i from SQL server 2005. This works fine when working with...

SQL Server 2005 : SQL Server 2005 General Discussion

getting Email addresses from Text - Hi Guys, I have written a very hasty piece of code and have not been given much time to review this...

Problem setting up linked server - First I am working in a virtual environment with 2 virtual servers that were created with exactly the same specs. Server...

SQL Server 2005 : SQL Server 2005 Integration Services

Demanding and difficult logic qns PART 2. Sorry but i need help. - Hi all, Please read the following attached word doc. Inside there is a target table with 6 columns which is...

SSIS Script task to change table name in data flow task - Hello, We need to change the table name of 'OLE DB Destination' task. I am not able to use variable due to...

SSIS Package hangs without any errors - Hello all, The package I've been working on just hangs now without any errors in the execute process window or the...

SQL Server 2005 : T-SQL (SS2K5)

Test results report - I am trying to generate a report showing which questions were missed on an online test for a given student....

SQL Server 7,2000 : Administration

Lock tabels - Hello, Can I put tabels in read only mode? Thank you Julia

SQL Server 7,2000 : Performance Tuning

High Volume of SQL Compilations/sec - hi there, We currently have an issue where on one of our SQL Servers, there is a high number of SQL...

SQL Server 2008 : SQL Server 2008 - General

In recovery message along the database name - I am using SQL server 2008 R2 express. Sometimes i see that only some database show "(In recovery)" along its...

Replace tokens for all job responses -SQL Agent - Can somebody help why [b]Replace tokens for all job responses to alerts[/b] need to be checked for WMI alerts ?

Unable to install sql server 2008 Management studio - Dear Sir/Madam, I downloaded Visual Studio 2010 from microsoft so its automatically installed sql server 2008 R2. In Sql server 2008...

How to tell Version installed with only SSAS installed - Hi all, I have to report on which editions and versions of SQL we have installed in our environment. I...

How to get list of affected tables in an Query - Hi Guys, First post here. Have a problem trying to build a work around for SQL Azure not supporting SELECT INTO... As...

truncate and load - Hi all, Is there any way to truncate and load the data when the tables have relationships. like disabling all the...

SQL Server usage in Financial Services - Hi, I am (still) often challenged about having SQL Server as the underlying DB for high volume applications (lots of transactions,...

Linked Servers Information - Dear, In the following figure/link, it shows two linked servers information. In the selected columns, it shows the opposite value to...

Problem in executing distributed query - Dear Sir, I have configured a linked server using SSMS between two sql servers 2008 R2. Now I wrote a trigger...

SQL 2008 and SQL 2012 licensing - Dear all, Let me make a couple of questions that made me confused alot. (1) We have some softwares that only...

Process Architecture Optimization - I have this scenario where we have som huge set of data in Sybase. Now there is a procedure which...

Partition Splitting Problem<Changed> - Hi All, I have monthly partition (Size 150 GB per month). I forgot to create partition for January-2013. So all the...

Monthly Aggregation - I've created following code: [code="sql"] select DATEpart(Mm,(Convert(Date,semua_tkh_Daftar_txt,103))) as MonthNo, DATENAME(Year,(Convert(Date,semua_tkh_Daftar_txt,103))) as Year, DATENAME(Mm,(Convert(Date,semua_tkh_Daftar_txt,103))) as Month, COUNT(doclinkid) as Registered, sum(case when REPWPK.T_BMAL.Semua_Status_Kod like 'S%'...

pdf generation in sql - i have a simple table with 3 columns and 3 rows. is it possible to generate a pdf document by directly...

SQlserver p2p replication 2008 conflicts - Hi, We have SQlserver 2008 R2 sp1 p2p replication with two nodes. Couple of weeks before we noted the replication...

transaction log backup - point in time recovery - Hi Guys, Just curious will point in time recovery work in this case? (1) Monday 2am - Transaction log backup via maintenance plan...

arcserve backup - Hi Guys, Is the below achievable via arc serve? Let's say currently I have the below backup strategy: 1. Daily full db backup...

My BiPolar Users aka Sort a AlphaNumeric Field as if its Numeric - My users wanted a a field to be alphanumeric. That's fine. But they also want it to be sorted as...

Could enabling "Optimize for Ad hoc Workloads" help performance some? - I have a specific question on this, and have done some digging. The application our Devs are working on seems...

Why am I getting deadlocks? - I'm pulling out my hair. The cluster is 2 nodes with dedicated HP blade,s 8 cores, 32gb of RAM, 4x1gb...

Partition Switching Problem - Hi All, I have monthly partition (Size 150 GB per month). I forgot to create partition for January-2013. So all the...

Unable to qualify columns wthin temporary tables - Hello, It appears that i cannot qualify columns within temporary tables. The following example generates an error: [code="sql"]DECLARE @MyTable TABLE ( COl1 INT, ...

Openrowset problem - Hi all, Is that possible to select The data from excel 2007 on sql server 2008 r2(64 bit),os Windows 7...

Import Excel data into DB automatically using scheduler...but DataSource excel changes daily - Hi, I was to import excel data into a table in SQL Server,regularly on a scheduled time. I was able to...

how top clause works - I found that TOP clause works little randomly with Select TOP 50 percent * from Tablename as it is not giving exact...

How to join Values - Hi Professional , Create Table, [code="sql"]CREATE TABLE abc (num int)[/code] Insert Into [code="sql"]insert into abc values (1010) insert into abc values (111) insert into abc values...

Error creating new db; "application is not configured for this database" - I have a db that I am trying to export from a slightly older version to a newer version. Vendor...

Insert multiple lines in a single row - Dear, I want to insert multiple lines in a single row in SQL Server 2008 R2. I have tried. But it's...

Problem with using SQL Server linked server to MySQL database - I have a MS SQL Server that does a lot of data manipulation and staging and then I need to...

SQL Query - Hi, I have an issue with the SQL query I have created. The result that this query should generate should be...

Trigger Help - Hi, I want to create trigger when i fore below code it should keep track of it.. SP_CONFIGURE 'SHOW ADVANCED OPTION',1 RECONFIGURE go SP_CONFIGURE...

point-in-time restore of a database requirements - Hi all I have a question about a point-in-time restore of a database. I thought I understood all about backups years...

A network-related or instance-specific error occurred while establishing a connection to SQL Server - Hi, The below error is logged into application logs intermittently.But, when I review SQL Server errorlog, event viewer application log, system...

argg.. UTF-8 troubles - I'm importing a UTF-8 flatfile through ssis and when I use the normal ado.net target connection to import the process...

SSMS Add-in for Extended Property MS_Description - Just wondering if anyone has seen any SSMS add-in for easily adding / updating Description meta data for different objects, mainly...

SQL Server 2008 : T-SQL (SS2K8)

Need a backup script to get backup history status for the latest backup from all linked sql servers - Hi Guys, I am actually in need of a t-sql or a powershell script that will help me get the latest...

Partition Switch to another database - is it possible to switch the data from one database to another. See the below test code [code="sql"]USE [AdventureWorks2008R2] GO ALTER TABLE...

how to concatenate row columns and joined in another table - Hi to all, Please bear with me. anyways, this is the tables i have Table1 Name Company OtherVisaID Mike ABC 1 Joey CBA 2 Table2 OtherVisaID TypeID...

how to concatenate rows and join in another table - Hi to all, Please bear with me. anyways, this is the tables i have Table1 Name Company ID Mike ABC 1 Joey CBA 2 Table2 OtherVisaID TypeID...

Wrong Execution Plan question - Hi, Our developer send me a query that takes long time to run. it has a query structure like this, select T1.*...

How to find (or create) the stats_stream, rowcount, and pagecount used in Update Statistics - I have been asked to script out the creation of specific tables in the database including primary key, nonclustered indexes,...

Not getting results expected from a Group by query - Hi All, Hope you can shed some light. I'm a little puzzled right now. I have the following query: SELECT 'Independance' as...

I'm trying to create a proc that will drop and create a function - To keep this simple i'm going to include an example however there are actually several scripts that i need to...

orphaned extended properties in SQL - Is it possible to have orphaned extended properties in SQL. And if so what is best practice to clean them up? And...

Pivot to produce a timetable - Hi everyone, I'm looking for a fix for an issue I'm working on. I've got a list of locations, start and...

Please help me understand this SQL - The code below is from a SQL trace I did to find out how our ERP system generates a gross...

Better Way to Perform this Query - Hello Everyone I hope that everyone is having a very good day. I need to write a query to select row counts,...

SQL Server 2008 : Working with Oracle

Need help - MS SQL Trigger for Oracle insert - Hi, is it possible to use a ms sql trigger to insert a row in Oracle Table ? I use this way...

SQL Server 2008 : SQL Server Newbies

Rowversion Duplication - Hi All Most off our tables have rowversion columns. We whant this row version to be unique through out the DB....

UDF Table - Hi i have little experience with sql server and i have just been thrown into the fire at my job...

SQL Server 2008 : SQL Server 2008 Administration

Why NULL values in sys.indexes - Hi, I queried the sys.indexes on a old DB. In the name column of this table i found NULL value.I am...

Just Deleting data will reclaim space?? - Hi Experts, We have deleted 144101324 records from our WH database will it release the space to disk? Thanks in Advance.

central management servers - I setup a sql server instance to host central management servers. Then I added all the sql servers to diffrent group...

Best way to Monitor for blocks every 2s - We have process that has a 4 second SLA for the round trip to and from the Web Services server. I...

Slow performance remains after query rebuild only gets resolved after updting statistics ? - 1 of the query's of a colleague of mine has been slowing down recently. He looked to the execution plans and...

Size of the ldf file increasing - I have some sql server database for which recovery model is set to full.Now for some databases the ldf size...

Is adding a User to the master Database a bad Idea? - I am setting up the Resource Governor on one of my servers and it creates a function in the master...

Programming : Powershell

Invoke-Sqlcmd Count - Hello, I searched for this on here and google and didn't see an exact answer to my question, so sorry if...

Help me Error SSIS Error (Agent Service) - An invalid reporting option was specified. Only E, W, I, C, D, P, V, and N are allowed Import Data Source...

SQLServerCentral.com : Anything that is NOT about SQL!

VB.net error - Hi, I get this error when I build my code: "Handles clause requires a WithEvents variable defined in the containing type or...

Employer asking for Social Media login? - I've never experienced this but found it bizarre. If I was ever asked to provide access to my social media...

Something random to ponder - So I had a funny thing happen to me a few minutes ago and couldn't help but share... QA Tester 11:54...

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

Multiple parameter ODBC - Hi I have created a report in BIDS 2008 that connects via ODBC connection. I am having issue with the parameters for...

Parameters in subreport - I have main report and subreport that is called from the main report. Subreport can be called multiple times depending...

Change hyperlink text - Using SSRS 2008 Standard. I'm not seeing a way to manipulate the hyperlink text. What I get is an entire URL...

Use subdomain to resolve Reports Manager - Using Server 2012 / SQL2012 how would i go about directing subdomain.domain.com to the actual "http://computername/reports" ? I already have the subdomain...

Reporting Services : Reporting Services 2005 Development

sql2005 - when i try to execute a query i got this message Joined tables cannot be specified in a query containing outer...

Database Design : Design Ideas and Questions

Building my first data warehouse. Advice welcomed :) - Hi all, I've been brought on board to help a company start to build their first business intelligence setup. I'm a...

Data Warehousing : Integration Services

Couldn't apply special formats for the data.This is the erroe i receive while i try to do it.. - Hi, I am trying to apply special formats attributes available in excel to my target file which is excel too. The process...

SSIS Package Not Writing all the Rows to OLE DB Destination Although shows the complete numbers on dataflow Path - my SSIS package loads data from excel file to SQL server table. The excel file has 5000 rows. When I execute...

Data Warehousing : Analysis Services

Measure between dates - I have a campaign dimension ( Its a marketing campaign for a product ) and i have to measure the sales that...

Why is the results take longer in MDX than TSQL? - Can anyone tell me why this simple MDX query takes 9 seconds and TSQL takes 5 seconds? -------------------- MDX ------------------------------- select null...

multi lingual capacity enterprise vs standard edition - Our SSAS pilot was started using the fully-fledged developer's edition where we added 2 additional languages. We tested the testcube...

Deply aggregations - Hi, I'm using SQL server 2008 R2. Monthly a job creates new 'month' partitions. So this partitions are not in my BIDS...