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

Using the Script Task in SSIS to Process Data Files When They Arrive

Orlando Colamatteo from SQLServerCentral.com

Larn how you can process data files with SSIS as soon as they arrive using the Script Task. More »

Getting Started With XML Indexes

Additional Articles from SimpleTalk

XML Indexes make a huge difference to the speed of XML queries, as Seth Delconte explains; and demonstrates by running queries against half a million XML employee records. The execution time of a query is reduced from two seconds to being too quick to measure, purely by creating the right type of secondary index for the query. More »

Last chance for a day of free SQL Server training at SQL in the City 2012

Press Release from Red-Gate

SQL Server developers and database administrators have one last chance for a full day of free training and networking at SQL in the City 2012. More »

Exceptional DBAs do more than respond to events and issues in their environments. In many cases, I think they even go beyond using metrics that detect problematic activity on their systems before users notify them. I think the best DBAs will actually mine the information they have about their systems to anticipate problems in advance.

In the past I've had monitoring systems that would respond to issues, and I had alerts setup on the system to notify of unusual events, like an unexpected data growth. What I had started to do before I became a manager was start to write system checks that anticipated future problems and allowed me as much lead time as possible to prepare for issues. An example of this was a set of queries I wrote that calculated data growth for all databases on an instance and then used that to calculate how many days would elapse before I ran out of space on the data drives.

You can write similar queries to look for other trends. Tracking the execution times of often-run queries, or those queries which are important to the application can allow a DBA to find potential issues. If the execution times are growing, the DBA can anticipate a problem occurring in the near future and begin taking action to rewrite, tune, change indexing, or some other measure. A broad spectrum of queries taking longer might be an indication that hardware needs to be upgraded. There's even a site devoted to metrics.

Instrumentation is important in understanding, analyzing, and predicting system performance. More and more tools are being released to gather detailed metrics on .NET code, in browsers, and more, but there is a wealth of information inside SQL Server on the performance of the platform. A little work can help you track and monitor the performance of your system and proactively maintain performance before your users complain.

Steve Jones from SQLServerCentral.com

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

• Watch the Windows Media Podcast - 22.9MB WMV
• Watch the iPod Video Podcast - 19.5MB MP4
• Listen to the MP3 Audio Podcast - 3.9MB MP3

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

Today's Question:

-- USING SQL SERVER 2012
DECLARE @t TABLE (A varchar(25)) 

INSERT @t values ('77.88.99.100')  

SELECT 
  PARSENAME(A,1) AS 'First selected'
, PARSENAME(A,2) AS '2nd selected' 
, PARSENAME(A,3) AS '3rd selected'
, PARSENAME(A,4) AS '4th selected'
 from @t 

The question is: (select 1)

a. Are the values returned  First selected 77,  2nd selected 88,3rd selected 99,4th selected 100'

b. Are the values returned first selected 100,  2nd selected 99,3rd selected 88, 4th selected 77

c. No values are returned.

By bitbucket

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

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

Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012

What is the result of this query? The answers reflect the three results in the last query.

declare @Quota1 int, @Quota2 int, @Quota3 int, @Value int;

Set @Value = 20;

select @Quota1 = @Value * 1.05;

select @Quota2 = cast(@Value as real) * 1.05;

Set @Value = 100;

select @Quota3 = cast(@Value as real) * 1.05;

Select @Quota1 * 5, @Quota2 * 5, @Quota3;

By Mike Tanner

Answer: 105, 105, 104

Explanation: @Quota1 will be implicitly casted, so is equivalent to @Quota2 (see e.g. http://msdn.microsoft.com/en-us/library/ms187928.aspx).

All are subject to conversion from real to int, but strangely @Quota3 suffers from rounding down whilst @Quota2 does not (on the systems I have tried it on).

The simple rule is to always be aware of the possibility of such rounding and allow for it. A pragmatic fix is to use e.g.

select @Quota3 = @Value * 1.0500000001

There are a variety of references to this truncation, e.g. http://msdn.microsoft.com/en-us/library/c151dt3s.aspx.

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

Last Accesses Stored Procedure

Jason Clements from SQLServerCentral.com

List of your Stored Procedures and when they were last accesses. More »

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : Administering

SQLServer port change - Hello, i have tried and tested port number changes with luck now luckily going to production linked server is not working...

Permission issue for excute export /import - In my environment , application owner asking sysadmin rights for excute import /export wizard, but in dba side wont provide the...

Creating a Non-Cluster Index - Hi Experts, I need a small help in creating non cluster index on a table as we were facing a performance...

sys.sp_sproc_columns Causing Blocking ?! - We just had an incident in which users across various databases suddenly were affected by blocking. I can usually track...

SQL Server crashes when data drive full? - Hi. Has anyone experienced this in SQL 2005; that SQL Server goes down when the data or log drive drives...

Process not releasing tempdb space - Any suggestions on how to check why tempdb space is NOT being released?

SQL Parse?? - Hey everyone, Trying to find a bit of help with a project I am working on. Basically, we are trying to...

Linked Server Raw Data Size Limit? - Is there a limit to how many bytes of data can be returned over a linked server? If so, is...

SQL Server 2005 : Backups

Differential backups getting smaller. - I'm wondering if anyone can give some advice please - I'm restoring a live backup from Thursday night and will be...

SQL Server 2005 : Business Intelligence

SSAS partitions - Hi all, SELECT dbo.FactStoreSalesInvoice.StoreSalesInvoiceRowInfoKey, dbo.FactStoreSalesInvoice.ProductKey, dbo.FactStoreSalesInvoice.VendorKey, dbo.FactStoreSalesInvoice.DeliveryCustomerKey, dbo.FactStoreSalesInvoice.BillingCustomerKey, dbo.FactStoreSalesInvoice.PayerCustomerKey, dbo.FactStoreSalesInvoice.SoldToCustomerKey, bo.FactStoreSale

patitioning monthly basis - Hi am trying to do partitions on monthly basis for every year in my solution. currently am trying it for material...

Quality Selection for BI Reporting Tools - [size="3"][font="Times New Roman"]Hi All, I am in dilema for selection of a BI Reporting Tool. After spending few hours I just...

SSIS Mapping documentation - I am working on a large data migration using SSIS. In past projects we have used SQL stored procedures and...

SQL Server 2005 : SQL Server 2005 Security

Amazon cloud connection to SQL Server - Hi, We are trying to connect a java application hosted on a Windows Server 2008 R2 machine hosted in Amazon...

Login Resolution To Wrong Domain Account - We have a domain ID that was created under a particular name and then renamed in AD under the same...

SQL Server 2005 : SQL Server 2005 Strategies

Redesign help - Hi, Ive finally been allowed to redesign some tables which are "wide" (Think Field1, Field2, Field3 ... Fieldn). There are multiple fields...

SQL Server 2005 : SS2K5 Replication

Error message - I have a Merge Replication that syncs data between two databases. When I look in the Event Viewer, I keep...

SQL Server 2005 : SQL Server 2005 Integration Services

insert data from file to sql table - hi i have flat file like this TH*4.2*876545*P~~IO*3456*color~ now i have two tables create table table1 (id nvarchar(10), t1 int, t2 int primary key, t3...

SQL Server 2005 : T-SQL (SS2K5)

SQL statement run info in Stored Proc - When you run a sql statement, in Server Management Studio, it tells you how many rows and how long it...

Calculating Yield - Hi all, I'm running into an issue calculating yield. [code="sql"];with totalcount as ( select cast(count(sn) as decimal) as total from completedUnit ) select (100 - (cast(count(distinct...

running sum cursor - Okay, I spent the last 24 hrs looking for a solution...I am totally new to server sql and after 24...

SQL Server 7,2000 : Administration

DTS packages - Hello, can some one tell me please: 1.I saved dts package,but how can I see location of dts package? 2.How can I call...

SQL Server 7,2000 : General

Should I become a DBA?!? - Hi All, I've been offered a job as the sole MSQL DBA at an up and coming online retailer. I'm from...

SQL Server 2008 : SQL Server 2008 - General

what is the Exec_context_ID in sys.dm_os_Tasks? - Is the exec_context_id to do with execution context, when thought of from a security perspective (i.e run a sproc under...

Very Small Tables - I was recently asked for my opinion on adding an identity column and primary key to a very small table....

Joins? - I am struggling to understand joins and hope somebody can help me I have three tables that I am querying, tables...

New SSRS 2008 R2 installation - I just finished installating SSRS 2008 R2 on a brand new machine. I have the ReportServer databases on that same...

Query Help - Hello Every one I have one Task [code="sql"] CREATE TABLE #x1( [cStudentId] [char](9) NOT NULL, [iSchoolCode] [int] NOT NULL, [dtEnrollmentDate] [datetime] NOT NULL, [cGradeCode] [char](2) NOT NULL, [cAbsenceCode]...

Update in all table - I have written an update statement which is updating some fields like this-- update L set L.UpdateBy = E.UserID, L.CreatedByID = E.UserID from EmployeeProfile E inner join...

Data Import Wizard - Unexpected Unrecoverable Error - Hi I'm trying to use the VFP OLEDB driver on windows 7 machine with SQL2005 and SQL2008 with the Import data...

Removing only contiguous/adjacent duplicate records from a rowset - In SQL 2008, is there any way to remove only adjacent/contiguous duplicate records? Only if a record repeats immediately, then...

Quick Question: Convert Implicit - I'm slightly stumped as to why I am seeing an implicit conversion seeing as though it's converting a varchar(255) to...

Retrieve data between 2 '.' - Hi geniuses! Hi have a field (VALUE), which contains data like: MNN.helloworld GFF.goodbyeworld SSW.seeyoulaterworld.oops And I want to retrieve data when there's only one '.' in...

xml as varchar datatype - Hi guys, I need some help (well a lot) with XML. I've never used it before so am basically green in...

xml as varchar datatype - Hi guys, I need some help (well a lot) with XML. I've never used it before so am basically green in...

Query performance change - The following query was executing within 1 minutes for the past 2 years without any issues. update dbo.INT_AS_VENTE_TITRE set P_STATUS...

Issue in using loopback linked server - I have two db's on a SQL2008 server let's say database A & database B with their own SQL log in....

sp_trace_getdata SP - In a production system i've seen a store procedure running all the time exec [master].dbo.sp_trace_getdata 8 even thouh i kill the proccess...

Brent Ozar's SQL Server on VMWare Training Video or a Book? - I've noticed that Brent Ozar has a SQL Server VMWare training video ([url=http://www.brentozar.com/sql-server-training-videos/tuning-sql-server-on-vmware/]here[/url]) which went on sale, and I was...

Login can only connect to one database - Hello Is there any reason why a login could connect to one database and not another? The login was created with master...

Installing SQL 2008 R2 onto a new PC - We've got new developer machines. They're Windows 7 machines, 64-bit. My current machine started life as a Windows XP machine,...

Split without delimiter based on position - I am having a query i need to split the query based on the position i specify Say string is 12345678910111213141516........ i...

How do you admin your databases? - So I would like to try and find out - ideally for those working for a single employer - not consulting multiplexers working...

Create select statement for the query - Hi, I have a requirement to create a CASE statement for the following condition: SUM of NCV_BL.TEU where NCV_BL.POL_LOCATION_CD or NCV_BL.POD_LOCATION_CD is...

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

SQL 2008 R2 x64 Threading Issue - Morning..... I currently experiencing a threading issue when trying to process cubes. Before starting the cube we select sql server the...

Upgrade from SQL Server 2000 with Linked servers and Replication jobs - Hello All, I am in the process of Upgrading from SQL Server 2000 Enterprise Edition (x86) to SQL Server 2008 Enterprise...

SSRS 2008 Sub reports - Hi, Im new to BIDS and I have create a main report, I have then created another report and added this...

Linked server using MySQL ODBC 5.1.8 - Hi , I am trying to add a linked server using the MySQL ODBC 5.1.8 but I am always getting the...

SQL Server 2008 : T-SQL (SS2K8)

Index with Include - Hi hope this is the correct forum to this question if not please redirect to me the correct forum recently my company...

Date in where clause - Hi I have something like this and the tables have no foreign keys. I am using date in where clause...

create table by using select command return data? - How to create table by using select command return data. Export utility not possible to create another data with table due...

Data dictionary procedure issue - Hi..I have lots of lots of (around 1000) tables for which I need to create a data dictionary..The create table...

Is there a Coalesce LIke function that returns the lowest/highest value of a set of params passed to it - Coalesce() returns the first non-null value passed to it. Does T-SQL have anything like this but that does this for...

Primary Key Implementation Issue - I need to create a Directory table for a project within an institute. How shall I implement a primary key,...

Why does my yesterday query return no results? - Hi all I have a datetime column in my DB and I am trying to fetch all records from yesterday...

Permission Denied When Using "WITH EXECUTE AS" - I have a stored procedure that uses "WITH EXECUTE AS" to allow the querying of some system views, the stripped...

how to show single column value (separated by ;) as multiple columns otherwise null - in my student table i have a column called as Subject, it has values as below Student ID Subject 101 EJB;C++;PERL 102 Dotnet 103 Java 104 Oracle;Java;Sql 105 Sql;SSIS i.e., subject column has multiple...

SSIS Package Inside Trigger keeps running endlessly - Hi, Following some requirements, that came in yesterday, I have been assigned a task of executing an SSIS package from inside...

Avoiding insertion of duplicate rows - I have a table with data. I need a query that need to insert only new rows instead of whole...

SQL Server 2008 : Working with Oracle

OraOLEDB.Oracle vs. MSDAORA, SQL 32 bit, 64 bit - After upgrading to SQL 2008 R2 on a new 64 bit server, linked queries are running 3 to 4 times...

SQL Server 2008 : SQL Server Newbies

Backup plan help required - Good afternoon, Please consider the following: [img]http://s17.postimage.org/ggq5fn4xr/Backup_Plan.jpg[/img] I have 18 servers at two different sites. The number of DBs on each server varies...

Dyn SQL - Need results to show when count is 0 group by datetime - So the guys in charge of this place made some interesting requests and pegged me as the person to do...

Convert DD-Mon-YYYY to DD/MM/YYYY - I need to convert dt_of_birth [varchar] (15) which is in the format DD-Mon-YYYY to DD/MM/YYYY dt_of _birth is specified in...

Stored procedure to split and insert values into tables - I have an empty employee table and employee_details table. The temp table which i created say it has 10 columns of...

Create and populate a Zip file - I have a small program that runs every day from my client machine that creates a new zip folder on...

SQL Server 2008 : Security (SS2K8)

Move logins from one to anothe - I would like to move a database from one server. In this move, I need a script to copy all...

SQL Server 2008 : SQL Server 2008 High Availability

SQL Mirroring error did not auto failover - Hi all Last night our principle instance alerted us to the fact some logs has not been sent over to the...

Sqlserver replication error - Hello Team, How to solve below error and may i know the reason why it happens The subscription(s) have been marked inactive...

SQL Server 2008 : SQL Server 2008 Administration

batch file script need - I need a batch file script to read the hostname, and replace the hostname dynamicaly where we see 'Hostname' in...

SQL Server Consolidation (25 servers) - Our company plans to consolidate SQL Servers. Currently there are about 25 servers. They try to minimize this number to 5-6....

Actual Size of my database? - Guys I am not a DBA but a developer so please bear with me. The size of my db is 10929.50...

Reading TRANSACTION LOG - Hello guys, I am new users of this forum I have question, in my company we had problem that one of program...

High Waits for LCK - Hi All I'm using the following DMV to get an idea of the highest waits on my system: [code="sql"]SELECT TOP 15 wait_type , wait_time_ms...

automate script needed - i need a automate script to add new sqlinstance name, the script will take the machinename dynamically and we need...

Please suggestion program for copy database files. - Dear All We have problem about copy database we have backup database 11 files and data size is 90 GB every...

Database Mail Error - This is a recent install of SQL Server 2008 R2 in a Windows Server 2008 R2 64 bit environment (which...

Performance Monitoring tool - hi all Good morning, can you suggest free MS SQL database performance monitoring tools? Thanks Selva

page writes/sec high on mirrored server - Hello I have a mirrored server in which the SQLServer:Buffer Manager: 'page writes/sec' is high, it averages around 280 with the...

memory not releasing - HI I have a server with 8 Gb Ram I installed Sql server 2008 R2 on 64 bit platform Enterprise edition,in...

Career : Certification

Would using the MS Press books be sufficient? - After being an MS SQL DBA for over a decade I'm finally looking at getting some certifications. Unfortunately though it'll...

Programming : Service Broker

Service broker filling up Tempdb - I’m new to SB. Never had issues with it previously. I have 2 service broker tasks/queues one works fine and I...

SQLServerCentral.com : Anything that is NOT about SQL!

How's this for a Job Description - So I recently got this job description in the mail and at first glance it looks pretty good, but at...

Crazy Interviews - Reading the topic 'Stupid Interviewer Tricks' reminded me of an interview where I couldn't tell if I was crazy or...

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

SQLServerCentral.com : SQLServerCentral.com Website Issues

Stairway Articles Offline - Hello, is it possible to download the Stairway articles for viewing offline in maybe epub or pdf format etc. It...

Reporting Services : Reporting Services

Parameters Users Utilization - Hi geniuses! I have a report with 3 parameters. PARAM1 PARAM2 (multivalue and depending on PARAM1 selection) PARAM3 (multivalue and depending on PARAM2...

Retrieve data when there's only 1 '.' - Hi geniuses. I got a report to build and I need data from a field which contains lots of junk data,...

Reportin services - AVG of avg - Hello, I have question, how to sum rows. I create query =avg(iif(filds!urgency.value=1 and filds!_time<=14,1,iif(filds!urgency.value=2 and filds!_time<=130,1,0)), everything is ok, but I...

Data Warehousing : Integration Services

SSIS-one source to 3 destination tables - I have one csv that needs to be distributed to 3 tables using SSIS. I am using the Multicast Transformation...

WMI task and WQL query - Hi guys, I am using a WMI task to watch for files. We get about 20 txt files. when they...

Data Warehousing : Analysis Services

Concatenating string to measures in MDX query - Hi, I have MDX Query which is select non empty { ORDER( TOPCOUNT(NonEmpty({ [Measures].[Date] },( [Measures].[Date] )),1,( [Measures].[Date] )), ( [Measures].[Date] ), BDESC ) } on columns from cube The...

MDX to Calculate Moving Avarage - Hello everyone, I am a mdx newbie and I neep (please) your Help. I have created a Cube that has 3...

Data Warehousing : Data Transformation Services (DTS)

OnTaskEvent Handler System variable - ExecutionValue not working in Execute task expression - Hi I am using the custom logging with the use of Execute SQL Tasks (through SQL Source property and expressions)...