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

Byte Me: For Every Lock

Bob Lang from SQLServerCentral.com

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

ADVERTISEMENT

ricky leeks presents
Ricky Leeks on Learning .NET Memory Management
Article 1
Avoiding Garbage Collection
 
  Article 2
Managed / Unmanaged Interoperability
Article 3
WPF and Silverlight Gotchas
 
  Article 4
Top 5 Misconceptions
Article 5
Top 5 Fundamentals of .NET
 
  Article 6
Memory Management Gotchas
Get all six articles in one FREE download.

Featured Contents

 

Stairway to SQL Server Agent - Level 9: Understanding Jobs and Security

Richard Waymire from SQLServerCentral.com

Security is a confusing topic to many, especially when it comes to understanding what rights are needed to monitor and use SQL Server Agent. This article will examine the rights and roles used for SQL Server Agent, as well as the security context requirements for jobs. More »


 

Free eBook: SQL Server Hardware

Press Release from Red-Gate

SQL Server Hardware will provide the fundamental knowledge and resources you need to make intelligent decisions about choice, and optimal installation and configuration, of SQL Server hardware, operating system and the SQL Server RDBMS. More »


 

SQL Server 2014 In-Memory OLTP Dynamic Management Views

Additional Articles from Database Journal

Greg Larsen provides a quick primer of the new Dynamic Management Views (DMVs) to help you better understand and manage your In-Memory OLTP tables and your Instances that support In-Memory OLTP tables. More »


 

From the SQLServerCentral Blogs - SSIS Performance Testing

Koen Verbeeck from SQLServerCentral Blogs

I had to do some performance testing for an upcoming MSSQLTips article and I thought I’d share the framework I... More »

Question of the Day

Today's Question (by Steve Jones):

In what editions of SQL Server 2014 are memory-optimized tables available? 

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: SQL Server 2014.

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 Hardware will provide the fundamental knowledge and resources you need to make intelligent decisions about choice, and optimal installation and configuration, of SQL Server hardware, operating system and the SQL Server RDBMS.

Pick up your copy of this great book from MVP Glenn Berry at Amazon today.

Yesterday's Question of the Day

Yesterday'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?

Answer: SELECT CAST(DECRYPTBYKEY(SaleValue) AS NVARCHAR(20)) FROM dbo.CustomerSales

Explanation:

The correct answer is: 

SELECT
 CAST(DECRYPTBYKEY(SaleValue) AS NVARCHAR(20))
     FROM dbo.CustomerSales

This assumes that the key is open in this session, but there is no need to include the actual key name in the decryptbykey call.

Ref: DECRYPTBYKEY - http://technet.microsoft.com/en-us/library/ms181860.aspx


» Discuss this question and answer on the forums

Featured Script

Search objects in Database

bpimenta from SQLServerCentral.com

Simple two step:

1.

replace <DATABASENAME> for the database name on where to search objects

2.

Replace % for the name of the object. If not the full name, leave the % in the beggining or/and at the end

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


SQL Server 2012 : SQL 2012 - General

SQL CLR Deployment Problem - Hi, I have created SQL Datbase project for CLR function and this project refers one class project which in turn...

Read response time Transaction Log - Hi We have poor performance spikes on a drive containing our log file but this is only for reads and seems...

SQL Agent Job Owner - I have read here and elsewhere that it is best practice to set the job owner to the sa account,...

Get Table Structure of Stored Procedure Output Table - To get the results of a stored proc into a table you always had to know the structure of the...

Partitions - I have a table with the following columns: [Cookie1] [int] NULL, [Cookie2] [int] NULL, [AdvertiserID] [int] NULL, [ActionID] [int] NULL, [PlacementID]...

sysarticles error while restoring - received error as Invalid object name 'sysarticles' while restoring a database. attached error screenshot . any help to resolve this pls?

How to query SQL server to know if the SQL instance is in shutdown-in-progress state ? - Hi folks, I have a requirement to query the state of a SQL instance to know if it is shutdown in...

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

Does anyone care that I passed 70-461? - Hey all, I decided to do a serious career turn about and get back into Comp Sci (which I started in...

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

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

32-bit legacy ODBC drivers vs 64-bit OS for SQL Server 2012 - Hello, After a lot of research on the web, books online, and SQL Server Central's forums, I haven't seen a clear...

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


SQL Server 2008 : SQL Server 2008 - General

Metrics to determine switch to Enterprise - Hi, I have a couple servers currently running on 2008R2 Standard edition. They're likely going to be virtualized in the near...

SQL CLR Deployment Issue - Hi, I have created SQL Datbase project for CLR function and this project refers one class project which in turn...

Is there any way to filter Severity 020 alerts for SSPI handshakes? - I configured alerts for severity alerts 016 through 025 as well as error numbers 823, 824 and 825 on our...

Duplicate Rows Withe Percentage - Hello I need to do this task, i have a table with several numeric values, and for each row i need...

Potential Locking Issue - Inserts Failing Due to Duplicate Values - I have an interesting issue. I have a very old database that I am managing that at one point in...

Deadlock - How to resolve this deadlock? Its an object lock. Both are different object but partition number shows same that is 9....

T-SQL help with specialized groupings - OK, so I figure I need to use a cursor or some advanced windowing functions, to get what I'm after...

data file movement - i need to move the data files as the database is configured in replication , how can i move to another...

Available space for a database - Hi All, I have a question about the space that is available for two particular databases that I have. Currently, Database...

How to modify default URL in SSRS 2008( Reports/Pages/Reports.aspx) to Reports/Pages/Reportviewer.aspx - We have SSRS Reports developed in SSRS 2005 with default URL as [b]Http://Hostname/Reports/Pages/Reportviewer.aspx Itempath=" [/b]". Recently upgraded to SSRS 2008...

Restore Database does not allow multiple file selections < SQL 2012 - I am running SSMS 2012. When trying to restore a database to a SQL 2012 instance, I am able to...

Deadlock occuring due to Schema Locks - Hi All, Here is the scenario, there are Parent and child table say Purchase_master and tmp_Purchase_info respectively. In a procedure(p1) i...

recursive structure. - We have a table containing a 'recursive' structure. Metadata: Parentname, Childname, A child 'points' to a parent. Starting at a topparent (level 0), I want...

Query optimization help - I have one simple query to optimize but somehow after creating couple of index this query is still having more...

Replication error : Error 14151 Severity 18 state 1 - Hi We had a problem with transactional replication yesterday whereby we started to receive the error: Replication Transaction Log Reader SubSystem agent...

Reg -XML Schema - Hi, I will get the xml file with schema using following query select * from employee for xml auto,xmlschema. I will store...

moved the recrods to column headings - Hi Team, Please help with this requriement. The result of the below query is coming as CostCentre CategoryName TypeName Number of Incident 5678...

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

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

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

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

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

monitor scripts..... - Hi smart folks, I am looking for some scripts or usp_, that give me report of tempdb uses every hours or...

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

xp_create_subdir for non-sysadmins - Hi, Is it possible for non-sysadmins to run xp_create_subdir on the Express version of 2008 R2? When researching the subject I...

Restore Failed Error - Urgent Reply Please! - Hi. I have a database backup file (.bak) taken from SQL Server 2005 Express which was running on a 32-bit Windows...


SQL Server 2008 : T-SQL (SS2K8)

Union difficulty - Hi All, I'm trying to take a portion of a stored procedure and perform a union with another table I've created...

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


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


SQL Server 2008 : Security (SS2K8)

Tools - Is there such a tool that will use the security setup from a SQL server, cross-referenced with Active Directory to...

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

SQL Virtual Network Name Change Error - I am in the process of configuring a two-node failover cluster for a migration project. We are transitioning a stand-alone...

Copy Job is not working in Log_shipping - Dear All, I have configured the Log Shipping in 2008, Backup Job is running but the Copy Job is not running , It...

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


SQL Server 2008 : SQL Server 2008 Administration

SSIS Job not able to trace in sp_who - Hi All, I am running an SQL agent Job which intern run the SSIS package which imports data to one of...

Renaming a filegroup - In one of our production databases a prior DBA made a typo when creating a new file group, does anyone...

sp_who2 command menu is set option on - Hi all, When running sp_who2, it appears one of my SQL commands is blocking but waiting on a process that is...

In Log shipping Copy Job is not Running successfully... - He Experts... I have Configured Log shipping in SQL server 2008, Copy_Job is not running and throwing an error like the Following...

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

Script to get the list of users and permissions in a database - Hello, can anyone provide a good script to get the list of all users and their permissions in a database ? I...


SQL Server 2008 : SQL Server 2008 Performance Tuning

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

Recover Data File - MDF - Hi Everybody, One of my sql server 2005 database file (mdf) has corrupted and do not have back up of...

Maintenance Plans Failing with "Failed to acquire connection "Local server connection"" - I had a massive outage a couple weeks ago on SQL 2005 x64 sp2 running on 64 bit Clustered SQL...


SQL Server 2005 : Business Intelligence

using dtutil in ssis2008 - I would like to know how to check for existing ssis package in file system for example in c drive...

loading multiple file types into one table - We have scenario where we want to load different types of files(csv, xls, txt) into one table using SSIS. How can...


SQL Server 2005 : SQL Server 2005 General Discussion

Need assistance in troubleshooting a KEY LOCK DEADLOCK - Greetings, Chasing a deadlock that has me perplexed. Here is the errorlog output of a single deadlock with trace flags 1222 and...

how to impletment sql job using sp_procoption with out waitfor delay. - HI, I have sheduled following store proc in masters USING sp_procoption, as i do not have sqlagent. q1) Can it be implemented...

Does sql job skips the secdulted time , if job takes more time to finsh than the scheduled time. - Hi, I wanted to call a stored proc, on time basis , so i have made a job in sqlserver2008r2 and...


SQL Server 2005 : SQL Server 2005 Security

How to hide objects (sp, views, tables) from a user? - Hi, I have a user that I want to give SELECT permissios to only a couple of tables. The database contains...


SQL Server 2005 : SS2K5 Replication

Can I Drop and Create SYNONYM in a replication database - Hi There, I hope this is a silly easy question for someone to answer :-) We have a replicated database and the...


SQL Server 2005 : SQL Server 2005 Performance Tuning

SQL Memory Usage - Hi, Is there any script to check or make a per hour report for memory consumed/used by SQL Server 2005 on...


SQL Server 2005 : SQL Server 2005 Integration Services

Import NULL GUID - SSIS Help needed - Hi Everyone, I am a newbie here. I am trying to import data from flat file to SQL DB using SSIS....

Get new files and folders from FTP - I need to check an ftp site nightly for new folders containing image files and download just the files and...

Need to check file name before it get process - Hi, I need help in checking the file name before i load it to staging tables. I have all the file stored...

loading data from SQL 2008 to AS400 - getting error, please help! - We are using ADO net connection for both SQL 2008 and AS400, when I am trying to insert all the...


Reporting Services : Reporting Services

Need clean glossy reports gen'd in SSRS - Hi - I am moderately experienced w/ developing & deploying SSRS reports. Can SSRS 2012 generate clean, glossy, embossed type reports? Similar to...

Report Builder 3.0 Exclusion Query based on Parameter and Field Values - Hi, I am creating a report which has a Full and Partial Parameter. If the Partial parameter is selected I want...

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


Reporting Services : Reporting Services 2008 Administration

2008 R2 and 2012 issue - Hello - I have both 2008 R2 and 2012 SSRS installed on the same PC. The 2008 R2 install is Standard...


Reporting Services : Reporting Services 2008 Development

How to get the Page Footer and Page header from Sub report - I've a main report with page header and page footer and . I'm using a sub report in that which has...

Different Images in the footer of a SSRS report - In the footer of a report I would like to display a image in first page and last page of...

Report Rendering Issue - Hello All, I have a report consisting of 2 columns that counts the number of distinct instances of a specific column: ie,...


Programming : XML

sp_xml_preparedocument - Return Value - According to the documentation sp_xml_preparedocument should return 0 (success) or >0 (failure). The following Code fails to write my error record...


Programming : Powershell

Dynamically creating / evaluating variables - I can't remember where I saw this, and can't find it now :-( I want the value of $cTime to be...

***-PSSnapin SQLServerCmdLetSnapin110 ....cannot add - Hello I have gotten part of this powershell script from the web, and first part configured by me. #Define Variables and configure...


Data Warehousing : Integration Services

.ods files - can we transfer data from .ods file to sql using ssis if yes how?

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


Data Warehousing : Performance Point

PerformancePoint query takes too long - I am creating an Analytic Grid report form and Analysis Services Cube. The report will have two dimensions on Rows, 1...


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


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


SQLServerCentral.com : Articles Requested

Rebuilding a single partition's indexes in SQL Server 2014 - How this works with an example.

Truncate and Restores - I am looking for a basic article that shows how truncate affects restores. - a table - a backup with data in...

Format results as HTML table w/ powershell - It's easy, but a reference that shows beginners how we take some data, perhaps performance or other data in a...


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


Career : Resumes and Job Hunters

Job market in Canada - Hi all, Wondering how the job market is in Canada for SQL DBAs. Which province to choose? Have looked at workopolis...

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