In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
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 Backup Pro Get compressed, verified, secure SQL server backups
Use SQL Backup Pro's automated scheduling to get faster, smaller backups. Then verify your restores using DBCC CHECKDB in one easy, automated process. Download a free trial now.
 
SQL Skills Deep technical training by world-renowned experts in 2013.
You can't get better ROI for your training budget. Read more.

In This Issue

Using XML from data variable option in SSIS

Learn how you can manipulat XML documents solely in memory. No file required.  More »


SQL Saturday #220 - Atlanta

A free day of training this weekend in Atlanta, GA. More »


Forwarding Pointers in Heaps

When no clustered index is defined on a table, that table is said to be a Heap. Heaps are not ideal when it comes to performance but there are lots of instances where you have heaps.  More »


From the SQLServerCentral Blogs - DTA and Hypothetical Indexes

For those of you that don’t know DTA stands for Database Engine Tuning Adviser and is available from the Tools... More »


Editorial - The Challenger

There's a lot of competition among database platform vendors. Once a platform is chosen, companies rarely change, and with good reason. The cost of the people building your application accounts for most of the resources you expend on a database system. Re-training, or replacing, is often cost-prohibitive. That hasn't stopped many companies from looking to less expensive alternatives to SQL Server, like MySQL, but I'm not sure the license savings offset all the other costs, including potential performance declines from mis-configured systems.

SQL Server has had a few competitors over the years that tried to provide compatibility and enable easy or seemless migration from SQL Server to a new database engine, but none of them seem to have been very popular.

NuoDB is the latest, providing a NewSQL, distributed cloud database that has many of the features that developers look for, but is built to integrate easily with .NET technologies, including Visual Studio, has LINQ and Entity Framwork compatibility, and runs on the Azure and AWS platforms. The company hopes it will replace SQL Server as the preferred database in the Azure IaaS cloud.

I don't know that many companies want to migrate their applications to a new platform, but I do know that there might be situations and problem domains where platforms other than SQL Server make sense. If the compatibility is close enough to limit the amount of code that has to change, it's worth looking at. The problem for this challenger, and many others, is that SQL Server has grown to include many other features, like SSIS, that companies find compelling.

Ultimately I think it's easier to stick to as few platforms as possible to allow your staff to build expertise in optimizing their code and configurations for a platform. Developers and administrators don't often become experts, but I'm not sure the situation is any better if you give them an additional platform to work with.

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

I have a table dbo.MyTable with contents like this:

I then run this query:

SELECT CASE
         WHEN ValueCol < -10 THEN 'Very negative'
         WHEN ValueCol < 0   THEN 'Bit negative'
         WHEN ValueCol = 0   THEN 'Zero'
         WHEN ValueCol <= 10 THEN 'Bit positive'
                             ELSE 'Very positive'
       END AS Category
FROM   dbo.MyTable
WHERE  KeyCol = 5;

What will happen?

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

This question is worth 2 points 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

I have a table dbo.MyTable with contents like this:

I then run this query:

SELECT CASE
         WHEN ValueCol < -10              THEN 'Very negative'
         WHEN ValueCol BETWEEN -1 AND -10 THEN 'Bit negative'
         WHEN ValueCol = 0                THEN 'Zero'
         WHEN ValueCol BETWEEN 1 AND 10   THEN 'Bit positive'
                                          ELSE 'Very positive'
       END AS Category
FROM   dbo.MyTable;

What will happen?

Answer: I get five rows of output, with four different values (one value is returned twice) for "Category"

Explanation: The expression "(something) BETWEEN (start) AND (end)" is equivalent to "(something) >= (start) AND (something) <= (end)". This will never evaluate to True if "(start)" is higher than "(end)". As a result, the expression "ValueCol BETWEEN -1 AND -10" is never true, so that value can not be returned by the CASE expression. SQL Server does not consider this an error, nor will it raise a warning.

A CASE expression evaluates the WHEN conditions in order. If none of them evaluate to True, the ELSE expression is returned, or NULL if there is no ELSE expression. Because of the "incorrect" BETWEEN, the row with ValueCol = -2 will evaluate all WHEN expressions to false and then return the ELSE expression.

References: BETWEEN: http://msdn.microsoft.com/en-us/library/ms187922.aspx
CASE: http://msdn.microsoft.com/en-us/library/ms181765.aspx

» Discuss this question and answer on the forums

SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.


Featured Script

Generate a random password

If you ever need a quick way to generate random passwords, this is a pretty useful way to do so. 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

sysJobActivity showing jobs from years ago that never ended - I've noticed that sysJobActivity shows a number of jobs where the stop_execution_date is NULL, but the run_requested_date is up to...

Stuck with this update statement - I have a update statement which is killing my PLE( Page Life expectancy), it is dropping from 4000 to 120....

'sp_MSforeachdb' expects parameter '@command1', - Hi, I get the error msg "Procedure or function 'sp_MSforeachdb' expects parameter '@command1', which was not supplied." when i run the...

SQL Server 2005 : Backups

Differential Sizes Increase After Index Rebuild - Hi Folks I am trying to understand why my differential backups are almost as big as my full backups after rebuilding the...

SQL Server 2005 : Business Intelligence

SSIS-Data in multiple .csv files to columns in same csv file - Hi Everyone I have the below problem,could anyone please suggest me a solution. file 1 has below data 1.csv A B C 2.csv E F G the data in the output...

SQL Server 2005 : Development

I need to create an XML file populated by fields data(eg: SQL Server DB table) - I have the following XMLSchema template: <?xml version="1.0" encoding="UTF-8"?> <DataMarketsFeed xmlns="http://www.test.co.uk/XMLSchema/DataMarketsFeed-v1-2" xmlns:cmn="http://www.test.co.uk/XMLSchema/DataFeedCommon-v1-2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <DataFeedHeader xmlns="http://www.test.co.uk/XMLSchema/DataFeedCommon-v1-2"> <FeedTargetSchemaVe

SQL Server 2005 : SQL Server 2005 General Discussion

HOW TO JOIN 2 DIFFERENT MYSQL TABLES WITH A SQL TABLE - I have the following query, I have 3 tables in the first tabel1 (tester.f_artclient) there are 3 colums : name, sellingprice, categorie...

Combining two query result sets. - Well I tried this in the reporting services forum got 1 reply and it seems to have fallen into the...

Updating field - Good day, I am having trouble with my update query. I want to update txtReferrence but the update value will be the...

PROFILER - Trace Templates that include Groups - Does anyone know if it is possible to save to template a Trace file that includes 1 or more GROUP...

SQL Server 2005 : SQL Server Express

Failed to generate a user instance of SQL Server. - My SqlExpress application was running fine, I renamed the ASPNETDB database (it was called [C:\inetpub\wwwroot\website1\App_Data\ASPNETDB.mdf] and the application ran ok....

SQL Server 2005 : SQL Server 2005 Integration Services

Paramater in AS400 Source - Currently I have an SSIS Package Truncates a Holding Table. Loads all of the records from an AS400 Table using...

Process data with special characters - Hi I am having difficulties with data that has hidden characters. I have the following and need to transfer this data...

SSIS Package Optimization - Folks, We are taking Oracle Source data using Data Flow Task in which Data Reader as Source [We have Separate DSN...

SSIS Integration Task - I have used SSIS a while ago where my requirement was to create a new table out of the excel...

configuring ssis pakage for environmental variable - I want proper steps to configure package for environmental variable and deploy that package and run it on another machine...

scheduling ssis pkg using sql agent in sql2005/2008 - I have deployed a ssis pkg that only has a script task showing messagebox. dabaseeployment was successfully done in sql database.now...

error row disposition on "output column in SSIS - I am importing Data from a SAP table to a SQL Table. Just a straight loading into SQL Table no...

SQL Server 2005 : T-SQL (SS2K5)

how to check if a user is an admin or is a guest? - hi i want to specify if my user is a member of administrator or it is a guest. how can i...

SQL Server 7,2000 : Administration

SQL VDI issue - Hi , Am using SQL server 2005 version in my project.. We have configured the backups using the Data protector tool. For...

I would REALLY like some fast help with this problem! - [font="Times New Roman"][size="4"][b][center]I would really like some help with this annoying longlasting problem[/center][/b] [b]So i have built my own computer, and...

SQL Server 7,2000 : T-SQL

Create FInancial week Dynamic based on parameter - Hi all, I need to generate the financial week but in my case the starting month of the financial week...

SQL Server 2008 : SQL Server 2008 - General

DR - Quickest way to recover system databases. - I was tasked to do a DR rehersal with one of our test SQL 2008 R2 server last week, while...

UpsizingWizard - Trying to use the Upsizing Wizard and link our Access 2007 tables into our SQL Server 2012 but we get...

To Split SP or Not to Split SP - Hi I have a question regarding the performance gains (if any) of splitting an SP into smaller SP's The procedure in question...

row_number with recursive cte and update - I am sorry if this has been answered before - I have seen and read many articles about row_number and cte...

1TB TempDB too big - Hi All, There is a set of stored procedures that is executed as a separate module. At the time this module...

Chinese Language and Decoding. - Dear Gurus, Your suggestion is required. Our team wants to override “windows regional language Settings” for Chinese Language by applying encoding...

query with linq generate different execution plan... - Hello I have a simple linq query : from c in list where DATE >= DA && DATE <= A select c.ID).Distinct().Count() When i run this...

How to display Financial Year wise Summary - Hi, I had requirement,I want a report in sql server 2008 as follows, Total PayDate Amount 2000 20110401 1000 20110501 1000 ...

Most recent write or read of tables in the database - The following query can be used to find out the most recent read or write of tables and procedures in...

Replicating GROUP_CONCAT Function of MySQL in SQL Server - I have a table named ss_1 that has columns as roll_no, name and marks. I need to display the marks...

SQL Server 2008 Audit - Hi Guys, I wish to audit all the activities performed by users having sysadmin role. Is there any straight foward way to...

New Alert for disabling jobs. - We have nearly 30-40 different servers and would like to create alert for jobs that are disabled. Need to create...

Updating a column in batches - [b]Background: [/b] I've added a new column to a table with some 800 million rows. I need the column populated with...

Parse Expression Error - Hi All, I have the following expression in a Precedence Constraint Editor [code]@Table_Number == "3290056" || @Table_Number== "3290057" || @Table_Number == “3290058” || @Table_Number == “3290059” || @Table_Number...

SSIS - Special characters in the data - Hi, I am having trouble exporting data to a flat file (.txt) because the fist line has special characters. [code="sql"] SELECT 'StaffCat, ' + 'EXCONUM,...

Indexed view - Hi I have a table which is large and we just need the last 2 month of data.I want to...

Copying database objects - How to copy the database objects (tables, views, Stored procedures, functions, schemas) from one database to another database of different...

Data flow task error in SSIS - Hi All, I am getting the below error in data flow task in SSIS Source: "Microsoft SQL Server Native Client 10.0" Hresult:...

Hash warnings - Hi - Can anbody advise me on the following: I am using red gates sql monitor and every night during the nightly...

Update Values on one table based on another table - TRIGGER ??? - Hi, I'm already sorry (especially if Sean is answering again) ... I am quite new to this whole posting stuff, but I...

Sending Multiple Independent Messages to Multiple Users - I have confusion regarding how to [b]send multiple messages to multiple users at the same time[u][/u][/b] at periodic intervals(weekly basis...

I know which *file* is being hammered -- how do I find out *who* is doing the hammering? - I have a production SharePoint system with about 100 databases, it just got really slow, users are complaining a lot. When...

Linked Servers - Link drops out intermittently..Grrrr - Hi All, I am experiencing an issue where two servers with matching linked server configurations are connected. They are configured...

SSIS Data flow failing - I have a data flow that has been working for months. Starting this week it will transfer some of the...

VIEW vs. DynamicSQL - Hello, I have few questions, I would like remove DSQL, but our design table is not good, but redesign table is...

Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80004005 - This lovely error happens frequently when I need to add articles to my transactional replication setup. After making the changes...

How to install an oracle linked server on SQL Server 2008 r2 failover cluster manager - How to install an oracle linked server on SQL Server 2008 r2 failover cluster manager I'm reading some articles and applying...

SQL Server 2008 : T-SQL (SS2K8)

Data Manipulation on Read Only subscribers - Hi Friends, Although the situation is not too tricky on its face but would request some feedback for implementing a correct...

valiate each parent intermediary is also a parent to itself - I have a table for which I want to validate where each parent intermediary is also a parent to itself....

Quering Code - Is there anyway to query all your Stored Proc's, Views, and functions? I need to check to see what code...

Trigger effect - Hi, I have a scenario... I have a table Table1 with columns ID, Names, WorkLocation I have created a trigger which onupdation...

Why XML Datatype present in SQL Server. - Hi all experts, Every now and then i am learning something new in SQL Server. Latest in that series is XML...

SQL Server 2008 : SQL Server Newbies

Linking tables - When linking your access tables to SQL what issues would you make sure to deal with before linking the tables?

THE CASE of the overwriting logic - Hi All, I have a CASE statement that checks if the date column is NULL or NOT, if there is a...

Difference in SQL - What is the difference between just SQL and T-SQL?

SQL Server 2008 : Security (SS2K8)

When to use Transparent Data Encryption (TDE)? - My company has been getting a lot of requests from our clients to put into place more stringent security practices....

SQL Server 2008 : SQL Server 2008 High Availability

Automatic Failover of Replication with Mirroring - Morning Everyone :-) I know this is a topic that has been widely discussed and I have read through a few...

Mirroring failover : SQL Server 2008 R2 - We use database mirroring ( high safety) for our HA needs. Our application uses db_datareader & db_datawriter role for running the applications. I had...

Async Mirroring - Large unsent log, but status is still "Synchronized" - I have a database mirrored using async mirroring. Last night "something" happened to cause a large amount of unsent log on...

Mirroring with replication question - Before I watched CBT Nuggets dicussing replication & mirroring. In it he described the following scenario (pictured below as I drew...

Ownership of cluster disk 'Cluster Disk xxx has been unexpectedly lost by this node. - My Cluster went down again. I don't have to say... I am having a not so good morning already ... :-( Here's...

SQL Server 2008 : SQL Server 2008 Administration

MAXDOP settings for OLAP - Hello, Does anyone have any idea what is the better way to set MAXOP settings for OLAP system. Here is my...

DB mirroring between SQL 2005 and SQL 2008 R2 - Is it possible to setup DB mirroring between SQL 2005 as primary and SQL 2008 R2 as Mirror.

TempDB full - if Temp Db full which databases will go Offline?

How to copy .bak files on an external drive which is not part of domain but requires authentication? - We are looking for a solution to copy .bak files on external network drive which does not allow access without...

check missing index - Is there some good queries for checking missing indexes on all tables of a database? THanks

SQl Agent job when query fails to return result - Hello, I am trying to put in place a job which will email me when a query fails to return results:...

Export table in to txt - dear Gurus, I want to export data in table to txt SELECT [StatBeginTime] ,[MDASum] ,[BureauName] ,[MDA] FROM [kpidb].[dbo].[t_MDA_1] the result : 2013-05-13 00:14:00.000 290 bjm 261651 2013-05-13 00:29:00.000 226 bjm 203767 2013-05-13...

Minimum setup for SQL Server Express application - We need to set up remote desktop (or laptop) computers (not in network) with a copy of the master database...

Performance Counters Scripts - Can you please any body give me all Performace Counters script, which generate report including all performance counters in SQL...

Change SQL Notification subject - Hi All, SQL Server Job System: 'TESTJOB' completed on \\Server_Name I wanted to see if I can modify this to send e-mails...

Older Versions of SQL (v6.5, v6.0, v4.2) : Older Versions of SQL (v6.5, v6.0, v4.2)

Linked Server - I have created Linked server through Oracle. Linked server created successfully but data showing only one row. Please do the needful...ASAP...

Career : Certification

70-448! - hello. I am preparing my exam 70-448 and want to do more test. What exams you recommend me? I saw SelfExamEngine and...

70-462: Can the Hyper-V environment be downloaded somewhere? - Hi all, When I just opened the 70-462 TK I saw a six server Hyper-V environment is neccesary to complete the...

Programming : General

The Empty Set vs No Result - So, I've got somewhat of an academic question, but one that might have some practical implications... depending on the answer. So...

SQLServerCentral.com : Anything that is NOT about SQL!

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

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

SQLServerCentral.com : SQLServerCentral.com Website Issues

Sharing good practice or Code - Hi steve, this is just suggestion for consideration. I have used this blog a lot and i read lot of good...

Reporting Services : Reporting Services

Report Manager DSN Problem - Hi I have created a DSN (using sybse db) and develop report in SSRS 2008R2. Reports working fine in BIDS. Now...

SSRS 2008 R2 report manager browser support - Is there a matrix documenting browser support for SSRS 2008 R2 Report Manager? I am able to find the matrix...

Permission by department manager from single report to view. - Hello, We are using SSRS 2005 and I have a report I want to give access to 5 different managers the...

Migrating Reporting Services Subscriptions - Hey all, We are currently in the process of upgrading our systems and migrating reporting services subscriptions also becomes a...

Data Warehousing : Integration Services

Where is the ideal place to install SSIS? - We're currently working on building out a new data warehouse and I'm looking for resources or best practices on where...

SSIS Integration Task - I have used SSIS a while ago where my requirement was to create a new table out of the excel...

Data Warehousing : Strategies and Ideas

High level architecture -- consolidating data from multiple servers - I'm looking for some input regarding how to architect a process for ongoing consolidation of data from multiple servers. Here's the...

SSIS package for DWH Load - Hi All, Please suggest the best option for designing SSIS package for DWH load. Here is the scenario. I have two star schema...

Data Warehousing : Analysis Services

MDX 101 Question - Anyone know why the following doesnt work? SELECT { [DimX].[DimId].Members, [DimX].[DimId].currentmember.properties("value" ) } ON ROWS, [Measures].[Mea_1] ON COLUMNS FROM Measures This keeps throwing the following exception: Query (2, 2)...

How to Release disk space after a cube processing failure - Hi, I have a SQLServer Data base stored on a drive that has abot 10 GB Free space. How ever the...