In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL DBA Bundle Top 5 Hard-earned Lessons of a DBA
New! Read Lesson 3, ‘Six Scary SQL Surprises’, and learn from Brent Ozar and the other experts in the DBA Team. Read now.
 
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 5GB free hosted storage with SQL Backup Pro
Download SQL Backup Pro and securely copy your backups to secure hosted storage. You’ll get your first 5GB of storage free. Try it now.

In This Issue

Ordered Loading of Databases Tables Using SSIS

Finding the load order for inserting data into a that respects referential integrity is sometimes difficult. If you are using SSIS, the task is made easier. More »


Managing the SQL Server Transaction Log: Dealing with Explosive Log Growth

You've just become responsible for a database, only to find that the log file is growing out of control. Why is it happening and what do you do to correct it? More »


From the SQLServerCentral Blogs - Getting the lowdown on 18456 errors

If you can establish a connection to a SQL Server, but are having problems logging in to it, you will... More »


Editorial - Most important DBA Skill?

Today we have a guest editorial as Steve is traveling.

If you were interviewing for a SQL Server DBA position and they asked you, "What is the most important skill that a DBA should have?", what would you say?

Technical skills? Working under pressure? Ability to work as part of a team? Ability to follow the standard procedures and rules that apply to your position?

All of these are important, but I think that one of the most important skills, if not the most important, is people skill.

I have known lots of people with excellent technical skills. They were smarter than me by a long shot, but they could not communicate with people to save their lives. They were either socially hindered, arrogant, or just plain rude when it came to talking to our customers. This led to our customers requesting that DBA X not be assigned to their project. The DBA in question was very good at his job from a technical point of view, but he was condescending and short tempered. He did not play well with others and this caused tensions inside the DBA team until the DBA in question finally left, much to the relief of all.

I would rather hire someone with a little less experience and technical skill who is a great communicator and people person than an expert who cannot relate to people or is rude and arrogant. If you have the right base skills, you can always learn the technical stuff. Learning the people stuff is not so easy.

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

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


Question of the Day

Today's Question:

What would be the output of this batch?

DECLARE @ToCreate bit;

IF @ToCreate = 1
    DECLARE @Table TABLE
    (id int, name varchar(50) )
ELSE
    INSERT INTO @Table (id, name)
    select 1, 'a';
    
SELECT * FROM @Table;

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

Which of the following hints require the word WITH?  (choose 3) Assume SQL Server 2012.

  • ROWLOCK
  • NOLOCK
  • FORCESCAN
  • FORCESEEK
  • XLOCK

Answer:

  • ROWLOCK
  • XLOCK
  • NOLOCK

Explanation: Omitting the WITH keyword is a deprecated feature and will be removed in a future version of Microsoft SQL Server. You should avoid using this practice in new development work. However, table hints ROWLOCK, XLOCK, and NOLOCK can still be used without the "WITH" keyword and will not cause a breaking issue when migrating to SQL Server 2012.

Index hints FORCESEEK and FORCESCAN require the "WITH" keyword. For a full list of table hints, see the Microsoft site:

http://msdn.microsoft.com/en-us/library/ms187373.aspx

» Discuss this question and answer on the forums


Featured Script

PowerShell to Pre-Create domain objects for SQL Server Cluster

Pre-Create Active Directory Virtual Computer objects to support a new SQL Cluster  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

Configuring MAX memory - Hello All, I have MS SQL 2005/SP3 running on Windows 2003 standard. The server has 4GB and that is the maximum...

What is 'available physical memory' in DBCC MEMORYUSAGE - Hi, Am new to SQL Server. Am trying to query the total memory in the system that runs the SQL...

Generating output to text file from SP via SQLCMD - Hi all, I have created a stored procedure to report on login mappings which I want to be able to run...

Login failed for user 'NT AUTHORITY\SYSTEM', Very straing - I get hundreds of these messages in my SQL Server logs every day (Exactly every 15 minutes). The messages have a...

SQL Server 2005 : Backups

SQL BACKUP JOB Failure - I am currently trying to investigate why our nightly full backups have been failing. The nightly full backups were working...

SQL Server 2005 : Business Intelligence

How to add database source name and database destination name dynamically on a SSIS? - I have a SSIS that should be execute since the server and invoke since a client. The SSIS copies the...

SQL Server 2005 : Development

Suppressing output from a stored procedure. - Hi, I'm calling DTExec from a stored procedure, using xp_cmdshell (trust me!). It returns (selects) lots of status information which I'm not...

SQL Server 2005 : SQL Server 2005 General Discussion

SQL to XML - Any suggestion on how to create XML like this from SQL Server? I was able to create the XML without the...

Memory setting change in existing cluster - In my new job, I've inherited a SQL Server 2005 two-node cluster configured as active-passive. Each server is Windows Server 2003...

Update Join Query Help!!!! - hi Guys I need help with a simple update join query that i need to do. below are my example of...

Adding Rows to an existing table sql 2005 - hi i have a table from navision PLSo that is being updated every hour now i have created a table on...

SQL Server 2005 : SQL Server 2005 Security

PCI Compliance - Hello, We have a database which needs to be secure in order for PCI compliance. What I am trying to establish...

SQL Server 2005 : SQL Server 2005 Strategies

Random number generator - Ok.Guys I need your help. I have a table of about 60000 records. I want to add a column say...

SQL Server 2005 : SS2K5 Replication

SQL 2005 SP3- Republishing Server Error "The specified automatic identity support parameters conflict with the settings in another article" - Hi all, I have a problem and would appreciate any advice in order to troubleshoot and address it. I have the following...

Removing a corrupted merge subscription... - Hello to all, Thanks for all your time and assistence and any input is greatly appreciated. I currently have a server that...

SQL Server 2005 : SQL Server Express

sql server 2005?????? - hello, i have big problems using sql server 2005 with access 2010, actually the data base was attached and detached from...

SQL Server 2005 : SQL Server 2005 Integration Services

Downgrade SSIS 2008 package to 2005 - I developed and tested 3 .dtsx packages in my development environment using Visual Studio 2008 v9.0.30729.4462 QFE, not realising that...

SQL Server 2005 : T-SQL (SS2K5)

SQL Report formatting with UNION Clause - I have an sql like this: SELECT 1 AS GROUPID, 'A' AS GROUPNAME, ''GROUPID, ''GROUPNAME UNION SELECT 2 AS GROUPID, 'D' AS GROUPNAME,...

Help with sql statement to delete old records for user if multiple records found - Here is how the data looks like for UserSession Table UserName LastUpdated sessionID --------- ------------- --------- User1 1/1/2013 10AM SESS01 User1 1/1/2013 11AM SESS02 User1 1/1/2013...

Update table using Group By - Hi, I have a column (Total) which needs to be updated based on a calculation of 2 other columns(Assigned + Unassigned) and...

DBCC Shrinkfile issues - First of all, I know I shouldn't do a shrink file because it causes fragmentation. however, I am working with...

SQL Server 7,2000 : Administration

Question about clearing tempdb - Hello, My question is simply, once I have cleared the temp database either by rebooting or restarting the service, should the...

SQL Server 7,2000 : In The Enterprise

Binaries corrupted in one node of Cluster SQL server 2000 - Suppose we have windows server 2003 EE SP1+ SQL server EE SP4 installed in cluster env. Suppose on one node...

SQL Server 7,2000 : T-SQL

Deleting Rows in a table.. - I have a table like that MemberId(F.K) ! Event ! Event Date 1 'Test Event1' "2012-03-20 05:39:51" 1 'Test Event1' "2012-03-21 05:39:51" 1 'Test Event1'...

SQL Server 2008 : SQL Server 2008 - General

asp_net user personalization copy - I have a web site running with asp_net personalization for some web parts on the page. I am wondering if...

How to Determine New SQL Box Specs for Migration from 2005 to 2008 - So I've been researching this, but can't get a clear how to on this. I just joined a new job...

SQL Server outof space question. - Hello Experts, One of our clients server ran into this issue. The server has both application and SQL Server installed on...

Reporting in records in the right order - hi guys I have some data from a system upgrade where stuff didn't get the right sequence numbers [code="sql"] create #test ( ref int, oldvalue...

Order issues - Hello! I am importing data from a source system running IBM iSeries. I use OPENQUERY to query the source system. The source...

Precedence Constraint editor - Hi any one explain how the precedence constraints works lfor logical Or and logical And. and in values what is...

Index maintenance increase log size - In SQL Server 2008R2, we are running an Index maintenance job on Sunday.It rebuilds/reorganize the indexes ONLINE on a database...

Truncate Log Script - Hi Buddies, I got a Script for Truncate Logs... Please SUGGEST how to make this runnable *************************************************************** DECLARE c CURSOR FOR SELECT database_id, name,...

Mega Problems installing SQL Server 2008 R2 Developer - I normally install SQL Server 2008 R2 Developer without any problems, for example on an unupdated version of Windows XP....

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

Log Shipping - Delete Log files - Hi, I have setup the log shipping on SQL server 2008 R2 and it's working fine . I have setup the...

How to track table schema changes - Hi, I have a development database and some developer are use this one. I need to track the new tables...

Best way to do same. - create table main(fileNo Integer) insert into main values(1) insert into main values(2) insert into main values(3) insert into main values(4) insert into main values(5) insert into...

Using MERGE tsql for FACT loading - Hi All, I m using the below tsql MERGE statement for loading data to one of the fact table in DWH...

Inserts are slow - I have a stored procedure that requires reading transactions one by one and then creating a few inserts per transaction....

Rebuild or DBREINDEX - Hi I have a table with a clustered index and 6 non clustered indexes.The size of the table is 400G...

Reg:History of maintenance plan - Hi, i went to maintenance plan s and want to view the history of that plan. when i opened the...

Renaming system databases - Can I rename the physical name of both mdf and ldf files of system databases such as msdb,model and master?

Backups Failing - Hi, In my production environment there are only two drives C: and D: drives. The backups has to go the...

Split a range of numbers in equal parts - Hi, I want to divide a range values into equal parts with t-sql If i have a range of valuessay...

Really Challenging Query: Break out average value across months - Racking my brain on this one. I have the following table AccountID DataDate DataValue 1 1/1/2012 100 1 2/5/2012 190 1 3/18/2012 118 1 6/5/2012...

Error converting varchar to numeric only in where clause - Hi experts... I have a problem with one of my SQL Server query's... First of all, we are in SQL Server 2008...

queries on Logins - Hi If I execute the stored procedure multiple times on same day, the first time category should not repeat for the...

Need Computed Column / Trigger / Function to insert data from another table - Hi, I've got the following Problem, which I'd like to solve in SQL Server 2008: I got a Table 'Products', each product...

Result of dynamic sql with parameteres into a variable - Edit 25.4.2013::: Problem was solved, but I have question about view, my last reply... thx for response //////// Hello, i have problem with...

Msg 468, Level 16, State 9, Procedure "procedurename", Line 129 Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. - Dear All, I have a stored procedure and I keep getting the following error message: Msg 468, Level 16, State 9, Procedure...

SQL Client Access Licence requirements - Hi SQL 2008R2 Std Ed. We are having an internal debate on what does (does not) require a SQL CAL for the...

SQL Server 2008 : T-SQL (SS2K8)

Stored Proc Novice Help - Hi all, I'd be really grateful if someone could help me (probably something very basic I'm missing!) I'm trying to create a...

Does a "Begin... End" in a While loop act as a transaction? - Reading Lynns comments in this topic: [url=http://www.sqlservercentral.com/Forums/Topic1447456-391-1.aspx]http://www.sqlservercentral.com/Forums/Topic1447456-391-1.aspx[/url], I found myself wondering if a script I've got to re-org or re-build...

error in Transactions... need urget help - hi, i have below code [code="sql"]declare @V_AreaId int = 1 declare @V_Area nvarchar(30)='xxx' declare @V_Pincode nvarchar(10) ='35646' BEGIN TRY BEGIN TRANSACTION IF NOT EXISTS (SELECT...

get max - CREATE TABLE [dbo].[Table_A]( [id] [smallint] NULL, [name] [varchar](50) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Table_B]( [ID] [smallint] NULL, [DATE] [date] NULL, [ORDER] [varchar](50) NULL ) ON [PRIMARY] GO INSERT INTO...

Table variable is not automatically dropped in TempDB - hi, I have created procedure like . USE [master] GO /****** Object: StoredProcedure [dbo].[b] Script Date: 04/28/2013 01:09:39 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc...

datetime comparison behaving oddly - I think I'm about to fill a gap in my knowledge base, because I've been banging my head against this...

SQL Server 2008 : SQL Server Newbies

Troubleshooting Web Application Performance - Hello, One of my clients has a few web applications using SQL Server as a backend. One of those applications...

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

How to merge two sql select statement results I tried it but it not get please see it in detailed explination with my query - Hi All, My Query Goes Like This, Select TT.ID,TT.NAME,ROUND((CAST((Sum(TJA.MINS)) as FLOAT)/60),0) AS HOURS From MYTABLE1 as TT INNER JOIN MYTABLE2 as TB On...

can any body explain normalization and types?/ - can any body explain normalization and types?

SQL Server 2008 : Security (SS2K8)

sql server login failure - I am working with asp.net and try to access through my login form sql server database mydb when logging in as windows...

SQL Server 2008 : SQL Server 2008 High Availability

Can't start mirroring that was previously disabled. - sqlserver 2008 R2. The error is: TITLE: Database Properties ------------------------------ An error occurred while starting mirroring. ------------------------------ ADDITIONAL INFORMATION: Alter failed for Database 'FASTContent_CrawlStoreDB_2d3e9be9ef044393a736f16d569f0952'. (Microsoft.SqlServer.Smo) For help, click:...

DBMirroring Mirror DB Transaction Status - In High safety mode without automatic failover, Principal database waits for the acknowledgement from the mirror database. If changes has...

DBmirroring state change SQL job - Hi All, I have created the sql job refering to the microsoft article. http://technet.microsoft.com/en-us/library/cc966392.aspx#XSLTsection129121120120 For the step: Record state change INSERT INTO dbo.[DB Mirroring...

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

multiple instance sql cluster with different windows configuration - We have a two node cluster. In order to utilize the max resources we are planning to add more instances...

SQL Server 2008 : SQL Server 2008 Administration

What is the service should be enable\start for witness server configuration in db mirroring - What is the service should be enable\start for witness server configuration in db mirroring?

How many connections possible to standby db in logshipping - How many connections possible to standby db in logshipping ?

dbo user in the database has a domain login account - I'm going over my server security to remove our previous DBA. One of the odd things a found on our...

huge log file - Dear Experts I have a 3 GB mdf file and 355 GB ldf file. What should I do, I have taken a...

Installing SP AND CU on Cluster - What is the best practice for installing a SQL Service Pack and a Cumulative Update on a clustered index during...

Table size swell after altering a column - Hi I recently ran some code that altered some columns in a 38 million row table from varchar to nvarchar. Before...

Programming : General

Hiding a dataGridView - Hi, Is there a way to hide or disable a datagridview completly? I have a few radio button options and would...

How to copy a table structure and data to a different server? - I am wondering if there is a simple command I can run to copy a table structure and its data...

Creating database,stored procedures from batch file - Hello, I am using below code to execute a sql scripts through batch file.....[passing parameters ]. [code="plain"] rem This script executes the scripts...

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

SQLServerCentral.com : SQLServerCentral.com Website Issues

feature request: social signal - I don't want to add "+1" -style comments to a discussion because they really junk-up a thread. It would also...

Reporting Services : Reporting Services

How to link Main dashboard to other dashboards via a dropdown list - Hello, I have created a Main dashboard using SSRS. It consists of a report with graphs etc called Dashboard.rdl I have...

Jasper Smith's SSRS Scripter - Anyone happen to have this tool handy? I used in the past to successfully migrate a 2005 SSRS instance to...

Data Warehousing : Integration Services

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

Can not open the file - Hi I have an issue .File is not loading from the source .Flat file source is the source [Flat File Source...

How to convert the data from stored procedure to excel sheet through SSIS. - I have one task, The task having stored procedure and the task will be run daily and insert the data...

Execute Process Task using WinSCP - Hi , I have to transfer file from sftp site to my local server on E:\ecol\ . I have used 3rd party...

Data Warehousing : Strategies and Ideas

Geography Dimension - I am currently working on a warehouse project that includes a dimension for geography. It includes Zip Code, City and...

Data Warehousing : Analysis Services

Tabular) DAX currency conversion multiple target values - Hi All I m novice in SSAS. SSAS 2012 SP1 on W7 x64 I need to be able to convert all sums into...

Data Warehousing : Data Transformation Services (DTS)

Error Importing from Oracle - I'm trying to use SSIS to import tables from an Oracle DB. The Import Wizard can connect to the Oracle...

Microsoft Access : Microsoft Access

Filter in Access- SQL - This is my sql query in access. However, I am not sure where I need to add or filter on...