Recently I ran into an issue where I needed to edit a large number of dtsConfig xml files.  I thought there was an option in the windows operating system to perform a find and replace across multiple files, but alas I can't find it...

Than I remember a vb script I had hobbled together from several other people to do this.  There are several share ware, free ware and other programs to do this, but I really think that find and replace is so basic of a task that it should be easy to kick out...

So here it is, remember that vbs files are a security issue when downloading from the internet, so i've uploaded them with a .txt extension.  Rename it to .vbs.  There are two programs, a replace.vbs and Replace_in_All_files.vbs.  The latter is the one that you must edit with the directory to look for files, extension of files to search and the find and replace string.

Use at your own risk.  The program will make a backup of the file.  VB Script is not the most efficient find and replace program for very large files, but if you have 100 or so dtsConfig files to edit, it works like a charm !

The same type of program can be done in a Power Shell script, and it is the cool new buzz language, but since it is only installed on 1 out 170 servers we support, it's not quite useful yet....i'll post the power shell script later.

replace.txt (1.76 KB)

Replace_in_All_files.txt (1.5 KB)

Recently had a need to ensure that some tables were indexed in a particular order, mostly due to a very small maintenance window.  I did not want to create any supporting tables to do this, so we quickly modified the reindex script to include a priority and list the tables in the query.  If there were more than just a handful, our thought was to use the extended properties of each table to add a priority, and then order by that priority for the reindexing.

The quick way for a few tables was just changing the select statement to:

SELECT table_name, table_schema,
 Case when table_name in ('Client','conflict') Then 1
    When table_name in ('appointment') then 2
  Else 3 End As Priority
FROM information_schema.tables a
WHERE a.table_type = 'BASE TABLE'
 AND a.table_name <> 'dtproperties'
 AND NOT (a.table_name LIKE 'tmp%')
 ORDER BY Priority,table_name 

Create Table BobTest (bobTestId int identity(1,1) primary key,
    bob_description varchar(100))
Create Table z_ReindexFirst (testid int identity(1,1) primary key)

EXEC sys.sp_addextendedproperty @name=N'ReindexPriority', @value=N'1' ,
     @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',
     @level1name=N'BobTest'

EXEC sys.sp_addextendedproperty @name=N'ReindexPriority', @value=N'2' ,
     @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',
     @level1name=N'z_ReindexFirst'

SELECT CAST(e.name AS VARCHAR(1000)) As ext_prop_name, 
       Coalesce(CAST(e.value AS VARCHAR(1000)),0) As ext_prop_value,
        t.name
       FROM sys.tables AS t 
       LEFT OUTER JOIN sys.extended_properties AS e 
                        ON t.[object_id] = e.major_id 
                        AND e.minor_id = 0
       WHERE e.name is null or e.name='ReindexPriority'
ORDER BY e.value desc,t.name

drop table bobTest
drop table z_ReindexFirst

Too many snakes, I finally had to take action and capture them this weekend, I caught 9 in all, and 1 donation from a neighbor, for 10.  At least 2 got away, so there's more...

I don't mind the occasional garden snake, they have their place, but when I can count 4 or 5 at a time sunning themselves in the front yard, it's time to go.

Now I didn't play any games with them, like the fellows on National Geographic, I just picked them up behind the head and put them in a bucket.  They were a pretty good size, and quite lively, each about 12-15" long.  If you sat quitely in the yard and waited in the sun, they just slowly start to come out of their holes, than you snag them.  At one point I had 3 or 4 poking their heads up at a time.

No pictures, I want to forget the whole thing.

I released them in the woods at the local park.

2000 Ford Windstar with a rough, low idle, occasionally stalling when coming to a stop. 

I put off repairing this for as long as I could, which as with most things, is not a good idea as it causes more damage.

The end diagnosis is what I expected, though the cost of repairing this via a professional is expensive, but I needed it done quick and with little frustuation.

Diagnosis:

• Intake Manifold Gasket leak
• EGR Valve failing due to working itself to death trying to compensate for the intake leak

Other necessary work (120K miles)

• Major tune-up
• 02 Sensor, there are 4, but supposedly only 1 is bad

Over-all putting off the work caused the EGR issue, but the tune-up and 02 sensor have been needed for a long time; i decided to have these done as well, if you've ever tried to get to the back plugs on the windstar you know why.

Total estimated cost $1100-1200, ouch.  I should do it myself, the intake kit is about $109.00, but it is a big job, and then throw in the 02 sensor, egr, plugs, wires etc and it's easily $400+ in parts alone....I needed it done right so we'd have a reliable vehicle.

SSRS - Reporting Services Subscriptions not sending

Recently we ran into an issue with a Reporting Service Instance that was not sending any email subscriptions.  We could find no errors or obvious issues.  Of course a quick reboot solved the problem, but only temporarily, the next day, the issue was right back.  Time to call out for help, Microsoft Support.

Microsoft Support found the problem and provided us some useful scripts.  Basically the issue was that someone had created a snapshot, that ran every night.  The snapshot ran for 12+ hours, causing performance issues and other problems with all other subscriptions.  This is a "shared" reporting service instance, set up for several application groups.  The scripts from Microsoft were quite useful, i've listed them here, all of them are in my Script Vault.

I found the scripts very useful, as it addresses one of my continual issues, which is addressing capacity in shared environments.  For our Shared Reporting Service Environment, we create a Directory / Folder for each group and tweak permissions to keep each application separated, than we turn over full permissions to each sub-folder to the application group. 

Obviously the danger here is that someone can create a poorly performing report that effects the entire environment.  The scripts included below can help identify which reports run the longest.  We then modified this query a bit more to roll-up the report times, by sub-directory, so we can determine which application is using the most capacity in the environment.  We can then engage this group to optimize their reports or began capacity planning to build them a dedicated environment.

Here is a script listing of what I added to the Script Vault

1.  Find What User runs What Report - (Rpt Svc - Who Runs What)
2.  Find what subscriptions have been run and when - (Rpt Svc - Subscr Run When)
3.  Find what SQL Agent Schedule goes with what Report (Rpt Svc - SQL Agent to Report)
4.  Average Report RunTime - (Rpt Svc - Avg Rpt Runtime)
5.  Average Report RunTime Per Parent Folder - (Rpt Svc - Avg RunTime Pnt Fld)

We then used the last two queries to develop a Reporting Services reports that is available to the administrators of each directory, so they can manage their own performance and capacity, thanks to Tom Reeves,  for developing the last two.

Not sure if I just work for the most screwed up company in the world, or if I've just had a long run where I only seem to work for screwed up companies.  Maybe i'm drawn to them in some sort technology sociopath mental illness issue.  I was once told that, ""People with Mental Illness Enrich Our Lives".   If the same applies to a particular companies technology, than i'm enriched a lot!  To the problem...

The infinite wisdom of the powers that be of my company changed the domain\user login from domain\{first initials}{lastname} to a "generic number".  I know there are a lot of "smiths" and "guptas", so the original naming convention was not perfect either, but it worked for 80% of the employees.  The generic number is very frustrating for SQL Server DBA's.

The fun of verifying users for SOX Audits, just became a joy on par with cleaning toilets and replacing sewer lines, no longer easy enough to just run a query to get user's and permissions, now you need to translate the cryptic AD Login, so that management can verify each user....hmmm...thankyou sir, may I have another?  Imagine looking at sp_who2, or Activity monitor, or whatever query you use to view who is connected to your sql server and seeing, {domain}\x111111 as the user who is running a table scan against every table in the most important database you have, and the user was even nice enough to put a table lock hint on the query, nice! 

Now go find x111111 so you can ring his bell, or revoke their access and maybe they'll call you.  The x111111 is useless to you, at least the other AD login convention was useful most of the time, now it's completely useless.  Not only does it frustrate me on troubleshooting problems, but also adding new users.   Even though we modified the ticket system to ask for the login, invariably a significant number of tickets don't include the login name, and we have to bounce tickets around until we can find the users new cryptic AD Login.

To resolve this I had to write a utility that queries active directory and looks up the id of x111111 and returns their name.  I've included the two scripts (vbs - so rename them), one does a lookup by name (so horkay returns a553542) or by id (so a553542 returns Robert Horkay).  We also incorporated them into a web page, so that we could quickly and easily run these queries against Active Directory - (don't forget that your web.config will need to impersonate an identity so that you can query active directory). 

Next I'm going to have to write a custom version of the Activity Monitor, that uses the CLR to translate the cryptic Active Directory Login to a usable name, so that we don't have to bounce between management studio and a web page.