In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Source Control Database source control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Get started now.
 
SQL Monitor Custom metrics from SQL Server MVPs
SQL Monitor is the only tool with a free library of custom metrics from SQL Server MVPs. Find new metrics for your servers.
 
SQL Prompt Make working with SQL a breeze
SQL Prompt 5 is the effortless way to write, edit, and explore SQL. It's packed with features such as code completion, script summaries, and SQL reformatting, that make working with SQL a breeze. Try it now.

In This Issue

Power View for Cubes

This document evaluates the latest functionality provided by Microsoft SQL Server 2012 With Power View for Multidimensional Models[1], this SQL Server 2012 CTP release allows connections between Power View and Multidimensional Models ( cubes) and not just Tabular Models. More »


Centralizing SQL Server Express Job Scheduling

I'm interested in monitoring all my SQL Server backups, but for SQL Server Express we're currently using scheduled backups using Windows Task scheduler on each SQL Server Express since SQL Agent does not come installed on Express Editions. Is there an alternative where I can utilize my existing SQL Server infrastructure? More »


From the SQLServerCentral Blogs - CREATE SCHEMA can do what?!?!?

So I was studying for the 70-451 and ended up reading the BOL entry for CREATE SCHEMA. I honestly can’t... More »


Editorial - How do you solve T-SQL problems?

Six scans, six logical reads. What could be the problem? There's no problem if you're always dealing with six rows of data. If you're dealing with 6mm, however, this is likely to be a performance problem on your server. Many developers might write a query, check the statistics, and see something like that shown above, and think that it's a good day's work.

Solving the problem is only half the job you should do. Solving things in an efficient way is the other half. When you tackle a new T-SQL challenge, what's your methodology? Do you have one? I don't need to know, though if you think you have a good one, post a note in the discussion, or perhaps write an article. If you aren't sure how you begin, other than randomly trying ideas you vaguely understand or have seen others try, perhaps you want to start by reading Kathi Kellenberger's Step by Step method.

The image above comes from Kathi's article, and is indeed the result of her first solution to a complex T-SQL problem. She lists out the basic steps she used to derive the solution, along with the business rules used. All too often I've found developers (including myself), start writing code without taking a few minutes to specifically state the business rules. Following that one step might eliminate lots of bugs in coding.

Her solution worked, but with 6 scans occurring on her 11 rows of data, she knew this would be an issue. Moving forward she asked for help, which is perfectly acceptable way of working in your career. Try to solve it yourself, get a solution working, and then ask if it can be improved. Not only did she end up with a better performing solution, but she learned a bit more along the way. Something we all can do.

» 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 will the following query return?

CREATE TABLE #Temp
(FirstName varchar(20),
 LastName varchar(20)
)

INSERT INTO #Temp
SELECT 'John', 'Smith'
union all
SELECT 'Joseph', 'Robertson'
union all
SELECT 'Robert', 'Jefferson'

SELECT substring(LastName, CHARINDEX('s', LastName)-1, 10) Name  from #Temp

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

This question is worth 1 point in this category: charindex. 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 Transaction Log Management

When a SQL Server database is operating smoothly and performing well, there is no need to be particularly aware of the transaction log, beyond ensuring that every database has an appropriate backup regime and restore plan in place. When things go wrong, however, a DBA's reputation depends on a deeper understanding of the transaction log, both what it does, and how it works. An effective response to a crisis requires rapid decisions based on understanding its role in ensuring data integrity.

Get your copy from Amazon today.


Yesterday's Question of the Day

I create the following two (2) tables , and insert the data as shown.

CREATE TABLE t1(x INT,A INT IDENTITY(1,1)); 

INSERT INTO t1 VALUES (10),(20),(4),(20),(10);

CREATE TABLE t2(y INT,B INT IDENTITY(15,15) PRIMARY KEY);

INSERT INTO t2 VALUES (1),(20),(3);

I then execute the following T-SQL statenent

SELECT x AS 'Answer' FROM t1 EXCEPT SELECT y FROM t2

The question is: How many rows are returned by the SELECT statment ?

Answer: 2 rows are returned by the SELECT statment

Explanation: EXCEPT returns any distinct values from the left query that are not also found on the right query. The key to this is of course the use of DISTINCT.

Ref: http://msdn.microsoft.com/en-us/library/ms188055(v=sql.110).aspx

» Discuss this question and answer on the forums

SQL Queries 2012 Joes 2 Pros® Volume 4: Query Programming Objects for SQL Server 2012

The SQL developer needs to be able to create processes for the working database by using one of the many programming objects (like functions, stored procedures, constrains, or triggers). By creating objects that talk with SQL you simply the way other programs (like applications or web pages) can interconnect. These eternal programs only need to call on the names of your programming objects by name rather than needing to submit large pieces of advance code. With SQL Queries 2012 Joes 2 Pros® Volume 4, you learn how programming objects work in SQL Server. For those of you who have read the 2008 series for the 70-433 Exam you will find a lot of the same material from the SQL 2008 book in this SQL 2012 book. This is because much of the 70-461 test covers the same material as the 70-433. I have added material that is new to the test and removed material that is no longer relevant. If you have already read this series or have already passed the 70-433 exam you may choose to read my book which covers only the changes from 70-433 to 70-461 entitled "Joes 2 Pros SQL 2012 Queries 70-461 Exam for SQL 2008 Pros".

Get your copy from Amazon today.


Featured Script

Search All SQL Script Objects in All Databases for Text

Edit the @SearchText and you can enter any text you would like to search for in SQL Objects. 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

sysJobActivity showing jobs from years ago that never ended - I've noticed that sysJobActivity shows a number of jobs where the stop_execution_date is NULL, but the run_requested_date is up to...

SSIS 2005 for French accents - I am running into issues with loading a UTF-8 flat file containing French characters using SSIS 2205 to a SQL...

Add User Mapping not working - I'm trying to add a user mapping to an already existing login. In the Login Properties for the login, I...

The database is not accessible (ObjectExplorer) - The users within a particular domain group are having trouble accessing all the appropriate databases via their SQL Server Management...

SQL Server 2005 : Backups

Excessive log growth in SIMPLE recovery model database - SQL 2012 - Hi, Apologies if this has been posted before, but I keeping coming across excessive log growth on my reporting databases that...

SQL Server 2005 : CLR Integration and Programming.

Using an ASP - I am a total newbie and have a question to ask. I am the only DBA on a team managing...

SQL Server 2005 : SQL Server 2005 General Discussion

Database data files - Hi All. I have a database with a single file which size is 288GB. I'm dropping the indexes and the data...

enabling READ_COMMITTED_SNAPSHOT - Hi, I'm currently installing service pack 1 to webCT 6 Campus Edition (Virtual Learning Environment) which requires READ_COMMITTED_SNAPSHOT to be turned...

SQL Server 2005 : SQL Server 2005 Integration Services

Not Possible to Import Data from Excel sheet - I was importing a table data from excel sheet using SSIE wizard. After mapping the table its giving error Operation stopped... -...

no matter how large the destination column eg. nvarchar(max), excel source choke on column - I'm using VS2010 BIDS, importing from Excel 97-2003 .xls worksheet. I've got the following config: Excel Source -> Conversion Split Transform...

Call SSRS report from ssis - I need to call a ssrs report from SSIS using a script task that has a File_ID as a parameter...

SQL Server 2005 : T-SQL (SS2K5)

How to count active jobs at the end of each month across a period of time - My apologies if this question has been answered elsewhere but I can’t find a solution. I need to count the...

OPENROWSET() Intermittently Fails to Read Excel File - I've had this problem a number of times: every so often, I receive errors like this when trying to query...

SQL Server 7,2000 : Administration

view the data from the sysmail_sentitems and sysmail_unsentitems and sysmail_faileditems without giving sysadmin role MSDB databasse - sysmail_sentitems sysmail_unsentitems sysmail_faileditems these are the views in msdb and without giving sysadmin role MSDB databasse, how to access the data???? Plzz help...

SQL Server 7,2000 : General

Newbie Question - Sorry for this newbie question, but what is the best way to push out data from a development server to...

SQL Server 7,2000 : Performance Tuning

Shrink large database - I have been asked to look a a sql 2000 database that has been neglected for some time. It has an...

SQL Server 2008 : SQL Server 2008 - General

How to call JAVA class file in sql trigger - Hi Team, I want a java batch file to be executed in a trigger, is it possible to call JAVA class...

SQL Server Security Issue - Hello all, There are accounts that were created well before my time in which the users (developers) know the passwords. These...

International Characters (Korean) displayed as Junk data after migration to PB12.5. - Dear Gurus, We have facing following issue as details are mentioned below. Your kind help and support is needed. [b]Details:[/b] [i]We have...

performance in sql server - Hi every one, I have 3 table as below: Table Attachment IDAttachment, Name 1 | A 2 | B Table ActionPerformedAttachment IDActionPerformedAttachment IDActionPerformed IDAttachment 1 | A | 1 2 | A | 1 3 | A | 1 4 | B | ...

open tranaction - can any one tell me what is an open transaction in detail.

Proper way to write a sproc - Hi I am an experienced sql developer and have written many stored procedures but i am worried that i am still...

Attaching a database in SSMS without sysadmin role gives error - I am accessing my sql server 2008 r2 express server using SSMS. I have a user which has got db_owner...

How to select this - Below are sample tables and sample date, please help --create table c (c_id integer, p_id varchar(10)) --create table f (c_id integer,f_id integer,e_dt...

How to select max based on condition. - Hi Friends, I want to select maximum date data based on some condition i.e. if alloc_dt column has value for c_id, then...

snapshot agent is not running - Hi, Friends I am working on sql server 2008 r2 and I want to replicate data by using snapshot replication, when...

Help with next date in SQL query - What I need is to be able to find out when a customer next called in from the date I...

Format the Amount - Dear, My client requires to add a hyphen(-) in lieu of comma(,) in the field of amount. But client requires only...

Update values for dynamic columns - I've been looking at dynamic SQL update statements and have been trying to tailor one specific to what I need. I...

Interview Questions i am not able to answer - 1. what type of pages are used in bulk log recovery model. 2. able to ping the server. but getting access...

Find date gaps - Hello everyone, I need to find a way to fill gaps in case a week is missing, example each record has...

Check values whic are not Decimals - HI, I have a column which is defined as Decimal(23,10). I want to get the values from the column which are not...

Parent child relationship in separate tables - Hello everyone, We are having data with parent child relationship. But they are spread across two tables due to our Business...

Hour not showing correctly - --Question 4b select History_Type_, TimeClicked, left(right(TimeClicked,11),2) as HourTimeClicked, count(left(right(TimeClicked,11),2)) as CountOfHourTimeClicked from clicktracking_ --with (nolock) inner join members_ on clicktracking_.MemberID_ = Members_.MemberID_ group by History_Type_, timeClicked, left(right(TimeClicked,11),2) OPN 2011-09-24 18:38:00 01 69 OPN180 2013-04-

space issue - We donot have space in database ,and the disk is fulled because of mdf's . how to release space on the...

SQL Service Broker suddenly stops working - Hi, I'm new in using service broker. Our existing service broker suddenly stops working and I can't figure out why. When I...

Antivirus on a SQL Server VM - hi guys, my vm admin wants to run AV at the host and not at the guest level. what do...

Import Excel File to SQL Server - I am having trouble while importing excel file to SQL Server. Excel File: ColumnA -------- b2 2 a2a 2aa When I import...it comes as ColumnA -------- b2 a2a 2aa Just number "2" doesn't comes. I...

Running DTS packages on 2008 R2 - Hope someone can help me on this one We have a 2008 R2 cluster which failed over recently, a number of...

Security Problems after SSRS Domain Migration - Morning all, I recently carried out an SSRS Migration from one domain to another. I used the backup and restore method, and...

Changing a user defined data type definition - I recently had cause to change the definition of a user defined data type and was I suppose intrigued by...

SSIS Derived Column Transformation Date Time Expression - I'm using Derived Column Transformation and I'd like to create column with the current date/time with .000 milliseconds. Can anyone...

Could not allocate space for object because the 'PRIMARY' filegroup is full - I have a SQL Server 2008 running on Windows 2008. The main database is located on the D: drive and...

Database suspended-Need help - Hello, Why database getting suspended stage? How can we solve this? What are all the problem we will get this error? could you please...

Questions about SSRS in 2008 - Hi guys, Here are my questions about SSRS 2008? 1-Is it possbile to add Notification email to the reports that has been...

SQL Server 2008 : T-SQL (SS2K8)

SQL round,FLOOR - Hi, I need -13599.99 as 13600 162157.36 as 162157 7415781.64 AS 7415782 How can I achive that ? thanks,

Finding the total count of ACTIVE Customers and their remaining TOTAL POINTS for each distinct groups of CATEGORY. - Hi I’ve a CustomerActivity table that records all the activities of the consumers. I've create a simplyfied version of the table with...

COUNT function during 0 case - I have two tables [b]1) Product with columns[/b] [code="sql"]ProductId Name 1 ABC 2 DEF [/code] [b]2) Sales[/b] [code="sql"]TransactionId ProductId ReqTime ResTime 1 1 05/20/2013 13:22 05/20/2013...

creating table for fiscal year - hi all i want to create table for fiscal year in sql server 2012. the fields are : 1-ID 2-StartDate 3-EndDate but i have some problems...

Display -0 - All, I want to display -0 when i run the below query select 0.0*-1

sqlCursor - Hi,all. I am beginner of learning sql cursor. Can someone help me to edit the following sql statement. I would...

Insert statment - Hi, I don't spend enough time SQL coding so I am wondering of someone can help me with my statement please,...

Database backup striping - Hello Pros, I manage 100 + SQL servers and wanted to find a way to backup all databases to multiple .bak files...

Intelisense is not working !!! Even After trying everything mention on msdn website - Hi all Experts, I am trying this since a long time. My intellisense is not working with a specified [b]Database[/b]. Intellisense...

SQL Server 2008 : Working with Oracle

Compare an Oracle Database with a Sql Database ! - Hi, first of all, i'm biginner in Bi Consulting with Microsoft tools. I want to compare two databases : Oracle Database and Sql...

SQL Server 2008 : SQL Server Newbies

SQL Version on Production Server - Out of curiosity - what it the version of SQL that is being used in the real world ?

linked tables - I have a SQL server express that I'm using to learn how SQL server works. Up till this point I've...

Backup and recovery for the accidental DBA - Hello - I am not a DBA, let's start with that...but I have acquired the task of backup up a db...

SQL Server 2008 : Security (SS2K8)

schemas - hi, I am trying to figure out the schemas for a new database, and im wondering about the best practice for...

Hiding Databases from users in SSMS - We have a group of users who need SSMS access to a group of databases on SQL Server 2008 R2....

Linked server error - Hello, I'm trying to setup a Linked server between SS 2005 and SS2012 but keep getting "Login failed for user 'NT...

Non-sysadmin needs ability to maintain SQL Agent Jobs - We have a team member that does not have sysadmin but we would like to give this person the ability...

sysadmin vs serveradmin - I am confused between sysadmin role and serveradmin role. According to books online sysadmin member can perform any activity in...

SQL Server 2008 : SQL Server 2008 High Availability

create a login on snapshot database on mirroring server - hi all, i would like to create a login on snapshot database on mirroring server. i have tried to create a...

One Way Transactional Replication: Multiple subscribers for single Publisher - Hi Friends, I have a scenario where there are two servers A and B. A act as a publisher and B...

SQL 2008 cluster does not failover to another node - Hi guys, I have this issue when I installed a SQL 2008 cluster instance on the Windows 2012 OS. I have...

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

SQL Server 2008 : SQL Server 2008 Administration

tempdb getting full - My tempdb is getting full within 2 hours , there are some executing SP that are using temporary tables, cursors and...

Server Configurations - SQL 2008 R2 - Hi All, I am in the process of putting together a proposal to upgrade our Production Server and wanted to ask...

Production release - Hi Friends, I have a question about Production release we are using SQL server 2005 enterprise edition physical production server, now I...

Moving System Databases - Is it safe to move the system databases (excl TEMPDB in this question) to another location from the default install...

connect to SQL 2005 Databse through application - we have .net application. we have to connect to Database through this application. i have tried below... 1) SQL Login with SysAdmin...

Moving from Standalone to clustered SQL - Hi - We have a standalone SQL box that we need to migrate all the DBs to a new clusted environment...

How can I kill ad-hoc or long time running queries, safely? - Ok, Need the final push on this ... I do have a requirement at work that I need to control or kill...

Backup failed - no SQL agent job - We recently found there are quite a few error logs in sql log: Message BackupVirtualDeviceFile::PrepareToFreeze: failure on backup device '{4FEEA406-DD73-47D8-BC10-0ADAD6A44D6D}1'. Operating system...

system state backup failed on SQL server 2008 box - Hi, Server os - win 2008 Enterprise edition Database - SQL 2008 Enterprise edition Event viewer Log Name: Application Source: Microsoft-Windows-Backup Date: 4/5/2012 11:16:14 AM Event ID: 517 Task Category:...

Replication - High memory usage when running Snapshot - If there are any replication gurus out there - I need your help!!! First the background ... a client of mine has transactional...

help out for some questions in the mirroring , replication - HI All I need to test the following things , then i need to implement. Questions: 1) can i do the mirroring aaaand replication...

dbmail problems on SQL 2008 /SBS 2008 64bit - Hi, I'm neither a DBA nor a Sys Admin person, More a coder but I've been asked to move a...

Career : Employers and Employees

Dilemma between Microsoft BI developer or Application Developer - I am stuck in a dilemma; at my workplace I have to choose my career track between as Microsoft BI...

What to do about a complete lack of jobs? - I've encountered the problem of not being able to find further employment as a SQL Server programmer/admin in my area,...

Where are the good Senior Level DBA's? - Hi all, Our company has been looking for a Senior level SQL Server DBA, and the last (2) candidates have been...

Programming : XML

XML View? - If i have at table with a xml column datatype and want to create sql view which displays certain values...

sp_xml_preparedocument does not recognize validated xml variable - Can anyone help I am trying to Shred a XML document into a data table in SQL Server.I am using...

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

Reporting Services : Reporting Services

SSRS Interview Questions - Hi Guys, Can anyone please respond to below interview questions, I need some understanding. I tried to google it but no-luck...

Reporting Services : Reporting Services 2005 Development

Report off of SSIS package writing to datareader dest. - Hi All, In SQL Server 2005, I'm struggling with defining a connection between Reporting Services and an Integration Services package that...

Database Design : Design Ideas and Questions

Complex many to many relationships - Hi all, Here is a relatively complex design that I would like to have vetted by experts! :) Say that you need...

Data Warehousing : Integration Services

File System Task - Copying files from Folders to multiple folder based on the first 4 digits of file name - Here is the 2nd task I want to do I have 10 folders from 10 services. Each of these services can...

Recording File Name etc into a SQL Table using a loop for each directory required - Hi, Im starting a new project today. What I have is an area with 10 folders where services drop their files...

Expose SSIS task script to application - Hi Is there a way where I can expose the vb.net/c# code used in Script task to external application? I mean...

How many packages can be created in single SSIS Project ? - Hi All, I'm new to SSIS world. I have some doubts as follows. 1. How many packages can be created in...

Dead Lock Issue -In update Scenario - HI , I have a Dataflowtask in which following following Transformations are used ---OLEDB Source[Pulls Data From X StagingTable] ---Balance data distributor is...