In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Prompt Make working with SQL a breeze
SQL Prompt 5 is the effortless way to write, edit, and explore SQL. It's packed with features such as code completion, script summaries, and SQL reformatting, that make working with SQL a breeze. Try it now.
 
SQL Backup Pro Quick, easy off site SQL Server backups
Back up to the cloud quickly and easily using SQL Backup Pro’s GUI. Get cheap, scalable storage for your SQL Server backups. Find out more.
 
SQL Search How do you search your database schema?
"I use SQL Search regularly and think it's great." Gregor Suttie, Senior Software Engineer, Pulsion Technology. Download Red Gate SQL Search while it's free.

In This Issue

Adding new files to a log-shipped database (SQL Spackle)

Freshly minted MCM Wayne Sheffield fills a log shipping crack that could otherwise cause you to re-initialize your log shipping. More »


Continuous integration for databases using Red Gate tools

Once databases are placed in source control, it is possible to integrate them into the existing build automation process. This will ensure that the whole development project, including the database, can be integrated regularly and tested by an automated build system. This, in turn, leads to opportunities for more frequent, reliable deployments. More »


From the SQLServerCentral Blogs - 9 resources not to be Skeptic about Big Data

2 years ago I posted “16 papers and original articles on Big Data” It’s now time to review and extend... More »


Editorial - Quickly Copy Data

There's no magic solution for many of the problems we face in SQL Server. Whether we are trying to maintain concurrency under high write loads, restore databases quicker, or ensure extremely high availability we need to make trade-offs. Sometimes those tradeoffs are frustrating, and people look to other solutions like NoSQL, not realizing that they are just making different trade-offs.

I see the frustrations and comments constantly on the SQLServerCentral forums, and one recent discussion was no exception. In this particular debate, a user was frustrated by the requirement to copy backup files across the Internet to another location. The transfers were taking too long and the poster were trying to find some magic way of decreasing the transfer time. Increasing the bandwidth, the simplest solution, wasn't an option, and that usually isn't easy to do in a corporate environment. The poster seemed to think there had to be some magic way of reducing the time it takes to copy large backup files, and there isn't any magic solution. The things that help reduce transfer time are often the common things we think of.

Compression is an obvious solution. There are numerous free programs to compress files and even software that will compress the backups as they occur. There are techniques in Windows for speeding up copies, though many are Windows version dependent. Beware, however, since copies from your server might cause you memory issues. However there might be other, more creative solutions that people come up with.

Imagine that you track the transfers, keeping meta data about the process. Perhaps you can find a way to send less data overall the next time. In some sense, this is the idea behind log shipping. If you only send the changes (the transaction log backup), you might save a tremendous amount of time and resources. That assumes, of course, that the changes in a period of time contain less data than the overall database. That might not be the case in some  workloads.

There are other services that might help. Imagine that you back up to the cloud, either because you use a toolfeature, or service if your application exists in the cloud. Spinning up a new instance with a copy of that data in the cloud might eliminate the need for transfers completely if the other location can just access the data in the cloud.

I'm sure some of you have other ideas, and I'm sure many of us would like to hear what creative ways you might have for moving data between locations.

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


The Voice of the DBA Podcast

No podcasts today due to some personal issues. The podcasts should return tomorrow.

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


Question of the Day

Today's Question:

Using SQL Server 2012 I execute the following

DECLARE @a VARCHAR(4) = 'r ',@b VARCHAR(1)= 'R' 

SELECT IIF (@a = @b, 'Yes', 'No') AS Result;

Question: What is returned by the select statement? Is "Yes", "No", or an error returned? Meaning IIF is not a function in SQL SERVER 2012? 

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

This question is worth 1 point in this category: IFF. 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 Transaction Log Management

When a SQL Server database is operating smoothly and performing well, there is no need to be particularly aware of the transaction log, beyond ensuring that every database has an appropriate backup regime and restore plan in place. When things go wrong, however, a DBA's reputation depends on a deeper understanding of the transaction log, both what it does, and how it works. An effective response to a crisis requires rapid decisions based on understanding its role in ensuring data integrity.

Get your copy from Amazon today.


Yesterday's Question of the Day

What is the output of the statements below?
DECLARE @Var VARCHAR = NULL
IF @Var = NULL
 PRINT 'Statement1 : Equal Null'
ELSE 
 PRINT 'Statement1 : Not null'

IF @Var is NULL
 PRINT 'Statement2 : Equal Null'
ELSE 
 PRINT 'Statement2 : Not null'

SET ANSI_NULLS OFF

IF @Var = NULL
 PRINT 'ANSI_NULLS OFF Statement1 : Equal Null'
ELSE 
 PRINT 'ANSI_NULLS OFF Statement1 : Not null'

IF @Var is NULL
 PRINT 'ANSI_NULLS OFF Statement2 : Equal Null'
ELSE 
 PRINT 'ANSI_NULLS OFF Statement2 : Not null'

SET ANSI_NULLS ON

Answer: Statement1 : Not null, Statement2 : Equal Null, ANSI_NULLS OFF Statement1 : Equal Null, ANSI_NULLS OFF Statement2 : Equal Null

Explanation: the output is:

Statement1 : Not null
Statement2 : Equal Null
ANSI_NULLS OFF Statement1 : Equal Null
ANSI_NULLS OFF Statement2 : Equal Null

SET ANSI_NULLS OFF allows NULL Comparisons. But Default Null comparison is not allowed in SQL.

Ref: http://msdn.microsoft.com/en-US/library/ms188048(v=SQL.105).aspx

» Discuss this question and answer on the forums

SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.


Featured Script

Using Run As to Make Your Life Simple

Using 'Run As' when working against different domains. 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

Need a way of estimating the number of users accessing SQL - Currently we're using SQL Server 2005. We're looking to upgrade to SQL Server 2012, but the biggest issue is the...

DBCC CHECKDB with allocation error - Hi, After server shutdown in the midnight due to power failure, our DB backup maintenance plan stopped running. Executing DBCC CHECKDB...

Will the speed of index rebuild be affected by log or DB backups ? - Experts, Will the speed of index rebuild process be affected by log or DB backups ? Thanks, Smith.

MSDB Huge, and Rapid Growth !! - For some reason my MSDB database has grown huge in just a few days. June 1 backup is 3.5 gig June 2...

SQL Server 2005 : Business Intelligence

Complex and weird requirement : multi-customers Datawarehouse - Hi, We have a project of having a kind of premade-standard-retailler-datawarehouse in the Cloud (hosted on a SQL Server somewhere,...

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER - I have created an SSIS package to copy the data from an Oracle database to SQL Server 2005. the package...

SQL Server 2005 : SQL Server 2005 General Discussion

2005 Cluster to 2008 R2 single migration - We have a SQL 2005 Standard cluster with 2 nodes on Server 2003 R2 Enterprise. There are two SQL instances...

How to export more than 3,00,000 records into a single Excel Work Sheet? - Hi, I have a query which returns more than 3,00,000 records. Is there a way to get this big an...

SQL Server 2005 : SQL Server 2005 Security

Run As Account does not exist on the target system or does not have enough permissions - Hi, I got the below error couple of the times and Reboot is fixing the issue.. I want to fix it...

Run As Account does not exist on the target system or does not have enough permissions - Hi, Run As Account does not exist on the target system or does not have enough permissions

SQL Server 2005 : SS2K5 Replication

Remove a column in an article from Replication without reinitializing the replication - Hi, I have a Publication with 10 huge tables. I need to remove one column (for security reasons) from an article....

SQL Server 2005 : SQL Server 2005 Integration Services

SSIS variables not getting set when executed in a SQL job - I have a package that loops through a directory and stores all the file names in that directory to a...

SSIS 2008 - Failed to lock variable Error (Oh, I fixed it) - I have a package in SSIS that I just created. It contains 2 ForEach loop containers. Within each container are...

SQL Server 2005 : T-SQL (SS2K5)

2008 SQL Stored Procedure output to a pre-existing Excel 2003 spreadsheet - I want to create a stored procedure where the output will to into an Excel spreadsheet that will have a...

So I got this .dbf file... - And the date column is showing up like this in Excel: BDATE 380629 300517 670520 660402 470228 860421 550102 410322 491101 430612 Which, next to the Age column, makes sense that it's...

Dynamic SQL Giving 'Name not a valid identifier error' - I have some SQL I'm going to put in a stored procedure which will insert records into a temp table....

SQL Server 2005 : SQL Server Newbies

SSIS not importing zipcode data correctly into SQL from Excel - Using SSIS under MS SQL 2005 Server SP2. Using SSIS to create a package that takes data from an Excel...

SQL Server 7,2000 : Administration

Changes the database which specified spid context to the specified database - hi, I want changes the database which specified spid context to the specified database A spid:53, current database :test B spid:54, current...

SQL Server 7,2000 : SQL Server Newbies

To merge several tables into one - Hi there, My company has several instances of the same app. One app/database per client. Now maintenance has become a nightmare....

SQL Server 7,2000 : Performance Tuning

Doubling up of Alpha numeric password - I have a VB6 application and the database is housed on an SQL2000 server. My problem is that the vb6...

Doubling up of Alpha numeric password - I have a vb6 application and the database is housed on an SQL2000 server. My problem is that the vb6...

SQL Server 7,2000 : T-SQL

Dynamically change database depending on the server? - We have 2 servers: - SRV-LIVESQL with database PRODUCTION - SRV-DEVSQL with database DEVELOPMENT We have 300+ scripts that we need to manually...

Which one to use among Ident_Current,Scope_Identity and @@Identity? - Hi All, In one of the stored procedure, I am inserting a record into table t1 which is having identity column...

SQL Server 2008 : SQL Server 2008 - General

How To Transparently Relocate SQL Server connections from old to new SQL Server without User/Client Actions/Changes - We have a single SQL Server running 2 named instances (no default instances) and one is running on SQL Server...

set statistics time question - Hi All, I'm checking the performance of 2 scripts, they get the same data. When I check the execution time with [code="sql"]set...

Help with restore backups - I have two backup files that need to be restored to the same database. First file is already restored. How...

Synchronization between SQL table and SharePoint List - I am at a lost on how best to synchronize data between a SharePoint List and a SQL Table. I...

Primary data file still growing when a secondary filegroup exists and checked as default? - I have a 750gb data file that is in the primary filegroup. It is still set to autogrowth 100mb. A...

Audit changes in table using stored procedure - I would like to ask for help with creating a stored procedure to generate below information: 1- A time stampt for...

(Change in Planning) How can I add some indicator to the record which states that it is deleted by the stored procedure. - Hi frinds, I tried to search it down..for the solution , but i think now i have to make a change in...

Scheduled Job - Hi We use a CMS to run our jobs on all the servers. I have logged onto ssms on the cms...

SQLCMD Utility and Input Parameters - Hello Everyone How is your day going so far? I am working with some SQL Command scripts. I was asked by an...

Querying sys.dm_db_partition_stats instead of sys.sp_spaceused - We have a daily process that saves into a table the storage info returned by the SP, but it's procedural,...

insert records into table which record insert wrong that record only rollback remains insert - hi friend i have small doubt in sql server plz tell me how to solve issue i want insert records into...

Schemas and selecting tables with out fully qualified names. - Im a little confused. I am moving a database from 2005 to 2008. The user the application connects as is...

Why is my SQL Server slow? - Hey, I have a SQL 2008 R2, running on Windows Server 2008, with all latest service packs and updates. It runs...

Execution plans - Hi All, one of the application created in .net and using sql in the backend is given to me for identify...

SQL 2012 Restore Issue - SQL 2012 Restore Issue. Anybody ever experience this I’m trying to restore a copy of DB ‘Y’ from another server. I already...

Activity monitor > %processor time - Hi Team, In my sql server 2008R2, Activity monitor > Overview. %Processor time is not showing. do we need to do any configuration...

How to avoid Keylookup - Hi, [code="sql"]SELECT L.ClaimSummaryUID, L.ClaimLineUID, L.ChargedAmount, L.PaidAmount, L.RevenueCode FROM dbo.#Cancels A INNER JOIN ClientDB.dbo.ClaimLine AS L ON L.ClaimSummaryUID = A.ClaimUID[/code] Above metioned is the...

Qery to fetch X months old data - Hi All, I Need all your help to find out the best approach for fetching out the past Records in a...

help with query - Hi Guys I am tasked with retrieving certain info from an SQL created database. There is one particular query I am struggling...

Combining SQL versions on Cluster - Hi All Is it possible to combine SQL server 2008 R2 and SQL server 2012 on the same Cluster? We are...

Import Data replaces NULL with 0 - I am using sql server 2008 r2 express. I am getting some problems when i use import data feature to...

Performace issue while updating records and trigger on table - Hi All, I am having a performance issue while updating records into sql server table, I have created below trigger to update...

Create FirstName and LastName to Replace Existing FirstName and LastName - Hello Everyone I hope that you all are having a very nice day. I am wanting to change all the FirstName and...

Query Help - Hello I need one help in developing Query [code="sql"] CREATE TABLE #TEMP ( cStudentID varchar(09), iSchoolCode int, cGradeCode char(02), dtEntryDate datetime, dtWithdrawDate datetime, iSchoolYearCode int ) INSERT INTO #TEMP VALUES ('001223436',301,'10','2004-09-02...

Allowing users to directly query data mart - I am looking for resources to help evaluate options for allowing users to query our data mart directly. (T-SQL). These...

Creatin of role & assign users - One Employee have been asked to Create a User defined Role named (ReadOnlyXYZ) then create individual 90 login account :w00t:with read...

How to get a list of replicated articles ? - I need to programmatically to get a list of all replicated articles for a given database. I tried select * from...

SQL Server 2000 to SQL Server 2008 Migration and Performance - Hi, am new to sql server dba and recently my client has moved his db's from SQL 2000 to SQL...

Database mail sending fails with SP_Send_DBmail. - Hi, I am stuck in below problem. I have two servers; ServerA and ServerB. I have linked serverA to serverB by sp_addlinkedserver @server...

SSIS - What is the best practice for creating a fixed length flat file. - I am running into the problem of SSIS putting the CRLF in random places at the end of the row....

Agent Job Links to Another SQL Server and Fails due to Service Account Privs - To my friends in the community, here is a stumper that I can't seem to find sufficient information on despite...

Import of huge XML file - Hi All! I have an xml file of 44 Gb (Not Meg, its really GB) Delivered by the Danish custom authorities. My problem...

Help with next date in SQL query - What I need is to be able to find out when a customer next called in from the date I...

SQL Server 2008 : T-SQL (SS2K8)

How To get Characters after a string - Hi All I need to select the document file extention but I'm not getting the same results if the fileName has...

get part of the string in between square brackets - hi i have the following string '[N] 18. Is the C.B. removed from the panel? [Yes / No]' and i need to...

check value in database - Hi, I have a table in which I have stored some data from XML. Now I want to validate that data...

Counting Days with Reset - I have been struggling with this all morning, and I think I am missing something obvious. What I need to...

Selecting XML Data - Hi I'm trying to execute a script in SSMS using SQL Server 2008 R2, but it's returning the following error message:"Msg...

Break a String apart - Hello Everyone I hope that you are having a very nice monday. I have found a column that is storing incorrect data....

selecting Distinct value in a column based on values another column. - Hi Experts, I have 2 columns as below -------------------------------- taxid tax_Combination -------------------------------- Bed+Vat ! Vat Bed+Vat ! Cess Bed+Vat ! BED VAT ! Vat CST ! CST from a query i will get the...

sp_OAGetProperty @obj, 'responseText', @response OUT cannot handle large output - Hi All, This is quite weird, so thought you can have some ideas, please: I use HTTP POST to send and XML...

SQL Server 2008 : SQL Server Newbies

Need some help with some SQL Queries - I'm struggling with following issues that I would like to see if it can be resolved using SQL Queries Following is...

Database mail - (Bear with me, I have Googled this first, and done a search here! I've followed the Troubleshooting Database Mail tips,...

SQL commands to learn - Can anybody recommend a good online guide to SQL commands? Or at least a list of the ones I should...

Calculating Sales History for Months/Years - I have a table - TrnDate, TrnYear, TrnMonth, StockCode, InvoiceQty The TrnYear and TrnMonth are the Financial Periods and dont always tie...

msbi - currently i am undertaking msbi course.. will it be a added value to my resume in which direction i should look...

SQL Server 2008 : SQL Server 2008 High Availability

Moving SQL DB - Perfect Storm - Am struggling to arrive at a solution for a requirement that has come up, We need to move a live Business...

Logshipping Configuration error:3201 - [b]Hi am configuring Logshipping on SQL Server 2008 R2 servers, my primary and secondary both servers are same editions and...

SQL Cluster - Multi Instance - I have a SQL 2008 R2 Cluster name of say SQLCluster1 on NodeA with an instance name of Instance1 ie....

SQL Server 2008 : SQL Server 2008 Administration

applying sp - hi there, do we need to take SQL Services offline while applying service packs.

Stopping Job if running on particular time - Hi Experts, We have maintenance jobs running on all servers on weekends .Is there anyway i can stop these jobs if...

Transactional replication question - Hi all, I'm testing transactional replication on my laptop. I'm not able to understand as to why I'm able to add/drop...

SQL Wait Statistics - Hi All I am troubleshooting an intermittent performance issue on a production SQL Server I checked wait stats and SQLTRACE_INCREMENTAL_FLUSH_SLEEP is at...

:unsure: Backup Particular DB Users and there Permissions. - Hi Experts, Is there any way to just script out users permissions in a given db. I know there is a...

Questions on Index Rebuilding (not reorganize) - Hi Guys, Need some information on Index Rebuilding. (sort_in_tempdb = off) When performing index rebulding (for indexes > 30% fragmented), will we expect...

Transaction Log File "Does not Exist" - Help, please - I'm having problems with modify the settings on a secondary transaction log file in SSMS, so I thought I'd try...

SQLServerCentral.com : Anything that is NOT about SQL!

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

Can I have a description witha parameter ? - Hi can I use a description for a parameter(Like Crystal) Example I get clientid and clientname for query but I only want to show...

Only display parameter values that have data agaisnt them - Hi I have a report outputting health data with 3 parameters: 1 Schema 2 Practice 3 SpecialtyReferredTo I want to populate the SpecialtyReferredTo parameter values...

An error has occurred during processing of the CustomReportItem. Could not load file or assembly 'Microsoft.ReportingServices.Designer, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. An attempt was made to load a program with an incorrect format. - Need immediate help on the below issue. I'm getting above error when I run an SSRS report. Environment Details: 1. SSRS 2012...

Data Warehousing : Integration Services

Different Connection Strings in SQL Task - I have created a SSIS package that selects records from a table in one database The data retrieved is used...

Strange issue this was working fine before any issues - The process cannot access the file 'a:\test\extracted\Data1_OPTS.Data1_OPTS.287.2_4_2.20130301.034710.dat' because it is being used by another process We have two packages with names...

SSIS Job to check if rows returned and email an alert if so - Hi everyone I am new to the world of SSIS so apologies if this is a basic question !! I have been...

help writing SUBSTRING() expression please - I have strings like this: MFMGOOGLE, inside my [Business Unit] column, from which I need to strip the "MFM". Yet...

Data Warehousing : Strategies and Ideas

Help needed for creating the star schema - Hi, We are planning to data warehouse the 2 different projects , to implement this we have to create the star schema...

Data Warehousing : Analysis Services

Any difference between processing via a .dtsx and from within BIDs? - Can I be 100% sure that when Processing my cube from within SSAS(BIDS) will have the same processing done via...

SQL 2012 SSAS performance issue - Hi, We had upgraded our SSAS instance with SQL2012 Sp1 and Prior to this up gradation of SP1 there were no...