In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Backup Pro Disaster Recovery is made quick and easy with SQL Backup Pro
Use SQL Backup Pro's restore wizards and scripts to get your database back online as quickly as possible following a disaster scenario. Download a free trial today.
 
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 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

An Introduction to Database Design

An introduction to database design for those people that might not understand what is involved. More »


Using MDX to Calculate Both Values and Percentages for Analysis Services

When creating pie charts using data from Analysis Services, having the MDX query calculate and return the percentages along with the counts or sums is extremely efficient. In this tip, we walk through an example of how this can be done. More »


Free Webinar: Statistics in Oracle and SQL Server

In their third live 'Oracle vs. SQL Server' discussion, Jonathan Lewis (Oracle Ace Director, OakTable Network) and Grant Fritchey (Microsoft SQL Server MVP) will look at statistics in Oracle and SQL Server. Expect a lively debate on Oracle vs. SQL Server from two leading RDBMS experts on Jan 23, 2013. More »


Free eBook: SQL Server Tacklebox

Get a free ebook from Rodney Landrum and Red Gate Software that helps you prepare to deal with the various crisis situations you might encounter with SQL Server. More »


From the SQLServerCentral Blogs - Statistics Update Clarification

By default statistics are created automatically within SQL Server. And, by default, these stats are updated automatically based on a... More »


Editorial - The Auditing Poll

Our database systems become more important to the operation of our world all the time. We store data from sensors, back commerce systems, and more. We know software has bugs, and there will be security lapses, perhaps even from insiders. I suspect that as our software evolves, auditing will become more and more important all the time.

This week I'm curious to what extent you have auditing in your environment. For some of us it's included in the software we write, for others we might have to retrofit auditing when some issue occurs, but most applications I've used have very limited auditing or logging capabilities.

How extensively is auditing deployed in your environment.

Whether it's something simple like a Trace or Extended Events, the built-in SQL Audit, or some third party product, what percentage of your applications use some sort of auditing or logging? Is it a requirement, perhaps for certain types of systems? Or is it rarely implemented?

I think auditing will become more prevalent in the future, perhaps even a core function that we expect to find in every piece of new software.

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

Don't forget to vote for the SQL Bits sessions you'd like to see. Steve Jones and Grant Fritchey have submitted sessions and would love to have your support.

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. You can also follow Steve Jones on Twitter:

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. They have a great version of Message in a Bottle if you want to check it out.

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:

How you find records that exist in one table and do not exist in another table?

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

This question is worth 1 point in this category: Subquery. We keep track of your score to give you bragging rights against your peers.

We'd love to give you credit for your own question and answer. To submit a QOD, simply log in to Contribution Center.

SQL Server 2012 Data Integration Recipes

SQL Server 2012 Data Integration Recipes provides focused and practical solutions to real world problems of data integration. Need to import data into SQL Server from an outside source? Need to export data and send it to another system? SQL Server 2012 Data Integration Recipes has your back. You'll find solutions for importing from Microsoft Office data stores such as Excel and Access, from text files such as CSV files, from XML, from other database brands such as Oracle and MySQL, and even from other SQL Server databases. You'll learn techniques for managing metadata, transforming data to meet the needs of the target system, handling exceptions and errors, and much more.

Get your copy from Amazon today.


Yesterday's Question of the Day

I execute the following T-SQL

DECLARE @A INT,@B INT, @C INT

SET @A = 2

SET @B = 3

SET @C = 4

SELECT  (@A + @B)-@C AS 'Result #1' 

SELECT @A + (@B-@C) AS 'Result #2' 

SELECT (@A + @B) * @C AS 'Result #3' 

SELECT @A + (@B * @C) AS 'Result #4' 

The question is what are the results returned by each of the 4 select statements.

The results are shown as: Result #1, #2, #3, #4

For example, the results might be 1,20,3,41 (not the correct answer)

Answer: 1,1,20,14

Explanation: You need to understand the precedence in mathematical operations. Parenthesis are handled before other operations. Multiplication and division come before addition and subtraction, which are handled left to right.

Reference: SQL Saturdays Math and SQL Server - http://brandietarvin.livejournal.com/75705.html

» Discuss this question and answer on the forums

SQL Server 2012 Data Integration Recipes

SQL Server 2012 Data Integration Recipes provides focused and practical solutions to real world problems of data integration. Need to import data into SQL Server from an outside source? Need to export data and send it to another system? SQL Server 2012 Data Integration Recipes has your back. You'll find solutions for importing from Microsoft Office data stores such as Excel and Access, from text files such as CSV files, from XML, from other database brands such as Oracle and MySQL, and even from other SQL Server databases. You'll learn techniques for managing metadata, transforming data to meet the needs of the target system, handling exceptions and errors, and much more.

Get your copy from Amazon today.


Featured Script

2008 Index Fragmentation Maintenance

Just a stab at automating index maintainenance in SQL Server 2008 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

I just became Keeper of the Ring. And I'm Boromir. - So, to put it simply, I suddenly found myself as unofficial DBA. I've been working with SQL in a casual...

Copy Between 2 and 3 billion rows from a heap table with no key to a new partioned table. - Hello, I need to copy Between 2 and 3 billion rows from a heap table with no identity to a...

Agent jobs don't run according to schedule - Has anyone seen in SQL 2005 SP3 where agent jobs simply don't abide by a schedule setup? I had a...

Reclaiming deleted but unused LOB space in SQL 2005 - Hi, I've spent most of a day playing/wrestling with this, and my google searches have found lots of information but no...

SQL 2005 - SELECT query takes long time to retrieve - SQL 2005 - SELECT query takes long time to retrieve records from a table which has about 250000 records. Even a...

DAC issue - I cannot connect an SQL instance using DAC from either a local machine or from a remote machine. The error...

SQL Server 2005 : Business Intelligence

How to handle mutiple outputs in xml source - Hi, I'm trying to load xml data into sql table using xml source, the file has around 6000 fileds. When i...

SQL Server 2005 : SQL Server 2005 General Discussion

call Java web service from SQL server 2005 store procedure and get response from web service in store procedure - Dear All, How to call Java web service from SQL server 2005 store procedure and get response from web service in...

Need help on creating SQL statement for deriving Consecutive worked days. - Hi all, Anyone can help me in deriving the sql statement to achieve the new column CONSECUTIVE_D? Consecutive days should be solely...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Procedure for T_SQL query optimization using Select and insert into on views - Hi friends, please see the below query.it returns 3122 rows and query is running 0.55.indexs also their and but its running...

set deadlock priority - hi, can we set deadlock priority on database level ??? please advice its urgent thanks sachin soni

blocking LCK_M_SCH_S, LCK_M_SCH_M - Hi Guru's, I am trying to under stand about no lock hint with a select statement. today when i was...

Problem with "If Exists (Select ...) Or Exists (Select ...)" - Does anybody know why the following [code="sql"] If Exists (Select * From Inserted I Join dbo.T_PaymentItemGroup PIG On PIG.PaymentItemGroupID = I.PaymentItemGroupID Join ...) Or Exists (Select * From...

SQL Server 2005 : SQL Server 2005 Integration Services

SSIS & Environment Variables - I've been struggling w/ some quirky issues lately regarding use of environment variables with package configurations in my SSIS packages. When...

Creating Header AND Footer to a flat file destination file - Hi Guys, I am working on a financial project; the requirement is interface flat file should not contain any duplicated arrangement....

SQL Server 2005 : T-SQL (SS2K5)

Getting wrong amount due to date divider - Hi, Having a hard time figuring this out. I have a query that is used for benefit deductions. We recently added...

DatePart week and year end - I have this in a stored procedure (SS2005): DATEPART(ww,Driver_summary.Dor_date) AS WeekNum When run for a period of 12/30/12 through 1/5/13, it is...

SQL Server 2005 : SQL Server Newbies

Free Datafeeds? - Hi, does anyone out there know of a free datafeeds I can avail of to build up a good end-to-end BI...

SQL Server 7,2000 : SQL Server Newbies

many to many join and union all - I'm having a hard time with this query this afternoon. I have 3 tables cash, revenue, expense. I need to...

SQL Server 2008 : SQL Server 2008 - General

Joining Table to Itself - I need to return the firstname_vc, and lastname_vc from the ar.staffmaster table of the Supervisors. A staff member in this...

How best to report changes to data - Hi all I'm looking for some inspiration on how best to report changes that have been made to records in a...

table partitioning - Hi Experts, Can we use same partition scheme and partition function for different tables in same schema ? I got 5 large...

Partitioning and the performance on writes. - Hello All, I get some results I am not expecting. The situation is something like : Full saturation test. Start with clearing the cache. 11...

Find plan_handle - Hi - If I know the executing query whats the best way to get the plan_handle such that I can look...

EXEC sql string that has table variable - Hi, Why the following code gives error? I have @temp table variable already declared, @p_SampleRecords variable already declared and assigned...

How to learn ssis (step-step) - Hi, I am totally new to SSIS, please guide me where to start learning SSIS 2008. Is there any e-book...

Change table name - Hi All, I know you can change table name using the sp_rename 'old_table_name', new_table_name', but I am more concerned about what...

In Cluster, SQL Agent fails to start, how does the cluster Act. - In Cluster, if SQL Agent fails, will the SQL Agent alone moved to another node or the entire SQL services...

Replication without need of Snapshot - Hi, I hope someonecan help me with this. We have a production server with a VLDB on it (~ 7 Billion rows, ~ 4...

Database Mail - Client not authenticated Issue - Hi, I have followed all the steps for configuring Database Mail as mentioned in the below link : http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/ While I am able...

Access Excel Hyperlinks from SQL - Hi, I have to import excel documents into a SQL table, that part is simple enough and can easily be achieved...

Script to list columnnames - Hi I often transfer data from one db to another (most 2008 but also 2005 sometimes), i have to generate scripts...

retrive the data from database date by date - hello sir i m confused about the code for retriving or displaying the data date by date from the database...

sql query to find out sql server configuration deatils? - any sql query to find out sql server configuration details 1. data and log file path 2. data and log file...

Problem with SSMS generated DDL (table with filestream) - Hi, I restored an external DB into a SQL Server 2008 R2 SP2 instance. I want to structure it (in...

find out particular query is executed in sql server? - find out : CREATE log on GRANT sp_adduser there queries are executed ( all details ) in particular sql server or instance?

Column Name change - Hi All, I have created a few tables with foreign keys and primary keys. Now i am supposed to change the...

Am not able to export 10 lakh records to excel sheet. - Hi all, Am not able to export more than 10 lakh records to excel sheet(2007 xlsx). I'll get sucess message...

Database checkbox in login mapping disappears - Hi, I'm seeing a strange issue. I want to use SSMS to assign a login to a database with db_datareader and...

Convert Non Clustered PKs to Clustered - My database that I have inherited suffers from many of the tables have PKs setup to have but they are...

DMV for capturing all SQL Statements instead of just the last - Is there a way to get from a DMV not the last statement executed by a Session but all statements...

How to stop a jobstep and go to the next in a scheduled job. - Hi fellow SQL server users, I have a SQL server agent job with several job steps. This job is scheduled and...

"USE DATABASE" in stored procedure - Need a favor..! The below Proc works fine when I execute, The Proc will generate script of permissions for the database...

Importing a csv file into a database - inserts quotes automatically into every field - We are using a SSIS package to insert a series of CSV files (saved in excel) into a database table. The...

MCTS Certificate for SQL Server 2008 - Hi all, I have a plan to get MCTS certificate for SQL Server 2008. As I knew, I must pass 3...

Date Comparisons - Other than issues with sorting and MAX, are there any potential issues with using something like CONVERT([varchar](10), datetimefield, 101) which...

Blocked Process Alert issues - This is really beginning to annoy me. I have Blocked Process Alerts on every single server in our environment. The alerts...

'not in' condition in Policy Based - Using Policy Based Management, I am creating a policy to fine databases which have not had a FULL or Incremental...

Disable TDE - Guys, What is the best way to disable TDE. I uncheck the "Set Database Encryption On property" but I am still...

SQL Server 2008 : T-SQL (SS2K8)

HOLDLOCK along with UPDLOCK - Hi, Using either HOLDLOCK or UPDLOCK as a lock hint allows other transactions to see the data, but, when used together,...

help regarding selecting the maximum value - Please find the test data as below.finding tough to come up with logic. Would appreciate greatly if some could help create table...

Execute a remote SP into a local temp table - Hello, I'm working with 2 servers both SQL Server 2008 R2 on the same Domain. One of these servers has a...

FASTFIRSTROW Hint - I've been doing a review of a DW project and noticed that several of the views use a WITH (FASTFIRSTROW) I...

Update multiple fields with different data - Hi All, I was just wondering if there was a way to update a field based on it's value multiple times...

Problemn with Right outer Join - All, I am facing issue while using Right outer join in one of my queries (explained below using the temp table).Left...

Max Date from Unnormalized Table - I have a table of dates similar to the attached. I can't change the table design (it's not my decision.) I need...

UPDATE common table expression - Hi all Can anyone advise please, I have a TSQL 2008r2 headache... I'm calculating the geo distance between a previous geo...

creating SQL login with cursor - Hi I have 100 or so users in a database but I need them all to have a server login...

Having doubt on procedure with joins - Hai friends i ve 5 tables now i wanna make a join with in that and i m done join now...

Need Result By using pivot or CTE - Hi all, I have a data like as shown below in my table. ID Name Mode ----------------------------- 1 AAAAAAA Phone 2 AAAAAAA Phone 3 AAAAAAA...

trying to query data from a mysql db table using sql server 2008 - i have the link server set up properly and had the select correct however got sidetraked and lost my query...

SQL Server 2008 : SQL Server Newbies

Query to return DateTime values after a set date? - Hi I have a MySQL query below. I needed to modify it so it returned only results from a set date/time,...

Batch move/deletion of records - I'm trying to write a stored procedure that selects 100000 records at a time, deletes from 1 table and adds...

SQL Server consuming 90% Memory - Hello Masters, My sql server has 12GB of RAM, I got alerts from windows team that SQL Server consuming about 90%...

SQL Server 2008 : SQL Server 2008 High Availability

Replace principal server in sql server 2005 database mirroring setup - Hi folks, I am a regular SSC reader but haven't yet made any contributions due to time constraints. I have a question...

Log Size Issue - How do i set the size of Log file so that it overwrites and we do not do manual tasks. Currently...

SQL Server 2008 : SQL Server 2008 Administration

Migration - Change Data Capture - How to work on CDC while migration? Do we have to setup completely from scratch or there's any other way?

Reducing Log Shipping Restore Latency - Hi, We use log shipping in our 2008 R2 databases. On one of our large heavily used databases, transaction log...

How to determine when someone logged in? - Please forgive my ignorance. I am more of a BI developer than a DBA. Is there a way to determine...

How to write continous select Statement avoiding the deadlock - Hi all, I have a requirement where in I need to select the date column of a table while it...

TempDB Space issue! - Another issue we are facing. We have mount point, under the mount point, we have data1, data2, log1, log2, tempdbmdf1, tempdbmdf2,...

Ask for help in index - Hi, I'm a beginner in index. I've table and data as following, [code="sql"] CREATE TABLE [dbo].[Zip]( [City_Cd] [varchar](100) NULL, [Zip_Cd] [varchar](10) NULL ) ON [PRIMARY] GO SET ANSI_PADDING...

Log not available error 9001 - Came in to work this morning to face a bunch of alerts for severity 21 errors. "DESCRIPTION: The log for database 'SpotlightManagementFramework'...

Career : Certification

Querying Microsoft SQL Server 2012 Training Kit (Exam 70-461) publishing delay - I've been waiting patiently for this training kit coming out for months now, expectantly waiting for it's arrival end of...

Programming : General

Insert error - convert failed - Hello, I have one script which displays current running job on the server. I want to insert this result into a temp...

Programming : Powershell

Help me Error SSIS Error (Agent Service) - An invalid reporting option was specified. Only E, W, I, C, D, P, V, and N are allowed Import Data Source...

Get SQL Query Results as E-Mail - Gurus, I am trying to write a code to send out list of failed jobs in the last 24 hours....

SQLServerCentral.com : Anything that is NOT about SQL!

Something random to ponder - So I had a funny thing happen to me a few minutes ago and couldn't help but share... QA Tester 11:54...

Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,...

Reporting Services : Reporting Services

Issue with Small matrix spilling over 2 pages - I have a small matrix that measures 3.5 on the ruler. my report is set as landscape. but it seems...

Subreport error: One or more parameters qere not specified in the the subreport... - Hi geniuses, I'm getting an error while creating a subreport inside a list. My subreport contains 1 textbox + 3 tablix, all...

Reporting Services regularly unresponsive until IIS Reset or Application Pool Recycle - Hi all, Posted a similar question recently, and thought I had found the solution, but now recently encountered it again, so...

Data Warehousing : Integration Services

Calling SSIS Package in a trigger - Hi i trying call SSIS Package from a Trigger is there any perforamnce issue to the database thanks in advance with...

SQL Numeric Datatype is saved as Text in Excel Wile tranformation. - Hi I am Using SSIS 2008, I want to Transform Sql Table into Excel File. For that I used Sql Server...

Importing one file into two database tables - I have one flat file that i import into a table using an SSIS package. In BIDS i have a...

Data Warehousing : Analysis Services

Deply aggregations - Hi, I'm using SQL server 2008 R2. Monthly a job creates new 'month' partitions. So this partitions are not in my BIDS...

Data Source table Name changed. Now Dimension Needs connecting to this new table - Hi, I have renamed a table in the data source (Id accidentally deleted it first) Now when I go into the actual...

STRTOSET - Constrained - I am running into the following error - restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated I receive...

Microsoft Access : Microsoft Access

Filter in Access- SQL - This is my sql query in access. However, I am not sure where I need to add or filter on...