Skip to Content.
Sympa Menu

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

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "James K. Lowden" <jklowden AT freetds.org>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] SELECT querys fine, but INSERT query packets get malformed?
  • Date: Mon, 2 Feb 2009 00:31:09 -0500

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 ==



  • Re: [freetds] SELECT querys fine, but INSERT query packets get malformed?, James K. Lowden, 02/02/2009

Archive powered by MHonArc 2.6.24.

Top of Page