In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Backup Are you sure you can restore your backups?
Run full restore + DBCC CHECKDB quickly and easily with SQL Backup Pro's new automated verification. Check for corruption and prepare for when disaster strikes. Try it now.
 
SQL Source Control Database source control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Get started now.
 
Cloud Services “Thanks for building such a useful and simple-to-use service”
- Steve Harshbarger, CTO, 10th Magnitude. Get started with Red Gate Cloud Services and back up your SQL Azure databases to Azure Blob storage or Amazon S3 – download a free trial today.

In This Issue

Fuzzy-String Search: Find misspelled information with T-SQL

An optimized Damerau-Levenshtein Distance (DLD) algorithm for "fuzzy" string matching in Transact-SQL 2000-2008 More »


SQL in the City - Austin 2012

A free day of training in Austin, TX with Grant Fritchey, Steve Jones and a few others. Join us to learn about SQL Server and how you can more efficiently work in your job every day. More »


SQL Server 2012: Migrating BLOBs to FILETABLEs

Learn about this new feature in SQL Server 2012 and how to move your binary data. More »


Run an Oracle package from SQL Server Integration Services

If you want to run an Oracle Package and then execute a web service, copy files or folders, a sequence of tasks, you may need to use SQL Server Integration Services (SSIS). More »


Editorial - Compress Everything

We are gathering more and more data all the time. There's no end in sight, and even as storage becomes more dense and cheaper per GB, we are needing more and more of it all the time. I saw a note recently that estimated in 2011 we gathered 1.8ZB worldwide. Not TB, not PB, but ZB. That is crazy. There are estimates that say some industries have more data per company than the US Library of Congress.

Our own databases are constantly growing, and even though many of them are relatively small, we keep multiple copies of the data. We have production, we have test, we have multiple development copies. Some companies have UAT, some have customer service environments. Overall, we may have a lot of data in our companies that's redundant, but necessary.

Managing all this storage can be a hassle for DBAs. It can be a pain for developers that need more drives for their environments. It can lead to arguments with SAN adminstrators for those servers that need additional storage to match production. Time becomes a factor as well when looking to restore, copy, move, or just query the systems.

An obvious solution is compression. There is data compression built into SQL Server, which works well in some data distributions. Third party products exist to compress backups, or even whole databases. Virtual restores can eliminate the need to have enough space for a backup and the data expanded in an MDF. There are solutions out there, and often they can return a very strong ROI against their costs.

If you have a storage crunch, take a look at the various third party products out there. You might something that makes your job easier, and perhaps even gets the SAN administrator to smile.

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

On an English SQL Server installed with the defaults, what is the output of the SELECT query?

CREATE TABLE test
(
 col1 INT,
 col2 CHAR(2)
) 
GO
INSERT INTO test
VALUES 
 (1,'AB'),
 (2,'Ab'),
 (3,'aB'),
 (4,'ab'),
 (5,'XY'),
 (6,'xy') 
GO
SELECT COUNT(*)
 FROM test
 WHERE col2 IN ( 'AB', 'aB', 'xy' ) 

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

We are executing below commands to take the backup of two databases:

BACKUP DATABASE TestDatabase_1 
  TO DISK = 'C:\TestDatabaseBackup.BAK'
  WITH COMPRESSION
GO
BACKUP DATABASE TestDatabase_2 
  TO DISK = 'C:\TestDatabaseBackup.BAK'
  WITH NO_COMPRESSION, INIT

What will be the result?

Answer: First backup command will complete successfully, second backup command will fail

Explanation: First backup command will complete successfully & second backup command will fail because compressed and uncompressed backups cannot co-exist in a media set. Please read the "Restrictions" on below given link:

Ref: Backup Compression - http://technet.microsoft.com/en-us/library/bb964719.aspx

» 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

What got restored from where, by who and when

Ever wondered what got restored, who did the restore and where the backup came from that did the restore? 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

Adding column(s) to linked server in sqlserver 2005 - Hi all, WE HAVE ESTABLISHED LINKS SERVER BETWEEN TWO SQL SERVERS 2005 IS IT POSSIBLE TO ADD COLUMN OR FIELDS IN LINKED...

time find out - Hi, This is vivek. I have one requirement please let me help. I have table contain empid,statues,datatimes 1000,IN,17-09-2012 10:10:10 1000,OUT,17-09-2012 11:20:30 '' ,IN,17-09-2012...

Logins enabled - Query to find out list of logins that are enabled in SQL SERVER 2005.

SQL2005 client components (SSMS, MS Visual Studio 2005,...) not compatible with windows 64bit??!! - Can anyone please confirm that installation of SQL2005 Client Components is not possible on windows 64bit? I tried to install SQL2005...

Communications to SQL server "freeze", then resume. - I am looking to see if anyone else has a few ideas of where to start in looking for problems...

Log Reader Agent Blocking - Hey all, I have a push transaction replication on SQL Server 2008 that has the Log Reader Agent trying to execute...

SQL Server 2005 : Backups

how to explore/ retrieve SQL Server Transaction Log information - Can anyone advise me on how to explore/ retrieve SQL Server Transaction Log information e.g. interprete record of table/ row...

SQL Server 2005 : Business Intelligence

Date Range filter in Performancepoint 2010 - is there any method to implement date range filter( fromdate and todate) in performancepoint 2010? I can implement the same in...

Looking for good books/resources for SQL 2k8 data warehouse - All, ** I think this is the appropriate forum but please let me know if not and I'll move it ** My...

SQL Server 2005 : Development

Excel Connection Manager error in SSIS 2012 - Please assist with error below: Could not retrieve the table information for the connection manager 'Excel Connection Manager'. Failed to connect to...

sqlmail timout connection pool - I am using an SQL Job to call a stored procedure which queries the database to get some email addresses...

SQL Server 2005 : SQL Server Express

How can we install SQl server 2005 Express edition on Windows 7 64-bit machine - Hi, I have a windows 7 home premium 64-bit laptop and i want to install SQL Server 2005 Express edition-(server and...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Rebuilding / Reorganizing indexes not working properly on sql server 2005 - Hi all, I run following script:- DECLARE @db_id SMALLINT; DECLARE @object_id INT; SET @db_id = DB_ID(N'SFNData'); SET @object_id = OBJECT_ID(N'FDIP_B'); IF @object_id IS NULL BEGIN PRINT N'Invalid object';...

SQL Server 2005 : SQL Server 2005 Integration Services

error - hi, i am getting this error: Error: 0xC02020A1 at Data Flow Task 1, Flat File Source [1]: Data conversion failed. The data...

SQL Server 2005 : T-SQL (SS2K5)

database stuck in single user and no spid - Hi i have a dev database that is stuck in single user mode when i try and access the database i...

Reporting on SQL Agent history in 2005 - Hopefully this is the right forum. It's fundamentally a T-SQL question. I'm trying to make a report that is useful for...

Dropping indexes - Hi All, I need to drop indexes on a table over 500mil records, I wandering is it better to drop the...

SQL Server 7,2000 : Administration

CryptAcquireContext failed. Error 0x57 - On a production db of over 100 GB, i am getting the below error freqently and i have to restart...

CryptoAPI function Error - While running a transaction which involves posting a payment to a customer account, i got the following error: CryptoAPI function 'CryptAcquireContext' failed....

SQL Server 2008 : SQL Server 2008 - General

Saving query results to CSV file with query in job - I am using SQL server 2008 R2 with no SSIS capabilities. I have set up a job in SQL Server...

how can we know the deleted userinformation on a particular table in DB - Hi , in last couple of days, one table is truncating means its having zero records init. it is very useful table...

Scripting object securables - Hi all I am wanting to script out all the object permissions for a particular user in my DB. I had...

New Line Character - Hi, Am using below query to display veh_name, expiration_date, for new line character, here am using "+CHAR(10)" what is the new line character,...

Use of cursor in ETL stored procedure for moving data from one table to many tables - Hello All, I am beginner to writing stored procedure. So checking out your views on implementing below requirement: I have one table...

IN Vs INNER JOIN - I want to know which query is more optimized? SELECT 1 FROM ABC WHERE Id IN (SELECT Id FROM ABC1) =================================================== SELECT 1...

sql server 2012 cluster take way too long to backup/restore - Hi experts, I need some directions on what sort of things should I look into why backup and restore take way...

SQL database security and vulnerabilities - Hi all I am a computer science degree student in my final year of study. Currently I am compiling a research...

DELETE statement won't complete - This is an odd one. A DELETE statement with a single filter, deletes around 1.5 million records from a table...

Help me make my mind up- sql or access - I have a client to whom I am writing a database program for in vb.net 2010 express. They currently have...

select query - I am using SQL 2008 and I need help to write a select query for the following output I need list...

new to DBA an dtechnology - Hi all, I am new to technology world, been in customer support, planing to move to technology, been thinking of doing...

Audit using Profiler ... - Hello, I'm a new member and it's my first post When I use a Profiler to monitor Log in/out (Audit Login + Audit...

send mail not working when used with attachment - hi, below is the script i have in SQL Server Agent. SET ROWCOUNT 0 EXEC msdb..sp_send_dbmail @profile_name = 'SQL Mail', @recipients = '<me@email.com>', @subject = 'subject', @body = 'Please...

NOLOCK/Isoloatin Level for READONLY database - Hello, We have a sole readonly database which is updated monthly at night during maintenance window. The db has several stored...

SQL services where set Automatic but doesn't came up after reboot server - SQL services where set Automatic but doesn't came up after reboot server . I have started SQL services manual, then it's...

Query plan - largest cost - This query takes about 45 seconds to complete (down from 10+ minutes by adding clustered index to table). Looking at...

Insert record in DATABASE SNAPSHOT seems to block server - In my server I have a few 10's of databases and on some of them I created a snapshot to...

Need to get YYYYMM from date ? - Hi Guys, Please help me to get YYYYMM from Getdate()? I have tried the below query.But it gives 20129 only not 201209...

Searching the time data type - I have two fields of type time. StartTime and EndTime. I want to see if Now is between these two...

Stored Procedure help - Hi, I need help in a stored procedure that, counts the number of Saturdays in a month, returns 12 rows,...

How to made phonotic search in SQL - Hi, i want to make phonotic search in SQL Server database for that I am using Soundex('AMIT') for phonotic search its...

Calculating the total for the month - Hi Everyone I have a table in SQL Server that contains sales performance by items sold by the week i which...

Using Print, will it slow down procedure? - I have a procedure that, due to the nature of the data, has to be iterated through record by record....

UniqueIdentifier as a Primary Key - Is it ever considered a best practice to use a UniqueIdentifier(GUID) as a primary key? Is there ever a case where...

Puzzle : Generating two unique numbers from an array of numbers (repetation not allowed) - Hi All, (This is just for fun, so you may choose to ignore this topic.) Today I read a puzzle somewhere which...

results from a SP into a #table ? - Hi, I am getting empty result set while trying to insert results from a proc into a #table unless I...

SQL Server 2008 : T-SQL (SS2K8)

how to find duplicate default constraints in sql server - how to find duplicate default constraints in a table ,example DF_abc1 is a default constraint FOR column Effective date and...

Find records which are within the depth range of other records - Hi, Many Thanks for your help in advance, this one has my stumped. I would like to concatenate the text fields...

'Arithmetic overflow error converting numeric to data type numeric' - This procedure working fine in SSMS with multiple execution. Problem is application side(c#), first time executed working fine and inserted data...

Select 2 tables - Hi All Consider the following 2 tables [code="sql"]create table T1 (Col1 int, Col2 int) go create table T2 (Col3 int, Col4 int)[/code] And the following query [code="sql"]select Col1,...

create database - How do you create a database from a data and log file?

Combining a Query to Include an Additional Column - Hi, I am trying to work with data from the ProjectServer_Reporting database, which is a part of the Microsoft Project Server...

how to find duplicate indexes in all the tables in a database - how to find duplicate indexes in a table ,example a nonclustered index names FX_LOCATION_ID is created on columns a,b and...

simple que - hi, i have 1 table which has millions of rows, i am doing select * from tablename and select count(*) from tablename does this...

PROD ISSUE : Conversion failed when converting the varchar value 'DT-1205-006049' to data type int. - The issue is that the query runs in test and doesnt run in prod. We have 2 fields in my example:...

Need Help In Query . - Could you please help me find records described as per below scenario. Campus code,address,CampusID(PK) Data:- DEL,ABC,1 CHN,DDJ,2 MUM,PQR,3 BNG,WRT,4 PUN,BNM,5 Enroll StudId,CampusID,EnrollDate,LstAttendDate,Enrolid(PK) 1,2,15-06-2012,NULL,1001 1,5,31-05-2011,31-05-201,1002 ChangeStatus StudId,Enrolid,NewStatId,PrevStatId,ChangeID(PK) 1,1001,1,0,5001 1,1001,2,1,5002 1

Query for treeview - I have a query which is working fine. Is it possible that if the table3's column(Child) is only related to table...

T-SQL Help Needed - CREATE TABLE Issue ( id int not null identity(1,1) primary key, name varchar(10) ) CREATE TABLE Jrn ( id int not null identity(1,1) Primary Key, issue_id int, created_dt datetime ) ALTER...

rCTE vs LIKE for Hierarchy - Hi people, I'm tunning a database and i've stumbled by some hierarchy queries, on the good old form of ID,...

UPDATE when the values are the same - Does an UPDATE query overwrite existing values if the already existing value is the same as the one your updating...

SQL Server 2008 : SQL Server Newbies

Lastname, Firstname switch - I dont know if this is any help but this is the code I started with: SELECT HOST9006.DESCRIPTION, HOST0110.ROOMNAME, HOST0140.NAME, dateadd(mi,...

Multiple values in some columns single value in another - Hi, I'm a first time poster long time reader of SSC (so perhaps I should already know this!). But... I'm having trouble...

How do I fix Damaged allocation pages - I get this result from dbcc checktable DBCC results for 'RELATED_PARTY'. Msg 8946, Level 16, State 3, Line 1 Table error: Allocation page...

Landing The First SQL Job.... - Hello All! I currently am a plumber by trade, been in that industry courtesy of my father in law since graduating...

SQL Server 2008 : Security (SS2K8)

Ownership Chains, Dynamic Queries, and SQL Injection - I was hoping to confirm that while SQL injection is possible with dynamic queries that don't use parameterized queries, ownership...

Standard Edition Auditing - Hello All, We all know that in SQL Server standard edition we cannot do any auditing on Server and DB...

SQL Server 2008 : SQL Server 2008 High Availability

unrestored log? - Hi, Principal server mirroring monitor history logs (intervel time every 3 min) Unsent Log 7588 KB unrestored log 600 KB Mirror over head - 40...

Backups & Restore.. - I know how to take Full backuo & differential backup.. But the problem here is how do i go and automate the...

Why can I connect to my new cluster via ODBC but not in SSMS? - I have made several attempts to create a 2008 R2 failover cluster in my virtual environment. I can create the...

2 node failover cluster, SQL and DTC rescources lye on seperate nodes - If we have 2 resource groups 1. MS-DTC 2. SQL Server And the MSDTC resources lyes on Node A, and the SQL Server...

Auditing database performance impact - Can anyone explain me that do we get any performance issues when we implement database auditing on whole database. This...

SQL Server 2008 : SQL Server 2008 Administration

Import data from a Source database which does not have the indexes. - Hi, I am importing whole data (with out any where condition) from a database, and that database tables donot have any...

Database going into suspected mode !!! Urgent - Primary server databases are working fine but my secondary databases are going into suspected mode. No of databases 20 all are...

Basuc question about High Availability solution. - Hi, I am very new to the DBA scenarios, i am learning the things from the Online stuff. For high availability solution: 1....

Where to find SP2 for SQL Server 2008 R2 Avanced? - Hello, I have the Advanced Services edition for SQL Server 2008 Express R2. I like to find the SP2 so I...

Strange Maintenance Plan SubPlans behaviour: each SP runs all the tasks of all other SP at odd times - Hello (SQL Server 2005) I have a problem with scheduling a Maintenance Plan (MP) with 3 subplans (SP). SP1 is scheduled to...

Plan Cache Performance - Hi All I'm using the below query to investigate plan re-use on my SQL Server How accurate are the counters used here? Has...

Reporting services+sharepoint - I am using SQL Server Reporting Sevices 2008 R2, and is in SharePoint Integrated Mode, whenver trying to deploy it...

SQL server performance disgnostic tool - I am Sr. DBA in one of the leading bank and we would like to buy MS SQL server performance...

SQLServerCentral.com : Anything that is NOT about SQL!

Suggestions on anything to bring for SQL Saturday in Chicago? - Laptop with SQL, 12yr old Appleton rum for the presenters, $1 bills to put in the presenters shorts, that sort...

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

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

Data Warehousing : Integration Services

temp folder for SSIS job - Hi All, We have a issue with respect to the temp Space for the SSIS Job Execution. When the Proxy User is...

Bring Text datatype data from sql table and pass to Execute SQL task or something to create a table - Hello Team, I have a text datatype field in the database. It has a create table script. In a SSIS project...

SSIS error 77 - Hi Guys, I am getting below error. Error 77 System.UnauthorizedAccessException: Access to the path 'E:\Backup_data\lity_dump\Automation_script\lity\lity\bin\Package.dtsx' is denied. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.File.SetAttributes(String...

In SSIS package Need to Create new table Dynamically for each 1000 Records - Dear all, I had a requirement like, I have a flat file with 10,000 records, I need to load this...

SSIS Job needs to run every 15 minutes for 8 hours to check for existance of a record in a Table for the Current Date - I have a C# Solution and it checks for the existence of a file named Complete.txt that is generated from...

capture oracles's (sequence.nexval) value in a column during data flow... - Hi all SQL server techies.. I have the following scenario I am doing an insert from sql server table to...

Data Warehousing : Analysis Services

Help in MDX or calc Members - Hi, I have 2 dimensions prod and prod_warr 1) prod-> which has list of prod's prod table schema prod_id,Name ------------ 1,a 2,b 3,c 2)...

How i can replace the sql query with mdx - Hi select * FROM users where user_id not in (select user_id from emp where is_actv=1). In this Users and Emp both are dimensions...