Skip to content

MS Access – Remove Source Control

If you are like me, you love source control. I recently discovered you can use source control in Access via a plugin. However if you want to push it out to a client, how do you remove source control? What you do is a compact and repair of the database. It will ask you if you wish to remove source control from the new copy of the database, click yes and your should be good to go.

Powershell – Copyrighting made easy

I recently needed to submit a program I’m working on for a copyright. One of the criteria is the first and last 25 pages of the program need to be submitted. I really didn’t want to go into every source code file and append it to a file manually, I have more important things to do than that. I used my old friend Powershell to get what I needed.

Here is the line:

> ls *.cs -recurse | ?{-not ($_.fullname -match “obj”) -and -not ($_.fullname -match “properties”)} | Get-Content > sourcecode.txt

What this is doing is grabbing all of my C# code from my solution (excluding code in the obj and properties folders) and dumping it to a text file.

From there I was able to upload one file.

Finding All The Logins Not Associated to Database Users

Today I ran into a particular issue, consolidating user accounts. In this particular exercise, I needed to remove old user accounts that were not tied to any databases. Here is a neat trick I did to get a reasonable number of logins to look at for disabling.

CREATE TABLE ##names (NAME SYSNAME);
EXEC sp_msforeachdb ‘insert into ##names (name) select name from [?].sys.sysusers’;

SELECT name,
loginname
FROM sys.syslogins l
WHERE NOT EXISTS ( SELECT *
FROM ##names u
WHERE u.name=l.name )

Using the unsupported sp_msforeachdb stored procedure I could dump all the usernames into a table and compare it to the logins. Simple, quick, dirty…But saved a lot of time!

Remotely connecting to SQL Server 2008 Integration Services – Access is denied error.

Very annoying error and I haven’t seen clear concise instructions for getting this to work. I will attempt to do that here.

  1. Open Administrative Tools > Component Services
  2. Navigate to DCOM Config and open the properties for MsDtsServer100
  3. Click on the Security tab
  4. Add the appropriate users/groups that you need and assign their permissions to BOTH “Launch and Activation Permissions” AND “Access Permisions” (Both of whom, select Customize then Edit)
  5. Click Ok to save changes and close those windows.
  6. Right click on Computer, select Manage.
  7. Navigate to System Tools > Local Users and Groups > Groups
  8. Double click on Distributed COM Users
  9. Add the users/groups you wanted to give permissions to.

Parenting Hack – Feeding a child who refuses to eat

Being the parent of a child in the terrible 2′s can be a daunting task; they are expressing the individuality and are testing the boundaries. My daughter was refusing to eat, it was just an act of defiance.

I then remembered something from the book the Art of War, take the middle ground. If she won’t eat food, maybe I could get her to just hold the food. Obviously, it should be something easy for a 2 year old to hold. In this case I used pancakes. I told her to help daddy by holding a pancake for me.

In a minute, she ate the pancake. I asked her to hold another one for me, and wouldn’t you know that she ate that one too!

So I hope this helps to feed your defiant 2 year old as well!

SQL Server Database Corruption

I’m currently studying for the 70-450 exam to obtain my MCITP in SQL Server Database Administration. I wanted to get a better understanding for SQL Server’s transaction log. I wanted to see if I could corrupt a databases MDF and recover data even after the last t-log backup. This is where the tail backup comes in.

The tail log backup can recover your data even if the MDF is corrupted. This is because it stores all the transactions since the last full backup and t-log backup. If the MDF is corrupted your t-log should still be ableĀ  to be backed up, unless that is corrupted too. If the database is not truly corrupt, a backup log statement with the norecovery option will work just fine. This puts your database into recovery mode and prevents users from going into the database, preserving the state of the database so the restoration can be performed.

Unfortunately this does not work when the database is corrupted. You have to use the option continue_after_error, this forces the backup to occur.

You can then restore all data back to the database using your full and t-log backups.

Immediately after bringing the database online, be sure to make a full backup. This way you have a good restore point and if you routinely are backing up your t-logs, your next transactional backup will work just fine.

SQL Server – Searching for Non-Alphanumeric characters Using Like

All I can say is I have never really needed to search for a percent sign or square brackets or a caret in a query until today. GooBling (Googling and Binging) didn’t return any useful results. Of course Kim Tripp, one of the sage SQL Server gurus always recommends checking Books Online first, shame on me for not checking there first but this wasn’t entirely obvious. Check out the details here: http://msdn.microsoft.com/en-us/library/ms179859.aspx

You can specify an escape character using a very, now, obvious escape character. An escape character placed in front of a pattern matching character will force SQL Server to recognize the pattern matching character as a normal character.

For example:

SELECT * FROM table WHERE col1 LIKE ‘%|%%’ ESCAPE ‘|’

The above query will return all rows where col1 contains a percent sign.

Follow

Get every new post delivered to your Inbox.

Join 33 other followers

%d bloggers like this: