create access views dinamically

Database
Highlighted
Enthusiast

create access views dinamically

hi all,
I'm trying to create multiple access views with dbc.SysExecSQL while looping a FOR cursor.
When I end the statement without commit work, I get 3722 Only a COMMIT WORK or null statement is legal after a DDL Statement. And after this I can't run anything until I do a stanalone "commit work;".

 

When I end the statement with a commit work; I I get 5568 SQL statement is not supported within a stored procedure.

 

Is there any way to save this situation? thank you in advance.


VERSION 14.10.07.10


Accepted Solutions
Senior Supporter

Re: create access views dinamically

seems you run the SP in  ansi mode.

So either run it in Teradata mode or commit after every create view statement.

An DDL sets locks on DBC and therefore it is not allowed to keep long transactions open as this would seriously impact other user / sessions.

1 ACCEPTED SOLUTION
1 REPLY
Senior Supporter

Re: create access views dinamically

seems you run the SP in  ansi mode.

So either run it in Teradata mode or commit after every create view statement.

An DDL sets locks on DBC and therefore it is not allowed to keep long transactions open as this would seriously impact other user / sessions.