In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Backup Pro "Cut the backup time by hours and the file size by 80%" Hazel Cawood, Systems Analyst
Find out how much time and space you can save with SQL Backup Pro. Get compressed, encrypted and fully verified backups. Download a free trial now.
 
Deployment Manager How to automate your .NET and SQL Server deployments
Deploy .NET code and SQL Server databases in a single repeatable process with Red Gate Deployment Manager. Start deploying with a 28-day trial.
 
SQL DBA Bundle ‘Disturbing Development’
Grant Fritchey & the DBA Team present the latest instalment of in the Top 5 hard-earned lessons of a DBA – read it now.

In This Issue

DAX Query - Part 2

A more detailed look at the DAX language and some of its more frequently used functions More »


Developing a Report Definition Customization Extension for Multi-Language Reports

When you need to present the same SQL Server Reporting Services (SSRS) report in eight or more different languages, or in different formats for different recipients, and the boss demands last-minute changes, it suddenly makes sense to use the Report Definition Customisation Extension (RDCE) .NET class library to create the final reports automatically. But how? More »


SQL Saturday #212 - Redmond

Join us for a free day of SQL Server training and networking in Redmond, WA More »


From the SQLServerCentral Blogs - A Rickety Stairway to SQL Server Data Mining, Part 10.4: Data Mining Dimensions vs. Drastic Dynamic DMX

by Steve Bolton                 Anyone who’s gotten this far in this series of amateur self-tutorials on SQL Server Data Mining (SSDM)... More »


Editorial - Managing Your IT Data

This editorial was originally published on Jan 20, 2009. It is being re-run as Steve is at SQL Bits.

I'm not sure how I ran across Splunk, but it intrigued me. It's a data management application, similar to something I've thought about building myself (snap fingers, darn, wish I'd done it first) when I was managing a lot of servers.

There's no denying that IT infrastructures are becoming incredbibly complex. Think about some of your mission critical applications and potentially how many different pieces of equipment or software interact to make them work? At Peoplesoft we had a failure one Thanksgiving and as I was listening to people talking about the various things that seemed to be working, I was amazed to find out we had over a dozen different servers all running parts of this one piece of our ERP infrastructure. And that's without counting network devices!

Trying to track down issues becomes hard when you need to correlate the logs from all these devices and match things up by time. Having some type of system that can bring together multiple logs, similar to the Log Viewer in SQL Server 2005 that lets you see multiple logs at one time is important. Splunk seems like a great type of application to help with this.

As I was reading about this product, I was wondering how this data is stored. After all, you'd think that a database would be natural here for indexing this type of material. I thought it likely that MySQL was being used but as I dug in I saw some interesting facts. First splunk stores a compressed copies of the various log data it reads, which can be a lot of data. However they use flat files and then create indexes on those files for quick querying. They say that their index size is 30% of the data size, which is reasonable, but I would have assumed they'd have multiple indexes to handle different queries.

In many ways this seems like a specialized search engine, with bots sent out not to read web pages, but log data. However archiving, scaling out to multiple machines, and helping you to answer questions about your environment are all search engine type features.

I'm still wondering how much data they produce and how easy this is to manage in the real world. I have friends running Openview and Unicenter, and both of those systems are cumbersome and complicated, not to mention expensive. If anyone's using Splunk, I'd be interested to hear how easy it is to use. and how well it manages what must be a tremendous amount of data.

And if anyone is using to solve SQL Server issues or answer BI type questions about their environment, please, write us an article.

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


The Voice of the DBA Podcasts

Everyday Jones

The podcast feeds are now available at sqlservercentral.mevio.com and you can see more great shows there. I've linked the feeds below.

Overall RSS Feed: or now on iTunes!

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

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:

Below are 5 statements, I execute all statements. Which statement(s) execute successfully or give error. Choose your answer below. The answers are listed in the order of the statements.

---- Statement 1
SELECT SYSOBJECTS.Name, SYSOBJECTS.Xtype, SYSOBJECTS.crdate, SYSOBJECTS.refdate
 FROM SYSOBJECTS
GO
---- Statement 2
SELECT Name, Xtype, crdate, refdate
 FROM SYSOBJECTS AS OBJ
GO
---- Statement 3
SELECT OBJ.Name, OBJ.Xtype, OBJ.crdate, OBJ.refdate
 FROM SYSOBJECTS AS OBJ
GO
---- Statement 4
SELECT SYSOBJECTS.Name, SYSOBJECTS.Xtype, SYSOBJECTS.crdate, SYSOBJECTS.refdate
 FROM SYSOBJECTS AS OBJ
GO
---- Statement 5
SELECT SYSOBJECTS.Name, OBJ.Xtype, OBJ.crdate, SYSOBJECTS.refdate
 FROM SYSOBJECTS AS OBJ
 GO 

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

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

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

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

I execute the following T-SQL statements

SET ANSI_WARNINGS on 

DECLARE @S VARCHAR(5)

--I execute Statement #1
SET @S = REPLICATE('1,1, ',12.5)

CREATE TABLE #T(S VARCHAR(10))

INSERT INTO #T

SELECT @S

I then execute Statement #2.

SELECT ISNULL(DATALENGTH(S),0) AS 'DataLength of S in table'
 FROM #T

The questions are: (choose two answers)

  • Does the SET @S (statement #1) return an error message?
  • What DATALENGTH of 'S' is returned (Statement # 2) ?

Answer:

  • The SET @S statment completes without returning an error
  • Statement #2 returns the datalength of 'S' = 5

Explanation: It appears that Microsoft's teams that composed the function, wrote the BOL page, etc, did not cooperate with each other. What more can I say, except when using items new to yourself, test, test and then test again, before placing into production. Definitely an item that should be submitted through CONNECT for correction.

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

» Discuss this question and answer on the forums

SQL Server Transaction Log Management

When a SQL Server database is operating smoothly and performing well, there is no need to be particularly aware of the transaction log, beyond ensuring that every database has an appropriate backup regime and restore plan in place. When things go wrong, however, a DBA's reputation depends on a deeper understanding of the transaction log, both what it does, and how it works. An effective response to a crisis requires rapid decisions based on understanding its role in ensuring data integrity.

Get your copy from Amazon today.


Featured Script

Get IP Addresses of all Hostnames using SQL Server

To get the IP addresses of Multiple hostnames from below stored procedure. 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

SQL SERVER 2005 Updates - If I have SQL SERVER 2005 SP 4 @ build 9.00.5296 dated October 24, 2011 Will I be able to install Microsoft...

SQl server carrier advice - HI, I have 4.5 years of SQL developer.I work with one of the reputed bank in the world. My situation is being...

Linked server for Oracle - Hi, I am having this weird problem with Oracle linked server on SQL 2005 x64. I have installed Oracle client and...

SQL Server 2005 : Business Intelligence

Script Task C# Code(The name 'Dts' does not exist in the current context) - [code="other"] // C# code using System; using System.Data; using System.IO; // Added using Microsoft.SqlServer.Dts.Pipeline; using Microsoft.SqlServer.Dts.Runtime; //[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { string SplitData = Dts.Variables["User::SplitData"].Value.ToString(); public override void

How can I store the username and password without config file - How can I store the username and password not in the config file or SQL Server in SSIS, so that...

SQL Server 2005 : Development

Rows Value into Columns - hi Guys, Need your help to sort out the Query, i have fix columns Field1 to Field10, and Variant Rows...

SQL Server 2005 : SQL Server 2005 General Discussion

Update Join Query Help!!!! - hi Guys I need help with a simple update join query that i need to do. below are my example of...

SQL Server 2005 : SS2K5 Replication

replication design advice - Hello Folks, we are planning to replicate database for reporting purpose and we need to restrict data based on companyID' we are...

SQL Server 2005 : SQL Server Express

sql server 2005?????? - hello, i have big problems using sql server 2005 with access 2010, actually the data base was attached and detached from...

SQL Server 2005 : T-SQL (SS2K5)

Need help in Pivoting the table. - Hi, I need help on Pivoting a table. I have to do it in the back end only. Here is the table...

SUM CASE, COLUMN MINUS COLUMN - I have this code working fine. I need to subtract one column from another to get the corrected results. I...

Droping out of a cusor - Hi what’s the best to way to drop out of a cursor if a condition is meet? Many thanks

Help with sql statement to delete old records for user if multiple records found - Here is how the data looks like for UserSession Table UserName LastUpdated sessionID --------- ------------- --------- User1 1/1/2013 10AM SESS01 User1 1/1/2013 11AM SESS02 User1 1/1/2013...

cannot insert duplicate key row in object 'tablename' with unique index 'indexname' - Hi, I have an MS Access application whose tables are linked via ODBC connection to SQL server 2005. In one of...

SQL Server 2005 : SQL Server Newbies

SQL inner join: ORA-00904: "Bonus.site_name": invalid identifier? - SELECT * FROM "Poker_Site" INNER JOIN "Bonus" ON "Poker_Site.site_name" = "Bonus.site_name"; Whats wrong with this select query/ I have tried removing the quotes. Bonus bonus_name NVARCHAR2 200 - - No sign_up_bouns NVARCHAR2 100 ...

Generate two columns from single column with alternating data - I have what I thought would be a simple PIVOT example, but I have not yet solved it without using...

SQL Server 7,2000 : Administration

adding a 2nd Tempdb file. - Hi I am considering adding a secondary tempdb file to my datawarehouse to hopefully improve performance. I am aware that I...

Need Help Stopping a Rouge Extended Procedure (XPSMTP) - I'm experiencing a very unusual problem with one of our old SQL 2000 installs. Backstory: we've used xpsmtp80.dll on all...

SQL Server 2008 : SQL Server 2008 - General

SQL Server 2008 R2 slow query performance to Oracle 11g using Openquery - Gang, I'm completely perplexed on this one. I am running a query against an Oracle 11g database. It looks something like...

SQL Server 2008 cluster node going down unexpectedly - Last night our primary SQL Server node went down and failed over to the secondary node. I was actually on the...

TFS Upgrade from 2005 to 2010 - Hi, recently I have been assigned to upgrade our TFS 2005 to TFS 2010. Here is what we are looking...

Percent to Total - Customer Sales Penetration - This is related to a previous post, but I didn't have all the information. Now, I'm including the selects to...

Lazy Writes / sec - on one of my servers i checked the Lazy Writes / sec and i get a very very high value of...

Sql server 2008 installation problem( must specify valid information for parsing the string) - Hello, When i am trying to install sql server 2008 using iso file , it shows an error message " must specify valid...

Tracking running queries - Hi all In one of the daily emails (quite a while ago), there was a method for getting server performance and...

count(*) vs count(1) or count(id) - Dear Experts, I want to know what is difference between count(*), count(id) and count(1) base on performance issues. We have some quires...

display specific string and that positions in sql server - hi friend i have small doubt in sql server plz tell me how to solve this i want display only specific...

why there are no sql server services listed in configuration manager - In installed another named instance of same edition and version sql server 2008 r2 on a server. Now i can t...

Ports and Firewall changes for named Instance - Hi All, What all ports should be opened for a named instance, which is listeneing on 1477. The named instance name is...

Are my statistics being used? - Hi, I have a system with a lot of blocking. From the last occurrence I found a statistics update causing a...

Trouble with SQL Server Agent 2008 - Hello all. This is on a Windows 7 Pro machine. As I mentioned in an earlier post today, I am...

Blocking - Hi, We are running one job which runs SELECT query from a view from another linked server. We are getting blocking...

Restoring SQl SERVER 2008 R2 - Hi Guys: Any guide how to restore sql server 2008 r2 backup on sql server 2000 thanx symun

SSIS - Excel 2010 - Provider=Microsoft.ACE.OLEDB.12.0 - I am requesting that the Microsoft Access Database Engine 2010 Redistributable be installed on my SQL 2008R2 Enterprise SP1 running...

Discriminator Field - Hi could anyone tell me whya discriminator column would be added to a table? what is a discriminator column? what is...

"SET STATISTICS TIME" and "SET STATISTICS IO" questions - 1) As I understood from here [url=http://www.sqlservercentral.com/articles/Performance+Tuning/measuringperformance/1323/][/url] "elapsed time" is the total time of statement's execution, but how than it can be...

Using MERGE tsql for FACT loading - Hi All, I m using the below tsql MERGE statement for loading data to one of the fact table in DWH...

Rebuild or DBREINDEX - Hi I have a table with a clustered index and 6 non clustered indexes.The size of the table is 400G...

Disk_Queue_Length is CRITICAL or high - This is regarding the Cluster servers 723/722/721 3 Nodes (Active/Active/Passive). At around Fri Mar 29 11:05:42 EDT 2013, We have...

Create SSIS package in SQL SERVER 2012 for SQL SERVER 2008 - Is it possible to develop SSIS packages in SQL SERVER 20012 for deploying in SLQ SERVER 2008?

SQL Server 2008 : T-SQL (SS2K8)

Combine date from two different date ranges in one dataset - Hello all, I have the following dataset that I am using: select HourOfTheDay ,sum(NumberOfOrdersInBatch) as NumberOfOrders ,AVG(TimeToProcess) as AverageTime from info.batchstatsTEST with (nolock) where TimeReceived between GETDATE()-1...

Hot to Get Cumulative Total.. - Hi Friends... I Having Table For The Following Structure.. [code="sql"] Create Table EmpProcessDetail ( EmpId varchar(25), EmpName varchar(50),ProcessName varchar(30),GroupCode varchar(5),PlannedDays int,ActualDays int, DelayedDays int,TotalDelayDays...

update record from another record in same table - Hi, I'm looking for the best way to update pkid 1 = pkid 2 update testTable set a.fname = b.fname from (select * from testTable where pkid...

Top 2 On Child Table - I have two tables, one Master, one Detail. I'm trying to build a query to get the top 2 products...

Add 2 values from 1 table - Morning all Good someone offer the best way to do this. I want to add 2 values from 1 table. Example: Select count (*) as...

is it possible to union all two CTEs? - I have two CTEs sharing same column names and I need to combine their results into one result set. Given...

varchar to datetime convertion challenge - I have a table w/a varchar(10) column (MoYear) which has the month and year with '/' between (i.e. 01/2013). My task...

How to pass where condition as parameter of stored procedure - How to pass where condition as parameter of stored procedure

T-SQL Greatest function workarounds - timing tests - EDIT: About four posts down is an expanded test script that if you get the chance to run for timing...

XML Question (?) - Hi, Let's say I have the following table with just 1 record: SELECT Field1, Field2, Field3 FROM tbl returns 11, 22, 33 What would...

problem with join T-Sql - i have customer table and it have 1 foreign key from other table in it then i have service table...

Comparing figures based on relative date - Hi All I'm having trouble working out a solution to the following issue: I have a table containing a date and a...

SQL Server 2008 : SQL Server Newbies

Sql Server Stored Procedure varaible declaration - i want to search data with different criteria. one is from "date" and other is from itemid and so on. in last...

Stored procedure to transfer Applications into Loans mismatching number of BEGIN and COMMIT statementserror: - I had a problem when transfering an active application and to a loan table where i have to calculate the...

Query for non-existent entries - Hi, I'd like to build a query that produces the missing entries in a scores per week table. Say that you...

Exclude rows with blanks in a particular field - I know this is a popular one and I've Googled it but I haven't found anything that has worked yet,...

Random Number Generator - I am trying to create a random number generator that would populate a number between 8-48 to 9 different lanes...

inherited an app, Error started the next day - Hi, First post, I've been using SQL for about 3 years, I just changed jobs and inherited a bunch of...

SQL Server 2008 : Security (SS2K8)

SQL Server 2008 Audit_Amazing_Expecting Comments - We enabled SQL Server external audit feature in our production server. We enabled only DML(Insert,Update,Delete) single audit event instead of...

SQL Server 2008 : SQL Server 2008 High Availability

database mirroring witness and client redirect - We are investigating using database mirroring as a DR solution for sharepoint 2010. the sharepoint documentation states that high availability...

Cluster configuration corrupted - We have SQL Server 2008 SP1 running a [b]single[/b] node cluster SQL 2008 R2 SP2. We've had multiple attempts to...

Mirroring with replication question - Before I watched CBT Nuggets dicussing replication & mirroring. In it he described the following scenario (pictured below as I drew...

cluster validation test failing while adding node to the existing cluster group - cluster validation test failing at Validate SCSI Device Vital Product Data (VPD) test. There's a hot fix to resolve this...

Can't start mirroring that was previously disabled. - sqlserver 2008 R2. The error is: TITLE: Database Properties ------------------------------ An error occurred while starting mirroring. ------------------------------ ADDITIONAL INFORMATION: Alter failed for Database 'FASTContent_CrawlStoreDB_2d3e9be9ef044393a736f16d569f0952'. (Microsoft.SqlServer.Smo) For help, click:...

SQL Service account change - Hi, Log shipping (backup job) failed after we change SQL service account in our production database and DR database server. Earlier...

Restoring System Databases - Hi , Im using an Active/Passive SQL Cluster 2008 R2 Standard Edition. In my task to relocate the System Databases, I messed...

SQL Server 2008 : SQL Server 2008 Administration

Can resource governor apply based on what DB is being accessed? - Sadly, I don't have a "play" environment at work anymore, and forgot to putz with this myself at home, so...

Proper way to move data in large filegroup using DBCC SHRINKFILE emptyfile option - Hi, I have a filegroup with one large datafile that is 300GB and I want to create 10- 30GB files and...

Help with a Deadlock Issue - Recently we have started seeing some deadlocks reported on a database instance. I have enabled the trace -T1222 and am...

SSMS very laggy when typing a query - This is a strange one. I am typing a query in SQL Server Management Studio. Nothing is executing on the...

MDW and upload schedules from different instances - I have recently set up MDW across our SQL estate. There are approximately 40 instances being monitored all sending their...

Getting bizarre error while installing SQL 2008 SP2 on standalone server - While trying to upgrade a SQL-2008 R2 installation to SQL 2008 SP2 I'm getting the following error in the rule-checking...

Programming : General

Auto Recovery Information in SSMS - I know this might be a simple question but I have not been able to find my answer as of...

Updating information in a datagrid by using a button - Hi, Another C# question here. What I am trying to do is update information within a datagrid that could contain multiple...

Programming : XML

Representing choice of elements using FOR XML EXPLICIT - Hi, I have a complicated set of schema files for which I am trying to generate a single XML file. The...

New to XML....??? - Hi, I',m new to XML and here is what I need to do... I want to create a SQL query to pull...

SQLServerCentral.com : Anything that is NOT about SQL!

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

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

SQLServerCentral.com : SQLServerCentral.com Website Issues

feature request: social signal - I don't want to add "+1" -style comments to a discussion because they really junk-up a thread. It would also...

Reporting Services : Reporting Services

Can I schedule a report to run via SQL Job Agent? - Hi I was wondering if I can schedule a report to be emailed via the job agent? I know I can Schedule...

one parameter returning all,remining parameters also need to return ALL - i have 6 parameters ,in this 4th parameter returns "ALL" 5th and 6th also in need to return all 5,6 have...

Reporting Services : Reporting Services 2005 Administration

SSRS 2005 on Windows 7 - "Unable to connect to the remote server" - I have installed SQL Server Standard Edition (x64) on a Windows 7 x64 OS I ensured that all the IIS 6...

Database Design : Design Ideas and Questions

Using Schema for Logical Organization - I was just wondering if anyone out there uses SQL Server schemas for logical organization, instead of security purposes. I'm...

Complex many to many relationships - Hi all, Here is a relatively complex design that I would like to have vetted by experts! :) Say that you need...

Data Warehousing : Integration Services

Execute Package Task or SQL Server Agent - I have inherited an SSIS package that has over a dozen Execute Package Task controls in it. They are all...

Forcing SSIS to use iexplorer web proxy - Hey. As the subject says, is there anyway to force the default connection method in SSIS to use the proxy...

Extract Speed from DB2 - Hello again all, I am back again. This time with a new company, new city, and new role! I am now in...

Data Warehousing : Strategies and Ideas

Data Warehouse Models - Someone from my company's strategy has said that the Data Warehouse model needs to be: - Atomic - 3rd NF - Relational But I...

Data Warehousing : Analysis Services

How to Release disk space after a cube processing failure - Hi, I have a SQLServer Data base stored on a drive that has abot 10 GB Free space. How ever the...

converting oltp database into dimension model (olap) database - I work on ssas using adventureworksDW2008 database for creting cube and understanding other features but if I want to convert...

downgrade SSAS 2012 cubes to 2008 for Cognos Trial - Hi Experts, I have installed the Cognos 10.1 trial version. But it doesn't support SSAS 2012 cube. I wanted to know whether...

using mdx query - When working with SSas after creating cube you see the data as measures and dimensions.But when does MDX comes in...