[RESOLVED] some tables not allowing update/insert

[ober0330]

So I have MSSQL Version 8 (I think that's 2003). This database is used with an old .NET application that I'm currently rewriting in PHP. Most of that is irrelevant, however.

I use one user to do all the work against the database. I can SELECT data from any table without problems. I can run UPDATE and INSERT statements against some of the tables. But there are a few tables where I cannot run UPDATE or INSERT statements from my application. I always get this error:

Incorrect syntax near the keyword 'and'.

Even when I don't have an "and" in my WHERE clause!? I can run the query directly in the database from Enterprise Manager without any issues, but I get the above when running from my application on those specific tables. I don't see any obvious differences between the tables that would cause this problem.

Can anyone shed any light on this issue?

[si_the_geek]

Can you show us the SQL statements, and the name of one of the tables and the fields in it.

So I have MSSQL Version 8 (I think that's 2003).

It is 2000.

[ober0330]

SQL statement: UPDATE DTPROJECTTRACKING SET Time_Unit = 1 WHERE CONTRACT_ID = '08132'

Here is the table:

CREATE TABLE [dbo].[DTPROJECTTRACKING] (
	[Contract_ID] [nchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Contract_serial_No] [int] NOT NULL ,
	[Project_ID] [nchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Service_ID] [nchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Time_Unit] [nchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Work_Complete] [decimal](18, 4) NULL ,
	[GPR] [decimal](18, 2) NULL ,
	[GER] [decimal](18, 2) NULL ,
	[Yr] [nchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[WriteOff] [decimal](18, 4) NULL ,
	[Billable] [decimal](18, 4) NULL ,
	[GPR_in_work_units] [decimal](18, 4) NULL ,
	[Tracked_Units] [decimal](18, 4) NULL ,
	[Revenue_Units] [decimal](18, 4) NULL ,
	[Mon] [nchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Exported] [bit] NULL ,
	[end_user_login] [nchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Modified] [bit] NULL ,
	[Discount_Value] [decimal](18, 2) NULL ,
	[Mtnc_Value] [decimal](18, 2) NULL ,
	[comment] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[unbilled_Tracked_Bill] [decimal](18, 4) NULL ,
	[unbilled_Bill_rev] [decimal](18, 4) NULL ,
	[consumed_unbilled] [decimal](18, 4) NULL ,
	[unbilled] [decimal](18, 4) NULL ,
	[reason_unbilled] [nchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[reasoncode_unbilled] [nchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

I didn't design the table so please ignore the crappy configuration.

Thanks for your help, please let me know if you need anything else!

[ober0330]

And if it helps, I'm using a global sa role in my connection string.

[techgnome]

Look to see if there are any triggers on the table... that's the only thing I can think of that would explain why some tables work and others don't. I bet there is a buggy trigger that's firing off.

-tg

[ober0330]

Hmm... didn't think of that. There are 43 stored procedures in the DB... any way to query those without looking through all 43??

[ober0330]

Oh wait, you meant trigger... not SP. Looking at the one trigger now.

[TysonLPrice]

You mean search the stored procedures for the table? If so:

----Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%yourtable%'
----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%YourTable%'

Credit for those goes here:

http://blog.sqlauthority.com/2006/12...red-procedure/

[TysonLPrice]

Oh wait, you meant trigger... not SP. Looking at the one trigger now.

Oops...too slow

[ober0330]

I wasted a ton of time on this yesterday.. should have come here first. It was the trigger. Thank you all!

[techgnome]

That's one reason I hate triggers... there isn't anything obvious to say that there is one... you have to intentionally go looking for it... and the error messages NEVER tell you that the error was in a trigger... just that there was "an error" ... At least you only lost a day.... I once lost a week because someone added a trigger and didn't document it.

-tg

[ober0330]

The crazy thing is that the functionality provided by the trigger is both inefficient and bad design. So add bad design on top of a flawed method of getting work done and you create a nightmare.

Thanks again for the suggestion!