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

Due Diligence

I often talk with people about building their brands and finding a way to ensure they are a highly desirable employee. One of the ways that I think people can do this is with a technical blog about their career. Having a technical blog allows someone to show off their skills in a particular area. The blog doesn't have to be ground breaking work or extremely innovative solutions to complex problems. While employers need those people, they also need people that do solid work every day on regular problems.

An interview isn't a great way to find good employees. Many of us have had experience with either (or both) sides of the interview table and realize that interviews aren't necessarily that helpful. If we bothered to track the impressions we make of candidates and compare that to the actual work they accomplish over the first year or two, I suspect we'd find that we have no evidence that were making great decisions. The success of employees seems to be a bit hit and miss.

A blog, however, provides the employer with a bit more confidence that a person can handle the job they are hired for. A blog takes time, and across months (or years), it can show quite a bit about a person's knowledge and skills. It allows hiring managers, and co-workers that may interview a person, the ability to perform a bit more due diligence and investigation into someone's skills than an interview provides. It's much more of a representative look at a person than what they say or write on a resume.

I know that it isn't a perfect solution. People plagiarize posts and copy from Books Online and more, but the Internet helps here. Search a few of their paragraphs and you might catch plagiarizers easily. After all, someone that wants to copy posts to avoid work, probably has a few other tricks in their bag to avoid doing other work for you.

Think about starting a blog today and giving potential employers a way to learn more about you.

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 ( 2.4MB) podcast or subscribe to the feed at iTunes and LibSyn. 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
5 Worst Days as a DBA

5 Worst Days in a DBA’s Life – Part 2

The DBA Team return in The Girl with the Backup Tattoo. When a crazy DBA tampers with his own backups, even the DBA Team need to call on some extra help to fix the damage. Will they succeed? Read the new article now.

SQL Monitor

An accidental DBA? Try SQL Monitor

Use the full product free trial to get easy-to-understand insights into SQL Server, and get suggestions on how to solve any problems you find. Begin your free trial.

SQL Backup and Restore eBook

Free eBook: SQL Server Backup and Restore

With the tools, scripts, and techniques in this free eBook, you will be prepared to respond quickly and efficiently to disaster, whether it's disk failure, database corruption, or accidental data deletion. Download the free eBook.

Featured Contents

 

Partitioning Data in a Multi-tenant Data Warehouse Solution

Sean Woehrle from SQLServerCentral.com

Part two of our series looks at the partitioning strategy to separate customers' data, providing security and performance. More »


 

Open Call for Editorials

Steve Jones from SQLServerCentral.com

Steve Jones is putting out an open call for guest editorial pieces in the spring of 2014. If you're interested, read on. More »


 

Microsoft Azure SQL Database Security - Firewall Configuration

Additional Articles from Database Journal

Marcin Policht reviews security related challenges of Microsoft Azure Software as a Service-based SQL Database, focusing in particular on the SQL Server and database-level firewall access control functionality and methods that can be employed to implement it. More »


 

From the SQLServerCentral Blogs - Read Flat File Contents to Variable – Write Variable Value to Flat File

Sam Vanga from SQLServerCentral Blogs

When working with SSIS, you’ll often find the need to read the contents of a flat file to a variable,... More »


 

From the SQLServerCentral Blogs - From Zero to Pull Request with Git on Windows

nycdotnet from SQLServerCentral Blogs

I wrote a guide about using Git and GitHub for "Windows people". This is the guide I wish was available when... More »

Question of the Day

Today's Question (by Steve Jones):

I have created a certificate for the login/user Sales1 as follows:

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

I have also created a symmetric key that has been used to encrypt some sales data as follows:

CREATE SYMMETRIC KEY SalesSymKey
 WITH ALGORITHM = AES_128
 ENCRYPTION BY CERTIFICATE Sales1Cert

I now want to give Sales2 access to decrypt the data encrypted by the SalesSymKey symmetric key, but I don't want them to have access to other keys that are encrypted with the certificate Sales1Cert. What two things should I do?

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

Tribal SQL

SQL Server Execution Plans shows you what's going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by SQL Server, including: Which indexes are being used, and where no indexes are being used at all. How the data is being retrieved, and joined, from the tables defined in your query. How aggregations in GROUP BY queries are put together. Grab your copy today from Amazon!

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

True or False: You can run SQL Server in your existing data center and store data files for a database in the Azure cloud storage?

Answer: True

Explanation:

This is true. Starting with SQL Server 2014, you can store your data files in Azure for a SQL Server running on your premises.

Ref: SQL Server Data Files in Windows Azure - http://msdn.microsoft.com/en-us/library/dn385720%28v=sql.120%29.aspx


» Discuss this question and answer on the forums

Featured Script

The dynamic unpivoter.

John Corcoran from SQLServerCentral.com

If you're ever stuck in a situation where you want to unpivot a table even a temp table this SP will solve your problems.

The sp takes 3 arguements.  The first being the name of the table that you want to unpivot, an optioinal second paramenter for the tableSchema.  By default the SP will use the dbo schema.  Finally, the third parameter is an output paramater to capture any errors.

example: exec [usp_dynamic_unpivot] '#test','dbo',''

There is only one prerequisite.  The table you want to unpivot must have primary key(s).  If not the SP will have no idea what to pivot on.  It needs the primary key to normalize the table.  The output is a table with the primary key(s) as columns and two additional columns called columnName and value.  The columnName column will contain all the pivoted column names and the value column will contain all the columnName values.

Since a null value can't be unpivoted all nulls are translated to a blank string.  Also, since the source of the data in the value column could be any datatype all datatypes are translated to a varchar(255) string.  Simply, update the SP if you need a longer string value.  Obvioulsy, you will not be able to unpivot a text, image, xml datatype datatype.

So, if you're ever stuck with having to compare a wide pivoted out table between two databases, simply run the unpivoter on each table, store the results in two temp tables.  Then you can simply join the two tables on the primary key and list out all the diffs.

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

Question About online data - front end = VB.net desktop app frame work 4.0 language = VB database = sql 2012 (express edition) for client end Its not web-front...

Microsoft SQL triggers on columns - Dear All I have one table namely consumer with approx 50 columns. I have created one same table with audit prefix including...


SQL Server 2012 : SQL 2012 - General

Not compatible witih version of Oracle client in Web Service Task within SSIS - I have a web service that was given to me and this web service calls a procedure in Oracle that...

Partial Match / Duplicates - Hi, I'm trying to figure out values that match partially or are duplicates, but it is supposed to be a row...

CDC (Change Data Capture) is not capturing data correctly - Hi, I am using SQL Server 2012 and to me a part of data captured by CDC is not making sense. I...

SQL Server 2012 studio multi-language support - Hello Dear DBA colleagues, What's the best way to change SQL Server 2012 management studio default language to spanish/portuguese ?, we are...

Database size from Backup file - Hello Guys, Can anyone please help me in finding the database size from the backup file. I have SQL 2012 backup file,...

Availability Group Failover Stops Working After First Failure. - Hello- I've setup a two node Cluster Server (non-shared storage) with a file sharing witness. I'm testing some of the different...

Deployment Failure Heirarchy - Working on analysis services tutorial where i am modifying measures and my heirarchy and this is my error message. Warning 1 Dimension...

Please create a master key in database of open master key in the session before performing this operation. Error 15581 - I really could use some guidance on this issue. Background.... Windows somehow got corrupted and wouldn't come back up. We did a...

SSIS dtexec reurn code 5 error in cluster - Hi, I am facing issues and unable to resove it. We made SSIS package in dev environment in windows 2008R2...

Production issue related to a rollback - Hi, I have a problem with a production server that is causing considerable locking and performance degradation. It all started the...

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

Webservices Deployment - Hi, Please some one let me know how to deploy web services on local machine. I created self certificate in IIS...

error: 0 - The token supplied to the function is invalid - Hi, I have multiple sites trying to communicate with a SQL Server 2012 Express database at another remote site. At one...


SQL Server 2012 : SQL Server 2012 - T-SQL

Need assistance with making a sproc SQL Injection proof - Hey guys, its beena while since I've been on a team where there is a web app so I'm having...

How to check column name in case sensitive? - Hi Friends, I have a table called SrcReg which is having a column name called IsSortSeqNo smallint. I am mapping this...

Convert Bigint time to datetime - Hi, How to convert bigint time to datetime (CST) Declare @MyBigIntTime BIGINT =1397750400000 Thanks, PSB


SQL Server 2008 : SQL Server 2008 - General

Bulk Insert Issue - Hi, I am trying to import CSV files into my table I have three columns in my table. TableName : Keys key nvarchar(50) English nvarchar(4000) Spanish nvarchar(4000) My...

using sp_MSforeachdb that will include DB name in the output - whats the syntax to run sp_MSforeachdb against all databases that will inlude database name In the output for: SELECT Count(*) Count_in_TableName FROM...

Change Date Format - I have a SQL query for SCCM that is pulling from an Active Directory System Discovery (v_R_System table). This conversion...

How to trace job id using sql server profiler - Hi....guys, We are running Stored procedure using SQL Server Agent. We need to trace job id of particluar SQL Server Agent job...

SQL Server connectivity Issue - Hello I got some strange error while connecting SQLDatabase through SSMS. I am able to connect to the Database when i remotely...

Row based log files - Hello. I want to log every rows in a table, like a versioning. For instance one user came and changed...

Log drive issue - Hi, My log drive for db's are increasing. Also, I ran the DBCC SQLPERF (logspace) which it is showing the log file...

Any DMV that can identify start time of currently running query within stored procedure - Hi, A stored procedure may have multiple lines of T-SQL code and one single line of SQL could be taking long...

Table size not reducing after large data delete and index rebuid - Hi I have a table with 160GB data size. To reduce size, I set the values of a varchar(max) column to...

Add Reference to SSIS 2008 package - I need to add a dll reference to my 2008 SSIS package for a script task that I'm doing. When...

Dedicated SQL Server instance for tempdb performance reasons - This question is about a dedicated SQL Server instance with the main reason being performance issues related to tempdb. Our company...

Find Indexed LOB columns - Is there a query to find the LOB/BLOB columns which are indexed I can get he query to give LOB/BLOB columns....

create procedure - hi guys my sql code so delete from numbers declare @i as int set @i = 3440000 while(@i < 3442047) begin insert into...

Instance failover failing in Cluster - I am getting the following error when I am trying to failover an instance to another node: The operation failed because...

help me for process string - hi I dont speek english well I Want help for send sms I want user saved parametrs for send Example: Save information you with [u]FieldName[/u]...

Random fill. - Two tables, First table has one numeric field from 1 to n (consequtive). (thousands of rows). And one or more columns to hold...

Audit report - Hi , i need to add column login type in the output as per the below script , can any one help...

Transaction replication lock issue - Hello team, I setup transaction replication between 2 servers and facing locks issue where when replication is running no one...

SQL script to LOOP in and find sequence gap on Unique column - Need help !!! Example of table structure : CREATE TABLE [dbo].[MaintenanceTable]( [TableName] [varchar](50) NOT NULL, [Note] [varchar](250) NULL, [MaintenanceOrder] [int] NOT NULL, CONSTRAINT [PK_MaintenanceTable] PRIMARY...

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

Need Query for Problem - [code="sql"] create table #sample ( product varchar(100), Price varchar(100) ) insert into #sample values ('Pen',10) insert into #sample values ('DVD',29) insert into #sample values ('Pendrive',45) insert into...


SQL Server 2008 : T-SQL (SS2K8)

Merging pseudo-duplicate records - Here's an interesting sql challenge that I haven't encountered before. We have a data warehouse staging database in which we...

Outer Apply within an Outer Apply - Can't seem to get the syntax correct. Maybe there is a better way to do this. I'm trying to put an...

split timedate range at intersection - Hi Guys Using SQL Server 2008 R2 I'm trying to create a report and chart for a a manufacturing resource's activity...

Get the Start and end date based on a criteria. - Hi All, I have a scenario where i need to get the starting and ending date time based on the crieteria....

How to create a temp table in a procedure rather than usinh INTO #temptable - Hello Please., I am having a quick question to automate something, we have 10 procedures in Sql server 2008 R, each procedure...

Spatial, lines crossing - Hi ... this is what I want to do to thousands of lines in one table .... find all lines with CROSSING...


SQL Server 2008 : SQL Server Newbies

Sorting - Hi, this is going to be my first time posting in this forum and I'm currently studying SQL at the...


SQL Server 2008 : SQL Server 2008 Administration

Migrating Reports and Subscriptions to another server - Hi, I have looked around and it doesn't seem possible, at least through a GUI but I'm going to ask anyway....

Public role has execute access on sp_addlinkedserver - Public role has execute access on sp_addlinkedserver in master db..(This is on SQL 2005 servers std/enterprise edition).. Is this default behaviour...

SP redundant call - Hi Experts, How to Check the stored procedures to find redundant calls within it? Thanks in Advance.

SQL Server 2008 on windows 8 - Hi Experts, I want to install SQL Server 2008 Enterprise edition 32 bit on Windows 8 64 bit, I am not...

DROP USER or sp_dropuser - Hi I need your help. I cannot find the similar question so open a new topic. I want to drop all...


SQL Server 2005 : Administering

CAL Licencing - Our MI department is currently looking at a Reporting Services 2005 solution. In the first instance they want to licence...


SQL Server 2005 : Business Intelligence

70-467 BI exam - how do I prepare for this? - Hi Plan is to take the 70-467 bi solutions exam. But very disappointing to see that no official guides or kits for this...


SQL Server 2005 : SQL Server Newbies

How to select results from column that groups results into different years - I have a table that stores what is essentially the value of each sale a rep makes. All the sales...


Reporting Services : Reporting Services

Install SSRS 2012 with SQL Server 2008r2 - We have sql server 2008r2, and reporting services 2008r2. My question is simple. Can we upgrade reporting services to 2012...

ssrs text at last page of the report - In my report i am using one tablix and one text box. Text box always bottom of the last page,...


Reporting Services : Reporting Services 2008 Administration

Report Model Changes - How do I the DiscourageGrouping attribute for a field? - I have a field 'comments' that is returning an error when I drag and drop it to report builder that...


Reporting Services : Reporting Services 2008 Development

Subreport left padding not working - I have developed a report which contains a matrix followed by a list object. Within the list object I have a...

Custom code erroring out - I created a public function under custom code in an SSRS report, but when I run it, it returns the...

How to convert nvarchar to date data type? - I have to convert nvarchar to date data type. and fetch the week number of that particular month from the...

How to make the report to display week wise data - Hi, I want my report to fetch data from Financial year starting to till date. i.e Apr 2013 to till date. till date...


Programming : General

No Value Given For One or More Required Parameter oledb to Excel - In order to avoid the 255 character limitation in Excel and SSIS I have written a VB.Net SSIS script to...

Retrieve data from SQL stored procedure in MS Excel - I have been searching this forum and the web to find the answer for this question, but no luck. I...


Programming : XML

Building XML and controlling Nodes - Hello comunity I have create TSQL query to output XML results, like that: SELECT top 1 c.fdata, c.fno, c.nome, c.morada, c.local, c.codpost, ...

Encoding of FOR XML results - What is the encoding of the XML resulting from a FOR XML query? Can I force an encoding?


Programming : Powershell

Concat hard value to each row of Hash Table - I'm a newbie to Powershell and this one is driving me nuts. I have a document with many many rows...


Data Warehousing : Strategies and Ideas

Global BI Architecture - Im in the process of starting to think about building a Data warehouse for out BI needs in the UK....


Data Warehousing : Analysis Services

MDX functions for rolling time periods - Hi All: I've looked on the web and found many examples of rolling time periods that suggest doing one of the...


Database Design : Design Ideas and Questions

HIPAA Compliant Architecture - Hi, I am developing a Medical Billing Software and should be HIPAA complaint. My current architecture is using seperate database for...

Audit approach for performance tunning - we do have requirement of tracking insert,update and delete action of tables with tracking userid. two approach is there. 1) [b]creating single...


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


SQL Server 7,2000 : Administration

xp_smtp_sendmail - Emailing a proc - I have xp_smtp_sendmail working fine with a simple job already. I now want to run stored procedure that spits out a...


SQL Server 7,2000 : Strategies

Batch Delete is Slow :angry: - Batch Delete is Slow I am deleting some 200000 records from 5000000 rows. set rowcount 10000 while begin Delete from mytable where mytime<=123456 if...


SQL Server 7,2000 : T-SQL

How to find PATINDEX for [ - Hi all, How can I find the character position of the first instance of the character [ in a text string. The...


Career : Certification

Formatting of SQL statements on exam - I'm getting ready to take my MS 70-461 exam and was wondering if the questions they ask that contain SQL...

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