In this issue

Featured Contents

Editorial

Featured Script

A primary cause of complexity is that software vendors uncritically adopt almost any feature that users want. --Niklaus Wirth source

 
 advertisement
 
SQL Source Control The best way to version control T-SQL
SSMS plug-in SQL Source Control connects SVN, TFS, Git, Hg and all others to SQL Server. Learn more.
 
Red Gate Cloud Services “Thanks for building such a useful and simple-to-use service”
- Steve Harshbarger, CTO, 10th Magnitude. Get started with Red Gate Cloud Services and back up your SQL Azure databases to Azure Blob storage or Amazon S3 – download a free trial today.
 
SQL Skills SQLskills Immersion Events - Deep SQL Server Training
Deep technical training by world-renowned experts in Bellevue, WA in August 2012. You can't get better ROI for your training budget. Learn more.

In This Issue

Stairway to T-SQL DML Level 9: Adding Records to a table using INSERT Statement

Not all applications are limited to only retrieving data from a database. Your application might need to insert, update or delete data as well. In this article, I will be discussing various ways to insert data into a table using an INSERT statement. More »


SQL in the City - Austin 2012

A free day of training in Austin, TX with Grant Fritchey, Steve Jones and a few others. Join us to learn about SQL Server and how you can more efficiently work in your job every day. More »


SQLskills training goes online worldwide (and free in September!)

SQLskills is recording their knowledge in conjunction with Pluralsight for you to view from the time and place of your choosing. And it's free in September. Read more to find out how you can get access. More »


Upgrading SSIS Custom Components for SQL Server 2012

Having finally got around to upgrading my custom components to SQL Server 2012, I thought I’d share some notes on the process. One of the goals was minimal duplication, so the same code files are used to build the 2008 and 2012 components, I just have a separate project file.  More »


Editorial - Rogue Algorithms

I have always thought that computers do some amazing things, but they tend to do what we tell them to do, even if what we tell them to do is not what we want them to do. In those cases we usually find computers are helping us make mistakes faster than ever before. As we automate and link more and more systems, this can become a bigger and bigger problem in the world.

I ran across this short link on a rogue algorithm that caused a fluctuation in a number of stocks. It looks like this was a case of poor analysis and poor QA at one firm, but imagine if we had some type of "update" released to a large number of companies? This type of problem could cause a dramatic short term fluctuation in the stock market, which shouldn't affect most of us in the long run, but you never know. If this caused a company to go out of business, and they were the company holding your retirement savings, you might feel differently.

This isn't necessarily a problem that affects just financial institutions and their custom software. This is potentially a problem in any business that writes its own algorithms. That includes many of our companies, and many of us. We write queries, reports, and develop algorithms that help "the business" analyze data and make decisions that can affect our companies.

This is worrisome to me, especially when we have companies that press for more and more analysis, with algorithms written more and more quickly to respond to business events. Success in this area often depends on the developer having an understanding of the nature of the business and the meaning of data, and strong working relationships with the business analysts. That comes over time, and is easily lost when employee turnover is too high.

I hope that companies are learning that there is value to retaining employees, especially those that work with data and have invested effort in learning more about their business. Unfortunately I think that it take a disaster like this for some managers to understand the value of retaining employees and their knowledge. Even more unfortunate is the fact some managers never learn that.

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

For SQL Server 2008 or later, given the following table:

CREATE TABLE dbo.Table1 (
    RowID INTEGER IDENTITY PRIMARY KEY CLUSTERED,
    DateTimeStamp DATETIME DEFAULT GETDATE(),
    Col1 INTEGER SPARSE,
    Col2 INTEGER SPARSE,
    Col3 INTEGER SPARSE,
    TblColumnSet XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);

What will be the result of the following statements?

INSERT INTO dbo.Table1 (Col1) VALUES (1), (2);
INSERT INTO dbo.Table1 (Col2) VALUES (3), (4);
INSERT INTO dbo.Table1 (Col3) VALUES (5), (6);
INSERT INTO dbo.Table1 (TblColumnSet) VALUES ('<Col1>1</Col1><Col2>2</Col2><Col3>3</Col3>');

SELECT RowID, DateTimeStamp, Col1, Col2, Col3, TblColumnSet
FROM dbo.Table1;

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

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


Yesterday's Question of the Day


DECLARE @Table AS Table (SlNo INT,Name VARCHAR(500),Salary MONEY NOT NULL)

BEGIN TRY
    BEGIN TRANSACTION        
    INSERT INTO @Table Values(1,'SQL 2005',2005)
    INSERT INTO @Table Values(2,'SQL 2008',NULL)
    INSERT INTO @Table Values(3,'SQL 2012',2012)
    COMMIT TRANSACTION
    SELECT * FROM @Table
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
    SELECT * FROM @Table
END CATCH

How many rows will be returned in SQL Server 2008?

Answer: 1

Explanation: The "ROLLBACK TRANSACTION" does not work with table variables. The second insert query will return error and will jump to the "BEGIN CATCH" Block, and the select will return 1 row.

Ref: Table Variables - http://msdn.microsoft.com/en-us/library/ms175010.aspx

» Discuss this question and answer on the forums

Professional SQL Server 2012 Adminstration

Microsoft SQL Server 2012 will have major changes throughout the SQL Server and will impact how DBAs administer the database. With this book, a team of well-known SQL Server experts introduces the many new features of the most recent version of SQL Server and deciphers how these changes will affect the methods that administrators have been using for years. Loaded with unique tips, tricks, and workarounds for handling the most difficult SQL Server admin issues, this how-to guide deciphers topics such as performance tuning, backup and recovery, scaling and replication, clustering, and security.

Get your copy from Amazon today.

SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.


Featured Script

Insert Update Stored Procedure for a table

Generic Script for Insert Update Stored Procedures 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

Trigger distribution job on completion of snapshot job - Since 30-Aug, the snapshot agent is running for more than 15mins, the interval between the snapshot & subscriber job is 15mins,...

Temp db log file is growing . - tempdblog file is growing to 5 gb. I dont have more space on the drive. want a immediate solution . without restarting...

xp_delete_file not deleting files on share drive - Hello, xp_delete_file not deleting files on share drive. is there any permission to be given?

Career growth - Production Support SQL Server DBA - I have just started as a Production Support SQL Server DBA . Earlier as I Was working as a development dba. Personally...

SQL Server 2005 : Backups

Change Reovery Model During Backup - I have a SQL 2005 production database running with a Full recovery model. Once a week I have a SP...

backup file size - Can anybody help in this matter Original database size [b]100 MB[/b] Backup files created, in scheduled time but size was [b]357...

SQL Server 2005 : Business Intelligence

BI Jobs? - Hi people! I'm asking directly to people who works in Business Intelligence! Definition of BI? Do performancepoint, reporting services, excel services...

Anything out there better than SSRS - Hi All Beginning to look around for other BI tools. Don't have a vast amount of knowledge in this area, but...

SQL Server 2005 : Development

Internet Movie Database - I'm a big fan of Internet Movie Database, but there are a few points on which it could be improved...so I intend to make my...

SQL Server 2005 : SQL Server 2005 General Discussion

Collation Error on Login's Properties - Hi everybody ! I have this issue when clicking on the properties of the logins on security folder : "Cannot resolve the...

"This file was created in a previous beta version of Excel 2007. Open the file with Excel 2007 to save it to the most recent version of the Excel 2007 file format before opening the file in Access 2007." - Hi, I am using SSIS 2005. Got the source file in XLSX format. As 2007 format not supported in SSIS 2005, so i...

How to dynamically assign colors to data - Hello, i am generating a matrix report using Visual studio 2005. The report as data contains project names on a...

Using Matrix Tool in Visual Studio 2005 - Hello, i am using a matrix which gives me data as follows Month1 Month 2 … Week Ending 1 Week Ending 2 ...

Using Parameters in Reporting services 2005 - Hi , i am trying to generate a report based on resource name. I have already added the @Res in my...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Is Database Engine Tuning Advisor's Recommedations are safe to apply on DB - Hi All, I am new to use Database Engine Tuning Advisor for tunning of my company's DataBase Named (ABCData) and I...

Deadlock investigation, help! - Hi All, I know this is a general cry for help for my specific deadlock but hoping you can help understand...

SQL Server 2005 : SQL Server 2005 Integration Services

SSIS FTP writes big temp files to profile folder on C... - Hello, I have a complex SSIS package from a vendor. It includes an FTP task which moves files that total about...

SQL Server 2005 : T-SQL (SS2K5)

Performance issue with tally solution - Hello everybody! I have a performance issue with a set based solution. Please could you advice me what I'm doing wrong? I...

Conversion failed when converting datetime from character string. - I have written the following query to extract data splitting a text string into separate fields. This query works fine...

SQL Server 7,2000 : Administration

Application Disconnectivity Issue! - Dear Team , Iam Facing an issue in production environment, while i have connected to DB server Application hang and stop...

SQL 2000 Connectivity Problem - Dear All We have production server in which sql 2000 is installed in cluster enviroment. We are facing connectivity problem from our...

SQL Server 7,2000 : T-SQL

Increment in sql server. - Hi all i need to do increment a number like aa001 to aa999 then again ab001 to ab999 aa001a0 to aa999a0...

Increment in sql server. - Hi all i need to do increment a number like aa001 to aa999 then again ab001 to ab999 aa001a0 to aa999a0...

SQL Server 2008 : SQL Server 2008 - General

[Err] 22003 - [SQL Server] Arithmetic overflow error converting numeric to data type numeric. - Hi there, I hope your help. This is the error in my query: [Err] 22003 - [SQL Server] Arithmetic overflow error converting numeric...

Lock Memory (KB) Counter shows 4 GB Usage, with 1800 transaction locks, how to reduce lock memory? - Hi as title states, in my SQL Server 2008 EE SP3 box with 64 GB memory and 59000 max server...

How to replace the strings? - Hi Friends, I have a column called Description which contains values like "Qty: 1 | Desc: 1991 LOADRITE BOAT TRAILER | Value: .00 | Recovered...

Running Values ( Cummulative) - Hi All, [code="sql"] Date Item_Name Value Cummulative_Value 2012-01-01 Actual 5 5 2012-02-01 Actual 5 10 2012-03-01 Actual 10 20 2012-04-01 Actual 5 25 2012-05-01 Actual

Mirror Time Recorded - Time to send - infinite? - Hi, what could be issues as below records for mirror server? It is showing time to send INFINITE Time Recorded Role - Mirror Mirror - Synchronized UnusendLog...

Using GUIDs as primary keys/clustered indexes - Hi All, One of my production databases has 120 tables out of which 104 tables are using GUIDs as clustered indexes....

Removing non-alphabetic characters from a column - I have a single column table C column is fname varchar(255) I want to remove dirty data ie anything that is not alphabetic...

"Free List Stalls" vs "Free List Empty" - I'm trying to understand the relationship (if any) between the SQL DMV counters: [b]SQLServer:Buffer Manager\Free list stalls/sec[/b] and [b]SQLServer:Buffer Partition\Free list empty/sec[/b] I...

Downgrade from 2008R2 to 2008 - Hi All Having an issue when downgrading from 2008R2 to 2008. Using the copy database wizard to perform the copy works well...

"This file was created in a previous beta version of Excel 2007. Open the file with Excel 2007 to save it to the most recent version of the Excel 2007 file format before opening the file in Access 2007." - Hi, I am using SSIS 2005. Got the source file in XLSX format. As 2007 format not supported in SSIS 2005, so i...

data warehouse - i have a data warehouse question for you, I have a set of reports that I am migrating to a...

could not find installable isam - Hi, I am using SSIS 2005. Got the source file in XLSX format. As 2007 format not supported in SSIS 2005, so i...

DBCC CHECKDB performance - I have a server and two instance SQL SERVER 2008 R2 SP2 and SQL SERVER 2012 and firstly i try DBCC...

SSIS DATA FLOW - Hi all, I'm new to sql server,so please help me to know about this. 1) whether the ssis package will...

Outer join not working correctly - In the words of David Gray, please forgive me if i have posted this in the wrong topic and have...

can any one plz tell me what is indexing for example ? - here i am having two tables and now i just search a word [code="plain"] DECLARE @alubm table ( alid int, cattyp varchar(10), ...

SQL 2008 BI vs SQL 2012 BI - Hi all, Just a little question ... I have ( over the past 18 months ) managed to get my DBA and DBD qualifications...

Is it possible to move table to different DB without changing connection strings? - Would it be possible to move a table to a new database, but still reference it on the old name? We...

SQL query to get all predecessors and successors for given node. - Hi All, I have table which holds ID and predecessors ID. One ID can have multiple predecessors. In such scenario I...

Basic Design Question - I'm trying to model the concept of "teams" of "players". Each team has exactly two players. Intuitively, it would seem...

Issue shredding Deadlock Graph XML - I'm working on a script that shreds deadlock graph information. Using the deadlock graph that was posted [url=http://www.sqlservercentral.com/Forums/FindPost1278601.aspx][u]here[/u][/url], I'm running...

how to make a search proc for this tables and their conditions ? - here iam having 5 tables for example i have declare 5 tables the @adforum table is used for add a bussiness...

temp table vs data flow task on physical table - Here is the scenario I have one staging table for csv file which is My source I am loading it into...

db_creator permission not working on SQL Server 2008 r2 - Hi Guys I have created an sql server login called devtest and have given it dbcreator permssions. When I login with the...

Unable to zip the backup data - Hi All, I am unable to zip the backup data. In my server i installed the 7z (zip software). My...

Odd behaviour from computed columns in a left outer join - I have the following query: [code="sql"] SELECT CE.ID , CA.CustomerID, CA.PostalCode FROM DBXN.CommunicationEntry CE LEFT OUTER JOIN ( SELECT A.ID , CA.CustomerID , A.PostalCode, A.EnglishAddressLine1 FROM DBXN.Customer_Address AS...

Avoid characters that viloate UTF-8 Encoding - I'm storing information in an XML file that is UTF-8. I have a stored procedure I use to query some tables...

What are the minimum SQL 2008 permissions for SharePoint 2007? - Sorry, I posted in the wrong forum earlier... My bad. Our shop is new to SharePoint, but we have experience with...

Run/create a query/datasource to Central Management Server query in SSRS - Simply put, you can run a query against all servers in the central management server group using SSMS. Does anyone...

SQL Server 2008 : T-SQL (SS2K8)

Generation of Records - I have got some Monthly Data which I want to pro-rata it on a Daily Basis. E.g. The Current Data looks like: [code="other"]YearMonth Value -------- ------ 201207 5000 201208 4000[/code] I...

Potentially delusional co-worker, inline vs. multi statement TVF - Greetings -- I am having a disagreement with a co-worker about what constitutes a multi-statement Table Valued Function. A sample call...

can this code be in a stored proc? - Hello, I have written a stored proc that creates temp tables and uses a table cursor and then drops the temp...

UNION TWO TABLES WITH ONLY ONE COLUMN IN COMMON - Hi all, I have two tables. One has info about folders and another one about files. Following are the table [code="sql"] DECLARE @Folders TABLE(...

Comparision of a datetime variable against a column of the type nvarchar - Hello All, (Sorry, if I have posted this question already in another topic) I have a table like this: [quote]MinionRpt ( Id [int],...

Fiscal Dates - My company wants to create a Financial Calendar Table which contains only the Fiscal Dates. The requirements is ti populate...

using IN statment & a string variable. - Hi, I need some assistance and i've read multiple answers which are very similar but just not sure how to tie...

ORDER BY PROBLEM - Hi, I have column in a table which needs to be ordered in an ascending order. [code="sql"]DECLARE @NumberTable TABLE( ItemNumber VARCHAR(50)) INSERT @NumberTable SELECT 'ABC-1702-XYZ' ItemNumber...

SQL Server 2008 : SQL Server Newbies

Master DB in Single User mode - Hi, Just noticed my Master DB is in single user mode on a test machine. Tried to switch it back...

Adding process to SQL query - I use the code below to extract a piece of data that is so many along in the delimited string...

Using Access to put data into SQL Server - Hello everyone. So I have a database in SQL Server 2008 that contains 5 tables. I have connected Microsoft Access...

Updating multiple rows for multiple values - Hi, I wasn't sure how to phrase my query so wasn't sure what to search for. I'm attempting the simplest of updates...

How to prevent deadlocks - We have been noticing more deadlocks in our database what kind of steps could we do to identify why? Thanks for...

How to create a policy that will periodically check the membership of sysadmin? - Specifically, I need to know how to set up the condition. In other words, which facet and properties do I...

SQL Server 2008 : Security (SS2K8)

User permission log. - If a user has changed. How I can see the permission it had before the change?

Orphaned Users - Hi All I'm planning on backing up and restoring +- 20 Databases from one SQL Server to another I've been reading about orphaned...

SQL Server 2008 : SQL Server 2008 High Availability

Mirroring Configuration - Hi, Finally sucssfully configured database mirroring with no errors. For High safty with manual failover method... Principal has been synchronized with Mirror...

Mirroring and CDC(change data capture) - I'm using SQLSERVER 2008R2 with HA config using async mirroring. on the principal server, i also activate change data capture...

LSAlert job fails in job activity monitor in both DC & DR (Primary & secondary) servers in logshipping - HI, 1. LSAlert job fails in job activity monitor in both DC & DR (Primary & secondary) servers in logshipping 2. While manualy executing...

database auditing performance issues? - Can anyone explain me that do we get any performance issues when we implement database auditing on whole database. I...

Restoring a filegroup of one database on to a different database on the same server - Can we do a restore of a filegroup on to a different database on the same server?

Removing .NDF files from a SQL Mirror setup - As a test I've migrated data from one file group to another on my principle and been able to successfully...

SQL Server 2008 : SQL Server 2008 Administration

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

Multiple cached plans for the same stored proc - Hi, When querying sys.dm_exec_procedure_stats, I noticed that some procedures appear multiple times in the result set because they have different cached...

SQL Server Agent service did not start due to a logon failure - I have a SQL Server MAchine with 3 instances. I can't get the SQL Server Agent Service to Start. The account was...

Killed 5 Processes Table Locks Yesterday, Still in Killed/Rollback Status - I got a e-mail yesterday after hours from a Lead and he told me that he thought they had locks...

SQLServerCentral.com : Anything that is NOT about SQL!

Quality of Service Document - Good morning all - I've been asked by the project management team to review our config management processes, and part of...

Fantasy football 2012 - Only 2.5 short months to football, can you believe it? I'm not ready, hardly feels like it's summer time yet. You...

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

ssrs null values - hi friends i have small data in sssrs .plz tell mehowto solve this issue how to handle nulls data in ssrs...

Database Design : Hardware

SQL Server 2005 64-bit 100% CPU - Hi people, I have a Windows Cluster with 3 instances of SQL Servers, conected true a Fiber Channel network to a...

Data Warehousing : Integration Services

SSIS Newbie - Hi All I am very new to SSIS and dealing with packages Please help me with something. When a package is created, is...

This file was created in a previous beta version of Excel 2007. Open the file with Excel 2007 to save it to the most recent version of the Excel 2007 file format before opening the file in Access 2007 - Hi, I am using SSIS 2005. Got the source file in XLSX format. As 2007 format not supported in SSIS 2005, so i...

How to use .xlsb file in SSIS 2005 - Hi, I have .xlsb file. It has been used as a source and to insert the data to staging table using...

SQL 2008 SSIS .. Need some help with converting Date values? - Hello, I have a "staged" table that holds many columns, one of them being a date column. The Date column is...

Data Flow Task - OLE DB Source missing columns - In my SSIS package I have several data flow tasks that are identical except in the OLE DB Source they...

Data Warehousing : Analysis Services

Fact/Dim design question for cubes - With Accounts cube we quite a few measures setup such as Active, Residential, CreditScore buckets. Any issues with creating FactAccounts table...

Article Discussions by Author : Discuss Content Posted by Leo Peysakhovich

How to find the status of a job in sql server 2005 (started,running, finished) - Hi guys, I stuck in an issue with sql server jobs. and struggling from 4 days to resolve the issue. I am...