SQL Server: Inline Queries Across Linked Servers

I’ve had to run a scheduled T-SQL query from one database to another linked server for an import operation. I'm finding that I have to do some comparisons between the result of two subqueries in order to get the proper value for one column. So the results I want would correspond to something like this:

Insert into localTable (columns)

Select col1, col2, col3,

CASE 
      WHEN subquery1result > subquery2result THEN subquery1result
      ELSE subquery2result

END As col4

From server.schema.dbo.table1

Where [stuff]

Note the reference to the linked server… First you have to set up the Linked Server in SQL Server, then you can reference the external database table as [LinkedServerName].[SchemaName].[DataBaseOwner].[TableName]

To flesh that out a little more, if I filled in those subqueries in-line:

Insert into localTable (columns)Select col1, col2, col3,

CASE 
    WHEN (select MAX(T2.datecol) from server.schema.dbo.table2 T2 where T2.id = T1.id) > 
         (select MAX(T3.datecol) from server.schema.dbo.table3 T3 where T3.id = T1.id) 
    THEN (select MAX(datecol) from server.schema.dbo.table2 T2 where T2.id = T1.id) 
    ELSE (select MAX(T3.datecol) from server.schema.dbo.table3 T3 where T3.id = T1.id) 

END As col4

From server.schema.dbo.table1 T1

Where [stuff]

But that doesn’t work – using subqueries in a CASE statement like that won’t fly.
I would normally do these subqueries as user-defined scalar functions, but I can't seem to make one that queries tables in a linked server. And I'm not allowed to modify the schema of the database on the linked server (vendor system).

Any ideas on another way of doing this? I suppose I should copy all relevant values into a holding table (on the local server) & then run the import from there instead of importing directly from the linked server. I was just hoping I could keep it as a straight 'Insert Into... Select From' query in a single step.

I think I stumbled upon a "good enough" solution (which suitably horrify a proper SQL-Ninja):

Insert into localTable (columns)

Select col1, col2, col3,
(Select MAX(DC) From 
   (
      select MAX(T2.datecol) as DC from server.schema.dbo.table2 T2 where T2.id = T1.id
      UNION
      select MAX(T3.datecol) as DC from server.schema.dbo.table3 T3 where T3.id = T1.id 
   ) as U
) As col4

From server.schema.dbo.table1 T1

Where [stuff]

It seems to work, though the query is a bit on the slow side (7 seconds to return ~2K rows). Well within reason for a nightly batch job though. Basically the UNION of multiple inline queries forms a set of it’s own, which we can further query from. In this case we’re choosing the Maximum value returned from two different queries.

My .NET app is really slow on one PC, fine on all others

So I had this small .NET Windows Forms application that has been running on a large number of our lab computers with no issues. Not that it matters, but this program would run in the background and record logins to each lab computer against the booking system for reserving the labs.

One such computer was recently upgraded to Windows 7 from XP as a result of the end-of-life support of XP (and a bit of common sense, which is known to happen once in a while).

So once the computer was freshly wiped and had Windows 7 installed & patched, my little program was installed... and it was dog slow.

I mean it was really slow. Where it used to go from launch to actually doing anything (such as becoming visible) in under a second, it was now taking 10 to 15 minutes before there was any evidence of it running. On the same machine.

Looking at the installed .NET frameworks on a working PC and the problem one, they were both the same (at the time, 4.5.1), and earlier versions were not installed side-by-side on either machine. The application was written in Visual Studio 2010 and targeted the 4.0 .NET framework.

Now keep in mind that the older machines used to have older versions of the framework which were then upgraded over time (2.0 -> 3.0 -> 3.5 -> 4.0 -> 4.5). However, being a clean install, the re-installed machine was set up with just .NET 4.5.1 without having passed through any of the earlier versions.

Turns out that was the problem.

Removing .NET, then re-installing it with 4.0 only, and then allowing it to update to 4.5.1 afterward on the problem machine fixed it. I don't know what the mechanism is that causes this, but when a .NET app is targeting an earlier framework than was ever installed on the destination PC, problems like this can happen.

IE11 is out - change in behaviour in Theatre Mode (F11)

We have some public display content (basically web-based slideshows on 42" screens around the building) that use Internet Explorer on Windows 7 in kiosk mode to render the content. And now IE11 for Win7 is out, so I figured I should test that out.

PS. The system was originally deployed with IE9, and the upgrade to IE10 caused no issues, and tests in IE11 show no rendering issues either.

However, when during the production cycle, to test content before deploying it I use IE in 'theatre mode' (F11) where the browser goes full-screen and the toolbars, window borders, etc are all hidden in order to have the same visual experience (on a monitor with t he same 1080p resolutions).

This is something I do often - and noticed that the behaviour in that mode has changed from IE10 to IE11: it used to be if you moved the mouse to the top edge of the screen the address bar etc will become visible, and then if you moved the mouse back down they will hide again.

No longer, now you have to right-click. Then it doesn't go away unless you exit F11 & then activate it again.

Anyone else seeing that? I haven't had a chance to test on multiple machines yet, so this may be some local oddity vs. a deliberate change in application behaviour.

Quick Byte: Script to set the homepage in IE

Quick Byte: VBScript to set the homepage in IE, possibly useful to set a standard setup for a corporate desktop, assuming you didn't have a better option like a GPO.

Dim WshShell

Set WshShell = CreateObject("Wscript.shell")

WshShell.RegWrite "HKEY_CURRENT_USER\Software\Microsoft\Internet Explorer\Main\Start Page", "http://parmedx.appspot.com", "REG_SZ"     


Set WshShell = Nothing
 

Quick Byte: Select all column names in a table

Quick Byte: T-SQL query to select all the field/column names from a given table:

Select COLUMN_NAME -- or Select * for all column attributes (ie data_type, default_value, ...)
From INFORMATION_SCHEMA.COLUMNS
Where TABLE_NAME = 'TableName'
Order By ORDINAL_POSITION

Applies to SQL Server.

Install network printers that are not already installed

Very handy VBScript which I found at WinVistaTips.com thanks to user "markm75". It lets you set up a list of network printers that a PC needs to have installed on it, checks to see which (if any) are not currently installed, and installs only the missing printers.

I've previously used .bat scripts to install network printers, but the problem with those is that the way we had to connect to them (using a command like the following: explorer \\printserver\printername$), once installed the print queue window would always be opened for every printer that was connected to. So if we had 3 printers that had to go on each PC and that script would run on each login, those 3 printers would run through the install process & the 3 print queue windows will open up on each login as well. Even if the printers were already installed.

This script handily works around both issues: printers that are already installed are just skipped from further processing, and if missing, the installation is silent without any dialogs or windows popping up (unless if UAC is required to install a driver or some such).

So here it is for posterity (in case those forums are ever shut down – and to improve it’s discoverability – it’s quite useful). Hopefully it will help others. I've adjusted the script a little from the original here, but it's identical where it counts.

Option Explicit

On Error Resume next

Dim Printers(3), WshNetwork, objWMIService, colPrinters, bFound, InstalledPrinter, Printer

'array of network printers we need to have installed

Printers(0) = "\\printserver\HP2600"    

Printers(1) = "\\printserver\HP4200"

Printers(2) = "\\printserver\Lexmark360"

Set WshNetwork = CreateObject("WScript.Network") 

Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")

' "." = local PC, else put in PC name instead of the dot

Set colPrinters = objWMIService.ExecQuery _

                ("Select * From Win32_Printer Where Local = False")     

                'Populate Printers Collection on the PC specified above

If colPrinters.Count <> 0 Then

    'loop through array of Printers we need to have installed

    For each Printer In Printers  

        bFound = False

        'loop through printers currently installed on target machine, 

        'compare each to the printer we need installed:

        For Each InstalledPrinter In colPrinters

            '.ShareName is the share ' servername for server 

            If Ucase(InstalledPrinter.ServerName & "\" & InstalledPrinter.ShareName) = _

               Ucase(Printer) Then

                   bFound = True 'if Printer is already installed/found, skip to next printer

                   Exit For

            End If

        Next 

        If bFound = False Then    'if Printer is not installed, install it

            If Printer <> "" Then '(if Printer is named/not null string)

                WshNetwork.AddWindowsPrinterConnection Printer

            End If

        End If        

    Next

End If

Set WshNetwork = Nothing

Set objWMIService = Nothing

Set colPrinters = Nothing

System File Checker

This is handy tool I found. Have you had a Windows system encounter problems due to corrupted system DLLs? A while back I encountered that on a Win7 system that would refuse to run Windows Updates with a "Program cannot start because sqmapi.dll is missing" error. It wouldn't run an offline SP1 install either due to the same problem.

The solution: System File Checker.

Steps:

1. Open an elevated command prompt (Start -> type "cmd" -> Right-click the cmd shortcut and select "Run as Administrator"
2. Provide admin credentials if UAC prompts you.
3. Type: SFC /scannow and press enter. The program will take a few minutes to check each system dll for corruption and for wrong versions, and replace them as needed.
4. When it's finished, type findstr /c:"[SR]" %windir%\Logs\CBS\CBS.log >%userprofile%\Desktop\sfcdetails.txt to put a log file onto your desktop, which you can review to see what was updated.
5. Repeat steps 3-4 two more times (run SFC three times) to ensure that no files were missed. You may want to reboot after each run - though when I did it I didn't have to.
6. Profit! (ie I was once again able to run Windows Update normally and get the Service Pack installed)

I've only used this on Windows 7 (Enterprise), but it's a feature that was introduced with Vista.

Microsoft support link:
http://support.microsoft.com/kb/929833