In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Monitor logo Get alerts within 15 seconds of SQL Server issues
SQL Monitor checks performance data every 15 seconds, so you can fix issues before your users even notice them. Start monitoring with a free trial.
 
SQL Source Control Database source control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Get started now.
 
sqlbackup Get compressed, verified, secure SQL server backups
Use SQL Backup Pro's automated scheduling to get faster, smaller backups. Then verify your restores using DBCC CHECKDB in one easy, automated process. Download a free trial now.

In This Issue

Using a Variable for an IN Predicate

How to use a passed string variable for an IN type predicate in your queries as a way to more efficiently handle an array of strings. More »


New Free Event for Database Professionals in Virginia

SQL Server developers and database administrators have the chance to attend a free half-day event, in Richmond, VA - hosted by SQL Server experts Steve Jones and Grant Fritchey.  More »


SQL Server Statistics Questions We Were Too Shy to Ask

If you need to optimise SQL Server performance, it pays to understand SQL Server Statistics. Grant Fritchey answers some frequently-asked questions about SQL Server Statistics: the ones we somehow feel silly asking in public, and think twice about doing so. More »


SQL Server Central Webinar Series #22 – What counts for a DBA (Wednesday, March 13 2013 5:00pm - 6:00pm BST)

In this session, Louis Davidson, Microsoft MVP, will discuss how being observant of the environment you work in can help you make sure that you are aware of the health of your database systems, as well as your career. More »


From the SQLServerCentral Blogs - Five Virtual Labs for the SQL Server 2012 DBA

Wouldn’t it be nice if you had a chance to try out the features of SQL Server?  Maybe give them... More »


From the SQLServerCentral Blogs - PerformancePoint ‘Named Set’ Filter

PerformancePoint provides several options to create filters.  One of the options available is ‘Named Set’.  Within SSAS cubes we can... More »


Editorial - Why SQL Server?

Over the last few months I've met quite a few people that were just starting to work with SQL Server. For many of them, they've attended talks or presentations of mine or others, trying to learn enough to become more competent at their jobs. As is the case for many people starting out with a technology, they were thrown into it at their job and are struggling to understand the technology.

That's how I started, but I'm wondering if that's how most of you started. I would guess most of us didn't necessarily choose to work with this platform, but since that time, many of us have had other choices. This Friday, I wanted to ask about your work in the SQL Server world and participation at SQLServerCentral. 

Why do you work with SQL Server?

It's a simple question, but one that many of you might not think about. Are you stuck with SQL Server? Did you fall into this role and enjoy it? Or have you never considered moving on? A lot of people get stuck in ruts, especially ones that work well for them, without ever considering other options.

For me, I fell into SQL Server when an instance was installed on my Novell network. I enjoyed working with sets of data and was amazed at how much easier it was to work with SQL Server than Oracle, and how much better performance was over dBase. It didn't hurt that the pay for database work was great, but I have enjoyed working with SQL Server, and with short forays into the DB2, Oracle, and MySQL worlds, I've learned that I enjoy SQL Server more.

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

Will this code execute successfully?

-- First Step
-- First Create a Table 
create table temp1
( name nvarchar(50)
)

-- Second Step
--First Stored Procedure
create proc sp1
as
select * from temp1
exec sp2

-- Third Step
-- Second Stored procedure

create proc sp2
as
select * from temp1
exec sp1

--Fourth step
exec sp1

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.

Performance Tuning with SQL Server Dynamic Management Views

This is the book that will de-mystify the process of using Dynamic Management Views to collect the information you need to troubleshoot SQL Server problems. It will highlight the core techniques and "patterns" that you need to master, and will provide a core set of scripts that you can use and adapt for your own requirements. Grab your copy today from Amazon!


Yesterday's Question of the Day

What will be the results from after running following statements on SQL 2008:

CREATE TABLE T1 (C1 Int)

INSERT T1
VALUES ( 1 ),
( 2 ),
( 3 ),
( 4 )

UPDATE dbo.T1 
 SET C1=C1+(SELECT MAX(C1) 
              FROM dbo.T1)
 WHERE (SELECT MAX(C1) FROM dbo.T1) > C1

select * from T1

drop table T1

Answer: 5,6,7,4

Explanation: SQL supports a concept called all–at–once operations, which means that all expressions that appear in the same logical query processing phase are evaluated as if at the same point in time. -- by Itzik Ben–Gan

Ref: http://www.ndc2010.no/index.aspx?id=268800
http://technet.microsoft.com/en-us/library/ms177523(v=sql.110).aspx

» Discuss this question and answer on the forums

Securing SQL Server

Written by Denny Cherry, a Microsoft MVP for the SQL Server product, a Microsoft Certified Master for SQL Server 2008, and one of the biggest names in SQL Server today, Securing SQL Server, Second Edition explores the potential attack vectors someone can use to break into your SQL Server database as well as how to protect your database from these attacks. In this book, you will learn how to properly secure your database from both internal and external threats using best practices and specific tricks the author uses in his role as an independent consultant while working on some of the largest and most complex SQL Server installations in the world. This edition includes new chapters on Analysis Services, Reporting Services, and Storage Area Network Security.

Get your copy from Amazon today.


Featured Script

Compress all Objects

A short compression script that will compress all tables or indexes. 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

DBCC CLEANTABLE Not working on my table - Hi All, Actually I am running below command, but i am not able to reduce "UnusedSpacedKB" 184 from XYZ Table. I am...

Adding an extra file to TEMPDB - We have a sever with 2 physical processors and 8 logical ones. We are seeing quite a lot of blocking...

Login failure - I had a user state they could not log into a specific database with an NT login and the SS...

SQL Agent skipping job runs? - Hello all, I've got a weird one. It appears that I have had the SQL Agent just skip running a scheduled...

SQL Server 2005 : Backups

SQL Server 2005 to 2012 Restore using Red-Gate Backup 5.3.x - I am using Red-Gate Backup tool 5.3 to take backup of the database in SQL Server 2005. Now I want...

SQL Server 2005 : Business Intelligence

SSIS Login Error - Hi all I have created a SSIS package that contains an OLEDB connection to a database using a SQL login on...

SQL Server 2005 : Development

Performce SP - Hi , One of SP is having below code and it is running more than 2 hrs. This job is daily...

SQL Server 2005 : SQL Server 2005 General Discussion

Creating a format file to import XML data - Hello gurus and geniuses Please forgive me starting yet another thread on this subject. I have searched the forum and read...

'The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated. - Hi, im a newbie in SQL. My colleage asked me to find out about this error 'The subscription(s) have been marked...

Import Excel Spreadsheet Data into SQL 2005 - I have some data that I need to import from an Excel spreadsheet. The trouble is that SQL 2005 is...

Execute Stored Procedure Multiple Times - We have a sproc that has been around a long time, it's ben tweaked & prodded, etc over the years to...

Reducing Initial LDF/MDF size - Hello, I have a database (version 9.0.4060) that has an initial log size set to 322GB. I would like to change...

SQL Server 2005 : SQL Server 2005 Strategies

TempDB optimization - hello, in ma current configuration i have one data file and one log file of a tempdb database. as a process of...

SQL Server 2005 : SS2K5 Replication

replication server name problem - Hi i am getting an error when i try to configure replication distibution.... [size="1"]SQL Server is unable to connect to server...

SQL Server 2005 : SQL Server 2005 Integration Services

Tip needed - Change Connection on several containers at the same time - I have a DTSX package that I need to change the connections on for about 30 containers. Is there a...

SQL Server 2005 : T-SQL (SS2K5)

Fuzzy lookup on multiple columns - I have a problem where i would need to do a fuzzy lookup on the below columns and get the...

find whether a upper case letter is there in a given string - Hi all, i want to know whether a upper case letter is there in a given string or not? if i provide...

SQL Server 2005 : SQL Server Newbies

Problem with openxml - Hi, I'm trying to read a column from the xml using openxml. Things are fine except while trying to read one...

SQL Server 2008 : SQL Server 2008 - General

Estimated Execution Plan Curiosity - I have a nightly job the runs about 50 insert & update statements and takes a few hours. I ran the...

Error login packet - Hi, We are using sql server 2008 with SP3. We are getting below alerts frequently on sql server.can any one help me...

Using a pecking order for addresses - Hello All, I have encountered a new problem that I haven't dealt with before. I have a table of individuals who...

Compare the rowcount - hello World, I have table with date and rowcount like below. Time stamp Row Count 2013-02-28 10:53:50.283 1211 2013-02-28 11:53:50.283 1212 2013-02-28 12:53:50.283 1213 2013-02-28 13:53:50.283...

Help getting all the weeks between two random dates for each row - HI All I need some pointers/help in getting this done I have some data sample below each row has two week numbers...

Accessing a cluster SQL Server instance without the instance name - Our application is currently connecting to a SQL default clustered instance using a ‘CNAME’ or alias. I am in the...

Query in SSRS - hi All ,i have one requirement where the SSRS report is displaying only certain data of a column based on...

MCTS 70-448 expiring in July 2013 - hi, I want to gain a certification of entry level. I'm working as a BI developer for around 5 years and...

Extended Properties for adding descriptions - Hi, I want to use extended properties for adding description to our database. I tried it and I am able to...

Publish SSRS 2008 in LAN - How to publish SSRS for internal alone. I have a project which needs to be circulated only to my team members...

ISO 8601 - The correct way to represent a date - [url]http://xkcd.com/1179/[/url] [img]http://imgs.xkcd.com/comics/iso_8601.png[/img]  

How to use uniquename function in my mdx script? - Hi All, I really need your help. For a SSRS report, I have this mdx script: select {[Geographie].[Commune].[AHUY], [Geographie].[Commune].[BRETENIERE]} on columns ,{[Activite].[Branche].&[B], [Activite].[Branche].&[C]} on...

how to get Mac Address in SSRS - Hi, I am having a requirement of taking the MAC Address using SSRS is it is possible to take? My Requirement: To...

Is my Create Index Online finished? - If I create an INDEX like the following [code] CREATE NONCLUSTERED INDEX [IX_table_col1_col2] ON [dbo].[table] ( [col1] ASC, [col2] ASC ) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY...

Optimizing query - Hi, Can someone help me in optimizing below query, is there any other way where I can improve this query SELECT A.Col1,...

SET ROWCOUNT - So I set the rowcount to 1 in a query session.....but now I can't get more than 1 row to...

Detect 32 or 64 bit SQL from registry. - I am trying to write up a script to detect whether the installed SQL Server 2008 instance is 32 or...

effective way to break a string into rows basing on lenght of string - /*Current table that has a Primary key and a text column, need to break this text into rows if the...

Index Selection in SQL Server - Hi, Consider the following example : I have a table Employees: [code="sql"] Create Table Employees ( EmployeeId BigInt Identity(1,1) , EmployeeName Varchar(30) NOT NULL...

Getting minimum of top n rows without using subquery - Hi, We use SELECT min([date]) FROM table WHERE [date] IN (select top(100) [date] from table order by [date]) query for selecting...

fetch tables most often queried - Is there a way to determine which tables of a database are being queried most often, sorted in descending order?

get the first and last day of any Year/Month - Hi, i have a view with two columns, lets say SpecYear and SpecMonth, both are integer. How can I build two...

Need help with dynamic sql query - Hi, I need help with this dynamic query to execute the sql statements but it gives the following error when...

Installing sql server 2008 in command line - I am using command line to install sql server 2008 automatically in silent mode.But if i select mixed authentication then...

error while installing sql server 2008 r2 express in a windows 8 pc - I was trying to install sql server 2008 R2 in windows 8 pc. First i installed sql server 2008 r2 by...

login_name shows up as blank in sys.dm_exec_sessions but not in sp_who2 - When I probe [b]sys.dm_exec_sessions [/b](joining with other DMVs to get active-session info) I get the login_name column to be blank...

SQL Server 2008 : T-SQL (SS2K8)

Compare the rowcount - hello World, I have table with date and rowcount like below. Time stamp Row Count 2013-02-28 10:53:50.283 1211 2013-02-28 11:53:50.283 1212 2013-02-28 12:53:50.283 1213...

A complicated situation - Hi, I am working on a project where the resistance to some antibiotics is studied. We have a database of patients...

select rows into colums - Dear T-sqlérs, I have a test table (see script below) with the following result name length balk1 7 balk1 6 balk1 9 stof1 6 stof2 6 stof3 6 stof4 6 stof5 6 stof5 7 stof6 6 stof7 6 stof8 6 stof9 6 stof9 7 stof10 6 stof11 6 stof12 6 Now I would like the result to be...

converting Varchar details into nvarchar of my table - hai friends , i am create on table like create table wish ( id int identity, name nvarchar(100), city varchar(100), comments nvarchar(max) ) insert...

sql pivot problem with unknown number of records to columns. - Hi Please help me out in this scenario. Table ID NAME 1 a 1 b 1 c 1 d 2 e 2 f 3 g 3 h 3 i 4 j 5 K 5 L 5 m 5 N 5 O 5 P required output id name1 name 2 name3 name4 name 5 name6 1 a b c 2 e f 3 g h i 4 j 5 k l m n o p i need a query which gives the

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

How to display Columns based on other table values - i have below tables 1) student (sno,name,subject,cost,city), 2) studentPT (sno,Ptname,EnrollDate) 3) StudentDrill (Sno, DrillName,EnrollDate) now the requirement is want to display sno,name,cost,PTCol (if...

Is a doomed transaction inevitable if deadlock occurs in a trigger (SQL Server 2008 R2)? - Hi all, Below are two scenarios, however, what I am trying to accomplish is to catch deadlock errors and re-try...

Restrict overlapping records - HI All, Just wanted to know whether can we create a constraint to restrict the overlapping records in the following...

Query to list all jobs - Hello, We are consolidating servers and I was asked to write a query that returns a list of jobs on a...

Right align Alpha field in msdb.dbo.sp_send_dbmail - I'd love to have some pointers on this problem. I see postings on the 'net claiming to correct the problem...

Updating specific rows - I am having a problem, but think I may be making it harder on myself. I need to update a...

Interesting Question in SQL - Hi Everyone.... I have a table (no Primary Key defined) with some value and I need to create a view on...

UDF Help - Hi Guys, I want to create UDF. Below is my logic. Please guide me where i am wrong. Alter Function [dbo].[udf_GPList](@EID int,@PID...

Calculation with aliases - Hi, I have the following query: [code="sql'] USE ICP; GO DECLARE @beginDatum Datetime DECLARE @eindDatum Datetime SELECT T.Ticket_ID, UA.Useraccount_First_Name, UA.Useraccount_Last_Name, A.Description, T.Ticket_Accepted_DateTime, T.Ticket_Closed_DateTime, DATEDIFF(second, T.Ticket_Accepted_DateTime, T.Ticket_Closed_DateTime) AS...

SQL Server 2008 : SQL Server Newbies

Removing a secondary tempdb file - Hello. I created a secondary tempdb file on our testing server the other day and i wish now to remove it. I...

Remove Leading Zero(s) only if needed - I have four tables that have a varchar data type which gets imports or daily user updates and this field...

Add existing packages - bulk copy? - Hi, We are shutting down a server and I want to get a copy of the packages in the MSDB before...

SQL Server 2008 : Security (SS2K8)

Find dependencies on a Login - This week I noticed a SQL login on a SQL 2008 R2 production server I and the other DBAs here...

SQL Server 2008 : SQL Server 2008 High Availability

Confusion regarding which full-backup file to apply on secondary in Log shipping - Hi we have a log shipping configured at one of client side and is out of shrink for log time...

Installation Error: Volume does not belong to the cluster group - I am installing SQL 2008 R2 cluster in windows 2008, I got the following error: The volumne contains SQL server data...

how to configure DB Mirroring Endpoints - I have one System (box)on which I have two databases which are to be mirrored on another instance on the...

SQL DTC Confusion - Hi All I am trying to figure out the ins and outs of DTC and how SQL uses it. Does SQL only...

SQL Server 2008 : SQL Server 2008 Administration

sysfiles and master_files out of sync - Hi all, I apologise if this has been raised before, but I couldn't find it using search... I've come across a situation...

Sudden slow performance from distributor to subscriber in transactional replication? - We've had transactional replication enabled for 6-8 months to update a secondary database instance that's used for reporting purposes. It's...

Multiple Transaction Logs, will removing one break Log Shipping? - I have a production server that a former DBA created additional LDF files for a given database - it is quite...

Does the adrenaline rush ever subside when rebooting a DB server. - I work in a small shop as the accidental DBA and for the first time rebooted our production DB server...

SQL Database code comparison - Good Day all, I am in a process of setting up a full blown test and development environment. We are primarily...

Identifying Machine\IP - Hi Experts, We had a situation in which one user fetching data . We got machine\username but searching with that value in...

Low Performance - High CPU Problem and others... - Hello everyone, I got the Problem, that my SQL Server has always 97-99% CPU usage. We have snapshot isolation mode enabled,...

Career : Certification

Which one is best certification - Dear Friends, I have 2.5 years experience in SQL Development and BI tools (SSRS,SSIS). Which one Certification is best for me. and...

Passed 70-433 yesterday (yay!). Now 70-451 or short path to MCSA 2012? - Hi guys and girls. I passed the 70-433 exam yesterday on the second attempt. I missed out by 2 questions...

How to pass a Microsoft certification [70-432] - I am planning to take up this certification .Can you guys please tell me how to clear this certification? That is...

Programming : Connecting

Execute a sql job from AS400 command line - I am trying to use this command to execute a sql job from an AS400 command line. I am having...

Programming : General

column selection on dynamic basis - Hi, How can i select columns of a table dynamically through a select statement if the column name need to be...

Query Analyzer - Hi, Does anyone know if Query Analyzer stores all the queries that are executed into a history file and if so...

SQLServerCentral.com : Anything that is NOT about SQL!

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

Count Function In Reporting Services - Dear All, I'm trying to do a simple query where I want to count a Column with a Dataset but its...

SSRS 2008 inserting blank page between the groups - Hi All, I have created a report in SSRS 2008. I have enabled property "Page break between the groups". Its working...

XML Data in Reporting Services - Dear All, I am trying to do the following: 1. Create a dropdown list using XML data which I've imported from a...

Query to Select data from a single table with mutliple conditions - Hi, I have a table with the below column headings. Business Unit Document ID Document Date Year Period Account Descr Dept Product Project Analysis Code Affiliate Currency Base Amount USD Amount Line Descr Reference Vendor Vendor Name Module Doc Line # AP Distrib...

Reporting Services : Reporting Services 2005 Development

Thousand separator in line chart - I have a line chart created in SSRS 2005 and need to use a thousand separator on the Y axis..can...

Data Warehousing : Integration Services

Moving SSIS packages from DEV to Prod - Hello , I have a SSIS package that I just developed for the Development Environment. I need to move the package...

How to convert datetime to string format (YYYYMMDD) in SSIS? - How to convert datetime to string format (YYYYMMDD) in SSIS? I have done using Datepart but for the date 01 to...

config file is hard coded with UNC path rather than the relative path - Guys, We are using Mercurial Hg for Source code control for our SSIS packages. Hg works by pulling the entire repository to...

IS 2008 Triggering an Excel Macro via Script Task - The Binary Code for the script is not found - I have a Macro which works in Excel. It basically just formats headers and creates a validation list so the...

Load Encrypted Column Data.............. - Hi all, I m having source as eaxcel with following type of data. EmpId,Fname,Lname,Sal and i want to load this data frequently into...

String to Date conversion with day name - Hi I have a csv file I use as a datasource and I need to pull the data into a table I'm...

Data Warehousing : Strategies and Ideas

sql12 tabular mode design - Hello, Im finding it a little "extra" work is needed to get a complicated tabular model designed vs traditional OLAP...

Fact/Dimension design suggestions - I'm from Higher Education and I'm working on a warehouse so our recruitment folks can compare application counts of certain...