Share
ReportWe have a created a view in our Diamond dictionary with an outer left join on one of the tables in the Virtual Table Definition. To create our tables we have the following code:
... code to create a list of Tables to create
while ... not end of list
... get the table name and physical name
{Check if table does not exist in SQL}
set l_SQLText to "select name from sysobjects where xtype='u' and name = '" + 'D_Physical Name' of table S_Tables_TEMP + "'";
set l_SQLStatus to SQL_Execute(l_TableConnection, l_SQLText);
if l_SQLStatus = 0 then
l_SQLStatus = SQL_FetchNext(l_TableConnection);
if l_SQLStatus = 31 then {No results found so create the table}
{Create the table}
set l_Flag to Table_SetCreateMode(true);
open table l_WorkingTable with name l_TableName;
set l_Error to err();
get first table l_WorkingTable;
call S_DB_Test_Status, 1, _line_, _script_, err(),,,,,,true;
if empty(l_Error) then
close table l_WorkingTable;
end if;
end if;
end if;
end while
This table in some installations never creates with the "open table" functionality and throws an unhandled script exception in Dynamics. The dexsql.log shows the following:
CREATE VIEW dbo.DSL07000 (dVNDRID, VENDNAME, VCHRNMBR, DSTSQNUM, ACTNUMST, PRCHAMNT, dTXSCHDID, dTXAMOUNTORIGINAL, dTXINCLAMOUNT, dTXAMOUNT, dPOSTAMOUNT, dPJENTRYTYPE, dPJID, dPJPHASE, dPJPOSTAMOUNT, dCOSTTYPE, dDATE, dPOSTINGDATE, dDOCNMBR, dDESC, dDESC2, dVENDORTYPE, dEMPID, DOCTYPE, dDEFAULTVENDORTYPE, dDEFAULTEMPID, DEX_ROW_ID) AS /* 14.00.0085.000 */ SELECT DPM300.dVNDRID, PM00200.VENDNAME, DPM300.VCHRNMBR, DPM300.DSTSQNUM, GL00105.ACTNUMST, DPM300.PRCHAMNT, DPM300.dTXSCHDID, DPM300.dTXAMOUNTORIGINAL, DPM300.dTXINCLAMOUNT, DPM300.dTXAMOUNT, DPM300.dPOSTAMOUNT, DPM300.dPJENTRYTYPE, DPM300.dPJID, DPM300.dPJPHASE, DPM300.dPJPOSTAMOUNT, DPM300.dCOSTTYPE, DPM300.dDATE, DPM300.dPOSTINGDATE, DPM300.dDOCNMBR, DPM300.dDESC, DPM300.dDESC2, DPM300.dVENDORTYPE, DPM300.dEMPID, DPM300.DOCTYPE, ISNULL(DPM00201.dDEFAULTVENDORTYPE," "), ISNULL(DPM00201.dDEFAULTEMPID," "), DPM300.DEX_ROW_ID FROM DPM300 JOIN PM00200 ON ( DPM300.dVNDRID = PM00200.VENDORID ) JOIN GL00105 ON ( DPM300.DSTINDX = GL00105.ACTINDX ) LEFT OUTER JOIN DPM00201 ON ( DPM300.dVNDRID = DPM00201.VENDORID )
/*
/* Date: 05/27/2016 Time: 10:05:38
SQLSTATE:(S0022) Native Err:(207) stmt(163458616):*/
[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name ' '.*/
/*
/* Date: 05/27/2016 Time: 10:05:38
SQLSTATE:(S0022) Native Err:(207) stmt(163458616):*/
[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name ' '.*/
/*
It seems that the ISNULL(DPM00201.dDEFAULTVENDORTYPE," ") does not define a column name and SQL is not happy with this. Copying the above view into SQL Query Analyzer give me the same error for the Invalid column name.
Is this a bug in Dexterity that it isn't creating the view statement correctly or is there a setting in SQL Server that will allow this view to be created without the column being defined?
This was tested on GP 2015, SQL Server 11.0.3156
*This post is locked for comments
All responses (
Answers (
3