In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
Download the eBook Understand Locking, Blocking & Row Versioning
Read Kalen Delaney's eBook to understand SQL Server concurrency, and use SQL Monitor to pinpoint excessive blocking and deadlocking. Download free resources.
 
Take our DB development survey $100 Amazon Vouchers Up For Grabs!
Take our short survey about database development and be entered into the prize draw! Begin database development survey.
 
Hard earned lessons by a DBA ‘Beating Backup Corruption’
The DBA Team are back, revealing more of their ‘Top 5 Hard-earned Lessons’. Lesson two is out now! Learn to beat backup corruption.

In This Issue

My SQL Server Database is Corrupt - Now What?!

How do you recover from corruption if your organization doesn't have a disaster recovery handbook? And how can you prevent the same corruption from recurring? More »


From the SQLServerCentral Blogs - My view isn’t reflecting changes I’ve made to the underlying tables.

Problem: You’ve added columns to the base table of one of your views, but the view isn’t reflecting the change. Over... More »


Editorial - Acing an Audit

I've been through relatively few audits in my database career. I've worked in a few industries that didn't require them, and avoided the stringent requirements of PCI and HIPAAISO 9000 was the first audit I encountered and I had been preparing for Sarbanes-Oxley (recently passed) when I left that company to come work for SQLServerCentral.

The preparation for an audit required a lot of work, meetings, and organization. The first time I suffered through an ISO audit, I was amazed at how much of our daily work was interrupted and the time spent ensuring we would pass the audit. The second time wasn't much better, though I'd instituted some processes and controls for the DBA group that did reduce the amount of preparation needed for our portion of the audit.

I wish that more companies I'd worked for had actually built the controls, security, and documentation into their processes. Maybe then they'd only need a 30 minute window to prepare for the audit. That's what an insurance company needed to do recently according to this piece. I found many of the rules and regulations required in the ISO and SOX documents to be ones I'd want to implement for my database systems. The hard part was getting management to agree and implement the rules as part of our daily work.

I did find it interesting that the company had built their own software to match their processes and allow employees to work efficiently. Lots of companies have struggled with the idea of becoming their own software company, but if software is truly going to be an important part of most businesses, perhaps it's a good investment for most of them.

» 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 rows get inserted into the table by this code?

CREATE TABLE Test_Table (PK BIT PRIMARY KEY, Comment VARCHAR(10));

ALTER TABLE Test_Table ADD CONSTRAINT PK_check CHECK (PK <> 0);

INSERT INTO Test_Table VALUES (0, 'row 1');
INSERT INTO Test_Table VALUES (1, 'row 2');
INSERT INTO Test_Table VALUES (2, 'row 3');
INSERT INTO Test_Table VALUES (1, 'row 4');
INSERT INTO Test_Table VALUES (NULL, 'row 5');

SELECT * FROM Test_Table;

DROP TABLE Test_Table;

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

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

Using SQL SERVER 2012

/* Version:
Microsoft SQL Server 2012 - 11.0.2218.0 (X64)
 Jun 12 2012 13:05:25
 Copyright (c) Microsoft Corporation
 Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) */

USE AdventureWorks2012

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

I create the following temporary table

CREATE TABLE #Seasons(Hdate DATE,Season VARCHAR(10))

I then execute the following T-SQL:

INSERT INTO #Seasons(Hdate,Season)  

   SELECT HireDate, CHOOSE(MONTH(HireDate),'Winter','Winter', 'Winter','Spring',   'Spring','Summer','Summer','Summer','Fall','Fall','Fall','Winter') AS Season_Hired  FROM  [HumanResources].[Employee] 

SELECT COUNT(Season),Season 
 FROM #Seasons 
 GROUP BY Season.

The questions are:  Which season has the least number of hires? Which season has the largest number of hires? (select 2)

Answer:

  • Winter season has the largest number of hires
  • Fall season has the least number of hires

Explanation: CHOOSE acts like an index into an array, where the array is composed of the arguments that follow the index argument. The index argument determines which of the following values will be returned.

Ref: http://msdn.microsoft.com/en-us/library/hh213019.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

SQL Job Timeline (Graphical)

This script generates a graphical timeline of all SQL jobs with a variable zoom level 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

SQL Connection memory usage is growing steadily - Issue: SQL connection memory usage is growing steadily (8G after only 2 days). It can keep increasing to use all...

Restoring databases created on another server with certificates - We will soon be converting our existing production databases to a a system where we use certificates on the actual...

Gettint minor error after 2005 installation - Upgraded 2000 to 2005. When I open management studio, i get below error, evn though it is opening. Access to...

Delete with TABLOCK - [b]To delete rows in a heap and deallocate pages, use one of the following methods. Specify the TABLOCK hint in the...

very high CX packet waits - i have noticed very high CXPacket waits on one of our dataware house server. All these queries are just select...

about storing sql script in a table? - Hi Can i store the database creation script in a table call the script in stored procedure to create new...

BCP native format data issue on import - I am using bcp in native format to export a products table out which has an integer primary key (NOTE it...

I wish to send results of 'query' from trigger by email to specific recipients - ALTER TRIGGER [trig_NO_LABOR_DATA] ON [dbo].[WIP_FG] AFTER INSERT AS --insert results of query into the table below INSERT INTO No_Prod_Data_Inn (job_number, fg_date, qty_scanned)...

SQL Server 2005 : Backups

Maintenance plan delete sometimes fails - I have a strange issue... in addition to our normal backups, there are a bunch of databases on our production...

backup all databases in SQL 2008 - Hi, Need a script to compress and backup all the databases in sql 2008, except system databases. Please let me...

SQL Server 2005 : Business Intelligence

Sample Insurance Data warehouse - Hi guys, I am looking for a sample data warehouse for insurance company in order to have a solid starting point. Can...

Stored Procedure explanation - Hi all, this is the first time am working on stored procedures, I need to make changes to the existing SP...

where to install the new warehouse SQL Server? - I’m starting to build the DM design for BI data warehouse; I have two SQL server 2008 databases that will...

SQL Server 2005 : SQL Server 2005 General Discussion

SQL Server taking 2 GB ram with out PAE - Dear Gurus, Can you please let me know why SQL server is taking more than 2 GB of RAM even PAE...

Help with a query - Hi, I have a query and I am not sure why it doesnt return any lines. I am building some kind...

SQL Server 2005 : SQL Server 2005 Strategies

Run stored procedure when temp table is created/dropped - Hi guys and girls, I need to update 1 or 2 records in my database each time a temp table with...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Can lots of User Connections cause high CPU use - If I use Activity Monitor I see 1400+ connections. Only 10 are "Runnable" the rest are sleeping. Meanwhile the CPU...

SQL Server 2005 : SQL Server 2005 Integration Services

SSIS Packages invoke from SQL AGent job from different SQL Server - Hello - Below is my setup : 1. 1 SQL Server 2012 [ destination server ] 2. 1 SQL Server 2008 [ source server ] 3....

passwords and package encryption - Due to unicode "upgrade", I'm looking at redoing about 20 packages. I use the wizard to generate the package. This...

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 7,2000 : Administration

Sleeping Processes - I have a question about "sleeping" processes.  We have an application that access our SQL Server.  I just took a...

SQL Server 7,2000 : General

address management software - Does anyone know of any address management software that is compliant with sql server 2000, i.e. for record duplication and...

SQL Server 7,2000 : SQL Server Newbies

Sleeping Process... - Hi, Can anyone get back to me with tips regarding this. I am gettign connection pool error while using the .Net...

SQL Server 2008 : SQL Server 2008 - General

permissions to create and run ssis - Hi all, I created database and login for one of our developer. What kind of permissions do i have to give him...

Function Not Working - How to create a computed column based on the Title nvarchar(max) column of another field: I want to make Title...

SQL Connection memory usage is growing steadily - Issue: SQL connection memory usage is growing steadily (8G after only 2 days). It can keep increasing to use all...

Query processor ran out of internal resources and could not produce a query plan. - I'm getting this error message on a query that I've put together. It queries Infor's Smartstream product for general ledger...

create synonym to schema - Hi I have a dev database and a live database both have linked servers to other databases in the dev environments and...

Partitioning key not know. - During a selection, the partitioning key is not known. Is it still possible that an partioned index is used in a...

Any chance of success? - I’m currently working on a migration project and need some feedback on whether or not this is the best way...

database restore 2008 r2 - Hi all, approximately how much time the sql server 2008 r2 will take to restore 66GB of .BAK file. Environment: windows...

SQL design discussion - help required - I'm developing a process for performing data cleaning / dedeuplication. To make the process as flexible as possible I am using...

sql server datapage storage content - hi all, Is the Data Page contains the data from a single table or multiple tables data.

Can concurrent INSERTs mess up a UNION? - Can SQL Server guarantee that if I issue a command like: [code="sql"] INSERT INTO T(...) SELECT t.a, t.b, 1 FROM sometable t UNION SELECT -t.a, t.b,...

Deattach/Attach actions made CPU utilization 100 % - Production db had the 100 GB of log file. to remove log file i did following steps. - Deattached Database - Renamed...

Stored procedure explanation - Hi all, this is the first time am working on stored procedures, I need to make changes to the existing SP...

TIME/DATE Datatypes Accuracy. - Hi all, What is the meaning of "Accuracy of 100 nonseconds" in case TIME data type in sql server 2008. And...

Inputs Required!! - Hello, I want to implement the audit trail on the daily transactions that are being performed. Please share the thoughts on how...

sql writer issues - hi.......guys. sql writer while taking backup it backup for entire volumes or it take only sql backup ??? thnx...........reply

partitioning - swapping in from smaller higher grain to lower grain - I have a table partitioned at the grain of Year. I have a second table partitioned at the grain of month. I...

ERROR SETTING FK - I receive the following error when trying to set FK The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_TRIP_DETAILS_RIDER_NO"....

UK Dates in Tab delimited file not importing correctly. - I'm sorry if this has been raised before. I have searched but have not found what I'm looking for. I have...

Unexpected behaviour of query - Hi All, I am using below query in sql server to find out top 5 amount and date by customer. I...

The CREATE TABLE SQL construct or statement is not supported. - Hi All, CREATE TABLE #Scope ( JobID BIGINT ,ListID INT ,BatchID INT ,SendStartTime DATETIME ,SendCompleteTime DATETIME ,PRIMARY KEY CLUSTERED (JobID, ListID, BatchID,...

Deadlock Issues (Why Certain Locks?) - I am having some problems with frequent deadlocks in a vendor-supplied database and I have been analyzing the database and...

Procedure is taking 15 seconds after service restart but from second time procedure takes 1 sec - Hi, I have one store procedure which is taking 15 seconds after restarting the SQL Server Service. But when I try...

Group By - Help - Hi geniuses, I need some help with a query I need for a report, here's what my table contain: Project | ReceiverGroup...

Distribution cleanup job failing - transactional replication - Since yesterday afternoon I've been seeing this in the Agent History: [quote]Executed as user: Domain\User. Time-out occurred while waiting for buffer...

SQL Server 2008 : T-SQL (SS2K8)

TSQL Query help - I have table from November with the flag 'N' and 'Y' ,when I get the laons for december , need to...

Pulling errors from a linked server - Hi all, I've got a linked server to an attomix database. When i send an erroneous statements to the server an...

Order of JOIN in multiple table joins - I want to know how order of joins executed in sql server. Suppose i have used 3 tables in join. table1...

what to place in Stored Proc sysname parameter - I have a table that my company uses for logging. One of the columns is declared as: event (sysname(nvarchar(128)),not null) We...

Inactive Extended Event Sessions - To check if an extended event session with a name exists I use the following statement: [code="sql"]SELECT 1 FROM sys.dm_xe_sessions WHERE...

Dynamic Grouping - Hey all, I had a query that i didnt like - as it was a pain to maintain. Basically there are 6...

Error On while executing to insert image on it??? - create table Wishing ( ID int identity, ImageName varchar(500), ImagePath varbinary(2000) ) insert into Wishing(Imagename,imagepath) values('sunrising','images/sun.jpeg') while i am inserting image to to table showing error on use...

Bulk Insert from file to table - Hi, I have .dat file with following format data. 1,2,3, 4,5,6, 7,8,9, i want load this format file into sql table using bulk insert...

IN or LEFT OUTER JOIN which is better ? - Hi, I am confused. Pls answer my question. should I use IN or LEFT OUTER JOIN in my sql query ? and why...

WHERE vs. GROUP BY again - The following two T-SQL statements return exactly the same row set whatever data is: [font="Courier New"]SELECT [i]something [/i]FROM [i]somewhere[/i] WHERE [i]condition[/i]...

t-sql 2008 r2 error message - This is the first time I am writing a trigger. I am getting the following error message on a sql...

Update works on some systems but on few others - Hi All, I have some update and insert queries to update GEO location data, which when I execute on my system...

Stuck with PIVOT - Hi, To simplify the problem, I have JOINed 3 tables together to create a new table with the following output: [img]https://sites.google.com/site/facesmapicons/PIVOT.jpg[/img] I...

SQL Server 2008 : Working with Oracle

After Insert trigger - Hi , I have a requirement where i have a table (EMP) with fields id, name, status What i want is after...

SQL Server 2008 : SQL Server Newbies

changing a sql 2005 developer license to production - I have a windows 2003 server running sql 2005 with a developer license installed. It's now in production. I have...

Pivot results not as expected - I have a select statement as below: SELECT Institution_Number, Attached_Account_Number, [1], [2], [3] FROM ( SELECT CARD_Attached_Acct.Institution_Number, CARD_Attached_Acct.Card_Number, CARD_Attached_Acct.Portfolio, CARD_Attached_Acct.Attached_Account_Type, CARD_Attached_Acct.Attached_Account_Number, Inquiry_Name_To_DDA.Name_ID, Inquiry_Name_To_DDA.Name_Line FRO

query to list linked server user - Hi How do i query the SQL server to list the "remote login" of a linked server? it sounds so simple but...

Trigger on insert - Hi , I have a requirement where i have a table (EMP) with fields id, name, status What i want is...

Property Collation is not available for Database - Backup job failed last night - viewing the history logs gave the following message "Could not find database ID 15. Database...

how to attach database with variable path by storedprocedure?? - please help me, how to attach database with variable path??

Stored procedure very slow execution - Hello, Please help me, I'm desperate. I have an old SQL2000 (SP4) - and no, I can't upgrade it, is in my...

SQL Server 2008 : SQL Server 2008 High Availability

Peer-to-peer replication and identity columns - All of the articles regarding identity columns for peer-to-peer replication talk about setting identity ranges when writing to multiple members...

SQL Server Clustering Questions - Hi, Can someone provide few important interview questions on clustering with answers?

SQL Server 2008 : SQL Server 2008 Administration

Red Gate Restore Failed - Hi Experts, I got below error when tried verfying backup. Got the exact error when tried to restore DB earlier. Please...

Restoring db's with certificates in another server - We will soon be converting our existing production databases to a a system where we use certificates on the actual...

Slow Reports - Index Update - Good Morning Everyone, Firstly I hope everybody is having a nice morning :-) I have a question that I hope someone can...

migrating dts 2000 packages to ssis 2008 packages - can please any one tell me that step by step process of how to migrate dts 2000 packages to ssis...

Red Gate SQL Monitoring fails - I just started a new DBA job and was introduced to SQL Monitoring from Red Gate. The program is not...

checkdb error - Hi experts, CHECKDB threw error Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'. The duplicate key value is...

Database sizing - I know how to estimate the size of a table (number of bytes stored per row X number of estimated...

Programming : General

Add SQL Objects to TFS/ Version Control - I work for a small company and we are trying to get all database objects in to TFS. Our dot...

SQLServerCentral.com : Anything that is NOT about SQL!

Web application design query - Hello, I am new to web application designing and using .net (MS Visual Studio 2010) to design an application. The issue I...

Data center outside US? - We currently host our own (aging) hardware on site and are considering moving to the cloud. Our consultant suggested a...

Reporting Services : Reporting Services

Another SSRS Conditional Formatting Question - Afternoon all... now ive been using SSRS for about 6 months and am becoming used to all the functions within it...

SSRS 2008R2 GROUPING - I have add multiple goups in ssrs 2008 r2.My problem is when i try to add a group 1 extra...

Refer out of scope expression - Here is my Expression that is trying to refer something out of scope: =iif(Fields!fee_name.Value<>"monthly" , StrConv(Replace(Fields!fee_name.Value,"_"," "), VbStrConv.ProperCase) , iif(ReportItems("Text58").value > Fields!fee.Value , StrConv(Replace(Fields!fee_name.Value,"_"," "), VbStrConv.ProperCase)...

Reporting Services - Multiple Page Breaks Issue - Hi All, I have a unique kind of requirement explained as under. I have two table controls on report design....

Database Design : Design Ideas and Questions

Policy database Design - i have enclosed here sheet which contains column's list . can anybody help how can i seggregate them into different tables...

Suggsted approach to masking - We have a reporting system that I present multiple databases to (i.e. for multiple clients). Our UAT & DEV source systems...

database design help please - Hi I am having some trouble on how to normalize some tricky tables. Basically it is a recurrence event table that...

Data Warehousing : Integration Services

SSIS iSeries Source Duplicates Records - I am creating a package to read data from our production iSeries (AS/400) and copying it into our new data...

Speed up simple SSIS load - Hi I just published a new post on SQL Server Central [url=http://www.sqlservercentral.com/blogs/katie-and-emil/2013/01/28/ssis-6-million-rows-in-60-seconds-using-standard-laptop/]SSIS 6 Million rows in 60 seconds using standard laptop[/url]...

where to install the new warehouse SQL Server - I’m starting to build the DM design for BI data warehouse; I have two SQL server 2008 databases that will...

SSIS, creating columns from rows based on id from result set - I have the following and is getting the attached error. I am trying to loop through a table and change...

Generic SSIS Package Creation - Hello, I have an Idea and want to see if this can be possible or any real time Developed solution already...

Data Warehousing : Strategies and Ideas

Business datasets (overview, structure, naming conventions, business rules) - Hi Guys I was wondering if someone could point me in the right direction. I need to gather information about different datasets...

Data Warehousing : Analysis Services

Computing on a cube? - Hallo, I'm totally new on computing on SSAS, and quite new to SSAS in general. I'd like to get some index...

SSAS CUBE PROCESSING ERROR - I am new bie trying to learn SSAS. I have these errors in trying to process my cube after it...

Testing Center : Question of the Day (QOD)

connections sleeping - i have a doubt, when i execute sp_who the display send me de current conections, but almost case there are...