Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 4 of 4 total |
Database specifier in a JOB |
Wed, Aug 26 2015 1:19 AM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Hi
I have written a script that works OK in EDB as long as I am positioned on a database. If not I get a message like this:- ElevateDB Error #403 An error occurred with the statement at line 4 and column 25 (The database Configuration is read-only and this operation cannot be performed (DROP TABLE ExportUsers)) I want to run this SQL as a JOB, but it won't run, presumably for the same reason. I've tried to use database specifiers like this 'DROP TABLE "DB9999"."ExportUsers"', but that gives a syntax error because of the specifier. Somehow I need to create link to the database that will work in a JOB. My script is below the signature Cheers Jeff -- BEGIN BEGIN EXECUTE IMMEDIATE 'DROP TABLE "ExportUsers"'; EXCEPTION END; EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE "ExportUsers" ( "Name" VARCHAR(40), "Password" VARCHAR(40), "Roles" VARCHAR(1024), PRIMARY KEY ("Name") )' ; EXECUTE IMMEDIATE ' INSERT INTO "ExportUsers" SELECT U.Name, U.Password, CAST(LIST(UR.Name) AS VARCHAR(1024)) AS "Roles" FROM Configuration.Users U LEFT OUTER JOIN Configuration.UserRoles UR ON UR.GrantedTo = U.Name WHERE UR.Name <> ''Public'' AND UR.Name <> ''Administrators'' GROUP BY U.Name ORDER BY U.Name' ; -- -- more stuff ....... -- EXECUTE IMMEDIATE ' EXPORT TABLE "ExportUsers" TO "ExportUsers.CSV" IN STORE "STORE-Transfers"' ; BEGIN EXECUTE IMMEDIATE 'DROP TABLE "ExportUsers"'; EXCEPTION END; END |
Wed, Aug 26 2015 2:46 AM | Permanent Link |
Uli Becker | Jeff,
> I have written a script that works OK in EDB as long as I am positioned > on a database. USE [<DatabaseName>] is what you want. From the manual: Usage -------------------------------------------------------------------------------- Use this statement in a script or job definition to open a new database or close a database that was previously opened. To open a new database, specify the database name after the USE keyword. Opening a new database automatically closes the current database that is open. To close the current database and not open a new database, leave the database name blank. This reverts the current database back to the default database, which is always the system-defined Configuration database for jobs. Uli |
Wed, Aug 26 2015 4:57 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | On 26/08/2015 6:46 p.m., Uli Becker wrote:
> Jeff, > >> I have written a script that works OK in EDB as long as I am positioned >> on a database. > > USE [<DatabaseName>] > > is what you want. > > From the manual: ... Thanks Uli I *have* read the manual from end to end, honestly As always, the solution is obvious once you know! Cheers Jeff -- |
Mon, Aug 31 2015 11:19 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Jeff,
<< I have written a script that works OK in EDB as long as I am positioned on a database. >> Sorry I missed this post. I got totally tied up in a new EWB release last week, and missed the DBISAM/EDB forums for a couple of days. My apologies. Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Tuesday, May 7, 2024 at 06:25 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |