Hi

I'm getting an error whilst deleting a field in SalesLogix Version 7.2.

Error Message:

Dropping Field TableName.FieldName: Failed to parse SQL

I've had this occasionally over the last couple of years but i'm getting it more often now.

In the past I have been able to delete some fields but now I don't seem to be able to delete anything.

I've been told that it could be something to do with the field being changed in SQL Management Studio but this has never happened as all my fields get created and dropped within SalesLogix, I never amend anything outside of SalesLogix when it comes to fields.

Can anybody help as to why I could be getting this error?

Thanks in advance

Dave

Dave, run SLXProfiler, attach it to admin.exe and then run your delete again. This will capture what it does and, hopefully, you'll see why (in Error tab) what the SQL that causes the issue is.

Hi Mike

Thanks for the reply.

The code it seems to fall down on seems to be a simple select statement from one of my custom tables. The table has been in place for several years and have never had any issues with it, but it seems to just hang for quite a while whilst it is going through the deletion process then the Failed to parse SQL error is thrown out.

as I said the last SQL statement that is run is a select statement which if I grab and run as administrator it falls over as administrator does not own the table  as it is owned by sysdba. Could this have something to do with it?

I've never had to consider this in the past when deleting from this table.

Does anybody have any ideas why this could be falling over?

Thanks

Dave

What' the name of the field? (i.e. a reserved word that wasn't reserved 10 years ago....TYPE, STATUS, et al?). And I've seen issues with exact definitions of tables\fields not matching up with Resynctabledefs\Sectabledefs causing issues with Mail Merge, dropping a field, etc. Not to mention constraints and stored procs tied into tables \ fields wreaking all sorts of havoc. Take a hard look at SecTableDefs.......any gaps or duplicates in FieldOffset? Misspelled field names?

I've got one table for a big customer that I've tried to delete fields from several times.....each time they magically reappear.....I think that some Business Intelligence \ Integration  tool set is going after the table and continues to resurrect the definition... I can't edit the table in SQL Server for example ( a favorite trick of mine for changing field sizes or datatypes).

I've also done the drop column delete corresponding row in SecTableDef's....this works fine as long as the field is not a Field Level Security field in SecProfiles.

How far along on the hotfixes are you? Recall the issues with bringing in a field in a bundle and correctly listing fields in Field Level Security in 7.2.x...... My guess is that Sectabledefs\SecProfile is off slightly from sysobjects.....

Hi RJ

Thanks for the reply.

I've had a look in the SECTABLEDEFS at the fieldoffset column, now when you say have a look for gaps what do you mean by gaps? as most fields have an incremental number that is appearing but some are just set to 0 (zero). It's only on fields from particular tables but nevertheless there are set to 0 along with the SEQCODE field being set to 2.

Is this something to be concerned about?

As I mentioned the deletion of any fields just falls over with a failed to parse SQL error. I used to be able to delete fields but I did occasionally get this error which I couldn't do anything about, however i'm now getting it all the time.

It looks like it has been happening for quite a long time.

In terms of hot-fixes we are some what behind as we had a lot of customistation done on the database before my time and as a result at some point we stopped upgrading and applying fixes.

What would be the consequenses of dropping deleting the field ref in SECTABLEDEFS?

Just to be clear, i'm running everything on our test system which I refresh regularly (and can break if necessary...!!).

Any more advice would be appreciated.

Thanks

Dave

I meant the field INDEX column, not field offset. Field offset is for keeping track of Field Level Security (which could be off given you hot fix installs in 7.2 with its fix in about HF 21ish).

Field Index must be unique.....

I've dropped fields through SQL server and deleted the sectabledefs row.....it won't synch out....but works fine on LAN \ WEB\ SLX Mobile.

I use ALTER on tables to change column characteristics.....if you can't delete a field through the SLX OLE DB Provider GUI this is an indication that the systable object definition is different than the sectabledefs.....and possibly a trigger, constraint, clustered index is tied into that table\field.

Hi RJ

I've checked the index field and there are definitely no duplicates, howevere there are indexes that are missiing (out of numeric order) that look like they have been deleted - would this matter?

What are the consequences of deleting the field through SQL Server Management Studio and then removing the row in the SECTABLEDEFS table, is this safe to do?

Thanks again

Dave

There should be missing Field Indexes (a field has been deleted.....) unless you do a reorder on them .....not really necessary. In SLX 2.x that field index actually identified the field being changed through synch......too easy to break.

The reason you have to delete something through SQL Server Management Studio is that something doesn't match up with SLX (ResyncTabledefs, SecTableDefs) and the actual systable sql server. Wrong data type, missing a field. If you are in DBMAnager and can't manipulate a 'valid' SLX table then something's amiss. I've seen Mail Merge, Export to Excel, you name it, KO'd because SLX had a field as CHAR(8) and SQL Server called it varchar(8) (SLX automagically CHAR's length 12 and less string fields!).

Have also played with SET USERDEF = 'T' in sectabledefs.....now there's some magic...

I've spent hours mucking with this..... bottom line solution (off hours, back up the database).

1. Create a bundle with the create table, all joins, add any lookups.

2. Back up the table to a 'clone' table: ACCOUNT_BAK or what have you.

3. Drop the SLX Table in SLX, In SQL server clear out resynctabledef and SecTableDefs for that table.

4. Install the Bundle into an SLX EVAL Database.....make sure the Table is PERFECT, just like you want it. Then bundle it back up. ALTER in SQL Server can be used to adjust field lengths....data types......since there is no data in the table this should go fast.

5. Install the bundle into the original database......bring over the backup data including any conversions, data corrections, NULL instead of "", stuff.

No easy solutions.