In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL DBA Bundle ‘Disturbing Development’
Grant Fritchey & the DBA Team present the latest installment of the Top 5 hard-earned lessons of a DBA – read it now.
 
SQL Developer Bundle 12 essential tools for database professionals
The SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.
 
SQL Monitor Optimize SQL Server performance
“With SQL Monitor, we can be proactive in our optimization process, instead of waiting until a customer reports a problem,” John Trumbul, Sr. Software Engineer. Optimize your servers with a free trial.

In This Issue

Converting Large Object Data Types

Eliminate pesky legacy TEXT, NTEXT and IMAGE data types with this handy script. More »


SQL Saturday #212 - Redmond

A free day of training in Redmond, WA this weekend on May 18, 2013. More »


How to Synchronize Two SSAS Servers

Unfortunately SQL Server Analysis Services (SSAS) does not support differential backups and creating full backups for the servers might take too much time. In this tip we are going to show how to synchronize two SSAS servers in order to have a failover server in case something goes wrong with one of the servers. More »


Case Study: Designing Scaled-Out Architecture

On Thursday May 16th 12PM Central time, Robert Davis will discuss public facing websites on a global scale when looking at Data Architecture. More »


From the SQLServerCentral Blogs - Modify a Power View Data Source

I’m currently setting up a demo environment using SQL Server 2012 and SharePoint 2013 (more on that in later blog... More »


Editorial - The Platform Problem

I really like the idea of Azure providing a Platform-as-a-Service (PaaS) for applications to be built on. As I've evolved in my career, I've learned I prefer not to manage individual machines or deal with the complexities of configuring anything outside of SSMS for SQL Server. Working with Hyper-V recently has cemented the idea that I don't become more productive by dealing with the complexity of the Windows host.

However I can't see many customers migrating the majority of their applications to the PaaS Azure service for one reason: there's no competition.

Moving to Azure means placing a big bet that Microsoft will continue to offer the same or more features, more powerful machines, and stability at a reasonable price. Moving to Azure also assumes that you are comfortable working with only Microsoft for the foreseeable future, using their data centers, staff, and products. Any code you write will be specific to Azure.

If you were to use the IaaS services from another vendor, like AWS and their virtual machines, a migration might still require some code changes. However, I'm sure if I contracted for virtual machines from Rackspace or some other provider, I could easily redeploy my application elsewhere. Well, perhaps not easily, but certainly easier than if all my code depended on a platform I can't run inside my own data center. A platform nobody outside of Microsoft can run.

My view is that Azure is a great platform, and one that could explode in usage. If we have choice.

Microsoft should sell us the code to run inside our own data centers, or at least license it for a number of large providers that might want to offer Azure services. Ultimately the success of the platform depends on people choosing Microsoft because it's the best choice, not because it's the only one.

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

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.

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.


Yesterday's Question of the Day

I have a table, Customer, with the CustomerID as an identity. I run this query:

select
 customerID, count(city) as 'City'
 into CustomerCities
 from Customer
 group by CustomerID

Does the CustomerCities table have a column with the identity property?

Answer: Yes

Explanation: The documentation for the INTO clause states that the identity property is not propagated if there is a GROUP BY or aggregate in the SELECT clause. However in SQL Server 2012, this does not appear to be the case. The identity property is propagated. I have submitted a documentation bug for this.

Ref: INTO - http://msdn.microsoft.com/en-us/library/ms188029.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

Union Forcing Distinct

The use of union would seem to force a distinct in each of the select statements being unioned. The use multiple unions, with mixed union and union all clauses, seems to cause haphazard results. 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

Forfiles Utility Error When No Files to Delete - I'm using the FORFILES utility to delete files from folders older than 'X' days. [font="Courier New"]forfiles /p "i:\objectscripts" /s /m *.* /c...

Log shipping 2k5 file retention issue - the files are not being deleted. - The issue I have is with the file retention of the .trn logs in my log shipping drive. 5 days...

Database exception error - Hi, We have an application which access data from SQL DB (SQL server 2005 std. edition). Around 40 members use 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

Loading Fact Tables - Step by Step Instructions Challenge - Dear All, This is my first Data warehouse project and I am having serious problems loading my fact table. I...

Foreign key constraints in data warehouses - I am just learning about data warehousing and business intelligence, and reading Ralph Kimball’s excellent book “The Data Warehouse Toolkit,...

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

how can I get the DTS Wizard? - Hi, My work laptop just died and I am working on a loaner until they can get mine fixed. I...

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

SQL Server 2005 : SS2K5 Replication

index fragmentation - hello experts, i have rebuilt all the indexes of my database but after that still some indexes have the same...

SQL Server 2005 : SQL Server Express

Cross-database query in SQLEXPRESS - The site I am working works on SQL Server 2008, but is being adapted to work on SQLEXPRESS. I am using...

Transaction Log Full in Simple Recovery mode - The transaction log in MSDB is 106% full and Log_Reuse_wait_Desc is 'Check Point'. The server is restarted every night and...

SQL Server 2005 : SQL Server 2005 Integration Services

how to merge single page tiff file to multi page tiff file - Hi I have a requirement to merge single page tiff files stroed in a table to a muli page tiff file... Can...

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

**************DD/MM/YY to MM/DD/YY************ - :crying: Hi, I need help on the following.I have a column 'Date' in Staging table with the following [b]Data type=varchar(50) Format/Style='dd/mm/yy' Example='18/11/07'[/b] I need to...

SQL Server 2005 : T-SQL (SS2K5)

VB.NET TreeView using Stored Procedure - There are 5 tables we have which has a parent child relation we have following tables. Order (Fields Order#, RefType, RefNum,...

BEFORE TRIGGER issue - I know that we don't have access to a MySQL BEFORE trigger but I have a strange issue that I...

SQL Server 7,2000 : Administration

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

Trace Group Simular Statemments - Hey I run serveral traces and have a problem analyzing data I know that there is some scripts out there, as can...

SQL Server 2008 : SQL Server 2008 - General

ssms 2012 express? - Hello - I'd like to download SSMS 2012 Express. It seems like when I google for it and get search results,...

Separating distinct invoices under one sales order - I am having some trouble coming up with a query to do this. I want to distinguish the unique/distinct invoices...

Trying to avoid a curser, getting Primary Key violation - I have a table that holds transactions on specific accounts. It is populated from another table. That being the case, column...

what are your key activities and plans that you are going to implement this year to stanadardise your SQL Server Environment - Hi Experts, Can you please let me know some general activities and plans by which we can improve the SQL...

index question - Hello - I know that indexes are generally used on columns that are referenced in the where clause. I assume the...

Replication - Remove X Alert Snapshot Agent - Want everything nice and tiddy here. Replication snapshot could not start because not enough room because DBA was supposed to...

Memory - Hi, We have two similar reporting servers. Lets assume them Server A and Server B On Server B, one of the ETL...

basic SQL & Crystal reports questions - can any one solve these please I have questions below which are basic questions when I am searching sql and...

Update Table based on a lookup table - I have 2 tables as following. i am trying to do the following update to the 2nd table (Address) 1) Check...

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

Running DBCC Check on my database - May I please get the correct syntax, I am getting this message, Msg 2508, Level 16, State 3, Line 2 The In-row...

blocked by spiD -2 - Hi , I got some sessions blocked by spid -2. But i'm not sure what exaclty is -2 meant by, i...

Problem in configuration of database - i have been using a local database for development purpose . now i was trying to connect the project to the...

Parsing a summary / detail flat file - My team has a flat file from another system that we need to parse and import into 2 tables. We're...

Select Top 10 Query - Hi All, Got a quick Select Top 10 question. I'm running a simple query to find top ten people who...

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

DB Restore confusion - Pocket Consultant - Hi guys, I'm just reading the SQL10 Adminstrator's Pocket Consultant (p 584) and was a bit confused with this part...

Partitioning on an existing large production table - how to decide? - Hello, I have a large production table: - 64 columns - around 800 millions of rows - around 80 GB of data + index (18...

SQL 2005 - Lock time out error - SQL 2005 - Cluster - Agent only/SQL restarted. Before restart, got this message in log, Configuration option 'Agent XPs' changed from 1...

View creation on Linked Server - Hi All, I have had an issue in SQL 2008 SP1 when creating a view in a db bases on...

Returned unique Rows from two different tables with the same ID - Hello, I have two tables ProspectLead and LeadfrontierData this two tables and linked by the ID ProspectLead.ID and LeadFrontierData.LeadID I need three columns...

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

Running DTS packages on 2008 R2 - Hope someone can help me on this one We have a 2008 R2 cluster which failed over recently, a number of...

Urgent Slow Login - Take Too much time to login in sql server. Latch and Buffer I/O are too high. It was working fine since last...

How to arrange Employee manager Hierarchy tree in sql server . - create table #Sample_emp ( Ename varchar(50), EmployeeId int, ManagerId int ) insert into #Sample_emp values ('Faisal Husain',11,NULl) insert into #Sample_emp values ('Deepak Zambre',12,11) insert into #Sample_emp values ('Milind...

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

SQL Condition for SSRS multi select parameters - Hi All, I can't figure out the best way to implement this..so here is my simplified table: [code="sql"] declare @tbl table(Id int identity...

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

How to convince people for small changes - I was checking in my database and found lots of unused indexes. I found it based on summing up the...

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

What is a semicolon used for in SQL Server? - Hello All, I have a co worker that uses the semicolon at the end of his SQL statements like Select * From Table1(nolock)...

SQL 2008 - SSIS - FTP task - I have to download file/s from Ftp server. Can we use 1 SSIS package with FTP task only, to download...

Cannot get to install SQL 2008 R2 on Windows 2003 x64 - Hi, Error is: Could not load file or assembly 'Microsoft.SqlServer.Configuration.WizardFramework, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The module was...

Low Batch Requests/Sec? - Hi All System Info... Server. 12G RAM, 2xQuad Core processors. Hard Drive C (RAID 1), Hard Drive E (RAID 10). 1G NIC SQL...

SQL Server Agent can't start - Hello, I have these messages while trying to start the Agent. Knowing that a day before, it worked without any problems...

SQL Server 2008 : T-SQL (SS2K8)

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

facing problem in configration of database - Hey! I have been using my local database for dev Purpose . Now i m trying to connect my application with to...

update MySQL from SQL Server - Hi All, I have managed to set up a linked server to a MySQL database and i want to update the...

Extracting files from "binary" stored in TEXT datatype - I've been asked to look into extracting files (mostly jpg or pdf) from a database where the files are stored...

Writing to local variable from CTE - Can I write to a local variable from inside a CTE? I am trying to get a comma delimited string of...

Performance Tuning - Hi, I am working on tuning an sp . I tried to remove the cursor and get the values in a...

Simple derived table with multiple rows - I'm having trouble creating a simple derived table. It is so simple to create a derived table with multiple columns...

Another grouping problem - I have found a bug in my duplicate macthing application and I believe it's with the way I group my...

Add 20 before regular expr (year) - Hi All, I am getting input file below format. Text-dd.mm.yy. i need to display as 20yy (year) and mm.dd (Issue) For that i...

Join creating two records - I have a join that is doing something that I can not explain. I have two tables.. linking on Delivery No...

SQL Server 2008 : SQL Server Newbies

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

SQL Server 2008 R2 Data Collection reports - I am new to the Data Collection Utility. It looks as if it would provide valuable Trending information as well...

Create index query for all tables - Dear All I wan to to drop and recreate indexex on all tables using query. I have drop index syntax as follows SELECT...

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

Restore is not happening - We have configured the log shipping. here backup job and copy jobs are working fine but restoring is not happening in...

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

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

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-448! - hello. I am preparing my exam 70-448 and want to do more test. What exams you recommend me? I saw SelfExamEngine and...

Programming : Connecting

Connecting to SQL Server from a Visual Studio 2012 Express Class - Can anyone tell me how to create a connection to SQL Server from a class created in VB in Visual...

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

Reporting Services : Reporting Services

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

Data Driven subscription issue - Hello, Can you please help me to solve following error for DD Subscription: While running the subscription it shows the status on...

Database Design : Design Ideas and Questions

Is Having Primary key on a table essential? - Hi everyone We are using Sql Server 2008 and we have a log table which has one clustered index(on creationdate, that...

Data Warehousing : Integration Services

including a count in the file name - I'm currently creating a flat file export for one of our clients, i've managed to get the file in the...

check for running packages - Hi guys, this a two-pronged question. I have an SSIS package that needs to run 24/7 as it uses Kosans File...

SSIS Calls SSRS Report with One Parameter and Then Emails - Sorry if this is the wrong section. I have a report that I have created that contains one parameter called AgentEmail. This...

SSIS Package to remove leading 0's - Hey Everyone, :-) I am creating an SSIS package and having some issues removing leading 0's from a field. I cannot...

What happens if new records are inserted in a source table during a package execution? - Hi Folks, I have a package that loads records in a CRM destination using a Web Service. This execution takes around...

Data Warehousing : Strategies and Ideas

creating an investment data warehouse - Looking for some help designing a small prototype for investments. Ideally, what I'm trying to show is a position of...

Strategies for dealing with Excel - Like a lot of organisations, we struggle with the fact that a lot of users manage important business data in...

Data Warehousing : Analysis Services

Creating Time Series Mining Structure with DMX - Hi. I´m trying to create a mining structure with the next dmx code: [code="sql"]CREATE MINING MODEL [Cassandra] ( [CompanyNK] TEXT KEY, [Date] Date...