In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
Deployment Manager NEW! Deployment Manager Early Access Release
Deploy SQL Server changes and .NET applications fast, frequently, and without fuss, using Deployment Manager, the new tool from Red Gate. Try the Early Access Release to get a 20% discount on Version 1. Download the Early Access Release.
 
SQL Monitor Monitor your business, not just your servers
SQL Monitor gives you extra monitoring flexibility with custom metrics - monitor what's most important for your environment. Find out more here.
 
SQL Connect Keep your database and application development in sync
SQL Connect is a Visual Studio add-in that brings your databases into your solution. It then makes it easy to keep your database in sync, and commit to your existing source control system. Find out more.

In This Issue

What is SSIS? Level 1 of the Stairway to Integration Services

Learn about the very basics of what Integration Services is and how it's used in SQL Server. More »


Designing & Maintaining SQL Server Transactional Replication Environments

Microsoft IT protects against unplanned Transactional Replication outages and issues by using best practices and proactive monitoring. This results in increased stability, simplified management and improved performance of transactional replication environments. More »


From the SQLServerCentral Blogs - Reminder to Register for the Monday Night Networking Party

We just crossed the 100 mark for registrants for our Monday Night Networking Party and still room for more. If... More »


Editorial - Innovation

Computers are so cool. I remember thinking that as a kid, thinking about how I could tell a computer to do something for me with a little programming. Some of my programs weren't that impressive, such as the ones that helped me perform chemistry lab calculations on an Apple II, but they were helpful. I remember having so many ideas about what I could make a computer do that I couldn't even find time to work on a fraction of them.

I think many of us have a similar excitement by computers, but the seemingly limitless number of things they can do. We love to be creative, and innovative, in how we get computers and applications to work together. Even administrators have some very creative ways they are using technology to manage or work with groups of computers.

This Friday, as you have your morning coffee (or tea, as Brian Kelley prefers), I'm wondering if that creativity is important in your company. When you think about the way management views Information Technology, the jobs of you and your peers, how would you answer this?

Does innovation matter much at your organization?

There's a good management article that talks about the ways in which innovation and creativity can be fostered. Some of this is important in all companies, but not in all jobs. Sometimes management just wants a job to do a job well. Is that the case for you?  While I'd like to see more managers fostering innovation at all positions, do you think it matters for your job?

» 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. You can also follow Steve Jones on Twitter:

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. They have a great version of Message in a Bottle if you want to check it out.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

» To submit an article, rant or editorial, log in to the Contribution Center


Question of the Day

Today's Question:

Look at this T-SQL.

begin try

Declare @ctr int
Declare @a float
Declare @b int

select @ctr = 1
select @a = 10
select @b = .001

while @ctr <= 10
 begin
  if @ctr = 4
   select @a/@b
  select @ctr = @ctr+1
 end
end try
begin catch
 select @ctr
end catch

select @ctr 'Counter'
What is the final value of the Counter?

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

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

 Inside the SQL Server Query Optimizer

This book will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way. Grab your copy today from Amazon!


Yesterday's Question of the Day

When a database transaction is rolled back successfully, the data returns to its previous consistent state. Does the seed value of an identity column also get reset to the previous value?

Answer: No

Explanation: Rollback Transaction command returns the data to previous consistent stage but seed values for identity columns are not reset.

Ref: @@Identity - http://msdn.microsoft.com/en-us/library/ms187342%28v=SQL.100%29.aspx (2nd paragraph).

» Discuss this question and answer on the forums

 Inside the SQL Server Query Optimizer

This book will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way. Grab your copy today from Amazon!


Featured Script

Find Foreign Keys

Lists the foreign keys between tables in the datavase, 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 Installation Issues - We have faced a installation problem in our UAT environment. 1. We have 5 named instance in the UAT machine and...

Missing Indexes - Hi everyone, I ran the missing indexes DMV and I'm trying to figure out what indexes need to be applied based...

SQL Server Agent job running query only sends half of the results to file - I have an SQL Server Agent job running every day at 9am to run a query and using the Advanced...

Deleting the tables which are not been used from long time. - HI Experts, Actually we are planning to replicate the prod data to our DR center. So, thinking to replicate only that...

Production to Staging and Vice Versa - Hi Experts, Please let me know what all need to check before and after restoring a database from production to stage...

SSRS 2005 report manager link not coming up - On my SQL Server 2005 installation. I am able to view the report server URL but I am unable to...

SQL Server 2005 : Backups

backup background process - when backup is taken can anyone tell me as to what exactly happens in the background

Excessive log growth in SIMPLE recovery model database - SQL 2012 - Hi, Apologies if this has been posted before, but I keeping coming across excessive log growth on my reporting databases that...

DDL trigger for RESTORE DATABSE - Hi, I have to create a user in each user database after a database has been created or restored in SQL...

SQL Server 2005 : Business Intelligence

trapping inconsistently linked queries - hello, is there any way that we can trap any query which might have been incorrectly linked? eg if have 2 tables...

SSIS: How to prevent rounding off - Hi all, I have created an SSIS package in which there is a task which we transfer data from oledb...

SSRS 2008 data refresh issue - Hi, I'm facing a problem in SSRS 2008, I'm getting data from a stored procedure. Now I alter the sp...

Migrate Crystal Reports to Reporting Service - As for Crystal Reports conversion to Reporting Services solution, has anyone tried Crystal Migration Services "http://www.crystalmigrater.com/Default.aspx"?

SQL Server 2005 : Development

trapping inconsistently linked queries(I posted this request in BI section which might not be the correct one for this, so am posting it again here) - (I posted this request in BI section which might not be the correct one for this, so am posting it...

SQL Server 2005 : SQL Server 2005 General Discussion

The Transaction log for the database 'tempdb' is full, please help! - Hi, I just started using SQL server management Studio, i have a database and a table with 498591 records. I have a...

SQL Server 2005 : SQL Server 2005 Security

Using Temporary Tables across different tasks in SSIS - Hi, We need to create a temporary table in Execute SQL Task and access it in subsequent Data Flow Task. I...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Indexing question - A basic question, i was analyzing an execution plan and found a keylookup using most of the CPU...So i tried...

Execution plan - Hi All, I have 2 sql sevr 2005 instances with same build no,same edition. I restored the database from one...

SQL Server 2005 : SQL Server 2005 Integration Services

Load data from multiple tabs in excel to different tables - Hi, I have an Excel File and has 3 tabs. Need to load the 3 tabs data to 3 different tables using...

Error : Opening a rowset for "Sheet$" failed. Check that the object exists in the database. - Hi, i am trying to load the data from excel sheet using For each loop container in ssis 2005. But it gave...

Need to call a batch file from a remote server - Hey guys, I'm creating a SSIS package and one of the first steps is to stop services on a remote server...

To insert varchar records in datetime field - Hi, I have table "Trial1" One of the columns is DATERECEIVED DATETIME NULL Loading data from Excel to staging table using ssis...

How to Extract Multiple tables using SSIS - Hi, I want to Extract(Export) 4 tables from SQL SERVER 2008 R2 to a Flat File. All the four tables are related...

SSIS Package Not Importing Last Row in Flat File - Hi all. I have an SSIS package that imports a bunch of .csv flat files. I am using a Flat...

"Delete files older than X days" error - Hi, I am using the script task to delete the old files(older than 10 days) present in a particular folder. (D:\FIles) [i]Code...

SQL Server 2005 : T-SQL (SS2K5)

Extract string before character occurrence. - Hi, This is bugging me, the solution must really be easy. I have a column in a table with a variable length...

Find sub-tree nodes - Hi, I have a tree structure represented by a simple table that references itself: [code] CREATE TABLE [dbo].[AframeGroup] ( [GroupID] [int] IDENTITY (1, 1)...

SQL Server 2005 : SQL Server Newbies

How to monitor a Production Database in SQL Server 2005 - Hi, Is there any inbuilt procedure exist to monitor a [b]database in SQL Server 2005[/b]? or How to monitor a...

SQL Server 7,2000 : Administration

Error while altering a table - maximum row size exceeds - Hi, I am trying to delete one column from my table using ALTER statement and the following is the SQL...

SQL Server 7,2000 : T-SQL

The object 'DF.....' is dependent on column 'COL1' . But How? - Hi, I was playing with alter table. When i come across this suituation. I tried to figure it out but :-( . Well...

SQL Server 2008 : SQL Server 2008 - General

XML Tag reading - I am reading below XML through T-SQL query, please see below and suggest me the solution DECLARE @xmlAttributeRelation XML SET @xmlAttributeRelation='<Entity> <Entity>1</Entity> ...

Multiple updates for save value - [font="Verdana"] Hi All, While working on one of the stored procedure, I observed one situation. Inside the stored procedure there is one...

check which SQL Server Service Start Type is set by query? - Hi all, we had a "funny" disaster in the morning, because the VM was restarted and the SQL Server Service wasn't,...

Appropriate Data Type - I'm looking for some second opinions on the choice of data types for some financial reporting. Two transactional sources are being...

FULL backup db error - Hi we are using Litespeed for backups. We got below error for full user db backup maintenance job. is this...

SQL Server Linked Server Distributed Query with Parameter vs Literal - Hi champs, Stuck with a baffling linked server problem. would greatly appreciate an explanation for the below behaviour. I have a SQL...

OPTION (RECOMPILE, QUERYTRACEON 8649) - What OPTION (RECOMPILE, QUERYTRACEON 8649) will do when i add it to query? eg.. SELECT COUNT(T.CompanyID), COUNT(UserDataID) FROM TargetsBeforeCurrentCriterion AS T LEFT...

Lined server connection issue - We have a SQL job that run against all servers and find the 'server total and free drive space'.  There...

How to Graph SQL Server Data from a PowerShell Script - Has anyone found a way to graph SQL Server data from a PowerShell script? This link describes something close to what...

SELECT TOP(100 PERCENT) in Derived Table - Hi All I'm trying to optimize a monstrous query in a report that a vendor supplied. Part of the query has...

Query Help - Hello Everyone I Have one task, in that there are two tables, one has student testing dates and other has student enrollment dates. [code="sql"] CREATE...

Will shrinking the transaction log reduce the size of a full backup? - Will shrinking the transaction log reduce the size of a full backup?

Antother Delete Duplicates Thread - I know this topic has been covered many times, but I cannot find the specific solution I am looking for. We...

Help on Performance issue on Recursive CTE - Expert, whatelse could i do to make this query perform better. this thing runs for about 20seconds on the table...

Send email to different recipients - Well, I need through of a Query send Email to different recipients. in the link below, show the Query http://img200.imagevenue.com/img.php?image=889928666_Query1_122_432lo.JPG in the image...

move transaction log to a different drive. - Hi SQL Guru's, When we migrated our mission critical application to a different server, one DBA placed the transaction log in...

Recursive function - sql2008 - Hi, I am new to sql server 2008 functions, I am facing an issue and request, if some can help. I have...

Compare two Databases - Hi, i have to solve the following problem. We used DoubleTake for the event of a disaster in SQL Server 2008...

UNIQUEIDENTIFIER vs BIGINT - Hi, I read somewhere that when ever there's a join between two tables, for example orderlines and products being joined by...

Optimize query needed - Hello Expert. I need to convert data from an existing column from BigInt to VarBinary type. I wrote the function to...

Statistics - Never calculated on index - In taking a deeper look at the statistics on our 2008 instance, I have found several index and column statistics...

How to Encrypt Database Objects in SQL Server 2008R2 - I am using SQL Server 2008R2, I want to encrypt all database objects before sending these to Production. I want this...

Failed to restore a SQL2000 backup to a SQL2008R2 instance - Hello gents The SQL2000 backup is fresh without corruption but when I tried to restore it to a SQL2008R2 instance, the...

Parameter issue - When am assigning a parameter of a procedure to a variable and use tha variable throguh out the procedure, am...

SQL Server 2008 : T-SQL (SS2K8)

Using BCP to generate INSERT scripts - Hello, For some reason I need to generate "Records Insert" for each table of one database. I already have a Stored...

Update a Record Using a Trigger with SELECT Statement - I'm trying to use a trigger to update the record that has been created with addtional details, however the statement...

Nested Triggers - Hi, Is there any easy way of determining nested triggers?! For example, if I update a table that has an update trigger...

creating procudre for dynamic table - Hi friends, create tab T1(name varchar(22),age numeric(22)) create tab T2(name varchar(22),age numeric(22)) insert into t1 values('ram',22) insert into t1 values('am',26) insert into t1...

SQL Server 2008 : Working with Oracle

DTS_E_PRIMEOUTPUTFAILED when connecting to Oracle - I thought I'd log this topic here after concluding an exhaustive investigation of this issue I've been having and thought...

SQL Server 2008 : SQL Server Newbies

would need some help - i would need to change the service code in the service table to be 2characters followed by 2 digits. For...

Initial file size for the .ldf - Hi Can I change the initial file size of the log file while the database is online. The last DBA mistakenly...

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

SQL Server 2008 : SQL Server 2008 High Availability

database does not start mirroring session? Error 1418 - Hi, 1. principal server - port number 5022 opend & confirm primary server itself with thru telnet command. c:> telnet primary server IP 5022 2....

Cannot connect with "SQL Server" driver but I can with "SQL Server Native Client 10.0" - Hello, hope someone can sort this out.. I have this strange issue in my setup. In my connection string I cannot connect...

Mirroring doubt - Hi All, Some one asked me he configured mirroring both servers are in different location and mirroring is working fine.. 1....

Cluster question... - Hi All, I have a small question? Scenario :- SQL Server 2008 on cluster environment and i want to restart the...

Replication Script Out. - Need Help! We have whole lot of replication. Can we have a automatic process to generate script for all Publication on...

adding witnes server - Hi Team, WE are adding new witness server sql server 2008 R2 SP1 Standard Edition. Prinicipal & Mirror servers Sql server 2008 Standards...

SQL Agent Access in Sql Server 2008 - I want to give access of a [u]SQL Agent[/u] to a user.. how do i do this:w00t:

DB Mirroring High Safety Mode wihtout witness - I have a general question regarding DB Mirroring, which is currently running on High safety Mode without witness. If server_A fails,...

Changing Locations of DataFile - Hi All, As i see some of the servers are having databases online... Now this Database have data file & log file on...

SQL Server 2008 : SQL Server 2008 Administration

Log shipping failing all of a sudden - My log shipping was working for the past two years and now it has started failing with "cannot find a...

SQL Server Agent job running query only sends half of the results to file - I have an SQL Server Agent job running every day at 9am to run a query and using the Advanced...

How to Restore model database from backup ? - Hi, I want to restore the model database from the latest backup , I am trying to create a scenario by deleting\moving...

Career : Certification

Microsoft Certified Master - There's been a lot of debate recently about whether certifications are worth doing and whether they provide you or your...

Programming : Connecting

Which OLEDB drivers are installed? - does anyone have any (non-developer) advice regarding how to determine which OLEDB drivers are installed on any given Windows machine....

Programming : Powershell

get-unique - I [strike]want[/strike] need to understand why the code below returns alpha ----- Y Can someone explain why I don't see alpha ----- X Y Bonus points:...

Get table's unique indexes - Is there a way to list the unique table constraints on a given table using smo/powershell? Or do I have to...

SQLServerCentral.com : Anything that is NOT about SQL!

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

SQLServerCentral.com : Suggestions

Do not require registration to just read articles - I would humbly suggest it would be beneficial to not require registration for merely reading articles. I know that registration...

Reporting Services : Reporting Services

'Negative sizes are not allowed' !? - Hi, While clicking in a textbox, wich is suppose to redirect me to another report, I get this error message: 'The value...

Decomposition Tree in SSRS !? - Hi geniuses! Is there a way to create some sort of 'Decomposition Tree' in SSRS? I want to be able to click...

Performance issue with Multi value parameter in SSRS - Hi, I have a multi value parameter in SSRS report which has a list of values around 600..When the user selects...

AutoRefresh fails after midnight on reports with date parameter - Hi We use some reports in our NOC which have date parameters with defaults set to the current day. These reports...

Unnamed Parameters - Hi, I have a problem where I want to use unnamed parameters in a DB2 odbc connection. I have the same...

Reporting Services : Reporting Services 2005 Development

Faulting application devenv.exe, version 8.0.50727.762 - Hi, Using BIDS 2005 and found the error that "Faulting application devenv.exe, version 8.0.50727.762, stamp 45716759, faulting module unknown, version 0.0.0.0,...

Data Warehousing : Integration Services

Handling datatype changes after deployment SSIS - Hi All, I have created a ssis project and deployed it to the server. And after the deployment has been done,...

SSIS- Need File Name which i am loading in Variable - I Need File Name which i am loading via Excel. Any Idea

Dynamic file name for FTP task. - Hey I am also working on same task like this In SSIS ftp task... Here I created a variable for getting...

Data Warehousing : Analysis Services

Count Comparison Month over Month - doesn't work when filtered - My first dive into MDX. I have a calculation which works when 2 entire months are compared. When I filter...

Notification Services : Administration

Query Analysis - Dear All, Need some Suggestion. I have two tables one is Master and other one is slave. MY master table has some set...