Stairway to XML: Level 4 - Querying XML Data

Rob Sheldon from SQLServerCentral.com

You can extract a subset of data from an XML instance by using the query() method, and you can use the value() method to retrieve individual element and attribute values from an XML instance. More »

Temporary Tables in Oracle and SQL Server

Press Release from SQLServerCentral.com

Jonathan Lewis (Oracle Ace Director, OakTable Network) and Grant Fritchey (Microsoft SQL Server MVP) will host a live discussion on Oracle and SQL Server, this time in relation to temporary tables. More »

TSQL Challenge 83 - Compare rows in the same table and group the data

Jacob Sebastian from SQLServerCentral.com

The challenge is to compare the data of the rows and group the input data. The data needs to be grouped based on the Product ID, Date, TotalLines, LinesOutOfService.  More »

Importing Data From Excel Using SSIS - Part 1

Additional Articles from MSSQLTips.com

Recently while working on a project to import data from an Excel worksheet using SSIS, I realized that sometimes the SSIS Package failed even though when there were no changes in the structure/schema of the Excel worksheet. I investigated it and I noticed that the SSIS Package succeeded for some set of files, but for others it failed. I found that the structure/schema of the worksheet from both these sets of Excel files were the same, the data was the only difference. How come just changing the data can make an SSIS Package fail? What actually causes this failure? What can we do to fix it? More »

Today's editorial was originally released on Oct 5, 2007. It is being re-run as Steve is on the SQL in the City US 2012 tour.

One of the really classic analogies in software is that it's like building a house. You have a foundation, multiple teams, lots of contractors that specialize in something, etc. And it's an analogy that's debated as to its relevance over and over. I won't go into the correctness of this analogy, but I wanted to comment on it.

Because Steve McConnell actually followed through on comparing building something to software. He built a fort for his kids and did an estimate and then tracked the results against his estimate with some comments. OK, so it's not a house, but it's an interesting exercise and there is some good information to get out of it.

As my own type of experiment, I did this with a loafing shed and here's the high level results: It took longer.

No surprise there. It's almost like that's a law. Hey, maybe it should be. I now declare Jones Law: It Always Takes Longer. :)

This being Friday, I'll put this into a poll:

What doesn't take longer than you expect in your life?

And don't give me the "I can run to the store and get milk and be back in 12:35." If you're tracking the time of your errands to the second, you need more stuff in your life. Get a hobby, or at least take off your watch.

Seriously, what things in your life actually go as planned when you estimate how long they'll take? It's a hard question. As I think about the things I do regularly, I know that we can get down to karate in about 15 minutes, but I also know that it could easily take anywhere from 1 to 5 minutes to get people and stuff into the car, so I have to leave early. But for irregular things, it's harder to guess well.

I've tiled 3 bathrooms in the house. I estimated the first would take a week, allowing for work, basketball games on TV, kids, life, etc. It took 3 months. OK, so it wasn't a huge priority, but it still took longer. I estimated the second at two weeks, figuring I'd learned things and it took about 4 weeks to get things completely done. Apparently I hadn't learned much. I was closer on the third (3 weeks on an estimate of 2), but the last one is the master and I'm a little worried about running long on the estimate there. I like my marriage :)

Everything from doing taxes, to shoveling manure to taking the kids to some event, always seems to run long. Maybe it's a question of priorities or maybe I'm overly optimistic. Maybe there's so much stuff in life that doesn't have a deadline that we just let it run to its conclusion without worrying about time.

I'm not sure work is much different. So often I see or hear people estimate something, from software to a meeting length to a server move. Everything always takes longer than we estimate, much to the dismay of our managers.

It's said that no battle plan survives contact with the enemy. I'm guessing that life must be a battle for me most of the time.

The Podcasting continues!

Send in some feedback and let us know what you think, audio, video, both, or none. Especially if you want it to continue! Positive comments to the webmaster here will get more more $$$$ :)

• Audio (MP3)
• No video editorial this Friday. My Logicam is a piece of @#%$#$% and the video camera editing wasn't done in time :(. However I do have a bit of fun about Senior DBAs

Music for today's Podcast from: Everyday Jones, All You Said was used in the stream.

Steve Jones from SQLServerCentral.com

» 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

Today's Question:

I set these

SET NUMERIC_ROUNDABORT OFF;SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,QUOTED_IDENTIFIER, ANSI_NULLS ON;

I  then execute the following T-SQL

CREATE VIEW dbo.customer_view
  AS
 SELECT
   [CustomerID]
 , [FirstName]
 , [LastName]
 , [Priority]
 , [CreateDate]
  FROM [dbo].[Customer]
   GO

The view is created successfully. I then execute the following T-SQL

CREATE UNIQUE CLUSTERED INDEX IDX_V1
  ON dbo.customer_view ([FirstName],[LastName])

Is the unique clustered index created? 

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

Troubleshooting SQL Server: A Guide for the Accidental DBA

Three SQL Server MVPs provide fascinating insight into the most common SQL Server problems, why they occur, and how they can be diagnosed using tools such as Performance Monitor, Dynamic Management Views and server-side tracing. The focus is on practical solutions for removing root causes of these problems, rather than "papering over the cracks". Grab your copy today from Amazon.

declare @a table
(
 a varchar(100) not null,
 b varchar(100) not null
)

declare @b table
(
 a varchar(100) not null,
 b varchar(100) not null
)

insert into @a values ('A','B')
insert into @a values ('C','D')

select *
 from @a

insert into @b (a)
 select a from @a

select *
 from @b

By SKR V

Answer: 2,0

Explanation: You cannot insert null values into a not nullable column. There is an error from the second insert, but the select returns 0 rows.

» Discuss this question and answer on the forums

SQL Server 2012 Integration Services Design Patterns

SQL Server 2012 Integration Services Design Patterns is a book of recipes for SQL Server Integration Services (SSIS). Design patterns in the book show how to solve common problems encountered when developing data integration solutions. Because you do not have to build the code from scratch each time, using design patterns improves your efficiency as an SSIS developer. In SSIS Design Patterns, we take you through several of these snippets in detail, providing the technical details of the resolution.

Get your copy from Amazon today

Generate MERGE statements with Table data

Daniel Nolan from SQLServerCentral.com

A stored procedure that produces a MERGE statement for a given table containing a VALUES clause for all rows, useful for migrating static data between environments. More »

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

SQL Server 2005 : Administering

DBA Interview Questions needed - Serious only please. - Hi dudes and dudettes... The boss and I are conviced that we need a dba since the 3rd party firm providing...

SQL Server 2005 : Business Intelligence

Reporting Services Login Box always prompting when deploying my report - Hi to all, I have a problem in deploying my report in BIDS because there's a Reporting Services Login box...

SQL Server 2005 : SQL Server 2005 General Discussion

Invoked Schedule Id of the Job : Urgent! - Hello: When a job step is being executed from SQL Server Agent, I need to determine the "schedule_id" that invoked the...

Current Running Job Schedule Id or Name - Hello: I have a scheduled job running in SQL Server. While the job is running, is it possible to find out...

SQL Server 2005 : SS2K5 Replication

Snap shot agent error - I get this error when I start the snapshot agent for a publication [code="plain"]Error messages: Message: The SQL command 'sp_MSactivate_auto_sub' had returned...

SQL Server 2005 : SQL Server 2005 Performance Tuning

CPU Usage, IO usage and memory usage for sql server 2005 - Hi, Which DMV can be used or is there any query which can give me an exact data about the CPU...

SQL Server 2005 : SQL Server 2005 Integration Services

imoprt XML Data from xml file having more than 15000 charecter row to sql server destination using ssis - In my Xml file one column having more than 15000 thousand charecter row, but in xml source read only 4000...

Importing XML Attributes via SSIS - Hello all. I am currently in the process of attempting to load a number of XML files into SQL (using SSIS),...

SQL Server 2005 : T-SQL (SS2K5)

How to Build a single row with groups of fields from several rows per key - i have several rows like: Customer sn val1, val2, Val3 ..... (sn is a seq# from 1 to 15) A 1 31 54 A...

Customize SQL error message for VB.NET App use - I am wondering if there is a way to overwrite or customize the system error thrown by SQL server. For...

How to partition data of huge table - I have a table(T1) of around 20 million record and about to grow in future by 100 to 200%. It...

Inserting data in one table as selecting from other tables - Hi all! I have a simple question. I've made a simple database in Access and upsized it in my SQL Server...

SQL Server 7,2000 : T-SQL

SUBSTRING And CHARINDEX to find start/end of string - Hi all, I have a big long text string that can vary in lengh and contrain audit information about data that...

Csv list in column to separate columns - Hi all, Can I ask please if I have a row and one column has a csv list in it. Is...

SQL Server 2008 : SQL Server 2008 - General

Memory utilization - SQL Server (2008) is using 92% of the memory. out of 56 GB SQL using 51.6 GB How to find out...

Derializer Report server Report Class 2012 ? - [b][u]serializer And Dserializer Rdlc File [/u][/b] [b]Report Class[u][/u][/b] name space >Microsoft.ReportingServices.ReportRendering Microsoft.ReportingServices.ProcessingCore.Dll (2012-06) [b]Testing....[u][/u][/b] > is false false= bool myBool = typeof(Report).IsSerializable; can't get ReportClass ..... like this...

Query Optimizer and value of local variable. - Let's take code example [url=http://www.sommarskog.se/query-plan-mysteries.html]from this[/url] article (part [i]Parameters and Variables[/i]): [code="sql"] CREATE PROCEDURE List_orders_1 AS SELECT * FROM Orders WHERE OrderDate >...

how to insert values to table only if the same row not exists - hay.... I want insert to table (table name is: "portfolioForExpert") 2 values: 1) expertID 2) portfolioID (value of "1" represent: "stocks",...

un split in sql - hello all. i use this function for split: ALTER FUNCTION dbo.GLB_Split(@String varchar(8000), @Delimiter char(1)) returns @temptable TABLE (id int identity(1,1),items varchar(8000))...

Need to fill the Gaps with previous value - Hi experts, I have a scenario to fill in the GAPS between the dates with previousdate+1 day. here is the table DDL...

Dying to solve this problem :-( "SINGLE QUOTATION" - it's only about ( SINGLE QUOTATION) in ASP.NET here is my SQL Query: [code="sql"]SELECT ID, Title FROM viewTopTitles WHERE Title = ' you're most welcome '[/code] but...

Error With Query - I Have Two Table [b]Arazi[u][/u][/b] and [b]AraziEghdamat[u][/u][/b] and the Relationship between them is 1(Arazi) to Many(AraziEghdamat). i want to select all...

Error when restoring - Error when restoring: Object cannot be cast from DBNull to other types. (mscorlib)

Using case in updating column - ok i have a table called "weights" that has following columns [code="other"] id shipweight1 shipweight2 shipweight3 [/code] Right now i have the following query [code="sql"] @id...

Why does this take so long! - Hi Everybody This is my first posting in this forum, so please bear with me if I should get some thing...

cant get backup on drive C:\ - I write this query for getting backup of DB.when i want to save it on Drive c or desktop it...

SQL Server 2008 : T-SQL (SS2K8)

stored procedure - create procedure outname @Cont Int, @Nm Nvarchar (100) output as select @Nm=FirstName from AdventureWorks.Person.Contact where ContactID=@Cont declare @Nam Nvarchar (100) exec outname 112, @Nam=@Nm output select @Nam is giving...

Nested queries problem in SQL2008R2 - Hello We did an upgrade to SQL2008R2 last night and the query below seems to take an excessive time to...

count(*) taking forever... emergency - Team - Friday night is turning bad for me... One of my SPs was taking forever and after a long haul I...

HTML using XML PATH - Hi, I am generating the HTML using the XML PATH query, which is happening using the below mentioned code, the only...

SQL Server 2008 : Working with Oracle

Linked server headaches - only failing when executed by sql server agent job - I have an Oracle 11g r2 linked server. Its currently working for queries and stored procedures. However, when attemping to...

SQL Server 2008 : SQL Server Newbies

Script to find incorrect data in all tables - Our clients use MS-SQL 2000 through 2008, and soon will have some using 2012. WinXP through Win7, very few on Vista....

Transposing, filtering, most recent record - Hi all, A user at a PC help forum told me to try here. I have a SQL database with a lot...

Need to find user accounts that are running jobs - I keep running in to jobs that are running under the account of the previous DBA that held my new...

Certifications for beginner - Hi ALL, I am currently working with SQL server 2005 database and would like to pursue certifications. From microsoft site, i...

Help reading Deadlock graph - I have been going through the forums on how to read this graph. I was asked where does a certain...

SQL Server 2008 : SQL Server 2008 Administration

Problem with Data collection jobs on one server. - Hi, I have a problem with one server and the default data collection job failing. If someone could help that would...

Logged into a SQL Server and not active state - Hi I need a little help in creating an email alert to my Team mentioning the list not active persons(Disconnected)...

What is a read ? - Hi All When using DMV's to assess your SQL instance, what is a read? For example, when dealing with DMV's like: sys.dm_exec_query_stats sys.dm_exec_requests There are...

Query Memory Usage - Hi All I'm using the sys.dm_exec_requests to check what's happening in my instance There is a column called granted_query_memory, what does this...

Problem With Moving MSDB? - Hey Pals! I moved My MSDB database to another location. after that i tried to start my server but unfortunately it...

Career : Certification

SQL 2012 Querying - 70-461 - In my 15 years of taking various Microsoft certification tests, this test is the first that actually represents day to...

70-461 - Hi, Id like to take this exam and looking for some good prep.. My T-SQL skills are basic at present...

Career : Employers and Employees

Better to be a "jack of all trades" or a "master of one" with SQL? - Or will the answer be, as one of the speakers at SQL in the City put it, "it depends?" Right now,...

Programming : General

Earn Cash Online (NAP235045) - SOFT CASH NETWORK offer one of the World’s best network marketing system with 100% secure and authentic payment, Everyone can...

SQLServerCentral.com : Anything that is NOT about SQL!

How to survey in IT field for the next 5-10 years - Due to heavy Competetion, I feel like surveying in IT field as a developer ( any tool) for the next 5...

Sybase DBA vs SQL DBA - what is the majore difference between from SYBASE DBA & SQL DBA from their day to day activity perspective? maintenance activity perspective? challenge...

Sybase 15.7 vs SQL 2012 - Hi All, How is the feature of sybase database in all over the world? I can see now a days lot...

Datastage Tools - All, I would like to know how is the opportunity for Datastage in all over the world. how about the future...

AB INITIO ETL Tool - All, I would like to know how is the opportunity for AB INITIO in all over the world. how about the...

GREEN PLUM Database - All, I would like to know how is the opportunity for GREEN PLUM database in all over the world. how about...

What DB does Sqlservercentral uses ? - As of my knowledge It would use sqlserver only. anything else apart from sqlserver ?

Reporting Services : Reporting Services

How to display Excel_32.gif image in SSRS for Excel files - In SSRS, uploaded Vis Studio reports display RB_document_32.gif next to the report name to indicate the file type. But when...

Data Warehousing : Integration Services

Pulling from public XML/API data sources for SSIS? - Hello all, I am in the process of building an experimental database for reporting purposes and would like to download...

How to use same connection manager in different control flow tasks? - Task needed to complete: 1) Run sql task to set database to single user 2)Transfer sql server tables in a single database...