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

Dream Jobs

Today we have a guest editorial from Andy Warren as Steve is at SQL in the City - Portland.

Have you ever thought, really thought, about what your dream job would look like? Dream jobs are one of those things where it seems like we should all have one in mind. But do we? And if not, why not?

I remember back in 2001 or so thinking that being a consultant was my dream job. It certainly looked attractive to arrive on the scene as ‘the expert’, solve an interesting problem or two, and then depart with a nice check.  I thought the best path to get there was to study and learn and I did that, eventually becoming a trainer and consultant, only to find that it was only partially satisfying – it didn’t scratch the entire itch and it had downsides I had not understood fully at the beginning, things like a lot of travel. I look back and realize I didn’t try hard enough to dig into and crystalize that vision to make sure my dream job really was my dream job.

Fast forward some to 2009 or so and I’m trying to find that vision for the next dream job. I made some changes, but it felt like treading water. The best I could define what was I looking for was “an industrial strength challenge”. That’s not much to work with. It seems like a worse effort than my first try! But that was what I was looking for and that helped me be ready when one came along. Here is the really interesting part – I couldn’t have on my best day described the job or the challenges that I got. That dream job lasted for two years and then the work was done. What to dream of next?

The next dream was to land a larger project. Not a complicated dream, because it was a project I could see in progress already and it was just a matter of getting a seat on the bus. Is it even fair to call it a dream? I got the seat, but the bus ran out of gas, and then it was time to dream once more.

Dreaming is hard. It carries the risk of failure. It’s not easy to dream beyond what we know right now or what we see someone else doing – we can’t see over the horizon and the horizon is often all too close. When people ask for help I try to help them refine that vision, get it so clear they can see it, but I know it’s not always that easy if the dream is big.

Have you ever landed your dream job and was it everything you hoped? Or do you have a vision of your dream job? I hope you’ll join me in the attached discussion forum for an interesting conversation!

Andy Warren from SQLServerCentral.com

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

ADVERTISEMENT
SQL Doc

Hate explaining your database in meetings?

SQL Doc quickly documents your entire database schema so that you can easily present it to others. "This tool is embarrassingly easy to use." David Hayden, DavidHayden.com. Download a free trial now.

SQL DBA Bundle

The seven tools in the SQL DBA Bundle support your core SQL Server database administration tasks.

Make backups a breeze! Enjoy trouble-free troubleshooting! Make the most of monitoring! Download a free trial now.

SQL Developer Bundle

12 must-have SQL Server tools

The award-winning SQL Developer Bundle contains 12 tools for faster, simpler SQL Server development. Download a free trial.

Featured Contents

 

Using Indexes to Reduce Blocking in Concurrent Transactions

Nilav Baran Ghosh from SQLServerCentral.com

New author Nilav Ghosh brings us a performance tuning article to help your queries run better. This article examines how indexes can help reduce blocking. More »


 

Explanation of SQL Server IO and Latches

Additional Articles from MSSQLTips.com

This article is a whistle-stop tour of my exploration of latches; their different types, their purposes, why they are required and where they fit into the SQL database engine, in the hope it will be interesting and useful to you. More »


 

From the SQLServerCentral Blogs - The Failed Job

Wayne Sheffield from SQLServerCentral Blogs

I was looking into a failed job on a SQL Server 2008R2 instance. My first step was to check the... More »


 

From the SQLServerCentral Blogs - SSRS Map Layers Parameter

MikeDavis from SQLServerCentral Blogs

With the new Map control in SSRS 2008 R2 there are three different map types, Road, Aerial, and Hybrid. If... More »

Question of the Day

Today's Question (by Raul Gonzalez):

In SQL Server 2008R2, if we create the following table, what will be the data type and nullability for the computed columns?


CREATE TABLE #t
    (
      col1 VARCHAR(8) NULL
    , col1Computed AS LEFT(col1, 4)
    , col2 VARCHAR(8) NOT NULL
    , col2Computed AS LEFT(col2, 4)
    , col3 CHAR(8) NULL
    , col3Computed AS LEFT(col3, 4)
    , col4 CHAR(8) NOT NULL
    , col4Computed AS LEFT(col4, 4)
    );

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


We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: computed columns.

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

ADVERTISEMENT

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

Yesterday's Question (by Steve Jacobs):

What is returned by these four queries? Row values in the answers are separated by commas.


CREATE TABLE #temp ( a NVARCHAR(10) );
INSERT INTO #temp
        SELECT '12345'
        UNION ALL
        SELECT '2A456436'
        UNION ALL
        SELECT 'afsdbcd';
--Q1
SELECT a
    FROM #temp
    WHERE a LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9]%';
--Q2
SELECT a
    FROM #temp
    WHERE PATINDEX('%[0-9]%', a) != 0;
--Q3
SELECT a
    FROM #temp
    WHERE PATINDEX('%[^0-9]%', a) != 0;
--Q4
SELECT a
    FROM #temp
    WHERE ISNUMERIC(a) = 1;
DROP TABLE #temp;

Answer: Q1:2A456436 Q2:12345,2A456436 Q3:2A456436,afsdbcd Q4:12345

Explanation:

Answer:

Q1:2A456436 Q2:12345,2A456436 Q3:2A456436,afsdbcd Q4:12345

I usually write a UDF but my preference is a .NET assembly to perform REGEX processes since .Net handles regular expressions more efficiencently than SQL Server.

PATINDEX:  http://technet.microsoft.com/en-us/library/ms188395.aspx

ISNUMERIC:  http://technet.microsoft.com/en-us/library/ms186272.aspx


» Discuss this question and answer on the forums

Featured Script

Search Table And Field In All Database And Return Field Value

Babak Pirooz from SQLServerCentral.com

This script will allow you to search table and field in all database and return field value. Below is an example of the what the output will look like.

More »

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2014 : Development - SQL Server 2014

Cursor to Delete data - Hey Guys, i have to delete data from a table which is older than 2 weeks, how can i use a...

SQl question - Hello Guys, Can you help me to solve these questions? Employee Customer id name age salary id name city industry type 1 ben 61...


SQL Server 2012 : SQL 2012 - General

SSIS Performance using local path vs. network path - Hi there, I was wondering if anyone checked already what happens when you create a SSIS Package importing a flatfile source...

Give user read only access to all databases current and future ? - Is there a way to give a user read only access to all databases current and future, without having to...

RESTORE, Media Set and Family Count - I backed up a SQL Express 2012 DB I was working on prior to the OS crashed. The hard disk...

Index hint on query - Hi All There is a query which uses 5 columns. It's a part of a very big query which is not...

Multiple Data Files - I have a db that has 3 data files. The first data file is on one drive and the remaining...

Is the AVG_RANGE_ROWS in DBCC SHOW_STATISTICS WITH HISTOGRAM first step 0 or 1 - Hi, with reference to http://technet.microsoft.com/en-us/library/ms174384.aspx [quote] B. Specifying the HISTOGRAM option [/quote] when I execute the following code [code="sqlcode"] USE AdventureWorks2012; GO DBCC SHOW_STATISTICS ("Person.Address", AK_Address_rowguid) WITH HISTOGRAM; GO RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS AC3973FF-355C-47B

Change initial sixe of transaction log file - Dear All We have a db with Full recovery Model. Daily full backup is taken and every hour transaction back up...

how sql server failover cluster 2012 installation works on top of sql server 2012 that already installed - this is steps that I have completed: 1/ window cluster 2012 created 2/ installed sql server 2012 (I selected new sql...

DBCC CHECKDB Hangs - Resource Governor Issue - All, I ran into a strange issue on one of our servers when playing around with DBCC CHECKDB and resource governor....

OUt of memory - I have a development 2012 instance that is used for testing. After running IS packages to populate a data warehouse...

DB Offline - Hi In event log i can see 'Database option changed from Offline to On'. Whant to chekc why did the db...

Memory Management - I have a SQL Server 2012 server with SP1 - 64GB RAM, server is solely dedicated to SQL Server so it's...

Permission required to install sql cluster - Hi All, I need to install a two node sql 2008 r2 cluster on win 2008. What are the permisson or previlages...


SQL Server 2012 : SQL Server 2012 - T-SQL

SQL Server 2012 Gotcha with Identity Column - We just ran across a gotcha with our SQL Server 2008 R2 to SQL Server 2012 migration. One of the applications...

Combine Multiple Queries into a CTE? - Hello all, I am looking to build a query with a result set that combines multiple queries to appear as...

Ranking value based on row value - Hi, I would like to create a ranking value based on the row value.For example consider the following sample Declare @t...

How to reduce the joins in sql querry - Hi I have select query with 10 Joins in place with different i want to improve the performance of the...

identifying many to many relationship between columns - Hello All, I am struggling with something that is conceptually simple but I'm at a loss at how to implement it....

Passing multiple values to a parameter in a stored produre - Can anyone help, I have the following stored procedure create procedure TEST_procedure @exampleid as update tablea set text_field = 'Y' where example_id in (@exampleid) I would like...

Select statement to return sum by year - I am working on a query to show a sum of costs by year in a vendor purchase table. the...


SQL Server 2008 : SQL Server 2008 - General

I am running this store procdure on new query window and it works fine but when it run through sql server agent it gives me error in three steps... - I NEED YOUR HELP IN THIS. I AM NOT ABLE TO FIGURE IT OUT WHY I AM GETTING THIS ERROR....

How to prevent NULL values - Hello, Any suggestions how I can prevent the NULL values in a column when the datatype is float? Thanks P.S. ISNULL(mycolumn,'0') is not...

connections with status set to 'sleeping' - Hi We recently had a performance issue and whilst it was occurring I ran adam Mechanics sp whoisactive in a...

Question about Transactional Replication Initializing from Backup - So yesterday in my test environment I did the following to set up transactional replication without using the snapshot agent. 1....

SQL Job - I want to find out how many SQL jobs are associated with one particular DB? Nita

Is this a bad habit i need to clean up after? - recently took over a new database, and found that every single table that has a clustered primary key ALSO has...

Error deployeeing SSIS package using the SSIS deployment utility - Hi, we recently upgraded our reporting server from SQL 2k5 64bit to SQL 2k8 R2 64 bit. It is running...

changing collation for a database - Hi, On our Production server for 1 database, Client want to change the collation setting to French language as the application...

Migration from Standard editon to enterprice edition - Hi, We have to migration all test,dev and prod (sql 2000 and 2005) servers to sql 2012 and sql 2008 r2. In...

Log file growth - Hello all, I have a DB that data file is 90 GB and the log file after the maintanance jobs...

Error for checkdb for master database - When i run dbcc checkdb on master database i get error like Msg 5030, Level 16, State 12, Line 1 The database...

Indexes - Need script on removing duplicate Indexes and unused ones.. below is Missing Index query.. Please advice..is it fine query?? SELECT migs.avg_total_user_cost * (migs.avg_user_impact / 100.0)...

Interactive query - I am not sure if 2k8 or higher supports interactive query, does anyone know any work around to write some...

nolock option - How does a nolock option in a query internally work in SQL Server???

Problem with bcp and coalesce - Hi Professionals I am trying to extract a particular set of columnheadings from the importedquery variable that I am passing in...

Transactional Replication Performance Issues After Migration From 2000 to 2008 - We currently have 140 SQL 2000 publications replicating to a single 2008 server with about 25 articles. 5 of the...

Split a string at the first space after 50 characters - Hi All, I have long descriptive varchar(max) column declare @str varchar(max) set @str = 'Guidelines and recommendations for lubricating the required...

BULK INSERT into a table using encryption - Hi all, I don't know if this can be done, but here goes: 1. I have a flat file containing employee numbers...

NOt able to apen SQL query Window - Hi, We are having sql server 2008 r2 and when we trying open sql query window getting below error. See the end...

Update query for similar values - Hi, I have a grid like this and when I change Designation value of Name X from PA to PAT, this...


SQL Server 2008 : T-SQL (SS2K8)

XML Parsing Issue - I am currently trying to parse the following XML using the following T-SQL code [code="sql"] DECLARE @XML XML = '<?xml version="1.0"?> <root triggerEvent="UserChanged" xmlns="http://www.xyz.com"> ...

T-SQL puzzler - given a cell in an N-dimensional space, return the cells that are inline with the given cell along each axis - The cells in an N-dimensional space are modelled with the 2 tables below. A script is needed that takes a...

Most efficient way to get a single unique combined record for distributed information corresponding to same ID - I have a scenario like this: [code="sql"] create table #temp(id int, name varchar(50), age int, contact_number varchar(50)) insert into #temp(id,name) select 1,'John' go insert into #temp(id,age) select...

Divide by zero error - Hi, I'm just trying to get a "percent of overtime" from the query below: I get a 'divide by zero error' I need...

Performance related Question - Please find the query below SELECT TOP 12 CPL.PERIODID AS MONTHID,ISNULL(TEMP.OUTSTANDINGAMOUNT,0) OUTSTANDINGAMOUNT FROM (SELECT PL.PERIODID as MONTHID, SUM((PL.BASEVALUE)*CASE WHEN CURR.RATE IS NULL THEN 1...

Trigger To Prevent a User From Inserting a Blank Column - Hi Guys, I need help with a trigger, I would like to prevent a user from inserting a blank string...

Need recursive CTE to extract BOM Model/Option columns with structure for lower level items - [b][u]What I Need....[/u][/b] I need to extract a partially "de-normalized" data set from an indented BOM in a MSSS 2008R2 environment....

Inserting error data into a table from CATCH statement within a TRY CATCH - I've been researching for past two days and hitting my head against wall, but I can't figure this out. I am...

Criteria Table - Hi all, I've had a trawl through the forum but can't find anything that will help although I'm sure this isn't...


SQL Server 2008 : Working with Oracle

Totally lost on creating a linked server from 2008R2 to Oracle - I've reviewed the two threads here, and many articles from doing a Goggle search. They all follow a theme, but...

Adding an Oracle Data Source in SSIS - Windows Server 2003 DC 32-bit/MSSQL 2008 /Oracle Client 10.2 I've tried adding various Oracle data sources in SSIS using: 1. Oracle Provider...


SQL Server 2008 : SQL Server Newbies

SQL Server 2008 R2 Logs - I find the following error message on the sql server Logs. There are a lot of them. Can anyone help...

CASE WHEN and Update of Table - I am looking at updating a table with a trigger and have attached the code - but am getting basic errors...

sql server agent won't start - SQL Server 2012 SQL ServerAgent is currently Stopped. I want it to be running, so I right click and select start...


SQL Server 2008 : Security (SS2K8)

Accidentally deleted all existing privilges for a domain account - Help - I was using a tool to copy permissions from old account to new accounts and I dont know what in...


SQL Server 2008 : SQL Server 2008 High Availability

clustering- quorum drive - We failed over the services to the passive node. In this process quorum has not moved to the passive node...

Log Shipping - I have set up a server to monitor log shipping (SQLMon01) from a primary (SQLApp01) to a secondary (SQLApp02) and...


SQL Server 2008 : SQL Server 2008 Administration

MemToLeave Values - As a followup to me earlier thread "Corrupt Backup File", I found the following script here to get some memory...

Error while installing SQL Server 2008 - Hi, I am trying to install SQL 2008 R2 Standard 64 Bits on a box which already has SQL 2005 Standard,...

How to find space used by a temporary table - Hello: For a permanent table using ''exec sp_spaceused <tablename> " will show me how much space is used by a table .... or...

Benefits of Using Proxy Account with SQL Agent - Can anyone tell me what are the benefits of using a proxy account with SQL Agent to run SSIS Jobs? I...

tempdb growth - One of our prod server started behaving weirdly from last couple of days. Suddenly we are seeing a growth in...

A network-related or instance-specific error occurred while establishing a connection - Hi, From some days I am facing this error. This error occurred frequently after some time on accessing the website. But database...

Script to Delete Reports Older than a Certain Number of Days - Hello Everyone! I am a novist in T-SQL programming. I have an urgent task, as part of the maintenance plan, described...


SQL Server 2008 : SQL Server 2008 Performance Tuning

Optimizing a view - Hey guys. I have a query that I would like to stick into a view for the ease of calling...

Execution Times vs Logical Reads/Scan Count - Hello guys, I want to discuss about your experience what is better. for example. I have procedure which has lot...


SQL Server 2005 : Administering

Audit tasks - Hi, I want to create a database that log into a table this type of operations: 1) t-SQL script passed againt the...

can we do database mirroring in different domains.Is it possible - Hi guys, can we do database mirroring in different domains.Is it possible can you please provide the answer its very urgent.

"Sql server error 5120" while attaching database - HI, I am trying to attach few databases to sql server 2005.I have my .mdf files in "D:\MSSQL\Data" and my .ldf...


SQL Server 2005 : Business Intelligence

How to change the connection strings of a child package? - Hi friends, I am trying to execute 10 packages through execute package component. I see the connection strings for the...

SSAS Deployment through Deployment Script - We have four file in SSAS bin directory i.e. XMLA file .asdatabase .deploymenttargets .configsettings .deploymentoptions How to deploy on SSAS production...


SQL Server 2005 : Development

Suppressing output from a stored procedure. - Hi, I'm calling DTExec from a stored procedure, using xp_cmdshell (trust me!). It returns (selects) lots of status information which I'm not...


SQL Server 2005 : SQL Server 2005 General Discussion

Permissions to querying system tables - what are the permissions (roles ) to querying system tables in sql server ???


SQL Server 2005 : SQL Server 2005 Performance Tuning

Poor query performance - I have a procedure making a call to a table that normally takes 5-10 seconds to return results. However, lately...


SQL Server 2005 : T-SQL (SS2K5)

Customized sorting query not accepted - [code] Select Distinct Category from menuGroup ORDER BY (CASE Category WHEN 'SVP' THEN 1 WHEN 'OS' THEN 2 WHEN 'Classification' THEN...

How to combine 3 rows into one - Hi, I have a query that returns data like this: Name emp id TypeCode TValue TypeCode TValue TypeCode Value Don King 1234 S...


SQL Server 2005 : SQL Server Newbies

Use Excel to input data to SQL Server 2005 - Hi all, My first post here on SSC. Some of the core systems where I work use Oracle databases and...


Reporting Services : Reporting Services

Parameter selection failure - I have a report being delivered through SSRS 2012 in Sharepoint Integrated mode. There are three datasets that use a...

Make the text Bold or Underline - Goal: Make the country's name to have underline or bold in the textbox. Please rememeber that there are lots of...

Validation message for report parameter date validation - Hi I have to show a validation message in my SSRS report if the report parameter start date is less than...

Default value for report parameter date field - Hi, My SSRS report parameter date field requires “next Monday” as default Start date and “Sunday of the next week” as...

Error In SSRS Expression - My SSRS report shows a #Error for the following expression. Is there any problem in the syntax? = "Quality Assurance" & First(Fields!Auditor.Value,...


Reporting Services : Reporting Services 2008 Development

ssrs piechart format to 2 decimal places - I have the following dataset for my ssrs report : [code="other"]Year Month Sales1 Sales2 2013 3 29.22 29.99[/code] I want to show...

ssrs 2008 use 2 iif statements - When I want to hide the heading in an SSRS 2008 R2 report, I will also want to set the...

render report directly to PDF (or any other format) - I have a report that i want to render directly to PDF (rather than exporting to PDF from the report...

Default values in SSRS not working from WebServer - Hi all, This is a puzzler. Created a report which has several cascading parameters, all driven by Shared Datasets. Within...

Group a column into different subtotals - Hi I know this might be a pretty obvious thing to many. I need help to split data in a...

Display Country's name in Textbox - Goal: Display the name of countries in a list based on selection. For instance, if I select Australia and France, the...


Programming : Powershell

Copy a file from each subdirectory to one folder - Hi, I'm trying to do something like this: [code="plain"] Get-ChildItem -Path "\\drive\s*\*.txt" | ForEach-Object { Copy-Item "\\drive\Erik" } [/code] But it isn't working, and I'm not really...


Data Warehousing : Integration Services

ssis script task throws error when run from sql agent job - When I run this package from Visual Studio, it works fine, but when I run it from the sql agent...

Create table in MS SQL Server ffrom Quickbook - Hi Experts Can anyone please guide me how to create destination dynamically as its from Quickbook Source table.In Quickbook i...

Date change format in SSIS - Hi I have a file i want to import into a database with the date format as: 20131113 When the date has...

MySQL to MSSQL - What is the best approach for extracting data from a MySQL Database and importing it into MSSQL? 1) export to...


Data Warehousing : Analysis Services

Dimension - Accessing related Attributes - Hi All This must be a seriously stupid question for people well versed in Dimensions and Hierarchies so I hope to...


Database Design : Hardware

My first proper server... - Hi, I'm looking to improve my Windows Server know how by getting my own server at home and installing SQL...


SQLServerCentral.com : Anything that is NOT about SQL!

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


Career : Employers and Employees

Jr DBA Team interview advice - Goodmorning, I have an interview tomorrow with about 3 DBAs. The Manager wants me to come in to meet with them as...

AGILE GONE WILD - Anybody have advice on conversions from "Technical Silos" to "Functional Groups". We are going through a re-org and what might...

To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2013 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com