Blog Page

Sample Scripts

sqlsafety 0 Comments

The links to download SQL Server Express, SSMS, and the AdventureWorks sample databases can be found in a previous post here.

As promised the sample T-SQL code can be downloaded here.

Move Tempdb

sqlsafety 1 Comment

Based on Microsoft best practices it is advisable to place the tempdb on a fast and separate I/O subsystem to ensure good performance, as well as other practices mentioned on Technet here.  So it is not completely unheard of to have already installed and configure SQL only to realize after that the tempdb is located on a less than optimal drive.  This is easily and quickly rectified with an ALTER DATABASE DDL statement:


USE MASTER;
GO

ALTER DATABASE tempdb
MODIFY
FILE (NAME = tempdev, FILENAME = ‘D:\TEMPDB\tempdb.mdf’);

ALTER DATABASE tempdb
MODIFY
FILE (NAME = templog, FILENAME = G:\TEMPDB\templog.ldf’);
GO

 

The messages shows that the query complete successfully, but also notes, “The file “tempdev” has been modified in the system catalog. The new path will be used the next time the database is started.”  The reason is that in order to move the log and/or master data file the database must be offline.  Every time SQL restarts the tempdb is recreated or truncated so upon the next restart the changes are applied.  Upon restart you can see that the data file is now on the new drive and in the new folder:

image

BUT WAIT!!!  It is important to remember that when SQL restarts it recreates the data and/or log file in the specified location, but IT DOES NOT delete the original data/log file.  It is important to delete this file to insure that it does not take up space and lead to confusion.

What is a SQL Server Agent Proxy Principal?

sqlsafety 0 Comments

If you examine the SQL Server Agent Proxy window you will notice a separate window where “Principals” can be assigned.  The question comes up “”What exactly is a proxy principal anyway?”

A  proxy is created to be mapped to a credential in order to allow a specific agent job subsystem the ability to run under the security context of the credential rather than in the context of the agent service account.  This provides the ability to escalate the security context of a single task in a job rather than having to escalate the permissions of the agent service account, but what is a proxy principal? 

SQL Login can be granted permission to manage SQL agent jobs by adding them to one of three fixed database roles in the MSDB database, technet outlines these roles here:

SQLAgentOperatorRole

SQLAgentReaderRole

SQLAgentUserRole

Since proxies are created to be used with SQL Server agent job tasks you might think that by assigning a user to these fixed database roles would allow the user to use in the appropriate task subsystem, but think again. 

To demonstrate this I have created a credential called SQLCredential that maps to a local user:

imageI  have also created a proxy for the Operating System (CmdExec) subsystem that maps to the SQLCredential:

imageIn the same instance of SQL I created a user, SQLAgentUser, and assigned it to the msdb fixed server roles SQLAgentOperatorRole, SQLAgentReaderRole, SQLAgentUserRole:

imageConnecting to SSMS as  SQLAgentUser and creating a job you would think that the newly created proxy would be available for an operating system(CmdExec), but guess again!

imageAs you can see the Run as option is disabled.  Despite the permissions provided in the 3 fixed database roles none of the permit the use of the proxies.

To allow the SQLAgentUser to assign a proxy they must be assigned this permission in the proxy principal.

From within the proxy window within the Principals select the Principal type of SQL Login and select the SQLAgentUser. 

imageOnce back in SSMS as SQLAgentUser and creating the job using the Operating system(CmdExec task the Proxy is now available:

image

It is possible to allow the all users assigned to a specific msdb fixed database role the ability to access a proxy by setting the role as a principal in the proxy:

image

#SQLSat255 Dallas Countdown!!

sqlsafety 1 Comment

We are just weeks away from the launch of SQL Saturday Dallas,THAT’S RIGHT!!  October is almost over!

If you are still sitting the fence as to whether attend let me help you make up your mind.

  1. Scott Klein Running SQL Server in a Windows Azure Virtual Machine – Deep Dive
  2. Kathi Kellenberger Women in Tech Panel
  3. Chaitanya Khaladkar Real-Time Analytics with Power View and SSAS Tabular in DirectQuery Mode
  4. Andy Leonard Hacking the SSIS 2012 Catalog
  5. Randy Dyess Why You Should Achieve Certification
  6. AJ Mendo Without A SQLTrace-Getting To Know Extended Events
  7. Erin Welker A Systematic Approach to ETL Performance Tuning
  8. John Sterrett Table Partitioning: the Secret Weapon for your Big Data Problems
  9. Grant Fritchey Common Problems in Backup and Recovery and How to Handle Them
  10. Mike Hotek SQL Server 2014 – In memory OLTP Development

I hope to see you there!

PowerPivot Virtual Class @Sungardps

sqlsafety 0 Comments

On 11/8/2013 beginning at 2:00PM EST I will be presenting a virtual class, Introduction to PowerPivot.  This 2 hour session will cover the history and background of PowerPivot as well the fundamentals of creating and maintaining PowerPivot projects.  You can sign up for this session by browsing to the Sungard Public Sector’s training page, https://train.sungardps.com/stc/customer/psciis.dll?mainmenu=customer, and searching the PowerPivot.  This session will be the first in a series meant to prepare attendees to integrate this FREE and powerful technology in their reporting and business intelligence solution.

You can find the outline of this course on my blog in the Upcoming Events tab here.  Hope to see everyone there!

MERGE Error UPDATE or DELETE Same Row More Than Once

sqlsafety 5 Comments

When running a MERGE statement I have received the error:

Msg 8672, Level 16, State 1, Line 1
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
” several times and am always forced to run back through the query logic to see what has occurred.  Reading the message it is fairly self explanatory as to what has happened.  A row in the target table will be affected more than once from within the MERGE statement which causes the query to fail.

To demonstrate this review the T-SQL code below that will create two tables and populate them with several rows:

USE tempdb;

CREATE TABLE #employeetarget(
FirstName  VARCHAR(50),
MiddleName VARCHAR(50),
LastName   VARCHAR(50),
JobTitle   VARCHAR(25));

CREATE TABLE #employeesource(
FirstName  VARCHAR(50),
MiddleName VARCHAR(50),
LastName   VARCHAR(50),
JobTitle   VARCHAR(25));

INSERT #employeetarget
VALUES(‘David’, ‘M’, ‘Dye’, ‘Da Boss’),
     (
‘John’, ‘A’, ‘Doe’, ‘Nobody’),
     (
‘Mike’, ‘B’, ‘Smith’, ‘Lazy’)

INSERT #employeesource
VALUES(‘David’, ‘M’, ‘Dye’, ‘Da Boss’),
     (
‘David’, ‘M’, ‘Dye’, ‘Unemployed’),
     (
‘John’, ‘A’, ‘Doe’, ‘Nobody’),
     (
‘Mike’, ‘B’, ‘Smith’, ‘Lazy’)

 

The #employeetarget table will need to be updated based on the records in the #employeesource table.  If the FirstName, MiddleName, and LastName of the employee are the same then the JobTitle in the target should be updated to the JobTitle in the source.  If the FirstName, MiddleName, and LastName do not match then it is a new employee and should be INSERTed into the target.  The below MERGE statement will accomplish this, but will also raise the aforementioned error:


MERGE
#employeetarget AS t
USING
#employeesource AS s
ON t.FirstName = s.FirstName
AND t.MiddleName = s.MiddleName
AND t.LastName = s.LastName
WHEN MATCHED THEN
UPDATE SET
 t.JobTitle = s.JobTitle
WHEN NOT MATCHED THEN
INSERT
VALUES
(s.FirstName, s.MiddleName, s.LastName, s.JobTitle);

To understand the error review the results of joining the target and source table together using the same criteria that is used in the MERGE:

SELECT t.*,
             
s.JobTitle
FROM #employeesource s JOIN #employeetarget t
ON t.FirstName = s.FirstName
AND t.MiddleName = s.MiddleName
AND t.LastName = s.LastName

image 

Despite there being only one row in the target for David M Dye the join criteria would result in that row being updated twice. To make matters more confusing breaking the logic out into a separate UPDATE statement and executing that alone succeeds, however querying the target and source again show that no change was made:

 UPDATE #employeetarget
SET JobTitle = s.JobTitle
FROM #employeetarget t JOIN #employeesource AS s
ON t.FirstName = s.FirstName
AND t.MiddleName = s.MiddleName
AND t.LastName = s.LastName

SELECT *
FROM #employeetarget t

image

Despite the statement running successfully the JobTitle remains the same?  The answer is yes the JobTitle was updated and this is also verified in the messages tab:

image

Neither temporary table has defined ordering with a primary key or clustered index so both tables are a heap.  Creating a clustered index on the JobTitle column and re-running the query returns different results:

CREATE CLUSTERED INDEX clx_name
ON #employeesource(JobTitle DESC);

UPDATE #employeetarget
SET JobTitle = s.JobTitle
FROM #employeetarget t JOIN #employeesource AS s
ON t.FirstName = s.FirstName
AND t.MiddleName = s.MiddleName
AND t.LastName = s.LastName

SELECT *
FROM #employeetarget t

image

 

Despite the ambiguity of the UPDATE and the join criteria the statement completes successfully, while the MERGE fails, but the resultant change of the UPDATE is inconsistent and can be partially based on the table ordering, the only way to insure ordering is to use ORDER BY.

So how can a MERGE statement be changed to insure that a record will only be affected once?  The example that I have given is very difficult since there is no flag or date column that shows the most recent record to identify to update the target.  To make this a bit more realistic let’s recreate the target and source tables to include a column, ModDate, that is used to show when a record was modified which will in turn be used to update only the most recent record.

–Create tables with a modified date
USE tempdb;

DROP TABLE #employeetarget;
GO

DROP TABLE #employeesource;
GO

CREATE TABLE #employeetarget(
FirstName  VARCHAR(50),
MiddleName VARCHAR(50),
LastName   VARCHAR(50),
JobTitle   VARCHAR(25),
ModDate        DATE);

CREATE TABLE #employeesource(
FirstName  VARCHAR(50),
MiddleName VARCHAR(50),
LastName   VARCHAR(50),
JobTitle   VARCHAR(25),
ModDate        DATE);

INSERT #employeetarget
VALUES(‘David’, ‘M’, ‘Dye’, ‘Da Boss’, ‘1/1/2012’),
     (
‘John’, ‘A’, ‘Doe’, ‘Nobody’, ‘1/1/2012’),
     (
‘Mike’, ‘B’, ‘Smith’, ‘Lazy’, ‘1/1/2012’);

INSERT #employeesource
VALUES(‘David’, ‘M’, ‘Dye’, ‘Da Boss’, ‘1/1/2012’),
     (
‘David’, ‘M’, ‘Dye’, ‘Unemployed’, ‘1/1/2013’),
     (
‘John’, ‘A’, ‘Doe’, ‘Nobody’, ‘1/1/2012’),
     (
‘Mike’, ‘B’, ‘Smith’, ‘Lazy’, ‘1/1/2012’);
GO

Identifying the most recent record can be done using ROW_NUMBER():

SELECT ROW_NUMBER() OVER(PARTITION BY FirstName, MiddleName, LastName ORDER BY ModDate DESC) AS RowNum,
     
FirstName,
     
MiddleName,
     
LastName,
     
JobTitle,
     
ModDate
FROM #employeesource

image

The above query can be incorporated into a common table expression that is used in the MERGE statement to only return the source rows where the RowNum = 1

WITH cteJobTitle
AS(
SELECT ROW_NUMBER() OVER(PARTITION BY FirstName, MiddleName, LastName ORDER BY ModDate DESC) AS RowNum,
     
FirstName,
     
MiddleName,
     
LastName,
     
JobTitle,
     
ModDate
FROM #employeesource)
–Succeeds
MERGE #employeetarget AS t
USING cteJobTitle
AS s
ON t.FirstName = s.FirstName
AND t.MiddleName = s.MiddleName
AND t.LastName = s.LastName
WHEN MATCHED
AND RowNum = 1
THEN
UPDATE
SET
t.JobTitle = s.JobTitle,
  
t.ModDate = s.ModDate
WHEN NOT MATCHED THEN
INSERT
VALUES
(s.FirstName, s.MiddleName, s.LastName, s.JobTitle, GETDATE());

SELECT*
FROM  #employeetarget

The results show that the JobTitle was updated to the most recent, Unemployed, based on the ROW_NUMBER:

image

SSIS and Stored Procedure OUTPUT Parameters

sqlsafety 2 Comments

I was recently asked to help configure an execute SQL task that would call a stored procedure and capture an output parameter in an SSIS variable.  Although I had done this before and it seems straight forward it took me quite some time, as well as opening up most every package I have ever created to find an example, to complete this.  Hopefully this post will save others time, as well as myself.

To demonstrate this first create a stored procedure in the AdventureWorks2012 database that uses an OUTPUT parameter:

 

USE AdventureWorks2012;
GO

CREATE PROC ssisoutput
@count INT OUTPUT
AS
SELECT
@count = COUNT(*)
FROM Person.Person;
GO

BACKGROUND

This is obviously a simplified example so to provide a better context let me describe the task I was confronted with.  The stored procedure that was called was running several transactions, inserts and updates, and the output parameters were recording the number of records that were affected.  The output parameters were then captured in SSIS parameters that were used to dynamically generate a send mail message that notified all operators of the number of records.

PACKAGE

Create a package called OutPutParameters.  Within the package create a variable called countout with a data type of Int16 and a package level scope

METHOD 1 OLEDB DECLARE

From within an SSIS package drag and drop an Execute SQL task onto the control flow design pane.  In the Execute SQL task configure the connection to use the instance where the AdventureWorks2012 database containing the stored proc resides:

imageLeave the SQLSourceType set to DirectInput and in the SQLStatement type the following query:

DECLARE @countout INT
EXEC
ssisoutput @count = @countout OUTPUT
SELECT ? = @countout

image

The above T-SQL statement first declares a variable, @countout, that will be used to hold the output parameter value.  The stored procedure is executed and the output parameter value, @count, is passed to the variable @countout specifying OUTPUT.

With the connection and statement being configured now configure the Parameter Mappings of the task mapping the countout variable to the output parameter:

 

imageNotice that the “?” in the SQLStatement acts a a placeholder for the parameter.  The Parameter Name uses the 0 based indexed value of the parameter placeholder.  Since there is only one one parameter placeholder in the query, “?”, the value is set to 0.

METHOD 2 OLEDB ASSIGNMENT

This method is almost identical except that the SQLStatement does not declare a variable, but rather directly assigns the output parameter to the parameter placeholder.  The ONLY difference is the SQLStatement:

EXEC ssisoutput @count = ? OUTPUT

imageThis is obviously a bit more efficient as there is no need for variable declaration and assignment.

METHOD 3 ADO.NET

The question often comes up in regards to being able to call a parameter by name rather than indexed ordinal position as it appears in the SQLStatement property.  Unfortunately this is only possible using an ADO.NET connection manager.

Drag and drop another execute SQL task onto the control flow design pane and configure it to use a new ADO.NET connection manager.

imageIn the SQLStatement property put in ONLY the stored procedure name ssisoutput, DO NOT INCLUDE EXEC, and configure the IsQueryStoreProcedure property to True.

image

In the Parameter Mappings window map the countout variable to the output parameter and use the Parameter Name @count, the name that is defined in the stored procedure.

image

Additional information on parameters in Execute SQL tasks can be found on Technet.  The sample package reference in this post can be downloaded here.

 

SSISEndtoEnd


Hit Counter provided by Sign Holders