In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Developer Bundle 12 essential tools for database professionals
The SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.
 
SQL Monitor Check SQL Server performance at a glance
We consulted 1000 SQL Server professionals to make SQL Monitor’s UI as clear as possible. Start monitoring with a free trial.
 
SQL Prompt Make working with SQL a breeze
SQL Prompt 5 is the effortless way to write, edit, and explore SQL. It's packed with features such as code completion, script summaries, and SQL reformatting, that make working with SQL a breeze. Try it now.

In This Issue

Error logging with Bulk Insert

Bulk Insert statement has an important feature of error logging which is rarely used. More »


SQL Saturday #211 - Chicago

SQL Saturday is coming to Chicago, IL on April 13, 2013. Join us for a day of free SQL Server training and networking. More »


SQL Server 2012 Window Function Basics

For some time, Microsoft had a few window functions, but not the full set specified in the SQL 2003 standard. Now, in SQL Server 2012 we have the whole range, and extremely useful they are too. There's no longer an excuse to avoid them, particularly now you have Rob Sheldon's gentle introduction. More »


From the SQLServerCentral Blogs - Is BETWEEN faster than GTE and LTE?

I know I have been light on blog posting this year. They always say that life tends to get in... More »


Editorial - Development, Operations, or Accounting

I think the idea of a platform of services, such as Windows Azure provides, is a great idea. I'd love to be able to stop worrying about hardware in many cases, and even skip some of the infrastructure of networking and managing specific machines. Just having services that I could deploy to that run my database and code, would smooth out some of the hassles of Information Technology in many companies. 

However the services have to work and work well. 

I've had concerns with AWS and their outages, though Netflix has run a thriving business on that platform and learned to work within that service. I think the Azure platform is similar, and they've had a few outages, I do have concerns over where their outages have occurred. There was an expired certificate, then supposedly maintenance, later revealed to be heat issues

These issues bother me. Some are unavoidable, but some aren't. I'm particularly concerned about the expiration of security certificates. This happened again recently to Azure, and it shouldn't. I don't know if this was a problem with the development groups that build applications using certificates, the operations groups that administer systems, or the accounting groups that might need to purchase new certificates, but part of what the "cloud" should bring is expertise and better qualities of service from the people doing the work.

I can hire semi-competent people that don't pay attention to details and suffer my own outages. At least then I know I'm to blame and I can replace them. However moving to a service like Azure or AWS means giving up a lot of control. If I can't count on better services than I get in-house, I'm not sure there are many advantages to moving.

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

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:

-- Create Table1
CREATE TABLE Table1
(ID INT, Value VARCHAR(10), Flag INT)
GO
INSERT INTO Table1 (ID, Value, Flag)
SELECT 1, 'First', 1
UNION ALL
SELECT 2, 'Second', 1
UNION ALL
SELECT 3, 'Third', 2
UNION ALL
SELECT 4, 'Fourth', 1
UNION ALL
SELECT 5, 'Fifth', 2
UNION ALL
SELECT 6, 'Sixth', 1
UNION ALL
SELECT 7, 'Seventh', 2
GO

-- Create Table2
CREATE TABLE Table2
(ID INT, Value VARCHAR(10), Flag INT)
GO
-- Populate Table2
INSERT INTO Table2 (ID, Value, Flag)
SELECT 1, 'First', 1
UNION ALL
SELECT 2, 'Second', 1
UNION ALL
SELECT 3, 'Third', 2
UNION ALL
SELECT 8, 'Eighth', 1
UNION ALL
SELECT 9, 'Ninth', 2
GO

What will be the output of these queries?

--Query 1
SELECT 
 * 
 FROM table1 t1
  LEFT JOIN table2 t2 
   on t1.id = t2.id AND t1.flag = 1

--Query 2
SELECT
 * 
 FROM table1 t1
  LEFT JOIN table2 t2 
   on t1.id = t2.id
 WHERE t2.flag = 1

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

This question is worth 1 point 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 Step by Step

Teach yourself the programming fundamentals of SQL Server 2012—one step at a time. Ideal for beginning SQL Server database administrators and developers, this tutorial provides clear guidance and practical, learn-by-doing exercises for building database solutions that solve real-world business problems.

Get your copy from Amazon today.


Yesterday's Question of the Day

What will be the result if we have following function and executing Query2?

CREATE Function fn_tst
(@str VARCHAR (MAX))
Returns @tbl Table 
(
str1 VARCHAR(100),
str2 VARCHAR(100),
str3 VARCHAR(100),
str4 VARCHAR(100),
str5 VARCHAR(100),
str6 VARCHAR(100)
)
AS
BEGIN

Declare @Mstr VARCHAR(3000),
@cmpstr VARCHAR(2000),
@str1 VARCHAR(100),
@str2 VARCHAR(100),
@str3 VARCHAR(100),
@str4 VARCHAR(100),
@str5 VARCHAR(100),
@str6 VARCHAR(100) 

DECLARE @i INT

SELECT @Mstr=@str ,@i=1

WHILE @i<= 6
 BEGIN
  SET @cmpstr=SUBSTRING( @Mstr ,1,ISNULL(NULLIF( CHARINDEX(',',@Mstr),0)-1,LEN(@mstr)))
  SELECT @str1=CASE WHEN @cmpstr Not like 'A%' THEN NULL ELSE @cmpstr END,
        @str2=CASE WHEN @cmpstr Not like 'B%' THEN NULL ELSE @cmpstr END,
        @str3=CASE WHEN @cmpstr Not like 'C%' THEN NULL ELSE @cmpstr END,
        @str4=CASE WHEN @cmpstr Not like 'D%' THEN NULL ELSE @cmpstr END,
        @str5=CASE WHEN @cmpstr Not like 'E%' THEN NULL ELSE @cmpstr END,
        @str6=CASE WHEN @cmpstr Not like 'F%' THEN NULL ELSE @cmpstr END

  SET @Mstr= SUBSTRING(@Mstr, CHARINDEX(',',@Mstr)+1 , LEN(@Mstr) )

  SET @i=@i+1
 END 

INSERT INTO @tbl 
 SELECT @str1 , @str2 ,@str3 ,@str4 ,@str5 ,@str6 

RETURN

END

--##Query 2
Select *
  from fn_tst('AA,BB,CC,DD,EE,FF')

Answer: NULL,NULL,NULL,NULL,NULL,FF

Explanation: The case statement will assign 'FF' to @str6 ,Remaining varaibles contains NULL values by default and not assigned with corresponding values of AA,BB,CC,DD and EE. The values are assigned to variables after completion loop.

Ref: http://msdn.microsoft.com/en-IN/library/ms181765.aspx

» Discuss this question and answer on the forums

SQL Server Transaction Log Management

When a SQL Server database is operating smoothly and performing well, there is no need to be particularly aware of the transaction log, beyond ensuring that every database has an appropriate backup regime and restore plan in place. When things go wrong, however, a DBA's reputation depends on a deeper understanding of the transaction log, both what it does, and how it works. An effective response to a crisis requires rapid decisions based on understanding its role in ensuring data integrity.

Get your copy from Amazon today.


Featured Script

How To Check SQL SERVER Uptime Through T-SQL

Services uptime can be checked through WMI scripts and other methods also. As a DBA most of us would like to know the uptime of SQL Server 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

find plan handle of query already executed - I am trying to find actual execution plan of a adhoc query. SO i want to find the plan handle...

Working with Oracle Dates with SSIS - I am trying to create an SSIS package that copies employee data from an Oracle database and insert it into...

SQL Server 2005 upgrade to 2008 or 2012 - Hi Guys, All the applications that I develep to my clients are in SQL Server 2005 and VB.NET I would like to...

Convert SQL 2005 apps to SQL 2008 or SQL 2012 - Hi Guys, All the applications that I develep to my clients are in SQL Server 2005 and VB.NET I would like to...

SQL Server 2005 upgrade to 2008 or 2012 - Hi Guys, All the applications that my clients have are in SQL Server 2005. I was thinking in ask to upgrade their...

File group is running out of space - Hi, I am getting below specified running out space alert even autogrowth option is enabled and disk got enough space. Alert message...

Database IO is frozen for snapshot / IO is thawed - I notice in my error log there are messages which says the Databasename IO is frozen for snapshot then follow...

Getting an error "Exception of type 'System.OutOfMemoryException' was thrown." during stored procedure execution - Hi I am running a stored procedure that have lacs of select command but query is showing this error. "An error occurred...

SQL Server 2005 : Data Corruption

2k5 MDF Emptyfile -> NDF - Hi, I wonder if someone can help? I was attempting to move contents of mdf to a secondary data file ndf but...

SQL Server 2005 : SQL Server 2005 General Discussion

prerequisite to apply service pack 4 - hello experts, In one of my production server i want to apply service pack 4 current configuration : sql server 2005 standard...

SQL Server 2005 : SQL Server 2005 Strategies

Moving prod DBs from local disk to SAN with absolute minimal downtime - [b][u]Background[/u][/b] I have a number of production databases that are currently on the local disk on the server. We have recently...

SQL Server 2005 : SQL Server 2005 Integration Services

thread count for ssis package and .bat file - hello all- i have 6 threads available. my ssis package has 4 containers which are using 4 threads. if each one...

Creating instances of excel file - I'm using SQL Server 2008 BI. I have an oledb source outputting data into an excel destination. I want the...

SQL Server 2005 : T-SQL (SS2K5)

Assing point to race times - I bulk insert a csv file (generated from our timing laptops at race events) into storage table in our database. the...

Query to retrieve a column depending on variable value? - Hi guys, I have a problema that I can't find a clue to face it: The situation: I have three different tables...

SQL Server 2005 : SQL Server Newbies

identity and unique identifier - In what situation or condition we use[b] identity[/b] and [b]unique identifier(new id())[/b]. can anybody explain with a example...

create table to group th e people of same department - NAME AGE SALARY DEPARTMENT SHARATH 24 21000 MARKETING RATHAN 35 21000 MARKETING RATS 28 21000 IT DARSHAN 23 20000 IT LINI 25 25000 IT SHAN 23 20000 MARKETING SUDARSHAN 22 20000 IT to above given table i wan to write a qury that display IT MARKETING RATS RATHAN DARSHAN SHARATH LINI SHAN

SQL Server 2008 : SQL Server 2008 - General

Database Restore Errors - Hello, I am running the below restore script and getting errors. Please advise what I need to do to correct. [code] RESTORE...

Linked server - I created a linked server in ServerA to ServerB but another user created linked server in ServerB to ServerA. Does...

SQL Server Agent account for job - Hello - I have a developer that wants to set up a job to run ActiveX Script. They can't see anything under...

T-SQL help - Hello I need one help I have list of dates from those date I need to display all dates with schoolIDs The list...

*MAY* need to switch DBs from Full Recovery to Simple, due to backup space issues... - First and foremost, I'm working with our backup admin to find a better solution for the problem, that won't require...

Transaction Log Backup Always Generates Job Failed Message - Hi everyone, I am having problems getting my daily transaction log backups to work on a maintenance plan. The plan is...

ANSI settings - Hi All, There is an application that uses DSN. The connection type is ODBC. In the DSN the ANSI settings are...

Snapshot, Replication...something to get OpenEdge into SQL 2008 R2 - I would love to setup a transactional replication between OpenEdge 11 and SQL 2008 R2...if possible. I have OpenEdge setup as...

SSIS SQL Server and File Server Setup - Good morning, Wondering if I could get some opinions on a setup for an ETL environment with hardware that has...

Using AOG spanning FCI. - I have read that all disk sizes and drive letters need to be the same when using AOG. Is this just...

Query Performance - Hi, I have written a script in which I am performing various scenario.. First I will validate if a voucher...

Mapping Different Logins to Same User in Database - Hi, My question is more on simplifying things for my environment so hoping that I can clearly propose the idea on...

Replication - missing records and large Msrepl_commands table - Hi We have run into a problem with missing data in our subscriber when using replication even though replication manager says...

Script out Agent job to run DBCC CheckDB or Maintenance Plan Check DB Integrity Task? - Or is this a "six of one, half-a-dozen of the other" sort of question, which really will boil down to...

How to change the report server log directory - Hi, I did the change in the config file found at at C:\Program Files\Microsoft SQL Server\MSRS10_50.XXX\Reporting Services\ReportServer\bin. By Default logs is created...

Including null complex type elements in XML output - Given two entities, mortgage and customer, each in a table and a third table linking customers to mortgages, I need...

TDE - HI, Can any one help me to scipt to Enabling TDE(Transparent Data Encryption) on all user databases. Please help. thanks

Migration detail for SQL SERVER 2000 TO SQL SERVER 2012 - HI i need help for migration the coding in sql server 2000 to sql server 2012 . regarding this what are the...

Output Parameter in Stored PRocedure - Hi all, I have created a sp in which I want to extract last identity value after doing insertion.... But...

executing batch file from remote server using SSIS - Can anybody help me>>>> we have a batch file in 227 server(admin login) and i want to execute it through SSIS...

Corruption - Renaming of system data databases to ldf :( - Hi, This morning a made a proper boo boo while building a new SQL Server 2008 R2 install. After the installation...

Updating multipe databases on multiple serves - Is there a way we can update multiple databases on Multiple servers? I am trying to sync multiple databases but...

Best Way to Speed Up Queries - Good Day, Please i need to speed up the runtime execution of queries on my online server, this database has over...

Server Review - What are the things we need to consider for reviewing a windows server keeping SQL server as our point of...

Splitting the rows -- distributing on logic - Hi Experts , here is sample table and data create table #sample ( Invtid varchar(255) ,[Final SiteId] varchar(255) ,Whseloc varchar(255) ,Days int ,Qty int ,[Aging Stock] int ,Priority int ) insert into #sample select '11003291001-----NOB30000','0V003','A00A00',269,285,305,1 union all select...

Data Compression in SQL Server: Shubham Saxena - Dear Friends, Please help!! I need to build up a function or stored procedure to compress/decompress a large object. like image or blob...

Named Instance and specified port not connecting - Hi all I have opened up a port on a remote SQL instance and can see that the port is LISTENING...

Need Create View Script for all tables - I manage a SQL 2008 R2 Production environment for an application. We have a new requirement to create views( simple--select...

Restore Change Data Capture database without KEEP_CDC option - Hello, Suppose you have a database that you need to restore on another server. This database has SQL Server CDC enabled,...

Subscriptions - Failure sending mail. 'Include Link' works, 'Include Report' does not. - Hi Everyone I'm hoping you can help me, I've followed most links on the net and decided to post. SQL 2008 R2 Local...

Differences between sql server 2008 and 2008 R2 - I did some research to find the differences between between sql server 2008 and 2008 R2 but couldnt find much....

SQL Server 2008 : T-SQL (SS2K8)

CHAR(10) sometimes work - IN SQL I want to print for example [quote]Ali is going to Alex[/quote] so I said [quote]declare @x varchar(max) set @x = 'Ali' + CHAR(10) + 'is...

Insert into new table based on conditions - Hi all, I'm struggling with a query which runs forever and I can't figure out what the problem is. In attachment you...

how to rename columns that you are unpivoting on? - I've successfully unpivoted Months01 through Months02 with a query that looks like this: [code="plain"]/****** Script for SelectTopNRows command from SSMS ******/ SELECT...

Calculate number of days missed per term - Need to update a new column (DaysMissed). Trying to calculate the number of days missed per term (accumulative) based on the...

Improve performance of query involving linked server - I am trying to make improvements to a job that does the following. 1) Load a table with about 40,000 rows...

Stored Procedure Records - I have created a stored procedure that will return approved purchase orders when executed. However, if I have it kickoff...

Selection using date held as a string - In our database we have a parameters table that holds a parameter type (e.g. D for date, N for number,...

MERGE TSQL in SQL SERVER 2008 - Hi ALL, Need one help/suggestion in tsql using MERGE in sql server 2008.I'm using the below code to do upsert in...

i need the max length value of every column in every table in a database - I'm creating some dynamic sql to loop through each table and column and basically generate some dynamis sql that will...

Generate all possible number combinations for a provided list of numbers - Hi Experts, I want to get "all possible number combinations" for provided list of numbers. As an example, if I have 4...

Query help - [code="sql"] I need a query to get the required output.. DECLARE @Table TABLE( stid INT, stuName VARCHAR(200), studwor varchar(100) ) INSERT INTO @Table SELECT...

Query help - [code="other"]I need a query to get the expected output Table:Student stguid stuName studwor stid ------------------------ ----------------- ----------------- ----------- 642-4d5d-9af0-4c7a18dd ChrisName Chris 255 4171-8655-2de255b88e08 ChrisCity SAN City 179 2a0d-4100-bd1c-343882 ChrisCou

difference betwwn inner join and intersect in sql - I would like to know when to use inner join/outer join and intersect/except as they probably are doing the same...

SQL Server 2008 : Working with Oracle

Need better solution to Import from Oracle - I maintain BI that needs to import the data from Oracle every day. I use the import wizard to create...

SQL 2008 R2 linked server to 11g - I've been tasked with setting up an Oracle linked server on a new SQL 2008 R2 install. I have installed...

SQL Server 2008 : SQL Server Newbies

SysDBs.logrotate - Hello Masters, On one of my server our client had create one scheduled job "SysDBs.logrotate" which is running every sunday, but...

Help with replication scenario - I have a scenario at work where I need to replicate (in the general sense, not necessarily in the "sql...

importing an excel file - hi professionals. I am trying to import and excel spreadhseet into the database by right clicking the database, tasks, import. I...

Error authenticating proxy - Hi, I've got a SQL Server 2008 job which ran ok yesterday but has fail today with an error message ' Error...

Had a job failure but it doesn't appear in the job history - Hello, I am documenting job failures in the last two months and I cannot find the one that we had in...

SQL Server 2008 : SQL Server 2008 High Availability

Quick Transaction Replication limitation question - I've searched and only found the limitations on Articles which is 32,767 but I need to know how many publications...

multiple instance sql cluster with different windows configuration - We have a two node cluster. In order to utilize the max resources we are planning to add more instances...

Is SAN admin correct? Windows 500 GB limitation on cluster failover? - Hello I was recently pulled into an ongoing project that is running into problems. The old project manager let me know...

SQL Server 2008 : SQL Server 2008 Administration

Logical CPU Count and Hyperthread Ratio ? - Hi, The query output as below [code="sql"]SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],cpu_count/hyperthread_ratio AS [Physical CPU Count], physical_memory_in_bytes/1048576 AS...

Length limitation on domain accounts? - I'm attempting to create a login from a Windows account like so: [code="sql"]USE [master] GO CREATE LOGIN [DOMAIN\MyLongerThan20CharacterServiceAccount] FROM WINDOWS WITH DEFAULT_DATABASE=[master]; GO[/code] But this...

Best practice to create a new database for Datamart - Hi, Is there any guidelines for creating a database for OLAP system.

Scripts To Monitor SQL SERVER - Hi Everybody , it's been two or three weeks I'm trying to find scripts to monitor different SQL SERVER indicators, but...

Installing SQL server 2008 Express from windows application - Hi I am trying to install SQL Server 2008 Express from Window Application I am using this command [code="other"] "/qs /ACTION=Install /SkipRules=VSShellInstalledRule RebootRequiredCheck /HIDECONSOLE...

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

SQLServerCentral.com : Anything that is NOT about SQL!

Encrypting files with PGP - While this is not related to sql server I imagine that many database administrators have had to deal with this...

What does Microsoft Update do? - For MS SQL Server, what does the Microsoft Update tool actually update? I had assumed that it would update the version...

SQLServerCentral.com : SQLServerCentral.com Website Issues

Any Plans for a Stairway to SQL Server Analysis Services - More of a question than a comment... do you have any plans for a "Stairway to SQL Server [b]Analysis[/b] Services"? Andrea

Reporting Services : Reporting Services

Trying to get SSRS 2005 reports to work on a user's machine - We've been replacing or upgrading users' machines from Windows XP to Windows 7. After doing that one of the users...

?? on =count adding a "When" - using this expression on a total in a matrix =count(Fields!xmark.Value) can I add a condition onto this so it only counts...

How to Get a List of All Reports Using a Specific View - Is there a way to get a list of all reports that are using a specific view?

RS in Sharepoint integrated mode and semicolon as export file field delimiter - First a bit ambigious on wether to ask for a new Sharepoint Topic group, or posting this question in RS...

code to multiply dose by times administered - Hey Guys I have to create a report which shows how many times a drug was administered and the dose is...

code to - Hey Guys I have to create a report which shows how many times a drug was administered and the dose is...

Creating Parameters for drill down charts in SSRS which references Cube (SSAS) - Am trying to build simple drill down Column charts in SSRS which uses SSAS (Cube) as a datasource: Requirment: To give...

Data-Driven Subscription - Hi there, Can someone please help? I have created an Invoice Report in SSRS 2008R2. If the report produces for example 150...

Query that needs to be used in Cascading parameters report - Hi All, I need a small help in SSRS report. My report has 5 parameters. @ApplicationName - 4 Names comes in the dropdown...

Basic CASE Statement in Cascading parameterized report - Hi All, Writing a small CASE statement that in SQL Server. Below is what I'm try to accomplish. If the user selects...

Line chart not showing multiple series data - I have a line graph chart which should display either a single or multiple series. It has three variables which...

code to show top five entries in a pie chart - Hello I have a pie chart which displays many legends on a pie chart but i need to write sql code...

Data Warehousing : Integration Services

How to create SSIS package in SQL Server Database System - Hi Friends, how we create packages in sql server database system. whatever iam creating packages those are saved in FileSystem. but...

VS 2005 Task Config Error - VS 2005 throws this error when I try to configure an Execute Process Task: 'The task with the name "Execute Process...

Data Warehousing : Analysis Services

Login failed for user 'NT Service\MSSQLServerOLAPService' 28000 - I am using SQL Server 2012 developer edition on Windows 7. I have the following error attempting to process a...