In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
Red Gate Deployment Manager NEW! Automate your .NET deployments
Deploy ASP.NET applications and SQL Server changes fast, frequently, and without fuss, using Deployment Manager, the new tool from Red Gate. Try it now.
 
SQL Backup Pro Quick, easy off site SQL Server backups
Take your backups into secure hosted storage quickly and easily using SQL Backup Pro’s GUI and step-by-step guides. Find out more.
 
SQL Source Control Is your SQL Database under Version Control?
SSMS plug-in SQL Source Control connects SVN, TFS, Git, Hg and all others to SQL Server. Learn more.

In This Issue

Parent Package Variable Configuration and Logging

In SSIS, when we use 'parent package variable configuration' the order of event execution is quite different than the normal execution order. In this article we will see what the impact on execution order when we use 'parent package variable configuration'. More »


QlikView for Beginners - An Introduction to QlikView

A brief introduction to getting started with QlikView. More »


Coping with Infrastructure Upgrades

A common topic for questions on SQL Server forums is how to plan and implement upgrades to SQL Server. Moving from old to new hardware or moving from one version of SQL Server to another. There are other circumstances where upgrades of other systems affect SQL Server DBAs. More »


From the SQLServerCentral Blogs - Let’s Talk About Joins

T-SQL Tuesday #37 This month please join us in the TSQL blog party that happens on the second tuesday of the... More »


Editorial - Better Licensing for SQL Server

People post licensing questions constantly at SQLServerCentral about the various scenarios and situations they face with their SQL Server instance configuration.  Many of the are fairly easy to answer, but more and more I read about environments that are complex. The problem is it is not clear what purchases are needed from the licensing documents.

Multiple cores, virtual environments, and the restrictions for different editions of SQL Server mean there are often no simple answers. I know Microsoft is trying to earn a profit and these days hardware advances complicate matters, but I do think that Microsoft can make this much easier to understand than it currently is.

There are lots of possible scenarios for systems, but publishing data on the Internet is easy. I don't understand why the licensing documents do not include 10 or 20 possible configurations for SQL Server environments and explain what the licensing requirements are for each one. I bet that there are even some developers at Microsoft that can build a way for anyone to submit their own scenario, get it added to the list, and an answer published. I would guess there might even be able to build a notification feature for the submitter.

It's easy to contact a Microsoft licensing representative, but many of these people aren't SQL Server experts, and often don't understand some of the implications of complex environments. What's worse is I have different friends getting different answers from Microsoft representatives.  By not using published scenarios and licensing requirements, and depending on employees' interpretation of the documents, Microsoft is as likely to under charge as over charge customers. Neither situation is a fair one.

I don't mind paying for the software I use; I just want to know what the cost is before I architect a complex environment.

Steve Jones


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:

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

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


Question of the Day

Today's Question:

I execute:

SET LANGUAGE us_english

I create a table  as

CREATE TABLE#T
(Id INT,Xday VARCHAR(20))

Which contains the following data

Id   Xday
1    25-DEC-2013 00:00:00
2    25DEC2013 00:00:00
3    2013DEC32 00:00:00

I then execute the following T-SQL Statements

-- SELECT #1
SELECT Id,TRY_PARSE(Xday AS DATETIME) FROM #T ORDER BY Id

-- SELECT #2
SELECT Id,PARSE(Xday AS DATETIME ) FROM #T ORDER BY Id

SELECT  the five (5) correct answers

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

This question is worth 2 points in this category: parse. 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.

Professional SQL Server 2012 Adminstration

Microsoft SQL Server 2012 will have major changes throughout the SQL Server and will impact how DBAs administer the database. With this book, a team of well-known SQL Server experts introduces the many new features of the most recent version of SQL Server and deciphers how these changes will affect the methods that administrators have been using for years. Loaded with unique tips, tricks, and workarounds for handling the most difficult SQL Server admin issues, this how-to guide deciphers topics such as performance tuning, backup and recovery, scaling and replication, clustering, and security.

Get your copy from Amazon today.


Yesterday's Question of the Day

I have the following table:

CREATE TABLE [dbo].[Customer](
     [CustomerID] [int] IDENTITY(1,1) NOT NULL,
     [FirstName] [varchar](25) NOT NULL,
     [LastName] [varchar](25) NOT NULL,
     [PhoneNumber] [varchar](15) NOT NULL
) ON [PRIMARY]

It contains the following data.

CustomerID FirstName  LastName   PhoneNumber

1          jeff       Jones      555-440-2323

2          Steve      Moden      555-440-2323

I have the following settings

SET ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,
QUOTED_IDENTIFIER,ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF

I create the following VIEW

CREATE VIEW V1 WITH SCHEMABINDING
AS
SELECT CustomerID,FirstName,LastName,PhoneNumber
FROM dbo.Customer
GO

CREATE UNIQUE CLUSTERED INDEX VnameInd ON V1 (LastName) 

I then execute the following:

SET NUMERIC_ROUNDABORT ON

UPDATE V1
 SET PhoneNumber = '999-888-4343'
 WHERE CustomerId = 2

Is the phone number for Steve Moden Updated?

Answer: No - an error message is returned

Explanation: If SET NUMERIC_ROUNDABORT is ON, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.

Ref: http://msdn.microsoft.com/en-us/library/ms188791.aspx

» Discuss this question and answer on the forums

Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012

Ace your preparation for Microsoft® Certification Exam 70-461 with this 2-in-1 Training Kit from Microsoft Press®. Work at your own pace through a series of lessons and practical exercises, and then assess your skills with practice tests on CD—featuring multiple, customizable testing options.

Maximize your performance on the exam by learning how to:

  • Create database objects
  • Work with data
  • Modify data
  • Troubleshoot and optimize queries

You also get an exam discount voucher—making this book an exceptional value and a great career investment.


Featured Script

Update Trigger to Store Only Updated Column and Former Value

History table needs individual rows for each column that has been updated, i.e., a column history. 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

Maintenance plan DB bAckup plan failing - I have scheduled the DB backup in our server but frequently its failing with below error Standard Edition (64-bit) RTM 9.00.1399.06 Agent is running...

SQL Server 2005 : Business Intelligence

Database Diagrams tool in SQLSERVER - Is there any Database Diagrams tool in SQLSERVER ? Hello : We are planning to design new Datawarehousing product. I am taking...

SQL Server 2005 : SS2K5 Replication

oracle golden gate slow reponse / lag observed during index rebuild job in sql 2008 r2 - Hi I want help if any one having oracle golden replication exeprience in SQL 2008 R2 edition. I have a source...

Merge publication keeps bulk copying - The merge publication keeps bulk copying tables into the subscriber database. It will copy the data over to the subscriber...

SQL Server 2005 : T-SQL (SS2K5)

Getting Error "All queries combined using a UNION..." Why?? - Hello - I have SQL View (which I would like to go on record I did not write it) that has...

Stop joined aggregates from multiplying - I have three tables. A customer table, an invoice table, and a payment table. I want to pull, in a...

exec master.dbo.xp_cmdshell - [b][/b]:ermm: Its been working all this while but for some reason it has stopped working for only 1 of our...

SQL Server 2005 : SQL Server Newbies

Split input string into multicolumn - multirows - Hello all - I am having hard time to split an input string into multicolumn - multirows. Task - Create a stored procedure that...

SQL Server 2008 : SQL Server 2008 - General

Missing Indexes on Temp tables - The DMV dm_db_missing_index_details has suggestions on missing indexes on permanent tables but I need some suggestions on ways to identify...

Partitioning - Hi I have problem with partitioning.I am testing partitioning ,but i am a bit confused about it. I don't know the concept...

How to Shcedule set of SP,executing sequencly - Hi, I want Scedule the Set of Stored Procedures And if any one SP gets errors then next one should gets...

70-433 exam - Hi All, how to prepare 70-433 exam can any one provide material plz plz...

Grouping by time - can't figure this out! - I have a bunch of data with timestamps - there may be any number of rows in a given time period,...

Profiler shows LoginName as 'sa', but 'sa' is disabled on the instance. - As the subject says, I have a profiler running and see some things executing as 'sa' in the LoginName field,...

Getting "severe error" when referencing a table that doesn't exist - This is the error (all databases): Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command....

T-SQL to get all available instances on the server - hi, can any body help me out on T-SQL to get all available instance names on the server.

bulk Insert Error - Hi I am trying to execute .tbl file to bulk insert in the server ServerA. The .tbl file was saved...

Access table from different instance - hello experts, i want to write a select query which will access table from different instance, but cant create linked server...

Question about RPC for linked servers. - OK, so I am trying to figure out what the purpose of the RPC option for sp_serveroption is for. I understand...

Value of column dependent on the values of others. How can I do it . Please help ! - Hello , this is the first time i'm posting here, but this time i really need your help. I need to...

Issues Getting Max(Status) by ID and Max(StatusDate) - I'm having one of those moments where I'm sure I'm just over thinking the problem, but none the less I...

Distributing the greatest value by rank in SQL - This seems as though it would be terribly simple...but I am stuck. I need to distribute as "average sales" value among...

Rebalancing data from a single file to multi-file setup - Hi, I'm inheriting a series of databases that have grown beyond 100GB yet only have a single datafile. I've been...

MDW best practices - Hi All, Please share the best practices to be followed to cinfigure MDW. I am goint to implement MDW to capture...

SQL Server 2008 : T-SQL (SS2K8)

Get continuous date with count - hi all, i am having a requirement like this input EmpID reportdate reportname noofdays 47 11/29/2012 Thursday 1 47 11/30/2012 Friday 1 47 12/4/2012 Tuesday 1 47 12/5/2012 Wednesday 1 47 12/7/2012 Friday 1 47 12/10/2012 Monday 1 48 11/29/2012 Thursday 1 48 11/30/2012 Friday 1 48 12/4/2012 Tuesday 1 48 12/5/2012 Wednesday 1 48 12/7/2012 Friday 1 48 12/10/2012 Monday 1 48 14/10/20

single quote in sql statement - I would like to set a variable that is a sql statement, but the syntax about the quotes is so...

how to read xml variable using openxml - Hi, I have a xml like this. <DocumentElement> <tLockhistory> <lockrequesteddate>2012-12-14T00:00:00+05:30</lockrequesteddate> <lockexpirydate>2012-12-29T00:00:00+05:30</lockexpirydate> <createddate>2012-12-14T00:00:00+05:30</createddate> </tLockhistory> <tLockhistory> <lockrequesteddate>2012-12-14

concatenate of two values - Hi i have table like [code="sql"] CREATE TABLE [dbo].[Mas_Shift] ( [ShiftId] [int] IDENTITY(1,1) NOT NULL, [FacilityId] [int] NULL, [ShiftType] [nvarchar](5) NULL, [Shift] [nvarchar](3) NULL ) INSERT [dbo].[Mas_Shift] ([ShiftId], [FacilityId], [ShiftType],...

Help Required to handle multiple employees with overlapping datetimes (concurrent activities) - Morning, I have a data set that contains employee shift data, when they logged in and logged out of a certain...

Need help with a query - I have the following query which returns a column name, the database name the column is from, the week (out...

Finding combinations of values - My table structure CREATE TABLE trefClientShares{ intLMid int NULL, intMLid int NULL, intISid int NULL, intFRid int NULL } I need to figure out...

ORDER BY Should be same as my input in IN() - DECLARE @T TABLE(ID INT,NAME VARCHAR(10)) INSERT INTO @T VALUES(1,'PPP') INSERT INTO @T VALUES(2,'AAA') INSERT INTO @T VALUES(2,'ZZZ') INSERT INTO @T VALUES(3,'XYZ') INSERT INTO @T...

NPV and IRR in SQL - Years ago I wrote a piece on the Net Present Value (NPV) function in SQL. If you don't know or...

SQL Server 2008 : SQL Server Newbies

Script logins - Hi When you script a login using SSMS the script generated always has "* For security reasons the login is created disabled...

Need help on an if statement - Hello all. Im still learning in t-sql trying to get my certification thats why I am not to familiar wich functions...

SQL Server 2008 : SQL Server 2008 High Availability

Problem Setting up SQL Logshipping Monitor Serve - Hi I'm trying to set up log shipping between 3 SQL Server 2008R2 servers, where there is a Primary server and...

Quick question about using CRYSTALDISKMARK to check LUNs - Ok, I've used CRYSTALDISKMARK before, bot never on a drive or LUN with mount points. I am about to validate performance on...

Archieving Data - Hi All, need advice from the Experts .. I have been asked to do archieving of the data.. because The data in the databases...

transactional Replication- Two log files - For some reason we have two log files on one of our production database. We have transactional replication setup on...

SQL Server 2008 : SQL Server 2008 Administration

Strange issue when attaching databases - We have a sandbox SQL Server that we use mostly for playing around with reporting data; basically stuff like running...

restore db from multiple diff vs last diff - which option (below) will the restoring the 2nd (last) diff be faster? option 1: restore full (10TB), 1st diff (3TB), 2nd...

Deadlock due to intra-query parallelism - Hi We have been having deadlocks a lot and to track them I enabled Trace Flags 1204 and 1222. The output...

Programming : Connecting

unable to connect on all instances on remote machine - Hi, I have a virtual network with machine DC (AD DC), Machine A and Machine B all on the same...

Reporting Services : Reporting Services

Count is returning a count of all rows - I have a dataset column in a table region and I want to count on a condition with an expression...

Is averaging textboxt value possible - Test results - Textbox header column in Table region Pass - Textbox value Fail - Textbox value Fail - Textbox value Pass - Textbox value etc. etc. I have a textbox in...

Data Warehousing : Analysis Services

Loading change data from AS400 (or other source systems) - Greetings all! So I will keep this concise as I can; I have a source as400 system I am extracting data...