SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

Featured Script

The Voice of the DBA

Secure Programming

This editorial was originally published on Mar 19, 2009. It is being re-run today as Steve is away at DevConnections.

Writing secure software is hard. The way most people learn software, from simple examples that slowly build our knowledge, doesn't encourage complicated solutions that provide robust error checking, error handling, and secure practices. Most examples that I've seen in the world include "dumbed down" code that is easy to understand and explain in a class or seminar.

And that's the code that often gets used by developers.

They go with the simple example, and enhance it, not really concerning themselves with best practices, or even great practices. In fact, if you search for sample code on the Internet, read articles on various sites about programming, or buy a book from an "expert" it seems that more often than not, the code isn't well written from a security perspective. Yet that's the code, and the mentoring, that many developers get.

And for a DBA, that code is a nightmare.

Too often the code samples and practices that are taught to people include dynamic SQL built in the application with little to no thought about SQL Injection. And it doesn't matter if the application is at fault. As the DBA it's often your phone that rings, and you that end up explaining to your boss why all the products on the site cost $1, or why every customer's name is "0wned by S&p#rG##k."

The NSA, along with a number of other companies, is trying to do something about poor programming practices. They have release a list of the 25 most dangerous programming mistakes. What's interesting is that some government agencies, and hopefully companies soon, are using this list as a litmus test for vendors. Software companies are being asked to guarantee that their software contains none of these mistakes.

I think it's a great idea, and I wonder how well it will be enforced. Microsoft and Oracle, among quite a few other software vendors, provided input into the list. You'd hope that their software would be fixed, or at least all new software would comply with the recommendations and not contain any of these mistakes.

Steve Jones


The Voice of the DBA Podcasts

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.

You can also follow Steve Jones on Twitter:

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.

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.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums

ADVERTISEMENT

Red Gate Product Highlight:

SQL Source Control


SQL Source Control is a source control plug-in that connects the source control system of your choice to SQL Server Management Studio.

Version control is standard for applications, but databases haven’t caught up. Learn how you can bring database development up to speed.

Get your SQL Server database under version control now!

sqlinthecity

SQL in the City

Don’t miss out on learning about best practices for SQL Server database development and administration from top SQL Server MVPs. These free events are coming to Pasadena, Atlanta and Charlotte.

Find out more and register.

Featured Contents

 

Stairway to MDX - Level 1: Getting Started with MDX

Bill Pearson from SQLServerCentral.com

To learn MDX, there is really no alternative to installing the system and trying out the statements, and experimenting. William Pearson, the well-known expert on MDX, kicks off a stairway series on this important topic by getting you running from a standing start. More »


 

Get CPU and Cores for SQL Server 2012 Licensing

Additional Articles from MSSQLTips.com

With SQL Server 2012, Microsoft introduced a new licensing model; licensing per core replaced the licensing per processor. We need to adjust budget to reflect licensing changes for our next Enterprise Agreement renewal, but we do not have processor core information from any of our server inventory tools. This tip explains how to quickly gather information about each server's processor cores without logging in to each server. More »


 

SQL in the City Charlotte 2013

Press Release from Red-Gate

Join Red Gate on the day before PASS Summit for a full-day of free training from SQL Server MVPs and top presenters. Get $200 off your PASS conference ticket when you register for SQL in the City Charlotte, which takes place on Monday October 14. More »


 

From the SQLServerCentral Blogs - Microsoft Hyper-V 2012 R2 Enhancements That DBAs Should Care About

kleegeek from SQLServerCentral Blogs

The last couple of months have shown that the hypervisor battles for bigger, faster, and smoother are still heated and... More »

Question of the Day

Today's Question (by Samith C):

create table #temp_test
(id int)

insert into #temp_test
 values(1)
insert into #temp_test
 values(NULL)
insert into #temp_test
 values(2)
insert into #temp_test
 values(3)
insert into #temp_test
 values(2)

select count(distinct id) 
 from #temp_test
What will be the result ?

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


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

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

ADVERTISEMENT

Microsoft SQL Server 2012

Teach yourself SQL Server Code Name Denali—one step at a time. Get the practical guidance you need to build database solutions that solve real-world business problems. Learn to integrate SQL Server data in your applications, write queries, and develop reports. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Dave):

Evaluate this statement: after repeated warnings since SQL Server 2005, the extended stored procedure application programming interface feature has finally been removed from SQL Server 2014.

Answer: Incorrect, the feature is still available in 2014

Explanation:

The warning to not use Database Engine Extended Stored Procedure Programming continues in SQL Server 2014.

Ref:


» Discuss this question and answer on the forums

Featured Script

The Ultimate Index Lister (Non-Pivoted)

Jesse Roberge from SQLServerCentral.com

Util_ListIndexes_Columns
By Jesse Roberge - YeshuaAgapao@Yahoo.com
Update - Fixed existance check for drop

Lists details for all indexes on one or more tables / schemas, including row count and size.
If you want one row per index instead of one row per member column at the expense of data type and other column information,
then use Util_ListIndexes instead.

Update 2009-01-14:
Added IndexDepth and FillFactor output columns
Removed duplicate output of 'is_unique'

Required Input Parameters
none

Optional Input Parameters
@SchemaName sysname='' Filters to a single schema. Can use LIKE wildcards. All schemas if blank. Accepts LIKE Wildcards.
@TableName sysname='' Filters to a single table. Can use LIKE wildcards. All tables if blank. Accepts LIKE Wildcards.

Usage
EXECUTE Util_ListIndexes_Columns 'dbo', 'Cart'

Copyright:
Licensed under the L-GPL - a weak copyleft license - you are permitted to use this as a component of a proprietary database and call this from proprietary software.
Copyleft lets you do anything you want except plagarize, conceal the source, proprietarize modifications, or prohibit copying & re-distribution of this script/proc.

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU Lesser General Public License as
published by the Free Software Foundation, either version 3 of the
License, or (at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU Lesser General Public License for more details.

see <http://www.fsf.org/licensing/licenses/lgpl.html> for the license text.

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 2014 : Administration - SQL Server 2014

Pro Active DBA - Hi Guys, I've recently been appointed to maintain my companies internal DB's. Must also stress the point that the DB's are...


SQL Server 2014 : Development - SQL Server 2014

CURSOR and TABLE - Hi all, I consider CURSOR and TABLE as [b]data types[/b] but some one has argued that these are [b]database objects[/b]. Can you...


SQL Server 2012 : SQL 2012 - General

Need A Tool to Reverse Engineer an ERD - Our friends have done us a solid with Visio. Since SQL 2005 and Visio 2007 I have been reverse engineering...

Unable to shrink Database - I have a production databases running on SQL Server 2008 R2 Ent editionand.The size of this DB is 170 GB....

SQL Server Merge - Pull replication error - I have a configuration problem to create a Merge/Pull replication between two databases SQLServer 2012. The machine Publisher is a Windows...

Traces question - Hi, I'm running a server trace to determine where the procedures take more time to execute. I'm tracking events 10, 12, 43...

Issue with AlwaysOn High Availability - Friends, We are facing an issue with our DBs. We have configured AHA for couple of SQL Server Instances. The first...

ssis vs jobs - I just started as dba at new co. I'm looking over what the previous guy did. He has some ETL...

Printed Books Vs E-Books - ;-)


SQL Server 2012 : SQL Server 2012 - T-SQL

behaviour of isolation level snapshot on partitioned tables and views - Hi there, more often then we which for, we have same requests that results in a set of queries that take...

ORDER BY, Bad form? - So there's a trick I've used a few times with the ORDER BY clause in SQL, and I was just...

Searching for n-categories - Hi there I have a challenge where I need some help. May someone can help me? I have a table where a...

FULLTEXT problem - Hi, I've a table with a fulltext index and when I search for an exact phrase I get a result but...

Query now runs forever - Any idea how this might run forever? It is running through 174,000 rows, but that usually takes sub-second. Would this...

how to update column city value from 'A' to 'B' and 'B' to 'A' in single query - hi, I have a table like id city 1 A so i want to update city column from A to B and again...


SQL Server 2008 : SQL Server 2008 - General

approximate timespans for db backups? - Hello, I was wondering if you generally see a fairly consistent timespan for database backups? For example, I just backed...

connection options to sql server 2008 - In a new position that I have, I am told that I will be connecting to reporting services for a...

Installing a 1t db and best practices to keep 3 data bases in sync - Hi, All This is my project which i am going to work on. -- have to install and configure test/dev/prod databases using...

Optimize join to return single value from (one to many) - Hi, I need to get column from JOINed table but its one to many so I need to select only single...

Linked server to DB2 won't show catalogs - I have created the ODBC connection within the IBM Configuration Assistant, then created the linked server to the DB2 database....

Trying to remove SQL 2008 R2, getting a dialog about needing to access core_shared.msi - I'm trying to remove SQL Server 2008 R2 Developer Edition from my developer machine, in preparation for installing SQL Server...

Truncation Error - In an SSIS package I am using a Data Flow Task and I am getting this warming: Warning 2 Validation warning. Load Daily...

SSRS - Cannot Create Connection - Hi There, I am new to SSRS and have basically created a bunch of reports (sysadmin) through visual studio, deployed...

Script for columns' name, data type, length for one table - I am looking for a simple script for columns' name, data type, length for one table and sort by name

Indexing a column with type 'uniqueidentifier' - Hi We have a SP which is giving performance issues. As an example it has been executed 4 times and has...

performance - Hello dear, in a server i install sql 2000 & sql2008 r2. and link to another server in sqlserver2000r r2 that have sqlserver2000. suddenly...

master.dbo.xp_create_subdir not working for Long Directory names - Hi All, I have created the attached sp for creating sub directories (which has been later integrated in my restore tool)...

NOdeA is crashed ..on ctive active cluster. - Hi, We have active active sql 2008 r2 running on Win 2008 r2 cluster. Instance_A is running on Node1 Instance_b is runing...

deleting all data\logins\views - What is the best way to remove all data/views/logons from a database ? The scenario is this: We have a database...

How to run multiple stored procedures in parallel? - I have a stored procedure which runs in database DB1 This stored procedure will have other stored procedures inside it which...

Using CASE Condition for Prefixing a field from a table - Hi All, I want to retain the same Employee Code when an employee is rehired and prefix with A,B,C based on...

SQL Server Database Backup Monitoring - Hi SQL Masters, Greetings! Do you have a script that will determine if the backup failed/succeeded? Sample output: Database Name Status Log Date --------------- ---------- -------------- AdventureWorks2008...

Need Help on Fastest Search Logic - Hi, I have two tables named "Table1" and "Table2". Table1 Details: id bigint, product_name nvarchar(1000),quantity int records count on Table1 : 25000( may increase in...

Prevent backups on C:\ drive? - Can anyone - off the top of their heads - think of a way of preventing backups being taken to the C:\...

Error message when changing location of share drive in SSIS package - Hi all, At work we have a job that does the backup of 3 tables to a sharedrive using a SSIS...

Send SQL Server Dashboard reports via database mail - Hi All, Is this is possible to enable a job, which sends us the dashboard reports of a server thru mail...

SQL to list the headers in a table? - Does anyone know offhand the SQL to list all the headers in a specified table? Thank you!

Rebuild index task/job fails every time... - We are using SQL Server 2008 Enterprise 64bit. And we have made standard database optimalisation jobs via the maintenance plans. The rebuild...


SQL Server 2008 : T-SQL (SS2K8)

Need help on SP - I'm trying to create an SP to execute the following msdb.dbo.sp_add_jobstep msdb.dbo.sp_update_jobstep A 3rd party software installer created about 300-400 jobs. And naturally,...

Dropping/Recreating Clustered Index - Hi SSC, I've got a table with a clustered index which needs to be dropped and recreated to add an additional...

BCP text out that includes quotation marks - I'm trying to export a text string that includes " to a text file using BCP. This code works as expected by...

Slow Script - Hello. I ran the following script that too 3 hours 25 minutes to insert about 40,000 rows. Is there any...

Displaying nvarchar datatype column having a date value - Please help me in displaying a nvarchar datatype column called 'txtValue' having date value in the format '15 Jul 2013'...

SQL Agent Question - Hi Guys, I have Virtual Machine that I installed SQL Dev Edition. Here I need SQL Agent So I can Schedule...

get a specific time window across the night between two days - Hi all I need some help me to get me in the right direction. I have collected some sql stats into a...

trying to write a case statement for datetime stamp - some trouble - Hi. I'm working on creating a histogram using time, as the set of 6 buckets that data can fall into....

Update script - Hi, I a mtrying to create update staments including data for the table in this way but I am getting error...

Return text from a PDF stored in the database (Adobe iFilter) - We are storing PDF files inside a SQL Server 2008R2 DB. We have installed the Adobe iFilter to create a...


SQL Server 2008 : Working with Oracle

Passing an argument to an Oracle Stored Procedure and returning a record set to SSRS 2008 R2 - Hi all! I am having an issue retreiving data from a Stored Procedure in Oracle to SSRS. First, I have to...


SQL Server 2008 : SQL Server Newbies

SQL Agent Job error - Hi, I am experiencing the following error below. I have an update uncf processes job that has 35 steps. The job...

Disable a Calculated Parameter ssrs report using SQL Server 2008 r2 - Hi, I have a Report created in SQl Server 2008 r2, in which i'm passing 3 parameter to the query the third one...


SQL Server 2008 : SQL Server 2008 High Availability

Multiple Mirrored databases, Dev, Test, Prod off of one Witness? - It was suggested to me that we could just have 1 witness for all of our {application name} databases for...


SQL Server 2008 : SQL Server 2008 Administration

SSAS Change Management - Not sure if this is the correct place to post this but I am looking to see if there is...

Create Full Backup with Multiple FileGroups - Hello Everyone I am in the process of writing a Full Backup Database script, but this database has multiple filegroups. The...

Restoring my StandBy / ReadOnly Db - HELP PLEASE - Hey guys... i really need some help... I'm new as SQL-DBA, Our LogShipping DB is out of sync (since the...

Backuping Stored procs of the Database by using scripts - hi all, i want to create a script to backup the stored procs of the database plz suggest.

Choosing replication type and preparing for it - Hi all, We have 15 servers (in different regions) and each need to work with subset of data and exchange modifications...

query to get permissions for a user on all databases - I want to monitor rights change on certain users with windows authentication and sql server authentication. I am planning to...


SQL Server 2008 : SQL Server 2008 Performance Tuning

Query Plans migrating from 2005 to 2008R2 - I'm migrating from a 2005 server to a 2008R2 Server on Windows 2012. I restore the backup to test on....


Cloud Computing : Amazon AWS and other cloud vendors

How to Import data from S3 directly into my sqlserver database on amazon EC2 - How can i take text files, or csv files from s3 and directly upload/insert them into a table in my...


SQL Server 2005 : Administering

Urgent: How to shrink this huge DB? - I inherited a bad designed DB with: 25GB mdf + 99GB ldf. As I know, most data are redundant and can be...


SQL Server 2005 : Business Intelligence

Reading Server details or Enviornment variables in SSRS report - Hi, Is there any way to read SSRS report server name or enivornment variables when report is run with limited access....


SQL Server 2005 : Development

comma delimited name column - I am not a DBA, but rather a .NET developer that has been thrust into working on an SQL database...


SQL Server 2005 : SQL Server 2005 General Discussion

Data Warehouse Update to Live Table - We have a small data warehouse that updates hourly. I've been having some issues lately where the process that updates...


SQL Server 2005 : SQL Server 2005 Integration Services

Export large amount of data from one table to another - Hi Guys, Is there a way to start up an export from one table to another, if the data export fails...

same issue - i have the Same issue. Please provide solution thanks in advance.

Send mail with resultset using script task - Using Script task i have to send data records. I am aware of send mail via script, need help on...


SQL Server 2005 : T-SQL (SS2K5)

Need help with Charindex function.. - I am trying to capture an email address from the below message..whats the best possible way to do it .. DECLARE...

using case when condition in WHERE in SP - i have a SP with where condition as where id=@id and month=@month and Eid=@Eid wht i need is if @Eid=0 i...


Reporting Services : Reporting Services

Question About Creating Grouped Reports Outside of the Wizard - So this has been bugging me for quite some time now. I know it's faster to create report using the...

Sum of results of an expression in SSRS - Hi, I have the below expression in result i am getting values for each month. Now, I want to calculate...

Sum of the row groups - Hi I have problem to calculate sum of rows (groups) Cell 'Sum2' has been calculated just right, but cell 'Sum1' not. Cell 'Sum1'...


Reporting Services : Reporting Services 2008 Development

graphs in ssrs 2008 - I have a question about accessing the graphical features in SSRS 2008. Can you tell me how to access the...

SSRS Expression to Find Leap Year Failing - urgent need - I have a report with the following objectives, purpose, and questions (below the generic code). The environment is SSRS on SQL...

Can I stop SSRS from attempting to render a chart? - Hi, I have a report that errors with the message "System.OutOfMemoryException" The report had a number of charts, but some of these...

Visual Timeline of booked out equipment report - Hi, I have a report request that is to show when equipment is in use and not. I want to try...

Using LIKE in drop down parameter - I have a report generated from a stored procedure. There is a code called "cip" with different lengths of code....


Programming : General

Primavera P6 installation - Dear folks, I'm trying to install Primavera P6 R8.3 on my machine with Windows Vista as Operative System (32-bit) The message that...

How to improve a aquery - Good morning, I am writing a query thaht bring data from 7 tables. In the first statge the data is stored in...


Data Warehousing : Integration Services

Need help to understand direction in Execute SQL task - Parameter mapping - Direction - Go to Execute SQL task - Parameter mapping - Direction (It has 3 options - Input, Output, ReturnValue) What does this direction mean ? I...

SSIS XML file not found in 2012, but works in 2008? - Hello, I have an SSIS 2008 package that goes to the following url ([url=http://corpslocks.usace.army.mil/lpwb/xml.lockqueue?in_river=GI&in_lock=01]http://corpslocks.usace.army.mil/lpwb/xml.lockqueue?in_river=GI&in_lock=01[/url]) and downloads the data into a SQL...


Data Warehousing : Strategies and Ideas

Data warehouse assessment - Hi, I'm searching articles, white papers, books, blogs, etc. about how perform a data warehouse assessment. I'd like to have a good...

DWH Practice - Hi all, I'm looking for some advice for practicing DWH architecture/development with scenarios as close as possible to the real...

Good analysing, reporting end-user tool ?? - Hi, I'm looking for a good analysing program for our end-users. It needs to be able to : build queries with a simple...


Data Warehousing : Analysis Services

Automated Cube Restore - I'm a bit of an SSAS Noobert so if I'm working at this in the wrong direction, I'd appreciate some...

Can we use the cube data without deploying the solution? - I'm new to SSAS and I've created an dummy solution of Analysis Services project. I don't have permission to deploy...

MDX Get last non empty value for EACH month - I need to get the last non empty value of Total Cost for EACH month. M,P,R are parcel types, B is...


SQLServerCentral.com : Anything that is NOT about SQL!

Fantasy football 2013 - I renewed the league, you should be getting an email soon. At the moment, there are no open spots, but...

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


SQL Server 7,2000 : Administration

DBCC LOGINFO has a status 2 that I can''t seem to get rid of - I have done as many tricks as I know (forcing a shrink with dummy records, backing up tx log regularly,...


SQL Server 7,2000 : T-SQL

I need help Please :( - I have a table name Employee Logs table consist of EmpID, Empname,Logdatetime. I can generate the first in and last out by...

Trigger to insert from one (new row) in table to another table - I have a table where rows are created via a stored procedure. I need a trigger that will then insert...

To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2013 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com