In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Monitor Get your priorities straight with SQL Monitor
“SQL Monitor gives me the ability to quickly see what my priorities should be, and delivers the information I need to make the right decisions,” Aaron Kolysko, DBA. Start monitoring with a free trial.
 
SQL Doc Hate explaining your database in meetings?
SQL Doc quickly documents your entire database schema so that you can easily present it to others. "This tool is embarrassingly easy to use." David Hayden, DavidHayden.com. Download a free trial now.
 
SQL in the City SQL in the City London - Free SQL Server training the Red Gate way
SQL Server MVPs, ingeniously simple tools and an awesome training experience. Find out more and register for the next event happening in London this June.

In This Issue

Rendering PDFs Natively in SQL

How to render PDF documents using SQL CLR. Also a good introduction on creating SQL CLR functions. More »


Using Policy-Based Management to Check Number of SQL Server Data and Log Files

To meet our database configuration standards we want to make sure that we have only one database transaction log file and more than one data file. In this tip we look at how this can be done using a policy. More »


SQL Saturday #220 - Atlanta

Join us for a free day of SQL Server training and networking in Atlanta on May 18th. More »


From the SQLServerCentral Blogs - Comparing an inner join vs a subquery

I was reading through Stackoverflow today and saw an interesting question. SQL inner join vs subquery. The user was comparing... More »


Editorial - Our Race with Machines

The Terminator and Matrix movies showcase a world where our computing machines have taken over the world to the detriment of humanity. These are worlds were the machines are superior to humans in many ways. It's a view that scares many people and has them concerned about the future as we find machines doing more and more work for us humans.

Whether you think we'll reach a point where machines are sentient or not, we do know that for single tasks, or a limited domain of tasks, machines can excel at some tasks. We've seen tremendous improvements in any number of fields where computer technology combined with machinery has greatly improved the way in which a business can function. These improvements have sometimes come at the cost of jobs, which can be a problem in some cases as humans must adapt to the changing ways that organizations function.

However the news isn't all bad for human workers. I ran across a very interesting TED talk about machines and how we might compete or work with them. It's from Erik Brynjolfsson and discusses some of the advancements, including the Deep Blue and Watson projects from IBM. While these projects have sometimes proved they can beat individual humans, groups of both humans and machines have proven to be even more capable of solving problems.

I don't know how the world will change, especially our jobs, but as I've watched technology be introduced, grow, and dominate business in the world, I've also noticed that there are more and more opportunities for those of us willing to work hard, learn, and adapt.

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

In SQL 2008, SQL 2008 R2, and SQL 2012, assuming that the database Playpen exists and you have full DDL and DML access to it and that nested triggers have not been disabled, what happens when the following code is executed?

use playpen
go
create table dbo.t1 (id int identity(1,1) primary key, x int not null);
insert t1(x) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);
go
create view dbo.t1v with schemabinding as
  select t.id as id, t.x as x, t.x+1 as y, t.x-1 as z
    from dbo.t1 as t where x/2 = ceiling(x/2.0);
go
create trigger t1vdel on dbo.t1v instead of delete as
  delete t from dbo.t1v as t where exists (select * from deleted d where d.id  < t.z);
go
declare @before int = 0, @after int = 0;
select @before = COUNT(*) from t1v;
delete t1v where id > 1;
select @after = COUNT(*) from t1v;
drop view t1v;
drop table t1;
select @before/@after;

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

This question is worth 2 points 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 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks.

Get your copy from Amazon today.


Yesterday's Question of the Day

Below are 5 statements, I execute all statements. Which statement(s) execute successfully or give error. Choose your answer below. The answers are in order of the numbered statements.

---- DROP TABLE
IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE OBject_ID =  OBJECT_ID('tempdb..#Table'))
    DROP TABLE #Table
GO
---- CREATE TABLE
 CREATE TABLE #Table (OrderNo INT, City VARCHAR(100), Country VARCHAR(100))
 GO
 INSERT INTO #Table
 SELECT 101, 'Reims', 'France' UNION ALL
 SELECT 102, 'Delhi', 'India' UNION ALL
 SELECT 103, 'Münster', 'Germany' UNION ALL
 SELECT 104, 'Lyon', 'France' UNION ALL
 SELECT 105, 'Reims', 'France' UNION ALL
 SELECT 106, 'Delhi', 'India' UNION ALL
 SELECT 107, 'Münster', 'Germany' UNION ALL
 SELECT 108, 'Lyon', 'France'
 GO
 ---- STATEMENT 1
 SELECT * FROM #Table
 GO
 ---- STATEMENT 2
 SELECT DISTINCT City, Country FROM #Table ORDER BY CITY, COUNTRY
 GO
 ---- STATEMENT 3
 SELECT City, Country FROM #Table ORDER BY CITY, COUNTRY
 GO
 ---- STATEMENT 4
 SELECT City, Country FROM #Table ORDER BY CITY, COUNTRY, OrderNo
 GO
 ---- STATEMENT 5
 SELECT DISTINCT City, Country FROM #Table ORDER BY CITY, COUNTRY, OrderNo
 GO
---- DROP TABLE
IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE OBject_ID =  OBJECT_ID('tempdb..#Table'))
    DROP TABLE #Table
GO 

Answer: successful, successful, successful, successful, Error

Explanation: If you are using DISTINCT then Column names specified in the ORDER BY clause, must be defined in the select list.

Ref: http://msdn.microsoft.com/en-in/library/ms188385.aspx

» Discuss this question and answer on the forums

SQL Backup and Restore

A DBA's tasks, from day-to-day, are rarely constant; with one exception: the need to ensure each and every day that any database in their charge can be restored and recovered, in the event of error of disaster. In this book, you'll discover how to perform each of these backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Red Gate's SQL Backup tool.

Get it from Amazon in print  or download the ebook for free from Red Gate


Featured Script

Display Job Schedule Description (SQL Server 2005)

The idea was taken from this script: http://www.sqlservercentral.com/scripts/Maintenance+and+Management/61448/ Which did not work in SQL 2005 due to table changes on MSDB 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

Log shipping 2k5 file retention issue - the files are not being deleted. - The issue I have is with the file retention of the .trn logs in my log shipping drive. 5 days...

Memory Optimization - Hi All Environment: Windows 2003 Enterprise Edition - 32bit SQL 2005 Standard Edition - 32Bit PAE has been enabled Server has 16GB of ram installed and 8GB...

Replicate from Non-Partioned Table (Publisher) to a Partitioned Table (Subscriber) - Is it possible to Replicate from a Non-Partioned Table (Publisher- Standard edition) to a Partitioned Table (Subscriber- Enterprise edition) using...

Memory Poke - I have a supplier who insists on having two jobs that every couple of hours change the Max Memory setting...

SQL Server 2005 : Business Intelligence

How to Unzip the zipped file - Hi, I have a WinZip file with the .dat.gz extension and i need to unzip that zip file and extract the...

How to check Edition of Analysis Services, version and edition of Reporting Services - Hello all, How can I determine the Edition of SQL Server Analysis Services installed? Say for a SQL Server Database Engine, I...

SQL Server 2005 : Development

Android Development - Hi, i am newbie to Andorid development .. suggest me good material to this journey thanks

Difference between varchar(max) and varchar(8000) - Hi, whats the actual difference between varchar(max) and varchar(8000). Is the difference applies same for nvarchar(max) and nvarchar(8000). Thanks in advance.

SQL Server 2005 : SQL Server 2005 Compact Edition

Can we query/import/replicate/Sync data from SQL Compact 4.0 to SQL standard edition 05/08? - Hi, I have a SQL Compact 4.0 (.sdf) data file. I want to periodically import or preferably sync that data within SQL...

SQL Server 2005 : SQL Server 2005 Integration Services

SSIS and MIMER - Hi, I have to copy some data on a nightly bassis from a MIMER database hosted on a UNIX platform....

SSIS Error - Code : 0xC002F304 (At Send Mail Task) - Hi all I have an SSIS package scheduled to run once a month. It's running under the SA Account and...

create a SSIS package which will call the stored procedure and dump the output of the procedure into table - I want to create a SSIS package which will call the stored procedure and dump the output of the procedure...

SSIS - Truncation Warnings - I have created a package to import data into a table that has been created per a data dictionary. I...

SQL Server 2005 : T-SQL (SS2K5)

Can a CURSOR be populated by firing a stored procedure? - The subject says it all. Can I do this? DECLARE Test AS CURSOR LOCAL FOR [b]EXECUTE uspMyStoredProcedure[/b] OPEN Test FETCH Next FROM Test...

Date Conversion Fails in Stored Procedure - But code works in a Trigger !? - Hi All, I have a Trigger which basically using the INSERTED and DELETED Tables - populates an "Audit" table - with DATE, TIME...

Data transfer while dynamic increment of duplicate values. - Hi, I have to insert data from one table to another in SQL 2005 (destination having lesser but the same name...

Convert Varchar to Decimal - Hi, Select LHP.LHP_PARAM_RESULT_VALUE, Cast(Replace(LHP.LHP_PARAM_RESULT_VALUE,',','') as decimal(18,2)) from LS_HMT_PARAM_RESULTS LHP Where ISNUMERIC(LHP_PARAM_RESULT_VALUE) = 1 AND LHP_PARAM_RESULT_VALUE <> '' This Query Run some results and with [b]Arithmetic overflow error...

compare varchar dates - I have a varchar column containing dates (not my design) with this format 2013-02-12 I need to extract records between two...

SQL Server 7,2000 : Administration

Delete or move tempdb .ndf file - We have tempdb on a different physical disk than our user databases.  A few weeks ago, we were running out...

SQL Server 7,2000 : T-SQL

Trace Group Simular Statemments - Hey I run serveral traces and have a problem analyzing data I know that there is some scripts out there, as can...

Database Mapping Error - Hi I am actually trying to make relationships in an already designed database.. Database is historic one and no proper relationships,...

SQL Server 2008 : SQL Server 2008 - General

Need to get some disk space back from a table, need to shrink... - OK, I know all the reasons I shouldn't be shrinking a database, but there's really no other option. Also, this...

SSIS - Cannot import config file - Hello, I have a 2008r2 SSIS package with dtsConfig files for different runtime environments. I have deployed the package to QA....

XML Error on output.. - With some help from the forum I got my job to run.. thanks... here is the job, I changed the query...

SQL 2008 - File Group change - Changing Primary filegroup to a new filegroup 1. Any negative impact i should cover before making the above move. It...

Excel 1 : Databases 0 - SME addicted to Excel! - Hi folks, I feel like I'm banging my head against a brick wall so figured I'd ask for some sage advice!...

Process stuck in Killed/Rollback status - I have a process that has been stuck in KILLED/ROLLBACK status for the past 24 hours. The statement is: ALTER SERVER...

find pairs of NULL records without resorting to CURSORS - [code="sql"] create table #tmp ( ref int, Date datetime, time int, seqno int ) insert into #tmp (ref,date,time,seqno) values(34227664,'20130227',0,399850) insert into #tmp (ref,date,time,seqno) values(34227664,'20130304',0,399860) insert into #tmp (ref,date,time,seqno) values(34227664,'20130313',0,399870) insert into...

SQL Server 2008 R2 poor performance on VMWare - We recently migrated our production server from a physical cluster environment to a VM environment, and ever since then the...

Split Comma separated string insert into Separate column . - DECLARE @strCSVString VARCHAR(4000) SET @strCSVString = 'Vinayak,20-07-2013,100,Sagar,23-10-2012,334,Leena,03-08-2014,93,Meera,20-07-2013,100,Ajad,20-07-2013,100,Ajay,23-10-2012,334,Vijay,03-08-2014,93' DROP TABLE #FinalResult DROP TABLE #Result CREATE TABLE #Result(ColumnValue NVARCHAR(100),cnt INT,rno INT) CREATE TABLE #FinalResult(Name NVARCHAR(100),Date NVARCHAR(100),Num NVARCHAR(100))

Error message during deletion - Hi All, I am trying to delete a record from a table using a stored procedure.While executing the SP i am...

Select only 1 row from multiple 'OR' {satisfied} conditions - Hello, I came across a situation in SQL 2008 to fetch only single row from a table even if each of...

Help with SQL Server backups slow ( backup performance slower than before after data purge) - Hi everyone, (searched SQL Central... I didn't find anything specific) Wanted to get some ideas on solutions. I have a DB...

SQL Audit Help - Does SQL Audit Really Store Audit Info ON The Windows Logs - First off I admit I am no SQL Audit guru but from what I have read so far it sounds...

? on running a sp in dbmail - Hi , I am trying to figure out how I can attach a file as an XML in dbmail and run...

SOX2 Compliance - SSAS Cube - We have enabled TDE on all databases that contain PII data for SOX2 compliance. However, how do we do this...

Concept of Session killing by Sql server. - Hi, I had make changes in server connection properties and set timeout time 300 Secs. Now i had connect to a database...

Deadlock Issue - We are running SQL Server 2008R2 Ent and we are experiencing a high incident rate of Deadlocks . We started by...

Speed issue with SQL Server 2008 compared to SQL 2000 - We have built a new virtual server with Hyper-V and 64bit windows 2008R2 with 3 processors and 12 GB of...

Need help rebuilding a SQL server with replicated databases - We have a production SQL server in a virtualized environment and we need to move it to a physical server...

Peer-Peer replication - I have just created peer to peer replication on couple of test server. Let's say TestServer1 and TestServer2. And test database called...

Installing SQL Server Express 2008 & Studio on Windows 8 - Hello all. My first post here. My question probably will seem amateur as I'm sure I is. I've been hired...

SQL Server outof space question. - Hello Experts, One of our clients server ran into this issue. The server has both application and SQL Server installed on...

Replication options - merge or peer-to-peer - I have read a lot of articles on this but still have trouble seeing the differences in these and when...

Running a sql server 2008 job to back up to network folder - I'm trying to schedule a back up database task to backup some specific databases to a network folder but kept...

Reporting Services configuration manager 2008 URL Issues - HI I got two XP sp3 machine having IIS 5.1 I install SQL 2008 on it and Configure my Report server...

SQL Server 2008 : T-SQL (SS2K8)

Partition Results - There is a heap like the one shown below: [code="sql"]USE tempdb; CREATE TABLE Heap ( [Column 0] NVARCHAR(100) ); INSERT Heap ([Column 0]) VALUES ( '01...

GROUP BY question - Morning all, Could someone help me generate the following grouping please? Sample data plus expected output below: Input Table: [b]SiteMatches[/b] Expected Output: [b]SiteMatches_Output[/b] [img]http://i.imgur.com/7FGfQPu.jpg[/img] [code="sql"]CREATE...

How to Insert/Upadte data based on exist in mathcin table, in single query - Hello All, please help me with this single select statement, [u]StudentNew[/u] table is exist with below values, (sno and sname is composite...

Isolation Level - Hi, I have a scenario in which I am performing insertion on a table using a stored procedure. For every...

Indexed view CI chosen of table CI. Why? - Hi, This is related to the table and query in question: [url]http://www.sqlservercentral.com/Forums/Topic1449684-392-1.aspx[/url] (Hope this is clear, otherwise I have to double post...

Understanding CASE processing - Hi, I found an SP that generates multiple index seeks from 1 case statement. By the looks of it each WHEN THEN...

conversion of the varchar value overflowed an int column - I have a stored procedure : In this prcedure I am returning @pSTN value and used in another sp as output....

Possible to use tored proc to kill all queries running longer than 2 hours AND are blocking other queries - Is it possible to use a stored procedure to kill all queries running longer than 2 hours AND are blocking?...

Unpivot Multiple Columns - This is my first shot at an trying to unpivot multiple columns, and I'm not getting any results. I have a...

Inner join returning all rows. - I figured it out....Thank you, I would like to return all the data for a [b]single[/b] empnum, but this query is...

SQL Server 2008 : SQL Server Newbies

SQL server as a back end for Access - Looking for good and/or bad reasons to use SQL server as a back end component to access 2007 and use...

SQL Startup options - 3609 - Hi All I have been told that if the drive that contains my tempdb has failed, I need to start SQL...

Rounding Up with given precision - I would like to round up some figures with given precision. So far I can't find a way to do...

verify the correct permission given - Today I tried to deploy a report in visual studio 2008. 1.its giving me a error that it can't be deployed...

Random Number Generator - I am trying to create a random number generator that would populate a number between 8-48 to 9 different lanes...

Updated muliple rows into one column? Why is it hard? HELP - Trying to update a table using integer number in 3 columns and a signing texts name to results in the...

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

check if database exist before restore - hi, i'm in the process of automating the database restore using powershell.... but before getting the default paths of data and log...

Ownership of cluster disk 'Cluster Disk xxx has been unexpectedly lost by this node. - My Cluster went down again. I don't have to say... I am having a not so good morning already ... :-( Here's...

SQL Server 2008 : SQL Server 2008 Administration

Row Versioning and SharePoint Foundation 2010 - Is it possible to enable row versioning for your SharePoint Foundation 2010 databases? We are running SQL Server 2008 R2...

How to track SP - Hi All, as a newbie database admin I am facing challenge. I need to track an SP. the SP name is...

The database principal owns objects in the database and cannot be dropped - Hi guys, Recently we got this error: The database principal owns objects in the database and cannot be dropped. The database version...

Why powershell? - Hi all experts, I have being reading about Powershell a lot this days. What i came to know about powershell is...

Regard to SQL Version, - I have to SQL Server and one has version, Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012...

Uninstalling an Instance of SQL failed - Good day, I have two instances of SQL 2008 installed on a single server, and I need to uninstall the one...

Career : Certification

70-461 - Hi, Id like to take this exam and looking for some good prep.. My T-SQL skills are basic at present...

Programming : Powershell

replace string in path using powershell - Hi, i'm trying to get the default sql server data and log file path using the below script... [quote]param ( $ClientName, $DESTINATIONDB ) $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlCmd = New-Object...

SQLServerCentral.com : Anything that is NOT about SQL!

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

SQLServerCentral.com : SQLServerCentral.com Website Issues

taptalk(iphone) forum app - it would be great to see the forum on taptalk so that it can be available on the iphone. see...

Reporting Services : Reporting Services

Data Driven subscription issue - Hello, Can you please help me to solve following error for DD Subscription: While running the subscription it shows the status on...

colour expression based on parameter selection in ssrs 2005 - Hi all, I have a report in it i need to produce colour based on comparison of comp and allcomp when...

Output of SSRS in XML format - Hi, I got my sp TO WORK.. FINANLLY... Now I wanted to schedule the report to run and email in XML format.... I...

SSRS Report breaking with an error "System.UriFormatException: Invalid URI: The Uri string is too long." - Need quick help on this issue. Environment Details: Reporting server migrated from 2005 to 2012. But we haven't migrated rdls and these...

Matrix Total - Hello, I'd appreciate if you guys help me with my problem in a matrix. In the following SSRS 2008 matrix, notice...

SSRS filters using "IN" - I have a table that I need to sort by multiple values and am not having much luck. Is is...

Data Warehousing : Integration Services

Whats the easiest way to read the first 10 characters of a flat file - Basically I am recieveing multiple flat files of two different file layouts, the file names do not give a clue...

What happens if new records are inserted in a source table during a package execution? - Hi Folks, I have a package that loads records in a CRM destination using a Web Service. This execution takes around...

How to avoid technically the null values while concatenating columns - HI, In SSIS by using dervied column i manipulate fields and end up in my desired results. But for an record if...

Data Warehousing : Strategies and Ideas

Designing a Dim table for SSAS and SSRS - New to SSAS. I'm working on my first cube. I'm creating a dim table for the accounts. The level of...