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

What Do You Want to Learn?

This is a busy time of year for me. Between September and October, I've attended 11 events in 7 cities and given 23 talks on 8 topics. It's been a hectic time and I'm looking forward to snow falling in the Colorado mountains and taking some time off to relax and enjoy life away from work.

It seems that every year I'm working on and delivering 3 or 4 new sessions on a variety of topics. I find the challenge of learning more about a technology or feature of SQL Server to be exciting, and it seems there's no shortage of new things I'd like to learn. As I watch different presentations, I find myself itching to work with the topic and learn how it might be useful for me. I often end up taking some area of SQL Server that interests me, and I think will interest other, and building a talk.

However I rarely hear from the rest of the community about the topics they'd like to learn about. This week I'd like you to think about the topics that you wish someone would present at your next user group, SQL Saturday, or conference:

What do you want to learn?

Let me know this week. I don't know that I'll tackle many of the ideas, but I'm sure there are speakers out there that will examine your opinions and consider building a presentation that might be in demand.

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 ( 13.4MB) feed

MP4 iPod Video ( 15.7MB) feed

MP3 Audio ( 3.3MB) feed

Feeds are available at iTunes and Mevio

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

ADVERTISEMENT
SQL Backup

Want faster, smaller backups you can rely on?

Use SQL Backup Pro for up to 95% compression, faster file transfer and integrated DBCC CHECKDB. Download a free trial now.

SQL Data Compare

Compare and synchronize database contents

“I've been really missing a neat data comparison tool - SQL Data Compare Pro seems to be the answer to my prayers.” Jan Hansen, Database Architect. Download a free trial.

SQL Monitor

What does normal looks like on your servers?

New benchmarks in SQL Monitor instantly show you if a performance spike is a problem, or within normal SQL Server behavior. Try it live.

Featured Contents

 

Blocking Users by IP

Brian Kelley from SQLServerCentral.com

SQL Server MVP Brian Kelley brings us a great new article that solves a problem that might help your security. In this short piece, we learn how we can use logon triggers to block users based on their IP address. More »


 

SQL Server SQLCMD Basics

Additional Articles from SimpleTalk

Sqlcmd makes many SQL Server tasks, such as automating test runs and maintenance tasks, easier and quicker. The sqlcmd command-line utility is valuable to any database developer or DBA as the prime means of executing batches of SQL Statements to SQL servers, and saving results to file. Rob Sheldon gives you the basic facts about this great utility. More »


 

From the SQLServerCentral Blogs - Building The Redneck Treadmill Desk

Tracy McKibben from SQLServerCentral Blogs

Don’t tell Brent Ozar, but here’s another DBA writing another helpful article about shrinkage. Before he gets bent out of... More »


 

From the SQLServerCentral Blogs - PowerShell: Delete Unneeded Rows/Columns In Excel

william_a_dba from SQLServerCentral Blogs

I had a project to automate the import of a large number of excel files via SSIS into SQL 2012.... More »

Question of the Day

Today's Question (by Rob Stebbens):

 This is a really simple problem that caught out all of my staff, with them making an incorrect assumption regarding the use of MAX(). A currency table contains the following data.

Currency

Description

Exchange_Rate

Date

Operator

USD

US DOLLAR

0.011978

2013-07-14

SYSTEM  

USD

US DOLLAR

0.011980

2013-07-13

SYSTEM  

USD

US DOLLAR

0.011979

2013-07-14

SYSTEM  

USD

US DOLLAR

0.011979

2013-07-13

SYSTEM  

USD

US DOLLAR

0.011990

2013-07-10

SYSTEM  

USD

US DOLLAR

0.011999

2013-07-09

SYSTEM  

What does this query return?

Select max(date), max(exchange_rate)
 from currency

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

Delivering Business Intelligence with Microsoft SQL Server 2012

Equip your organization for informed, timely decision making using the expert tips and best practices in this practical guide. Delivering Business Intelligence with Microsoft SQL Server 2012, Third Edition explains how to effectively develop, customize, and distribute meaningful information to users enterprise-wide. Learn how to build data marts and create BI Semantic Models, work with the MDX and DAX languages, and share insights using Microsoft client tools. Data mining and forecasting are also covered in this comprehensive resource.

Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday'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;

Answer:

  • NULL,0,1,0,0,1,4
  • James,2,2,2,1,1,4
  • Eric,1,1,1,1,1,4

Explanation:

The answer is

NULL,0,1,0,0,1,4
Eric,1,1,1,1,1,4
James,2,2,2,1,1,4

The explanation:

  1. Count and Count_Big returns the number of items in a group.  Count always returns int while Count_Big returns bigint.
  2. Coalesce returns the first nonnull expression among its arguments
  3. ISNULL replaces the value with the specified replacement value.
  4. DISTINCT returns all distinct values (removes duplicates) from the result set.
  5. @@ROWCOUNT returns all rows affected by the last statement.  If you happen to have over 2 billion rows, use @@ROWCOUNT_BIG.

NOTE: You will notice that James was case insenstive (COLLATE SQL_Latin1_General_CP1_CI_AS ).  If you wish to return counts based on case sensitivity, you will have to add

COLLATE SQL_Latin1_General_CP1_CS_AS

to field name to make the field values case sensitive as such:

CREATE TABLE TEST

( NAME VARCHAR(20) COLLATE SQL_Latin1_General_CP1_CS_AS )

References:


» Discuss this question and answer on the forums

Featured Script

Right or Wrong, Dynamic SQL AND a Cursor

Kurt Zimmerman

OK, so I'm no big fan of dynamic SQL or cursors, however there are times where they do the job.  

Case in point, today I had to restore a database and a bunch of transaction logs.  To do this I started by manually restoring the database but because I had a bunch of transaction logs I felt I would feel the time would be better spent writing a quick transaction log restore script.  I know this will occur in the future and I like about having the necessary control over the code.  

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

how to write Pivot Query for Following - Hi Everyone I have wrote a query as SELECT PD.Name,PD.ContactNumber,Sum(cast(dbo.GetNumericValue(GI.AAY_Wheat)as numeric(18,0)))[Allocation], 'Week '+cast(DATEPART(wk, GI.EnteredOn)as varchar(50)) AS WeekNumber, sum(cast(dbo.GetNumericValue(GI.AAY_Wheat)as numeric(18,0))) AS...

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

Replication keeps snapshotting the same tables - Hi I am creating replication atm and one of my publications (the distribution job) seems to be in a loop delivering...

Logic for resolving issue - Hi Expert, I need your help for resolving my one issue Table script, [code="sql"]create table tmp_medal (medal_name nvarchar(50) null, winner_id int null) [/code] insert statements, [code="sql"]insert into tmp_medal...

how can integrate report builder with ssrs - l need help with ssrs

What is the correct way to restore a database when users are still connected? - Hi, I have been a DBA for good few years now and have always had trouble explaining the best way...

Control flow viewer for SSIS 2012 - Hi, in case of a SSIS pkg having more tasks, in order to document the control flow I should be useful...

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

Log Shipping broken by Veeam - WE are moving into SQL 2012 and concurrently moving to Veeam for Virtual Server backup. We have found that our...

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

"Failure sending mail: The specified string is not in the form required for an e-mail address.Mail will not be "resent. - Hi, I am getting "Failure sending mail: The specified string is not in the form required for an e-mail address.Mail will...


SQL Server 2012 : SQL Server 2012 - T-SQL

To get employee hierarchy - Hi [code="sql"] -- Create an Employee table. CREATE TABLE dbo.MyEmployees ( EmployeeID smallint NOT NULL, FirstName nvarchar(30) NOT NULL, LastName nvarchar(40) NOT NULL, Title nvarchar(50) NOT NULL, DeptID smallint...

Query reports duplicates when running a GROUP BY but not when I try to see the actual records? - I have a query that joins several tables and I am trying to identify duplicates, so I am running some...

UDF - IF(@parameter) - Hello Is it possible to build an udf that accepts 3 parameters(@a,@b, @c) and, if @a is null (build cte1) else...

Issue with View - Error - Hi there, I am currently looking at someone else code and am getting an error when I try and run...

What does *= mean in a Select statement? - I'm no genius, and I've just taken over a System that has many more stored procedures than I like. Some...

OpenRowSet does not delete records in Remote DB - Hi All, I am trying to execute the following query in SQL SERVER 2012. SELECT * FROM OPENROWSET ('SQLNCLI', 'Server=DBName;UID=newuser;PWD=newuser;', 'exec TESTDB.[dbo].[DeleteTableData]') I am...

OpenRowSet does not delete records in Remote DB - Hi All, I am trying to execute the following query in SQL SERVER 2012. SELECT * FROM OPENROWSET ('SQLNCLI', 'Server=DBName;UID=newuser;PWD=newuser;', 'exec TESTDB.[dbo].[DeleteTableData]') I am...

Conditional Default value for a column - Dear All Whant to assign conditional default value during table creation Like create table aa ( cc int null , bb int default (case...

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

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


SQL Server 2008 : SQL Server 2008 - General

How to migrate SSIS form Sql 2005 to Sql 2008 r2 - Hi, we have migration work which we need to migrate our sql 2000 server ,sql 2005 server to sql server 2008...

Memory "leak"!! - Hi, We have a procedure that takes a year as parameter and calculates the revenue for the company from all the...

Patch SQL 2008 Express - Maybe a dumb question, but can you patch express edition?

collation conflict in union conflict - Hello All - I am trying to run the following statement but it keep erroring due to a collation conflict - Without...

Embedd Image from databse mail - Hi I want to send an image in mail body from database mail [code="sql"] Declare @html_docy nvarchar(max) = '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML...

please help with cte - I am trying to use a cte to compile this query and I am getting this error 'cteSource' has fewer...

remove duplicates and sum column - Hi Professionals. I am enquiring if it is possible within SQLServer 2008 to remove duplicate rows in the DB and sum...

How are Sys.Object Object_IDs Allocated? - Hi, We recently came across an issue in Oracle where the ID of new contraints hit the max value of...

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

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

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

Pseudo PIVOT query - I have data in my base table as shown below. [img]http://www.sqlservercentral.com/Forums/Attachment14581.aspx[/img] I need to convert this data into the format below for...

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

Running Putty through Agent, package hangs. - I'm running into a odd error using Putty in one of my packages and having it scheduled through the agent....


SQL Server 2008 : T-SQL (SS2K8)

Best way to determine tables which contain a list of specific columns - I’ve got an issue which has come up with enough regularity that I wanted to see if you knew of...

Getting Current Stock Report - Hi Experts, I have a table for maintaining stocks as follows, InventoryId - bigint TransactionDate - datetime InwardId - int ProductId - int InventoryStatus - int (0 - Available, 1 - Sold, 2...

Query Help - Hello, I am attempting to run the following query.. select DM.DOCNUM, DM.VERSION, G.U_G, G.USER_GP_ID from GetWindowsACL_Chicago G join MHGROUP.DOCMASTER DM on G.Matter = DM.C2ALIAS where G.U_G...

Stuck with query - Hi All Not sure why I'm having such a mental block on this. Below is sample code and data with inline...

comparing ip addresses in sql - i have a table with 2 fields IpMax, IPMin i want to compare an ip whether it is in the range(in...


SQL Server 2008 : July CTP

SSRS 2005 Matrix Total column - Hi, I need help on using SSRS 2005 Matrix. I need to add the last 3 columns. Kindly refer on the...


SQL Server 2008 : SQL Server Newbies

SQL Server 2008 Data Compression - Can a table be compressed (row or page) as an online operation? What is the syntax to do it as...


SQL Server 2008 : SQL Server 2008 High Availability

Add third node sql cluster - I have a 2 node cluster (2-physical servers) “instance1” is passive for “instance2” & “instance2” is passive for "instance1" vice versa....

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


SQL Server 2008 : SQL Server 2008 Administration

Error Restoring Database including Trasaction Log - I know that I should provide the error but I really can't do that now. This works: [code="sql"] RESTORE DATABASE MyDW FROM DISK = 'D:\Backup\Complete\MyDW\MyDW_backup_2013_11_06_120630_5254371.bak' WITH...

"Table Scan Behavior" in NUMA - I was reading at msdn article at [url]http://technet.microsoft.com/en-us/library/ms345403(v=sql.105).aspx[/url] and found that in case of table scan behavior , "[i]A table scan...

Memory not being shared between instances - Hi, I have a 2008 R2 64b server with 4 instances on it. One is using ALL the RAM and the...

MAXDOP setting ?? - I have sql queries which return around 40-50 millions records for loading information into Reporting database. I was trying to...

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

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


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

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

udf very slow? - I created a User Defined Function that performs some arithmetic calculations on a few columns and returns an amount. The UDF...

Simple query maxing CPU - Good afternoon everyone, I've got a simple query that should be no problem at all but which maxes out the CPU...


SQL Server 2005 : Administering

Logshipping between machines not on a domain - Hi, I'm not sure if this is the right topic or thread to post my concern on. What I am trying...

Strange column names in execution plan - In the attached execution plan, at the sort operator(50% cost),when i hover over the operator in the plan i see...

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

Script to Change Collation of User Database - Hello, Is their is any Script to change collation of user database ? The process Which I know; First Backup the User database 1.creation...


SQL Server 2005 : Business Intelligence

SSIS Script task with C# Help - Hi Fellow, I have SSIS Package which does ETL process. I have For each loop container in that container I...


SQL Server 2005 : Development

COM class error - Hi, In a page, I written a code to generate Excel file using Com+ Component. On server, when my source code...


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

SQL Server replication requires the actual server name to make a connection - Hi, I'm working on a virtual server with SQL Server 2005 installed. The server was called VM_NNNN_ABC1 and the SQL Server had...


SQL Server 2005 : SQL Server 2005 Strategies

How disable DBCC CHEKCDB command - Hi, For error someone run DBCC CHECKDB in production database, now i want disable or cancel this command for prevent this...


SQL Server 2005 : SQL Server Express

Getting a "not a valid login" error when installing SQL 2008 R2 Express - I've got a SQL script to create a database and populate it with the necessary tables, views and stored procedures....


SQL Server 2005 : SQL Server 2005 Performance Tuning

Slow performance - I have face slow to retrieve the data from particular table. This table frequently data inserted and same time 50 user...


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

OLE DB Error Redirect - Generic Error Descriptions - I have a very simple package I'm experimenting with error handling on, which takes a flat file (CSV) and then...

Excel connection manager in BIDS Cannot aquire connection - I have SQL SERVER 2005 installed and Excel 2013. I have created a dtsx package using SQL server Import and export...

SSIS - Failed to retrieve data for this request. - I can run SSIS Wizard to do on the fly exports/imports on my production server. I can even tell it...

Ideas on resolving error -- "VS_NEEDSNEWMETADATA". - Hi I am running a large dtsx package and in a data flow task I am getting the following error...


SQL Server 2005 : T-SQL (SS2K5)

string splitting with charindex - Hi i have the following field in a progress db which contains analysis codes for a given account. I need...


Reporting Services : Reporting Services

Show a Customized Text in the Prompt List - Goal: Display the text "All State-Province" instead of "All geographies" at the prompt list in SSRS Problem: I tried googling around and in...


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

Development environment on a separate domain !!?? - Our management wants to move our development environment to a separate domain. They say it's good to isolate it so that...

Chart - Partial Months - I am hoping someone has some advice on this. I have the query below that shows totals for each month. Everything...

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


Programming : General

SSDT 2012 REquired Permissions - We are not allowed to have persistent admin privileges on our workstation where I work. After I installed SSDTBI 2012...


Programming : XML

Spliting XML file into Parent/Child table using SSIS package - Hi, I want to load xml file into table. The xml file has many hierarchy level and relationship. I want to...


Programming : Powershell

SQL inventory powershell script - Hi everyone. Hope someone can offer some advice on my issue. I have been learning Powershell with the aid of the...


Data Warehousing : Integration Services

SSIS - Remove unused variables and such from SSIS ? - How do you remove things like unused variables from SSIS ? I am using 2008.

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


Data Warehousing : Analysis Services

Link server Issue - Hello expert, Can you please help me? I have created the c# project VFE_AS_Functions to build the Like function to be used...

SSAS 2008 R2 - Problems with total of a calculated measure in Excel - Hi to all. I try to explain my problem. I use SSAS 2008 R2 and i have a Multidimensional Cube called SALES. In...

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


Database Design : Disaster Recovery

SS2012 - Log Shipping vs Mirroring vs AlwaysOn - I'm looking for an easy failover technology, in the case where our primary SQL Server 2012 instance goes down. I have...


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

Query help: OUTER JOIN with GROUP BY clause - I am querying two tables: [b]tblPurhcaseOrders[/b] - holds purchase orders with various columns, one of which is a foreign key called OrderTypeID [b]tblOrderTypes[/b]...

Query Help - Hi, SQL Server Version 2000 [code="other"] create table books(book_name varchar(10),code varchar(3),qty int,day varchar(10)) insert into books values('.Net',null,null,'x') insert into books values('Book1','NBC',2,'Mon') insert into books values('Book2','NAA',1,'Wed') insert into...

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