Float comparison tolerance

Had the age-old problem with significant figures when comparing floats in Python today, and came up with this little function to deal with it. It ensures that the two values are equal down to 10 significant figures, no matter at what scale they start. This is as opposed to the conventional test, which checks at a fixed scale (e.g. return abs(a - b) < 1e-10).

def equal(a, b):
return abs(a - b) <= abs(a - b)/10000000000

Testing:

a = .0000000000000000837
b = .00000000000000004315
c = a + 100
d = b + 100

equal(a,b)
False

equal(b,a)
False

equal(c,d)
True

equal(d,c)
True

equal(1,1)
True

(Of course, we wound up defining a hard limit, 1e-10, and not using my function, but I still thought it was clever.)

UPDATE: Yes, I recognize that this is hardly a new problem or solution, but it's MY BLOG and I mostly use it as a memory aid anyway - outside readers are incidental (which is a good thing, given my Google Analytics stats).

Automated linked server test

Got a big production event scheduled, so I wrote this quick automated test to be incorporated into the post-event testing. I'll probably improve on it incrementally as I notice shortcomings, but at the moment it's just a basic test of data access via all linked servers set up on a machine.


/*** Linked Server Test ***/

declare @linked table (name sysname, done bit, retval int)
declare @srvr sysname
declare @sql varchar(255)
declare @retval int
insert into @linked select name, 0 as done, NULL as retval from sys.servers
while exists (select 1 from @linked where done = 0)
begin
select top 1 @srvr = name from @linked where done = 0
begin try
exec @retval = sp_testlinkedserver @srvr
end try
begin catch
set @retval = sign(@@error)
end catch
exec(@sql)
update @linked set done = 1, retval = @retval where name = @srvr
end
select * from @linked

pyodbc returns an int instead of rows in a cursor

Just a quick note about pyodbc: if you're getting a TypeError from a pyodbc cursor because it's an int instead of a rowset, it's probably because the proc or query being called is returning extra info or messages. Try turning off anything that's returning extraneous info, like ANSI_WARNINGS.

SQL 2008 installation problems

Several of us at my office had issues installing SQL 2008 RC0 - the installer kept failing the "Restart computer" check.

So I started digging through the registry to find the culprit flag... when this forum post turned up and pointed me to exactly the right place.

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3492057&SiteID=1

Turns out our virus scanning software always posts a pending file rename, which SQL setup interprets as the computer needing a reboot. I cleared these values and the installer ran fine.

UPDATE: Had the same problem with SQL 2008 RTM. Fix works for that too, and probably for many other installers that refuse to start due to a pending file operation.

Returning rows from a stored procedure with pyodbc, pt 2

Pyodbc really doesn't like extraneous information to be returned with its (ONE AND ONLY ONE!) result set from a stored procedure.

In my latest fight with pyodbc, I had a stored proc returning a rowset with a few thousand rows. This worked fine most of the time, except in a few cases with certain input parameters. In those cases, the cursor returned by execute() would contain an int, so any cursor method I called on it would break.

At first I thought I might have hit some kind of row size limit or rowcount limit, but realized the result set wasn't big enough for that to be the case. The proc wasn't throwing off any errors when run in SQL Management Studio with the same input parameters, so that wasn't the problem.

Then I noticed that it was returning a single warning message in the Messages tab.

Warning: Null value is eliminated by an aggregate or other SET operation.

This is a fairly common warning in SQL land, so I hadn't paid any attention to it. But it is possible to suppress, using the following statement:

SET ANSI_WARNINGS OFF

Sure enough, this did the trick. The moral of this story (just like last time) is that pyodbc wants a single rowset and that's it; returning any data in any of the alternate ODBC channels seems to break it, or at least, not yield the cursor in the result set that I expected.

Speeding up Vista User Access Control (UAC)

My HP laptop (tx1210) runs Vista Home Premium, and I'm constantly performing actions on it that pop up the User Access Control window. I don't have a problem with this in and of itself, as I've read Microsoft's side of the argument and I'm willing to give them the benefit of the doubt and assume that this does actually provide some additional security.

That said, it's slow as hell.

The UAC popup isn't bad on my monster desktop, which has a top-end Core 2 Duo, 4 GB of RAM, and a decent video card, but it takes FOREVER on my laptop - probably 3-5 seconds to black out the screen and pop up the prompt, and another 3-5 to black out the screen again and return me to what I was doing. This is unacceptable in 2008.

After reading a few unhelpful posts directing me to disable UAC completely, I found one that mentioned that it wasn't as slow with Aero (the pretty transparency and other visual effects package in the higher versions of Vista) disabled. So I opened the Performance Information & Tools feature in the Control Panel and selected Adjust Visual Effects. After turning off the vast majority (I left Use visual styles on windows and buttons because I just couldn't bear to look at the old grey XP-style taskbar any more), guess what? UAC is now lightning-fast and doesn't bother me at all.

I'll probably try re-enabling a few more settings and find the balance between speed and graphical sugar, but disabling most of the visual effects seems to have done the trick, and is already improving my Vista experience dramatically.

Python's subprocess doesn't like SmartFTP

Well, that was a frustrating afternoon. I was trying to automate the update process described in my previous post - an executable that uses SmartFTP's COM library to connect to an FTP server and download new files - by calling it from a Python script that would check the output and re-run the updater as necessary. I wanted to use the subprocess module, as I was advised by the Python docs that this was the New Cool Thing, and I wanted to use the environment variable functionality that it provided.

However, I consistently received the following error:

[20080506 19:05:00] SmartFTP FTP Library v1.5.8.21
[20080506 19:05:00] Resolving host name "ftp.******.com"
[20080506 19:05:00] Unable to resolve host name.
[20080506 19:05:00] 1

So I replaced the hostname with the IP address and got this error instead:

The requested service provider could not be loaded or initialized.

I found this phrase in the Microsoft Winsock documentation, at which point I had a mild aneurysm and went to work on something else for a while. I came back a little later to read that documentation, but it was unhelpful.

Finally I switched over to declaring the environment variables and calling the executable in a batch file and called that using os.system instead of subprocess... and it worked fine. So it appears that there's some incompatibility between this vendor app and/or SmartFTP's FTP library and Python's subprocess module. Guess I'll be using the tried and true os.system for now, because it's definitely not worth any more debugging time.