A community of more than 1,500,000 database professionals and growing

Advanced Event Behavior – Level 10 of the Stairway to Integration Services

Andy Leonard from SQLServerCentral.com

The tenth article of this series starts to look at more advanced events and how we can use those events to better control and enhance our packages. More »

What MAXDOP Setting Should be Used for SQL Server

Additional Articles from MSSQLTips.com

I have a busy SQL Server and notice that several queries are running in parallel. I know I can set the max degree of parallelism setting, but what MAXDOP should I use? More »

From the SQLServerCentral Blogs - Tip: OVER and PARTITION BY

Jen McCown from SQLServerCentral.com

For the month of December, I’ll be taking a little holiday from blogging. In the meantime, enjoy a few of... More »

I have an encryption talk that I give and usually find a few people in the audience that have implemented encryption. In almost every case this has been because of PCI or HIPAA regulations that dramatically reduce penalties if data is encrypted. Whether you agree with the regulations or not isn't important. There are rules that some of us have to follow because of our data and my guess is that the number and scope of those rules will increase in the future, not just in these industries, but others as well.

If you are covered by HIPAA law, you may have gotten some increased scrutiny this year. There are audits underway from the Office of Civil Rights (OCR) for 115 organizations that will help them to ensure they comply with regulations. Penalties aren't supposed to be assessed unless there are serious violations, but starting in 2013, the  Health Information Technology for Economic and Clinical Health (HITECH) Act requires that the auditing program will be enforced with surprise audits.

For those managing health care data, you should be sure that you are complying with HIPAA regulations. If you're not, you ought to make sure your boss is aware that next year you could have a surprise audit and should be ensuring that you meet the laws regulations. The OCR has released their audit protocol, and you should be sure that you understand what is being evaluated.

If you aren't regulated by PCI or HIPAA, you might still check over the protocol as much of it is good practice for securing any data. It can be general, but if you abide by the spirit of the criteria, I'd bet that will pass an audit by your security group.

Steve Jones from SQLServerCentral.com

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

• Watch the Windows Media Podcast - 16.6MB WMV
• Watch the iPod Video Podcast - 18.5MB MP4
• Listen to the MP3 Audio Podcast - 3.7MB MP3

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

Today's Question:

How many queries does the following code produce?

Begin Transaction

   SET QUOTED_IDENTIFIER ON;

   SET ARITHABORT ON;

   SET NUMERIC_ROUNDABORT OFF;

   SET CONCAT_NULL_YIELDS_NULL ON;

   SET ANSI_NULLS ON;

   SET ANSI_PADDING ON;

   SET ANSI_WARNINGS ON;

COMMIT

Note: This has only been tested in SQL Server 2008.

By Dave62

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.

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.

What is the output of this query in SQL Server 2008. Assume that getdate() is today's date.

create table #test(id int,EmpName varchar(50),dateofjoin datetime default getdate())

insert into #test
select 1,'malli',null
union
select 2,'reddy',' '
union
select 3,'test',getdate()

select * from #test

drop table  #test

A)

B)

C) Error

By malls reddy

Answer: A

Explanation: When we give a default datetime it will use 1900-01-01 00:00:00.000 time as the default datetime.

Ref: Datetime - http://msdn.microsoft.com/en-us/library/ms187819.aspx

» Discuss this question and answer on the forums

Pro SQL Server 2012 Practices

Expert SQL Server Practices 2012 Edition is an anthology of high-end wisdom from a group of accomplished database administrators who are quietly but relentlessly pushing the performance and feature envelope of Microsoft SQL Server 2012. With an emphasis upon performance—but also branching into release management, auditing, and other issues—the book helps you deliver the most value for your company’s investment in Microsoft’s flagship database system.

Get your copy from Amazon today.

Query to get Database Size and Growth Report

Santosh Kamble from SQLServerCentral.com

Query to get database size, growth report. More »

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : Administering

SQL Server services failing - Please assist my SQL Server service is failing to start after power cut. TITLE: Surface Area Configuration ------------------------------ An error occured while performing...

Rebuild the log file to reduce VLFs - Hi all So I've been poking around one of the databases and as part of my ongoing health checks, I reviewed...

sql server memory usage - does it ever use page file? - Lets say my physical box has 64 gb RAM and out of that 52 gb is assigned to sql server....

Unable to start SQL agent with domain account - Hi, I've recently changed our SQL services to run under domain account instead of the local account they used to use....

Isolation level using linked server - Our situation: - our EPR system uses Progress database (blackbox for me, don't even know where it is installed) - on a...

SQL Server 2005 : Backups

SnapManager for SQL job - Hi....I am new to Snapmanager for SQL jobs trying to find a solution for my issue. I run the SnapManager...

SQL Server 2005 : Business Intelligence

Dynamic columns SSRS - Please check the attachment.in my SSRS report i can hide/display Months columns based on input parameter "columns. "Total" column should...

Connection Manager Issue - Hi All, while am to test the connection of my package, getting an error message: The connection Failed bacuase of an...

Run Multiple SQL Queries - I am new to SSIS so need help with some issues. Creating a task which involves running multiple sql queries...

SQL Server 2005 : Data Corruption

BACKUP DATABASE is terminating abnormally - Dear All, Could someone please advice on this. Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally. Msg 845, Level...

SQL Server 2005 : Development

Copy DB Tables from PRod to DEV - Hello, I have situation that my developer wants some tables from Prodction DB server to their desktop m/c. We have already sql...

multipart identifier could not be bound - create table s(sid int, sno int) create table t(sid int, sno int) insert into s(sid,sno) values (1,1001) insert into s(sid,sno) values (2,1002) insert into...

SQL Server 2005 : SQL Server 2005 General Discussion

Regarding stored procedure - alter proc procstudent1 @proctamil int as begin update student set tamil=@proctamil where studname='mani' alter table student add place varchar(30) end execute query: exec procstudent1 @proctamil=57 error: Msg 2705, Level 16, State 4, Procedure...

Regarding stored procedure - I am using two sql statements in stored procedure, the two sql query is update and alter. in some times i need...

Cannot retrieve data from the database - Here is the code I am using to retrieve data that I have seen on the database: <!-- #INCLUDE FILE="sqlconnect.asp" --> <% Dim...

HI can any one say what is the use of these in detail - HI can any one say what is the use of these 1 SET SHOWPLAN_ALL ON 2 SET SHOWPLAN_ALL OFF 3...

A very tricky query--please help - I have a table as follows id firstname lastname 1 Jay Jones 2 Nancy Cartwright 3 Carlos Ruiz 4 Steve Shepperson The other table is as...

SQL Server 2005 : SQL Server 2005 Performance Tuning

need one doubt clarification application team trying to execute a simple query - hi all need one doubt clarification application team trying to execute a simple query but its taking long time i tried evry thing suppose if...

SQL Server 2005 : SQL Server 2005 Integration Services

Conversion of DTS 2000 Packages to SSIS Pcakages in SQL 2008 - Hi , I have 65 DTS:2000 packages in our project. I just want to convert all these packages to SSIS packages...

Data Import from CSV file - I have a CSV file which is having record like mentioned below. [code="other"] UserName User Id Type RamKumar ram MSAD RamaC rama MSAD RamesK rames MSAD Rameshv ramesh MSAD RamKumar ramp PCG Ramig rami ...

SQL Server 2005 : T-SQL (SS2K5)

append sp - Hi, not quite sure if this is info info...but i wanna convert this sp to an append sp. [code] with rows as ( select...

SQL Server 7,2000 : T-SQL

select query for overlap date range!!! - Hi can any one please help me in giving the solution with an sql statement which returns the overlapping date ranges here...

SQL Server 2008 : SQL Server 2008 - General

how to create a flat file from SQL tables - Hi, I am trying to create a flat file from a database. I am just learning how to work on sql...

Use 'case-when' But error conditions overlap in query. - I try 'case when' with 'null' and 'not null' set to value 'ErrorCode' field. [b]Ex.[/b] [code="sql"]EmpNo|ChkDate |ChkIn |ChkOut |ErrorCode 00001|2012-10-01 00:00:00.000|2012-10-01 07:21:00.000|2012-10-01 17:05:00.000|0 00002|2012-10-01...

Specify multiple file extensions in maintenance cleanup task - Is it possible to specify more than one file extension in a single Maintenance Cleanup Task or do you have...

How do I lock Queries - hi How do I lock Queries?

Transfer Data from Table to TextFile - how to transfer data from table to textfile with upto date range.

Would you store foreign key from grandparent tables? Why or why not? - I'm curious about potential ramifications of storing a foreign key that is derivable. For example, consider that we have traditional 3...

New login & Access to DB - Hi Friends, i have asked to perfrom a task to create a New login & then provide the access rights to a...

Logging field changes in table using triggers or service broker - When someone changes a value in a table I want to retain the old value(s) for a time in a...

Logical Query Processing - Dear all, It was my understanding that when processing a query the order in which it is processed is FROM, JOIN,...

TSQL Problem: Week between 2 different months - Hello, I have a script problem. I calculate data per week (group by week) However, for indicators, I have business rules which apply...

SQL Server responds to overwhelming network traffic by shutting down - Dear Everyone, We had a network issue this morning, which resulted in extraordinary levels of network traffic for a short while....

BUFFER I/0 - My Sql Server Buffer I/O wait is very high I have 390 GB RAM I set the sql server min 300 gb...

Huge difference for same operator in different environment - [font="Tahoma"] We have the same query performing differently in DEV and QA environment with 99% identical data. DEV was restored from...

Database Backup Strategy - Dear, Currently we are taking full database backup twice. There is no log backup in our plan. If transaction log grows...

More or less SARGABLE clauses in a WHERE Clause - Question - Is it better to have more SARGABLE Clauses in a WHERE Statement OR less to find a record(s). Is it...

Database Mirroring Testing & Configuration - Dear, 1. I want to configure database mirroring with automatic failover option. Now I backup database and log file from Principal....

Alter TempDB !! Same disk or seperate ?? - i read an article [url]http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx[/url] where this was quoted [quote]Depends why you're adding files. If you're adding files just to...

Log File Reuse Reason Explain - Dear, If I execute the following query, [code="sql"] select name, log_reuse_wait, log_reuse_wait_desc from sys.databases[/code] The result looks alike, log_reuse_wait|log_reuse_wait_desc -------------- ------------------- 2 LOG_BACKUP Now my query is when does...

bringing consecutive days on the same row as start date and end date - I want group data so that the consecutive days for same Location and Contac tName come on one row as...

Shrink database after archiving - We've archived massive chunk of data from two databases on a SQL instanace. Now want to shrink the database to...

How to join multiple rows of subtable to parent table - I have 2 related tables and the second table can have many rows associated with the parent table. I need...

Detect change of month and year in datestamp - I have a table with datestamp entries as below SELECT st.startup_Time FROM #KWHtemp st OUTPUT: 2011-12-31 14:28:37.000 2011-12-31 15:11:04.213 2012-01-01 14:28:37.000 2012-01-01 15:11:04.213 ................................... ................................... 2012-02-02 07:21:54.423 2012-02-02 07:37:10.180 (the...

Calculated for the two columns of the table - I have two columns. Name column 1 is F1 Name column 2 is F2 I would calculate two columns And the third field to...

Amount of data - I was wondering if there is a way to "trap" or track the amount of data (in bytes) that a...

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

how to write a stored procedure to check if the members membership has expired - i have a table named users where i have two three column userid, email, membershipexpiry i want to write a stored...

LIKE with and without wildcards in WHERE clause - I have a set of pretty well tuned queries with indexes and all the appropriate trinkets. This is a 'last...

SQL Trace causes application to hang - Hi, We've recently upgraded to Windows 2008 R2, SQL Server 2008 R2 SP + CU4, multi-instance cluster. 32 cores (64 with h/t),...

Iterating thru a Tables columns - I have a dataset that i need to iterate thru in a row wise manner where each row has columns...

How to check mirrored database - Dear, I have implemented database mirroring with automatic failover option. My principal database is a busy one. Now I am not...

rebuild index worsens query performance - we have a query which runs several times a day reads from several tables in one database ("W"), and two tables...

NOLOCK Hint Corrupts Results from SELECT - Good day fellow SQL Server Enthusiasts, I'm having an issue with the NOLOCK query hint causing erroneous joins. In a nutshell,...

Merge replication changing the distributor - I have a client who has a single SQL server that is currently their OLTP database server and is handling...

Master Data Services Complicated Business Rules - We are investigating the use of Master Data Services at my company. One of the stumbling blocks I've run into...

SQL Server 2008 : T-SQL (SS2K8)

Sql Query - get most recent data and put record numbers into columns - I am not sure exactly how to phrase what I am asking so thanks for looking and I will try...

Delete records - I have table with 20k records. I want to delete 20k-1000 records from the table. i.e keep the top 1000...

Update null to unknown in a table for all the columns - Hi, i need query to update all the columns which ever carrying null to 'unknown' using a single query or procedure...

read UNCOMMITTED run slower than read COMMITTED - Hello I have the following query which runs considerably slower when we change the isolation level to read uncomitted, I just...

Update statement question - I'm doing a DB review and have always been taught to write updates with a Join like using this syntax. [code="sql"] Update...

Exercises in t-SQL - I'm in the process of setting up a training environment here, it's a mechanism for me to bring high flyers...

SQL Server 2008 : SQL Server Newbies

How to create a marker field based on data - I have a dataset for students and they have either selected 'Math' or 'Science' or both. So I have 2 rows,...

Alternative to views - Databases in our servers communicate with each other using views. For instance, dbA has views that gets data from db2...

SQL Server 2008 : Security (SS2K8)

Limit Concurrent Logins by database and/or user ID - In my environment I have a sql server 2008 database on 64bit architecture. The production database accessed by users is...

SQL Server 2008 : SQL Server 2008 High Availability

Cannot Repair or remove node - I have a 2 node cluster. When I move the instance of SQL from node B to Node A, SQL...

Sudden failover in veritas cluster - hello experts, In one of my critical production erver veritas clustering configured, yesterday sundden failover happened and now its runing on...

How to change the subject of alert email in SQL SERVER 2008 R2 - Hi Everyone, I have set up some alerts for mirroring monitoring, and in case of failure we recieve notification emails.Is there...

Mirroring configure HA and DR - Hi, Database have been configured High availability in same datacenter by using database mirroring.. For the anohter requirement the same principal database...

SQL Server 2008 : SQL Server 2008 Administration

Cost threshold for parallelism - Hi all When dealing with the Cost threshold for parallelism, I am almost certain that the [i]Cost[/i] is not an amount...

user permission - Hi, Here is the database settings: There is domainName\users unders security\ logins. db_owner is NOT checked for database1 Then I go to database1, security\users. I...

sys.configurations missing 'backup compression default' - Hello, We have a SQL 2008 database with compatibility level set to 80. I don't know if this is the cause...

need script to set Port number - need script to set Port number .

windows cluster ip Configuration. - hello experts, i want to configure cluster in my laptop (8GB RAM, i7, 1 TB disk), so i have installed VMware...

witness server - with out witness server can we set up mirroring?

Setting Notification email (When the job fails) - Hi Everyone, I have setup a job(SQLS ERVER 2008 R2) in two servers Server1.Domain1 & Server1.Domain2 and I have enabled notification email...

SQLServerCentral.com : Anything that is NOT about SQL!

VB script error - Hello, I am trying to upgrade an SSIS package to 2008. The package includes a Script task editor with the following...

Fantasy football 2012 - Only 2.5 short months to football, can you believe it? I'm not ready, hardly feels like it's summer time yet. You...

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,...

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

my dataset not showing any columns - HI all i am trying to create a report with data from a stored procedure, i created a stored procedure...

Eliminating empty output files in SSRS subscription - I have a report with a subscription in SSRS. This subscription post by mail a pdf file. When i have an...

Restrict Date Parameter - Hello, Is there any way i can restrict calendar ( Parameter )to go back 1/11/2012. You can't choose date before 1st Nov,...

Generate one PDF per record - Hi all, I've built a report which displays data from a db. This works fine, rendering the report correctly. My query returns...

Forward Dependencies are not valid. - I am working on reports from our HR system that allow a manager to only see their own direct reports...

format date within reporting services - I need to display a date in uk format without the time portion. My report runs from a AS cube...

Database Design : Design Ideas and Questions

Lots of Key Lookups vs. UniqueIdentifier Clustered Index - Hello. I'm working on a system that was originally designed with UUID (uniqueIdentifier) clustered indexes. Later the clustered indexes were rebuilt on...

Data Warehousing : Integration Services

Best Way to Migrate Data from Different DB SQL Servers - have data like the following: : PK Desc InsertedByUserID UpdatedByUserID 1 Johnny 13 90 2 Frankie 34 75 3 Brenda

SSIS Flat File Destination Formatting to add a carriage return - I have an SSIS package I am building. The result is a flat file created from a sql query that...

error when creating SSIS Packages - Hi i have a task to develop SSIS packages, i've installed SQL Server 2008 R2 Developer and installed Intergration Services but...

(Rebuild index) or (drop and recreate index)after ETL Fact Load - Hi, (Rebuild index after the fact load completed) or (drop index before load starts and recreate index after fact Load ends)...

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...

Testing Center : SQL Server Security Skills

How can I unencrypted a stored procedure - I got few stored procedures With Encryption for the database that I converted to SQL Server 2000.   I need to...