In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQl Developer Bundle 12 essential tools for database professionals
The SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.
 
SQL Monitor Uncover the flexibility of custom metrics
Keep up to date from anywhere with SQL Monitor, and monitor the most important data for your servers and applications. Download a free trial.
 
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.

In This Issue

Shrinking Transaction Log File "the right way"

Wrote this post in response of what I see as common misconception in the approach for truncating/shrinking transaction log file More »


SQL in the City - Seattle 2012

Start the week in Seattle off with a free day of training on Nov 5, 2012 with SQL in the City. Grant Fritchey, Steve Jones and more will be talking SQL Server in the Pacific Northwest. Join us and debate and discuss SQL Server the Red Gate Way. More »


SQL Southwest, Thursday 18th Oct - User Group Meetup and Virtual Meetup

October's meeting on Thursday 18th will be a virtual meeting which means anyone in the world can attend if they have access to a PC with an internet connection. We are pleased to announce that Grant Fritchey will be giving us 2 sessions.  More »


From the SQLServerCentral Blogs - Generate Uniqueidentifier with SSIS

If you are trying to generate Uniqueidentifier/Newid() in SSIS data flow, you will soon realize that there is no out-of-the-box... More »


Editorial - I'm Not a Rock Star

I used to play in a band in high school. I stood in front of crowds in bars and restaurants, dreaming of being a Rock Star. It never happened, and I'm not sure any of us had that much musical talent, but we had a lot of fun. As with most dreams, it was revived a few times in college, but eventually I let go of it, moving on to more practical, and less risky, career choices. However there are still times I've thought, "What if I'd tried harder…."

I've had a great career and wonderful life and have no regrets. I'm lucky enough to work for a great company, and I get to spend my days at home, doing something I really enjoy. I get to travel as well, doing talks and meeting lots of data professionals all over. Both of those bring a smile to my face. Over the last couple weeks I participated in the SQL in the City tour, and while it was a tough schedule to follow, I really enjoyed the chance to meet so many DBAs and developers all around the country. We've still got one more event, on Monday, Nov 5, in Seattle, the week of the PASS Summit. Come by if you'll be in town. It's free, just register.

Our tour covered five cities across eleven days, fortunately circling around Denver so I was able to go home a few times for a day or two. It's much less grueling a schedule than many athletes or musicians follow, but it did teach me one thing: I wouldn't have done well as a rock start. Delivering the same talks every two or three days was harder than I expected, and I didn't love that part of the events. I would have preferred a little more variety of presentations, and it's something I want to consider for future tour events.

One very important thing I've learned across the last decade or so is that I need to understand who I am and what I like to do. I realized that often in my career I was willing to bend and flex to meet the demands of various employers. However when the bending and flexing was too far from my comfort zone, or occurred too regularly, I haven't enjoyed the jobs.

It's important to know what you like, what you don't, and what you are good at accomplishing and then finding the job that fits you. I might do another tour, but I won't do them constantly. It's not something I like or want to become a large part of my 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. 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:

What list determines the words that are ignored in full text searches?

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

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

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.


Yesterday's Question of the Day

I Set the options to support indexed views.

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL,
    ARITHABORT,QUOTED_IDENTIFIER, ANSI_NULLS ON;

I then execute the following T-SQL statement

CREATE VIEW dbo.c_view
 WITH SCHEMABINDING 
AS 
 SELECT [CustomerID]
      , [FirstName]
      , [LastName]
      , [Priority]
      , [CreateDate]
  FROM [dbo].[Customer]

The view is created. I then execute the following T-SQL statement

CREATE STATISTICS CV_Stats ON Dbo.C_view (LastName)

The question is: are the Statistics created ?

Answer: No

Explanation: The answer is no. A view must have a clustered index before statistics can be created on it.

Ref: http://msdn.microsoft.com/en-us/library/ms191432.aspx#Restrictions
http://msdn.microsoft.com/en-us/library/ms188038.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.


Featured Script

Create A Folder With T-SQL

Pass a folder name to this SP and it will create the folder for you. 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

Unable to deallocate kept page - DBCC CHECKDB - Hello all, Received a call this morning that the database maintenance plan on a server is failing on it's Check Database...

Small database with 1 million users - Hi all, We've a small database (1 GB) and SQL 2005 server is clustered one. Now Business is forecasting that database...

Installing Fulltextsearch in existing named instance of sqlserver 2005 - Hi, In our server sqlserver named insance is installed Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright...

Log shipping hybrid - Within a few months we hope to be using Netapp's Snap Manager for Sql Server to "replicate" changes in our...

How full is my datafile? - Hi folks! When I create my database I define sizes for the datafiles that make it up, by default the MDF...

data folder permission - Hi, By default installation, SQLServer2005MSSQLuser$<INSTANCENAME>$MSSQLSERVER is having the below access to SQL Server Data folder. Full Control Modify Read and Execute List Folder Contents Read Write Auditor...

SQL Server Agent and sa Password Change - SQL 2000, 2005, 2008 - Many instances - I am changing the sa password. Some posts seem to say that there isn't any...

SQL Server 2005 : Backups

Backup process dilemma - Good afternoon, I have 5 servers (1 SQL Server instance per server ALL SQL Server 2005). See below for totals and time...

Any Alternate to back up and Restore - I have Db almost 700GB which we daily restore on Production after making back up on build server. As the...

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

Back SQL Agent Jobs - Hi All I need to back up all the Agent Jobs i have on my servers, is there some ways...

SQL Server 2005 : Business Intelligence

Dev environment to support 2012 , Poerview, Power Pivot, tabular SSAS - I have been a bit slow to get to grips with some of the new stuff such as power pivot,...

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

SQL Server 2005 : Development

XML in SQL Server - I have a table like below. value xml_1 1+2 <root><s>1</s><s>2</s></root> 3+4 <root><s>3</s><s>4</s></root> I want to get the value like, 1+2 1 1+2 2 3+4 3 3+4 4 Can you guys please help...

Deplying CLR Stored procedure - HI, How can i deploy the CLR Stored procedure to production i.e., different server. thanks,

SQL Server 2005 : SQL Server 2005 General Discussion

confusion in setting up superkey - Dear Sir, I am in great trouble, my problem is I have a vendor Table which consist of some fields. I explaining...

FULL Recovery mode changed to BULK LOGGED does that break the t-log chain? - Should be a quick question I hope. I've forgotten the answer. If you change a 2005 database from FULL to...

SQL Server 2005 : SQL Server 2005 Security

How Can A File be Totaly Removed from an SQL Server Database - Hi Folks My administrators have asked how does sql server handle the "Total" removal of a file from a SharePoint SQL...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Explicit Transaction to improve performance? - [font="Verdana"]Hi All, I have one Stored Procedure which frequently gets executed. Though it is a straight forward Stored Procedure with one...

SQL Server 2005 : SQL Server 2005 Integration Services

How to change the schema prefix for a table when using Transfer SQL Server Objects Task - I am wanting to use Transfer SQL Server Objects Task to copy tables and their data from one database to...

The package failed to load due to error 0xC0010014 "One or more error occurred. - Dear Experts, Could please help me in resolving the below error.... Trying to execute a .dtsx package, but facing the below...

SQL Server 2005 : T-SQL (SS2K5)

first official day of every month for past 3 years - i have a table and a column with dates. there are multiple dates for same date . these dates are inserted...

multi table match query - I have 5 tables 1) cropid( this is the id for each time my procedure runs) create table croprerults( cropid...

Select column name and values. - I need query for Select column name from information schema and display values for selected column names. Using below query i...

Time and Attendance need help pls... - Please can someone help me.... I have a query which query the min and max of time in and out...the scenario...

SQL Server 2008 : SQL Server 2008 - General

REPLICATION - hOW TO DELETE THE ROWS FROM ATABLE WHICH IS CONFIGURED IN REPLEICATION ?

How to see the complete data that exists in a column (data type defined as a text) in query analyzer - Hello friends, There is a big xml string in a column (data type defined as a text) When I query in query...

Point in time restore - I prefer to use T-sql to do my point in time restore than use the GUI. But when you have hundreds...

clustering in sql - 1.what is the SQL network name’s dependency in cluster? 2.Prerequesities to install cluster in sql 2005?

Active Directory Groups - Excuse my ignorance, but I'm fairly new to SQL and Visual Studio. I'm creating a VB app that allows users...

Tempdb - How temp db worksin ? sql

how do i upgrade my sql server 2008 r2 x86 to sql server 2008 r2 x64 ? - Hi, i have just install a sql server 2008 R2 standard in my server having Win server 2008 R2 x64 OS. But...

How to populate end date based on start date - Hi Professional , I have to populate end date based on start date of next record - 1 So please sugget me how...

upgrading sql 2000 to sql 2008 will make any problem to my database ? - hi, i am running sql 2000 now my company has decided to go for sql server 2008. i have many tables,indexes,functions,procedures...

change location of utf file in log shipping - Hi every body Is there any way to change the location of .tuf file in log shipping? I am using sql server...

do this update even make sense - update acrt set dbo.ACRT.MIDIRB = dbo.IRBT.IRBRATING from acrt,IRBT where dbo.IRBT.id = dbo.IRBT.id +1 and NEGWATCH = 1

Run time of Cursor - hi all i have stored procedure with 45 cursor, so the stored procedure taking long time to finish. is it...

Differential vs LOG bAckup - What are the differences between Differential vs LOG bAckup Bulk recovery vs simple Recovery

using dateadd function - Hi All I'm new @ SQL server. i need to select record that are within 30 days of this given date...

How to get backup of Accidentel Executed procedure script - Hello, someone executed accidentally old script in a database , have any way to get back date procedure script . if have,...

newbie help pls - Customer CustomerID CustomerName Birthdate 1 John Doe 1/1/1970 08:31 AM 2 Jane Doe 1/1/1971 01:18 PM 3 Jon Public 1/1/1972 11:58 PM 4 Jane Public 1/1/1973 07:00 AM 5 John Smith 1/1/1974 08:31 AM Order OrderID PO Number OrderDate 1000 ABC123 1/1/2012 01:00...

Date diff between timein and timeout rows - Autoid Empid TimeIn Timeout 1 1009 13-10-2012 22:10 13-10-2012 22:15 2 1009 13-10-2012 22:20 13-10-2012 22:32 3 1009 13-10-2012 22:34 13-10-2012 22:36 I need to find the idel time for this.. like...

Arithmetic overflow error converting nvarchar to data type numeric. - hello when i run the following sql query: "update table set price = price * 1.1 " i get the following error : "Msg 8115, Level...

How to select the inherited values in hierarchical data structure is efficient way - I have hierarchical data structure with [State] as top level, [Region] as intermediate level and [Branch] as leaf level. Region can...

How do I join tables, but select top 1 from 1-many tbl? - Essentially I have a Contact table and a History table. I'd like to get the join the tables, but only show...

UDF Performance When Used As A "Macro" - My group is having a discussion as to performance hits when a UDF is used as a macro - IE it...

Best way to insert a lot of data in a database - Hello, I've generated one file per table of my database. Each file contains 'insert' statement for each records of the table....

UCT to WST -15min - Hi, So I need to write a query that will if something was modified in the last 15 min. The...

SQL 2008 R2 Standard Edition license requirements for a Virtual Server - Hi All, I've read several things on the web today and got myself totally confused. Can someone explain to me how I...

please help with query - This should be straightforward, I just don't know how to write it, any help is appreciated. I have 2 tables, one...

Columns not in key look up, why? - Hey all, We came across a problem today, no solutions yet, here is the scene, we have a query which uses...

Importing VFP data using DTSWizard - So after beating my head against the wall for a few weeks I'm hoping someone can help me out. I am...

SSIS Package execution error with Proxy account (SQL Agent Job) - Getting issue while scheduling the SSIS package using SQL agent job. I have designed an SSIS package which will connect to...

Linked server permissions issue - Hello everyone, Hope all is well. I have recently setup a linked server from sql server 2005 to mas90 providex database and...

SQLWEP errors in Windows Event log - I'm seeing the following errors in my Windows Application event log: Cannot create the event notification 'SQLWEP_CE492A26_8D18_48CA_B4A0_0221AB3AD1DD', because you do not...

XML + SOAP - Can you guys help me with this XML? [code="sql"] if object_id('tempdb.dbo.#Temp') is not null drop table dbo.#Temp CREATE TABLE #Temp (col xml) INSERT #Temp (col) select...

SQL Server 2008 : T-SQL (SS2K8)

Dumb? SQL Question - I'm working on a dynamic SQL builder and came across the following by accident. [code] -- Some background -- To query from...

error - incorrect syntax near '4' - CREATE PROCEDURE usp_update @pcid int ,@ppid int ,@ptid int ,@pqty int AS BEGIN SET NOCOUNT ON; DECLARE @Rate int SET @Rate = (select prate from M_PRDT where PID=@ppid) IF EXISTS...

Using CTE doughts - Hello comunity I have build this current CTE query to have a drilldown information to know from the customer order, which...

Prioritize the records in a table - I have a table which has id and statement columns, statement column contains statement like insert into tablename, update tablename,...

Avg count of cases per day - Hello everyone. I am looking to produce a result set from a helpdesk application that tells me the average number...

Should I use a nested subquery? - SELECT DISTINCT RepairSheetH.RepairSheetNr, Vehicle.unitnr, Vehicle.groupid, Vehicle.brandId, Vehicle.modelId, Vehicle.version, Vehicle.Type_Fleet, RepairSheetH.close_date, CASE WHEN RepairSheetH.unitnr = Vehicle.unitnr THEN count(vehicle.modelId) ELSE 0 END as...

SQL Server 2008 : SQL Server Newbies

Select Query to return all rows from one table - Hi All, I have three tables tPeriod which has PeriodID int, PeriodDiscription char tTimeTable which has DayID int, StaffID int, SubjectID int,...

User - select from master database query. - Hi, I need to access the master table to checkl the applications logged into the database for a licensing issue. If...

I can't get Modify (Maintenance Plans) to do anything. - In SSMS, when I highlight a maintenance plan that I successfully created, right click, and then select Modify, nothing happens....

remote table update performance - Hi all, I am having problem updating remote table. It just taking too long. I am new to SQL Server and...

Trying to view properties of the Database - When I right click on the database to view properties on any of the databases on the server I get...

Design Question for Discriminator Column - I have a requirement to record payments and payment method information. A payment can be received as ONLY ONE of...

SQL Server 2008 : SQL Server 2008 High Availability

Log Shipping principal taken offline, how to stop witness messages? - I have taken the primary server in a log shipping pair offline permantly; the machine has been re-purposed. I thought...

uninstall default instance - I have 2 Windows 2008 R2 Servers that are clustered. I am installing sql server 2008 r2 sp2 cu2 in...

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

Running SQL 2005 & 2008 in a 2 node cluster - Is it possible to have a setup running SQL 2005 & 2008 in a 2 node cluster? Thanks Pete

SQL Server 2008 : SQL Server 2008 Administration

Plan Cache usecounts/execution_count - Hi all I'm using the below script to check my plan cache [code="sql"]SELECT top 20 SUBSTRING(t.text, ( s.statement_start_offset / 2 ) + 1, ( ( CASE statement_end_offset WHEN -1 THEN...

Idera Tools - Web Resources / Support Forums? - Hi, At my previous company we were using various Red Gate tools and I found the resources on the website and...

Only send DBMail when query results are present - I have a job in SQL Server Agent that sends the results of a query to my email twice daily,...

Shrink of data file - DBCC SHRINKFILE - taking a long time and not completing - stuck at 99% completion - We had a large data purge recently where a large part of the data in a file was deleted. File is...

Can't revert from snapshot - I wanted to remove user data from a database to make a new version of the database with no user...

Problem with write access for Windows users on SQL 2008 R2 - Hi, I am having strange issue with my SQL 2008 R2 Server. We have an active-active cluster with 2 different instances...

Reporting database Slow - Hi Experts, Team is complaining that their database is slower than usual and reports are not getting generating and everything is...

Can DDL Admin be assigned at the schema level instead of the DB level? - Specialized User / Role / Schema setups We don't want users to be able to modify objects in other schemas, just a...

Programming : Powershell

Remove items from an array based on another array - Hi, I have 2 Array or ArrayList objects, with data like [code="vb"] $Results = New-Object system.Collections.ArrayList $Results.Add("IMP_Trn_Project_Agent_6,User1") $Results.Add("IMP_Trn_Project_Agent_6,User2") $Results.Add("IMP_Trn_Project_Agent_6,User3") $Remove = New-Object system.Collections.ArrayList $Remove.Add("User2") [/code] How can I efficiently remove from the...

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

Reporting Services : Reporting Services

Sum with condition not working - Hopefully someone here can help me solve this problem. What I have so far is a list with two tables in...

Internal Error: Services limit has been reached - Has anyone had this issue before. I have a report which has a basic query. It selects several columns from 2...

Inherit Multi Value Parameter - Hi geniuses! I have 3 reports: Report1 with 2 tablix in it (tablix1 and tablix2). These tablix are quite small, and the...

MDX performance issue to go upto day level - Hi experts, The data exists in the OLAP database as follows: [b]Currency Rate | Revenue | Receipt Date[/b] 3000 | 40000 | 12/02/2012 3001 | 34000 | 13/02/2012 ...

Language Setting for a Field (Run Time) - Hello all, I am trying to set Language setting for a filed in my table dynamically. I have an expression which...

Reporting Services : Reporting Services 2005 Development

Failed to host SSRS Report on other server - [font="Courier New"][size="3"]Hi All, I am working on SSRS, I have developed a report when deployed it in local instance its working...

Database Design : Design Ideas and Questions

Normalization from an excel form - Hey guys, I have some problems with normalizing the data. Could you take a look at this file http://speedy.sh/YtK9G/example.xlsx These is the list...

Data Warehousing : Integration Services

Generate XML using SSIS - Can someboy tell me is it possible to generate XML using SSIS script task and SSIS variables (not from SQL...

Data Warehousing : Analysis Services

Filter on measure? - I have a cube with measures and dimensions. Now, one of reports which I need to create must have filter...