In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Backup Pro "Cut the backup time by hours and the file size by 80%" Hazel Cawood, Systems Analyst
Find out how much time and space you can save with SQL Backup Pro. Get compressed, encrypted and fully verified backups. Download a free trial now.
 
Deployment Manager How to automate your .NET and SQL Server deployments
Deploy .NET code and SQL Server databases in a single repeatable process with Red Gate Deployment Manager. Start deploying with a 28-day trial.
 
SQL DBA Bundle ‘Six Scary SQL Surprises’
Brent Ozar joins the DBA Team, for Lesson 3 of their ‘Top 5 Hard-earned Lessons’. Gain valuable tips from the pros - Read now.

In This Issue

Using Regular Expressions to Convert PL/SQL code to T-SQL

When converting Oracle PL/SQL to T-SQL, there are a number of ways to do this, but this article shows us how this can be done with regular expressions in Management Studio. More »


SQL Server 2008 R2 Build List

The list of builds for SQL Server 2008 R2 up to CU12 for SP1 and CU6 for SP2. More »


SQL Server Central Webinar Series #23: Safeguard your data offsite with SQL Backup Pro

You may already have a great backup and recovery plan. However, if something were to happen to your databases and you needed to restore from your backups, you’d want to be in control, with the ability to access a copy of those backups quickly, restoring them with minimal downtime and minimal fuss. In this session Grant Fritchey, SQL Server MVP, will discuss the key reasons why you need to have offsite backups, and the advantages of hosted storage. He’ll address some of the fears surrounding cloud backups, and show how offsite backup is made quick and easy with the new ‘backup to hosted storage’ features in SQL Backup Pro 7.3.  More »


Comparison of Queries Written in T-SQL and SQL Server MDX

Beginning to learn and comprehend SQL Server Analysis Services (SSAS) MDX queries can be difficult after one has spent years writing queries in T-SQL. When trying to write SQL Server MDX queries, it's worth considering "How would I write this query in T-SQL?"  More »


From the SQLServerCentral Blogs - T-SQL Tuesday #41 Becoming a SQL Server “Presenter” GET INVOLVED!

Hello Dear Reader!  It’s been a while since I participated in a T-SQL Tuesday, and this month Bob Pusateri (@SQLBob|Blog)... More »


From the SQLServerCentral Blogs - T-SQL Tuesday #41: How I Got My Start in Presenting

For this month's T-SQL Tuesday, Bob challenges us to discuss how we came to love presenting. Here's my story. I developed a... More »


Editorial - Upgrading to 2012

It's been a little over a year since SQL Server 2012 was released in its RTM version. In that time, I've been using it on most of my machines for testing and demos. I like working with the new features and enhancements, and I think this is the best version of SQL Server yet. However I also keep a version of SQL Server 2008 running in a virtual machine since this is the version that runs SQLServerCentral.

In the last three years, we've continued to run the site on SQL Server 2008 because it works well. We don't need the newer features, and since we have a clustered pair of instances, an upgrade would be a substantial cost. That cost is hard to justify when there isn't a business benefit I can point to. I'm sure we could write more efficient code with the new T-SQL enhancements and improve performance, but since we've invested in beefy hardware, I'm not sure we would gain much with an upgrade. Quite a few of the people I've talked to in the last couple years feel the same way.

This week I'm curious to see if any of you are looking to upgrade. I know for new systems it might make sense to just install the latest version of SQL Server, but what about existing systems? The question this week is:

Are you upgrading any existing systems to SQL Server 2012 and why?

I'm looking for those drivers that provide enough benefit for you to upgrade. There might be features you are taking advantage of that others can use, so share with us the exact reasons for your upgrade. As budgets shrink, especially for systems that are working well, it can be hard to justify upgrades across the board for all your servers, but there are sometimes reasons to upgrade individual instances.

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

When running the code below on SQL Server 2008 and above, what will the final query (with the comment "Predict the output of this query") return?

CREATE TABLE dbo.Test
   (PrimKey int NOT NULL,
    ValueCol int NOT NULL,
    PRIMARY KEY (PrimKey)
   );
go
CREATE TRIGGER TestTrig
ON dbo.Test
AFTER INSERT
AS
IF EXISTS(SELECT *
          FROM   inserted
          WHERE  ValueCol < 0)
BEGIN;
  RAISERROR ('Negative values are not allowed!', 16, 1);
  ROLLBACK TRAN;
END;
go
-- Insert attempt #1
INSERT INTO dbo.Test (PrimKey, ValueCol)
VALUES (1, 1);
-- Insert attempt #2
INSERT INTO dbo.Test (PrimKey, ValueCol)
VALUES (2, -2);
-- Insert attempt #3
INSERT INTO dbo.Test (PrimKey, ValueCol)
VALUES (3, 3), (4, 4);
go
-- Predict the output of this query:
SELECT COUNT(*) FROM dbo.Test;
go
DROP TRIGGER TestTrig;
go
DROP TABLE dbo.Test;
go

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

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

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.


Yesterday's Question of the Day

What results do we get from this code?

create table #T1(id1 int );
create table #T2(id2 int );
create table #T3(id3 int );

insert into #T1 values(1);
insert into #T2 values(1);
insert into #T2 values(2);
insert into #T3 values(1);
insert into #T3 values(2);
insert into #T3 values(3);

SELECT 
  sum(id3)
, COUNT(id3)
 FROM #T1, #T2,#T3;

drop table #T1,#T2,#T3;

Answer: 12,6

Explanation: The SELECT statement will cross join three tables. Each row of one table is combined with each row of the other table. The total rows count will be 1*2*3=6! The summary of id3 will be 2*1+2*2+2*3=12!

Ref: http://msdn.microsoft.com/en-us/library/ms190690(v=sql.105).aspx

» Discuss this question and answer on the forums

Pro SQL Server 2012 Practices

Expert SQL Server Practices 2012 Edition is an anthology of high-end wisdom from a group of accomplished database administrators who are quietly but relentlessly pushing the performance and feature envelope of Microsoft SQL Server 2012. With an emphasis upon performance—but also branching into release management, auditing, and other issues—the book helps you deliver the most value for your company’s investment in Microsoft’s flagship database system.

Get your copy from Amazon today.


Featured Script

Script to find the total used size of all database inside SQL Server

Script to find the total used size of all database inside SQL Server 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

Shrink log file - I have a Database (about 1GB) with 2 log files (one at about 4GB and one at about 13 GB)...

SQL Server service account locked - Hello, We had a case where in the domain account which is running SQL server got locked. Database connectivity through management studio...

How to find bound defaults? - Is there an easy way to find all the bound defaults on table objects. We have an app where many of...

SQL 2005 Full text index problem - Today I've come upon a full text catalog error that has me baffled. All queries against my full text indexes...

SQL Server 2005 : Backups

full and differential backup - hi when i am taking full backup and i am restoring full back up i am getting error like "the database...

SQL Server 2005 : Business Intelligence

Error while importing multiple sheets of same excel into one table using ssis. - Hi, I am importing multiple sheets of same excel into one table using ssis. I am following below flow. Data flow task->Excel Source->Look...

how to generate records automatically from csv to sqlserver table using ssis etl - Hi All, i have one requirement in ssis. I have one csv file (source) having one column called UOM (Unit of...

How to set user for deploying reports? - When I deploy my report from BIDS (2008) it doesn't ask me for a user name, it picks a user...

SQL Server 2005 : Data Corruption

Stand by/read only database on SUSPECT - Hi all, I had the issue twice back to June, and Oct this year. Our environment uses log shipping between two...

SQL Server 2005 : Development

How to write these values down a column - Hi, I've been trying to think of a way to change the appearance of the following data. It currently looks like this ClaimKey ...

Automatic Update from Excel to SQL - I need to load the data from excel to a table in SQL Server 2008 and automate the updates.ie., If...

SQL Server 2005 : SQL Server 2005 General Discussion

interview questions - any info on really good interview questions for a sql server dba?

could not start the SQL Server(MSSQLSERVER) Service on local computer. - Hi, I installed sql server 2005 on my machine i keep getting the following errors......when i start up the services I...

Find number of weekdays(Monday,Tuesday..) between two dates monthwise - Hi All, I need to find average week day count on a monthly basis. My table has the creation date as...

SQL Server 2005 : SQL Server 2005 Performance Tuning

so many indexes indexes - what if i create so many indexes in a database, on each and every table small or large...? any bad...

SQL Server 2005 : SQL Server 2005 Integration Services

load multiple xml file into a single flat file destination - hi guys i need help, i've created a ForeachLoop Container which loop through all the files .xml in my source directory...

PGP - Encrypt and decrypt files through batch - I just installed PGP software on my machine. Now I want to use it, but not in the GUI interface...

SQL Server 2005 : T-SQL (SS2K5)

Excel VBA call to execute stored procedure. - How do you surround a stored procedures parameters so that any with a single quote don’t cause a crash. Excel VBA...

Format values as Percent - Hi ALL, SQL: Select MyColumn FROM ( SELECT CAST(CAST(0.10*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn Union SELECT CAST(CAST(0.011*100 AS numeric(10,2)) AS varchar(5)) + '%' as...

SQL Server 7,2000 : Administration

SQL server 2000- Maximum RAM support - Hi, What is the amount of total RAM supported by SQL server 2000 irrespective of any operating system compatible with it....

SQL server 2000 - Profiler error - Hi, I am using profiler to execute procedure integrated into database ( I am not author and I dont know what is...

SQL Server 2008 : SQL Server 2008 - General

Why such a delay in the commit? - I have a scheduled job (.Net, WinOS Task Scheduler) which completes shortly after it's called but the SQL work it...

Update on ID's - Hi Guys, Need a more efficient way to do this : [b]Old Fact Table[/b] FiscalID SID Value 1 344 78.44 2 345 44.33 3 346 44.55 4...

Can full text search on a column in Multi Languages like En&Fr? - I have a table column contains English and French. My FTS always returns something unexpected. I am not sure because...

partitioning strategy for this scenario: - HI Here is the scenario: Data is loaded in Table "ABC" every week and size of 1 week data is around 10...

adding a prefix to a column in an SQL view - Hi all, Is it possible to add a prefix to a returned result in a view in sql. eg select productId from...

Plan Reuse - Unrelated tables in execution plan - Hi all I have been banging my head against a brick wall over this one. Hopefully somebody can help. One our users...

Indexes with JOINs - Hi all, I have some knowledge about indexes but it's very fuzzy when the queries become more complex, where upon...

SSIS-Error in FTP task - Hello All, Iam getting the below error when i try to run a SSIS package through SQL server job. 'FTP task cannot...

get overtime hours - i am have a problem to get overtime hours the table EmployeeNo TimeIn TimeOut BreakHours WorkingHours 001 06:20 19:00 1 8 how query to...

job fails with linked server - Hello everybody. I'm getting an error in a job that I can't explain myself. I have two servers: ERPSERVER and STAGINGSERVER on STAGINGSERVER...

In my laptop some times sqlserver getting problem while connecting in to the instance - HI all , In my laptop some times sqlserver getting problem while connecting in to the instance 1. it is unable...

Performance Tuning - I have a Table called Employees with following Columns EmployeeID INT EmployeeName VARCHAR(256) Salary MONEY DeptID SMALLINT The table has...

PBM(Policy Based Management) Implementation issues - Hi My policies just noticed that Public has the 4 default Connect permissions on endpoints. To get rid of them I...

xp_cmdshell slow response - I am using xp_cmdshell to bcp out a small table to a file and than copy it inside the local...

Error on SQL job - SQL 2008 - Hi, Can someone help us to determine the resolution for the encountered error below. Message Executed as user: HO\sqlibg. The instance of the...

How to know the original user name - Hi all, I'm having hard time to figure out a way to create a DDL trigger while capturing the original user...

Ghost Cleanup and CHECKPOINT after Migration to SQL 2008 R2 - I am migrating a database from SQL 2005 to SQL 2008 R2. As soon as the database is restored over...

read values in column from xlsx files in SSIS - is it possible to write a code that will read the values in a specific column from xlsx files in...

Update statement really slow but only on the first execution - Hi, I have a standard update statement that updates one row at a time. The table size is 340,000 rows, so...

HOW TO MAKE DYNAMIC PIVOT FOR THIS TABLES WHEN I PASS ONLY ONE PARAMETER @DID INT? - here iam having two table @DETAILS and @CONTACTDETAILS in this two table @DETAILS is a static table in which cotactid...

Compare scripts B4 and After a Performance Fix ! - "How can I"/"what are all the ways I can" compare a query/SP before and after a Performance Fix, to know...

Strange datetime conversion issue - I have been using a query that joins several raw tables (nvarchar datatypes), has several conversions, nested queries, CASE WHEN...

Count number of visits by week, ending on Saturday - I am trying to get a count of visits by week with the week ending on Saturday. My problem is...

Is there a better way to insert data than Access? - Is there a better tool to insert data into SQLserver than Access ? For managers. Small number of people. Very limited...

Weekly report through whole year - Hi there I have 2 tables and sample data: -------------------------------------------------------------------------------------------------------------------------------------------------------- CREATE TABLE [dbo].[tblProduct]( [ProductID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NOT NULL, CONSTRAINT [PK_tblProduct] PRIMARY...

DBCC on a detached MDF file - Hello, I am by no means above a SQL novice, but I am trying to figure out if there is a...

Trace Flag 8048, 8015, or SUMA? - Hola! Recently included SQL Server startup Trace Flag 8048 to resolve a serious spinlock contention issue in a SQL Server...

SSIS Transfer SQL Server Object Task - Hello, all! Due to database corruption referenced in a previous post, I am attempting to transfer all objects and data out...

SSIS and VS 2010 - I got my fresh new copy of VS 2010 yesterday and immediately began playing. I want to build an SSIS...

SQL Server 2008 : T-SQL (SS2K8)

Is there a TVP equivalent to an optional parameter? - I'm trying to help my client solve an issue with frequent "breaking changes" to procedures that use table-valued-parameters. The calling...

avoiding duplicates in comma separated values column - Hi Guys, I am working on stored proc to update master table from temp table and getting following issue. [b]temp_table[/b] sa, Logger,...

TOP 25% rows by Weightage - I have a table as below. I need to find the members which are in the bottom quartile (25%) of...

Show Results When more than just my result shows up. - Hey Guys, I'm trying to Show All location changes in our system in the last 5 days, now all active...

Trigger that collects data from two tables - Hi, Is it possible to create an After Update trigger on table A that can gather both the delete and insert...

Multiple DMLS in one when clause of MERGE Statement - Hi All, I want to use 2 DMLS in When not Matched by target clause ie. first insert and then update.But...

CTE - UNION ALL - GROUP BY - Today I got a mail from one of our developers. It is a little complicated to explain and I cannot...

SQL Server 2008 : SQL Server Newbies

Problem with joins - Hi, I need a help. i have 5 tables defined below:- these tables are used to record all data pertaining to emails. Emarketing_email_history : message_stub,acct_id,.... Survey_email_history...

Backup overwrite - Hello Masters, Is it possible to know when last backup was restored on specific database ? Is it possible to know if...

Not all Databases getting backed up - I have a maintenance plan that makes a full backup of 45 databases every night, at least it supposed to....

How to add variables to a table in SSIS - Hi all, I'm in need to import several flat files into a SQL table. I'm used to importing one at...

not able to access databases - hello masters, My servers are on virtual environment (SQL SEVER 2005), some one rebooted storage server and now I am able...

Backup failed - Hello Masters, My daily differential backup job failed for user databases. all the user database are in simple recovery model. Below...

SQL Server 2008 : Security (SS2K8)

Stored Procedures Execute Permissions - My global office programmed all our stored procedures and it contains a lot of create/delete temp tables, truncate tables, update,...

SQL Server 2008 : SQL Server 2008 High Availability

Oracle Virtual Machine and SQL Server 2012 Clustering - Hello All, My current project is running on Oracle Sun Fire x2-8 server and they have brought me in to...

DB Movement - DB movement activity on cluster server... What are do's n dont's??? The Cluster environment is well set & operational.. So Only Moving DB's to...

Installing SPs & CUs in Clusters - [url=http://support.microsoft.com/kb/958734]This Knowlege Base article[/url] describes the procedure for installing Service Packs & Cumulative Updates on SQL Clustered instances. Is says that before...

MS-SQL failover instance did not start after a planned failover/hotfix installation. - Ok, I am running SQL2012 on Windows 2008R SP1 servers. Planned a short downtime to apply KB2718576 on my SQL Cluster,...

Changing Owner or Mirrored DBs - How would I go about changing owners of a database that are within a mirror? Jonathan

SQL Server 2008 : SQL Server 2008 Administration

DBcreator - We have a build process , the build account is a dbcreator, after it create the database, it cannot access the...

service pack - I am using SQL server installation to repair an exiting crashed SQL server 2008 Sp3 My question is after I do...

Issue with Linking Servers - This is my current situation. I have job scheduled to run every morning that collects data from various servers to...

Min and Max server memory - Am a junior DBA and We have an issue from last couple of days. We have a server of Windows...

Data Warehouse Backup Strategy - We’re migrating from SQL server 2000 to 2008 and I’m trying to come up with a backup strategy for our...

Why powershell? - Hi all experts, I have being reading about Powershell a lot this days. What i came to know about powershell is...

Moving server to different zone in Datacenter - I am involved in moving some servers to different zone within the same data-center. There are no IP changes, so...

CU installed or not? - We single node cluster running SQL 2008 R2 SP2. We noticed the Application Even Log contianing thousands of messages like Information...

Report Services / Upgrade from Standard to Enterprise - Hello - I am trying to do a an upgrade from Standard to Enterprise using the SQL Server Installation center (Maintenance...

Transactional replication issues - Hello There, I am new to this forum, Could anyone help me with this questions please. I am working in retail industry and...

Have upgraded from 2005 to 2008R2 but still getting security updates for 2005 - Hi there, We've recently upgraded a 2005 SQL cluster (active / active) to 2008 R2. There still seems to be SQL 2005...

Reindex task taking for ever - SQL Server 2008 R2 - 10.50.2500. We have a Database, about 35 GB in size, which has got several tables and...

Programming : XML

Query XML which has multiple child and subchild - Hi , We have a requirement in which we need to Query an XML which has child and sub child(structure mentioned...

Programming : Powershell

Can't script the schema for a FK - Hi, I use the below script to script out FKs in a database: #DECLARE TIMESTAMP FOR THE FILES $timestamp = Get-Date -Format yyyy-MM-dd #SCRIPT SL SQLSERVER:\SQL\'MyServer'\DEFAULT\Databases\'MyDB'\Tables $so...

SQLServerCentral.com : Anything that is NOT about SQL!

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

Issue with link to report viewer - Hi community Has anyone ever experienced this before: I have placed a link to a report I created which looks like [url]http://myserver/ReportServer/Pages/ReportViewer.aspx?%2fScelo%2fOn+Demand+Billing%2fTime&rs:Command=Render[/url] Now...

Report not working after concatenation of fields. - Hello everyone, I am having problems running a report after making a change to concatenate two fields. I have a report with...

Reporting Services : Reporting Services 2005 Administration

Deploy SSRS reports - HI, I'm trouble to deploy report to my local server at home, I'm receive message [Reporting Services Login] box. I login with...

Site Settings and Security Tabs are missing from Reports Manager link - To the SQL forum readers. From our SQL server 2005 Reporting Server page the tabs for Security and Site Settings are missing....

Database Design : Design Ideas and Questions

films database (yeah, yeah, hear me out!) - I've done a fair bit of Googling and asking dba friends, but I wanted to see what some of you...

Temporal Data - Have you experienced the need for analyzing data temporally in your business? Do your existing information management systems allow you...

Data Warehousing : Integration Services

Excel Source DT_NTEXT Problem - i am trying to load the data from excel files and facing problem in Text data. Excel source in SSIS tries...

how to check whether in the given column we have values in date format - Hi, I have a table like the one mentioned below. Eid Variable 1 na 2 none 3 4/18/2013 4 18/2013 5 2013 6 9999999 I need to check for eid where variable column has data in...

Data Warehousing : Analysis Services

Retrieving the ALL member only - Hi, I have a problem with retrieving the ALL member. With MDX executed from SSMS, it returns the 'All' value into a...

Simple MDX query - Hi, I'm trying to something that I think should be straight forward in MDX but i'm struggling. I'm new to MDX...