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

Risk and Assumptions

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

I was reading Buck Woody's blog the other day and he had a post on Risk where he quoted Warren Buffet: "Risk comes from not knowing what you're doing." I think that's true and that's part of the reason we'd had issues in our financial markets over the last couple of years. It's also the reason that we have issues in many of the systems that we build.

Often the people building IT systems go through an extensive evaluation to determine the needs and wants of their users. It's an attempt to identify exactly what to build so that the user is pleased, a good estimation can be made, and everyone finishes the project happy.

And that almost never happens.

There are people that are better at this than others, but the overall state of the software development process is pretty poor. That means that any project you undertake, or that your customers or clients ask you to undertake is fraught with risk. I can see why there are plenty of people that don't want to hire contractors to get software built, or that our management doesn't want to start new projects with their IT departments.

I don't have a solution, and I'm not completely sure where the problems lie, but I do know that as the economy slows, people have less tolerance for risk. That means less IT projects, and potentially the need for less IT staff and contractors. Which then contributes to the slowdown of the economy as there are less people at work.

If I were working in IT or consulting right now, I would be working to lower risk as much as possible. Adopting techniques that you are confident in, learning new skills and then applying them, even in side projects, can help grow your skills, increase the chances of projects being completed that the client is happy with, and keeping you employed. Now is the time to reassess the way you work, and find ways to show that you can work with your clients to meet their needs in an efficient manner.

Efficient in their eyes, not yours.

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 Monitor

Optimize SQL Server performance

“With SQL Monitor, we can be proactive in our optimization process, instead of waiting until a customer reports a problem,” John Trumbul, Sr. Software Engineer. Optimize your servers with a free trial.

SQL Data Generator

Generate realistic test data, fast

“In less than the time it took me to get my coffee, I had a database with 2 million rows of data for each of 10 tables.” Stephanie Beach, QA Manager. Try SQL Data Generator now.

SQL Backup Pro

"A real time saver" Andy Doyle, Head of IT Services

Andy and his team saved time by automating backup and restores with SQL Backup Pro. Find out how much time you could save. Download a free trial now.

Featured Contents

 

Stairway to Server-side Tracing - Level 2: Creating a SQL Trace Using T-SQL (Stairway Series)

Dan Guzman from SQLServerCentral.com

This is the second article in our Stairway Series on SQL Tracing. Learn how to build traces in code with T-SQL. More »


 

Improved Application Availability During Online Operations in SQL Server 2014

Additional Articles from Microsoft MSDN

SQL Server 2014 introduces enhancements to two common database maintenance operations namely Partition Switching and Online Index Rebuild which greatly increases enterprise application availability by reducing maintenance downtime impact. More »


 

From the SQLServerCentral Blogs - Good Advice on "Certification"

Buck Woody (blog | twitter) is often seen as one of the wise men of the SQL Server community and with... More »

Question of the Day

Today's Question (by Sergiu Ungureanu):

In SSMS open a new query and run the followings (assume the default server language is us_english):

set language 'us_english' --just to make sure
select @@LANGUAGE
select @@datefirst

set datefirst 2
select @@DATEFIRST

set datefirst 3
select @@DATEFIRST

Open a new query in the same DB and run this again:

select @@DATEFIRST

What value will it retrieve?

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 1 point in this category: @@DATEFIRST.

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

SQL Backup and Restore

A DBA's tasks, from day-to-day, are rarely constant; with one exception: the need to ensure each and every day that any database in their charge can be restored and recovered, in the event of error of disaster. In this book, you'll discover how to perform each of these backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Red Gate's SQL Backup tool.

Get it from Amazon in print  or download the ebook for free from Red Gate

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

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

Answer:

  • change the protection of the private key to the database master key
  • remove the private key
  • change the password for the certificate encryption

Explanation: You cannot change the meta data for a certificate: the subject, or dates. However, you can remove the private key, load the private key, change the password protecting the certificate or change the protection from a password to the DMK.

Ref: ALTER CERTIFICATE - http://msdn.microsoft.com/en-US/library/ms189511%28v=sql.90%29.aspx


» Discuss this question and answer on the forums

Featured Script

Prevent DISK FULL when database grows

Gonzalo Moles from SQLServerCentral.com

This procedure helps you to know when the database space free is over the @percent filled up and the disk space left is below  @M_free_high bigint OR the disk space left is below < @M_free_low

Example:  EXEC msdb.dbo.[get_space_free] 2000, 15000, 80 
This means 'Give me information of all databases filled at 80% capacity with less than 15 Mbytes (15 Gbytes) of disk space OR just all database drives with less than 2000 Mbytes (2 gbytes)'
Very useful to prevent DISK FULL when database grows.
This is the display: 

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


SQL Server 2014 : Development - SQL Server 2014

how to write a query with subquery - i want to query for consumer, phone and address, SSN information consumer table is Consumer Phone are in table ConsumerPhone with FK...

how to write query - Hello I have to generate a report in which amount should be 13 digits. e.g 2000- convert it into paisa 200000 then...


SQL Server 2012 : SQL 2012 - General

Selectively insuring encryption on a connection - I have several machines at AWS EC2 and would like to insure connections coming in from the outside are encrypted....

capture the cpu & memory details store into excel sheet - HI, how to capture the cpu & memory details through powershell and store the results into excel sheet. Advanced Thanks!!! Regards, raj

file copy - I copied a 200 GB backup file from one server to another. I started this at 3 PM yeasterday when...

running sql 2012 and sql 2008 on same server - Hi, our client to want to install both csql 2008 and sql 2012 on same windows cluster. IS It possibility of running...

multi-valued report parameter weirdness - SSRS 2012, SQL 2012 I watched Brian Knight's video on filtering a report using a multi-valued parameter (here:[url]http://www.sqlservercentral.com/articles/Video/64369/[/url]), and after seemingly...

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


SQL Server 2012 : SQL Server 2012 - T-SQL

How to Split An Incoming Array Parameter from VB6 into SQL Server 2012 - I have a need of passing-in an array of email addresses from a VB6 application to SQL Server 2012 stored...

Help on a complex query that may be easier than what we have?? - Hello all. We have a solution to a problem but think there is a much easier way to do this...I was...

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

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

Compare View and Table... - A Table and View having same structure and amount of data 40 Columns and 1.5 Million Data... This data has to...

Early Month end processing, How do you handle the dates? - We have always waited until the first of each month to start our month end processing. We would use code...

Correct permissions after restore from another domain? - Hi, I've been restoring databases from another domain. As a result the users that are restored with the db have the...

Import Text File to SQL Database - I have a text file with no delimiters and fixed width fields that I need to import into a SQL...

Delay in Update Large Table Wtih Trigger - Hi all, I have the following statement in a trigger, in order to update a link table that has 5000000 records. UPDATE...

Select record from group - I was assigned a difficult project. It is beyond by skill. I need expert to help me. From sample data, 1)...

How to add this condition into where clause? Thanks. - I need to have a query like: declare @dt bit select * from document where case @dt when 1 then doctype in (1,...

MS Security patch MS11-049 (KB2494086) fails on SQL R2 build version 10.50.1753. - Trying to install MS Security patch MS11-049 (KB2494086) on SQL Server 2008 R2 build version 10.50.1753. The patch installation fails in...

Datediff in means - Hi all, Please what is the syntax for datediff in minutes. I have tried the expression datediff("m", fields!startddate.value, fields!enddate.value) but it is...

Relation ship in SQL Server - i want to create Primary key for 3 columns in parent table and foreign key in for 2 coloumns in...

SQL job owner - What is the purpose and significance of the SQL job owner setting on a job? I've never considered this before, but...

replication - i did not implement replication in my current environment , i have 4 publishers iwant to know how many distribution and...

SSAS Instal in a Cluster - I need to install SSAS as an extra feature on a 2 node SQL Cluster. This will have to run...

Run Query and email results as CSV. - Morning everyone. I have a query that works ok , and uses msdb.dbo.sp_send_dbmail to send the results. The results in .csv look terrible. Can...

Select several subqueries with more than 1 value - Hi. There is no problem with this query [code="sql"] select (select 'hello'), (select 'world') [/code] But if one select statement return more than 1...

String as a date format - Hi All, From other source I am getting a data as string format like 'Sep 6 2013'. I want to see...

sp_register_custom_scripting - Hi All, I have replication configured using a third party tool (power exchange). I get issues whenever i execute schema level...

Max Memory - I have set Max Memory to 6 GB. But SQL Server is using only 2.9 GB Query used: SELECT object_name, counter_name, cntr_value...

Why is my logon trigger blocking logons? - I've created the following trigger to track logons: [code="other"] Use master Go Create database AuditDB Go USE [AuditDb] GO CREATE TABLE [dbo].[ServerLogonHistory]( [SystemUser] [varchar](512) NULL, [DBUser] [varchar](512)...

SSIS PACKAGE DATA DUMP FROM SQL VIEW TO CSV AND PUT IT ON A FTP SERVER - Hi, I have a scenario where I need to create a SSIS Package and make it run automatically every night. What the...

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

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

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

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

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

Error DTExec: The package execution returned DTSER_FAILURE (1) - Hi, A Maintenance Plan Job for UpdateStatistics is scheduled from SQL Server 2008 and it is a weekly job (Sunday). Last...

Boss keeps creating views as a quick fix - Good Morning Everyone. This is my first post in the forums, but I find the emails very informative and knowledgeable. I...

Extracting Data From a MySQL Linked Server - Hi all, I am using MS SQL 2008, and I am trying to extract data from a MySQL database. I am...


SQL Server 2008 : T-SQL (SS2K8)

Exclude rows - Hey, Say I have a table with the following rows; Case | Hours | Type xxx | 3 | 1 xxx | 3 | 2 xxx | 4 | 1 xxx | 1 | 1 and I...

Add Table to Different FileGroup - Hello Everyone I would like to create a new filegroup and add, for now, only one table, in the new filegroup...

The working of PIVOT - Can you please explain how PIVOT is used in this query? i.e. logic behind the working of PIVOT in the...

Reset Identity Column only on 1 Times when Month have changed - Hi, I want to reset an Identity Column 1 Time in Month (by T-SQL -not job). How can I make sure that...

Query Help - Hi I want to update Flag column in second table based on the Adder names. If the Applicatiion has atleast one...

Only functions and some extended stored procedures can be executed from within a function. - Hi, I have this function called fn_GetTimedKey() when I run it in my SQL2005 environment it works fine. When I run it...

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

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

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


SQL Server 2008 : SQL Server Newbies

Creating Database: Error Message. - [code="sql"] CREATE DATABASE ApressFinancial ON PRIMARY ( NAME = N'ApressFinancial', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ApressFinancial.mdf' , Size = 3072KB, Maxsize = Unlimited, FILEGROWTH = 1024KB ) LOG ON (Name...

making records unique - Hi everyone, I've a table with 15 fields with 70,000 rows of data. I now realise I need to make...

restore database bakup - how to restore a database backup in sql server 2008 on particular time scheduled

problem with subtracting one millisecond - Hi, what I am doing seems to be very simple. I just want to subtract one millisecond from midnight to receive...

Comparing a to a substring - I have a filed UserName as for Table A "Z109032, My name" and a query as SELECT LEFT(UserName, 7) as CAR...

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

Add a record to a replicated table..... - Hi I am quite new to replication but i did my research and have setup transactional replication between two servers and...

SSIS Assigning a value to a package variable - Hi All Have a foreach loop that iterates through files. If an error it writes log and I simple want to...


SQL Server 2008 : SQL Server 2008 High Availability

Central publisher with two subscribers merge replication - [b]Scenario:[/b] I am (not very professional) a DB admin of a company located in CityA with two branches in CityB and...

Transact log shipping not working - I am able to create and successfully run the 1st transact log shipping wizard and it finsihes and creates the...


SQL Server 2008 : SQL Server 2008 Administration

SQL Server Browser - Hi, I am working with a cluster with 2 nodes, active-active. The nodes have several instances and in total 3 different...

Transaction Isolation question - The isolation level of the database that I'm running the query on is READ COMMITTED. I ran query 1 and...


SQL Server 2008 : Data Corruption (SS2K8 / SS2K8 R2)

DBCC CHECKDB ON PRODUCTION SERVER? - Hi All, We perform a monthly restore activity on a Test Server and then run CHECKDB on the test server itself. There...


SQL Server 2005 : Administering

Interpret Output of TSQL_Replay trace - Can someone please tell me which column provides the actual execution timings based on the output from TSQL_Replay trace?


SQL Server 2005 : Business Intelligence

Open a report by clicking another report - Hi, Is it possible to click a chart / X axis value, grab the values as parameters and open another report passing...

Calculate measures and mdx - In cognos, we have complex measures calculated by joining multiple tables, and we can see the SQL queries in IQD...


SQL Server 2005 : SQL Server 2005 General Discussion

copy permission of a logins to a different server - I have a server A and a Server B. An user call SQLuser on both Serves. Both Server A and...

True Aging Report - I have worked with SQL for many, many years and have never had the need to ask a question that...


SQL Server 2005 : SS2K5 Replication

Replicate Indexes - Hello, Good Morining! I need your knowledge about replication. I have been replicated a database, but I have to add indexes...


SQL Server 2005 : SQL Server 2005 Integration Services

SSIS Package - Change connection property without opening package - I am using SQL Server 2008 SSIS. I used to have a DTS package using Dynamic property task so when I...

SSIS Package fails when scheduled a SQL Agent job - Hi All, I have a SSIS package which get the result from a query and dumps that in a TXT file. It...

how to merge single page tiff file to multi page tiff file - Hi I have a requirement to merge single page tiff files stroed in a table to a muli page tiff file... Can...


SQL Server 2005 : T-SQL (SS2K5)

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

Using a variable for a linked server name in stored procedures - Hi, We have a number of SQL db environments for our CRM system, e.g. LIVE, TEST, DEVELOP, etc. and occasionally we...


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


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


Reporting Services : Reporting Services 2005 Development

SSRS 2005: Allowing the user to specify sort order - I've got a report I recently did in SSRS 2005. The users want to be able to sort the returned...


Reporting Services : Reporting Services 2008 Administration

SSRS Authentication pop up is not available - I have migrated SSRS reports from 32 bit Windows Server 2003 system to Windows server 2008 R2 64 bit system. The...


Reporting Services : Reporting Services 2008 Development

Repeat group header after each group - I am displaying the data in a Matrix and need to view my header after each group . Found lot of...


Programming : General

How to read xml files from App.config file path? - I have been on working windows console application to import xml data into database. I am new to console applications, I...


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

Unable to Execute SSIS Package from Visual Studio 2012 in Debug Mode - The issue I am having is when a right click a component within the SSIS package - eg within the Data...

Connect from SSIS 2008R2 to SQL Server 2012 - Is it possible to connect from a SSIS 2008R2 package to a SQL Server 2012 database? The SSIS 2008 R2...

SSIS Package WinZip all Files in a Folder with spaces in the folder path - Hello All, I have a SSIS package that uses an execute SQL task to locate and configure the package with parameters...

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 etl tables backup concept - Hello, I have a production database which is loaded regularly via SSIS packages. In the database there are etl (schema) tables which...


Data Warehousing : Analysis Services

How to direct a data source view to another data source - Hi, We are doing a parallel migration from SQL Server 2005 to 2008 R2. We've moved the database, Integration Services and...


Data Warehousing : PowerPivot

SSAS and PowerPivot - Just trying to get my head around something : What is the advantage, or reason why someone would use an SSAS...


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!

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


Microsoft Access : Microsoft Access

Pivoted reports - I want my report to be pivoted. my data product - amt - month - Year abc - 9.99 - 7 - 2013 abc - 39.99 - 7 - 2013 abc - 19.99 - 7 - 2013 abc...

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