In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Monitor The easiest installation of a third party tool, ever!
- Aaron Kolysko, Monsoon Commerce. Get started with SQL Monitor today to gain effortless insights into the health of your own servers - download a free trial
 
SQL in the City Get free SQL Server training from industry-leading experts and MVPs,
as SQL in the City tours six US cities between September 28 and November 5. Find out more and register for your local event.
 
SQL Source Control Need to share database changes?
Keep database dev teams in sync using your version control system and the SSMS plug-in SQL Source Control. Learn more.

In This Issue

Stairway to XML: Level 1 - Introduction to XML

In this level, Rob Sheldon explains what XML is, and describes the components of an XML document, Elements and Attributes. He explains the basics of tags, entity references, enclosed text, comments and declarations More »


SQLServerCentral Webinar Series #21 - Forgotten Rings & Other Monitoring Stories

Most common monitoring metrics are important and useful, especially over time, but they can fall short. How do you gather information to determine, for example, if you have buffer cache pressure? Register now for the free webinar. Wednesday, October 17 2012 4:00pm - 5:00pm BST More »


SQL in the City - Chicago 2012

A free day of training in Chicago on Oct 5, 2012. Join Grant Fritchey, Steve Jones and more to discuss, debate, ask questions, and learn about how to better run your organizations SQL Servers. More »


Report Builder 3.0: Formatting the Elements in your Report

here is a lot that can be done to make basic tabular reports more readable, using Microsoft's free Report Builder. Rob Sheldon continues his exploration of the power of this tool by showing how to format various elements within reports. More »


From the SQLServerCentral Blogs - Interesting Reading for September 18, 2012

More stuff you may find interesting. Prepare for your first security breach. Sobering. As data professionals we’re going to get pulled... More »


Editorial - Data Worms

Effective data administration include backups. I would argue this is the most important part of managing your data since no matter what happens, backups give you the ability to recover your system. Whether it's an accidental data modification by a user, a hardware failure, a natural disaster or some other event, having a copy of your data (or log) on some other system or media can be the difference between getting back to work and having to go find other work.

For the most part we've been concerned over disasters that are random, and while they may be destructive, their effect is usually limited in scope. SQL injection attacks often affect a single system, and with a good backup, you can usually recover your data quickly. However that may be changing as the world grows closer together.

The Shamoon malware has been causing problems lately and making companies rethink their incident response. The worm has wiped workstations and destroyed data. Destructive software is nothing new, but as more and more cyber warfare takes places at the government level, it's likely that retaliation might target civilian targets, especially successful companies. I'd hate to think that destructive malware might target databases, but I'm sure it will. The SQL Slammer worm was a complete disruption of our database services for days at JD Edwards almost a decade ago, but it could have been much worse if that malware had been written to be destructive.

We don't have default names and passwords in SQL Server, but we should make sure all accounts have strong passwords. There's also some value in not configuring all systems identically. It makes management easier, but it also makes a successful attack that much more successful. 

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


The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

Everyday Jones

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

You can also follow Steve Jones on Twitter:

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


Question of the Day

Today's Question:

Which of following are used wen configuring resource governor work? (Choose 3)

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

This question is worth 1 point in this category: Resource Governor. 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 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

Given the following code

create table #Test (ID int primary key);
insert into #Test values (2);
insert into #Test values (1);
select top 1 ID from #Test tablesample (10 percent)
drop table #Test
Which statements are true? (choose 2)

Answer:

  • The query will return zero or one row
  • The query may return the value 1

Explanation: The primary key will be clustered (Create Table on MSDN), causing the data to be stored in order. Due to the size of the data, we will only have one page, and the tablesample will thus retrieve 0 or one whole pages. If any pages are retrieved, the rows are retrieved in order due to the clustering key, thus the first row will have a value of 1.

» Discuss this question and answer on the forums

SQL Server Hardware will provide the fundamental knowledge and resources you need to make intelligent decisions about choice, and optimal installation and configuration, of SQL Server hardware, operating system and the SQL Server RDBMS.

Pick up your copy of this great book from MVP Glenn Berry at Amazon today.


Featured Script

Run SQL Hosted VBS Script from SQL

This routine allows you to run VBScript directly from SQL (i.e. without having to create a file in advance) by pushing the script to a temp file. 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

Logshipping restore job failed - Hi Experts, In one of my prod server I have configured logshipping for two databases in a single server (SQL Server...

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

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

is it required to run reindex on a table soon after a column is dropped or added?if yes then why? - Hi, Should reindexind be done on a table after a new column is added to the table..does it depend on if...

Indexes question - Is it ok to apply indexes on all the columns involved in the WHERE clause of queries? Our database is...

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

Question on ACID properties - Experts, One of the interviewer asked me something like below question. It was not clear to me anyway. "When we get an...

SQL Server Jobs issue - We have faced a scenario in which jobs are scheduled to run on daily basis,but one day what happened that...

Partitioned tables - We've a very large table and I'm thinking to partition it. Either one of these 3 main columns(appid, date or...

Sql Server DR Strategy - Advice Reqd - Hi SQL Guru’s, We plan to implement a DR strategy for our mission critical production server. We would need your advice...

There is no row at position 0. - Hi Experts, I am getting the error given below when i selected the properties of a database. I am getting the...

Update Stats with full scan on all tables in all databases - I run the following code which worked sql2k but does not work in sql2k5 and get the following error. I...

I'm new in Forum SQL ServerCentral - Hi people, my name is Lucas, i'm brazilian i student sql server, i need contacts for best thus my english...

Hungarian notation convention - Hi everyone, Does anyone know where I can find a list of SQL data types and their Hungarian notation convention prefixes? I...

Not able to restart the MSSQL Server Services - Hi, I am working on SQL Server 2005 June CTP on Windows 2003 Server SP 1. I restarted my computer but...

SQL Server 2005 : Backups

SQL Server Database Engine cannot obtain a LOCK resource at this time - The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when...

SQL Server 2005 : Business Intelligence

Need list of COlumns/Table Names used in SSIS/SSRS - Hi All, Recently we are planning to do some schema level changes to few tables, so now i have to find...

Need MDX query to calculate weighted averages - I have a simple fact table that doubles as a dim table (date, quarter form a hierarchy in the time...

SQL Server 2005 : Working with Oracle

ABCD... of Oracle - Hi, I am a sql server developer/DBA. I am interested to learn Oracle from begining. Can anybody pls guide on this...

SQL Server 2005 : SQL Server 2005 General Discussion

BCP Error On New Server - I have a SQL Job that executes a BCP command to create an file of exported data. The job has...

Delete Constraint rule Set Default problem - I just want to ask if Set Default works with columns that are string related? I've tried setting up my a...

SQL Server 2005 : SS2K5 Replication

The merge process was unable to deliver the snapshot to the Subscriber. - Hi All, I setup a merge replication between SQL2005. I have two subscribers. First subscriber is working perfectly, but the second is...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Execution plan - Hi All, I have 2 sql sevr 2005 instances with same build no,same edition. I restored the database from one...

How much more RAM is needed as database grows in size to keep up with the performance? - Hi, Tried to google it, but could not find anything suitable. I am trying to do some capacity planning and I now...

Server becomes very slow [RESOURCE_SEMAPHORE] waittype. - From last week our server periodically becomes very slow, I'm getting multiple RESOURCE_SEMAPHORE waittypes. The server box specs are: 4 Quad...

SQL Server 2005 : SQL Server 2005 Integration Services

SSIS error from flat file to table - hi, i am inserting data from flat file to sql table usig SSIS. my flat file is empid,empname,salary 1,david,1000 2,krik,3456 3,van,4532 4,venus,345 my table in sql is create...

Best practices in Oracle ? - Hi, Do we have any naming convention in oracle store procedure like in sql server ? For example,in sql server store procedure...

SQL Server 2005 : T-SQL (SS2K5)

Compare rows in the same table and group the data - Hello All, Please point me in the right direction as to where i can start to resolve this problem. I have...

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

Call Stored Procedure from a Function - Has anyone tried calling a stored procdure from a user defined function. I have a procedure as Proc_XXX with 7...

SQL Server 2005 : SQL Server Newbies

Quickest Way to Find an Index - Hi Folks can comeone tell me the quickest way to find an index in some 100 plus databases ? is there a...

How did you learn SQL - I have been assigned a program for work called PDMWorks Enterprise that uses SQL 2005 for the metadata. Do you...

Cannot insert data into SQLServer table linked in MSACCESS db - Dear all, I am trying to configure an MSACCESS db to link a table from SQL server and enable the...

SQL Server 7,2000 : General

OT - Learning Oracle 8i - Hi all, We now have a new digital phone system that using an Oracle 8i database. I've used Microsoft SQL for...

SQL Server 7,2000 : Working with Oracle

Learning Oracle - Found some resources, SQL Reference Guide for Oracle 10g. Guess what, Oracle actually has CTE's, they just don't call it...

SQL Server 2008 : SQL Server 2008 - General

Problem with simple databases - Hi I have a database in simple recovery model (sql server enterprise edition 2008).I need its log , but because it's in...

Timeout Error - Hi everyone, My web application is not able to connect to my database server every time it throws timeout expired error. Even...

Outlook Data to SQL Table - Hi Everyone, I want to be able to automatically populate an SQL table with data from emails I receive in a...

SSIS - Flat File Manager help - Hi Guys, I'm new to SSIS & I need some assitance around the Flat File Manager concept. I've designed my whole package and...

Continuing career with SQL - Hello, I hav just joint this forum. I have to ask few things. I have worked with SQL 2000 and basic 2005.database design. I've...

Variable String + Variable - I am modifying a script that creates Jobs, is it possible to achieving the following; @command=N'Some Command' + @ANOTHER_VARIABLE I need to tag...

A Database with 70 Table - hi everybody I Have Database With 70 Tables and this my first time that i have alot table in my DB....

Sum with Multi Category and Group By Acc No ? - SELECT SUM(AMOUNT) as SumAmount,TransType_ID,Transaction_AccNo FROM Tbl_Transaction Group By TransType_ID,Transaction_AccNo below is the sql query result SumAmount TransType_ID Transaction_AccNo 65000.000 0 1 -35157.000 3 1 -1872.235 2 1 [b]Question = i want to show only...

Importing and Splitting 150,000 columns flat file into three tables of 50,000 cloumns each - Hi guys, I need help. I have a task to split a flat file with 150,000 columns into 3 50,000 columns...

SQL 2008 R2 Install - I have the complete SQL install files (2008 R2). It has x64 and x86 folder. I need to copy this...

Replication: Publication error - any help on this Publication error SQL Server could not create publication 'Publication_Name'. ------------------------------ ADDITIONAL INFORMATION: A log reader agent can only be installed...

SSIS file import - Hi, I would like to build a package that imports a file from a file server on a daily basis. the...

Looking for a scripted way to track CPU utilization throughout the day - I am looking for a scripted way (i.e., a TSQL query) to track CPU utilization throughout the day on a...

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

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

SQL Server Could not start Snapshot Agent - Hi , I am trying to setup the Transactional Replication using pull subscription between server A and Server B. Server A and...

how to best summarize data - I’ve got the following data and I’m trying to get to the following results. I’m assuming I need to use...

E-mail Unicode - Good day, I have a problem in time to shoot an e-mail via sp_send_dbmail procedure, when I put some special...

Linked Server Security - I do not really understand linked server security in sql server 2008. I have a SQL Server logon for the...

Why use SNAPSHOT isolation with Change Tracking? - Microsoft has strongly recommended that we use SNAPSHOT isolation with Change Tracking because of the improved row versioning. Can anybody...

SQL Server 2008 : T-SQL (SS2K8)

How do I use one column for node names and the others for elements in that node? - I'm trying to query some data with FOR XML to output the XML. Let's say I have data like this...

End Of Year - Good Day To All, I'm working on a budgetreport that ask the user to enter the up to date. If the user...

how to get current year - Table contains Orderdate Column which contains years starting from 2001 to 2010. i need to get currect year using any...

Cursor within triggers... - Hi, I have a products table and a storage products table. [code="sql"] CREATE TABLE Products (Product INT NOT NULL, Description NVARCHAR(50), TotalStock INT) CREATE...

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

Datatype truncation - Can anyone tell why ltrim and rtrin function truncate value of float datatype values ?

SQL Server 2008 : Working with Oracle

Oracle Database - Hello all... I am new to database learning. But really got interested in OCA studies. So I have decided to opt that...

Compare Oracle Tables to SQL Server tables - I'm dealing with constant Oracle Schema changes and I need to synchronize my SQL Server Tables with changes that are...

SQL Server 2008 : SQL Server Newbies

Multi database to SQL Server at record level - Hi All I have created a touchscreen data collection system that stores its information on local Access 2007 databases. The reason...

Bulk insert of large files (more then 450MB) - Hi everyone I have to populate a table with a pretty large text file and I'm happily using the very good...

DBCC SHRINKFILE failing with error Msg 3140, Level 16, State 5, Line 1, Could not adjust the space allocation for file - Hi, I am trying to run SHRINKFILE on one of our data files but it is failing with error [i][b]"Msg 3140,...

SQL Server 2008 : SQL Server 2008 High Availability

Sync Type-Replication Support Only - Hi, I have setup replication with sync type-replication support only and it's working fine. If I need to reinitialize the subscriptions...

SQL Server 2008 : SQL Server 2008 Administration

Data compression - Hi, Can anyone help me find the steps/process for data compression? Thank you!

Moving master LDF to log drive - I normally leave sys db's in the default location (not tempdb). However, I have a production 2008 instance, where the...

SQL Performance Statistics - Hi All How accurate are the counters in sys.dm_os_performance counters When I run this query [code="sql"]select * from master.sys.dm_os_performance_counters where counter_name='Batch Requests/sec'[/code] I get a cntr_value...

Security for SQLAgent Jobs - We have some users who sometimes need to directly edit certain databases. Their protocol sensibley is to back a database...

New Windows 2008 R2 server with SQL 2008 R2 - Hard Drive Setup - Okay I my be a little out of my league here, but I am learning. Please bear with me. Sorry...

SQL Jobs monitoring tool - We have a large number of jobs running on SQL DB servers. Is there a tool to find out what...

x-cmdShell access - Dears all how can i restrics xp_CmdShell accesss to run some command? for example xp-cmdshell can not run format syntax or delete...

Backup taking MUCH longer on prod HELP:) - I have two environments: DEV: VM 4 virtual cores 16 GB Mydatabase 137242.19 MB Attached SAN storage SQL 2008 R2 standard PROD: Physical 32 Core 2 Processor 64 GB RAM Mydatabase...

Programming : General

Timeline for learning SQL - I was asked during a job interview about a timeline for learning SQL. I have worked with a data warehouse...

Is DBA a limited Profession ? - What after several years of DBA , Sr DBA.. Then what ? Just curious to know.... Isnt it DBA a profession which...

Programming : XML

How to filter the namespace from the xml in SQLServer..plz help - i have fetched a big xml from db.there is one namespace attached with it.i want to remove the namespace.plz help

Programming : Service Broker

Delay in Asynchronous Procedure Calls - I would like to call stored procs asynchronously, but there seems to be a delay in service broker so that...

Monitoring date specific changes - I have a Bookings table as such:- CREATE TABLE [dbo].[Bookings]( [Id] [int] IDENTITY(1,1) NOT NULL, [DateCreated] [smalldatetime] NOT NULL, [Start] [smalldatetime] NOT NULL, [Duration] [smallint]...

Programming : Powershell

How to copy last line of .txt file into new .txt file? - Windows Server 2008 R2 I'm totally new to PowerShell but was trying to use it to solve an issue with data...

SQLServerCentral.com : Anything that is NOT about SQL!

Attended an Interview - Recently I attended an interview for a role of Senior SQL Developer and I wondered the kind of questions panel...

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

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

Quick Interview question... I don't know if i got it right or wrong.... - Hi Experts, I had a manager asked me this question during an interview: There is SSRS Tree prompt in a report, where...

Grouping Values in a Report?? - Hi, I have an SSRS report to build that looks like this. Name Count Total BIKE COMPANIES 50 3000 HELMET COMPANIES 10 200 The problem...

AutoRefresh fails after midnight on reports with date parameter - Hi We use some reports in our NOC which have date parameters with defaults set to the current day. These reports...

Database Design : Design Ideas and Questions

odd data design (at least to me) - I've encountered a data design that is new to me. Frankly, I want to gouge my eyes out because it...

Data Warehousing : Integration Services

SSIS - Connection manager Variable file name - Hi Guys, I'm new to SSIS & I need some assitance around the Flat File Manager concept. I've designed my whole package and...

Data Warehousing : Analysis Services

time dimension was not generated because it is bound to a time binding - I am using SQL Server 2008 and am trying to generate a date dimension in Analysis Services using the "Generate...

Microsoft Access : Microsoft Access

Can Access FrontEnds run T-SQL/XML - Hi guys, I'm not knowledgeable about XML or T-SQL but do you know whether it's possible to use syntax such...

Article Discussions by Author : Discuss Content Posted by Dinesh Asanka

SSIS Transaction and checkponts in SQL server 2008 R2 - SSIS Packages Containers Checkpoints and Transactions. Hi evryone, I have a SQL server 2008 R2 on a windows 2008 server. I have...