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 Source Control Get your SQL Server database under version control now!
Version control is standard for applications, but databases haven’t caught up. So how can you bring database development up to speed? Find out…
 
SQL Monitor Get alerts within 15 seconds of SQL Server issues
SQL Monitor checks performance data every 15 seconds, so you can fix issues before your users even notice them. Start monitoring with a free trial.

In This Issue

SSRS Report Content Filtering by User Group

How to get an SSRS user's AD group membership in order to filter report content More »


SQL Saturday #202 - Edinburgh

Join us for a free day of SQL Server Training on June 8, 2013. There's also a Friday Preconference Training Day with Jonathan Kehayias and Joseph Sack. More »


Some "Laws" of Software Development

Despite all the advances in software tools, there seem to be several enduring truths about software development. By understaning these 'laws', Ziv’s law, Humphrey’s Law and Conway’s Law for example, you can remove some of the mystery of the process. Al Noel discusses these and other laws that seem to apply generally to the art of programming. More »


From the SQLServerCentral Blogs - Have you configured Database Mail to utilize multiple SMTP Accounts ?

Lets start discussing about this subject by closely looking at the term Redundancy.  What is redundancy all about ? Wikipedia defines redundancy as “In engineering, redundancy is the... More »


Editorial - Relying on Algorithms

We are increasingly living in a world that is affected by software (and the data it uses). I have to admit that I'm slightly worried about this as I look at the quality of software, the bugs, the lack of effective testing. It's not that companies don't perform a lot of testing; many do, but it seems that many developers, and even QA people, don't really know how to effectively test. This is especially true of testing for abnormal or unexpected situations. It there's one area of software development that seems woefully immature, it's testing.

However testing isn't the only problem. We might not like the way that software is designed, and used, to alter our world. As we become more dependent on software to accomplish tasks for us, we will run into new situations that the software may not have been designed to handle. This opinion piece on the ways in which software might alter our lives is both fascinating and scary. How do we want software to behave in unfamiliar situations? It's not an easy question to answer, and it's certainly not a question with a simple answer in many scenarios.

We learn to depend on systems when they work for us. Whether in business, government, or our personal lives, when a system works, we want to use it more. However that's not always the best long term solution. Humans adapt, and software, especially the systems using a constant stream of historical data for decisions, has flaws. More than a few of you might have encountered this type of situation with Netflix or Amazon recommendations when you have kids sharing your account. 

In some sense I think that we not only need adaptive algorithms, that we can customize over time, but we also need to understand the data that flows through the algorithms. We need ways to remove some data from consideration by the software, when we find it is removing more value than it is adding.

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

create table #temptable1
(name1 char(10),
 name2 char(10),
 zip int
)
GO

insert into #temptable1 
values
(NULL,NULL,11),
('1','2a',NULL),
(NULL,'a2',111),
(NULL,NULL,NULL)
GO
select name1,name2,zip, COALESCE(name1,name2,zip) AS name3 
 from #temptable1
While executing the select statement, an error for a data type conversion is returned. Which row (numbered in insert order) gives the error?

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

This question is worth 2 points in this category: Coalesce. We keep track of your score to give you bragging rights against your peers.

We'd love to give you credit for your own question and answer. To submit a QOD, simply log in to Contribution Center.

Securing SQL Server

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

Get your copy from Amazon today.


Yesterday's Question of the Day

Using SQL Server 2012 or 2008R2, I create the following table

CREATE TABLE dbo.Orders
(orderid INT NOT NULL,orderdate DATETIME NOT NULL,
empid INT NOT NULL,custid VARCHAR(5) NOT NULL,
qty INT NOT NULL,salesprice MONEY NOT NULL 
CONSTRAINT PK_Orders PRIMARY KEY(orderid));
GO
ALTER TABLE dbo.Orders ADD
TotalSales AS (Salesprice * qty) PERSISTED
GO

I then insert the following data into the table dbo.Orders

INSERT INTO dbo.Orders (orderid, orderdate, empid, custid, qty,salesprice)
VALUES
(301, '20120802', 3, 'A', 10,5.00), (11, '20121224', 1, 'A', 12,9.09),
(105, '20121224', 1, 'B', 20,7.25), (4, '20130109', 4, 'A', 40,7.65),
(160, '20110118', 1, 'C', 14,3.01), (21, '20130212', 2, 'B', 12,6.57),
(40, '20130212', 4, 'A', 10, 11.21), (22, '20130216', 2, 'C', 20,0.09),
(30, '20130418', 3, 'B', 15,33.33), (3, '20120418', 3, 'C', 22,2.20),
(317, '20110907', 3, 'D', 30, .47);

I then execute the following T-SQL

SELECT grouping_id(custid,empid) AS grp_id
,custid,empid,YEAR(orderdate) AS orderyear,salesprice, 
SUM(qty) AS qty,TotalSales
FROM dbo.Orders
GROUP BY GROUPING SETS (
( custid,empid),( custid, YEAR(orderdate)),
(YEAR(orderdate)),
( empid, YEAR(orderdate)),( empid,TotalSales),
( custid,salesprice),()) ORDER BY grp_id;

Select the four(4) correct answers

Answer:

  • The highest value sold to one custid is $499.95
  • The highest quantity sold to any custid is 50
  • Empid with highest value sold is 3
  • The year 2013 has the highest quantity sold

Explanation: Groups a selected set of rows into a set of summary rows by the values of one or more columns or expressions in SQL Server 2012. One row is returned for each group. Aggregate functions in the SELECT clause

Microsoft® SQL Server® 2012 Step by Step

Teach yourself the programming fundamentals of SQL Server 2012—one step at a time. Ideal for beginning SQL Server database administrators and developers, this tutorial provides clear guidance and practical, learn-by-doing exercises for building database solutions that solve real-world business problems.

Get your copy from Amazon today.


Featured Script

Truncate or switch range of a partitioned table

Script to duplicate structure, switch or truncate choosen partitions of a table 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

Split a column - Hi: I would like someone can help. I heve a column with many rows like: NAME Jonh*Smith*Alu Alex*Campos*kery Chipper*Jon*Ali Larry*Walker*kin AND...

Determine the size of the changed data in a database??? - We are upgrading to new servers/datacenter. One of the metrics the network team wants from us is to determine how...

SQL Server 2005 : SQL Server 2005 General Discussion

How do I handle default values? - Greetings experts, I have 2 fieldnames on 2 different tables. One fieldname is called SeatCapacity. This fieldname name has a value of...

SQL Server 2005 : SQL Server 2005 Integration Services

Unable to pass SQL execute Task Result set values to Data Flow component. - Hi All, I’m facing following issue in SSIS package, I need to get records in one table. Then each value should...

how to write expression for Derived Column transform - I need to filter out all records as follows: where [Month 01] is null and [Month 02] is null and ...

Execute java in Task - Hi, I have a jar file and i need execute it sentence: java -jar openpgp.jar -d "c:\example.asc" "c:\file pgp\readme.txt.asc" In Integration...

SQL Server 2008 : SQL Server 2008 - General

Connectivity issue - Hi, I am trying to connect to a named instance on another server myhost\myinstance, but get an error message: A network-related or...

How to reduce the size of the t-log file of a mirrored DB that is still in restoring mode - Hello gents This might look like an oddball question, but one mirrored DB on our DR box has a huge t-log...

How to rollback an insert query on sql server ? , please suggest bit high priority .. thanks in advance - How to rollback an insert query on sql server ? , please suggest bit high priority .. thanks in advance

Location and name of SQL Server 2008 R2 for Connection String - I have SQL Server 2008 R2. I need the location and name of SQL Server 2008 R2 to put into my...

help needed urgently with datediff query - Hi - I have been trying to return the date diff value and keep getting an error saying: character constant must contain...

Xml Query Performance Sql Server - Hello, I have the below data structure The query is taking a while to run. I have a primary xml index,...

which one is best query? - first query select COUNT(id) from tbl_birthmaster1 where status_info=1 and zone_id=1 select COUNT(id) from tbl_birthmaster1 where isnull(pdffilepath,'')!='' and status_info=1 and zone_id=1 select...

Agent Job Links to Another SQL Server and Fails due to Service Account Privs - To my friends in the community, here is a stumper that I can't seem to find sufficient information on despite...

SQL Server can't connect to database - I downloaded file SQLEXPRADV_x86_ENU.exe (version 10.00.1600.22), and used it to install SQL Server 2008 Express with Advanced Services, which I...

SQL Server 2008 : Working with Oracle

mysql connector ODBC 5.2 not showing - I tried to transfer data from ms sql server 2012 to mysql using sql server export, but could not find...

SQL Server 2008 : SQL Server Newbies

get lookup data - I have two tables, lets say Table 1 and Table 2 as below Table 1: Col1 Col2 1 _A 2 _B 3 1 4 2 Table...

IDENTITY_INSERT, and Using Alphanumeric Autoincrement Primary Key - Hi Folks, Please help me on below few queries My table is Users(ID IDENTITY(1,1) PRIMARY KEY, NAME nvarchar(10), MANGER_ID fk_User_id References...

SQL Server 2008 : SQL Server 2008 High Availability

DB Reindex - Yesterday i did rebuild my index but while checking I came to know that there are still some fragmentation..what is...

Mirrioring on SMTP mail server - Hi I have 2 servers A & B (SQL Server 2008R2). I have to setup mirrioring and data of Server A will...

What do u mean by DB Refresh - What do u mean by DB Refresh..What r the steps to perform this task...

SQL Server 2008 : SQL Server 2008 Administration

Copy SQL .bak file to other server using FTP and restore the backup to a database - Hi, I am looking for solution for below approach using TSQL or powershell or using SQL agent job. 1. Perform backup on...

Programming : Connecting

mysql connector ODBC 5.2 - I tried to transfer data from ms sql server 2012 to mysql using sql server export, but could not find...

Database Design : Design Ideas and Questions

What normal form would this be considered? 2NF or 3NF? - Lets say I have a table with four columns named PersonID1, PersonID2, JobTitle, and PreviousJobTitle. There is a composite primary...

Data Warehousing : Integration Services

help writing SUBSTRING() expression please - I have strings like this: MFMGOOGLE, inside my [Business Unit] column, from which I need to strip the "MFM". Yet...