In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Source Control Get your SQL Server database under version control now!
Version control is standard for applications, but databases haven’t caught up. So how can you bring database development up to speed? Why should you start? Find out…
 
SQL DBA Bundle Top 5 hard-earned Lessons of a DBA
New! Part 4, ‘Disturbing Development’ by Grant Fritchey, features the return of Joe Deebeeay and a server-threatening encounter with ORMs - read it here.
 
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

Usage of CTE - Trick with Dates

This article exlores the usage of CTEs as a replacement to cursors in order to generate additional data by applying logic to existing data. More »


Attention users running SQL Server 2008 & 2008 R2!

Updates to the latest CUs for SQL Server 2008 and SQL Server 2008 R2 from Aaron Bertrand. More »


PASS Data Architecture Virtual Chapter presents Steve Simon

On Thursday June 20 at 12 noon Central time, Steve Simon will discuss the challenges of designing financial warehouses. More »


Managing Slowly Changing Dimension with MERGE Statement in SQL Server

Slowly Changing Dimension (SCD) Transformation is a quick and easy way to manage smaller slowly changing dimensions but it has several limitations and does not perform well when the number of rows or columns gets larger. Arshad Ali explores some of the alternatives you can use for managing larger slowly changing dimensions. More »


From the SQLServerCentral Blogs - Pivoting weird data in SSIS – Sort of…

Sometimes we get data in some bad forms. For example look at the below table: The names and the amounts are... More »


Editorial - The Best Programmers

Are you one of the best people at your job? If you are, then do you experience some of these feelings and look to move on?

  • Frustration
  • Boredom
  • Lack of a challenge

Or have you settled into a routine that you like and can live with? If it’s the latter, you might not really be one of the really talented people. Read this piece on How to Keep Your Best Programmers. I found it interesting that it looks at the motivations and reasons why very talented people might not like a job and move on. I think it's mostly correct, though it's not necessarily talent that determines whether people stay or go.

I think most people find these same frustrations or issues with their jobs. Whether you're a guru level programmer, or a basic beginner, if you perceive an inversion of meritocracy (or you are bored, or you don't enjoy the job), you'll look to leave. Whether you can is another thing completely.

Many of us have various levels of responsibility, to our families, creditors, or even ourselves. Those responsibilities may cause us to rethink the problems with our current job, and appreciate the security of just having a job. This is especially true in today's world where loyalty is in short supply from employers, and many of us know that the next position may not be more stable in terms of employment for the foreseeable future. As the saying goes, the devil you know is better than the devil you don't.

That's the crux of the issue. A job is a job. I've found that to be true across many positions over the years. Some I enjoy more, but ultimately there are always tasks or duties in every position that are drudgery. They are just work. As long as you understand that, and appreciate the challenging and interesting things about your job, you'll be fine. It's also why I place more importance on the other staff than I do on the job responsibilities. It's much easier to put up with a boring or annoying job if the people are interesting than it is to put up with a great job when the people suck.

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

How many rows will the select statement return as a result?

CREATE TABLE Emptable
(Empid INT IDENTITY,
 Departmentid INT,
 Name VARCHAR(10),
 Addr VARCHAR(10))
GO
INSERT INTO Emptable
 VALUES
 (2,'Name1','Addr1'),
 (5,'Name2','Addr2'),
 (6,'Name3','Addr3'),
 (3,'Name4','Addr4'),
 (2,'Name5','Addr5'),
 (3,'Name6','Addr6'),
 (4,'Name7','Addr7'),
 (5,'Name8','Addr8'),
 (6,'Name9','Addr9'),
 (7,'Name10','Addr10')
 GO

SELECT *
 FROM Emptable
 WHERE (Departmentid <> ALL (SELECT Departmentid 
                              FROM Emptable AS Emptable_1 
                              WHERE (Empid < 5)
                             )
        )
 GO

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.

SQL Server 2012 Data Integration Recipes

SQL Server 2012 Data Integration Recipes provides focused and practical solutions to real world problems of data integration. Need to import data into SQL Server from an outside source? Need to export data and send it to another system? SQL Server 2012 Data Integration Recipes has your back. You'll find solutions for importing from Microsoft Office data stores such as Excel and Access, from text files such as CSV files, from XML, from other database brands such as Oracle and MySQL, and even from other SQL Server databases. You'll learn techniques for managing metadata, transforming data to meet the needs of the target system, handling exceptions and errors, and much more.

Get your copy from Amazon today.


Yesterday's Question of the Day

You have a table named Product with three columns: ProductID, Name and Color. Which of the following queries runs successfully based on this information? (select 3)

Answer:

  • SELECT ProductID,Name,Color FROM Product
  • SELECT ProductID, Name FROM Product
  • SELECT ProductID Name FROM Product

Explanation: if you want to list more than one column in select statement, T-SQL syntax requires coma between column names. According to this information the first and the third queries will run successfully. On the other hand if you have two column names without a comma, the query will run successfully, too. Because the first one is parsed as a column name and the second one is evaluated as an column alias.

» Discuss this question and answer on the forums

SQL Server 2012 Data Integration Recipes

SQL Server 2012 Data Integration Recipes provides focused and practical solutions to real world problems of data integration. Need to import data into SQL Server from an outside source? Need to export data and send it to another system? SQL Server 2012 Data Integration Recipes has your back. You'll find solutions for importing from Microsoft Office data stores such as Excel and Access, from text files such as CSV files, from XML, from other database brands such as Oracle and MySQL, and even from other SQL Server databases. You'll learn techniques for managing metadata, transforming data to meet the needs of the target system, handling exceptions and errors, and much more.

Get your copy from Amazon today.


Featured Script

Restore Script Generator

Create restore script from a SQL Backup file. A must have for your DBA Tool Belt. 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

Log File Continues to grow - Hi, With the help I received on this forum, I setup my log file to backup every hour via a maintenance...

sql 2000 jobs - i have given access for a login to view sql jobs in sql 2000 server and the user is able...

Check Database Integrity taking a long time - Hi Guys, I have just noticed that in the past few days a Maintenance Plan job that we have on...

Will shrinking the DB improve time taken by index rebuild process ? - Experts, Will shrinking the DB improve time taken by index rebuild process ? If yes, why ? Thanks, Smith.

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

SQL PROFILER EVENT CLASS - hI After running profiler for 5 min , i was putting the saved file into table .when i open it showing...

can't connect to Integration Services from my computer - hi guys i am trying to connect to ssis through my local computer, i already connected to the database engine...

SQL Server 2005 : Backups

How to restore master database in a SQL Server 2005 Cluster - I did the following: a. Change the SQL Server start up parameter to add -m (Single User Mode). b. STOP all SQL...

SQL Server 2005 : Business Intelligence

running package from TSQL - Hi I have a package which will be placed at a shared location and multiple user will execute it with...

how to use For Loop Container in SSIS to execute and procedure names stored in a column - Hi all. I need a good resource to figure out how to use For Loop Container in SSIS in order to...

SQL Server 2005 : SS2K5 Replication

Will Transactional Replication Supports Simple recovery Model ? - Hi I got a doubt in Transactional replication . Wil it supports for simple recovery model databases? My doubt is transactions are...

SQL Server 2005 : SQL Server Newbies

Grouping by day on a rollover count - Hi, I have a table containing a count that rollsover at [u]around[/u] 32000 to 0 possibly many times a day and...

Barcodes Code128 generator function - I'm looking for a [b]barcode generating Function[/b] (in SQL 2005), that uses the standard [u]Code128[/u]. The result of calling the...

SQL Server 2008 : SQL Server 2008 - General

dynamic sql question - I am new to dynamic SQL and I have the following from my sql profiler. my understanding was the syntax was exec...

how to identify which user is running query and how long is it running? - how to identify which user is running query and how long is it running?

Change Tracking and Log Shipping - I am trying to test, and it is not working so I think I have the answer. Here is what I...

When compressing a DB, will it cause growth first? - I know the subject isn't the clearest, but I think it'd be too long to put the entire question... I'm looking...

Database Mail - Could not connect (no such host)! - Hi all, Tried to find a fix on here for my issue but everything I've found re the above problem me...

Partitioning in SQL Server 2008 - Hi All, I have got few queries on partitioning in SQL Server. - Why should we go for partition in SQL...

Back up failed - Hi All I have one backup database schedule Every night. It went well everyday but today it failed. I check backup location,...

How to find dependencies in dynamic sql queries - I think it is far fetched because after searching the internet for days I found nothing. I want to find out...

How to find dependencies in dynamic sql queries - I think it is far fetched because after searching the internet for days I found nothing. I want to find out...

what is the use of power shell in sql server 2008 - what is the use of power shell in sql server 2008

PhysicalDisk perfmon counters versus LogicalDisk counters - Hi, We have a Physical disk which is partitioned into 4 drives,D,E,F,J. I am monitoring individual logical disk idle time and...

script in sql server 2008 for generating .ndf files - can any one give me script for generating .ndf files in sql server 2008

How to remove comma and convert in to INT - Hi, How to remove comma and how to convert this column into [numeric](17, 2) this formate. I have conevrt my this column...

generate scripts in sql server - I have some doubts related to SQL server 2008 R2 express 'generate scripts' option. 1)If i select script entire database and...

CHECKDB issue - Hi all, Someone restarted a server last week & SQL Server came back up as "in recovery" then completed after a while....

merge query - i want to merge this query [size="1"]DECLARE @MinDate datetime,@MinDate datetime SELECT @MinDate = MIN([Date]), @MaxDate = MAX([Date]) FROM attend_log SELECT p.Date, q.TimeIn, q.TimeOut, q.shift, p.eid FROM ( SELECT f.[Date],eid FROM dbo.CalendarTable(@MinDate,@MaxDate,0,0) f CROSS JOIN (SELECT DISTINCT...

Delete records without logging - Deal all, we have a database AA in SQL Server,while inserting the records to table DB log file size are growing...

snapshot generation - what is the difference between snapshot generation process in snapshot replication and transactional replication

Maintenance plans - Hi all, Five days back i created a maintenance plan to delete backup files older than 2 days and the plan...

which is better performance wise - Two Scenarios 1) A table with 200 fields. OR 2) 200 fields spanning across multiple tables? For e.g 10 tables with 20 fields each. Which...

How to avoid deadlocks - Hi, I am facing few problems with deadlocks. we have few tables and each table is having more than 6 million...

SHOW OFF DAY - HI i want to show off days in my report my sql data is like this date----------timein------timeout--shift----------eid 2-May-2013--9:20AM---6:17PM-----G-----------17090 3-May-2013--9:09AM---2:01PM-----G-----------17090 4-May-2013--9:03AM---10:41AM----G-----------17090...

SQL Server 2008R2 Cluster SAN - Hi, I would like to know if it's possible to install SQL Server binaries on SAN drives (as we only have...

Find out if replication failed. - Is there a way to find out (perhaps using DMVs) through TSQL if Replication failed between a given time frame?...

SQL 2012 and AG's - Recent post from Brent Ozar. Is what he is saying correct. In SQL 2012, if your primary drops offline or the...

Insert is taking long time. - hi team, Insert one records is taking 8 to 1o seconds, indexes,stats every thing is good. i've rebuild the indexes also, same...

building up dynamic column creation within procedure - Hi Professionals I have a procedure that passes in 1:CSV file, 2:Table_name 3:Column_count The column_count can vary and this is where I...

Need a help in backup Encyption Certificate - HI ..I am having trouble with keeping backup of encryption certificate. I created database encryption key with folowing query. [b]Use Test GO CREATE...

How to connect with SQL Server using http based API - Hi, I am new to this. I want to connect SQL SERVER 2008 with a CRM called Salesforce (without using any...

Shrinking the log file does not reduce size - I have a database which had mdf size of 350 MB and ldf size 4.9 GB When i try to shrink...

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

SQL Server 2008 : T-SQL (SS2K8)

data type change - what are the list of things to be considered when data type is changed in a table... how to do impact...

options for a column - We have a view that is using concatenate string for street.( prefix+ streetname+ suffix) Now since we are using entity...

Arithmetic overflow error - I have a select statment,but get an error: Arithmetic overflow error converting float to data type numeric. SELECT CAST(SUM(C.PeriodsAbsent) AS numeric(4,1))...

WHERE NOT EXISTS() causes query to hang - Hi Friends, I have an unusual problem that I've been unable to find any info to help, so I'm hoping someone...

performance tunning. - I have a query. This select query has few fat tables attached to each other. Is there a way i...

how to get name only from these column of enmae from emp table? - Hai friends, My emp Table is create table emp ( location varchar(20), ename varchar(30) ) insert into emp (location,ename) values('A','00001:ravi') insert into emp (location,ename) values('A','00002:rahie') insert into emp...

Need Query for a problem - Hello Everybody, Consider the following table, create table SampleTable (Category varchar(100), Organisams varchar(100) ) Insert into Sampletable values ('Animals','Lion') Insert into Sampletable values ('Birds','Dove') Insert into Sampletable...

Report keeps on failing - Hi, I am new in this forum and this is my first topic. I have created an SSRS report that is...

Identify postcode from multiple fields - Hello, I have a table with five fields used to hold address data. Due to poor practices the users have been...

SQL Server 2008 : SQL Server Newbies

Extracting Excel files from Multiple subfolders and load them into sql server table basing on city name - Hi, I have a folder which contain's Sub folders(City Names) and in those sub folders I have excel files. I...

How to get this trigger on insert/update working - Hi all! I need to do something upon update/insert of a new record, and as it has to be automatical, i...

files and filegroups - Hi I have a database with 3 filegroups primary,secondary and third. secondary marked as default. now i have created a table on...

Buffer cache hit ratio down.. - Hello Masters, I had SQL Server 2005 SP3 on my server and from yesterday I got an alert that "Buffer cache...

Search for relationship - I have a table having company names and another table having Revenueid and amount of Revenue but dont seem t...

Questions on resources and removing instances - Hi there, I've been working with SQL Databases for over 5 years now. Prior to this job, I has no working...

get lookup data - I have two tables, lets say Table 1 and Table 2 as below Table 1: Col1 Col2 Col3 1 _A 11 2 _B 12 3...

SQL Server 2008 : Security (SS2K8)

SCCM Security Config for SQL Servers - Hi All, I prefer my systems to work on the least privilege wherever possible but I have an account connecting...

Recovery Model - SIMPLE V/S FULL - Hi, If we having Full backup every night but it's set up FULL Recovery model, I have a question that if...

How to prevent ANY use of xp_CmdShell? - Please forget whether or not you're pro or con on the subject of the use of xp_CmdShell for just a...

User (db_datareader) can't see tables in MS; can SELECT data! - We have an Acitve Directory user that is assigned db_datareader for a given database. Given any table name in the...

SQL Server 2008 : SQL Server 2008 High Availability

SQL Clustering and VMware - My production servers are going back on lease soon. They consist of two physical SQL Server 2008 R2 servers, clustered,...

Mirroring Strange Behaviour Question - Hi all Last night one of our mirrored databases failed over and then failed back automatically. This seems strange behaviour to...

SQL Server 2008 : SQL Server 2008 Administration

Centralised monitoring of disk space - Hi Experts, I want to monitor disk space of all servers and i have created a centralised server from where i...

How to Export data from SQL Server to CSV using SQL server Management studio? - Hi All, Good morning, I have requirement export data from SQL server to CSV using SSMS, because we don't have access to...

Backup Failed because of error 0xC0010014 - Hi, Backup jobs failed frequently due to as below error.. after resarting server backup jobs running fine.. Pl. suggestion me if any...

User Connection Memory - Memory Leak?? - Hello, I have had an issue on a SQL and SSRS server that i have been unable to resolve without getting...

SQL Agent permissions model help - Hi there, I've been trying to get some security defined around SQL Agent to a point I'm happy with the...

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

The database principal owns objects in the database and cannot be dropped - Hi guys, Recently we got this error: The database principal owns objects in the database and cannot be dropped. The database version...

Programming : General

get set of random rows with distinct values - I have an interesting little T-SQL problem that has me beaten. I want to retrieve a small set of randomly selected...

Programming : XML

Reading XML from column - Hello all, This question has probably been asked before but I just can't figure out what to do. I have a table: ------------------------------------------------------------------------------------------------------------------------------------------------- [font="Arial"]/******...

Programming : Service Broker

SQL Server WMI provider is not available - hi, I'm trying to configure Service Broker between a SQLServer 2008 R2 and a SQL 2012 instance. Using ssbdiagnose I get the...

Programming : Powershell

Write PowerShell Output to SQL Server Table - Heh... First time I've had to admit being a "newbie" in a long time so be gentle with me. I...

SQLServerCentral.com : Anything that is NOT about SQL!

Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,...

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

Session Execution Time On Cached/Snapshotted Reports - I am currently using the following in the footer of all reports. ="Execution Time: " + IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds < 1, "0 seconds", ( IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours >...

Internet Explorer 10 and SSRS 2005 - I have Reporting Services 2005 running on Windows Server 2003 and IIS 6. Everything looks fine with Internet Explorer 9,...

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

Execution of Subscribed report through Stored procedure - Hi, I have a requirment where reports which are subscribed needs to executed through the Stored procedure I mean for eg My...

Reporting Services : Reporting Services 2005 Administration

New Group Permisssion - I have 50 users (in Active Directory) which are in a group and I would like to give them view...

Data Warehousing : Integration Services

Configuration - Hi , I have around 50 Entities.Where in for each entity I have around 6-20 Packges .In each package Iam calling...

What assemblies do i need to add to my SSIS Project? - I just inherited an SSIS package with a script component but it seems to be missing a bunch of assemblies....

SQL agent job fails when accessing the csv file - Hi All, I have a csv file in a shared folder in a server. My package is running fine in the...

Microsoft Access : Microsoft Access

Access Query ported to SQL - Here is the "inner" part of a query in Access. UPDATE [Vehicle Detail] SET [Vehicle Detail].ReturnDate = #4/29/2013#, [Vehicle Detail].ReturnSource = 'ReturnProcess' WHERE ((([Vehicle...