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 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.
 
Deployment Manager NEW! The easiest way to deploy .NET code
Deploy ASP.NET applications fast, frequently, and without fuss, using Deployment Manager, the new tool from Red Gate. Try it now.

In This Issue

Designing a Dashboard - Level 6 in the Stairway to Reporting Services

This article walks through a sample dashboard, highlighting sparklines, databars, and indicators. More »


Testing the StreamInsight Service for Windows Azure

Getting 'up to speed' with StreamInsight is easier if you take the time to run it and test it out. Roger Jennings expains how to be up and running in nine simple steps, once you've sorted out the prerequisites More »


From the SQLServerCentral Blogs - T-SQL Tuesday #40– File and Filegroups

It’s the second Tuesday of the month and time for T-SQL Tuesday again. This is a monthly blog party, where... More »


From the SQLServerCentral Blogs - T-SQL Tuesday #40 - Proportional Fill within a Filegroup

T-SQL Tuesday #40 is underway, and this month's host is Jennifer McCown (blog|twitter).  The topic is about File and Filegroup... More »


Editorial - Inconsistency

Jeff Bezos has done an amazing job building Amazon into an amazing technology company. I still remember making my first purchase when they were just a bookseller. Now I buy a lot from them, and the various other vendors that use their platforms. While they are well known as a retail powerhouse, they might just as easily be seen as an incredible software company that provides a wide variety of platforms and services from the cloud.  

I've also followed the rise fo 37 Signals, and Jason Fried, who I think has built a company the right way. In many ways the things I admire about that company are also things I admire about Red Gate Software. These are companies that not only run successful and profitable businesses, but they do so in a way that inspires people and gives them more than just a paycheck.

Recently Jeff Bezos stopped by 37 Signals and gave some advice. He said that the people he had found in his life that were "right a lot" about various subjects were those that often changed their minds. That's a strong, but thought-provoking statement to me. As I think about it, it's how I tend to view the world. I have some strong, well thought-out (I think) opinions, but I'm open to the fact that I might be wrong. Not because I don't understand the problem or situation, but because I might not have all the information. I am very open to the idea that I don't know what I don't know about many subjects, especially databases.

This week, I want to know what you've changed your mind about in your career. It could be something in databases, or technology in general, but I'm wondering:

What strong opinion did you hold that has been changed over time?

Please avoid political or socials issues and stick to something in technology that's you have altered your standing on. And if you haven't changed your mind on anything, perhaps that's something to think about as well.

» Join the debate, and respond to today's editorial on the forums

Advertisement: Setup monitoring on of your SQL Server instances in minutes, with pre-configured alerts. SQL Monitor, from Red Gate, includes many very common monitoring metrics that you might not think to enable yourself. We also include support for any custom metrics you might wish to write, and a site at SQL Monitor Metrics that has code from many well known and respected SQL Server experts.

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. You can also follow Steve Jones on Twitter:

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. They have a great version of Message in a Bottle if you want to check it out.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

» To submit an article, rant or editorial, log in to the Contribution Center


Question of the Day

Today's Question:

What will the following expression return?

select isnull ( convert (char(3),1/9) ,'*')

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

This question is worth 1 point in this category: Convert. 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

DECLARE @Datetime datetime = '2013-02-24 23:59:30'
SELECT Day(Convert(smalldatetime,@Datetime))
SELECT Day(Convert(Date,@Datetime))

Do the select statements return the same day? What will be the outcome?

Answer: First statement 25th and second is 24th

Explanation: Smalldatetime rounds up on the 30th second and down for 0-29, so 30 and up become the next day therefore the first statement shows 25th. Conversion to date simply truncates the time so returns the 24th.

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

Convert XML to String with formatting

Converts xml value into multi line string with indents 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 is not found in Object Explorer - Dear cronies I have to need scheduler used to run the stored procedure but i can't schedule because of sql server...

SQL Server 2005 : Backups

Server is rebooting after taking backup and it is not giving the Bak file also - HI to all, Server is getting restarted whenever we are running the SQL Backup [scheduled backup maintenance plan job and...

SQL Server 2005 : Business Intelligence

SSRS: How to make two tables have the same number of row - hi experts I have the following dataset1(MDX) (always has more rows than dataset2) dataset2(MDX) tablix1(source is dataset1) tablix2(source is dataset2) what i need to accomplish is...

SQL Server 2005 : Development

Pivot Query- need help - HI All, I am trying to convert rows to columns using Pivot function. Here is my case SELECT Month(a.createddate) as month, count(statusid) as...

Pivot - Guys, I have a table as InvoiceNumber, BillName 1, Amit 2, Amit 3, BBB 4, Amit The BillName can be same or different. I need the...

SQL Server 2005 : SQL Server 2005 General Discussion

email multiple query results to multiple staff - Hello all, The scene: There are 3 people who are all responsible for a number of specific jobs to them. I want to...

SQL Server 2005 : SQL Server 2005 Performance Tuning

High Index Count/Space versus High CPU and Logical/Physical Reads - We are working on trying to reduce the high number of IO waits on one of our production servers, a...

SQL Server 2005 : SQL Server 2005 Integration Services

Pull "unformatted" data into existing table structure via SSIS? - We created a bunch of tables some with 40+ columns each and this was a tedious process. We also have...

SSIS & Environment Variables - I've been struggling w/ some quirky issues lately regarding use of environment variables with package configurations in my SSIS packages. When...

SQL Server 2005 : T-SQL (SS2K5)

CASE in WHERE clause - I am converting embedded SQL (in Java code) to a stored procedure. They are building SQL WHERE clause dynamically in JAVA. What...

SQL QUERY - sql query to find a particular word in a field of table eg: in a field, gunapandian is a name, if...

SQL Server 7,2000 : Administration

query data using linked server - HI, Linked servers in test environment working differently than prod. Not sure what is causing slowness in test environment? is this...

SQL Server 7,2000 : T-SQL

Merging two select statements for MTD and YTD - Hi Guys, i have two select statements that i want to merge in one with union. the first query should return YTDSALES...

SQL Server 2008 : SQL Server 2008 - General

How can I get these values with a match field that has different file extensions. - Hello and thank you for the help. I am trying to write a select statement where I can view 3 columns...

"Truncation may occur due to inserting data from data flow column...." in SSIS 2008 - HI All, Am getting that error in SSIS 2008 even i changed the column length in flat file source in advanced...

How to add below complex conditions in SQL script.. - [size="2"][font="Courier New"]Hi All, Below is my written query DECLARE @FromDate DATETIME DECLARE @EndDate DATETIME SET @FromDate = '2013-01-01 00:00:00.000' SET @EndDate = '2013-02-13 00:00:00.000' SELECT DISTINCT year(sd.FKDAT) As YEARWISE_DATA, so.vkbur...

Reg Backup Stratergy - Is it best practice to take backups of system databases and user created databases in separate through maintenance plans and...

Errors for sql server connection in asp server - In an asp server we are getting error for SQL server connection like below [highlight="#FAFAD2"] System.InvalidOperationException: Timeout expired. The timeout period elapsed...

need assistance with query - Hello all! How could I return the top ten highest values in a column? WOuld it be somewhere along the lines...

update still difficult?? - I have a table which has three columns [code] create table employees ( ID int NOT NULL PRIMARY KEY NAME varchar(10) NULL EXTENDEDID int...

Why is "instead of delete" trigger not fired by delete inside "after update" trigger - One of my tables has both an "after update" and an "instead of delete" trigger. When I perform a delete...

ProcessAddress times out every time - result set size, time of day seem irrelevant - I am using the ProcessAddress geocoding function found [url=http://yaddressudf.codeplex.com/]here[/url] on CodePlex, but no matter what, it always times out on...

new to calling procedures or scripts - WHat is the best way to go around this. I know I can just add the code all in one...

How to Get The Most Filled Records from Similar Records - How to Get The Most Filled Records from Similar Records I have table called "Locations" and it contains 4 Columns "Neighborhood,City,Governorate,Country" [b]Neighborhood,...

Fuzzy Matching Barcodes - I have a list of 1 million+ products with numeric barcode values stored as VARCHAR(50) that can range in length...

Diagnosing SQL response time issues - We moved our server racks in our colo facility over the weekend, upgraded to a new firewall, and installed new...

Temp table join example - Hi, I'm looking for a basic example of a temporary table joined to and actual table I'm guessing it should be like: Select...

Not able to modify existing maintenance plan? - Hi, Tlog backup failed and no errors are written error log and application logs as well.. when try to editing existing maintenance...

Slow query - optimisation help required! - I have the following query: [code="sql"]INSERT INTO dbo.Load9_PotentialOverlaps_(Master_Id, Master_GUID, Master_SubmissionID, Duplicate_Id, duplicate_GUID, Duplicate_SubmissionID, MatchKeyType) SELECT a.id, a.GUID, a.SubmissionID, b.id, b.GUID, b.SubmissionID,...

Linked Server Replication - Good Morning I have been struggling with data synchronization between SQL SERVER and MYSQL for ages, I have tried all possible...

Recommended index causing query to run twice as long / HASH vs NESTED LOOP - Hey all, Been working on some performance tuning and running into a wall at this point. I've taken some initial passes...

SQL AGENT IS NOT NOT ABLE TO START.... - Hi, Can anyone help me ? SQL AGENT IS NOT NOT ABLE TO START....Getting an error " The process terminated unexpectedly [0x8007042b] in...

Clearing/deleting/resetting asynchronous file target - Extended events - Does anyone have any suggestions on how to clear/delete/reset the asynchronous file target files for extended events?

Encryption doubts - I want to encrypt my database. So which method is simple and the best. If i use symmetric key then...

Is this is the Best Practice to select E: for the SQL Server root directory? - Hi Experts, Is this is the Best Practice to select E: or any other driver(not C:) for the SQL Server root...

Diagnosing Page Latch Issue - I'm working with both a Vendor's DB and a purge script they've provided, and I need some outside opinions. This purge...

How to get Below T-SQL qury Output..? - [size="3"][font="Courier New"]Hi All, This is the query I have written DECLARE @FromDate DATETIME DECLARE @EndDate DATETIME SET @FromDate = '2013-01-01 00:00:00.000' SET @EndDate = '2013-02-13 00:00:00.000' SELECT year(sd.FKDAT)...

SQL Server for Content Censorship - Hi Champs, We have little strange requirement in a case of SharePoint where backend is SQL. Users in social features of...

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?

SQL Query causing CPU Spikes on SQL Server 2008 r2 - Hi, I have a curious case here: there is a SQL Query that is causing 90% of CPU on one of...

SQL Server 2008 : T-SQL (SS2K8)

Query Help - If I want to Sum a field where the date has passed, how would I do that? So my goal...

How to make triple-pass UPDATE single-pass? - I'm attempting to optimize some code that I was just handed, and I'm not exactly sure if what I want...

Insert Performance - Hi, Which will perform faster/better inserting million rows to a table or put all insert in a transaction then commit? Does it...

How do I optimize a query with a text column? - I am looking for a way to optimize a SELECT query that includes a column with a data type text....

Split a String - Hello Everyone I am working on some old data that should not be store this way, but it is. My data is...

select rows into colums - Dear T-sqlérs, I have a test table (see script below) with the following result name length balk1 7 balk1 6 balk1 9 stof1 6 stof2 6 stof3 6 stof4 6 stof5 6 stof5 7 stof6 6 stof7 6 stof8 6 stof9 6 stof9 7 stof10 6 stof11 6 stof12 6 Now I would like the result to be...

Complex SQL QUERY with DateDIFF - Hi all, I need to script a query to discard access logs that not exceeding a predetermined margin (eg. 2 seconds)....

Hierarchy example - Ken Henderson's book - not working - I’m working on the hierarchy lesson in Ken Henderson’s The Guru’s Guide to Transact-SQL. He wants to show two things....

SQL Server 2008 : SQL Server Newbies

Creating my first UD - I really like sql server, but one of the things that drives me crazy is formatting columns. The numbers in...

supplying a schema in queries, performance? - Hi A random question, if you write queries and put the schema before objects will it affect performance in any way...

looping thru views - I have a database with a large number of views. I would like to to be able to loop through...

SQL Server 2008 : SQL Server 2008 High Availability

DB for Reporting - We have a policy here that no one gets access to the production databases ever. There is only 1 dedicated...

replicating data from mssql to mysql - Hi, Any body tried to replicate data real time from mssql to mysql database. I know mysql is not supported as...

Failed Drive on Mirrored Server - Hi, We recently lost a drive the contained the transaction logs for a mirrored server. The principal databases responded to this...

sql server mirroring synchronization not happening - Hi all, I have mirroring setup, Seems to everything fine.But synchronization not happening.i don't understand what was the problem. Please any one...

Database mirroring : Transaction log space pressure - Hi, With database mirroring, if the mirror database fails, the transaction log space on the principal database cannot be reused even...

SQL Server 2008 : SQL Server 2008 Administration

SQL Ring Buffers - Hi All When analyzing SQL's ring buffers using the below [code="sql"]SET QUOTED_IDENTIFIER ON SELECT EventTime, n.value('(Pool)[1]', 'int') AS [Pool], n.value('(Broker)[1]', 'varchar(40)') AS [Broker], ...

Grant persmissions to database - We have 5 small in-house developed applications whose databases are hosted on a SQL server. For each application we have an...

Cannot connect after change Service Account - I have a 2008 R2 SQL Server that is not clustered. I changed the SQL Service Account username and password....

How to find cause of Replication delay? - Hi everyone, I just have a quick question about finding the cause of a delay I saw with one of my...

SSIS Job fails using Domain Account - I get this error when I attempt to run the Package using the Domain Account, SQLServerAgent. [quote] Message Executed as user: MyDomain\SQLServerAgent. Microsoft...

SSMS showing local DBs not remote DBs when editing remote maint package - Hi All When I open an Integrity check task from a server (SQL 2008 R2) with my SSMS(SQL2012) on my PC...

dadication and shared connection in sql - is there a concept of dadicated connection and shared connection in sql similar to oracle.if yes, how it has to...

DR/HA proposal - I need to write DR/HA proposal any suggestions?

Big table - We have a lookup table GeographicLookup table with a primary key called gisGeorgraphicLookup. It now has 2 million records, with each...

Daylight saving time and SQL agent job - I have a SQL agent job scheduled at 2:00 am on Sunday. But it was triggered at 2:59:50 am instead of...

Negative Available Space for tempdb - I'm checking all my databases and when I look at my templog setting in the Shrink File window I see...

Question about DBA authority versus responsibility - Hi, I'm looking for some advice for how I can assert myself in this situation without alienating my colleagues or causing...

The distribution agent failed to create temporary files - Hi I'm getting this error with a transactional replication setup on SQL 2008R2 Standard (SP1): The distribution agent failed to create...

Career : Certification

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

Programming : General

Save SP to table - Hi there! Hmmm ... actually I read a lot of articles about how to accomplish that, and indeed I could make this...

"instead of delete" trigger is not fired from "after" trigger - Hi everyone, I have a problem that "instead of delete" trigger is not fired from "after insert" trigger. Reading BOL it...

SQLServerCentral.com : Anything that is NOT about SQL!

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 reports logic - HI friends i have small doubt in ssrs report.plese tell me how to solve this one If i generate the report...

Counting for Labels - I am running all of my production labels using SSRS 2008R2 I am trying to constuct a label that will Print...

Commas in mailto - I'm trying to add a hyperlink to a textbox to e-mail addresses. The following will work: (1) mailto:aa@bb.com However, if I...

Reporting Services : Reporting Services 2005 Development

Startswith Eqivalent in Reporting Services? Need help please! - I have a Crystal Report that I am converting to Reporting Services. The report lists accounts by SIC Code, which...

Subreport with uniqueidentifier or guid parameter - Hi, I am adding a subreport to a report and I need to pass a uniqueidentifier or Guid to the subreport....

Data Warehousing : Integration Services

T-SQL Merge over databases in different servers - Hi Folks, I would like to know if it´s possible to use the T-SQL MERGE if the source DB is a...

Issue in using Execute Process Task in a ForEach Loop container for unzipping multiple files. - Hi, I am using SSIS 2005. I have multiple source files which are zipped. I have to unzip those source files...

SSIS task taking time but SP called is quick - Hi, We have a huge process that we perform using SSIS, which is mostly just calling SP's using SQL task. When...

Conditional Lookup - Currently working on a project for a client which involves me building a matching solution in SSIS. One of the...

Audit transformation in SSIS - Hi everyone. This is my first time posting on this forum so let me know if you need more information...

SQL license question related to the SSIS loop transform - Is a "lookup transform" considered part of the "basic transformations" included in sql server standard license? SQL Server enterprise says...

Documentation in SSIS - I am just starting learn and use SSIS, and was wondering how you document changes? In a stored proc I...

Package encryption and Source Code Control - Hi guys, Background: We are using Mercurial Hg Source code control for our SSIS solutions. Currently we have a package that connects...

Data Warehousing : Analysis Services

Check measures across Analysis Services DBs - Currently I have 12 Analysis Services DBs that schedule to update every month. (They are all the same hierarchy/Structure). After the...

trying to make my first report and it is not going :( - Hi, I am new here, I have a question, unfortunately every time I solve it on my own I keep mixing...

Restricting admin permissions - Hi I have a strange request from our BI department and I'm pretty sure it's not possible, but just want to...

MDX simple concept question - As near as I can tell from documentation, the Descendants() function returns a set. This returns a set of 1,500+ product...

MDX First month of year - This shouldn't be as hard as it seems; I want to always get the first month of the year (January)...