In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Source Control The best way to version control T-SQL
SSMS plug-in SQL Source Control connects SVN, TFS, Git, Hg and all others to SQL Server. Learn more.
 
SQL Monitor Free eBook: SQL Server Concurrency
Every DBA must understand SQL Server concurrency and how to troubleshoot any issues. Kalen Delaney's eBook explains all - download it today.
 
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.

In This Issue

SQLServerCentral Runs sp_Blitz - Security

A series that looks at the SQLServerCentral database servers using the Brent Ozar Unlimited sp_blitz script. Read about what we learned. More »


Introducing Boomerang - A Notification Framework

Boomerang is a notification framework for professional professionals and system integrator's providing service oriented infrastructure with a SQL based rapid development interface.  More »


SQL Server T-SQL Tuning -TVF and Scalar Functions

A UDF is very convenient for centralising business logic as we can specify a set of business logic in one UDF which references multiple stored procedures and ad-hoc queries. However, they can lead to significant performance degradation due to their demands on the CPU More »


From the SQLServerCentral Blogs - Auto grow–On or Off Straw Poll

David Postlethwaite who contributes to this blog regularly sent me an email this morning asking about Database Auto grow. This... More »


Editorial - A Patch Disaster

Can you imagine sending the wrong patch to the wrong machines? That's what happened with an Australian bank. Am OS patch was sent to many more machines than it should have. The patch was designed for desktops, but managed to get deployed on servers and resulted in some sort of software corruption.

SQL Server users are fortunate that we rarely have security patches for our platform. There are cumulative updates every other month, but the majority of them aren't aimed at SQL Server, and many of them may not even be required for the host Windows OS. Your organization's policy may require the OS patches, and if they do, you should be aware of when and how they are being deployed. 

Even if patches are not supposed to be deployed to your servers, you should plan on being aware of the deployment date. You never know when an administrator will make a mistake and deploy a patch to your database server and necessitate a restore. I would recommend that you double check your backups and ensure restore scripts are handy on any patch day.

Our computer infrastructure becomes more complex all the time. At the same time, many of us become more specialized, working in a more focused area, and counting on others to manage the parts of our system we do not have time to worry about. People will make mistakes, and we should ensure we can recover our systems from those mistakes.

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

Don't forget to vote for the SQL Bits sessions you'd like to see. Steve Jones and Grant Fritchey have submitted sessions and would love to have your support.

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 execute the following T-SQL

DECLARE @A INT,@B INT, @C INT

SET @A = 2

SET @B = 3

SET @C = 4

SELECT  (@A + @B)-@C AS 'Result #1' 

SELECT @A + (@B-@C) AS 'Result #2' 

SELECT (@A + @B) * @C AS 'Result #3' 

SELECT @A + (@B * @C) AS 'Result #4' 

The question is what are the results returned by each of the 4 select statements.

The results are shown as: Result #1, #2, #3, #4

For example, the results might be 1,20,3,41 (not the correct answer)

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

This question is worth 1 point in this category: Precedence. 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 Data Integration Recipes

SQL Server 2012 Data Integration Recipes provides focused and practical solutions to real world problems of data integration. Need to import data into SQL Server from an outside source? Need to export data and send it to another system? SQL Server 2012 Data Integration Recipes has your back. You'll find solutions for importing from Microsoft Office data stores such as Excel and Access, from text files such as CSV files, from XML, from other database brands such as Oracle and MySQL, and even from other SQL Server databases. You'll learn techniques for managing metadata, transforming data to meet the needs of the target system, handling exceptions and errors, and much more.

Get your copy from Amazon today.


Yesterday's Question of the Day

We created three tables and some values for the columns

CREATE TABLE TT1 
( ID INT
, Name1 VARCHAR(20)
, Name2 VARCHAR(20)
, Name3 VARCHAR(20)
)
CREATE TABLE TT2 
( ID INT
, Name2 VARCHAR(20)
, Name3 VARCHAR(20)
)
CREATE TABLE TT3 
( ID INT
, Name3 VARCHAR(20)
)

INSERT TT1
VALUES ( 1, 'A', 'B', 'C'),
( 3, 'AA', 'BB', 'CC'),
( 4, 'AAA', 'BBB', 'CCC'),
( 6, 'AAAA', 'BBBB', 'CCCC'),
( 7, 'AAAAA', 'BBBBB', 'CCCCC')
INSERT TT2
VALUES ( 1, 'B','C' ),
( 2, 'BB' ,'CC'),
( 3, 'BBB' ,'CCC'),
( 6, 'BBBB' ,'CCCC'),
( 8, 'BBBBB' ,'CCCCC')
INSERT TT3
VALUES ( 1, 'C' ),
( 3, 'CC'),
( 5, 'CCC'),
( 6, 'CCCC'),
( 8, 'CCCCC')

Then we use following SQL query for the results,

SELECT TT2.ID AS ID1
     , Name1+TT2.Name2 AS N1
     , t1.Name2 AS N2
     , t3.Name3 AS N3
     , COUNT(DISTINCT TT2.ID)
 FROM TT3 t3
  LEFT JOIN TT2 
    ON t3.ID = TT2.ID
  cross JOIN TT1 t1  
 WHERE TT2.ID > 7
 GROUP BY TT2.ID
     , Name1 + TT2.Name2
     , t1.Name2, t3.Name3
 ORDER BY Name1 + TT2.Name2

Does the query run successfully?

Answer: yes

Explanation: The aliases that are used in the query are valid, so the query executes

Ref: Using Table Aliases - http://msdn.microsoft.com/en-us/library/ms187455(v=sql.105).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

Script to replace 5 consecutive digits with special character

In a given input string, The script searches for 5 consecutive digits and replaces it with a special character.  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

Agent jobs don't run according to schedule - Has anyone seen in SQL 2005 SP3 where agent jobs simply don't abide by a schedule setup? I had a...

Script to show the statistics from all user tables from a DB - Enjoy! :-D [code="sql"]Set nocount on DECLARE crComando CURSOR READ_ONLY FOR select name from sys.objects where type = 'u' order by name DECLARE @name varchar(400) OPEN...

SQL 2008 R2 - CDC or Trigger - Both a trigger and cdc (change data capture) can be enabled to monitor changes to a table. When would you...

Log size is Big - i am new in DBA. If in a database MDF file Size is 10 GB and LDF file size is 50...

Error 26073 - Hello, We are also getting similar error on our SQL server in cluster environment Error 26073 TCP connection closed but a child process...

Reclaiming deleted but unused LOB space in SQL 2005 - Hi, I've spent most of a day playing/wrestling with this, and my google searches have found lots of information but no...

How to free table space - Hi all, Below is the result when we run the sp_spaceused for a table: name rows reserved data index_size unused ---------- ----------- ------------------ ------------------ ------------- mytable 0...

SQL Server 2005 : Business Intelligence

SSIS Not Working Under Credential - Hi guys Hoping someone can point me in the right direction. I have an SSIS package that reads in a number of...

HOW run ssis package on table update - Hi i have created ssis package to move data from one table to other in different instances when i update...

Error while connecting to the server.. - Please Help!!!!! This error is driving me crazy! "A network-related or instance-specific error occured while establishing a connection to SQL Server....

SQL Server 2005 : Data Corruption

ERROR 823 in SQL 2000 -> Need help - Hi All, Good day and Happy New Year !! I need your inputs for the below issue - We have a 1.5...

SQL Server 2005 : SQL Server 2005 General Discussion

Maintenance Plans issues in SQL Server 2005 - Hi experts! This is my first time creating a Maintenance Plan in SQL Server 2005 and I’ve a couple of questions...

duplicate row after a truncate - I got an error that i thought was impossible. I am not sure if i understand what is going on,...

Assigning current date without time value to column - Hi all, I have the following: [code="sql"]Insert into table(col A) Select DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) As ColA from table B [/code] I...

SQL Server 2005 : SQL Server Express

SQL Express 2005 stoppped running on win xp sp3 - Hi, we have ms sql express installed on a computer which we use as server and it is on LAN. Before...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Which Query has better statistics ? - Queries [code="sql"]set statistics time on SELECT [SalesOrderID], * FROM Sales.SalesOrderDetail WHERE ProductID = 750 UNION SELECT [SalesOrderID], * FROM Sales.SalesOrderDetail WHERE ProductID = 953 SELECT SalesOrderID, * FROM Sales.SalesOrderDetail WHERE...

Problem with "If Exists (Select ...) Or Exists (Select ...)" - Does anybody know why the following [code="sql"] If Exists (Select * From Inserted I Join dbo.T_PaymentItemGroup PIG On PIG.PaymentItemGroupID = I.PaymentItemGroupID Join ...) Or Exists (Select * From...

sql server memory - Hi All, I am not sure if this is a fair ask or no but this is something i want to...

When to Index - I have both reporting and operational systems.. So looking at each system.. what becomes a good point for indexes? (see query at...

Memory Settings Help Please - I have a server running 2003 Enterprise x64 Edition SP2 Build 3790 OS and SQL Server 2005 (9.00.5254.00) Enterprise Edition...

Error while generating reports - After 12 hours of analyzing, DB Tuning Advisor started step 4 "Generating Reports", but finished with a warning. The Tuning...

SQL Server 2005 : SQL Server 2005 Integration Services

Unable to run my SSIS 2005 package using bat file... - Hi all, I had a simple batch file to run my ssis package. But i keep getting this error which i...

How to load data to target tables (only if no dataflow task is fail in ssis package) - Hello All, In my ssis package, i have 25 data flow tasks which will load from some third party database in...

ADO.NET Destination Custom Propertiees? - Hi I tried to loading xml into Oracle table using SSIS, OLEDB destination wont support, so I use ADO.Net Destination, ADO.NET Destination...

Integration and Browser services stopped - Hi all, In our server Integration and Browser services are stopped by its own.I dono why its happening daily and manually...

Migrating Informatica Packages to SSIS - Hi , First come First i like to keep my request in front of you all. i got a new requirement...

SQL Server 2005 : T-SQL (SS2K5)

Wanting to learn. Who can best explain this in a simple yet crystal manner to a noob?? - Hi all, I am very new to T-SQL.. and i came across this: ROW_NUMBER ( ) OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause ) Can...

SQL Server 2005 : SQL Server Newbies

Initialisation file - Hi, I'm totally new to SQL Server *.sql command files. I'd like to create a SQL file that runs the initialisation...

SQL Server 7,2000 : Administration

Anyway to write VB/C#/C++ scripts in SQL 2000? Need financial calculations (IRR, NPV, etc) - I'm working on a SQL 2000 database server (I know .. but it is what it is unfortunately), and I need...

SQL Year and Week numbers - Hi does anyone know how to create a query in SQL 2000 or 2005 to get the year and week...

SQL Server 2008 : SQL Server 2008 - General

sql job fails --- need help - Hello I have one up and running job since few years but today i am facing one strange error. here is error...

How to search in text column? - When I use "Search Customer Reviews" in Amazon.com, it surprises me that it only take less than half second with...

How to stop a jobstep and go to the next in a scheduled job. - Hi fellow SQL server users, I have a SQL server agent job with several job steps. This job is scheduled and...

#Temp tables in SP - hi All, I am facing a typical problem in my SP, actually the SP is using may temp tables. For better...

Form which is connected to SQL database - Hi all. I have got a task which I'm working with on the moment. I'm looking for an answer here, as this...

storing the multimedia files on sql server database - hi all, i want to store multimedia files like images,audio and video files on sql server database without path... is it...

Heavy waits on PAGEIOLATCH_SH got cleared after restarting web application - Hi, I noticed unusually heavy waits on PAGEIOLATCH_SH during an UPDATE activity and it was taking few hours. Below error is noticed...

How to first date of the previous Quater of the given date. - Hi, I am using Sql server 2008, My requirement is to find the first date of the previous Quater of...

Is it Possible in SSRS 2005 - Hi all, Could any one help me out on this : Can we create a SSRS report from different data sources ex:Oracle...

Doubt in SSRS - Hi all, Could any one let me know the Difference between SSRS 2005 and SSRS 2008 R2 for export to...

sql query1 - hi friens i have small doubt in sql server.plese tell me how to solve this issuse. table data contains like id ...

How to move table partitions to different file groups - Hi Experts, Can you please help me in movin my 4 table partitions of a table to 4 different file groups....

Passing Object Type Variable to Child Package - Hi Friends, I am deriving a result set in a parent package and I have to pass the same to the...

Fire Trigger when stored procedure is created or modified. - Hi, Can anyone please let me know how to fire trigger when stored procedure is created or modified ? I want such...

Indexing. - I know this is a situation based question but on the general though, what columns would be best suitable to...

"USE DATABASE" in stored procedure - Need a favor..! The below Proc works fine when I execute, The Proc will generate script of permissions for the database...

Question about Optional Parameters in Stored Procedures - Is this the standard way to deal with optional parameters in SP? [code="sql"] CREATE Procedure AddMonth ( @EnterMonth as int, @product int = null ) AS INSERT INTO X SELECT...

SSIS Not Working Under Credential - Hi guys Hoping someone can point me in the right direction. I have an SSIS package that reads in a number of...

Can we have same Partition function and Partition Scheme to all the tables in a schema ? - Can we use same Partition function and Partition Scheme to all the tables in a schema ?

New more powerful servers are slower than old ones? - We have 2008R2 servers, We are testing performance on new hyper-v servers before we migrate from old 2005 phsyical servers...

sql server 2008 - Replication-Replication Snapshot Subsystem: agent TOMMY-PC\MSSQLSERVER2008-SOURCE-SOURCE_ONE-1 failed. Executed as user: TOMMY-PC\TOMMY. The directory name is invalid. The step failed. - Hi Guys, My distributor and publication is in the same instance. After setting up the distributor and publication, i realise that one...

SqlDateTime overflow - I receive this error when inserting in the database. I know my dates are good. SqlDateTime overflow. Must be between...

Date Comparisons - Other than issues with sorting and MAX, are there any potential issues with using something like CONVERT([varchar](10), datetimefield, 101) which...

The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset ... - I am kind of in a bit of a Jam. Seeing a lot of Storage I/O issues. The OS is MS Windows...

Has anyone used Data Domain storage for SQL 2008 backups? - We are creating a Microsoft Failover Cluster with Windows Server 2008 and SQL Server 2008. Our storage administrator would like...

SQL Server 2008 : T-SQL (SS2K8)

Deadlock list - Hi All Without using Trace we need last week dead lock list for this any script please suggest us. Thanks in advance.

creating SQL login with cursor - Hi I have 100 or so users in a database but I need them all to have a server login...

Problem with inserting the data into temp table - Hi all, I am trying to stored the output of sp_spaceused into a temp table as follow. [code="sql"] if exists (select 1 from...

Reg: Image Directly Insert into database binary Format - Dear All I am working in University Project.I have 10000 students physical photos in one directory in corresponding register number...

Having doubt on procedure with joins - Hai friends i ve 5 tables now i wanna make a join with in that and i m done join now...

Best way to Calculate Human Age from a Birthdate - Hello Everyone Happy New Year!!! I am fooling around with some code, and was wondering if there is a really great function...

Need Result By using pivot or CTE - Hi all, I have a data like as shown below in my table. ID Name Mode ----------------------------- 1 AAAAAAA Phone 2 AAAAAAA Phone 3 AAAAAAA...

Need to find out whether date is of which data type - Suppose I have a variable which contains date value in a string format. eg: declare @value1 varchar(50) = '2013-01-08 15:44:12.2081606 +05:30' Now my...

Puzzle: vertex covers in SQL - I have to do a book chapter on graph database. I already did “The Kevin Bacon” problem in SQL for...

Compare one row with another in a Table ! - Hi , House_Acc Accountid repcode 123 1 J978A 123 2 J978A 123 3 J978A 123 4 EG567 456 21 BR5TG 456 22 BR5TG 678 66 ZHR06 678 45 ZHR06 678 34 NH678 How...

SQL Server 2008 : SQL Server Newbies

DB Mirroring and Log Shipping - Hi all, I'm using SQL Server 2008 R2 and was wondering if it's possible to set up DB mirroring on a...

Slow Batch Process - Windows 2008 R2 Server VM on Hyper-V host SQL 2008 R2 RAID 1 Array for Log (Seperate physical spindles) RAID 10 Array for...

WHERE [column name] NOT IN check after each insert - I have a table like this: OLD_TABLE: ID National_Code ---------------------- 1 0000000000 2 1111111111 3 1111111111 4 1111111111 5 1111111111 6 2222222222 7 3333333333 I need to insert above National_Code values...

Update statics - Hello Masters, What exactly "Update static" is ? Is it just removing fragmentation from database that is created by DML queries? Is...

Decimal separator missed when pasting into excel from sql query - I have an sql query which returns some numeric values that sql server is representing with a decimal point. Since...

Replacing fixed data in a WHERE clause with dynamic data - Hi, I am new to SQL 2005 and have a question about Stored Procedures and have not been able to find...

SQL Server 2008 : SQL Server 2008 High Availability

One Node in Cluster has completely failed and we need to rebuild the cluster - One node in our cluster had a hardware failure and comepletely down and need to rebuild. I am asking for...

Update statistics - hello - i have bi-monthly maintenance plan that re-builds all the indexes of my databases where fragementation > 5%. my indexes...

SQL Server 2008 : SQL Server 2008 Administration

How to write continous select Statement avoiding the deadlock - Hi all, I have a requirement where in I need to select the date column of a table while it...

Data distribution - How can I find data distribution for a column?

TempDB Space issue! - Another issue we are facing. We have mount point, under the mount point, we have data1, data2, log1, log2, tempdbmdf1, tempdbmdf2,...

Databases are under "Suspect" Mode - All, I have couple of the databases are under suspect mode. What are some of the process you should follow under this...

Restore C drive (OS, SQL assemblies) to another disk to save the day - The C drive (OS, SQL assemblies) are stored in SAN Array1 and all data & log files are stored in SAN...

SSRS 2008 - How to handle subscriptions on 2 instances of SSRS on a cluster? - Hi all, I ran into an interesting question from a developer on how to handle subscriptions on 2 instances of...

Database Mail e-mail notifications not being sent on completion of backup jobs - Hello -- I configured our backup jobs to send notifications to the administrators on their respective completion. However, the notifications have...

Career : Certification

Help with where to start learning more in depth - Want a DBA job - This has probably been discussed before but thought id create my own thread so i can inform you of my...

Programming : General

Mulit row update triggers - How can I create an update trigger that can allow for multiple rows being updated at the same time? I...

Programming : Powershell

Help me Error SSIS Error (Agent Service) - An invalid reporting option was specified. Only E, W, I, C, D, P, V, and N are allowed Import Data Source...

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

Failed SQL job info from PS to sql table - Hi All, Please have a look at the below link and help me to get the details (as 4 Columns) into...

SQLServerCentral.com : Anything that is NOT about SQL!

How's this for a Job Description - So I recently got this job description in the mail and at first glance it looks pretty good, but at...

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

Timeout expired in reporting services log - We are getting the Time out expired error message in reporting service log file while executing the one report which...

Data Warehousing : Integration Services

Getting better performance when lowering DefaultBufferMaxRows value - is it normal? - Hi, We have server "A" running Windows Server 2003 on 64Bit OS with 128G of memory and we are setting a...

SSIS Exchange Web Services - Could not load file or assembly - Hi I'm trying to write a Source component in a dataflow task to recover some details from Exchange. Unfortunately when I...

SSIS Agent Job Passing Parameters - I'm trying to set up a SQL Agent job to run a SSIS package. I want to pass a parameter to...

Data Warehousing : Analysis Services

MDX: Why is MDX so much slower than getting the ALL data from the cube? - I have a pivot grid attached to the cube. The cube has 7 million records and shows all our content...

Processing of an Cube kills the service - Hi, Im very new to OLAP, so be nice :-) Version 9.00.1399.06 Windows 2003 We have a cube the for some reseaon refuses to...