In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
Deployment Manager How to automate your .NET and SQL Server deployments
Deploy .NET code and SQL Server databases in a single repeatable process with Red Gate Deployment Manager. Start deploying with a 28-day trial.
 
SQL Backup Pro Get compressed, verified, secure SQL server backups
Use SQL Backup Pro's automated scheduling to get faster, smaller backups. Then verify your restores using DBCC CHECKDB in one easy, automated process. Download a free trial now.
 
SQL Source Control Database source control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Get started now.

In This Issue

Loading XML Data into SQL Server (SQL Spackle)

Learn how to load data in an XML file into a table in SQL Server. More »


Why is your ASP.NET application running slow?

Any queries run against SQL Server or Oracle databases are recorded, along with how many times those queries were run and how long they took. That information is shown alongside your .NET code, so you can quickly see why the code you wrote resulted in those queries being executed. This can be particularly powerful if you’re using ORMs like Entity Framework to access data, which tend to be an impenetrable black box where performance issues can arise, but where it’s difficult to understand why. More »


From the SQLServerCentral Blogs - Connecting Windows Server 2003 VM To External Network In Hyper-V

The Windows 8 and Windows Server 2012 SP1 Virtual Machines on my laptop are connecting just fine to my physical... More »


From the SQLServerCentral Blogs - When will we look back at this?

It’s amazing how software has advanced. I look at the software I use, and I’m amazed. I still remember texting... More »


Editorial - The Bicentennial

Tomorrow is SQL Saturday #200 in Philadelphia. I'm traveling today to the City of Brotherly Love for the event. My partner, Andy Warren, co-founder of SQLServerCentral and SQL Saturday, is also making his way there for the celebration. This is the bicentennial event, and even though it might not be the two hundredth event to actually occur, it represents an amazing success of the SQL Saturday franchise and I am thrilled to be a small part of these events that have helped train, teach, and network so many SQL Server professionals all around the world. We even had Bill Gates learn of the events at SQL Saturday #175 in Fargo just a month ago.

When the first SQL Saturday took place in Orlando in 2007, Andy and I weren't sure how it would survive or grow. We wondered if we'd ever see 50 total events, or even 12 occur in a single year. Five and a half years later, these events have exploded, with over 80 taking place in the last year. Under the stewardship of Karla Landrum, more SQL Server professionals are getting free training every year than they ever might have dreamed of. 

And they're excited by the opportunities offered by these events.

I've seen people drive from Jacksonville to Pensacola for an event. People come from Virginia to Ohio for a day. Plenty of other people adjust their travel plans to come a day early or stay a day late to attend a SQL Saturday. We've had attendees and speakers bring their spouses and children, and even sessions they could participate in. Hundreds of speakers have donated their time and money, traveled long distances to help others. We've had networking sessions, games, BBQs and water skiing scheduled at SQL Saturdays. Summer, fall, winter, and spring, in every month, in over 20 countries, the SQL Server community has come together to teach, learn, debate, practice, and take pride in the work we do.

I'm proud of what we've done together and amazed at how often we have done it. I'm looking forward to tomorrow and all the events that will come in the future.

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

PS - Putting together a SQL Saturday isn't too hard. Some events are extremely well organized and funded, but really if you can find a venue that will allow you to use a few rooms, you're set. Speakers will come, you can charge for lunch, and people will learn. That's what it's all about. Contact Karla Landrum if you want an event in your area.


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:

Using SQL Server 2012 or 2008R2, I create the following table

CREATE TABLE dbo.Orders
(orderid INT NOT NULL,orderdate DATETIME NOT NULL,
empid INT NOT NULL,custid VARCHAR(5) NOT NULL,
qty INT NOT NULL,salesprice MONEY NOT NULL 
CONSTRAINT PK_Orders PRIMARY KEY(orderid));
GO
ALTER TABLE dbo.Orders ADD
TotalSales AS (Salesprice * qty) PERSISTED
GO

I then insert the following data into the table dbo.Orders

INSERT INTO dbo.Orders (orderid, orderdate, empid, custid, qty,salesprice)
VALUES
(301, '20120802', 3, 'A', 10,5.00), (11, '20121224', 1, 'A', 12,9.09),
(105, '20121224', 1, 'B', 20,7.25), (4, '20130109', 4, 'A', 40,7.65),
(160, '20110118', 1, 'C', 14,3.01), (21, '20130212', 2, 'B', 12,6.57),
(40, '20130212', 4, 'A', 10, 11.21), (22, '20130216', 2, 'C', 20,0.09),
(30, '20130418', 3, 'B', 15,33.33), (3, '20120418', 3, 'C', 22,2.20),
(317, '20110907', 3, 'D', 30, .47);

I then execute the following T-SQL

SELECT grouping_id(custid,empid) AS grp_id
,custid,empid,YEAR(orderdate) AS orderyear,salesprice, 
SUM(qty) AS qty,TotalSales
FROM dbo.Orders
GROUP BY GROUPING SETS (
( custid,empid),( custid, YEAR(orderdate)),
(YEAR(orderdate)),
( empid, YEAR(orderdate)),( empid,TotalSales),
( custid,salesprice),()) ORDER BY grp_id;

Select the four(4) correct answers

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

This question is worth 2 points in this category: Grouping. 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.

Microsoft® SQL Server® 2012 Step by Step

Teach yourself the programming fundamentals of SQL Server 2012—one step at a time. Ideal for beginning SQL Server database administrators and developers, this tutorial provides clear guidance and practical, learn-by-doing exercises for building database solutions that solve real-world business problems.

Get your copy from Amazon today.


Yesterday's Question of the Day

Populate a table variable as shown below and set the variable @x to an integer value between 1 and 19 (there will be 20 rows in the table variable). Then execute the two SELECT statements.  In which scenario will statement 1 return more rows than statement 2?  (No cheating!)

DECLARE @Table TABLE
( iAsInt int,
  iAsString varchar(2));
    
DECLARE @i int, @x int;
SET @i = 1;

WHILE @i <= 10
BEGIN
  INSERT INTO @Table
  VALUES (@i,     CAST(@i AS varchar(2))),
         (@i + 1, CAST(@i AS varchar(2)));
    
  SET @i = @i + 1;
END

SET @x = ??? --set an integer value between 1 and 19

--statement 1
SELECT TOP (@x) WITH TIES iAsInt, iAsString FROM @Table ORDER BY iAsInt;
--statement 2
SELECT TOP (@x) WITH TIES iAsInt, iAsString FROM @Table ORDER BY iAsString;

Answer: When @x is even

Explanation: The key is that the INSERT loop inserts two '1' values into the iAsString column, but only one 1 into iAsInt. So iAsString starts '1','1','2','2','3'... and iAsInt starts 1,2,2,3,3...

When sorting by iAsInt, the first set of ties occurs at value 2, as there is only one row where iAsInt = 1. So if you select the top 1, you get one row where iAsInt = 1. If you select the top 2, you get one row where iAsInt = 1, and two where iAsInt = 2, for a total of 3. This pattern repeats with odd and even values, where the total rows returned equals @x when @x is odd, and @x+1 when @x is even.

When sorting by iAsString, the first set of ties occurs at value '1'. So if you select the top 1, you get two rows where iAsString = '1'. If you select the top 2, you also get two rows where iAsString = '1'. When you move on to the top 3 and 4, both result in a total of four rows. This pattern repeats with odd and even values, where the total rows returned equals @x+1 when @x is odd.

I don't think a reference is really necessary here, it's just a matter of working out the logic. But here's TOP anyway...

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

» Discuss this question and answer on the forums

Microsoft® SQL Server® 2012 Step by Step

Teach yourself the programming fundamentals of SQL Server 2012—one step at a time. Ideal for beginning SQL Server database administrators and developers, this tutorial provides clear guidance and practical, learn-by-doing exercises for building database solutions that solve real-world business problems.

Get your copy from Amazon today.


Featured Script

Get Rid of All Those Pesky 1 MB File Growth DB Files

Use sp_MSForeEachDB to set all 1 MB file growth databases to 10%.  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

Scheduled job not running - One of my jobs stopped running. The job is enabled but the next run date in the Job Activity Monitor...

Using Maintenance Plan to Delete Old Files - I set up a maintenance plan to delete .csv files that are generated by SQL Server Agent for another purpose....

SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file - Hi, I have noticed the below message in the error log: 2009-05-27 20:22:24.95 [b]spid2s[/b] SQL Server has encountered 1 occurrence(s) of I/O...

SQL Server 2005 : Business Intelligence

SSRS DEPLOYEMENT PROBLEM - Hi every body, I created a report with SSRS without problem, but when I tried to deploy it , I received the...

deleting rows from a table after processing them in SSIS - Hi Guys, I am a newbie and I need to do the following task: I have to process the rows in one...

dynamic import of multiple flat files - hi need your help regarding dynamically importing multiple flat files into different table of sql server database. the format of files...

Import Excel binary files into SQL Db using SSIS - Hello, I have a several .xlsb (MS Excel binary) files with data that I need to import into a SQL...

SQL Server 2005 : Development

Extracting data from xml - I am trying to read data from the following sample XML file. I have tried SSIS XML task to remove...

SQL Server 2005 : SQL Server 2005 General Discussion

Management Studio becomes Unresponsive - I have noticed that everytime I commit a large task in SQL Server Management Studio (e.g. the rebuilding of a...

SQL Server 2005 : SQL Server 2005 Integration Services

how to write expression for Derived Column transform - I need to filter out all records as follows: where [Month 01] is null and [Month 02] is null and ...

Derived Column to handled mulitple conditions in CASE statement? - HI, I've got a case statement that works, but because I have to do it in SSIS I am at...

Execute Process Task to execute a .jar - I'm trying to run an Execute Process Task that initiates a java program. The error I receive when I run...

Argument "Server1" for option "connection" is not valid. The command line parameters are invalid. The step failed. - I have a SSIS package run fine in BID, but when run as a sql agent job, it failed at...

SQL Server 2005 : T-SQL (SS2K5)

syntax stumped - Hi, Think I'm overlooking the obvious I have this statement: [code="sql"]select *, '20' as [Yrs of Serv] from cte where [Due Date for 20...

Convert integer to 4-character alphnumeric representation - I need to take a number (an integer identity column) and convert it to a 4 character representation of that...

Could not create an acceptable cursor - Hi, First time poster here, I'm having problems with an update statement at the moment, with an error I've not seen...

SQL Server 7,2000 : General

Explaination of Visual Studio Versions and Compatibility? - I'm looking to find a chart or explanation of how the Visual Studio versions work with the various versions of...

SQL Server 7,2000 : Strategies

Data Warehouse question - This is more of a 'what is a general best practice' question than a specific code or syntax question. Recently the...

SQL Server 7,2000 : Performance Tuning

Shrink large database - I have been asked to look a a sql 2000 database that has been neglected for some time. It has an...

SQL Server 7,2000 : Replication

Adding Subscription using Backup Set - Hi All, I want to add a Subscription using Backup Set to Publisher setuped Sql Server 2000 Enterprise Edition. We...

SQL Server 7,2000 : SQL Server Agent

Database Mail - Im having problems sending mail from an on premise database server to an off premise exchange server with office 365....

SQL Server 2008 : SQL Server 2008 - General

Linked servers migration - I have to migrate linked servers from SQL server 2005 to SQL server 2008 R2. Could somebody please tell me how...

using dbcc show statistics - Is it possible to identify candidate keys using dbcc show statistics and if so how would i do it? I am...

need script - I need a script to list all columns which is having not null constraint on it. the script i have...

BCP Failure - I am using bcp to create a text file. It was working fine but stopped working. Here is the bcp...

Linked server on SQL server 2008R2 connecting to SQL 2005 stopped working - Hi Got a linked server on SQL Server 2008R2 connecting to an SQL server 2005 that was working fine but...

Creating views on multiple tables - Hi, Is it possible to create views using multiple tables from multiple databases???? I have 4 servers which are connected remotely and...

Delete take too much time - Hi, I m try to delete some old data to speed up select statement but it takes too much time to...

Scheduling a package but not to run on sunday!! - Hi, Am trying to schedule an ssis package for once a month is it possible to tell the scheduler, if it...

Triiger -> call batch -> Batch call java - Hi Team, am having a java file, which i want to execute in sql trigger using a batch. when an event occurs,...

How to build/configure Reporting server in sql server 2008 - Dear All, 1-We have sql server 2008 with 2 nodes with cluster(Active/Passive). 2-Due to performance problem we are going to separate the...

SQL Server only using 4Gb of 8Gb RAM - Hi all, I'm slightly befuddled and was wondering if anyone could shed some light on why my test box running a...

System Recommendations - Hi, Does anyone know of where or a how best to guide system recommendations for a BI Database server... Typically how is...

TimeDiff without Weekends - Good Morning I am trying to get the time/date difference between 2 dates without any weekends here is the code I...

Creating two databases at the same time, strange behavior - Situation: Create a database in SQL Server 2008 R2, using the wizard. (with a big logfile so it takes some time) When...

SQL Server 2008 Database Mail - Hi SQL Masters, I have a situation on my SQL Server hosted on Virtual machine (Oracle Virtual Box). I have a...

CTE Help! - I have two tables, #allowance #actual with sample data as below [code="sql"] create table #allowance(id int, free_quantity numeric(18,4)) insert into #allowance (id, free_quantity) select 947, 8.0 insert into...

importing data from 2008 named instance to 2005 - Hi All, I am planning to copy a data from SQL server 2008 Enterprise Edition to SQL server 2005 Enterprise edition...

Render CSV with different row header format,detail format, footer format, is possible? - I am just trying for fulfil the request of my task master. I know there are better tools (ssis) for...

Is there an open source equivalent to sqlcmd.exe? - Is there an open source equivalent to sqlcmd.exe for Windows Server 2008 that will connect to SQL Server 2008 R2?...

List of queries using MAXDOP - Gurus, currently, in our environment we configured our sql servers for MAXDOP of 4. here is our config: 4 CPUS - each...

The best method for reading books - [b]Please answer me because your method in reading and learning will help me greatly [/b]

GeoSpatial Data Types - Hi, I'm going crazy here. I need to store Google map locations in a SQL table. I create the table with two...

Importing text file(without delimiters) to SQL server. - Hi Experts, i have a text file with 1 -3 lakh records and 5 columns or so.... each column is seperated...

Servers Consolidation and Linked Servers - Hi, We are planning to consolidate 5 servers in a 2-node active/active cluster. Some of the servers have Linked server between them. After...

NOLOCK hint. - we have a high activity transactional database and we have put NOLOCK hints on all SELECT statements... for sometime now we...

Making a copy of a table. - What is a good method to make a copy of a table. Only the table not the data. Including all indexes. Excluding referential...

SQL Consulting Help - My company is in need of a SQL Server consultant. We need someone to come in and do an evaluation...

SQL Server can't connect to database - I downloaded file SQLEXPRADV_x86_ENU.exe (version 10.00.1600.22), and used it to install SQL Server 2008 Express with Advanced Services, which I...

Help with next date in SQL query - What I need is to be able to find out when a customer next called in from the date I...

Unable to stop a job running "sql server analysis services command" - Hello, From time to time, a SQL Server job with a step type "sql server analysis services command" is running for...

The log for database is not available; Database was shutdown due to error 9001 in routine 'XdesRMFull::Commit'. - Testing and Production db's have lost connection to the translog 2 days in a row now. Today, my prod db...

SQL Agent not able to start on SQL Server 2008 Developer Edition - Dear All, I have SQL Server 2008 Developer Edition (64-bit) SP1 installed on a Windows Server 2008 R2 machine. For some...

SQL Server 2008 : T-SQL (SS2K8)

Flagging Records within a table of sequenced numbers - I have a problem I'm hoping someone can help me with. I have a table of sequenced numbers. In this...

using row_number() over partition by to get datediff by row - I have a list of events that occur for a patient. I need to count the number of events that...

Delete failing in distributed transaction on linked server - I am looking to "transfer" records from a table on Server A to a table on Server B. And then...

Funnies on Views??? - Hi all Just a quick one. As we all know a view is a set of columns from a table we...

I want to write validation Query which should return invalid parents if any.... - I have a table for which I want to validate where each parent intermediary is also a parent to itself....

How to get date as my column not as row - I have data that looks like the following: Sales_2012 Plan fiscal_week_2013 Sales_2013 LY PN 232.2935700 286.8642 2013-01-05 263.1448400 0.132811553931518638 -0.0826852665251378 237.2016700 286.8642 2013-01-12 258.2310900 0.088656289814485707 -0.0998144463016142 232.0180700 286.8642 2013-01-19 264.8221900 0.141386056698083903 -0.0768380765586006 238.2087000 286.8642 2013-01-26 266.3660400 0

SQL Server 2008 : Working with Oracle

Installing Oracle 11g Client in a sql server 2008R2 active/passive Clustered Environment - I'm trying to get a step-by-step approach to installing Oracle 11g Client in a sql server 2008r2 Active/Passive Clustered Environment....

SQL Server 2008 : SQL Server Newbies

SQL Server SSMS Doesn't like the ORDER BY Clause. What?? - OK. I have the most basic of queries and SSMS is barking at me. I created this as a View...

Read Log from Windows 2003 Task Scheduler - I am looking for help in reading Windows 2003 task scheduler log history to find out if a task has...

Comparing rows between two tables - I have two tables that I have to compare to get the differences. Table Design Service varchar(40) CustomerName varchar(50) ConnectDate datetime Disconnect Date datetime There is...

Bulk Copy. - Hi Experts, i have a text file with 1 -3 lakh records and 5 columns or so.... each column is seperated...

Power Pivot - Hi Guys.Need some of your expertise. I just wanted to know is it possible to run more then 1 query on...

Converting a old .sql to new - Hey all, I have a question from a complete noob to sql. I have a old set up .sql that...

SQL Server 2008 : SQL Server 2008 Administration

Locking while using sp_updateextendedproperty - I am seeing locking/blocking when using sp_addextendedproperty and sp_updateextendedproperty. BOL doesn't mention this behaviour can anyone shed any light onto what...

Moving SQL Server 2008 R2 to new Server Hardware - Hi All, I'm tasked with moving an existing SQL Server 2008 R2 environment to a new server. The current server is...

SQL 2008 Audit doesn't show parameters - I need to audit all accesses, updates, inserts, deletes, executes, etc. done to specific tables in our database. I upgraded...

Career : Certification

70-448! - hello. I am preparing my exam 70-448 and want to do more test. What exams you recommend me? I saw SelfExamEngine and...

Programming : XML

Getting node values from an XML column - I have a table with a uniqueID column (context_id) and a column containing XML data in the format shown below: [code="xml"] <?xml...

Programming : Powershell

Help making my RestoreDB script better pls? - I'm a total noob with posh :-) I got it powershell running and then hacked together this script. I was following the...

SQLServerCentral.com : Anything that is NOT about SQL!

What is Functional DBA? - Hi All, I hear “Functional DBA”, I search in Google to know exact meaning for it and found that “A functional...

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

Exporting to csv issue from ssrs 2008 R2 - Hi there peeps I have a simple Tablix Report that contains a number of fields that I'm exporting as a csv...

3 Parameters, select 1 parameter, pass the other 2 parameters - :crazy: Hey Everyone, This site is awesome first of all and has been very helpful. I have come across a new...

Report Manager Security Permission Issue - I have a folder in SSRS Report Manager that a user created for testing reports. The issue is the user...

Data Warehousing : Integration Services

Opening a rowset for "A03210" failed - Hi all, I'm getting this error since this morning and can't find out why it happens. From what I saw on...

update column base on another column while doing import - hi guys i have excel souce with data that i need to import into oledb destination, here's an example of...

Convert String to Datetime (USING SSIS) - HI Guys. Need some help. I want to insert a value "5/27/2013 16:42:37.490000" (Read from a flat file (DT_STR)) into a...

SSIS structure for Documentation - Hi, I am trying to extract some information from SSIS packages store on the server in [msdb].[dbo].[sysssispackages] For all packages, i need...

Data Warehousing : Analysis Services

Storage Modes in SSAS 2008R2 (MOLAP, ROLAP, HOLAP and InMemory) - Can anyone throw some light on Storage Modes in SSAS 2008R2 (MOLAP, ROLAP, HOLAP and InMemory). I overall aware about the...

Any solution for better performance about a cube with 3 distinct counts on the same source DB table on SQL Server 2008 R2? - [b]Backgrounds:[/b] I have a big table about 180G for size and 4 billion rows on SQL server 2008R2 64bit so far,...

MDX ParallelPeriod Function - HELP - Hi to all. I have a calculated measure in one of my cubes. This is named DELTA SALES AMOUNT It calculates [b]Parallelperiod[/b] about...

Urgen help needed on MDX or SSAS cube design - Hi, I just recently found we have duplicated records in our fact tables which are allowable. That means the combination...

Login failed for user 'NT Service\MSSQLServerOLAPService' 28000 - I am using SQL Server 2012 developer edition on Windows 7. I have the following error attempting to process a...

SSAS CUBE PROCESSING ERROR - I am new bie trying to learn SSAS. I have these errors in trying to process my cube after it...

Article Discussions by Author : Discuss Content Posted by Unknown Unknown

SQL Query date conversion problem - Hi, The query below is working fine for the date as 04/08/2008 and 08/08/2008, however it is not working for the...