×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Dexterity Techniques

Creating Tables on SQL Systems by winthropdc
Posted: 2 Jan 05 (Edited 5 Jul 05)

Creating Tables on SQL Systems
==============================

On non-SQL systems, tables are automatically created the first time they are referenced.  However, on a SQL system we need to ensure that the user has the correct permissions before creating a table.  This means that the tables must be created as part of the installation process.

Below is some example code which can be called to create tables with their Dexterity Auto Stored Procedures and grant access to both. This method does not need the creation of an AutoGrant Stored procedure to work as it grants access with pass through SQL commands.

These examples are assuming that the tables you have created have been added to a new table group of the appropriate series.  If you are using Series 3rd party, you must also create entries in the SY_Pathnames table to ensure that the new tables are created in the correct datbases.

NOTE: In the script examples below, please replace the SYSTEM_TABLE_NAME placeholder with the name of your System Series table (to be created in the DYNAMICS database) and/or the COMPANY_TABLE_NAME placeholder with the name of your Company series (or Financial, Sales, Purchasing, Inventory, Payroll series) table (to be created in the current Company database).

Please create the SQLSaUser() and Grant_Table_Access() global functions first so that they can be used by the global procedures.


{ Global Function: SQLSaUser }

function returns boolean OUT_Access;

OUT_Access = false;
if 'SQL Server' of globals = 0 then
    abort script;
end if;

if SQLSaUser' of globals
    OUT_Access = true;
end if;

{ The following is using the new v8.00 functions }
if syUserInRole('User ID' of globals, ROLE_SYSADMIN) or
   (syUserIsDBO ('User ID' of globals, 'Intercompany ID' of globals) and syUserIsDBO ('User ID' of globals, SQL_SYSTEM_DBNAME)) then
    OUT_Access = true;
end if;



{ Global Function: Grant_Table_Access }

{ Grant Table Access for SQL }

function returns boolean OUT_Success;
in integer l_AltDictID;
in string IN_Table_Name;
in string IN_DB_Name;

local    long l_context;
local    long l_error;
local    string l_physical_name;

pragma(disable warning LiteralStringUsed);

if 'SQL Server' of globals = 0 then
    set OUT_Success to true;
    abort script;
end  if;

set l_physical_name to getPhysicalName(IN_Table_Name,l_AltDictID) of form XTableInformation;

if SQL_Connect(l_context) <> OKAY then
    set OUT_Success to false;
    abort script;
end if;

set l_error to SQL_Clear(l_context);
if SQL_CmdAppend(l_context, "use " + IN_DB_Name) = OKAY then
    if SQL_ExecCmd(l_context) <> OKAY then
        set OUT_Success to false;
        abort script;
    end if;
else
    set OUT_Success to false;
    abort script;
end if;


set l_error to SQL_Clear(l_context);
if SQL_CmdAppend(l_context, "grant all on " + l_physical_name + " to DYNGRP ") <> OKAY then
    set l_error to SQL_ExecCmd(l_context);
    if l_error <> OKAY then
        set OUT_Success to false;
        abort script;
    end if;
end if;


set OUT_Success to false;

{ Now send off the big SQL Statement for finding all of the stored procedures }
set l_error to SQL_Clear(l_context);
if SQL_CmdAppend(l_context, "DECLARE @command varchar(255) ") = OKAY then
if SQL_CmdAppend(l_context, "DECLARE TheCursor CURSOR for ") = OKAY then
if SQL_CmdAppend(l_context, " select 'grant all on '+ rtrim(name) + ' to DYNGRP' ") = OKAY then
if SQL_CmdAppend(l_context, " from sysobjects where name like 'zDP_"+l_physical_name+"%' ") = OKAY then
if SQL_CmdAppend(l_context, "set NOCOUNT on ") = OKAY then
if SQL_CmdAppend(l_context, "open TheCursor ") = OKAY then
if SQL_CmdAppend(l_context, "FETCH NEXT FROM TheCursor INTO @command ") = OKAY then
if SQL_CmdAppend(l_context, "while(@@fetch_status <> -1) begin ") = OKAY then
if SQL_CmdAppend(l_context, "    if (@@fetch_status <> -2) begin ") = OKAY then
if SQL_CmdAppend(l_context, "        exec (@command) ") = OKAY then
if SQL_CmdAppend(l_context, "    end ") = OKAY then
if SQL_CmdAppend(l_context, "    FETCH NEXT FROM TheCursor INTO @command ") = OKAY then
if SQL_CmdAppend(l_context, "end ") = OKAY then
if SQL_CmdAppend(l_context, "DEALLOCATE TheCursor ") = OKAY then
if SQL_CmdAppend(l_context, "set NOCOUNT off ") = OKAY then
    set l_error to SQL_ExecCmd(l_context);
    if l_error <> OKAY then
        set OUT_Success to false;
        abort script;
    else
        set OUT_Success to true;
    end if;
end if;
end if;
end if;
end if;
end if;
end if;
end if;
end if;
end if;
end if;
end if;
end if;
end if;
end if;
end if;


if SQL_Terminate(l_context) <> OKAY then
end if;

pragma(enable warning LiteralStringUsed);



{ Global Procedure: Create_System_Tables }

if 'SQL Server' of globals = 0 then
    abort script;
end if;

if not SQLSaUser() then
    abort script;
end if;

Table_SetCreateMode(true);    {Turns on create ability}
open table SYSTEM_TABLE_NAME
close table SYSTEM_TABLE_NAME
Grant_Table_Access(Runtime_GetCurrentProductID(), technicalname(table SYSTEM_TABLE_NAME), SQL_SYSTEM_DBNAME);

{ Repeat for other System Tables}

Table_SetCreateMode(true);    {Turns off create ability}



{ Global Procedure: Create_Company_Tables }

if 'SQL Server' of globals = 0 then
    abort script;
end if;

if not SQLSaUser() then
    abort script;
end if;

Table_SetCreateMode(true);    {Turns on create ability}
open table COMPANY_TABLE_NAME
close table COMPANY_TABLE_NAME
Grant_Table_Access(Runtime_GetCurrentProductID(), technicalname(table COMPANY_TABLE_NAME), 'Intercompany ID' of globals);

{ Repeat for other Company Tables}

Table_SetCreateMode(true);    {Turns off create ability}


David Musgrave [MSFT]
Senior Development Consultant
MBS Services - Asia Pacific

Microsoft Business Solutions
http://www.microsoft.com/BusinessSolutions

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.
This posting is provided "AS IS" with no warranties,
and confers no rights.  
 

Back to Microsoft: Dynamics GP (Great Plains) FAQ Index
Back to Microsoft: Dynamics GP (Great Plains) Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close