In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL in the City London 2013 SQL in the City London - Free SQL Server training the Red Gate way
SQL Server MVPs, ingeniously simple tools and an awesome training experience. Find out more and register for the next event happening in London this June.
 
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.
 
SQL Prompt Make working with SQL a breeze
SQL Prompt 5 is the effortless way to write, edit, and explore SQL. It's packed with features such as code completion, script summaries, and SQL reformatting, that make working with SQL a breeze. Try it now.

In This Issue

Adding Rows in Incremental Loads - Level 3 of the Stairway to Integration Services

Learn how to incrementally load new rows into a database from an Integration Services package. MVP and SSIS guru Andy Leonard continues his series on learning SSIS. More »


SQL Saturday #197 - Omaha

Join us on April 6th in Omaha for SQL Saturday #197. SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server.  More »


Using the SQL Server APP_NAME function to control stored procedure execution

In this tip we will look at one way to achieve control of stored procedures to ensure that reuse is for the intended purpose and changes do not break other applications that may be using this same code. More »


From the SQLServerCentral Blogs - We Need a Place for SQLFamily News

I’m writing this because I was thinking about a member of the local PASS chapter on the way home last... More »


From the SQLServerCentral Blogs - PowerShell Usage in DBA Work – Case Study 7/7

Reading/scanning SQL Server Logs is a required DBA work item in all my work environments. I know there are lots... More »


Editorial - The Command Shell

Recently I heard a few people arguing over the use of xp_cmdshell in a particular situation. One person was adamant that there was a security risk in using this feature. Many of you probably feel the same way, and even the SQL Server platform has recognized there could be dangers with this feature and has it disabled by default, as part of the secure by default installation.

However the security around this procedure has been improved over the years. Non system administrators cannot execute xp_cmdshell by default. Administrators can open up access using a proxy account, but this requires specific configuration changes by administrators. This means that a lot of the danger of using xp_cmdshell for administrative tasks has been removed.

Or has it? This Friday I wanted to poll you and find out what you think. Many of you are creative in how you use SQL Server and will think of possibilities that many of us would not consider.

Is there a security risk in allowing xp_cmdshell to be used by members of the sysadmin role?

I'm not looking for potential issues if a proxy account exists. Instead I'm asking if there are real dangers in allowing administrators to use this tool? I assume you trust your administrators and they will not maliciously use this tool to cause issues in your SQL Server. Let us know how you feel this week.

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

declare @a char(5), @b char(5), @c char(5)

set @a = 'abcde'

set @b = ' ab'

set @c = 'ab '

select LEN(@a), LEN(@b), LEN(@c)

What will be returned by the select statement?

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

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

SQL Server Execution Plans

SQL Server Execution Plans shows you what's going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by SQL Server, including: Which indexes are being used, and where no indexes are being used at all. How the data is being retrieved, and joined, from the tables defined in your query. How aggregations in GROUP BY queries are put together. Grab your copy today from Amazon!


Yesterday's Question of the Day

Will this code work correctly, meaning the table gets truncated? Or is there an error for the reference of Col_2?

CREATE TABLE dbo.Test
(
     Col_1 INT PRIMARY KEY
    ,Col_2 INT REFERENCES Test(Col_1)
    ,Col_3 INT IDENTITY
)
;
INSERT INTO dbo.Test VALUES (1,1);
INSERT INTO dbo.Test VALUES (2,2);   
INSERT INTO dbo.Test VALUES (3,3);   
GO
TRUNCATE TABLE Test;
GO

Answer: The truncate table runs successfully.

Explanation: You can truncate a table that has a foreign key that references itself. Actually, I find that most of people don't know about this. They think if a table contains foreign key, you can't use truncate on that table.

Ref: http://msdn.microsoft.com/en-IN/library/ms177570.aspx

» Discuss this question and answer on the forums

SQL Server Execution Plans

SQL Server Execution Plans shows you what's going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by SQL Server, including: Which indexes are being used, and where no indexes are being used at all. How the data is being retrieved, and joined, from the tables defined in your query. How aggregations in GROUP BY queries are put together. Grab your copy today from Amazon!


Featured Script

Copy Sql Objects

Copy tables, procedures, functions and view from one schema and another schema. 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

Database User and Roles Script Extract Needed - I'm looking for a script that extracts the code to create users and granted roles per database. I've found partials...

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

I cannot import SSIS package into MSDB folder. - Hi all, I am working on SSIS and created integrated services project and saved them under a new folder. I want to...

SQL Server 2005 : Development

Split strings alternative to XML - Hi guys, For some performance considerations, we would like to try alternative to the code below. Anyone could help on how to...

SQL Server 2005 : SQL Server 2005 Strategies

SQL 2005 Mirroring Power Loss (No Witness) - Hi We have a basic SQL Mirroring setup with 2 servers with No Witness or Automatic Failover. I have some questions about...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Reorganze index and Statistics - Looking for some clearer answers here... we have quite large databases 1TB+ and we are doing nightly Reorganize of our index....

Indexes with Include - I am performance tuning a database for a client. I have used several methods to identify tables that could use...

SQL Server 2005 : SQL Server 2005 Integration Services

SSIS Child package file locking issue - I'm calling a child package in a for each loop from the parent, the child loads data from a flat...

SSIS unique records load and keep the duplicate records in another text file - Hi , I have been given an task to load a text file to a database which is simple but the...

Foreach Loop (Text Files as Flat File Source and Excel files as Excel File Destination) - Hi, I'm pretty new to SSIS. Is it possible if I already have those existing text files (a_points.txt, b_points.txt, c_points.txt) and...

"Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT' could not be opened. Operating system error code 5(Access is denied.). - I've created a set of SSIS packages which run fine in our test environment. The packages access a SQL server...

SQL Server 2005 : T-SQL (SS2K5)

Is it Possible to renumber a Identity Column? - hello all, we have a table that is fairly large and get many inserts and deletes.. and basically the identity column...

SQL Server 7,2000 : Administration

Buffer cache hit ratio ( week) - Hi everybody , I need your help please : I want to create a graph to show the progression of the "buffer...

SQL Server 7,2000 : Working with Oracle

mutation in Trigger - Hi Team, Am a sql developer and new to oracle, i was assinged a trigger for am having two tables Table1 : Test Cols : ID...

SQL Server 7,2000 : T-SQL

Using CASE in real world examples - Hello, I am new to this forum. I am using Microsoft Access 2010, which includes SQL. Here's my problem: What I want to do...

SQL Server 2008 : SQL Server 2008 - General

select case with multiple - Here is my scenario, The user puts in a customerID, possible start and end date and selects a status from...

add image dynamically in crystal report - hi i want add image dynamically in report

skipping reboot check while installing sql server and remove command line appearing during install - While i am installing sql server 2008r2 by using configuration file sometimes i am getting error in instillation due to...

Alerts not firing - I have created an alert that listens for a specific error. On that error being thrown, the alert is supposed to...

I need to write a SQL SELECT stmt that puts the results out to a CSV file in SQL 2008 - I need to write a SQL SELECT stmt that puts the results out to a CSV file in SQL 2008....

Covering Index help - Is there a difference between a 'covering' index and an index with 'included' columns - or is that exactly what a...

Batch job to update a SP on independent machines - We have 20+ machines that are running SQL 2008 Express. We need to add a SP & table and update existing...

some one write query for this problem - I have a table that contain a row table name = PMInfo pmid,member 1, jay-raj-kit i want output like that 1 , jay 1 , raj 1, kit Regards Arjun.

CDC and Change Tracking - Hi, What are the difference between CDC and Change Tracking? Which is more efficient and less resource consuming. Is there is a...

Settings for All sprocs - I want to SET NOCOUNT ON For all Sprocs in my db. Is this a manual task or can this be...

2 couloms from 2 dates - I have rows of 2 dates and data i would like to sum one date into one Coulomb the other to...

How to find SSIS Version - Hi Can any one please let me know how to find the version of SSIS that is runnning on the server. Many...

code for store image in sql - hi i want to store image in sql

Publication and Subscription issues over 2 domains on WAN - Good Morning Ladies and Gents, I am struggling to win with Publications and Subscriptions. The situation is as follows, A client of...

how i can save image in sql server 2008 - hi every one i am using vb 6.0 and i want to save my image in sqlserver 2008 how can i...

Find out The column list which are referenced by paricular table in stored procedure using DMV - How to find out The column list which are referenced by paricular table in stored procedure using DMV

There is insufficient memory available in the buffer pool. - Please help me. I m try to import data from csv to table using bulk insert but it gives me insufficient...

Find a word in a String - How do I find a word in string? That word stands by itself In this example, CD is the word, it...

Error in application when started by SQL server agent - I' running SQL server 2008R2. I have a C# app that accesses a database and then constructs and sends an...

how to group using cases - hi, i need to group people who weight more less than 200 ordered by age (from the oldest to the youngest),...

Existing While Loop - I'm looking at some code from a project that is not performing very well, and I've noticed that they have...

Space between parameters in reporting services - Hi, I am stuck with an issue which relates to managing space between parameters. I have a multi-value parameter with values...

The delivery extension for this subscription could not be loaded. - Hi All, I have a single SSRS report which is set with a daily (weekday) subscription. Randomly I get the...

SQL Query Confusion - Hi, I have 2 tables Table Member with columns A B C D apple bread carrot Chocolate Mango Begal Onion Candy Table Util Columns A B C...

data design considerations - Scenario is that I want to have a primary key to identify a new entry in a main table. Call...

Optimal values needed for Server - Hi, Can any one tell me what these items are total cpu usage,context switches/sec,paging rate,network bandwidth utilization,disk queue length .Under what...

Enabling Trace Flag T1118 - Hi, We are using Sql server 2008 R2 express. We heard enabling T1118 is good for tempdb contention.Somewhere it said...

Propagate view changes. - For security reasons, we have exact same view ( with same name) in different schemas based on different clients. Now if...

Is DBCC CHECKDB an appropriate weekly check for a 365x24x7 availability db? - We have a db that has a mission critical website sitting on top of it. We check it weekly with...

Monitor SSIS Job - We have a SQL Job that calls an SSIS package. This package used to take about 10 minutes to run....

Migration - Any article/guidance/experiences on Migrating application data from Iseries to SQL Environment using SSIS as well as wherescape? thanks.

sys.objects$ (with a Dollar sign) - I was wondering why there wasn't a sys.functions view similar to sys.procedures,sys.tables and sys.objects. if you run [b]exec sp_helptext sys.procedures[/b] in...

ssms connect to database engine hangs - After installing Oracle 11g client on our server I can no longer connect to SSMS database engine. I can connect...

MS SQL + MS Visual Studio VS Wherescape RED - Have you ever heard of or used Wherescape RED to do the data warehouse developing? The fact is it is...

SQL Server 2008 : T-SQL (SS2K8)

Combine multiple combinations to one - Hi, The following test condition : [code="sql"] IF object_id('tempdb..#testEnvironment') IS NOT NULL BEGIN DROP TABLE #testEnvironment; END; CREATE TABLE #testEnvironment ([Sample_ID] INT, [Rep_ID] INT, [Result] VARCHAR(20)) INSERT...

Just a random observation with 'WITH' - Following is my observation with 'WITH' I executed the below query [code="sql"]SELECT * FROM Sales.SalesOrderDetail (NOLOCK)[/code] Which run sucessfully now i execute the below query [code="sql"]UPDATE...

Update Help.. - Hi Guys, Need favor. Here is the e.g. I am using TABLEA.ID (as an e.g) in my SP.I am not...

How do i line up the weeks in T-SQL ? - How do i line up the weeks ('Weeks-2013' and 'Weeks-2012') in T-SQL ? Below is sample data to test with: SELECT DISTINCT...

Reorder a custom order column - Hi, Been stuck on this for a little while and hoping that someone can help me figure out my logic problems. So...

Return 1 record with data from multiple tables - All, I have data in numerous tables. The first 2 tables are simple: tblProducts ----------------- ProductID ProductName CategoryID tblCategories ---------------- CategoryID CategoryName Each product links to a category. That part is...

Performance ISSUE by USING CURSOR HELP !!!! - Hello Mate, I have the following TSQL but I meet a performance issue on Phase 3. Phase 1 and Phase 2...

SQL Server 2008 : SQL Server Newbies

Error authenticating proxy - Hi, I've got a SQL Server 2008 job which ran ok yesterday but has fail today with an error message ' Error...

SQL Logical errors - Hi All, I need some one help me identify why am getting logical errors in the output of my query below....

Columns to be included in Index - Dear All I have one table with millions of rows, with columns as Id, stringcol1,stringcol2,Bigintcol3,numericcol4 Primary key is Id. Index created on stringcol1 When...

New San/Compellent testing - I need to create a query that will execute for 20 minutes or more so that connectivity and redundancy can...

Reporting Service errors when deploying - Thanks in advance for any help... I have SS2k8R2 Developer with reporting and analysis services running. I'm learning to use Reporting...

SQL Server 2008 : SQL Server 2008 High Availability

Inserting and Retrieving images from SQL Server Database using vb6.0 - hi i am having a one form in vb. i want to store and retrieve image in sql server 2008 ?? regard, gamit

SQL Server 2008 : SQL Server 2008 Administration

Restore verifyonly on the last backup - Hi , Somebody can Help me : I want a script to use 'restore verifyonly' command on the last full backup of...

SQL Server access violation - Hi All, My sql server crashed and I see the following error in my error log. I browsed the net got...

sql2008 port number changed - not able to connect after changing the port number.

Buffer cache hit ration ( for week ) - Hi everybody , I need your help please : I want to create a graph to show the progression of the "buffer...

Availability and (simulation) using backup ( SQL SERVER) - Hi everybody, I want to know if the last backup is physically accessible and know what is spend if used...

Server doesn't accept connections from the web! - I am making a VB.Net app that accesses my SQL Server, but when I try to run the app from...

transaction log size reduction - Does backup of transaction log reduce the log file size or it is DBCC SHRINKFILE really make the size small? I...

Crippling high RAM usage, Googled SQL buffer-related usage doesn't show the issue - I have an SQL Server 2008R2 server instance that is hitting the RAM ceiling and crippling the server. - Server has...

Migrating Maintenance plans - Hi, Is it possible to migrate maintenance plans from 1 server to another server? SueTons.

Catching culprits of high tempdb growth - Hello, We are facing issues with tempdb on our SQL server 2008 clustered instance. The SQL version is Microsoft SQL Server...

Loading Users into instance security - We are moving from a server with SQL server 2005 to one with SQL server 2008. Is there any way...

Find SPID of Linked Server Call - Hi SCC, I have a stored procedure on server A that makes a linked server call to server B.... on server...

Monitoring error - I'm using Red-Gate Monitor tool to help me in managing our SQL Server. I see very frequently these two messages:...

Career : Certification

Upgrading from 2008 to 2012 - Hi, I have the MCTS in SQL2008 and want to upgrade to 2012. Currently I have been unable to find...

Load simulation - One of the Exam topics I find difficult to prepare for is performance tuning. The performance of any SQL instance...

How to study for MCSA SQL Server 2012 - Hey guys, I just decided upon a career change at the end of last year and want to become a...

Slave to 70-450!! - Hi all, I'm in need of some advice from some learned peeps regarding study material etc for my 70-450 exam. I've...

SQLServerCentral.com : Anything that is NOT about SQL!

What does Microsoft Update do? - For MS SQL Server, what does the Microsoft Update tool actually update? I had assumed that it would update the version...

So How Do You do your mass Mailings? - Once a year I help out a local non profit promote their annual event. This is the third year for...

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

add image in report - hi i have three reports and three images and i want to add that three images in that three reports how...

Format Date - hi I wanted to put a parameter by default DATE of yesterday? How do I create the Format

Basic Expression - Hello, I am using expressions for the first time in SSRS. Eventually i want to design a dashboard. I am testing an...

Reporting Services : Reporting Services 2005 Development

Error: "The item '/Data Sources' already exists."?! - Hello, I'm the new guy that was tasked with creating a new report. I've created the report and viewed it. It looks...

Repeating TABLE Header on each page of the report when you print the exported to excel report. - Hi, I have generated the report and looks fine. But while printing the QA Smile wants the report headers(TABLE HEADERS) need...

Data Warehousing : Integration Services

Corrupted File is getting processed - Hi, I am getting an issue while loading the data from .csv file to SQL Server table. My pacakge has flat...

64-bit driver for Excel - My understanding is that the file structure for the 64bit and 32bit versions of Excel are the same in Office...

Developing SSIS Packages - I'm tired of typing this question into Google and getting 1,389,456 wrong answers. So I thought to myself, "Why don't...

Data Warehousing : Strategies and Ideas

Experiences using Wherescape Red on SQL Server? - I am struggling to find much content out online so I was wondering if anyone wanted to share their experiences...

SQL Server 2012 VLDB Data Warehouse Design Question - Appreciate feedback to this question. I'm working on a design for VLDB that will be in the several TB size...

Data Warehousing : Analysis Services

adding columns - hi, i have an mxd query that returns the data for yesterday (dynamic). what do i need to do to return...

Sum and Group Results - Date Comparison - I am working on a MDX Dataset from a TFS2010 Cube (I Have also posted on the Team Foundation Server...