In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Monitor Optimize SQL Server performance
“With SQL Monitor, we can be proactive in our optimization process, instead of waiting until a customer reports a problem,” John Trumbul, Sr. Software Engineer. Optimize your servers with a free trial.
 
SQL Prompt Make working with SQL a breeze
SQL Prompt 5 is the effortless way to write, edit, and explore SQL. It's packed with features such as code completion, script summaries, and SQL reformatting, that make working with SQL a breeze. Try it now.
 
SQL Backup Free eBook! SQL Server Transaction Log Management
Understanding how log files work can make all the difference in a crisis. Try SQL Backup Pro to put the book's advice into practice. Download your free resources now.

In This Issue

A Quick Tour of the Performance Dashboard Reports

SQL Server Performance Dashboard comprises a set of custom reports that give you nitty gritty details about the performance of your SQL Server instance. More »


SQL Server 2012 Integration Services - Using PowerShell to Configure Project Environments

Continuing our discussion on how to leverage the capabilities of PowerShell to automate the most basic SSIS management tasks, this article will explore more complex topics by demonstrating the use of PowerShell in implementing and utilizing project environments. More »


From the SQLServerCentral Blogs - SSAS Cube Backup strategies and Step by Step approach to setup and configure backup

There are different ways to take Cube database backups. SQL Agent JobXMLA script for backupROBOT jobASCMD commandSSIS PackageSSIS packageAMO (Analysis Management... More »


Editorial - The Watson Service

The Watson project from IBM continues to grow and expand in new ways tha twe might never have thought of a few years ago. After winning Jeopardy, the compuer software moved into the medical fields and was sent to college last year. However now Watson is coming to the masses, or at least a portion of them. The Watson Engagement Advisor is being put to the test as a customer service worker. From banks to cell phone providers to insurance companies, the Watson service is poised to advise and interact with consumers by providing advice, troubleshooting and more.

Perhaps the age of the machines is really upon us. If the Watson service can do as good a job as humans, on average, then it becomes a matter of time before many companies decide it's more efficient to replace large numbers of their human staff with the service. As the text to speech engines improve and computer power grows, we might not be able to distinguish these machines from many of humans they replace.

I don't know if I think this is good or bad, but it's likely to come true in some sectors. While Watson may not have the creativity or flexibility of humans, it also won't have the memory lapses, bad days, and inherent prejudices of people. I suspect some people will have poor experiences with the service, but most will not and at some point it will becomes more likely to interact with a Watson-like service than a human for many service oriented tasks.

I can even see that affecting us in technology. How long before Microsoft has a similar service that might troubleshoot issues with products, perhaps even working with developers to find bugs and workarounds. I'd imagine that this service would be a much better way to actually triage and route issues to Microsoft developers than the Connect system.

I don't know that such a system would replace many developers, but I suspect it could easily learn over time to give you advice, such as which SSIS component might best improve a particular workflow. From there it might not be a large leap to have many CRUD applications generated by an interactive Watson programmer. I'm not sure what would be more scary then. That the machines will take some of our jobs, or that they have the intelligence to do so.

» 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:

What is output of these queries?

declare @test table (Value varchar(50));
declare @date date ='10-10-2012'
declare @t1 varchar(10)

set @date='10-10-2012'

insert into @test values ('10'), ( @date) ----cast as varchar
 select * from @test
go
declare @test table (Value varchar(50));
declare @date date ='10-10-2012'
declare @t1 varchar(10)

set @date='10-10-2012'

insert into @test
 select '10'
 union
 select @date --cast as varchar

select * from @test
go
declare @test table (Value varchar(50));
declare @date date ='10-10-2012'
declare @t1 varchar(10)

set @date='10-10-2012'

insert into @test values('10')
insert into @test values(@date)

select * from @test
go

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

This question is worth 1 point in this category: Dates and conversion. 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.

Expert Performance Indexing for SQL Server 2012

Expert Performance Indexing for SQL Server 2012 is a deep dive into perhaps the single-most important facet of good performance: indexes, and how to best use them. The book begins in the shallow waters with explanations of the types of indexes and how they are stored in databases. Moving deeper into the topic, and further into the book, you will look at the statistics that are accumulated both by indexes and on indexes. All of this will help you progress towards properly achieving your database performance goals.

Get your copy from Amazon today.


Yesterday's Question of the Day

Suppose I am upgrading SQLServer 2008 R2 to SQLServer 2012.In need to identify the deprecated features that will not be supported in feature releases. What are the event classes to identity the deprecated features.

Answer:

  • Deprecation Final Support
  • Deprecation Announcement

Explanation: We use SQL Trace for identifying the deprecated features. Under the Event tab, check "Show All Events" (checkbox). Select Deprecation. Under this there are two features

  • Deprecation Final Support
  • Deprecation Announcement

EX: trace looks like this

Deprecation Final Support	
Specifying table hints without using a WITH keyword is a deprecated feature and will be removed in a future version.

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

» Discuss this question and answer on the forums

Expert Performance Indexing for SQL Server 2012

Expert Performance Indexing for SQL Server 2012 is a deep dive into perhaps the single-most important facet of good performance: indexes, and how to best use them. The book begins in the shallow waters with explanations of the types of indexes and how they are stored in databases. Moving deeper into the topic, and further into the book, you will look at the statistics that are accumulated both by indexes and on indexes. All of this will help you progress towards properly achieving your database performance goals.

Get your copy from Amazon today.


Featured Script

Finding queries to tune

A query to help identify queries that need some tuning. 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

High Memory utilization - Hi Everyone, One of our server in the production environment is hitting 93% of memory utilization and sqlserver is the only...

Linking between Oracle and Unisys DMS II - Can anyone help me with the query please..?? I just wanted to know if i can establish the link server from...

SQL agent configured to call a SP - All, My problem scenarion is: "I have a SQL agent job setup that calls a SP which collects all data and sends...

Partition of table Base on Varchar COlumn - Hi team, I am looking for Partitioning / Slip the Data in a table to Multiple Small Files. Logical Partition can be done...

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

SQL Server 2005 : Backups

Restore existing database failed due to space issue - All, I have two data servers S1 and S2. I have a database D1. D1 exists in both S1 and S2....

SQL Server 2005 : Business Intelligence

Import Dynamic File Name with a Date/Time as the file type (YYYYMMDDHRMMSS) - For some unknown reason, the creator of this file set the naming convention to something I can't begin to fathom......

SSAS - how to view cubes - I come from Cognos, and try to understand how users can view and manipulate cubes once they are created? do...

SQL Server 2005 : Data Corruption

sql server not starting - Hi I was moving my system databases from one location to another,changed the master db location successfully. while changing the model...

SQL Server 2005 : SQL Server 2005 General Discussion

DTSX Variable Issue, can't distinquish between "42" and "00" - Correction to the title... Can't distinguish between "42" and "IM" I have a For Loop that iterates through text files and...

A severe error occurred on the current command.The results, if any, should be discarded. - Hi all, i have some problem with my SQL Server, if i am creating any new Db, and i am...

SQL Server 7,2000 : Administration

Change IP adresses of clustered SQL 2000 - Dears, I need to change all the IPs related to a production DB (4 IPs (NICs , Windows and SQL) ) The...

SQL Server 7,2000 : In The Enterprise

Change IP adresses of clustered SQL 2000 - Dears, I need to change all the IPs related to a production DB (4 IPs (NICs , Windows and SQL) ) The...

SQL Server 2008 : SQL Server 2008 - General

DB Size Growing after moving fields - Hi All, I am new here, but have read the site a lot. I have been doing some SQL maintenance on...

SSIS Package Error - User is again getting error when he executed an SSIS package.. he wants to transffer 9Lakh records from SQL database to...

Back up - What is the use of WITH CHECKSUM in backup statement?

Use of not exists - Hi I need to get the data from my "live" load table that does not exist in my archive_load Trying: select count([Live_Key]) FROM...

The size of a table. (space_used against calculated space). - With sp_space_used you get the 'gross' size of a table. (Used_size for data) Summing all fields with a datalength function also...

How to get results from an SP into a table. - Dear reader, Below is an example to get data from a stored procedure into a table. Is there a more generic method...

Foreignkey based primary key reocrd insert SSIS - Dear all, I am balamurugan,new for ssis.we have sql server source & destination is oracle.we need to implement the following steps...

ctrl+tab quits working in SSMS - Something I have done has disabled ctrl+tab from working. I don't want to restart SSMS, as I have numerous tabs...

Transaction log file growth - HI everybody. We backup our log files every 10 mins. 3 days ago we had an incident where users where complaining...

Moved Tempdb .mdf now can't restart SQL Server. - Hi all, I've been experimenting with my test box (luckily!!) and was moving various database .mdf and .ldf files to different...

SQL Date Issue - Hi, I wish to create a column 'GoalBehindFlag' which will work like a flag providing me 0s and 1s if the...

SQL Server Transactional Replication . Artice not created by snapshot - Good Day, We are running SQL Server 2008r2 using Transactional Replication. We selected a numbe of tables to replicate . One of...

Performance Improvement - Hi, I have a small query - by changing the datatypes in a table, will it improve the performance. For example,...

SQl Server R2 Enterprise Evaluation link anyone? - Hi, Anyone got a link or is hosting the older 2008 R2 enterprise evaluation version, I need to load up an...

Column update - this is my select query [size="1"] select CalDate, Timein, Timeout , CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Timein],[Timeout]),0),108) AS SpendTime_runtime, CAST (ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) / 60 as varchar ) + ':' +-- Hours ( + ':' separator ) CAST...

Error - i am having this error Msg 257, Level 16, State 3, Procedure query, Line 21 Implicit conversion from data type datetime to...

How to get the list of tables that has specific column name from linked server, available in SQL SERVER 2008 R2 - Hi, I have an access to oracle linked server called [oracleLS] it is there under the SERVER OBJECT => LINKED SERVER =>...

Querying sys.dm_db_partition_stats instead of sys.sp_spaceused - We have a daily process that saves into a table the storage info returned by the SP, but it's procedural,...

Performace issue while updating records and trigger on table - Hi All, I am having a performance issue while updating records into sql server table, I have created below trigger to update...

Database Virtualisation Tool - Hi, I would like to know if anyone has come across or used Delphix virtualised tool? Thanks

SQL Server 2008 Database Mail - Hi SQL Masters, I have a situation on my SQL Server hosted on Virtual machine (Oracle Virtual Box). I have a...

how to archive data based on on archive it self? - Hi I have an archive tabel in Oracle. We copy this to a ms sql 2008 r2 database for datawarehouse purpose. The...

SQL Agent Service runs fine, but keeps logging this error [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001] - Hi All I need some help here, I have looked and googled to no avail. I have an installation of SQL...

SQL Server 2008 : T-SQL (SS2K8)

Simplifying The Stored Procedure - Hi Friends, In a new assignment as DBA, I am trying to make few codes better and have tasted some success,...

Odd Conversion Error - Hello Everyone I would like to remove the '(999)' from the phone number that someone that did not know what they...

Case statement with subquery - I have a CASE statement with a subquery that works most of the time but if the subquery comes back...

Money not accepted a datatype - I have the following: DECLARE BalanceDue money, ktr int but I get a red underline under "money" and the message I get...

Primary Key Violation when inserting records in table - I have stored procedure which inserting records in two table.both table have primary key and when i am running that...

help getting return of function and loop into stored proc - Hi, I have a tricky issue I am struggly with on a mental level. In our db we have a table showing...

Update Performance - Hi Guys, Any Ideas what the best way would be to perform this update? UPDATE D SET Track_ID = P.Track_ID, Territory_ID = P.Territory_ID, Major = P.Major FROM Staging.Track_Mapping P INNER JOIN Staging.Track_Play D ON P.ISRC = D.ISRC_Code AND P.Event_Date = D.Event_Date Both tables...

SQL Server 2008 : July CTP

SSRS 2008 - Export to excel having more than 256 colums - hi , I have report in 2008 with more than 256 columns,when i try to export to excel it is giving...

SQL Server 2008 : SQL Server Newbies

Choosing the correct driver - I'm writing some documentation on how to connect Access to SQL via ODBC. I honestly always just choose "11.0" when...

Linked Servers (SQL 2005) - So I am a newb.... I have a database system that uses SQL 2005. The server that these systems reside...

Inserted & deleted table - Hi, I have confused with below query . Could anyone please help me.... Client have sent me one view query , there he put...

Delay in Print - Dear All I have procedure A,B,C. Procedure A calls B and C. Procedure B and C have loops and print statement...

NOCHECK CONSTRAIN - Dear All There is hardly any data in the table on which i have defined foreign key. But when i am...

defferred name resolution - Dear All I am using sql 2008 . While compiling the procedures it does not give error even if table not present. I...

Calculating Sales History for Months/Years - I have a table - TrnDate, TrnYear, TrnMonth, StockCode, InvoiceQty The TrnYear and TrnMonth are the Financial Periods and dont always tie...

SQL Connection - I just had sql server 2012 installed on my local machine but after typing my code I clicked on the...

SQL Server 2008 : Security (SS2K8)

Sql Serer Data Protection Tool - Hi, We are looking any Data Security tool for out existing Data for Protection, we would like to do the Data...

SQL Server 2008 : SQL Server 2008 High Availability

Full Text catalog index reorg or rebuild - Hello Team, I would like to know what basis Full text catalog index can rebuild or reorg? Thanks

DB mirroring question - In asynchronous mode of db mirroring, to manually failover, why do we have to change the transaction safety to FULL?

Move mirror without breaking it - We have 3 servers A,B and C. Configure mirroring between A and B. Move mirror from B to C without breaking the...

DB Mirroring fails due to different file locations in Mirror - Hi, I have a DB Mirroring configuration where the physical files are located in different logical directories(Data file locations). Principal...

Please Please help me- upgrading to sql 2008 R2 enterprise edition from sql standard edition on SQL Failover Cluster - I'm currently looking into the possibility of configuring a 2008 R2 failover cluster for my organisation. I'm curious as to...

SQL Server 2008 : SQL Server 2008 Administration

SQL Server TDE Encryption - Hi All, I have a PRODDB which is encrypted with TDE .I have DMK and DBCert Key with me .I also...

SQL 2008 R2 High Memory Usage - Dears, is it normal when sqlservr.exe takes that much of memory usage ? The installed is Microsoft SQL Server 2008 R2...

sp_executesql -- can produce very wrong execution plan - A front end app sends a query to SQL 2008 using sp_executesql. The query returns a count as the final result. The...

Does a backup cause TempDB to grow - I received an alert this morning that our Temp drive had run out of space. When investigating it, I found...

IP Address Doesnt turn from offline to online - :unsure: Hi I got a problem, I think it is a little bit weird kind of problem, I haven't find anything...

:unsure: Backup Particular DB Users and there Permissions. - Hi Experts, Is there any way to just script out users permissions in a given db. I know there is a...

Building a server? - OK, I am in charge of specifying our new SQL server, our server now is 5 years old and we...

Career : Certification

Refer books for exam 70-448 & 70-452 - Hi, I am planning to take exam 70-448 & 70-452 next month. [b]Can anyone help me with books need to refered...

Microsoft-BI certification for SQL Server 2008. - Hi, I am planning to take Microsoft-BI certification in next month for SQL Server 2008. For this I need to...

Advice - Hi I'm new here. I wanted to know which certification is most relevant if I want to go into analytics...

Programming : Powershell

Open Excel Error Using PowerShell in SQL Server Agent Job - I am trying to run a Powershell script, which opens and modifies an Excel spreadsheet, from a SQL Agent job...

SQLServerCentral.com : Anything that is NOT about SQL!

The Beer Cooler Thread - On popular request - at least one! - a dedicated topic about beer. I shall start with my favorite beer: Duvel. It is...

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

Display all the days in a months in a matrix - Hi, Can someone please suggest, how this is can be done. I need to produce all the days in the month, to...

User cannot run reports - Hi, I deployed SSRS for SQL 2008 R2. I'm funding the permissions are a pain in the neck. Firstly I set...

How to find FIRST matching value from the table , - Hi Team , How to find first matching value from the table , I need to get the first Australia amount 5000...

Move Report Designer (rdl) to Report Service (rdl) - Can I take a rdl/txt file from Report Designer (2.0) And try to upload it to Reporting Service ? Do I...

Client side function error - I am trying to add a function to a subreport but continually get the error 'subreport could not be shown'. I...

SQL Query - I have the following query: SELECT Sales_Intrastat.SalesID, Sales_Intrastat.Year, Sales_Intrastat.Period, Sales_Intrastat.Sales, Sales_Intrastat.CostOfSales, InstrastatCustomers.Depot, InstrastatCustomers.AccountName, InstrastatCustomers.CountryCode, InstrastatCustomers.AccountNumber, CountryCode.Description, InstrastatCustomers.ContractC

custom columns in a tablix (matrix) - HI , I was wondering if someone could help me. I have a SQL table with the following fields: -Variable -Period -Value which generates the following...

Report Showing Missing Images - I have a report that shows badge photo images for staff that is working using an external image source for...

Exporting to csv issue from ssrs 2008 R2 - Hi there peeps I have a simple Tablix Report that contains a number of fields that I'm exporting as a csv...

How Indicators works - Hello everyone - I was wondering if anyone can provide some insight on how indicators work. I read about the percentage...

Report Manager DSN Problem - Hi I have created a DSN (using sybse db) and develop report in SSRS 2008R2. Reports working fine in BIDS. Now...

Issue with link to report viewer - Hi community Has anyone ever experienced this before: I have placed a link to a report I created which looks like [url]http://myserver/ReportServer/Pages/ReportViewer.aspx?%2fScelo%2fOn+Demand+Billing%2fTime&rs:Command=Render[/url] Now...

SSRS 2008 Report Drop-Down/Context Menu Not Available - WHY?? - Hello All [b][u]Environment[/u][/b] Windows Server 2008 R2 Ent SSRS 2008 R2 IE 7, 8 and 9 SSRS 2008 R2 Report Manager usually has a Drop-Down/Context...

Second level drill-down icon doesn't display properly - Hello all. My report has two drill-down levels Top level - Region (visible) drill-down level - Market (toggle item Region) drill-down level - Client (toggle item Market) I...

Reporting Services : Reporting Services 2005 Administration

SSRS Error: Failure sending mail: The report server has encountered a configuration error. Mail will not be resent - Hi All, I have been trying to send ssrs report to users via email and i followed all the rules like...

Database Design : Hardware

Separation of MDF and LDF files in a VM enviornment - Within a VM environment that is using one massive array of hard disks. is it really beneficial to create 2...

Data Warehousing : Integration Services

The process cannot access the file because it is being used by another process - small files are locked a bigger one works fine - SQL Server 2008R2 SP2. I have three levels of SSIS packages : master staging fileprocessing main The staging package calls the fileprocessing package...

Data Warehousing : Strategies and Ideas

Log ship to local db for ETL source data - I'm using log shipping to maintain a failover copy of my main db on a secondary server. I'd like to...

Help needed for creating the star schema - Hi, We are planning to data warehouse the 2 different projects , to implement this we have to create the star schema...