In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
Red Gate Deployment Manager NEW! Never waste another weekend deploying
Deploy SQL Server changes and ASP .NET applications fast, frequently, and without fuss, using Deployment Manager, the new tool from Red Gate. Try it now.
 
SQL Monitor "It's the freaking iPhone of SQL monitoring"
"Everyone just gets it… that has tremendous value" - Rob Sullivan, DBA, IdeasRun. Get started with SQL Monitor today - download a free trial.
 
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.

In This Issue

5 Reasons You Must Start Capturing Baseline Data

It is widely acknowledged within the SQL Server community that baselines represent valuable information that DBAs should capture. Unfortunately, very few companies manage to log and report on this information, and DBAs are then forced to troubleshoot from the hip and scramble to find evidence to prove that the database is not the problem. This article will make a compelling argument for why DBAs must start capturing baseline information, and will create a roadmap for subsequent posts. More »


SQL Saturday #179 - Kharkov, Ukraine

SQL Saturday in the Ukraine. If you want a free day of training, sign up and attend. More »


Create SQL Server Analysis Services Partitions using AMO

When you have SSAS cubes with millions of rows of data, it is very helpful to create partitions. If you have a few cubes you could probably do this manually, but if there are many or if you want to automate this process you should look for smarter solutions such as programming the creation of partitions dynamically.  More »


From the SQLServerCentral Blogs - Quick Tip – Speed a Slow Restore from the Transaction Log

Here’s a quick tip for you: During some restore operations on Microsoft SQL Server, the transaction log redo step might be... More »


Editorial - Human Power

This editorial was originally published on Mar 20, 2008. It is being re-run as Steve is on vacation.

I actually got this from my Mom, and thought it was amazing. A look at how you can move rocks. Now this aren't just any rocks, these are Stonehenge-sized rocks. And the guy moving the rocks isn't any bigger than I am.

The process is amazing, but to me the most amazing is that this is this is a relatively ordinary person, a retired worker, that engineered this process. He had a passion for moving things, and built a hobby that has provided a very plausible solution to a problem, or at least a question, that many people have thought about for a long time and not gotten better ideas.

The power of the human mind, as diverse and varied as we all think, is amazing. To me, this is a great argument for why we should constantly invest of all types of basic research, use the power of the Internet to distribute the workloads to those people that are interested, and allow them to correspond with each other.

I can even see corporations getting involved and helping people to move forward. I think the X-Prize that I've mentioned in my car updates, is another great example of just how far we can move forward when a variety of people tackle a challenge that they have a passion for.

So if you have a passion, pursue it. And share your knowledge with others. With the power of so many minds working on problems and sharing their accomplishments, who knows what we will accomplish?

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


The Voice of the DBA Podcasts

Everyday Jones

The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). 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.

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:

declare @x float, @y float

set @x = 3.65
set @y = 3.75

--What values are returned by the following statements?
select 'x = ' + str(@x,10,1) 
select 'y = ' + str(@y,10,1)

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

This question is worth 1 point 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.

Microsoft SQL Server 2012 Security Cookbook 

Each recipe comprises step-by-step instructions followed by an analysis of what was done in each task and other useful information. The book is designed so that you can read it chapter by chapter, or look at the list of recipes and refer to them in no particular order. Each example comes with its expected output to make your learning even easier thus enabling you to successfully secure your SQL Server 2012 database. This book is for SQL Server administrators, developers, and consultants who want to secure their SQL Server database with cutting edge techniques for data and code encryption, user authentication and authorization, protection against brute force attacks, denial-of-service attacks, and SQL Injection, securing business intelligence, and more. Working knowledge of SQL Server is expected.

Get your copy from Amazon today.


Yesterday's Question of the Day

What is returned from #tblTrans when you run the below code.

CREATE TABLE #tblTrans(RowId TINYINT)
GO
BEGIN TRAN
	DECLARE @getId TINYINT 
	SET @getId=100
	INSERT INTO #tblTrans (RowId) VALUES(@getId)
	IF @getId >10
	RAISERROR('RowId should not be greater than 10',11,16)
	PRINT @@ERROR
IF @@ERROR = 0
BEGIN
	COMMIT TRAN 
	PRINT 'I am here at commit!'
END
ELSE
BEGIN 
	ROLLBACK TRAN
	PRINT 'I am here at rollback!'
END
GO
SELECT * FROM #tblTrans

Answer: 100

Explanation: @@ERROR Returns the error number for the last Transact-SQL statement executed. In this example, though error is generated using RAISERROR the next immediate statement is PRINT @ERROR which resets the error number to "0". It is best practice to remove the PRINT statement when you move your code to production environment.

Ref: Using @@Error - http://msdn.microsoft.com/en-us/library/ms190193%28v=SQL.105%29.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

SQL Server Documenter

This script was designed to provide you with all the information needed to rebuild your server in case of a disaster. Current version work with 2005, 2008, 2008R2 and 2012. 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

Find out the size of database tables, row count, data size, index growth - I want to create table and all columns. column names are database name, current date, row count, data size, index...

SQL Server 2005 : Business Intelligence

how to reduce the Time Processing for a SSRS Report - I have a SSRS Report which retrives the data from SSAS cube. When i look at the TimeProcessing for the...

SQL Server 2005 : SQL Server 2005 General Discussion

SQL Server 2005 Data Reader - Good Morning. Is there documentation somewhere that explains the inner workings of the Data Reader? I mean absolutely step by painful...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Huge cost issue with Temp DB using 2008r2 sp2 - Hi Guys, I have an issue that is completely baffling I need a guru's guidance / help. so a little bit of...

SQL Server 2005 : SQL Server 2005 Integration Services

Adding table columns and updating them in Execute Sql Task - Hi, Relatively new to SSIS. I have a table that I've created in a data flow. Back out in the...

rolling back data in SSIS package - Hi, I have an SSIS 2008 package which will call a stored procedure and store the data into a variable...

how to use substring to get the values in bold - Hello All, Could you please tell me how to get the value after 'between', before 'and' i.e., BOKSAM KAND LAMOG also...

SQL Server 2005 : T-SQL (SS2K5)

Stored Proc - Nullable Parameters for optional filtering - handling LinkTable LEFT JOINs - Hey all, Pretty long title, but the problem itself is fairly simple to explain. Suppose I have the following tables: [code] CREATE TABLE...

WHERE Filter - Hi to all. I haven't posted for a while and it is now time to wear my sql cap again...

SQL Server 7,2000 : Administration

Remove Duplicated row on a iseries table - I have a table similar to below Customer Referencedate card_number created_by John 112233451 2008-02-01 48001796 Sen John 112233451 2008-02-01 48001796 Sen Eddy 123565654 2008-03-26 48001799 Mark Mary 124578128 2009-10-27 48001850 Mark From the above table I need to remove all duplicated rows leaving...

SQL Server 2008 : SQL Server 2008 - General

setup maximum and minimum memory - I have a dedicated SQl server that has 12 gb physical memory. I manually change the default max memory to 8.5...

SQL Server 2005 Cluster - Removing / Rebuilding / Adding Node - We have a two node SQL Server 2005 SP2 cluster, Running Windows Server 2003 SP1. My company implemented a new...

size of LOB data 41GB smaller after BACKUP/RESTORE - Hi everyone, Maybe someone more experienced than I can explain this to me. Made a backup of our largest production database,...

Problem when using a User Defined Function in a SET loop - I am using SQL Server 2008 R2. I created a User Defined Function like this: [quote] CREATE FUNCTION [dbo].[Custom_StringToTableWithID] ( @string VARCHAR(MAX), @delimiter...

Using Full-Text Search - Hi, I am quering a big table (about 150,000 rows) with a big varchar field (size 2000) which can't be indexed...

Time out in insert statement - Hi every body I have a large table (270 G) and i only have insert and select statement over this table....

Reading data from Excel using Sql - Hi, could any one shed some light to read data from Excel sheet using SQL code. Requirements 1)Excel sheet will be placed in...

Dependent Delete statements - i need to delete the records from a table with multiple conditions , like below first i am clearing non numeric...

SQL IO Performance - I'm running out of brain cells trying to figure out what I've overlooked. Hoping someone has a quick checklist of...

ATTACHED SUSPECT DATABASE in SQL2008 R2 - hello to all i would like to ask some assistance for attaching a suspect db a size of 400 gigz,...

SQL Server 2008 : T-SQL (SS2K8)

TSQL Split XML attributes - I have an xml string and need to manipulate it within sql. [code="xml"] <RuleData ReturnVariable=""> <MateProperties> <Property Key="value" DisplayName="name value" /> <Property...

Get Records according to dataset. - Hello Expert ! I have a CTE Query but I have nothing concepts about it. And I am new in programming....

xml data type vs several records - Hello I am trying to save student answers to questions in a quiz I am looking for best performance and...

Convert values of mulitiple rows into single column with no duplicate entries - hi all, i went through the forum and found out a way of concatenating row values into column using stuff, but...

how to save student answers for quiz questions of different types Multiple choice essay - I have a table student attempt Quizid studentid questionid attemptid studentanswer If the question is multiplechoice the answer will be a for example if the question is...

Avoiding IF/ELSE - --I believe the DDL and my commented out notes and questions should explain what I am hoping to achieve CREATE TABLE...

Avoiding cursor: Help with getting only first match after previous match - Hi I'm sure there is a better name for this problem, and its probably quite a common one with a...

SQL Server 2008 : SQL Server Newbies

New DBA job, done some digging on Database and have some concerns! - I've recently started a Job at company working as the sole DBA. I come from a .net developer background and...

Alternative to views - Databases in our servers communicate with each other using views. For instance, dbA has views that gets data from db2...

SQL Server 2008 : SQL Server 2008 High Availability

SQL SP/CU taking HOURS on Multi-Node Multi-Instance Cluster - We need to install SQL SP2 and CU1 at multiple sites on SQL/WIN 2008r2 EE clusters with 5 nodes, 22...

SQL Server 2008 : SQL Server 2008 Administration

backup/restore process - Hi, Hi, Our business can afford to loose data up-to one hour. So in-case of say if a table is dropped or a...

Login status - for sql server logins, there is a property menu of status that I can disble the login, and deny permission...

Stop sql serive also stops another service - Hello, When I tried to stop a sql server service on a server, it also pops up a message saying...

Programming : Powershell

Get SQL Query Results as E-Mail - Gurus, I am trying to write a code to send out list of failed jobs in the last 24 hours....

Script to loop through SQL Server Instances and pass Instance Name to Connection String - I am trying to use a SQL Table to loop through SQL Instances and pass the Instance to a script: $DestinationSQLServer...

SQLServerCentral.com : Anything that is NOT about SQL!

DOT.NET Data Reader - Is there any documentation about the inner workings of the DOT.NET data reader. I mean Sstep by Painful Step. I am...

Is it possible to do it? - Hello, This is not about SQL so I am posting it here. Is it possible to have a batch file which...

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

Database Design : Design Ideas and Questions

Advanced group design - Hi All I ask you guidance on this design question because this seems to be one of those things where if...

Lots of Key Lookups vs. UniqueIdentifier Clustered Index - Hello. I'm working on a system that was originally designed with UUID (uniqueIdentifier) clustered indexes. Later the clustered indexes were rebuilt on...

Data Warehousing : Analysis Services

MDX to fill listbox - Hi, I'm using an MDX statement to fill a listbox with available dates: Current statement is SELECT NON EMPTY [Period].[Period by Financial...

inventory of standard vs enterprise capabilities - I spend a week coming to grips with SSAS , being accustomed to Cognos DMR (Dimensionally Modelled Relational). The first 3 days...