In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Data Compare Save time when comparing and synchronizing database contents
"It has also streamlined our daily update process and cut back literally a good solid hour per day." George Pantela, GPAnalysis.com. Download a free trial of SQL Data Compare now.
 
SQL DBA Bundle Top 5 Hard-earned Lessons of a DBA
New! Read Lesson 3, ‘Six Scary SQL Surprises’, and learn from Brent Ozar and the other experts in the DBA Team. Read now.
 
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.

In This Issue

Trigger an Email of an SSRS Report from an SSIS Package

This article demonstrates how to trigger an SSRS subscription to email a report from an Execute SQL Task using SQL Server Agent and the ReportServer.dbo.AddEvent stored procedure. More »


Accounting for Windows Azure

On Thursday March 21st 12PM noon Central, Steve Hughes will discuss Accounting for Windows Azure when looking at Data Architecture.  More »


Full Text Searches on Documents in FileTables

SQL Server's FileTable technology is an intriguing way of accomodating file-based text data in a database, and allowing for complex searches. The best way of learning FileTable is to try it out and experiment. Feodor shows how to set it up, add some sample data and set up full-text search. More »


From the SQLServerCentral Blogs - Do You Know sys.dm_exec_text_query_plan?

One of the most fundamental DMF’s a SQL Server DBA should know is sys.dm_exec_query_plan. This Dynamic Management Function allows us to see... More »


From the SQLServerCentral Blogs - Apply CU2 for SQL2012 SP1 – Fix for Windows Installer starting repeatedly and causing CPU spikes !

CU2 for SQLServer 2012 SP1 is one key update which you should review and deploy if found necessary. I have... More »


Editorial - Serious Storage

Years ago I worked for a company that had a Novell network. We had a multi-server environment with lots of users and were having issues with both space and users. We bought a Netframe server, packed with 350MB drives and a limited edition 1000 user version of Netware v3.11. This was also the time when I got to use my C-language experience, writing a login utility that would handle our user IDs above 250 on Netware since that was the limit for all of our other servers.

That server, which cost something like $280,000 in 1991 was the biggest one on our network, with something like 8GB of storage. That seems like a pittance today, especially compared with the sale EMC just made. The Vatican is getting 2.8PB of storage from EMC for its library. EMC is also providing consulting services to digitize some of the historic manuscripts and documents that have been deteriorating from user and handling.  It's an ambitious 9-year project, of which this is just the first 3 years.

That's a serious amount of storage, and while most of it will be used for raw, unstructured storage of images, some will have to house a database. There will be the equally critical part of cataloging and organizing the meta data about these documents into some type of database. I don't know if this will be a relational or some other store, but without some database that keeps track of what each image represents and how to retrieve it, it's entirely possible that these documents might get lost, in the same manner they may be lost in physical storage today.

The scale of this project somewhat astounds me. Going from MB and GB to thinking about PB and EB is something many of us will deal with over the next decade as our organizations gather, store, and manage more and more data. Perhaps a few of us will get to work on some interesting projects like this one that look to preserve valuable knowledge from our past.

» 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. Comments are definitely appreciated and wanted, and you can get feeds from there. 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.

You can also follow Steve Jones on Twitter:

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


Question of the Day

Today's Question:

You are working with an American English SQL Server instance. The full text search thesaurus file needs to be enhanced to include additional terms for your application. You edit the tseng.xml file, remove the comments, include your additions and save it. You reload the thesaurus in SQL Server, but your queries do not seem to be using the thesaurus file.

What could be the problem?

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

This question is worth 1 point in this category: full text search. 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.

 Inside the SQL Server Query Optimizer

This book will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way. Grab your copy today from Amazon!


Yesterday's Question of the Day

--I have the following table

CREATE TABLE #T(ID INT,[Year] VARCHAR(4),M VARCHAR(2),D VARCHAR(2),H VARCHAR(2),Mi VARCHAR(2),S VARCHAR(2),MS VARCHAR(3))

Various clerical personnel have entered into table #T the following data.

INSERT INTO #T 
SELECT 1,'2013','1','22','11','11','11','995' UNION ALL
SELECT 2,'2013','1','22','11','11','11','992' UNION ALL
SELECT 3,'2013', '1', '31', '15', '34', '21', '003' 

To verify the input data I then created the following T-SQL statements.

DECLARE @Y VARCHAR(4),@M VARCHAR(2),
        @D VARCHAR(2),@H VARCHAR(2),
        @Mi VARCHAR(2),@S VARCHAR(2),@Ms VARCHAR(4),@IC INT 

SET @IC = 1

WHILE @IC <= (SELECT MAX(ID) FROM #T)
 BEGIN
  SET @Y = (SELECT [Year] FROM #T WHERE ID = @IC)
  SET @M = (SELECT M FROM #T WHERE ID = @IC)
  SET @D = (SELECT D FROM #T WHERE ID = @IC)
  SET @H = (SELECT H FROM #T WHERE ID = @IC)
  SET @Mi = (SELECT Mi FROM #T WHERE ID = @IC)
  SET @S = (SELECT S FROM #T WHERE ID = @IC)
  SET @Ms = (SELECT MS FROM #T WHERE ID =@IC)
  SELECT @IC + 3 AS 'Select number', DATETIMEFROMPARTS (@Y,@M,@D,@H,@Mi,@S,@Ms) AS 'Datetime'
  ,CAST(@S AS CHAR(2)) + ':' + CAST(@Ms AS CHAR(3)) AS 'Original seconds input'   SET @IC = @IC + 1 
END

The question is: which select number returned the datetime value with the same number of seconds as was input. The (same number of seconds) is expressed as 59:005 and this is NOT the correct answer.

Answer: Select number 6

Explanation: Accuracy - Rounded to increments of .000, .003, or .007 seconds, so SELECT 6 returns the same numberof seconds.

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

» Discuss this question and answer on the forums

 Inside the SQL Server Query Optimizer

This book will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way. Grab your copy today from Amazon!


Featured Script

Get List of tables with no identity columns

Get list of tables with no identity fields 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

maxdop doesn't take effect? - I have a SQL 2008R2 server with 16 cores. At the server level i have CTP set to 20 and...

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

Filegroups - Hi guys I have a problem, i'd created a new file group and i wanted to transfer some tables to the...

SQL Server 2005 : Business Intelligence

Synchronize Analysis Services Databases - When I try to synchronize 2 db i have an tcp error transportation. I'd like to know if it's necessary to...

SQL Server 2005 : SQL Server 2005 General Discussion

DataTransfer - Hi All, I have 13 databases say portfolio databases named (a, b, c, d,....m) I need to transfer few tables(17 tables) from...

Conflicting Version number and Build Number on @@Version - Morning everyone. I installed 2005 (X64) SP3 & 4 on a server over the weekend. An odd thing came up when I...

SQL Server 2005 : SQL Server Express

BCP error 'Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file' - The code is designed to geneate a csv file from a query using BCP. It works okay when run against...

SQL Server 2005 : SQL Server 2005 Performance Tuning

sql pages out before hitting low memory - What can I do to prevent SQL from paging out its memory? Situation: I receive calls about SQL App being slow. event viewer...

Can Someone Explain These Wait Times to Me? - So I know what wait times are, the amount of time SQL spends waiting on a resource. What I don't...

SQL Server 2005 : SQL Server 2005 Integration Services

General SSIS package to export any table into .csv - Hi guys, I need to create an SSIS package that takes a table name as a parameter and exports out its...

Access Schemas - I was recently assigned a task of dynamically extracting Access schemas...How do i approach using SSIS..Any thoughts or suggestions would...

SSIS - Package Configurations - Store Client Certificate in SQL Server Table for HTTP Connection Manager -> CLIENT CERTIFICATE - Dear All, Hi! I have a requirement to download Access file from HTTPS server using Client Certificate. To achieve the...

SSIS & Environment Variables - I've been struggling w/ some quirky issues lately regarding use of environment variables with package configurations in my SSIS packages. When...

error 0x80040E21 "Multiple-step OLE DB operation generated errors...." - I've used the Import / Export wizard to dump a tables from a SQL 2005 db to a series of Excel...

SQL Server 2005 : SQL Server Newbies

Barcodes Code128 generator function - I'm looking for a [b]barcode generating Function[/b] (in SQL 2005), that uses the standard [u]Code128[/u]. The result of calling the...

SQL Server 7,2000 : Administration

CAST CONVERT doesn't work - Hello everybody I have th following CAST Convert statement: SELECT CAST(CONVERT(CHAR(8), EZACDT) AS DATE) AS Accounting_Date FROM SYM_SPT_SNI_01.dbo.DBA_MVX_CINACC00 WHERE EZCONO = 2 AND EZDIVI = '001' And get...

SQL Server 2008 : SQL Server 2008 - General

deadlock graph no statement information - In a deadlock graph, hovering over the oval for the victim only shows "Statement:". The other oval shows Statement: followed...

BombProof Data Import from Excel ? - I have a very large spreadsheet of 125,000 rows, 200 columns, some of which contain a lot of text. I...

Static port on named instance in a cluster. - I have a two node SQL cluster (2008R2). Each node on the cluster hosts a named instance. I am having...

sql agent job output file to different server has error - I have a SQL agent job that selects from a table and using the advanced options I can send the...

Help with XML Explicit and nesting - Hello, I currently have a very basic SQL statement to dump my results out in XML format: SELECT 1 AS tag, NULL...

Differential backups being blocked, CHECKPOINT command - All my differential backups are being blocked by a SPID which has a command of CHECKPOINT. Is this something to be...

Trace - to Capture all or most queries - A. Are black box and defaula traces same? B. Is it possible to capture all the queires in the black/default trace,...

Print error message - Hi, here is the script below: In this script I am matching voucher type and according to that I am inserting...

Alter numeric(12,6) to numeric(19,4) - is it metadata only or is it recreate table? - Would be most grateful for confirmation/infirmation of theory below: Changing an existing table column from NUMERIC (12,6) to NUMERIC (19,4) should...

Full Text Search with multiple keyword or Text Phrase - Hi All, Recently, we had a requirement to implement keyword based search & user can enter only one "word" means one keyword....

Top N makes query run faster - I have a select query which brings back 35k rows. it runs in approx 5 seconds. I moved this to a...

How to Zip a folder with files using Execute process task IN ssis - Hi, i need to zip a file in a folder using SSIS , so i tried with "Execute Process Task". I have a...

SQL Server Database Error - Hi Experts, Here is an error that has just posted in the system center operation manager(SCOM). Event ID: 9511 from Source Office...

First and Last number - I have table in which i have data like - Columns are VoucherNo and TransactionID PD0000021 TRN0000003 PD0000022 TRN0000003 PD0000023 TRN0000003 PD0000024 TRN0000003 PD0000025 TRN0000003 PD0000026 TRN0000003 PD0000027 TRN0000003 PD0000028 TRN0000003 PD0000029 TRN0000003 PD0000030 TRN0000003 VB0000001 TRN0000001 VB0000002 TRN0000001 VB0000003 TRN0000001 VB0000004 TRN0000001 VB0000005

how to change update excel file sheet name by using SQL Query - Dear all, Hope good all are i have a small requirement.. actually i have excel file dail i am opening the file and...

Insertion in table - HI, This is the table structure and script below: CREATE TABLE [dbo].[GV_Booklet]( [BookletId] [int] IDENTITY(1,1) NOT NULL, [LeafCount] [nchar](10) NULL, [Denomination] [int] NULL, [VoucherTypeId] [int] NOT...

Best Approach to Archieve DB - Example - We have a DB which have loads of data & we want to archiev it to some Server in such...

Finding Comma in the string - Guys, Declare @string VARCHAR(MAX) SET @string IN ('sql','server','2008') Here, How to find a comma in the above string? whether comma(,) exists or...

SQL architecture - Can any one explain the SQL architecture

Transact replication on two computers - Hello I am trying create transactional replication with updatable with subscriptions on two PC ( between instances database is OK ). On PC1...

problem in execute the stored procedure - hi i craete a SP in my database . by this command : USE [MyDatabase]; GO CREATE PROCEDURE test AS SET NOCOUNT ON; SELECT id, feed1,...

Read Committed_Snapshot - "Read Committed_Snapshot" isolation level in sql has any negatives or disadvantages?

how to combine and convert two integer columns to datetime - I have date stored in a column as integer type and time stored in a column as integer type. How...

Database name is visible but not available. - When I reboot my server and log into SSMS my database is listed but it cannot be expanded. The table...

SQL 2008 - Min and Max server memory - For SQL 2008, the min and max server memory settings are limited by total memory. If my server has 4gb...

can we install 2008 R2 integration services and 2012 integration services on the same server - Hi DBA'S I had 2012 integration services installed on a server. i am trying to connect to Oracle but it is...

How to add below complex conditions in SQL script.. - [size="2"][font="Courier New"]Hi All, Below is my written query DECLARE @FromDate DATETIME DECLARE @EndDate DATETIME SET @FromDate = '2013-01-01 00:00:00.000' SET @EndDate = '2013-02-13 00:00:00.000' SELECT DISTINCT year(sd.FKDAT) As YEARWISE_DATA, so.vkbur...

Why is "instead of delete" trigger not fired by delete inside "after update" trigger - One of my tables has both an "after update" and an "instead of delete" trigger. When I perform a delete...

query help - Hi All, can any one help on below query declare @tbl table (TBLID int identity(1,1),ID int,patientName varchar(10),age int,city varchar(100),Mobile int) insert @tbl(ID,patientName,age,city,Mobile)...

sp_updatestats and the default sampling rate - Using SQL Server 2008R2 As my Production database approaching 500GB and potentially can grow up to 1TB in 2 years, issue...

Primary key or not to primary key, that is the question - Yeah, bad taste on the subject but nonetheless, you're reading my post :). I'd like to pose a scenario and see...

Few tricky Questions about SQL Server 2008 that I came across... - Few Questions about SQL Server 2008 that I came across. [b]Q: Which of the following allows for the truncation of...

SQL Server 2008 : T-SQL (SS2K8)

How can I create intervals fro start-dates only - Let's assume we have 1) A key value, like a product number 2) and a ValidFrom date, like this MyProductKey, 20100111 MyProductKey, 20110205 MyProductKey, 20120101 What...

Ranking functions, views, and predicates - So, I ran into a nifty little "bug" I'll call it. From what I can find this was an issue...

BCP Error : Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.EXTGEN_InitSessionContextSp'. - DECLARE @Cmd VarChar(4000) DECLARE @SProcName VarChar(40) DECLARE @SP_Parm1Value VarChar(40) DECLARE @SP_Parm2Value VarChar(4000) set @SProcName = 'Rpt_JobTransactionsSp' set @SP_Parm2Value = '|SRMQDIC|,|ROD|,|B|,|HNS|,|0|,|B|,null,null,null,null,|000|,|9999|,|1/1/2013|,|3/17/2013|,null,null,null,null,null,null,null,null,|T|,null,null,|1|,|0|,|1033|' set @SP_Parm2Val

outputting the Stored Proc output to Text File Pipe (|) Seperated with Headers - Command to run Exec Rpt_OrderReport '1/1/2013' , '1/31/2013' Need a code which will save the output to Text file with headers and column...

For Learning - I want to improve my DB Knowledge , i have experience in MS-SQL and ORACLE. My Problem is when i face any...

Sql Query - I have to return one result set by writing two different logic. Both of the logic are very complex.. one...

Stop large queries from running - Hi, I have a query window with a lot of code but I do not want to run it entirely in...

Pivot Query with Count - Hi there I am fairly new to SQL Server TSQL and I have a strong MS Access background. I can't get my...

problem in select command - hi please look at this select command : [Code][b]select top 2 * from dbo.NFFeeds order by id desc [/b][/code] for my database the...

Custom sp_who - Hello All, Couldn't find anything that addresses this specifically, so here goes. I have a group that I want to allow to...

SQL Server 2008 : SQL Server Newbies

a query like factorial - Hi. I ask you for help because its more than 2 days im thinking about this question. Imagine we have a table...

Error "Must declare scalar variable ..." when using cursor - Hi, Hope you can help me. Every week KPI's for our HR-dept. have to be generated. The problem with this is that the...

Obtaining Before & After Record & Missing Record. - I have a database that has two tables. On table is a date table that has all of the possible...

Do Users based on logins in master have access to other databases? - When I create a user and select master as the default database does that mean or imply that user can...

Alpha character in SPID? Disk I\O Bottleneck Troubleshooting. - Hi. OS is Server 2003 R2 Enterprise Service Pack 2. SQL is Microsoft SQL Server 2005 - 9.00.3353.00. I was tasked with troubleshooting the...

SQL Server 2008 : SQL Server 2008 High Availability

Cluster setup--system databases - We created a single node cluster and installed sql server failover cluster on it. Plan is to move LUNS and...

Mirror consideration - Hi, We are using mirroring as high available technology. I read a lot about mirroring considerations, but can't find easily considerations...

Sql Server 2008 r2 active/active configuration - Hello guys, Please can anybody point in right direction of implementing sql server failover clustering in active/active mode. There are alot of...

SQL Server 2008 : SQL Server 2008 Administration

Automate Data Compression - I would like to automate the compression job for every week end by filtering on newly created tables/indexes with more...

Database restoration to network mapped path.. - As i Don't have enough Space in my server machine.Is it possible to restore DB to a network mapped path?...

Log shipping in sql server 2000 - Hi all I have one doubt about log shipping here we have two servers one is sql server 2000 and another...

can not find Model after Master restore - I was doing some testing to ensure I had all the version build files required to restore the Master database...

Grant persmissions to database - We have 5 small in-house developed applications whose databases are hosted on a SQL server. For each application we have an...

Problem with Database mail - We have SQL SERVER 2008 R2,which Database mail does not work properly.When I try to send test mail,it says it...

Rebuild system database failure - I have a situation where all system database (.mdf, .ldf) were wiped out after system recovery from a major outage....

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

Reporting Services : Reporting Services

Changing Field Value With Parameter - Hi, I'd like to change a field value in my main data source query using a parameter. I cannot figure this...

Problem accessing SRSS Website by Name from the SRSS Server - I am having a very strange problem when I am logged into a particular SRSS Server that is hosting a...

Setting ToDate parameter - Hi All, Quick question, I'm looking to set my 'ToDate' parameter to the last day of the month up to...

Report runs no data on client but shows on server also downloads from client - Hello, I just installed SSRS on an existing SQL Server 2012 server. I created a simple test report that runs and...

SSRS Drill through - Hi , My question may be a bit simplistic,but can i drill through from one excel sheet to another? I have researched...

Best Practice: Custom Front End for SSRS - Hi All, I am from a small software company that sells products exclusively for the mining industry. We have decided as...

SSRS Email Subscription having Issue. - Hi All, I am using SQL Server 2012 Reporting Service. I have 5 Reports those need to deliver on email with...

Divide by Zero frustration - It doesn't seem to matter how I word this my Expression, I keep getting the following error: "rsRuntimeErrorInExpression The value...

Default Values for parameters resetting - After abandoning my struggle to solve the Culture info problem I was having with dates, I decided to create the...

Reporting Services : Reporting Services 2005 Development

Special Characters - HI All, Can anyone help me in skipping the special characters in the Result set. column value For ex. If the actual...

Subreport with uniqueidentifier or guid parameter - Hi, I am adding a subreport to a report and I need to pass a uniqueidentifier or Guid to the subreport....

Possible to add in column from another data source in the same report? - Hi all, A burning qns. Currently my report displays 7 column from a datasource A. Is it possible to add the...

Database Design : Design Ideas and Questions

Building my first data warehouse. Advice welcomed :) - Hi all, I've been brought on board to help a company start to build their first business intelligence setup. I'm a...

Data Warehousing : Integration Services

Share SSIS package with sister office - What is the best way to share an SSIS package? I have a sister office who receives all the same...

Special characters in SSIS - Hi, I have an issue with the loading of our data warehouse to do with special characters. The source seems to replacing...

running SSIS packages in msdb with SQL Agent on SQL Server 2008 R2 - Hello, I am quite new to SSIS, and I've run into a problem deploying a set of packages on SQL...

Only one column not extracting from Excel source - I am trying to extract the data out of an excel file with 3 columns Success, Failure and Waiting. The...

Conditional Lookup - Currently working on a project for a client which involves me building a matching solution in SSIS. One of the...

Data Warehousing : Analysis Services

dates and columns - hi, i have another question (or two) :) 1) i want the mdx to always return the data from yesterday without choosing...

trying to make my first report and it is not going :( - Hi, I am new here, I have a question, unfortunately every time I solve it on my own I keep mixing...

MDX simple concept question - As near as I can tell from documentation, the Descendants() function returns a set. This returns a set of 1,500+ product...

@CURRMBRRANGE to MDX query - Hi , Please help me out to get the Proper MDX query .. Below is my Essbase formula need to be converted...