Tuesday, December 08, 2015

Insert horizontal lines in Word 2007

If you google "Insert Horizontal Lines in Word 2007", you will get all sorts of hits such as the following: https://support.office.com/en-in/article/Insert-horizontal-lines-9bf172f6-5908-4791-9bb9-2c952197b1a9

However, what if simply typing --- and hitting "Enter" doesn't work?!!!

I poked and prodded and searched for quite literally hours trying to find this.

Microsoft has cleverly hidden this in the following ways.

First, you have to figure out where your auto-format settings are.
For Word 2007, in order to access them, you have to add them to the quick access toolbar, as described here.

The second step is to access the auto-correct settings, and then go into Options


Finally, and perhaps most *cough-cough* intuitively of all, you go to "Apply as you type", and select the "Border Lines" check-box.



And, NOW, you can finally insert your horizontal lines by typing ---[Enter] again.

Let the Good Times Roll :)

Friday, November 27, 2015

Capturing Messages, using SMO in VB.NET

I have done some searching for a simple, clear example of how to capture all messages when running a query against Microsoft SQL Server using the SQL Management Objects (SMO).

I haven't been at all happy with what I've found, so I thought I would post the following example, hopefully as a clear and concise code example

VB.NET
SMO

A few notes:
1 - "Messages" is a text TextBox that I am using, as you might guess, to write messages to
2 - Note the "AddressOf" in the AddHandler method call. This registers the method so that messages can be passed to the application and handled.
3- I had to define MigDBServer as
Private MigDBServer As Microsoft.SqlServer.Management.Smo.Server
4 - It is also necessary to add a reference to the ddl that contains the Microsoft SMO extensions.

Add Reference by going to Project-> Add References.
Then choose Assemblies->Extensions, and select Microsoft.SqlServer.Smo



Code:
Click Image to view larger Version:




Capturing Messages, using SMO in VB.NET

I have done some searching for a simple, clear example of how to capture all messages when running a query against Microsoft SQL Server using the SQL Management Objects (SMO).

I haven't been at all happy with what I've found, so I thought I would post the following example, hopefully as a clear and concise code example

VB.NET
SMO

A few notes:
1 - "Messages" is a text TextBox that I am using, as you might guess, to write messages to
2 - Note the "AddressOf" in the AddHandler method call. This registers the method so that messages can be passed to the application and handled.
3- I had to define MigDBServer as
Private MigDBServer As Microsoft.SqlServer.Management.Smo.Server
Click Image to view larger Version:


Thursday, July 16, 2015

Monitor SQL Server Service using PowerShell

Mashrur Shuvo posted a script over at SQL Server Central that will user powershell to monitor your sql server services.

If you're like me, and you have trouble getting budget approval for 3rd party monitoring tools, then this is very Useful (with a capital - U ;-)   ) !
http://www.sqlservercentral.com/scripts/Monitor+SQL+Server+and+Service/127705/

Thursday, May 21, 2015

DB2 LUW Invalid Packages - detection, Investigation, and Resolution

I was not able to find what I considered a good, clear reference for this when I recently encountered it at a customer installation.

I found that there were quite a few invalid packages that needed to be dealt with.
The worst, of course, were those with a value of 'X' in the VALID indicator. (VALID = 'X').

So, here are some simple steps I followed to investigate and resolve these issues.
You, of course, may want to investigate these items further.

Get the packages that have issues:

db2 "select pkgschema, pkgname, unique_id, valid from syscat.packages where VALID <> 'Y'" > invalid_packages.txt

To get the ones that require manual intervention, use
db2 "select pkgschema, pkgname, unique_id, valid from syscat.packages where VALID = 'X'"

Figure out if those packages contain your SQL:
db2 "select pkgschema,pkgname,unique_id, TEXT from syscat.statements where UNIQUE_ID = x'414141414157465A' AND PKGNAME = 'STADMG02'"
NOTE on the above: The TEXT field will show you the query associated with your package, but it is defined as a CLOB, and is therefore quite large. If you need to see it, redirect to a file. Otherwise it might be best to omit it.

Do something about those packages :)
db2 rebind package NULLID.STADME01 RESOLVE ANY REOPT ONCE
Where NULLID is the schema used by the jdbc driver, and STADME01 is the package name returned in the PKGNAME field. The schema name and the package name are retreived from your first query at top.

EDIT: To auto-generate a script that rebinds everything:
db2 -x "select 'rebind package ' || pkgschema || '.' || pkgname || ' RESOLVE ANY REOPT ONCE ;' from syscat.packages where VALID <> 'Y'" > rebind_other_packages.sql

Here's an example of me doing this in our internal environment:

/home/db2xbox >db2 "select pkgschema, pkgname, unique_id, valid from syscat.packages where VALID = 'X'"

PKGSCHEMA                                                                                                                        PKGNAME                                                                                                                          UNIQUE_ID             VALID
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------- -----
NULLID                                                                                                                           STADMG02                                                                                                                         x'414141414157465A'   X
NULLID                                                                                                                           STADMG01                                                                                                                         x'4141414141494157'   X
NULLID                                                                                                                           STADME01                                                                                                                         x'41414141414B4B30'   X

  3 record(s) selected.

/home/db2xbox >db2 "select pkgschema,pkgname,unique_id, TEXT from syscat.statements where UNIQUE_ID = x'414141414157465A' AND PKGNAME = 'STADMG02'"
NOTE: I did note run the above ↑↑↑↑↑↑ because the output is quite large due to he "TEXT" field which is defined as a CLOB.
/home/db2xbox >db2 rebind package NULLID.STADMG02 RESOLVE ANY REOPT ONCE
DB20000I  The REBIND PACKAGE command completed successfully.
/home/db2xbox >

/home/db2xbox >db2 describe table syscat.statements

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
PKGSCHEMA                       SYSIBM    VARCHAR                    128     0 No
PKGNAME                         SYSIBM    VARCHAR                    128     0 No
STMTNO                          SYSIBM    INTEGER                      4     0 No
SECTNO                          SYSIBM    SMALLINT                     2     0 No
SEQNO                           SYSIBM    INTEGER                      4     0 No
TEXT                            SYSIBM    CLOB                   2097152     0 No
UNIQUE_ID                       SYSIBM    CHARACTER                    8     0 No
VERSION                         SYSIBM    VARCHAR                     64     0 Yes

Wednesday, March 25, 2015

Recording General System Performance Data for AIX

Collect Topas Info

Introduction

I identified a need to record general system information for review in association with DB2TOP data.
This is the very brief procedure I developed to do so.

Generate the data

Execute TOPAS_NMON by using the following commands:
topas_nmon -s 5 -c 100 -f
This will sample every 5 seconds for 100 samples and save to a file in your current directory.
NOTE: You can also leave off the -c and just let it run. When ready to stop it, issue kill command.
                ps -ef | grep -i "topas"
                then, use the kill command, passing in the pID, to end it.

Analyze using NMON Analyzer

  1. Download NMON Analyzer here. (https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/Power+Systems/page/nmon_analyser  )
  2. Open NMON Analyzer.
  3. Enable Macros.
  4. Set Settings in Settings tab as desired. NOTE: Default values are fine.
  5. Set desired settings in Analyzer.
  6. Click "Analyze nmon data" button to perform analysis.



Monday, February 16, 2015

Microsoft SQL Server Express - automating backups

MS SQL Tips article on automating SQL Server Express backups and the purging of old backups

The very brief, yet excellent article by , linked above, contains a PowerShell script that would allow one to:

  1. Automate backups on SQL Server Express
  2. Automatically clean up backups that are aged > x days.
I always advocate that a DBA should test their backups in SQL Server. I would therefore recommend adding a step to Ahmad's script that performs a restore of the backup.

There are some assumptions, here:
  • These are relatively small databases, and are using simple recovery model
  • Along those same lines, there is room on the server's storage media to hold a "throw-away" database.
In sitting down to actually code this up, it occurred to me that this is WAY more trouble than it's worth ;-)

I would probably follow an approach that generates the necessary backup and restore scripts from SSMS, then executes them from either a normal command shell, or the powershell, as the case may be.

For those who use SQL Server Express, if you have not already encountered it, I recommend all of MSSQL Tips articles on Express Edition.




Minimally Logged Operations in SQL Server

I'm posting this mostly so I can find it later :) The following article has a great table that defines when operations will be minimall...