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

Interfaces

This editorial was originally published on April 23, 2009. It is being re-run as Steve is on vacation.

Data quality is an issue in many of our systems that rely on humans for data entry. Even if you only import data from other sources, how reliable is the data that exists in those systems?

I found an interesting article on computer interfaces and it highlights some of the ways that have been designed to work with computers and put data into, as well as get data out of, computer systems. Most of these are probably familiar to you and there's nothing really groundbreaking in the article.

It had me thinking, however, of whether or not there's a better way to input and extract data from systems. I've been watching Star Trek: The Next Generation on DVD lately, and they primarily use voice recognition, but I see all kinds of flaws in that. My wife has worked with a lot of speech technology, and her current company makes the Dragon speech recognition software. I asked her if she thought it was good. She did, but it paled when compared to a good, old-fashioned keyboard. The rate of input and the few mistakes she makes typing far outweigh the benefits of using speech. Perhaps that will improve in the future, but I wonder if it will ever really supplant the keyboard.

Multi-touch has gained a lot of notoriety lately, especially in presentations, but I'm not sure it's a great way to get data out of a system for a single user. There are other possibilities, but in anything we develop, the rate of input as well as mistakes, are things on my mind. As a data guy, I am entirely too aware of how much work it is to clean data later.

Managing, storing, and securing data all is great, and you can do the best job in the world, but if users can't easily access and use the data, is it valuable? If it's not correct, is it useful? The way we work with computers will likely evolve, and while I'm not sure what will work best, I do know that us DBAs will always be in demand to ensure the data is as correct as it can be.

Steve Jones

Steve Jones from SQLServerCentral.com

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


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.


ADVERTISEMENT

16 essential SQL Server tools

In one installer, the award-winning SQL Toolbelt contains everything you need to work with SQL Server. "The SQL Toolbelt provides tools that database developers as well as DBAs should not live without." William Van Orden. Download a free trial.

ADVERTISEMENT
SQL Compare

Need to compare and sync database schemas?

Let SQL Compare do the hard work. ”With the productivity I'll get out of this tool, it's like buying time.” Robert Sondles. Download a free trial.

SQL Backup Pro

"A real time saver" Andy Doyle, Head of IT Services

Andy and his team saved time by automating backup and restores with SQL Backup Pro. Find out how much time you could save. Download a free trial now.

Featured Contents

 

How to Design, Build and Test a Dynamic Search Stored Procedure

Dwain Camps from SQLServerCentral.com

Learn how to design, build and test a dynamic search SP in just a few simple but methodical steps. More »


 

SQL Server 2008 R2 Build List

Steve Jones from SQLServerCentral.com

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


 

Different Ways to Restore a SQL Server Database

Additional Articles from MSSQLTips.com

This article describes the SQL Server database restore principles for full backups, differential backups and transaction log backups and how to perform the restores to get to a particular point in time. This tip describes SQL Server database restore principles on a database that is using the FULL recovery model. More »


 

From the SQLServerCentral Blogs - Adding a column to a table with a default (NULL vs NOT NULL)

Kenneth Fisher from SQLServerCentral Blogs

There are lots of ways to learn new things, or be reminded of old things. One of my favorites is... More »


 

From the SQLServerCentral Blogs - Is There Interest in SQL Server Security Pre-Cons?

I’m very passionate about security, especially database security. As the numbers with regards to data breaches continue to climb, this... More »

Question of the Day

Today's Question (by Steve Jones):

I am working in Management Studio on a SQL Server 2012 instance. I want to execute SQLCMD using the IDE. How do I do this?

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

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

Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012

Ace your preparation for Microsoft® Certification Exam 70-461 with this 2-in-1 Training Kit from Microsoft Press®. Work at your own pace through a series of lessons and practical exercises, and then assess your skills with practice tests on CD—featuring multiple, customizable testing options.

Maximize your performance on the exam by learning how to:

  • Create database objects
  • Work with data
  • Modify data
  • Troubleshoot and optimize queries

You also get an exam discount voucher—making this book an exceptional value and a great career investment.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jacobs):

What type of join is produced when running the SQL Script below?


SELECT city
    FROM contacts
UNION
SELECT city
    FROM contacts2
    GROUP BY city
    ORDER BY 1

Answer: None of the above

Explanation:

The above query does not produce a join.  It produces a distinct sort then concatenation of values from one table and the other. 

UNION:  http://technet.microsoft.com/en-us/library/ms180026.aspx


» Discuss this question and answer on the forums

Featured Script

Security Queries: Server-level

Greg Drake from SQLServerCentral.com

This script contains various queries I have written on the subject of security as it applies to a single SQL Server instance. It's arranged so I can either (A) run one query I need alone or (B) run the whole thing as a batch to dump out everything at once.

Option B might not seem useful at first (information overload!), but consider that if your output in SSMS is set to "Results to Text", this will allow you to quickly copy off a single text file that contains everything you might want to know about that server's security at a point in time. Furthermore, the queries are carefully structured and ordered to be consistent. This makes two of these text files easy to compare with a standard compare/diff/merge application (e.g. WinMerge, Beyond Compare).

Included queries:

  • Server information & settings
  • Server roles
  • Server logins
  • Server role membership
  • Server role and login permissions
  • Server-level SQL/Assembly module EXECUTE AS
  • Database ownership
  • SQL Agent Job ownership
  • SQL Agent Schedule ownership
  • SSIS package ownership
  • Maintenance plan ownership

Please also see related script "Security Queries: Database-level".

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

SQl question - Hello Guys, Can you help me to solve these questions? Employee Customer id name age salary id name city industry type 1 ben 61...


SQL Server 2012 : SQL 2012 - General

Oracle OLE DB connection Crashing in SSIS 2012 - Oracle OLE DB connection in 2012 SQL instance \ SSIS package is crashing with this error. Any one faced with similar...

SSRS report grouping - Hi, I am cregrating a report which shows sales information by country. I have result set like Country, BusinessType, NetSales When the...

SQL 2012 database compatibility modes - we're planning for a migration from SQL 2008 to SQL 2012. in planning for this migration, we have contacted vendors...

sql_statment_recompile - Dear All Want to capture data related to sql_statment_recompile event. This captured information needs to be stored to the tables. Please...

DB Roles - Dear All Need to create 3 users, please guide what roles to selected for these users 1. User who can run DDL...

Query against snapshot databases create more physical io requests - Hi all, I had an issue where a query executed against a snapshot database was taking an age, the same query...

Script required to restore Database - Hi, I need to restore a database. My database consists of 7345 data files. I got a request to restore the...

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

Migrating databases. Connect string questions - Hi everyone, We are required to move off of some old hardware due to warranty expiration. It is turning out to...

SQL Wait type xe_live_target_tvf - Hi All, We're having some performance problems with our server, and so checked the wait stats. When I run the following...


SQL Server 2012 : SQL Server 2012 - T-SQL

That's bug? - howdy everybody, when I wrote this: SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\OPENROWSET\AD_SE.xlsx', 'SELECT * FROM [Sheet1$]'); I had this: OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked...

Question about 45 Database Performance Tips for Developers - [url]http://www.red-gate.com/products/sql-development/sql-prompt/entrypage/sql-performance-tips-ebook[/url] I was reading through, and got sort of confused about the 23rd point: [quote] If you need to insert many rows at...

sql server application audit setup - How can i setup the audit on sql server database tables for the change made by the application users. :crying: As...

create a view to show the backup status in every 10 mins - I have a question, my table have following data: userID, startTime, EndTime ————————————— 101, 04/11/2013 11:00:00, 04/11/2013 11:55:00 102, 04/11/2013 11:00:00, 04/11/2013 11:24:00 103, 04/11/2013 11:20:00,...

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

Returning latest revision - Hey there, First thanks for looking. :) I have a table that pulls out the following when i use the sql (select...

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

Linked Server - How to escape reserved-word column names in update? - I have discovered that my previous error was caused by a different problem: The following query fails when [b]the destination column...

Convert to numeric overflow issue - I have a column in a table that is decimal(22,6). When I try to convert it to decimal(22,8), I get the...


SQL Server 2008 : SQL Server 2008 - General

SSIS 2008 Variable Data Type Mapping/Conversion Problem - Greetings, I am using SSIS 2008 to try and get the results of a Sybase stored procedure into a SQL...

Backups are still larger after data cleanup. Shrink? - I had an event where my database backups had grown considerably. After some searching I found that the culprit was...

Displaying Database Image in Report - Our Application allows us to save images into a chart note. It appears that these images are open via SVG...

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

hierarchical linking of locations to each other - I need help here, I have this table, I may need to create another column I'm not sure yet. CREATE TABLE...

Downgrading Compatibility Level - I recently had to take a snapshot for replication of a client database, this failed because the database contained an...

Create a Table From a Query - I would like some subsets (tables) from may main table. dumb q: How can i quickly create a new table from...

how to create a loop??? - fairly new with advanced queries and need help.. how can I make the statement below automatic (loop) so I do...

Questions regarding Spatial Data performance - Hi all, I was wondering if anyone could chime in with random tips or thoughts on what I'm doing. So I've...

Regarding storedprocedure - I have written a stored procedure like USE [nxnv1_temp] GO /****** Object: StoredProcedure [dbo].[SP_CONSULTATION_DETAILS1] Script Date: 11/02/2013 10:06:41 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER...

SSIS expression return only file name part. - I would like to create a variable fileName in SSIS package using an expression, basically I would like to return...

Minimum permission to run exec sp_helpsrvrolemember - What is the minimum permission for a user to run exec sp_helpsrvrolemember 'sysadmin' ? Thanks in Advance !

recovery model simple - Howdy Everybody, My database is Simple (Recovery Model) and when i generated backup, the log file was to become big 30gb)......

What is Default Locking level in SQL Server 2008 or 2008 R2? - Hi guys Like to know what kind of default locking level in sql server is ? Page ,table or row level?...

hierarchical linking of locations to each other - I need help here, I have this table, I may need to create another column I'm not sure yet. CREATE TABLE...

SQL Search very slow please help - Hi I have couple of SQL Tables with customers information the database works fine on SQL server 2005 but when...

Defrag DISK where MDF and LDF files are - What is the best practices around this? Should SQL Server be stopped to allow the disk to fully defrag? What...

SQL Server 2008 R2 poor performance on VMWare - We recently migrated our production server from a physical cluster environment to a VM environment, and ever since then the...

Oldest non-distributed LSN but No Transactional Replication - My transaction log and mdf backup file keep ballooning in size and DBCC check log gives [quote]Transaction information for database 'hub_live'. Replicated...


SQL Server 2008 : T-SQL (SS2K8)

Count - Hi I Have A Table with 6 columns. Select count(*) from ( Select Column1,Column2,Count(*) From Table1 group by Column1,Column2 ) <------------------------------------------------> Select Count(*) from ( Select T1.Column1,T1.Column2,T1.Column3,T1.Column4,T1.Column5,T1.Column6 From...

Sp runs from 7 sec to 10 min (UnitPricingTrans) - I compiled a stored procedure (UnitPricingTrans) 3 days ago. The first execution took 10 min. When I ran it second time it...

Duplicate Rows - Hello. I have a table that contains several duplicate rows. I would like to remove the duplicates except for one...

Regarding storedprocedure - I have written a stored procedure like USE [nxnv1_temp] GO /****** Object: StoredProcedure [dbo].[SP_CONSULTATION_DETAILS1] Script Date: 11/02/2013 10:06:41 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER...

Pivot, Unpivot and Cross Apply !!!! Nothing seems to work - Consider the below T-SQL [code="sql"] CREATE TABLE Temp10(Source nvarchar(50),Target nvarchar(50),Property1 int,Property2 int, Property3 int,Property4 decimal,Property5 nvarchar(100),Property6 nvarchar(50)) INSERT INTO Temp10 values('A','DEF',10,8,20,12.34,'Good','Bad') INSERT INTO Temp10 values('A','GKL',2,14,0,20.4,'Bad','Good') INSERT...


SQL Server 2008 : SQL Server Newbies

Consolidating an entire year into a single query - I Have a query that counts individual monthly orders just a simple count as below, however i want to consolidate...

Database activity - Hello, We have a SQL Server 2005 Enterprise Edition - 64 bit instance with 12 user databases. The activity monitor database drop...

How to create view in Database that uses a Linked Server on the same SQL Server "contains more than the maximum number of prefixes" - Sorry to be such a newbie. What would be the best options here? The Linked Server query time even using...

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

Averaging and Grouping - Hey Everyone, I'm new to writing SQL queries and I'm a bit stumped in this one. The below query currently defines...

Looping through select statement - I have a select statement in which the total number of columns is dynamic. First 2 columns are static , and...

parse string and update table in mysql - I need to parse a string in one column called "location" and copy the contents of "city,state" into 2 new...

do the amount of disks on a machine affect SQL's performance - Hi I have a new install of SQL on a server spec'd by a former coworker A Rac server with a...

Linked Server Windows 2008 64 bit - SQL Server 2008 - Oracle 11g Client - ODBC Question - Installed the 11g Client on my Windows 7 (32 bit) and the Windows 2008 Server (64-bit). TNSPing connects to report success...

Scheduled SSIS package not pulling all data - I have a scheduled SSIS package that is not including all information every morning into the flat file it generates....


SQL Server 2008 : Security (SS2K8)

Advantage of ISO 27001 standard - • ISO 27001 standard map out a framework for effective security information. • ISO 27001 commit the organization to compliance with legal, regulatory,...

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


SQL Server 2008 : SQL Server 2008 High Availability

log shipping setup advise needed - Hi, I was wondering if experts here can help with an advise :-) Let's say I have a site A with...

Issue with Logshipping - Can someone help me on this.. For testing,I've configured logshpping on same server (primary and secondary are in same instance).. LSCOPY job...

Logshipping to another server with different drive names - Hello, We have primary with drive F,L and secondary server with drives D,L. Data files from F goes to D in secondary...


SQL Server 2008 : SQL Server 2008 Administration

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

jobs taking too long to finish - On one of our prod server, every sunday we perform full backups and few other archival processes. We use third...

Dynamic TCP/IP Port - Hi I'm a little confused, one of our Production Instances is configured to listen on a Dynamic port, which I thought...

RS cannot be found - Hi, On a server there are two instances: sql2008r2 and sql2012. The RS configuration manager for 2012 finds the RS 2012 instance,...

Copying database error - HI Experts I am trying to copy a database on the same server but the error says the job is failed...

daylight savings time puzzle - I have an hourly payroll process (sql job) which needs to be disabled at 2 AM this sunday, and then...

backup file with datetime stamp - How to create backup files with datatime stamps this is to retain 2 days backups on the disk BACKUP DATABASE [DBNAME]...

Failed to map 8388608 bytes of contiguous memory - My customer is running a small e-commerce site on a SQL Server 2008 R2 SP2 Workgroup Edition x64. The SQL...


SQL Server 2008 : SQL Server 2008 Performance Tuning

table locking issues - I have an audit table that is written to and has records deleted from all the time through out day. some...

Stuck tracking down CXPACKET issue - We have a server that often has the following happen: 1) It breaks a SPID down and assignes hundreds of threads...

SQL Server Puzzling Performance - Hi Guys, Been hanging around these forms for a very long time but never posted much. I am experiencing something that...


SQL Server 2005 : Administering

Error in creating a snapshots - Hello I use bellow code to create a snapshot,but I have this error "Msg 5014, Level 16, State 3, Line 1 The...


SQL Server 2005 : Business Intelligence

truncation error - hi have a derived column with this in it BLOOM_COUNTRY != "" ? "BLOOMBERG" + BLOOM_COUNTRY : CO1 before that i have a dervided column that set...


SQL Server 2005 : Development

Regarding storedprocedure - I have written a stored procedure like USE [nxnv1_temp] GO /****** Object: StoredProcedure [dbo].[SP_CONSULTATION_DETAILS1] Script Date: 11/02/2013 10:06:41 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER...


SQL Server 2005 : SQL Server 2005 Security

Determine the user who entered data - I have been tasked to enable auditing on data entry in a SQL Server 2005 database. Specifically, I need to...


SQL Server 2005 : SQL Server Express

Cannot find SSMS on server to connect to SQLExpress2005 - Hi everyone, I have a SQL Express 2005 instance on our Blackberry server. I can see the location of the database...


SQL Server 2005 : T-SQL (SS2K5)

Output Query & Stored Procedure results to Text File with Headers (Pipe Seperated ) - Our users want a Dump of the SQL Data which could come by running Query and Stored Procedure every day...

Logic question regarding inserting of rows from 1 table to another. - at various times i need to copy the rows to another table but it has minor differences. table1 t1.f1 int, t1.f2...

IF table exists, DROP TABLE then CREATE TABLE - script not working - Hi all, This code to change a column name is not working and I'm not able to figure out why: IF OBJECT_ID(N'dbo.mtzcus_2014_CoreMenuReportCriteria',...


SQL Server 2005 : SQL Server Newbies

Three tables SQL query - I have a table (Vehicles) which contains a list of vehicles. VehicleID PlateNo CurrentDriver I also have a table (History) which contains a the...


Reporting Services : Reporting Services

Customize report server - Hello and happy Monday :) (Yea, right!) Question for everyone... I would like to customize my report server home page to NOT show...

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

Parameter Boolean - Hello Friends, new time come on a question. Thanks to new time I have responded. The following parameters related to ask is,...

Date range defaults - Hello and happy Friday! I have a several reports that accept date range parameters. These are not the textboxes that show...

Kill Single Report in SSRS 2005 - Is there a way to kill an individual report in SSRS 2005? Possibly using DMV's to determine the SPID? I...


Reporting Services : Reporting Services 2008 Development

SSRS 2008 substring process - In an SSRS 2008 existing report, I have one column that I need to separate the values. I am not...

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

Page Number on a Contents Page - I have a long report which is split up into a few sections and the users want a contents page...

Deploy report problem - Hello, When i deploy a report i get the error: "An attempt has been made to use a data extension 'ORACLE' that...

SSRS error when exporting as PDF: Index was outside the bounds of the array - Hello all... I have an SSRS report that, when you try to save it as a pdf, tiff, or print preview,...


Programming : General

Create a Batch file - Please help ! - Hello there ! Please help me to create a batch file which does/satisfy the below requirement.., I want to Copy older...


Data Warehousing : Integration Services

What CodePage to use for UTF-8 - I have a SSIS package, loading a file that is UTF-8. It is erroring out about there being cha's in...


Data Warehousing : Strategies and Ideas

Aggregation With Condition - Below is the scenario in which I have a question: Fact/Measures Table with the following columns: Fact_ID - Key - bigint Document_ID - bigint Version_ID - bigint Item_ID - bigint Amount1...


Data Warehousing : Analysis Services

refresh Excel report based on Offline-SSAS-Cube (a *.cub-File) not possible - Hi, we can't refresh the Excel-reports based on a *.cub file even if there is a newer .cub-file. Background: We use SQL Server...


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 : T-SQL

Help comparing Sum to Total amt - I need a little help. I have an invoice table and and invoice detail table. I pulled the data I...


Career : Certification

70-461 - Hi, Id like to take this exam and looking for some good prep.. My T-SQL skills are basic at present...


Microsoft Access : Microsoft Access

How to avoid opening a password-protected mdb - I'm looping through my pc's folders to open each mdb that I encounter and look for a sub in its...

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