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

Doing What It Takes To Get The Job Done

Today we have a guest editorial as Steve is speaking at SQL Intersection.

As I’m writing this I’m wondering what sentiment the title will evoke – cynicism, curiosity, agreement? It’s a phrase that too often means going above and beyond for longer than is rational in an effort to get to some arbitrary (or at least it seems that way) goal. Yet it’s also something that we say about our best people as a point of pride, and something I imagine many of say about ourselves as well.

I was thinking about this recently because my nephew was doing some weekend work for me and by the end of the day was tired, really tired. Not because I worked him hard (I did) but because he had been at work until midnight the day before. He had told his manager he would have something done by the end of the day and as such things go, it turned out to be a lot more work than it had seemed. At 5 pm on Friday he was just over half way done. Surely he could have explained why the work was taking longer, but instead he chose to keep working until it was done. Not because it was life or death, not because he would be fired if he didn’t get it done, but because he said he would get it done.

Maybe – hopefully – he earned some extra respect from his manager. Maybe – probably – he’ll look a little bit harder or dig a little big deeper before making a commitment the next time. Certainly he remained true to his values.

It’s not always as easy as working harder and it’s not always as simple as doing whatever it takes, I know that, something an IT career will teach you in a hurry. But sometimes it is, and nothing stands out more than someone who doesn’t get it done when that time arrives.

Andy Warren from SQLServerCentral.com

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

ADVERTISEMENT
sqlprompt

FREE eBook "45 Database Performance Tips for Developers"

Improve your database performance with 45 tips from SQL Server MVPs and industry experts. Get the free eBook here.

DBA Term

NEW! The DBA Team in The Girl with the Backup Tattoo

Pina colada in the disk drives! How could any DBA do such a thing? And can the DBA Team undo the damage? Find out in Part 2 of their new series, 5 Worst Days in a DBA’s Life. Read the new article now.

SQL Source Control

24% of devs don’t use database source control – make sure you aren’t one of them

Version control is standard for application code, but databases haven’t caught up. So what steps can you take to put your SQL databases under version control? Why should you start doing it? Read more to find out…

Featured Contents

 

Why every SQL Server installation should be a cluster

Francis Apel from SQLServerCentral.com

In this article, I’ll attempt to “convince” you that every SQL Server you put into production should be a SQL Server Failover Cluster Instance (FCI). More »


 

SQL Server Backup Crib Sheet

Additional Articles from SimpleTalk

Robyn Page's crib sheet was a Simple-Talk classic, providing a terse but thorough roadmap of all of the important SQL Server backup-related considerations. It returns, newly revised for SQL Server 2014. More »


 

From the SQLServerCentral Blogs - SQL Server – Hide system objects in Object Explorer – SQL Server Management Studio

Vishal.Gajjar from SQLServerCentral Blogs

By default, SQL Server system objects are listed in Object Explorer in Management Studio. These system objects include system database,... More »


 

From the SQLServerCentral Blogs - April 2014 – SQL Server Monthly Maintenance Checklist

StrateSQL from SQLServerCentral Blogs

image source Make sure you start the month right by taking the time to make certain your SQL Server environment is... More »

Question of the Day

Today's Question (by Steve Jones):

I have data for my salespeople in a table as follows:

CustomerID  LastSale                Salesman    Sales

----------- ----------------------- ----------- --------------------

1           2014-01-02 00:00:00.000 1           5000

2           2014-01-05 00:00:00.000 2           1000

2           2014-01-06 00:00:00.000 2           300

1           2014-01-06 00:00:00.000 1           50

However the data in the last column, sales, was encrypted as follows:

CREATE CERTIFICATE Sales1Cert
AUTHORIZATION Sales1 WITH SUBJECT = 'Salesperson1 certificate'

OPEN SYMMETRIC KEY salesSymKey
 DECRYPTION BY CERTIFICATE sales1cert

      INSERT customerSales
              SELECT 1
                    , '1/2/2014'
                    , 1
                    , ENCRYPTBYKEY(KEY_GUID('SalesSymKey'),
                                   CAST(5000 AS NVARCHAR(20)))
      INSERT customerSales
              SELECT 2
                    , '1/5/2014'
                    , 2
                    , ENCRYPTBYKEY(KEY_GUID('SalesSymKey'),
                                   CAST(1000 AS NVARCHAR(20)))
      INSERT customerSales
              SELECT 2
                    , '1/6/2014'
                    , 2
                    , ENCRYPTBYKEY(KEY_GUID('SalesSymKey'),
                                   CAST(300 AS NVARCHAR(20)))
      INSERT customerSales
              SELECT 1
                    , '1/6/2014'
                    , 1
                    , ENCRYPTBYKEY(KEY_GUID('SalesSymKey'), CAST(50 AS NVARCHAR(20)))

Which query will let me decrypt the contents of the last column?

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 2 points 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

Professional SQL Server 2012 Internals and Troubleshooting

The 2012 release of SQL Server is the most significant one since 2005 and introduces an abundance of new features. This critical book provides in-depth coverage of best practices for troubleshooting performance problems based on a solid understanding of both SQL Server and Windows internals and shows experienced DBAs how to ensure reliable performance. The team of authors shows you how to master the use of specific troubleshooting tools and how to interpret their output so you can quickly identify and resolve any performance issue on any server running SQL Server. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Andy Warren):

Background: The XYZ corporate database team has decided to re-engineer their backup process by building a queue table in MSDB that will hold all of the databases that need to be backed up on each instance. They are running SQL 2012 Standard Edition with the latest patches applied. The table has been created and now the lead DBA is doing some validation.

She starts by running the following query to check the data in the table:

select * from backuplist

The query returns 40 rows. She joins backuplist to databases and still gets 40 rows returned. Looking at the design, the backuplist table has a primary key on name, but no foreign key on name. Before adding the foreign key she checks for non-matching rows by running this query:

select * from backuplist d left join databases b on d.name = b.name where b.name is null

This query returns 0 rows. Good! Now to add the foreign key she executes the following:

ALTER TABLE [dbo].[databases]  WITH CHECK ADD  CONSTRAINT [FK_backlist_backuplist] FOREIGN KEY([name]) REFERENCES [dbo].[backuplist] ([name])
The alter fails. She double checks the data check query and runs it again:
select * from backuplist d left join databases b on d.name = b.name where b.name is null
It returns 0 rows, confirming no one has changed the data in a way that would prevent adding the foreign key. She re-executes the alter statement to add the foreign key and it still fails. 
Why can't the foreign key be created?
Hints:
  • It's not a syntax error in the alter statement
  • If you could see the error message the problem would be obvious
  • There is nothing unusual about the instance
  • The primary key is a single column (name)
  • All objects discussed are part of the dbo schema

Answer: Databases is not a table

Explanation:

The syntax for the alter command for the foreign key is incorrect is not the right answer. All things being equal, this should create the foreign key. It just doesn't this time. It was also ruled out as a cause by the first hint.

The query to check the data is incorrect is not the right answer either. I made it deliberately confusing by appearing to swap the table aliases (databases=b and backuplist=d), but it would return unmatched rows. 

Need to run DBCC UPDATEUSAGE first, then try again is not the right answer. This one, along with It's time to run DBCC CheckD are there to tempt you into thinking something is really wrong and you don't have a better idea. Neither would resolve the issue.

There are duplicate rows in backuplist and or databases is not the correct answer. There can't be any duplicates in backuplist because the question states it has a primary key on name. There could be duplicates in databases but it wouldn't cause the problem we have with creating the foreign key.

Databases is not a table is the right answer. To build the question I created a synonym for sys.databases like this:

create synonym databases for master.sys.databases

It could also have been a view. Both the synonym and the view would respond to queries perfectly and make all seem well, but when you run the alter you get this:

Msg 4909, Level 16, State 1, Line 1
Cannot alter 'dbo.databases' because it is not a table.
Clues were about it being in MSDB and showing the schema as dbo, and the hint about everything being in dbo also. I suspect it would be easily solved if you were in SSMS even without the error message, but harder when you can't see the environment.
Note that if you really wanted to solve this problem you'd have to put a trigger on backuplist, as far as I know it's the only way to do cross database checks. Or you could move the backuplist table into master (and even add a synonym to that back to MSDB if you wanted to).
Please add a comment on what you liked or didn't like about the question.

» Discuss this question and answer on the forums

Featured Script

Script move of DB files from one location to another

Greg Ryan from SQLServerCentral.com

This Script generates the sql and powershell scripts necessary

to move the Database Mdf and Ldf files from one Drive\Path to another

Detach (Sql)

Move (Powershell)

Attach (Sql)

It can script all database files for a server at once or with the addtion of some selection critera a subset of DBs

Also includes reverse scripts if this a a temporary move.

See Script for instructions

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

Very High Full Backup size - On my production sql server , teh mdf size is 400 MB , ldf is about 1.2 GB . If i take full...

Filter audit in SQL 2012 - Trying to create an audit that goes to the application log. With a certain criteria, select, update, delete etc.. where...

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

query help needed - I have one table that contains members, and a query which contains related records of payments made grouped by month...

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

Database and its Objects Naming Standards - I am trying to establish the standards for naming convention in my new project. Can you please provide me the...


SQL Server 2012 : SQL 2012 - General

Retrieve binary file from SQL Server - Hi All, I have been trying to store binary file in a folder from the SQL Server. Here is the code I...

Calculate tempdb size for Read commited snapshot enabled - Hi All, I receive Error: 3967, Severity: 17, State: 1. Insufficient space in tempdb to hold row versions. We have 8...

SSRS to SAP - can i connect to SAP from SSRS and execute reports?

Need help in SSIS. - Hi, I need find out the number of columns in flat file before i process that particular file. I have file name...

Extracting Data from Compressed CLOB XML using SSIS - Hi Source System : Oracle Target System : Ms Sql Server 2012 ETL Tool Used : SSIS My source data is present in XML File...

SQL Cluster 2012 AlwaysOn-Two Secondary Replicas - Dear Gurus, I need your kind support for the below query. while configuring SQL Server 2012 Cluster with Always on, why...

Time out Errors - Hi, I want to find out the time out errors for a particular SP. Please let me know how to check...

How do i change a string key to an int - normal methods not working! - did VARCHAR(20) SELECT top 10 CAST(did AS INT) FROM table Msg 245, Level 16, State 1, Line 3 Conversion failed when converting the...

Creating 2 AlwaysOn clusters for 1 app - We have 2 SQL 2012 servers. Our application has 2 databases. We are creating an AlwaysOn cluster. Is it good...

smalldatetime - Why has smalldatetime changed from 2005 to SQL 2008\2012? i dont understand the logic behind it?

Extended Event - Hi, I didn't get chance to work with the extended events. I am working in the analysis of deadlock. I have extended...

Analyzing Trace for Deadlock - Hi, I need some one advice. I need to analyze the deadlock. Below I am providing some details In the Event class:...

Execution Plan Icons - Hi, I want to find out more information about the execution plan. I saw Parallelism (Gather Streams) in the excution plan....

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

blocking - Hi, I want to implement a script which will run continuously to find out blocked sessions and send an email when...


SQL Server 2012 : SQL Server 2012 - T-SQL

Need to un-pivot some columns, pivot one column - CREATE TABLE [dbo].[MyTable]( [CampusID] [int] NOT NULL, [Campus] [varchar](20) NOT NULL, [TermID] [int] NOT NULL, [Term] [varchar](12) NOT NULL, [StudentID] [int] NOT NULL, [Qualification] [varchar](15) NOT...

Updating a table - I am going to try and simplify this because I think I am thinking to hard for the problem at...

Which is Better? - Hello Everyone I am reviewing code in a SQL database that was coded by a BA and a Old oracle developer....


SQL Server 2008 : SQL Server 2008 - General

How to trace for 'SELECT *' with server-side trace - Hello experts, I want to capture times when a view (or any query, really, but especially views) has been written...

Counter data not recorded with PerfMon - Greetings all, I've used PerfMon a few times a year with a saved template, but recently I got a new computer...

Question regarding adding instances. - Scenario: We have 4 customers on a VM. There is only 1 DB per customer and each DB is about 10...

How can I find a sql statement executed on a single table? - All, I have a very large web app with inbedded sql that I cannot find. This sql needs to be changed...

?? on get info when last date was today - 80 ?? - Hi I have the following below to get the info i need. Except I only want the records when the most recent...

Calculate number of records for each range of values - I have a table where I need to calculate number of records for each range of values. My table is...

combine views - Hi, I have two views: ytd and ytdper I'd like to combine the two views into one but I'm not sure...

Migrate scheduled jobs - Hi all, Is there a 'best practice' for bringing scheduled jobs across from SQL 2000? I've generated a script but it...

Hash match - Hello, I checked Hash match in my query... I saw that Probe Residual is this.. [MCS_MESDB].[dbo].[TB_HIST_STATION_CPM01].[COMMIT_NO] as [H].[COMMIT_NO]=[MCS_MESDB].[dbo].[TB_INSPECTION_CPM01].[COMMIT_NO] as [I].[COMMIT_NO] in both table is...

split big query into two different query using global temp table - Hi All, I need urgent help, i have a query which has more than 4000 char and SSIS 2008 R2...

Insert into table varying with time - Inserting from my current table to archive table on same database [code="other"]Insert into archivetable Select * from table[/code] Select * from table has 20 columns...

CLR stored procedure runs slowest on server than on workstation - Hello, For months, we are wondering why one of our CLR stored procedures is running slowest on a server than on...

rebuild increases memory - Hello, it is possible to handle increasing memory by rebuild and update statistics ? Every weekend is increased about 20-30GB. After fill...

script to find the roles - i need to prepare the audit report for 2005 and 2008 servers and also the report is in same format...

Total Server Memory Of SQLServer - Hi Our server has the total memory almost 25 G and the memory that sql server has in task manager is...

Cluster Question - Windows 2008 cluster and windows 2008 r2 cluster SQL 2008 For the above configuration, is there a way or query to find...

Split contents of a field into multiple rows with a position/order assigned - Hi i have the following table of data (sample of one below in script) [b]I need to convert the last column...

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

generate insert statement from table - I have a table with the following columns: customnr number wk1 wk2 wk3 wk4 1200 20 5 6 7 8 1300 10 4...

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


SQL Server 2008 : T-SQL (SS2K8)

convert column in qry1 and return unmatched - I have a problem.... I have cpt codes in qry1 that need to be converted then match against qry2 and return...

Need help on T-SQL. May be Pivot Tables - I've tables and data as following, [code="sql"] GO /****** Object: Table [dbo].[CV] Script Date: 04/15/2014 00:49:42 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE...

Update table with multiline comments - Hi All, How to update column with multiline coments. eg. I need to update Notification column in my table as below. Exit Formalities...

How to get distinct records from table - Suppose I have 2 table 1)Main 2)History Main table maintain all the records having columns MAIN_SKU,DEDUCTIBLE_AMT,model_id,catagory,ModifiedDate IF DEDUCTIBLE_AMT is changes it...

Convert the stored procedure to "Standard SQL Select" - I have a very simple stored procedure to be used in Dundas Dashboard: [code="sql"] ALTER proc [dbo].[ddGetCurrentOpenAndClosed] AS declare @Open int declare @Closed int select @Open...

Set value based on previous row - I am trying to code a rule at the moment which sets a value based on the value of the...

Eliminating duplicates while insert - Hi, WITH cte_OrderProjectType AS ( select Orderid, min(TypeID) , min(CTType) , MIN(Area) from tableA A inner join tableB B ON A.PID = B.PID left join tableC...


SQL Server 2008 : SQL Server Newbies

Real Time Data Stream - Hello forum. I want to introduce myself as Adam. I am a self starter and self motivated learning. I am...

Execution Script Failure... - I'm attempting to run a script via management studio. Created a new job. Under Job Step Properties, Type was left at...

Comma Seperators - I am trying to remove "," from strings .( Only if there is no string after the comma) example : abc , cde,xyz,aaa, zzz, yyy...

More Memory - We have a Sql Server running Server 2003 Std X64 SP2, On this we have a Microsoft SQL Server 2008...

SQL Server developer entry level jobs - Hi All, where do I start looking for entry level jobs in North-East US ,any suggested web-sites? ... one more question:...


SQL Server 2008 : Security (SS2K8)

Linked Server using DOMAIN security fails - Hello, I have been attempting to create a linked server but have only been able to get it to work in...


SQL Server 2008 : SQL Server 2008 High Availability

cluster-sql service restarted on the same node - I have a active passive cluster.I see the following errors before the sql server got restarted on the same node. [i]Event...

Unable to configure mirroring - we are getting the following error when we are trying to configure mirroring. How could we over come this? [IMG]http://i58.tinypic.com/fuxe6t.jpg[/IMG]

Restore Job is running Successfully But the Data Restoring is happening Second Successful Run - Dear All, I have Configured Log Shipping in 2008 successfully. But Restoring is not happening properly. The thing is that i have scheduled...

All the 3 jobs are running successfully but Restoration in Secondary server is not happening - Dear All, Am using 2008, I have configured Log Shipping successfully. All the 3 jobs are running successfully but the thing is after...


SQL Server 2008 : SQL Server 2008 Administration

Low Cache hit ratio in sql server - Hi, recently i received alerts regarding for Low cache hit ratio(50%)for user database.. I verified all ways to getting solution. i...

Installed Instances are reflecting in the Local Services - Dear SQL Experts , My System is in Lan. I have Installed new instance in that ,It was successfully got installed without...

Object already exists, but can't drop it! - Hi All, My client has just started getting a problem with their SQL server. All of a sudden many many of...

Windows authentication only allows login with sysadmin role - Hi, I'm trying to set up our dev team to log in with Windows credentials, but the only security level that...

Transfer of Logins from 2k to 2K8 - Hi all, Is this feasible via the SSIS Transfer Logins task?? I've read a lot of articles talking about doing this...

2008 R2 SP2 Install fails with no messages - I'm installing SP2 on my 2008 R2 servers and it has gone well for the most part. However, I have...


SQL Server 2008 : Data Corruption (SS2K8 / SS2K8 R2)

Corruption in user Database, Unable to repair using DBCC checkdb - Hi Folks, We are receiving following message on one of the user databases and unable to repair via DBCC check DB....


SQL Server 2008 : SQL Server 2008 Performance Tuning

Improving a slow EF query - Hi All, I've been trying to speed up a query that is taking about 8 sec at the moment and runs...

how to improve performance on a query - This is the case: We have an accounting vendor package that is required to run a transaction against a table to...

How to find and fix - I am getting the following error, about once per day, and the server it is trying to connect to is...


SQL Server 2005 : Administering

Enabling LPIM and AWE - I'm attempting to enable AWE on SQL Server 2005 SP2 Standard but have read there may/may not be other items...

commit transcation - In MS-SQL Server all DML statements are: 1.Auto committed 2. Need to be committed with commit command after transaction. 3.Each DML...


SQL Server 2005 : Business Intelligence

SSRS and SSIS Deployment Process - Can anyone provide me the best practice procedures for deploying SSIS and/or SSRS from the development environment to Test and...

fail job scheduled when package fails - Hi I have one of my sql agent job scheduled and it is always success though the package execution fails. I want...


SQL Server 2005 : Data Corruption

Backup Failed SQL 2005 - Hi, I cant able to take the database full backup its says database log file is corrupted. Please find the attached...


SQL Server 2005 : SQL Server 2005 Performance Tuning

ALLOW_PAGE_LOCKS vs DEADLOCKS vs REBUILD INDEXES - Hi, I would like to know if there is a link between any of these 3 things. Specifically the following: 1. If the...


SQL Server 2005 : SQL Server 2005 Integration Services

How to Use XML Config File to Allow SSIS Pkg to Run Successfully on Development/Production Locations? - I'm new to SSIS and am working my way through a project for a client in order to get some...


Reporting Services : Reporting Services

Change alternate row color within each section - I have a report which has one drilldown for each parent group row. How do I change alternate color background...

SSRS version issue - Months ago we upgraded a SQL server 2005 to 2008 R2 reporting server, and the reports are upgraded too. I know...

Alternate Background Color in Rows - Hi, I am working on a report in the Reporting Services.  I was wondering if we can set alternate background color...

Chart Legend - I have a pie chart,which has 5 fields(each one is a slice of the pie). If one of the fields is...


Reporting Services : Reporting Services 2008 Development

Passing multivalue Parameters between report links - Hello, I am trying to pass report parameters from one report to another linked report via a text box. When a...

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

Unable to access server connecting through Tableau - Hello, I have a SQL Server 2008 R2 instance with user accounts, admins, set up for TCP/IP, enabled to connect remotely. On...


Data Warehousing : Integration Services

Weird problem with data flow task - I've got a data flow task where the source is a hard-coded parameterized query, i.e. SELECT ColA, ColB, ColC FROM TABLE_DTL WHERE LastModifiedNumericDate...

Question: How do I pervent Chinese characters from turning into ? (SSIS 2008R2) - Ok, I've been confronted by this problem during the week where I'm the only guy not on break on our...


Database Design : Hardware

Placement of SQL Server Data and TLog files in a SAN Infrastructure and Monolith Storage Environment - Hi Everyone, The reason for my post is to get others opinions, thoughts, and any facts regarding the placement of SQL...


SQLServerCentral.com : Anything that is NOT about SQL!

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

HTTPS: Perfect Forward Secrecy, SSLv2, cipher suites, wider SSL coverage, IIS version, etc. - After revamping my Firefox cipher suite list (about:config, search for tls and then search for ssl) and adding Calomel SSL...


SQL Server 7,2000 : T-SQL

Financial Calculations in SQL - Could someone please share some information and some links on writing stored procedures for the calculation of different Financial information...


Career : Certification

70-462 Trainning Kit: Cannot connect from SQL-A to SQL-CORE - 70-462 Training Kit : Cannot connect from SQL-A to SQL-Core Currently, I have set up lab environment on my lap top...

SQL Server 2014 (really 2012 R2) certification - Well it appears the MCSA SQL Server 2012 certification will have a longer lifespan since Microsoft is not going to...

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