In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Developer Bundle 12 essential tools for database professionals
The SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.
 
SQL DBA Bundle Top 5 hard-earned Lessons of a DBA
New! Part 4, ‘Disturbing Development’ by Grant Fritchey, features the return of Joe Deebeeay and a server-threatening encounter with ORMs - read it here.
 
SQL Data Compare Save time when comparing and synchronizing database contents
"It has also streamlined our daily update process and cut back literally a good solid hour per day." George Pantela, GPAnalysis.com. Download a free trial of SQL Data Compare now.

In This Issue

Excel Function Returns Cell Address

This article details an Excel 2010 function to return the cell address of min and max functions. More »


High IO Wait on SQL Server Replication Distribution Database

Have you ever heard this question? The database refresh has gone from 10-15 minutes to 1.5 hours. Nothing has changed on the application server and the consultant said ask the DBAs to check the database server. Where do you start to find the problem? Check out this tip to learn more. More »


From the SQLServerCentral Blogs - Security Questions: Accessing SQL Server Securables

As I mentioned in the introductory post, during the Introduction to SQL Server Security session for Pragmatic Work’sTraining on the T’s, I... More »


From the SQLServerCentral Blogs - SQL Server security webinar series is coming soon!

I've gotten in contact with most of the speakers who submitted SQL Server security talks for the PASS Summit. All... More »


Editorial - The Joy of Technology

I ran across this piece on the joy of being a programmer, which resonated with me. I started at a programmer, at about the same age, 10 or so. I'm a little older than the author, but I've spent most of my life in the technology business, primarily working with SQL Server, but I've built software as well. I'm still building a little software, mostly for fun. I recently set up a Raspberry Pi to stream Airplay music from my idevices and created a ball shooting Lego Mindstorm robot. Granted the latter wasn't much programming, but I've backed a Kickstarter project that will allow me to write some more complex code for the Legos with my kids and I'm looking forward to receiving the kit.

Programming has been a fun hobby and career for me. Whether that's been working in a front end language like C++ or VB, writing T-SQL that produces reports, or even scripting administrative tasks in VB Script or Powershell, it's always a challenge. I find myself going through the same pattern of emotions over and over. Excitement as I start a project, confidence in an initial solution, frustration when things don't work, and a smile after the burst of creativity that finds a solution. Granted, I may iterate through these emotions a few times, perhaps even spending more time struggling than solving, but it's been an endeavor that I enjoy most of the time.

Just like the author, I've had success in my career, and found technology to be a better job than many other jobs that people I've worked with have had. Looking at the accountants, the managers, the salespeople, and all the other workers in the various organizations I've been a part of, I think I've had one of the better jobs that exist in the world today. Physically easy work, mentally challenging, well paid, and with demand for workers. I hope you feel the same way about your technology career.

» 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 be the output of below query when it is executed? (In the answer options, the rows are comma separated)

SELECT TOP(6) WITH TIES COL 
FROM 
(
SELECT 1 COL
UNION ALL
SELECT 2 
UNION ALL
SELECT 3 
UNION ALL
SELECT 4 
UNION ALL
SELECT 5 
UNION ALL
SELECT 4 
UNION ALL
SELECT 3 
UNION ALL
SELECT 4 
) A
 ORDER BY COL

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

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

Securing SQL Server

Written by Denny Cherry, a Microsoft MVP for the SQL Server product, a Microsoft Certified Master for SQL Server 2008, and one of the biggest names in SQL Server today, Securing SQL Server, Second Edition explores the potential attack vectors someone can use to break into your SQL Server database as well as how to protect your database from these attacks. In this book, you will learn how to properly secure your database from both internal and external threats using best practices and specific tricks the author uses in his role as an independent consultant while working on some of the largest and most complex SQL Server installations in the world. This edition includes new chapters on Analysis Services, Reporting Services, and Storage Area Network Security.

Get your copy from Amazon today.


Yesterday's Question of the Day

create table #temptable1
(name1 char(10),
 name2 char(10),
 zip int
)
GO

insert into #temptable1 
values
(NULL,NULL,11),
('1','2a',NULL),
(NULL,'a2',111),
(NULL,NULL,NULL)
GO
select name1,name2,zip, COALESCE(name1,name2,zip) AS name3 
 from #temptable1
While executing the select statement, an error for a data type conversion is returned. Which row (numbered in insert order) gives the error?

Answer: 3

Explanation: COALESCE returns the first nonnull expression among its arguments. The table contains char and int values together. If all expressions are nonnullable, the result we get also as nonnullable. Since in COALESCE expressions we have char values, with int values, the result returns the data type of the expression with the highest data type precedence. It will return the result up to the 2nd row, stop at the 3rd row and won't execute further. Int has a higher precedence than char and varchar, so the return type of your COALESCE must be of type int. The char values cannot be converted.

Ref: http://msdn.microsoft.com/en-us/library/ms190349.aspx
http://msdn.microsoft.com/en-us/library/ms190309.aspx

» Discuss this question and answer on the forums

Securing SQL Server

Written by Denny Cherry, a Microsoft MVP for the SQL Server product, a Microsoft Certified Master for SQL Server 2008, and one of the biggest names in SQL Server today, Securing SQL Server, Second Edition explores the potential attack vectors someone can use to break into your SQL Server database as well as how to protect your database from these attacks. In this book, you will learn how to properly secure your database from both internal and external threats using best practices and specific tricks the author uses in his role as an independent consultant while working on some of the largest and most complex SQL Server installations in the world. This edition includes new chapters on Analysis Services, Reporting Services, and Storage Area Network Security.

Get your copy from Amazon today.


Featured Script

What got restored from where, by who and when

Ever wondered what got restored, who did the restore and where the backup came from that did the restore? 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

Unable to connect to database after domain migration - Hi We just migrated our windows 2003 server to a new domain. We created a new service account and set the...

Split a column - Hi: I would like someone can help. I heve a column with many rows like: NAME Jonh*Smith*Alu Alex*Campos*kery Chipper*Jon*Ali Larry*Walker*kin AND...

Run As Account does not exist on the target system or does not have enough permissions - Hi, I got the below error couple of the times and Reboot is fixing the issue.. I want ti fix it...

Is there any way to find why the log had grown at a particular time - Hello, My error log says that the log file of one of my DB was full. Now when I check its...

Determine the size of the changed data in a database??? - We are upgrading to new servers/datacenter. One of the metrics the network team wants from us is to determine how...

Correcting Login to User mappings - I have a server where there are logins created that are mapped to dbo in a database. I want to change...

Crystal Report 2008 can't see my SQL DB - Hi friends, I have created a local SQL database using Visual Studio 2005 (not by SQL Server Management Studio), and located...

SQL Server 2005 : Business Intelligence

Import Excel binary files into SQL Db using SSIS - Hello, I have a several .xlsb (MS Excel binary) files with data that I need to import into a SQL...

SQL Server 2005 : SQL Server 2005 General Discussion

BCP Utility to output contents into CSV - I have a stored procedure that gets a SQL query, file path (to output) and file name as parameter which...

sql service broker - Hi Need help on getting code and info on service broker (for sql server 2005 only) for auditing DML commands. Respond if any. Thanks

SQL Server 2005 : SS2K5 Replication

Best practice for housekeep replication data - I am planning to setup snapshot replication and transaction replication from sql server to oracle server, in the process of...

SQL Server 2005 : SQL Server 2005 Integration Services

FTP TASK Error. - I am having a problem with a package I created several years ago. I am using a SQL task that...

how to write expression for Derived Column transform - I need to filter out all records as follows: where [Month 01] is null and [Month 02] is null and ...

Derived Column to handled mulitple conditions in CASE statement? - HI, I've got a case statement that works, but because I have to do it in SSIS I am at...

SQL Server 2005 : T-SQL (SS2K5)

VB.NET TreeView using Stored Procedure - There are 5 tables we have which has a parent child relation we have following tables. Order (Fields Order#, RefType, RefNum,...

SQL Server 7,2000 : Administration

Error: 17806, Severity: 20, State: 2. - Hi I got this error .Please help me to solve this. Error: 17806, Severity: 20, State: 2. SSPI handshake failed with error code...

SQL Server 7,2000 : Replication

The process could not execute ''sp_replcmds'' on PRODUCTION - Hi Champs, Thursday i run DDREINDEX on my Production Server, Next day i went to office i find out that Replication is...

SQL Server 2008 : SQL Server 2008 - General

how to assign additional SQL Server administrators - When I ran SQLEXPRADV_x86_ENU.exe (run under Windows' Administrator user account) to install SQL Server 2008 Express with Advanced Services, when...

Trying to use case when to pivot data - I'm trying to pivot this data but, I can't seem to get rid of the "extra zeros". Here's my query. [code="sql"] SELECT provider,...

Calling Batch file using SQL Script - Hi Team, Am calling a batch file from sql script, passing parameters to batch file and trying to print the parameters...

help with Arithmetic overflow error; Calculating Lunar Phases. - ok a few years ago, I was poking around with calculating Lunar Phases,which I adapted off of a math web...

Process For Data Migration - Hi All, I have a requirement to migrate a database but, with the migrated version to have column name changes...

Join Problem - my table is like this for attendance create table attendance ( CID int, BID int, date datetime, eid int, timein datetime, timeout datetime, ) and my shift table is like...

2 transaction log files -- which one is in use? - Hi , Today we discovered a database that has 2 log files (reason unknown; they're on the same disk). This database...

tempdb keeps growing - I have configured my tempdb as follows: 8 data files with an initial size of 14GB each. This was based on...

Query Tuning - Hi all, How to do query tuning. Is there any methods for query tuning.how to find Query cost .What is the...

DROP PK on highly called big table - HI I have a table with 80 million records with PK on an int column. A service selects data from this...

SSIS package execution remote vs / local - Hi all. Hope I'm posting in the right forum. We currently schedule SSIS packages using the Windows task scheduler, but we...

SSRS Plug-ins - Hi, I have users who want to graphically create their own SQL Reports (in R2 and 2012) . We have setup...

SQL server Query - PLease tell me what is the Query Recasting

Import Export Wizard Performance - I am having access to sql server 2005. Question : I need to load a text file data to the sql server...

SSRS - reports rendering incorrectly - Hi all I am currently having this problem on SSRS rending incorrectly. The problem is basicall that reports do not display correctly...

Gmail Setup For SMTP - Hi, I am set Gmail For SMTPin mssql.Configuration had been done. But While sending Mail.It is showing Error "530.5.5.1 Must issue...

Connectivity issue - Hi, I am trying to connect to a named instance on another server myhost\myinstance, but get an error message: A network-related or...

How to reduce the size of the t-log file of a mirrored DB that is still in restoring mode - Hello gents This might look like an oddball question, but one mirrored DB on our DR box has a huge t-log...

How to rollback an insert query on sql server ? , please suggest bit high priority .. thanks in advance - How to rollback an insert query on sql server ? , please suggest bit high priority .. thanks in advance

Xml Query Performance Sql Server - Hello, I have the below data structure The query is taking a while to run. I have a primary xml index,...

tempdb is full - Msg 1105, Level 17, State 2, Line 2 Could not allocate space for object 'dbo.SORT temporary run storage: 141509403869184' in database...

calculate Excess/Short - i have a problem kindly help me out. this type of data i want: Date------------Time in----Time out-----Spent time-------excess/short 2013-01-01-------09:14-----19:06-----------09:52-------------00:52 2013-01-02-------09:52-----18:36-----------08:44-------------00:16 2013-01-03-------09:15-----18:56-----------09:41-------------00:41 excess short comes from spent...

Different actual execution plan for same query - I have a query that I execute with a recompile hint with sp_executesql. This query performs rather ok. Then I...

issues with setting up Publisher in SQL2008 - I have a two server configuration where the distribution db is on another server and the publisher is on a...

facing issue in looping through in SOL to XML - I have the following query which is supposed create xml for each employee. Here is the query .... select E.FirstName + ' ' + E.LastName as 'emp/@Name',P.PositionName...

SQL Server only using 4Gb of 8Gb RAM - Hi all, I'm slightly befuddled and was wondering if anyone could shed some light on why my test box running a...

SSIS - What is the best practice for creating a fixed length flat file. - I am running into the problem of SSIS putting the CRLF in random places at the end of the row....

Maximum number of Instances in SQL 2008 - Hi - I'm a bit confused on the number of instances allowed to run under a single SQL 2008 license. This article...

Risks of not upgrading from SQL 2000, SQL 2005 - Hello - I am inheriting 5 servers to administer from a jack-of-all-trades DBA/developer in another department. 2 of the instances are SQL...

Agent Job Links to Another SQL Server and Fails due to Service Account Privs - To my friends in the community, here is a stumper that I can't seem to find sufficient information on despite...

Need to be alerted to disallowed email addresses being entered - Admin cleaning up after App programmers! :-) - Hi all I think I may be on the right track when considering creating a scheduled job. I need to capture...

Why is CDC returning Insert/Delete when I UPDATE a row in a table? - This is my second post asking this question. Hopefully someone will respond this time. I am experimenting with using CDC...

Error - I am getting while using database. I was trying to take databse in OFFLine. Msg 952, Level 16, State 1, Line...

option (maxdop 1) - I've noticed some blocking sessions caused by the following SQL but it's nowhere to be found in my SP's and...

SQL Server 2008 : T-SQL (SS2K8)

(SQL) Excel file has bigger size using OPENQUERY to update it - Maybe the solution can be so easy but I can't find it so I write here for some help. We have...

triggers - Hello all, I've created a trigger that I'm having a bit of a problem with. After a user completes a warrantly...

selecting Distinct value in a column based on values another column. - Hi Experts, I have 2 columns as below -------------------------------- taxid tax_Combination -------------------------------- Bed+Vat ! Vat Bed+Vat ! Cess Bed+Vat ! BED VAT ! Vat CST ! CST from a query i will get the...

Import excel custom formatting cell to sql server 2008 - I have an excel sheet . having different data column One column having data like Effiency ======= 70% 80% 50% Column format = [<0]"";0% when I importing data from...

using row_number() over partition by to get datediff by row - I have a list of events that occur for a patient. I need to count the number of events that...

Delete failing in distributed transaction on linked server - I am looking to "transfer" records from a table on Server A to a table on Server B. And then...

SQL Server 2008 : SQL Server Newbies

SQL: Excel file has bigger size using OPENQUERY to update it - Maybe the solution can be so easy but I can't find it so I write here for some help. We have...

Get Value from another table - I have two tables, lets say Table 1 and Table 2 as below Table 1: Col1 Col2 Col3 1 _A 11 2 _B 12 3...

query to search - I have a table with fields and data type like companycode [nchar(4)] actcode [nchar(12)] vounum [nchar(14)] trnam [decimel (18,6)] I like to...

get lookup data - I have two tables, lets say Table 1 and Table 2 as below Table 1: Col1 Col2 Col3 1 _A 11 2 _B 12 3...

IDENTITY_INSERT, and Using Alphanumeric Autoincrement Primary Key - Hi Folks, Please help me on below few queries My table is Users(ID IDENTITY(1,1) PRIMARY KEY, NAME nvarchar(10), MANGER_ID fk_User_id References...

msbi - currently i am undertaking msbi course.. will it be a added value to my resume in which direction i should look...

Read Log from Windows 2003 Task Scheduler - I am looking for help in reading Windows 2003 task scheduler log history to find out if a task has...

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

How have Trigger's example after insert send to mail on outlook?. - I create trigger after insert into table i want send mail on outlook and example trigger please. thank you.

SQL Server 2008 : Security (SS2K8)

Pre Login HAndShake Error in Sql Server - 2005 SSMS - Error: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: Shared...

SQL Server 2008 : SQL Server 2008 High Availability

DB Reindex - Yesterday i did rebuild my index but while checking I came to know that there are still some fragmentation..what is...

What do u mean by DB Refresh - What do u mean by DB Refresh..What r the steps to perform this task...

Mirroring Alert - Quick question... I have an alert set up to email me whenever one of my mirrors move into state 6 ("connection...

Installing Windows Service Pack on Cluster - suggested way? - Can someone direct me to a Microsoft article or possibly a SQL Server Central article showing the suggested process to...

SQL Server 2008 : SQL Server 2008 Administration

shrinking tempdb log file - Last week our tempdb log file grew in size and alerts started pouring in as we have just few space...

Questions on Index Rebuilding (not reorganize) - Hi Guys, Need some information on Index Rebuilding. (sort_in_tempdb = off) When performing index rebulding (for indexes > 30% fragmented), will we expect...

Backups failing after full transaction log - The sequence of events went like this. Transaction log disk ran out of space. I was able to add space to...

Transactional Push Replication - Overhead of having Distributor on the Publisher - I've recently set up Transactional Replication with Push subscriptions. At the moment the distributor is running on the publisher server....

Client tools? - What are client tools in SQL Server and why do we really need them.

Database Documention Tool - Hi All, Can any one guide me to find what is the best sql server database documentation tool? I have searched...

Copy SQL .bak file to other server using FTP and restore the backup to a database - Hi, I am looking for solution for below approach using TSQL or powershell or using SQL agent job. 1. Perform backup on...

[sys].[dm_os_volume_stats] - I'm working on collecting stats on my sql servers to better keep an eye on them, and there's a table...

Career : Certification

70-448! - hello. I am preparing my exam 70-448 and want to do more test. What exams you recommend me? I saw SelfExamEngine and...

Programming : Connecting

mysql connector ODBC 5.2 - I tried to transfer data from ms sql server 2012 to mysql using sql server export, but could not find...

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

Is there a way to disable SSRS cache? - I'm working on a report that has a local query that just does a select from a table. The table...

Report Manager Security Permission Issue - I have a folder in SSRS Report Manager that a user created for testing reports. The issue is the user...

Database Design : Design Ideas and Questions

What normal form would this be considered? 2NF or 3NF? - Lets say I have a table with four columns named PersonID1, PersonID2, JobTitle, and PreviousJobTitle. There is a composite primary...

Data Warehousing : Integration Services

finding and replacing ID's and Updating dataflow with new ID --HELP!! - Hi All, I created a SSIS package that check first whether the ID in the file being imported matches to an...

How to Desc the memory Pressure in SSIS - How to Desc the memory Pressure in SSIS ? 1.Set the diff buffer max=0 2.Reduce the engine threads 3.Increase BLOB temp storage 4.Reduce buffer...

SSIS 2008 R2 - FTP - fails when run from a job, but ok when manually - I am trying to stay away from another batch file, is possible. I have an SSIS package that pulls data...

help writing SUBSTRING() expression please - I have strings like this: MFMGOOGLE, inside my [Business Unit] column, from which I need to strip the "MFM". Yet...

Opening a rowset for "A03210" failed - Hi all, I'm getting this error since this morning and can't find out why it happens. From what I saw on...

Data Warehousing : Strategies and Ideas

Retrieve more than 1 million rows from Cube - Hello All, Am new to ssas and been trying to implement it for our current system Here is what i have built Fact...

Data Warehousing : Analysis Services

MDX ParallelPeriod Function - HELP - Hi to all. I have a calculated measure in one of my cubes. This is named DELTA SALES AMOUNT It calculates [b]Parallelperiod[/b] about...

Copy table to create an alias (Tabular model) - Hi guys, does anyone know how to copy and paste a table to create an alias with in a tabular model. e.g....