Help Needed With Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help Needed With Procedure

Author

Topic

abhinav19
Starting Member

7 Posts

Posted - 2005-08-13 : 05:00:03

Hi All

I have this scenario, We have our datawarehouse on Oracle. We use Analysis services to build the cube. Now I have made a DTS package to process the cube. I use the sp_start_job to run the package as job.

I have made one java class that will sit in Oracle databse & insert one row a table (one for each cube). I have made an AFTER INSERT trigger that will fire the sp_start_job whenever a row is inserted into that table. I want to log the exception that occurs when a user inserts a row when cube is under process. The exception occurs when the trigger fire the sp_start_job procedure thats why my java class inserts into both the tables i.e. cubetable & the cube_err_log.

I do not want the record to be in cubetable when exception occurs i just want the exception to be logged in the cube_err_log table.

Plz see the procedure below.

CREATE FUNCTION processCube (@tbName VARCHAR(25)) RETURNS VARCHAR
AS
BEGIN
DECLARE @strQry AS VARCHAR(100)
DECLARE @dttm AS DATETIME

SELECT @strQry = 'INSERT INTO ' + @tbName + ' VALUES(' + '''@dttm''' + ', null, null)'
@dttm = SELECT GETDATE()

BEGIN TRAN
EXECUTE (@srtQry)
IF (@@ERROR <> 0) BEGIN
INSERT INTO cube_err_log (cube_name, stime, err_msg) VALUES (@tbName, @dttm, @@ERROR)
ROLLBACK TRAN
RETURN "Error!"
END
RETURN "Success"
COMMIT TRAN

When I compile I get the following errors.

Server: Msg 170, Level 15, State 1, Procedure processCube, Line 9
Line 9: Incorrect syntax near '@dttm'.
Server: Msg 443, Level 16, State 1, Procedure processCube, Line 9
Invalid use of 'getdate' within a function.
Server: Msg 137, Level 15, State 1, Procedure processCube, Line 12
Must declare the variable '@srtQry'.
Server: Msg 170, Level 15, State 1, Procedure processCube, Line 19
Line 19: Incorrect syntax near 'TRAN'.

I want to achieve if my trigger returns an exception then, It should insert the error into the table cube_err_log.

How can I get the exact message thats in generated by the MS SQLServer for a particular error & return it to the calling module?

e.g. my java code generates this error

[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]SQLServerAgent Error: Request to run job FinanceBRS (from User sa) refused because the job is already running from a request by User sa.

Plz help!
Regards
Abhinav

querybest
Starting Member

22 Posts

Posted - 2005-08-13 : 05:06:25

try this please

CREATE FUNCTION processCube (@tbName VARCHAR(25)) RETURNS VARCHAR
@strQry AS VARCHAR(100),
@dttm AS DATETIME
AS
BEGIN
.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources