In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Data Compare Save time when comparing and synchronizing database contents
"It has also streamlined our daily update process and cut back literally a good solid hour per day." George Pantela, GPAnalysis.com. Download a free trial of SQL Data Compare now.
 
SQL Monitor Custom metrics from SQL Server MVPs
SQL Monitor is the only tool with a free library of custom metrics from SQL Server MVPs. Find new metrics for your servers.
 
SQL Skills Deep technical training by world-renowned experts in 2013.
You can't get better ROI for your training budget. Read more.

In This Issue

Stairway to MDX - Level 10: “Relative” Member Functions: .CurrentMember, .PrevMember, and .NextMember

SSAS Maestro, SQL Server MVP and Business Intelligence Architect Bill Pearson introduces three “major players” within the MDX “relative” functions. These basic, but highly employed, functions include the .CurrentMember, .PrevMember and .NextMember functions. More »


Migrating Databases Checklist Part1

SQL Server databases move around as an organisation’s data grows, applications are enhanced or new versions of the database software are released. If not anything else, servers become old and unreliable and databases eventually need to find a new home. Here's what to do when migrating your databases. More »


From the SQLServerCentral Blogs - SSRS – Using a Parameter to make Dynamic Columns

Multiple value parameters are a common tools used in SSRS. You can use this tool to select which columns actually... More »


From the SQLServerCentral Blogs - Looking to SQL Server 2014 High Availability In Standard Edition

During the keynote for TechEd North America 2013, Microsoft announced the planned release of SQL Server 2014.  As part of... More »


Editorial - Data Vision

One of the things that I often do when analyzing data is examine visual representations. I don't ignore data, but often a graph or picture of who the data is distributed or organized gives me a starting place for more details examination of the actual numbers. It has worked well for me and going back and forth from numbers to graphs allows me to better understand the ways in which patterns and tendencies are embedded.  Humans are visual, and we are expanding that capability more and more to machines.

Years ago I worked for a company that imported wood and sold it in the US. In one of our plants we sorted and organized wood pieces using a combination of human and machine efforts. Conveyor systems moved wood along and humans examined the individual piece as they traveled by. Using chalk marks, they were able to mark items as better or worse quality than others. A computer system scanned for chalk marks and was able to separate the wood more effectively and efficiently than in the past.

That type of human and machine analysis is being used more in many industries. However instead of using humans to do analysis, computer systems can now do that in some cases. I ran across a piece about software that examines physical parts from a manufacturing process, looking for defects. The computer system can do a better job, faster than most humans. There might be a verification step from humans, but for many parts, this means a higher level of both quality and productivity.

I could imagine this type of computer examination has a place in data systems as well. We could train an algoirthm to look for patterns in a visualizaiton, and perhaps highlight them for more examination by a human. We could even have some secondary systems that examine error outputs from something like an ETL process and direct developers to the potential issues in their logic, or in the source files, pointing out data or formatting problems. A little more checking, or real time testing, might help improve the overall quality of our processes, whether in the real world or embedded in software.

» 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. Due to technical issues, we have no podcasts today. Actually, Steve's camera died and he is in the process of replacing it. Hopefully we'll be back tomorrow with the podcast.

Everyday Jones

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

You can also follow Steve Jones on Twitter:

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


Question of the Day

Today's Question:

I am connected to a SQL Server 2012 RTM server with a database that has the READ_COMMITTED_SNAPSHOT option set to ON. I then execute this query:

SELECT *
FROM dbo.MyTable
EXCEPT
SELECT *
FROM dbo.MyTable
WHERE NOT (NumericColumn BETWEEN -1 AND -10);

In the result set, I get 5 rows. What is the most probable reason?

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

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

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

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks.

Get your copy from Amazon today.


Yesterday's Question of the Day

What is output of these queries?

declare @test table (Value varchar(50));
declare @date date ='10-10-2012'
declare @t1 varchar(10)

set @date='10-10-2012'

insert into @test values ('10'), ( @date) ----cast as varchar
 select * from @test
go
declare @test table (Value varchar(50));
declare @date date ='10-10-2012'
declare @t1 varchar(10)

set @date='10-10-2012'

insert into @test
 select '10'
 union
 select @date --cast as varchar

select * from @test
go
declare @test table (Value varchar(50));
declare @date date ='10-10-2012'
declare @t1 varchar(10)

set @date='10-10-2012'

insert into @test values('10')
insert into @test values(@date)

select * from @test
go

Answer: Error,Error, 2 rows one with 10 and the second with '10-10-2012'

Explanation: When inserting multiple values using a single insert with different data types, you need to explicitly cast values to compatible data types. Otherwise the statement will throw an error. The script below shows how you can do this:

declare @test table (Value varchar(50));
 
declare @date date ='10-10-2012'
 
Declare @t1 varchar(10)
 
set @date='10-10-2012'
 
insert into @test values ('10'), ( CAST(@date AS VARCHAR))  ----cast as varchar
 
select * from @test
 
go
 
declare @test table (Value varchar(50));
 
declare @date date ='10-10-2012'
 
Declare @t1 varchar(10)
 
set @date='10-10-2012'
 
insert into @test
 
select '10'
 
union
 
select  CAST(@date AS VARCHAR)   --cast as varchar
 
select * from @test
 
Go
 
declare @test table (Value varchar(50));
 
declare @date date ='10-10-2012'
 
Declare @t1 varchar(10)
 
set @date='10-10-2012'
 
insert into @test values('10')
 
insert into @test values(@date)
 
select * from @test
 
Go

» Discuss this question and answer on the forums

Expert Performance Indexing for SQL Server 2012

Expert Performance Indexing for SQL Server 2012 is a deep dive into perhaps the single-most important facet of good performance: indexes, and how to best use them. The book begins in the shallow waters with explanations of the types of indexes and how they are stored in databases. Moving deeper into the topic, and further into the book, you will look at the statistics that are accumulated both by indexes and on indexes. All of this will help you progress towards properly achieving your database performance goals.

Get your copy from Amazon today.


Featured Script

Space used by db files

Often I've been requested to move a db from one server to another. This script is based on another script I found on the Internet, I cannot remember the name of the Author or track down which site it was. 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

How to Insert Default value of Column In Case of Check Constraint Violation Error - Declare @Var int Set @Var=5 Create table Table(id int) ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD CONSTRAINT [CK_Table1_ID] CHECK (([ID]>=(0) AND [ID]<=(3))) GO ALTER TABLE [dbo].[Table1]...

High Memory utilization - Hi Everyone, One of our server in the production environment is hitting 93% of memory utilization and sqlserver is the only...

SQL agent configured to call a SP - All, My problem scenarion is: "I have a SQL agent job setup that calls a SP which collects all data and sends...

Separate one column in many columns - I have a table with the next structure: CREATE TABLE students ( ID_Student INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Name nchar(30), rate nchar(5) ...

How to Create Linked Server for a MySQL database using SQL Server Management Studio - It took me about a day to figure this out, so I thought I'd try to save someone else the...

SQL Server 2005 : Backups

Restore existing database failed due to space issue - All, I have two data servers S1 and S2. I have a database D1. D1 exists in both S1 and S2....

SQL Server 2005 : Business Intelligence

Import Dynamic File Name with a Date/Time as the file type (YYYYMMDDHRMMSS) - For some unknown reason, the creator of this file set the naming convention to something I can't begin to fathom......

SSAS - how to view cubes - I come from Cognos, and try to understand how users can view and manipulate cubes once they are created? do...

Removing column headers in CSV export file - Hi when I export report to CSV file through sql reporting, header row is included, but there is no header row...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Execution plan shennanigans...... - Hey folks, I'm looking for some help in understanding why my query is behaving differently under certain circumstances. The query...

How to restore master database.? - what are the steps to restore master database..? Thanks.

Server becomes very slow [RESOURCE_SEMAPHORE] waittype. - From last week our server periodically becomes very slow, I'm getting multiple RESOURCE_SEMAPHORE waittypes. The server box specs are: 4 Quad...

SQL Server 2005 : SQL Server 2005 Integration Services

Problems with excel 2007 import using SSIS 2005 - Hi, I am trying to import data from excel 2007 into my sql server 2005 database using SSIS 2005. SSIS 2005...

Using Script Task to check if Folder Exists - I am creating a new directory/folder in my package, but before I do that I want to check if the...

SQL Server 2005 : T-SQL (SS2K5)

2008 SQL Stored Procedure output to a pre-existing Excel 2003 spreadsheet - I want to create a stored procedure where the output will to into an Excel spreadsheet that will have a...

Emailing in HTML - Hi All, I am trying to format an auto-generated email from SQL into HTML, i've sussed a bit of it...

SQL Server 7,2000 : Administration

Change IP adresses of clustered SQL 2000 - Dears, I need to change all the IPs related to a production DB (4 IPs (NICs , Windows and SQL) ) The...

SQL Server 7,2000 : General

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32) - I have a Stored Procedure and getting a Error "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit...

Index Scan vs Index Seek - What is the difference between index seek and index scan?

SQL Server 7,2000 : Security

Login failed for user with token-based server access validation error - Can anyone help as I am at a loss with this one. I am running SQL Server 2000 Standard Edition on...

SQL Server 2008 : SQL Server 2008 - General

which type of value should be given to timestamp datatype - example:--- use 12 go insert into tstamp values( ?????)

sql server 2008 - I am confused how to install sql server 2008

Resource Governor in SQL Server 2008 - Hi, Am looking to implement Resource Governor functionality, and would like to know the links or any useful details which...

SQL 2008 - Configuring SSIS in Cluster - After making the changes to the xml file to have the cluster name in all the nodes, can i connect...

Transaction log size far exceeds database size when running delete - we setup a purge process and had about 20GB of free log space but kept filling up the T-log. After breaking...

Benefits in using a unique login for each user. - My colleague has received a new application to maintain, written in 4G language. The users have their own, unique credentials to...

Learn pivot but got an error - I copied code exactly from http://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query as below but got an error said that Incorrect syntax near 'PIVOT'. You may...

Can't update to SP3 for SQL 2008 - Hello I am running SQL Server Enterprise 2008 RTM and need to upgrade to service pack 3 to support a database...

PAGELATCH_UP Won't Let Go! - All, I ran into an issue where my backups and checkdb jobs are failing on one database. Result of a checkdb [quote]Msg...

Switch Connection - I am trying to load data from a replicated database in SSIS-if for some reason the replicated fails then i...

Date and Time when Table last queried - Hi, Is it possible to find out when a table was last accessed i.e Table has been selected from a query. Thanks

Cannot connect from VM with Windows XP to SQL Server 2008 R2 on Host Windows 8 - Hi, I have a PC running windows 8 and SQL server 2008 R2 express and a virtual machine with Windows XP....

The size of a table. (space_used against calculated space). - With sp_space_used you get the 'gross' size of a table. (Used_size for data) Summing all fields with a datalength function also...

How to get results from an SP into a table. - Dear reader, Below is an example to get data from a stored procedure into a table. Is there a more generic method...

SQL Date Issue - Hi, I wish to create a column 'GoalBehindFlag' which will work like a flag providing me 0s and 1s if the...

SQL Server Transactional Replication . Artice not created by snapshot - Good Day, We are running SQL Server 2008r2 using Transactional Replication. We selected a numbe of tables to replicate . One of...

SQl Server R2 Enterprise Evaluation link anyone? - Hi, Anyone got a link or is hosting the older 2008 R2 enterprise evaluation version, I need to load up an...

Error - i am having this error Msg 257, Level 16, State 3, Procedure query, Line 21 Implicit conversion from data type datetime to...

Division problem in SQL - Hello All, Can anyone please explain why do we always get 0 when we divide 1 by any other larger number. select...

Using the same partition function and scheme for multiple tables - I have a database where most of the tables have an integer field say "SetId" denoting a batch of data. If...

creating logon & logoff triggers in MSSQL - We have several database that we track user logons for, until now all these databases resided in Oracle. For Oracle...

SQL Server Corporate Standards - Automated Compliance Checking - Does anyone know of a flexible automated SQL Syntax checker? I want to be able to analyse scripts before they are...

Converting DateDiff to HH:MM:SS - Evening All, We're having some issues with some data and i will do my best to explain, The question relates to vehicle...

DMV sys.dm_db_missing_index_group_stats returns no rows - Hi All, I am using a script to review missing indexes on various SQL Servers. Most of the time, it works just...

SQL Server 2008 : T-SQL (SS2K8)

Hi guys this urgent reg int to varchar conversion - I am a fresher dba i got a task where i need to convert int value to varchar please help...

Pivoting help - Hi, I trying to get the dataset Ptid Test Result Date 1 BP Neg 1/1/2013 1 CG Pos 1/2/2013 I want result as Ptiid...

using Correlated subQuery in Join not working - I have a large query that is returning multiple rows that I tracked down to my jornal table. It has 2...

subquery returning more than one value - I have two tables, a and b. I want to select all from table a (except where proc_num is null)...

Simplifying The Stored Procedure - Hi Friends, In a new assignment as DBA, I am trying to make few codes better and have tasted some success,...

Rollback statement in T-SQL - The functionality of Rollback statement in T-SQL is such a way it rollbacks to the outermost nested transaction. This is...

Case statement with subquery - I have a CASE statement with a subquery that works most of the time but if the subquery comes back...

help getting return of function and loop into stored proc - Hi, I have a tricky issue I am struggly with on a mental level. In our db we have a table showing...

Update Performance - Hi Guys, Any Ideas what the best way would be to perform this update? UPDATE D SET Track_ID = P.Track_ID, Territory_ID = P.Territory_ID, Major = P.Major FROM Staging.Track_Mapping P INNER JOIN Staging.Track_Play D ON P.ISRC = D.ISRC_Code AND P.Event_Date = D.Event_Date Both tables...

Creating hierarchical metadata based on DMVs or system tables - I have a requirement to archive & purge my OLTP data. The database is a tightly defined relational database with Primary...

SQL Server 2008 : Working with Oracle

Oracle 32 bit client on 64 SQL Server 2008 - Specs: [b]Windows Server 2003 R2 x64 SP2 SQL Server 2008 10.0.2531 (64 bit) Visual Studio 2008 v9.0.30729.1 SP .NET Framework v 3.5 SP1[/b] We have...

SQL Server 2008 : SQL Server Newbies

select not working - Dear All In procedure i am using Remark table to capture all the steps executed (instead of print). From another session...

Choosing the correct driver - I'm writing some documentation on how to connect Access to SQL via ODBC. I honestly always just choose "11.0" when...

Linked Servers (SQL 2005) - So I am a newb.... I have a database system that uses SQL 2005. The server that these systems reside...

Inserted & deleted table - Hi, I have confused with below query . Could anyone please help me.... Client have sent me one view query , there he put...

Delay in Print - Dear All I have procedure A,B,C. Procedure A calls B and C. Procedure B and C have loops and print statement...

NOCHECK CONSTRAIN - Dear All There is hardly any data in the table on which i have defined foreign key. But when i am...

SQL Server 2008 : SQL Server 2008 Administration

SQL Server 2008 R2 Maintenance plans and backups - Hi, I have a customised maintenance plan that rebuilds the indexes and updates the statistics and then takes a full...

Automated backup with no Agent i.e. SQL Express. Hide logon details - Hi [url=http://support.microsoft.com/kb/2019698]http://support.microsoft.com/kb/2019698[/url] Shows a SP to backup DB(s) and how to use windows scheduler to achieve this. This would work ok if...

SQL Server TDE Encryption - Hi All, I have a PRODDB which is encrypted with TDE .I have DMK and DBCert Key with me .I also...

sp_executesql -- can produce very wrong execution plan - A front end app sends a query to SQL 2008 using sp_executesql. The query returns a count as the final result. The...

Does a backup cause TempDB to grow - I received an alert this morning that our Temp drive had run out of space. When investigating it, I found...

IP Address Doesnt turn from offline to online - :unsure: Hi I got a problem, I think it is a little bit weird kind of problem, I haven't find anything...

Career : Certification

Refer books for exam 70-448 & 70-452 - Hi, I am planning to take exam 70-448 & 70-452 next month. [b]Can anyone help me with books need to refered...

Advice - Hi I'm new here. I wanted to know which certification is most relevant if I want to go into analytics...

SQL 2012 BI MCSA - 70-462 or 70-463 - Which to take next - Passed 70-461 today. Have a project to establish a BI warehouse. What would make sense to take as the next...

70-463 Exam v. Training Kit - How do the test exam questions from the 'Implementing a Data Warehouse with Microsoft SQL Server' training kit compare to...

Failed 70-462. Worth doing 70-461 before resit? - Took 462 yesterday and only managed to get 612 vs the pass mark of 700. Main problem is that I'm...

Programming : Powershell

Open Excel Error Using PowerShell in SQL Server Agent Job - I am trying to run a Powershell script, which opens and modifies an Excel spreadsheet, from a SQL Agent job...

SQLServerCentral.com : Anything that is NOT about SQL!

NOLOCK is giving different result sets.Why? - I have 2 select statements. One with NOLOCK hint and other without NOLOCK hint. I am getting different result sets....

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

Display all the days in a months in a matrix - Hi, Can someone please suggest, how this is can be done. I need to produce all the days in the month, to...

How to find FIRST matching value from the table , - Hi Team , How to find first matching value from the table , I need to get the first Australia amount 5000...

SQL Query - I have the following query: SELECT Sales_Intrastat.SalesID, Sales_Intrastat.Year, Sales_Intrastat.Period, Sales_Intrastat.Sales, Sales_Intrastat.CostOfSales, InstrastatCustomers.Depot, InstrastatCustomers.AccountName, InstrastatCustomers.CountryCode, InstrastatCustomers.AccountNumber, CountryCode.Description, InstrastatCustomers.ContractC

custom columns in a tablix (matrix) - HI , I was wondering if someone could help me. I have a SQL table with the following fields: -Variable -Period -Value which generates the following...

Report Showing Missing Images - I have a report that shows badge photo images for staff that is working using an external image source for...

Reporting services - Here is my company's requirement: We want the user's to create their own reports, so I was thinking of using...

Reporting Services : Reporting Services 2005 Administration

SSRS Error: Failure sending mail: The report server has encountered a configuration error. Mail will not be resent - Hi All, I have been trying to send ssrs report to users via email and i followed all the rules like...

Data Warehousing : Integration Services

Issue with connection to DB2 source connection manager - Hello All, In my package source table is exist in DB2 and target will be Oracle db, just a simple...

The process cannot access the file because it is being used by another process - small files are locked a bigger one works fine - SQL Server 2008R2 SP2. I have three levels of SSIS packages : master staging fileprocessing main The staging package calls the fileprocessing package...

HOW TO CREATE DYNAMIC FOLDER TO INSERT EXPORTED FILES INTO ?? - Hi All, In my actual project, my boss asked me to export data from database to csv flat files. So, as I...

Where is the ideal place to install SSIS? - We're currently working on building out a new data warehouse and I'm looking for resources or best practices on where...

Data Warehousing : Strategies and Ideas

Help needed for creating the star schema - Hi, We are planning to data warehouse the 2 different projects , to implement this we have to create the star schema...

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

SQL 2012 SSAS performance issue - Hi, We had upgraded our SSAS instance with SQL2012 Sp1 and Prior to this up gradation of SP1 there were no...