[freetds] SELECT querys fine, but INSERT query packets get malformed?

James K. Lowden jklowden at freetds.org
Mon Feb 2 00:31:09 EST 2009


Scott Kovach wrote:
> We CAN connect, send queries, and get results from querys from the 
> remote sql server just fine.  However, all of the "INSERT" or "UPDATE" 
> queries fail, and the response is "Incorrect syntax near the keyword 
> 'and'." (note:  there is no 'and' in the query) The DBA of the remote 
> sql server is saying that they fail due to some problem with the packets
> 
> sent:
> 
> "Error: 17832, Severity: 20, State: 7
> Connection opened but invalid login packet(s) sent.  Connection closed.

I don't see any such error in the log you sent, nor any protocol problem. 
The server accepts your packets.  I've also never seen that error. 
Severity 20 messages are quite rare.  This might help:

	http://support.microsoft.com/kb/169521

Emphasis on "might".  

But that also might be a blind alley, because there is a real error
message in your log, as you mention.  I suspect a trigger, or perhaps many
triggers.  

You send a simple insert statement.  In response, you'd normally get one
DONE packet.  But the servers sends -- count 'em -- 33 DONEINPROC packets!
 

A little tutorial example is appended to this message to help explain the
significance of those packets.  .  

> I dont see how the sql server response ... can be caused by the packets
> sent

It can't.  I'd look at the constraints and triggers for VA_ORDHDR.  

To satisfy your DBA, try this:

	select * into ##VA_ORDHDR 
	from VA_ORDHDR where 0=1
	INSERT INTO ##VA_ORDHDR 
		(VA_ORDER_NUMBER,VA_BUYERID,SHIP_CODE) 
	VALUES ('1411-1','SC1276191463KO','VA03R')

I bet that works.  

> If anyone can tell me if the failed inserts/updates are a freetds/php
> issue or whether its an issue on the remote sql server, i would be
> forever grateful. 

Your inserts and updates are being rolled back by the server because of
the error.  

You can take PHP out of the equation by issuing your INSERT statement via
bsqldb or tsql.  I'm sure you'll see the same behavior.  Note however that
this kind of thing can be highly data-dependent, because the trigger will
respond differently depending on what it's presented with.  

In sum:

1.  In the log you attached, the login packet was accepted by the server. 
FreeTDS will *not* send malformed login packets.  The source of your 17832
error, if any, very likely lies elsewhere.  

2.  The server's response packet indicates extensive processing in
reaction to your insert statement.  Somewhere in that code is your
mysterious "and".  

HTH.  

--jkl

P.S. Exempli Gratia

Given two identical tables and two triggers:

1> create table H (name varchar(10), value int, primary key(name))
2> go
1> create table D (name varchar(10), value int, primary key(name))
2> go
1> create trigger Hins on H for insert 
   as 
	print 'inserted into H, inserting into D' 
	insert D select * from inserted 
	print 'end Hins'
2> go
1> create trigger Dins on D for insert as print 'executing Dins'
2> go

When inserting into H, the trigger copies the row to D.  The server
returns the print messages and the the DONEINPROC packets to indicate the
progress.  

$ grep -E 'Received|marker|msgno|dbcmd' dump | grep -v default
...
dblib.c:1247:dbcmd(0x8055000, insert H values ('one', 1)
net.c:652:Received packet
token.c:512:processing result tokens.  marker is  ab(INFO)
dbutil.c:87:msgno 0: "inserted into H, inserting into D"
token.c:512:processing result tokens.  marker is  ff(DONEINPROC)
token.c:512:processing result tokens.  marker is  ab(INFO)
dbutil.c:87:msgno 0: "executing Dins"
token.c:512:processing result tokens.  marker is  ff(DONEINPROC)
token.c:512:processing result tokens.  marker is  ff(DONEINPROC)
token.c:512:processing result tokens.  marker is  ab(INFO)
dbutil.c:87:msgno 0: "end Hins"
token.c:512:processing result tokens.  marker is  ff(DONEINPROC)
token.c:512:processing result tokens.  marker is  79(RETURNSTATUS)
token.c:512:processing result tokens.  marker is  fd(DONE)

Notice how after every PRINT message there's a DONEINPROC packet.  Now
pair those up and ignore them.  There's one more DONEINPROC.  

A trigger that affects data looks, on the wire, like a stored procedure. 
The Dins trigger doesn't affect any data and so produces no DONEINPROC
packet.  The Hins trigger OTOH does affect data, and produces not only a
DONEINPROC but a RETURNSTATUS as well.   

Whereas an insert into a triggerless table elicits no DONEINPROC packets: 

dblib.c:1247:dbcmd(0x8055000, select * into #H from H where 0=1 insert #H
values ('one', 1)
net.c:652:Received packet
token.c:512:processing result tokens.  marker is  fd(DONE)
token.c:512:processing result tokens.  marker is  fd(DONE)

One DONE packet for each statement.  

== example ends ==



More information about the FreeTDS mailing list