Thursday, November 30, 2017

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

Tuesday, October 24, 2017

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;

Friday, September 29, 2017

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. 


Monday, April 03, 2017

Lettering in Macros


Our Nosy Dog, Alex

Visual Basic for Applications, embedded in the Microsoft Office Suite, is one of the most unknown power tools available for office automation.  (Check out a primer on Excel VBA here)

One of the most powerful aspects is the ability to loop through contents and perform an action on every row or column in a range.

Some functions in Excel, however, doesn't use the column number as an identifier.  Instead, they use the normal spreadsheet nomenclature of column letter plus row number. So, the upper-left cell in a spreadsheet, row 1,  column 1, is called "A1".

If you are trying to loop through columns using a for loop, this will cause you a bit of a headache.

I found a solution that is elegant!

VBA
Function Letter(num as integer) as string
     letter =split(Cells(1, num ).Address, "$")(1)
End Function 

Dot Net
Function Letter(byref ws as Worksheet, byval Num as Integer) as String
        Dim a() As String = Split(CType(ws.Cells(1, Input), Range).Address, "$")
        Return a(1)
End Function


Tuesday, February 14, 2017

Elbow Room

Duncan and Trixie cuddling
What do you do when you have an old SQL Server that is running out of space?   Well, you can free up space by deleting unneeded files, add additional space, or buy a new server, right?

Well, we have a server was constantly running out of space on its tiny 50 gig system partition and we were months away from retirement (best case), we've deleted all unneeded junk and there was no possibility of adding space.

I pulled out my favorite tool, SpaceSniffer, which shows disk usage.  Six gigabytes were taken up in the c:\windows\Installer folder.

We had adequate space on the data partition, so the plan was to move the installer folder to the D drive.  Since many software packages need the information in this folder in order to work, we needed to fool them into thinking the folder was still on C.

My Coworker, Mike, remembered an article he saw on Microsoft.  The solution was to create a symbolic link to the folder.  The kicker is that this has to be done at system level, which is higher than just administrator.  After some Googling, here are the steps to do what we did.


  1. Copy the c:\Windows\Installer folder to the D Drive
  2. Delete the c:\Windows\Installer folder
  3. Download the Sysinternals PSTools from https://technet.microsoft.com/en-us/sysinternals/pstools.aspx
  4. Unzip these tools to a folder on the server.
  5. Run CMD as administrator on the server
  6. change into the PSTools folder, and execute PsExec by typing
    PSEXEC -i -s -d CMD
  7. in the new Command Window that opens, test that you're running as system by typing
    whoami /user
    You should see "nt authority\system s-1-5-18"
  8. in the new command window, type
    mklink /d c:\Windows\Installer d:\Installer
    this will create the link, so software that navigates to C:\Windows\Installer will go, instead, to d:\Installer. 
  9. Reboot
Viola, the server now had 6 gig free on C: and was now happy!

Addendum:
Since writing this post, I've had to use this trick at least a dozen times.  I ran into a situation where I have already moved the installer folder and still ran out of space.  I found another folder, the SoftwareDistribution folder(where Windows Updates downloads its updates.) that I could move.

This required an extra step of stopping the WindowsUpdate service before the move, since it may recreate the folder when it is missing.  Once you are done, restart.

I found, however, that despite being an administrator, I didn't have the rights to stop the WindowsUpdate task.

Skip steps 1 and 2 above.  Follow the rest of the steps until step 7.  While in the NT Authority command prompt, type "Services.msc"  this starts the Windows Services console.  Find and stop the Windows Update.  Move the folder (steps 1 and 2) and then proceed.

Friday, June 10, 2016

When your backup is against the wall

Our flooded backyard on Christmas Day 2015
So, we had a developmental database which a developer wanted to clean up and put on a production server.  I did a backup, and cleaned up the database and had it moved to the new server.

But, communication is difficult.  He wanted to clean up the database once it landed on the new server, not while it was on the old server since there was software using it.

No problem, I'll restore the database.  Except, I made a boo-boo.  I used the GUI to do the backup, and wound up doing a transaction log backup.

If you're not familar, SQL Server (and most other enterprise database platforms), SQL can make a full backup of its data in a handy file.  It also records all changes made in what is called transaction log backups.  Transaction log backups are much faster, so you can do them much more often.  On this database, it was every 30 minutes.

In order to restore the database, I had to restore the full backup and every log file after.  So the key to this is the WITHNORECOVERY keyword.  Do the restore as a single script, with the "WithRecovery" tag on only the last entry

restore database OOPS from disk ='d:\OOPS_backup_2016_06_09.bak' WITH NORECOVERY;
Restore log OOPS FROM DISK =  'd:\OOPS_backup_2016_06_10_080020_0584990.trn' WITH NORECOVERY;
Restore log OOPS FROM DISK =  'd:\OOPS_backup_backup_2016_06_10_083023_4130230.trn' WITH NORECOVERY;
Restore log OOPS FROM DISK =  'd:\OOPS_backup_backup_2016_06_10_090017_4780062.trn' WITH NORECOVERY;
Restore log OOPS FROM DISK =  'd:\OOPS_backup_backup_2016_06_10_093017_3391104.trn' WITH NORECOVERY;
Restore log OOPS FROM DISK =  'd:\OOPS_backup_backup_2016_06_10_100019_1231400.trn' WITH NORECOVERY;
Restore log OOPS FROM DISK =  'd:\OOPS_backup_backup_2016_06_10_103017_7468920.trn' WITH RECOVERY

NOTE:  If you are getting an exclusive access error, you'll have to put the database into single user mode by doing a
ALTER DATABASE OOPS SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Then, after you're done, put it back in multiuser mode by doing a
 ALTER DATABASE OOPS SET MULTI_USER WITH ROLLBACK IMMEDIATE
 Also, you might need to use the REPLACE option on the full backup by doing a WITH REPLACE, NORECOVERY

Tuesday, May 31, 2016

Do I have the Right?


So many times, I needed to see what groups I'm in, who else is in a group, or get a list of Domain groups.

This is trivial from the command line.

NET USER - NET USER does the following

  • Retrieve a list of users on the machine from which you execute the command.
  • Retrieve a list of users from the domain
  • Retrieve the user's account information for a local account
  • Retrieve the user's account information for a domain account

Syntax

  • NET USER - Retrieve local user list
  • NET USER  /DOMAIN - Retrieve domain user list
  • NET USER UserName - Retrieve local user account 
  • NET USER UserName  /DOMAIN - Retrieve domain user account 

NET GROUP, NET GROUPS, NET LOCALGROUP- NET GROUP series of commands does the following.  Note:  NET GROUP and NET LOCALGROUP (singular) get information for a single group while NET GROUPS and NET LOCALGROUPS (plural) gets information about the groups that exist.
  • Retrieve a list of groups on the machine from which you execute the command.
  • Retrieve a list of groups from the domain
  • Retrieve the list of users that belong to a specific group on the local machine
  • Retrieve the list of users that belong to a specific group on the domain 
Syntax
  • NET LOCALGROUPS
  • NET GROUPS /DOMAIN
  • NET LOCALGROUP "group name"
  • NET GROUP "Group Name" /DOMAIN