In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Data Compare Save time when comparing and synchronizing database contents
"It has also streamlined our daily update process and cut back literally a good solid hour per day." George Pantela, GPAnalysis.com. Download a free trial of SQL Data Compare now.
 
SQL Monitor Check SQL Server performance at a glance
We consulted 1000 SQL Server professionals to make SQL Monitor’s UI as clear as possible. Start monitoring with a free trial.
 
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.

In This Issue

Dynamic ETL with SSIS

Learn how to dynamically load data from ETL load files using SSIS as a shell. The code downloads files from FTP, parses them and loads them into the database. More »


Monitor SQL Instance Balance across Windows Cluster Nodes Using PowerShell

This tip will demonstrate how to monitor SQL Failover Cluster Instances if they become unbalanced using Windows PowerShell. More »


From the SQLServerCentral Blogs - A Replacement for DBCC IND in SQL Server 2012

Each release of SQL Server is chalk full of new features and an assorted amount of bells and whistles.  Some... More »


Editorial - The Security of You

This editorial was originally published on Nov 10, 2008. It is being re-run as Steve is at SQL Bits.

I've written about biometrics in the past and even polled you about your concerns with this data v credit card data. Surprisingly most people felt that credit card data was more of an issue.

There is a report on CNet that some people are concerned about the security of this data, which makes sense given the way that much of our data has been treated over the last few years. I haven't seen any reports of biometric data being copied, but I'm sure that's a matter of time. It seems to be some type of corollary to Murphy's Law, call it the Jones Observation: If we can store data, someone can copy it.

Biometric data sounds really, really cool, and I know there are all sorts of ways that it can be secured, that we can scan multiple parts of your body in case you lose a finger, get a sore throat, have a few too many adult beverages the night before, etc. However the fundamental problem isn't false positives, and it's not false negatives.

It's the fundamental inability of any organization of any size to be sure that the data they stored is still the data that's there. This is going to be one place that it will pay to somehow replace the digital representation of your finger with a criminals, giving him the access to whatever you're protecting. And when he can't remember the password or PIN, the "second factor" in authentication, I'm sure that someone will be happy to verify his fingerprints and then reset the password for him.

It sounds like a good idea to biometrically verify people's identity, and it looks cool in the movies when those computers remember who you are. But in practice it doesn't work well, probably never will, and it will be an area that mistakes, serious mistakes can be hard to correct because people will have so much faith that those 1s and 0s really do represent you.

ID cards and pass codes are fallible; I completely agree with that. But we KNOW they're fallible and so we accept some issues and we don't necessarily trust them, at least not in very secure places. However I think we're just naturally going to believe more in biometrics, something I'm not sure is a good idea. I know that if I have to start using these, I'm going to want to some escrow of my digital representations, just in case there are problems.

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


The Voice of the DBA Podcasts

Everyday Jones

The podcast feeds are now available at sqlservercentral.mevio.com to get better bandwidth and maybe a little more exposure :). 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.

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:

Below is a table which has one column with identity property. How do I insert values in this table without changing the identity property of the column?

CREATE TABLE TableA(ID INT IDENTITY (1,1))

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.

Microsoft® SQL Server® 2012 Step by Step

Teach yourself the programming fundamentals of SQL Server 2012—one step at a time. Ideal for beginning SQL Server database administrators and developers, this tutorial provides clear guidance and practical, learn-by-doing exercises for building database solutions that solve real-world business problems.

Get your copy from Amazon today.


Yesterday's Question of the Day

Below are 5 statements, I execute all statements. Which statement(s) execute successfully or give error. Choose your answer below. The answers are listed in the order of the statements.

---- Statement 1
SELECT SYSOBJECTS.Name, SYSOBJECTS.Xtype, SYSOBJECTS.crdate, SYSOBJECTS.refdate
 FROM SYSOBJECTS
GO
---- Statement 2
SELECT Name, Xtype, crdate, refdate
 FROM SYSOBJECTS AS OBJ
GO
---- Statement 3
SELECT OBJ.Name, OBJ.Xtype, OBJ.crdate, OBJ.refdate
 FROM SYSOBJECTS AS OBJ
GO
---- Statement 4
SELECT SYSOBJECTS.Name, SYSOBJECTS.Xtype, SYSOBJECTS.crdate, SYSOBJECTS.refdate
 FROM SYSOBJECTS AS OBJ
GO
---- Statement 5
SELECT SYSOBJECTS.Name, OBJ.Xtype, OBJ.crdate, SYSOBJECTS.refdate
 FROM SYSOBJECTS AS OBJ
 GO 

Answer: successful, successful, successful, Error, Error

Explanation: Recently, I found that a few beginners are using the table alias and the table name both in their queries. That's wrong. If you are using table alias then you cannot use table name for reference in that query.

Ref: http://msdn.microsoft.com/en-us/library/ms187455%28v=sql.90%29.aspx

» Discuss this question and answer on the forums

Microsoft® SQL Server® 2012 Step by Step

Teach yourself the programming fundamentals of SQL Server 2012—one step at a time. Ideal for beginning SQL Server database administrators and developers, this tutorial provides clear guidance and practical, learn-by-doing exercises for building database solutions that solve real-world business problems.

Get your copy from Amazon today.


Featured Script

PowerShell to SlipStream SQL 2008

PowerShell script to SlipStream SQL Server 2008 installer with SPx patch. 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 SERVER 2005 Updates - If I have SQL SERVER 2005 SP 4 @ build 9.00.5296 dated October 24, 2011 Will I be able to install Microsoft...

SQl server carrier advice - HI, I have 4.5 years of SQL developer.I work with one of the reputed bank in the world. My situation is being...

SQL Server 2005 : Backups

Who uses the Commvault IdataAgent for sql backups? - I'm pretty sure if you have a log chain in your commvault backups ( full, differential, incremental/Tlog backups) you don't want...

SQL Server 2005 : Business Intelligence

Best Practice for loading DW tables from live source with timestamp/Rowversion column to identify changes - I want to build a DW and I need to identify records that have changed, and luckily each table in...

How can I store the username and password without config file - How can I store the username and password not in the config file or SQL Server in SSIS, so that...

SQL Server 2005 : Development

CURSOR - running very slow - I am new to sql server 2008 R2. I have an issue with CURSOR. This is an simple cursor to...

Rows Value into Columns - hi Guys, Need your help to sort out the Query, i have fix columns Field1 to Field10, and Variant Rows...

Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction - Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim....

SQL Server 2005 : SQL Server 2005 General Discussion

Synonyms on a table - Hi All, I have a synonym on a base table. The base table is changed as per some logic. Somehow I...

SQL Server 2005 : SQL Server 2005 Integration Services

Problem with connection argument in CMDEXEC command - I'm putting this under the SQL Server 2005 Integration Services subject because I'm trying to execute a package using a...

Script to open encrypted, dynamic excel files - Hi I need a script for my SSIS package that will open my encrypted excel file, that has a dynamic...

SQL Server 2005 : T-SQL (SS2K5)

Error converting string to date - Hi, I've got a really annoying problem in that I have a char column (loaded from csv) that contains dates in...

SUM CASE, COLUMN MINUS COLUMN - I have this code working fine. I need to subtract one column from another to get the corrected results. I...

Droping out of a cusor - Hi what’s the best to way to drop out of a cursor if a condition is meet? Many thanks

Unpivoting multiple columns. - hi all, I want to unpivot multiple columns in my table and i came across this article http://mangalpardeshi.blogspot.com/2009/04/unpivot-multiple-columns.html This blog is very...

SQL Server 2005 : SQL Server Newbies

Generate two columns from single column with alternating data - I have what I thought would be a simple PIVOT example, but I have not yet solved it without using...

Computed column with subquery - Hi, My question is about calculating a computed column in SSIS. Unfortunately, the column is based on another column in the...

SQL Server 7,2000 : Administration

Need Help Stopping a Rouge Extended Procedure (XPSMTP) - I'm experiencing a very unusual problem with one of our old SQL 2000 installs. Backstory: we've used xpsmtp80.dll on all...

SQL Server 7,2000 : Data Corruption

ERROR 823: I/0 error (torn page) detected during read at the offset 0x00000000182e000 in file D:\program files\microsoft\SQL server\MSSQL\data\msdbdata.mdf - Hi, Pls i'm having the error 'ERROR 823: I/0 error (torn page) detected during read at the offset 0x00000000182e000 in...

SQL Server 2008 : SQL Server 2008 - General

Various connections for the same session - Hello: I'm working with SQL Server 2008 R2 and launching sys.dm_exec_connections and sys.dm_exec_sessions views I have found something strange: various connections...

Using SQL Management Query in Visual Studio - I am trying to use a query that works in SQL Management in Visual Studio but the code it not...

How could i find the client? - I have a consolidated SQL Server Machine, and none of application are running on this. But I have an error...

email and sms - I have this code use IT go Create Trigger Notify on IT.WorkRequest for insert ,update, delete as begin set nocount on; DECLARE @body NVARCHAR(MAX) = N''; SELECT @body += char(25) + char(25)...

Tricks to check if a Restored Database has SQL Replication remnants - We have Clients that will restore a Production Database into a Development/Testing Server. One of the options that we have...

Stored procedure not giving desired output - I am getting my data of current year even after adding range date. i tried everything but not found way...

Need help rebuilding a SQL server with replicated databases - We have a production SQL server in a virtualized environment and we need to move it to a physical server...

Peer-Peer replication - I have just created peer to peer replication on couple of test server. Let's say TestServer1 and TestServer2. And test database called...

How SQL Server inserts record into CDC table (with Latency) - Hi All, I have a question related to CDC latency in SQL Server 2008 R2. Suppose I have two tables T1 and...

SQL 2k5 to 2k8 - Migrate Logins - Migraion of SQL 2005 to SQL 2008 Am using sp_help_revogin to migrate all login. For most user db's, user-db mapping...

Outer apply random value - Hi, I'm having problems getting a random value with outer apply and newid(). Here's the DDL: [url=http://sqlfiddle.com/#!6/47f38/2]SQLFiddle[/url] The wanted result would be...

Migration from SQL server 2005 to SQL server 2008 - I am at my client location and they have assigned me a migration task from SQL server 2005 to sql...

Question about MAX_QUEUE_READERS in Service Broker. - Guys, I have a Question about MAX_QUEUE_READERS option in Service Broker. In a high transaction environment where inserts/updates/deletes are very high, would...

Tracking running queries - Hi all In one of the daily emails (quite a while ago), there was a method for getting server performance and...

display specific string and that positions in sql server - hi friend i have small doubt in sql server plz tell me how to solve this i want display only specific...

Are my statistics being used? - Hi, I have a system with a lot of blocking. From the last occurrence I found a statistics update causing a...

Trouble with SQL Server Agent 2008 - Hello all. This is on a Windows 7 Pro machine. As I mentioned in an earlier post today, I am...

Peer-to-peer replication - Question. if I insert a row on one server in the peer-to-peer topology, is it possible that an update is...

Blocking - Hi, We are running one job which runs SELECT query from a view from another linked server. We are getting blocking...

Login audit - Hi, I've been asked to produce a report on who has accessed a particular database on an SQL server we have....

Disk_Queue_Length is CRITICAL or high - This is regarding the Cluster servers 723/722/721 3 Nodes (Active/Active/Passive). At around Fri Mar 29 11:05:42 EDT 2013, We have...

Error while taking backup for a user not having sysadmin permission - I am accessing my sql server 2008 r2 express server using SSMS. I have about 5 logins. For a particular...

SQL migration - Whats the best way to copy logins, users, logins with password along with exact status of enable/disable from source 2008...

Low Batch Requests/Sec? - Hi All System Info... Server. 12G RAM, 2xQuad Core processors. Hard Drive C (RAID 1), Hard Drive E (RAID 10). 1G NIC SQL...

SQL Server 2008 : T-SQL (SS2K8)

Get Latest Revision of Attribute Based on Date - Hi all, First real post here although an avid reader of some of the posts. Mainly a DBA but started to...

A Pivot, but not exactly. - I have a table that could look like the following: ID FK_ID Value 1 100 'Blue' 2 100 'Black' 3 100 'Green' 4 101 'Blue' 5...

Update Column after Alter Table ADD column - hi, I just came across an error, the cause of which is unknown to me. why would a TSQL : ----------------------------------------------------------------------- IF NOT EXISTS...

sql count - I have a table like the following: Name Time1 Time2 Time3 leo 06:03 0615 08:13 John 06:17 10:03 11:05 Can anyone tell me how...

Replacing a string when the value is found in a table - Hi guys, I'm trying to create a function where I can pass a string value that will be used for...

Converting particular set of Rows as Columns - Converting one row as one Column is possible using Pivot. But is there any way to convert particular set of...

Making Query SQL 2000 compatable - I have a query i wrote for 2008 to get the top 3 salesPeople per territory by their total sales....

update record from another record in same table - Hi, I'm looking for the best way to update pkid 1 = pkid 2 update testTable set a.fname = b.fname from (select * from testTable where pkid...

is it possible to union all two CTEs? - I have two CTEs sharing same column names and I need to combine their results into one result set. Given...

Logging Raiserror log messages into a table - Hi, I have several raiserror statements (all informational messages ) inside my stored procedure, Can I have them logged into a table...

SQL Server 2008 : SQL Server Newbies

Query for non-existent entries - Hi, I'd like to build a query that produces the missing entries in a scores per week table. Say that you...

Updated muliple rows into one column? Why is it hard? HELP - Trying to update a table using integer number in 3 columns and a signing texts name to results in the...

deletion of db taking long time - Dear All I was doing bcpin with 20 million records. I stoped it in between. Then tries to delete the table...

Selecting/Deleting rows with the same column information - I need to delete some rows from our system that have the same transaction id based on an accounting date....

SQL Server 2008 : SQL Server 2008 High Availability

cluster validation test failing while adding node to the existing cluster group - cluster validation test failing at Validate SCSI Device Vital Product Data (VPD) test. There's a hot fix to resolve this...

SQL Service account change - Hi, Log shipping (backup job) failed after we change SQL service account in our production database and DR database server. Earlier...

error from passive node - Hi Guys, We have got a active passive cluster. and I am getting service account trying to access the master database...

Ownership of cluster disk 'Cluster Disk xxx has been unexpectedly lost by this node. - My Cluster went down again. I don't have to say... I am having a not so good morning already ... :-( Here's...

SQL Server 2008 : SQL Server 2008 Administration

SQL failed logins are appearing in the error log but application user failed logins aren't? - I'm having an issue where SQL failed logins i.e. my admin account or any user accessing SQL from the database...

Checking if any database objects are encrypted - Hi, I have just been given a database to check over to see if we can get at all the data....

Production Move - [i]Hi All, I am going to a move a server to production. The present production is 2005 enterprise, now i am...

Maintaining Statistics - Hi, I am having a little issue in understanding how to implement a good maintenance plan in updating statistics. I have...

SQL Cluster Gateway Info Lost on Node Move or Failure Simulation - Hello All, First time poster so if this has been asked, please forgive. I have searched the forums but didn't find...

Programming : XML

Retrieve data from sql server and update/add existing xml file - Hi guys, i have to retrieve data and log file paths from sql server database and add that output to xml...

Programming : Service Broker

Question about MAX_QUEUE_READERS - Guys, I have a Question about MAX_QUEUE_READERS option in Service Broker. In a high transaction environment where inserts/updates/deletes are very high, would...

Programming : Powershell

Retrieve data and log file names from SQL database - Hi, I'm trying to pull the logical and physical file names from sql server database using powershell and add those values...

how can i get Powershell - to search stored procedures for server names? we have the list of server names, but not sure if we must script...

SQLServerCentral.com : Anything that is NOT about SQL!

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

Can I schedule a report to run via SQL Job Agent? - Hi I was wondering if I can schedule a report to be emailed via the job agent? I know I can Schedule...

one parameter returning all,remining parameters also need to return ALL - i have 6 parameters ,in this 4th parameter returns "ALL" 5th and 6th also in need to return all 5,6 have...

Reporting Services : Reporting Services 2005 Development

Report Filter: Long Drop Down List Performance - So I have a VERY simple report returning three columns from one table. It has four report filters: Transaction ID User Name Start...

Data Warehousing : Integration Services

SSIS - Unzipping two times - Hi, I am new to SSIS. I am struck with unzipping with ssis, I have my files in my Folder and...

SSIS - Comparing row values. - Hi All I have a question regarding managing data based on whether or not the row has previously been included in...

Unable to Load SSIS Solution in new Machine - Hi SSIS Experts, I have copied the SSIS solution from one machine to my dev server. This Dev server has SQL...

Changed SSIS now won't run in a job - I had a SSIS package running fine for months through a SQL Agent job. Then ... I deleted a task and...

SSIS Newbie - I was recently assigned a SSIS project for a client. The requirements are; Load data from 6 database tables into one excel...

Execute Package Task or SQL Server Agent - I have inherited an SSIS package that has over a dozen Execute Package Task controls in it. They are all...

Extract Speed from DB2 - Hello again all, I am back again. This time with a new company, new city, and new role! I am now in...

Data Warehousing : Strategies and Ideas

Data Warehouse Models - Someone from my company's strategy has said that the Data Warehouse model needs to be: - Atomic - 3rd NF - Relational But I...

Data Warehousing : Analysis Services

Confusion on Facts and dimensions - Hey all, I have just built a basic cube and all seems ok. I have just had my first confusion when...

Any Good Links For SSAS Beginners - Hi All, I wish to learn SSAS, so can anyone provide the good links to learn. Thank you very much in advance...

converting oltp database into dimension model (olap) database - I work on ssas using adventureworksDW2008 database for creting cube and understanding other features but if I want to convert...

using mdx query - When working with SSas after creating cube you see the data as measures and dimensions.But when does MDX comes in...