Replacement for SendKeys in VBA

We have an MS-Access 2016 application that runs on a terminal server which we are upgrading from 2008 R2 to 2012. The prelim testing was done on Windows 10 which seemed equivalent, but not so much.

One feature of the app is an auto-test button, which uses VBA to open forms, click buttons, and make selections from drop-down lists.

The portion of the code for the drop-down lists uses the built-in VBA SendKeys to enter some text and press {ENTER} to make a selection; it works on Windows 10 but unfortunately, not on Windows Server 2012.

After a long Google search, I found a simple replacement in one of the msdn blogs:

Public Sub WSSendKeys(T As String)
Dim WshShell As Object
Set WshShell = CreateObject("wscript.shell")
WshShell.SendKeys T, False
End Sub

...the app already has a reference to the Windows Scripting Runtime, so you'll need to add that in order to use this idea.

Alter Table Drop Column

If you've worked with SQL Server for a while, you're familiar with the syntax:

ALTER TABLE table_name
DROP COLUMN column_name

Easy enough, right? But if you have a default constraint on that column, you need to drop the constraint before you can drop the column.

If you originally created the constraint & specified the name, something like this:

ALTER TABLE table_name
ADD column_name bit NOT NULL
CONSTRAINT DF__table_name__column_name DEFAULT (0)

then you simply drop the constraint, then drop the column. Easy enough.

However, if you did not explicitly name the constraint, the system-generated name might be something like "DF__table_nam__colum__51EF2864" so you would have to go look up the name. If you are doing some clean-up, removing a number of columns, that can take some time.

The good news is that you can run some code to get the constraint name & drop it, then drop the column, like this:

CREATE PROC dbo.wmAlterTableDropColumn
(
@table_name sysname,
@column_name sysname
)
AS

DECLARE @constraint sysname, @sql nvarchar(4000)

IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(@table_name) AND [name] = @column_name)
BEGIN
SELECT
@constraint = dc.name
FROM
sys.tables t
INNER JOIN sys.columns c
ON t.object_id = c.object_id
INNER JOIN sys.default_constraints dc
ON t.object_id = dc.parent_object_id
AND c.column_id = dc.parent_column_id
WHERE
t.name = @table_name
AND c.name = @column_name

IF @constraint IS NOT NULL
BEGIN
SET @sql = 'ALTER TABLE ' + @table_name + ' DROP CONSTRAINT '+ @constraint
EXEC sp_executesql @sql
END

SET @sql = 'ALTER TABLE ' + @table_name + ' DROP COLUMN ' + @column_name
EXEC sp_executesql @sql
END

GO

...and then to use this for our example, you would run this:

EXEC dbo.wmAlterTableDropColumn 'table_name', 'column_name'

SSIS imports Excel with some blank columns

I am working with SSIS to import an Excel file into SQL Server. Not being an SSIS expert, I usually use the import wizard in SSMS which lets me create the destination table, load the data, and save the package to the file system. Then I will review the wizard-created table, modify any data types as needed, and then generate a proper CREATE TABLE statement.

In some cases however, the wizard does not assign the correct data types, and the saved package does not work - one or more columns will be blank. This is caused by the baked-in behavior of Excel, where it examines the beginning of the data & guesses the data types. If the first few rows are not fully populated with the appropriate data, that's a problem.

The fix for this is remarkably simple, but took me lots of Googling to find the solution Import Excel in SSIS and get Null values in stead of Numeric values posted by Kasper de Jonge...

When you open the package in SSIS, in the Excel connection manager you simply add "IMEX=1" so it looks this:

Excel 8.0;HDR=YES;IMEX=1

which forces SSIS to read the Excel file using the data types you specify in the package.

SSMS Utility Explorer

One of the features of SSMS 2008R2 is the Utility Explorer window, which "connects to Database Engine instances to provide a tree view of all the objects in the SQL Server Utility." (https://msdn.microsoft.com/en-us/library/ee210556%28v=sql.105%29.aspx)

This feature is available in the Enterprise & Developer Editions.

Out of curiosity, I opened that window to check it out, and then closed the window. Thought that would turn off the feature. However, every time I opened SSMS, the UE window opened too. Easy enough to close it with a single click, but could not turn it off.

I found that there is a config file windows.prf located here:
C:\Users\first_name.last_name\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell

(whew!)

... and luckily there was a backup file in there, so all I had to do is close SSMS, rename the windows.prf to something else, and then rename the backup to windows.prf and Voila! the Utility Explorer window does not open when you launch SSMS.

TortoiseSVN and SSMS icon overlays

Tortoise SVN is one of the popular source-control systems, which we use at our office. If you've used this product, when you commit you'll sometimes see a message that a new version is available; I had 1.8.x installed, so this time I did the update but - got bit by version 1.9.2 ...

This is using a recent Windows 7 x64 machine, by the way.

After the update, the Windows folders all had the familiar icon overlays, but when I launched SQL Server Management Studio & hit the File Open dialog, none of the colored icons were present. Hmmm. Google'd about looking for a solution & found numerous registry hacks involving icon overlay keys, keys that were added by/for Groove and/or Windows 10; finally found the answer: version 1.9.2 seems to have a problem.

So I uninstalled the 1.9.2 client and downloaded the previous 1.9.1 version, and Voila! the icons all came back. Good times.

sp_who2 with sql text

SQL Server has an undocumented stored procedure sp_who2 that provides information about the connections to the database.

Recently I have been using sp_who2 to track down connections that are "suspended" which in my case means that our MS-Access application is not processing all of the requested data in a timely manner. This can interfere with other users, even though there is no actual blocking going on. Another issue that we're seeing is that users will log-in to their terminal server environment, launch the application & then leave it open for days at a time. Even though the connections are sleeping, they still require some resources on the SQL server.

The task at-hand is 1) determine which connections (SPID's) are suspended & what SQL was running, and 2) determine which connections have been sleeping for more than a day. The first item will reveal the SQL so we can find an alternate solution; the second will allow us to identify the sleepers so we can close them out.

I started by creating a temp table & inserting the sp output, and then running some code to look up the SQL text. . .

DECLARE
    -- set this to the name of your database
    @database sysname = 'my_database',
    @not_sleeping int,
    @not_today int,
    @spid int,
    @sqltext varbinary(128),
    @sql_command nvarchar(4000),
    @today nvarchar(50)

-- LastBatch only provides month & day
SELECT @today = LEFT(CONVERT(nvarchar,GETDATE(),101),5)

-- create temp table to receive sp output, and execute
IF OBJECT_ID(N'tempdb..#sp_who2', N'U') IS NOT NULL
DROP TABLE #sp_who2

SET NOCOUNT ON

CREATE TABLE #sp_who2
    (
    SPID int,
    Status nvarchar(1000) NULL,
    Login sysname NULL,
    HostName sysname NULL,
    BlkBy sysname NULL,
    DBName sysname NULL,
    Command nvarchar(1000) NULL,
    CPUTime int NULL,
    DiskIO int NULL,
    LastBatch nvarchar(1000) NULL,
    ProgramName nvarchar(1000) NULL,
    SPID2 int,
    REQUESTID int NULL
    )

INSERT #sp_who2
EXEC sp_who2

-- look up the sql text for each SPID
ALTER TABLE #sp_who2 ADD sql_command nvarchar(4000)
-----------------------------------------------------------
WHILE EXISTS (SELECT 1 FROM #sp_who2 WHERE sql_command IS NULL)
BEGIN
    SELECT @spid = spid FROM #sp_who2 WHERE sql_command IS NULL

    SELECT @sqltext = sql_handle
    FROM sys.sysprocesses
    WHERE spid = @spid

    SELECT @sql_command = TEXT
    FROM sys.dm_exec_sql_text(@sqltext)

    UPDATE #sp_who2
    SET sql_command = @sql_command
    WHERE spid = @spid
END
-----------------------------------------------------------
SELECT @not_sleeping = COUNT(*)
FROM #sp_who2
WHERE DBName = @database
 AND status <> 'sleeping'

SELECT @not_today = COUNT(*)
FROM #sp_who2
WHERE DBName = @database
 AND    LEFT(LastBatch,5) <> @today

-- display a list of all SPID's of interest
IF @not_sleeping <> 0 OR @not_today <> 0
BEGIN
    SELECT
        SPID,
        Status,
        Login,
        HostName,
        BlkBy,
        DBName,
        Command,
        CPUTime,
        DiskIO,
        LastBatch,
        ProgramName,
        SPID2,
        REQUESTID,
        sql_command
    FROM
        #sp_who2
    WHERE
        DBName = @database
     AND    (
        Status <> 'sleeping'
        OR
        LEFT(LastBatch,5) <> @today
        )
END
---------------------------------------

DROP TABLE #sp_who2

T-SQL Select Schema

Let's say that you have the same table that exists in two different schemas -

A.my_table
B.my_table

...and you want to write a query with a parameter so you can select from one schema or the other. Without resorting to dynamic SQL.

One possible solution is to write a UNION query that will deliver the data from one schema or the other, like this:

DECLARE @schema sysname
SET @schema = 'A'

SELECT *
FROM A.my_table
WHERE @schema = 'A'
UNION ALL
SELECT *
FROM B.my_table
WHERE @schema = 'B'

Here we are using the ALL keyword to make sure that we retrieve all records; normally a UNION will only retrieve unique records.

Of course, the DDL for both tables must match, or the UNION will fail; the number of columns must be the same, and the data types must be compatible.

This idea was actually applied to an SSRS report - for which, the data source must be either T-SQL text or a stored procedure.

Programming Tab Controls

MS-Access tab controls offer a method to present multiple groups of similar data, making it easy for user to locate specifics within an intuitive & user-friendly layout - very much like an Excel workbook. The main control is of course called a Tab Control, and the tabs within it are called Pages.

For example, you might have a main form where you select a customer, and then you have a tab control that has pages that display related data for such things as Contacts, Sales, Invoices, Payments, Credit Data and so on. Usually each tab has a subform that contains the data related to the current selection from the main screen.

However, when you access the tab using VBA, there are few variations that are not exactly clear in the online documentation.

For starters, when you place a control on a tab, the Parent is actually the Main Form, not page of the the tab control. So if you have a main form called "main_form", a tab control named "MyTab", and several pages named Page1, Page2, and Page3 - and then - you place a command button Button1 on Page1, the full reference to that control is

Forms!main_form!Button1

...note that the Page number has no effect on that. Therefore, when you have a form with one or more tabs controls, you must be careful to use a unique name for each of the controls, whether on the main form, or on any of the tabs.

If you wish to detect which tab has been clicked, you will use the OnChange event; in this example when you click the first tab Page1 which happens to have a page index of 1, the MyTab value will be 1 -- so to get the name of the page you will need to use

Private Sub MyTab_Change()
With Me
MsgBox .MyTab.Pages(.MyTab).Name
End With
End Sub

to get the name of the page that was clicked.

SQL - list columns in a table, updated, again

The code has been updated again, this time instead of using sp_columns we are using the system tables directly. This provides access to additional data, such as whether the column is computed. Plus, in the event that Microsoft changes sp_columns - that would not break our code.

One significant change since the previous January 2012 version is that option 4 now provides a skeleton for an insert stored procedure - all of the column names are provided with data types in a DECLARE statement as parameters, followed by the INSERT for column names, and finally the SELECT statement that references the parameters. In this option, the code is excluding non-updating columns such as identity, timestamp, image, or any computed columns.

In all prior versions, we bracket [] any columns that have non-alphanumeric characters such as spaces or hyphens. In order to accommodate the #4 option, we have a large REPLACE statement to convert those to underscores. If you have any other characters to exclude, you can edit the REPLACE (which occurs in two places) to make the corrections.

EDIT ON 10/20/2012 - this new rewrite of the wm_columns will not work on views, because sp_tables actually pulls in both tables and views; where of course sys.tables only looks at tables. Hmmm. I will probably add one more option to this, so views will be back in the list.

SQL - list columns in a table, updated

Way back in April 2009, I had published the original version of my stored procedure wm_columns which can be used instead of sp_help or sp_columns to view a list of column names for a table or view.

The new version has a number of enhancements, and you can view the complete code here.

The first parameter is required, which is the name of a table or view, such as:
wm_columns 'my_table'
...and the default schema is dbo

You may also include the schema, e.g. in AdventureWorks you can use this:
wm_columns 'Person.Contact'

The second parameter is optional, and has the following choices:

0 = display the column list in physical order
1 = display the column list in alphabetical order
2 = display a select statement
3 = display an insert statement
4 = display an insert statement, with data types added as comments

For the options 2, 3, and 4, column names are bracketed with [] if they contain spaces or other non-alphanumeric characters.

For the options 3 and 4, any identity or timestamp columns are displayed but commented-out.

The third parameter, when used with option 0 or 1, allows you to apply a fuzzy filter to the column name, for example:

wm_columns 'my_table',0,'id'
...will list only the columns that contain 'id' in the name.

The third parameter, when used with option 2, allows to you use an alias for the table, for example in AdventureWorks:

wm_columns 'Person.CountryRegion',2,'cr'
...will display a select statement like this:
SELECT
cr.CountryRegionCode,
cr.Name,
cr.ModifiedDate
FROM
Person.CountryRegion cr