Problem with odbc and Sql Server

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

.

Relevant Pages

  • Re: Different empty_string/NULL handling under ODBC and OLE DB?
    ... NULL whereas empty string is stored as a space. ... When migrating from ODBC to OLE DB we get errors when inserting an ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • SQL Server ODBC Driver Ignores Authentication Setting
    ... set their ODBC connections with SQL Server Authentication, ... Microsoft Data Access Components 2.6 RTM, ... authentication to log into the database. ...
    (microsoft.public.sqlserver.security)
  • Re: Linking tables access - sql server 2005
    ... Another advantage of this method is that you don't need an ODBC setting on the local computer, ... Create a linked table to SQL Server without using a DSN ... Name of the table that you are linking to on the SQL Server database ... Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As ...
    (microsoft.public.access.adp.sqlserver)
  • RE: [ODBC SQL Server Driver][Shared Memory]ConnectionWrite (send()
    ... the ODBC source using named pipe, but I don't know how to enable named pipes ... If it is not working I am going to use ado to connect to sql server. ... Microsoft Online Community Support ...
    (microsoft.public.sqlserver.connect)
  • Re: [PHP] ODBC and long text fields
    ... We've a PHP app that uses ODBC to talk to a MS SQL server. ... The internal code for a query is ...
    (php.general)