In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
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.
 
Deployment Manager Total Deployment
Easy release management for your .NET apps, services, and databases. Get Deployment Manager now.
 
SQL in the City SQL in the City is going on another US tour
SQL in the city is coming to the US, sign up to receive free SQL Server training from industry-leading experts and MVPs. Register for an event now: Pasadena -10/9, Atlanta - 10/11, Charlotte - 10/14.

In This Issue

An Overview of SSIS Variables – Step 13 of the Stairway to Integration Services

In this level of the Integration Services Stairway article, Andy Leonard examines SSIS variables. More »


Review: Stellar Phoenix SQL Database Recovery

Stellar Phoenix SQL Database Recovery Software Review More »


Check If a SQL Server Database Is In Pseudo-Simple Recovery Model Using Windows PowerShell

Check if databases are really in FULL recovery model with a recovery model called pseudo-simple, where the database still behaves like it is still in SIMPLE recovery model until a full database backup is taken.  More »


From the SQLServerCentral Blogs - ORDER BY the numbers

Have you ever needed to order by a calculated column? You might have written it something like this: SELECT LoginID, YEAR(HireDate)... More »


Editorial - SQL Server Should Work for Us

I ran across a post the other day from someone that was trying to find out why their maintenance plan failed. This person had received a failure notice from SQL Agent, which is good. We should all be aware of failed jobs from some sort of monitoring system. Like any good DBA, this person checked the job history, saw an error, couldn't figure it out and posted a question at SQLServerCentral, looking for help. That's a good plan for most anyone ;)

Experienced DBAs know that to debug this issue, you need to look at the maintenance plan log, which has more details. The job history contains a minimal amount of information and usually doesn't help. If you examine the maintenance plan log, it's usually easy to determine which part of the plan failed since the plans are fairly simple constructs. The really exceptional DBAs don't use maintenance plans and instead would rely on some sort of tool or well known script instead to handle their maintenance.

However why do we need to go to the maintenance plan's log? SQL Server includes the job history. It includes maintenance plans. Why doesn't the job understand there is a maintenance plan, read it's log, and return the information? Or give us a button on the job history that loads up the maintenance plan log? That's a simple thing to do, and isn't the job of software to make tasks easier?

This is one of those places where SQL Server feels a bit immature and unrefined. I understand the complexity of the entire product and the limited resources that are devoted to enhancing and growing the product. However, where are the resources that make SQL Server easier for the average and accidental DBAs to use? Those are the majority of the people using the platform.

SQL Server led the industry in producing tools that made it easy to manage and use. Other platforms are quickly catching up, however, and if SQL Server can't continue to improve its toolset, in addition to its features, people will consider other platforms. The cost of SQL Server has risen, but so has the revenue. Do us, and yourself, a favor, Microsoft. Put a team of 50 people to work on usability and improving the tooling. It will be a great investment for the future.

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

What would the following return?
create table #Employee
(
PositionId int,
Birthday datetime
)

insert into #Employee
select 0, '1995-01-01'
union all
select 1, '1983-08-01'
union all
select 2, '1948-07-31'
union all
select 3, '1932-02-29'
union all
select 4, '1980-06-15'

--Query 1
SELECT BirthMonth = CHOOSE(Month(Birthday), 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December')
 FROM #Employee

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

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

SQL Server Execution Plans shows you what's going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by SQL Server, including: Which indexes are being used, and where no indexes are being used at all. How the data is being retrieved, and joined, from the tables defined in your query. How aggregations in GROUP BY queries are put together. Grab your copy today from Amazon!


Yesterday's Question of the Day

How many rows will the select statement return as a result?

CREATE TABLE Emptable
(Empid INT IDENTITY,
 Departmentid INT,
 Name VARCHAR(10),
 Addr VARCHAR(10))
GO
INSERT INTO Emptable
 VALUES
 (2,'Name1','Addr1'),
 (5,'Name2','Addr2'),
 (6,'Name3','Addr3'),
 (3,'Name4','Addr4'),
 (2,'Name5','Addr5'),
 (3,'Name6','Addr6'),
 (4,'Name7','Addr7'),
 (5,'Name8','Addr8'),
 (6,'Name9','Addr9'),
 (7,'Name10','Addr10')
 GO

SELECT *
 FROM Emptable
 WHERE (Departmentid <> ALL (SELECT Departmentid 
                              FROM Emptable AS Emptable_1 
                              WHERE (Empid < 5)
                             )
        )
 GO

Answer: 2

Explanation: The ALL clause compares scalar value with the single column set of values. The select statement has a subquery within it, so the subquery returns a single column set of values.

SELECT Departmentid,Empid FROM Emptable AS Emptable_1 WHERE (Empid < 5))
will give an error because we are trying to get two columns in the resultset (In this case DepartmentID, EmpID) and comparing with one column DepartmentID.

Ref: http://technet.microsoft.com/en-us/library/ms173270.aspx

» Discuss this question and answer on the forums

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.


Featured Script

iSpy (on your development)

Using EventNotification (Service Broker - DDL_EVENTS) to audit Schema change on a Server, or any of it's databases 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

Why SQL Jobs history getting removed automatically in SQL Server 2005? - Why SQL jobs history getting removed automatically in SQL Server 2005 even not configured any user process to remove it?...

Database Engine becomes unresponsive - Hi All, Running MSSQL Server 2005 Enterprise Edition. Recently there's been an upgrade to an application and ever since we are seeing...

Log File Continues to grow - Hi, With the help I received on this forum, I setup my log file to backup every hour via a maintenance...

Check Database Integrity taking a long time - Hi Guys, I have just noticed that in the past few days a Maintenance Plan job that we have on...

Will shrinking the DB improve time taken by index rebuild process ? - Experts, Will shrinking the DB improve time taken by index rebuild process ? If yes, why ? Thanks, Smith.

Step fails, but job reports success. - I have a job that has multiple steps. Each step is set to 'Go to next step' on success or...

How to get file date using xp_cmdshell? - Hi, I have a file in my C: directory, using SQL, I am trying to get the date of that file. ...

SQL Server 2005 : Backups

Netapp SnapManager for SQL - Hello, Is anyone using Netapp's Snapmanager for SQL to do backups/restores? Have you had any issues with it? All comments are...

How to restore master database in a SQL Server 2005 Cluster - I did the following: a. Change the SQL Server start up parameter to add -m (Single User Mode). b. STOP all SQL...

SQL Server 2005 : Business Intelligence

running package from TSQL - Hi I have a package which will be placed at a shared location and multiple user will execute it with...

how to use For Loop Container in SSIS to execute and procedure names stored in a column - Hi all. I need a good resource to figure out how to use For Loop Container in SSIS in order to...

SQL Server 2005 : Development

Qry - In my table i have following result sets sno 101 102 103 105 106 107 i want following structures 101,102,103,105,106,107

Performance counters on Windows server 2003 - Hi, At my place of employment we have a SQL 2005 installation thats running on Windows Server 2003. I set up...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Too many parallel nested transactions - Just recentally we have started to get dump files with the following description: Description: Too many parallel nested transactions When this...

SQL Server 2005 : SQL Server 2005 Integration Services

Troubleshoot Report - Help! Can you point me too some resources that will help me to work out how to identify the source...

Stored procedures and data flow task - Hello, I have a complex stored procedure which returns some results. I want to take this stored procedure and use it...

SQL Server 2005 : T-SQL (SS2K5)

adding line break changes execution plan - Hi , I am confused I had seen a rather long query in SQL profiler its all on one line and...

SQL Server 2005 : SQL Server Newbies

Adding an Extended stored procedure - I am having some problems creating / using an extended stored procedure. (SQL 2005 sp 4) When trying to create a...

SQL Server 2008 : SQL Server 2008 - General

Permission to run only certain SQL jobs in 2008 - I have an AD group Group1 that needs access to the SQL jobs ( only certain ones) so that any users...

Import action stopping in middle? - Hi, Import action stopping in middle due to as below errors, pl suggestion me what could be issues? it may be...

Utility that converts JET to T-SQL - I have been looking for a tool that converts JET SQL to T-SQL...I see plenty of references to SSMA for...

how to make trigger - [size="1"] CREATE TABLE [dbo].[patient]( [pid] [nvarchar](50) NOT NULL, [name] [varchar](50) NOT NULL, [email] [varchar](50) NOT NULL, [password] [varchar](50) NOT NULL, [createdon] [datetime] NOT NULL, [modifiedon] [datetime] NOT...

can anyone give me tips how to use powershell with respect to sql server 2008 - can anyone give me tips how to use powershell with respect to sql server 2008

Auto Populate Dimention Usage - Hi I am creating a cube (first one ish) and i have messed around with it a bit and now my...

Different plan for a query In APP and SQL - Hi I execute a query from web App and I get the select statement from sql profiler. I run exactly the statement...

merge case when - i have this syntax case when convert(varchar(10),t.[Timein],108) >= convert(varchar(10),t4.ltime,108) and spendtime is not null then 'Late' else '' end remarks case when (540-...

Will View Increase the Performance of a query? - Hi All, I just wanted to know whether retrieving data from a view will be faster than directly querying from table. Say...

SQL Command - what is the command to know how many users are using table emp at this moment.

Need Sub-Query ? - Create Table Table1 ( Sno int, Sname varchar(20) ); insert into table1 values(1,'a'), (2,'b'), (1,'c'), (2,'d') select * from Table1 Sno Sname -- ------ 1 a 2 b 1 c 2 d i...

Table Partition - Hi All, I have partitioned five tables with same date range for all tables(with single partition function and single partition scheme)...

CTE Problem :By Shubham Saxena - Dear friends, I need to concatenate a char with in CTE for ex: with cteexp(q,col) as (select cast(1 as int) p,'X' as...

String or binary data truncation. - Can someone help me with determining why my TestTable data does not roll over into my ResultTable data? I have...

Group by Rollup Output Format - Dear, I execute the following query and get the result. [code="sql"] select ISNULL([State],'Whole State') AS [State], ISNULL(City,'All City') AS City, SUM([Population]) AS [Population] from...

Trying to understand this SQL Query - Hi. I am fairly new to SQL Server and have come across this SQL Server Query at work and I am...

Regarding how many time an USP is executed. - Is there any way to find out how many times a USP was executed and how much time it took...

When compressing a DB, will it cause growth first? - I know the subject isn't the clearest, but I think it'd be too long to put the entire question... I'm looking...

Database Mail - Could not connect (no such host)! - Hi all, Tried to find a fix on here for my issue but everything I've found re the above problem me...

Partitioning in SQL Server 2008 - Hi All, I have got few queries on partitioning in SQL Server. - Why should we go for partition in SQL...

How to find dependencies in dynamic sql queries - I think it is far fetched because after searching the internet for days I found nothing. I want to find out...

SQL 2012 and AG's - Recent post from Brent Ozar. Is what he is saying correct. In SQL 2012, if your primary drops offline or the...

Dynamically choose source and destination - Hey Gurus, I have just started working with SSIS and have following task at hand and I feel lost. Can...

Need a help in backup Encyption Certificate - HI ..I am having trouble with keeping backup of encryption certificate. I created database encryption key with folowing query. [b]Use Test GO CREATE...

sql service startup account - Hi Team, I have sql service account which does not have sa rights but have all required domain rights and there...

.ldf deleted and now cant attach database - Hi All, I had deleted my .ldf as it was getting far too large! and then when i went back to...

SSRS - Login failed for user error - Hello! I hope that someone may be able to help as I've been unsuccessful in getting this to work. I have...

SQL Server 2008 : T-SQL (SS2K8)

Searching on Binary data type columns - Hey guys, We have this system that is not built for reporting but we are having to write reports for it...

combining multiple rows into one - Hello all, I have a table that stores pictures for my users but now i need to get multiple pictures into...

smart first name matching in TSQL - Hello experts, I looking for script (actually for data) for smart first name conversion. I.e. if user enter William or Billy then...

Query to delete the records with top 3 marks from a student table - Hi Pls help me to find a Query to delete the records with top 3 marks from a student table

If/Then in table valued functions - Hello All, Can I use conditional logic in a function that returns a table? CREATE FUNCTION dbo.TestFunction (@param1 int) RETURNS TABLE AS RETURN SELECT 1...

how to get name only from these column of enmae from emp table? - Hai friends, My emp Table is create table emp ( location varchar(20), ename varchar(30) ) insert into emp (location,ename) values('A','00001:ravi') insert into emp (location,ename) values('A','00002:rahie') insert into emp...

Using between in where clause from subquery - I am trying to find out how to do the same thing I do in a join in a where...

SQL Server 2008 : SQL Server Newbies

Move a 2008 database to a new machine on 2008R2 - I was supposed to be migrating some 2008 (SP3) databases from old physical machines to new virtual machines. I was...

Table 1 New Record Autocounter field value copied to Table 2 and creates new record - When the user selects an Add New record - Table1 uses the Autocounter field to generate a PrimaryKey and creates the...

Trigger to set a value for a column - I have a column that has to be set with a certain value as A,B,C or D when a certain...

Replace the values from the lookup tables - [b]Lookup Table 1:[/b] CREATE TABLE [dbo].[LT1]( [LT1_ID] [varchar](25) NULL, [Desc] [varchar](25) NULL); insert into [LT1] ([LT1_ID] ,[Desc]) values ("Cat_Code","Category"), ("Prod_Code", "Product") [b]Lookup Table 2:[/b] CREATE TABLE [dbo].[LT2]( [LT2_ID]...

ldf file size - Dear All I have a db for which recovory model is Full. And i am taking only full back up. .Ldf...

SQL to split row by date (split into multiple rows) - I am looking for help with splitting a row into multiple rows based on dates overlapping. As an example, I have...

SQL Server 2008 : Security (SS2K8)

How to prevent ANY use of xp_CmdShell? - Please forget whether or not you're pro or con on the subject of the use of xp_CmdShell for just a...

SQL Server 2008 : SQL Server 2008 High Availability

Replication SYNC Error - I have setup a new replication via backup and restore but its giving me two errors first "the row was...

SQL Server 2008 : SQL Server 2008 Administration

SQL Server Agent jobs running under SA - I have an issue with some of my jobs. They are owned by the same domain account that the SQL...

Stop particular event logging in SQL Server errorlog - Hi, I need to stop a particular event "Error: 1105, Severity: 17, State: 2. Could not allocate space for object 'dbo.XXX'.'XXXXX' in...

How do you deal with Sql Service Pack Upgrades....? - Years ago we installed a sql server service pack on a non-clustered server and the results were usually fine. Now...

page life expectancy - I'd like to get an idea of what is normal when it comes to the page life expectancy. I was...

Centralised monitoring of disk space - Hi Experts, I want to monitor disk space of all servers and i have created a centralised server from where i...

User Connection Memory - Memory Leak?? - Hello, I have had an issue on a SQL and SSRS server that i have been unable to resolve without getting...

Tracking Job Info from AutoEmail - I'm trying to track down the job_id for a job that sent an auto-email alert to us this morning. The...

creating an instance of the com component with clsid {aa40d1d6-caef-4a56-b9bb-d0d3dc976ba2} from the IClassFactory failed due to the following error : c001f011 - Hi Folks, i tried to edit a job step from ssms and am getting the following error. i've attached the screenshot of...

Programming : General

how to group by in subquery - I have a scenario where i have a message table, which is like this [code="plain"]MessageId int primary key token text hash nvarchar(50) subject text name...

get set of random rows with distinct values - I have an interesting little T-SQL problem that has me beaten. I want to retrieve a small set of randomly selected...

Programming : Powershell

Write PowerShell Output to SQL Server Table - Heh... First time I've had to admit being a "newbie" in a long time so be gentle with me. I...

Open Excel Error Using PowerShell in SQL Server Agent Job - I am trying to run a Powershell script, which opens and modifies an Excel spreadsheet, from a SQL Agent job...

SQLServerCentral.com : Anything that is NOT about SQL!

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

SSRS List & Blank Parameters - Hi geniuses! Is there a way to have a report with 3 multivalue parameters: AREA, LOCATION and PROJECT, exposing only 1...

Internet Explorer 10 and SSRS 2005 - I have Reporting Services 2005 running on Windows Server 2003 and IIS 6. Everything looks fine with Internet Explorer 9,...

How Indicators works - Hello everyone - I was wondering if anyone can provide some insight on how indicators work. I read about the percentage...

SSRS How to trigger subscription when data is ready - hi guys, I have a few Reports that are subscripte bei a normal subscription meaning having a fixed time where it...

Data Warehousing : Integration Services

Replacing Batch File call in SSIS Package - I am new to SSIS. I am trying to replace aaa.bat file call from SSIS package by Storedprocedure(batch file by...

Configuration - Hi , I have around 50 Entities.Where in for each entity I have around 6-20 Packges .In each package Iam calling...

What assemblies do i need to add to my SSIS Project? - I just inherited an SSIS package with a script component but it seems to be missing a bunch of assemblies....

SQL agent job fails when accessing the csv file - Hi All, I have a csv file in a shared folder in a server. My package is running fine in the...

Unable to Import Null rows - Hello, I am importing the following data into a table. Country Acno ContCode SalesValue SalesDate FRA S000 6030 151.64 2013-06-10 NLD S001 NULL 1315.53 2013-06-10 FRA S003 6027 29.39 2013-06-10 If i run the package manually in Visual...

Microsoft Access : Microsoft Access

Access Query ported to SQL - Here is the "inner" part of a query in Access. UPDATE [Vehicle Detail] SET [Vehicle Detail].ReturnDate = #4/29/2013#, [Vehicle Detail].ReturnSource = 'ReturnProcess' WHERE ((([Vehicle...