Painless Parameters

 "Hey, can you remove John Smtih's email from the ETL process notification and put Julie Jones?" My coworker asked as he caught me in the hall.

While developing the dozens of ETL processes for our COVID response, I had added a Send Mail feature for both success and failure, so the team knew, immediately if the jobs succeeded or failed.  

In the height of pandemic response, tidiness wasn't our top priority.  Come to think of it, neither were family time or sleep. Our operational tempo was such that I had hard coded the email addresses.  After all, I was changing the SSIS packages several times a month,  at least , as the fluid requirements changed.

Now, as the pandemic's intensity waned, I decided that NOW was the the time to do it right.  

As any programmer knows, a professional doesn't hard code items that change frequently.  The way we use SSIS, if something changes, we're usually remapping fields, changing logic, or changing database connections.  So we've not had much  call for creating parameterized variables in SSIS.

Here's How I pulled it off.

• First, I created variables to hold the parameters.
• Right click in a blank portion of the control flow window, and select variables 
•  I created variables to hold the parameters I wanted to pass. In my example, I am passing the To Email Address, Subject and body
• I select the data type as String. 

• Note: I provided default values, so that when the package is deployed, I don't have to mess with it.
• Second, I configured my Send Mail Task to use the variables.
• First I edit the Send Mail Task
• I go to Expressions
• I add the three variables to the three properties I want to configure
  
•  then I save and build the SSIS and deploy
  

Critical Note:  Copy your Variable Names.  You'll need them later.  SSMS does not populate them for you.

• Next, create your SQL Agent Job in SSMS
• When defining the Step correlated to your SSIS package, go to "Set Values" tab
• Under Properties, put the variable name in the Property Path column, and the value you wish the variable to be set to in the Value column.  
• Note, formatting of the variable name is as follows
• \package.Variables[Variable Name].Value

And you're done!

Left overs

A Hungry Squirrel stealing bird food.

If you are migrating a database from one database to another, you want to make a full backup right before shutting the database down.

Here are some things we don't want.

1. We don't any users modifying the database once we start the process.  Imagine a user adding a record as you back up the database.  The database won't contain that record, and now it's lost.  That's bad.
2. We don't want to throw away the database on the source server until we are sure the destination is up and working. 

What we do is 

• Set the database to single_user mode.  This prevents anyone from modifying anything.
• Take a full backup of the database
• Restore the backup to the new server
• fix the orphaned users.

I did this recently and left the source database in Single_user Mode.  This caused the backups to fail.  

This script fixes the problem. Note:  I always do this remotely logged into the database server.

use olddb
Alter Database olddb set single_user with rollback immediate;
backup olddb to disk ='d:\olddb.bak' with copy_only
alter database olddb modify name =olddb_Migrated;
alter database olddb_migrated set multi_user;
use master;
alter database olddb_Migrated set offline;

Denying Reality

So I moved a database from an instance to another server.  Everything seemed to go right, until the next morning, when the developer who manages that system couldn't create a table.

He was in the db_owner role.  The db_onwer role can do pretty much anything they want to a database.  Except he couldn't.

I manually granted him "create table" permissions, and it still didn't work.

I used

 select IS_ROLEMEMBER('db_owner','UserName');

and I verified that he was in the db_owner role.  I then ran

 execute as user ='UserName' SELECT * FROM fn_my_permissions (NULL, 'DATABASE') order by permission_name ;  

That listed every permission he had, and Create Table was not one of them

SELECT p.[name] [User], s.state_desc, s.permission_name 

FROM [sys].[database_permissions] s 

  JOIN [sys].[database_principals] p 

    ON s.grantee_principal_id = p.principal_id AND

   s.class = 0 

ORDER BY [User], s.state_desc ,s.permission_name ;

I found that someone (probably me around 13 years ago) had sent explicit deny permissions on the public role.

A deny is saying "I don't ever want anyone in this role/group to ever access this."  He never noticed it before on the instance because he was a sysAdmin..

The solution was to revoke the permissions.  A Revoke is a removal of a permission record.  The permission record can be a grant (allows access) or a deny (prevents access).  If no permission record exists, access is denied.

to revoke, you use the form

revoke CREATE TABLE to public

Restoring Your Rights

After a hard day shredding Toliet Paper, sometimes you just need to cuddle with your stuffed eyeless frog.

I had one of those days.  One of those days where everything goes wrong, like the one Elvis sang about?

I wasn't feeling tired and not well.  I was working with a user to see why he couldn't access one of his databases, and I clicked on a group which I THOUGHT pertained to our web team.  I saw this group had sysadmin permission.  The web team shouldn't have sysadmin, so I removed the permission and clicked apply.

Then I realized, with that all-too-common sinking feeling that I had modified the wrong group.  I had removed administrative access from the dba group.   A former boss of mine once told me one 'uh oh' was worth a 1,000 'attaboys.''  This was a big 'uh oh'.

Since we disabled the SA account, as is the best practices, I couldn't log in with this super-admin account and fix my blunder.

So here's what I did.

Services

• I logged into the server
• I started the MS SQL Configuration Manager
• I clicked on "services" and clicked properties 
• I added a "-m" as startup parameter.  This starts the service in Single_user mode (we asked in interviews why one would want to use Single_user mode.)
• I stopped the SQL Agent job
• I restarted the SQL Service
• I ran SSMS as admin and then just checked the box.  
• I removed the -m and restarted the SQL Service and started the Agency service.

Note:  SSMS will need to be restart after you do this for it to realize your change in creds.

Re-start  the Service 

Start-Up Parameters

Finding My Identity

My son, the toilet paper mummy

Microsoft SQL Server has a wonderful property called Identity.  This is similar to the auto-number field in Microsoft Access in that it creates a unique ID for each row inserted into the database.

I had a user come to me and said "We need the table to generate the ID automatically, but it's not doing it.  Can you fix it?"

Sure, I'm a DBA. I can fix anything.  Or at least, I will create an approximation of fixing close enough that you don't know the difference.

The data had been imported awhile ago from an Access database. So the existing data had id's assigned.  Typically, Identity columns start at one and increment by one whenever a row is added.

Luckily, SQL identity columns allow you to set the seed (aka the starting number) and the increment value (so you can change the increment interval).  I could find the seed by doing a Max(ID) +1.  You can insert into an Identity column, but you can't update an identity column.

I thought about creating a new Identity column and copying the ID  values into it. 

Problem one, by default, one cannot insert into a column that is designed to be filled automatically.  It's just not done.  I learned that SQL allows us to ignore that constraint.  by using SET IDENTITY_INSERT ON.  

When I tried to update the values in the new Identity column, I learned that YOU CAN"T UPDATE and Identity column. 

My solution was to create a script that created a new table with our identity value and insert from one into the other.

 The process is comprised of four steps

1. Script the existing table to your query window
2. Find the seed by finding the maximum ID and adding one. (you can script this as well)
3. Modify the ID field to be an Identity with your seed
4. Do an INSERT INTO SELECT command 
5. Drop the old table
6. Rename the new table
7. Recieve praise for doing the impossible.

USE [yourDatabase GO

SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ONGO

                             -- This creates the table

CREATE TABLE [dbo].[AutoNumberTemp](

[ID] [numeric](18, 0) IDENTITY(1807,1) NOT NULL, [userid] [nvarchar](50) NULL, ) ON [PRIMARY]go

                             -- This allows us to insert into our Identity column

SET IDENTITY_INSERT [dbo].[AutoNumberTemp] ON
GO

                             -- This copies data from our original table into our new table

INSERT INTO [dbo].[AutoNumberTemp] ( [ID]      ,[userid]
  FROM [dbo].[AutoNumber] WITH (TABLOCKX)

-- Turn off Identity_Insert, because it's not a good idea to leave on 
SET IDENTITY_INSERT [dbo].[AutoNumberTemp] OFF

                             /* Braver DBA's may omit the backup and replace it with a drop table */
exec sp_rename '[dbo].[AutoNumber]','[AutoNumber_old]';
/* Now we rename the new table with the old tables names */

exec sp_rename 'AutoNumberTemp','AutoNumber';go

Have Cursor, Will Travel

A bright rainbow over our house

When I write software that accesses databases, I usually iterate, or loop through, all of the results.  This is very handy.

Sometimes, when working in SQL, you need to do act on each row of a query.  To do so, we uses cursors. 

A cursor is how the SQL Server keeps track of where in the result set it is.  To use a cursor, you will need to declare at least variables. One variable will hold the cursor, and one variable for each column you want to play with.

Let's say, for example, we need to add a counting integer to every row.

First we declare our cursor and our variable to hold our primary key.  I'm also declaring an integer to hold our counter.

DECLARE @aCursor as CURSOR;
DECLARE @anID as INT;

DECLARE @aCounter as INT; 

Next, we have to define what the cursor is.  Here we set our cursor variable to a query.  The setting statement is in orange and the query is in green.

SET @aCursor = CURSOR FOR
SELECT IDNumber
FROM .[dbo].SomeTable 

Next, we have to open the cursor.  

OPEN @aCursor ;

Then, we need to see if there is a row to process.  The data from the query is put into the a variable (in orange below.)  If you have more than one column, separate them by commas. 

FETCH NEXT FROM @aCursor INTO @anID 

Next, we set up your pretest loop need to see if there is a row to process.  The data from the query is put into the a variable (in orange below.)  The code that repeats for each row is in between the BEGIN and END

WHILE @@FETCH_STATUS = 0
 BEGIN

    -- Increment our Counter

    SET @aCounter=@aCounter+1;

    --Update SomeTable with our counter

    UPDATE Sometable set CountField= @aCounter 

     WHERE IDNumber=@anID ;

    FETCH NEXT FROM @aCursor INTO @anID
  END

Lastly, we need to clean up after ourselves.  First we close and then deallocate the cursor.

CLOSE @aCursor;
DEALLOCATE @aCursor;

Very Hidden!

Our cat, Marble, looking out of the window.

Most experience Excel users know that a spreadsheet may be hidden.  Sometimes, you need reference information but would not like to expose that to user interaction.  But, if you can easily hide a sheet, you can easily unhide a sheet.

I just learned today that a spreadsheet may have three values.  Visible, hidden, and very hidden.  I kid you not, it's called "very hidden."  That sounds like mostly dead from The Princess Bride.

You can only very hide or very unhide a page from the VBA editor, which you access from the developer tab (which is not exposed by default.)  Cick on a worksheet, then, in the property window, you can modify the values.

Very Hidden Dialog

So Why?

So you have a spreadsheet, and you want to store data for a drop down.  You could store it in a separate file, but that is messy.  You could store it in a database, but that can present its own series of challenges.  You can embed the data directly as a spreadsheet in your workbook, but that would mean you can have users change the values.  Every programmer knows that allowing users to change such data is a recipe for disaster.  "It doesn't work!"  they will cry, after messing with the spreadsheet labeled. "Do not change"

You can hide a sheet, sure.  But you underestimate the resourcefulness of the general user.  If a feature is available in a menu, a user will find it and use it.  Trust me, they will FIND IT.

The "Very Hidden" selector is in a different editor, only accessible through a tab which is usually not exposed.  Therefore, if someone can get there, they should be sophisticated enough to read (and heed) the "Don't Change This" warnings.