In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Data Generator How do you generate test data for your database?
SQL Data Generator quickly populates databases with intelligent and meaningful test data. "SQL Data Generator is simple and effective." Michael Gaertner, Quintech. Download a free trial now.
 
SQL Monitor Custom metrics from SQL Server MVPs
SQL Monitor is the only tool with a free library of custom metrics from SQL Server MVPs. Find new metrics for your servers.
 
SQL Toolbelt Want to work faster with SQL Server?
If you want to work faster try out the SQL Toolbelt. "The SQL Toolbelt provides tools that database developers as well as DBAs should not live without." William Van Orden. Download the SQL Toolbelt here.

In This Issue

dbWarden - A Free SQL Server Monitoring Package

dbWarden is a comprehensive monitoring and alerting solution for SQL Server 2005 or newer. It features an emailed Health Report and includes email and text notifications with customizable metrics for alerts such as Blocking, Long Running Queries and SQL Jobs, CPU %, Log file and TempDB growth. More »


Comparing Multiple Rows Insert vs Single Row Insert with Three Data Load Methods

The performance of 'extract, transform, load' (ETL) processes for large quantities of data can always be improved by objective testing and experiment with alternative techniques. The cause of poor performance can sometimes be surprising. More »


Do you regularly run queries and scripts in SSMS?

SQL Server Management Studio is where most SQL scripts and queries are run, and although it does a decent job, it doesn't provide much help storing, sharing and managing the scripts particularly in the context of a team. Red Gate is considering building functionality into SSMS to help its users consume and share queries and scripts. Please help us by completing this short survey to help us define the requirements.  More »


From the SQLServerCentral Blogs - UNPIVOT a table using CROSS APPLY

I generally spend part of each day looking through http://www.stackoverflow.com, http://dba.stackexchange.com or http://www.sqlservercentral.com. I read through questions that have already... More »


Editorial - The DBA Office

I've written about offices I like in the past. I've worked in a variety of offices, and having telecommuted for the last decade, I find that I mostly prefer a variety of environments. I have a standing desk, where I do most of my work, but I often find myself moving to another room with my laptop to work in a quieter, or louder, environment. I work at a coffee shop, in hotels, even in the car at times. It seems like I might be working too much, but I think I have a good balance between life and work. I just find myself working for short stretches in very disparate environments as I wander through life.

For me, the ideal environment has varied according to the job that I did. Software development seems to require more quiet and separation than operational work. The more I coordinate with others, the more I like an open floor plan. The more I have to concentrate on my own work, the more I prefer a peaceful, solitary environment. As a DBA, I've had to work in both environments and I've had my own office, a shared office, a hot desk, and an open plan. I often haven't had a choice of the arrangement in most jobs, so it's a good thing I'm adaptable.

I ran across a piece that looks at the history of the open and closed office, as well as different ways or organizing desks. There are great pros and cons in the article for each setup, and the compromises people make. This week, I'm wondering what many of you would like your arrangement to be.

What would be your ideal office work environment?

Would you prefer an open plan? A closed office? A hot desk arrangement that incorporates both of these? Do you want public spaces at times and private locations where you can get solo work done if you need it? If you have preferences, or comments about your current setup, let us know today. Maybe we can design a few "ideal" office spaces together.

» 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. You can also follow Steve Jones on Twitter:

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. They have a great version of Message in a Bottle if you want to check it out.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

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


Question of the Day

Today's Question:

How many different kinds of payload types for endpoints can you create in SQL Server 2012?

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

This question is worth 1 point in this category: Administration. 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 Hardware will provide the fundamental knowledge and resources you need to make intelligent decisions about choice, and optimal installation and configuration, of SQL Server hardware, operating system and the SQL Server RDBMS.

Pick up your copy of this great book from MVP Glenn Berry at Amazon today.


Yesterday's Question of the Day

What will be the outpt if i run following queries?

Declare @smldatetime SmallDateTime
      , @dt DateTime
      , @dt2 DateTime2
      , @d Date

Select
  @d=NULL
, @smldatetime='' 
, @dt='' 
, @dt2 =''

Select
  CAST(@d as varchar) 
, CAST(@smldatetime as VARCHAR)
, CAST(@dt as VARCHAR)
, CONVERT(VARCHAR,@dt2 )

Answer: NULL, Jan 1 1900 12:00AM ,Jan 1 1900 12:00AM,1900-01-01 00:00:00.0000000

Explanation: The default values for the various types are returned, except for NULL, which is a valid value for the dates.

Ref: http://msdn.microsoft.com/en-us/library/ff848733.aspx

» Discuss this question and answer on the forums

SQL Queries 2012 Joes 2 Pros® Volume 4: Query Programming Objects for SQL Server 2012

The SQL developer needs to be able to create processes for the working database by using one of the many programming objects (like functions, stored procedures, constrains, or triggers). By creating objects that talk with SQL you simply the way other programs (like applications or web pages) can interconnect. These eternal programs only need to call on the names of your programming objects by name rather than needing to submit large pieces of advance code. With SQL Queries 2012 Joes 2 Pros® Volume 4, you learn how programming objects work in SQL Server. For those of you who have read the 2008 series for the 70-433 Exam you will find a lot of the same material from the SQL 2008 book in this SQL 2012 book. This is because much of the 70-461 test covers the same material as the 70-433. I have added material that is new to the test and removed material that is no longer relevant. If you have already read this series or have already passed the 70-433 exam you may choose to read my book which covers only the changes from 70-433 to 70-461 entitled "Joes 2 Pros SQL 2012 Queries 70-461 Exam for SQL 2008 Pros".

Get your copy from Amazon today.


Featured Script

Find the position of all occurrences of an expression within a string

This iTVF returns both the relative and the actual positions of ALL occurrences of a string within a string. 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

Cannot generate SSPI context. (.Net SqlClient Data Provider) - Hi.. Suggest me to resolve this issue please… For some reason SQL service account password changed and from that point I am...

Very strange behaviour - Multiple Exec plans - Attached is the complete code - I have been battling for weeks on this issue, i was able to resolve the issue but i don't the...

Index Rebuild Taking too Long? - I have an instance running the following version: Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c)...

ANSI to UTF-8 conversion - I am running a .bat file that runs a SQL script that extracts data from a SQL Server database and...

about storing sql script in a table? - Hi Can i store the database creation script in a table call the script in stored procedure to create new...

Database in Recovery Pending State - One of my databases is in [b]Recovery pending[/b] state. I tried to run an Alter command on the database to...

SAP configuration for SQL SERVER 2005 - Hi All, Can someone tell me how to configure SAP with SQL SERVER 2005 to integrate on.Is there any doc on...

SQL Server 2005 : Business Intelligence

Loading Fact Tables - Step by Step Instructions Challenge - Dear All, This is my first Data warehouse project and I am having serious problems loading my fact table. I...

Sample Insurance Data warehouse - Hi guys, I am looking for a sample data warehouse for insurance company in order to have a solid starting point. Can...

SQL Server 2005 : SQL Server 2005 General Discussion

how to List out all tables indexes having index fragmentation more than 50% of databases. - HI to every one can any one guide me to find out this if any incident occurred in sql server...

Japanese characters as question marks - Hi, I'm having a bit of a strange issue with Japanese characters. 1) They're in the Excel file I'm importing. 2)...

SQL Server 2005 : SS2K5 Replication

Queue reader aborting - Hi all, This is my detailed error. [b]The Queue Reader Agent has encountered the error "Row handle is invalid". when connecting to...

SQL Server 2005 : SQL Server Express

Unspecified error MS visual database tools - Hi, i am using sql server management studio 2005 express, and every time i try to open a table in...

SQL Server 2005 : T-SQL (SS2K5)

I am getting learn to post,am i correct now??????? What will be your reply for this??????? - --===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable --===== Create the test table...

SQL Server 2005 : SQL Server Newbies

Undeleatable record (on linked table) - Hi, I've got a system made up of a SQL 2005 backend and an Access front-end. The front-end only holds the...

SQL Server 7,2000 : General

QuickBooks to SQL Server - Does anyone have any ideas of ways to communicate between SQL Server and Quickbooks?  I have found a couple of...

address management software - Does anyone know of any address management software that is compliant with sql server 2000, i.e. for record duplication and...

SQL Server 7,2000 : SQL Server Agent

cmdexec step running and not doing anything - Hi, I have a job in which one  step is of type cmdexec,  Agent history shows the step ran successfully with duration...

SQL Server 2008 : SQL Server 2008 - General

Row count per filegroup - Hi, I have a partitioned table and its spread on filegroups monthly. My question is how do i find out how many...

Unused Indexes - Hello, I have been running the DBTA on a database to get some recommendations, I have the results, nothing has been...

Alternative for Excell (Access). - Dear reader, [b]It's not difficult to convince any manager; that storage in SQL-server would be a good idea. But because of...

Weekly report through whole year - Hi there I have 2 tables and sample data: -------------------------------------------------------------------------------------------------------------------------------------------------------- CREATE TABLE [dbo].[tblProduct]( [ProductID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NOT NULL, CONSTRAINT [PK_tblProduct] PRIMARY...

SSIS - Format issue in sending message through MSMQ task - Hi, I am trying to send some messages to a public MSMQ through the SSIS package using MSMQ task. I want to...

Query CPU du session - Hi I need your help, someone know what queries use to obtain : the charge of CPU due to sessions and...

Cursor help - Hi, I have made a stored procedure with a cursor: [code="sql"]USE [DB_admin1] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> --...

SQL query help - hi guy i need help with a query!!! basically i have a table with l01 and 109 table looks a little like...

Divide by zero error message - Dear All, I'm getting the following error message Msg 8134, Level 16, State 1, Procedure "stored procedure name", Line 149 Divide by...

NOLOCK and PAGELATCH_SH - I'm troubleshooting a latch contention issue at the moment. One of the symptoms operations noted during the incident itself was...

Partitioning Tables - Querying Question. - Hello, I hope someone can answer a question I was asked but was not sure about. If you partition a table...

SQL Server Error Log stops logging activities of the server - Hi Guys. Is it normal that SQL Server Error Log stops logging server activities like backup operations, errors? Though server activities...

How to Put results of FISQL in file - Hi , I need help : I have two machines: one with my sql server and the other with a CENTOS. In CentOS...

Multiple tables in a Report - Hey guys, I have a challenge in displaying the database server health statistics with a minimum refresh rate (about 5-30 seconds)....

Sql Jobs steps - Hello I have a several SQL JObs I need to run in sequence. For some reason when I run a...

DBCC on a detached MDF file - Hello, I am by no means above a SQL novice, but I am trying to figure out if there is a...

SSRS Paramaters - Hi everyone I have a report in SSRS which contains the paramater @Region. The paramater allows for multiple selections and...

Convert IP address to Binary (32) - Hello, I am trying to convert an IP address from regular format (24.55.187.203) to binary 32-bit format (00011000001101111011101111001011). Can someone please help...

Restoring database - Hello, I am trying to automate my code by restoring once a week database from one server to another, this is...

Get data from 3 or more tables - I have tables with following definition' [code="sql"] CREATE TABLE [dbo].[items]( [srno] [int] IDENTITY(1,1) NOT NULL, [itemname] [nvarchar](255) NULL, [unitsymbol] [nvarchar](50) NULL, [itemtype] [nvarchar](50) NULL, [purchaseledger] [int] NULL, [salesledger]...

Ignoring timeout in stored procedure - I know that this might cause a lot of suggestions about query optimization and such, but I have a somewhat...

uniqueidentifier scope? - Hello - When a column is defined as a uniqueidentifier does that mean that the value is unique compared to all...

Need help on Powershell for SQL 2008 R2 - Folks , I created powershell script to be used with SQL 2008 which uses Invoke-sqlcmd . The script runs fine on SQL...

SSIS list file names in directory - Hello All, I am trying to create an SSIS package that will look at a directory, get a list of all...

Parallel Data Warehouse for OLTP - Hi, Is Parallel Data Warehouse feature of SQL Server 2008 R2 targeted only for OLAP/BI impementations? Can we use it for hosting...

Adding to Sales Quantities - Hi I have a table that forecasts our Sales for the upcoming year. It contains the following columns: Year (DATE),...

PIVOTing multiple columns? - I'm working on a PIVOT, and I can't see to get it to work. Here is the PIVOT query: [code="sql"]SELECT * FROM ( SELECT...

Initial size for TempDb data and Log file? - Hi, We have installed SQL Server 2008 R2 SP1 instance and it's having Share Point 2010 databases. We have 2 dedicated drives...

Restore/Backup minimal permissions - We use Netbackup for our SQL servers to backup and restore databases. I would like the service account used by...

SQL Server 2008 : T-SQL (SS2K8)

Not able improve the performace of the query used in SSRS report - I have used the below query in SSRS report but it is not at all rndring data.Can you plese help/guide...

How to script table data with either TSQL or Powershell? - Hi, I can use GUI of the MS to script out data from a table. Is this possible using either Powershell...

Replace Data in a String Based on Data in Table In line Query - Hi All, I trying to update a String based on the Inline View. String: -1 * [SAL] - [BON] - [COMM] In Line Query: Select * from TABLE Values...

t-sql trimming before and after stored proc name - Hi, I have requirement to get only stored proc name and trim everything before and after stored procedure name. Here...

Migration from MSDE (SQL2K) to SQLEXPRESS (SQL2K8) - Query Performance Degradation - I have migrated some databases from MSDE (SQL2K) to SQLEXPRESS (SQL2K8) Just using the backup and restore functions provided and made...

DATABASEPROPERTYEX and the IsSubscribed property - Hi All, When trying to examine if a database is a subscriber, I am trying to use DATABASEPROPERTYEX like: select DATABASEPROPERTYEX ( 'SubscriberDB'...

CTE - Temp table - CROSS APPLY - I had some free time at work and was trying out some silly queries when I came across this. CTE referenced...

Broadcast calender generator - Hi guys, Did anyone ever have to create a function/stored procedure that would take a date as an input and generate...

sql trigger problem - I am trying to write my first trigger on a sql server 2008 r2 database. I keep getting a procedure error...

How To Validate in Procedure - I Created a one Procedure ..its given Below... [code="sql"]create procedure [dbo].[USP_Reliance_Master_Upload] as begin declare @dt datetime , @Mnth varchar(10) BEGIN TRY SELECT @dt...

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 Server 2008 : SQL Server Newbies

How calc change in value over several records for different categories - Test data [code="sql"] IF OBJECT_ID('tempdb..#priceData') IS NOT NULL DROP TABLE #priceData; create table #priceData(symbol varchar(10), quote_date [datetime],close_price [decimal](6,2)) INSERT INTO #priceData (symbol, quote_date, close_price)...

Inserting into an auto increment PK field - i have a table as follows ColA PK autoincrement Colb nchar ColC ncahr ColD nchar and i have to insert values into this...

timeline in cross-tab?? - Hello experts, My problem is as follows. On one hand I have got the beginning and end date of a tournement,...

Temp Table Column name or number of supplied values does not match table definition - Hi all, Starting to play around with PBM and found an example of using DBCC LOGINFO. When i copy the...

Slows system performance - Dear All I am running a process (updation/deletion/insert) of 2 million records. After some time this process makes my system very...

SQL Server 2008 : SQL Server 2008 High Availability

Using Differentials to set up mirroring - Ive set up mirroring many times with a full backup and a log backup or two. Someone mentioned recently that you...

SQL Server 2008 R2 cluster with default instance - Hi, I have a server with two instances: one default and one named using SQL Server 2008 R2 Enterprise on Windows...

Log shipping Monitor server - Hi, What is the threshold limit to configure log shipping in monitor server? We have configured successfully around 1107 databases in one...

SQL Server 2008 : SQL Server 2008 Administration

unique constraint on a large column - I have a table with a column of NVARCHAR(2000) in size. There is a requirement to ensure the data in the...

Create DML trigger for single table in SQL 2008 and send email alert to group - Hi, i have a requirement to Create DML trigger for single table ( check for any updates ) in SQL 2008 and send...

Transactional replication not replicating one table properly - Hi I've 2008 R2 transactional replicatin establishe. One table is cauing an issue which is out of sync by 30K rows...

partitioning issue - Hi Here are some details of structure before I state the problem: Table Employee (dummy name) is of size 750GB on SQL...

SQL Server 2008 R2 SP2 and KB2551254 - Hello, I'm in process of installing several servers with SQL Server 2008 R2 SP2. So far I've completed 5 of them...

SQL 2K8 Edition Upgrade? - Hi, I want to upgrade SQL 2008 enterprise edition from standard edition. what are the points should take care before and after...

Obscure Replication Error - I've had multi-terabyte transactional replication going for a couple years now, for the most part things flow quite smoothly with...

Moving the SQL Server error logs, SQL Agent logs, and Full-Text logs - Ok, during the installation of SQL Server 2008 R2 (Enterprise x64) I changed the "Data root directory" thinking it would...

Log shipping and CDC - I come seeking the wisdom of the collective. I have searched to no avail to try and understand the implication...

Programming : General

changing directory in .bat files - Hi All, I am trying to use to compress some backup files with the 7zip program. I am able to compress the...

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

Reporting Services : Reporting Services

Jasper Smith's SSRS Scripter - Anyone happen to have this tool handy? I used in the past to successfully migrate a 2005 SSRS instance to...

Database Design : Disaster Recovery

Enable back DB Mirroring - Hi Everyone, I Have disabled database mirroring on principal server using below command for restoring database over a mirrored database because...

Database Design : Design Ideas and Questions

films database (yeah, yeah, hear me out!) - I've done a fair bit of Googling and asking dba friends, but I wanted to see what some of you...

Database Design : Relational Theory

Question Regarding Implementing a Work Schedule Table - Hello, I have a question pertaining to the best way to design/add an addition to a database. The specs that I'm...

Data Warehousing : Integration Services

SSIS performance Insert Update and Delete - This is a bit of a hybrid question between T-SQl and SSIS I am looking at using CDC to load a...

Connection string problem. - Hi All, Can we connect with the database which resides in cloud? Appreciate if anybody help on this. Thanks Abhas.

SQL server agent job fails - Hi, I have a SSIS package that picks up csv files from a shared folder on daily basis and load the...

SSIS FIlter conditions - Hi, I have a csv flat file with data from 2009 until date. I wanna restrict 2009 - 2011 data from flat...

Row count difference between flat file source and sql table - I have an SSIS package that imports flatFile data to a table. The package has error output for flat file...

SSIS doesn't import TXT file - I'm trying to do import of that TXT file [url=http://sdrv.ms/SVhAnp][/url] into that table [url=http://sdrv.ms/SViksM][/url] via this DTSX [url=http://sdrv.ms/SViyAa][/url]. Surprisingly, it gives me no error at all...

Data Warehousing : Strategies and Ideas

Data Warehouse Development Process - Hi, I am trying to create a simple yet robust development lifecycle for my organization. I do not want to...

Fact/Dimension design suggestions - I'm from Higher Education and I'm working on a warehouse so our recruitment folks can compare application counts of certain...

Data Warehousing : Analysis Services

How to Monitor SSAS - We migrated from 2005 to 2008 and have several cubes. Despite having a better server, our performace has gone way...

Computing on a cube? - Hallo, I'm totally new on computing on SSAS, and quite new to SSAS in general. I'd like to get some index...