In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL DBA Bundle ‘The Case of the Missing Index’
Discover the Top 5 hard-earned lessons of a DBA, presented by The DBA Team. Learn from lesson one now.
 
SQL Backup Keep your SQL Server backups safe in hosted storage
Protect your backups by taking them off site into secure hosted storage. Use SQL Backup Pro to compress and encrypt your backups and upload them to Amazon storage. Find out more.
 
SQL Monitor Free eBook: SQL Server Concurrency
Every DBA must understand SQL Server concurrency and how to troubleshoot any issues. Kalen Delaney's eBook explains all - download it today.

In This Issue

MDX Guide for SQL Folks: Part II - Hierarchies and Functions

Learn everything about MDX by drawing only on your SQL knowledge.  More »


Checking SQL Server Services Owner

Not only is the DBA responsible for the running status of the Services, they are responsible that the Service is running with a proper owner. More »


Win a Collection of SQL and .NET Books. And a Bookshelf

After winning a number of awards for our software, Red Gate is giving away books to 300 people as a celebration. More »


PASS Data Architecture VC presents James Serra on Introduction to Master Data Services

On Thursday August 20th 12PM noon Central, James Serra will discuss how companies can consolidate their enterprise data with the new feature of SQL Server 2012 Master Data Services. More »


From the SQLServerCentral Blogs - You Need to Manage Passwords

I saw a note this week from CNet about a system built to crack passwords (also on ArsTechnica). It reminded... More »


Editorial - Holidays and other Social Technologies

Today we have a guest editorial from Bill Nicolich.

This scenario seems to be stereotypical. The more outward-facing departments in a company, like the sales and marketing departments, will decorate the place and get involved in Halloween and other holidays. The more internal departments, like Technology and Accounting, will mostly ignore the event and won't participate. That creates a sort of cultural divide, though there are always exceptions.

This holiday cycle has got me thinking again about what passes for technology.

In the culture I'm in, living in the USA, technology mostly refers to computer technology. The last thing on people's minds when they think of technology are things like holidays and celebrations. But they are. They're social technologies designed to heighten social interaction, mark the passage of time, create anticipation and so forth.

Ever since reading Neil Postman's book Technopoly: The Surrender of Culture to Technology, I've been interested in broadening out the definition of technology to include far more than computing, and to include things like the various emotions in our register as a species, games, rituals and the like.

The question is, does that get me anything to have a broad definition of technology?

Well, I think it gets me a way to get more perspective. I get more options. Instead of reaching into the pocketbook and buying a new gadget to promote my children's education, I can think about how they used to use a piece of chalk and slate and seemed to do quite well in learing the ABC's and 123's - and maybe money spent on caring educators might do better.

The leading thinkers on methodology in software development are talking about things like finite and infinite games, software development as group theory building, building team cultures where responsible failures and risk-taking are cultiavated and encouraged. There's a lot of talk going on about things other than gadgetry.

So, as I look at that I guess I buy into it. I see more reasons to participate in activities that seem on the face of it to be somewhat of a time sink - like decorating and participating in holiday activities.

These are ancient technologies that our species have utilized to good effect - and even in a modern context, they seem to stand up for reasons that pop up on the radar of conscious awareness and strategic action.

There's this book that I'm excited about: Finite and Infinite Games: A Vision of Life as Play and Possibility by James P. Carse - and for me, this is an area that's really interesting for several reasons. One, it's about cultural technology. Two, it belongs to an area that is widening up - that is creating more awareness around play at work similar to the book Reality is Broken: Why Games Make us Better and How They Can Change the World.

There's the suggestion that although not intuitive, there's areas to look at that can enhance what we're doing at work.

I suppose we're running into the same problem with how games are defined. They're increasingly defined as video games - whereas a broader definition includes the dating game, the career game and other games that involve strategic action in the face of various challenges.

Lastly, I think there is a dialectic to look at regarding Halloween and holidays. You have two poles representing perspectives on participation with one that says absolutely not and the other with absolutely yes - and let's spend half the budget on it.

In the middle is an area of conflict between the two sets of values, one more social and the other more individualistic. There's the chance in the middle where values collide to learn how to integrate the values and then go from antithesis to synthesis.

So, I did a little walking around and talking about it with team members - primarily because I truly value understanding who I work with and learning to appreciate their perspectives and personalities. I learned something and I encountered some strong opinions. So, doing that was very worthwhile.

For me, I'm going to go in the middle where values and things tend to collide and look to learn something.

What are you planning to do?

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

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


Question of the Day

Today's Question:

Can be table variables used in UDFs?

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.

Professional SQL Server 2012 Adminstration

Microsoft SQL Server 2012 will have major changes throughout the SQL Server and will impact how DBAs administer the database. With this book, a team of well-known SQL Server experts introduces the many new features of the most recent version of SQL Server and deciphers how these changes will affect the methods that administrators have been using for years. Loaded with unique tips, tricks, and workarounds for handling the most difficult SQL Server admin issues, this how-to guide deciphers topics such as performance tuning, backup and recovery, scaling and replication, clustering, and security.

Get your copy from Amazon today.


Yesterday's Question of the Day

I execute:

SET LANGUAGE us_english

I create a table  as

CREATE TABLE#T
(Id INT,Xday VARCHAR(20))

Which contains the following data

Id   Xday
1    25-DEC-2013 00:00:00
2    25DEC2013 00:00:00
3    2013DEC32 00:00:00

I then execute the following T-SQL Statements

-- SELECT #1
SELECT Id,TRY_PARSE(Xday AS DATETIME) FROM #T ORDER BY Id

-- SELECT #2
SELECT Id,PARSE(Xday AS DATETIME ) FROM #T ORDER BY Id

SELECT  the five (5) correct answers

Answer:

  • Select #1 Returns 3 rows
  • Select #1 does Return a row with a NULL value for Xday
  • Select # 2 Returns an error message
  • Select #2 Returns 2 rows
  • Select #2 does NOT Return a row with a NULL value for Xday

Explanation: http://msdn.microsoft.com/en-us/library/hh213126.aspx string_value must be a valid representation of the requested data type, or PARSE raises an error http://msdn.microsoft.com/en-us/library/hh213126.aspx Returns the result of an expression, translated to the requested data type, or null if the cast fails. http://technet.microsoft.com/en-us/library/hh213126.aspx TRY_PARSE relies on the presence of .the .NET Framework Common Language Runtime (CLR). -- Also has a list of specific cultures

» Discuss this question and answer on the forums

Professional SQL Server 2012 Adminstration

Microsoft SQL Server 2012 will have major changes throughout the SQL Server and will impact how DBAs administer the database. With this book, a team of well-known SQL Server experts introduces the many new features of the most recent version of SQL Server and deciphers how these changes will affect the methods that administrators have been using for years. Loaded with unique tips, tricks, and workarounds for handling the most difficult SQL Server admin issues, this how-to guide deciphers topics such as performance tuning, backup and recovery, scaling and replication, clustering, and security.

Get your copy from Amazon today.


Featured Script

Find memory used by particular Database

Using sys.dm_os_buffer_descriptors we can find out how much memory a database or a object is consuming. 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

Maintenance plan getting failed for system db and user db - We have two different Maintenance plan for system database and user database doing reindexing for system db whereas reindexing and...

error 18272/16/1: during restore restart, an i/o error occurred ... *.CKP file not found. - I'm getting the above error on my DR server. The logs are shipped from live to DR & I now get this...

Querry getting delyaed - Hi We have a table which contains allmost 1 lak to 2 lak of records querring on that table getting delayed...

Update doesn't update if the condition of Instead of update is unfulfilled - Hello, i'm newbie in t-sql.. and when i try to catch event before update on trigger sql using instead of...

Two databases share same Full Text Catalog - Hi Administrators :), I need to duplicate one database. It should be copied on same server, just with other name. That...

SP in Rollback - I have a script that is marked as being in rollback in the master database. The wait time for this is...

Maintenance plan DB bAckup plan failing - I have scheduled the DB backup in our server but frequently its failing with below error Standard Edition (64-bit) RTM 9.00.1399.06 Agent is running...

TempDB Growing MDF - Hi I am responsible for a SQL Server in an emergency Organization in Austria. We use MS Sql Server Version 2005 (9.00.3054.00) Since...

SQL Server 2005 : Business Intelligence

ssis FEL containter - Hi All, I have Foreach Loop Containter and it has the following items: (1) Execute SQL Task - executes .sql scripts taken...

Database Diagrams tool in SQLSERVER - Hello : We are planning to design new Datawarehousing product. I am taking care of Database Design from start. I am...

SQL Server 2005 : CLR Integration and Programming.

How to use raiserror in UDF's - Im working in a Oracle to SQL migration project, I need to migrate a function which is using Raiserror() I have...

SQL Server 2005 : Data Corruption

CHKDSK on a SAN Volume - I need a little direction. I have a clustered SQL db (Sql 2000 / Win2k). I have errors in the log for corruption...

SQL Server 2005 : Development

Complicated Query Requirement - Hi All, I want the following manipulation without using and loop r cursors, i tried with CTE, but i didnt get...

SQL Server 2005 : SQL Server 2005 Strategies

How to notify through DB MAIL when sql server agent stopped automatically? - Hello all, Can someone tell me how can we send email when my sql sevrer agent stops automatically, i have my...

SQL Server 2005 : SQL Server 2005 Performance Tuning

DeadLocks and exchangeEvent - We are getting a lot of deadlocks in our web application w/SQL 2005 backend. I am trying to get a...

SQL Server 2005 : SQL Server 2005 Integration Services

SSIS Newbie - Hi All, I am new to SSIS,I have given task of implementing the following task create SSIS packages which takes input parameters...

SQL Server 2005 : T-SQL (SS2K5)

Stop joined aggregates from multiplying - I have three tables. A customer table, an invoice table, and a payment table. I want to pull, in a...

exec master.dbo.xp_cmdshell - [b][/b]:ermm: Its been working all this while but for some reason it has stopped working for only 1 of our...

Delete trigger - I'm not sure what is going on here - if my brain has gone on Christmas holiday early or what, but...

SQL Server 2005 : SQL Server Newbies

Split input string into multicolumn - multirows - Hello all - I am having hard time to split an input string into multicolumn - multirows. Task - Create a stored procedure that...

SQL Server 7,2000 : Administration

SQL Server on a virtual server - Hi, I'm collecting information, recommendations about advantages and disadvantages of usage a virtual server for a [b] SQL Server in production[/b]...

SQL Server 7,2000 : T-SQL

Not getting result of MAX() - Hi i have a table having 2000000 rows. and i want a result of : select max(pksrnoN) from Table1 it is numeric...

SQL Server 2008 : SQL Server 2008 - General

Percentage Chnage - partition by date? - Hi All, I have a quick question. My code below shows the data I'm pulling from my temp table [code="sql"]select [year],[date],[dayofweek],[daynum], cast(count(CASE...

Combine 2 unrelated queries into a single SP - Hi All, Is there any way to combine multiple SQL stored procedures into a single SP ,so that the single SP...

db in restoring - one of my db is in restoring status, we are unable to find the connections on the database . how can...

Import from Access - I have an Access database with one single file that I want to import to my SQL Server DB... If...

Need Help on SSIS ..Urgent Task - Hi Koen, SSIS TASK Help: Good day. I am new to SQL. I have a task to do. Hope you can help...

SQL Server 2008 r2 log file in C:\USERS\Local\Temp\sql.log grown upto 10GB - Hello All, I have SQL Server 2008 r2 installed on the D:\ on our development server. In C:\USERS\Local\Temp\sql.log there is an...

Error :The Data Pump Task requires Transformation to be specified while executing DTs packages - I need updation through Data transformation Packages. I trying to update database with DTs packages and right know source and destination...

How to handle the a delete scenario in the following case? - Hi All, Here is an interesting and a challenging scenario that really gave us one hell of time during development. [b]The...

Alter Index : Rebuild - HI i have rebuilt one of the clustered index , table definition [code="sql"]CREATE TABLE [Person].[Person_BK]( [BusinessEntityID] [int] identity NOT NULL, [PersonType] [nchar](10) NOT NULL, [NameStyle]...

Rar a folder using sql script - When i execute the below command, i get the below error: exec master..xp_cmdshell 'rar a -ep2 -m3 -t D:\CDR_FTP\Archive\20121216.rar D:\CDR_FTP\Archive\20121216\' 'rar' is...

Missing Indexes on Temp tables - The DMV dm_db_missing_index_details has suggestions on missing indexes on permanent tables but I need some suggestions on ways to identify...

How to Shcedule set of SP,executing sequencly - Hi, I want Scedule the Set of Stored Procedures And if any one SP gets errors then next one should gets...

Grouping by time - can't figure this out! - I have a bunch of data with timestamps - there may be any number of rows in a given time period,...

bulk Insert Error - Hi I am trying to execute .tbl file to bulk insert in the server ServerA. The .tbl file was saved...

How to install an oracle linked server on SQL Server 2008 r2 failover cluster manager - How to install an oracle linked server on SQL Server 2008 r2 failover cluster manager I'm reading some articles and applying...

Value of column dependent on the values of others. How can I do it . Please help ! - Hello , this is the first time i'm posting here, but this time i really need your help. I need to...

Identifying Querys causing CPU spike - Hi Everyone, I have a situation where the CPU on my data warehouse server was pegged at 100% for approx. 10...

Not in sql error - Hi guys plz help I am getting below error when i include not in (6,8) in my sql statement. Msg 8180,...

Issues Getting Max(Status) by ID and Max(StatusDate) - I'm having one of those moments where I'm sure I'm just over thinking the problem, but none the less I...

Enhanced Variable Declaration in SQL Server 2005 Mode - In SQL Server 2008 the new enhanced variable declaration functionality is working nicely (allowing you to assign a value to...

Distributing the greatest value by rank in SQL - This seems as though it would be terribly simple...but I am stuck. I need to distribute as "average sales" value among...

Rebalancing data from a single file to multi-file setup - Hi, I'm inheriting a series of databases that have grown beyond 100GB yet only have a single datafile. I've been...

how to find ASCII characters in a table? - Can any one tell me the sql query to find the ASCII characters (0 to 127) from multiple columns in...

MDW best practices - Hi All, Please share the best practices to be followed to cinfigure MDW. I am goint to implement MDW to capture...

Removing data collection jobs - All, I have been able to successfully disable data collection, but when attempting to delete the jobs, I get: "The delete statement...

SQL Server 2008 : T-SQL (SS2K8)

showing error my procedure - hai freinds i wrote the query for login page if already user again registered means it ll through error unfortunately...

Some doubts on T-SQL basic fundamentals - If there is an update statement using 'where' clause, and the 'where' clause returns ten rows, how exactly sql engine...

Get continuous date with count - hi all, i am having a requirement like this input EmpID reportdate reportname noofdays 47 11/29/2012 Thursday 1 47 11/30/2012 Friday 1 47 12/4/2012 Tuesday 1 47 12/5/2012 Wednesday 1 47 12/7/2012 Friday 1 47 12/10/2012 Monday 1 48 11/29/2012 Thursday 1 48 11/30/2012 Friday 1 48 12/4/2012 Tuesday 1 48 12/5/2012 Wednesday 1 48 12/7/2012 Friday 1 48 12/10/2012 Monday 1 48 14/10/20

single quote in sql statement - I would like to set a variable that is a sql statement, but the syntax about the quotes is so...

String collation - Hi: Given any random data string, is there a way to know the collation of the string? I guess more than...

how to read xml variable using openxml - Hi, I have a xml like this. <DocumentElement> <tLockhistory> <lockrequesteddate>2012-12-14T00:00:00+05:30</lockrequesteddate> <lockexpirydate>2012-12-29T00:00:00+05:30</lockexpirydate> <createddate>2012-12-14T00:00:00+05:30</createddate> </tLockhistory> <tLockhistory> <lockrequesteddate>2012-12-14

concatenate of two values - Hi i have table like [code="sql"] CREATE TABLE [dbo].[Mas_Shift] ( [ShiftId] [int] IDENTITY(1,1) NOT NULL, [FacilityId] [int] NULL, [ShiftType] [nvarchar](5) NULL, [Shift] [nvarchar](3) NULL ) INSERT [dbo].[Mas_Shift] ([ShiftId], [FacilityId], [ShiftType],...

Help Required to handle multiple employees with overlapping datetimes (concurrent activities) - Morning, I have a data set that contains employee shift data, when they logged in and logged out of a certain...

Finding combinations of values - My table structure CREATE TABLE trefClientShares{ intLMid int NULL, intMLid int NULL, intISid int NULL, intFRid int NULL } I need to figure out...

Need a TSQL Book that I can expertise in writing Tsql - Hello guys, I need to know ur best strategy how you learned Tsql, I know it will sound silly but I...

ORDER BY Should be same as my input in IN() - DECLARE @T TABLE(ID INT,NAME VARCHAR(10)) INSERT INTO @T VALUES(1,'PPP') INSERT INTO @T VALUES(2,'AAA') INSERT INTO @T VALUES(2,'ZZZ') INSERT INTO @T VALUES(3,'XYZ') INSERT INTO @T...

NPV and IRR in SQL - Years ago I wrote a piece on the Net Present Value (NPV) function in SQL. If you don't know or...

Update Query Fails Table Gets Locked By Select Query - Hi, My update query fails as table gets locked by select query. When i am selecting data it comes around 500 something. But...

SQL Server 2008 : SQL Server Newbies

Need Help on SSIS ..Urgent Task - SSIS TASK Help: Good day. I am new to SQL. I have a task to do. Hope you can help me...

Regarding replication - Hello Masters, If I will add any new table or database to existing configured Replication, is it needed any downtime ?

Instance level backup - Hi All, Is there any way that we can take instance level backup. I have very large number of databases in...

Script logins - Hi When you script a login using SSMS the script generated always has "* For security reasons the login is created disabled...

DBCC Commands - Hi All, This is Rangaraja.im fresher. Can anyone please explain mostly used in real time DBCC Commands? Regards, Rangaraja rangarajasqldba@gmail.com

SQL Server 2008 : SQL Server 2008 High Availability

transactional Replication- Two log files - For some reason we have two log files on one of our production database. We have transactional replication setup on...

SQL Server 2008 : SQL Server 2008 Administration

Perfmon SQLServer:CLR CLR Execution counter high - I'm monitoring the SQLServer:CLR CLR Execution Performance Monitor counter on my relatively small OLTP database server (SQL 2008 R2 latest...

Table has large amount of Data querry geting delayed - Hi We have a table which contains allmost 1 lak to 2 lak of records querring on that table getting delayed...

Problem with Datetime Function - I have SP which contains function convert(varchar(12),[datetime],112) =convert(varchar(12),getdate(),112) this takes lots of time during the Market hours and it is very...

dbcc indexdefrag vs reorganize - dbcc indexdefarg or reorganize which one i need to use in production ? I am using sqlserver2005 and 2008 r2 versions

DB Shrink - I have a database that is 5.81836 GB in size with 2.3623 GB free space (due to old data being...

deleted Mount point accidentally - Hi All, I have a cluster in which has 3 mount points. I was going to look at properties of one of...

Can a cluster instance be changed from SQL Server 2008 Enterprise to Standard? - One of our DBA's installed an instance of SQL Server 2008 R2 Enterprise Edition onto our multi instance cluster. It...

Deadlock due to intra-query parallelism - Hi We have been having deadlocks a lot and to track them I enabled Trace Flags 1204 and 1222. The output...

Career : Employers and Employees

Money vs Job Satisfaction, where is the balance? - Im currently in a job i really enjoy. As a consultant for a small specialized database consultancy firm I get...

SQLServerCentral.com : Anything that is NOT about SQL!

Is there something wrong with the search on the site? - It's coming back with no results for anything you put in there. I even tried just searching on 'sql' and...

Fantasy football 2012 - Only 2.5 short months to football, can you believe it? I'm not ready, hardly feels like it's summer time yet. You...

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

Where can I find KPI gif images - So I want to add some KPI images files to some of my reports, does anyone know where I can...

[rsErrorReadingDataSetField] The dataset contains a definition for the Field. The data extension returned an error during reading the field. There is no data for the field at position 1 - Hi, I am trying to create a report which takes the instance_name and Database_name as input parameters and displays the...

Is it possible to code custom gradients? - Hello all, I'm playing about with SSRS being relatively new to it and I've got the hang of assigning colours to...

Multi-value filters. Tricky problem with the use of wildcard. SSRS 2005 - Hi pros out here, In my report i would like to create an optional multivalue filter for @accessVar. However my biggest...

Count is returning a count of all rows - I have a dataset column in a table region and I want to count on a condition with an expression...

Database Design : Hardware

Lots of Memory or Fusion I/O - My first post so hello to everyone for the first time :) I work for a company who make a large...

Data Warehousing : Integration Services

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER error - Hi All, I have 4 simple SSIS packages that run successfully on my machine locally. I had restored the database from my...

Import/Export Wizard Weird Behavior in SQL Server 2008R2 - Hi All, I saw some weird behavior of Import/Export wizard in SQL Server 2008R2. I made a video of it please...

While Importing a data need to find error line number and then rollback - Friends, My requirement is to import data from a remote server, if error occurs then get the row numbers and rollback,...

to check file exist or not in sharepoint using script task in ssis 2008 r2 - Hi All, I have sql server 2008 R2 installed on my system. I am trying to check if file exist...

Data Warehousing : Analysis Services

Loading change data from AS400 (or other source systems) - Greetings all! So I will keep this concise as I can; I have a source as400 system I am extracting data...