General Problems
Does pymssql support SQL Server 2005 {Express | Workgroup | Standard | Enterprise } edition?
I cannot connect to MS SQL at all! It's up and working, and other hosts connect to it normally. What's up?
What means "Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library"?
I can't connect to database named my-database. What's wrong?
Column names are truncated to 30 characters.
Linux/*nix Problems
What do I need FreeTDS for?
How to connect to non-default (named) instance?
How can I set the MS SQL port number? There's no such option in pymssql.
How to configure freetds.conf?
It keeps displaying "connection refused." What's with that port 4000? I've not set it anywhere!
I can't use it, Python displays that thelibsybdbwas not found.
All dates returned from queries have month shifted back by one.
I've got problems with text truncation -- strings returned from SQL are shorter than I'd expect.
Windows Problems
How to connect to non-default (named) instance?
Strings longer than 255 characters get truncated. Why?
General Problems
Q: Does pymssql support SQL Server 2005 {Express | Workgroup | Standard | Enterprise } edition?
A: Yes it does. I've tested it with almost all available SQL 2000/2005 releases, see the front page for details. To be able to connect to SQL 2005 you must select "Client components" and then "Connectivity components" upon installation of SQL 2005. Also keep in mind that SQL 2005 doesn't accept remote connections by default. You need to enable them in SQL Server Surface Area Configuration applet.
Q: I cannot connect to MS SQL at all! It's up and working, and other hosts connect to it normally. What's up?
A: Every time I receive such support request, it turns out that it's the <put your favorite network security term here> problem: a packet filter, a firewall, an IPS/IDS or other network device prevents your client host from connecting to MS SQL Server. Check those conditions carefully as they are sometimes very difficult to diagnose remotely. If you're 100% sure that it's not the network problem, try other FAQs, as they describe other connectivity problems.
Q: What means "Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library"?
A: If you connect to a SQL Server 2000 SP4 or SQL Server 2005, and if you make a SELECT query on a table that contains a column of type NTEXT, you may encounter the following error:
_mssql.error: SQL Server message 4004, severity 16, state 1, line 1:
Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier.
It's the SQL Server complaining that it doesn't support pure Unicode via TDS or older versions of ODBC. There's no fix for this error. Microsoft has deprecated DB-Library a long ago, in favor of ODBC, OLE DB, or SQL Native Client. Many new features of SQL 2005 aren't accessible via DB-Library so if you need them, you have to switch away from pymssql or other tools based on TDS and DB-Library.A workaround is to change the column type to NVARCHAR (it doesn't exhibit this behaviour), or TEXT.
Q: I can't connect to database named my-database. What's wrong?
A: my-database is not a valid database identifier; just use [my-database] with square brackets to overcome that problem.
Q: Column names are truncated to 30 characters.
A: Yes, the same behaviour can be observed under Windows and Linux, the same with PHP and its mssql connector. I did several tests and in every case column names were truncated to 30 characters. Sebastian Auriol <sebauriol at users.sourceforge.net> suggested obvious workaround:
SELECT very_very_long_column_name_1 AS col1, ... very_very_long_column_name_n AS coln, FROM tableLinux/*nix Problems
Q: What do I need FreeTDS for?
A: FreeTDS library is the communication layer, which is used on *nix systems by pymssql to connect to MS SQL servers and exchange data with them. It works similarly in other MS SQL plugins, be it Perl or PHP.
Q: How to connect to non-default (named) instance?
A: First, make sure that the SQL Server instance you're connecting to is configured to listen on a static TCP port. It can be set on the server using Server Network Utility available in the Microsoft SQL Server start menu group -- or in SQL Server Configuration Utility in case of SQL 2005. The simplest method to connect to this instance is then to use:
conn = _mssql.connect('sqlhost:4801', 'user', 'password')
(the port number shown is an example)
Q: How can I set the MS SQL port number? There's no such option in pymssql.
A: There are two methods. You can use the following notation:
conn = _mssql.connect('sqlhost:1433', 'user', 'password')
Or, alternatively, you can set up server aliases in freetds.conf. See below.Q: How to configure freetds.conf?
Q: It keeps displaying:
src/tds/login.c: tds_connect: 192.168.0.222:4000: Connection refused
What's with that port 4000? I've not set it anywhere!A: Port number can be set in freetds.conf, along with some other options. The default for FreeTDS is port 4000, hence that error.
Here's an example that shows how to configure MS SQL hosts and instances. There are two MS SQL instances installed on the same Windows 2000 computer. This configuration is currently in effect in one of my sites (here it's obfuscated, naturally...)# the default instance [SQL_A] host = sqlserver.example.com port = 1433 tds version = 7.0 # second (named) instance; the instance name is not used on *nix # I configured this instance to listen statically on port 1444 # Network configuration -> TCP/IP -> Properties -> Default port [SQL_B] host = sqlserver.example.com port = 1444 tds version = 7.0To connect use the name in [...]:
conn_a = _mssql.connect('SQL_A', 'userA', 'passwordA')
conn_b = _mssql.connect('SQL_B', 'userB', 'passwordB')More examples can be found here: http://www.freetds.org/userguide/freetdsconf.htm
Remember that FreeTDS library must be able to find your
freetds.conf, and must have permissions to read it. The default is system-wide/etc/freetds.conf(or similar), but if you aren't root, just be aware that FreeTDS looks for config in the following places:
- The environment variable FREETDSCONF can point to specific config, for example:
FREETDSCONF=/usr/pkg/etc/freetds.conf- Next it is being looked for in
~/.freetds.conf-- where ``~'' is home directory of the user running this Python process,- In a default system configuration directory, for example
/etc/freetds.confon Linux,/usr/local/etc/freetds.confon FreeBSD,/usr/pkg/etc/freetds.confon NetBSD,/opt/csw/etc/freetds.confon Solaris. Beware your vendor can specify another default place forfreetds.conf, one that I'm aware of is Mandriva and/etc/freetds_mssql.conf.You can mix SQL 2000 and SQL 2005 instances on one machine, if you need to. pymssql will have no problems connecting to them.
Q: I can't use it, Python displays the following:
>>> import _mssql
Traceback (most recent call last):
File "<stdin>", line 1, in ?
ImportError: Shared object "libsybdb.so.3" not foundA: Do check that your
/etc/ld.so.confcontains the directory where you installed libsybdb.so.3. It can be/usr/lib,/usr/local/libor/usr/pkg/freetds/libon NetBSD. Put proper directory in/etc/ld.so.confthen do ``ldconfig''. On Solaris I set LD_LIBRARY_PATH to the directory with libdb.so.* before launching Python.Q: All dates returned from queries have month shifted back by one, e.g. it returns 2005-00-01 where it should be 2005-01-01. What can I do?
A: The proper solution is to recompile FreeTDS with --enable-msdblib option, and the problem will disappear itself. If it's not possible, the workaround is to redesign your queries to return date converted to string instead of bare datetime. For example if the original is:
SELECT date FROM table
you can change it to:
SELECT CONVERT(CHAR(10),date,120) AS date FROM table
Then SQL will return string instead of date and FreeTDS + pymssql will not be processing it at all.Q: I've got problems with text truncation -- strings returned from SQL are shorter than I'd expect.
A: See info on text truncation here: http://www.freetds.org/userguide/troubleshooting.htm
Windows Problems
Q: How to connect to non-default (named) instance?
A: Just use the same notation as with other Windows applications connecting to MS SQL:
conn_a = _mssql.connect('sqlhost', 'userA', 'passwordA')
conn_b = _mssql.connect(r'sqlhost\instancename', 'userB', 'passwordB')If you also need to specify a port number in addition to the instance name, use the following syntax:
conn_c = _mssql.connect(r'sqlhost\instancename,portnumber', 'userC', 'passwordC')Q: Strings longer than 255 characters get truncated. Why?
A: All strings longer than 255 characters get truncated. It appears that types
varcharandnvarcharare affected. You have to cast them totextto get what you expect. This is a gift from Microsoft, an expression of their lack of support for DB-Library. See also this question. For the curious - for strings longer than 255 characters DB-Library function dbdatlen() returns 255, as well as function dbcollen() which holds maximum possible length for the column. dbdata() also returns already truncated string.