SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

Featured Script

The Voice of the DBA

Lobbying for Change

I ran across a note recently on Twitter from Adam Machanic. He wrote:  Just spent most of the day working through a subtle PK issue - 1 bad row out of 18M. Would have killed for this. The item in question was a Connect item, one with almost 500 votes. It's a good one, and I'd encourage you to vote for it. I know that it seems many of these items are never worked on, but some changes make it into the product, so I'd ask you to continue to vote for change.

When Connect was first introduced, Andy Warren and I debated the value of the platform. On one hand it made good sense to directly feed information back to developers, but on the other hand, it was likely that those items that got more notoriety or votes might get fixed, even if they weren't necessarily good ideas. The popularity of an item doesn't necessarily mean it's one that should be fixed in the product first. We also worried about one of the big problems of the platform and that is that a tremendous amount of noise of entered and it becomes hard to triage the submissions.

As I watch Connect evolve, I can't help but think that it's been mostly a failure from my perspective, with a few notable successes, like Service Pack 3 for SQL Server 2005. There's too much noise and too many items ignored. However I also do think that those items that get lots of vots do get more consideration from Microsoft. More votes doesn't mean that the feature will get fixed, but I do believe the item gets talked about. (As an aside, please vote for more, final Service Packs)

Personally, I think that raising awareness of possible suggestions or problems is a good idea. I'd love to see a top 10 list of Connect items from MS for consideration every month. Having them highliht some items they're considering from the list might help focus attention from customers. I don't think that's likely, but I wonder if highly debated suggestions might be worth highlighting at SQLServerCentral. Would you like to see a Connect item of the week? Something you could vote on or even debate as a good idea? I would, and I'd consider adding them as a way to help improve the platform that I enjoy working on the most.

Steve Jones from SQLServerCentral.com

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


The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.0MB) podcast or subscribe to the feed at iTunes and Mevio . feed

The Voice of the DBA 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.

Everyday Jones

Follow Steve Jones on Twitter to find links and database related items and announcements.

ADVERTISEMENT
The Girl with the Backup Tattoo
Read the free article
In a time of deep confusion
- What do you mean there's Piña Colada in the disk drive?
 
When your worst nightmares are revealed
- The server is down. The restores have failed. The data is corrupt. The backups, will no one think about the backups?
 
There's only one number you need to call.
- Robyn Page?
 
And...?
- The DBA Team!
 
Read how the DBA Team came to the rescue once again, thwarting a sinister plot of corporate sabotage.
Read the free article

Featured Contents

 

Divide and Conquer - Performance Tuning

Claire Mora from SQLServerCentral.com

Sometimes we need to break down a complex problem into a multiple stage solution to achieve optimal performance More »


 

14 SQL Server Indexing Questions You Were Too Shy To Ask

Additional Articles from SimpleTalk

The first thing you need to understand about SQL Server is indexes, but somehow many of the basic questions don't often get asked or answered much on forums. Rob Sheldon answers some of these questions about SQL Server Indexes and indexing. More »


 

SQL Saturday #289 - Orange County, CA

Press Release from SQL Saturday

SQL Saturday is coming to Huntington Beach, CA on April 26 2014. This is a free day of training and networking for SQL Server Professionals - there are also 2 paid-for precons on April 25 presented by Jason Horner and Bradley Bell. Register for SQL Saturday Orange County while space is available. More »


 

From the SQLServerCentral Blogs - Manipulating Dates and Times in T-SQL

Dwain Camps from SQLServerCentral Blogs

In SQL 2008, Microsoft introduced some new date and time data types to augment the options available in prior versions.... More »


 

From the SQLServerCentral Blogs - Data Collector in SQLServer 2014 – What’s New !

AnupWarrier from SQLServerCentral Blogs

Today I noticed couple of changes(Neat changes !) for Data Collector in SQLServer 2014. This post is a detailed walk-through of setting... More »

Question of the Day

Today's Question (by Steve Jones):

You are using a symmetric key to encrypt some data in a stored procedure. However you want to follow good coding practices and stop using the key when you are done with the encryption before you execute other code. What T-SQL would you use to ensure that the symmetric key, SalesSymKey, cannot be used later in your session?

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


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

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

ADVERTISEMENT

SQL Server 2012 AlwaysOn Joes 2 Pros®: A Tutorial for Implementing High Availability and Disaster Recovery using AlwaysOn Availability Groups

SQL Server 2012 brought in some great new features and one of the important ones is scalability and performance via AlwaysOn. AlwaysOn is a superset feature and is a combination of many things you will learn about. As the language suggests, this technology achieves a SQL Server infrastructure that can be "always on". For businesses that run 24x7 downtime means the loss of business. This type of risk is out of the question for these businesses. This book discusses in detail the concepts of SQL Server AlwaysOn starting from the basics.

 

Yesterday's Question of the Day

Yesterday's Question (by Shanjan Sapra):

What is the surest way to check that your syntax is valid and will work with SQL Server?

Answer: Set the context of your execute with the help of NOEXEC setting

Explanation:

You can set the context of your execute to On or Off with the help of NOEXE setting. Example using setting NOEXEC.

Check the following query:

USE AdventureWorks2012
GO

-- Change Setting of NoEXEC to ON
SET NOEXEC ON
;
-- INSERT Statement
INSERT INTO
IT.Shift
(Name,StartTime, EndTime,ModifiedDate
)
VALUES  ('Shanjan', GETDATE(), GETDATE()+1, GETDATE
());
-- Change Setting of NoEXEC to OFF
SET NOEXEC OFF
;
GO
-- Check Table Data
SELECT
*
FROM IT.Shift
;

Here is the result of the query when we executed.


» Discuss this question and answer on the forums

Featured Script

Generate script to rebuild indexes in a database

Andrés Michaca from SQLServerCentral.com

Generate script for reconstgruir ena database indexes

---Objetivo: RECONSTRUYE INDICES DE UNA BASE DE DATOS
---Fecha: 19/03/2014
---Autor:  Andrés Noé Michaca Trujillo
select 'ALTER INDEX  [' + ltrim(rtrim(ind.name))+'] on [' + ltrim(rtrim(esq.name))+'].['+ltrim(rtrim(OBJECT_NAME(ind.object_id))) +']  REBUILD PARTITION  =  ALL  WITH  ('+
    ltrim(rtrim(case  ind.is_padded
                   when  1 then ' PAD_INDEX = ON, '
                   else ' PAD_INDEX = OFF, '
                end
    )   )+
    ' STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, '+
    ltrim(rtrim(case
                   when ind.is_unique =0  and ignore_dup_key= 1 then ' IGNORE_DUP_KEY = ON, '
       when ind.is_unique =0  and ignore_dup_key= 0 then ' IGNORE_DUP_KEY = OFF, '
                   else ''
           end
   )  )
    +'ONLINE = OFF,'+
    ltrim(rtrim(case ind.allow_row_locks
                     when 1 then ' ALLOW_ROW_LOCKS = ON, '
                     else ' ALLOW_ROW_LOCKS = OFF, '
                 end
   )  ) +
    ltrim(rtrim(case ind.allow_page_locks
                     when 1 then ' ALLOW_PAGE_LOCKS = ON)'
                    else ' ALLOW_PAGE_LOCKS = OFF)'
                end
   )   )    
from sys.indexes ind
inner join sys.sysobjects obj on
obj.id =ind.object_id
inner join sys.schemas esq on
esq.schema_id =obj.uid
where obj.type='U' and ind.name is not null and upper(OBJECT_NAME(ind.object_id))<> 'SYSDIAGRAMS'
order by OBJECT_NAME(ind.object_id)

More »

Database Pros Who Need Your Help

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

SQL Server 2014 : Administration - SQL Server 2014

Hardware Requirements for 1 TB Database with Backup - We are migrating Databases, which we expect to reach 1 TB at the final lap. We need the following info...

SQL Server 2014 Developer Edition - availability for download ? - Does anyone know when SQL Server 2014 Developer Edition will be availability for download ? I thought SQL Server 2014 was...

High Memory is 70% & growing Fast - HI Experts, My database server memory utilisation is growing faster from past 1 week. it remained same for 1 week around...


SQL Server 2014 : Development - SQL Server 2014

Cannot deploy cube -analysis services tutorial- - 0 I am a first time SQL server user trying to do the Analysis Services Tutorials. I am on the...


SQL Server 2012 : SQL 2012 - General

Replaying workloads with minimal external factors - Hi all, I'm currently working on a project at work to test the effects of database compression, trying to obtain measurable...

Backup Compression Restrictions - I came across the below Backup Compression restrictions. can someone please add on any more restrictions to backup compression in...

Problem with DatabaseMail - Hi all We've just moved our database to a new server, and we're having problems getting databaseMail to work. Essentially, when we...

Which path will be the perfect in MCSD for me to deal with SQL Server Stuff ? - I intended to be a SQL Server developer and Administrator , but I planning to take MCSD before MCSA SQL Server...

Export SSIS Package/Project - Using SQL Server 2012 and Visual Studio 2010 as the development platform. I Deploy the Project and the processes run...

Simple SSAS (for dummies) Tutorial? - Hi, I'm okay at SQL Server and Reporting Services, but know close to nothing about SSAS. I get the theory behind...

WebServices - Hi, I need your help. I need to set up web services locally on my machine. I am working in a team....

simple store procedure - Hey Guys, I am using SQL Ser 2012 and using adventureworks 2012 db. I am creating a simple SP wherein I search...

COMPARE TWO TABLES COPY TABLEA VALUES TO TABLEB - I HAVE TWO TABLES LIKE.. TABLE_A SNO NAME MOBILE EMPID 1 RAJU 9014494876 A12345 2 VAMSHI 9765432123 B12367 3 KARTHIK 986623456 C34567 TABLE_B SNO NAME MOBILE EMPID 1...

Execution Plan - Hi, I saw Index scan in execution plan. The table has primary key. We are selecting that PK data based on...

SSIS Package - FTP Task fails when run from a job - We are running SQL Server 2012 SP1 64-Bit EE on Windows Server 2008 R2 SP1. I have a SSIS Package...

Moving over from Oracle - I have recently retired from my previous job as an Oracle DBA, now I'm working for a small IT firm...


SQL Server 2012 : SQL Server 2012 - T-SQL

Find the Numbers from a string - Hi experts, i hope my problem will be solved. I have a string and i want to get only the numbers...

Transpose data? - Hi all, I'm new to SQL and these forums, but I'd love some help! I have a table that has data...

More than 1 alphanumeric chars in a string - I have an employee table Surname GivenName ABC x.yz A.BC X.*YZ A*.BC xyz The query needs to get me the surname and givenname which have...

Get list of all workers having same SET of Rates - Hi All, I have attached some test data for you that has two temp tables "#worker" and "#worker_rate". The issue...


SQL Server 2008 : SQL Server 2008 - General

Ideas on how to flag existing user tables for future deprecation - We are in the process of moving about 20 databases to a new data center. As part of that move,...

Use of create statistics - What is the use of create statistics ? I am having a DB With AUTO_CREATE and AUTO_UPDATE for statistics ON, then...

The character string that starts with ... is too long. Maximum length is 4000 - Can anyone tell me if there is a way to get around the 4000 character limit for executing dynamic SQL...

Need Query for summarizing data - Hi there, I need query for the attached output [img]http://www.sqlservercentral.com/Forums/Attachment15316.aspx[/img] [code="sql"] create table #Wholeseller ( wsid varchar(100),[Productid From] int, [Productid To] int, units int) create table #Retailer ( retid...

rebuild index for 1 table at 1 time? - Hi, I have a huge DB with 30 tables out of which 10 are extremely huge tables. From the index physical...

Out Of Memory Exception - Hi All, Application users are getting following error very often and developers think that its SQL server error. But no errors...

How to find Alternative weeks in sql server 2008? - Hi team, Currently I have Type, Internval, Rstartdate,ID in the view. I need to add " Week" to that and following is...

Can't understand why some data is repeated - Hi Everyone, I am trying to eliminate repeated data in my results, towards which end I am using the ROW_NUMBER() function,...

Is there a way? - I have many tables that have 1000+ columns, of which about 85% of them are NULL. Is there a way...

Doubt on Dynamic SQL - I have a set of 5 queries as 5 rows in a table. I am looping 1 by 1 and...

Link SQL job history to SSIS log table - The SSIS package is saved at the file system of the SQL server. The job calls the SSIS package. The...

SQL Server Read ismore - Insert into Table Select * from anothertable where somecondition in(select some_condition from thirdtable) While doing this operation i see there are more reads...

Number of columns in table - I've been researching issues on the number of columns in a table vs performance. I have a Customer table, 87 columns,...

how can i set up windows event log notification through mail using SQL server - how can i set up windows event log notification through mail using SQL server. Scenario: i have to audit an specific eventid...

Urgent help with STRING in SQL - Hi friends, I need a urgent help with SQL String validation please- I have a field in DB that is nvarchar(500) named...

SQL Server on domain or workgroup - Hi Friends, I have very limited knowledge on this topic. I have worked and working on SQL servers on the machines...

how to update value from c2 to c1 - c1<---->c2 1<---->2 2<---->3 3<---->4 11<---->12 12<---->13 13<---->14 We want to update last value of c2 to c1 , which value is linking c2 and c1 output c1<---->c2 4<---->2 4<---->3 4<---->4 14<---->12 14<---->13 14<---->14

Trying to restore a .BAK from another domain - We are trying to restore a .BAK from another domain. There are no trusts created and we are told it...

Get the hierarchy orders(top to bottom) of the table - Can we get the hierarchy orders of table Eg: 10 tables Table1 is master to table2 and so on till table10 Just...

Select statement and variables - Hi, I have a query which returns the correct data when I specify the variable @InYearMonths as any number between 1...

Table Partitioning - I am trying to partition a large table. The table is wide as well as long. There is a clustered...

Partition key should be part of Primary key - Hi, I would like to know, when I'm creating a partition on a table, partition field should be part of...


SQL Server 2008 : T-SQL (SS2K8)

Embedding a variable into a parameter when calling a stored procedure - HI all, I want to add the result of a Select statement inside a parameter, not too sure how to...

Complex SQL Pivot Query Urgent Help Needed - Hi Guys, I want to convert the data from Original Table to Reporting View like below, I have tried but not...

Arrival and Depart location query help. - Hi all, I have a question regarding the selection of arrival and departure city. [code="sql"] CREATE TABLE #XY123 ( tktamt MONEY, departcty...

Query regarding Update the data using application - SELECT ContactID,FirstName,MiddleName,LastName,Description FROM Contact Contact table contains 4 columns as explained. in the application there is one tab called Contact where...

Table Size Query - Hi Experts, I'm using Ms SQL Server 2008 R2 (Enterprise edition), I need some help in compiling the correct SQL query...

Help need in Avoiding Loop - Hi, Here is my table structure: [code="sql"] ;with Users as ( select 1 as UserId, 2 as PendingAmount,10 as AvailableAmount union all select 2 as...


SQL Server 2008 : SQL Server Newbies

Combining multiple rows into columns - I would like to create a query that will join two tables and have the differences into columns instead of...

Insert Into with nested Selects - Hello, I have a table HemAnimal and a table HemTransfer. For some reason there is no row created in the table HemTransfer...

How do I recover a db corrupted more than 1 full backup ago, and apply all subsequent diff backups??? - 'morning all I've had a report of missing data (restore from full was done, diff wasn't applied) in a db that...

decrypt to date datatype - I accidentally used date data type (rather than datetime) to encrypt a column. As I understood from online research, that...


SQL Server 2008 : SQL Server 2008 High Availability

Windows Server 2012 R2 Clustering Issues - Hi, I am setting up a test Windows Server 2012 R2 VM with SQL Server 2008 R2 cluster. Setting up Windows...


SQL Server 2008 : SQL Server 2008 Administration

Procedure Execution Error - Hi Experts, We are getting below error when tried to execute a procedure.. Msg 18483, Level 14, State 1, Line 1 Could not...

Possible to write a trigger to monitor for specific privileges being granted? - We're working with a customer right now who's looking to move their existing SQL server to our control. Currently, one...

I can't able to restore the Tail Log back up.. - Dear All, Am Using 2008, i have restored the full and Differential backup with norecovery model, But when am trying to restore the...

Recommend me Best tools for monitoring SSIS, SSRS and SSAS - I would like to know best tools for monitoring SSIS, SSRS and SSAS. Thanks.

Dtabase Mail Profile - I need help here, I have this web application which uses a mail profile which is created in SQL naming CSR...

While restoring the database full/differential/ log file am getting an error like "Exclusive action could not be obtained because the database in use" - Dear All, am using 2008, I have taken backups all the backups and trying to restore it but it is throwing error...

TEMPDB issue - Hello, We have Sql 2008 and currently we having issue that TEMPDB is FULL, I have restarted the server but it's...

SQL Server Agent jobs not running as scheduled - Hi all, has anyone encountered sql server agent not actually running the jobs it is schdeduled to run?! It was recently...


SQL Server 2008 : SQL Server 2008 Performance Tuning

LINQ vs SQL Questions - I've run into issues in the past dealing with extremely poorly written queries with LINQ. I've been under the impression...


Cloud Computing : SQL Azure - Development

Azure Newbie Question - I want to set up a demo SQL Server 2012, Sharepoint 2013, Powerview instance. I want to do BI Development,...


SQL Server 2005 : Administering

calling sqlwb from batch file. - Darlings ... Potentially dim question but seriously annoying issue. I'm calling sqlwb from a batch file. (Ultimately I will have a bunch...

SQL Server Agent : Job Failure - Hello, I wish to put the result of a query in Excel then send it to a number of persons at...


SQL Server 2005 : Backups

How do you restore a deleted record/s directly from the SQL transaction log? - How do you restore a deleted record/s directly from the SQL transaction log? I would also like to add that...


SQL Server 2005 : Business Intelligence

OlapEvent = ObjIdUpdate(2) - Hi All When I execute sp_WhoIsActive to see who is active, I constantly see this running select SELECT @OlapEvent = ObjIdUpdate(2) with wait...

Error while exporting data from SQL to Access - Hi, I am developing a SSIS package where I am trying to pull records from SQL server tables and pushing the...


SQL Server 2005 : CLR Integration and Programming.

How to send security headers when calling the web service from clr stored procedure - Hi, I am able to call the web service from clr stored procedure before. Now service provider enabled authentication. Now CLR stored procedure...


SQL Server 2005 : SQL Server 2005 General Discussion

Find and Replace Injection String in Server 2005 - A website I manage has recently fallen victim to an SQL injection, so I've been trying to find a quick...


SQL Server 2005 : SS2K5 Replication

The row was not found at the Subscriber when applying the replicated command. - Hello, I have the following problem: I have a bidirectional transactional replication on SQL Server 2005 Std Ed. SP 1 . On...


SQL Server 2005 : SQL Server 2005 Performance Tuning

SQLOS: Runnable, Running, Wait List - I was reading a Microsoft White Paper (haven't completed the reading yet) Using the illustration below, why would SPID 56 go...


SQL Server 2005 : SQL Server 2005 Integration Services

Flat File Source Problem in SSIS - Hi, Is any one have/had problem. I have very simple SSIS Package. Flat file source and SQL Destination. No other...

Variable As OLE DB Connection. - Hi Guys, Need Help,I want to know what is the best way to deal this kinda Problem or approach. I...

SSIS Package not failing? - Hello - My SQL Server Agent shows successful completion of SSIS job but messages in Log File view shows that there...

Unable to Send Files to FTP Server - Hi, Sorry if the topic already exists. I want to process an excel report and send the file to FTP server....


SQL Server 2005 : T-SQL (SS2K5)

Convert Row Data in 1 Field to Many Columns - Hello this is my first post and my TSQL is average at best so any help will be much appreciated I...


Reporting Services : Reporting Services

How can I access OUTPUT variable of a store procedure in RDL dataset - Is there a way to access OUTPUT variable of a store procedure in RDL dataset. If the store procedure has not...

Multiple Excel Tabs Using a Single Dataset - I have a request for an excel spreadsheet with multiple tabs. Each tab will have a unique customers billing detail....

SSRS Report Viewer Scroll to Load Next Page - When I preview reports in Report Builder 3.0 there is a functionality that when I reach the bottom of the...

SSRS shows wrong form - I am learning SSRS from a book. The book tells me to modify a simple existing report as follows - >...

Report Builder 3.0 Expression count - I have got a dataset which is pulling monitoring counter values for logical drives, ram and cpu for multiple machines....

SSRS 2005 report: Cannot bulk load Operating system error code 5(Access is denied.) - I built a SSRS 2005 report, which calls a stored proc on SQL Server 2005. The proc contains following code: ...

SSRS Interview Questions and Answers - Hi all, Please help me with SSRS interview Questions and Answers. I want to go for the Interview for SSRS and...


Reporting Services : Reporting Services 2005 Development

How to increase the legend size of the charts - Hi, I am new to ssrs. Could anyone help me in finding out option to increase the legend size of the...


Reporting Services : Reporting Services 2008 Development

SSRS Reports Timeout decrease for Standard Subscriptions - Hi, I am trying to reduce the SSRS Time out limit as there are number of reports which runs for very...


Programming : XML

Noob xquery problem - Hi all, I'm still having a hard time writing out xquery statements. I have a better grasp of querying element...


Data Warehousing : Integration Services

Query from Source not sure how to get into Destination - I am using SSIS 2012 and am pulling from a Server in cloud that I have read only rights to....

Offline-Jobs schedule - I have scheduled my jobs successfully and they run fine in SQL Server Agent 2008. But When I shutdown my...

CSV file to table - I'm wondering if the data from a sample csv file can be transformed and loaded into a table. I have...


Data Warehousing : Analysis Services

Calculated member formula for All member is not SUM of children - Hello everyone, I am working on a proof of concept to move some heavy analytical calculations into SSAS. Everything was working...


SQLServerCentral.com : Anything that is NOT about SQL!

Cloud based learning environments - Hi all, I wondered if anyone could help suggest a cloud hosting provider. I am mentoring a young SQL Server newbie and...

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 : Articles Requested

T-SQL Comparing Rates - A writeup based on this thread. Show how this works and why one method may be better than the other [url]http://www.sqlservercentral.com/Forums/Topic1537283-3077-1.aspx[/url]

Export with Headers - How can you export data from a query, using bcp, SQLCMD, etc. and include the column headers at the top?...


SQL Server 7,2000 : In The Enterprise

Unable to Kill Process :dbcc inputbuffer - Hi, Trying to kill this process using kill 14 but does not work. dbcc inputbuffer(14) shows the following. How can I force to...


SQL Server 7,2000 : T-SQL

redirecting output from SP - Hi, is it possible to redirect all the messaging output from a stored procedure to a variable (or anywhere else) which...


Microsoft Access : Microsoft Access

reference recordsource on subform - I have a form with 3 subform. On Currrent event on subform1, I am getting the ID and then using...

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