problem with variable assignment

[GoogleCodeExporter]

Hello,

What steps will reproduce the problem?
1. connected to MsSQL server
2. run the query including some assigmnets
3. runtime error raised

Example:
DECLARE @l int
SET @l = 15
SELECT @l

What is the expected output? What do you see instead?
Output should be 15.

Traceback (most recent call last):
  File "./get_all.py", line 29, in <module>
    for r in result:
pyodbc.ProgrammingError: No results.  Previous SQL was not a query.

What version of the product are you using? On what operating system?
* Ubuntu, 9.04. 
* libodbc++-dev 0.2.3+0.2.4pre compiled from sources without the unicode
* pyodbc-2.1.5 compilation with python2.6 fails;
* compiling with python25 is OK, however linking against -lodbc does not
work, so I replaced it with -lodbc++-mt
* tdsodbc 0.82-4 (from the distribution)

Please provide any additional information below.
Normal SELECT/INSERT queries are OK.

Thanks,
 Józsi

Original issue reported on code.google.com by steger.j...@gmail.com on 8 May 2009 at 12:18

[GoogleCodeExporter]

Hmmm...  This works on Windows 7 with SQL Server Express 2008, pyodbc 2.1.5, and
Python 2.6.

Can you provide an ODBC trace?  I'll generate one on Windows and compare.

Original comment by mkleehammer on 11 May 2009 at 11:56

• Changed state: Investigating

[GoogleCodeExporter]

Hello mkleehammer,

some more information about the SQL server:
Microsoft SQL Server Developer Edition (64-bit)
Microsoft Windows NT 6.0 (6002)
NT x64
10.0.2531.0

Running the tracer the following output was generated server side:

Audit Login -- network protocol: TCP/IP
set quoted_identifier off
set arithabort off
set numeric_roundabort off
set ansi_warnings off
set ansi_padding off
set ansi_nulls off
set concat_null_yields_null off
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
   steger     227446 68 2009-05-13 16:53:53.590   
SQL:BatchStarting use nmVO   steger     227446 68 2009-05-13 16:53:53.590   
SQL:BatchCompleted use nmVO   steger 0 0 0 0 227446 68 2009-05-13 16:53:53.590
2009-05-13 16:53:53.590  
SQL:BatchStarting SET IMPLICIT_TRANSACTIONS ON   steger     227446 68 2009-05-13
16:53:53.590   
SQL:BatchCompleted SET IMPLICIT_TRANSACTIONS ON   steger 0 0 0 0 227446 68 
2009-05-13
16:53:53.590 2009-05-13 16:53:53.590  
SQL:BatchStarting EXEC sp_datatype_info 93,3   steger     227446 68 2009-05-13
16:53:53.597   
SQL:BatchCompleted EXEC sp_datatype_info 93,3   steger 0 74 0 1 227446 68 
2009-05-13
16:53:53.597 2009-05-13 16:53:53.597  
SQL:BatchStarting SELECT nodeID, label, ip, hostname FROM 
Shared.getEtomicInfo()  
steger     227446 68 2009-05-13 16:53:53.607   
SQL:BatchCompleted SELECT nodeID, label, ip, hostname FROM 
Shared.getEtomicInfo()  
steger 0 38 0 1 227446 68 2009-05-13 16:53:53.607 2009-05-13 16:53:53.607  
SQL:BatchStarting 
DECLARE @l int
SET @l = 15
SELECT @l as x
   steger     227446 68 2009-05-13 16:53:53.617   
SQL:BatchCompleted 
DECLARE @l int
SET @l = 15
SELECT @l as x
   steger 0 0 0 0 227446 68 2009-05-13 16:53:53.617 2009-05-13 16:53:53.617  
Audit Logout    steger 0 112 0 173 227446 68 2009-05-13 16:53:53.590 2009-05-13
16:53:53.763

In the meantime at client I got:
Traceback (most recent call last):
  File "./get_all.py", line 34, in <module>
    for r in result:
pyodbc.ProgrammingError: No results.  Previous SQL was not a query.

In the code it was like
...

cnxn = connect("DRIVER={FreeTDS};DSN=%s;UID=%s;PWD=%s" % (dsn, username, 
password))
result=cnxn.cursor().execute("""
DECLARE @l int
SET @l = 15
SELECT @l as x
""")

And you cannot iterate over result.

Thanks for the investigation in advance,
Józsi

Original comment by steger.j...@gmail.com on 13 May 2009 at 8:11

[GoogleCodeExporter]

The server side trace looks normal.  Can you try a client-side trace?  It's 
looking
like either TDS or the driver.

Here's the test I ran on Windows:
from pyodbc import connect

cnxn   = connect("DRIVER={SQL
Server};server=localhost;database=forecast;trusted_connection=yes")
cursor = cnxn.cursor()

cursor.execute("""
               DECLARE @l int
               SET @l = 15
               SELECT @l as x
               """)
print cursor.description

> (('x', <type 'int'>, None, 10, None, None, True),)

Original comment by mkleehammer on 14 May 2009 at 12:27

[GoogleCodeExporter]

Hello mkleehammer,

I have a different connection string:
cnxn   = 
connect("DRIVER={FreeTDS};DSN=XXXXXXXX;UID=XXXXXXXXXXX;PWD=XXXXXXXXXXXX")

otherwise than that everything is the same, and the cursor.description is 

> None

I wonder how we can check if it is TDS/FreeTDS issue or not.

Thanks,
 Józsi

Original comment by steger.j...@gmail.com on 27 May 2009 at 9:07

[GoogleCodeExporter]

I am seeing this issue on windows connecting to an SQL Server 2008 instance.

The connection string looks like:
'DRIVER={SQL
Server};TDS_Version=8.0;SERVER=%(host)s;DATABASE=%(database)s;UID=%(username)s;P
WD=%(password)s'

cursor.description is None

I've tried both calling code like the above, and wrapping it in a stored 
procedure
and calling that with both the EXEC and {CALL ...} syntax. I have tried with and
without column aliasing and with and without an explicit cast to int.

A DB side trace shows all statements executing as expected. Although I have to 
admit
I have not idea what the sp_prepexec SP calls are supposed to look like.

Original comment by long.hai...@gmail.com on 30 Nov 2009 at 1:39

[GoogleCodeExporter]

hmmm, I solved the problem immediately after posting. Adding a SET NOCOUNT ON 
to the
SP I'd wrapped the code in made the problem go away. It seems with NO COUNT SQL
Server was returning a resultset that was not a resultset...

Original comment by long.hai...@gmail.com on 30 Nov 2009 at 1:43

[GoogleCodeExporter]

Original comment by mkleehammer on 24 Aug 2010 at 4:13

• Changed state: Done

[GoogleCodeExporter]

Original comment by mkleehammer on 21 Nov 2010 at 4:44

• Changed state: Complete