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 Optimize SQL Server performance
“With SQL Monitor, we can be proactive in our optimization process, instead of waiting until a customer reports a problem,” John Trumbul, Sr. Software Engineer. Optimize your servers with a free trial.
 
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

SSIS Deployments

Four ways to deploy SSIS packages are shown in this piece. More »


Using Policy Based Management for checking SQL Server database file extensions

We need to exclude SQL Server database files from Antivirus and Third Party Backup Software (to make sure that files are not accessed directly). How can we be sure that all of our SQL Server file extensions are using the standard file extensions for database files? More »


From the SQLServerCentral Blogs - A Rickety Stairway to SQL Server Data Mining, Part 10.3: DMX Prediction Queries

by Steve Bolton                In the last two installments of this series of amateur self-tutorials on SQL Server Data Mining (SSDM),... More »


Editorial - The IT Employee Benchmark

Today's editorial was originally released on July 22, 2008. It is being re-published as Steve is at SQL Intersection.

How often have you met someone in the Information Technology field that seems to naturally understand how computers work? Someone that seems to be able to solve problems with almost any system or technology, even those outside of their area of expertise? They just seem to have an "IT instinct" for how systems and computers work together.

The value of an IT employee is probably impossible to predict. We have all sorts of certifications, tests, and more that we use along with an interview to make a decision about who to hire. In many cases is comes down to the "instinct" of the interviewer. Even then I'm sure that we find that the skills of those we interview often don't match up later with our expectations. They might greatly exceed or fall short of the performance we expect.

So how do we find good employees? I caught this article from eWeek that talks about the skills that CIOs want in their technical staff. It's an interesting read and the comments from CIOs are what I've really expected for most of my career. They want passionate people, strong technical skills, almost innate skills to work with computers, and the ability to translate that technical knowledge into business value.

Their views are certifications are what I've thought as well, and the article seems to capture it better than I've done in the past: "While education, training and certifications definitely add credibility to a candidate's claims, there are a lot of other aspects that should be considered while hiring." Others feel differently with one director saying certifications, degrees, all count for nothing when he looks at a prospective employee. I'm not sure I agree with that, but I am glad that a set of letters after your name do not count for everything.

Finding someone with good instincts is hard, and it's something I think occurs almost with luck. It's why every IT department should have one or two openings permanently available in case someone that's really valuable comes along. Identifying that person can be hard, and to a large extent a hiring manager needs to trust the recommendations of his staff or friends at other companies, but when that great talent becomes available, you want to be able to secure it.

There's never a guarantee that someone will perform at the level you hired them at, but I think that finding good people and listening to those good employees instincts is good way to start.

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


The Voice of the DBA Podcasts

Everyday Jones

The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

Overall RSS Feed: or now on iTunes!

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

» To submit an article, rant or editorial, log in to the Contribution Center


Question of the Day

Today's Question:

What will happen after running this code?

CREATE TABLE dbo.Test
(
 Col_1 INT IDENTITY(1,1) PRIMARY KEY
,Col_2 INT REFERENCES Test(Col_1)
,Col_3 INT IDENTITY
)
;

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

This question is worth 1 point in this category: IDENTITY Property. 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 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.


Yesterday's Question of the Day

If we run the code below:



--statement 1
CREATE TABLE temp 
(
ID INT NOT NULL,
MyText TEXT NOT NULL
);
GO
--statement 2
CREATE TRIGGER dbo.CheckTextSize
ON dbo.temp
AFTER INSERT
AS
IF (SELECT COUNT(*) FROM Inserted WHERE DATALENGTH (MyText) > 1000000) = 1
 BEGIN
  RAISERROR ('Too large Text Size being inserted', 16, 1);
  ROLLBACK TRAN;
 END
GO

--statement 3 (text size being inserted into text column is < 1000000 characters in length)
INSERT INTO temp
 VALUES (1, 'valid text size')
GO

--statement 4 (Assume text size being inserted into text column is > 1000000 characters in length)
INSERT INTO temp
 VALUES (2, 'invalid text size .......(text size > 1000000 characters)')
GO

--statement 5 (Assume text size being inserted into text column is > 1000000 characters in length)
INSERT INTO temp
 SELECT 3, 'invalid text size .......(text size > 1000000 characters)'
 UNION ALL
 SELECT 4, 'invalid text size .......(text size > 1000000 characters)'
GO

--statement 6
SELECT * FROM dbo.temp
GO

DROP TRIGGER dbo.CheckTextSize;
GO
DROP TABLE temp;
GO

How many rows are returned by the select statement (statement 6) ?

Answer: 4

Explanation: Cannot reference text, ntext, or image columns from the 'inserted' and 'deleted' tables in the create statement of triggers. So the trigger is not created and all the rows are succesfully inserted.

Ref: http://msdn.microsoft.com/en-us/library/ms191300.aspx
http://msdn.microsoft.com/en-us/library/ms190267(v=sql.105).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

Run SQL code on each database

You can use this to run a script/s on multiple 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

Index Rebuild Taking too Long? - I have an instance running the following version: Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c)...

Query Multiple Databases - 04/04/2013 I am a newbe database admin and I have been tasked to run queries against multiple databases. Each server has...

Who created the job? - I want to setup a standard where all the jobs are running under a dedicated service account. Mentioned below are...

Database in Recovery Pending State - One of my databases is in [b]Recovery pending[/b] state. I tried to run an Alter command on the database to...

SQL Server 2005 : Backups

RedGate Backup - I installed Redgate Backup5 on Windows 2003 + SQL Server 2005 with SP4.But I am not able to see sql backup...

SQL Server 2005 : Business Intelligence

Loading Fact Tables - Step by Step Instructions Challenge - Dear All, This is my first Data warehouse project and I am having serious problems loading my fact table. I...

SQL Server 2005 : Data Corruption

Database Error - MySQL - 1030 - Can somebody tell me what this error code means, 1030? How do you handle/deal with this?

SQL Server 2005 : Development

split single row into four rows. - Hi All, I am having a table which contains 4 field as below. stuid,studname,startdate,starttime,endtime. now if starttime is 7am and endtime is 8am...

SQL Server 2005 : SQL Server 2005 General Discussion

How to add a running total? - I need a running total on a table with two fields (Year and Count of Orders in that year). E.g. (using...

account lockout error in SQL job step - in our production server one of the job is failing at step-3 due to account has been locked. the job will...

Convertsionsproblem between timestamp and bigint - Hello, I need to store the value of a timestampvalue into a different Database to determine changes at the source. To...

Reporting Services and Subscriptions - Stop Sending Empty Report - We have a report that runs on a nightly basis that gets emailed out via subscription when the report is...

sql 2005, mdf restoration, w/out ldf, new box - Here is the situation: > C drive containing the SQL server 2005 is gone > D drive for data is available > E...

SQL Server 2005 : SQL Server 2005 Strategies

Moving prod DBs from local disk to SAN with absolute minimal downtime - [b][u]Background[/u][/b] I have a number of production databases that are currently on the local disk on the server. We have recently...

SQL Server 2005 : SS2K5 Replication

Transaction Log Growing due to Pending Transaction (Replication) - SQL Server 2005 SP4 with Transactional replication. I have this Database Log, which keeps growing due to a Pending transaction in...

SQL Server 2005 : SQL Server 2005 Integration Services

SSIS question - how do i modify a package which has been deployed to the server? This package is being used for dba...

SQL Server 2005 : T-SQL (SS2K5)

Compare two data sets-- Please help - Hi, I have a scenario while comparing the data between two data sets. [code="sql"]CREATE TABLE DATA (ID BIGINT, CODE VARCHAR(100), CNAME VARCHAR(100)) TRUNCATE TABLE DATA INSERT INTO...

I need the code part for splitting the row into different columns? - 1 : 7 ; 9 : 21:Task Completed:2013-04-08, 1 : 8 ; 10 : 22:Good:2013-04-08, 1 : 9 ; 8 : 19:Not Bad:2013-04-08, 1 : 10 ; 7 : 18:Bad:2013-04-08, 1 : 11...

What is an index.is is object ??how visualize... - Hi we can visualize a table as m*n matrix where m and n indicates number of rows and columns . But...

What is an index.is is object ??how visualize it?? - What is an index.is is object ??how visualize...

Need help - Hello, I need syntax to get the all the values of one employee from the employee table, in a single...

SQL Server 2005 : SQL Server Newbies

Undeleatable record (on linked table) - Hi, I've got a system made up of a SQL 2005 backend and an Access front-end. The front-end only holds the...

SQL Server 7,2000 : Strategies

Data conversion data validating using information_schema.columns - I am looking for an efficient to validate data for a data conversion. The path I am taking is importing...

SQL Server 7,2000 : Performance Tuning

Need help optimizing a query - Just fair warning, I am definitely a newbie, I fell into this work so to speak. I have a query that...

SQL Server 2008 : SQL Server 2008 - General

Multiple Data and Log Files - Hi Guys, I wanted to know if it's possible to go back to a single data and log files from multiple...

Optimize for adhoc workloads - Can anyone please tell me where I can find the optimize for adhoc workloads settings in a table / view because...

Help with tuning stored proc - Hi all I have a proc here where the estimated amount of rows is around 2 trillion where the actual numbers...

Query Timeout help - Hi, Could anyone tell me why the following query is timing out please? [code="other"]SELECT PLAN_HANDLE FROM SYS.DM_EXEC_PROCEDURE_STATS PS WHERE PS.PLAN_HANDLE = 0x05000A00ED4FE245402231CC000000000000000000000000[/code] If there...

Write query for Suond like search - hello everyone I am working on database where I would require to make search which base on sound like search: e.g. In my...

Trace flag 8295? - Hi, Does anyone have where trace flag 8295 is used for. I found it active on a customer server, SQL Server...

Parallel Data Warehouse for OLTP - Hi, Is Parallel Data Warehouse feature of SQL Server 2008 R2 targeted only for OLAP/BI impementations? Can we use it for hosting...

Sum 'LastPeriod' short version - Maybe my last post was too long ... Does anyone know if the results of a 'LastPeriod' MDX query can be...

Can I SUM the 'LastPeriod' query results? - Hi, I want to create 'Rolling' Month on Month, Quarter on Quarter queries with Revenue data. So, for example if I want...

Add date range by record line - I'm trying to add a date range to this query, where the min date is the last 10 days, the...

CASE statement - [code="sql"] WHERE SS.[Key]='A' AND SO.[type]='X_P' AND vu.[Status]=1 AND 1= CASE WHEN @CreateDateDatTime > vu.LastRunDate THEN 1 ELSE ---Secondary filter ( L.create_date>=ISNULL(vu.LastRunDate,'01/01/1900') OR...

Are Queries Actually Using the Index - Hello Everyone I have a SQL database that is the backend to a Java application. There are indexes on the column...

Active Directory Connection - At work we have an application that has the ability to use 3 active directory groups to setup security within...

Transaction Log Growth - How to find the transaction/process/Job which cause growth of transaction log size? Thanks

When to update statistics manually, if at all? - So, I'm using my google foo to try to find some articles about why we should not be updating statistics...

SSIS script task - I need to set the unicode property of a connection manager (which is a variable) to true in a Script...

Tracking a change to the notifications on an Agent job - Hi everyone, So I've had someone change the notifications, specifically the Page: nofitication on all agent jobs on our production server....

Using AOG spanning FCI. - I have read that all disk sizes and drive letters need to be the same when using AOG. Is this just...

Publishing and accessing SQL Server 2008 Reports - I 'm new to SQL Server database and I need help. I 'm trying to publish sql reports for users...

can't connect to sql 2000 from ssms 2008 - i installed ssms 2008 on a windows 7 pc, connection to sql 2008 instance is fine, but can't connect to...

SQL Server 2008 : T-SQL (SS2K8)

how to convert the below subquery into join..single update statment with joins - here is the complete query..the ulitmate aim is the update the claim table... but it should be only one statement without...

Dead Lock Question - Hi Guys, I have two questions. 1) My first question, how i can find out on which table of my...

SQL /Procedure - for Transpose and Calculation - Hi Team, I have a Complex requirement. Source: -------- NAME PAYMENT_TYPE PAYMENT SUDHIR SAL 30.3 SUDHIR ADV 10.3 SUDHIR ALL_1 10 SUDHIR ALL_2 10 SUDHIR ALL_3 10 MADHAV SAL...

Need help with Dates - Dear friends, I'm new to TSQL and need help please - I have to Calculate a field value as Position Duration = (Work...

Rounding up - Hello Everyone! I have this issue where i use a store procedure to create a report: ALTER Procedure [dbo].[A_RepChequesxCheque] @FechaInicio T_DATETIME, @FechaTermino T_DATETIME AS Begin select ant.CheckId,...

T-SQL Variable vs String Equivalent - Got a question with some coding standards. There are developers declaring a variable to a constant value and using that...

Inserting missing records using multiple fields as the key - Hi, I have a complex problem that I'm pretty sure should have a very simple solution. If I could just find...

Confused selecting Info From Two DB - i have two database AgencyDB(primary DB) and AgencyBackupDB(Deleted Info from AgencyDB by user) and i have this three table in...

Query performance help needed - I have multiple pairs of "Offers" and each Offer is comprised of several lists of "Items". My pairs of Offers are...

Passing Multiple Values As Parameters to get Multiple Columns with Comma Seperation(MSSQL) - Create procedure temp ( @MID smallint ) as Begin select TranID, [MonthValue]=(CASE WHEN @MID=1 THEN Jan WHEN @MID=2 THEN Feb WHEN @MID=3 THEN Mar WHEN @MID=4 THEN...

Function for similar phrases - Hi All, Is there any built in function available for checking the similarity of the phrases eg: US Eastern District Court...

Broadcast calender generator - Hi guys, Did anyone ever have to create a function/stored procedure that would take a date as an input and generate...

How to call a batch file to execute from an SP - Hi All, Need your assistance please, I am not very good with scripting. I have created a draft of SP, and I...

pull out all values within an XML column - i'm wondering if something like this is possible in some way: [code="sql"] select OriginalSchema.value('Form[1]/Codes[1]/code[' + convert(varchar(10),y.myNum) + ']','varchar(max)'), --OriginalSchema.value('Form[1]/Codes[1]/code[sql:variable("y.myNum")]','varchar(max)'), * from ( select id, ROW_NUMBER() over(partition by id...

Checking to see if a time is between two times - Hi all, I'm trying to break down events by hour of day as 00-23. So if an event has a duration of...

SQL Server 2008 : SQL Server Newbies

Monitoring Lazy Writer - Hi All I have set up a test scenario to monitor the effects of the Lazy Writer by limiting my SQL...

timeline in cross-tab?? - Hello experts, My problem is as follows. On one hand I have got the beginning and end date of a tournement,...

synonyms for table column - Dear All The way we hide the tbale name from user by using the synonyms. Is there a way to hide...

Temp Table Column name or number of supplied values does not match table definition - Hi all, Starting to play around with PBM and found an example of using DBCC LOGINFO. When i copy the...

Want to categorize the salaries of employee. - Hi Team, I've Table People Where there are n no of employees involved. The salaries of the employee are present in Column...

auto foreign key generation - I created a table using the statement below in one of my databases (database1). The table is to be updated...

Slows system performance - Dear All I am running a process (updation/deletion/insert) of 2 million records. After some time this process makes my system very...

how to find owner(SPID) of the temp tables - Dear All In temp db i find many temp tables created. I would like to know which SPID has created these....

SQL Server 2008 : SQL Server 2008 High Availability

MSDTC in SQL 2008 cluster - HI, Do I need to configure MSDTC if I only have 2-nodes active-passive setup? Kindly advice Thanks

Enable back Database Mirroring - Hi Everyone, I Have disabled database mirroring on principal server using below command for restoring database over a mirrored database because...

Moving Mirror mdf and ldf files to another drive - I'm currently running SQL Server 2008 R2 Enterprise with 3 servers in an active / passive / witness high availability mirror. As a...

Seeking some guidance on the use of Log Shipping in SQL Server 2008 - I have recently taken on responsability for the database part of our Sharepoint solution. From looking at the documentation there...

Manual failover of cluster-MSDTC - I tried to manually failover the sql service to another node. I was successfull in doing so, but MSDTC remains...

SQL Server 2008 : SQL Server 2008 Administration

how to enable back database mirroring - Hi Everyone, I Have disabled database mirroring on principal server using below command for restoring database over a mirrored database because...

Max server memory for second instance - We have a SQL instance with max memory set to 20 GB out of total 24 GB. Now I would like...

Moving data file - For a db involved in db mirroring, what steps do I take to move its data and log file to...

SQL Server 2008 Link Server Connection Error - Sir, I am working in Medical college campus.They have 5 server in our campus. I have done a Port for SQL 1433...

SQL Server silent installation - problem with cleartext passwords - Hi, We are planning to install SQL 2008 on over a large number of servers using the silent installation technique....

Moving the SQL Server error logs, SQL Agent logs, and Full-Text logs - Ok, during the installation of SQL Server 2008 R2 (Enterprise x64) I changed the "Data root directory" thinking it would...

Deadlock Graph from the xml from system_health extended event - How to get the Deadlock Graph from the xml extracted from system_health extended event. I'm using SQL Server 2008R2 SP2, and...

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

SQLServerCentral.com : Anything that is NOT about SQL!

Encrypting files with PGP - While this is not related to sql server I imagine that many database administrators have had to deal with this...

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

Reporting Services : Reporting Services

Jasper Smith's SSRS Scripter - Anyone happen to have this tool handy? I used in the past to successfully migrate a 2005 SSRS instance to...

SSRS 2008 R2 - SEGMENT table - Hello All, We are using SQL server 2008 R2 SSRS. The segment table in the SSRS database (Not SSRS Tempdb database)...

SSRS 2005: Exporting to an Excel spreadsheet doesn't do anything - This user has another problem with a .RDLC file that's in an Intranet application we wrote. He can bring it...

Data Regions within table/matrix cells are ignored - Hi, I have Table and in the Table footer I have another Table. I get message for inner Table that "Data Regions...

Database Design : Disaster Recovery

Enable back DB Mirroring - Hi Everyone, I Have disabled database mirroring on principal server using below command for restoring database over a mirrored database because...

Data Warehousing : Integration Services

Extract data from CSV file - SSIS - Hi all, I have a csv file with data from 2005...and on daily basis the data is getting appended in the...