I got an interesting query from a guy on how to alter a table without using any SQL statements from within Dexterity.
As per the Dexterity Guide, a table cannot be altered from within Dexterity, that means only thru’ Dex coding. If you’re converting tables stored in a SQL database, you must use pass-through SQL to perform the conversion.
Having said that, we can do it from Dexterity but calling a SQL Stored Procedure which will contain the necessary SQL Statements for that table alteration.
1. Make the necessary table changes on Dex Dictionary.
2. Write a SQL Stored Procedure like below (The SQL SP is written on SQL Server 2005 DB and uses SQL 2005 syntaxes):
/* Drop the SP if it exists */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[Custom_Tables_Alteration]‘) AND type in (N’P‘, N’PC‘))
DROP PROCEDURE [dbo].[Custom_Tables_Alteration]
/* Create the SP */
CREATE PROCEDURE Custom_Tables_Alteration
/* This statement will move the records from the actual table that needs to be altered to a Temp Table */
SELECT * INTO [custom_table_temp] FROM [custom_table]
/* This statement will truncate the table before it is being altered and then alter the table based on the changes that you have done on Dex Dictionary */
TRUNCATE TABLE [custom_table]
ALTER TABLE [custom_table]… … …
/* This statement will move back the records from the Temp table to the Altered Table */
INSERT INTO [custom_table]([field1], [field2], …, [fieldn]) SELECT [field1], [field2], …, [fieldn] FROM [custom_table_temp]
/* This statement will grant the necessary privileges to the DYNGRP Role */
GRANT SELECT, INSERT, UPDATE, DELETE ON [custom_table] TO DYNGRP
3. Write a Dex Trigger on a Successful Company Login, to execute this SQL Stored Procedure, by using call sproc statement. NOTE: This has to be executed only if the user logged on is SA. And we should design this in such a way that it does not alter the table as and when you log on to GP as SA. That’s very important. Well, there are so many ways using which we can validate this.
So that’s it. We are good to go with the new table definition.
Now the points that we need to understand:
1. We should take care of the relevant Table Alteration on both Dex Dictionary and SQL.
2. We should take care of the removed fields and the data which were stored on that field before this alteration.
3. If a field is added to the table and is defined as NOT NULL, then we should make sure that we include that field on the INSERT INTO … SELECT … statement in the above stored procedure and assign a relevant value (either a ZERO or an empty string ”, not NULL STRING).
This is just one way of doing the Table Alterations. There can be more. I would update this post as and when I get something better than this.
Hi VaidyThis technique does not cover changes to indexes or re-creating the zDP auto stored procedures.I was planning to post some methods to handle table changes in the next few weeks. I will give you some alternative methods to try out.Davidhttp://blogs.msdn.com/DevelopingForDynamicsGP/
Vaidy, Can I create(added extra index,…) the table with same physical name of table which I want to alter? This method will make any impact in GP core table structure?-Jeganeedhi
Well, that’s exactly what David had mentioned on his comment. The approach which I explained will not create/recreate the Table Indexes. But we can recreate the Indexes for any table from within Dex using Table_CreateIndexes() functions.I am working on this piece of information, and also waiting for David’s article/inputs on a more professional and exact steps on Altering a Table.Vaidy
Hi,Do you have a second part of this article?Thanks, Ariel
Hi Ariel,I did not get a better approach after this article. And it's been very long time after this article. I will review this and get back with another article in near future.Vaidy