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

Effectiveness

I saw a great quote recently on an internal email at Red Gate. We had deployed a new feature to a website, the application seemed to be working, but someone questioned the architecture of the implementation. The person that was in charge of the project sent this back at the end of an explanation:

"It’s a little clunky, but we decided it was better to get it done than make it perfect."

That's a great quote. In fact, that's what I've aimed for in most of my career. Move things forward, get them done in an effective manner. That's what I've tried to do as long as I've been in technology. Examine a problem, consider various solutions, and then choose one to move forward with. At the end of the day, as has been said by many people, "shipping is a feature".

This doesn't mean giving up on quality or accepting anything, but it does mean that you continue to move forward. You may make compromises on code quality, but small compromises. Your code has to still solve the problem. Your database still has to handle the load. You still need quality work, but a solution you can get done quickly that performs 10% worse may be better than a faster solution that takes twice as long to develop.

Lots of code lives in systems for years. We want this code to be "good" in that it needs to work, handle the load, and contain few bugs. However lots of code also gets refactored over time. We should minimize bad code as much as possible, but less than perfect code isn't bad. Sometimes it's just a little clunky, but it gets the job done.

Steve Jones from SQLServerCentral.com

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


Video and Audio versions

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.

Follow Steve Jones on Twitter to find links and database related items and announcements.

Steve Jones

Windows Media Video ( 14.7MB) feed

MP4 iPod Video ( 17.2MB) feed

MP3 Audio ( 3.6MB) feed

Feeds are available at iTunes and Mevio

To submit an article, rant or editorial,
log in to the Contribution Center

ADVERTISEMENT
SQL Prompt free eBook

FREE eBook –
"45 Database Performance Tips for Developers"

Improve your database performance with 45 tips from SQL Server MVPs and industry experts. Get the eBook here.

SQL Monitor Hosted

New! SQL Monitor Hosted

Start monitoring your SQL Servers in under 5 minutes. Get clear insights into server performance, whilst we manage the monitoring software. Find out more.

Deployment Manager

Deployment Manager 2 is now free!

The new version includes tons of new features and we've launched a completely free Starter Edition! Get Deployment Manager here

Featured Contents

 

SSIS Distributed Transactions

Brian Ellul from SQLServerCentral.com

This article shows the steps to set-up distributed transactions for SSIS. More »


 

Building Real-World Microsoft BI Dashboards Today

Additional Articles from SQL Server Magazine

There is a lot of Microsoft buzz about Power BI and Excel these days, but customers need real-world, professional business intelligence solutions that meet their complex real-world requirements today. In this article, Jen Underwood shares what technologies were used to develop a dashboard solution for a Fortune Global 500 company using Microsoft Business Intelligence technologies, and why. Some of the decisions may surprise you and the lessons learned are sure to be of value. More »


 

From the SQLServerCentral Blogs - Removing HTML formatting tag - BR - from XML using XQuery

ArthurOlcot from SQLServerCentral Blogs

This is a personal pet hate of mine.. Mixing HTML formatting tags directly with XML that aren’t escaped or contained... More »

Question of the Day

Today's Question (by Steve Jacobs):

What is returned from the query below (please select the correct 3 answers. Each represents a row)


CREATE TABLE TEST ( NAME VARCHAR(20) );
INSERT TEST
        ( NAME
        )
        SELECT NULL
        UNION ALL
        SELECT 'James'
        UNION ALL
        SELECT 'JAMES'
        UNION ALL
        SELECT 'Eric';
SELECT NAME
      , COUNT(NAME) AS T1
      , COUNT(COALESCE(NULL, '')) T2
      , COUNT(ISNULL(NAME, NULL)) T3
      , COUNT(DISTINCT ( Name )) T4
      , COUNT(DISTINCT ( COALESCE(NULL, '') )) T5
      , @@ROWCOUNT T6
    FROM TEST
    GROUP BY Name;
DROP TABLE TEST;

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 2 points 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

SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

Can you store filestream containers on compressed volumes?

Answer: yes

Explanation:

You can store FILESTREAM containers on compressed NTFS volumes. It is recommended that you set the clusters to 4kb.

Ref: FILESTREAM OVERVIEW - http://technet.microsoft.com/en-us/library/bb933993%28v=sql.105%29.aspx

FILESTREAM best practices - http://technet.microsoft.com/en-us/library/dd206979%28v=sql.105%29.aspx


» Discuss this question and answer on the forums

Featured Script

Convert int to Excel column name

Peter Diplaros from SQLServerCentral.com

This script converts a numeric column index to an Excel column name (ex: AC). The script doesn't use cursors or any other iteration. It converts the value using only set-based code so it is very suitable for using within a function.

The @ColumnIndex parameter is 1-based (1 = column A)

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

How many GB of memory does 1 database take - Hello - I need to take each database off of a physical instance and give memory, cpu specs of what each...


SQL Server 2014 : Development - SQL Server 2014

Cursor to Delete data - Hey Guys, i have to delete data from a table which is older than 2 weeks, how can i use a...


SQL Server 2012 : SQL 2012 - General

SQL Server 2012 gone after upgrade to Windows 8.1 - I recently upgraded to Windows 8.1 on my desktop box. When I sat down to work today SQL Server seems...

what is this meaning of this xquery? - Hi, with reference to http://technet.microsoft.com/en-us/library/ms190687.aspx originally the query is [quote] SELECT CatalogDescription.query(' declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription"; /child::PD:ProductDescription/child::PD:Features/parent::PD:ProductDescription/child::PD:Summary ') FROM Production.Product

Startup Parameter issue in SQL server 2012 - HI All, I changed startup parameter in my sql server. While starting sql service i am getting below issue 2013-11-06 16:42:00.53 Server...

Why query is performing good on recreating a function?? - I have attached a script file with 2 queries and their execution plans for your reference. You can see a clear...

Governing SQL Server agent jobs across SQL instances - Hi, I'm searching a software that allows to govern or orchestrate SQL Server agent jobs distributed on more SQL Server instances. With...

NUMA - Node - Hi, I need to verify memory size of each NUMA node in the system. Please share me the method how to...

Permissions and encryption - Is there any way to restrict permissions so that a user can read from a database (and even decrypt data)...

Stored procedure performance update after index rebuild - I have a stored procedure that runs every hour from 5 AM to 10 PM to check the pending orders...

Express edition Performance tunning - Hi Can anyone guide regarding how to do performance tuinning using SQl 2012 Express edition Regards

Suggest a port other then default - Hi at one of the client i have to suggest him a port other then default.Can anybody tell me how...


SQL Server 2012 : SQL Server 2012 - T-SQL

Covert in Case - Hi, I have the following part in my Select statement - Select CASE WHEN dbo.[IH_RE-TNCY-PERSON].[ON-TNCY] = '1' THEN 'yes' ELSE dbo.[IH_RE-TNCY-PERSON].[ON-TNCY] END AS...

Need help on optimizing query - Hi There, I need distinct customer count for a certain time slab. Consider the following table, [code="sql"] create table #sample (saledate datetime,custname VARCHAR(100)) Insert...

Customer Purchase Incentive Validation in T-SQL - Hello all - I would like your input on how to address this problem using T-SQL. I don't expect you to...

sp_OAMethod to retrieve the file attributes from a folder - Hi, I found the below code that will bring back "some" of the attributes of any file in the selected folder......

display order by like 1,2,3,4,5...............plz write quarie - Examples of values I want to put in order houseno 3-13-1 3-13-3/a 3-13-3/b 3-13-2/a 3-13-4 3-13-6 3-13-5/a 3-13-4/c i want output like this below houseno 3-13-1 3-13-2/a 3-13-3/a 3-13-3/b 3-13-4 3-13-4/c 3-13-5/a 3-13-6

download IIS log from .log file - Hello. I'm sorry for my English. I have a task, download into my table IIS log and compress this table Now I did...


SQL Server 2008 : SQL Server 2008 - General

Index/table rebuild - Hi everyone, We plan to implement a custom maintenance plan that will rebuild tables and indexes based on the information that...

With execute as issue across databases - I have an SSRS report that executes a stored procedure that truncates a table and populates it from another database...

?? CASE in a where statement - Hi Looking to have a where clause dependent on a parameter value Parameter = @ALL it currently uses Where.... AND (CLIENT_IDENTIFIER_TYPE.Code between @Code and...

session error - Hi friends, I'm connecting to sql server 2012 database from management studio 2008R2 from my laptop. I always leave the...

Setting up linked server with a MySQL Database - I'm trying to set up a linked server with an outside vender. I'm using SQLSRVR 2008 R2, the ODBC connection...

Shared data - So I have a scenario where two seperate entities of our business need access to data across a few tables....

Need help to import a text file - Hi, Client had provided a text file and he want us to export into sql database.But when i open the flat...

Compact, fully indexed, archive database - Hi guys, I was wondering how to create a compact database that is fully indexed (without fragmentation). I've got a 7 GB...

Replication issue - Im facing issue while configuring transactional replication getting the below error , iam new to replication Messages SQL Server could not disable...

Memory taken by procedure - Hi, Is there any away of knowing the memory a specific procedure is taking in memory when compiled? I have some procedures...

Help to Optimize query - Hi There, I need distinct customer count for a certain time slab. Consider the following table, [code="sql"] create table #sample (saledate datetime,custname VARCHAR(100)) Insert...

ldap query with variable - I am querying Active Directory and am able to get results when I hard code the filter requirements. But I'd...

calculating a date in the future - hello, i was trying to find dates in the future when a few doubts arise with this sql i can calculate, lets...

Database mailer is not sending mail - Hello, We have a database mail configured in SQL Server. When we are trying to send a mail using any procedure it...

XML - SQL - I need to import a load of XML into SQL server. There is no database for this currently. My understanding...

Rolling 3 month average cost help - Hi guys, I need some help calculating a 3 month avergae rolling cost. I have been trying to figure this out...

MsxEncryptChannelOptions and Multi-server administration - Setting up the multi-server administration failed for me because the MsxEncryptChannelOptions registry value on the server I am attempting to...


SQL Server 2008 : T-SQL (SS2K8)

How to bring in more than one column from left table in a CURSOR/dynamic sql query - First of all, if this can be done without the use of a cursor, I'm open to switching away from....

Multiple Column Joins - I have two tables and I need to join on one column if there is a match else join on...

sql help table join - Hi, I have the following two tables [code="other"] SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[productOld]( [warehouse] [varchar](2) NULL, [product] [varchar](10) NULL ) ON [PRIMARY] GO SET...

MSDB.dbo.sp_send_dbmail HTML formatting fails, but unformatted works - This sample code should work if folks want to test this - just add your email in the 2 sections [insert...

Export particualr column of Stored Procedure to a Table - Hi I know that It's possible to export the entire result set of a Stored Procedure to file, table etc.. but...

How can I select values where first 2 characters are non numeric and last character is 1 - I need to make a condition where I exclued results from a column where the first 2 characters are not...

Determine time elapased by hour - Hi, Wondering if anyone has an elegant solution to finding out how much time has elapsed for a specific hour. [code="sql"] declare @t...

Phone number question - detecting and replacing - Hello, I have a website/database app with a text column that stores a user's bio where users are not allowed to...


SQL Server 2008 : SQL Server Newbies

Need all activity by product code even if it's empty. - Trying to show all activity (typically dollars) and eventually group by product code. there are some months when some (if...

inserting cte table into temp table? - Hi all, I've been using the following code in a SP that get's 50 random rows from a table, is...

sql server agent won't start - SQL Server 2012 SQL ServerAgent is currently Stopped. I want it to be running, so I right click and select start...


SQL Server 2008 : Security (SS2K8)

DTS Package SAVE AS - Possible security vulnerability? - I am posting this not only to point out what appears to be a security vulnerability but to hear opinions...

Enabling "Enforce password expiration" on an account with an old password - It's required at my employer to have password policy and expiration enforced for all logins. Well, turns out there's a...

ISO 27001 Standard and Database Administration - Hi All, I am working for a small company that is looking to tighten their overall IT security via ISO 27001...


SQL Server 2008 : SQL Server 2008 High Availability

SqlServer 2008 cluster adding RAW disks VMWARE - We have a sql server 2008 custer on Wndows server 2003 virtualized using VMWARE We are in the process of adding...

understanding the need of database snapshot - hi everyone,I want to understand the actual real time benefit of database snapshot. when u have routine backup and restore mechanism,u...

When Database Mirroring fails, testing recovery scenarios - I have a mirror setup, and I want to test failover recovery when things go bad. Like in clustering, I can...


SQL Server 2008 : SQL Server 2008 Administration

Linked Server Fails with SQL Login. Works as Server Admin - I have a linked server to MySQL on a Windows server 2003 32 bit server. I can run selects & updates...

Database went into Restoring State for unknown reason - I have a Database that went into Restoring State for unknown reason. I had recovered it this morning. Then I performed...

Monitoring question - Can someone please share the scripts to find out the most and least utilized databases? I'll be monitoring them for a...

DELETE running for 8 hours need to Stop the process - Someone execute a DELETE query and it has been running for for 8 hours need to Stop the process. If I...

Concurrent fullbackups in sql server - We've a server (SQL 2008R2) with two sets of full backups running at the sametime..One is local backup with sql...

user connections sql server - How many user connections can sql server handle? In the sp_configure the default values for min 0 and max 32767....

Permissions issue with temp tables - I was 'cleaning' up some security issues on database. I added users to a Group login ID used by the...

Backup & Restore Question - I have one scenario based query based on this backup schedule: Full Backup: Monday/Thursday @10:00 PM Differential Backup: Tuesday/Wednesday/Friday/Saturday @10 PM Transaction Backup:...


SQL Server 2008 : SQL Server 2008 Performance Tuning

SQL 2005 T-SQL Performance tuning - Hi, I require your help for optimizing/tuning the below query. This query is a part in a stored procedure and this query...


SQL Server 2005 : Administering

can't connect to linkedserver - Hello I create my new linked server and an user for it with a username and password. but I can't connect to...

CHANGE TRACKING - Hello, I am testing Change Tracking on some of the exiting tables. Have implemented CT on an existing table with 100...


SQL Server 2005 : Business Intelligence

displaying a query's value in an ssis variable - Hi all you clever people I have the following query (I suppose it is self-explanatory, but...): select max(a.TransDateNumeric) as ToDate from...


SQL Server 2005 : CLR Integration and Programming.

Return image from file system as if it were in a blob column - I have been tasked with writing a CLR proc (my first) to retrieve an image from the file system. Background: Currently,...


SQL Server 2005 : SQL Server 2005 General Discussion

convert date time - this is my query select 1 as CID, substring(abd,24,5) as EID,substring(abd,3,12) as Checktime,substring(abd,17,2) as [status],substring(abd,32,2) as BID from ABCD its give me...

T-SQL syntax - Getting a syntax error on the "WHERE" clause (second last line)... That clause should modify the main "UPDATE tblBoMDetail" command... Ideas? [code="sql"] DECLARE ...

Update SQL job step - I need to add my email address on the failure steps on SQL agent....There are hundred of jobs setup and...

SQL 2005 Filling NULLS - Hello all! Im trying to get a query to work (well actually a view) so that i get data from...


SQL Server 2005 : SS2K5 Replication

Replication move from one server to another server - Hi We need to migrate databases and its replication setup to new servers. On current environment, we have publisher,subscriber and distribution...


SQL Server 2005 : SQL Server 2005 Integration Services

SSIS (dtexec) and bulk load permission - Hi, I am an accidental DBA trying to install existing SQL Server 2005 in a new environment with existing contents. I...


SQL Server 2005 : T-SQL (SS2K5)

Min/Max values using order by - Hi folks, I have a product table that needs 4 columns updating using joins to 2 other tables. Columns to be joined: MaxPrice,...

Manually increment smallint column during an INSERT command. - Here is how the table is created... SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[SPEDDTL]( [spdd_f1] [char](2) NOT NULL, [spdd_f2] [char](1) NOT...

Customer Purchase Incentive Validation in T-SQL - Hello all - I would like your input on how to address this problem using T-SQL. I don't expect you to...


Reporting Services : Reporting Services

Reporting Services security migration from native mode to sharepoint 2013 integrate mode - I already migrate the reporting services 2008 R2 native mode items, shcedules, etc. to my sharepoint 2013 reporting services, all...

Picture shifs thing around in a form. - When I add a new element to my form say a picture everything in the report seems to shift down...

SSRS installtion failed - Hi guys, may be a foolish question... does internet connection does effect the ssrs installation. I was asked to install ssrs on 4...

Sharepoint integrated reporting deployment problem - SQL 2012 Sharepoint 2010 VS / BIDS 2010 using Integrated mode for SSRS with SharePoint report server url is http://XXXdev002/sites/Reports/_vti_bin/ReportServer/ reports folder is at...


Reporting Services : Reporting Services 2005 Administration

SSRS history - The reports being run by SSRS are not being logged in the ExecutionLog table correctly any more. It used to...


Reporting Services : Reporting Services 2008 Administration

Change SSRS Data Sources - Evening Guys, I have about 150 Reports that use their own connection to a couple of data sources; these sources are...


Reporting Services : Reporting Services 2008 Development

Format the Last Three Row - Goal: Make the three last row with value(1239, 1551, 4776) to use decimal value (xx.00) Problem: What guideline can you provide to do...

Display Country's name in Textbox - Goal: Display the name of countries in a list based on selection. For instance, if I select Australia and France, the...

Credentials - Everytime I try to access a report from the report server , it asks me for my credentials. Is there a way...

ssrs 2008 r2 error:The Hidden expression for the tablix 'Tablix3' contains an error: The query returned no rows. The expression therefor evaluates to null - In a SSRS 2008 r2 existing report, I am getting the following error message when the value selected by the...

Extend a column based on Selection in Parameter Prompts - I have a parameter prompt with hierarchy (Category, sub category, product) If I select for instance "Locks" in the prompt, the...

Conditional Formatting of Table Row - I am trying to find a way to format a border style for a row if the value in a...

Resetting Parameter Default Value - I have two parameters, ParameterA and ParameterB. I want to set the default value of ParameterB based upon the selected value...

Cognos to SSRS 2008 - All, we are going to migrate 41 Cognos report to SSRS. This project is in initial stage now. As part of this...


Data Warehousing : Integration Services

SSIS Package with variables for Multiple Excel files - I've a simple SSIS package with a ScriptTask and SendEmailTask. ScriptTask is used to simply OPEN an EXCEL file (for...

SSIS 2012 ODBC connection to Progress DB Gotcha - Thought I'd share my experience of setting up an ODBC connection to our source Progress database using the OpenEdge 10.2B...

Import Excel to Sql table - Can anyone help me on this. what is the easiest way to do this? I used a excel source and...

Tips for avoiding stored passwords with DontSaveSensitive? - Hi all, We have an in development data warehouse project in SSIS 2012 and I am trying to set up our...


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


Data Warehousing : Analysis Services

New Column with numbers - Goal: Create a new column, named test , containing numbers in SSAS only inside of DSV design view. If StatePrinceCode's column has value...

How SSAS actually got used? - I have been learning SSAS and actually been through 2 books. One question though is how the SSAS actually get...

Calculating MTD, YTD and ITD - I have developed a Cube for client and want to implement the time intelligence on Date, Month and Year hierarchy...


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

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

Databases on accessible - Hello, I have 2 database that became unailable and message I got datebase log not Availabile,when I rebotted sql server services...


Career : Resumes and Job Hunters

I'm looking for remote DB Developer's job (also DBA's) from 10$ per hour! - I'm experienced DBD. Also I know SQL Server DBA concepts. My oDesk profile is https://www.odesk.com/o/profiles/users/_~013a5369b6351c048b/ I'm looking for any IT job!

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