SQLServerCentral - www.sqlservercentral.com

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

Featured Contents

Featured Script

The Voice of the DBA

Versions of Disaster

Today's editorial was originally released on Jan 13, 2009. It is being re-run as Steve is on vacation.

I wrote about versioning of old software recently and how I had to restore an old version of SQL Server in response to a lawsuit. We had some challenges because the backup file that we had was from years before and we weren't sure which version of SQL Server we needed. I forget how we finally determined which service pack was needed, perhaps we read master somehow to get a build.

In any case, when you apply patches or change how SQL Server functions, you can change the way that code is executed or even the results that might be returned to an application. You would hope that code would break and error out rather than return different results than you expect.

Since many of us patch servers when Service Packs come out, or when we find a hot fix we need, and we are constantly deploying and changing code, do we pay enough attention to the server version as we make these deployments? I started thinking about this after the last editorial and I think that we often take it for granted that we can easily recreate our environments.

Consider what would happen in the event of a disaster. Suppose that one of your server instances, any particular instance, died and you had to go back to a backup of the database, would you know what version of SQL Server is needed? Do you know what version each of your instances is using right now?

In some ways this makes me think that only installing RTM and Service Pack versions in your production environment is a good idea. It's easier to track things if you keep all your instances within a very narrow band of versions, and the worst case would be attempting a restore on RTM, then SP1, then SP2, etc. until you hit the correct version. Imagine now if you had to work through the various builds on my build list.

I used to think that I'd want to keep current on my patches. In one large environment, we were actually pretty good about deploying patches to hundreds of instances inside a month, so we always had a large percentage of our servers, and usually all the critical servers, at the same patch level. However if a disaster had occurred within the month, we wouldn't necessarily have been sure of what versions were installed.

I really don't have a great recommendation on how to handle this other than build some automated system that tracks the current build number on a daily basis, perhaps even putting it in each database. At least then you'll have it handy in the event of a disaster.

Steve Jones


The Voice of the DBA Podcasts

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.

Steve Jones from SQLServerCentral.com

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

ADVERTISEMENT
SQL Compare

Need to compare and sync database schemas?

Let SQL Compare do the hard work. ”With the productivity I'll get out of this tool, it's like buying time.” Robert Sondles. Download a free trial.

SQL DBA Bundle

‘10 Tips for Efficient Disaster Recovery’

Learn Steve Jones’ disaster recovery lessons, and be better prepared for future disasters, big or small. Read now.

SQL Doc

Hate explaining your database in meetings?

SQL Doc quickly documents your entire database schema so that you can easily present it to others. "This tool is embarrassingly easy to use." David Hayden, DavidHayden.com. Download a free trial now.

Featured Contents

 

Soundex - Experiments with SQL CLR

David Poole from SQLServerCentral.com

Using test driven development to turbocharge Soundex More »


 

SQL Saturday #213 Providence, RI

Press Release from SQLServerCentral.com

Join us for a free day of SQL Server training and networking on September 21 in Providence Rhode Island. Featured speakers include Paresh Motiwala, Wayne Sheffield, and Jason Brimhall. More »


 

SQLskills and SQL Intersection

Press Release from SQLServerCentral.com

This fall SQLskills is running the SQL track at SQLIntersection, with an amazing lineup of speakers and sessions. Join Paul Randal and Kimberly Tripp in Las Vegas for the event, Oct 28-30. More »


 

Condensing a Delimited List of Integers in SQL Server

Additional Articles from SimpleTalk

In real-world applications, it often makes sense to show denormalized data, such as delimited lists, within the application's user interface. Dwain Camps shows why, and how, the distribution business stores information about 'islands' in sequences in order to track the status of the shipping of a consignment. It makes a great SQL puzzle. More »


 

From the SQLServerCentral Blogs - T-SQL Tuesday #46 Rube Goldberg Machine aka Automating Deleting Older Certificates

SQLBalls from SQLServerCentral.com

Hello Dear Reader!  This is the second Tuesday of the month and you know what that means, T-SQL Tuesday the... More »

Question of the Day

Today's Question (by Steve Jones):

What can change with the ALTER CERTIFICATE command? (choose 3)

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


We keep track of your score to give you bragging rights against your peers.
This question is worth 2 points in this category: T-SQL.

We'd love to give you credit for your own question and answer.
To submit a QOD, simply log in to the Contribution Center.

ADVERTISEMENT

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

How many results are returned from this batch?

DECLARE @i TABLE( mychar VARCHAR(10));

INSERT @i VALUES ('Steve'), ('Stephan'), ('Stephanie')
               , ('Sterling'), ('Steppenwolf')

SELECT mychar
    FROM @i
    WHERE mychar LIKE 'Ste[^p]%'

Answer: 2

Explanation: The correct answer is 2. The carot (^) as a wildcard performs a "not" match. In this case, those items that are like "ste" but not "step" are returned.

Ref: ^ Wildcard - http://msdn.microsoft.com/en-US/library/ms188342%28v=sql.90%29.aspx


» Discuss this question and answer on the forums

Featured Script

Analyzing Dangerous Settings in SQL Server

Rudy Panigas from SQLServerCentral.com

Hello everyone,

To analyze dangerous settings in your SQL Server, just copy, paste and execute this script.

Please test this on you development system first.

Once executed the script will detect your SQL server setting and look for any dangerous settings. If dangerous settings are detected, text will be displayed with more information. No settings are changed so you will have to make the changes manually.

Only use/change dangerous SQL server setting when instructed by Microsoft support to fix a specific issue/problem.

Thanks,

Rudy Panigas

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 2014 : Administration - SQL Server 2014

Place system and data files on the salme disk - Hi, Our DBA is currently installing a SQL cluster attached to a SAN. In order to optimize disk space usage, he...

Tempdb data file fills up very often - Tempdb fills up very often. tempdb configuration is name fileid filename filegroup size maxsize growth usage tempdev 1 N:\Data\tempdb.mdf PRIMARY 29428480 KB Unlimited 10% data only templog 2 N:\Data\templog.ldf NULL 512 KB Unlimited 10% log only I want to know why its happening very often...

High Memory consuming by Database - Hello Everyone, I have a database which size is 250 GB and it contains the information about 70 Lak users with...


SQL Server 2012 : SQL 2012 - General

Management/SQL Server Logs - Hi All, Our sever logs show a lot login failed Messages, we actually don't feel we have login problems. Even during...

Syncing/Refreshing data from one environment to another - Hi, We have dev,test and prod environments. The developers make the DDL and data changes on dev and the we will...

Data Type Money is adding extra zero - Trying to copy one table to another in sql 2012, data type was set before me. Strangely, my end result...

Installed wrong edition - Hi, I installed Standard Edition of SQL Server 2012 instead of Enterprise Edition. Do I have to remove all of the...

Become a License Reseller or Not? - I've been pondering this question for awhile now and i just can't seem to find worthwhile information without initiating the...

GEO - Spatial - I need to find the name of the county based on longitude and latitude values. Could someone give some ideas...

SQL server cluster Failover installation - Hi guys i am supposed to do a failover installation on Windows server. I have to create a cluster group, Do...


SQL Server 2012 : SQL Server 2012 - T-SQL

Help On Query - Hi Everybody, [code="plain"] Create Table #sample ( Name Varchar(100), Mark1 int, Mark2 int, Mark3 int) insert into #sample values ('Vignesh',100,59,95) insert into #sample values ('ram',23,45,33) insert into #sample values...

Query plan parameter run time value not appearing in sys.dm_exec_query_plan - Hello --- when looking at a simple query plan for a select with "Include Actual Execution Plan" enabled in sql server...

How do I convert column data into row data? - table gennum tollnum, n1, n2, n3 800123, 1234, 1235, 1236 999123, 9876, 9875, 9874 I want to my data to look like: tollnum,code 800123, 1234 800123, 1235 800123,...


SQL Server 2008 : SQL Server 2008 - General

Calculate totals in each category by day - I need to calculates for each of a category by day, and I need to carry over those totals where...

Strange Working of Sql server database mail- Need urgent help - It may be a stupid question/problem,,, But i am struck up in a situation where i am not able to...

sys.dm_db_stats_properties WHERE modification_counter < 0 - Hi, In SQL Server 2008 R2 with SP2, we can get statistics that are [b]NOT modified[/b] using the query [i]SELECT * FROM...

Use data from Flat File for Variable in SSIS 2008 - I am trying to use a text file to provide a variable in an SSIS package so that the end...

Possibly Dumb Question - Why would you want to add SSAS or SSRS as a feature of a database engine instance as opposed to...

Total calculated in stored procedure for report sometimes returned as 0 - This is a problem that I thought was a Reporting Services issue but now realize is something different, possibly in...

How to generate sequence of rows? - Hi Friends, I have a table having some data where i want to generate sequence of rows based on premise value....

optimize pivot query - Hi, I did this code to select 2 best selling prodID (by quantity) for each Year period and put it in...

Getting duplicate values evenif used distinct - Hi All, I used distinct in my query but then also I am getting almost 100K of duplicate records, below is...

set SET FMTONLY ON/OFF at database level - Developers are having an issue with BizTalk and SQL Server . They asked me if we could SET FMTONLY ON at...

One sa password to rule them all? - Just wanting to get your views on the use of a common sa password for all instances vs individual sa...

Combine fields - Hello, I have to create a table and fill it with data from another tables. My question is can you held me...

different IP address can connect SQL? - Hi, we have two IPs configured in server one is Primary IP and Backup IP.. whenever install SQL Server by default taking...

Difference - What is the difference between T-sql and sql server?

Backup Incomplete - Hi Guys We have a VB application which includes SQL Express 2008R2. As part of that, we include an application which...

DBCC FREEPROCCACHE - What are the best practices around using DBCC FREEPROCCACHE to free up space in TEMPDB when it won't shrink? TEMPDB ran...

Format equivalant function in sql server 2008 - Team, I have used format function sql server 2012 to get month number as "09" select format(dateadd(m,-11,'2013-08-01'), 'MM') Could you please help me...

Simple Transpose - why can't I do it?? - Guri (is that the plural of Guru?) I have a result set that I need to present in a graph: Results: Category PrevWk2 PrevWk1 CurrWk BlueStore ...

SSIS Package runs as a package but won't run in job scheduler - I have a package that will not complete in the job scheduler, runs fine as a package. Part of the...

Index rebuild Maintenance Plan may not be running properly - Hi I have 2 jobs from a maintenance plan and 1 backup job from sqlbackup (redgate). The job are scheduled as...

SSAS dimention filter - Hi All, I am working on SSAS Project… and stuck on following issue.. Currently having following scenario.. Having ProductCategory, Product, SalesOrder, Time dimensions… Sum...

tempdb data files - move/initial size - I discovered that we have a couple data files assigned to our tempdb. The primary data file (tempdev.mdf) is on...

Investigate mail service notifications on DB server - Hi Fnds, i am looking to find kind of investigation notifications from sqlservers. so i want to identify those servies which...

SQL Server 2008 using more than Max Specified Memory - We have a newer SQL Server 2008 R2 machine running on Server 2008 x64, and we noticed that the sql...


SQL Server 2008 : T-SQL (SS2K8)

Trapping error from sp in another db - SQL Server 2008 R2 Two computers: MachineOne and MachineTwo Two instances of SQL server,: MachineOne.ServerOne and MachineTwo.ServerTwo Two databases:MachineOne.ServerOne.DBOne, and Machine2.Server2.DBTwo. On [MachineTwo].[ServerTwo].[DBTwo] I...

SELECT TOP ???? - Hello Everyone I am a bit curious after doing some accidental testing. I was going thru someone elses code, and noticed...

SP_SEND_DB_MAIL Alignment Issue - Hi I have a script, which when ran inserts values from multiple tables into another table, then executes 'sp_send_dbmail'. I use...

variable product costing - Not sure how to explain this but here goes... I have a forecast of volume for a product for the year....

Compare a date to a previous group of records' max date - I want to reference a previous group's max date and compare it to the date on the current record. My data...

Weird Date Issue slowing down Stored Proc - Hi All, can anyone help shed some light on the following issue... SQL Server 2008 SP. My SP returns data almost instantly...

IF EXISTS ( SELECT 1 ......vs..... IF EXISTS ( SELECT * - Hi, is there a difference in terms of performance?. The execution plans are identical. Does an index on or off, an...

I can't make heads nor tails of this.... - I was given this query this morning. It's not a pattern of syntax I can ever recall seeing before. I...

Function Vs Stored Procedure - Hi Every one, We all Knows that we call function through select and Sp through Exec.My Question is why can't we...

concatenate with leading zeros - Hi Everyone I am creating a view which involved concatenation of 2 int columns. The data in the columns look like...

Complex hierarchy: how to build? - (SQL SERVER 2008 R2) Hi guys, I need some help on how to retrieve Hierarchy in a table. In this case, the example...


SQL Server 2008 : SQL Server Newbies

Inserting a record into a table - I have a table as follows ID Type XID Note 21 Note 1 This is a note 21 Note 2 This is not...

Creating a CSV file from a Trigger - Hi All, I am in the process of starting a project and wanted to understand the best way to automate the...

Quotes Identifire Default value - Dear all We are using SQL 2012 Express edition & Enterprise edition. As per the http://technet.microsoft.com/en-us/library/ms174393.aspx default value for Quotesidentifier is...

Alternative to Cursor - I am looking for a way to call a stored procedure for each record of a select statement. I have read...

comparing a column to Todays date - how do we compare a column name "LogDATE" to todays date and if its exists we print out a statement...

Pivot views - I am currently reading Itzik Ben Gans book about Microsoft Server 2008 TSQL Fundamentals and I have run into another...

Does stored procedure exist? - Hi all! I want to check about a sored procedure does exist before i Create/alter it. How to? When it's about a table,...

Recalculate Wages (Reposted for SQL 2008 with additional columns) - Recalculate "Amount" Column -------------------------------------------------------------------------------- Our Salaried Employees have to log in their time by department and by type of Earnings (Regular, Holiday,...

I have written a Stored Procedure having 3 nested while loops in it .This is Working fine and I am getting desired result as I have tested this on small set of data ,but while running on large set of records it taking huge of amount of time, might be due to multiple nested while looping . Co - Hi Guys, I have written a Stored Procedure having 3 nested while loops in it .This is Working fine and I...


SQL Server 2008 : Security (SS2K8)

Deleted all users - Hi, We have a prod server that is 2005. Then we have a dev server that is 2008R2. We copied over the...


SQL Server 2008 : SQL Server 2008 High Availability

Transact Log Shipping - I am trying to set up Transact Log shipping to test the theory for management. I am having trouble though...

Domain controller needed for Clustering? - I started on a new job and I noticed they're using 2 redundant domain controllers on the host. I asked...

Regarding Architectiure - Hi i need information regarding Architecture. for this how can we start the process and what are the requirements we required is...

What are ways of Exporting data of Wahehouse Database from Production to Development Server? - Hi, I would like to export data of warehouse from Production to dev for special reasons, what are best possible ways for...


SQL Server 2008 : SQL Server 2008 Administration

Is sys.dm_db_index_usage_stats a relatively good indicator of DB useage? - I'm trying to determine approximately the last time a database was "touched" by users. Thus far, sys.dm_db_index_usage_stats seems to be...

Adding a Node to a cluster SQL Server 2008 R2 - Hi All, Just looking for a bit of clarification on the steps i need to take to add a node to...

How to fix Logon trigger issue - hi, I have this trigger (see below) and I get the login failure due to trigger execution (SQL error 17892) every...

Query tuning performance - how can we get execution plan of a query executed on two different days so that i come to know...


SQL Server 2008 : SQL Server 2008 Performance Tuning

Moving to new servers - We are moving our databases from some virtual servers to physical servers. There are numerous difference other than virtual & physical...

What is the difference between sp_who2 and sp_whoisactive which one is better - Hi all, What is the difference between sp_who2 and sp_whoisactive which one is better i have found a script for sp_whoisactive...


SQL Server 2005 : Administering

Linked Server error - Dear All, When I tried to create a Linked Server I get the following error message would you please let me...

Getting an error "Exception of type 'System.OutOfMemoryException' was thrown." during stored procedure execution - Hi I am running a stored procedure that have lacs of select command but query is showing this error. "An error occurred...

copy files through network to another server - I'm planning to upgrade sql server 2005 to 2008. For now I'm just planning on it. If we use side...


SQL Server 2005 : Business Intelligence

More data extract - expert opinion - Good Day to you all. I need your expert opinion on handling the following scenario: In our Service we are running MS-SQL...

Slow queries in reporting services! Bug or feature? - Hi all together, in my company we are using reporting services to provide some statistics and reports to our customers. In...


SQL Server 2005 : SQL Server 2005 General Discussion

How to get the correct Return_Amt from a table? - Please can anyone help me? Table Name: tableSTK ItemID varchar(8) ItemDesc varchar(100) Price money Table Name: tableABC ItemID varchar(8) ConditionAmt money Return_Amt money The ConditionAmt and Return_Amt in the...


SQL Server 2005 : SS2K5 Replication

sp_MSsubscription_cleanup blocking sys.sp_MSadd_distribution_history - Hi, Just getting started on understanding blocked processes and how to resolve them. Last night between 23:05 and 23:08 150 - 200 Blocked...

sp_reinitsubscription multiple subscribers - we are trying to target the re-initialization of a subscription at certain subscribers at the same time. the following has been...

Multiple subscribers for one publication - I have two subscribers pulling from a single publication. I need to create a third subscriber. I would like to...

How not to replicate certain delete statements - I have a database for an order handling system that is replicated to an other database. The publisher and distributor database...


SQL Server 2005 : SQL Server 2005 Integration Services

SSIS Flat File Import Error - Hello, I am having an issue importing a flat file into an SSIS package. The file I am importing has a...

SSIS Sheduled package not working - Hi all, A package has been created and I have scheduled it, when it runs it errors. The description to the...


SQL Server 2005 : T-SQL (SS2K5)

how to get data from a table all in one result row - I'm need help with a query. The table has an id, a name, and a type. Sample: ID Name Type 123 description...

Pivot Help--I need to Pivot really to just make the rows the column - [code="sql"]SELECT CONVERT(varchar,C.SCHED_DTTM,101) as DATE,C.INV_ITEM_ID ,SUM(C.QTY_REQUESTED) AS QTY FROM PS_IN_DEMAND C WHERE C.SCHED_DTTM>=GETDATE() AND C.IN_FULFILL_STATE IN (10,20,30,40) GROUP BY C.SCHED_DTTM,C.INV_ITEM_ID ORDER BY...

request on a query to capture the cell area a given x,y coordinate would be found in? - Hi, I have a problem I would like to solve that I believe is possible with SQL query although I don't...


SQL Server 2005 : SQL Server Newbies

Return value if field not there in Query - I want to check field/column exist or not in a table and populate the value Example: Does the column1,column2 exists: case1:return column1...


Reporting Services : Reporting Services

Same report in two folders on the Web Interface - Hi We use the Web interface for users to run reports The interface has folders. I wanted one of the reports to...

can two server have the RS installed on it but using the same reporting DB (on the DB server) at the same time ? - Hi, can two server have the RS installed on it but using the same reporting DB (on the DB server) at...

Can we display some text message before exporting data in SSRS - Hi Folks, Hope all are well!! I have the requirement to display some text message(like you are the responsible for the data...


Reporting Services : Reporting Services 2005 Administration

Cannot start Reporting Services Service under certain AD accounts - Hello. We have an AD account set up which we use to run our Reporting Services instance under. However, following...


Programming : Powershell

Who am I ? - Hi, I run, now, my jobs with steps written in powershell. I would like to get informations about the current job, the...


Data Warehousing : Integration Services

XML Source Adapter converts empty string into NULL - For the string data type, the XML source adapter converts incoming empty (zero length) strings to NULL. This means there's...

SSIS Dataflow source, inline query or SP - I realized I have an inconsistent philosophy. When working in Reporting Services I always use stored procedures for datasets. However,...


Data Warehousing : Strategies and Ideas

DWH Practice - Hi all, I'm looking for some advice for practicing DWH architecture/development with scenarios as close as possible to the real...


Data Warehousing : Analysis Services

strange behavior from Analysis service - Hi all, so I am new to the company and working on this package that builds cubes. SQL Job constantly fails with...


Data Warehousing : Performance Point

Multiple Roles in one data connection (or duplicate reports instead) - Hi, I have an SSAS cube with 2 roles- Agent Role- shows each sales agent his own deals only Africa Role-...


Database Design : Virtualization

For a Mac, should I get VMware Fusion or continue using Parallels? - ok. 1st, I've used both products for quite a while. I have VMware on my PC Laptop, and Parallels on my...


SQLServerCentral.com : Anything that is NOT about SQL!

Fantasy football 2013 - I renewed the league, you should be getting an email soon. At the moment, there are no open spots, but...

SP_UpdateStats - Hey Guys, I read this nice article from Grant on Simple Talk: [url]http://www.simple-talk.com/sql/database-administration/grant-fritcheys-sql-server-howlers/[/url] In this article, Grant says that executing sp_updatestats will update...

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


Career : Employers and Employees

advice please - I have one year exp in sql server 2008 ssrs reporting I have learned basics of t-sql and recently I have...

To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2013 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com