In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL DBA Bundle ‘Six Scary SQL Surprises’
Brent Ozar joins the DBA Team, for Lesson 3 of their ‘Top 5 Hard-earned Lessons’. Gain valuable tips from the pros - Read now
 
Deployment Manager NEW! The easiest way to deploy .NET code
Deploy ASP.NET applications fast, frequently, and without fuss, using Deployment Manager, the new tool from Red Gate. Try it now.
 
SQL Monitor Optimize SQL Server performance
“With SQL Monitor, we can be proactive in our optimization process, instead of waiting until a customer reports a problem,” John Trumbul, Sr. Software Engineer. Optimize your servers with a free trial

In This Issue

Re-throw Errors with Logging

This article brings you a technique and framework to use in your stored procedures that can allow you to re-throw and log errors. More »


How to Post Performance Problems

How do you get help? You can post a question on SQLServerCentral, but to get the most help you can, read this article from MVP Gail Shaw. More »


Ad-Hoc XML File Querying

When you need to shred just part of the data within a large XML file into a SQL Server table, the most efficient way is to just select what you need via XQuery or by using XPath, before shredding it into a table. But precisely how would you do that? More »


From the SQLServerCentral Blogs - Day 31 of 31 Days of Disaster Recovery: Backup and Restore of the Resource Database

It has been a long journey to the final day my 31 Days of Disaster Recovery series, but we have... More »


Editorial - Halo 4 and Hadoop

Halo 4 was one of the more successful games that Microsoft has launched on the XBOX console. The latest version of a franchise that is as old as the XBOX platform, Microsoft could have just delivered a game and assumed the popularity of the previous games would translate into popularity for this title. However they decided to do more, and to use data to help them ensure they were engaging with their customers.

One of the ideas was a contest that coincided with the launch. However this wasn't a static contest that was developered prior to the contest with fingers crossed it would engage gamers. Instead the team used data as the contest was ongoing to understand how players were using the game. Using Hadoop and Azure, the team decided to analyze information from the online games. They build a system that can consume the data generated, analyze and process this data with HDInsight and then respond to queries from the business people.

It's a great use of the tools that were available to the groups at Microsoft. This is much easier for them than most of us, since they are generating lots of data in the Azure cloud anyway, and it resulted in a better application all around. Developers had insight into which features they should work on, marketers better understood their clients, and gamers had a fairer game because cheaters were detected.

This isn't the system many of us would use, but parts of it do showcase how you can build a useful feedback loop into your application, and there are plenty of options in the Microsoft stack that can help you process large amounts of data.

Steve Jones

No Podcasts today due to technical difficulties. We hope to have them back tomorrow.

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

Will this code work correctly, meaning the table gets truncated? Or is there an error for the reference of Col_2?

CREATE TABLE dbo.Test
(
     Col_1 INT PRIMARY KEY
    ,Col_2 INT REFERENCES Test(Col_1)
    ,Col_3 INT IDENTITY
)
;
INSERT INTO dbo.Test VALUES (1,1);
INSERT INTO dbo.Test VALUES (2,2);   
INSERT INTO dbo.Test VALUES (3,3);   
GO
TRUNCATE TABLE Test;
GO

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

SQL Server Execution Plans shows you what's going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by SQL Server, including: Which indexes are being used, and where no indexes are being used at all. How the data is being retrieved, and joined, from the tables defined in your query. How aggregations in GROUP BY queries are put together. Grab your copy today from Amazon!


Yesterday's Question of the Day

A Principle server sends a transaction to the mirror and waits until the transaction is committed on the mirror server. Then the transaction is committed on Principle Server. This mode is called?

Answer: High Safety

Explanation: This is a synchronous mode called High Safety.

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

» Discuss this question and answer on the forums

SQL Server Execution Plans

SQL Server Execution Plans shows you what's going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by SQL Server, including: Which indexes are being used, and where no indexes are being used at all. How the data is being retrieved, and joined, from the tables defined in your query. How aggregations in GROUP BY queries are put together. Grab your copy today from Amazon!


Featured Script

Backup all SSAS databases using TSQL

Enumerates and backups all SSAS DB's 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

sql server agent jobs - Can anyone tell me how can i copy the sql server agent jobs easily from production to test server without...

SQL Server can't restore from UNC path - I have two servers networked but not on a domain, just separate Workgroups. I am able to ping both servers...

LDF is IDF instead - The production database has 1 MDF and 1 log file. The log file extension is idf instead of LDF. Would this...

Log Shipping 'skipping' all logs and not restoring - I am trying to configure Log Shipping between a production and tests Server. The configuration of Log Shipping seemed to...

SQL Server 2005 : Business Intelligence

I cannot import SSIS package into MSDB folder. - Hi all, I am working on SSIS and created integrated services project and saved them under a new folder. I want to...

Need to add % sign for only few rows - Hi I have SSRS (2008) Report which line MTD YTD Total Line 1- Balance 10000 200001 208987 Line 1- Volumne 100 200 330 Line 1- Avg 42 67 78 Line 2- Balance ...

Creating Real time DASHBOARDS - Hi to all. I work for a company that uses from 2005 , SSAS of MICROSOFT. Now we are using SSAS 2008 R2...

Using SQLCMD within SSIS package - I'm trying to work out a way to use SQLCMD to run a backup script through an SSIS package. I...

SQL Server 2005 : SQL Server 2005 General Discussion

Barcode 128 generator (using scalar function) - I'm looking for a barcode generating Function that uses the standard Code128. Apparently no function that fixes this has been...

retrieve the previous version of a stored procedure - Hi Is there any way to retrieve the previous version of a stored procedure after compilation and closing the window? please...

SQL Server 2005 Cluster - [sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed - Hi all, Am hoping that you can help me with a problem that seems to be quite popular on the web...

SQL Server 2005 : SQL Server 2005 Strategies

To Database or File System - I was looking for others thoughts on topic of saving pdf to Database or File System. Currently we save all...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Updating covering Index - Dear All, I have a covering index Idx1 on Col1 included Col2 and Col3. My question is if i run the following...

Reorganze index and Statistics - Looking for some clearer answers here... we have quite large databases 1TB+ and we are doing nightly Reorganize of our index....

Indexes with Include - I am performance tuning a database for a client. I have used several methods to identify tables that could use...

SQL Server 2005 : SQL Server 2005 Integration Services

Data Type Conversion of Excel Source - I would like to import an excel sheet to a DB table which is having around 100 columns having data...

SSIS and dealing with embedded double quotes - Is there a way for SSIS to overlook embedded double quotes in the data so when I run my package...

ForEach Loop forEXCEL File - Hi, Any sample code/steps for conversion of EXCEL file into CSV file using ForEach Loop? Thanks

SQL Server 2005 : T-SQL (SS2K5)

Query to retrieve a column depending on variable value? - Hi guys, I have a problema that I can't find a clue to face it: The situation: I have three different tables...

SQL Server 2005 : SQL Server Newbies

SQL Query help - I need help with the following SQL query: select whoverified, whochecked AS 'userid', count(*) whoverified, count (*) whochecked from rxproductivity join users on rxproductivity.whoverified...

SQL error Connection. - http://imageshack.us/photo/my-images/339/89149644.jpg http://imageshack.us/photo/my-images/4/36524814.jpg When i Click start at "Service manager" they be like this . Im using SQL 2000 =Sory my bad english

SQL Server 7,2000 : T-SQL

Using CASE in real world examples - Hello, I am new to this forum. I am using Microsoft Access 2010, which includes SQL. Here's my problem: What I want to do...

SQL Server 2008 : SQL Server 2008 - General

Arithmetic overflow error converting varchar to data type numeric. - Here is my query, select a.row,sum(CONVERT(decimal(11,0), a.value)) as total from (SELECT adc.row,adc.col,adc.value FROM AnesthesiaDrugCells adc right join AnesthesiaDrugs ad on adc.row = ad.row where...

Execute SQL in all database - Hi, I would like do execute multiple statements in all databases. I created the following code: DECLARE @C1_NAME VARCHAR(1000), @SQL VARCHAR(1000) DECLARE C1...

running total possible?? - Hi I have a view which selects name and date say to make it short... each name can contain more than one...

Options for deleting millions of rows from a heap? - One of the Devs I work with "inherited" a DB that started life back in the '80s with ISAM and...

Any potential issues with putting a Stored Procedure in Master? - I'm working in a rather locked down environment, and would rather avoid adding a DB if I can. I'm thinking...

The delivery extension for this subscription could not be loaded. - Hi All, I have a single SSRS report which is set with a daily (weekday) subscription. Randomly I get the...

Count number of days that a doctor works - I need to count the number of days that a doctor works in a month. My data has the doc's...

Table design and query performance - Hi All, I'm working on database where a table contains an ID that is prefixed with a 2 letter identifier type i.e AB123456 AB345234 AB423423 XY456789 XY234344 Ideally...

SQL Query Confusion - Hi, I have 2 tables Table Member with columns A B C D apple bread carrot Chocolate Mango Begal Onion Candy Table Util Columns A B C...

Difference between? - 1) Declare @i int=0 select @i=id+1 from user; select @i /* here id is an identity column*/ 2) select @i=max(id)+1 from user; ...

restore database without overwriting users - I am receiving a daily download of a database and restoring it locally to do some custom reporting on. I...

Deadlock Analysis Results from trace flag 1222 - Hi. I have just captured deadlock info into the error log using trace flag 1222. Can anybody help me disecting the...

SQL registration in Management STudio - From time to time, I need to test a users login with SQL, so I go to a registered servers...

Parallel Thread Deadlocks Help? - Trace Flag 1222 enabled Profiler Captured Database Tuning Adviser I’m believe I’m getting “Intra-Query Parallel Thread Deadlocks” and not really sure how to...

Minimal Logging Enhancement in SQL Server 2008 - Hi folks, SQL Server 2008 has enhanced INSERT..SELECT statement to allow minimal logging in certain cases, documented here: [url=http://msdn.microsoft.com/en-us/library/ms174335(v=sql.100).aspx]http://msdn.microsoft.com/en-us/library/ms174335(v=sql.100).aspx [/url](See section: Best...

Inserting rows from one table to another another with PK column value incrementing - Hi, We are using Sql server 2008 R2 express. We have two tables,table1(id int,name nvarchar(50)) and table2(id int,name nvarchar(50)). For both...

Creation of auto Increment row - Hi, I am using Sql server 2008 R2 express.I want a column [b]id[/b] with auto incrementing value in my table.But i...

Enabling Trace Flag T1118 - Hi, We are using Sql server 2008 R2 express. We heard enabling T1118 is good for tempdb contention.Somewhere it said...

fragmentation in database - Hi apart of avg_fragmentation_in_percent in dm_db_index_physical_stats what other factor should be considered while considering table to be defragmentated eg table...

KILLED/ROLLBACK STATE - SERVICE RESTART - Hi all, Is there any fix other than sql service restaRT when there is transaction stuck in killed/rollback state (after killing...

reorganize index - Hi Guys, I'm going to reorganize indexes with > 10%. Is there a need to update statistics after the re-org? Understand that if...

Slow Cascade Stored Procedure & Hang - Table Image : http://tinypic.com/r/1075g6v/6 So I have this query which searches id by id and calculates the cost accordingly. But is very...

SQL Server 2008 : T-SQL (SS2K8)

system tables and views - Does a database diagram exist of SQL Server's system tables? I am looking for documentation that will show me on...

problem with union - Hi all, I have a problem with my "union" query, this a sample of my query. What I'd like to...

Query help - [code="sql"] I need a query to get the required output.. DECLARE @Table TABLE( stid INT, stuName VARCHAR(200), studwor varchar(100) ) INSERT INTO @Table SELECT...

I think I already know the answer but... - I suspect the answer is going to be "No" but thought I'd check with more experienced heads than my own. I...

Reorder a custom order column - Hi, Been stuck on this for a little while and hoping that someone can help me figure out my logic problems. So...

Return 1 record with data from multiple tables - All, I have data in numerous tables. The first 2 tables are simple: tblProducts ----------------- ProductID ProductName CategoryID tblCategories ---------------- CategoryID CategoryName Each product links to a category. That part is...

Query help - [code="other"]I need a query to get the expected output Table:Student stguid stuName studwor stid ------------------------ ----------------- ----------------- ----------- 642-4d5d-9af0-4c7a18dd ChrisName Chris 255 4171-8655-2de255b88e08 ChrisCity SAN City 179 2a0d-4100-bd1c-343882 ChrisCou

How to call a batch file to execute from an SP - Hi All, Need your assistance please, I am not very good with scripting. I have created a draft of SP, and I...

How to concatenate group of rows - Hi, I have a data structure as followed : [Name], [Task] and the data is : John, task1 John, task2 Joe, task1 Joe, task3 Joe, task4 Jane, task1 Jane,...

SQL Server 2008 : SQL Server Newbies

New San/Compellent testing - I need to create a query that will execute for 20 minutes or more so that connectivity and redundancy can...

Reporting Service errors when deploying - Thanks in advance for any help... I have SS2k8R2 Developer with reporting and analysis services running. I'm learning to use Reporting...

how to insert datetime column into table - All, I'm having strange issue trying to do the following which I thought was just going to be straight forward select top...

SQL Server 2008 : Security (SS2K8)

Disable & Rename 'SA' - Hi All, Our SQL risk assessment determined that 'sa' should be renamed and disabled. As a fallback readiness, i gathered all...

Cannot use the special principal "dbo" - Hi, In one of our servers, there was a created a login name = <domain>\FarmA This was assigned as username = 'dbo' to one...

SQL Server 2008 : SQL Server 2008 High Availability

Transaction Log Full - Hi, I need help with my database. The database is mirror to another database and not the disk is full. No...

DB for Reporting - We have a policy here that no one gets access to the production databases ever. There is only 1 dedicated...

SQL Server 2008 : SQL Server 2008 Administration

owners_id remains NULL - I had recently move my data and log files to different locations on the server box to keep them all...

SA Disabled\Locked - Hi Experts, We have two situations here 1. ‘SA’ is locked and we don’t have any login with sysadmin previleage 2. SA is disabled...

Missing Lookup operator? - Hi All Consider the following table and their indexes [code="sql"] create table test1 (Col1 int identity (1,1) primary key clustered, Col2 int, Col3...

connecting sql server 2008 to visual studio 2008 - I cannot connect my server name to server whenever i m connecting error is displayed A network-related or instance-specific error occurred...

Cant see SQL Server 2008 R2 on local, no matter what - Hi, no matter I tried I still can't access instance of MSSQLServer I install on my machine, and I'm trying...

Running SQL Profiler Trace on a specific table - Is there a way to run a SQL Trace on a specific table? I've got a table in the production...

Catching culprits of high tempdb growth - Hello, We are facing issues with tempdb on our SQL server 2008 clustered instance. The SQL version is Microsoft SQL Server...

starting sql server - Is this possible to restart the sql server in single user mode if yes is there any impact on server

HIgh CPU usage by SQLserver.exe - HI All, We have moved one of our 2005 database to 2008 R2 windows server- with 16GB RAM and 4CPU's. The database...

Database Mail Disabled but SQL Server Agent Sends Out Alert Emails - Since executing sysmail_stop_sp (disabling the Mail queue) - sp_send_dbmail will stop working (as expected) but the sql agent still sends out...

Career : Certification

How to study for MCSA SQL Server 2012 - Hey guys, I just decided upon a career change at the end of last year and want to become a...

Slave to 70-450!! - Hi all, I'm in need of some advice from some learned peeps regarding study material etc for my 70-450 exam. I've...

Programming : Service Broker

Fire and Forget Solution. - Fire and Forget Solution The solution is let the target end the conversation first. The initiator can simply send the message...

SQLServerCentral.com : Anything that is NOT about SQL!

So How Do You do your mass Mailings? - Once a year I help out a local non profit promote their annual event. This is the third year for...

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

SQL or Oracle - Having only ever done MS SQL I don’t know a great deal about Oracle. There are a few Oracle DBAs...

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

2nd drilldown to show extra data - Hello, The stored Proc. (sp) returns fields as follows: CityID, cityName, citydate, population, area, counties, ... The above fields are used to populate...

Documenting SSRS RDL files - I'm working in SSRS2008 R2 and I have several projects, which in turn have several report definitions held within them....

Could not connect to the report server http://localhost/reportserver - I currently have SQL2k8 R2 Reporting services on client machine (Windows 7). I created and deployed reports on this machine....

date parameter issue - Hi I have created a parameter with a called MonthEnding that has a datatype Date/Time. In my report parameter properties i...

Data Warehousing : Integration Services

SSIS-get latest file from a group of files based on last modified date - Hi All, I have a set of csv files that is being backed up in a folder on daily basis. Say FOr...

SSIS package wont write to excel destination - i am adding a part to an existing SSIS package which is to write to an excel destination what i...

SSIS packages deployment strategies - Hi friends, I would like to discuss some ideas toward the deployment of SSIS packages across servers (Development, test, production and...

Data Warehousing : Analysis Services

Can´t view properties for analysis project. - Hi, i have an analysis services db which contains datasources, data source views, dimensions and cubes.. I have used the "open...