creating stored procedures

UDA
Enthusiast

creating stored procedures

hi,

I am trying to create stored procedure in BTEQ but getting error.

Procedure:
CREATE PROCEDURE DDD()
BEGIN
DECLARE e CURSOR FOR
SELECT * from dw_indial.dim_application FOR READ ONLY;
END;

Error:
DECLARE e CURSOR FOR
$
*** Failure 3707 Syntax error, expected something like a name or a Unicode
delimited identifier or a 'TRANSACTION' keyword or a 'FASTEXPORT' keyword bet
ween the 'BEGIN' keyword and the 'DECLARE' keyword.

Please let me know if any issue in definition?
10 REPLIES
Enthusiast

Re: creating stored procedures

i tried making the procedure in SQL assistant:
CREATE PROCEDURE DDD()

BEGIN
DECLARE a INTEGER;

SELECT application_key into a from dim_application ;
END;

Its throwing error :
SPL1040:E(L3), Invalid definition for stored procedure 'E(3524):The user does not have CREATE PROCEDURE access to database dw_indial.'.

I am trying to make the procedure with DBC user and Database dw_indial is in DBC.

Please let me know where the issue is?
Enthusiast

Re: creating stored procedures

kindly update !!!
Enthusiast

Re: creating stored procedures

Child user does not necessarily inherit all the rights and privileges of the parent user. In other words, inspite of your user being under DBC user, you need to explicitly provide appropriate rights for execution of appropriate DDL/DML statements.
Enthusiast

Re: creating stored procedures

From TD Documentation

Privileges That Must Be Explicitly Granted

For Created User/Database: The created user or database does not get the following 12 privileges automatically on itself;they must be granted explicitly:

• ALTER EXTERNAL PROCEDURE
• ALTER FUNCTION
• ALTER PROCEDURE
• CREATE DATABASE
• CREATE EXTERNAL PROCEDURE
• CREATE FUNCTION
• CREATE PROCEDURE
• CREATE USER
• DROP DATABASE
• DROP USER
• EXECUTE FUNCTION
• EXECUTE PROCEDURE
Enthusiast

Re: creating stored procedures

hi

thanks for the information. I am trying to assign rights explicitely to DBC using teradata administrator. I want DBC to have rights on mis_monitoring schema.

Please let me know how can i assign the required rights. The snapshot of the available rights is attached.

Regards
Enthusiast

Re: creating stored procedures

hi,

i have given the required grants , now i am stuck with error 'C\C++ compiler not installed'

I installed vc++ express edition and followe the steps prescribed in one of the post:
---------------------------------------
If you installed VC Express into the default location, create a text file with the following contents:

CompilerPath:C:\Program Files\Microsoft Visual Studio 8\VC\bin\
LinkerPath:C:\Program Files\Microsoft Visual Studio 8\VC\bin\

The file should contain only the text shown. Assume it is saved at c:\cufvc.dat

Ensure the TD demo is running. Then run, from the TD Command prompt:

cufconfig -i -f c:\cufvc.dat -o

The output should show something like this:

Version: 4
ModTime: 1189876140
SWDistNodeID: 33
SourceDirectoryPath: C:\Program Files\NCR\TDAT\tdconfig\Teradata\tdbs_udf\usr\
CompilerTempDirectory: C:\Program Files\NCR\TDAT\tdconfig\UDFTemp\
UDFLibraryPath: C:\Program Files\NCR\TDAT\tdconfig\udflib\
CompilerPath: C:\Program Files\Microsoft Visual Studio 8\VC\bin\
LinkerPath: C:\Program Files\Microsoft Visual Studio 8\VC\bin\
UDFServerMemPath: C:\Program Files\NCR\TDAT\tdconfig\udfsrv\
MaximumCompilations: 10
UDFServerTasks: 2
SecureServerAMPs: 20
ParallelUserServerAMPs: 2
SecureServerPEs: 20
ParallelUserServerPEs: 2
TDSPLibBase: C:\Program Files\NCR\TDAT\tdconfig\tdsplib\
SecureGroupMembership: tdatudf

Exiting cufconfig...

Note the updated values of Compiler and Linker paths.

This will update the UDF GDO, and hopefully will allow you to build your UDFs successfully.

----------------------------

Still i am getting the same error.

Please suggest.
Enthusiast

Re: creating stored procedures

hi All,

thanks , i have re-installed teradata and its working fine now.

Re: creating stored procedures

hi, when i am executing procedure it shows "c/c++ compiler not installed".

please suggest to rectify that message.

Enthusiast

Re: creating stored procedures

You need to install the C/C++ compliler... this is required to compile the stored procedure!