In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Developer Bundle 12 essential tools for database professionals
The SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.
 
SQL Backup Pro Gold Community Choice Award for SQL Backup Pro
Try award-winning SQL Backup Pro for faster, smaller, fully verified SQL Server backups. Download a free trial now.
 
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

Using the Script Task in SSIS to Process Data Files When They Arrive

Learn how you can process data files with SSIS as soon as they arrive using the Script Task. 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 »


Partitioning in SQL Server - Part 2

Arshad Ali examines the different concepts of partitioning in SQL Server 2012 and provides a step-by-step guide on creating a partition table/index. More »


From the SQLServerCentral Blogs - Day 5 of 31 Days of Disaster Recovery: Dealing With Corruption in a Nonclustered Index

Day 5 of 31 Days of Disaster Recovery: Back That Thang Up 31 Days of Disaster Recovery Welcome to day 5... More »


Editorial - Statistical Protection

The things people can do with data is amazing. I remember reading about the anonymous data set released by Netflix and how some of the people were identified based on other, related actions on the Internet. This de-anonymization, while scary, was amazing to me. There have been other, related reports of similar "attacks" taken against other data sets. These reports worry me that we will have more and more data security issues in the future, not less.

I ran across an article that talked about protecting data in statistical databases. These are the databases that contain data from multiple sources, and are used to analyze the information from these sources. The security of these databases becomes important when the data contains information about individuals that we consider sensitive. Interestingly enough, it seems that the security protections being used are query restrictions.

However these restrictions are the reverse of what we might expect. There might be minimum restrictions on the number of rows returned, to try and prevent information about a specific individual from being returned. There are also limitations on the types of queries that can be run, usually requiring aggregate functions in the query, and restricting which aggregates are allowed.

This is definitely an area of our industry that needs more work and research. Lots of organizations, especially government organizations are being called on to open their databases up to the public, and many of them are doing so right now, allowing queries of their statistical databases. This might improve the use of this information by the public, but there are plenty of ways in which this data could be potentially misused. If your companies wants to open some of your data to clients or customers, you might raise the concerns with possible abuses of the database and ask that time and effort be included to try and secure the data, possibly by implementing query restrictions.

Steve Jones


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:

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

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


Question of the Day

Today's Question:

Here are some declarations:

set nocount on
declare @i int, @n numeric(10,4), @d datetime, @v varchar(12);

These declarations are followed by a single select statement in the same batch; the declarations work, but the select statement fails.  Which of the following statements could be the failing select statement? (choose 3)

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.

SQL Server 2012 Integration Services Design Patterns

SQL Server 2012 Integration Services Design Patterns is a book of recipes for SQL Server Integration Services (SSIS). Design patterns in the book show how to solve common problems encountered when developing data integration solutions. Because you do not have to build the code from scratch each time, using design patterns improves your efficiency as an SSIS developer. In SSIS Design Patterns, we take you through several of these snippets in detail, providing the technical details of the resolution.

Get your copy from Amazon today



Yesterday's Question of the Day

I execute the following T-SQL statement (Statement #1)

USE AdventureWorks2012

CREATE TABLE #r([Rank] INT,TotCnt INT)

I then execute the following T-SQL statement (Statement #2)

INSERT INTO #r 

SELECT RANK() OVER(ORDER BY TotCnt DESC),TotCnt

FROM (SELECT SalesOrderID, Count(*) AS TotCnt

FROM SALES.SalesOrderDetail GROUP BY SalesOrderID) AS Cust

I then execute the following T-SQL statement (Statement #3)

SELECT TOP(10) [Rank],TotCnt FROM #r ORDER BY [Rank]

I then execute the following T-SQL statement (Statement #4)

DROP TABLE #r

I then execute the following T-SQL statement (Statement #5)

CREATE TABLE #r([Rank] INT,TotCnt INT)

I then execute the following T-SQL statement (Statement #6)

INSERT INTO #r 
 SELECT DENSE_RANK() OVER(ORDER BY TotCnt DESC),TotCnt
  FROM (SELECT SalesOrderID, Count(*) AS TotCnt
        FROM SALES.SalesOrderDetail GROUP BY SalesOrderID
        ) AS Cust

I then execute the following T-SQL statement (Statement #7)

SELECT TOP(10) [Rank],TotCnt FROM #r ORDER BY [Rank]

DROP TABLE #r

The question is:  What select (Rank or Dense_Rank) returned the following:

Rank   TotCnt

1      72

1      72

3      71

3      71

5      68

6      67

6      67

8      66

8      66

8      66

Answer: Rank

Explanation: For Dense_Rank: http://msdn.microsoft.com/en-us/library/ms173825.aspx/a>.

If two or more rows tie for a rank in the same partition, each tied rows receives the same rank. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number two. This is one more than the number of distinct rows that come before this row. Therefore, the numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks.

For Rank: http://msdn.microsoft.com/en-us/library/ms176102.aspx

If two or more rows tie for a rank, each tied rows receives the same rank. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number three, because there are two rows that are ranked higher. Therefore, the RANK function does not always return consecutive integers

» Discuss this question and answer on the forums

SQL Server 2012 Integration Services Design Patterns

SQL Server 2012 Integration Services Design Patterns is a book of recipes for SQL Server Integration Services (SSIS). Design patterns in the book show how to solve common problems encountered when developing data integration solutions. Because you do not have to build the code from scratch each time, using design patterns improves your efficiency as an SSIS developer. In SSIS Design Patterns, we take you through several of these snippets in detail, providing the technical details of the resolution.

Get your copy from Amazon today



Featured Script

AUTOGENERATE INSERT UPDATE SELECT STATEMENTS UPDATED

I have made an update of this script http://www.sqlservercentral.com/scripts/Metadata/64603/. the new version includes can generate 4 stored procedures for every table in a database 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

create report with date suffix - I need to create a report & run weekly to output to file on server with filename_date×uffix.txt. The original report (which is...

Error 26073 - Hello, We are also getting similar error on our SQL server in cluster environment Error 26073 TCP connection closed but a child process...

Creating Trigger on View - Dear, I have a view that is comprised of multiple tables containing only today's data. I used only select statement in...

SSIS Problem With Checkpoint Settings - What can be the cause for the following scenario? SSIS package executed by a job fails with the following error "The package...

sql auditing triggers - I have to write DDL trigger in sql 2005, using CLR feature. Can anyone provides help in this regard

SQL Server 2005 : Backups

Backup Log Incorrect Function Message (nonrecoverable I/O error). - We started receiving this message mid-last week: [b]Msg 3271, Level 16, State 1, Line 4 A nonrecoverable I/O error occurred on file...

SQL Server 2005 : Development

Qry - In my table i have following result sets sno 101 102 103 105 106 107 i want following structures 101,102,103,105,106,107

SQL Server 2005 : SQL Server 2005 General Discussion

Dynamic Where Clause for Multiple paramaters with AND Operator - Hi I am Trying to Build Dynamic Where Clause for Multiple Parameters and they could be Null Also .Please find...

using sys.dm_exec_sessions , where to find database name/id - im using sys.dm_exec_sessions to show me all current sessions ( instead of using sp_who2). the problem is that sys.dm_exec_sessions doesnt have...

Copy tables from one database to another - Anyone know how I can easily copy selected tables from one database to another? (different servers). In Enterprise Manager it was...

SQL Server 2005 : SQL Server Express

SQL Express 2005 stoppped running on win xp sp3 - Hi, we have ms sql express installed on a computer which we use as server and it is on LAN. Before...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Multiple Optional Parameters - I am in the process of creating a stored procedure which will be used for searching database tables. The user can...

SQL Server 2005 : SQL Server 2005 Integration Services

SSIS XML Destination - Hi guys, Using SSIS pacakge i want to get the XML format dataset output from the OLEDB soure data Is it possible?...

Why Use SSIS? - Basically the issue is- If i run t-sql srcipts, to pull data from the staging area, remove duplicates, validate , data and...

SQL Server 7,2000 : Administration

xp_sendmail not sending @message text - xp_sendmail will send out the email with correct to address, correct subject line but the message text is blank. This...

Loading Trace Files To A Table - Hello, We run profiler traces for auditing logins and i am using server side tracing and everything works great. I just want...

SQL Server 7,2000 : SQL Server Newbies

Analyst seeking wisdoms while learning the platform - Im a business/financial/data analyst who wishes to make a transition to a BI solutions development/BI information delivery role specializing in...

SQL Server 7,2000 : Performance Tuning

Transactions Per Second - Hi I'm doing some performance monitoring and was wondering if someone could shed some light on this counter? In particular...

SQL Server 7,2000 : T-SQL

sp_MSForeachdb then sp_MSforeachtable - Last posting of the week for me. Trying to get this to work and it's giving a syntax error. I'm...

BCP error -Unable to open BCP host data-file - SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file NULL   I am getitng the above error when i...

SQL Server 2008 : SQL Server 2008 - General

Excel Database Tasks - Hi, just thought I would post an update on EDT - Excel Database Tasks. After so many hours of development Version 2.3 now...

Unidirectional Merge Replication Pub to Sub - I have a 2008 R2 Standard table that I want to merge into a master copy on another server. The...

Index usage and RID lookup. - Hello All, An index with three fields, A,B,C ( A selection with SELECT * FROM A_TABLE WHERE A = 6 AND C =7 performance question: [b]Does this...

sql server 2008 r2 trial problem - I have some problem, last admin was installed 2008 r2 enterprise trial. Today SQL has been stopped, because trial is...

Restoring database from DPM snapshot - Hi there, We are currently reviewing our backup routines/recovery plans as we found that we were making a number of duplicate...

different reales of sql server 2008 - I want to know the actual difference between sql 2008 R2 and Sql server 2008 .which one is preferable to use. also...

Generate Months from unique records with different date ranges - Hello I have a database that records contracts and their amounts (each contract has a start and end date); each contract...

configuring Loghsipping in 2k8 - Hi, If anyone knows configuring the logshipping using scripts instead of GUI in slq server 2008.. Provide scripts..

Phd Suggestions - Hi, Planning to go ahead with Phd in Database Admin, Kindly provide some suggestions on it... More efficient topic for research...

NULL VALUES - My client "My 1st" has a sql database with many, many null values in each table. Its because they do...

I NEED HELP WITH SQL EXPRESS 2008 R2 - I am a newbie just been doing this for about 3 mo's. I installed sql and all went well. During setup...

Column Name Case Sentive - Hi, im Matias from Argentina, i need to know if exist a way to change the collation of the TABLE column...

Sequential YYYYMM calc problem - First the code... [code="sql"] DECLARE @Current INT DECLARE @Prev1 INT DECLARE @Prev2 INT DECLARE @Prev3 INT DECLARE @Prev4 INT DECLARE @Prev5 INT DECLARE @Prev6 INT DECLARE @Prev7 INT DECLARE @Prev8...

Event ID for drop db user event - Hi all, is there an Event ID that gets written into Windows log when the db user is deleted? The only...

Index fregment doesn't come to 0 - Hi, I have just shifted my database of sql 2000 to sql 2008 64 bit after running Upgrade adviser. then i run...

Need help using the most efficient aggregation method in SQL Server 2008 R2 to sum up child rows in same table - I need to find a way to do an INSERT into the same table (named ResultsHistory) by summing up the...

Function taking long time - Hi, I have given a function which takes about 3 minutes to executes, can u plz suggest how to improve...

Table Variable Parameters - Odd design choices or am I just not finding the answer - So, I'm currently looking over some 2k5 to 2k8 upgrades we're doing (yeah, I know) and I'm reviewing using table...

Shrink database - I received this from a vendor as part of recommended maintenance for sql 2008R2 databases. I always thought it was...

I need help with my query. I found part of this, but doesn't work. Please help. - I need to get the emails from the n.Note field, which is a nVarchar(max) column. [b] I have the following, but I...

weird memory usage on sql server - Hi Folks, I got this weird issue on sql server. I am running window 2008 r2 enterprise 64 bit with 8 gb...

Google Analytics SQL Import - Hi All I was just wondering if anyone has ever done a Google Analytics import to SQL via SSIS? A quick google...

Unable to shrink transaction log - could not locate files - Hello folks, OK so I have done as much digging as I can on the net and haven't found a solution...

Database Diagram printing problems - Greetings all; I have seen this issue/problem for a number of releases now and I have not been able to...

SQL Server 2008 : T-SQL (SS2K8)

Tally Calendars and 'Week 1' - Hey guys, I know there's been a few discussions on here about tally calendars and their use, I've got one on...

help me remove key lookups from the query below - Hi, I have a stupid written query. I saw that 99% of the cost is going into key look ups... i...

top 10 product in each month each year - hi i have two tables table A main_table id fill_date 1 09/04/2003 2 12/31/2005 3 01/05/1985 product id name 1 oxygen 2 detox 3 carbo what i want is top 10 product...

Measure Transaction size - All, I have two queries like below. declare cursor c1... open c1 fetch c1 into ... while @@sql_status .. begin BEGIN TRAN insert t1 insert t2 insert t3 update t4 COMMIT fetch c1...

Inline transaction not committing properly - Hello I'm getting the following error when trying to run the attached script Msg 207, Level 16, State 1, Line 76 Invalid column...

Can someone help me with the following TSQL search string extract? - I would like to extract Tow (Tow: 3PM to 8PM Mon-Fri, 4AM -11PM Fri-Sat|) information from the string below: 8Am...

t-sql 2008 r2 pass paramters - In need to pass 5 parameters to a stored procedure in sql server 2008 r2 standard edition database. The 5...

Puzzle: vertex covers in SQL - I have to do a book chapter on graph database. I already did “The Kevin Bacon” problem in SQL for...

Compare one row with another in a Table ! - Hi , House_Acc Accountid repcode 123 1 J978A 123 2 J978A 123 3 J978A 123 4 EG567 456 21 BR5TG 456 22 BR5TG 678 66 ZHR06 678 45 ZHR06 678 34 NH678 How...

Using Results from one column to calculate another column - Doing this in SQL 2008. Is it possible to use the results of one column to calculate another column? Example: Select .. VERY...

return zero for months where no data exsits - Hi, I have a table (Users) with rows containing data about registered user, Table Columns: UserId, RegisteredDate I want to know how many...

Connecting to multiple sql servers - Hi, I'd like to connect to multiple databases on different servers all from the same query. So, is it possible to...

Dynamic SQL - which would you use? - When I need to execute code depending upon the values of parameters I often implement one of 2 methods: In this...

SQL Server 2008 : Working with Oracle

SQL transactional replication to Oracle 11g server taking slower than normal - Hi, I have a push transactional replication set up that pushes to a Oracle 11g db. What used to take...

SQL Server 2008 : SQL Server Newbies

Select records from current month - Hi, I use a very basic select query [code] SELECT * FROM Client_Trades.DBO.Trades WHERE MONTH(Trade_Date) = '01' [/code] To extract records from a particular month. Is there...

Decimal separator missed when pasting into excel from sql query - I have an sql query which returns some numeric values that sql server is representing with a decimal point. Since...

Help regarding Performance Statistics Script - Hi all, I am a newbie in SQL Server. Today i came across the PerformanceStatisticsScript. I found it in SQLNexus.codeplex.com and...

Server build design for BI servers - Hi all, Looking to try and confirm to a standard build for all our servers. We have a lot of...

SSIS Division - How do I evenly divide $10752.00 into 7 In SSIS ? (even division of a decimal) For example:$102.10 / 2 Result:- 51.05 51.05

Error handling in while loop...skip if failure - Hello, I have the following stored procedure that runs as a job: [code="sql"] CREATE PROCEDURE [dbo].[proc_Master_Schedule] AS set XACT_ABORT ON DECLARE @Count INT DECLARE @LoopCount...

Find Fields that are True - Hi, Folks! Need help. I have a simple table with 29 fields(unique id, 14 True/False, 14 text) I want to find all...

Taking a vertical list and transposing it to horizontal and then grouping - Hello, Is this possible? I have several columns, such as: Coulmn A red green yellow black Column B shirt jumper blouse coat Column C for women for men for girls extra large I have permutated the columns in...

Translate rows in columns - [font="Courier New"] Hello, I have the following TitleDetailsRequest table: see TitleDetailsRequest.jpg attachment. I need to get an output of the EN_No and FR_No...

Renameing a column in all tables in a sql 2008 database - I have to rename a column and add columns to all tables ( could be up to 300 tables ) in a...

SQL Server 2008 : Security (SS2K8)

assigning a special user some special privileges - Hello all, I need some help assigning a special user some special privileges (Role). I need to give the user reading rights...

SQL Server 2008 : SQL Server 2008 High Availability

difference between SQL Service pack patching in 2005 and 2008 Cluster evn - HI. Just wanted to know if there is any difference betwen Sql patching in 2005 and 2008 Cluster evn

SQL Server 2008 : SQL Server 2008 Administration

BackupExec SQL Agent or SQL Server Native backups???? - I'm looking after a whole bunch of SQL Servers, both 2005 and 2008, some full and some simple recovery databases...

adding space to tempdb - In order to avoid any failure please add some space to tempDB database on xyz server. I am a new...

Error 26073 - Hello, We are also getting similar error on our SQL server in cluster environment Error 26073 TCP connection closed but a child process...

Backup and Transaction Log Maintenance - Good Morning Everyone, I hope everyone is having a nice Monday morning :crying: I came into work today and I noticed...

Partition including refernce tables and multiple keys - Hi All, We are planning to partition our table which have many reference tables. Is it possible to have multiple keys...

SQL Server 2008 R2 TEMPDB massive autogrowth suddenly. - Today while i was at lunch, TEMPDB had massive autogrows until it filled the disk it sits on. When i...

Database Mail e-mail notifications not being sent on completion of backup jobs - Hello -- I configured our backup jobs to send notifications to the administrators on their respective completion. However, the notifications have...

(Restoring...) question - Hi everyone. Could anyone help with this one. A database has incorrectly been restored with (RESTORE WITH NORECOVERY) configured, therefore database is...

Are these SQLIO results ok? Need to ensure that new SAN IO latency is topnotch - Hi, Just run SQLIO on my new Cluster and got very interesting results that I would like to share and/or get...

Programming : General

Theoretical: Checking variable positions in a date-ordered queue - I have a theoretical question that sprang from, of all things, looking at my public library holds this morning. :cool:...

Big Data - I am hearing this term "big term" very loud these days. Everybody is talking about it. Does any one know...

SQLServerCentral.com : Anything that is NOT about SQL!

PolyServe replacement options - While it seems HP still has not made the official announcement, some of us have had conversations with HP representatives...

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

SSRS Background colors (flashing colors every 2 seconds) - Hi, i have a table in SSRS which currently displays customer information. I currently have it alternating colors on a...

SQL Report with multiple matrix - I have a matrix reporting data by week by location. The matrix works great for this. The challenge, however, is...

Timeout expired in reporting services log - We are getting the Time out expired error message in reporting service log file while executing the one report which...

Workaround - LookupFunction in 2008 - Hi geniuses! I have to edit some reports in SSRS 2008 and figure that I'm going to need to use data...

Reporting Services : Reporting Services 2005 Administration

access to report server url - Using SQL SERVER 2008r2 Reporting Services. I accessed my report server url using my admin credentials. I used New Role...

Reporting Services : Reporting Services 2005 Development

SSAS group anything outside top N in SSRS report. - Group, I am working on a series of reports for a sales dashboard. All of the reporting services charts I am...

Database Design : Design Ideas and Questions

Urgent normalisation help please! - :-)

Data Warehousing : Integration Services

How to get the data in destination database immediately when source table updates? - We have 2 databases Source Database and Destination Database, We want to transfer data from source table records to destination...

Need to export excel sheet with SPECIAL formats - Hi, I need to export an Excel sheet with special formats (mandatory) where SQL is the source. I have achieved it...

SSIS2005: DataFlow Task from OLE DB Source to Excel File Destination - Hi guys, Once again it looks like Excel is the poor relation in SSIS. I am trying to create a DataFlow task...

Data Warehousing : Analysis Services

Processing of an Cube kills the service - Hi, Im very new to OLAP, so be nice :-) Version 9.00.1399.06 Windows 2003 We have a cube the for some reseaon refuses to...

MDX FLOW - Hi friends, I'm new to mdx . anyone please explain the flow of this or any simple mdx expression.so that i...

Dynamic Security for dimensional members? - Ok so here's the problem. I have a corporate BFC (big fat cube) which some 20 different services access and within...

Data Warehousing : Data Transformation Services (DTS)

Getting the name of a file in a location when the file name changes using DTS - To all, Using SQL 2000 DTS. I have an Excel connection named "FileToProcess". I have a global variable named "gvSourceFilePath" and I have...