In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
Red Gate Deployment Manager NEW! Automate your .NET deployments
Deploy ASP.NET applications and SQL Server changes fast, frequently, and without fuss, using Deployment Manager, the new tool from Red Gate. Try it now.
 
sqlmonitor "It really helped us isolate where we were experiencing a bottleneck"
- John Q Martin, SQL Server DBA. Get started with SQL Monitor today to solve tricky performance problems - download a free trial.
 
SQL Source Control Is your SQL Database under Version Control?
SSMS plug-in SQL Source Control connects SVN, TFS, Git, Hg and all others to SQL Server. Learn more.

In This Issue

MDX Guide for SQL Folks: Part I - Navigating The Cube

Learn all you need to know about MDX, by drawing only on your current SQL knowledge.  More »


Manipulating XML Data in SQL Server

When the average database developer is obliged to manipulate XML, either shredding it into relational format, or creating it from SQL, it is often done 'at arms length'. A shame, since effective use of techniques that go beyond the basics can save much code, More »


From the SQLServerCentral Blogs - Lost in Translation – Deprecated System Tables – sysindexes

This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective... More »


From the SQLServerCentral Blogs - SSIS and Oracle All Your Non-options

Why do some things have to be so hard? I have been asking myself that question for the better part of... More »


Editorial - Checking In

It's been a crazy couple of months for me, with the US tour of SQL in the City and the fall DevConnections conference taking me away from the ranch and to a variety of places. Those events had me away from home and delivering 14 talks on a variety of topics with a few more coming next week at SQL in the City - Seattle 2012. I'm just stopping by for a few days at home before I head back out to the Pacific Northwest for a few days.

My job is not the typical job that DBAs have, and my schedule results in a number of trips all around the US and sometimes to Europe. Despite that travel, my daily chores still need to get complete. I don't have to manage servers like many of you, but I do have a certain number of processes that need to run every day and require me to prepare things in advance when I'm away. I'm glad for all the automation since I'm not sure how I'd cope otherwise.

It's not that much different than my former production DBA routine. There were times when I was in a class, or attending an event, or even working on a large deployment of some application. At those times I'd often be away from my desk, and less available then other times, but I still had to keep an eye on the rest of the systems. Like many of you, I setup automation, crossed my fingers, and hoped that no major issues would crop up while my attention was focused elsewhere.

Over time I learned to let go a little as a DBA. If something broke, then it had to wait, or a manager had to decide which crisis needed to be handled first. I realized I couldn't stress myself out about other systems and had to count on them running while I was away. These days I also do the best I can, but I also realize that at times I might have something break while I'm away. I have to trust that someone else will fix it, or I'll deal with it when I return.

Next Thursday.

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

How many 8-bit characters are there between '0' and 'Z' (inclusive) in the collation Latin1_General_CI_AS?

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

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

Troubleshooting SQL Server: A Guide for the Accidental DBA

Three SQL Server MVPs provide fascinating insight into the most common SQL Server problems, why they occur, and how they can be diagnosed using tools such as Performance Monitor, Dynamic Management Views and server-side tracing. The focus is on practical solutions for removing root causes of these problems, rather than "papering over the cracks". Grab your copy today from Amazon.


Yesterday's Question of the Day

Halloween protection in database systems has to do with which type of T-SQL statement?

Answer: UPDATE

Explanation: Halloween protection refers to the mechanisms designed to prevent an update statement from selecting the same rows for change over and over again.

Ref: Make Functions Schema-bound - http://blogs.msdn.com/b/ianjo/archive/2006/01/31/521078.aspx

» Discuss this question and answer on the forums

SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.


Featured Script

Generate Insert Scripts

Generate an insert of your data  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

Fastest way to blank all records in one char field - We have a table with hundreds of thousands of social security numbers, which we want to blank. Table has 60...

Tempgb log - not able to shrink....none of the queries are helping - I have gone through many many different forums in trying to identify my tempdb issues. None of the queries are...

Get job list - Need a query which gets me the list of jobs which is scheduled to run on a particular day and...

help with this query!!!! - My goal here to apply the condition to the query while joining the table instead of doing it at the...

Error 229: Execute permition denied on object............. - Hello again, People are reporting the following error: Error 229: Execute permission denied on object 'xp_sqlagent_enum_jobs', database 'master', owner 'dbo'. Can you tell...

Unable to install client component of sql server 2005 - When I install only client component on my desktop, I keep on getting the following error and it failed to...

SQL Server 2005 : Backups

Log backup Fails - I have full backup scheduled daily at evening 5 PM. Differential occurs daily after every 3 hours throughout the day. Log backups...

SQL Server 2005 : Business Intelligence

Does it make sense to store measures in a dimension table? - Hi everybody: Right now I´m working in a BI solution and I received a set of tables with a multidimensional structure....

We are processing cubes Two methods using SSIS script Task. Which one is better. - Hi All, We are processing cubes two methods using script task. we are following below two methods. Method 1: Method 1 is cubes...

How do I know - SSRS - I believe if SSRS has been installed/enabled on my server, I can find out be looking in the sys.databses table...

Exporting from SSIS 05 to ACCESS 97 - I have a pckage that imports data from an old Access 97 database. It imports fine, but when I export (using...

SQL Server 2005 : SQL Server 2005 General Discussion

Full Time and Part Time Administration! - :-) Challenging position offers a variety of tasks and responsibilities. Ideal candidate must be professional, dependable, organized, take pride in there...

can we connect to 64-Bit SQL server instance with 32-bit SQL server? - Hi, I have installed sql server 2005 standard edition 32-Bit in my XP desktop (VDI).I tried to connect to one of...

need documentation steps to upgrade sql2005 workgroup editon to sql2005 standard editon - Hi to every one need reply soon 1. what are the all possible ways to upgrade sqlserver 2005 workgroup editon...

Is it "better" to specify the definition of a temporary table, or just define it on the fly? - The subject line says it all, but just to make it clearer... I could use a [b]CREATE TABLE #TempTable[/b] to define...

SQL Server 2005 : SQL Server 2005 Integration Services

Error :t" property not set correctly, parameters not set correctly, or connection not established correctly. - I have an SSIS job running in production.The job failed today with an error message Executed as user: Test\SQLService. t" property...

truncation error but data types are the same - Afternoon all, I have a flat file .csv format that I am importing. It is 53 columns across and when it hits...

SQL Server 2005 : T-SQL (SS2K5)

SELECT DISTINCT AND ORDER BY CASE - Hi i am trying to use select distinct with order by case but getting an error like [color=#990000]"ORDER BY items...

Getting Output value from sp_executesql for XML issue - Hi, I am trying to get an xml blob into a var from dynamic sql. However, whenever I run this: DECLARE...

trying to export to .CSV with comma delimited AND double-quotes - What I have is a simple table (in SQL 2005) that provides input to a mapping program. It has four columns....

SQL Server 2005 : SQL Server Newbies

RE:- HELP! Cursor results displayed in table - I have the following code below that prints a set of results to screen using put_line but I need to...

SQL Server 7,2000 : Administration

SET ARITHABORT ON; - Hi All The SET ARITHABORT ON; is on as default for SSMS but not for all other methods of connection ie...

SQL Server 7,2000 : Data Corruption

MDMP files - Hi, In one of my production server, am seeing many MDMP files created on Oct 24. I just refreshed my mind...

SQL Server 7,2000 : Performance Tuning

Query to Rebuild Indexes on SQL Server 2000 Database - Hi, Can anyone please let me know the query to rebuild all the indexes in an SQL Server 2000 Database?? I need...

SQL Server 2008 : SQL Server 2008 - General

Upgrading a sql server 2005 clustered environment to sql server 2008 R2 - I'm trying to determine the best approach(i.e. in-place or clean install) to upgrade our sql server 2005 clustered environment to...

How to find Procs/Views/Functions using particular database? - Hi All, I would like to find out all procs, views, functions, jobs, SSIS packages everything which is using particular database...

Previously well-behaved query is now causing tempdb to choke up - Hello Experts Running (trying to run!) on Microsoft SQL Server Enterprise Edition (64-bit), version 10.0.2531.0 (SP1) Virtual instance, 16384 MB memory. I am...

Problems with unicode - I'm having a problem inserting a value into a varchar datatype field, the value I'm trying to update a value...

Dynamic SQL and sys.sql_dependencies - Hi, When writing a procedure using dynamic SQL (cause of parameters that may not be used, ...) SQL Server doesn't "fill" the...

Service Broker Contract - Can two service broker queues use the same contract?

Incorrect syntax near the keyword 'IF'. - I'm trying to create a stored procedure, but I keep getting "Incorrect syntax near the keyword 'IF'." Below is my full...

can I get the cuerry of an existing table...??? - I wanted to copy a table and its data to another db in another pc.... I was wondering wether there...

Best index structure for table that holds a large amount of log data - Folks I have a ASP.NET solution that needs to log every time a method is invoked.(that part I have done) I am...

SSIS in cluster setup - Since SSIS is not cluster-aware, what is the best approach to configure SSIS in SQL 2008 Cluster. When the active goes...

Heigh utilization on RAM even when server is ideal - Hi, I have just create a new database server (intel Xenon E5620 2.4 Ghz * 2 processors) with win server 2008 R2...

SSRS Report ON CMS - Hi DBA's, I hope you are njoying the Winter. :) I need to run one report for all the server at one...

I can not send Email from SQL Server !!!! - Hi every body, I have big problem at least for me, i want to send an Email using SQL Server 2008, this...

Help! I am having an rage day in SSMS (auto-deletion of closing parenthesis) - So I've been working with SQL for a long long time, and I have noticed this little feature of the...

Recent experiences with SQL 2008 Administration training providers in CA - Wanted to see if there were any recommendations for classroom training, SQL 2008 R2 administration in California - global knowledge, new...

SQL Server query Automation - Hi, I am testing one application I am preparing some queries as given below Step1: SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = 'CMC_PDRA_RATE_AGES' Step...

CMS - I am able to run a query in central management server created on one of the SQL servers lets say...

Is schema in oracle same as SQL Server schema - is schema in oracle same as SQL Server schema?

Check Constraint Works on Inserts, but Not on Updates - I created a UDF to check for more than one instances of a value 1 in a bit column corresponding...

finding char based on ascii value in Ireport - Dear All, How to find a ASCII characters based on the ASCII values in Ireport .. Like in Crystal Report Chr(33)...

Memory Pressure - Hi, We have Production server win 2003 with Sql server 2008 running on it. Ram --4GB. We have performance issue for a database...

Query CPU Performance - Hi there, We have been having issues with CPU performance of a new server when running queries. The worst example was...

does temp tables are created unique for each sp call. or will it make a queue on server ? - Hi, I am working on a software in which there a SP which is called very frequently, all most all users...

Replication process - Hello All, We have a replication job which was configured some time back.I could see some tables sysarticles,syssubscriptions,sysreplservers related to the...

Suspended Queries - Hi Team, I am getting blocking in one of my sql server. I can query which is suspened causing the block. It is...

How would you convert this date in SSIS or after lodaed in database 2007-02-20 05:20:36.449923 - Have google a lot of different formats and I must be missing something. How would one look to convert this...

How to give a user ONLY permission to a view, and nothing else in the database - I need to give user Joe select permissions to a view. The view has columns from two tables from the...

100 Most famous interview Questions and Answers - Please go to the following link:- http://sqlcheatsheet.wordpress.com/2012/10/26/sql-server-2008-2008-r2-cheatsheet/ Download the PDF (sqlserver2008r2_cheatsheet_v1-01.pdf). Password: harinam This is very good document for the freshup your memories...

Question on Lock wait types - Hello Everyone, Our production server experiences lot of blocking and locking from application. I have attached the top waits from...

Connot connect to server via name but IP works, not a SQL Browser service issue :) - Ok, here is the issue and my steps to troubleshoot: I can connect to my SQl server using SSMS by IP...

OPTIMIZE FOR ADHOC WORKLOADS question - I recently turned on OPTIMIZE FOR ADHOC WORKLOADS on one of my servers. My understanding is that after this flag...

SQL 2008 BI vs SQL 2012 BI - Hi all, Just a little question ... I have ( over the past 18 months ) managed to get my DBA and DBD qualifications...

Database Mirroring History - Hi All, Is it possible to extract the Database Mirroring History exactly as it is in the GUI Database Mirroring Monitor? I...

SQL Server 2008 : T-SQL (SS2K8)

Inventory Report - My database with the following table: ITDetail Table [IMG]http://i.imgur.com/WcwRI.jpg[/IMG] InvTrans Table [IMG]http://i.imgur.com/hPnlz.jpg[/IMG] Currency Table [IMG]http://i.imgur.com/YUNxC.jpg[/IMG] Database Relationship [IMG]http://i.imgur.com/CTCdo.jpg[/IMG] I need to build a report with the time parameter (use...

Partitioning on a computed column (int based) - I am trying to implement Search functionality using the partitioned table concept. Every search that user performs, their hits need to...

Change Data Capture 2008 - I have enable the change data capture on db and enabled one table and i made updates on couple of...

char(??) vs varchar(??) - Hi all. I have a database with over 200 tables. They all use char(xx) instead of varchar(xx). I heard that...

A maths puzzle for anyone up for a challenge! - Hi, I've been given the task of creating a single unique numerical value from two composite unique alphanumeric values. It needs...

SQL Server 2008 : SQL Server Newbies

Confused by table size - I have a table in a database I inherited that has an mdf file of 25GB, with ~870MB space available....

How to import data from .csv file into a table in sql server using bcp in/out - Hi experts, I have a .csv file from which i have to do bcp out and bcp in, in command prompt...

SQL Server 2008 : Security (SS2K8)

Sending remote attachments using sp_send_dbmail - Hi, I am using Windows Authentication to send email using sp_send_dbmail. Sending email works if I do not try to add a...

SQL Server 2008 : SQL Server 2008 High Availability

Transactional Replication - Inner workings - Hi all Just wondering how the inner working of replication happens. eg. You have server A replication to Server B. (transactional...

Cluster Quorum - Node and Disk - Hi Guys Please help me understand something the Cluster Quorum and specifically Node and Disk Majority I understand that if you have...

What credentials are used for mirroring connections between partners? - I know when I configure security for a new mirror, the wizard prompts me for credentials to connect to the...

Database Level Clustering in SQL 2008 - Hi Techs, is it possible to have DB level clustering in sql 2008? if so how can we achieve this? I...

Principal Disconnected, In Recovery... on both servers after restart - Hi, today I had the problem, that after a restart of my two mirrored database servers on both servers all databases...

SQL Server 2008 : SQL Server 2008 Administration

A few questions regarding Server Side Tracing - I've recently started working at a new company and they have server side tracing setup. I've read thru the stairways...

DBCC FREEPROCCACHE - Can we use in DBCC FREEPROCCACHE in production database? Thanks in advance

Reorgnize index - When I use sql server maintenance plan, there is a task called reorganize index. If I setup a job to reorgnize...

Remove Key lookup - Is creating covering index the only solution to remove key lookup in the execution plan?

lightspeed silent installation - Getting below licence expired error While installing lightspeed with silently but Licence key is valid and not expired. Error saving license. SLSInstall...

SQL Server Agent will not start after upgrade - Yesterday I upgraded a Windows Server 2008 R2 server from SQL Server 2005 SP4 to SQL Server 2008R2 SP2 in...

Monitoring Windows Event Logs - I am curious about which tools people are using to monitor Windows Event Logs on servers hosting SQL Server.

Programming : XML

Returning multiple rows from XML - Greetings, I am new to XML and am struggling with shredding an XML column into a tabular format. Here is what I...

SQLServerCentral.com : Anything that is NOT about SQL!

Not enough work? - Just curious, has anyone ever had to quit a job because they just can't keep you busy?:doze:

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

Subscription of multiple valued parameterized report in SSRS - Hi, I have a report in SSRS report server URL, it takes location as a parameter from the query dynamically has...

little help with installation - Hello... I arrived at my station at new work with very little software installed. All installs were default, nothing prepared for...

Parameterised MDX (SSAS2008) from SSRS2008 - I'm trying to run a MDX query that contains the topcount() function and I wish to parameterised the count parameter....

Data Warehousing : Integration Services

How to create a Connection Manager to access directly to a PROGRESS DB - Good evening to all. I am new of this forum. I ha ve a quite good experience in SSIS and SSAS 2008...

SSIS send mail question - I am trying to create a package that reads email data from a tables based on if an email has...

Windows batch file executions within SSIS fail when run from SQL Job Agent - Windows batch file executions do not fail within SSIS when SSIS run using DTExec The account we are using to execute...

Data Warehousing : Strategies and Ideas

Does it make sense store measures in a dimension table? - Hi everybody: Right now I´m working in a BI solution and I received a set of tables with a multidimensional structure....

Data Warehousing : Analysis Services

SSAS front end tools - Hi, I built a dimensional model with the fact and dim tables populated and created a cube using SSAS 2008. Now...

Conditional Calculated Measures - I am having an issue trying to add some conditional logic to a calculated measure. What I am trying to...

nable to build SQL Server OLAP Cube when there is no rows corresponding to foreign key in a table - I am new to SQL Server OLAP Cubes. I am having the following issue like ex I have purchase order and...

Microsoft Access : Microsoft Access

Ms access add record to table via unbound text box - Front end MM Access adp file. On SQL server 2005 Form bound but user must not change data directly Unbound text box...

Access 2007 front end to SS2K8 problem - My app includes 1) SS2K8 database on the back end 2) Access 2007 front end NOT LINKED, data flows through ADO...