In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Source Control Is your SQL Database under Version Control?
SSMS plug-in SQL Source Control connects SVN, TFS, Git, Hg and all others to SQL Server. Learn more.
 
SQL Monitor Understand Locking, Blocking & Row Versioning
Read Kalen Delaney's eBook to understand SQL Server concurrency, and use SQL Monitor to pinpoint excessive blocking and deadlocking. Download free resources.
 
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.

In This Issue

Using a TSQL semaphore in SSIS

This article shows how you can use a TSQL semaphore in SSIS. More »


Developing a SQL Server Backup Strategy

Developing a backup procedure that makes sure you can do restores should be one of your top priorities as a DBA. In this article Greg Larsen discusses some of the concepts to consider when developing, building and testing a backup strategy. More »


From the SQLServerCentral Blogs - Observe Carefully When Things Are Running Fine

Cross-posted from The Goal Keeping DBA blog: As a worker in information technology, I’ve always been a big proponent of studying systems... More »


Editorial - Global Insecurities

Reading this article about the insecurities found from various scan in 2012 is a little scary. I wonder how many of my former employers have videoconferencing systems, remote control/access systems, or some commercial software with a default password connected to the Internet and unsecured? Reading the article I'd bet at least one of them does.

I also wonder how many of them have old versions of software with known vulnerabilities that can be exploited, not just by dedicated hackers, but by script kiddies. Lots of people have excess time available, powerful computing resources, and mischief in their hearts.

This is slightly maddening to those concerned about the security of computer systems. How hard is it to change the default passwords on the installation of an application? How much more time does it take to configure a system properly? It doesn't' take much at the moment, but it does take time in advance. Proper security requires knowledge, which means that an administrator much have spent time learning how to properly configure a system, or getting a comprehensive list of vulnerabilities and their patches.

I'd love to see vendors publish a best practices document, or a couple of them, for each version of software they release. Give people specific steps to follow on the installation of the software to ensure it is securely configured as well as known vulnerabilities and the patches available. I can publish information, and there are likely any number of blogs out there that my give some best practices, but for new users, the vendor's site is the only resource that many people will follow.

I know I'd be willing to allow vendors to link to any best practices I published, or republish the information on their own site if they wanted to. I'm sure others would feel the same way. Now if only the vendors would agree to use the information.

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

Using SQL SERVER 2012, I have created the following table

CREATE TABLE #Leaders(title INT,Name VARCHAR(50),Score INT) 

The #Leaders contains the following data

1,'GilaMonster',35452

1,'Jeff Moden',31783

1,'Steve Jones',30901

2,'RBarryYoung',9851

2,'Koen Verbeeck',8842

3,'Matt Miller', 6980

3,'WayneS',6284

4,'someone',3657

I then execute the following T-SQL

SELECT CHOOSE(Title,'SS Champion','SS Crazy Eights','SS Certifiable','Hall of Fame') AS Title 

,Name,Score 

FROM #Leaders

The question is: Is the value of Title returned as an integer or as characters ?

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

This question is worth 1 point in this category: T-SQL. We keep track of your score to give you bragging rights against your peers.

We'd love to give you credit for your own question and answer. To submit a QOD, simply log in to Contribution Center.

Microsoft SQL Server 2012 Master Data Services

Harness your master data and grow revenue while reducing administrative costs. Thoroughly revised to cover the latest MDS features, Microsoft SQL Server 2012 Master Data Services, Second Edition shows how to implement and manage a centralized, customer-focused MDS framework. See how to accurately model business processes, load and cleanse data, enforce business rules, eliminate redundancies, and publish data to external systems. Security, SOA and Web services, and legacy data integration are also covered in this practical guide.

Get your copy from Amazon today.


Yesterday's Question of the Day

How you find records that exist in one table and do not exist in another table?

Answer: all of them are correct

Explanation: References: http://msdn.microsoft.com/en-us/library/ms184297(v=sql.105).aspx
http://msdn.microsoft.com/en-us/library/ms177682.aspx/a>
http://blog.sqlauthority.com/2008/04/22/sql-server-better-performance-left-join-or-not-in/

» Discuss this question and answer on the forums

SQL Server 2012 Data Integration Recipes

SQL Server 2012 Data Integration Recipes provides focused and practical solutions to real world problems of data integration. Need to import data into SQL Server from an outside source? Need to export data and send it to another system? SQL Server 2012 Data Integration Recipes has your back. You'll find solutions for importing from Microsoft Office data stores such as Excel and Access, from text files such as CSV files, from XML, from other database brands such as Oracle and MySQL, and even from other SQL Server databases. You'll learn techniques for managing metadata, transforming data to meet the needs of the target system, handling exceptions and errors, and much more.

Get your copy from Amazon today.


Featured Script

Finding actual number of physical cpu installed

The below query finds out actual number of physical cpu installed on the 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

Full Backup - Restore point in time. - Hello All, Is it posible to restore to any time in point in restore. i am getting issues if i have...

How can we secure backup files ? - Edit Sorry.. I din't want to ask this question as I had got answers earlier. Not seeing the option to delete the...

Generate Update statements for an existing table - [b]Requirement:[/b] A SQL Script that can be used to generate the 'Update' Statements for a Table which has data I found a...

SQL Server 2005 : Business Intelligence

Update Recordset with foreach loop - Hello all, I am very inexperienced with BI and SSIS. Here's what I am doing, I have an OLE DB...

How to handle mutiple outputs in xml source - Hi, I'm trying to load xml data into sql table using xml source, the file has around 6000 fileds. When i...

SQL Server 2005 : SQL Server 2005 General Discussion

SQL ADVANCE COPY/MOVE AND RENAME FUNCTIONS - Dear Experts, I am kinda stuck into a situation where i am planning to automate one manual process. On the daily basis...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Plan Usage for stored procedures - Hi, I've been running the following script to capture plan usage stats. [code="sql"]SELECT DB_NAME(st.dbid) DBName ,OBJECT_SCHEMA_NAME(st.objectid, dbid) SchemaName ,OBJECT_NAME(st.objectid, dbid) StoredProcedure ,MAX(cp.usecounts)...

SQL Server 2005 : SQL Server 2005 Integration Services

SSIS Script task to change table name in data flow task - Hello, We need to change the table name of 'OLE DB Destination' task. I am not able to use variable due to...

Issues With DB2 CHARACTER Field Source - SSIS 2008 - I don't see a forum for SSIS 2008, so I guess I will post here?? I have an OLE DB connection...

Creating Header AND Footer to a flat file destination file - Hi Guys, I am working on a financial project; the requirement is interface flat file should not contain any duplicated arrangement....

SQL Server 2005 : T-SQL (SS2K5)

Test results report - I am trying to generate a report showing which questions were missed on an online test for a given student....

SQL Server 7,2000 : Data Corruption

Logical consistency error - Hi, Often am getting logical consistency error in one my production server. Recently i did DBCC with allow dataloss option, to...

SQL Server 2008 : SQL Server 2008 - General

Does Change Data Capture require a table have a primary key - or can it record before and after column changes based on the LSN only? An extract from a file based legacy...

Bulk Inserting Comma separated Quotation Delimited text file - I am receiving a text file with comma separated data with quotation marks around certain fields that I need to...

four part naming not woprking - select * from linkedserver.database.dbo.table where 1=2 Returns: Non-interface error: Invalid schema or catalog specified for the provider how can we get around this? Thanks

Database Data File Physical Fragmentation From Small Auto-Growth Setting - To All, I need some advice. I've just started a new job and one of the first problems I've encountered is...

Problem with using SQL Server linked server to MySQL database - I have a MS SQL Server that does a lot of data manipulation and staging and then I need to...

Transaction log SHRINK - Hi, There's been lots of posts about shrinking databases so sorry to tread over old ground but I have a fairly...

SQL Security Best Practices - Dealing With User Name Changes - Are there any Best Practices from Microsoft or anyone else on how best to deal with SQL Logins & DB Users...

Multiple outputs from XML Source - Hi, I'm trying to load xml data into sql table using xml source, the file has around 6000 fileds. When i...

SQL Query - Hi, I have an issue with the SQL query I have created. The result that this query should generate should be...

what is Scalability of Database - What is scalability of a database.I have googled it but I didn't get exact answere.

Local System Account=fast, domain acct=slow - I am an ERP software specialist. I have installed SQL Server 2008 R2 at several sites and generally have MSSQLSERVER service...

Replication without need of Snapshot - Hi, I hope someonecan help me with this. We have a production server with a VLDB on it (~ 7 Billion rows, ~ 4...

Passing Object Type Variable to Child Package - Hi Friends, I am deriving a result set in a parent package and I have to pass the same to the...

Question about Optional Parameters in Stored Procedures - Is this the standard way to deal with optional parameters in SP? [code="sql"] CREATE Procedure AddMonth ( @EnterMonth as int, @product int = null ) AS INSERT INTO X SELECT...

Trigger Help - Hi, I want to create trigger when i fore below code it should keep track of it.. SP_CONFIGURE 'SHOW ADVANCED OPTION',1 RECONFIGURE go SP_CONFIGURE...

How to rename the file on the base of the query output in SSIS - Hi All, I want to rename the file on the base of query output in SSIS. Example: File name: report.txt Query: Select MAX(Batchlog) from...

SSIS Packages - Best Practice - Folks, I'm new to SSIS (2008 R2), and I've developed a handful of packages and want to know if I'm "doing...

DB Block Size - What is the recommended block size of operating system on dss systems? Also what is the recommended block size of oltp...

Index rebuild fragmentation criteria confusion - There is tons of info regarding selective index rebuild/reorganize (with magic numbers 10-30%) depending on output of DMV sys.dm_db_index_physical_stats. But...

No Targets Found (policy Based Management) - I have successfully configured a Group of 2 servers in Central management . when I evaluated 1 policy against that group....

Cannot login to Analysis Services 2008 from SQL Server Management Studio - Last week I installed SQL Server 2008 developer edition with service pack 1 on windows 7 Home Premium. Before install...

SQL Server 2008 : T-SQL (SS2K8)

Using a function on SELECT error - Hello comunity I have create this function to return total pairs: CREATE FUNCTION [dbo].[fnQtRefCor] (@bostamp varchar(25), @ref varchar(20), @cor varchar(50)) RETURNS int AS BEGIN DECLARE @retval int SELECT...

SELECT vs INSERT INTO - I've been searching for pointers on SELECT..INTO vs INSERT..INTO around forums and the web and haven't been successful yet. Basically,...

Flattening a Parent Child Hierarchy - Hi, We are starting work on a BI project that is given data in a traditional Parent-Child Hierarchy format. The Problem is...

SQL Server 2008 : SQL Server Newbies

ORDER BY non-sequential number sequence - I have a table where a certain column contains a status id as an integer. I want to sort on...

Create Table and Bulk Insert - Hi guys, I have a .csv file wich has the following information (exactly like this): List Name: User Format Version:1.2.5.0 Date:12/11/2012 12:00:34...

SQL Server 2008 : SQL Server 2008 Administration

SQL job error - Anyone have any idea why I receive this error on SQL job? Step ID 1 Server SRIN_SVRA Job Name 1 Year Backup Maintain BDSBPM.Subplan_1 Step Name Subplan_1 Duration 00:00:34 Sql Severity 0 Sql...

how do I know files are backed up in tapes - We have maintenance plan setup for back ups. But we also have tape backup for the backup files. My question is from...

SQL Server Not Using All Allocated Memory - I am running SQL Server 2008 R2 on Windows 2008 R2 enterprise Edition with 64GBs of RAM. I have set...

2008 R2 SP2 Install fails with no messages - I'm installing SP2 on my 2008 R2 servers and it has gone well for the most part. However, I have...

Reporting Services : Reporting Services

Report Parameters Selected Shown in Report and/or Footer - Hi, I'm reasonably new to Reporting Services and still learning so A) bear with me and B) please excuse any...

Database Design : Design Ideas and Questions

Conditional joining of Table A nested ID/heirarchyid to table A or B? - It's been a long time since I've dealt with complex database design, and in this case, I'd like to ask...

Data Warehousing : Integration Services

Script Component and Querying Database - I need help understand how to use the Script Component to create a connection to the database, submit a SQL,...

Extract only numbers from string which contains special characters too - Hi, i have a column called phone numbers whose datatype is varchar(50) that means it contains integer values, string values, special...

logging output from Execute T-SQL task - I have a Execute T-SQL task which purged records from table. I would like to record in a log file...