Problem with odbc and Sql Server

[Frank Millman]

Hi all

I have found a problem using MS Sql Server connecting via the odbc
module from python-win32.

I am liaising with Mark Hammond, and he is trying to help, but he is
very busy, and I cannot be certain whether the problem originates with
the odbc module, with the ODBC Driver, or with Sql Server itself.

If anyone can help me to pinpoint this, I will be very grateful.

Assume a table 't1' with a column 'c1' of type varchar(10).

>From Python, set c1 to an empty string -
    cur.execute("UPDATE t1 SET c1 = ?",[''])

The result is that c1 is actually set to a string of 10 spaces.

If I execute the command without using parameters -
    cur.execute("UPDATE t1 SET c1 = ''")

it works - c1 is set to an empty string.

I am using Windows Server 2003 (SP1), Sql Server 2000 (SP4), and ODBC
Driver 2000.86.1830.00.

I tried it on Sql Server 2005, also using Windows Server 2003. It gives
the same problem.

Mark has a test suite which creates an MS Access database on the fly,
and allows you to run any odbc command against it. If I try the above
command with this setup, it works correctly. If I modify the test suite
to connect to my installed Sql Server, it fails. This seems to suggest
that the problem is not coming from the odbc module.

I have googled the MS Sql Server newsgroup, searching for 'empty
string'. I found several posts, but they were all to do with the fact
that, in an earlier version of Sql Server, trying to set a varchar
column to an empty string resulted in it being set to a single space. I
would have thought that, if my problem comes from the MS side, I would
find some reference to it, but I could not.

One way of proving it would be to execute the command from some other
tool that allows you to pass paramaterised commands via the ODBC driver
through to Sql Server. I seem to remember reading that you can do this
from VB and from MS Access, but I have no idea how to do this. I have
MS Access installed on my machine, so if anyone can talk me through the
steps required, I can give it a try myself. Alternatively, if anyone
has a setup where they can test this, I would be very interested to
hear the result.

Any suggestions will be much appreciated.

Thanks

Frank Millman