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

July 4, 2014

It's a summer Friday, at least in the US and the northern part of the planet. It's also a holiday in the US, and I hope my fellow citizens remember the significance of today and read one of our founding documents.

I'm away on sabbatical, and I'm assuming that things are going great. I'm actually writing this in advance and scheduling it, but I'll likely be working just as hard, trying to improve myself in a few ways. I don't have the exact plans, but perhaps I'm working on a project, volunteering, in school, something else, or all of the above.

In any case, this is a slow day in the US, and at SQLServerCentral as we repeat a number of previously published articles. We re-use this content because we've found ove the years that people are busy. They're busy with their careers, and busy in their lives, and many of them miss any number of newsletters and pieces we've released. Re-running them allows people to see something they might have missed, or perhaps review a popular piece they have forgotten about.

I'd encourage you today, if you are working, to take a few minutes and browse one of the articles, perhaps trying to follow along in the code or even solve the problem without looking at how the author does. Brush up on some skills and be better prepared for some future assignment.

Have a great day.

Steve Jones from SQLServerCentral.com

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

ADVERTISEMENT
HDFS Explorer

HDFS Explorer - manage Hadoop storage

HDFS Explorer is a new free tool that lets you manipulate data on Hadoop clusters. You can rename, copy, and move files with an Explorer-like interface, and quickly get at query results. Get it now.

DBA Team Article 3

New: 5 Worst Days in a DBA’s Life Part 3

There’s sinister work afoot at Monte Bank. Backups corrupted beyond repair – and without a trace of the cause. Can the DBA Team save the data? Find out in this new article.

sqlsourcecontrol

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…

Featured Contents

 

SSRS In a Flash - Level 1 in the Stairway to Reporting Services

Jessica M. Moss from SQLServerCentral.com

Learn the basics of Reporting Services, what it is, and what it can do from you. From MVP Jessica Moss, we have a new series that can help you get started with this part of SQL Server. More »


 

The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

Jeff Moden from SQLServerCentral.com

Many people have used a "Numbers" or "Tally" table without really knowing what it does. This is an introduction as to how a Tally table replaces a loop. More »


 

Granular or Cell Level Encryption in SQL Server

Additional Articles from Database Journal

Arshad Ali demonstrates granular level encryption in detail and explains how it differs from Transparent Data Encryption (TDE). More »

Question of the Day

Today's Question (by Steve Jones):

What was the code name for SQL Server 6.0?

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 7 points in this category: Trivia.

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

ADVERTISEMENT

SQL Server Execution Plans

SQL Server Execution Plans shows you what's going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by SQL Server, including: Which indexes are being used, and where no indexes are being used at all. How the data is being retrieved, and joined, from the tables defined in your query. How aggregations in GROUP BY queries are put together. Grab your copy today from Amazon!

Yesterday's Question of the Day

Yesterday's Question (by Abbas Kapasi):

How many rows will be returned by each SELECT statement?

CREATE TABLE A (col1 tinyint);
CREATE TABLE B (col1 tinyint);

INSERT INTO A VALUES(1) ,(1),(1)
INSERT INTO B VALUES(1) ,(1)

SELECT * FROM A CROSS JOIN B --1
SELECT * FROM A INNER JOIN B ON a.col1=b.col1 --2
SELECT * FROM A LEFT OUTER JOIN B ON a.col1=b.col1 --3
SELECT * FROM A RIGHT OUTER JOIN B ON a.col1=b.col1 --4
SELECT * FROM A FULL OUTER JOIN B ON a.col1=b.col1 --5

Answer: 6,6,6,6,6

Explanation:

The values in col1 in both the tables is matched against each other resulting in 6 rows for all the SELECT statements.


» Discuss this question and answer on the forums

Featured Script

Check Backups

Greg Ryan from SQLServerCentral.com

Daily maintenance script to check on backups

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

Backup Database is terminated Abnormally Failed on SQL Server 2014 RTM - My Database backup is not working in SQL 2014 RTM version. It works fine on SQL Server 2008 R2. All...

SQL 2012 Maintenance Clean Up Task - Hi, I am looking for a table where Maintenance Clean Up Task configuration is stored. For example, Delete file older than...

Give user/domain group view only rights on jobs. - Good afternoon. I have been struggling with this one for awhile now.I have a domain group which only must view...


SQL Server 2014 : Development - SQL Server 2014

Generate column numbers using dynamic SQL and pivot command - I have the following SQL which i want to convert to a stored procedure having dynamic SQL to generate column...

Cursors - Cursors are bad in SQL Server and should be avoided unless there's absolutely no other way to achieve a task. However,...


SQL Server 2012 : SQL 2012 - General

Cost threshold for parallelism - Subtree Cost - Hi all, wonder if somebody would be so kind and clear something up!? Firstly am I right in thinking that if...

WAITFOR wait type - Hi Experts, One of our main application is connecting from different node and all connection from all node is showing WAITFOR...

Enable\Disable AG Listener - Hi Experts, How can one enable\disable Availability Group listener? I know AG listener can be deleted and added but didnt find...

Buy SQL Server 2012 Developer License - Hi, anybody knows if I can buy a Developer License for SQL Server 2012, and where? On Microsoft Store, it isn't...

Identifying specific blocker SQL stmt when it is part of an explicit tran - App developers are complaining that a blocking report I sent them today does not have enough info to help them...

xml render from SSRS 2012 returning an empty document - Afternoon all, I have an SSRS report that is designed to query data from a database and produce an xml document...

FROM clause have the same exposed names; Reportbuilder - Hello, We're getting Msg 1013, Level 16, State 1, Line 1 The objects "MYTEST2.TEST" and "mytest.TEST" in the FROM clause have the same...

SQL 2012 AlwaysOn Read-Intent Secondary - Hi all, I need a quick help over here. Currently, we have 3 scripts that needs to be executed and setup...

Reset SA password - We have CRM on a SQL 2012 Server, however no one in the company knows the SA password and there...

tabular model cube - Is it OK to call tabular model database as tabular cube?

SSIS 2008 Script Tasks not functioning as expected after SQL Server 2012 install - Hi, I have SQL Server 2008 developer edition on my PC, and have installed SQL Server 2012 developer edition. I have...


SQL Server 2012 : SQL Server 2012 - T-SQL

Exit Code 1073741571 - I have an SSIS process that is misbehaving. Here is the edited error code: [quote]Selected row details: Date 7/3/2014 1:39:49 PM Log Job History (XXXXXXX_Daily_Files_Import) Step...

Need help with backup statement for a SQL Server 6.5 instance - Hi all: Yes-your eyes are not failing you-my client has a SQL 6.5 instance for databases that have not been backed...

SQLServer2012 Locking - I have a insert stored procedure that inserts data with the values passed as parameters to this proc and then...

Select query error - Hi everyone. I have a query that does return results , but returns an error. This is the SQL statement: DECLARE @DBuser_sql VARCHAR(4000) DECLARE...

Constraint question - I've added an IsActive bit field to a slowly changing dimension, and I want to enforce the rule that only...

Recursive query to explode a BOM - Hi, I'm trying to use a recursive query to find path between assembly and parts. The BOM is similar to(I've limited the...

Return Count - Hi all, I have a scenario in which I need to capture count of rows returned by execution of stored procedure. I...

Stored Procedure Performance - I was wondering if anyone knows of a way to speed this stored procedure up some, there are no pk...

Optimize SQL - Hi, We have an application management planning in which we must manage: First   - Daily attendance of resources   - For each day ranges...


SQL Server 2008 : SQL Server 2008 - General

Export Wizard Error - I'm hoping someone can help me. I have a big query, like 2 million records. It has some exotic characters,...

Locks on a table - Hi,I need help regarding my issue. We are using a application which is used by multiple users simultaneously.Using this application,different users...

OPENXML vs XQuery - This question pertains to SQL versions 2008 and higher - which provides better performance for parsing/reading a XML data type, OPENXML...

Backup Database is terminated Abnormally Failed on SQL Server 2014 RTM - My Database backup is not working in SQL 2014 RTM version. It works fine on SQL Server 2008 R2. All...

Anyone got a script to get compression estimate of all the tables on all databases - Ok,anyone that can help me I've already got a script to get the estimate off all the tables and indexes...

Is there a clever way to do an UPDATE/MERGE and insert rows when there are duplicate matches? - Hi - I'm trying to do something like below and wondering if there's a way I can do it with the...

Select from table belong to another database - Hi I'm working with SQL2012 Express, and I have created 2 database, each database has different users login. Like this: User1 can...

Multi-value parameter in SSRS shows blank - Hi, I am trying to run a report based on a stored procedure. The report uses 3 parameters and one of...

Select date where <year part = '2014'> - Getting a bit frustrated over this simple select statement. I'd like to select all entries (including full date) where the year...

updating views - Hi all, I am a newbie to sql . i want to knw more on views. I know the main purpose of...

timeout period elapsed prior to completion. - Application Error during upload data into database,. The timeout period elapsed prior to completion of the operation or the server is...

Backups and 3rd party support...now I have a headache. - Hi, I am not sure this is the correct forum for this, but it is 2008 backup related. Sort of. I got...

Page life expectancy - I'm getting page life expectancy low alert. Threshold is set to 300. Any idea what to look for and how...

varbinary start with 0x504B0304140002000800 - hello all. I have a table with varbinary column.every column starts with 0x504B0304140002000800 and I tried to retrieve varbinary columns to...

Import Wizard Error - Hi, I'm hoping someone can help with a mystifying error. I have run a query in sql server, saved the results...

HIPAA Compliance Monitoring - This is a question for those of you who are in the health industry. What are you using for HIPAA...

Good practice to separate tables with static data? - Hi! I have a database with several tables, some of them contain static data, meaning rows that are added to the...

Running total error - Hi, I am trying to calculate running total in sql as its not giving me the desired result on SSRS (on...

Complete Table backup using sql job - Hi team, created a job to delete all the records from table 'Unify 'daily. i want the same job to take...

Copying from SQLSMS grid, pasting in Excel, the word "NULL" is pasted! - When I copy/paste grid results from SQL Server Management Studio --> an Excel file, the WORD "NULL", the actual letters...

Merge with duplicate rows - Hello all, I am running merge stored procedures against various staging and final tables in my database. I have one...


SQL Server 2008 : T-SQL (SS2K8)

Report Non-existant child rows - I am trying to get a "Zero Values" report. The following code works except when there is no child row. [code="sql"] declare...

Counting Decimal Places in a float field - I have a table with three columns: UniqID, Latitude, and Longitude. I need to write a query to identify when the...

If not exists returning false when should be true - Edit: Actually title should be returns true when should false. Am I doing this all wrong? I want to check a...

Replace a Cursor - Hi All Could anyone help me on getting the below Cursor query convert to a Recursive CTE or with a while...

Need to add Balance of previous Row to Current Row - Hello I am novice to intermediate writer of T-SQL. I need help with a query. Here is my current Query: [code="sql"]SELECT [FISCALYEAR], [ACCTPERIOD], SUM([ACTIVITYDEBIT])...

columns compare - --drop table #temp create table #temp (id int, idvalue int) insert into #temp(id,idvalue) select 1095,75 union all select 1096,61 union all select 1097,65 union all select 1098,69 union all select...

Need help with employee hierrarchy - Hi, I need help with the following query: ---------------------------------------------------------------------------------------------- declare @LoginId nvarchar(20) set @LoginId='A-1519' declare @LoginName nvarchar(100) set @LoginName=(select [FirstName] + ISNULL(''+[MiddleName],'') + ISNULL(''+[LastName],'') from [dbo].[Employee] where EmpId=@LoginId) ;WITH...

APR Calculation NEEDED. - I was hoping someone could help me with doing an APR calculation in SQL. I have Loan Amount, # of months(term),...

Finding maximum value out of 5 different columns - Hey Guys, How can we find maximum value on column level? Suppose we have table A with four columns col1,col2,col3,col4,...


SQL Server 2008 : SQL Server Newbies

Order of IN operator - select empid from consumer where empid in (2323,1236,5623) I want result would be of same order as i put in IN...

Create Function - Query works fine stand-alone but has error message for the line - I can't seem to find any syntax problems - can someone please advise? The Select Count ... forward works great in a query...


SQL Server 2008 : Security (SS2K8)

Cannot start a transaction for OLE DB provider "MSOLAP" for linked server - Hi All Server1 (SQL Server 2008R2): [i]Cannot start a transaction for OLE DB provider "MSOLAP" for linked server "LINKED_OLAP"[/i] is the message I'm...

Windows administrator privileges on your own workstation-new management policy - Because I'm in a small company I am doing .NET and SQL development as well as database administration. It took...


SQL Server 2008 : SQL Server 2008 High Availability

Question about initial backup for Log Shipping - Hi All, I'm in the process of setting up log shipping for several SQL 2008 DB's. Everything works fine using...


SQL Server 2008 : SQL Server 2008 Administration

Thinking outside the box on table partitioning - I have some tables that I think would be good candidates for table partitioning to simplify maintenance. Some of the...

How to get started with freelancing database projects ? - Hi Experts, Need your advice on this. I wanted to do some Freelancing for Database projects for database administration, converting DTS to...

SQL SERVER PATCHING USING SCCM - Hi, Can we patch the SQL Server using SCCM? Please provide me the steps to do it or provide me any...

Database Time out - Hi Experts, One of our application is getting database timeout when they try to generate report. I have checked server when...

Copy partition data on OLTP 24x7 instance - Hi, I am looking to change a number of table partitions from a specific range ie currently 30 day to 15...

Interview question :critical issue faced by dba - Hi Experts, I need some guidance on this. Recently I have attended a job interview for sql server database administration and was...

CPU Issue and very strange - I am working on SQL server 2008 r2 (windows 2008), DB Server is under CPU pressure ( always 80 to 100%...

Table Designing - Hi Experts, Can you please guide me on steps to take care while designing tables? What all things me need to make...

How to take the backup of 700gb with less time - Hi Experts I have a Question like "If the Database size is more that 700GB" means how can i take...

Task durations shown do not add up to total time spent on Maintenance Plan (massive difference!) - I have a maintenance plan in Sql Server 2008 R2 where the durations shown do not add up to the...


SQL Server 2008 : SQL Server 2008 Performance Tuning

Partitioned Table Practices - Hi, When DBA's partition tables, what are some of the things they look at to determine the number of partitions, files,...

SQL Server 2008 performance issue - We have a dedicated Windows Server 2008 R2 (64bit) running SQL Server 2008 SP3. The server has sufficient resources or...


Cloud Computing : SQL Azure - Development

Add primary key to an existing table - Hi, I created a table in my Azure SQL Database, Part of the code is here.. [code="sql"] CREATE TABLE [dbo].[DEVAppointments]( [ClusterInd] [int] IDENTITY(1,1)...


SQL Server 2005 : Backups

Can't shrink transaction log on sql server 2005 Standard edition, 64 bit - Hi. We have a DB on a sql server 2005 server that is 600MB data and 242 GB log, but...


SQL Server 2005 : Business Intelligence

Storing the OLEDB destination as variable - I am working on a project where I will be loading 50-100 tables from an Oracle source to SQL. I...


SQL Server 2005 : SS2K5 Replication

Replication In Mirrord Database - I've three server 1. Server-1 2. Server-2 3. Server-3 All three server have sql server 2005 developer Edition (SP4) installed. Now the...


SQL Server 2005 : SQL Server 2005 Integration Services

Dyncamically change Column lenght in Foreach Loop container of SSIS - Hi All, I'm trying to load data from multiple files into SQL server using Foreach Loop Container in SSIS 2008. All...


Reporting Services : Reporting Services

Render to PDF and resetting page numbers does not work when run from Report Server - Hi Everyone, I'm having a rather strange problem. I've got a report that produces statements for a group of people. The...


Reporting Services : Reporting Services 2008/R2 Administration

any issues using ssrs2008 r2 to connect to a sql 2012 database - Probably not but wanted to know if there any issues/gotchas regarding having an ssrs 2008 server utilize a sql 2012...

SSRS 2008 R2 - Fixed header when scrolling - Hello SSRS 2008 R2 gurus, Hope you can help me out on this issue. We have two environment, one is for staging...

Report builder 3 can connect to a model created from a data source but not a data source - Group, I apologize if this is already in here. I'm to my wits end trying to resolve this. We use IBM...


Reporting Services : Reporting Services 2008 Development

SSRS dashboard solution - Hi all, I have a request to create a dashboard solution that will be real time to show performance data...


Reporting Services : SSRS 2012

Grouping the data as in PB report - We are migrating the PB to SSRS report, in one report , they want to show the report as below format....


Programming : Powershell

Powershell exit code - I have a script that I'm trying to run in TeamCity, essentially I want to force an exit code if...

Passing Parameter to SQl Agent Job that runs powershell - Hi I have created a SQL Agent Job with a powershell step. The powershell script looks like the following: [code="other"] Param ( [string]$A, [string]$B, [int]$C ) #Do something...


Data Warehousing : Integration Services

Convert .xlsx to .txt - I need to generate a .txt file via a SSRS report. My thought was to export the report as .xlsx...

SSIS and Multiple values in columns problem - I have a staging table which load data into another table but I‘m having problems adding the business logic in...

Problem with Multiple executions of SSIS packages - Hi Folks, I have a master package calling a child package 5 times in parallel. At times, we are observing that...

Schema comparision - Hi I m integrated the mvc application with SSIS package. I m sending the excel file from mvc application to SSIS...


Data Warehousing : Strategies and Ideas

Figuring out Financial Package (Agresso) Schema - We are beginning exploration of all our systems and data, with the aim of eventually building a Data Warehouse. All financial...

Books on implementing data lineage - Hi, We are currently working on implementing the data lineage within our data warehouse and I don't seem to find a...


Data Warehousing : Analysis Services

SSAS processing - Hi, I am currently processing an entire OLAP database that contains 3 cubes. I am currently using "ProcesFull" process option. The...

MDX CALCULED MEASURES FOR ALL LEVEL OF A HIERARCHY - Hi, Could someone help me to write a MDX script to calculate a measure for all level of a hierachy? I explain...


Data Warehousing : Data Mining

data mining using SSAS design question - Hello - I have a design question and want to understand whether my problem could be solved using SSAS data mining...


Database Design : Design Ideas and Questions

Procedures and functions with cross-database dependencies - Long story short - our company's primary operational database is poorly designed and has been poorly maintained in the past. We...


SQLServerCentral.com : Anything that is NOT about SQL!

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

ISNUMERIC() bug? - Hello,  I just encountered a strange result using the ISNUMERIC function...specifically something like SELECT ISNUMERIC('9990D7') or SELECT ISNUMERIC('9D7'). These statements return a...


Career : Employers and Employees

How long do you give a new job - Im in my new role 1 week now. I know this is not nearly enough time to judge a position,...

Best way to prepare for interview? - Need help, it always take me ~ 3 months for preparation. I work in some area and forget the others. Last time it...

This email has been sent to {user_email}. 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 ©2014 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com