In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Connect Develop seamlessly between Management Studio and Visual Studio
SQL Connect is a Visual Studio add-in that makes it easy to keep your database and Visual Studio project in sync. Find out more.
 
SQL in the City SQL in the City - Free SQL Server training the Red Gate way - heading your way this Fall
SQL Server MVPs, ingeniously simple tools, and an awesome training experience. Register now for an event happening near you.
 
SQl Source Control Connect your existing source control system to SQL Server
SSMS plug-in connects SVN, TFS, Git, Hg and all others to SQL Server. No source control system needed to evaluate. Learn more.

In This Issue

Working with SQL Agent Durations

SQL Agent stores duration in HHMMSS format - not always useful. Discover how to use Powershell, some basic math, and T-SQL to tame these unruly values. More »


Provisioning a New SQL Server Instance – Part Two

So how should you install and configure SQL Server 2012 properly? Glenn Berry completes his two-part series by explaining the steps needed to complete the preparation and do the actual installation. More »


SQL in the City - New York 2012

Come join Grant Fritchey, Steve Jones and others for a free day of training in New York City on Sept 28, 2012. More »


From the SQLServerCentral Blogs - Another Execution Plan… In the Cloud!

A couple of weeks ago I posted about how different execution plans look when using the Azure Management Portal. I... More »


From the SQLServerCentral Blogs - Rant: Applying Common Sense in IT

I was watching a training video last night and the instructor made the statement we hear all the time: "Test this... More »


Editorial - Being Exceptional

The last few years SQLServerCentral and Red Gate have sponsored the Exceptional DBA awards. Each year we've asked for applications from DBAs doing great work for all shapes and sizes of companies. Once again this year we've gotten a number of great nominations from exceptional DBAs and their colleagues. Our judges have decided on five finalists, and now we'd like to get your opinion on who is the best DBA in 2012. 

Vote today.

As DBAs we are often working in the background, unnoticed until something goes wrong. Then we get the kind of attention we're not looking to have. All our hard work, all our good work, is forgotten when things go wrong. That's the nature of the business and all of us should understand that when we start down a career in the technology arena.

The Exceptional DBA Awards aren't perfect, and we might not find the best DBA in the land, but we are looking to find the best DBA that our judges, and you, can from the nominations that we did receive. We'll recognize the best with a trip to the PASS Summit and an awards ceremony on Tuesday, November 6th at the SQLServerCentral party. It's a great networking event and chance to have a good time after SQL in the City - Seattle 2012 and before the rest of the week of SQL Server presentations.

Take a moment and vote today, and recognize one of your peers as the Exceptional DBA of 2012. Then think about how you can improve your work in 2013. I'd like to see you nominated next year.

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

What would be the result of the three given queries? 

-- Query # 1
INSERT INTO @Exceptions VALUES (0),(1),(2),...,(1000)
-- Query # 2
INSERT INTO #Exceptions VALUES (0),(1),(2),...,(8060)
-- Query # 3
INSERT INTO Exceptions VALUES (0),(1),(2),...,(65365)

S : Query ran successfully

E : Error in Query , Maximum Number of Row Value Expressions reached

(Note: Please consider "..." as continuation of the inserts in the same format, with sequential numbers)

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.

Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports

This is the industry’s most comprehensive and useful guide to SQL Server 2008 and 2008 R2. It presents start-to-finish coverage of SQL Server’s core database server and management capabilities, plus complete introductions to Integration, Reporting, and Analysis Services, application development, and much more. Grab your copy today from Amazon!


Yesterday's Question of the Day

How can you check the SPN registration on the server?

Answer: setspn -L domianname\servername

Explanation: By writing setspn -L domianname\servername command in cmd we can check either SPN is registered on the server or not.Where as -R is used for reset HOST ServicePrincipalName and -D is used to delete arbitrary SPN.

Ref: http://technet.microsoft.com/en-us/library/bb735885.aspx

» Discuss this question and answer on the forums

Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports

This is the industry’s most comprehensive and useful guide to SQL Server 2008 and 2008 R2. It presents start-to-finish coverage of SQL Server’s core database server and management capabilities, plus complete introductions to Integration, Reporting, and Analysis Services, application development, and much more. Grab your copy today from Amazon!


Featured Script

Drop a table and views

The script drops a table or view passed in. 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

Is using Maintenance plans a good idea for this? - Good morning, We have 5 SQL Servers used by operations team. QBDB01 - 62 Databases QBDB02 - 41 Databases QBDB03 - 39 Databases QBDB04 - 31 Databases QBDB05 - 28 Databases The...

Database to Database migration tools? - I have a user wanting to transfer data from one database to another within MSSQL 2008 R2. They are wanting...

Data archive techniques - Please share your experiences on archiving older data from tables that have grown very large. We've looked at several approaches including...

How to stop other application accessing SQL port 1433 - We are planning to do some maintanance on SS 2008 and we DO NOT want any users / application accessing the...

Upgrading SQL Cluster from 208 SP2 to SP3 ( ACt/passive ) - Hello , Can someody point me to the document for upgrading to SS 2008 SP2 to SP3 on a Active Passive...

Killed Process still rolling back - So the night shift guy was running a process that had a problem, so he killed it through Activity Monitor....

SQL Server 2005 : Backups

Insane sized tran log backups are filling up the disk. - I have a particular DB on a SS2005 Enterprise. It is scheduled to be backed up in FULL every day...

SQL Server 2005 : CLR Integration and Programming.

CLR DLL Assembly Registeration problem. - I have a DLL that uses Microsoft Excel objects and works fine in my local environment. My local desktop has...

Problem migrating CLR Assembly from 32-bit to 64-bit environment SQL 2005/OLEDB/VFP - Hi, I am currently in the process of migrating our database server (which is Windows Server 2003 32-Bit, SQL2005) over to...

SQL Server 2005 : Data Corruption

Regarding database backup corrupted - Hello Team, I was facing an issue with database backup which I took from sql server 2005. Actually in my office I...

SQL Server 2005 : Working with Oracle

Excel Pivot Table Show Zeros For Numeric Oracle Data Via SQL Linked Server - I have a view in Oracle 11G. I want to show the contents in an Excel 2010 Pivot Table without...

Refresh from Oracle - Hi friends, We need to refresh the data from Oracle database to SQL Server 2005 every hour or so.. We...

SQL Server 2005 : SQL Server 2005 General Discussion

Insufficient permissions to access Reporting in Management Studio & web reports - Hi all, I've just added SQL Reporting Services to our SQL 2005 server, but cannot access the web interface (http://localhost/Reports) or...

Database Archival Procedure in SQL 2005 - Hi, I there any inbuilt Best Archiving procedure is there in SQL Server 2005 for Data archive? Any Step by...

Single sql column defined as 2 separate indexes-unique and non unique? - HI, I have a database that was set up by a vendor application. I have discovered at least 10 occurrences...

query between 2 servers - Hi everyone! I'm self-taught in SQL, so my knowledge is fragmented, but I'm not a complete novice. I'm migrating an Access db...

SQL Server Tools - Recomendations On Stress Testing and I/O Testing On New SQL Server - Need to test/stress a new SQL Server box to see what she can do. I went to get a copy...

SQL Server 2005 : SQL Server 2005 Strategies

How do you use parameters in SSIS lookup functionality - Hi everybody...I"m new to SSIS, a tool my company is making us use primarily as a DTS tool. What I'm...

SQL Server 2005 : SS2K5 Replication

The merge process was unable to deliver the snapshot to the Subscriber. - Hi All, I setup a merge replication between SQL2005. I have two subscribers. First subscriber is working perfectly, but the second is...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Any info on Deadlock detection algorithms? - Hi Gurus, I'm trying to find a starting point from which to troubleshoot an issue we are facing where the same...

SQL Server 2005 : SQL Server 2005 Integration Services

Number/Letter column issue - I have a column in a Excel Spreadsheet with a code in it that is either a single digit number...

Delete files from different folders, approach plz - Hi I have a requirement that I need to implement delete logic before loading into it. I have around 10...

Duplicate error - I am trying to insert values from source to destination and i ma getting the following error.What should be the...

Data split while loading into CSV - Hi, I am loading data from sql server to a flatfile (.csv format). One of the column will have values like =HsGetValue("GPDetail","Scenario#Actual;Year#2012;Period#P07;View#YTD;Entity#G1G2_0609000;Value#<Entity...

Want my SSISpackage/ SQL Job should fail if the SSIS Config file path is wrong - I have one SSIS package. In the package I configured the config file, and it is enabled. I am calling...

How to pass value to Data Flow task's variable - Hi, How to pass value to variable Of OLEDB Source of data Flow Task. I am using SQL Command As Data Access...

I have an Excel that has to be imported to SQL Server using SSIS. The file has merged cells which don't seem to import properly. Instead they show up as NULL. Does anyone know how to import an Excel file with merged cells? Thanks. - .

Parse XML - Hi Everyone. I am facing problem while parsing the below XML. [code="xml"] <site> <status>0</status> <bankAccount uniqueId="23232323" acctType="checking"> <accountName>TESTDATA</accountName> <accountNumber>503-1123xxx</accountNumber> <accountHolder>accountHolder</accountHolder> <balance balType="availableBalance"

Adding files of a SSIS package to another SSIS package - As i told in description i have two SSIS packages P1 and P2. P1 has one DTSX and P2 has...

SSIS using SQl cmd???? - Hi, For creating a SSIS package I am using “SQL Server Business Intelligence Development Studio” that provides me a facility to...

SQL Server 2005 : T-SQL (SS2K5)

how to find gap ranges in range based entry. - Hi ! i have a table "bridgeP" with cols bridgePid int, fromlen decimal(10,2) , tolen decimal(10,2) bridgePid is autogenerated, and fromlen and tolen has...

Update table based on column grouping - Hi, I have a following table (table1) which needs several updates. All the updates are grouped by the 'phone' column: [b]Id, phone,...

GREATEST and LEAST function - Hi, I'm looking for a function in T-SQL for getting the biggest or the smallest value between two or more columns...

SQL Server 7,2000 : General

greatest(a,b,...) function - MySQL, oracle, and postgres all seem to support a function called GREATEST which is passed several parameters and returns the...

SQL Server 7,2000 : In The Enterprise

2005 Cluster unable to add new database - I have a 2 node windows 2003 cluster with SQL Server 2005 running. The first node is the owner of...

Log shipping a distribution server - Hi, I'm trying to get log shipping to work with a distribution server used in transactional replication. Shipping the distribution...

SQL Server 7,2000 : SQL Server Newbies

How to run.bat file via SSIS package - I like to run the .bat file every 1/2 hr via SSIS package. Can you please help me how to...

SQL Server 7,2000 : Replication

subscriber''s records disappear with merge replication - Hello everyone, I am struggling with getting a merge replication working.  On the publisher, I have an empty table.  The subscriber...

SQL Server 7,2000 : SQL Server Agent

Ideas welcome on SSIS jobs, Agent and kicking off another SSIS job - Hi. I'm looking for some ideas. Currently I have 3 SSIS packages which are scheduled in SQL Server Agent. The run...

SQL Server 2008 : SQL Server 2008 - General

How to acquire a lock on table for specified time - Hi, How to place a lock on database table for specified time and release the lock after specified time is complete? Regards, Narasimhulu

Need Create View Script for all tables - I manage a SQL 2008 R2 Production environment for an application. We have a new requirement to create views( simple--select...

Unix Epoch to Sql Datetime Query - Hi Guys, I am getting one file where the datetime is in seconds (bigint) , its in Unix Epoch format , how can...

Time clarification while tuning - Hi All, I have a small doubt while tracing the the T-SQL DURATION. In T-SQL DERATION a column name is...

Update Statistics - I don't have a clear picture on how update statistics works internally but let's say i perform an update statistics...

SSIS create text file and add text - Hello everyone... I have an SSIS package that copies a template file before it extracts data from the database. Somehow my...

How long did each SSIS task took? - Can someone help me how to find elapsed time for each task in SSIS package Thanks!

high reads - i have a proc who on occasion it's execution plan goes to hell and it uses 11 million reads to...

FTP Task fails "unable to connect", FTP Connection Manager tests is SUCCESS - I have 2005 FTP task converted to 2008. The FTP task fails with [FTP Task] Error: Unable to connect to FTP...

anyway to avoid precompilation of stored proc - Hi, I have noticed that a stored proc takes up to 40 seconds when first run, and then less than 1...

Exception handling when no results are returned - I have a tentative solution below, it's just really inefficient. So I thought I would check if anyone had suggestions...

BulkImport Problem - I have Unicode Data file which is Fixed Length Following Table Structure with Create Statement. sample Data file and format file i...

Suspect Mode in Sql Server 2008 - What is the cause of database in suspect mode and how to overcome from it?

SQL Procedure Performance in different networks - Hi, In my project there are two servers local and central. My central server is in Network1 and local server are...

SSIS loop through file names defined in database table and apply File System task to each - Hello, My objective using SSIS is to loop through a SQL Server 2008 database table I created called Files in a...

CONVERT DATA COLUMN TO ROW WTIH COMMA - HI ALL, HOW TO COVERT COLUMN DATA IN TO ROW WITH COMMA SEPARATOR DATA LIKE THAT :- TABLE NAME : - TEST COLUMN NAME : - COL1 ORIGINAL...

Strange Management Studio 2008 R2 (10.50.1600 and 10.50.1617) behaviour - ********************************** Would be nice to know if this was already addressed or if MSFT was planning to address it any time...

use SYSDATETIMEOFFSET in a default value of a stored procedure - Hi friends, Is there a good way to use SYSDATETIMEOFFSET in a default value of a stored procedure ? Here is my...

drop and recreate foreign keys - I have to truncate some of the tables in a database. But because these tables have foreign key constraints, I cannot...

MS SQL + MS Visual Studio VS Wherescape RED - Have you ever heard of or used Wherescape RED to do the data warehouse developing? The fact is it is...

SQL Server 2008 : T-SQL (SS2K8)

Extract string between delimiters - HI I want to extract string between the delimters where data is as follows: abc_def_cet_qwe output required in only def , how...

Optimizing Stored Procedures that utilize only local variables (and lots of them) - Our proprietary reporting module calls stored procedures that have no parameters but make extensive use of local variables. A single...

Urgent request please - if i have records as follows: ID Status 1 0 2 1 3 0 4 1 5 1 6 1 7 1 8 1 then I need to find from...

passing more than one value to a SP - I need to execute this stored procedure. Problem is that I need to pass in more than one value. At...

Will indexed view help this query? - Hi I have a fact table with around 100 million rows. Below is my query to pull top 10 records. ( i...

Improving query performance to detect first duplicate - I need to query some tables looking for All Sales that are active at the same time and have at...

UPDATE when the values are the same - Does an UPDATE query overwrite existing values if the already existing value is the same as the one your updating...

SQL Server 2008 : Working with Oracle

Excel Pivot Table shows zeros from a SQL View hitting an Oracle View via a Linked Server - I have a view in Oracle 11G. I want to show the contents in an Excel 2010 Pivot Table without...

SQL Server 2008 : SQL Server Newbies

Please give query for below output - Hi All, My output looks like this 100 200 300 400 500 But I want to convert above output to '100,200,300,400,500,' For this how to write the query??? Regards, VenkiDesai.

Convert column to row - Hello everyone, I am a newbie to sql and I am faced with the problem below. I have 3 tables [b]Categories[/b] Cat_ID | Cat_Name 1 | Cosmetics 2...

The Best SQL Server Tutorials - I want to curate some of the best SQL Server online tutorials as part of my skill inventory. I'm particularly...

Landing The First SQL Job.... - Hello All! I currently am a plumber by trade, been in that industry courtesy of my father in law since graduating...

SQL Server 2008 : Security (SS2K8)

Login failed when creating a linked server - Hi, I'm trying to create a linked server from a remote sql server to another remote sql server on the network....

Giving users the right to execute a query w/o access to table - our general policy is to not provide direct data access to any user or group in the company for our...

SQL Server 2008 : SQL Server 2008 Administration

Changing default language - Hi Friends, As we know that we can change the default language of any SQL Server Instance if needed. My question...

Isolation Level - Ok so here is the situation. I have a database that has a crappy front end application that connections in...

Capture who created the Database - Hello, I have requirement where I need to know who had created a database. I know that I need to use...

Moving tempDB from RAID10 to RAID5 for OLTP? - My data files and tempDB file (single temp file) share the same RAID10 LUN and our SAN controller is saturated/overloaded....

Error during backup (Operating system error 1003) with Tivoli Data Protection - Hi, Our SQL Server instance have 5 databases. The backup use Tivoli Data Protection to store backup directly to tape. The backup...

Db Snapshots question - Hi, I have a question on database snapshots. I have created a table and inserted 2 rows. create table tab7...

Career : Certification

Number of MCSE Data Platform certified people in the world ? - I am curious to know the number of MCSE Data Platform certified people in the world as of today. There...

Programming : General

Need a "Test" or Dummy XP Dll - I am working with a customer of mine to implement Audit alerts (from a C2 trace file) when/if someone adds...

Programming : Powershell

Get-WMIObject Win32_Volume fails on proxy account from Agent Job - It may just be a case of me missing something, but I get unexpected behaviour when running a job as...

SQLServerCentral.com : Anything that is NOT about SQL!

SSD storage performance increase - So, I've updated all my storage for my ESXi server to SSD storage. I fitted it saturday afternoon and moved...

tablet ipad or nexus 7 or kindle fire ? - Hi, I think the ipad has that cool fad factor, but still overpriced. I am interested in buying one but...

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

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

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

dynamic reports at the time of prview reports in ssrs - hi friends i have small doubt in ssrs plz tell me answer i developed one report in ssrs. in that report...

SSRS to multiple PDF based on input parameters - hello, i need to generate multiple pdf reports based on input parameters using ssrs. suppose there are 1000 branches then it...

Database Design : Design Ideas and Questions

database table design - hi all i am getting software asset from different client machine nearly 2000 client per day,i am getting data in...

Data Warehousing : Integration Services

import UNIX to sql - Hi guys, I am getting pipe delimeted files from SAP and importing them into SQL using SSIS. I have found...

Configuration File ignored in Execute Package Utility - I do have the following problem. I created an Upadte-Package in SSIS with 2 configuration files. Works fine on my local...

The column "Column 2" cannot be processed because more than one code page (65001 and 1252) are specified for it. - I get this error if i try to import a tab delimited flat file to a table in sql server. [i]The...

Data Warehousing : Strategies and Ideas

Where to put a Type 2 SCD? - This is actually a follow up to an earlier question about a Type 2 SCD. Anyway, we have decided on the...

Staging DB -> Star Schema DB -> SSAS Cube? - I have to modify the design of a data mart I "inherited." Currently the big picture is this: ETL Staging DB...