Archives of the TeradataForum

Message Posted: Wed, 11 Aug 2004 @ 09:40:22 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Maybe this is a good a place for a FAQ on DBC.SysExecSQL since this construct seems to be very confusing to most people.

     DBC.SysExecSQL

is an INTERNAL Stored procedure known only to the PARSER and only CALL-able from inside another Stored procedure. It doesn't exist in any dictionary table or take up any space on the system.

It is a place holder to get the compiler to ignore what is in the string and have it interpreted at RUNTIME.

Programmers can equate it to a

     SYSTEM(str);

call in your favorite programming or scripting language.

When the stored procedure is run, the string is created and passed to the parser as if it was SQL embedded in the original stored procedure and it is parsed and executed at that time.

There are no permissions required to CALL the DBC.SysExecSQL Store procedure. However, there is a restriction.

To CALL DBC.SysExecSQL in your Stored procedure, your stored procedure must be stored in your HOME/LOGON Database.

that is if your LOGON is BLORT and you are in database BLORT ;

you can create a stored procedure ( like BLORT.sp1 ) which uses

     CALL DBC.SysExecSQL  (:mystr);

If you logon as BLORT and your set up by default takes you to ( or you execute )

     database Finance;

you can't write store procedures which does

     CALL DBC.SysExecSQL  (:mystr);

you will get the error message

SPL1078:E(L227), User is not authorized to use the DDL/DCL/Dynamic SQL statement.

This means you are not in your Logon/UserID Database.

Reset your Default Database to BLORT and try again. If it still fails check the text file and make sure the name of the procedure is not database qualified or uses Database BLORT

     BLORT.something

You will need to make sure your DBA has granted Database/User BLORT PERM SPACE or you won't be able to create your stored procedure.

*** Failure 2644 No more room in database BLORT.

The Query

     sel databasename,permspace
         from dbc.databasesx;

will show user BLORT all the databases BLORT has access to. If BLORT doesn't have any space ( or does and it is still out of space ) you will need to talk to your DBA to get more space.

I hope this helps.

	<Prev	Next>		<<First	<Prev	Next>	Last>>