In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Monitor Free eBook: SQL Server Concurrency
Every DBA must understand SQL Server concurrency and how to troubleshoot any issues. Kalen Delaney's eBook explains all - download it today.
 
SQL Prompt Make working with SQL a breeze
SQL Prompt 5.3 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.
 
SQL DBA Bundle ‘The Case of the Missing Index’
Discover the Top 5 hard-earned lessons of a DBA, presented by The DBA Team. Learn from lesson one now.

In This Issue

Dynamic PIVOT CLR

Learn how yo can use a SQL-CLR procedure to handle all your PIVOT queries. More »


Red Gate Wins 8 Awards and You Can Get a Prize

Red Gate won 8 awards in the recent SQL Server Pro annual product reviews. As a celebration, we're giving away a few prizes. More »


SQL Server 2012 Integration Services - Implementing Package Security using Access Control

SQL Server 2012 Integration Services offers a wide range of powerful features that allow you to streamline and automate tasks involving data extraction, transformation, and loading. However, incorporating these features into your existing business intelligence framework frequently necessitates additional security measures ensuring that data which is being processed remains protected from unauthorized access. More »


From the SQLServerCentral Blogs - Tables without a Primary Key

As well as creating the script for yesterday’s post on finding Tables without a Clustered Index I tweaked the script... More »


From the SQLServerCentral Blogs - Querying Performance Counters in SQL Server

In a previous post, there was a comparison between sysperfinfo and sys.dm_os_performance_counters which included the use of a simple query. ... More »


Editorial - Try To Be More Inefficient

This editorial was originally published on Apr 3, 2008. It is being re-run as Steve is out of town.

Yep, I'm not advocating the most efficient way to do things. Not in your SQL code, mind you, but in some other areas. There's more to your job than coding, and it doesn't always pay to be as efficient as possible

Meet with people.

Not meeting meet with people, in a conference room at a scheduled time, but just an ad hoc meeting. A "dynamic DBA meeting", stop by someone's office or work space and take a few minutes to talk to them. Understand what they do on a regular basis, their pain points, their likes and dislikes.

Invite them to lunch, or some other social event outside of work. Or just bring your clubs to work (or borrow one) and take someone to hit some golf balls at lunch. Even if you have no idea how to do it, laugh at yourself and spend a few minutes with someone that does enjoy it.

I'm not trying to find you a new best friend, though if that happens, it would be great. Instead I think that it's just important to develop a bond, a well-rounded relationship with those that you need to work. Especially those knowledge workers whose knowledge you need. Remember that to build an application, secure the data properly, even to just understand the data, often you'll need to ask questions of the people that use the data. Even older developers or DBAs might not get things right and you'll need to go to the source.

So make sure that you try to create that bond with someone new and don't just spend all your days being as efficient at writing code as possible.

Steve Jones


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.

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

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


Question of the Day

Today's Question:

Which function is not an aggregate function?

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

This question is worth 1 point 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

True or false: In SQL Server 2005 and higher, if you have optional monitoring server in your log shipping setup, you cannot configure the monitor server to record the status and historic information about log shipping jobs. (like backup, copy, restore of the transaction log), but only to send alerts to the operator when it fails.

Answer: False

Explanation: You can configure the monitor server to record/keep-track of the activity.

Ref: http://msdn.microsoft.com/en-us/library/ms187103.aspx. (and see the monitor server under the "Terms and Definitions".)

» Discuss this question and answer on the forums

 Inside the SQL Server Query Optimizer

This book will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way. Grab your copy today from Amazon!


Featured Script

Get Scheduled Jobs from Multiple Servers

Script to loop thru multiple linked server and get agent scheduled jobs for the next XX hours. 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

Restore DB with actual data size - Hi, I have a SQL Server database whose data files have been created with an initial size of 150 GB,...

Can we Upgrade from sql server enterprise edition 2k5 to sql server enterprise edition 2012? - hello All, I need experts advised who have already started to work on sql serevr 2012, i came across an...

Increasing the size of the container - Hello -- One of our systems is running SQL Server 2005, and has a database that was initially 'sized out' at...

Upgrade from SQL Server 2005 Standard to Enterprise - Hi, Im having some problems with this, it is the first time im attempting to upgrade to a different edition. The...

SQL Server 2005 : Business Intelligence

i am working in ssis and i have written vb script in script task for protected excel package conveted unprotected excel package but without using Msoffice so i also have PIA,intel.office.pro - Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x80040154): Retrieving the COM class factory...

Excel Connection Manager issue will only preview data when XLSX spreadsheet is open - We receive a spreadsheet via FTP from a vendor and have to load the data into SQL Server. When I...

SSIS - E-mailing from causes error - All, A week ago some outside consultants upended my world. They switch the e-mail server from "tom" to "katherine". We...

How To create Two hierarchies for two separate columns in fact which refers to single dimension - Hi All, I want to create Two hierarchies for two separate columns in fact which refers to single dimension. e.g I...

How to count multiple entries in one column in SSRS? - If I have 1 column displaying current product of 200 accounts. How can I see product counts? So if they're...

Performance Issue while Inserting data in to DB2 from SQL Server using SSIS package - Hi, I am hoping someone will have a solution for my problem. I am having a performance issue while inserting data...

SQL Server 2005 : Data Corruption

checkdb error on index ID 0 - I need help on fixing checkdb error. Here's the output of checkdb: Server: Msg 8909, Level 16, State 1, Line 1 Table error:...

SQL Server 2005 xp_logininfo error message - If anyone has a valid solution, I am all ears. Here is the issue....when you run xp_logininfo, for some AD accounts...

SQL Server 2005 : SQL Server 2005 General Discussion

Microsoft SQL Server Management Studio Express - Hello All, We are trying to create a centralised DB which could be used from a client location. Please let me...

Different domains causing poor query performance? - I think my powers of google-fu must be letting me down - can anyone help with the following:- It has been suggested...

Your Thoughts on ORM Tools - We're at a potentially critical point in one of our major software products, where we're (finally!!!) switching from VB6 to...

SQL Server 2005 : SQL Server 2005 Strategies

Object Relational Modeling (ORM) Examples - I really need some help on this one. One of our development teams is looking into ORM as a way...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Very long avg queue length for SQL Server - I am looking into a very unwell data warehouse running on sql server 2005. For the size of the DW...

SQL Server 2005 : T-SQL (SS2K5)

How to return a certain percentage of rows (NOT TOP PERCENT QUESTION) - I want to return all values that represent x% of the total values within a table. Here's some test data: [code]create...

SQL Server 2005 : SQL Server Newbies

How To Determine The Number of Open Connections In Sql Server 2000 - Good Afternoon Guys, would it be possible to know the number of connections a person has in a Database in Sql...

SQL Server 7,2000 : Administration

Large master database - Hello All, SQL Server 2000 SP4. My master database file is over 1 gigabyte. This seems excessive. I am trying to...

SQL Server 2008 : SQL Server 2008 - General

How to get a Random (Ranged) Number based on a Percentage Index - Hello, I have a table with 1 million records. For each of these records I want to generate a random...

Deadlock issue - I have 2 tables: [code="sql"] CREATE TABLE [Prescription].[Weight] ( [SessionID] INT NOT NULL, [TargetWeight] REAL NULL, [Intake] REAL NULL, [WeightLoss] REAL NULL, .................................... ); ALTER...

SSIS - How to set a variable's value? - I am trying to set default value for a Variable "vSourceSPList" with List source properties. The property name for SharePoint...

Web enabled SQL Server Management Studio? - Does anyoen know if there is a web enabled version of SQL Server Management Studio?

Date Format - Hi Team, Am using below code to display name and "Date of birth" but Field DOB is not coming in Date format. [b]select...

Error 17886 Only on Prod, never on Dev or Test or Staging - Hi All We have an issue were we are getting error 17886 logged between once to fifty times a day since...

update statement where SET value is variable - PROBLEM: Table J.JTypeID is full of NULLs. I would like to run a statement to update J.JTypeID with the relevant...

trigger when a specific column gets updated - Hi Team, It's my first time to use triggers so Is there a way to create a trigger when a specific...

SQL Trigger for multiple columns - Insert / update - hi Team, I want to create a trigger, that that should fire when ever particular columns are updated/inserted. am using below query.....

Transaction Log backup - In Management Studio, in SQL 2008 TLog backup Maintenance plan creation - Back up the tail of the log, and leave...

Create trigger to track value of sp_configure - Hi, i need to create trigger that sends mail whenever any value changed in sp_configure.. I am looking for idea or...

Major difference between cross join and inner join - Hi All, Can any one explain Wat is the main difference between cross apply and inner join??? according to my view both are...

ADD drive in sql cluster dependency - HI, We have Sql server 2005 cluster running on WIn 2003 Can any one tell me the process and steps how to...

How to track the changes to data/log file Autogrow settings - Hi, We have a situation where the Autogrow settings were changed to Unlimited and would want to know when and...

CHECKPOINT block ALTER DATABASE - Hi, I tried to modify the size of autogrowth for one of the data files in my database. However, it took...

span sql instance across servers - Is it possible to have a SQL Server instance, say SQLProd01, span multiple windows servers? Let's go with 3 servers...

Disabled SQL Server Browser - Is there a good reason to disable the SQL Server Browser if you are going to accept remote connections?

Query to find the start day of the week as Monday - Hi All, How to specify the start day of the week as Monday for the below records I have tow fields Record...

conversion of varbinary(max) to filestream - What are the best steps to convert varbinary data to filestream? Right now we don't have it turn on and...

Failover Problem - Dear, I have configured mirroring with high safety without automatic failover mode. For testing purpose I want to failover between Principal...

A quick and easy way to pull up table names, column names, and specific data structure - This is another one of my fun little tools that I decided to share. There are times that I need...

Mirror server hardisk I/O utlisiation 100% - Hi, Should my mirror server hard disk I/O utilization be between 90% and 100%. ? I understand its the mirror server and...

database is not connecting . - Hi All, While connecting to database am getting below error. "A transport-level error has occurred when sending the request to the...

How to Know server is in SRDF - We have SRDF DR setup in our SQL env and one common question which arise everyday is how can one...

Identity Seeding - Just curious Anyone found a reason to declare an Identity column that wasn't (1,1)? Sure, legacy data can cause you to start...

Backup Overwrite - I have a requirement where i need to take daily backup with datetime stamp , the issue is we have limited...

Performance tuning for ORM application for 2008R2 migration - Hi all, I am responsible for migrating an in house .Net application which was developed with an Object Relational Model Data...

Msg 468, Level 16, State 9, Procedure "procedurename", Line 129 Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. - Dear All, I have a stored procedure and I keep getting the following error message: Msg 468, Level 16, State 9, Procedure...

SQL Server 2008 : T-SQL (SS2K8)

Get list of dates falling on Monday between two given dates - Dear all, Can any one please help me providing the SQL query statement on getting the list of all dates that...

Update statement works, but I don't think it should - So I am updateing one record, my update statement returns three records. Normally I would expect to see an error....

Send Alert when no log backup completed in 24 hours - Dear Forum, I'm sure someone has completed this type of request, but here is the back ground. I have a...

How to improve query with multiple LIKEs with leading wildcards - I have an app that I'm adding a generic search function to. The user types in a word, or phrase,...

Interview Questions - Hi..I had an interview and had few questions: Can anyone please help me finding out answers for the below questions? 1) Design...

Convert varchar - is there any way to convert varchar value to TIME datatype?

Straight & Reverse Check - kcehc eserveR & Straight - All, I have two table as below. [code="sql"]create table currency ( currencypair varchar(30) not null, rate_value numeric(18,6) null ) insert into currency select 'BNGCZK',null union select 'CHFCLP',null union select 'CHFLKR',null union select 'CHFPEN',null union select 'CHFSIT',null union select...

How to use values keyword as parameter of a function - I would like to have a function which accepts any number of parameters with the same type and returns true...

SSMS Query Logging - We have a user who has come from an environment (SAS) where all of the queries she ran were logged,...

SQL Server 2008 : SQL Server Newbies

Backup failing - Hello Masters, I have SQL Server 2008, My Maintenance plan for backup(Full, Diff and Log) has been failing from last two...

Add Many-to-Many all at once - I have a construct that looks like this: tblItemMaster (ItemID int Identity, other columns); tblTPItemMaster (tpItemID int Identity, other columns); tblXrefItemtpItem (ItemID, tpItemID,...

xp_cmdshell works and then doesn't work - A stored procedure intermittently fails, and it appears to be because xp_cmdshell stops working. If I try it manually, here is...

Why does only one user have access? - If a coworker gets a new computer, they can't access our database from any other user account on the computer...

Force read lock in a table, to avoid problems between parallel processes. - I am quite new to this, I'll try to explain my task and how I thought to solve it. I have...

unable to connect to sql server - Hi all! im posting this here since i am, in every sense of the word, a newbie. We have 3 MS failover...

Reduce database size - Have been asked by a customer to reduce the database size to latest 10% of its data. Not sure how...

SQL Server 2008 : SQL Server 2008 High Availability

SQL Cluster question - We have a cluster named Cluster1 which is a single node cluster. It has SQL Server 2008R2 Enterprise edition installed...

SQL Server 2008 : SQL Server 2008 Administration

Access to the path '$RECHCLE.BIN' is denied. - Hi, I have a long running SSIS package running on SQL Server 2008 R2 SP1 and hosted on a Windows Server...

Filegroup and number of files per FG questions - Redesign of 2 TB DB with 2,000+ batches per second - Hi all, We're currently under going an exercise to re-design the storage structure of our production DB. This is a system...

another index question, large table indexing - I've been looking for information on how to handle a large tables index. I haven't found a similar situation yet....

database log file - If a database is setup to readonly and in simple recovery mode. Will the log file grow or not? thanks

move db files to another directory - I would like to move database files to another drive. I used sql like below: ALTER DATABASE Z_20090501_coreSPSS MODIFY FILE ( NAME = coreSPSS,...

Error, not sure where to start: Attept to fetch logical page... failed. incorrect checkum - i hope this is the right forum :D [code="plain"]i've been going through my sql logs and have noticed that every 10...

Reduce size of BAK file after deleting database table records? - Hi All, Firstly wanted to wish everyone a Happy Christmas and I hope someone may be able to help me...

SQL Server index fragmentation is high after rebuilt - I have few indexes in my db, which report high fragmentation even after we rebuild indexes. The page count on...

sys.dm_exec_query_memory_grants - hello experts, got a server with 32GB of RAM, configured max memory as 28GB. with this query --> "select session_id, requested_memory_kb, granted_memory_kb,...

Career : Certification

Best laptop to buy for sql server environments - Hi Guys, I'm looking to buy a new laptop that I'll be able to use to build virtual SQL environments so...

Programming : Connecting

SQL 2008 MySQL Linked Server Issue - Hello! I'm having an issue setting up a linked server connection from SQL 2008 R2 (x64) on a Windows 2008...

Programming : Powershell

Query to variable - not wanting table style result - Hi I am trying to run a script that queries the last full backup file for a specific database. Invoke line...

Inserting into table with Powershell - I have a script that I'm adapting from something I found on the internet where I'm getting database size information...

SQLServerCentral.com : Anything that is NOT about SQL!

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

Reporting Services : Reporting Services

Better Looking Reports - Hi geniuses, Does anybody know any sql component to improve the look of a reporting services report? Thanks to all. Have a...

Report Builder Multi-value parameter textbox expression - Hi, I have a distinct list of state-county parameter and a text box that displays this value if selected. How do I...

Combining values into a category in SSRS pie chart - Hello everyone. I'm new to SSRS and need a bit of a hint to get the ball rolling. I selected...

Report Manager DSN cannot connect to database. Help! - I am trying to connect to an INFORMIX database through ODBC. I am using the "INFORMIX-CLI 2.5 (32bit)" driver and...

Reporting Services : Reporting Services 2005 Development

Select all records in current month - I have to change a prior report which included all records in which DateFinished is in the last 2 weeks...

Database Design : Relational Theory

ER vs ORM and Other Modeling Notations - Which is the dominant modeling language/notation? Is one more effective than the other and which are most often used in...

Data Warehousing : Integration Services

send email of all defaulters - Hi, Please help on below scenario to send mail. I am having a table which contains student attendance. If student is absent...

Error trying to run an SSIS Package - Hi Forum I getting the following error: [b]SSIS Error Code DTA_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit vesion...

Microsoft Access : Microsoft Access

Access 2003 ADP runs SQL 2005 SP to selectively delete rows - does not remove any rows - Run through the VBA code, SP removes no records. Run through the database queries window, SP removes no records. Run the SP...

Article Discussions by Author : Discuss Content Posted by Jay Dave

How to failover if Principal is disconnected - how to make Mirror database as Principal database when it is configured with high safety without automatic failover if Principal...