Possible bug?

Toad for SQL Server
1

On all versions of TOAD for SQL Server 5.x and 6.0, I am encountering an error in the SQL Editor when editing within the data grid. After editing a field in the grid and clicking the Save checkbox or moving off the record, I get an error dialog titled ‘Error posting changes’ with the text 'Incorrect syntax near the keyword ‘SET’. This only occurs on certain tables and the common denominator seems to be that the table contains a calculated field. Has anybody else seen this behavior and if verified, can it be put in the bug queue? Is there any workaround other than not using calculated fields?

Eric

2

Hello Eric,

Thank you for your report.

Can you share with us creating script of your table that generates the error when updating?

–Julia

3

This is the table script. Removing the calculated column SimplePostal removes the problem I’m seeing. My assumption is that TOAD doesn’t differentiate properly between normal and calculated columns when generating the UPDATE statement to post edits from the data grid.

CREATE TABLE [dbo].[OrgLocation] (

[LocationID] int IDENTITY(1, 1) NOT NULL,

[DepartmentID] int NOT NULL,

[ProjectID] int NULL,

[Name] varchar(100) NULL,

[Description] varchar(500) NULL,

[Street1] varchar(60) NULL,

[Street2] varchar(60) NULL,

[City] varchar(35) NULL,

[State] varchar(2) NULL,

[PostalCode] varchar(15) NULL,

[SimplePostal] AS (case when [Country] IS NULL OR [Country]=‘US’ then substring([PostalCode],(1),(5)) else [PostalCode] end),

[Province] varchar(35) NULL,

[Country] varchar(2) NULL,

[Latitude] float NULL,

[Longitude] float NULL,

[GeoAccuracy] tinyint NULL,

[Active] bit NOT NULL,

[IsDeleted] bit NOT NULL,

[UpdateDate] datetime NOT NULL,

[UpdatedBy] int NOT NULL)

4

Hi,

thanks for the script. I’ve checked with your table but I don’t get the same error. I was able to update data in all columns from this table without any errors. I didn’t remove SimplePostal column.

Please, can you add more details? Your steps, screenshots, Toad options…

–Julia

5

I’m working in Toad 6.0, Windows XP, SQL*Server 2008 connected via VPN to my target database. The steps are: 1) Open the Edit SQL window, 2) Issue a SELECT statement to retrieve the rows to edit, 3) Click the bottom left button to clear the Read Only flag and enable editing, 4) Edit the row. I don’t know what settings affect this, but don’t particularly remember doing anything after the default install.

The SQL statement used to retrieve the data prior to editing was:

SELECT *

FROM OrgLocation

WHERE LocationID IN (

SELECT PrimaryLocationID

FROM Organization

WHERE CEEBCode IN (‘310715’));

The generated statement (from SQL Profiler starts like:

exec sp_executesql N’UPDATE SET [Name] = @f1, Street1 = @f2, City = @f3, Latitude = @f4, Longitude = @f5, GeoAccuracy = @f6 WHERE (LocationID = @f7 OR (@f8 = 1 AND LocationID IS NULL)) AND (DepartmentID = @f9 OR (@f10 = 1 AND DepartmentID IS NULL)) AND (ProjectID = @f11 OR (@f12 = 1 AND ProjectID IS NULL)) AND …

The problem is obvious - the statement doesn’t supply the table name for the execute_sql stored procedure. It appears that the cause is related to the starting SQL statement somehow. I tried altering it to:

SELECT *

FROM OrgLocation

WHERE LocationID = 27984;

and the error disappeared. Similar syntax in other tables (without calculated fields) doesn’t seem to cause a problem. Puzzling…

6

Hi,

can you take a look on you settings from the ‘Define Custom Statements to Edit’ dialog?

JT-069.png

It can affect Data grid behaviour when update any row.

–Julia.

7

Looks like you’re onto something… when I use the SQL statement with the IN clause and select 'Define custom editing…" all fields appear in both Key Columns and Updatable Columns and Table Name is empty. That would explain the result, but it still seems like TOAD should properly infer all this from the statement/table. Looking at this and other tables, all of which have defined primary keys (type int), TOAD does not seem to infer key columns when a SQL statement is entered, but will do so when selecting the table, right-click, View Details, Data tab. It also fails to infer the table name in some cases - I’d speculate that this appears to occur if there is more than one FROM clause as there is in my example.

So, for example (these are invented, but show the impact of different statement formats):

SELECT *

FROM [table]

WHERE keyID = 1

  • Key columns are not recognized even though PK is defined
  • Key columns should reflect PK of the table

  • Table name OK

  • All columns populated to updatable columns

  • Columns should omit calculated columns and probably identity columns as well (which my PK column is)

SELECT KeyID, ColumnName

FROM [table]

WHERE keyID = 1

  • Key columns are not recognized even though PK is defined
  • Key columns should reflect PK of the table

  • Table name OK

  • All columns populated to updatable columns

  • Columns should omit calculated columns and probably identity columns as well (which my PK column is)

SELECT *

FROM [table]

WHERE keyID IN (

SELECT KeyID

FROM [table2]

WHERE x = 1)

  • All result columns populated to key columns
  • Key columns should reflect PK of the table
  • No table name populated
  • Table name of top-level statement should be populated
  • All result columns Updatable columns are correct
  • Columns should omit calculated columns and probably identity columns as well (which my PK column is)
8

Eric,

Can you please do me a favor and tell me what script toad sends back as update statement?

In order to do it, you should turn on (for troubleshooting only) EXECUTION TRACE (MainMenu>Tools>Execution Trace>Output)

When open OutputWindow (MainMenu>View>Output)

Right after that repeat your case and let me know what toad sends to database.

9

Actually I have reproduced your issue =) I have completely missed the fact you are in editor…

I have created CR 110694 to track it, hope it should be fixed in 6.1

Thanks for detailed instructions!