Skip to Content.
Sympa Menu

freetds - [freetds] FreeTDS, ODBC and SAS failure

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Ben Herman <bherman AT stat.brown.edu>
  • To: freetds AT lists.ibiblio.org
  • Subject: [freetds] FreeTDS, ODBC and SAS failure
  • Date: Fri, 10 Jan 2003 17:42:36 -0500

I've been trying to get freetds to work with unixodbc and SAS for a couple of Months now and have pretty much exhausted my welcome at SAS Tech support so I thought I would give the minds behind the scenes a try. Basically, I can open the database an see that there are tbles there but I can't actually retrieve the tables. I'm wondering if perhaps there might be something I forgot to set up on the SQL Server.

TIA
Ben

My system is:
Machine hardware: sun4u
OS version: 5.8
Processor type: sparc
Hardware: SUNW,Sun-Fire-880

and I'm trying to connect to a MS-SQL 2000 server on a dual processor Xeon machine (Dell)

UnixODBC 2.2.3

Freetds 0.60

isql, tsql, sqsh, etc all work perfectly... it's only when I try to use the ODBC interface that I get problems.

Depending on the TDS version I use either only 1 or 2 of the unittests pass all others fail.


==============================================================
t0002: FAIL
Error dropping??
==============================================================

2003-01-10 17:18:55 processing result tokens. marker is e3
2003-01-10 17:18:55 inside tds_process_default_tokens() marker is e3
2003-01-10 17:18:55 processing result tokens. marker is ab
2003-01-10 17:18:55 Msg 5701, Level 0, State 1, Server GAIA\TEST, Line 1
Changed database context to 'tempdb'.
2003-01-10 17:18:55 processing result tokens. marker is fd
Sending packet @ 2003-01-10 17:18:55
0000 01 01 00 20 00 00 00 00 64 72 6f 70 20 74 61 62 |... ....d rop tab|
0010 6c 65 20 23 6f 64 62 63 74 65 73 74 64 61 74 61 |le #odbct estdata|


Received header @ 2003-01-10 17:18:55
0000 04 01 00 80 00 33 01 00 |.....3..|


Received packet @ 2003-01-10 17:18:55
0000 aa 00 6c 00 00 0e 75 05 0b 00 57 43 61 6e 6e 6f |..l...u.. .WCanno|
0010 74 20 64 72 6f 70 20 74 68 65 20 74 61 62 6c 65 |t drop th e table|
0020 20 27 23 6f 64 62 63 74 65 73 74 64 61 74 61 27 | '#odbcte stdata'|
0030 2c 20 62 65 63 61 75 73 65 20 69 74 20 64 6f 65 |, because it doe|
0040 73 20 6e 6f 74 20 65 78 69 73 74 20 69 6e 20 74 |s not exi st in t|
0050 68 65 20 73 79 73 74 65 6d 20 63 61 74 61 6c 6f |he system catalo|
0060 67 2e 09 47 41 49 41 5c 54 45 53 54 00 00 01 fd |g..GAIA\T EST....|
0070 00 02 00 c7 00 00 00 00 |........|


2003-01-10 17:18:55 processing result tokens. marker is aa
2003-01-10 17:18:55 Msg 3701, Level 11, State 5, Server GAIA\TEST, Line 1
Cannot drop the table '#odbctestdata', because it does not exist in the system catalog.
2003-01-10 17:18:55 processing result tokens. marker is fd
Sending packet @ 2003-01-10 17:18:55
0000 01 01 00 2a 00 00 00 00 63 72 65 61 74 65 20 74 |...*....c reate t|
0010 61 62 6c 65 20 23 6f 64 62 63 74 65 73 74 64 61 |able #odb ctestda|
0020 74 61 20 28 69 20 69 6e 74 29 |ta (i int )|


Received header @ 2003-01-10 17:18:55
0000 04 01 00 11 00 33 01 00 |.....3..|


Received packet @ 2003-01-10 17:18:55
0000 fd 00 00 00 c6 00 00 00 00 |......... |


2003-01-10 17:18:55 processing result tokens. marker is fd
Sending packet @ 2003-01-10 17:18:55
0000 01 01 00 29 00 00 00 00 69 6e 73 65 72 74 20 23 |...)....i nsert #|
0010 6f 64 62 63 74 65 73 74 64 61 74 61 20 76 61 6c |odbctestd ata val|
0020 75 65 73 20 28 31 32 33 29 |ues (123) |


Received header @ 2003-01-10 17:18:55
0000 04 01 00 11 00 33 01 00 |.....3..|


Received packet @ 2003-01-10 17:18:55
0000 fd 00 10 00 c3 00 00 00 01 |......... |


2003-01-10 17:18:55 processing result tokens. marker is fd
Sending packet @ 2003-01-10 17:18:55
0000 01 01 00 2d 00 00 00 00 73 65 6c 65 63 74 20 2a |...-....s elect *|
0010 20 66 72 6f 6d 20 23 6f 64 62 63 74 65 73 74 64 | from #od bctestd|
0020 61 74 61 20 77 68 65 72 65 20 30 3d 31 |ata where 0=1|


Received header @ 2003-01-10 17:18:55
0000 04 01 00 1e 00 33 01 00 |.....3..|


Received packet @ 2003-01-10 17:18:55
0000 a0 00 02 01 69 a1 00 05 00 07 00 08 38 fd 00 10 |....i.... ...8...|
0010 00 c1 00 00 00 00 |......|


2003-01-10 17:18:55 processing result tokens. marker is a0
2003-01-10 17:18:55 processing result tokens. marker is a1
2003-01-10 17:18:55 inside tds_process_default_tokens() marker is a1
2003-01-10 17:18:55 processing result. type = 56, varint_size 0
2003-01-10 17:18:55 processing result tokens. marker is fd
Sending packet @ 2003-01-10 17:18:55
0000 01 01 00 23 00 00 00 00 73 65 6c 65 63 74 20 2a |...#....s elect *|
0010 20 66 72 6f 6d 20 23 6f 64 62 63 74 65 73 74 64 | from #od bctestd|
0020 61 74 61 |ata|


Received header @ 2003-01-10 17:18:55
0000 04 01 00 23 00 33 01 00 |...#.3..|


Received packet @ 2003-01-10 17:18:55
0000 a0 00 02 01 69 a1 00 05 00 07 00 08 38 d1 00 00 |....i.... ...8...|
0010 00 7b fd 00 10 00 c1 00 00 00 01 |.{....... ..|


2003-01-10 17:18:55 processing result tokens. marker is a0
2003-01-10 17:18:55 processing result tokens. marker is a1
2003-01-10 17:18:55 inside tds_process_default_tokens() marker is a1
2003-01-10 17:18:55 processing result. type = 56, varint_size 0
2003-01-10 17:18:55 processing result tokens. marker is d1
Sending packet @ 2003-01-10 17:18:55
0000 06 01 00 08 00 00 00 00 |........|


2003-01-10 17:18:55 inside tds_process_default_tokens() marker is d1
2003-01-10 17:18:55 processing row. column is 0 varint size = 0
2003-01-10 17:18:55 processing row. column size is 4
2003-01-10 17:18:55 clearing column 0 NULL bit
Received header @ 2003-01-10 17:18:55
0000 04 01 00 11 00 33 01 00 |.....3..|


Received packet @ 2003-01-10 17:18:55
0000 fd 00 20 00 fd 00 00 00 00 |.. ...... |

==============================================================
t0003: Fails
-- Expected another recordset
==============================================================


2003-01-10 17:23:09 processing result tokens. marker is fd
Sending packet @ 2003-01-10 17:23:09
0000 01 01 00 3f 00 00 00 00 73 65 6c 65 63 74 20 2a |...?....s elect *|
0010 20 66 72 6f 6d 20 23 6f 64 62 63 74 65 73 74 64 | from #od bctestd|
0020 61 74 61 20 73 65 6c 65 63 74 20 2a 20 66 72 6f |ata selec t * fro|
0030 6d 20 23 6f 64 62 63 74 65 73 74 64 61 74 61 |m #odbcte stdata|


Received header @ 2003-01-10 17:23:09
0000 04 01 00 34 00 33 01 00 |...4.3..|


Received packet @ 2003-01-10 17:23:09
0000 a0 00 02 01 69 a1 00 05 00 07 00 08 38 fd 00 11 |....i.... ...8...|
0010 00 c1 00 00 00 00 a0 00 02 01 69 a1 00 05 00 07 |......... .i.....|
0020 00 08 38 fd 00 10 00 c1 00 00 00 00 |..8...... ...|


2003-01-10 17:23:09 processing result tokens. marker is a0
2003-01-10 17:23:09 processing result tokens. marker is a1
2003-01-10 17:23:09 inside tds_process_default_tokens() marker is a1
2003-01-10 17:23:09 processing result. type = 56, varint_size 0
2003-01-10 17:23:09 processing result tokens. marker is fd
2003-01-10 17:23:09 processing result tokens. marker is a0
2003-01-10 17:23:09 processing row tokens. marker is a0
2003-01-10 17:23:09 inside tds_process_default_tokens() marker is a0
2003-01-10 17:23:09 processing row tokens. marker is a1
2003-01-10 17:23:09 inside tds_process_default_tokens() marker is a1
2003-01-10 17:23:09 processing result. type = 56, varint_size 0
2003-01-10 17:23:09 processing row tokens. marker is fd

==============================================================
connect: Fails
Unable to open data source (ret=-1)
SQL error 08001 -- [unixODBC]Could not find Servername parameter
==============================================================

01e0 00 00 00 00 00 00 0c 10 75 73 5f 65 6e 67 6c 69 |........u s_engli|
01f0 73 68 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |sh....... .......|


Sending packet @ 2003-01-10 17:29:38
0000 02 01 00 4c 00 00 00 00 00 00 00 00 00 00 0a 00 |...L..... .......|
0010 00 00 00 00 00 00 00 00 00 00 00 00 00 69 73 6f |......... ....iso|
0020 5f 31 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |_1....... .......|
0030 00 00 00 00 00 00 00 00 00 00 00 05 01 31 30 32 |......... ....102|
0040 34 00 00 04 00 00 00 00 00 00 00 00 |4........ ...|


2003-01-10 17:29:38 inside tds_process_login_tokens()
Received header @ 2003-01-10 17:29:38
0000 04 01 00 a1 00 33 01 00 |.....3..|


Received packet @ 2003-01-10 17:29:38
0000 e3 00 11 01 08 43 4c 49 4e 49 43 41 4c 06 6d 61 |.....CLIN ICAL.ma|
0010 73 74 65 72 ab 00 3c 00 00 16 45 02 00 00 27 43 |ster..<.. .E...'C|
0020 68 61 6e 67 65 64 20 64 61 74 61 62 61 73 65 20 |hanged da tabase |
0030 63 6f 6e 74 65 78 74 20 74 6f 20 27 43 4c 49 4e |context t o 'CLIN|
0040 49 43 41 4c 27 2e 09 47 41 49 41 5c 54 45 53 54 |ICAL'..GA IA\TEST|
0050 00 00 00 e3 00 09 03 05 69 73 6f 5f 31 01 00 ad |........i so_1...|
0060 00 20 01 04 02 00 00 16 4d 69 63 72 6f 73 6f 66 |. ......M icrosof|
0070 74 20 53 51 4c 20 53 65 72 76 65 72 00 00 5f 08 |t SQL Ser ver.._.|
0080 00 c2 e3 00 0b 04 04 31 30 32 34 04 34 30 39 36 |.......10 24.4096|
0090 fd 00 00 00 00 00 00 00 00 |......... |


2003-01-10 17:29:38 inside tds_process_default_tokens() marker is e3
2003-01-10 17:29:38 inside tds_process_default_tokens() marker is ab
2003-01-10 17:29:38 Msg 5701, Level 0, State 2, Server GAIA\TEST, Line 0
Changed database context to 'CLINICAL'.
2003-01-10 17:29:38 inside tds_process_default_tokens() marker is e3
2003-01-10 17:29:38 inside tds_process_default_tokens() marker is e3
2003-01-10 17:29:38 increasing block size from 4096 to 1024
2003-01-10 17:29:38 inside tds_process_default_tokens() marker is fd
2003-01-10 17:29:38 leaving tds_process_login_tokens() returning 1
Sending packet @ 2003-01-10 17:29:38
0000 01 01 00 14 00 00 00 00 75 73 65 20 43 6c 69 6e |........u se Clin|
0010 69 63 61 6c |ical|


Received header @ 2003-01-10 17:29:38
0000 04 01 00 66 00 33 01 00 |...f.3..|


Received packet @ 2003-01-10 17:29:38
0000 e3 00 13 01 08 43 4c 49 4e 49 43 41 4c 08 43 4c |.....CLIN ICAL.CL|
0010 49 4e 49 43 41 4c ab 00 3c 00 00 16 45 01 00 00 |INICAL..< ...E...|
0020 27 43 68 61 6e 67 65 64 20 64 61 74 61 62 61 73 |'Changed databas|
0030 65 20 63 6f 6e 74 65 78 74 20 74 6f 20 27 43 4c |e context to 'CL|
0040 49 4e 49 43 41 4c 27 2e 09 47 41 49 41 5c 54 45 |INICAL'.. GAIA\TE|
0050 53 54 00 00 01 fd 00 00 00 e2 00 00 00 00 |ST....... .....|


2003-01-10 17:29:38 inside tds_process_default_tokens() marker is e3
2003-01-10 17:29:38 inside tds_process_default_tokens() marker is ab
2003-01-10 17:29:38 Msg 5701, Level 0, State 1, Server GAIA\TEST, Line 1
Changed database context to 'CLINICAL'.
2003-01-10 17:29:38 inside tds_process_default_tokens() marker is fd
SQLGetFunctions: fFunction is 1
SQLGetFunctions: fFunction is 2
SQLGetFunctions: fFunction is 1001
SQLGetFunctions: fFunction is 3
SQLGetFunctions: fFunction is 4
SQLGetFunctions: fFunction is 72
SQLGetFunctions: fFunction is 55
SQLGetFunctions: fFunction is 5
SQLGetFunctions: fFunction is 6
SQLGetFunctions: fFunction is 56
SQLGetFunctions: fFunction is 40
SQLGetFunctions: fFunction is 7
SQLGetFunctions: fFunction is 8
SQLGetFunctions: fFunction is 58
SQLGetFunctions: fFunction is 9
SQLGetFunctions: fFunction is 41
SQLGetFunctions: fFunction is 10
SQLGetFunctions: fFunction is 11
SQLGetFunctions: fFunction is 12
SQLGetFunctions: fFunction is 59
SQLGetFunctions: fFunction is 13
SQLGetFunctions: fFunction is 60
SQLGetFunctions: fFunction is 15
SQLGetFunctions: fFunction is 1006
SQLGetFunctions: fFunction is 16
SQLGetFunctions: fFunction is 14
SQLGetFunctions: fFunction is 42
SQLGetFunctions: fFunction is 17
SQLGetFunctions: fFunction is 43
SQLGetFunctions: fFunction is 44
SQLGetFunctions: fFunction is 45
SQLGetFunctions: fFunction is 1014
SQLGetFunctions: fFunction is 46
SQLGetFunctions: fFunction is 47
SQLGetFunctions: fFunction is 61
SQLGetFunctions: fFunction is 62
SQLGetFunctions: fFunction is 63
SQLGetFunctions: fFunction is 18
SQLGetFunctions: fFunction is 48
SQLGetFunctions: fFunction is 64
SQLGetFunctions: fFunction is 19
SQLGetFunctions: fFunction is 65
SQLGetFunctions: fFunction is 66
SQLGetFunctions: fFunction is 67
SQLGetFunctions: fFunction is 49
SQLGetFunctions: fFunction is 20
SQLGetFunctions: fFunction is 50
SQLGetFunctions: fFunction is 21
SQLGetFunctions: fFunction is 1019
SQLGetFunctions: fFunction is 22
SQLGetFunctions: fFunction is 68
SQLGetFunctions: fFunction is 51
SQLGetFunctions: fFunction is 52
SQLGetFunctions: fFunction is 53
SQLGetFunctions: fFunction is 70
SQLGetFunctions: fFunction is 54
SQLGetFunctions: fFunction is 23
Sending packet @ 2003-01-10 17:29:38
0000 01 01 00 12 00 00 00 00 75 73 65 20 74 65 6d 70 |........u se temp|
0010 64 62 |db|


Received header @ 2003-01-10 17:29:38
0000 04 01 00 62 00 33 01 00 |...b.3..|


Received packet @ 2003-01-10 17:29:38
0000 e3 00 11 01 06 74 65 6d 70 64 62 08 43 4c 49 4e |.....temp db.CLIN|
0010 49 43 41 4c ab 00 3a 00 00 16 45 01 00 00 25 43 |ICAL..:.. .E...%C|
0020 68 61 6e 67 65 64 20 64 61 74 61 62 61 73 65 20 |hanged da tabase |
0030 63 6f 6e 74 65 78 74 20 74 6f 20 27 74 65 6d 70 |context t o 'temp|
0040 64 62 27 2e 09 47 41 49 41 5c 54 45 53 54 00 00 |db'..GAIA \TEST..|
0050 01 fd 00 00 00 e2 00 00 00 00 |......... .|


2003-01-10 17:29:38 processing result tokens. marker is e3
2003-01-10 17:29:38 inside tds_process_default_tokens() marker is e3
2003-01-10 17:29:38 processing result tokens. marker is ab
2003-01-10 17:29:38 Msg 5701, Level 0, State 1, Server GAIA\TEST, Line 1
Changed database context to 'tempdb'.
2003-01-10 17:29:38 processing result tokens. marker is fd

==============================================================
Date:
I don't understand this one at all
==============================================================
odbctest
--------

connection parameters:
server: 'CSS_DATA'
user: 'brown'
password: '????'
database: 'tempdb'
use tempdb
select convert(datetime, '2002-12-27 18:43:21')
Date returned: 2002-12-27 18:43:21
select convert(datetime, '2002-12-27 18:43:21')
Date returned: Dec 27 2002 06:43PM
Invalid returned date

==============================================================
Print:
I don't understand why this get "failed" either
==============================================================

2003-01-10 17:39:08 processing result tokens. marker is e3
2003-01-10 17:39:08 inside tds_process_default_tokens() marker is e3
2003-01-10 17:39:08 processing result tokens. marker is ab
2003-01-10 17:39:08 Msg 5701, Level 0, State 1, Server GAIA\TEST, Line 1
Changed database context to 'tempdb'.
2003-01-10 17:39:08 processing result tokens. marker is fd
Sending packet @ 2003-01-10 17:39:08
0000 01 01 00 1c 00 00 00 00 70 72 69 6e 74 20 27 54 |........p rint 'T|
0010 65 73 74 20 6d 65 73 73 61 67 65 27 |est messa ge'|


Received header @ 2003-01-10 17:39:08
0000 04 01 00 35 00 33 01 00 |...5.3..|


Received packet @ 2003-01-10 17:39:08
0000 ab 00 21 00 00 00 00 01 00 00 0c 54 65 73 74 20 |..!...... ..Test |
0010 6d 65 73 73 61 67 65 09 47 41 49 41 5c 54 45 53 |message.G AIA\TES|
0020 54 00 00 01 fd 00 00 00 f7 00 00 00 00 |T........ ....|


2003-01-10 17:39:08 processing result tokens. marker is ab
2003-01-10 17:39:08 processing result tokens. marker is fd


==============================================================
SAS:
When I simply try to open a table in SAS I get an empty table and this trace
==============================================================



2003-01-08 12:48:49 inside tds_process_default_tokens() marker is e3
2003-01-08 12:48:49 inside tds_process_default_tokens() marker is fd
SQLGetFunctions: fFunction is 1
SQLGetFunctions: fFunction is 2
SQLGetFunctions: fFunction is 1001
SQLGetFunctions: fFunction is 3
SQLGetFunctions: fFunction is 4
SQLGetFunctions: fFunction is 72
SQLGetFunctions: fFunction is 55
SQLGetFunctions: fFunction is 5
SQLGetFunctions: fFunction is 6
SQLGetFunctions: fFunction is 56
SQLGetFunctions: fFunction is 40
SQLGetFunctions: fFunction is 7
SQLGetFunctions: fFunction is 8
SQLGetFunctions: fFunction is 58
SQLGetFunctions: fFunction is 9
SQLGetFunctions: fFunction is 41
SQLGetFunctions: fFunction is 10
SQLGetFunctions: fFunction is 11
SQLGetFunctions: fFunction is 12
SQLGetFunctions: fFunction is 59
SQLGetFunctions: fFunction is 13
SQLGetFunctions: fFunction is 60
SQLGetFunctions: fFunction is 15
SQLGetFunctions: fFunction is 1006
SQLGetFunctions: fFunction is 16
SQLGetFunctions: fFunction is 14
SQLGetFunctions: fFunction is 42
SQLGetFunctions: fFunction is 17
SQLGetFunctions: fFunction is 43
SQLGetFunctions: fFunction is 44
SQLGetFunctions: fFunction is 45
SQLGetFunctions: fFunction is 1014
SQLGetFunctions: fFunction is 46
SQLGetFunctions: fFunction is 47
SQLGetFunctions: fFunction is 61
SQLGetFunctions: fFunction is 62
SQLGetFunctions: fFunction is 63
SQLGetFunctions: fFunction is 18
SQLGetFunctions: fFunction is 48
SQLGetFunctions: fFunction is 64
SQLGetFunctions: fFunction is 19
SQLGetFunctions: fFunction is 65
SQLGetFunctions: fFunction is 66
SQLGetFunctions: fFunction is 67
SQLGetFunctions: fFunction is 49
SQLGetFunctions: fFunction is 20
SQLGetFunctions: fFunction is 50
SQLGetFunctions: fFunction is 21
SQLGetFunctions: fFunction is 1019
SQLGetFunctions: fFunction is 22
SQLGetFunctions: fFunction is 68
SQLGetFunctions: fFunction is 51
SQLGetFunctions: fFunction is 52
SQLGetFunctions: fFunction is 53
SQLGetFunctions: fFunction is 70
SQLGetFunctions: fFunction is 54
SQLGetFunctions: fFunction is 23
odbc:SQLGetInfo: info option 47 not supported
odbc:SQLSetStmtOption: Statement option 5 not implemented
odbc:SQLGetConnectOption: Statement option 102 not implemented
odbc:SQLSetConnectOption: Statement option 101 not implemented
odbc:SQLSetStmtOption: Statement option 7 not implemented
odbc:SQLSetStmtOption: Statement option 6 not implemented
odbc:SQLSetStmtOption: Statement option 8 not implemented
odbc:SQLGetConnectOption: Statement option 102 not implemented


=======================================================
Benjamin A. Herman Tel: (401) 863-9188
Center for Statistical Sciences Fax: (401) 863-9182
Brown University, Box G-H
167 Angell Street
Providence, RI 02912
---------------------------------------------------------------------------------
bherman AT stat.brown.edu
=======================================================





Archive powered by MHonArc 2.6.24.

Top of Page